Esempio n. 1
0
        ///<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");
            }
        }
Esempio n. 2
0
        ///<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");
            }
        }
Esempio n. 3
0
        ///<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");
            }
        }
Esempio n. 4
0
        ///<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());
        }
Esempio n. 6
0
        ///<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());
                }
            }
        }
Esempio n. 7
0
        /// <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");
            }
        }
Esempio n. 8
0
        /// <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());
        }