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");
}
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);
}
}
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");
}
/// <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;
}
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;
}
/// <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;
}
public static void CompactDatabase(SQLiteClient m_db)
{
m_db.Execute("vacuum");
}
/// <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 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)");
}
public void InitDatabase()
{
bool dbExists;
try
{
// Open database
dbExists = System.IO.File.Exists(Config.GetFile(Config.Dir.Database, "YouTubeFm_Data_V01.db3"));
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "YouTubeFm_Data_V01.db3"));
DatabaseUtility.SetPragmas(m_db);
if (!dbExists)
{
m_db.Execute(
"CREATE TABLE VIDEOS(ID integer primary key autoincrement,VIDEO_ID text, ARTIST_ID text, TITLE text, IMG_URL text, LENGTH integer,STATE integer, rating integer, hd integer)\n");
m_db.Execute(
"CREATE TABLE PLAY_HISTORY(ID integer primary key autoincrement,VIDEO_ID text, datePlayed timestamp, loved integer)\n");
CreateArtistTable(m_db);
DatabaseUtility.AddIndex(m_db, "idx_video_id", "CREATE INDEX idx_video_id ON VIDEOS(VIDEO_ID)");
DatabaseUtility.AddIndex(m_db, "idx_ARTIST_ID", "CREATE INDEX idx_ARTIST_ID ON VIDEOS(ARTIST_ID)");
DatabaseUtility.AddIndex(m_db, "idx_his_video_id", "CREATE INDEX idx_his_video_id ON PLAY_HISTORY(VIDEO_ID)");
DatabaseUtility.AddIndex(m_db, "idx_his_date", "CREATE INDEX idx_his_date ON PLAY_HISTORY(datePlayed DESC)");
}
else
{
if(!DatabaseUtility.TableColumnExists(m_db,"VIDEOS","hd"))
{
m_db.Execute("ALTER TABLE VIDEOS ADD hd integer");
}
if (!DatabaseUtility.TableExists(m_db, "ARTISTS"))
{
CreateArtistTable(m_db);
}
}
}
catch (SQLiteException ex)
{
Log.Error("database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public void InitDatabase()
{
bool dbExists;
try
{
// Open database
dbExists = System.IO.File.Exists(Config.GetFile(Config.Dir.Database, "YouTubeFm_V01.db3"));
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "YouTubeFm_V01.db3"));
DatabaseUtility.SetPragmas(m_db);
if (!dbExists)
{
m_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)\n");
m_db.Execute("CREATE TABLE TAGS(ID integer primary key autoincrement,ARTIST_ID text, ARTIST_TAG text)\n");
}
}
catch (SQLiteException ex)
{
//Log.Instance.Error("database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
private static void InitDB()
{
if (System.Diagnostics.Process.GetCurrentProcess().ProcessName.ToLower() == "devenv")
return;
string databaseFile = Settings.GetPath(Settings.Path.database);
bool writeToLog = false;
if (System.IO.File.Exists(databaseFile)) writeToLog = true;
try
{
m_db = new SQLiteClient(databaseFile);
m_db.Execute("PRAGMA cache_size=5000;"); // Each page uses about 1.5K of memory
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 temp_store = MEMORY;");
if (writeToLog)
MPTVSeriesLog.Write("Successfully opened database '" + databaseFile + "'");
}
catch (Exception ex)
{
MPTVSeriesLog.Write("Failed to open database '" + databaseFile + "' (" + ex.Message + ")");
}
}
private DBMovieInfo getMovieInfo(string idMovie)
{
DBMovieInfo movieRes = new DBMovieInfo();
try
{
SQLiteClient mp_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
SQLiteResultSet sqlResults = mp_db.Execute("SELECT * FROM movieinfo WHERE idMovie LIKE '" + idMovie + "'");
SQLiteResultSet.Row sqlRow = sqlResults.GetRow(0);
System.Collections.Hashtable columns = sqlResults.ColumnIndices;
movieRes.Popularity = int.Parse(sqlResults.GetField(0, int.Parse(columns["strVotes"].ToString())));
movieRes.Runtime = int.Parse(sqlResults.GetField(0, int.Parse(columns["runtime"].ToString())));
movieRes.Score = float.Parse(sqlResults.GetField(0, int.Parse(columns["fRating"].ToString())));
movieRes.Year = int.Parse(sqlResults.GetField(0, int.Parse(columns["iYear"].ToString())));
string Title = sqlResults.GetField(0, int.Parse(columns["strTitle"].ToString()));
if (!Title.Contains("unknown"))
movieRes.Title = Title;
string Certification = sqlResults.GetField(0, int.Parse(columns["mpaa"].ToString()));
if (!Certification.Contains("unknown"))
movieRes.Certification = Certification;
string Tagline = sqlResults.GetField(0, int.Parse(columns["strTagLine"].ToString()));
if (!Tagline.Contains("unknown"))
movieRes.Tagline = Tagline;
string Summary = sqlResults.GetField(0, int.Parse(columns["strPlotOutline"].ToString()));
if (!Summary.Contains("unknown"))
movieRes.Summary = Summary;
string imdb_id = sqlResults.GetField(0, int.Parse(columns["IMDBID"].ToString()));
if (!imdb_id.Contains("unknown"))
movieRes.ImdbID = imdb_id;
string genreMain = sqlResults.GetField(0, int.Parse(columns["strGenre"].ToString()));
if (!genreMain.Contains("unknown"))
{
string[] genreSplit = genreMain.Split('/');
foreach (string genre in genreSplit)
{
movieRes.Genres.Add(genre.Trim());
}
}
string castMain = sqlResults.GetField(0, int.Parse(columns["strCast"].ToString()));
if (!castMain.Contains("unknown"))
{
string[] castSplit = castMain.Split('\n');
foreach (string cast in castSplit)
{
string castFinal = cast;
if (cast.Contains(" as "))
castFinal = cast.Remove(cast.IndexOf(" as "));
movieRes.Actors.Add(castFinal.Trim());
}
}
string idDirector = sqlResults.GetField(0, int.Parse(columns["idDirector"].ToString()));
if (!castMain.Contains("unknown"))
{
SQLiteResultSet sqlDirector = mp_db.Execute("SELECT strActor FROM actors WHERE idActor LIKE '" + idDirector + "'");
movieRes.Directors.Add(sqlDirector.GetField(0, 0));
}
string writers = sqlResults.GetField(0, int.Parse(columns["strCredits"].ToString()));
if (!writers.Contains("unknown")) {
string[] writerArray = writers.Split(new string[] {"\n", " "}, StringSplitOptions.None);
foreach (string writer in writerArray) {
if (!movieRes.Writers.Contains(writer.Trim()))
movieRes.Writers.Add(writer.Trim());
}
}
movieRes.GetSourceMovieInfo(SourceInfo).Identifier = sqlResults.GetField(0, int.Parse(columns["idMovie"].ToString())).ToString();
mp_db.Close();
}
catch
{
return null;
}
return movieRes;
}
/// <summary>
/// Method to find the file's unique id in the MyVideo's database
/// </summary>
/// <param name="fileName">Filename to look for in the MyVideo's database</param>
/// <returns>unique id as string</returns>
private string getMovieID(string fileName)
{
string idMovie = String.Empty;
fileName = fileName.Replace("'", "''");
try
{
SQLiteClient mp_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, @"VideoDatabaseV5.db3"));
SQLiteResultSet results = mp_db.Execute("SELECT idMovie FROM files WHERE strFilename LIKE '\\" + fileName + "'");
idMovie = results.GetField(0, 0);
mp_db.Close();
}
catch
{
}
return idMovie;
}
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 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;
}
/// <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);
}
}
}
/// <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 database(string filename) {
_sql = new SQLiteClient(filename);
try { //"IF NOT EXIST" DOES NOT EXIST in the SQLite version for C#.
_sql.Execute("CREATE TABLE roms (id INTEGER AUTOINCREMENT PRIMARY KEY, file TEXT, sum TEXT)");
} catch (SQLiteException e) { /* Oh well. */ }
}
