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