public override string WriteRenameColumn(SQColumn column, string oldName)
{
SQTable table = GetTable(column.Table.Name);
// get list of columns
List <string> newColumns = new List <string>();
List <string> oldColumns = new List <string>();
foreach (SQColumn col in table.Columns)
{
if (oldName.ToUpper() == col.Name.ToUpper())
{
newColumns.Add(column.Name);
oldColumns.Add(col.Name);
}
else
{
newColumns.Add(col.Name);
oldColumns.Add(col.Name);
}
}
table.GetColumnByName(oldName).Name = column.Name;
return(WriteUpdateTable(table, newColumns, oldColumns));
}
public override string WriteCreateTable(SQTable table)
{
// create the table
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE " + table.Name);
sb.AppendLine("(");
for (int i = 0; i < table.Columns.Count; i++)
{
SQColumn col = table.Columns[i];
sb.AppendLine((i > 0 ? "\t," : "\t") + GetCreateColumnText(col));
}
foreach (SQColumn col in table.Columns)
{
if (col is SQColumn_SQLite)
{
SQColumn_SQLite l = (SQColumn_SQLite)col;
if (l.ForeignKey != null)
{
sb.AppendLine(string.Format(
"\t,CONSTRAINT [FK_{0}_{1}_{2}_{3}] FOREIGN KEY ([{1}]) REFERENCES [{2}] ([{3}])",
l.Table.Name, l.Name, l.ForeignKey.Table.Name, l.ForeignKey.Name));
}
}
}
sb.AppendLine(")");
return(sb.ToString());
}
public override string WriteCreateTable(SQTable table)
{
// create the table
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE " + GetSafeObjectName(table.Name));
sb.AppendLine("(");
for (int i = 0; i < table.Columns.Count; i++)
{
SQColumn col = table.Columns[i];
sb.AppendLine((i > 0 ? "\t," : "\t") + GetCreateColumnText(col));
}
foreach (SQColumn col in table.Columns)
{
if (col.IsPrimary)
{
sb.AppendLine(string.Format("\t,CONSTRAINT {0} PRIMARY KEY ({1})", GetSafeObjectName("PK_" + table.Name + "_" + col.Name), GetSafeObjectName(col.Name)));
}
}
sb.AppendLine(");");
return(sb.ToString());
}
public override string WriteCreateTable(SQTable table)
{
// create the table
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE dbo." + table.Name);
sb.AppendLine("(");
for (int i = 0; i < table.Columns.Count; i++)
{
SQColumn col = table.Columns[i];
sb.AppendLine((i > 0 ? "\t," : "\t") + GetCreateColumnText(col));
}
sb.AppendLine(")");
SQColumn pk = table.GetPrimaryKey();
if (pk != null)
{
sb.AppendLine("ON [PRIMARY]");
sb.AppendLine("GO");
sb.AppendLine("ALTER TABLE dbo." + table.Name + " ADD CONSTRAINT");
sb.AppendLine("PK_" + table.Name + " PRIMARY KEY CLUSTERED (" + pk.Name + ")");
sb.AppendLine("WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]");
}
return(sb.ToString());
}
public SchemaTable EnsureSchema(Type type)
{
if (!_SchemaTables.ContainsKey(type))
{
SchemaTable res = new SchemaTable(type);
if (res.Columns.Count < 1)
{
_SchemaTables.Add(type, null);
}
else
{
_SchemaTables.Add(type, res);
SQTable curr = Adp.GetTable(res.Table.Name);
if (curr != null)
{
if (!res.LockSchema)
{
}
}
else if (!DisableSchemaBuild)
{
Adp.CreateTable(res.Table);
foreach (SchemaHintForeignKey fk in res.FKeyHints)
{
SchemaTable fkt = EnsureSchema(fk.ForeignKey);
Adp.AddForeignKey(res.GetColumnByName(fk.Storage).Column, fkt.Table.GetPrimaryKey());
}
}
}
}
return(_SchemaTables[type]);
}
private string WriteUpdateTable(SQTable table, List <string> newColumns, List <string> oldColumns)
{
StringBuilder sb = new StringBuilder();
// rename the existing table
string tableRename = table.Name + "_" + Guid.NewGuid().ToString().Replace("-", "");
sb.AppendLine(string.Format("ALTER TABLE [{0}] RENAME TO [{1}];\r\n\r\n", table.Name, tableRename));
// create the new table
sb.AppendLine(WriteCreateTable(table) + ";\r\n\r\n");
// copy data from the old table
SQInsertFromQuery copyQuery = new SQInsertFromQuery(new SQAliasableObject(table.Name), newColumns, oldColumns)
{
From = new SQFromClause(tableRename)
};
sb.AppendLine(Write(copyQuery) + ";\r\n\r\n");
// remove the old table
sb.AppendLine(WriteRemoveTable(tableRename));
return(sb.ToString());
}
internal SQTable GetTable()
{
if (_Table == null)
{
_Table = GetTable(true);
}
return(_Table);
}
public override SQTable GetTable(string name)
{
SQLiteConnection sqlcon = new SQLiteConnection(ConnectionString);
sqlcon.Open();
DataTable dt = sqlcon.GetSchema("Columns", new string[] { null, null, name, null });
DataTable fks = sqlcon.GetSchema("ForeignKeys", new string[] { null, null, name, null });
sqlcon.Close();
SQTable res = null;
if (dt.Rows.Count > 0)
{
res = new SQTable();
res.Name = name;
foreach (DataRow row in dt.Rows)
{
SQDataTypes dataType = GetTypeFromName(row["DATA_TYPE"].ToString());
int precision = row["NUMERIC_PRECISION"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_PRECISION"]);
res.Columns.Add(new SQColumn_SQLite()
{
Name = row["COLUMN_NAME"].ToString(),
DataType = dataType != SQDataTypes.Int32 ? dataType : precision == 19 ? SQDataTypes.Int64 : SQDataTypes.Int32,
IsIdentity = row["AUTOINCREMENT"].Equals(true),
IsPrimary = row["PRIMARY_KEY"].Equals(true),
DefaultValue = row["COLUMN_DEFAULT"],
Length = Convert.ToInt64(row["CHARACTER_MAXIMUM_LENGTH"]),
Nullable = row["IS_NULLABLE"].Equals(true),
Precision = precision,
Scale = row["NUMERIC_SCALE"] == DBNull.Value ? 0 : Convert.ToInt32(row["NUMERIC_SCALE"])
});
}
foreach (DataRow row in fks.Rows)
{
((SQColumn_SQLite)res.GetColumnByName(row["FKEY_FROM_COLUMN"].ToString())).ForeignKey = new SQColumn()
{
Name = row["FKEY_TO_COLUMN"].ToString(),
Table = new SQTable()
{
Name = row["FKEY_TO_TABLE"].ToString()
}
};
}
}
return(res);
}
internal SQTable GetTable(bool refresh)
{
if (!refresh)
{
GetTable();
}
_Table = new SQTable();
_Table.Name = Name;
foreach (JSColumn col in Columns)
{
_Table.Columns.Add(col.GetColumn());
}
return(_Table);
}
public override string WriteRemoveColumn(SQColumn column)
{
// add foreign key to from property
SQTable table = GetTable(column.Table.Name);
table.Columns.Remove(table.GetColumnByName(column.Name));
// get list of columns
List <string> columns = new List <string>();
foreach (SQColumn col in table.Columns)
{
columns.Add(col.Name);
}
return(WriteUpdateTable(table, columns, columns));
}
public override string WriteAddForeignKey(SQColumn from, SQColumn to)
{
// add foreign key to from property
SQTable table = GetTable(from.Table.Name);
((SQColumn_SQLite)table.GetColumnByName(from.Name)).ForeignKey = to;
// get list of columns
List <string> columns = new List <string>();
foreach (SQColumn col in table.Columns)
{
columns.Add(col.Name);
}
return(WriteUpdateTable(table, columns, columns));
}
public override string WriteCreateTable(SQTable table)
{
// create the table
StringBuilder sb = new StringBuilder();
sb.AppendLine("CREATE TABLE " + table.Name);
sb.AppendLine("(");
for (int i = 0; i < table.Columns.Count; i++)
{
SQColumn col = table.Columns[i];
sb.AppendLine((i > 0 ? "\t," : "\t") + GetCreateColumnText(col));
if (col.IsPrimary)
{
sb.AppendLine("\t,PRIMARY KEY (" + col.Name + ")");
}
}
sb.AppendLine(")");
sb.AppendLine("ENGINE=INNODB");
return(sb.ToString());
}
public ActionResult CreateScript()
{
string def = Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
dg.Relate();
SQAdapter adp = (Request["Lang"] == "MySQL") ? (SQAdapter) new MySQLAdapter()
: (Request["Lang"] == "SQL Server") ? (SQAdapter) new SQLServerAdapter()
: (Request["Lang"] == "PostgreSQL") ? (SQAdapter) new PostgreSQLAdapter()
: (SQAdapter) new SQLiteAdapter();
StringBuilder sb = new StringBuilder();
foreach (JSTable table in dg.Tables)
{
SQTable t = table.GetTable();
sb.AppendLine("-- Create Table: " + t.Name);
sb.AppendLine("--------------------------------------------------------------------------------");
sb.Append(adp.WriteCreateTable(t));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
foreach (JSFKey fk in dg.FKeys)
{
sb.AppendLine(string.Format("-- Create Foreign Key: {0}.{1} -> {2}.{3}", fk.From.GetTable().Name, fk.From.GetColumn().Name, fk.To.GetTable().Name, fk.To.GetColumn().Name));
sb.Append(adp.WriteAddForeignKey(fk.From.GetColumn().GetColumn(), fk.To.GetColumn().GetColumn()));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
return(Content(sb.ToString()));
}
public virtual void CreateTable(SQTable table)
{
ExecuteQuery(WriteCreateTable(table));
}
public abstract string WriteCreateTable(SQTable table);
public override void CreateTable(SQTable table)
{
string[] sqls = WriteCreateTable(table).Split(new string[] { "\r\nGO\r\n" }, StringSplitOptions.RemoveEmptyEntries);
ExecuteQuery(sqls[0]);
ExecuteQuery(sqls[1]);
}
private void HandleRequest(string command)
{
switch (command)
{
case "PreserveSession":
{
Respond(User != null ? "true" : "false");
}
break;
case "GetCurrentUser":
{
Respond(User);
}
break;
case "GetDiagrams":
{
List <DGDiagram> dgs = DGDiagram.ByUserID(UserID);
Respond(dgs);
}
break;
case "GetSharedDiagrams":
{
if (UserID != 0)
{
Respond(DGVSharedDiagram.ByEmail(User.Name));
}
else
{
Respond("ERROR:Not Logged In");
}
}
break;
case "GetSharing":
{
if (UserID != 0)
{
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg != null && dg.UserID == UserID)
{
Respond(DGShare.ByDiagram(diagramID));
}
}
}
break;
case "ShareDiagram":
{
if (UserID != 0)
{
string email = _Ctx.Request["Email"];
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg.UserID == UserID)
{
dg.Share(email, DGSharePermisson.View);
Respond(DGShare.ByDiagram(diagramID));
}
else
{
Respond("ERROR:Permission Denied");
}
}
}
break;
case "UnshareDiagram":
{
if (UserID != 0)
{
string email = _Ctx.Request["Email"];
Int64 diagramID = Convert.ToInt64(_Ctx.Request["DiagramID"]);
DGDiagram dg = DGDiagram.ByID(diagramID);
if (dg.UserID == UserID)
{
dg.Unshare(email);
Respond(DGShare.ByDiagram(diagramID));
}
}
}
break;
case "SaveDiagram":
{
string def = _Ctx.Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
DGDiagram diagram = DGDiagram.ByUserID(UserID).ByID(dg.ID);
if (diagram == null)
{
diagram = new DGDiagram();
diagram.UserID = UserID;
}
if (diagram.UserID != UserID)
{
Respond("ERROR:Permission Denied");
}
else
{
diagram.Name = dg.Name;
diagram.PrimarySnapshot.DiagramData = def;
diagram.Save();
Respond(diagram.ID);
}
}
break;
case "TogglePublicAccess":
{
DGDiagram dg = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (dg != null)
{
if (dg.UserID == UserID)
{
dg.AllowPublicAccess = !dg.AllowPublicAccess;
dg.Save();
Respond(dg.AllowPublicAccess);
}
else
{
Respond("ERROR:Permission Denied");
}
}
}
break;
case "GetDiagram":
{
if (_Ctx.Request["ID"] == "WelcomeDiagram")
{
string email = ConfigurationManager.AppSettings["WelcomeDiagram Email"];
DGUser user = DGUser.ByName(email);
if (user != null)
{
DGDiagram dg = DGDiagram.ByUserID(user.ID).ByName(ConfigurationManager.AppSettings["WelcomeDiagram Name"]);
Respond(dg);
}
}
else
{
DGDiagram dg = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (dg != null)
{
if (dg.UserID == UserID ||
dg.AllowPublicAccess ||
DGShare.ByEmailAndDiagram(User.Name, dg.ID).Count > 0)
{
Respond(dg);
}
}
}
}
break;
case "DeleteDiagram":
{
DGDiagram dg = DGDiagram.ByUserID(UserID).ByID(Convert.ToInt64(_Ctx.Request["ID"]));
dg.Delete();
Respond("OK");
}
break;
case "AddSnapshot":
{
string def = _Ctx.Request["Snapshot"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
// TODO: check user
DGDiagram diagram = DGDiagram.ByUserID(UserID).ByID(Convert.ToInt64(_Ctx.Request["DGID"]));
if (diagram != null)
{
DGSnapshot snapshot = new DGSnapshot()
{
Name = _Ctx.Request["Name"],
DiagramID = diagram.ID,
IsDefault = false,
DiagramData = def
};
snapshot.Save();
Respond(diagram.ID);
}
}
break;
case "GetSnapshots":
{
DGDiagram diagram = DGDiagram.ByID(Convert.ToInt64(_Ctx.Request["DGID"])) ?? new DGDiagram();
if (diagram.UserID == UserID ||
DGShare.ByEmailAndDiagram(User.Name, diagram.ID).Count > 0)
{
Respond(DGSnapshot.ByDiagramID(diagram.ID));
}
}
break;
case "DeleteSnapshot":
{
DGSnapshot snapshot = DGSnapshot.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (snapshot != null)
{
if (snapshot.Diagram.UserID == User.ID)
{
snapshot.Delete();
Respond("OK");
}
else
{
Respond("ERROR:Permission Denied");
}
}
else
{
Respond("ERROR:Bad Input ID");
}
}
break;
case "LoadSnapshot":
{
DGSnapshot snapshot = DGSnapshot.ByID(Convert.ToInt64(_Ctx.Request["ID"]));
if (snapshot.Diagram.UserID == UserID ||
DGShare.ByEmailAndDiagram(User.Name, snapshot.Diagram.ID).Count > 0)
{
Respond(snapshot);
}
else
{
Respond("ERROR:Permission Denied");
}
}
break;
case "CreateScript":
{
string def = _Ctx.Request["Diagram"];
JavaScriptSerializer ser = new JavaScriptSerializer();
JSDiagram dg = ser.Deserialize <JSDiagram>(def);
dg.Relate();
SQAdapter adp = (_Ctx.Request["Lang"] == "MySQL") ? (SQAdapter) new MySQLAdapter()
: (_Ctx.Request["Lang"] == "SQL Server") ? (SQAdapter) new SQLServerAdapter()
: (_Ctx.Request["Lang"] == "PostgreSQL") ? (SQAdapter) new PostgreSQLAdapter()
: (SQAdapter) new SQLiteAdapter();
StringBuilder sb = new StringBuilder();
foreach (JSTable table in dg.Tables)
{
SQTable t = table.GetTable();
sb.AppendLine("-- Create Table: " + t.Name);
sb.AppendLine("--------------------------------------------------------------------------------");
sb.Append(adp.WriteCreateTable(t));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
foreach (JSFKey fk in dg.FKeys)
{
sb.AppendLine(string.Format("-- Create Foreign Key: {0}.{1} -> {2}.{3}", fk.From.GetTable().Name, fk.From.GetColumn().Name, fk.To.GetTable().Name, fk.To.GetColumn().Name));
sb.Append(adp.WriteAddForeignKey(fk.From.GetColumn().GetColumn(), fk.To.GetColumn().GetColumn()));
sb.AppendLine("");
sb.AppendLine("");
sb.AppendLine("");
}
Respond(sb.ToString());
}
break;
case "DoChangeScript":
{
}
break;
}
}
public override SQTable GetTable(string name)
{
string varTable = CreateVariable("Table");
string varPK = CreateVariable("PK");
SQSelectQuery q = new SQSelectQuery();
q.Columns.AddRange(new List <SQAliasableObject>
{
new SQAliasableObject("cols.COLUMN_NAME"),
new SQAliasableObject("IS_NULLABLE"),
new SQAliasableObject("DATA_TYPE"),
new SQAliasableObject("CHARACTER_MAXIMUM_LENGTH"),
new SQAliasableObject("NUMERIC_PRECISION"),
new SQAliasableObject("NUMERIC_SCALE"),
new SQAliasableObject("EXTRA", "IS_IDENTITY"),
new SQAliasableObject("CONSTRAINT_TYPE")
});
q.From = new SQFromClause(new SQFromTable("INFORMATION_SCHEMA.COLUMNS", "cols")
{
Join = new SQJoin("INFORMATION_SCHEMA.KEY_COLUMN_USAGE", "tuse")
{
JoinType = SQJoinTypes.Left,
Predicate = new SQCondition("tuse.COLUMN_NAME", SQRelationOperators.Equal, "cols.COLUMN_NAME")
.And("tuse.TABLE_NAME", SQRelationOperators.Equal, "cols.TABLE_NAME"),
Join = new SQJoin("INFORMATION_SCHEMA.TABLE_CONSTRAINTS", "tcons")
{
JoinType = SQJoinTypes.Left,
Predicate = new SQCondition("tcons.CONSTRAINT_NAME", SQRelationOperators.Equal, "tuse.CONSTRAINT_NAME")
.And("tcons.TABLE_NAME", SQRelationOperators.Equal, "cols.TABLE_NAME")
.And("tcons.CONSTRAINT_TYPE", SQRelationOperators.Equal, varPK)
}
}
});
q.Condition = new SQCondition("cols.TABLE_NAME", SQRelationOperators.Equal, varTable);
q.Parameters.Add(new SQParameter(varTable, name));
q.Parameters.Add(new SQParameter(varPK, "PRIMARY KEY"));
SQSelectResult selres = Select(q);
MySqlDataReader rdr = (MySqlDataReader)selres.Reader;
try
{
if (rdr.HasRows)
{
SQTable res = new SQTable()
{
Name = name
};
while (rdr.Read())
{
res.Columns.Add(new SQColumn()
{
Table = res,
Name = rdr.GetString(0),
DataType = GetTypeFromName(rdr.GetString(2)),
Length = rdr.IsDBNull(3) ? 0 : rdr.GetInt64(3),
Nullable = rdr.GetString(1) == "YES",
Precision = rdr.IsDBNull(4) ? 0 : Convert.ToInt32(rdr.GetInt64(4)),
Scale = rdr.IsDBNull(5) ? 0 : rdr.GetInt32(5),
IsIdentity = rdr.GetString(6).ToLower().Contains("auto_increment"),
IsPrimary = rdr.IsDBNull(7) ? false : rdr.GetString(7) == "PRIMARY KEY"
});
}
return(res);
}
}
finally
{
selres.Close();
}
return(null);
}
static void Main(string[] args)
{
// get an adapter
SQAdapter adp = !string.IsNullOrEmpty(Settings.Default.SQLServerConn) ? (SQAdapter) new SQLServerAdapter(Settings.Default.SQLServerConn)
: !string.IsNullOrEmpty(Settings.Default.MySQLConn) ? (SQAdapter) new MySQLAdapter(Settings.Default.MySQLConn)
: null;
if (adp == null)
{
return;
}
// Create Table 1
// -------------------------------------------------------
Console.WriteLine("Creating table: FamousQuote");
Console.WriteLine("ID Int64");
Console.WriteLine("FamousPersonID Int64");
Console.WriteLine("Quote String(500)");
SQTable quote = new SQTable()
{
Name = "FamousQuote",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
},
new SQColumn()
{
Name = "FamousPersonID", DataType = SQDataTypes.Int64, Nullable = false
},
new SQColumn()
{
Name = "Quote", DataType = SQDataTypes.String, Length = 500
}
}
};
// delete table if it already exists
if (adp.GetTable(quote.Name) != null)
{
Console.WriteLine("Table '" + quote.Name + "' exists. Deleting...");
adp.RemoveTable(quote.Name);
}
adp.CreateTable(quote);
Console.WriteLine("Table Created. ");
// Create Table 2
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Creating table: FamousPerson");
Console.WriteLine("ID Int64");
Console.WriteLine("FirstName String(50)");
Console.WriteLine("LastName String(50)");
SQTable person = new SQTable()
{
Name = "FamousPerson",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "ID", DataType = SQDataTypes.Int64, IsPrimary = true, IsIdentity = true
},
new SQColumn()
{
Name = "FirstName", DataType = SQDataTypes.String, Length = 50
},
new SQColumn()
{
Name = "LastName", DataType = SQDataTypes.String, Length = 50
}
}
};
// delete table if it already exists
if (adp.GetTable(person.Name) != null)
{
Console.WriteLine("Table '" + person.Name + "' exists. Deleting...");
adp.RemoveTable(person.Name);
}
adp.CreateTable(person);
Console.WriteLine("Table Created. ");
// add a foreign key
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Adding a foreign key.");
Console.WriteLine("FamousQuote.FamousPersonID -> FamousPerson.ID");
adp.AddForeignKey(quote.GetColumnByName("FamousPersonID"), person.GetColumnByName("ID"));
// make a couple of insert query objects
// -------------------------------------------------------
string varFirstName = adp.CreateVariable("FirstName");
string varLastName = adp.CreateVariable("LastName");
SQInsertQuery personInsert = new SQInsertQuery()
{
Table = new SQAliasableObject(person.Name),
ReturnID = true,
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FirstName", varFirstName),
new SQSetQueryPair("LastName", varLastName),
}
};
string varPersonID = adp.CreateVariable("PersonID");
string varQuote = adp.CreateVariable("Quote");
SQInsertQuery quoteInsert = new SQInsertQuery()
{
Table = new SQAliasableObject(quote.Name),
ReturnID = false,
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FamousPersonID", varPersonID),
new SQSetQueryPair("Quote", varQuote),
}
};
// add some data using insert objects
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Inserting Data");
Console.WriteLine("Inserting Person. ");
personInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varFirstName, "I"),
new SQParameter(varLastName, "Asimov")
};
Int64 id = personInsert.ExecuteReturnID <Int64>(adp);
Console.WriteLine("Inserting Quote. ");
quoteInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varPersonID, id),
new SQParameter(varQuote, "Never let your sense of morals get in the way of doing what's right. ")
};
quoteInsert.Execute(adp);
Console.WriteLine("Inserting Quote. ");
quoteInsert.Parameters = new List <SQParameter>()
{
new SQParameter(varPersonID, id),
new SQParameter(varQuote, "I do not fear computers. I fear the lack of them. ")
};
quoteInsert.Execute(adp);
// update data
// -------------------------------------------------------
new SQUpdateQuery()
{
UpdateTable = new SQAliasableObject(person.Name),
SetPairs = new List <SQSetQueryPair>()
{
new SQSetQueryPair("FirstName", varFirstName)
},
Condition = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
Parameters = new List <SQParameter>()
{
new SQParameter(varFirstName, "Isaac"),
new SQParameter(varLastName, "Asimov")
}
}.Execute(adp);
// Select data
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Selecting quotes by Asimov. ");
SQSelectQuery select = new SQSelectQuery()
{
From = new SQFromClause(new SQFromTable(quote.Name, "q")
{
Join = new SQJoin(person.Name, "p")
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
}
}),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("q.Quote")
},
Condition = new SQCondition("p.LastName", SQRelationOperators.Like, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
};
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("SQL: ");
Console.Write(select.Write(adp));
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Result: ");
// get datatable instead of reader. this closes the reader
// automatically
foreach (DataRow row in select.Execute(adp).GetDataTable().Rows)
{
Console.WriteLine(row[0]);
}
// Select data joinless
// -------------------------------------------------------
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Selecting quotes by Asimov. Joinless. ");
select = new SQSelectQuery()
{
From = new SQFromClause(
new SQFromTable(quote.Name, "q"),
new SQFromTable(person.Name, "p")
),
Columns = new List <SQAliasableObject>
{
new SQAliasableObject("q.Quote")
},
Condition = new SQCondition("p.ID", SQRelationOperators.Equal, "q.FamousPersonID")
.And("p.LastName", SQRelationOperators.Like, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
};
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("SQL: ");
Console.Write(select.Write(adp));
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Result: ");
SQSelectResult res = select.Execute(adp);
while (res.Reader.Read())
{
Console.WriteLine(res.Reader.GetValue(0));
}
res.Close();
// Delete data
// -------------------------------------------------------
new SQDeleteQuery()
{
DeleteTable = new SQAliasableObject(quote.Name),
Join = new SQJoin(person.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("FamousPersonID", SQRelationOperators.Equal, "FamousPerson.ID")
},
Condition = new SQCondition("LastName", SQRelationOperators.Equal, varLastName),
Parameters = new List <SQParameter>
{
new SQParameter(varLastName, "Asimov")
}
}.Execute(adp);
// Remove created tables
// -------------------------------------------------------
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Removing Table '" + quote.Name + "'");
adp.RemoveTable(quote.Name);
Console.WriteLine("Removing Table '" + person.Name + "'");
adp.RemoveTable(person.Name);
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
public SchemaTable(Type t)
{
Columns = new List <SchemaColumn>();
TableHints = new List <SchemaHintTable>();
FKeyHints = new List <SchemaHintForeignKey>();
ColumnHints = new List <SchemaHintColumn>();
ObjectType = t;
// gather all hints first from properties, then from those
// declared on the class, TODO: last gather hints from a
// delegate call allowing the hints on classes onto which
// attributes cannot be added
#region Gather Hints
foreach (PropertyInfo pi in t.GetProperties())
{
foreach (Attribute att in Attribute.GetCustomAttributes(pi))
{
if (att is SchemaHintColumn)
{
((SchemaHintColumn)att).PropertyName = pi.Name;
ColumnHints.Add((SchemaHintColumn)att);
}
else if (att is SchemaHintForeignKey)
{
SchemaHintForeignKey fk = (SchemaHintForeignKey)att;
if (string.IsNullOrEmpty(fk.PropertyName))
{
fk.PropertyName = pi.Name;
}
FKeyHints.Add((SchemaHintForeignKey)att);
}
}
}
foreach (Attribute att in Attribute.GetCustomAttributes(t))
{
if (att is SchemaHintTable)
{
TableHints.Add((SchemaHintTable)att);
}
else if (att is SchemaHintColumn)
{
ColumnHints.Add((SchemaHintColumn)att);
}
else if (att is SchemaHintForeignKey)
{
FKeyHints.Add((SchemaHintForeignKey)att);
}
}
#endregion
// construct table using the type and the table hints
Table = new SQTable()
{
Name = t.Name
};
foreach (SchemaHintTable hint in TableHints)
{
if (!string.IsNullOrEmpty(hint.TableName))
{
Table.Name = hint.TableName;
}
if (hint.LockSchema.HasValue)
{
LockSchema = hint.LockSchema.Value;
}
if (hint.NoInheritedProperties.HasValue)
{
NoInheritedProperties = hint.NoInheritedProperties.Value;
}
}
foreach (PropertyInfo pi in t.GetProperties())
{
if (NoInheritedProperties && pi.DeclaringType != t)
{
continue;
}
// collect all column hints specific to this property to be
// passed in the schemahintcol constructor
List <SchemaHintColumn> colHints = new List <SchemaHintColumn>();
foreach (SchemaHintColumn colHint in ColumnHints)
{
if (colHint.PropertyName == pi.Name)
{
colHints.Add(colHint);
}
}
// create the schemacolumn and if a db column could be created
// for it, use add it to the table.
SchemaColumn col = new SchemaColumn(this, colHints, pi);
if (col.Column != null)
{
Columns.Add(col);
Table.Columns.Add(col.Column);
}
}
}
public void CreateTable(SQTable table)
{
_Adapter.CreateTable(table);
}
static void TestAdapter(SQAdapter adp, List <string[]> data)
{
SQTable tMake, tModel, tBodyType, tTrans, tVehicle;
#region Create Tables
SQTable[] tables = new SQTable[]
{
tMake = new SQTable()
{
Name = "Make",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "MK_ID", DataType = SQDataTypes.Int64, IsPrimary = true
},
new SQColumn()
{
Name = "MK_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tModel = new SQTable()
{
Name = "Model",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "MD_ID", DataType = SQDataTypes.Int64, IsPrimary = true
},
new SQColumn()
{
Name = "MD_MKID", DataType = SQDataTypes.Int64
},
new SQColumn()
{
Name = "MD_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tBodyType = new SQTable()
{
Name = "BodyType",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "BT_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
},
new SQColumn()
{
Name = "BT_Name", DataType = SQDataTypes.String, Length = 250
},
new SQColumn()
{
Name = "BT_Dummy", DataType = SQDataTypes.String, Length = 36
}
}
},
tTrans = new SQTable()
{
Name = "TransmissionType",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "TR_ID", DataType = SQDataTypes.String, Length = 20, IsPrimary = true
},
new SQColumn()
{
Name = "TR_Name", DataType = SQDataTypes.String, Length = 250
}
}
},
tVehicle = new SQTable()
{
Name = "TestedVehicle",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "TV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
},
new SQColumn()
{
Name = "TV_VIN", DataType = SQDataTypes.String, Length = 25
},
new SQColumn()
{
Name = "TV_MDID", DataType = SQDataTypes.Int64
},
new SQColumn()
{
Name = "TV_BTID", DataType = SQDataTypes.String, Length = 20
},
new SQColumn()
{
Name = "TV_TRID", DataType = SQDataTypes.String, Length = 20
},
new SQColumn()
{
Name = "TV_Year", DataType = SQDataTypes.Int32
}
}
}
};
Log.Info("------------------------------------------------------");
Log.Info("Creating Tables");
foreach (SQTable t in tables.Reverse <SQTable>())
{
if (adp.GetTable(t.Name) != null)
{
Log.Info("Table '" + t.Name + "' exists. Deleting...");
Log.Info("SQL: " + adp.WriteRemoveTable(t.Name));
adp.RemoveTable(t.Name);
}
Log.Info("Creating table '" + t.Name + "'");
Log.Info("SQL: " + adp.WriteCreateTable(t));
adp.CreateTable(t);
}
#endregion
#region Add Foreign Keys
Log.Info("------------------------------------------------------");
Log.Info("Adding Foreign Keys");
Log.Info("Creating Foreign Key Model -> Make");
adp.AddForeignKey(tModel.GetColumnByName("MD_MKID"), tMake.GetColumnByName("MK_ID"));
Log.Info("Creating Foreign Key Vehicle -> Model");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_MDID"), tModel.GetColumnByName("MD_ID"));
Log.Info("Creating Foreign Key Vehicle -> Body");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_BTID"), tBodyType.GetColumnByName("BT_ID"));
Log.Info("Creating Foreign Key Vehicle -> Transmission");
adp.AddForeignKey(tVehicle.GetColumnByName("TV_TRID"), tTrans.GetColumnByName("TR_ID"));
#endregion
#region Import Data
Log.Info("------------------------------------------------------");
Log.Info("Importing Data");
List <string> makeIDs = new List <string>();
List <string> modelIDs = new List <string>();
List <string> bodyIDs = new List <string>();
List <string> transIDs = new List <string>();
string varMakeID = adp.CreateVariable("MakeID");
string varMake = adp.CreateVariable("Make");
string varModelID = adp.CreateVariable("ModelID");
string varModel = adp.CreateVariable("Model");
string varTransID = adp.CreateVariable("TransID");
string varTrans = adp.CreateVariable("Trans");
string varBodyID = adp.CreateVariable("BodyID");
string varBody = adp.CreateVariable("Body");
string varVIN = adp.CreateVariable("VIN");
string varYear = adp.CreateVariable("Year");
string varDummy = adp.CreateVariable("Dummy");
SQInsertQuery iMake = new SQInsertQuery()
{
Table = new SQAliasableObject(tMake.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("MK_ID", varMakeID),
new SQSetQueryPair("MK_Name", varMake)
},
Parameters = new List <SQParameter> {
new SQParameter(varMakeID, ""), new SQParameter(varMake, "")
}
};
SQInsertQuery iModel = new SQInsertQuery()
{
Table = new SQAliasableObject(tModel.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("MD_ID", varModelID),
new SQSetQueryPair("MD_Name", varModel),
new SQSetQueryPair("MD_MKID", varMakeID)
},
Parameters = new List <SQParameter> {
new SQParameter(varModelID, ""), new SQParameter(varModel, ""), new SQParameter(varMakeID, "")
}
};
SQInsertQuery iBodyType = new SQInsertQuery()
{
Table = new SQAliasableObject(tBodyType.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("BT_ID", varBodyID),
new SQSetQueryPair("BT_Name", varBody),
new SQSetQueryPair("BT_Dummy", varDummy)
},
Parameters = new List <SQParameter> {
new SQParameter(varBodyID, ""), new SQParameter(varBody, ""), new SQParameter(varDummy, "")
}
};
SQInsertQuery iTrans = new SQInsertQuery()
{
Table = new SQAliasableObject(tTrans.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("TR_ID", varTransID),
new SQSetQueryPair("TR_Name", varTrans)
},
Parameters = new List <SQParameter> {
new SQParameter(varTransID, ""), new SQParameter(varTrans, "")
}
};
SQInsertQuery iVehicle = new SQInsertQuery()
{
Table = new SQAliasableObject(tVehicle.Name),
SetPairs = new List <SQSetQueryPair>
{
new SQSetQueryPair("TV_VIN", varVIN),
new SQSetQueryPair("TV_MDID", varModelID),
new SQSetQueryPair("TV_BTID", varBodyID),
new SQSetQueryPair("TV_TRID", varTransID),
new SQSetQueryPair("TV_Year", varYear)
},
Parameters = new List <SQParameter> {
new SQParameter(varVIN, ""),
new SQParameter(varModelID, ""),
new SQParameter(varBodyID, ""),
new SQParameter(varTransID, ""),
new SQParameter(varYear, "")
}
};
Log.Info("------------------------------------------------------");
Log.Info("Testing Inserts...");
Log.Info("Make SQL: " + iMake.Write(adp));
Log.Info("Model SQL: " + iModel.Write(adp));
Log.Info("BodyType SQL: " + iBodyType.Write(adp));
Log.Info("Transmission SQL: " + iTrans.Write(adp));
Log.Info("TestedVehicle SQL: " + iVehicle.Write(adp));
int vehicleCount = 0;
SQTransaction trn = adp.OpenTransaction();
foreach (string[] dataline in data)
{
string makeID = dataline[2];
string make = dataline[3];
// model id's are unique with respect to the make
string modelID = makeID + dataline[4];
string model = dataline[5];
string transID = dataline[14];
string trans = dataline[15];
string bodyID = dataline[8];
string body = dataline[9];
string VIN = dataline[10];
string year = dataline[6];
int numYear;
// if there's no year, it's not a valid vehicle. skip this line
if (!int.TryParse(year, out numYear) || numYear <= 0)
{
continue;
}
long numMakeID = Convert.ToInt64(makeID.TrimStart('0'));
long numModelID = Convert.ToInt64(modelID.TrimStart('0'));
if (!makeIDs.Contains(makeID))
{
makeIDs.Add(makeID);
iMake.Parameters[0].Value = numMakeID;
iMake.Parameters[1].Value = make;
iMake.Execute(trn);
}
if (!modelIDs.Contains(modelID))
{
modelIDs.Add(modelID);
iModel.Parameters[0].Value = numModelID;
iModel.Parameters[1].Value = model;
iModel.Parameters[2].Value = numMakeID;
iModel.Execute(trn);
}
if (!transIDs.Contains(transID))
{
transIDs.Add(transID);
iTrans.Parameters[0].Value = transID;
iTrans.Parameters[1].Value = trans;
iTrans.Execute(trn);
}
if (!bodyIDs.Contains(bodyID))
{
bodyIDs.Add(bodyID);
iBodyType.Parameters[0].Value = bodyID;
iBodyType.Parameters[1].Value = body;
iBodyType.Parameters[2].Value = Guid.NewGuid().ToString();
iBodyType.Execute(trn);
}
iVehicle.Parameters[0].Value = VIN;
iVehicle.Parameters[1].Value = numModelID;
iVehicle.Parameters[2].Value = bodyID;
iVehicle.Parameters[3].Value = transID;
iVehicle.Parameters[4].Value = numYear;
long id = iVehicle.ExecuteReturnID <Int64>(trn);
vehicleCount++;
if (vehicleCount > 0 && vehicleCount % 500 == 0)
{
Log.Info("Records Inserted: " + (vehicleCount + bodyIDs.Count + transIDs.Count + modelIDs.Count + makeIDs.Count));
}
}
trn.Commit();
Log.Info("------------------------------------------------------");
Log.Info(string.Format(@"Insert Test Complete.
Makes: {0};
Models: {1};
BodyTypes: {2};
TransmissionTypes: {3};
TestedVehicle: {4}; ", makeIDs.Count, modelIDs.Count, bodyIDs.Count, transIDs.Count, vehicleCount));
#endregion
Log.Info("------------------------------------------------------");
Log.Info("Select Test");
SQSelectQuery q = new SQSelectQuery()
{
From = new SQFromClause(
new SQFromTable(tVehicle.Name),
new SQFromTable(tModel.Name),
new SQFromTable(tTrans.Name),
new SQFromTable(tBodyType.Name),
new SQFromTable(tMake.Name)
),
Columns = new List <SQAliasableObject> {
new SQAliasableObject("Model.*, make.*")
},
Condition = new SQConditionGroup(
new SQCondition("MD_ID", SQRelationOperators.Equal, "TV_MDID")
.And("BT_ID", SQRelationOperators.Equal, "TV_BTID")
.And("MD_MKID", SQRelationOperators.Equal, "MK_ID")
.And("TV_TRID", SQRelationOperators.Equal, "TR_ID"))
};
Log.Info("Selecting with joinless joins types");
Log.Info("SQL: " + q.Write(adp));
DataTable dt = new DataTable();
SQSelectResult res = q.Execute(adp);
int count = 0;
while (res.Reader.Read())
{
count++;
}
res.Close();
Log.Info("Rows Found: " + count);
Log.Info("------------------------------------------------------");
Log.Info("Rename Column");
Log.Info("Rename BT_Dummy to BT_DummyOK");
SQColumn dummyColumn = tBodyType.GetColumnByName("BT_Dummy");
string oldname = dummyColumn.Name;
dummyColumn.Name = "BT_DummyOK";
Log.Info("SQL: " + adp.WriteRenameColumn(dummyColumn, oldname));
adp.RenameColumn(dummyColumn, oldname);
Log.Info("------------------------------------------------------");
Log.Info("Remove Column");
Log.Info("Removing column BT_DummyOK");
Log.Info("SQL: " + adp.WriteRemoveColumn(dummyColumn));
adp.RemoveColumn(dummyColumn);
Log.Info("------------------------------------------------------");
Log.Info("Insert From Test");
SQTable tVehicleList = new SQTable()
{
Name = "SimpleVehicle",
Columns = new SQColumnList()
{
new SQColumn()
{
Name = "SV_ID", DataType = SQDataTypes.Int64, IsIdentity = true, IsPrimary = true
},
new SQColumn()
{
Name = "SV_VIN", DataType = SQDataTypes.String, Length = 25
},
new SQColumn()
{
Name = "SV_Year", DataType = SQDataTypes.Int32
},
new SQColumn()
{
Name = "SV_Make", DataType = SQDataTypes.String, Length = 250
},
new SQColumn()
{
Name = "SV_Model", DataType = SQDataTypes.String, Length = 250
}
}
};
Log.Info("Create new table");
if (adp.GetTable(tVehicleList.Name) != null)
{
Log.Info("Table '" + tVehicleList.Name + "' exists. Deleting...");
Log.Info("SQL: " + adp.WriteRemoveTable(tVehicleList.Name));
adp.RemoveTable(tVehicleList.Name);
}
Log.Info("Creating table '" + tVehicleList.Name + "'");
Log.Info("SQL: " + adp.WriteCreateTable(tVehicleList));
adp.CreateTable(tVehicleList);
SQInsertFromQuery insertFrom = new SQInsertFromQuery(new SQAliasableObject(tVehicleList.Name),
new string[] { "SV_VIN", "SV_Year", "SV_Make", "SV_Model" },
new string[] { "TV_VIN", "TV_Year", "MK_Name", "MD_Name" })
{
From = new SQFromClause(
new SQFromTable(tVehicle.Name)
{
Join = new SQJoin(tModel.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("TV_MDID", SQRelationOperators.Equal, "MD_ID"),
Join = new SQJoin(tMake.Name)
{
JoinType = SQJoinTypes.Inner,
Predicate = new SQCondition("MD_MKID", SQRelationOperators.Equal, "MK_ID")
}
}
}),
Condition = new SQCondition("TV_Year", SQRelationOperators.GreaterThanOrEqual, varYear),
Parameters = new List <SQParameter> {
new SQParameter(varYear, 1990)
}
};
Log.Info("Do Insert From");
Log.Info("SQL: " + insertFrom.Write(adp));
insertFrom.Execute(adp);
q = new SQSelectQuery()
{
Columns = new List <SQAliasableObject> {
new SQAliasableObject(SQAggregates.COUNT.Create(adp, "*"), "RecordCount")
},
From = new SQFromClause(new SQFromTable(tVehicleList.Name))
};
Log.Info("Get record count for " + tVehicleList.Name);
Log.Info("SQL: " + q.Write(adp));
res = q.Execute(adp);
if (res.Reader.Read())
{
Log.Info("Count: " + res.Reader.GetValue(0));
}
res.Close();
Log.Info("------------------------------------------------------");
Log.Info("Rollback Test");
trn = adp.OpenTransaction();
SQDeleteQuery delete = new SQDeleteQuery()
{
DeleteTable = new SQAliasableObject(tVehicleList.Name),
Condition = new SQCondition("SV_Year", SQRelationOperators.LessThan, varYear),
Parameters = new List <SQParameter> {
new SQParameter(varYear, 2000)
}
};
Log.Info("Deleting data from " + tVehicleList.Name);
Log.Info("SQL: " + delete.Write(adp));
delete.Execute(trn);
res = q.Execute(trn);
if (res.Reader.Read())
{
Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
}
res.Close();
Log.Info("Rolling Back");
trn.RollBack();
res = q.Execute(adp);
if (res.Reader.Read())
{
Log.Info("Get record count for " + tVehicleList.Name + ": " + res.Reader.GetValue(0));
}
res.Close();
}
