in src/SqlProviderBase.ts [152:568]
protected _upgradeDb(trans: SqlTransaction, oldVersion: number, wipeAnyway: boolean): Promise<void> {
// Get a list of all tables, columns and indexes on the tables
return this._getMetadata(trans).then(fullMeta => {
// Get Index metadatas
let indexMetadata: IndexMetadata[] =
map(fullMeta, meta => {
const metaObj = attempt(() => {
return JSON.parse(meta.value);
});
if (isError(metaObj)) {
return undefined;
}
return metaObj;
})
.filter(meta => !!meta && !!meta.storeName);
return trans.runQuery('SELECT type, name, tbl_name, sql from sqlite_master', [])
.then(rows => {
let tableNames: string[] = [];
let indexNames: { [table: string]: string[] } = {};
let indexTables: { [table: string]: string[] } = {};
let tableSqlStatements: { [table: string]: string } = {};
for (const row of rows) {
const tableName = row['tbl_name'];
// Ignore browser metadata tables for websql support
if (tableName === '__WebKitDatabaseInfoTable__' || tableName === 'metadata') {
continue;
}
// Ignore FTS-generated side tables
const endsIn = (str: string, checkstr: string) => {
const i = str.indexOf(checkstr);
return i !== -1 && i === str.length - checkstr.length;
};
if (endsIn(tableName, '_content') || endsIn(tableName, '_segments') || endsIn(tableName, '_segdir')) {
continue;
}
if (row['type'] === 'table') {
tableNames.push(row['name']);
tableSqlStatements[row['name']] = row['sql'];
const nameSplit = row['name'].split('_');
if (nameSplit.length === 1) {
if (!indexNames[row['name']]) {
indexNames[row['name']] = [];
}
if (!indexTables[row['name']]) {
indexTables[row['name']] = [];
}
} else {
const tableName = nameSplit[0];
if (indexTables[tableName]) {
indexTables[tableName].push(nameSplit[1]);
} else {
indexTables[tableName] = [nameSplit[1]];
}
}
}
if (row['type'] === 'index') {
if (row['name'].substring(0, 17) === 'sqlite_autoindex_') {
// auto-index, ignore
continue;
}
if (!indexNames[tableName]) {
indexNames[tableName] = [];
}
indexNames[tableName].push(row['name']);
}
}
const deleteFromMeta = (metasToDelete: IndexMetadata[]) => {
if (metasToDelete.length === 0) {
return Promise.resolve([]);
}
// Generate as many '?' as there are params
const placeholder = generateParamPlaceholder(metasToDelete.length);
return trans.runQuery('DELETE FROM metadata WHERE name IN (' + placeholder + ')',
map(metasToDelete, meta => meta.key));
};
// Check each table!
let dropQueries: Promise<any>[] = [];
if (wipeAnyway || (this._schema!!!.lastUsableVersion && oldVersion < this._schema!!!.lastUsableVersion!!!)) {
// Clear all stores if it's past the usable version
if (!wipeAnyway) {
console.log('Old version detected (' + oldVersion + '), clearing all tables');
}
dropQueries = map(tableNames, name => trans.runQuery('DROP TABLE ' + name));
if (indexMetadata.length > 0) {
// Drop all existing metadata
dropQueries.push(deleteFromMeta(indexMetadata));
indexMetadata = [];
}
tableNames = [];
} else {
// Just delete tables we don't care about anymore. Preserve multi-entry tables, they may not be changed
let tableNamesNeeded: string[] = [];
for (const store of this._schema!!!.stores) {
tableNamesNeeded.push(store.name);
if (store.indexes) {
for (const index of store.indexes) {
if (indexUsesSeparateTable(index, this._supportsFTS3)) {
tableNamesNeeded.push(getIndexIdentifier(store, index));
}
}
}
}
let tableNamesNotNeeded = filter(tableNames, name => !includes(tableNamesNeeded, name));
dropQueries = flatten(map(tableNamesNotNeeded, name => {
const transList: Promise<any>[] = [trans.runQuery('DROP TABLE ' + name)];
const metasToDelete = filter(indexMetadata, meta => meta.storeName === name);
const metaKeysToDelete = map(metasToDelete, meta => meta.key);
// Clean up metas
if (metasToDelete.length > 0) {
transList.push(deleteFromMeta(metasToDelete));
indexMetadata = filter(indexMetadata, meta => !includes(metaKeysToDelete, meta.key));
}
return transList;
}));
tableNames = filter(tableNames, name => includes(tableNamesNeeded, name));
}
const tableColumns: { [table: string]: string[] } = {};
const getColumnNames = (tableName: string): string[] => {
// Try to get all the column names from SQL create statement
const r = /CREATE\s+TABLE\s+\w+\s+\(([^\)]+)\)/;
const columnPart = tableSqlStatements[tableName].match(r);
if (columnPart) {
return columnPart[1].split(',').map(p => p.trim().split(/\s+/)[0]);
}
return [];
};
for (const table of tableNames) {
tableColumns[table] = getColumnNames(table);
}
return Promise.all(dropQueries).then(() => {
let tableQueries: Promise<any>[] = [];
// Go over each store and see what needs changing
for (const storeSchema of this._schema!!!.stores) {
// creates indexes for provided schemas
const indexMaker = (indexes: IndexSchema[] = []) => {
let metaQueries: Promise<any>[] = [];
const indexQueries = map(indexes, index => {
const indexIdentifier = getIndexIdentifier(storeSchema, index);
// Store meta for the index
const newMeta: IndexMetadata = {
key: indexIdentifier,
storeName: storeSchema.name,
index: index
};
metaQueries.push(this._storeIndexMetadata(trans, newMeta));
// Go over each index and see if we need to create an index or a table for a multiEntry index
if (index.multiEntry) {
if (isCompoundKeyPath(index.keyPath)) {
return Promise.reject('Can\'t use multiEntry and compound keys');
} else {
return trans.runQuery('CREATE TABLE IF NOT EXISTS ' + indexIdentifier +
' (nsp_key TEXT, nsp_refpk TEXT' +
(index.includeDataInIndex ? ', nsp_data TEXT' : '') + ')').then(() => {
return trans.runQuery('CREATE ' + (index.unique ? 'UNIQUE ' : '') +
'INDEX IF NOT EXISTS ' +
indexIdentifier + '_pi ON ' + indexIdentifier + ' (nsp_key, nsp_refpk' +
(index.includeDataInIndex ? ', nsp_data' : '') + ')');
});
}
} else if (index.fullText && this._supportsFTS3) {
// If FTS3 isn't supported, we'll make a normal column and use LIKE to seek over it, so the
// fallback below works fine.
return trans.runQuery('CREATE VIRTUAL TABLE IF NOT EXISTS ' + indexIdentifier +
' USING FTS3(nsp_key TEXT, nsp_refpk TEXT)');
} else {
return trans.runQuery('CREATE ' + (index.unique ? 'UNIQUE ' : '') +
'INDEX IF NOT EXISTS ' + indexIdentifier +
' ON ' + storeSchema.name + ' (nsp_i_' + index.name +
(index.includeDataInIndex ? ', nsp_data' : '') + ')');
}
});
return Promise.all(indexQueries.concat(metaQueries));
};
// Form SQL statement for table creation
let fieldList = [];
fieldList.push('nsp_pk TEXT PRIMARY KEY');
fieldList.push('nsp_data TEXT');
const columnBasedIndices = filter(storeSchema.indexes, index =>
!indexUsesSeparateTable(index, this._supportsFTS3));
const indexColumnsNames = map(columnBasedIndices, index => 'nsp_i_' + index.name + ' TEXT');
fieldList = fieldList.concat(indexColumnsNames);
const tableMakerSql = 'CREATE TABLE ' + storeSchema.name + ' (' + fieldList.join(', ') + ')';
const currentIndexMetas = filter(indexMetadata, meta => meta.storeName === storeSchema.name);
const indexIdentifierDictionary = keyBy(storeSchema.indexes, index => getIndexIdentifier(storeSchema, index));
const indexMetaDictionary = keyBy(currentIndexMetas, meta => meta.key);
// find which indices in the schema existed / did not exist before
const [newIndices, existingIndices] = partition(storeSchema.indexes, index =>
!indexMetaDictionary[getIndexIdentifier(storeSchema, index)]);
const existingIndexColumns = intersection(existingIndices, columnBasedIndices);
// find indices in the meta that do not exist in the new schema
const allRemovedIndexMetas = filter(currentIndexMetas, meta =>
!indexIdentifierDictionary[meta.key]);
const [removedTableIndexMetas, removedColumnIndexMetas] = partition(allRemovedIndexMetas,
meta => indexUsesSeparateTable(meta.index, this._supportsFTS3));
// find new indices which don't require backfill
const newNoBackfillIndices = filter(newIndices, index => {
return !!index.doNotBackfill;
});
// columns requiring no backfill could be simply added to the table
const newIndexColumnsNoBackfill = intersection(newNoBackfillIndices, columnBasedIndices);
const columnAdder = () => {
const addQueries = map(newIndexColumnsNoBackfill, index =>
trans.runQuery('ALTER TABLE ' + storeSchema.name + ' ADD COLUMN ' + 'nsp_i_' + index.name + ' TEXT')
);
return Promise.all(addQueries);
};
const tableMaker = () => {
// Create the table
return trans.runQuery(tableMakerSql)
.then(() => indexMaker(storeSchema.indexes));
};
const columnExists = (tableName: string, columnName: string) => {
return includes(tableColumns[tableName], columnName);
};
const needsFullMigration = () => {
// Check all the indices in the schema
return some(storeSchema.indexes, index => {
const indexIdentifier = getIndexIdentifier(storeSchema, index);
const indexMeta = indexMetaDictionary[indexIdentifier];
// if there's a new index that doesn't require backfill, continue
// If there's a new index that requires backfill - we need to migrate
if (!indexMeta) {
return !index.doNotBackfill;
}
// If the index schemas don't match - we need to migrate
if (!isEqual(indexMeta.index, index)) {
return true;
}
// Check that indicies actually exist in the right place
if (indexUsesSeparateTable(index, this._supportsFTS3)) {
if (!includes(tableNames, indexIdentifier)) {
return true;
}
} else {
if (!columnExists(storeSchema.name, 'nsp_i_' + index.name)) {
return true;
}
}
return false;
});
};
const dropColumnIndices = () => {
return map(indexNames[storeSchema.name], indexName =>
trans.runQuery('DROP INDEX ' + indexName));
};
const dropIndexTables = (tableNames: string[]) => {
return map(tableNames, tableName =>
trans.runQuery('DROP TABLE IF EXISTS ' + storeSchema.name + '_' + tableName)
);
};
const createTempTable = () => {
// Then rename the table to a temp_[name] table so we can migrate the data out of it
return trans.runQuery('ALTER TABLE ' + storeSchema.name + ' RENAME TO temp_' + storeSchema.name);
};
const dropTempTable = () => {
return trans.runQuery('DROP TABLE temp_' + storeSchema.name);
};
// find is there are some columns that should be, but are not indices
// this is to fix a mismatch between the schema in metadata and the actual table state
const someIndicesMissing = some(columnBasedIndices, index =>
columnExists(storeSchema.name, 'nsp_i_' + index.name)
&& !includes(indexNames[storeSchema.name], getIndexIdentifier(storeSchema, index))
);
// If the table exists, check if we can to determine if a migration is needed
// If a full migration is needed, we have to copy all the data over and re-populate indices
// If a in-place migration is enough, we can just copy the data
// If no migration is needed, we can just add new column for new indices
const tableExists = includes(tableNames, storeSchema.name);
const doFullMigration = tableExists && needsFullMigration();
const doSqlInPlaceMigration = tableExists && !doFullMigration && removedColumnIndexMetas.length > 0;
const adddNewColumns = tableExists && !doFullMigration && !doSqlInPlaceMigration
&& newNoBackfillIndices.length > 0;
const recreateIndices = tableExists && !doFullMigration && !doSqlInPlaceMigration && someIndicesMissing;
const indexFixer = () => {
if (recreateIndices) {
return indexMaker(storeSchema.indexes);
}
return Promise.resolve([]);
};
const indexTableAndMetaDropper = () => {
const indexTablesToDrop = doFullMigration
? indexTables[storeSchema.name] : removedTableIndexMetas.map(meta => meta.key);
return Promise.all([deleteFromMeta(allRemovedIndexMetas), ...dropIndexTables(indexTablesToDrop)]);
};
if (!tableExists) {
// Table doesn't exist -- just go ahead and create it without the migration path
tableQueries.push(tableMaker());
}
if (doFullMigration) {
// Migrate the data over using our existing put functions
// (since it will do the right things with the indexes)
// and delete the temp table.
const jsMigrator = (batchOffset = 0): Promise<any> => {
let esimatedSize = storeSchema.estimatedObjBytes || DB_SIZE_ESIMATE_DEFAULT;
let batchSize = Math.max(1, Math.floor(DB_MIGRATION_MAX_BYTE_TARGET / esimatedSize));
let store = trans.getStore(storeSchema.name);
return trans.internal_getResultsFromQuery('SELECT nsp_data FROM temp_' + storeSchema.name + ' LIMIT ' +
batchSize + ' OFFSET ' + batchOffset)
.then(objs => {
return store.put(objs).then(() => {
// Are we done migrating?
if (objs.length < batchSize) {
return undefined;
}
return jsMigrator(batchOffset + batchSize);
});
});
};
tableQueries.push(
Promise.all([
indexTableAndMetaDropper(),
dropColumnIndices(),
])
.then(createTempTable)
.then(tableMaker)
.then(() => {
return jsMigrator();
})
.then(dropTempTable)
);
}
if (doSqlInPlaceMigration) {
const sqlInPlaceMigrator = () => {
const columnsToCopy = ['nsp_pk', 'nsp_data',
...map(existingIndexColumns, index => 'nsp_i_' + index.name)
].join(', ');
return trans.runQuery('INSERT INTO ' + storeSchema.name + ' (' + columnsToCopy + ')' +
' SELECT ' + columnsToCopy +
' FROM temp_' + storeSchema.name);
};
tableQueries.push(
Promise.all([
indexTableAndMetaDropper(),
dropColumnIndices(),
])
.then(createTempTable)
.then(tableMaker)
.then(sqlInPlaceMigrator)
.then(dropTempTable)
);
}
if (adddNewColumns) {
const newIndexMaker = () => indexMaker(newNoBackfillIndices);
tableQueries.push(
indexTableAndMetaDropper(),
columnAdder()
.then(newIndexMaker)
.then(indexFixer)
);
} else if (recreateIndices) {
tableQueries.push(indexFixer());
}
}
return Promise.all(tableQueries);
});
});
}).then(noop);
}