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));
}
