public void Migrate(DatabaseConnection connection) { connection.RunInTransaction(db => { if (!db.TableExists("user_info")) { return; } string createQuery = #if EMBY "create table if not exists user_info_tmp(Id TEXT NOT NULL, Guid GUID NOT NULL, UserId TEXT NOT NULL, LastModified INTEGER NOT NULL, Type TEXT NOT NULL, PRIMARY KEY (Id, UserId))"; #else "create table if not exists user_info_tmp(Guid GUID NOT NULL, UserId TEXT NOT NULL, LastModified INTEGER NOT NULL, Type TEXT NOT NULL, PRIMARY KEY (Guid, UserId))"; #endif db.Execute(createQuery); db.Execute("insert into user_info_tmp select * from user_info"); db.Execute("drop table user_info"); db.Execute("alter table user_info_tmp rename to user_info"); string createIndex = #if EMBY "create index if not exists idx_user_info on user_info(Id, UserId)"; #else "create index if not exists idx_user_info on user_info(Guid, UserId)"; #endif db.Execute(createIndex); }, TransactionMode.Deferred); }
public void Migrate(DatabaseConnection connection) { connection.RunInTransaction(db => { var selectTable = "select name from sqlite_master where type='table' and name not like 'sqlite_%';"; List <string> tables; using (var entitiesStatetment = db.PrepareStatement(selectTable)) { tables = entitiesStatetment.ExecuteQuery().Select(row => row.GetString(0)).ToList(); } foreach (var table in tables) { db.Execute($"drop table '{table}';"); } var selectIndex = "select name from sqlite_master where type='index' and name not like 'sqlite_%';"; List <string> indexes; using (var indexesStatetment = db.PrepareStatement(selectIndex)) { indexes = indexesStatetment.ExecuteQuery().Select(row => row.GetString(0)).ToList(); } foreach (var index in indexes) { db.Execute($"drop index '{index}';"); } }, TransactionMode.Deferred); }
protected void RunDefaultInitialization(DatabaseConnection db) { var queries = new List <string> { "PRAGMA journal_mode=WAL", "PRAGMA page_size=4096", "PRAGMA synchronous=Normal" }; if (EnableTempStoreMemory) { queries.AddRange(new List <string> { "pragma default_temp_store = memory", "pragma temp_store = memory" }); } else { queries.AddRange(new List <string> { "pragma temp_store = file" }); } db.ExecuteAll(string.Join(";", queries.ToArray())); }
protected bool AddColumn(DatabaseConnection connection, string table, string columnName, string type, List <string> existingColumnNames) { if (existingColumnNames.Contains(columnName, StringComparer.OrdinalIgnoreCase)) { return(false); } connection.Execute("alter table " + table + " add column " + columnName + " " + type + " NULL"); return(true); }
public IStatement[] PrepareAll(DatabaseConnection connection, List <string> sql) { var length = sql.Count; var result = new IStatement[length]; for (var i = 0; i < length; i++) { result[i] = connection.PrepareStatement(sql[i]); } return(result); }
protected List <string> GetColumnNames(DatabaseConnection connection, string table) { var list = new List <string>(); using (var statement = PrepareStatement(connection, "PRAGMA table_info(" + table + ")")) { foreach (var row in statement.ExecuteQuery()) { if (!row.IsDBNull(1)) { var name = row.GetString(1); list.Add(name); } } } return(list); }
public void UpdateVersion(DatabaseConnection connection, bool dbJustCreated) { if (dbJustCreated) { connection.Execute($"PRAGMA user_version = {DbVersion};"); return; } int version; using (var versionStatement = connection.PrepareStatement("PRAGMA user_version;")) { version = versionStatement.ExecuteQuery().FirstOrDefault().GetInt(0); } if (version >= DbVersion) { return; } _logger.LogInformation($"InfuseSync: DB version {version} is outdated. Will migrate to version {DbVersion}."); for (var migrateVersion = version; migrateVersion < DbVersion; ++migrateVersion) { var migration = migrations[migrateVersion]; if (migration != null) { _logger.LogInformation($"InfuseSync: Performing migration for DB version {migrateVersion}."); migration.Migrate(connection); } else { _logger.LogInformation($"InfuseSync: Migration not found for DB version {migrateVersion}."); } } connection.Execute($"PRAGMA user_version = {DbVersion};"); _logger.LogInformation($"InfuseSync: DB migration finished."); }
protected DatabaseConnection CreateConnection(bool isReadOnly = false) { if (_connection != null) { #if EMBY return(_connection.Clone(false)); #else return(_connection); #endif } lock (WriteLock) { if (!_versionLogged) { _versionLogged = true; } ConnectionFlags connectionFlags; if (isReadOnly) { connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } else { connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } if (EnableSingleConnection) { connectionFlags |= ConnectionFlags.PrivateCache; } else { connectionFlags |= ConnectionFlags.SharedCached; } connectionFlags |= ConnectionFlags.NoMutex; #if EMBY var db = SQLite3.Open(DbFilePath, connectionFlags, null, false); #else var db = SQLite3.Open(DbFilePath, connectionFlags, null); #endif try { if (string.IsNullOrWhiteSpace(_defaultWal)) { var query = "PRAGMA journal_mode"; #if EMBY using (var statement = PrepareStatement(db, query)) { foreach (var row in statement.ExecuteQuery()) { _defaultWal = row.GetString(0); break; } } #else _defaultWal = db.Query(query).SelectScalarString().First(); #endif } var queries = new List <string> { "PRAGMA synchronous=Normal" }; if (CacheSize.HasValue) { queries.Add("PRAGMA cache_size=" + CacheSize.Value.ToString(CultureInfo.InvariantCulture)); } if (EnableTempStoreMemory) { queries.Add("PRAGMA temp_store = memory"); } else { queries.Add("PRAGMA temp_store = file"); } db.ExecuteAll(string.Join(";", queries.ToArray())); } catch { db.Dispose(); throw; } #if EMBY _connection = db; return(db); #else _dbConnection = db; _connection = new ManagedConnection(db); return(_connection); #endif } }
public IStatement PrepareStatement(DatabaseConnection connection, string sql) { return(connection.PrepareStatement(sql)); }