Skip to content

Instantly share code, notes, and snippets.

@growse
Created October 23, 2011 00:13
Show Gist options
  • Save growse/1306667 to your computer and use it in GitHub Desktop.
Save growse/1306667 to your computer and use it in GitHub Desktop.
SQLite schema updater
/*
I needed a way to look at an existing SQLite schema, compare it to a text file containing some table
definitions and then work out if the two were the same. Here's how I did it.
The hashtable 'givenschema' contains each table create statement keyed by the name of the table.
*/
public static void CheckSchema()
{
lock(myconnection)
{
Hashtable p = new Hashtable();
foreach (string key in givenschema.Keys)
{
p.Clear();
p.Add("@tblname",key);
string sql = GetSingle("select sql from sqlite_master where tbl_name=@tblname",p).ToString();
string newsql = givenschema[key].ToString().Replace("%NAME%",key);
newsql = newsql.TrimEnd(";".ToCharArray());
if (sql==null)
{
ExecuteNonQuery(newsql,null);
}
else if (newsql!=sql)
{
ArrayList currenttable = new ArrayList();
ArrayList newtable = new ArrayList();
ArrayList commonitems = new ArrayList();
string[] columns = sql.Remove(0,sql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray());
foreach (string column in columns)
{
string trimmedcolumn = column.Trim();
currenttable.Add(trimmedcolumn);
}
columns = newsql.Remove(0,newsql.IndexOf("(")+1).TrimEnd(")".ToCharArray()).Split(",".ToCharArray());
foreach (string column in columns)
{
string trimmedcolumn = column.Trim();
newtable.Add(trimmedcolumn);
}
foreach (string item in currenttable)
{
if (newtable.Contains(item))
{
commonitems.Add(item);
}
}
try
{
myconnection.Open();
SQLiteTransaction t = myconnection.BeginTransaction();
SQLiteCommand mycmd = myconnection.CreateCommand();
mycmd.CommandText = newsql.Replace(key,key+"_temp").Replace("CREATE TABLE","CREATE TEMPORARY TABLE");
mycmd.ExecuteNonQuery();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
foreach (string item in newtable)
{
if (commonitems.Contains(item))
{
sb.Append(item+",");
}
else
{
sb.Append("null,");
}
}
mycmd.CommandText="INSERT INTO "+key+"_temp SELECT "+sb.ToString().TrimEnd(",".ToCharArray())+" FROM "+key;
mycmd.ExecuteNonQuery();
mycmd.CommandText="DROP TABLE "+key;
mycmd.ExecuteNonQuery();
mycmd.CommandText=newsql;
mycmd.ExecuteNonQuery();
mycmd.CommandText="INSERT INTO "+key+" SELECT * FROM "+key+"_temp";
mycmd.ExecuteNonQuery();
mycmd.CommandText="DROP TABLE "+key+"_temp";
mycmd.ExecuteNonQuery();
t.Commit();
myconnection.Close();
mycmd.Dispose();
t.Dispose();
}
catch (Exception)
{
throw;
}
finally
{
myconnection.Close();
}
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment