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); } } }
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. */ } }