/// <summary>
/// Read timers from database
/// TODO: catch exceptions
/// </summary>
public void readTimersfromSQL()
{
lock (Timers)
{
SqlWrapper ms = new SqlWrapper();
TimeSpan ts;
DateTime n = DateTime.Now;
AOTimers m_timer;
byte[] blob = new byte[10240];
Timers.Clear();
ms.SqlRead("SELECT * FROM " + getSQLTablefromDynelType() + "timers WHERE ID=" + ID.ToString() + ";");
DataTable dt = ms.ReadDT("SELECT * FROM " + getSQLTablefromDynelType() + "timers WHERE ID=" + ID.ToString() + ";");
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
ts = TimeSpan.FromSeconds((Int32)row["timespan"]);
m_timer = new AOTimers();
m_timer.Timestamp = DateTime.Now + ts;
m_timer.Strain = (Int32)row["strain"];
m_timer.Function = new AOFunctions();
blob = (byte[])row[3];
m_timer.Function.ReadFunctionfromBlob(ref blob, 0);
}
}
}
}
/// <summary>
/// Read timers from database
/// TODO: catch exceptions
/// </summary>
public void ReadTimersFromSql()
{
lock (this.timers)
{
SqlWrapper ms = new SqlWrapper();
TimeSpan timeSpan;
DateTime now = DateTime.Now;
byte[] blob = new byte[10240];
this.timers.Clear();
ms.SqlRead(
"SELECT * FROM " + this.GetSqlTablefromDynelType() + "timers WHERE ID=" + this.Id.ToString() + ";");
DataTable dt =
ms.ReadDatatable(
"SELECT * FROM " + this.GetSqlTablefromDynelType() + "timers WHERE ID=" + this.Id.ToString()
+ ";");
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
timeSpan = TimeSpan.FromSeconds((Int32)row["timespan"]);
AOTimers aoTimer = new AOTimers
{
Timestamp = DateTime.Now + timeSpan,
Strain = (Int32)row["strain"],
Function = new AOFunctions()
};
MemoryStream memstream = new MemoryStream((byte[])row[3]);
BinaryFormatter bin = new BinaryFormatter();
aoTimer.Function = (AOFunctions)bin.Deserialize(memstream);
}
}
}
}
public static void lintels()
{
SqlWrapper ms = new SqlWrapper();
List<lintel> doors = new List<lintel>();
lintel ll;
ms.SqlRead("SELECT * FROM doors");
#region MySql
if (ms.ismysql)
{
while (ms.myreader.Read())
{
ll = new lintel();
ll.ID = ms.myreader.GetInt32("ID");
ll.X = ms.myreader.GetFloat("X");
ll.Y = ms.myreader.GetFloat("Y");
ll.Z = ms.myreader.GetFloat("Z");
ll.HZ = ms.myreader.GetFloat("HZ");
ll.frompf = ms.myreader.GetInt32("playfield");
ll.topf = ms.myreader.GetInt32("toplayfield");
ll.toid = ms.myreader.GetInt32("toid");
ll.proxy = ms.myreader.GetInt32("proxy");
doors.Add(ll);
}
ms.myreader.Close();
ms.mcc.Close();
ms.mcc.Dispose();
}
#endregion
#region MsSql
if (ms.ismssql)
{
while (ms.sqlreader.Read())
{
ll = new lintel();
ll.ID = ms.sqlreader.GetInt32(0);
ll.X = ms.sqlreader.GetFloat(1);
ll.Y = ms.sqlreader.GetFloat(2);
ll.Z = ms.sqlreader.GetFloat(3);
ll.HZ = ms.sqlreader.GetFloat(4);
ll.frompf = ms.sqlreader.GetInt32(5);
ll.topf = ms.sqlreader.GetInt32(6);
ll.toid = ms.sqlreader.GetInt32(7);
ll.proxy = ms.sqlreader.GetInt32(8);
doors.Add(ll);
}
ms.sqlreader.Close();
ms.sqlcc.Close();
ms.sqlcc.Dispose();
}
#endregion
#region PostgreSql
if (ms.isnpgsql)
{
while (ms.npgreader.Read())
{
ll = new lintel();
ll.ID = ms.npgreader.GetInt32(0);
ll.X = ms.npgreader.GetFloat(1);
ll.Y = ms.npgreader.GetFloat(2);
ll.Z = ms.npgreader.GetFloat(3);
ll.HZ = ms.npgreader.GetFloat(4);
ll.frompf = ms.npgreader.GetInt32(5);
ll.topf = ms.npgreader.GetInt32(6);
ll.toid = ms.npgreader.GetInt32(7);
ll.proxy = ms.npgreader.GetInt32(8);
doors.Add(ll);
}
ms.npgreader.Close();
ms.npgcc.Close();
ms.npgcc.Dispose();
}
#endregion
bool found;
foreach (lintel l1 in doors)
{
found = false;
foreach (lintel l2 in doors)
{
if (l1.ID != l2.ID)
{
if (l1.topf != 0)
{
if ((l1.frompf == l2.topf) && (l1.topf == l2.frompf))
{
found = true;
l1.toid = l2.ID;
ms.SqlUpdate(
"UPDATE doors set toid=" + l2.ID.ToString() + " where id=" + l1.ID.ToString());
Console.WriteLine(l1.ID.ToString());
}
else if ((l1.topf == l2.frompf) && (l2.topf == 0))
{
l1.toid = l2.ID;
ms.SqlUpdate(
"UPDATE doors set toid=" + l2.ID.ToString() + " where id=" + l1.ID.ToString());
Console.WriteLine(l1.ID.ToString());
}
}
}
}
if (!found)
{
ms.SqlUpdate("UPDATE doors SET proxy=1 where ID=" + l1.ID.ToString());
}
}
}
/// <summary>
/// Check our tables and create/fill them if they don't exist
/// </summary>
public void CheckDBs()
{
SqlWrapper ms = new SqlWrapper();
List<string> tablelist = new List<string>();
List<string> tabletodo = new List<string>();
bool allok = true;
// ToDo: check if database exists and create it if not (parsing the connection string)
if (this.ismssql)
{
ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
}
else if (this.isnpgsql)
{
ms.SqlRead("SELECT table_name FROM information_schema.tables;");
}
else if (this.ismysql)
{
ms.SqlRead("show Tables");
}
if (ms.myreader.HasRows)
{
while (ms.myreader.Read())
{
tablelist.Add(ms.myreader.GetString(0));
}
}
else
{
allok = false;
}
ms.sqlclose();
string[] sqlfiles = Directory.GetFiles("SQLTables");
bool isin;
foreach (string s in sqlfiles)
{
isin = false;
foreach (string table in tablelist)
{
if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
{
isin = true;
break;
}
}
if (!isin)
{
tabletodo.Add(s);
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table " + s + " doesn't exist.");
allok = false;
}
}
if (!allok)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
string answer = Console.ReadLine();
string sqlquery;
if (answer.ToLower() == "y")
{
foreach (string todo in tabletodo)
{
long filesize = new FileInfo(todo).Length;
Console.ForegroundColor = ConsoleColor.Green;
Console.Write("Table " + todo.PadRight(67) + "[ 0%]");
if (filesize > 10000)
{
string[] queries = File.ReadAllLines(todo);
int c = 0;
sqlquery = string.Empty;
string lastpercent = "0";
while (c < queries.Length)
{
if (queries[c].IndexOf("INSERT INTO") == -1)
{
sqlquery += queries[c] + "\n";
}
else
{
c--;
break;
}
c++;
}
ms.SqlInsert(sqlquery);
c++;
string buf1 = string.Empty;
while (c < queries.Length)
{
if (queries[c].ToLower().Substring(0, 11) == "insert into")
{
break;
}
c++;
}
if (c < queries.Length)
{
buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
buf1 = buf1 + "VALUES ";
StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
while (c < queries.Length)
{
if (Buffer.Length == 0)
{
Buffer.Append(buf1);
}
string part = string.Empty;
while (c < queries.Length)
{
if (queries[c].Trim() != string.Empty)
{
part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
part = part.Substring(part.IndexOf("(")); // from '(' to end
part = part.Substring(0, part.Length - 1); // Remove ';'
if (Buffer.Length + 1 + part.Length > 1024 * 1000)
{
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
Buffer.Append(buf1);
string lp2 =
Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
.ToString();
if (lp2 != lastpercent)
{
Console.Write(
"\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
lastpercent = lp2;
}
}
Buffer.Append(part + ", ");
}
c++;
}
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
if (lp != lastpercent)
{
Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
lastpercent = lp;
}
}
}
else
{
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
}
else
{
sqlquery = File.ReadAllText(todo);
ms.SqlInsert(sqlquery);
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
Console.WriteLine();
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Database is fine.");
}
/// <summary>
/// Check our tables and create/fill them if they don't exist
/// </summary>
public void CheckDBs()
{
SqlWrapper ms = new SqlWrapper();
List <string> tablelist = new List <string>();
List <string> tabletodo = new List <string>();
bool allok = true;
// ToDo: check if database exists and create it if not (parsing the connection string)
if (this.ismssql)
{
ms.SqlRead("SELECT table_name FROM INFORMATION_SCHEMA.TABLES;");
}
else if (this.isnpgsql)
{
ms.SqlRead("SELECT table_name FROM information_schema.tables;");
}
else if (this.ismysql)
{
ms.SqlRead("show Tables");
}
if (ms.myreader.HasRows)
{
while (ms.myreader.Read())
{
tablelist.Add(ms.myreader.GetString(0));
}
}
else
{
allok = false;
}
ms.sqlclose();
string[] sqlfiles = Directory.GetFiles("SQLTables");
bool isin;
foreach (string s in sqlfiles)
{
isin = false;
foreach (string table in tablelist)
{
if (s.ToLower() == Path.Combine("SQLTables", table + ".sql").ToLower())
{
isin = true;
break;
}
}
if (!isin)
{
tabletodo.Add(s);
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table " + s + " doesn't exist.");
allok = false;
}
}
if (!allok)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.Write("SQL Tables are not complete. Should they be created? (Y/N) ");
string answer = Console.ReadLine();
string sqlquery;
if (answer.ToLower() == "y")
{
foreach (string todo in tabletodo)
{
long filesize = new FileInfo(todo).Length;
Console.ForegroundColor = ConsoleColor.Green;
Console.Write("Table " + todo.PadRight(67) + "[ 0%]");
if (filesize > 10000)
{
string[] queries = File.ReadAllLines(todo);
int c = 0;
sqlquery = string.Empty;
string lastpercent = "0";
while (c < queries.Length)
{
if (queries[c].IndexOf("INSERT INTO") == -1)
{
sqlquery += queries[c] + "\n";
}
else
{
c--;
break;
}
c++;
}
ms.SqlInsert(sqlquery);
c++;
string buf1 = string.Empty;
while (c < queries.Length)
{
if (queries[c].ToLower().Substring(0, 11) == "insert into")
{
break;
}
c++;
}
if (c < queries.Length)
{
buf1 = queries[c].Substring(0, queries[c].ToLower().IndexOf("values"));
buf1 = buf1 + "VALUES ";
StringBuilder Buffer = new StringBuilder(0, 1 * 1024 * 1024);
while (c < queries.Length)
{
if (Buffer.Length == 0)
{
Buffer.Append(buf1);
}
string part = string.Empty;
while (c < queries.Length)
{
if (queries[c].Trim() != string.Empty)
{
part = queries[c].Substring(queries[c].ToLower().IndexOf("values"));
part = part.Substring(part.IndexOf("(")); // from '(' to end
part = part.Substring(0, part.Length - 1); // Remove ';'
if (Buffer.Length + 1 + part.Length > 1024 * 1000)
{
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
Buffer.Append(buf1);
string lp2 =
Convert.ToInt32(Math.Floor((double)c / queries.Length * 100))
.ToString();
if (lp2 != lastpercent)
{
Console.Write(
"\rTable " + todo.PadRight(67) + "[" + lp2.PadLeft(3) + "%]");
lastpercent = lp2;
}
}
Buffer.Append(part + ", ");
}
c++;
}
Buffer.Remove(Buffer.Length - 2, 2);
Buffer.Append(";");
ms.SqlInsert(Buffer.ToString());
Buffer.Clear();
string lp = Convert.ToInt32(Math.Floor((double)c / queries.Length * 100)).ToString();
if (lp != lastpercent)
{
Console.Write("\rTable " + todo.PadRight(67) + "[" + lp.PadLeft(3) + "%]");
lastpercent = lp;
}
}
}
else
{
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
}
else
{
sqlquery = File.ReadAllText(todo);
ms.SqlInsert(sqlquery);
Console.Write("\rTable " + todo.PadRight(67) + "[100%]");
}
Console.WriteLine();
}
}
}
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("Database is fine.");
}
