1 package liquibase.snapshot.jvm;
2
3 import java.sql.DatabaseMetaData;
4 import java.sql.ResultSet;
5 import java.sql.SQLException;
6 import java.sql.Statement;
7 import java.text.ParseException;
8 import java.util.ArrayList;
9 import java.util.HashMap;
10 import java.util.HashSet;
11 import java.util.List;
12 import java.util.Locale;
13 import java.util.Map;
14 import java.util.Set;
15
16 import liquibase.database.Database;
17 import liquibase.database.core.SQLiteDatabase;
18 import liquibase.database.jvm.JdbcConnection;
19 import liquibase.database.structure.Column;
20 import liquibase.database.structure.Index;
21 import liquibase.database.structure.PrimaryKey;
22 import liquibase.database.structure.Sequence;
23 import liquibase.database.structure.Table;
24 import liquibase.database.structure.View;
25 import liquibase.database.typeconversion.TypeConverterFactory;
26 import liquibase.exception.DatabaseException;
27 import liquibase.executor.ExecutorService;
28 import liquibase.logging.LogFactory;
29 import liquibase.snapshot.DatabaseSnapshot;
30 import liquibase.statement.core.GetViewDefinitionStatement;
31 import liquibase.statement.core.SelectSequencesStatement;
32 import liquibase.util.StringUtils;
33
34 public class SQLiteDatabaseSnapshotGenerator extends JdbcDatabaseSnapshotGenerator {
35
36
37
38
39 public SQLiteDatabaseSnapshotGenerator() {
40 }
41
42 @Override
43 public boolean supports(Database database) {
44 return database instanceof SQLiteDatabase;
45 }
46
47 @Override
48 public int getPriority(Database database) {
49 return PRIORITY_DATABASE;
50 }
51
52 @Override
53 protected void readTables(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
54 throws SQLException, DatabaseException {
55
56 Database database = snapshot.getDatabase();
57
58 updateListeners("Reading tables for " + database.toString() + " ...");
59 ResultSet rs = databaseMetaData.getTables(database.convertRequestedSchemaToCatalog(schema),
60 database.convertRequestedSchemaToSchema(schema), null, new String[] { "TABLE", "VIEW" });
61
62 try {
63 while (rs.next()) {
64 String type = rs.getString("TABLE_TYPE");
65 String name = rs.getString("TABLE_NAME");
66 String schemaName = rs.getString("TABLE_SCHEM");
67 String catalogName = rs.getString("TABLE_CAT");
68 String remarks = rs.getString("REMARKS");
69
70 if (database.isSystemTable(catalogName, schemaName, name) || database.isLiquibaseTable(name)
71 || database.isSystemView(catalogName, schemaName, name)) {
72 continue;
73 }
74
75 if ("TABLE".equals(type)) {
76 Table table = new Table(name);
77 table.setRemarks(StringUtils.trimToNull(remarks));
78 table.setDatabase(database);
79 table.setSchema(schemaName);
80 snapshot.getTables().add(table);
81 } else if ("VIEW".equals(type)) {
82 View view = new View();
83 view.setName(name);
84 view.setSchema(schemaName);
85 try {
86 view.setDefinition(database.getViewDefinition(schema, name));
87 } catch (DatabaseException e) {
88 System.out.println("Error getting view with " + new GetViewDefinitionStatement(schema, name));
89 throw e;
90 }
91 snapshot.getViews().add(view);
92 }
93 }
94 } finally {
95 rs.close();
96 }
97 }
98
99 @Override
100 protected void readViews(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
101 throws SQLException, DatabaseException {
102
103 Database database = snapshot.getDatabase();
104
105 updateListeners("Reading tables for " + database.toString() + " ...");
106 ResultSet rs = databaseMetaData.getTables(database.convertRequestedSchemaToCatalog(schema),
107 database.convertRequestedSchemaToSchema(schema), null, new String[] { "TABLE", "VIEW" });
108
109 try {
110 while (rs.next()) {
111 String type = rs.getString("TABLE_TYPE");
112 String name = rs.getString("TABLE_NAME");
113 String schemaName = rs.getString("TABLE_SCHEM");
114 String catalogName = rs.getString("TABLE_CAT");
115 String remarks = rs.getString("REMARKS");
116
117 if (database.isSystemTable(catalogName, schemaName, name) || database.isLiquibaseTable(name)
118 || database.isSystemView(catalogName, schemaName, name)) {
119 continue;
120 }
121
122 if ("TABLE".equals(type)) {
123 Table table = new Table(name);
124 table.setRemarks(StringUtils.trimToNull(remarks));
125 table.setDatabase(database);
126 table.setSchema(schemaName);
127 snapshot.getTables().add(table);
128 } else if ("VIEW".equals(type)) {
129 View view = new View();
130 view.setName(name);
131 view.setSchema(schemaName);
132 try {
133 view.setDefinition(database.getViewDefinition(schema, name));
134 } catch (DatabaseException e) {
135 System.out.println("Error getting view with " + new GetViewDefinitionStatement(schema, name));
136 throw e;
137 }
138 snapshot.getViews().add(view);
139 }
140 }
141 } finally {
142 rs.close();
143 }
144 }
145
146
147
148
149 @Override
150 protected void readForeignKeyInformation(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
151 throws DatabaseException, SQLException {
152 updateListeners("Reading foreign keys for " + snapshot.getDatabase().toString() + " ...");
153
154
155 }
156
157
158
159
160 @Override
161 protected void readPrimaryKeys(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
162 throws DatabaseException, SQLException {
163 Database database = snapshot.getDatabase();
164 updateListeners("Reading primary keys for " + database.toString() + " ...");
165
166
167
168 List<PrimaryKey> foundPKs = new ArrayList<PrimaryKey>();
169
170 for (Table table : snapshot.getTables()) {
171 ResultSet rs = databaseMetaData.getPrimaryKeys(database.convertRequestedSchemaToCatalog(schema),
172 database.convertRequestedSchemaToSchema(schema), table.getName());
173
174 try {
175 while (rs.next()) {
176 String tableName = rs.getString("TABLE_NAME");
177 String columnName = rs.getString("COLUMN_NAME");
178 short position = rs.getShort("KEY_SEQ");
179
180 if (!(database instanceof SQLiteDatabase)) {
181 position -= 1;
182 }
183
184 boolean foundExistingPK = false;
185 for (PrimaryKey pk : foundPKs) {
186 if (pk.getTable().getName().equals(tableName)) {
187 pk.addColumnName(position, columnName);
188
189 foundExistingPK = true;
190 }
191 }
192
193 if (!foundExistingPK) {
194 PrimaryKey primaryKey = new PrimaryKey();
195 primaryKey.setTable(table);
196 primaryKey.addColumnName(position, columnName);
197 primaryKey.setName(rs.getString("PK_NAME"));
198
199 foundPKs.add(primaryKey);
200 }
201 }
202 } finally {
203 rs.close();
204 }
205
206 }
207
208 snapshot.getPrimaryKeys().addAll(foundPKs);
209 }
210
211 @Override
212 protected void readColumns(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
213 throws SQLException, DatabaseException {
214 Database database = snapshot.getDatabase();
215 updateListeners("Reading columns for " + database.toString() + " ...");
216
217 if (database instanceof SQLiteDatabase) {
218
219 for (Table cur_table : snapshot.getTables()) {
220 Statement selectStatement = null;
221 ResultSet rs = null;
222 try {
223 selectStatement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection()
224 .createStatement();
225 rs = databaseMetaData.getColumns(database.convertRequestedSchemaToCatalog(schema),
226 database.convertRequestedSchemaToSchema(schema), cur_table.getName(), null);
227 if (rs == null) {
228 rs = databaseMetaData.getColumns(database.convertRequestedSchemaToCatalog(schema),
229 database.convertRequestedSchemaToSchema(schema), cur_table.getName(), null);
230 }
231 while ((rs != null) && rs.next()) {
232 readColumnInfo(snapshot, schema, rs);
233 }
234 } finally {
235 if (rs != null) {
236 try {
237 rs.close();
238 } catch (SQLException ignored) {
239 }
240 }
241 if (selectStatement != null) {
242 selectStatement.close();
243 }
244 }
245 }
246 } else {
247
248 Statement selectStatement = null;
249 ResultSet rs = null;
250 try {
251 selectStatement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection()
252 .createStatement();
253 rs = databaseMetaData.getColumns(database.convertRequestedSchemaToCatalog(schema),
254 database.convertRequestedSchemaToSchema(schema), null, null);
255 while (rs.next()) {
256 readColumnInfo(snapshot, schema, rs);
257 }
258 } finally {
259 if (rs != null) {
260 try {
261 rs.close();
262 } catch (SQLException ignored) {
263 }
264 }
265 if (selectStatement != null) {
266 selectStatement.close();
267 }
268 }
269 }
270 }
271
272 private Column readColumnInfo(DatabaseSnapshot snapshot, String schema, ResultSet rs) throws SQLException,
273 DatabaseException {
274 Database database = snapshot.getDatabase();
275 Column columnInfo = new Column();
276
277 String tableName = rs.getString("TABLE_NAME");
278 String columnName = rs.getString("COLUMN_NAME");
279 String schemaName = rs.getString("TABLE_SCHEM");
280 String catalogName = rs.getString("TABLE_CAT");
281
282 String upperCaseTableName = tableName.toUpperCase(Locale.ENGLISH);
283
284 if (database.isSystemTable(catalogName, schemaName, upperCaseTableName)
285 || database.isLiquibaseTable(upperCaseTableName)) {
286 return null;
287 }
288
289 Table table = snapshot.getTable(tableName);
290 if (table == null) {
291 View view = snapshot.getView(tableName);
292 if (view == null) {
293 LogFactory.getLogger().debug("Could not find table or view " + tableName + " for column " + columnName);
294 return null;
295 } else {
296 columnInfo.setView(view);
297 view.getColumns().add(columnInfo);
298 }
299 } else {
300 columnInfo.setTable(table);
301 table.getColumns().add(columnInfo);
302 }
303
304 columnInfo.setName(columnName);
305 columnInfo.setDataType(rs.getInt("DATA_TYPE"));
306 columnInfo.setColumnSize(rs.getInt("COLUMN_SIZE"));
307 columnInfo.setDecimalDigits(rs.getInt("DECIMAL_POINTS"));
308 Object defaultValue = rs.getObject("COLUMN_DEF");
309 try {
310 columnInfo.setDefaultValue(TypeConverterFactory
311 .getInstance()
312 .findTypeConverter(database)
313 .convertDatabaseValueToObject(defaultValue, columnInfo.getDataType(), columnInfo.getColumnSize(),
314 columnInfo.getDecimalDigits(), database));
315 } catch (ParseException e) {
316 throw new DatabaseException(e);
317 }
318
319 int nullable = rs.getInt("NULLABLE");
320 if (nullable == DatabaseMetaData.columnNoNulls) {
321 columnInfo.setNullable(false);
322 } else if (nullable == DatabaseMetaData.columnNullable) {
323 columnInfo.setNullable(true);
324 }
325
326 columnInfo.setPrimaryKey(snapshot.isPrimaryKey(columnInfo));
327 columnInfo.setAutoIncrement(isColumnAutoIncrement(database, schema, tableName, columnName));
328 columnInfo.setTypeName(TypeConverterFactory.getInstance().findTypeConverter(database)
329 .getDataType(rs.getString("TYPE_NAME"), columnInfo.isAutoIncrement()).toString());
330
331 return columnInfo;
332 }
333
334 @Override
335 protected void readIndexes(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
336 throws DatabaseException, SQLException {
337 Database database = snapshot.getDatabase();
338 updateListeners("Reading indexes for " + database.toString() + " ...");
339
340 for (Table table : snapshot.getTables()) {
341 ResultSet rs = null;
342 Statement statement = null;
343 Map<String, Index> indexMap;
344 try {
345 indexMap = new HashMap<String, Index>();
346
347
348
349 statement = ((JdbcConnection) database.getConnection()).getUnderlyingConnection().createStatement();
350 String sql = "PRAGMA index_list(" + table.getName() + ");";
351 try {
352 rs = statement.executeQuery(sql);
353 } catch (SQLException e) {
354 if (!e.getMessage().equals("query does not return ResultSet")) {
355 System.err.println(e);
356
357 }
358 }
359 while ((rs != null) && rs.next()) {
360 String index_name = rs.getString("name");
361 boolean index_unique = rs.getBoolean("unique");
362 sql = "PRAGMA index_info(" + index_name + ");";
363 Statement statement_2 = null;
364 ResultSet rs_2 = null;
365 try {
366 statement_2 = ((JdbcConnection) database.getConnection()).getUnderlyingConnection()
367 .createStatement();
368 rs_2 = statement_2.executeQuery(sql);
369 while ((rs_2 != null) && rs_2.next()) {
370 int index_column_seqno = rs_2.getInt("seqno");
371
372 String index_column_name = rs_2.getString("name");
373 if (index_unique) {
374 Column column = snapshot.getColumn(table.getName(), index_column_name);
375 column.setUnique(true);
376 } else {
377 Index indexInformation;
378 if (indexMap.containsKey(index_name)) {
379 indexInformation = indexMap.get(index_name);
380 } else {
381 indexInformation = new Index();
382 indexInformation.setTable(table);
383 indexInformation.setName(index_name);
384 indexInformation.setFilterCondition("");
385 indexMap.put(index_name, indexInformation);
386 }
387 indexInformation.getColumns().add(index_column_seqno, index_column_name);
388 }
389 }
390 } finally {
391 if (rs_2 != null) {
392 try {
393 rs_2.close();
394 } catch (SQLException ignored) {
395 }
396 }
397 if (statement_2 != null) {
398 try {
399 statement_2.close();
400 } catch (SQLException ignored) {
401 }
402 }
403 }
404
405 }
406 } finally {
407 if (rs != null) {
408 try {
409 rs.close();
410 } catch (SQLException ignored) {
411 }
412 }
413 if (statement != null) {
414 try {
415 statement.close();
416 } catch (SQLException ignored) {
417 }
418 }
419 }
420
421 for (Map.Entry<String, Index> entry : indexMap.entrySet()) {
422 snapshot.getIndexes().add(entry.getValue());
423 }
424 }
425
426
427 Set<Index> indexesToRemove = new HashSet<Index>();
428 for (Index index : snapshot.getIndexes()) {
429 for (PrimaryKey pk : snapshot.getPrimaryKeys()) {
430 if (index.getTable().getName().equalsIgnoreCase(pk.getTable().getName())
431 && index.getColumnNames().equals(pk.getColumnNames())) {
432 indexesToRemove.add(index);
433 }
434 }
435 }
436 snapshot.getIndexes().removeAll(indexesToRemove);
437 }
438
439 @Override
440 protected void readSequences(DatabaseSnapshot snapshot, String schema, DatabaseMetaData databaseMetaData)
441 throws DatabaseException {
442 Database database = snapshot.getDatabase();
443 updateListeners("Reading sequences for " + database.toString() + " ...");
444
445 String convertedSchemaName = database.convertRequestedSchemaToSchema(schema);
446
447 if (database.supportsSequences()) {
448
449 List<String> sequenceNamess = (List<String>) ExecutorService.getInstance().getExecutor(database)
450 .queryForList(new SelectSequencesStatement(schema), String.class);
451
452 for (String sequenceName : sequenceNamess) {
453 Sequence seq = new Sequence();
454 seq.setName(sequenceName.trim());
455 seq.setName(convertedSchemaName);
456
457 snapshot.getSequences().add(seq);
458 }
459 }
460 }
461
462 }