public void CreatingAndRemovingTableInDB()
{
DBInstance dbInstance = new DBInstance("sensor_readings");
dbInstance.InitialiseSQLiteConnection();
string newTableName = "test";
dbInstance.ExecuteNonQuery(
SensorReadingTableDetails.CreateTableSQLString(newTableName));
bool doesTableExistAfterCreation = dbInstance.DoesTableExistInDB(newTableName);
Debug.Log("Table '" + newTableName + "' should now exist: " + doesTableExistAfterCreation);
dbInstance.ExecuteNonQuery(
SensorReadingTableDetails.DropTableSQLString(newTableName));
bool doesTableExistAfterRemoved = dbInstance.DoesTableExistInDB(newTableName);
Debug.Log("Table '" + newTableName + "' should now not exist: " + doesTableExistAfterRemoved);
dbInstance.CloseSQLiteConnection();
if ((doesTableExistAfterCreation) && (!doesTableExistAfterRemoved))
{
Assert.Pass();
}
else
{
Assert.Fail();
}
}
public static int update(DBInstance dbmgr, List <Param> request, int id = 0)
{
//id=0为插入新字段
using (DbConnection conn = dbmgr.GetDbConnection())
{
conn.Open();
using (DbTransaction tx = conn.BeginTransaction())
{
string sql = "";
if (id == 0)
{
id = dbmgr.getNewId("tb_notify_log");
sql = "insert into tb_notify_log (id,created_user,created_date,updated_user,updated_date,`notify_time`,`log_request`,`log_url` ) values (" + id.ToString() + ",-2,now(),-2,now(),@notify_time ,@log_request ,@log_url )";
}
else
{
sql = "update tb_notify_log set updated_user=-2, updated_date=now() ,`notify_time`=@notify_time ,`log_request`=@log_request ,`log_url`=@log_url where id=" + id.ToString();
}
dbmgr.ExecuteNonQuery(tx, sql, request);
tx.Commit();
return(id);
}
}
}
public static int update(DBInstance dbmgr, List <Param> request, int id = 0)
{
//id=0为插入新字段
using (DbConnection conn = dbmgr.GetDbConnection())
{
conn.Open();
using (DbTransaction tx = conn.BeginTransaction())
{
string sql = "";
if (id == 0)
{
id = dbmgr.getNewId("tb_payment");
sql = "insert into tb_payment (id,created_user,created_date,updated_user,updated_date,`orderno`,`time_end`,`openid`,`total_fee`,`trade_type`,`transaction_id`,`result_code` ) values (" + id.ToString() + ",-2,now(),-2,now(),@orderno ,@time_end ,@openid ,@total_fee ,@trade_type ,@transaction_id ,@result_code )";
}
else
{
sql = "update tb_payment set updated_user=-2, updated_date=now() ,`orderno`=@orderno ,`time_end`=@time_end ,`openid`=@openid ,`total_fee`=@total_fee ,`trade_type`=@trade_type ,`transaction_id`=@transaction_id ,`result_code`=@result_code where id=" + id.ToString();
}
dbmgr.ExecuteNonQuery(tx, sql, request);
tx.Commit();
return(id);
}
}
}
public void InsertingSensorReading()
{
DBInstance dbInstance = new DBInstance("sensor_readings");
dbInstance.InitialiseSQLiteConnection();
string newTableName = "testTable";
dbInstance.ExecuteNonQuery(SensorReadingTableDetails.CreateTableSQLString(newTableName));
int numberOfRowsBeforeAddition = dbInstance.GetNumberOfRowsInTable(newTableName);
Debug.Log("Number of rows in table before addition: " + numberOfRowsBeforeAddition);
BaseSensorReading newSensorReading = new BaseSensorReading
{
sensorName = " ",
sensorType = Sensor.Type.Interaction,
areaName = " "
};
newSensorReading.SetDateTime(1, 1, 1, 1, 1, 1);
string insertSensorReadingString = SensorReadingTableDetails.InsertSensorReadingSQLString(newSensorReading, "testBookmark", 0, newTableName);
Debug.Log(insertSensorReadingString);
dbInstance.ExecuteNonQuery(insertSensorReadingString);
int numberOfRowsAfterAddition = dbInstance.GetNumberOfRowsInTable(newTableName);
Debug.Log("Number of rows in table after addition: " + numberOfRowsAfterAddition);
dbInstance.ExecuteNonQuery(SensorReadingTableDetails.DropTableSQLString(newTableName));
dbInstance.CloseSQLiteConnection();
if (numberOfRowsBeforeAddition == numberOfRowsAfterAddition - 1)
{
Assert.Pass();
}
else
{
Assert.Fail();
}
}
/// <summary>
/// Utility method which executes a SQL string to create a table in the database
/// for sensor readings.
/// </summary>
/// <param name="tableName">Name of table to be created in the database.</param>
public static void CreateTableInDatabaseForSensorReadings(string tableName)
{
dbInstance.ExecuteNonQuery(SensorReadingTableDetails.CreateTableSQLString(tableName));
}
