/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize(IDbConnection connection, SemaphoreSlim writeLock)
{
WriteLock.Dispose();
WriteLock = writeLock;
_connection = connection;
string[] queries =
{
"create table if not exists UserDataDb.userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
"drop index if exists UserDataDb.idx_userdata",
"drop index if exists UserDataDb.idx_userdata1",
"drop index if exists UserDataDb.idx_userdata2",
"drop index if exists UserDataDb.userdataindex1",
"create unique index if not exists UserDataDb.userdataindex on userdata (key, userId)",
"create index if not exists UserDataDb.userdataindex2 on userdata (key, userId, played)",
"create index if not exists UserDataDb.userdataindex3 on userdata (key, userId, playbackPositionTicks)",
"create index if not exists UserDataDb.userdataindex4 on userdata (key, userId, isFavorite)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "userdata", "AudioStreamIndex", "int");
_connection.AddColumn(Logger, "userdata", "SubtitleStreamIndex", "int");
}
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "sync14.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false);
string[] queries =
{
"create table if not exists SyncJobs (Id GUID PRIMARY KEY, TargetId TEXT NOT NULL, Name TEXT NOT NULL, Profile TEXT, Quality TEXT, Bitrate INT, Status TEXT NOT NULL, Progress FLOAT, UserId TEXT NOT NULL, ItemIds TEXT NOT NULL, Category TEXT, ParentId TEXT, UnwatchedOnly BIT, ItemLimit INT, SyncNewContent BIT, DateCreated DateTime, DateLastModified DateTime, ItemCount int)",
"create index if not exists idx_SyncJobs on SyncJobs(Id)",
"create table if not exists SyncJobItems (Id GUID PRIMARY KEY, ItemId TEXT, ItemName TEXT, MediaSourceId TEXT, JobId TEXT, TemporaryPath TEXT, OutputPath TEXT, Status TEXT, TargetId TEXT, DateCreated DateTime, Progress FLOAT, AdditionalFiles TEXT, MediaSource TEXT, IsMarkedForRemoval BIT, JobItemIndex INT)",
"create index if not exists idx_SyncJobItems on SyncJobs(Id)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "SyncJobs", "Profile", "TEXT");
_connection.AddColumn(Logger, "SyncJobs", "Bitrate", "INT");
PrepareStatements();
}
public void Execute(IDbConnection db)
{
if (!db.TableExists(db.GetTableName <SiteSettings>()))
{
db.CreateTable <SiteSettings>();
}
else
{
// Append the analytics specific columns
db.AddColumn <SiteSettings>(x => x.AnalyticsProfileId);
db.AddColumn <SiteSettings>(x => x.AnalyticsToken);
}
}
public void Execute(IDbConnection db)
{
if (!db.TableExists(db.GetTableName<SiteSettings>()))
{
db.CreateTable<SiteSettings>();
}
else
{
// Append the analytics specific columns
db.AddColumn<SiteSettings>(x => x.AnalyticsProfileId);
db.AddColumn<SiteSettings>(x => x.AnalyticsToken);
}
}
public static void CreateColumnIfNotExists <T>(this IDbConnection db, Expression <Func <T, object> > field)
{
if (!db.ColumnExists <T>(field))
{
db.AddColumn <T>(field);
}
}
public static void AddColumn <T>(this IDbConnection dbConn, Expression <Func <T, object> > field)
{
var modelDef = ModelDefinition <T> .Definition;
var fieldDef = modelDef.GetFieldDefinition(field);
dbConn.AddColumn(typeof(T), fieldDef);
}
public static void InitProtoTable <B>(this IDbConnection db)
where B : IBuilder, new()
{
var desc = new B().DescriptorForType;
var model = ModelDefinition <B> .Definition;
model.Name = desc.Name;
model.IgnoredFieldDefinitions.Clear();
var fieldList = new List <FieldDefinition>();
var fieldMap = desc.Fields
.ToDictionary(f => f.Name, StringComparer.OrdinalIgnoreCase);
foreach (var field in model.FieldDefinitions)
{
if (fieldMap.ContainsKey(field.Name))
{
fieldList.Add(field);
}
}
model.FieldDefinitions = fieldList;
model.AfterInit();
if (db.TableExists <B>())
{
var columns = GetColumnNames <B>(db, model.ModelName);
var missing = model.FieldDefinitions
.Where(field => !columns.Contains(field.FieldName));
foreach (var field in missing)
{
field.DefaultValue = fieldMap[field.Name].DefaultValue.ToString();
db.AddColumn(typeof(B), field);
Console.WriteLine(db.GetLastSql());
}
}
}
public static void AddColumnIfNotExists <TModel>(this IDbConnection db, Expression <Func <TModel, object> > field)
{
if (db.ColumnExists(field))
{
return;
}
db.AddColumn(field);
}
public static void AddColumn <TModel>(this IDbConnection db, string columnName, Type columnType, bool isNullable, string?defaultValue = null)
{
db.AddColumn(typeof(TModel), new()
{
Name = columnName,
FieldType = columnType,
IsNullable = isNullable,
DefaultValue = defaultValue,
});
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false);
string[] queries =
{
"create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
"create unique index if not exists userdataindex on userdata (key, userId)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "userdata", "AudioStreamIndex", "int");
_connection.AddColumn(Logger, "userdata", "SubtitleStreamIndex", "int");
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize(IDbConnector dbConnector)
{
var dbFile = Path.Combine(_appPaths.DataPath, "userdata_v2.db");
_connection = await dbConnector.Connect(dbFile).ConfigureAwait(false);
string[] queries = {
"create table if not exists userdata (key nvarchar, userId GUID, rating float null, played bit, playCount int, isFavorite bit, playbackPositionTicks bigint, lastPlayedDate datetime null)",
"create index if not exists idx_userdata on userdata(key)",
"create unique index if not exists userdataindex on userdata (key, userId)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "userdata", "AudioStreamIndex", "int");
_connection.AddColumn(Logger, "userdata", "SubtitleStreamIndex", "int");
}
public static void AddColumnIfNotExists <TModel>(this IDbConnection db, string columnName)
{
if (db.ColumnExists <TModel>(columnName))
{
return;
}
PropertyInfo?property = typeof(TModel).GetProperty(columnName, BindingFlags.Instance | BindingFlags.Public);
if (property is null)
{
return;
}
ParameterExpression parameter = Expression.Parameter(typeof(TModel), "x");
UnaryExpression body = Expression.Convert(Expression.Property(parameter, property), typeof(object));
Expression <Func <TModel, object> > expression = Expression.Lambda <Func <TModel, object> >(body, parameter);
db.AddColumn(expression);
}
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "authentication.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, Logger).ConfigureAwait(false);
string[] queries = {
"create table if not exists AccessTokens (Id GUID PRIMARY KEY, AccessToken TEXT NOT NULL, DeviceId TEXT, AppName TEXT, AppVersion TEXT, DeviceName TEXT, UserId TEXT, IsActive BIT, DateCreated DATETIME NOT NULL, DateRevoked DATETIME)",
"create index if not exists idx_AccessTokens on AccessTokens(Id)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "AccessTokens", "AppVersion", "TEXT");
PrepareStatements();
}
public async Task Initialize(IDbConnector dbConnector)
{
var dbFile = Path.Combine(_appPaths.DataPath, "authentication.db");
_connection = await dbConnector.Connect(dbFile).ConfigureAwait(false);
string[] queries =
{
"create table if not exists AccessTokens (Id GUID PRIMARY KEY, AccessToken TEXT NOT NULL, DeviceId TEXT, AppName TEXT, AppVersion TEXT, DeviceName TEXT, UserId TEXT, IsActive BIT, DateCreated DATETIME NOT NULL, DateRevoked DATETIME)",
"create index if not exists idx_AccessTokens on AccessTokens(Id)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "AccessTokens", "AppVersion", "TEXT");
PrepareStatements();
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
string[] queries = {
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
"create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
"create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
"create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, _logger);
_connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
PrepareStatements();
_mediaStreamsRepository.Initialize();
_chapterRepository.Initialize();
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
var createMediaStreamsTableCommand
= "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, IsCabac BIT NULL, KeyFrames TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))";
string[] queries = {
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
"create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
"create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
"create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
"create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
"create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
"create index if not exists idx_"+ChaptersTableName+" on "+ChaptersTableName+"(ItemId, ChapterIndex)",
createMediaStreamsTableCommand,
"create index if not exists idx_mediastreams on mediastreams(ItemId, StreamIndex)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, _logger);
_connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "MediaType", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "Overview", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentIndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID");
_connection.AddColumn(_logger, "TypedBaseItems", "Genres", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentalRatingValue", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "SchemaVersion", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "SortName", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "HomePageUrl", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "VoteCount", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "DisplayMediaType", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "DateCreated", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "DateModified", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ForcedSortName", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsOffline", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "LocationType", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsSeries", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsLive", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsNews", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsPremiere", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "EpisodeTitle", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsRepeat", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsHD", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "ExternalEtag", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME");
PrepareStatements();
new MediaStreamColumns(_connection, _logger).AddColumns();
var chapterDbFile = Path.Combine(_appPaths.DataPath, "chapters.db");
if (File.Exists(chapterDbFile))
{
MigrateChapters(chapterDbFile);
}
var mediaStreamsDbFile = Path.Combine(_appPaths.DataPath, "mediainfo.db");
if (File.Exists(mediaStreamsDbFile))
{
MigrateMediaStreams(mediaStreamsDbFile);
}
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
string[] queries = {
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
"create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
"create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
"create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
"create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, _logger);
_connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "MediaType", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "Overview", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentIndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "PremiereDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ProductionYear", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentId", "GUID");
_connection.AddColumn(_logger, "TypedBaseItems", "Genres", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "ParentalRatingValue", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "SchemaVersion", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "SortName", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "HomePageUrl", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "VoteCount", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "DisplayMediaType", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "DateCreated", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "DateModified", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ForcedSortName", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsOffline", "BIT");
PrepareStatements();
_mediaStreamsRepository.Initialize();
_chapterRepository.Initialize();
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize(IDbConnector dbConnector)
{
var dbFile = Path.Combine(_config.ApplicationPaths.DataPath, "library.db");
_connection = await dbConnector.Connect(dbFile).ConfigureAwait(false);
var createMediaStreamsTableCommand
= "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))";
string[] queries = {
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)",
"create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
"create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
"create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
"create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
"create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
"create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)",
"create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT, PRIMARY KEY (ItemId, UserDataKey))",
"create index if not exists idx_UserDataKeys1 on UserDataKeys(ItemId)",
"create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
"create index if not exists idxPeopleItemId on People(ItemId)",
"create index if not exists idxPeopleName on People(Name)",
"create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
"create index if not exists idx_"+ChaptersTableName+"1 on "+ChaptersTableName+"(ItemId)",
createMediaStreamsTableCommand,
"create index if not exists idx_mediastreams1 on mediastreams(ItemId)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID");
_connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
_connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float");
_connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateModifiedDuringLastRefresh", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text");
_connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT");
string[] postQueries =
{
"create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
"create index if not exists idx_Type on TypedBaseItems(Type)"
};
_connection.RunQueries(postQueries, Logger);
PrepareStatements();
new MediaStreamColumns(_connection, Logger).AddColumns();
var mediaStreamsDbFile = Path.Combine(_config.ApplicationPaths.DataPath, "mediainfo.db");
if (File.Exists(mediaStreamsDbFile))
{
MigrateMediaStreams(mediaStreamsDbFile);
}
DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb");
}
public void Install()
{
try
{
Installer.logger.Debug((object)"创建数据库表……");
using (IDbConnection dbConn = HelperConnection.GetConnectionFactory().OpenDbConnection())
{
using (IDbTransaction dbTransaction = dbConn.OpenTransaction())
{
#region wcs接口表
dbConn.CreateTableIfNotExists <INF_EQUIPMENTREQUESTEntity>();
dbConn.CreateTableIfNotExists <INF_EQUIPMENTSTATUSEntity>();
dbConn.CreateTableIfNotExists <INF_JOBDOWNLOADEntity>();
dbConn.CreateTableIfNotExists <INF_JOBFEEDBACKEntity>();
dbConn.CreateTableIfNotExists <INF_LEDINFOEntity>();
#endregion
#region 位置管理
dbConn.CreateTableIfNotExists <LocDetailEntity>();
dbConn.CreateTableIfNotExists <LocGroupEntity>();
dbConn.CreateTableIfNotExists <LocLimitEntity>();
dbConn.CreateTableIfNotExists <LocRelateEntity>();
dbConn.CreateTableIfNotExists <LocStockEntity>();
dbConn.CreateTableIfNotExists <LocStorehouseEntity>();
dbConn.CreateTableIfNotExists <LocStationInfoEntity>();
dbConn.CreateTableIfNotExists <LocStationRelateEntity>();
#endregion
#region 托盘管理
dbConn.CreateTableIfNotExists <PalletTypeEntity>();
dbConn.CreateTableIfNotExists <PalletInfoEntity>();
dbConn.CreateTableIfNotExists <PalletEntity>();
dbConn.CreateTableIfNotExists <PalletDetailEntity>();
dbConn.CreateTableIfNotExists <PalletMvInfoEntity>();
#endregion
#region 物料管理
dbConn.CreateTableIfNotExists <ProductEntity>();
dbConn.CreateTableIfNotExists <ProductExtendEntity>();
dbConn.CreateTableIfNotExists <ProductBOMEntity>();
dbConn.CreateTableIfNotExists <ProductBOMITEMEntity>();
dbConn.CreateTableIfNotExists <ProductBOMCheckEntity>();
dbConn.CreateTableIfNotExists <ProductSKUEntity>();
dbConn.CreateTableIfNotExists <ProductSKUTransferEntity>();
dbConn.CreateTableIfNotExists <ProductBatchnoEntity>();
#endregion
#region 订单管理
dbConn.CreateTableIfNotExists <OrderEntity>();
dbConn.CreateTableIfNotExists <OrderDetailEntity>();
#endregion
#region 计划管理
dbConn.CreateTableIfNotExists <PlanEntity>();
dbConn.CreateTableIfNotExists <PlanDetailEntity>();
dbConn.CreateTableIfNotExists <PlanDetailPalletEntity>();
#endregion
#region 基础信息
dbConn.CreateTableIfNotExists <BasicBuniessTypeEntity>();
dbConn.CreateTableIfNotExists <BasicCustomerEntity>();
dbConn.CreateTableIfNotExists <BasicMasterEntity>();
dbConn.CreateTableIfNotExists <BasicProducerEntity>();
dbConn.CreateTableIfNotExists <BasicVendorEntity>();
#endregion
#region 搬运任务管理
dbConn.CreateTableIfNotExists <TaskEntity>();
dbConn.CreateTableIfNotExists <TaskStepEntity>();
dbConn.CreateTableIfNotExists <TaskStatEntity>();
#endregion
#region 系统配置表
dbConn.CreateTableIfNotExists <SysRuleEntity>();
dbConn.CreateTableIfNotExists <SysRuleRouteEntity>();
dbConn.CreateTableIfNotExists <SysRuleRouteDetailEntity>();
dbConn.CreateTableIfNotExists <SysCheckEntity>();
dbConn.CreateTableIfNotExists <SysSceneEntity>();
dbConn.CreateTableIfNotExists <SysAlterInfoEntity>();
#endregion
#region 板管理
dbConn.CreateTableIfNotExists <ViewLayoutEntity>();
#endregion
#region 历史记录表
//dbConn.CreateTableIfNotExists<ZHISPalletEntity>();
//dbConn.CreateTableIfNotExists<ZHISPalletInfoEntity>();
dbConn.CreateTableIfNotExists <ZHISTaskEntity>();
#endregion
#region 设备管理
dbConn.CreateTableIfNotExists <EQTypeEntity>();
dbConn.CreateTableIfNotExists <EQInstanceEntity>();
#endregion
#region 操作日志
dbConn.CreateTableIfNotExists <SysOplogEntity>();
#endregion
#region 添加字段
if (!dbConn.ColumnExists <LocGroupEntity>(x => x.GROUPMODEL))
{
dbConn.AddColumn <LocGroupEntity>(x => x.GROUPMODEL);
}
if (!dbConn.ColumnExists <LocGroupEntity>(x => x.DIRECTIONENTRY))
{
dbConn.AddColumn <LocGroupEntity>(x => x.DIRECTIONENTRY);
}
if (!dbConn.ColumnExists <LocDetailEntity>(x => x.REMARK))
{
dbConn.AddColumn <LocDetailEntity>(x => x.REMARK);
}
if (!dbConn.ColumnExists <LocStationInfoEntity>(x => x.PRIORITY))
{
dbConn.AddColumn <LocStationInfoEntity>(x => x.PRIORITY);
}
if (!dbConn.ColumnExists <LocStationInfoEntity>(x => x.TASKCOUNT))
{
dbConn.AddColumn <LocStationInfoEntity>(x => x.TASKCOUNT);
}
if (!dbConn.ColumnExists <PlanDetailPalletEntity>(x => x.BATCHNO))
{
dbConn.AddColumn <PlanDetailPalletEntity>(x => x.BATCHNO);
}
if (!dbConn.ColumnExists <PlanDetailPalletEntity>(x => x.PALLETCREATEDATE))
{
dbConn.AddColumn <PlanDetailPalletEntity>(x => x.PALLETCREATEDATE);
}
if (!dbConn.ColumnExists <TaskEntity>(x => x.CREATEDATETIME))
{
dbConn.AddColumn <TaskEntity>(x => x.CREATEDATETIME);
}
if (!dbConn.ColumnExists <ZHISTaskEntity>(x => x.CREATEDATETIME))
{
dbConn.AddColumn <ZHISTaskEntity>(x => x.CREATEDATETIME);
}
#endregion
dbTransaction.Commit();
}
dbConn.Close();
}
Installer.logger.Debug((object)"创建表结束!");
}
catch (Exception ex)
{
Installer.logger.Error((object)ex.ToString());
throw ex;
}
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize()
{
var dbFile = Path.Combine(_appPaths.DataPath, "library.db");
_connection = await SqliteExtensions.ConnectToDb(dbFile, _logger).ConfigureAwait(false);
string[] queries =
{
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB)",
"create index if not exists idx_TypedBaseItems on TypedBaseItems(guid)",
"create table if not exists ChildrenIds (ParentId GUID, ItemId GUID, PRIMARY KEY (ParentId, ItemId))",
"create index if not exists idx_ChildrenIds on ChildrenIds(ParentId,ItemId)",
//pragmas
"pragma temp_store = memory",
"pragma shrink_memory"
};
_connection.RunQueries(queries, _logger);
_connection.AddColumn(_logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(_logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(_logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(_logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(_logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(_logger, "TypedBaseItems", "OfficialRating", "Text");
PrepareStatements();
_mediaStreamsRepository.Initialize();
_chapterRepository.Initialize();
}
public override void Apply(IDbConnection db)
{
db.AddColumn <UserView>(tbl => tbl.NrOfProfiles);
}
/// <summary>
/// Opens the connection to the database
/// </summary>
/// <returns>Task.</returns>
public async Task Initialize(SqliteUserDataRepository userDataRepo)
{
_connection = await CreateConnection(false).ConfigureAwait(false);
var createMediaStreamsTableCommand
= "create table if not exists mediastreams (ItemId GUID, StreamIndex INT, StreamType TEXT, Codec TEXT, Language TEXT, ChannelLayout TEXT, Profile TEXT, AspectRatio TEXT, Path TEXT, IsInterlaced BIT, BitRate INT NULL, Channels INT NULL, SampleRate INT NULL, IsDefault BIT, IsForced BIT, IsExternal BIT, Height INT NULL, Width INT NULL, AverageFrameRate FLOAT NULL, RealFrameRate FLOAT NULL, Level FLOAT NULL, PixelFormat TEXT, BitDepth INT NULL, IsAnamorphic BIT NULL, RefFrames INT NULL, CodecTag TEXT NULL, Comment TEXT NULL, NalLengthSize TEXT NULL, IsAvc BIT NULL, Title TEXT NULL, TimeBase TEXT NULL, CodecTimeBase TEXT NULL, PRIMARY KEY (ItemId, StreamIndex))";
string[] queries = {
"create table if not exists TypedBaseItems (guid GUID primary key, type TEXT, data BLOB, ParentId GUID, Path TEXT)",
"create table if not exists AncestorIds (ItemId GUID, AncestorId GUID, AncestorIdText TEXT, PRIMARY KEY (ItemId, AncestorId))",
"create index if not exists idx_AncestorIds1 on AncestorIds(AncestorId)",
"create index if not exists idx_AncestorIds2 on AncestorIds(AncestorIdText)",
"create table if not exists UserDataKeys (ItemId GUID, UserDataKey TEXT Priority INT, PRIMARY KEY (ItemId, UserDataKey))",
"create table if not exists ItemValues (ItemId GUID, Type INT, Value TEXT, CleanValue TEXT)",
"create table if not exists ProviderIds (ItemId GUID, Name TEXT, Value TEXT, PRIMARY KEY (ItemId, Name))",
// covering index
"create index if not exists Idx_ProviderIds1 on ProviderIds(ItemId,Name,Value)",
"create table if not exists Images (ItemId GUID NOT NULL, Path TEXT NOT NULL, ImageType INT NOT NULL, DateModified DATETIME, IsPlaceHolder BIT NOT NULL, SortOrder INT)",
"create index if not exists idx_Images on Images(ItemId)",
"create table if not exists People (ItemId GUID, Name TEXT NOT NULL, Role TEXT, PersonType TEXT, SortOrder int, ListOrder int)",
"drop index if exists idxPeopleItemId",
"create index if not exists idxPeopleItemId1 on People(ItemId,ListOrder)",
"create index if not exists idxPeopleName on People(Name)",
"create table if not exists "+ChaptersTableName+" (ItemId GUID, ChapterIndex INT, StartPositionTicks BIGINT, Name TEXT, ImagePath TEXT, PRIMARY KEY (ItemId, ChapterIndex))",
createMediaStreamsTableCommand,
"create index if not exists idx_mediastreams1 on mediastreams(ItemId)",
};
_connection.RunQueries(queries, Logger);
_connection.AddColumn(Logger, "AncestorIds", "AncestorIdText", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Path", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "StartDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "EndDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ChannelId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsMovie", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsSports", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsKids", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "CommunityRating", "Float");
_connection.AddColumn(Logger, "TypedBaseItems", "CustomRating", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IndexNumber", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsLocked", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "Name", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "OfficialRating", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "MediaType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Overview", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentIndexNumber", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "PremiereDate", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ProductionYear", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentId", "GUID");
_connection.AddColumn(Logger, "TypedBaseItems", "Genres", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ParentalRatingValue", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "SchemaVersion", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "SortName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "RunTimeTicks", "BIGINT");
_connection.AddColumn(Logger, "TypedBaseItems", "OfficialRatingDescription", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "HomePageUrl", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "VoteCount", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "DisplayMediaType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateCreated", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "DateModified", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "ForcedSortName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsOffline", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "LocationType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsSeries", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsLive", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsNews", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "IsPremiere", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "EpisodeTitle", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsRepeat", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataLanguage", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PreferredMetadataCountryCode", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsHD", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "ExternalEtag", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastRefreshed", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastSaved", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "IsInMixedFolder", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "LockedFields", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Studios", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Audio", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "ExternalServiceId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "Tags", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsFolder", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "InheritedParentalRatingValue", "INT");
_connection.AddColumn(Logger, "TypedBaseItems", "UnratedType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "TopParentId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsItemByName", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "SourceType", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "TrailerTypes", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "CriticRating", "Float");
_connection.AddColumn(Logger, "TypedBaseItems", "CriticRatingSummary", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "InheritedTags", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "CleanName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PresentationUniqueKey", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "SlugName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "OriginalTitle", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "PrimaryVersionId", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "DateLastMediaAdded", "DATETIME");
_connection.AddColumn(Logger, "TypedBaseItems", "Album", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "IsVirtualItem", "BIT");
_connection.AddColumn(Logger, "TypedBaseItems", "SeriesName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "UserDataKey", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "SeasonName", "Text");
_connection.AddColumn(Logger, "TypedBaseItems", "SeasonId", "GUID");
_connection.AddColumn(Logger, "TypedBaseItems", "SeriesId", "GUID");
_connection.AddColumn(Logger, "TypedBaseItems", "SeriesSortName", "Text");
_connection.AddColumn(Logger, "UserDataKeys", "Priority", "INT");
_connection.AddColumn(Logger, "ItemValues", "CleanValue", "Text");
_connection.AddColumn(Logger, ChaptersTableName, "ImageDateModified", "DATETIME");
string[] postQueries =
{
// obsolete
"drop index if exists idx_TypedBaseItems",
"drop index if exists idx_mediastreams",
"drop index if exists idx_"+ChaptersTableName,
"drop index if exists idx_UserDataKeys1",
"drop index if exists idx_UserDataKeys2",
"drop index if exists idx_TypeTopParentId3",
"drop index if exists idx_TypeTopParentId2",
"drop index if exists idx_TypeTopParentId4",
"drop index if exists idx_Type",
"drop index if exists idx_TypeTopParentId",
"drop index if exists idx_GuidType",
"drop index if exists idx_TopParentId",
"drop index if exists idx_TypeTopParentId6",
"drop index if exists idx_ItemValues2",
"drop index if exists Idx_ProviderIds",
"drop index if exists idx_ItemValues3",
"drop index if exists idx_ItemValues4",
"drop index if exists idx_ItemValues5",
"create index if not exists idx_PathTypedBaseItems on TypedBaseItems(Path)",
"create index if not exists idx_ParentIdTypedBaseItems on TypedBaseItems(ParentId)",
"create index if not exists idx_PresentationUniqueKey on TypedBaseItems(PresentationUniqueKey)",
"create index if not exists idx_GuidTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,Type,IsFolder,IsVirtualItem)",
//"create index if not exists idx_GuidMediaTypeIsFolderIsVirtualItem on TypedBaseItems(Guid,MediaType,IsFolder,IsVirtualItem)",
"create index if not exists idx_CleanNameType on TypedBaseItems(CleanName,Type)",
// covering index
"create index if not exists idx_TopParentIdGuid on TypedBaseItems(TopParentId,Guid)",
// live tv programs
"create index if not exists idx_TypeTopParentIdStartDate on TypedBaseItems(Type,TopParentId,StartDate)",
// covering index for getitemvalues
"create index if not exists idx_TypeTopParentIdGuid on TypedBaseItems(Type,TopParentId,Guid)",
// used by movie suggestions
"create index if not exists idx_TypeTopParentIdGroup on TypedBaseItems(Type,TopParentId,PresentationUniqueKey)",
"create index if not exists idx_TypeTopParentId5 on TypedBaseItems(TopParentId,IsVirtualItem)",
// latest items
"create index if not exists idx_TypeTopParentId9 on TypedBaseItems(TopParentId,Type,IsVirtualItem,PresentationUniqueKey,DateCreated)",
"create index if not exists idx_TypeTopParentId8 on TypedBaseItems(TopParentId,IsFolder,IsVirtualItem,PresentationUniqueKey,DateCreated)",
// resume
"create index if not exists idx_TypeTopParentId7 on TypedBaseItems(TopParentId,MediaType,IsVirtualItem,PresentationUniqueKey)",
// items by name
"create index if not exists idx_ItemValues6 on ItemValues(ItemId,Type,CleanValue)",
"create index if not exists idx_ItemValues7 on ItemValues(Type,CleanValue,ItemId)",
// covering index
"create index if not exists idx_UserDataKeys3 on UserDataKeys(ItemId,Priority,UserDataKey)"
};
_connection.RunQueries(postQueries, Logger);
PrepareStatements();
new MediaStreamColumns(_connection, Logger).AddColumns();
DataExtensions.Attach(_connection, Path.Combine(_config.ApplicationPaths.DataPath, "userdata_v2.db"), "UserDataDb");
await userDataRepo.Initialize(_connection, WriteLock).ConfigureAwait(false);
//await Vacuum(_connection).ConfigureAwait(false);
}