1 /* 2 * Copyright (C) 2016 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package androidx.room.parser 18 19 import org.hamcrest.CoreMatchers.`is` 20 import org.hamcrest.CoreMatchers.not 21 import org.hamcrest.MatcherAssert.assertThat 22 import org.junit.Test 23 import org.junit.runner.RunWith 24 import org.junit.runners.JUnit4 25 26 @RunWith(JUnit4::class) 27 class SqlParserTest { 28 29 @Test 30 fun multipleQueries() { 31 assertErrors("SELECT * FROM users; SELECT * FROM books;", 32 ParserErrors.NOT_ONE_QUERY) 33 } 34 35 @Test 36 fun empty() { 37 assertErrors("", ParserErrors.NOT_ONE_QUERY) 38 } 39 40 @Test 41 fun deleteQuery() { 42 val parsed = SqlParser.parse("DELETE FROM users where id > 3") 43 assertThat(parsed.errors, `is`(emptyList())) 44 assertThat(parsed.type, `is`(QueryType.DELETE)) 45 } 46 47 @Test 48 fun badDeleteQuery() { 49 assertErrors("delete from user where mAge >= :min && mAge <= :max", 50 "no viable alternative at input 'delete from user where mAge >= :min &&'") 51 } 52 53 @Test 54 fun updateQuery() { 55 val parsed = SqlParser.parse("UPDATE users set name = :name where id = :id") 56 assertThat(parsed.errors, `is`(emptyList())) 57 assertThat(parsed.type, `is`(QueryType.UPDATE)) 58 } 59 60 @Test 61 fun explain() { 62 assertErrors("EXPLAIN QUERY PLAN SELECT * FROM users", 63 ParserErrors.invalidQueryType(QueryType.EXPLAIN)) 64 } 65 66 @Test 67 fun validColumnNames() { 68 listOf("f", "fo", "f2", "f 2", "foo_2", "foo-2", "_", "foo bar baz", 69 "foo 2 baz", "_baz", "fooBar", "2", "*", "foo*2", "dsa$", "\$fsa", 70 "-bar", "o").forEach { 71 assertThat("name: $it", SqlParser.isValidIdentifier(it), `is`(true)) 72 } 73 } 74 75 @Test 76 fun invalidColumnNames() { 77 listOf("", " ", "fd`a`", "f`a", "`a", "\"foo bar\"", "\"", "`").forEach { 78 assertThat("name: $it", SqlParser.isValidIdentifier(it), `is`(false)) 79 } 80 } 81 82 @Test 83 fun extractTableNames() { 84 assertThat(SqlParser.parse("select * from users").tables, 85 `is`(setOf(Table("users", "users")))) 86 assertThat(SqlParser.parse("select * from users as ux").tables, 87 `is`(setOf(Table("users", "ux")))) 88 assertThat(SqlParser.parse("select * from (select * from books)").tables, 89 `is`(setOf(Table("books", "books")))) 90 assertThat(SqlParser.parse("select x.id from (select * from books) as x").tables, 91 `is`(setOf(Table("books", "books")))) 92 } 93 94 @Test 95 fun unescapeTableNames() { 96 assertThat(SqlParser.parse("select * from `users`").tables, 97 `is`(setOf(Table("users", "users")))) 98 assertThat(SqlParser.parse("select * from \"users\"").tables, 99 `is`(setOf(Table("users", "users")))) 100 assertThat(SqlParser.parse("select * from 'users'").tables, 101 `is`(setOf(Table("users", "users")))) 102 } 103 104 @Test 105 fun tablePrefixInInsert_set() { 106 // this is an invalid query, b/64539805 107 val query = SqlParser.parse("UPDATE trips SET trips.title=:title") 108 assertThat(query.errors, not(emptyList())) 109 } 110 111 @Test 112 fun tablePrefixInInsert_where() { 113 val query = SqlParser.parse("UPDATE trips SET title=:title WHERE trips.id=:id") 114 assertThat(query.errors, `is`(emptyList())) 115 } 116 117 @Test 118 fun tablePrefixInSelect_projection() { 119 val query = SqlParser.parse("SELECT a.name, b.last_name from user a, book b") 120 assertThat(query.errors, `is`(emptyList())) 121 assertThat(query.tables, `is`(setOf(Table("user", "a"), 122 Table("book", "b")))) 123 } 124 125 @Test 126 fun tablePrefixInSelect_where() { 127 val query = SqlParser.parse("SELECT a.name, b.last_name from user a, book b" + 128 " WHERE a.name = b.name") 129 assertThat(query.errors, `is`(emptyList())) 130 assertThat(query.tables, `is`(setOf(Table("user", "a"), 131 Table("book", "b")))) 132 } 133 134 @Test 135 fun findBindVariables() { 136 assertVariables("select * from users") 137 assertVariables("select * from users where name like ?", "?") 138 assertVariables("select * from users where name like :name", ":name") 139 assertVariables("select * from users where name like ?2", "?2") 140 assertVariables("select * from users where name like ?2 OR name LIKE ?1", "?2", "?1") 141 assertVariables("select * from users where name like @a", "@a") 142 assertVariables("select * from users where name like \$a", "\$a") 143 } 144 145 @Test 146 fun indexedVariablesError() { 147 assertErrors("select * from users where name like ?", 148 ParserErrors.ANONYMOUS_BIND_ARGUMENT) 149 assertErrors("select * from users where name like ? or last_name like ?", 150 ParserErrors.ANONYMOUS_BIND_ARGUMENT) 151 assertErrors("select * from users where name like ?1", 152 ParserErrors.cannotUseVariableIndices("?1", 36)) 153 } 154 155 @Test 156 fun foo() { 157 assertSections("select * from users where name like ?", 158 Section.text("select * from users where name like "), 159 Section.bindVar("?")) 160 161 assertSections("select * from users where name like :name AND last_name like :lastName", 162 Section.text("select * from users where name like "), 163 Section.bindVar(":name"), 164 Section.text(" AND last_name like "), 165 Section.bindVar(":lastName")) 166 167 assertSections("select * from users where name \nlike :name AND last_name like :lastName", 168 Section.text("select * from users where name "), 169 Section.newline(), 170 Section.text("like "), 171 Section.bindVar(":name"), 172 Section.text(" AND last_name like "), 173 Section.bindVar(":lastName")) 174 175 assertSections("select * from users where name like :name \nAND last_name like :lastName", 176 Section.text("select * from users where name like "), 177 Section.bindVar(":name"), 178 Section.text(" "), 179 Section.newline(), 180 Section.text("AND last_name like "), 181 Section.bindVar(":lastName")) 182 183 assertSections("select * from users where name like :name \nAND last_name like \n:lastName", 184 Section.text("select * from users where name like "), 185 Section.bindVar(":name"), 186 Section.text(" "), 187 Section.newline(), 188 Section.text("AND last_name like "), 189 Section.newline(), 190 Section.bindVar(":lastName")) 191 } 192 193 fun assertVariables(query: String, vararg expected: String) { 194 assertThat((SqlParser.parse(query)).inputs.map { it.text }, `is`(expected.toList())) 195 } 196 197 fun assertErrors(query: String, vararg errors: String) { 198 assertThat((SqlParser.parse(query)).errors, `is`(errors.toList())) 199 } 200 201 fun assertSections(query: String, vararg sections: Section) { 202 assertThat(SqlParser.parse(query).sections, `is`(sections.toList())) 203 } 204 } 205
