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