///<summary>For example, might returns "0", "", or "01-JAN-0001" for cols with types OdDbType.Byte, OdDbType.Text, and OdDbType.DateTime respectively.</summary> private static string GetOracleBlankData(DbSchemaCol col) { switch (col.DataType) { case OdDbType.Bool: case OdDbType.Byte: case OdDbType.Currency: case OdDbType.Float: case OdDbType.Enum: case OdDbType.Int: case OdDbType.Long: return("0"); case OdDbType.Date: case OdDbType.DateTime: case OdDbType.TimeOfDay: return("TO_DATE('0001-01-01','YYYY-MM-DD')"); case OdDbType.DateTimeStamp: //timestamp is stored as a date and trigger combination return(null); case OdDbType.Text: case OdDbType.TimeSpan: case OdDbType.VarChar255: return(null); //stored as NULL, default: throw new ApplicationException("type not found"); } }
///<summary>For example, might returns "0", "", or "01-01-0001" for cols with types OdDbType.Byte, OdDbType.Text, and OdDbType.DateTime respectively.</summary> private static string GetMySqlBlankData(DbSchemaCol col) { switch (col.DataType) { case OdDbType.Bool: case OdDbType.Byte: case OdDbType.Currency: case OdDbType.Enum: case OdDbType.Float: case OdDbType.Int: case OdDbType.Long: return("0"); case OdDbType.Date: return("'0001-01-01'"); //sets date to 01 JAN 2001, 00:00:00 case OdDbType.DateTimeStamp: return("NOW()"); case OdDbType.DateTime: return("'0001-01-01 00:00:00'"); case OdDbType.TimeOfDay: case OdDbType.TimeSpan: return("'00:00:00'"); case OdDbType.Text: case OdDbType.VarChar255: return("\"\""); //sets to empty string default: throw new ApplicationException("type not found"); } }
///<summary>For example, might return "NUMBER(11) NOT NULL".</summary> private static string GetOracleType(DbSchemaCol col) { switch (col.DataType) { case OdDbType.Bool: return("number(3)"); case OdDbType.Byte: return("number(3)"); case OdDbType.Currency: return("number(38,8)"); case OdDbType.Date: return("date"); case OdDbType.DateTime: return("date"); case OdDbType.DateTimeStamp: //also requires trigger, trigger code is automatically created above. return("timestamp"); case OdDbType.Float: return("number(38,8)"); case OdDbType.Enum: return("number(3)"); case OdDbType.Int: return("number(11)"); case OdDbType.Long: return("number(20)"); case OdDbType.Text: if (col.TextSize == TextSizeMySqlOracle.Small) { return("varchar2(4000)"); } else //textSize == medium or large { return("clob"); } case OdDbType.TimeOfDay: return("date"); case OdDbType.TimeSpan: return("varchar2(255)"); case OdDbType.VarChar255: return("varchar2(255)"); default: throw new ApplicationException("type not found"); } }
///<summary>Generates C# code to Add Column to table. Returns a string that contains the code to add to the convert script.</summary> ///<param name="tableName">The name of the table being modified.</param> ///<param name="col">The column that will be added. Includes information on type and name.</param> ///<param name="tabInset">The number of tabs that should be added to each of the lines of code.</param> ///<param name="doRunQuery">If set to true, the generated queries will be ran against the connected database.</param> ///<param name="tableType">The type of the C# table object.</param> public static string AddColumnEnd(string tableName, DbSchemaCol col, int tabInset, bool doRunQuery, Type tableType) { StringBuilder strb = new StringBuilder(); tb = new string('\t', tabInset); //Reset this variable everytime as it is static. //Generate the MySQL query and run it if necessary. string command = "ALTER TABLE " + tableName + " ADD " + col.ColumnName + " "; string columnDef = GetMySqlType(col) + " NOT NULL"; if (!GetMySqlBlankData(col).In("\"\"", "0")) { columnDef += " DEFAULT " + GetMySqlBlankData(col) + (GetMySqlType(col) == "timestamp"?" ON UPDATE CURRENT_TIMESTAMP":""); } command += columnDef; if (doRunQuery) { DataCore.NonQ(command); } string commandAddIndex = ""; if (col.DataType == OdDbType.Long) //Generate and run index regardless of large table status { commandAddIndex = "ALTER TABLE " + tableName + " ADD INDEX (" + col.ColumnName + ")"; if (doRunQuery) { DataCore.NonQ(commandAddIndex); } } //If there is a large table property set to true, we will use the the large table helper in the convert script. if (tableType.GetCustomAttributes <CrudTableAttribute>().Any(x => x.IsLargeTable)) { //With the AlterLargeTable method, we can add columns and new indexes at the same time. string primaryKeyColName = tableType.GetFields().First(x => x.GetCustomAttribute <CrudColumnAttribute>().IsPriKey).Name; strb.Append(rn + tb + "LargeTableHelper.AlterLargeTable(\"" + tableName + "\",\"" + primaryKeyColName + "\"," + rn + tb + "\tnew List<Tuple<string,string>> { Tuple.Create(\"" + col.ColumnName + "\",\"" + columnDef + "\") }"); //Add the parameter to LargeTableHelper to specify a new index. Index name will be the same as the column name. if (col.DataType == OdDbType.Long) { strb.Append("," + rn + tb + "\tnew List<Tuple<string,string>> { Tuple.Create(\"" + col.ColumnName + "\",\"\") }"); } strb.Append(");"); } else //Use the raw command in the convert script for adding the column and the index { strb.Append(rn + tb + "command=\"" + command + "\";"); strb.Append(rn + tb + "Db.NonQ(command);"); if (col.DataType == OdDbType.Long) //key or foreign key { strb.Append(rn + tb + "command=\"" + commandAddIndex + "\";"); strb.Append(rn + tb + "Db.NonQ(command);"); } } return(strb.ToString()); }
public static string Create() { StringBuilder strb = new StringBuilder(); //This is a stub that is to be replaced with some good code generation: strb.Append(@"using System; using System.Collections.Generic; using System.Text; namespace OpenDentBusiness { ///<summary>Please ignore this class. It's used only for testing.</summary> public class SchemaCrudTest { ///<summary>Example only</summary> public static void AddTableTempcore() { string command="""";" ); Type typeClass = typeof(SchemaTable); FieldInfo[] fields = typeClass.GetFields(); FieldInfo priKey = CrudGenHelper.GetPriKey(fields, typeClass.Name); List <FieldInfo> fieldsExceptPri = CrudGenHelper.GetFieldsExceptPriKey(fields, priKey); List <DbSchemaCol> cols = CrudQueries.GetListColumns(priKey.Name, null, fieldsExceptPri, false); strb.Append("\r\n" + CrudSchemaRaw.AddTable("tempcore", cols, 3, false, false)); strb.Append(@" } ///<summary>Example only</summary> public static void AddColumnEndClob() { string command="""";" ); DbSchemaCol col = new DbSchemaCol("ColEndClob", OdDbType.Text, TextSizeMySqlOracle.Medium); strb.Append("\r\n" + CrudSchemaRaw.AddColumnEnd("tempcore", col, 3, false, typeClass)); strb.Append(@" } ///<summary>Example only</summary> public static void AddColumnEndInt() { string command="""";" ); col = new DbSchemaCol("ColEndInt", OdDbType.Int); strb.Append("\r\n" + CrudSchemaRaw.AddColumnEnd("tempcore", col, 3, false, typeClass)); strb.Append(@" } ///<summary>Example only</summary> public static void AddColumnEndTimeStamp() { string command="""";" ); col = new DbSchemaCol("ColEndTimeStamp", OdDbType.DateTimeStamp); strb.Append("\r\n" + CrudSchemaRaw.AddColumnEnd("tempcore", col, 3, false, typeClass)); strb.Append(@" } ///<summary>Example only</summary> public static void AddIndex() { string command="""";" ); strb.Append("\r\n" + CrudSchemaRaw.AddIndex("tempcore", "ColEndInt", 3)); strb.Append(@" } ///<summary>Example only</summary> public static void DropColumn() { string command="""";" ); strb.Append("\r\n" + CrudSchemaRaw.DropColumn("tempcore", "TextLargeTest", 3)); strb.Append(@" } //AddColumnAfter //DropColumnTimeStamp //DropIndex //etc. } }"); return(strb.ToString()); }
///<summary>Writes any necessary queries to the end of the ConvertDatabase file. Usually zero or one. The convertDbFile could also be the one in the Mobile folder.</summary> ///<param name="logFileName">If this is blank, will display the results in a MessageBox, otherwise writes the results to the file.</param> public static void Write(string convertDbFile, Type typeClass, string dbName, bool isMobile, bool doRunQueries, bool doAppendToConvertDbFile, string logFileName = "") { StringBuilder strb; FieldInfo[] fields = typeClass.GetFields(); //We can't assume they are in the correct order. FieldInfo priKey = null; FieldInfo priKey1 = null; FieldInfo priKey2 = null; if (isMobile) { priKey1 = CrudGenHelper.GetPriKeyMobile1(fields, typeClass.Name); priKey2 = CrudGenHelper.GetPriKeyMobile2(fields, typeClass.Name); } else { priKey = CrudGenHelper.GetPriKey(fields, typeClass.Name); } string tablename = CrudGenHelper.GetTableName(typeClass); //in lowercase now. string priKeyParam = null; string priKeyParam1 = null; string priKeyParam2 = null; if (isMobile) { priKeyParam1 = priKey1.Name.Substring(0, 1).ToLower() + priKey1.Name.Substring(1); //lowercase initial letter. Example customerNum priKeyParam2 = priKey2.Name.Substring(0, 1).ToLower() + priKey2.Name.Substring(1); //lowercase initial letter. Example patNum } else { priKeyParam = priKey.Name.Substring(0, 1).ToLower() + priKey.Name.Substring(1); //lowercase initial letter. Example patNum } string obj = typeClass.Name.Substring(0, 1).ToLower() + typeClass.Name.Substring(1); //lowercase initial letter. Example feeSched or feeSchedm List <FieldInfo> fieldsExceptPri = null; if (isMobile) { fieldsExceptPri = CrudGenHelper.GetFieldsExceptPriKey(fields, priKey2); //for mobile, only excludes PK2 } else { fieldsExceptPri = CrudGenHelper.GetFieldsExceptPriKey(fields, priKey); } CrudSpecialColType specialType; string command = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '" + dbName + "' AND table_name = '" + tablename + "'"; if (DataCore.GetScalar(command) != "1") { if (!CrudGenHelper.IsMissingInGeneral(typeClass)) { strb = new StringBuilder(); strb.Append("This table was not found in the database:" + rn + tablename); if (doAppendToConvertDbFile) { strb.Append(rn + "Query will be found at the end of " + Path.GetFileName(convertDbFile)); } if (doRunQueries) { strb.Append(rn + "Table will be added to database."); } if (string.IsNullOrEmpty(logFileName)) { MessageBox.Show(strb.ToString()); } else { File.AppendAllText(logFileName, strb.ToString() + "\r\n"); } strb = new StringBuilder(); strb.Append(rn + rn + t4 + "/*"); List <DbSchemaCol> cols = null; if (isMobile) { cols = CrudQueries.GetListColumns(priKey1.Name, priKey2.Name, fieldsExceptPri, true); } else { cols = CrudQueries.GetListColumns(priKey.Name, null, fieldsExceptPri, false); } strb.Append("\r\n" + CrudSchemaRaw.AddTable(tablename, cols, 4, isMobile, doRunQueries)); strb.Append(rn + t4 + "*/"); if (doAppendToConvertDbFile) { File.AppendAllText(convertDbFile, strb.ToString()); } } } List <FieldInfo> newColumns = CrudGenHelper.GetNewFields(fields, typeClass, dbName); if (newColumns.Count > 0) { strb = new StringBuilder(); strb.Append("The following columns were not found in the database."); for (int f = 0; f < newColumns.Count; f++) { strb.Append(rn + tablename + "." + newColumns[f].Name); } if (doAppendToConvertDbFile) { strb.Append(rn + "Query will be found at the end of " + Path.GetFileName(convertDbFile)); } if (doRunQueries) { strb.Append(rn + "Column will be added to table."); } if (string.IsNullOrEmpty(logFileName)) { MessageBox.Show(strb.ToString()); //one message for all new columns in a table. } else { File.AppendAllText(logFileName, strb.ToString() + "\r\n"); } strb = new StringBuilder(); strb.Append(rn + rn + t4 + "/*"); for (int f = 0; f < newColumns.Count; f++) { specialType = CrudGenHelper.GetSpecialType(newColumns[f]); OdDbType odtype = GetOdDbTypeFromColType(newColumns[f].FieldType, specialType); TextSizeMySqlOracle textsize = TextSizeMySqlOracle.Small; if (specialType.HasFlag(CrudSpecialColType.TextIsClob)) { textsize = TextSizeMySqlOracle.Medium; } DbSchemaCol col = new DbSchemaCol(newColumns[f].Name, odtype, textsize); strb.Append(CrudSchemaRaw.AddColumnEnd(tablename, col, 4, doRunQueries, typeClass)); } strb.Append(rn + t4 + "*/"); if (doAppendToConvertDbFile) { File.AppendAllText(convertDbFile, strb.ToString()); } } }
/// <summary>For example, might return "bigint".</summary> private static string GetMySqlType(DbSchemaCol col) { switch (col.DataType) { case OdDbType.Bool: return("tinyint"); case OdDbType.Byte: return("tinyint unsigned"); case OdDbType.Currency: return("double"); case OdDbType.Date: return("date"); case OdDbType.DateTime: return("datetime"); case OdDbType.DateTimeStamp: return("timestamp"); case OdDbType.Float: return("float"); case OdDbType.Enum: return("tinyint"); case OdDbType.Int: if (col.IntUseSmallInt) { return("smallint"); } else { return("int"); } case OdDbType.Long: return("bigint"); case OdDbType.Text: if (col.TextSize == TextSizeMySqlOracle.Small || col.TextSize == TextSizeMySqlOracle.Medium) { return("text"); } else //textSize==TextSizeMySqlOracle.large { return("mediumtext"); } case OdDbType.TimeOfDay: return("time"); case OdDbType.TimeSpan: return("time"); case OdDbType.VarChar255: return("varchar(255)"); default: throw new ApplicationException("type not found"); } }
/// <summary>Generates C# code to Add Column to table. List of DbSchemaCol cols should not contain the new column to be added.</summary> public static string AddColumnEnd(string tableName, DbSchemaCol col, int tabInset) { StringBuilder strb = new StringBuilder(); tb = ""; //must reset tabs each time method is called for (int i = 0; i < tabInset; i++) //defines the base tabs to be added to all lines { tb += "\t"; } strb.Append(tb + "if(DataConnection.DBtype==DatabaseType.MySql) {"); strb.Append(rn + tb + t1 + "command=\"ALTER TABLE " + tableName + " ADD " + col.ColumnName + " " + GetMySqlType(col) + (col.DataType == OdDbType.DateTimeStamp?"":" NOT NULL") + (col.DataType == OdDbType.Date?" DEFAULT '0001-01-01')":"") + "\";"); // strb.Append(rn+tb+t1+"//If ColEnd might be over 65k characters, use mediumtext"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); if (col.DataType == OdDbType.DateTimeStamp) //set value of new timestamp column to now() { strb.Append(rn + tb + t1 + "command=\"UPDATE " + tableName + " SET " + col.ColumnName + " = NOW()\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); } if (col.DataType == OdDbType.Long) //key or foreign key { strb.Append(rn + tb + t1 + "command=\"ALTER TABLE " + tableName + " ADD INDEX (" + col.ColumnName + ")\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); } strb.Append(rn + tb + "}"); strb.Append(rn + tb + "else {//oracle"); strb.Append(rn + tb + t1 + "command=\"ALTER TABLE " + tableName + " ADD " + col.ColumnName + " " + GetOracleType(col) + "\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); if (col.DataType == OdDbType.DateTimeStamp) //set value of new timestamp column to SYSTIMESTAMP { strb.Append(rn + tb + t1 + "command=\"UPDATE " + tableName + " SET " + col.ColumnName + " = SYSTIMESTAMP\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); } if (GetOracleBlankData(col) != null) //Do not add NOT NULL constraint because empty strings are stored as NULL in Oracle //Non string types must be filled with "blank" data and set to NOT NULL { strb.Append(rn + tb + t1 + "command=\"UPDATE " + tableName + " SET " + col.ColumnName + " = " + GetOracleBlankData(col) + " WHERE " + col.ColumnName + " IS NULL\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); strb.Append(rn + tb + t1 + "command=\"ALTER TABLE " + tableName + " MODIFY " + col.ColumnName + " NOT NULL\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); if (col.DataType == OdDbType.Long) //key or foreign key { string indexName = tableName + "_" + col.ColumnName; strb.Append(rn + tb + t1 + "command=@\"CREATE INDEX " + indexName.Substring(0, Math.Min(30, indexName.Length)) + " ON " + tableName + " (" + col.ColumnName + ")\";"); strb.Append(rn + tb + t1 + "Db.NonQ(command);"); } } //if(cols != null) {//this should be removed once the nulls have been removed from the function calls. // cols.Add(col); // for(int i=0;i<cols.Count;i++) {//check for timestamp columns // if(cols[i].DataType == OdDbType.DateTimeStamp) { // strb.Append(rn+tb+t1+"command=@\"CREATE OR REPLACE TRIGGER "+tableName+"_timestamp"); // strb.Append(rn+tb+t1+" BEFORE UPDATE ON "+tableName); // strb.Append(rn+tb+t1+" FOR EACH ROW"); // strb.Append(rn+tb+t1+" BEGIN"); // for(int j=0;j<cols.Count;j++) {//Each column in the table must be set up to change timestamp when changed // strb.Append(rn+tb+t2+" IF :OLD."+cols[j].ColumnName+" <> :NEW."+cols[j].ColumnName+" THEN"); // strb.Append(rn+tb+t2+" :NEW."+cols[i].ColumnName+" := SYSDATE;"); // strb.Append(rn+tb+t2+" END IF"); // } // strb.Append(rn+tb+t1+" END "+tableName+"_timestamp;\";"); // strb.Append(rn+tb+t1+"Db.NonQ(command);"); // } // } //} strb.Append(rn + tb + "}"); return(strb.ToString()); }