public static bool DatabaseObjectExists(this SqliteConnection sql, string table)
{
RowsRet rows = null;
rows = sql.ExecuteCommand(
"SELECT COUNT(*) FROM sqlite_master WHERE name=?", new object[] { table });
int m_startRow = 1;
if (rows == null || rows.Rows.Count < ((m_startRow == 0) ? 2 : 1))
{
return(false);
}
else
{
int count = int.Parse(rows.Rows[(m_startRow == 0) ? 1 : 0].Cols[0]);
if (count >= 1)
{
return(true);
}
else
{
return(false);
}
}
}
public static List <int> findIds(string table, string commandText, List <string> parameters)
{
List <int> ids = new List <int>();
try
{
OpenDB();
#if MONO
var cm = detailsDB.CreateCommand();
cm.CommandText = commandText;
foreach (String s in parameters)
{
var p = cm.CreateParameter();
p.Value = s;
cm.Parameters.Add(p);
}
var r = cm.ExecuteReader();
while (r.Read())
{
object obj = r.GetValue(r.GetOrdinal("id"));
if (obj != null)
{
ids.Add(int.Parse((String)obj));
}
}
r.Close();
#else
RowsRet ret = null;
Object [] ob = new Object[parameters.Count];
for (int i = 0; i < parameters.Count; i++)
{
ob[i] = parameters[i];
}
ret = sqlDetailsDB.ExecuteCommand(commandText, ob);
for (int i = 0; i < ret.Rows.Count; i++)
{
Row row = ret.Rows[i];
ids.Add(int.Parse(row["id"]));
}
#endif
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return(ids);
}
private void UpdateNextIndexForTableFromDB(string tableName)
{
string statement = "SELECT MAX(ID) FROM " + tableName + ";";
RowsRet ret = sql.ExecuteCommand(statement);
if (ret.Rows.Count == 0)
{
nextIndexForTable[tableName] = 1;
}
else
{
nextIndexForTable[tableName] = ret.Rows[0].IntValue("ID") + 1;
}
}
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 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 void UpdateNextIndexForTableFromDB(string tableName)
{
string statement = "SELECT MAX(ID) FROM " + tableName + ";";
RowsRet ret = sql.ExecuteCommand(statement);
if (ret.Rows.Count == 0)
{
nextIndexForTable[tableName] = 1;
}
else
{
var row = ret.Rows[0];
String text = row.Cols[0];
int index;
int.TryParse(text, out index);
nextIndexForTable[tableName] = index + 1;
}
}
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 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
}
}
}
public DBLoader(String filename)
{
Type type = typeof(T);
#if ANDROID
string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData);
#else
string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
#endif
path = Path.Combine(path, "Combat Manager");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
try
{
string fullfilename = Path.Combine(path, filename);
#if !MONO
sql = new SQL_Lite();
sql.SkipHeaderRow = true;
sql.Open(fullfilename);
string backtext = Assembly.GetExecutingAssembly().GetName().Version.ToString();
string backname = fullfilename + backtext + ".db";
#else
#if ANDROID
Android.Util.Log.Error("DBLoader", "fullfilename " + fullfilename);
#endif
sql = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + fullfilename);
sql.Open();
#endif
#if !MONO
//make a backup
if (File.Exists(fullfilename) && !(File.Exists(backname)))
{
try
{
File.Copy(fullfilename, backname);
}
catch (IOException ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
}
#endif
bool needsCopy = false;
#if !MONO
try
{
sql.ExecuteCommand("SELECT name FROM sqlite_master");
}
catch (SQL_Lite_Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
sql.Dispose();
sql = null;
string errorname = fullfilename + ".error.db";
if (File.Exists(errorname))
{
File.Delete(errorname);
}
File.Move(fullfilename, errorname);
sql = new SQL_Lite();
sql.SkipHeaderRow = true;
sql.Open(fullfilename);
}
#endif
List <DBTableDesc> tables = GetTablesForType(type);
foreach (DBTableDesc desc in tables)
{
RowsRet ret = sql.ExecuteCommand("SELECT name FROM sqlite_master WHERE type='table' AND name=?",
new object[] { desc.Name });
if (ret.Rows.Count == 0)
{
String str = CreateTableStatementForDesc(desc);
sql.ExecuteCommand(str);
}
else
{
if (!TableMatchesDescription(sql, desc))
{
needsCopy = true;
break;
}
}
}
if (needsCopy)
{
#if ANDROID
Android.Util.Log.Error("DBLoader", "DBL needs copy");
#endif
string newfile = fullfilename + ".tmp";
if (File.Exists(newfile))
{
#if ANDROID
Android.Util.Log.Error("DBLoader", "DBL new file exists");
#endif
File.Delete(newfile);
#if ANDROID
Android.Util.Log.Error("DBLoader", "DBL new file delete");
#endif
}
#if !MONO
SQL_Lite sql2 = new SQL_Lite();
sql2.SkipHeaderRow = true;
sql2.Open(newfile);
#else
LogError("DBLoader", "NewFile " + newfile);
SqliteConnection sql2 = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + newfile);
sql2.Open();
#endif
foreach (DBTableDesc table in tables)
{
CopyTable(sql, sql2, table);
}
sql.Dispose();
sql2.Dispose();
File.Delete(fullfilename);
File.Move(newfile, fullfilename);
#if !MONO
sql = new SQL_Lite();
sql.SkipHeaderRow = true;
sql.Open(fullfilename);
#else
sql = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + fullfilename);
sql.Open();
#endif
}
LoadTableNextIndexes(tables);
LoadDBItems();
}
#if !MONO
catch (SQL_Lite_Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex);
}
#else
finally
{
}
#endif
}
public Row(RowsRet ResultSet)
{
this._ResultSet = ResultSet;
}
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);
}
}
}
public static Dictionary <string, string> LoadDetails(string ID, string table, List <string> fields)
{
var dict = new Dictionary <string, string>();
string selectFields = "";
bool first = true;
foreach (string s in fields)
{
if (!first)
{
selectFields += ", ";
}
first = false;
selectFields += s;
}
string commandText = "Select " + selectFields + " from " + table + " where ID=?";
try
{
OpenDB();
#if MONO
var cm = detailsDB.CreateCommand();
cm.CommandText = commandText;
var p = cm.CreateParameter();
p.Value = ID;
cm.Parameters.Add(p);
var r = cm.ExecuteReader();
r.Read();
foreach (string s in fields)
{
object obj = r.GetValue(r.GetOrdinal(s));
if (obj != null)
{
dict[s] = obj.ToString();
}
else
{
dict[s] = null;
}
}
r.Close();
#else
RowsRet ret = null;
ret = sqlDetailsDB.ExecuteCommand(commandText, new object[] { ID });
if (ret == null || ret.Count() == 0)
{
return(dict);
}
foreach (string s in fields)
{
dict[s] = ret.Rows[0][s];
}
#endif
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
return(dict);
}
