// Changed - added column check for actorinfo thumbURL column
private void Open()
{
Log.Info("opening video database");
try
{
// Open database
String strPath = Config.GetFolder(Config.Dir.Database);
try
{
Directory.CreateDirectory(strPath);
}
catch (Exception) {}
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
DatabaseUtility.SetPragmas(m_db);
CreateTables();
//
// Check and upgrade database with new columns if necessary
//
UpgradeDatabase();
}
catch (Exception ex)
{
Log.Error("videodatabase exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
Log.Info("video database opened");
}
public void Dispose()
{
if (sqlClient != null)
{
sqlClient.Dispose();
sqlClient = null;
}
}
public void OpenDatabase()
{
var isDatabaseFileExists = File.Exists(Constants.DatabaseName);
_database=new SQLiteClient(Constants.DatabaseName);
if(!isDatabaseFileExists)
{
CreateTables();
}
}
public void Dispose()
{
if (m_db != null)
{
m_db.Close();
m_db.Dispose();
m_db = null;
}
}
public bool Init()
{
if (string.IsNullOrEmpty(DatabasePath))
{
Logger.LogError("No database path has been specified");
return false;
}
sqlClient = new SQLiteClient(DatabasePath);
return true;
}
public static void SetPragmas(SQLiteClient m_db)
{
m_db.Execute("PRAGMA encoding = \"UTF-8\"");
m_db.Execute("PRAGMA cache_size=4096");
m_db.Execute("PRAGMA page_size=8192");
m_db.Execute("PRAGMA synchronous='OFF'");
m_db.Execute("PRAGMA count_changes=1");
m_db.Execute("PRAGMA full_column_names=0");
m_db.Execute("PRAGMA short_column_names=0");
m_db.Execute("PRAGMA auto_vacuum=0");
}
public void Close()
{
try
{
if (dbClient != null)
dbClient.Close();
dbClient = null;
}
catch (Exception ex)
{
logger.Error("close: " + ex);
}
}
private DataWorker()
{
//Check if file exists otherwise create file & tables
dbFile = Config.GetFile(Config.Dir.Database, dbName);
if (!File.Exists(dbFile))
{
db = new SQLiteClient(dbFile);
db.Execute("CREATE TABLE servers (name char(20), address char(250), username char(50), password char(50));");
}
else {
db = new SQLiteClient(dbFile);
}
}
private FavoritesDatabase()
{
try
{
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "OnlineVideoDatabase.db3"));
DatabaseUtility.SetPragmas(m_db);
DatabaseUtility.AddTable(m_db, "FAVORITE_VIDEOS", "CREATE TABLE FAVORITE_VIDEOS(VDO_ID integer primary key autoincrement,VDO_NM text,VDO_URL text,VDO_DESC text,VDO_TAGS text,VDO_LENGTH text,VDO_OTHER_NFO text,VDO_IMG_URL text,VDO_SITE_ID text)\n");
DatabaseUtility.AddTable(m_db, "FAVORITE_Categories", "CREATE TABLE FAVORITE_Categories(CAT_ID integer primary key autoincrement,CAT_Name text,CAT_Desc text,CAT_ThumbUrl text,CAT_Hierarchy text,CAT_SITE_ID text)\n");
DatabaseUtility.AddTable(m_db, "PREFERRED_LAYOUT", "CREATE TABLE PREFERRED_LAYOUT(Site_Name text, Category_Hierarchy text, Layout integer, PRIMARY KEY (Site_Name, Category_Hierarchy) ON CONFLICT REPLACE)\n");
}
catch (SQLiteException ex)
{
Log.Instance.Error("database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public static void Close()
{
string databaseFile = Settings.GetPath(Settings.Path.database);
try
{
m_db.Close();
m_db.Dispose();
m_db = null; ;
MPTVSeriesLog.Write("Successfully closed Database: " + databaseFile);
}
catch (Exception)
{
MPTVSeriesLog.Write("Failed closing Database: " + databaseFile);
}
}
/// <summary>
/// Check if a table column exists
/// </summary>
/// <param name="table">table name</param>
/// <param name="column">column name</param>
/// <returns>true if table + column exists
/// false if table does not exists or if table doesnt contain the specified column</returns>
public static bool TableColumnExists(SQLiteClient m_db, string table, string column)
{
SQLiteResultSet results;
if (m_db == null)
{
return false;
}
if (table == null)
{
return false;
}
if (table.Length == 0)
{
return false;
}
// This only works for tables that are not empty
//results = m_db.Execute("SELECT * FROM '" + table + "'");
//if (results != null)
//{
// for (int i = 0; i < results.ColumnNames.Count; ++i)
// {
// if ((string)results.ColumnNames[i] == column)
// {
// return true;
// }
// }
//}
//return false;
// We will use --> PRAGMA table_info( your_table_name )
// PRAGMA returns one row for each column in the named table.
// Columns in the result set include the columnID, column name, data type,
// whether or not the column can be NULL, and the default value for the column.
// More info: http://www.sqlite.org/pragma.html
results = m_db.Execute("PRAGMA table_info('" + table + "')");
if (results != null)
{
for (int i = 0; i < results.Rows.Count; ++i)
{
if ((string)results.Rows[i].fields[1] == column) // fields[1] is column name
{
return true;
}
}
}
return false;
}
private static void InitDB()
{
if (System.Diagnostics.Process.GetCurrentProcess().ProcessName.ToLower() == "devenv")
return;
string databaseFile = Settings.GetPath(Settings.Path.database);
bool databaseExists = true;
if (!File.Exists(databaseFile))
databaseExists = false;
try
{
m_db = new SQLiteClient(databaseFile);
// check database integrity
if (databaseExists)
{
if (CheckIntegrity())
{
// backup volume if all is good
Backup();
}
else
{
// restore last known good volume if corrupt
Restore();
}
}
ExecutePragmas();
MPTVSeriesLog.Write("Successfully opened database. Filename = '{0}'", databaseFile);
return;
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Failed to open database. Filename = '{0}', Reason = '{1}'", databaseFile, ex.Message);
// restore last known good volume
Restore();
ExecutePragmas();
}
}
public FolderSettingsSqlLite()
{
try
{
// Open database
Log.Info("open folderdatabase");
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "FolderDatabase3.db3"));
DatabaseUtility.SetPragmas(m_db);
DatabaseUtility.AddTable(m_db, "tblPath", "CREATE TABLE tblPath ( idPath integer primary key, strPath text)");
DatabaseUtility.AddTable(m_db, "tblSetting",
"CREATE TABLE tblSetting ( idSetting integer primary key, idPath integer , tagName text, tagValue text)");
}
catch (Exception ex)
{
Log.Error(ex);
}
}
private void Open()
{
try
{
// Maybe called by an exception
if (m_db != null)
{
try
{
m_db.Close();
m_db.Dispose();
Log.Warn("PictureDatabaseSqlLite: Disposing current instance..");
}
catch (Exception) {}
}
// Open database
try
{
Directory.CreateDirectory(Config.GetFolder(Config.Dir.Database));
}
catch (Exception) {}
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "PictureDatabase.db3"));
// Retry 10 times on busy (DB in use or system resources exhausted)
m_db.BusyRetries = 10;
// Wait 100 ms between each try (default 10)
m_db.BusyRetryDelay = 100;
_dbHealth = DatabaseUtility.IntegrityCheck(m_db);
DatabaseUtility.SetPragmas(m_db);
CreateTables();
InitSettings();
}
catch (Exception ex)
{
Log.Error("picture database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
Open();
}
Log.Info("picture database opened");
}
// Changed - added column check for actorinfo thumbURL column
private void Open()
{
Log.Info("opening video database");
try
{
if (m_db != null)
{
Log.Info("Opening video database: VideoDatabase already opened.");
return;
}
// Open database
String strPath = Config.GetFolder(Config.Dir.Database);
try
{
Directory.CreateDirectory(strPath);
}
catch (Exception) {}
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
_dbHealth = DatabaseUtility.IntegrityCheck(m_db);
DatabaseUtility.SetPragmas(m_db);
CreateTables();
//
// Check and upgrade database with new columns if necessary
//
UpgradeDatabase();
// Clean trash from tables
//CleanUpDatabase();
// Update latest movies
SetLatestMovieProperties();
}
catch (Exception ex)
{
Log.Error("videodatabase exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
Log.Info("video database opened");
}
private bool ConnectToDatabase()
{
try
{
this.m_sqlClient = new SQLiteClient(Application.StartupPath+"/WebMeeting_Client.dll");
}
catch(Exception e)
{
// display error
//MessageBox.Show(e.Message.ToString());
return false;
}
return true;
}
public static bool IntegrityCheck(SQLiteClient m_db)
{
SQLiteResultSet results;
if (m_db == null)
{
return false;
}
results = m_db.Execute("PRAGMA integrity_check;");
if (results != null)
{
if (results.Rows.Count == 1)
{
SQLiteResultSet.Row arr = results.Rows[0];
if (arr.fields.Count == 1)
{
if (arr.fields[0] == "ok")
{
Log.Debug("IntegrityCheck: the {0} is OK", m_db.DatabaseName);
return true;
}
}
}
}
Log.Error("IntegrityCheck: the {0} is corrupt.", m_db.DatabaseName);
return false;
}
private void CreateArtistTable(SQLiteClient db)
{
db.Execute("CREATE TABLE ARTISTS(ID integer primary key autoincrement,ARTIST_ID text, ARTIST_NAME text, ARTIST_IMG text, ARTIST_BIO text, ARTIST_USER text, ARTIST_TAG text, ARTIST_GENRE text, ARTIST_IMG_URL text, ARTIST_PLAYED integer)\n");
DatabaseUtility.AddIndex(m_db, "idx_artists_name", "CREATE INDEX idx_artists_name ON ARTISTS(ARTIST_NAME)");
DatabaseUtility.AddIndex(m_db, "idx_artists_ARTIST_ID", "CREATE INDEX idx_ARTIST_ID ON ARTISTS(ARTIST_ID)");
}
// Properties
public SQLiteException(string message, SQLiteClient.SqliteError code) : base(message)
{
this.errorCode = code;
}
public bool InitDB(string dbFilename)
{
try
{
var str = Path.Combine(Config.GetFolder((Config.Dir) 4), dbFilename);
if (File.Exists(str))
{
if (new FileInfo(str).Length > 0L)
{
dbClient = new SQLiteClient(str);
return true;
}
}
}
catch
{
dbClient = null;
}
return false;
}
public void InitDB(string type)
{
logger.Debug("initDB: Start: "+type);
try
{
IsScraping = false;
var DBFile = Config.GetFile((Config.Dir) 4, dbFilename);
var flag = false;
flag = (!File.Exists(DBFile));
dbClient = new SQLiteClient(DBFile);
dbClient.Execute("PRAGMA synchronous=OFF;");
dbClient.Execute("PRAGMA encoding='UTF-8';");
dbClient.Execute("PRAGMA cache_size=5000;");
dbClient.Execute("PRAGMA temp_store = MEMORY;");
if (flag)
CreateDBMain() ;
logger.Info("Successfully Opened Database: "+dbFilename);
UpgradeDBMain(type);
if (type.Equals("upgrade", StringComparison.CurrentCulture))
return;
if (HtAnyFanart == null)
HtAnyFanart = new Hashtable();
try
{
m_db = MusicDatabase.Instance;
logger.Debug("Successfully Opened Database: "+m_db.DatabaseName);
} catch { }
try
{
// v_db = VideoDatabase.Instance;
logger.Debug("Successfully Opened Database: "+VideoDatabase.DatabaseName);
} catch { }
}
catch (Exception ex)
{
logger.Error("initDB: Could Not Open Database: "+dbFilename+". " + ex);
dbClient = null;
}
}
public static void CompactDatabase(SQLiteClient m_db)
{
m_db.Execute("vacuum");
}
public void Dispose()
{
if (!disposed)
{
disposed = true;
if (m_db != null)
{
try
{
m_db.Close();
m_db.Dispose();
}
catch (Exception) {}
m_db = null;
}
}
}
/// <summary>
/// Check if a table exists
/// </summary>
/// <param name="table">name of table</param>
/// <returns>true: table exists
/// false: table does not exist</returns>
public static bool TableExists(SQLiteClient m_db, string table)
{
SQLiteResultSet results;
if (m_db == null)
{
return false;
}
if (table == null)
{
return false;
}
if (table.Length == 0)
{
return false;
}
results = m_db.Execute("SELECT name FROM sqlite_master WHERE name like '" + table + "' and type like 'table'");
// UNION ALL SELECT name FROM sqlite_temp_master WHERE type='table' ORDER BY name");
if (results != null)
{
if (results.Rows.Count == 1)
{
SQLiteResultSet.Row arr = results.Rows[0];
if (arr.fields.Count == 1)
{
if (arr.fields[0] == table)
{
return true;
}
}
}
}
return false;
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main()
{
SQLiteClient db;
SQLiteResultSet results;
Console.WriteLine("Basic test app for SQLite.NET. Enter a single line sql query and it will be run against the database.\r\n");
Console.WriteLine("Opening database 'test.db'...\r\n");
try {
// Open database
db = new SQLiteClient("test.db");
} catch (SQLiteException e) {
Console.WriteLine("Fatal error: {0}", e.Message);
Console.ReadLine();
return;
}
Console.WriteLine("Available tables:");
ArrayList tables = db.GetColumn("SELECT name FROM sqlite_master WHERE type = 'table'");
foreach (string tableName in tables)
{
Console.WriteLine("\t" + tableName);
}
// Main loop
while (true)
{
Console.Write("SQL> ");
string input = Console.ReadLine();
if (input == null || input.Trim() == "")
{
continue;
}
if (input == ".quit")
{
return;
}
try {
results = db.Execute(input);
ConsoleTable table = new ConsoleTable();
table.SetHeaders(results.ColumnNames);
// Loop through the results and display them
foreach (ArrayList arr in results.Rows)
{
table.AppendRow(arr);
}
while (results.IsMoreData)
{
Hashtable foo = results.GetRowHash();
foo.GetType();
}
Console.WriteLine(table.ToString());
} catch (SQLiteException e) {
Console.WriteLine(e.Message);
}
}
}
public static void Close ()
{
string databaseFile = Settings.GetPath(Settings.Path.database);
try
{
m_db.Close();
m_db.Dispose();
m_db = null;
MPTVSeriesLog.Write("Successfully closed database. Filename = '{0}'", databaseFile);
}
catch (Exception)
{
MPTVSeriesLog.Write("Failed to close database. Filename = '{0}'", databaseFile);
}
}
public static void AddIndex(SQLiteClient dbHandle, string indexName, string strSQL)
{
SQLiteResultSet results;
results =
dbHandle.Execute("SELECT name FROM sqlite_master WHERE name='" + indexName + "' and type='index' " +
"UNION " +
"SELECT name FROM sqlite_temp_master WHERE name ='" + indexName + "' and type='index'");
if (results != null && results.Rows.Count == 1)
{
return;
}
try
{
dbHandle.Execute(strSQL);
}
catch (SQLiteException ex)
{
Log.Error("DatabaseUtility exception err:{0} stack:{1} sql:{2}", ex.Message, ex.StackTrace, strSQL);
}
return;
}
private void Open()
{
Log.Info("MusicDatabase: Opening database");
try
{
// Open database
try
{
Directory.CreateDirectory(Config.GetFolder(Config.Dir.Database));
}
catch (Exception) {}
if (!File.Exists(Config.GetFile(Config.Dir.Database, "MusicDatabaseV13.db3")))
{
if (File.Exists(Config.GetFile(Config.Dir.Database, "MusicDatabaseV11.db3")))
{
Log.Info("MusicDatabase: Found older version of database. Upgrade to new layout.");
File.Copy(Config.GetFile(Config.Dir.Database, "MusicDatabaseV11.db3"),
Config.GetFile(Config.Dir.Database, "MusicDatabaseV13.db3"));
// Get the DB handle or create it if necessary
MusicDbClient = DbConnection;
UpgradeDBV11_V13();
return;
}
if (File.Exists(Config.GetFile(Config.Dir.Database, "MusicDatabaseV12.db3")))
{
// upgrade DB (add last fm user table)
File.Copy(Config.GetFile(Config.Dir.Database, "MusicDatabaseV12.db3"),
Config.GetFile(Config.Dir.Database, "MusicDatabaseV13.db3"));
// Get the DB handle or create it if necessary
MusicDbClient = DbConnection;
if (!CreateDatabase())
{
Log.Error("MusicDatabase: Error creating new database. aborting upgrade}");
}
return;
}
// Get the DB handle or create it if necessary
MusicDbClient = DbConnection;
// When we have deleted the database, we need to scan from the beginning, regardsless of the last import setting
_lastImport = DateTime.ParseExact("1900-01-01 00:00:00", "yyyy-M-d H:m:s", CultureInfo.InvariantCulture);
Log.Info("MusicDatabase: Database does not exist. Create it.");
if (!CreateDatabase())
{
return;
}
}
// Get the DB handle or create it if necessary
MusicDbClient = DbConnection;
}
catch (Exception ex)
{
Log.Error("MusicDatabase: exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
Log.Info("MusicDatabase: Database opened");
}
/// <summary>
/// Helper function to create a new table in the database
/// </summary>
/// <param name="strTable">name of table</param>
/// <param name="strSQL">SQL command to create the new table</param>
/// <returns>true if table is created</returns>
public static bool AddTable(SQLiteClient dbHandle, string strTable, string strSQL)
{
if (TableExists(dbHandle, strTable))
{
return false;
}
try
{
//Log.Info("create table:{0} {1}", strSQL,dbHandle);
dbHandle.Execute(strSQL);
//Log.Info("table created");
}
catch (SQLiteException ex)
{
Log.Error("DatabaseUtility exception err:{0} stack:{1} sql:{2}", ex.Message, ex.StackTrace, strSQL);
}
return true;
}
/// <summary>
/// The main entry point for the application.
/// </summary>
static void Main()
{
SQLiteClient db;
SQLiteResultSet results;
Console.WriteLine("Basic test app for SQLite.NET. Enter a single line sql query and it will be run against the database.\r\n");
Console.WriteLine("Opening database 'test.db'...\r\n");
try {
// Open database
db = new SQLiteClient("test.db");
} catch (SQLiteException e) {
Console.WriteLine("Fatal error: {0}", e.Message);
Console.ReadLine();
return;
}
Console.WriteLine("Available tables:");
ArrayList tables = db.GetColumn("SELECT name FROM sqlite_master WHERE type = 'table'");
foreach (string tableName in tables) {
Console.WriteLine("\t" + tableName);
}
// Main loop
while (true) {
Console.Write("SQL> ");
string input = Console.ReadLine();
if (input == null || input.Trim() == "") {
continue;
}
if (input == ".quit") {
return;
}
try {
results = db.Execute(input);
ConsoleTable table = new ConsoleTable();
table.SetHeaders(results.ColumnNames);
// Loop through the results and display them
foreach (ArrayList arr in results.Rows) {
table.AppendRow(arr);
}
while (results.IsMoreData) {
Hashtable foo = results.GetRowHash();
foo.GetType();
}
Console.WriteLine(table.ToString());
} catch (SQLiteException e) {
Console.WriteLine(e.Message);
}
}
}
public static void CompactDatabase(SQLiteClient m_db)
{
m_db.Execute("PRAGMA count_changes=0");
m_db.Execute("vacuum");
m_db.Execute("PRAGMA count_changes=1");
}