private void InsertItemSubtables(object item, DBTableDesc table, int index) { foreach (DBFieldDesc desc in table.SubtableFields) { Object subitem = desc.Info.GetGetMethod().Invoke(item, new object[] { }); if (subitem != null) { if (IsEnumerationTableType(desc.Info.PropertyType)) { foreach (Object o in ((IEnumerable)subitem)) { if (IsSimpleValueEnumeration(desc.Info.PropertyType)) { InsertSimpleSubtableItem(o, desc.Subtable, index); } else { InsertItemForTable(o, desc.Subtable, index); } } } else { InsertItemForTable(subitem, desc.Subtable, index); } } } }
private int GetNextIndex(DBTableDesc table) { int index = nextIndexForTable[table.Name]; nextIndexForTable[table.Name] = index + 1; return(index); }
private int InsertItem(T item) { DBTableDesc table = RootTableDesc; int val = InsertItemForTable(item, table, 0); return(val); }
private int InsertSimpleSubtableItem(object item, DBTableDesc table, int owner) { string command = "INSERT INTO " + table.Name + " (ID, OwnerID, " + table.Fields[0].Name + ") VALUES (?,?,?);"; int index = GetNextIndex(table); object[] insertParams = new object[] { index, owner, item }; sql.ExecuteCommand(command, insertParams); return(index); }
public List <object> LoadSimpleValues(DBTableDesc table, int owner) { string field = table.Fields[0].Name; Type type = table.Type.GetGenericArguments()[0]; RowsRet ret = sql.ExecuteCommand("SELECT " + field + " FROM " + table.Name + " WHERE OwnerID = ?;", new object[] { owner }); List <object> list = new List <object>(); foreach (Row row in ret.Rows) { list.Add(ParseObjectForType(row.Cols[0], type)); } return(list); }
public static string CreateTableStatementForDesc(DBTableDesc table) { String str = "CREATE TABLE " + table.Name + "(ID INTEGER PRIMARY KEY ASC"; if (!table.Primary) { str += ", OwnerID INTEGER"; } foreach (DBFieldDesc field in table.Fields) { str += ", " + field.Name + " " + field.Type + (field.Nullable ? "" : " NOT NULL"); } str += ");"; return(str); }
public void CreateDeleteTableItemStatements(DBTableDesc basetable, int index, List <String> statementList, List <object[]> paramList, bool ignoreCurrent) { object[] idParam = new object[] { index }; if (!ignoreCurrent) { statementList.Add("DELETE FROM " + basetable.Name + " WHERE ID = ?;"); paramList.Add(idParam); } foreach (DBFieldDesc field in basetable.SubtableFields) { DBTableDesc table = field.Subtable; RowsRet ret = sql.ExecuteCommand("SELECT ID FROM " + table.Name + " WHERE OwnerID = ?", idParam); foreach (Row row in ret.Rows) { CreateDeleteTableItemStatements(table, row.IntValue("ID"), statementList, paramList); } } }
private bool TableMatchesDescription(SQL_Lite sql, DBTableDesc table) #endif { RowsRet ret = sql.ExecuteCommand("PRAGMA table_info(" + table.Name + ");"); Dictionary <string, DBFieldDesc> fieldInfo = new Dictionary <string, DBFieldDesc>(); foreach (var field in table.Fields) { fieldInfo.Add(field.Name, field); } bool tableMatches = true; foreach (Row row in ret.Rows) { string name = row["name"]; if (fieldInfo.ContainsKey(row["name"])) { fieldInfo.Remove(row["name"]); } else { if (name != "ID" && name != "OwnerID") { tableMatches = false; break; } } } if (fieldInfo.Count > 0) { tableMatches = false; } return(tableMatches); }
private static List <DBTableDesc> GetTablesForType(Type startType, string baseName, bool addSubtables) { Dictionary <string, DBTableDesc> tableDesc = new Dictionary <string, DBTableDesc>(); DBTableDesc mainTable = new DBTableDesc(startType.Name, startType); if (baseName != null && baseName.Length > 0) { mainTable.Name = baseName + mainTable.Name; } else { mainTable.Primary = true; } tableDesc.Add(mainTable.Name, mainTable); List <PropertyInfo> propInfo = GetUsableFields(startType); System.Diagnostics.Debug.Assert(propInfo.Count > 0); string subtableBaseName = startType.Name + "__"; if (baseName != null && baseName.Length > 0) { subtableBaseName = baseName + subtableBaseName; } foreach (PropertyInfo info in propInfo) { Type type = info.PropertyType; DBFieldDesc desc = GetDescForType(info.Name, type, info); DBTableDesc fieldSubtable = null; if (desc != null) { mainTable.Fields.Add(desc); } else if (IsEnumerationTableType(type)) { DBFieldDesc subdesc = GetDescForType(info.Name, type.GetGenericArguments()[0], info); if (subdesc != null) { DBTableDesc subtable = new DBTableDesc(subtableBaseName + info.Name, info.PropertyType); subtable.Fields.Add(subdesc); tableDesc.Add(subtable.Name, subtable); fieldSubtable = subtable; } else { List <DBTableDesc> newList = GetTablesForType(type.GetGenericArguments()[0], subtableBaseName, addSubtables); System.Diagnostics.Debug.Assert(newList.Count > 0); fieldSubtable = newList[0]; foreach (DBTableDesc subtable in newList) { System.Diagnostics.Debug.Assert(subtable.Fields.Count > 0); if (!tableDesc.ContainsKey(subtable.Name)) { tableDesc.Add(subtable.Name, subtable); } } } } else { List <DBTableDesc> newList = GetTablesForType(type, subtableBaseName, addSubtables); System.Diagnostics.Debug.Assert(newList.Count > 0); foreach (DBTableDesc subtable in newList) { if (!tableDesc.ContainsKey(subtable.Name)) { tableDesc.Add(subtable.Name, subtable); } } fieldSubtable = newList[0]; } if (fieldSubtable != null && addSubtables) { DBFieldDesc fieldDesc = new DBFieldDesc(); fieldDesc.Name = info.Name; fieldDesc.Subtable = fieldSubtable; fieldDesc.Info = info; mainTable.Fields.Add(fieldDesc); } } List <DBTableDesc> tables = new List <DBTableDesc>(); foreach (DBTableDesc desc in tableDesc.Values) { tables.Add(desc); } return(tables); }
private static void CopyTable(SqliteConnection sql, SqliteConnection sql2, DBTableDesc table) #endif { #if ANDROID Android.Util.Log.Error("DBLoader", "CopyTable"); #endif String str = CreateTableStatementForDesc(table); sql2.ExecuteCommand(str); #if ANDROID Android.Util.Log.Error("DBLoader", "Created"); #endif if (sql.DatabaseObjectExists(table.Name)) { RowsRet data = sql.ExecuteCommand("Select * from " + table.Name); #if ANDROID Android.Util.Log.Error("DBLoader", "Select from table.name"); #endif List <string> validOldFields = new List <string>(); List <DBFieldDesc> invalidNewFields = new List <DBFieldDesc>(); foreach (DBFieldDesc desc in table.Fields) { if (data.HasColumn(desc.Name)) { validOldFields.Add(desc.Name); } else { invalidNewFields.Add(desc); } } StringBuilder commandBuilder = new StringBuilder(); commandBuilder.Append("Insert into " + table.Name + " (ID"); int count = validOldFields.Count + 1;; if (!table.Primary) { commandBuilder.Append(", OwnerID"); count++; } StringBuilder fieldBuilder = new StringBuilder(); StringBuilder valueBuilder = new StringBuilder(); foreach (string strField in validOldFields) { fieldBuilder.Append(", " + strField); } for (int i = 1; i < count; i++) { valueBuilder.Append(", ?"); } foreach (DBFieldDesc desc in invalidNewFields) { if (desc.Nullable == false) { if (desc.Type == "INTEGER") { fieldBuilder.Append(", " + desc.Name); valueBuilder.Append(", 0"); } } } fieldBuilder.Append(") VALUES ( ?"); valueBuilder.Append(");"); commandBuilder.Append(fieldBuilder); commandBuilder.Append(valueBuilder); string command = commandBuilder.ToString(); foreach (Row row in data.Rows) { List <object> values = new List <object>(); values.Add(row["ID"]); if (!table.Primary) { values.Add(row["OwnerID"]); } foreach (string strField in validOldFields) { values.Add(row[strField]); } object[] objParams = values.ToArray(); sql2.ExecuteCommand(command, objParams); #if ANDROID Android.Util.Log.Error("DBLoader", "row command"); #endif } } }
private static void CopyTable(SQL_Lite sql, SQL_Lite sql2, DBTableDesc table)
private bool TableMatchesDescription(SqliteConnection sql, DBTableDesc table)
public void CreateDeleteTableItemStatements(DBTableDesc basetable, int index, List <String> statementList, List <object[]> paramList) { CreateDeleteTableItemStatements(basetable, index, statementList, paramList, false); }
private int InsertItemForTable(object item, DBTableDesc table, int owner) { List <object> insertParams = new List <object>(); int index; if (table.Primary && ((IDBLoadable)item).DBLoaderID != 0) { index = ((IDBLoadable)item).DBLoaderID; } else { index = GetNextIndex(table); } int count = 1; insertParams.Add(index); if (!table.Primary) { insertParams.Add(owner); count++; } StringBuilder strb = new StringBuilder(); strb.Append("INSERT INTO " + table.Name + "(ID"); if (!table.Primary) { strb.Append(", OwnerID"); } List <DBFieldDesc> subtables = new List <DBFieldDesc>(); foreach (DBFieldDesc field in table.Fields) { if (field.Subtable == null) { strb.Append(", " + field.Name); insertParams.Add(field.Info.GetGetMethod().Invoke(item, new object[] {})); count++; } else { subtables.Add(field); } } strb.Append(") VALUES (?"); for (int i = 1; i < count; i++) { strb.Append(", ?"); } strb.Append(");"); string command = strb.ToString(); sql.ExecuteCommand(command, insertParams.ToArray()); InsertItemSubtables(item, table, index); if (table.Primary) { IDBLoadable loadable = (IDBLoadable)item; loadable.DBLoaderID = index; } return(index); }
private List <object> LoadValues(DBTableDesc table, int owner) { List <object> valueList = new List <object>(); RowsRet ret; if (table.Primary) { ret = sql.ExecuteCommand("SELECT * FROM " + table.Name + ";"); } else { ret = sql.ExecuteCommand("SELECT * FROM " + table.Name + " WHERE OwnerID = ?;", new object[] { owner }); } foreach (Row row in ret.Rows) { ConstructorInfo info = table.Type.GetConstructor(new Type[] { }); Object item = info.Invoke(new object[] {}); int index = row.IntValue("ID"); foreach (DBFieldDesc field in table.ValueFields) { string stringVal = row[field.Name]; object obVal = ParseObjectForType(stringVal, field.Info.PropertyType); field.Info.GetSetMethod().Invoke(item, new object[] { obVal }); } foreach (DBFieldDesc field in table.SubtableFields) { if (IsEnumerationTableType(field.Info.PropertyType)) { List <object> list; if (IsSimpleValueEnumeration(field.Info.PropertyType)) { list = LoadSimpleValues(field.Subtable, index); } else { list = LoadValues(field.Subtable, index); } ConstructorInfo cons = field.Info.PropertyType.GetConstructor(new Type[] { }); IList newList = (IList)cons.Invoke(new object[0]); foreach (object listitem in list) { newList.Add(listitem); } field.Info.GetSetMethod().Invoke(item, new object[] { newList }); } else { List <object> list = LoadValues(field.Subtable, index); if (list.Count > 0) { field.Info.GetSetMethod().Invoke(item, new object[] { list[0] }); } } } if (table.Primary) { IDBLoadable loadable = (IDBLoadable)item; loadable.DBLoaderID = index; } valueList.Add(item); } return(valueList); }
private static void CopyTable(SqliteConnection sql, SqliteConnection sql2, DBTableDesc table) #endif { String str = CreateTableStatementForDesc(table); sql2.ExecuteCommand(str); if (sql.DatabaseObjectExists(table.Name)) { RowsRet data = sql.ExecuteCommand("Select * from " + table.Name); List <string> validOldFields = new List <string>(); foreach (DBFieldDesc desc in table.Fields) { if (data.HasColumn(desc.Name)) { validOldFields.Add(desc.Name); } } StringBuilder commandBuilder = new StringBuilder(); commandBuilder.Append("Insert into " + table.Name + " (ID"); int count = validOldFields.Count + 1;; if (!table.Primary) { commandBuilder.Append(", OwnerID"); count++; } foreach (string strField in validOldFields) { commandBuilder.Append(", " + strField); } commandBuilder.Append(") VALUES ( ?"); for (int i = 1; i < count; i++) { commandBuilder.Append(", ?"); } commandBuilder.Append(");"); string command = commandBuilder.ToString(); foreach (Row row in data.Rows) { List <object> values = new List <object>(); values.Add(row["ID"]); if (!table.Primary) { values.Add(row["OwnerID"]); } foreach (string strField in validOldFields) { values.Add(row[strField]); } object[] objParams = values.ToArray(); sql2.ExecuteCommand(command, objParams); } } }