public PrimaryKey(ExtractedTableMap table)
{
_tableName=table.TableName;
_fields=new List<string>();
foreach (ExtractedFieldMap efm in table.PrimaryKeys)
_fields.Add(efm.FieldName);
}
public ForeignKey(ExtractedTableMap table,string externalTable,string id)
{
_internalTableName=table.TableName;
_externalTableName=externalTable;
_update="";
_delete="";
_internalFields=new List<string>();
_externalFields=new List<string>();
foreach(List<ForeignRelationMap> frms in table.RelatedFieldsForTable(externalTable))
{
if (frms[0].ID == id)
{
foreach (ForeignRelationMap frm in frms)
{
_internalFields.Add(frm.InternalField);
_externalFields.Add(frm.ExternalField);
_update = frm.OnUpdate.Replace("_", " ");
_delete = frm.OnDelete.Replace("_", " ");
}
break;
}
}
}
internal override List<Trigger> GetDeleteParentTrigger(ExtractedTableMap table, ExtractedTableMap parent)
{
List<Trigger> ret = new List<Trigger>();
string tmp = "AS \n BEGIN \n IF ((SELECT COUNT(*) FROM DELETED)>0) BEGIN DELETE FROM " + parent.TableName + " WHERE ";
string fields = "(";
foreach (ExtractedFieldMap efm in parent.PrimaryKeys)
fields += string.Format("CAST({0} AS VARCHAR(MAX))+'-'+",efm.FieldName);
fields = fields.Substring(0, fields.Length - 5) + ")";
tmp += fields + " IN (SELECT " + fields + " FROM DELETED);\nEND\nEND\n\n";
ret.Add(new Trigger(Pool.Translator.GetDeleteParentTriggerName(Pool.Mapping[table.TableName],this),
"ON " + table.TableName + " AFTER DELETE",
tmp));
return ret;
}
internal override void GetAddAutogen(ExtractedTableMap map, out List<IdentityField> identities, out List<Generator> generators, out List<Trigger> triggers,out List<StoredProcedure> procedures)
{
Type t = Pool.Mapping[map.TableName];
PropertyInfo pi = Pool.Mapping[map.TableName, map.PrimaryKeys[0].FieldName];
bool imediate = t == null;
if (imediate)
t = Pool.Mapping.GetTypeForIntermediateTable(map.TableName, out pi);
identities=new List<IdentityField>();
generators=null;
triggers = new List<Trigger>();
procedures = new List<StoredProcedure>();
ExtractedFieldMap field = map.PrimaryKeys[0];
if ((map.PrimaryKeys.Count>1)&&(!field.AutoGen))
{
foreach (ExtractedFieldMap efm in map.PrimaryKeys)
{
if (efm.AutoGen)
{
field=efm;
break;
}
}
}
string fields = "";
string primarys="";
foreach (ExtractedFieldMap efm in map.Fields)
{
if (!efm.AutoGen)
fields+=","+efm.FieldName;
if (efm.PrimaryKey)
primarys+="tbl."+efm.FieldName+" = ins."+efm.FieldName+" AND ";
}
primarys = primarys.Substring(0,primarys.Length-4);
if (field.Type.ToUpper().Contains("DATE")||field.Type.ToUpper().Contains("TIME"))
{
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t,pi,this) : Pool.Translator.GetInsertTriggerName(t,this)),
"ON "+map.TableName+" INSTEAD OF INSERT\n",
"AS "+
"BEGIN SET NOCOUNT ON;\n"+
"INSERT INTO " + map.TableName + "(" + field.FieldName + fields + ") SELECT GETDATE()" + fields.Replace(",", ",tbl.") + " from INSERTED tbl;\n" +
"END"));
}else if (field.Type.ToUpper().Contains("INT")){
if (map.PrimaryKeys.Count==1)
identities.Add(new IdentityField(map.TableName,field.FieldName,field.FullFieldType,"1"));
else
{
string code = "AS \nINSERT INTO "+map.TableName+"(";
string selCode = "SELECT ";
string condCode = "";
string partCode = "";
string autogenField="";
foreach (ExtractedFieldMap efm in map.Fields)
{
if (efm.ComputedCode == null)
{
if (!efm.AutoGen)
{
code += efm.FieldName + ",";
selCode += "ins." + efm.FieldName + ",";
if (efm.PrimaryKey)
{
condCode += string.Format("ins.{0}=tbl.{0} AND ", efm.FieldName);
partCode += efm.FieldName + ",";
}
}else
autogenField=efm.FieldName;
}
}
code = code.Substring(0, code.Length) +autogenField+ ") " + selCode;
code += string.Format("ISNULL((SELECT MAX(tbl.{0}) FROM {1} tbl WHERE {2}),-1)+ins.ROWNUM FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY {3} ORDER BY {3}) AS ROWNUM FROM inserted) ins;",
new object[]{
autogenField,
map.TableName,
condCode.Substring(0,condCode.Length-4),
partCode.Substring(0,partCode.Length-1)
});
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "ON " + map.TableName + " INSTEAD OF INSERT\n", code));
}
}
else if (field.Type.ToUpper().Contains("VARCHAR")){
XmlDocument doc = new XmlDocument();
doc.LoadXml(new StreamReader(Assembly.GetAssembly(typeof(MsSqlConnection)).GetManifestResourceStream("Org.Reddragonit.Dbpro.Connections.MsSql.StringIDProcedures.xml")).ReadToEnd());
foreach (XmlElement proc in doc.GetElementsByTagName("Procedure"))
procedures.Add(new StoredProcedure(proc.ChildNodes[0].InnerText,
proc.ChildNodes[1].InnerText,
proc.ChildNodes[2].InnerText,
proc.ChildNodes[3].InnerText,
proc.ChildNodes[4].InnerText));
string code = "AS \n";
code += string.Format(@"DECLARE @IDVAL VARCHAR(38),
@cnt BIGINT;
SET @cnt = 1;
WHILE (@cnt>0)
BEGIN
SET @IDVAL = (SELECT [dbo].[Org_Reddragonit_Dbpro_Connections_MsSql_GeneateUniqueID] (
CEILING(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )*((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()))*(Year(GetDate())*Month(GetDate())*Day(GetDate())*RAND()))
,CEILING(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )*((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()))*(Year(GetDate())*Month(GetDate())*Day(GetDate())*RAND()))
,CEILING(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )*((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()))*(Year(GetDate())*Month(GetDate())*Day(GetDate())*RAND()))
,CEILING(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )*((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()))*(Year(GetDate())*Month(GetDate())*Day(GetDate())*RAND()))
,CEILING(RAND( (DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()) )*((DATEPART(mm, GETDATE()) * 100000 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(ms, GETDATE()))*(Year(GetDate())*Month(GetDate())*Day(GetDate())*RAND()))));
SET @cnt = (SELECT COUNT(*) FROM {0} WHERE {1} = @IDVAL);
END
INSERT INTO {0}({1}{2}) SELECT @IDVAL{3} from INSERTED tbl;",
new object[]{map.TableName,field.FieldName, fields, fields.Replace(",", ",tbl.")});
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)),
"ON " + map.TableName + " INSTEAD OF INSERT\n",
code));
}else
throw new Exception("Unable to create autogenerator for non date or digit type.");
}
internal override void GetDropAutogenStrings(ExtractedTableMap map, out List<IdentityField> identities, out List<Generator> generators, out List<Trigger> triggers)
{
Type t = Pool.Mapping[map.TableName];
PropertyInfo pi = Pool.Mapping[map.TableName, map.PrimaryKeys[0].FieldName];
bool imediate = t == null;
if (imediate)
t = Pool.Mapping.GetTypeForIntermediateTable(map.TableName, out pi);
identities=new List<IdentityField>();
generators=null;
triggers = new List<Trigger>();
ExtractedFieldMap field=map.PrimaryKeys[0];
if ((map.PrimaryKeys.Count>1)&&(!field.AutoGen))
{
foreach (ExtractedFieldMap efm in map.PrimaryKeys)
{
if (efm.AutoGen)
{
field=efm;
break;
}
}
}
if (field.Type.ToUpper().Contains("DATE") || field.Type.ToUpper().Contains("TIME") || (map.PrimaryKeys.Count > 0) || (field.Type.ToUpper().Contains("VARCHAR")))
{
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "", ""));
}
else if (field.Type.ToUpper().Contains("INT"))
{
identities.Add(new IdentityField(map.TableName,field.FieldName,field.FullFieldType,""));
}
else
{
throw new Exception("Unable to create autogenerator for non date or digit type.");
}
}
internal override List<Trigger> GetVersionTableTriggers(ExtractedTableMap table,VersionTypes versionType)
{
Type t = Pool.Mapping.GetTypeForVersionTable(table.TableName);
List<Trigger> ret = new List<Trigger>();
string tmp = "AS \n BEGIN\n";
tmp+="\tINSERT INTO "+table.TableName+" SELECT null";
for(int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
tmp+=",tbl."+efm.FieldName;
}
tmp+=" FROM INSERTED ins, "+Pool.Mapping[t].Name+" tbl WHERE ";
for (int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
if (efm.PrimaryKey)
{
tmp+="tbl."+efm.FieldName+" = ins."+efm.FieldName+" AND ";
}
}
tmp=tmp.Substring(0,tmp.Length-4)+";";
tmp+="\nEND\n\n";
ret.Add(new Trigger(Pool.Translator.GetVersionInsertTriggerName(t,this),
"ON "+Pool.Mapping[t].Name+" AFTER INSERT,UPDATE ",
tmp));
return ret;
}
internal override List<Trigger> GetDeleteParentTrigger(ExtractedTableMap table, ExtractedTableMap parent)
{
List<Trigger> ret = new List<Trigger>();
string tmp = "AS \nBEGIN\n";
tmp += "DELETE FROM " + parent.TableName + " WHERE ";
foreach (ExtractedFieldMap efm in parent.PrimaryKeys)
tmp += efm.FieldName + " = old." + efm.FieldName+" AND ";
ret.Add(new Trigger(Pool.Translator.GetDeleteParentTriggerName(Pool.Mapping[table.TableName],this),
"FOR " + table.TableName + " ACTIVE AFTER DELETE POSITION 0",
tmp.Substring(0,tmp.Length-4)+";\nEND\n\n"));
return ret;
}
internal override void GetAddAutogen(ExtractedTableMap map, out List<IdentityField> identities, out List<Generator> generators, out List<Trigger> triggers,out List<StoredProcedure> procedures)
{
identities=null;
generators = new List<Generator>();
triggers=new List<Trigger>();
procedures = new List<StoredProcedure>();
ExtractedFieldMap field = map.PrimaryKeys[0];
if ((map.PrimaryKeys.Count>1)&&(!field.AutoGen))
{
foreach (ExtractedFieldMap efm in map.PrimaryKeys)
{
if (efm.AutoGen)
{
field=efm;
break;
}
}
}
Type t = Pool.Mapping[map.TableName];
PropertyInfo pi = Pool.Mapping[map.TableName, map.PrimaryKeys[0].FieldName];
bool imediate = t == null;
if (imediate)
t = Pool.Mapping.GetTypeForIntermediateTable(map.TableName, out pi);
if (field.Type.ToUpper().Contains("DATE")||field.Type.ToUpper().Contains("TIME"))
{
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t,pi,this) : Pool.Translator.GetInsertTriggerName(t,this)),"FOR "+map.TableName+" ACTIVE BEFORE INSERT POSITION 0",
"AS \n" +
"BEGIN \n" +
" NEW." + field.FieldName + " = CURRENT_TIMESTAMP;\n" +
"END"));
}else if (field.Type.ToUpper().Contains("INT"))
{
if (map.PrimaryKeys.Count==1)
{
generators.Add(new Generator((imediate ? Pool.Translator.GetIntermediateGeneratorName(t,pi,this) : Pool.Translator.GetGeneratorName(t,pi,this))));
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "FOR " + map.TableName + " ACTIVE BEFORE INSERT POSITION 0",
"AS \n" +
"BEGIN \n" +
" NEW." + field.FieldName + " = GEN_ID("+generators[generators.Count-1].Name + ",1);\n" +
"END"));
}else{
string code = "AS \n";
string declares="";
string sets="";
string queryFields="";
foreach (ExtractedFieldMap efm in map.PrimaryKeys)
{
declares+="DECLARE VARIABLE "+efm.FieldName+" "+efm.FullFieldType+";\n";
if (!efm.AutoGen)
{
sets+=efm.FieldName+" = new."+efm.FieldName+";\n";
queryFields+=" AND "+efm.FieldName+" = :"+efm.FieldName;
}
}
code+=declares;
code+="BEGIN \n";
code+=sets;
code+="SELECT MAX("+field.FieldName+") FROM "+map.TableName+" WHERE ";
code+=queryFields.Substring(4)+" INTO :"+field.FieldName+";\n";
code+="IF ("+field.FieldName+" is NULL)\n";
code+="\tTHEN "+field.FieldName+" = 0;\n";
code+="NEW."+field.FieldName+" = "+field.FieldName+"+1;\n";
code+="END";
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "FOR " + map.TableName + " ACTIVE BEFORE INSERT POSITION 0", code));
}
}else if (field.Type.ToUpper().Contains("VARCHAR"))
{
XmlDocument doc = new XmlDocument();
doc.LoadXml(new StreamReader(Assembly.GetAssembly(typeof(FBConnection)).GetManifestResourceStream("Org.Reddragonit.Dbpro.Connections.Firebird.StringIDProcedures.xml")).ReadToEnd());
foreach (XmlElement proc in doc.GetElementsByTagName("Procedure"))
procedures.Add(new StoredProcedure(proc.ChildNodes[0].InnerText,
proc.ChildNodes[1].InnerText,
proc.ChildNodes[2].InnerText,
proc.ChildNodes[3].InnerText,
proc.ChildNodes[4].InnerText));
string code = "AS \n";
code += "DECLARE VARIABLE IDVAL VARCHAR(38);\n"+
"DECLARE VARIABLE CNT BIGINT;\n";
code += "BEGIN \n";
code += "CNT=1;\n";
code += "WHILE (CNT>0) DO\n";
code += "BEGIN\n";
code += "EXECUTE PROCEDURE GENERATE_UNIQUE_ID returning_values IDVAL;\n";
code += "SELECT COUNT(*) FROM " + map.TableName + " WHERE ";
code += field.FieldName+" = :IDVAL INTO :CNT;\n";
code += "END\n";
code += "NEW." + field.FieldName + " = IDVAL;\n";
code += "END";
triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "FOR " + map.TableName + " ACTIVE BEFORE INSERT POSITION 0", code));
}
else
throw new Exception("Unable to create autogenerator for non date or digit type.");
}
internal override List<Trigger> GetVersionTableTriggers(ExtractedTableMap table,VersionTypes versionType)
{
Type t = Pool.Mapping.GetTypeForVersionTable(table.TableName);
List<Trigger> ret = new List<Trigger>();
string tmp = "AS \n";
for (int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
tmp+="DECLARE VARIABLE "+efm.FieldName+" "+efm.FullFieldType+";\n";
}
tmp+="BEGIN\n";
for (int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
tmp+="\t"+efm.FieldName+" = new."+efm.FieldName+";\n";
}
tmp+="\tINSERT INTO "+table.TableName+"("+table.Fields[0].FieldName;
for (int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
tmp+=","+efm.FieldName;
}
tmp+=") VALUES(null";
for (int x=1;x<table.Fields.Count;x++)
{
ExtractedFieldMap efm = table.Fields[x];
tmp+=",:"+efm.FieldName;
}
tmp+=");\nEND\n\n";
ret.Add(new Trigger(Pool.Translator.GetVersionInsertTriggerName(t,this),
"FOR "+Pool.Mapping[t].Name+" ACTIVE AFTER INSERT POSITION 0",
tmp));
ret.Add(new Trigger(Pool.Translator.GetVersionUpdateTriggerName(t,this),
"FOR "+Pool.Mapping[t].Name+" ACTIVE AFTER UPDATE POSITION 0",
tmp));
return ret;
}
internal abstract List<Trigger> GetDeleteParentTrigger(ExtractedTableMap table, ExtractedTableMap parent);
internal abstract List<Trigger> GetVersionTableTriggers(ExtractedTableMap table,VersionTypes versionType);
internal abstract void GetAddAutogen(ExtractedTableMap map, out List<IdentityField> identities, out List<Generator> generators, out List<Trigger> triggers,out List<StoredProcedure> procedures);
internal abstract void GetDropAutogenStrings(ExtractedTableMap map,out List<IdentityField> identities,out List<Generator> generators,out List<Trigger> triggers);
