Newer
Older
import junit.framework.Assert.assertEquals
import org.junit.Test
import unibz.cs.semint.kprime.adapter.service.XMLSerializerJacksonAdapter
import unibz.cs.semint.kprime.domain.dql.*
import unibz.cs.semint.kprime.usecase.common.SQLizeSelectUseCase
import unibz.cs.semint.kprime.usecase.common.UnSQLizeSelectUseCase
import kotlin.test.assertEquals as assertEquals1
val query = Query()
var select = query.select
var attr = Attribute()
attr.name = "Name"
select.attributes.add(attr)
attr = Attribute()
attr.name = "Surname"
select.attributes.add(attr)
val fromT1 = From()
fromT1.tableName = tableName
select.from.add(fromT1)
select.where.condition = "Name='Gigi'"
return query
}
private fun unionFixtureQuery(): Query {
var query = Query()
query.select = simpleQueryFixture("Table1").select
var simple2 = simpleQueryFixture("Table2")
val union = Union()
return query
}
@Test
fun test_from_simple_query_to_xml() {
// given
val query = simpleQueryFixture("Table1")
// when
var queryXml = XMLSerializerJacksonAdapter().prettyQuery(query) as String
// then
<select>
<attributes>
<attributes name="Name"/>
<attributes name="Surname"/>
</attributes>
<from>
</from>
<where condition="Name='Gigi'"/>
</select>
<union>
<selects/>
</union>
<minus>
<selects/>
</minus>
</query>
""".trimIndent(),queryXml)
}
@Test
fun test_from_simple_query_to_sql() {
// given
val query = simpleQueryFixture("Table1")
// when
var querySql = SQLizeSelectUseCase().sqlize(query)
SELECT "Name","Surname"
FROM Table1
WHERE Name='Gigi'
""".trimIndent(),querySql)
@Test
fun test_from_simple_select_to_sql(){
// given
val select = Select()
val att = Attribute()
select.attributes.add(att)
val from = From()
from.tableName="tab"
select.from.add(from)
select.where.condition="a = b"
// when
val selectSql = SQLizeSelectUseCase().sqlize(select)
FROM tab
WHERE a = b
""".trimIndent(),selectSql)
}
@Test
fun test_simple_join_to_sql(){
// given
val select = Select()
val att = Attribute()
att.name="ww"
select.attributes.add(att)
val from = From()
from.tableName="Orders"
val join = Join()
join.joinLeftTable = "Orders"
join.joinOnLeft = "customerId"
join.joinRightTable = "Customer"
join.joinOnRight = "customerId"
join.joinType = "INNER"
from.addJoin(join)
select.from.add(from)
select.where.condition="a = b"
// when
val selectSql = SQLizeSelectUseCase().sqlize(select)
// then
assertEquals1("""
SELECT "ww"
FROM Orders
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
INNER JOIN
ON Orders.customerId = Customer.customerId
WHERE a = b
""".trimIndent(),selectSql)
}
@Test
fun test_multiple_join_to_sql(){
// given
val select = Select()
val att = Attribute()
att.name="ww"
select.attributes.add(att)
val from = From()
from.tableName="Orders"
val join = Join()
join.joinLeftTable = "Orders"
join.joinOnLeft = "customerId"
join.joinRightTable = "Customer"
join.joinOnRight = "customerId"
join.joinType = "INNER"
from.addJoin(join)
val join2 = Join()
join2.joinLeftTable = "Customer"
join2.joinOnLeft = "orderId"
join2.joinRightTable = "Sales"
join2.joinOnRight = "orderId"
join2.joinType = "LEFT"
from.addJoin(join2)
select.from.add(from)
select.where.condition="a = b"
// when
val selectSql = SQLizeSelectUseCase().sqlize(select)
// then
assertEquals1("""
SELECT "ww"
FROM Orders
INNER JOIN
ON Orders.customerId = Customer.customerId
LEFT JOIN
ON Customer.orderId = Sales.orderId
WHERE a = b
""".trimIndent(),selectSql)
}
@Test
fun test_from_union_query_to_xml() {
// given
val query = unionFixtureQuery()
// when
var queryXml = XMLSerializerJacksonAdapter().prettyQuery(query) as String
// then
<select>
<attributes>
<attributes name="Name"/>
<attributes name="Surname"/>
</attributes>
<from>
</from>
<where condition="Name='Gigi'"/>
</select>
<union>
<selects>
<selects>
<attributes>
<attributes name="Name"/>
<attributes name="Surname"/>
</attributes>
<from>
</from>
<where condition="Name='Gigi'"/>
</selects>
</selects>
</union>
<minus>
<selects/>
</minus>
</query>
""".trimIndent(),queryXml)
}
@Test
fun test_from_union_query_to_sql() {
// given
val query = unionFixtureQuery()
// when
var querySql = SQLizeSelectUseCase().sqlize(query)
SELECT "Name","Surname"
FROM Table1
WHERE Name='Gigi'
UNION
SELECT "Name","Surname"
FROM Table2
WHERE Name='Gigi'
""".trimIndent(),querySql)
}
@Test
fun test_from_xml_to_query() {
}
@Test
fun test_from_minimal_sql_to_query() {
// given
val sqlQuery="""
SELECT *
FROM tab1
""".trimIndent()
val query = UnSQLizeSelectUseCase().fromsql("query1",sqlQuery)
assertEquals("*",query.select.attributes[0].name)
assertEquals("tab1",query.select.from[0].tableName)
}
@Test
fun test_from_conditional_sql_to_query() {
// given
val sqlQuery="""
SELECT alfa,beta
FROM tab1
WHERE a = b
""".trimIndent()
val query = UnSQLizeSelectUseCase().fromsql("query1",sqlQuery)
assertEquals("alfa",query.select.attributes[0].name)
assertEquals("beta",query.select.attributes[1].name)
assertEquals("tab1",query.select.from[0].tableName)
assertEquals("a = b",query.select.where.condition)
}
@Test
fun test_from_3_union_sql_to_query() {
// given
val sqlQuery="""
SELECT alfa,beta
FROM tab1
WHERE a = b
UNION
SELECT gamma,theta
FROM tab2
WHERE c = d
""".trimIndent()
val query = UnSQLizeSelectUseCase().fromsql("query1",sqlQuery)
assertEquals("alfa", query.select.attributes[0].name)
assertEquals("beta", query.select.attributes[1].name)
assertEquals("tab1", query.select.from[0].tableName)
assertEquals("a = b", query.select.where.condition)
val select = query.union.selects()[0]
assertEquals("gamma", select.attributes[0].name)
assertEquals("theta", select.attributes[1].name)
assertEquals("tab2", select.from[0].tableName)
assertEquals("c = d", select.where.condition)
@Test
fun test_from_union_sql_to_query() {
// given
val sqlQuery="""
SELECT alfa,beta
FROM tab1
WHERE a = b
UNION
SELECT gamma,theta
FROM tab2
WHERE c = d
UNION
SELECT delta,zeta
FROM tab3
WHERE e = f
""".trimIndent()
val query = UnSQLizeSelectUseCase().fromsql("query1",sqlQuery)
assertEquals("alfa", query.select.attributes[0].name)
assertEquals("beta", query.select.attributes[1].name)
assertEquals("tab1", query.select.from[0].tableName)
assertEquals("a = b", query.select.where.condition)
val select = query.union.selects()[0]
assertEquals("gamma", select.attributes[0].name)
assertEquals("theta", select.attributes[1].name)
assertEquals("tab2", select.from[0].tableName)
assertEquals("c = d", select.where.condition)
val select1 = query.union.selects()[1]
assertEquals("delta", select1.attributes[0].name)
assertEquals("zeta", select1.attributes[1].name)
assertEquals("tab3", select1.from[0].tableName)
assertEquals("e = f", select1.where.condition)
val sqlize = SQLizeSelectUseCase().sqlize(query)
SELECT "alfa","beta"
SELECT "gamma","theta"
SELECT "delta","zeta"
FROM tab3
WHERE e = f
""".trimIndent(),sqlize)
}