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