public SqliteDatabaseManagerUnitTest() { if (File.Exists(dbFile)) { File.Delete(dbFile); } _password = Encoding.UTF8.GetBytes("test"); _manager = new SqliteDatabaseManager("test.db", _password); }
private bool IsTableExists(SqliteDatabaseManager manager, string tableName) { string sql = "SELECT count(0) FROM sqlite_master WHERE type='table' AND name='" + tableName + "'"; SqlTemplate sqlTemplate = new SqlTemplate(); sqlTemplate.SqlExpression = sql; var result = manager.ExecuteScalar(sqlTemplate); return("1".Equals(result.ToString())); }
public void TestQueryData() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); List <SqlTemplate> sqlTemplates = new List <SqlTemplate>(); for (int i = 0; i < 1000; i++) { SqlTemplate sqlTemplate = new SqlTemplate(); string sql = string.Format("INSERT INTO student values(?, ?)"); sqlTemplate.SqlExpression = sql; sqlTemplate.Params = new object[] { "student" + i, 20 }; sqlTemplates.Add(sqlTemplate); } int result = manager.ExecuteDML(sqlTemplates); Assert.AreEqual(1000, result); SqlTemplate querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student LIMIT 200"; var items = manager.QueryData(querySqlTemplate); Assert.AreEqual(200, items.Count()); } finally { DeleteDatabase(dbFilepath); } }
public void TestCreateTableVersionIfNotExists() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); PrivateObject obj = new PrivateObject(manager); var r1 = obj.Invoke("CreateDatabaseIfNotExists", Path.GetFullPath(dbFilepath)); var r2 = obj.Invoke("CreateTableVersionIfNotExists"); Assert.AreEqual(true, IsTableExists(manager, "table_version")); } finally { DeleteDatabase(dbFilepath); } }
public void TestUpdateTableVersion() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); PrivateObject obj = new PrivateObject(manager); obj.Invoke("UpdateTableVersion", "test", 99); var version = manager.GetTableVersion("test"); Assert.AreEqual(99, version); } finally { DeleteDatabase(dbFilepath); } }
public void TestCreateTableIfNotExists() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER," + "classroom varchar(100))"; createTableTemplate.Version = 2; obj.Invoke("CreateTableIfNotExists", createTableTemplate); version = manager.GetTableVersion("student"); Assert.AreEqual(2, version); } finally { DeleteDatabase(dbFilepath); } }
public void TestInsertOrReplaceDatas() { string dbFilepath = "my.db"; DeleteDatabase(dbFilepath); try { var manager = new SqliteDatabaseManager(dbFilepath, _password); manager.Initialize(); CreateTableTemplate createTableTemplate = new CreateTableTemplate(); createTableTemplate.TableName = "student"; createTableTemplate.Version = 1; createTableTemplate.CreateSql = string.Format("CREATE TABLE IF NOT EXISTS {0}(", "student") + "name varchar(100) PRIMARY KEY," + "age INTEGER)"; PrivateObject obj = new PrivateObject(manager); obj.Invoke("CreateTableIfNotExists", createTableTemplate); var version = manager.GetTableVersion("student"); Assert.AreEqual(1, version); string json = @"[ { ""name"": ""marray"", ""age"": 20 }, { ""name"": ""Jack"", ""age"": 21 } ]"; TableInfo tableInfo = new TableInfo(); tableInfo.TableName = "student"; tableInfo.ColumnNames = new string[] { "name", "age" }; IEnumerable <Dictionary <string, object> > datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json); var effectRows = manager.InsertOrReplaceDatas(tableInfo, datas); Console.WriteLine("effectRows: " + effectRows); SqlTemplate querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student"; var items = manager.QueryData(querySqlTemplate); Assert.AreEqual(2, items.Count()); json = @"[ { ""name"": ""Jack"", ""age"": 30 } ]"; datas = JsonConvert.DeserializeObject <IEnumerable <Dictionary <string, object> > >(json); effectRows = manager.InsertOrReplaceDatas(tableInfo, datas); Console.WriteLine("effectRows: " + effectRows); querySqlTemplate = new SqlTemplate(); querySqlTemplate.SqlExpression = "SELECT * FROM student"; items = manager.QueryData(querySqlTemplate); Assert.AreEqual(30.ToString(), items.Last()["age"].ToString()); } finally { DeleteDatabase(dbFilepath); } }