void store_CreateSchema(object sender, SQLiteConnection cnn, int actualVersion)
{
// cnn connection running in transaction
SQLiteStore store = (SQLiteStore)sender;
if (actualVersion <= 2)
{
// expand new schema
if (actualVersion == 2)
{
store.DropTable(cnn, "Attributes");
store.DropTable(cnn, "Files");
}
TableDef tableDef = new TableDef
{
name = "Attributes",
displayName = "File Attributes",
columns = new ColumnDef[] {
new ColumnDef {name = "Name", displayName = "Name", type = "NVARCHAR"},
new ColumnDef {name = "Value", displayName = "Value", type = "NVARCHAR"},
new ColumnDef {name = "FileId", displayName = "FileId", type = "TABLE", parentTable = "Files"}
}
};
store.AddTable(cnn, tableDef);
tableDef = new TableDef
{
name = "Files",
displayName = "My Files",
columns = new ColumnDef[] {
new ColumnDef {name = "FileName", displayName = "File name", type = "NVARCHAR"},
new ColumnDef {name = "FilePath", displayName = "Path", type = "NVARCHAR"},
new ColumnDef {name = "FileSize", displayName = "Size", type = "INT"}
}
};
store.AddTable(cnn, tableDef);
}
else if (actualVersion == 3)
{
ColumnDef[] newCols = new ColumnDef[] {
new ColumnDef{ name = "state", displayName = "State", type = "TEXT", defValue = "'n/a'"},
new ColumnDef{ name = "image_size", displayName = "Image Size", type = "TEXT", defValue = "''"},
new ColumnDef{ name = "pixel_format", displayName = "Pixel Format", type = "TEXT", defValue = "''"}
};
store.AddColumns(cnn, "Files", newCols);
}
}
private void LoadTableDefs(SQLiteConnection cnn)
{
if (_tableDefs == null)
{
_tableDefs = new Dictionary <string, TableDef>(StringComparer.CurrentCultureIgnoreCase);
}
else
{
_tableDefs.Clear();
}
// read table defs
SQLiteCommand cmd = GetCommand(cnn, "SELECT Id, Name, DisplayName FROM TableDefs");
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TableDef tableDef = new TableDef
{
id = reader.GetInt32(0),
name = reader.GetString(1),
displayName = reader.GetString(2)
};
_tableDefs.Add(tableDef.name, tableDef);
}
reader.Close();
// read column defs
cmd.CommandText = "SELECT Id, Name, DisplayName, Type, ParentTable FROM ColumnDefs WHERE TableId = ?";
SQLiteParameter idParam = new SQLiteParameter();
cmd.Parameters.Add(idParam);
cmd.Prepare();
foreach (TableDef tableDef in _tableDefs.Values)
{
List <ColumnDef> colDefs = new List <ColumnDef>();
idParam.Value = tableDef.id;
reader = cmd.ExecuteReader();
while (reader.Read())
{
ColumnDef colDef = new ColumnDef()
{
id = reader.GetInt32(0),
name = reader.GetString(1),
displayName = reader.GetString(2),
type = reader.GetString(3),
parentTable = reader.GetString(4),
tableName = tableDef.name
};
colDefs.Add(colDef);
}
reader.Close();
tableDef.columns = colDefs.ToArray();
}
}
private void ConfigureAdapter(TableDef tableDef)
{
SQLiteDataAdapter adapter;
if (!_adapters.TryGetValue(tableDef.name, out adapter))
{
SQLiteCommandBuilder builder = new SQLiteCommandBuilder();
// save default builder to quote identifiers
if (_builder == null)
_builder = builder;
adapter = new SQLiteDataAdapter(string.Format("SELECT * FROM {0}", QuoteIdentifier(tableDef.name)), _connString);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
builder.DataAdapter = adapter;
// to update primary key
//adapter.InsertCommand = builder.GetInsertCommand();
//adapter.InsertCommand.CommandText = adapter.InsertCommand.CommandText + "; SELECT last_insert_rowid() AS Id";
//adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
_adapters.Add(tableDef.name, adapter);
}
adapter.Fill(_dataSet, tableDef.name);
}
private void LoadTableDefs(SQLiteConnection cnn)
{
if (_tableDefs == null)
_tableDefs = new Dictionary<string, TableDef>(StringComparer.CurrentCultureIgnoreCase);
else
_tableDefs.Clear();
// read table defs
SQLiteCommand cmd = GetCommand(cnn, "SELECT Id, Name, DisplayName FROM TableDefs");
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
TableDef tableDef = new TableDef
{
id = reader.GetInt32(0),
name = reader.GetString(1),
displayName = reader.GetString(2)
};
_tableDefs.Add(tableDef.name, tableDef);
}
reader.Close();
// read column defs
cmd.CommandText = "SELECT Id, Name, DisplayName, Type, ParentTable FROM ColumnDefs WHERE TableId = ?";
SQLiteParameter idParam = new SQLiteParameter();
cmd.Parameters.Add(idParam);
cmd.Prepare();
foreach (TableDef tableDef in _tableDefs.Values)
{
List<ColumnDef> colDefs = new List<ColumnDef>();
idParam.Value = tableDef.id;
reader = cmd.ExecuteReader();
while (reader.Read())
{
ColumnDef colDef = new ColumnDef()
{
id = reader.GetInt32(0),
name = reader.GetString(1),
displayName = reader.GetString(2),
type = reader.GetString(3),
parentTable = reader.GetString(4),
tableName = tableDef.name
};
colDefs.Add(colDef);
}
reader.Close();
tableDef.columns = colDefs.ToArray();
}
}
// Add new table
// cnn specified by CheckSchema create/update schema while Store object creating
// specify newSchemaVersion after Store object created
public void AddTable(SQLiteConnection cnn, TableDef tableDef, int newSchemaVersion = 0)
{
if (newSchemaVersion != 0 && _schemaVersion >= newSchemaVersion)
throw new ArgumentException("New schema version should be greater then current");
string quotedTableName = QuoteIdentifier(tableDef.name);
string sql = string.Format("INSERT INTO TableDefs (Name, DisplayName) VALUES ('{0}', '{1}');\n", tableDef.name, tableDef.displayName);
string colList = "Id INTEGER PRIMARY KEY AUTOINCREMENT, ";
string valList = string.Format("('Id','INTEGER','Id','',(SELECT Id FROM TableDefs WHERE Name = '{0}')), ", tableDef.name);
foreach (ColumnDef column in tableDef.columns)
{
if (column.type.StartsWith("TABLE")) // TABLE parentTable
colList += string.Format("{0} INTEGER {1}, FOREIGN KEY({0}) REFERENCES {2}(Id), ",
QuoteIdentifier(column.name),
(column.allowDBNull) ? string.Empty : "NOT NULL",
QuoteIdentifier(column.parentTable)
);
else
colList += string.Format("{0} {1} {2} {3}, ",
QuoteIdentifier(column.name),
column.type,
(column.allowDBNull) ? string.Empty : "NOT NULL",
(column.defValue != null) ? "DEFAULT " + column.defValue : string.Empty
);
valList += string.Format("('{0}','{1}','{2}','{3}',(SELECT Id FROM TableDefs WHERE Name = '{4}')), ",
column.name, column.type, column.displayName, column.parentTable, tableDef.name);
}
sql += string.Format("INSERT INTO ColumnDefs (Name,Type,DisplayName,ParentTable,TableId) VALUES {0};\n", valList.Remove(valList.Length - 2));
sql += string.Format("CREATE TABLE {0} ({1});\n", quotedTableName, colList.Remove(colList.Length - 2));
if (newSchemaVersion != 0 && _schemaVersion != newSchemaVersion)
sql += string.Format("UPDATE Store SET Version = '{0}';", newSchemaVersion);
ExecuteNonQuery(cnn, sql);
// update _tableDefs if schema loaded
if (_schemaVersion > 0)
{
_tableDefs.Add(tableDef.name, tableDef);
ConfigureAdapter(tableDef);
}
if (newSchemaVersion != 0)
_schemaVersion = newSchemaVersion;
}