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 } }