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