/// <summary> /// Create a new <see cref="SQLite3Transaction" /> /// </summary> /// <param name="connection"> /// The <see cref="SQLite3" /> connection on which to create the transaction /// </param> internal SQLite3Transaction(SQLite3 connection) { if (connection == null) throw new ArgumentNullException("connection"); if (connection.InTransaction) throw new SQLite3Exception("There is a transaction present"); _connection = connection; _connection.Query("BEGIN"); }
private static void Main() { string filename = Path.Combine( Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "test.sqlite" ); using (var db = new SQLite3(filename)) { try { using (SQLite3Transaction transaction = db.BeginTransaction()) { db.Query("CREATE TABLE User ( ID INTEGER PRIMARY KEY, Name TEXT UNIQUE );"); db.Query("INSERT INTO User (Name) VALUES ('John')"); db.Query("INSERT INTO User (Name) VALUES ('Jeff')"); db.Query("INSERT INTO User (Name) VALUES ('Jesus')"); transaction.Commit(); } } catch (SQLite3Exception ex) { Console.WriteLine("EXCEPTION: " + ex.Message); } using (IEnumerator<User> enumerator = db.QueryEnumerator<User>("SELECT ID, Name FROM User")) { enumerator.MoveNext(); enumerator.MoveNext(); Console.WriteLine(enumerator.Current); enumerator.Reset(); while (enumerator.MoveNext()) Console.WriteLine(enumerator.Current); } } }
protected ManagedConnection CreateConnection(bool isReadOnly = false) { if (_connection != null) { return(_connection); } lock (WriteLock) { if (!_versionLogged) { _versionLogged = true; _logger.LogInformation("Sqlite version: {ver}", SQLite3.Version); _logger.LogInformation("Sqlite compiler options: {opts}", string.Join(",", SQLite3.CompilerOptions.ToArray())); } ConnectionFlags connectionFlags; if (isReadOnly) { //_logger.LogInformation("Opening read connection"); //connectionFlags = ConnectionFlags.ReadOnly; connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } else { //_logger.LogInformation("Opening write connection"); connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } if (EnableSingleConnection) { connectionFlags |= ConnectionFlags.PrivateCache; } else { connectionFlags |= ConnectionFlags.SharedCached; } connectionFlags |= ConnectionFlags.NoMutex; var db = SQLite3.Open(DbFilePath, connectionFlags, null); try { if (string.IsNullOrWhiteSpace(_defaultWal)) { _defaultWal = db.Query("PRAGMA journal_mode").SelectScalarString().First(); _logger.LogInformation("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); } var queries = new List <string> { //"PRAGMA cache size=-10000" //"PRAGMA read_uncommitted = true", "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"); } //foreach (var query in queries) //{ // db.Execute(query); //} db.ExecuteAll(string.Join(";", queries.ToArray())); } catch { using (db) { } throw; } _connection = new ManagedConnection(db, false); return(_connection); } }
/// <inheritdoc /> public void Perform() { var dataPath = _appPaths.DataPath; using (var connection = SQLite3.Open( Path.Combine(dataPath, DbFilename), ConnectionFlags.ReadOnly, null)) { using var dbContext = _dbProvider.CreateContext(); var authenticatedDevices = connection.Query("SELECT * FROM Tokens"); foreach (var row in authenticatedDevices) { var dateCreatedStr = row[9].ToString(); _ = DateTime.TryParse(dateCreatedStr, out var dateCreated); var dateLastActivityStr = row[10].ToString(); _ = DateTime.TryParse(dateLastActivityStr, out var dateLastActivity); if (row[6].IsDbNull()) { dbContext.ApiKeys.Add(new ApiKey(row[3].ToString()) { AccessToken = row[1].ToString(), DateCreated = dateCreated, DateLastActivity = dateLastActivity }); } else { var userId = new Guid(row[6].ToString()); var user = _userManager.GetUserById(userId); if (user is null) { // User doesn't exist, don't bring over the device. continue; } dbContext.Devices.Add(new Device( new Guid(row[6].ToString()), row[3].ToString(), row[4].ToString(), row[5].ToString(), row[2].ToString()) { AccessToken = row[1].ToString(), IsActive = row[8].ToBool(), DateCreated = dateCreated, DateLastActivity = dateLastActivity }); } } var deviceOptions = connection.Query("SELECT * FROM Devices"); var deviceIds = new HashSet <string>(); foreach (var row in deviceOptions) { if (row[2].IsDbNull()) { continue; } var deviceId = row[2].ToString(); if (deviceIds.Contains(deviceId)) { continue; } deviceIds.Add(deviceId); dbContext.DeviceOptions.Add(new DeviceOptions(deviceId) { CustomName = row[1].IsDbNull() ? null : row[1].ToString() }); } dbContext.SaveChanges(); } try { File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old")); var journalPath = Path.Combine(dataPath, DbFilename + "-journal"); if (File.Exists(journalPath)) { File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal")); } } catch (IOException e) { _logger.LogError(e, "Error renaming legacy activity log database to 'authentication.db.old'"); } }
/// <inheritdoc/> public void Perform() { var dataPath = _paths.DataPath; _logger.LogInformation("Migrating the user database may take a while, do not stop Jellyfin."); using (var connection = SQLite3.Open(Path.Combine(dataPath, DbFilename), ConnectionFlags.ReadOnly, null)) { var dbContext = _provider.CreateContext(); var queryResult = connection.Query("SELECT * FROM LocalUsersv2"); dbContext.RemoveRange(dbContext.Users); dbContext.SaveChanges(); foreach (var entry in queryResult) { UserMockup?mockup = JsonSerializer.Deserialize <UserMockup>(entry[2].ToBlob(), JsonDefaults.GetOptions()); if (mockup == null) { continue; } var userDataDir = Path.Combine(_paths.UserConfigurationDirectoryPath, mockup.Name); var config = File.Exists(Path.Combine(userDataDir, "config.xml")) ? (UserConfiguration)_xmlSerializer.DeserializeFromFile(typeof(UserConfiguration), Path.Combine(userDataDir, "config.xml")) : new UserConfiguration(); var policy = File.Exists(Path.Combine(userDataDir, "policy.xml")) ? (UserPolicy)_xmlSerializer.DeserializeFromFile(typeof(UserPolicy), Path.Combine(userDataDir, "policy.xml")) : new UserPolicy(); policy.AuthenticationProviderId = policy.AuthenticationProviderId?.Replace( "Emby.Server.Implementations.Library", "Jellyfin.Server.Implementations.Users", StringComparison.Ordinal) ?? typeof(DefaultAuthenticationProvider).FullName; policy.PasswordResetProviderId = typeof(DefaultPasswordResetProvider).FullName; int?maxLoginAttempts = policy.LoginAttemptsBeforeLockout switch { -1 => null, 0 => 3, _ => policy.LoginAttemptsBeforeLockout }; var user = new User(mockup.Name, policy.AuthenticationProviderId, policy.PasswordResetProviderId) { Id = entry[1].ReadGuidFromBlob(), InternalId = entry[0].ToInt64(), MaxParentalAgeRating = policy.MaxParentalRating, EnableUserPreferenceAccess = policy.EnableUserPreferenceAccess, RemoteClientBitrateLimit = policy.RemoteClientBitrateLimit, InvalidLoginAttemptCount = policy.InvalidLoginAttemptCount, LoginAttemptsBeforeLockout = maxLoginAttempts, SubtitleMode = config.SubtitleMode, HidePlayedInLatest = config.HidePlayedInLatest, EnableLocalPassword = config.EnableLocalPassword, PlayDefaultAudioTrack = config.PlayDefaultAudioTrack, DisplayCollectionsView = config.DisplayCollectionsView, DisplayMissingEpisodes = config.DisplayMissingEpisodes, AudioLanguagePreference = config.AudioLanguagePreference, RememberAudioSelections = config.RememberAudioSelections, EnableNextEpisodeAutoPlay = config.EnableNextEpisodeAutoPlay, RememberSubtitleSelections = config.RememberSubtitleSelections, SubtitleLanguagePreference = config.SubtitleLanguagePreference, Password = mockup.Password, EasyPassword = mockup.EasyPassword, LastLoginDate = mockup.LastLoginDate, LastActivityDate = mockup.LastActivityDate }; if (mockup.ImageInfos.Length > 0) { ItemImageInfo info = mockup.ImageInfos[0]; user.ProfileImage = new ImageInfo(info.Path) { LastModified = info.DateModified }; } user.SetPermission(PermissionKind.IsAdministrator, policy.IsAdministrator); user.SetPermission(PermissionKind.IsHidden, policy.IsHidden); user.SetPermission(PermissionKind.IsDisabled, policy.IsDisabled); user.SetPermission(PermissionKind.EnableSharedDeviceControl, policy.EnableSharedDeviceControl); user.SetPermission(PermissionKind.EnableRemoteAccess, policy.EnableRemoteAccess); user.SetPermission(PermissionKind.EnableLiveTvManagement, policy.EnableLiveTvManagement); user.SetPermission(PermissionKind.EnableLiveTvAccess, policy.EnableLiveTvAccess); user.SetPermission(PermissionKind.EnableMediaPlayback, policy.EnableMediaPlayback); user.SetPermission(PermissionKind.EnableAudioPlaybackTranscoding, policy.EnableAudioPlaybackTranscoding); user.SetPermission(PermissionKind.EnableVideoPlaybackTranscoding, policy.EnableVideoPlaybackTranscoding); user.SetPermission(PermissionKind.EnableContentDeletion, policy.EnableContentDeletion); user.SetPermission(PermissionKind.EnableContentDownloading, policy.EnableContentDownloading); user.SetPermission(PermissionKind.EnableSyncTranscoding, policy.EnableSyncTranscoding); user.SetPermission(PermissionKind.EnableMediaConversion, policy.EnableMediaConversion); user.SetPermission(PermissionKind.EnableAllChannels, policy.EnableAllChannels); user.SetPermission(PermissionKind.EnableAllDevices, policy.EnableAllDevices); user.SetPermission(PermissionKind.EnableAllFolders, policy.EnableAllFolders); user.SetPermission(PermissionKind.EnableRemoteControlOfOtherUsers, policy.EnableRemoteControlOfOtherUsers); user.SetPermission(PermissionKind.EnablePlaybackRemuxing, policy.EnablePlaybackRemuxing); user.SetPermission(PermissionKind.ForceRemoteSourceTranscoding, policy.ForceRemoteSourceTranscoding); user.SetPermission(PermissionKind.EnablePublicSharing, policy.EnablePublicSharing); foreach (var policyAccessSchedule in policy.AccessSchedules) { user.AccessSchedules.Add(policyAccessSchedule); } user.SetPreference(PreferenceKind.BlockedTags, policy.BlockedTags); user.SetPreference(PreferenceKind.EnabledChannels, policy.EnabledChannels); user.SetPreference(PreferenceKind.EnabledDevices, policy.EnabledDevices); user.SetPreference(PreferenceKind.EnabledFolders, policy.EnabledFolders); user.SetPreference(PreferenceKind.EnableContentDeletionFromFolders, policy.EnableContentDeletionFromFolders); user.SetPreference(PreferenceKind.OrderedViews, config.OrderedViews); user.SetPreference(PreferenceKind.GroupedFolders, config.GroupedFolders); user.SetPreference(PreferenceKind.MyMediaExcludes, config.MyMediaExcludes); user.SetPreference(PreferenceKind.LatestItemExcludes, config.LatestItemsExcludes); dbContext.Users.Add(user); } dbContext.SaveChanges(); } try { File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old")); var journalPath = Path.Combine(dataPath, DbFilename + "-journal"); if (File.Exists(journalPath)) { File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal")); } } catch (IOException e) { _logger.LogError(e, "Error renaming legacy user database to 'users.db.old'"); } } #nullable disable
public SQLiteConnection(string conn) { var result = SQLite3.Open(conn, out db, SQLiteFlags.SQLITE_OPEN_CREATE | SQLiteFlags.SQLITE_OPEN_READWRITE, IntPtr.Zero); SQLite3.CheckResult(db, result); }
public long InsertId() { return(SQLite3.LastInsertRowid(database.Handle)); }
protected virtual Sqlite3Statement Prepare() { var stmt = SQLite3.Prepare2(Connection.Handle, CommandText); return(stmt); }
protected IDatabaseConnection CreateConnection(bool isReadOnly = false) { if (_connection != null) { return(_connection.Clone(false)); } lock (WriteLock) { if (!_versionLogged) { _versionLogged = true; Logger.Info("Sqlite version: " + SQLite3.Version); Logger.Info("Sqlite compiler options: " + string.Join(",", SQLite3.CompilerOptions.ToArray())); } ConnectionFlags connectionFlags; if (isReadOnly) { //Logger.Info("Opening read connection"); //connectionFlags = ConnectionFlags.ReadOnly; connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } else { //Logger.Info("Opening write connection"); connectionFlags = ConnectionFlags.Create; connectionFlags |= ConnectionFlags.ReadWrite; } if (EnableSingleConnection) { connectionFlags |= ConnectionFlags.PrivateCache; } else { connectionFlags |= ConnectionFlags.SharedCached; } connectionFlags |= ConnectionFlags.NoMutex; var db = SQLite3.Open(DbFilePath, connectionFlags, null, false); try { if (string.IsNullOrWhiteSpace(_defaultWal)) { using (var statement = PrepareStatement(db, "PRAGMA journal_mode".AsSpan())) { foreach (var row in statement.ExecuteQuery()) { _defaultWal = row.GetString(0); break; } } Logger.Info("Default journal_mode for {0} is {1}", DbFilePath, _defaultWal); } var queries = new List <string> { //"PRAGMA cache size=-10000" //"PRAGMA read_uncommitted = true", "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"); } //foreach (var query in queries) //{ // db.Execute(query); //} db.ExecuteAll(string.Join(";", queries.ToArray())); } catch { using (db) { } throw; } _connection = db; return(db); } }
public ConnectionBuilder() { this.connection = SQLite3.OpenInMemory(); }
public static SQLiteException New(SQLite3.Result r, string message) { return new SQLiteException(r, message); }
/// <inheritdoc /> public void Perform() { HomeSectionType[] defaults = { HomeSectionType.SmallLibraryTiles, HomeSectionType.Resume, HomeSectionType.ResumeAudio, HomeSectionType.LiveTv, HomeSectionType.NextUp, HomeSectionType.LatestMedia, HomeSectionType.None, }; var chromecastDict = new Dictionary <string, ChromecastVersion>(StringComparer.OrdinalIgnoreCase) { { "stable", ChromecastVersion.Stable }, { "nightly", ChromecastVersion.Unstable }, { "unstable", ChromecastVersion.Unstable } }; var displayPrefs = new HashSet <string>(StringComparer.OrdinalIgnoreCase); var customDisplayPrefs = new HashSet <string>(StringComparer.OrdinalIgnoreCase); var dbFilePath = Path.Combine(_paths.DataPath, DbFilename); using (var connection = SQLite3.Open(dbFilePath, ConnectionFlags.ReadOnly, null)) { using var dbContext = _provider.CreateContext(); var results = connection.Query("SELECT * FROM userdisplaypreferences"); foreach (var result in results) { var dto = JsonSerializer.Deserialize <DisplayPreferencesDto>(result[3].ToBlob(), _jsonOptions); if (dto == null) { continue; } var itemId = new Guid(result[1].ToBlob()); var dtoUserId = new Guid(result[1].ToBlob()); var client = result[2].ToString(); var displayPreferencesKey = $"{dtoUserId}|{itemId}|{client}"; if (displayPrefs.Contains(displayPreferencesKey)) { // Duplicate display preference. continue; } displayPrefs.Add(displayPreferencesKey); var existingUser = _userManager.GetUserById(dtoUserId); if (existingUser == null) { _logger.LogWarning("User with ID {UserId} does not exist in the database, skipping migration.", dtoUserId); continue; } var chromecastVersion = dto.CustomPrefs.TryGetValue("chromecastVersion", out var version) ? chromecastDict[version] : ChromecastVersion.Stable; dto.CustomPrefs.Remove("chromecastVersion"); var displayPreferences = new DisplayPreferences(dtoUserId, itemId, client) { IndexBy = Enum.TryParse <IndexingKind>(dto.IndexBy, true, out var indexBy) ? indexBy : (IndexingKind?)null, ShowBackdrop = dto.ShowBackdrop, ShowSidebar = dto.ShowSidebar, ScrollDirection = dto.ScrollDirection, ChromecastVersion = chromecastVersion, SkipForwardLength = dto.CustomPrefs.TryGetValue("skipForwardLength", out var length) && int.TryParse(length, out var skipForwardLength) ? skipForwardLength : 30000, SkipBackwardLength = dto.CustomPrefs.TryGetValue("skipBackLength", out length) && !string.IsNullOrEmpty(length) && int.TryParse(length, out var skipBackwardLength) ? skipBackwardLength : 10000, EnableNextVideoInfoOverlay = dto.CustomPrefs.TryGetValue("enableNextVideoInfoOverlay", out var enabled) && !string.IsNullOrEmpty(enabled) ? bool.Parse(enabled) : true, DashboardTheme = dto.CustomPrefs.TryGetValue("dashboardtheme", out var theme) ? theme : string.Empty, TvHome = dto.CustomPrefs.TryGetValue("tvhome", out var home) ? home : string.Empty }; dto.CustomPrefs.Remove("skipForwardLength"); dto.CustomPrefs.Remove("skipBackLength"); dto.CustomPrefs.Remove("enableNextVideoInfoOverlay"); dto.CustomPrefs.Remove("dashboardtheme"); dto.CustomPrefs.Remove("tvhome"); for (int i = 0; i < 7; i++) { var key = "homesection" + i; dto.CustomPrefs.TryGetValue(key, out var homeSection); displayPreferences.HomeSections.Add(new HomeSection { Order = i, Type = Enum.TryParse <HomeSectionType>(homeSection, true, out var type) ? type : defaults[i] });
public void MultipleMigrationsCanCreateAndPopulateDatabase() { var workDirPath = Path.Combine(temp_path, "multiple_migrations_can_create_and_populate_database"); Directory.CreateDirectory(workDirPath); var DBPath = Path.Combine(workDirPath, "db.sqlite3"); var Migrations = new Dictionary <string, string>(); Migrations["1_create.sql"] = string.Join( "\n", new string[] { "--- database id 1", "--- database up", "CREATE TABLE test (", " id INT NOT NULL,", " name VARCHAR(10) NOT NULL", ");", "--- database down", "DROP TABLE test;" } ); Migrations["2_populate.sql"] = string.Join( "\n", new string[] { "--- database id 2", "--- database up", "INSERT INTO test", " (id, name)", "VALUES", " (1, 'Jacinto'),", " (2, 'Susana');", "--- database down", "DELETE FROM test WHERE id IN (1, 2);" } ); var config = new Configuration { DBPath = DBPath, MigrationsProvider = new TestMigrationsProvider { MigrationsDictionary = Migrations } }; var migrator = new SQLiteMigrator(config); migrator.init(); using (var db = SQLite3.Open(DBPath)) { var data = db.Query("SELECT id, name FROM test ORDER BY id ASC") .Select((arg) => new Tuple <int, string>(arg[0].ToInt(), arg[1].ToString())) .ToArray(); Assert.AreEqual(data.Length, 2, "There should be 2 rows in the table"); var jacinto = data[0]; var susana = data[1]; Assert.AreEqual(jacinto.Item1, 1, "Jacinto's ID should be 1"); Assert.AreEqual(jacinto.Item2, "Jacinto", "Jacinto's name should be Jacinto"); Assert.AreEqual(susana.Item1, 2, "Susana's ID should be 2"); Assert.AreEqual(susana.Item2, "Susana", "Susana's name should be Susana"); } }
/// <summary> /// bind a value to the statment with his index</summary> /// <returns> /// returnif the operation is a success </returns> /// <param name="value"> the object to bind</param> /// <param name="index"> the index of parameter</param> private bool bindValue(object value, int index) { if (value == null) { SQLite3.BindNull(stmt, index); } else { if (value is Int32) { SQLite3.BindInt(stmt, index, (int)value); } else if (value is String) { SQLite3.BindText(stmt, index, (string)value, -1, new IntPtr(-1)); } else if (value is Byte || value is UInt16 || value is SByte || value is Int16) { SQLite3.BindInt(stmt, index, Convert.ToInt32(value)); } else if (value is Boolean) { SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0); } else if (value is UInt32 || value is Int64) { SQLite3.BindInt64(stmt, index, Convert.ToInt64(value)); } else if (value is Single || value is Double || value is Decimal) { SQLite3.BindDouble(stmt, index, Convert.ToDouble(value)); } else if (value is DateTime) { SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, new IntPtr(-1)); #if !NETFX_CORE } else if (value.GetType().IsEnum) { #else } else if (value.GetType().GetTypeInfo().IsEnum) { #endif SQLite3.BindInt(stmt, index, Convert.ToInt32(value)); } else if (value is byte[]) { SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, new IntPtr(-1)); } else if (value is Guid) { SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, new IntPtr(-1)); } else { throw new NotSupportedException("Cannot store type: " + value.GetType()); } } return(true); }
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 } }
/// <inheritdoc/> public void Perform() { var logLevelDictionary = new Dictionary <string, LogLevel>(StringComparer.OrdinalIgnoreCase) { { "None", LogLevel.None }, { "Trace", LogLevel.Trace }, { "Debug", LogLevel.Debug }, { "Information", LogLevel.Information }, { "Info", LogLevel.Information }, { "Warn", LogLevel.Warning }, { "Warning", LogLevel.Warning }, { "Error", LogLevel.Error }, { "Critical", LogLevel.Critical } }; var dataPath = _paths.DataPath; using (var connection = SQLite3.Open( Path.Combine(dataPath, DbFilename), ConnectionFlags.ReadOnly, null)) { _logger.LogWarning("Migrating the activity database may take a while, do not stop Jellyfin."); using var dbContext = _provider.CreateContext(); var queryResult = connection.Query("SELECT * FROM ActivityLog ORDER BY Id ASC"); // Make sure that the database is empty in case of failed migration due to power outages, etc. dbContext.ActivityLogs.RemoveRange(dbContext.ActivityLogs); dbContext.SaveChanges(); // Reset the autoincrement counter dbContext.Database.ExecuteSqlRaw("UPDATE sqlite_sequence SET seq = 0 WHERE name = 'ActivityLog';"); dbContext.SaveChanges(); var newEntries = queryResult.Select(entry => { if (!logLevelDictionary.TryGetValue(entry[8].ToString(), out var severity)) { severity = LogLevel.Trace; } var newEntry = new ActivityLog( entry[1].ToString(), entry[4].ToString(), entry[6].SQLiteType == SQLiteType.Null ? Guid.Empty : Guid.Parse(entry[6].ToString())) { DateCreated = entry[7].ReadDateTime(), LogSeverity = severity }; if (entry[2].SQLiteType != SQLiteType.Null) { newEntry.Overview = entry[2].ToString(); } if (entry[3].SQLiteType != SQLiteType.Null) { newEntry.ShortOverview = entry[3].ToString(); } if (entry[5].SQLiteType != SQLiteType.Null) { newEntry.ItemId = entry[5].ToString(); } return(newEntry); }); dbContext.ActivityLogs.AddRange(newEntries); dbContext.SaveChanges(); } try { File.Move(Path.Combine(dataPath, DbFilename), Path.Combine(dataPath, DbFilename + ".old")); var journalPath = Path.Combine(dataPath, DbFilename + "-journal"); if (File.Exists(journalPath)) { File.Move(journalPath, Path.Combine(dataPath, DbFilename + ".old-journal")); } } catch (IOException e) { _logger.LogError(e, "Error renaming legacy activity log database to 'activitylog.db.old'"); } }
internal static void BindParameter(SQLitePCL.sqlite3_stmt stmt, int index, object value, bool storeDateTimeAsTicks) { if (value == null) { SQLite3.BindNull(stmt, index); } else { if (value is Int32) { SQLite3.BindInt(stmt, index, (int)value); } else if (value is String) { SQLite3.BindText(stmt, index, (string)value, -1, NegativePointer); } else if (value is Byte || value is UInt16 || value is SByte || value is Int16) { SQLite3.BindInt(stmt, index, Convert.ToInt32(value)); } else if (value is Boolean) { SQLite3.BindInt(stmt, index, (bool)value ? 1 : 0); } else if (value is UInt32 || value is Int64) { SQLite3.BindInt64(stmt, index, Convert.ToInt64(value)); } else if (value is Single || value is Double || value is Decimal) { SQLite3.BindDouble(stmt, index, Convert.ToDouble(value)); } else if (value is TimeSpan) { SQLite3.BindInt64(stmt, index, ((TimeSpan)value).Ticks); } else if (value is DateTime) { if (storeDateTimeAsTicks) { SQLite3.BindInt64(stmt, index, ((DateTime)value).Ticks); } else { SQLite3.BindText(stmt, index, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss"), -1, NegativePointer); } } else if (value is DateTimeOffset) { SQLite3.BindInt64(stmt, index, ((DateTimeOffset)value).UtcTicks); } else if (value.GetType().GetTypeInfo().IsEnum) { SQLite3.BindInt(stmt, index, Convert.ToInt32(value)); } else if (value is byte[]) { SQLite3.BindBlob(stmt, index, (byte[])value, ((byte[])value).Length, NegativePointer); } else if (value is Guid) { SQLite3.BindText(stmt, index, ((Guid)value).ToString(), 72, NegativePointer); } else { throw new NotSupportedException("Cannot store type: " + value.GetType()); } } }
public static extern SQLite3.Result Config(SQLite3.ConfigOption option);
private int GetLastInsertId() { return((int)SQLite3.LastInsertRowid(db.Handle)); }
public void TestJoins() { var addressSelector = RS.RowToObject <Address>(); var personSelector = RS.RowToObject <Person>(); var businessSelector = RS.RowToObject <Business>(); var employedBySelector = RS.RowToObject <EmployedBy>(); using (var db = SQLite3.OpenInMemory()) { db.InitTable <Address>(); db.InitTable <Person>(); db.InitTable <Business>(); db.InitTable <EmployedBy>(); var addresses = db.InsertOrReplaceAll( new Address[] { new Address() { ZipCode = 12345 }, new Address() { ZipCode = 67890 } }, addressSelector).Values.ToList(); var people = db.InsertOrReplaceAll( new Person[] { new Person() { FirstName = "Bob", LastName = "Doe", AddressId = addresses[0].Id.Value }, new Person() { FirstName = "Jane", LastName = "Doe", AddressId = addresses[0].Id.Value }, new Person() { FirstName = "Doe", LastName = "Adear", AddressId = addresses[1].Id.Value }, new Person() { FirstName = "AFemale", LastName = "Dear", AddressId = addresses[1].Id.Value }, }, personSelector).Values.ToList(); var businesses = db.InsertOrReplaceAll( new Business[] { new Business() { Name = "ACompany", AddressId = addresses[0].Id.Value, WebSite = new Uri("http://www.acompany.example.com") }, new Business() { Name = "BCompany", AddressId = addresses[1].Id.Value, WebSite = new Uri("http://www.bcompany.example.com") } }, businessSelector).Values.ToList(); var employeesToBusiness = db.InsertOrReplaceAll( new EmployedBy[] { new EmployedBy() { EmployeeId = people[0].Id.Value, BusinessId = businesses[0].Id.Value }, new EmployedBy() { EmployeeId = people[1].Id.Value, BusinessId = businesses[0].Id.Value }, new EmployedBy() { EmployeeId = people[2].Id.Value, BusinessId = businesses[1].Id.Value }, new EmployedBy() { EmployeeId = people[2].Id.Value, BusinessId = businesses[0].Id.Value }, }, employedBySelector).Values.ToList(); var peopleWhoWorkAtQuery = SqlQuery.From <Person>() .Join <EmployedBy>((person, employedBy) => person.Id == employedBy.EmployeeId) .Join <Business>((person, employedBy, business) => business.Id == employedBy.BusinessId) .SelectDistinct() .Where <long>((person, employedBy, business, businessId) => // FIXME: Would be cool if you could instead use // business == businesses[0] // and then introspect the primary keys to make it work business.Id == businessId); var peopleWhoWorkAt = db.Query(peopleWhoWorkAtQuery, businesses[0].Id) .Select(x => Tuple.Create(personSelector(x), businessSelector(x))) .ToList(); Assert.Equal(peopleWhoWorkAt.Count, 3); } }
protected SQLiteException(SQLite3.Result r, string message) : base(message) { this.Result = r; }
/// <inheritdoc /> public void Perform() { HomeSectionType[] defaults = { HomeSectionType.SmallLibraryTiles, HomeSectionType.Resume, HomeSectionType.ResumeAudio, HomeSectionType.LiveTv, HomeSectionType.NextUp, HomeSectionType.LatestMedia, HomeSectionType.None, }; var chromecastDict = new Dictionary <string, ChromecastVersion>(StringComparer.OrdinalIgnoreCase) { { "stable", ChromecastVersion.Stable }, { "nightly", ChromecastVersion.Unstable }, { "unstable", ChromecastVersion.Unstable } }; var dbFilePath = Path.Combine(_paths.DataPath, DbFilename); using (var connection = SQLite3.Open(dbFilePath, ConnectionFlags.ReadOnly, null)) { using var dbContext = _provider.CreateContext(); var results = connection.Query("SELECT * FROM userdisplaypreferences"); foreach (var result in results) { var dto = JsonSerializer.Deserialize <DisplayPreferencesDto>(result[3].ToString(), _jsonOptions); var chromecastVersion = dto.CustomPrefs.TryGetValue("chromecastVersion", out var version) ? chromecastDict[version] : ChromecastVersion.Stable; var displayPreferences = new DisplayPreferences(new Guid(result[1].ToBlob()), result[2].ToString()) { IndexBy = Enum.TryParse <IndexingKind>(dto.IndexBy, true, out var indexBy) ? indexBy : (IndexingKind?)null, ShowBackdrop = dto.ShowBackdrop, ShowSidebar = dto.ShowSidebar, ScrollDirection = dto.ScrollDirection, ChromecastVersion = chromecastVersion, SkipForwardLength = dto.CustomPrefs.TryGetValue("skipForwardLength", out var length) ? int.Parse(length, CultureInfo.InvariantCulture) : 30000, SkipBackwardLength = dto.CustomPrefs.TryGetValue("skipBackLength", out length) ? int.Parse(length, CultureInfo.InvariantCulture) : 10000, EnableNextVideoInfoOverlay = dto.CustomPrefs.TryGetValue("enableNextVideoInfoOverlay", out var enabled) ? bool.Parse(enabled) : true, DashboardTheme = dto.CustomPrefs.TryGetValue("dashboardtheme", out var theme) ? theme : string.Empty, TvHome = dto.CustomPrefs.TryGetValue("tvhome", out var home) ? home : string.Empty }; for (int i = 0; i < 7; i++) { dto.CustomPrefs.TryGetValue("homesection" + i, out var homeSection); displayPreferences.HomeSections.Add(new HomeSection { Order = i, Type = Enum.TryParse <HomeSectionType>(homeSection, true, out var type) ? type : defaults[i] });
public int LastInsertRowId() { return((int)SQLite3.LastInsertRowid(Connection.GetConnection().Handle)); }
private List <object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow) { var lstRes = new List <object[]>(); SQLitePCL.sqlite3_stmt stQuery = null; try { stQuery = SQLite3.Prepare2(database.DatabaseNotAsync.Handle, sqlString); var colLenght = SQLite3.ColumnCount(stQuery); if (includeColumnNamesAsFirstRow) { var obj = new object[colLenght]; lstRes.Add(obj); for (int i = 0; i < colLenght; i++) { obj[i] = SQLite3.ColumnName(stQuery, i); } } while (SQLite3.Step(stQuery) == SQLite3.Result.Row) { var obj = new object[colLenght]; lstRes.Add(obj); for (int i = 0; i < colLenght; i++) { var colType = SQLite3.ColumnType(stQuery, i); switch (colType) { case SQLite3.ColType.Blob: obj[i] = SQLite3.ColumnBlob(stQuery, i); break; case SQLite3.ColType.Float: obj[i] = SQLite3.ColumnDouble(stQuery, i); break; case SQLite3.ColType.Integer: obj[i] = SQLite3.ColumnInt(stQuery, i); break; case SQLite3.ColType.Null: obj[i] = null; break; case SQLite3.ColType.Text: obj[i] = SQLite3.ColumnString(stQuery, i); break; } } } return(lstRes); } catch (Exception) { return(null); } finally { if (stQuery != null) { SQLite3.Finalize(stQuery); } } }