public static void CreateBidDatabase(string path)
{
var db = new SQLiteDatabase(path);
db.NonQueryCommand("BEGIN TRANSACTION");
createAllBidTables(db);
db.Insert(MetadataTable.TableName, new Dictionary <string, string> {
{ MetadataTable.Version.Name, Properties.Settings.Default.Version.ToString() }
});
db.NonQueryCommand("END TRANSACTION");
db.Connection.Close();
}
static private void migrateFromTempTables(Dictionary <string, string> tableMap, SQLiteDatabase db)
{
string commandString;
foreach (KeyValuePair <string, string> tablePair in tableMap)
{
string tableName = tablePair.Key;
string tempName = tablePair.Value;
commandString = "insert into '" + tableName + "' select * from '" + tempName + "'";
db.NonQueryCommand(commandString);
commandString = "drop table '" + tempName + "'";
db.NonQueryCommand(commandString);
}
}
private static void updateToVersion(DataTable dataTable, SQLiteDatabase db, int originalVersion,
int updateVersion, Dictionary <string, string> tempMap, List <string> currentTables, List <TableBase> AllTables)
{
TableMapList mapList = buildMap(dataTable, originalVersion, updateVersion, tempMap, currentTables);
foreach (TableMap map in mapList)
{
if (isSingleRow(map))
{
DataTable dt = combinedTable(map, db);
string insertColumns = DatabaseHelper.FieldsString(map.UpdateFields);
foreach (DataRow row in dt.Rows)
{
List <string> values = new List <string>();
foreach (var field in map.OriginalFields)
{
values.Add(row[field].ToString());
}
string insertValues = DatabaseHelper.ValuesString(values);
string command = String.Format("insert into {0} ({1}) values ({2})", map.UpdateTableName, insertColumns, insertValues);
db.NonQueryCommand(command);
}
}
else
{
foreach (string table in map.OriginalTableNames)
{
migrateData(table, DatabaseHelper.FieldsString(map.TableFieldsDictionary[table]),
map.UpdateTableName, DatabaseHelper.FieldsString(map.UpdateFields), db);
}
}
foreach (TableBase table in AllTables)
{
if (tempMap[table.NameString] == map.UpdateTableName)
{
foreach (TableField field in table.Fields)
{
if (!map.UpdateFields.Contains(field.Name))
{
string command = String.Format("update {0} set {1} = \'{2}\' ",
tempMap[table.NameString], field.Name, field.DefaultValue);
db.NonQueryCommand(command);
}
}
}
}
}
}
public static bool Update(string dataBasePath, List <UpdateItem> updates)
{
List <string> omitErrors = new List <string> {
"Note",
"Exclusion",
"ProposalScope",
"ScopeBranch",
"Location"
};
SQLiteDatabase db = new SQLiteDatabase(dataBasePath);
db.NonQueryCommand("BEGIN TRANSACTION");
bool success = true;
foreach (UpdateItem item in updates)
{
if (item.Change == Change.Remove)
{
bool delSuccess = delete(item, db);
if (!delSuccess)
{
success = false;
}
}
else if (item.Change == Change.Add)
{
bool addSuccess = add(item, db);
if (!addSuccess)
{
success = false;
}
}
else if (item.Change == Change.Edit)
{
bool editSuccess = edit(item, db);
if (!editSuccess && !(omitErrors.Contains(item.Table)))
{
success = false;
}
}
}
db.NonQueryCommand("END TRANSACTION");
db.Connection.Close();
return(success);
}
public static void CreateTableFromDefinition(TableBase table, SQLiteDatabase db)
{
string tableName = table.NameString;
List <TableField> primaryKey = table.PrimaryKeys;
List <TableField> fields = table.Fields;
string createString = "CREATE TABLE '" + tableName + "' (";
foreach (TableField field in fields)
{
createString += "'" + field.Name + "' " + field.FieldType;
if (fields.IndexOf(field) < (fields.Count - 1))
{
createString += ", ";
}
}
if (primaryKey.Count != 0)
{
createString += ", PRIMARY KEY(";
}
foreach (TableField pk in primaryKey)
{
createString += "'" + pk.Name + "' ";
if (primaryKey.IndexOf(pk) < (primaryKey.Count - 1))
{
createString += ", ";
}
else
{
createString += ")";
}
}
createString += ")";
db.NonQueryCommand(createString);
}
private static void migrateData(string originalTable, string originalFields,
string updateTable, string updateFields, SQLiteDatabase db)
{
string commandString = String.Format("insert into {0} ({1}) select {2} from '{3}'",
updateTable, updateFields, originalFields, originalTable);
db.NonQueryCommand(commandString);
}
static private void removeOldTables(List <string> tableNames, SQLiteDatabase db)
{
foreach (string table in tableNames.Where(table => table != MetadataTable.TableName))
{
string commandString = "drop table '" + table + "'";
db.NonQueryCommand(commandString);
}
}
public static void UpdateVersionNumber(SQLiteDatabase db)
{
string commandString = "update " + MetadataTable.TableName + " set " + MetadataTable.Version.Name + " = '" + Properties.Settings.Default.Version + "' ";
db.NonQueryCommand(commandString);
}
private static void cleanMigratedDatabase(SQLiteDatabase db, int version)
{
if (version < 10)
{
string commandString = "";
#region Point Types
List <String> deprecatedTypes = new List <string>()
{
"BACnetMSTP",
"BACnetIP",
"LonWorks",
"ModbusTCP",
"ModbusRTU"
};
foreach (var item in deprecatedTypes)
{
commandString = String.Format("update {0} set {1} = '{2}' where {3} = '{4}'",
PointTable.TableName, PointTable.Type.Name, "AI", PointTable.Type.Name, item);
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}' where {3} = '{4}'",
IOTable.TableName, IOTable.IOType.Name, "AI", IOTable.IOType.Name, item);
db.NonQueryCommand(commandString);
}
#endregion
#region HardwiredConnections
string deviceJoinTable = String.Format("select {0}, {1} as ConDevID from {2} inner join {3} where {2}.{4} = {3}.{5}", HardwiredConnectionChildrenTable.ConnectionID.Name, SubScopeDeviceTable.DeviceID.Name, HardwiredConnectionChildrenTable.TableName,
SubScopeDeviceTable.TableName, HardwiredConnectionChildrenTable.ChildID.Name, SubScopeDeviceTable.SubScopeID.Name);
string connectionConnectionTypeTableJoin = String.Format("select {0}, {1}, {2} from {3} inner join ({4}) where {5} = ConDevID", HardwiredConnectionChildrenTable.ConnectionID.Name,
DeviceConnectionTypeTable.TypeID.Name, DeviceConnectionTypeTable.Quantity.Name, DeviceConnectionTypeTable.TableName,
deviceJoinTable, DeviceConnectionTypeTable.DeviceID.Name);
commandString = String.Format("insert into {0} ({1}, {2}, {3}) {4}", HardwiredConnectionConnectionTypeTable.TableName, HardwiredConnectionConnectionTypeTable.ConnectionID.Name,
HardwiredConnectionConnectionTypeTable.TypeID.Name, HardwiredConnectionConnectionTypeTable.Quantity.Name, connectionConnectionTypeTableJoin);
db.NonQueryCommand(commandString);
#endregion
#region Network Connections
commandString = String.Format("insert into {0} select {1}, {1} from {2}", NetworkConnectionProtocolTable.TableName, NetworkConnectionTable.ID.Name, NetworkConnectionTable.TableName);
db.NonQueryCommand(commandString);
//Considered to account for the lack of protocol-compliant devices
//commandString = String.Format("delete from {0}", NetworkConnectionChildrenTable.TableName);
//db.NonQueryCommand(commandString);
#endregion
#region Bid Controllers and panels
if (dbType == DBType.Bid)
{
var controllerQuery = String.Format("select {0} as ControllerID from {1} where {0} not in (select {2} from {3})", ProvidedControllerTable.ID.Name, ProvidedControllerTable.TableName, SystemControllerTable.ControllerID.Name, SystemControllerTable.TableName);
var bidControllerJoin = String.Format("select {1} as BidID, ControllerID from {0} join ({2}) ", BidInfoTable.TableName, BidInfoTable.ID.Name, controllerQuery);
commandString = String.Format("insert into {0} ({1}, {2}) {3}", BidControllerTable.TableName, BidControllerTable.BidID.Name, BidControllerTable.ControllerID.Name, bidControllerJoin);
db.NonQueryCommand(commandString);
var panelQuery = String.Format("select {0} as PanelID from {1} where {0} not in (select {2} from {3})", PanelTable.ID.Name, PanelTable.TableName, SystemPanelTable.PanelID.Name, SystemPanelTable.TableName);
var bidPanelJoin = String.Format("select {1} as BidID, PanelID from {0} join ({2}) ", BidInfoTable.TableName, BidInfoTable.ID.Name, panelQuery);
commandString = String.Format("insert into {0} ({1}, {2}) {3}", BidPanelTable.TableName, BidPanelTable.BidID.Name, BidPanelTable.PanelID.Name, bidPanelJoin);
db.NonQueryCommand(commandString);
}
#endregion
#region Manager Templates
Guid templatesGuid = Guid.NewGuid();
var idField = dbType == DBType.Bid ? BidInfoTable.ID.Name : TemplatesInfoTable.ID.Name;
var managerTable = dbType == DBType.Bid ? BidInfoTable.TableName : TemplatesInfoTable.TableName;
var managerID = String.Format("select {0} from {1} limit 1", idField, managerTable);
commandString = String.Format("insert into {0} ({1}, {2}) values (({3}), '{4}')",
ManagerTemplatesTable.TableName, ManagerTemplatesTable.ManagerID.Name, ManagerTemplatesTable.TemplatesID.Name, managerID, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesControllerTable.TableName, TemplatesControllerTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesEquipmentTable.TableName, TemplatesEquipmentTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesSystemTable.TableName, TemplatesSystemTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesSubScopeTable.TableName, TemplatesSubScopeTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesMiscCostTable.TableName, TemplatesMiscCostTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesPanelTable.TableName, TemplatesPanelTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("insert into {0} ({1}) select {2} from {3}", TemplatesParametersTable.TableName, TemplatesParametersTable.ParametersID.Name, ParametersTable.ID.Name, ParametersTable.TableName);
db.NonQueryCommand(commandString);
commandString = String.Format("update {0} set {1} = '{2}'", TemplatesParametersTable.TableName, TemplatesParametersTable.TemplatesID.Name, templatesGuid.ToString());
db.NonQueryCommand(commandString);
commandString = String.Format("insert into {0} values ('{1}')", ScopeTemplatesTable.TableName, templatesGuid.ToString());
db.NonQueryCommand(commandString);
#endregion
}
}
