/// <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
strb.Append(rn+tb+t1+"command=@\"CREATE INDEX "+tableName+"_"+col.ColumnName+" 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();
}
///<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>
public static void Write(string convertDbFile,Type typeClass,string dbName,bool isMobile) {
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)) {
MessageBox.Show("This table was not found in the database:"
+rn+tablename
+rn+"Queries will be found at the end of "+Path.GetFileName(convertDbFile));
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));
strb.Append(rn+t4+"*/");
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);
}
strb.Append(rn+"Query will be found at the end of "+Path.GetFileName(convertDbFile));
MessageBox.Show(strb.ToString());//one message for all new columns in a table.
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==CrudSpecialColType.TextIsClob || specialType==CrudSpecialColType.TextIsClobNote){
textsize=TextSizeMySqlOracle.Medium;
}
DbSchemaCol col=new DbSchemaCol(newColumns[f].Name,odtype,textsize);
strb.Append(CrudSchemaRaw.AddColumnEnd(tablename,col,4));
}
strb.Append(rn+t4+"*/");
File.AppendAllText(convertDbFile,strb.ToString());
}
}
///<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>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 return "bigint NOT NULL".</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>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");
}
}
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));
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));
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));
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));
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();
}