/// <summary>
/// attempts to validate if table schema is correct, will attempt to add missing table fields,
/// will throw exception on any sql error, not all CREATE TABLE column constraints are allowed (ref:sqlite doc)
/// </summary>
/// <param name="expectedFieldSchema"></param>
/// <param name="tableName"></param>
/// <param name="connectionStr"></param>
public static void ValidateTable(SQLiteFieldDef[] expectedFieldSchema, string tableName, string connectionStr)
{
string[] existingFieldNames = GetTableSchemaColNames(tableName, connectionStr);
string[] expectedFieldNames = expectedFieldSchema.Select(x => x.fieldName).ToArray();
List<SQLiteFieldDef> missingFields = new List<SQLiteFieldDef>();
foreach (var field in expectedFieldNames)
{
if (!existingFieldNames.Contains(field))
{
missingFields.Add(expectedFieldSchema.Where(x => x.fieldName == field).Single());
Logger.LogInfo("sqlite table: "+tableName+" validation, missing field: " + field);
}
}
foreach (var field in existingFieldNames)
{
if (!expectedFieldNames.Contains(field))
{
Logger.LogInfo("sqlite table: " + tableName + " contains unexpected field: " + field);
}
}
try
{
if (missingFields.Count > 0)
{
AddFieldsToTableSchema(missingFields.ToArray(), tableName, connectionStr);
}
}
catch (Exception _e)
{
Logger.LogError("problem updating table: " + tableName + " schema", THIS, _e);
throw;
}
}
/// <summary>
/// attempts to create a new table if not exists, will throw exception on any sql errors
/// </summary>
/// <param name="fieldDefinitions"></param>
/// <param name="tableName"></param>
/// <param name="connectionStr"></param>
public static void CreateTableIfNotExists(SQLiteFieldDef[] fieldDefinitions, string tableName, string connectionStr)
{
string fields = "";
string strIfNotExist = " IF NOT EXISTS ";
foreach (SQLiteFieldDef field in fieldDefinitions)
{
fields += String.Format(" {0},", field.fieldDef);
}
fields = fields.Substring(0, fields.Length - 1);
string command = String.Format("CREATE TABLE {0} {1} ({2});", strIfNotExist, tableName, fields);
ExecuteSQL(command, connectionStr);
}
/// <summary>
/// attempts to add missing fields to table schema, throws exception on any sql errors
/// </summary>
/// <param name="fields"></param>
/// <param name="tableName"></param>
/// <param name="connectionStr"></param>
public static void AddFieldsToTableSchema(SQLiteFieldDef[] fields, string tableName, string connectionStr)
{
using (SQLiteConnection con = new SQLiteConnection(connectionStr))
{
con.Open();
SQLiteTransaction trans = con.BeginTransaction();
try
{
foreach (var field in fields)
{
Logger.LogInfo("table: " + tableName + " adding field: "+field.fieldDef);
string command = "ALTER TABLE " + tableName + " ADD COLUMN " + field.fieldDef;
SQLiteCommand com = new SQLiteCommand(command);
com.Connection = con;
com.Transaction = trans;
com.ExecuteNonQuery();
}
trans.Commit();
Logger.LogInfo("table: " + tableName + " schema updated");
}
catch (Exception _e)
{
Logger.LogError("AddFieldsToTableSchema error", THIS, _e);
try
{
trans.Rollback();
}
catch (Exception _ee)
{
Logger.LogError("AddFieldsToTableSchema rollback error", THIS, _ee);
}
throw;
}
}
}
