public SQLiteImageCache(string dbPath) { if (sqliteAssembly == null) { sqliteAssembly = System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(ApplicationPaths.AppConfigPath, "system.data.sqlite.dll")); AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(SqliteResolver); } SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder(); connectionstr.PageSize = 4096; connectionstr.CacheSize = 4096; connectionstr.SyncMode = SynchronizationModes.Normal; connectionstr.DataSource = dbPath; connection = new SQLiteConnection(connectionstr.ConnectionString); connection.Open(); string[] queries = {"create table if not exists images (guid, width, height, updated, stream_size, data blob)", "create unique index if not exists idx_images on images(guid, width)", }; foreach (var query in queries) { try { connection.Exec(query); } catch (Exception e) { Logger.ReportInfo(e.ToString()); } } alive = true; // tell writer to keep going Async.Queue("ImageCache Writer", DelayedWriter); }
public SQLiteDisplayRepository(string dbPath) { SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder(); connectionstr.PageSize = 4096; connectionstr.CacheSize = 1024; connectionstr.SyncMode = SynchronizationModes.Normal; connectionstr.DataSource = dbPath; connectionstr.JournalMode = SQLiteJournalModeEnum.Persist; //maybe better performance...? connection = new SQLiteConnection(connectionstr.ConnectionString); int retries = 0; bool connected = false; while (!connected && retries < MAX_RETRIES) { try { connection.Open(); connected = true; } catch (Exception e) { Logger.ReportException("Error connecting to database! Will retry " + MAX_RETRIES + " times.", e); retries++; Thread.Sleep(250); } } if (!connected) throw new ApplicationException("CRITICAL ERROR - Unable to connect to database: " + dbPath + ". Program cannot function."); string[] queries = { "create table if not exists display_prefs (guid primary key, view_type, show_labels, vertical_scroll, sort_order, index_by, use_banner, thumb_constraint_width, thumb_constraint_height, use_coverflow, use_backdrop )", "create index if not exists idx_display on display_prefs (guid)" }; foreach (var query in queries) { try { connection.Exec(query); } catch (Exception e) { Logger.ReportInfo(e.ToString()); } } alive = true; // tell writer to keep going Async.Queue("Sqlite Display Writer", DelayedWriter); }
public void FixUpSchema(SQLiteConnection connection) { //make sure all our columns are in the db var cmd = connection.CreateCommand(); cmd.CommandText = "PRAGMA table_info(items)"; List<string> dbCols = new List<string>(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { dbCols.Add(reader[1].ToString()); } } if (!dbCols.Contains("obj_type")) connection.Exec("Alter table items add column obj_type"); foreach (var col in this.AtomicColumns) { if (!dbCols.Contains(col.ColName)) { Logger.ReportInfo("Discovered new attribute: " + col.ColName + " on object type: "+ ObjType+". Adding to schema."); connection.Exec("Alter table items add column "+col.ColName); } } }
private SqliteItemRepository(string dbPath) { Logger.ReportInfo("==========Using new SQL Repo========"); dbFileName = dbPath; SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder(); connectionstr.PageSize = 4096; connectionstr.CacheSize = 4096; connectionstr.SyncMode = SynchronizationModes.Normal; connectionstr.DataSource = dbPath; connectionstr.JournalMode = SQLiteJournalModeEnum.Delete; connection = new SQLiteConnection(connectionstr.ConnectionString); int retries = 0; bool connected = false; while (!connected && retries < MAX_RETRIES) { try { connection.Open(); connected = true; } catch (Exception e) { Logger.ReportException("Error connecting to database! Will retry "+MAX_RETRIES+" times.", e); retries++; Thread.Sleep(250); } } if (!connected) throw new ApplicationException("CRITICAL ERROR - Unable to connect to database: " + dbPath + ". Program cannot function."); displayRepo = new SQLiteDisplayRepository(Path.Combine(ApplicationPaths.AppUserSettingsPath, "display.db")); playbackStatus = new FileBasedDictionary<PlaybackStatus>(GetPath("playstate", ApplicationPaths.AppUserSettingsPath)); //string playStateDBPath = Path.Combine(ApplicationPaths.AppUserSettingsPath, "playstate.db"); string[] queries = {"create table if not exists provider_data (guid, full_name, data)", "create unique index if not exists idx_provider on provider_data(guid, full_name)", "create table if not exists items (guid primary key)", "create index if not exists idx_items on items(guid)", "create table if not exists children (guid, child)", "create unique index if not exists idx_children on children(guid, child)", "create table if not exists list_items(guid, property, value, sort_order)", "create index if not exists idx_list on list_items(guid, property)", "create unique index if not exists idx_list_constraint on list_items(guid, property, value)", "create table if not exists schema_version (table_name primary key, version)", //"create table if not exists recent_list(top_parent, child, date_added)", //"create index if not exists idx_recent on recent_list(top_parent, child)", //"attach database '"+playStateDBPath+"' as playstate_db", //"create table if not exists playstate_db.play_states (guid primary key, play_count, position_ticks, playlist_position, last_played)", "pragma temp_store = memory", // @"create table display_prefs (guid primary key, view_type, show_labels, vertical_scroll // sort_order, index_by, use_banner, thumb_constraint_width, thumb_constraint_height, use_coverflow, use_backdrop )" //, "create table play_states (guid primary key, play_count, position_ticks, playlist_position, last_played)" }; foreach (var query in queries) { try { connection.Exec(query); } catch (Exception e) { Logger.ReportInfo(e.ToString()); } } alive = true; // tell writer to keep going Async.Queue("Sqlite Writer", DelayedWriter); }