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 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 int CreateTableIfNotExists(CreateTableTemplate createTableTemplate)
{
lock (_dbLocker)
{
string tableName = createTableTemplate.TableName;
int version = createTableTemplate.Version;
string createSql = createTableTemplate.CreateSql;
int dbTableVersion = GetTableVersion(tableName);
if (version > dbTableVersion)
{
int effectRows = 0;
SqlTemplate createSqlTemplate = new SqlTemplate();
createSqlTemplate.SqlExpression = createTableTemplate.CreateSql;
effectRows += DropTableIfExists(tableName);
effectRows += ExecuteNonQuery(createSqlTemplate);
effectRows += UpdateTableVersion(tableName, version);
return(effectRows);
}
else
{
return(0);
}
}
}
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);
}
}
