/// <summary>
/// Creates a new connection to the icontent.cache file.
/// </summary>
/// <param name="filename">The file to connect to.</param>
public IGADatabaseConnector(String filename)
{
try {
sqlite = new SqliteConnection("URI=file:" + filename + ",version=3");
sqlite.Open();
SqliteCommand query = new SqliteCommand("SELECT [appId] FROM [contentlist] LIMIT 1", sqlite);
Object result = query.ExecuteScalar();
sqlite.Close();
if (result == null)
{
this._appID = 0;
}
else
{
this._appID = (int)result;
}
if (this._appID > 0) {
this._appSupported = Common.AppInfos.ContainsKey(this._appID);
} else {
this._appSupported = false;
}
if (this._appSupported)
{
_appInfo = Common.AppInfos[this._appID];
}
} catch (Exception) {
throw new DatabaseConnectionFailureException();
}
}
public new object ExecuteScalar(string sql, string connectionString)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = 1200;
conn.Open();
object retVal = cmd.ExecuteScalar();
conn.Close();
return retVal;
}
}
static void Test(bool v3, string encoding) {
if (!v3)
Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
else
Console.WriteLine("Testing Version 3");
System.IO.File.Delete("SqliteTest.db");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
if (v3)
connectionString += ",Version=3";
if (encoding != null)
connectionString += ",encoding=" + encoding;
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );
dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );
dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );
Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read())
for (int i = 0; i < reader.FieldCount; i++)
Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
Console.WriteLine("read and display data using DataAdapter/DataSet...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(" " + myRow[myColumn]);
}
}
}
/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
DataTable dt = new DataTable();
adapter.Fill(dt);
DataView dv = new DataView(dt);
foreach (DataRowView myRow in dv) {
foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
Console.WriteLine(" " + myRow[myColumn.ColumnName]);
}
}*/
try {
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Console.WriteLine("Should not reach here.");
} catch (Exception e) {
Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
/*try {
dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
} catch (Exception e) {
Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
}*/
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
/// <summary>
/// Creates a new entry in the cache file.
/// </summary>
/// <param name="entry">The entry to create.</param>
/// <returns>The new entry's contentId.</returns>
public uint NewEntry(ContentEntry entry)
{
uint active = 1;
if (!entry.Active)
{
active = 0;
}
uint activate = (uint)((TimeSpan)entry.Activate.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
uint expiry = 0;
if (entry.Expires)
{
expiry = (uint)((TimeSpan)entry.Expiry.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds;
}
String p = entry.GetPropsAsString();
SqliteCommand query = new SqliteCommand(@"INSERT INTO [content] ([active], [activate], [expire], [dayparts], [contentType], [descriptor], [size], [viewcount], [viewlimit], [displayafter], [props], [data]) VALUES (@active, @activate, @expire, @dayparts, @contentType, @descriptor, @size, @viewcount, @viewlimit, @displayafter, @props, @data); SELECT last_insert_rowid() AS contentId;", sqlite);
query.Parameters.Add(new SqliteParameter("@active", (int)active));
query.Parameters.Add(new SqliteParameter("@activate", (int)activate));
query.Parameters.Add(new SqliteParameter("@expire", (int)expiry));
query.Parameters.Add(new SqliteParameter("@dayparts", (int)entry.DayParts));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@descriptor", entry.Descriptor));
query.Parameters.Add(new SqliteParameter("@size", (int)entry.Size));
query.Parameters.Add(new SqliteParameter("@viewcount", (int)entry.ViewCount));
query.Parameters.Add(new SqliteParameter("@viewlimit", (int)entry.ViewLimit));
query.Parameters.Add(new SqliteParameter("@displayafter", (int)entry.DisplayAfter));
query.Parameters.Add(new SqliteParameter("@props", p));
query.Parameters.Add(new SqliteParameter("@data", entry.Data));
// get the contentId back
sqlite.Open();
Object cidR = query.ExecuteScalar();
uint cid = (uint)((long)cidR);
// shove cid into props
p = "contentId=" + cid.ToString() + "&" + p;
// reset connection
sqlite.Close();
sqlite.Open();
// shove in updated props
query = new SqliteCommand(@"UPDATE [content] SET [props]=@props WHERE [contentId]=@cid", sqlite);
query.Parameters.Add(new SqliteParameter("@props", p));
query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
// reset connection
sqlite.Close();
sqlite.Open();
// shove in corresponding contentlist entry for record
query = new SqliteCommand(@"INSERT INTO [contentlist] ([appId], [contentType], [contentId], [userInfo], [zoneId]) VALUES (@appid, @contentType, @cid, @userinfo, @zoneid)", sqlite);
query.Parameters.Add(new SqliteParameter("@appid", this._appID));
query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType));
query.Parameters.Add(new SqliteParameter("@cid", (int)cid));
query.Parameters.Add(new SqliteParameter("@zoneid", entry.Properties["zoneId"]));
query.Parameters.Add(new SqliteParameter("@userinfo", entry.Properties["userInfo"]));
if (query.ExecuteNonQuery() == 0)
{
sqlite.Close();
throw new DatabaseUpdateFailureException();
}
sqlite.Close();
return cid;
}
/// <summary>
/// Exports a single image from the database.
/// </summary>
/// <param name="contentId">The contentId to export.</param>
/// <returns>A byte[] containing the raw image or video data, or null if there was an error or there is no data.</returns>
public byte[] ExportImage(uint contentId)
{
SqliteCommand cmd = new SqliteCommand(@"SELECT [data] FROM [content] WHERE [contentId] = @cid LIMIT 1", sqlite);
cmd.Parameters.Add(new SqliteParameter("@cid", (int)contentId));
sqlite.Open();
byte[] idata = null;
try
{
idata = (byte[])cmd.ExecuteScalar();
}
catch (Exception) { } // pass
sqlite.Close();
return idata;
}
public void ScalarReturn()
{
// This should return the 1 line that got inserted in CreateTable() Test
SqliteCommand cmd = new SqliteCommand("SELECT COUNT(*) FROM t1 WHERE t LIKE '%äöüß'",_conn);
using(_conn)
{
_conn.Open();
Assert.AreEqual(1, Convert.ToInt32(cmd.ExecuteScalar()));
}
}
private object ExecuteScalar(string sql, IDbConnection conn, IDbTransaction tran)
{
sql = sql + " --dataProfilerIgnore";
SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection)conn, tran);
cmd.CommandTimeout = CommandTimeout;
return cmd.ExecuteScalar();
}
private object ExecuteScalar(string sql)
{
using (SqliteConnection conn = new SqliteConnection(connectionString))
{
object retVal = null;
try
{
sql = sql + " --dataProfilerIgnore";
SqliteCommand cmd = new SqliteCommand(sql, conn);
cmd.CommandTimeout = CommandTimeout;
conn.Open();
retVal = cmd.ExecuteScalar();
}
finally
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
}
return retVal;
}
}