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