public void AddColumns(ColumnDef[] columns, bool dropExisting)
{
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
using (connection)
{
connection.ConnectionString = connString;
connection.Open();
if (dropExisting)
DropAllColumns(cmd);
string Sql = string.Empty;
string colList = string.Empty;
string valList = string.Empty;
foreach (ColumnDef column in columns)
{
colList += string.Format("[{0}] {1} {3}, ", column.name, column.type,
(column.allowDBNull) ? "NULL" : string.Empty);
valList += string.Format("('{0}','{1}'), ", column.name, column.displayName);
}
Sql += string.Format("ALTER TABLE Items ADD {0};\n", colList.Substring(0, colList.Length - 2));
Sql += string.Format("INSERT INTO ExtColumns (ColumnName, DisplayName) VALUES {0}", valList.Substring(0, valList.Length - 2));
cmd.CommandText = Sql;
cmd.ExecuteNonQuery();
}
}
// Manager
public void AddColumns(string tableName, ColumnDef[] columns, bool dropExisting)
{
string quotedTableName = QuoteIdentifier(tableName);
using (DbConnection cnn = GetConnection())
{
cnn.Open();
DbCommand cmd = GetCommand(cnn);
if (dropExisting)
DropAllColumns(cmd, quotedTableName);
string Sql = string.Empty;
string colList = string.Empty;
string valList = string.Empty;
foreach (ColumnDef column in columns)
{
colList += string.Format("{0} {1} {3}, ", QuoteIdentifier(column.name), column.type,
(column.allowDBNull) ? "NULL" : string.Empty);
valList += string.Format("('{0}','{1}'), ", column.name, column.displayName);
}
Sql += string.Format("ALTER TABLE {0} ADD {1};\n", quotedTableName, colList.Substring(0, colList.Length - 2));
Sql += string.Format("INSERT INTO ExtColumns (ColumnName, DisplayName) VALUES {0}", valList.Substring(0, valList.Length - 2));
cmd.CommandText = Sql;
cmd.ExecuteNonQuery();
}
}
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);
}
}
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 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();
}
}
public void AddColumns(SQLiteConnection cnn, string tableName, ColumnDef[] newColumns, int newSchemaVersion = 0)
{
if (_schemaVersion != 0 && _schemaVersion >= newSchemaVersion)
throw new ArgumentException("New schema version should be greater then current");
string quotedTableName = QuoteIdentifier(tableName);
string sql = string.Empty;
string colList = string.Empty;
string valList = string.Empty;
foreach (ColumnDef column in newColumns)
{
if (column.type.StartsWith("TABLE")) // TABLE parentTable
colList += string.Format("ALTER TABLE {0} ADD {1} INTEGER {2}, FOREIGN KEY({1}) REFERENCES {3}(Id), ",
quotedTableName,
QuoteIdentifier(column.name),
(column.allowDBNull) ? string.Empty : "NOT NULL",
QuoteIdentifier(column.parentTable)
);
else
sql += string.Format("ALTER TABLE {0} ADD {1} {2} {3} {4};\n",
quotedTableName,
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, tableName);
}
sql += string.Format("INSERT INTO ColumnDefs (Name,Type,DisplayName,ParentTable,TableId) VALUES {0};\n", valList.Remove(valList.Length - 2));
if (newSchemaVersion != 0 && _schemaVersion != newSchemaVersion)
sql += string.Format("UPDATE Store SET Version = '{0}';", newSchemaVersion);
ExecuteNonQuery(cnn, sql);
// update _tableDefs if schema already loaded
if (_schemaVersion > 0)
{
LoadTableDefs(cnn);
_adapters.Clear();
foreach (TableDef tableDef in _tableDefs.Values)
ConfigureAdapter(tableDef);
}
if (newSchemaVersion != 0)
_schemaVersion = newSchemaVersion;
}
