public SQLiteTester()
{
//SQLiteClient.CreateDB(TestFilename);
dbClient = new SQLiteClient();
dbClient.Open(TestFilename);
if (!dbClient.ExistTable("Member"))
{
GDebug.Log(dbClient.CreateTable("Member",
new SQLiteFieldAffinity(TypeAffinity.Text, "ID"),
new SQLiteFieldAffinity(TypeAffinity.Text, "PW")));
}
var resultDataSet = dbClient.ExecuteDataset("SELECT * FROM Member WHERE PW LIKE '%345%'");
GDebug.Log("Result " + resultDataSet.Tables[0].Rows[1][0]);
object result = dbClient.ExecuteScalar("SELECT PW FROM Member WHERE ID='isg1153'");
if (result != null)
{
string foundPw = result as string;
GDebug.Log(foundPw);
}
else
{
GDebug.Log(dbClient.InsertRow("Member",
new SQLiteField("ID", DbType.String, "isg1153"),
new SQLiteField("PW", DbType.String, "123456")));
}
dbClient.Close();
}
public override async Task OnStartAsync(StartKind startKind, IActivatedEventArgs args)
{
// TODO: add your long-running task here
SQLiteClient.Init();
await ImageCache.Instance.InitializeAsync(ApplicationData.Current.LocalCacheFolder, "ImageCache");
ImageCache.Instance.MaxMemoryCacheCount = Int32.MaxValue;
#if DEBUG
var taskConfig = new InstantUploadRegistration();
await taskConfig.EnableAsync();
#endif
if (startKind == StartKind.Launch)
{
if (Configuration.IsFirstRun)
{
Configuration.RemoveCredentials();
Configuration.IsBackgroundTaskEnabled = false;
NavigationService.Navigate(typeof(WelcomePage));
}
else
{
NavigationService.Navigate(typeof(FilesPage));
}
}
}
/// <summary>
/// Initiation of the DatabaseManager.
/// </summary>
/// <param name="dbFilename">Database filename</param>
/// <returns>if database was successfully or not</returns>
private bool InitDB()
{
string dbFilename = "PictureDatabase.db3";
try
{
String filename = Config.GetFolder(Config.Dir.Database) + @"\" + dbFilename;
if (File.Exists(filename))
{
if (new FileInfo(filename).Length > 0)
{
PicturesDB = new SQLiteClient(filename);
DatabaseUtility.SetPragmas(PicturesDB);
return(true);
}
}
}
catch //(Exception e)
{
//logger.Error("initDB: Could Not Open Database: " + dbFilename + ". " + e.ToString());
}
PicturesDB = null;
return(false);
}
public WatchList()
{
_watchItemList = new List <WatchItem>();
//_seriesList = new List<SeriesItem>();
_sqlClient = new SQLiteClient(Config.GetFile(Config.Dir.Database, "MyTorrents.db3"));
DatabaseUtility.SetPragmas(_sqlClient);
if (!DatabaseUtility.TableExists(_sqlClient, "WatchList"))
{
SQLiteResultSet result = _sqlClient.Execute("CREATE TABLE WatchList (Search CHAR(256), Added BIGINT, Label CHAR(256) );");
}
if (!DatabaseUtility.TableExists(_sqlClient, "SeriesWatchList"))
{
SQLiteResultSet result = _sqlClient.Execute("CREATE TABLE SeriesWatchList (SeriesName CHAR(256), OriginalSeriesName CHAR(256), SeriesID CHAR(256), Added BIGINT, Quality CHAR(256), Tracker CHAR(256), Type CHAR(256), Source CHAR(256), Folder CHAR(256), IncludeSpecials CHAR (256));");
}
RSSChannelManager channelManager = RSSChannelManager.Instance();
channelManager.RSSChannelUpdated += new RSSChannelUpdated(channelManager_RSSChannelUpdated);
System.Timers.Timer SearchTimer = new System.Timers.Timer(30 * 60 * 1000);
SearchTimer.Elapsed += new ElapsedEventHandler(SearchTimer_Elapsed);
SearchTimer.Start();
if (IsMPTVSeriesAvailable())
{
MPTVSeriesWorker.DoWork += new DoWorkEventHandler(MPTVSeriesWorker_DoWork);
MPTVSeriesWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(MPTVSeriesWorker_Completed);
MPTVSeriesWorker.RunWorkerAsync();
}
}
public void Open()
{
var isNew = !File.Exists(dbPath);
var args = new Dictionary <string, string>()
{
{ "foreign keys", "true" }
};
this.client = new SQLiteClient(dbPath, !isNew, args);
var connection = client.GetConnection();
if (isNew)
{
if (usePassword)
{
connection.ChangePassword(dbPwd);
}
CreateTables();
}
else
{
if (usePassword)
{
connection.Close();
connection.SetPassword(dbPwd);
connection.Open();
}
}
client.ExecuteNonQuery("select * from " + TablePasswords + " limit 1");
}
/// <summary>
/// Check if a view exists
/// </summary>
/// <param name="table">name of view</param>
/// <returns>true: view exists
/// false: view does not exist</returns>
public static bool ViewExists(SQLiteClient m_db, string view)
{
SQLiteResultSet results;
if (m_db == null)
{
return(false);
}
if (view == null)
{
return(false);
}
if (view.Length == 0)
{
return(false);
}
results = m_db.Execute("SELECT name FROM sqlite_master WHERE name like '" + view + "' and type like 'view'");
// UNION ALL SELECT name FROM sqlite_temp_master WHERE type='view' 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] == view)
{
return(true);
}
}
}
}
return(false);
}
private bool verifyIntegrity(SQLiteClient client)
{
string query = "PRAGMA integrity_check;";
logger.Info("Executing SQL integrity check");
try {
SQLiteResultSet results = client.Execute(query);
if (results != null)
{
if (results.Rows.Count == 1)
{
SQLiteResultSet.Row arr = results.Rows[0];
if (arr.fields.Count == 1)
{
if (arr.fields[0] == "ok")
{
logger.Info("Database integrity check succeeded");
return(true);
}
}
}
}
}
catch (Exception e) {
logger.Info("Integrity check failed, database is corrupt. Reason = '{0}', Filename = '{1}'", e.Message, client.DatabaseName);
return(false);
}
logger.Info("Integrity check failed, database is corrupt. Filename = '{0}'", client.DatabaseName);
return(false);
}
private void SaveStudent(object sender, EventArgs e)
{
Student _student = new Student();
_student.Name = txtName.Text;
_student.Surname = txtSurname.Text;
_student.About = txtAbout.Text;
_student.BirthDate = pickerBirthDate.Date;
SQLiteClient _client = new SQLiteClient();
var result = _client.Insert(_student);
if (result > 0)
{
DisplayAlert(_student.Name, "Eklendi", "Ok");
}
else
{
DisplayAlert("Hata Oluştu", "Öğrenci Eklenemedi!", "Ok");
}
var startPage = (Start)this.Parent;
startPage.Detail = new List();
startPage.IsPresented = false;
}
public FolderSettingsSqlLite()
{
try
{
// Open database
Log.Info("Open FolderDatabase");
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "FolderDatabase3.db3"));
_dbHealth = DatabaseUtility.IntegrityCheck(m_db);
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)");
// Indexes for tblPath
DatabaseUtility.AddIndex(m_db, "idx_tblPath_strPath", "CREATE INDEX idx_tblPath_strPath ON tblPath (strPath ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblPath_idPath_strPath", "CREATE INDEX idx_tblPath_idPath_strPath ON tblPath (idPath ASC, strPath ASC)");
// Indexes for tblSetting
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_idPath", "CREATE INDEX idx_tblSetting_idPath ON tblSetting (idPath ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_tagName", "CREATE INDEX idx_tblSetting_tagName ON tblSetting (tagName ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_idPath_tagName", "CREATE INDEX idx_tblSetting_idPath_tagName ON tblSetting (idPath ASC, tagName ASC)");
// Cleanup DB
Log.Debug("Cleanup FolderDatabase");
string strSQL = String.Format("delete from tblPath where idPath not in (select idPath from tblSetting)");
m_db.Execute(strSQL);
}
catch (Exception ex)
{
Log.Error(ex);
}
}
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);
}
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. */ }
}
public static SQLiteClient Create(SQLiteAsyncConnection connection)
{
var client = new SQLiteClient(connection);
client.InitializeAsync();
return(client);
}
/// <summary>
/// 删除对应事件的记录表格
/// </summary>
/// <param name="incidentId">事件id</param>
public static void DeleteTable(uint incidentId)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = string.Concat("DROP TABLE IF EXISTS [record_", incidentId, "];");
SQLiteClient.Write(cmd);
}
}
/// <summary>
/// 清空索引表数据
/// </summary>
public static void CleanAll()
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "DELETE FROM [dir_index] WHERE TRUE;";
SQLiteClient.Write(cmd);
}
}
/// <summary>
/// 获取所有的记录成功的事件
/// </summary>
public static IncidentBean[] GetSuccessIncident()
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [incident] WHERE [state] = 0;";
return(GetBeanByTable(SQLiteClient.Read(cmd)));
}
}
protected override void OnStartup(StartupEventArgs e)
{
base.OnStartup(e);
var client = new SQLiteClient();
client.TestSeed();
client.TestAdd();
}
/// <summary>
/// 获取所有根标签
/// </summary>
public static TagBean[] GetRootTag()
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [tag] WHERE [parent_id] = 0;";
return(GetBeanListByTable(SQLiteClient.Read(cmd)));
}
}
//建立索引
internal static void BuildIndex(uint incidentId)
{
string path = Base.locolPath + @"\Data\index.sql";
string sql = TextFile.ReadAll(path, Encoding.UTF8);
sql = sql.Replace("{incidentId}", incidentId.ToString());
SQLiteClient.ExecuteSql(sql);
}
private static SQLiteClient GetConnection()
{
if (_client == null)
{
_client = new SQLiteClient();
}
return(_client);
}
/// <summary>
/// 获取所有的数据
/// </summary>
/// <returns></returns>
public static DirTagBean[] GetAll()
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [dir_tag];";
return(GetBeanByTable(SQLiteClient.Read(cmd)));
}
}
public static void SetPragmas(SQLiteClient m_db)
{
m_db.Execute("PRAGMA encoding = \"UTF-8\"");
m_db.Execute("PRAGMA cache_size=-5000"); // abs(-5000*1024) Bytes of memory
m_db.Execute("PRAGMA page_size=4096"); // Windows fastest value 4096
m_db.Execute("PRAGMA synchronous='OFF'");
m_db.Execute("PRAGMA auto_vacuum=0");
}
/// <summary>
/// 获取表中的记录总数
/// </summary>
/// <param name="incidentId">对应的事件id</param>
/// <returns>记录总数</returns>
public static long Count(uint incidentId)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT COUNT(1) FROM [record_" + incidentId + "];";
DataTable table = SQLiteClient.Read(cmd);
return((long)table.Rows[0][0]);
}
}
/// <summary>
/// 获取所有子标签
/// </summary>
/// <param name="ParentId">父级标签id</param>
public static TagBean[] GetChildTag(uint ParentId)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [tag] WHERE [parent_id] = @parent_id;";
cmd.Parameters.Add("parent_id", DbType.UInt32).Value = ParentId;
return(GetBeanListByTable(SQLiteClient.Read(cmd)));
}
}
/// <summary>
/// 通过标签id获取数据实体
/// </summary>
public static TagBean GetOneById(uint tagId)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [tag] WHERE [id] = @id;";
cmd.Parameters.Add("id", DbType.UInt32).Value = tagId;
return(GetBeanListByTable(SQLiteClient.Read(cmd))[0]);
}
}
/// <summary>
/// 删除一行数据
/// </summary>
/// <param name="id">对应的数据path</param>
public static void DeleteOneByPath(string path)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "DELETE FROM [dir_tag] WHERE [path]=@path;";
cmd.Parameters.Add("path", DbType.String).Value = path;
SQLiteClient.Write(cmd);
}
}
/// <summary>
/// 删除一行数据
/// </summary>
/// <param name="id">对应的数据id</param>
public static void DeleteOneById(uint id)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "DELETE FROM [dir_tag] WHERE [id]=@id;";
cmd.Parameters.Add("id", DbType.UInt32).Value = id;
SQLiteClient.Write(cmd);
}
}
/// <summary>
/// 获取对应标签id的标注路径
/// </summary>
public static DirTagBean[] GetAllByTag(uint tagId)
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.CommandText = "SELECT * FROM [dir_tag] WHERE [tag_id] = @tag_id;";
cmd.Parameters.Add("tag_id", DbType.UInt32).Value = tagId;
return(GetBeanByTable(SQLiteClient.Read(cmd)));
}
}
public void Dispose()
{
if (m_db != null)
{
m_db.Close();
m_db.Dispose();
m_db = null;
}
}
public void Run(IBackgroundTaskInstance taskInstance)
{
if (Configuration.IsBackgroundTaskEnabled)
{
var reg = new BackgroundTaskConfiguration();
reg.Enabled = true;
}
SQLiteClient.Init();
}
public void Dispose()
{
if (sqlClient != null)
{
sqlClient.Close();
sqlClient.Dispose();
sqlClient = null;
}
}
