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=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; } } } 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)), "BEFORE INSERT ON " + map.TableName + " FOR EACH ROW", " NEW." + field.FieldName + " := CURRENT_TIMESTAMP;\n")); }else if (field.Type.ToUpper().Contains("INT")) { if (map.PrimaryKeys.Count==1) { Generator gen = new Generator((imediate ? Pool.Translator.GetIntermediateGeneratorName(t, pi, this) : Pool.Translator.GetGeneratorName(t, pi, this))); gen.Value=1; generators.Add(gen); triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "BEFORE INSERT ON " + map.TableName + " FOR EACH ROW", " NEW." + field.FieldName + " := nextval('"+gen.Name + "');\n")); }else{ string code=""; string queryFields=""; foreach (ExtractedFieldMap efm in map.PrimaryKeys) { if (!efm.AutoGen) { queryFields+=" AND "+efm.FieldName+" = NEW."+efm.FieldName; } } code+="NEW."+field.FieldName+" := (SELECT COALESCE(MAX("+field.FieldName+"),-1)+1 FROM "+map.TableName+" WHERE "; code+=queryFields.Substring(4)+");"; triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "BEFORE INSERT ON " + map.TableName + " FOR EACH ROW", code)); } }else throw new Exception("Unable to create autogenerator for non date or digit type."); }
internal override void GetAddAutogen(ExtractedTableMap map, out System.Collections.Generic.List<IdentityField> identities, out System.Collections.Generic.List<Generator> generators, out System.Collections.Generic.List<Trigger> triggers, out List<StoredProcedure> procedures) { Type t = Pool.Mapping[map.TableName]; identities=new List<IdentityField>(); generators=null; triggers=new List<Trigger>(); procedures = null; 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")) { triggers.Add(new Trigger(Pool.Translator.GetInsertTriggerName(t,this),"BEFORE INSERT ON "+map.TableName, "FOR EACH ROW BEGIN\n SET NEW."+field.FieldName+" = CURRENT_DATE();\n END")); }else if (field.Type.ToUpper().Contains("INT")) { if (map.PrimaryKeys.Count==1) { identities.Add(new IdentityField(map.TableName,field.FieldName,field.FullFieldType,"0")); }else{ string code = "FOR EACH ROW\nBEGIN\n"; string queryFields=""; foreach (ExtractedFieldMap efm in map.PrimaryKeys) { if (!efm.AutoGen) queryFields+=" AND "+efm.FieldName+" = NEW."+efm.FieldName; } code+="SET NEW."+field.FieldName+" = (SELECT (CASE WHEN MAX("+field.FieldName+") IS NULL THEN 0 ELSE MAX("+field.FieldName+")+1 END) FROM "+map.TableName+" WHERE "; code+=queryFields.Substring(4)+");\n"; code+="END"; triggers.Add(new Trigger(Pool.Translator.GetInsertTriggerName(t,this), "BEFORE INSERT ON " + map.TableName, code)); } }else throw new Exception("Unable to create autogenerator for non date or digit type."); }
internal override List<Trigger> GetDeleteParentTrigger(ExtractedTableMap table, ExtractedTableMap parent) { List<Trigger> ret = new List<Trigger>(); string tmp = "\tDELETE 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), "AFTER DELETE ON " + parent.TableName + " FOR EACH ROW", tmp.Substring(0, tmp.Length - 4) + ";")); return ret; }
internal override List<Trigger> GetVersionTableTriggers(ExtractedTableMap table, VersionField.VersionTypes versionType) { Type t = Pool.Mapping.GetTypeForVersionTable(table.TableName); List<Trigger> ret = new List<Trigger>(); string tmp = ""; 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("; if (table.Fields[0].Type=="DATETIME") tmp+="CURRENT_DATE()"; else tmp+="0"; for (int x = 1; x < table.Fields.Count; x++) { ExtractedFieldMap efm = table.Fields[x]; tmp += ",NEW." + efm.FieldName; } tmp += ");"; ret.Add(new Trigger(Pool.Translator.GetVersionInsertTriggerName(t,this), "AFTER INSERT ON " + Pool.Mapping[t].Name+ " FOR EACH ROW", tmp)); ret.Add(new Trigger(Pool.Translator.GetVersionUpdateTriggerName(t,this), "AFTER UPDATE ON " + Pool.Mapping[t].Name+" FOR EACH ROW", tmp)); return ret; }
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=null; triggers=new List<Trigger>(); generators=new List<Generator>(); 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")) { triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "", "")); } else if (field.Type.ToUpper().Contains("INT")) { triggers.Add(new Trigger((imediate ? Pool.Translator.GetInsertIntermediateTriggerName(t, pi, this) : Pool.Translator.GetInsertTriggerName(t, this)), "", "")); generators.Add(new Generator((imediate ? Pool.Translator.GetIntermediateGeneratorName(t, pi, this) : Pool.Translator.GetGeneratorName(t, pi, this)))); } else { throw new Exception("Unable to create autogenerator for non date or digit type."); } }
internal override void GetDropAutogenStrings(ExtractedTableMap map, out System.Collections.Generic.List<IdentityField> identities, out System.Collections.Generic.List<Generator> generators, out System.Collections.Generic.List<Trigger> triggers) { Type t = Pool.Mapping[map.TableName]; identities=new List<IdentityField>(); triggers = new List<Trigger>(); generators = null; 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")) { triggers.Add(new Trigger(Pool.Translator.GetInsertTriggerName(t,this), "", "")); } else if (field.Type.ToUpper().Contains("INT")) { if (map.PrimaryKeys.Count>1) triggers.Add(new Trigger(Pool.Translator.GetInsertTriggerName(t,this), "", "")); else identities.Add(new IdentityField(map.TableName, field.FieldName, field.FullFieldType,"")); } else { throw new Exception("Unable to create autogenerator for non date or digit type."); } }
private void ExtractExpectedStructure(ref List<Type> types, out List<ExtractedTableMap> tables, out List<Trigger> triggers, out List<Generator> generators, out List<IdentityField> identities, out List<View> views, out List<StoredProcedure> procedures, Connection conn) { List<View> tviews; List<StoredProcedure> tprocs; List<Trigger> ttriggers; tables = new List<ExtractedTableMap>(); triggers = new List<Trigger>(); generators = new List<Generator>(); identities = new List<IdentityField>(); views = new List<View>(); procedures = new List<StoredProcedure>(); List<Trigger> tmpTriggers = new List<Trigger>(); List<Generator> tmpGenerators = new List<Generator>(); List<IdentityField> tmpIdentities = new List<IdentityField>(); List<StoredProcedure> tmpProcedures = new List<StoredProcedure>(); Dictionary<string, string> AutoDeleteParentTables = new Dictionary<string, string>(); for (int x = 0; x < types.Count;x++ ) { Type type = types[x]; List<Type> ttypes; ConnectionPoolManager.GetAdditionalsForTable(_pool, types[x], out tviews, out tprocs, out ttypes,out ttriggers); foreach (View vw in tviews) { if (vw.RequiredTypes != null) { while (vw.RequiredTypes.Count > 0) { Type tp = vw.RequiredTypes.Dequeue(); if (!types.Contains(tp)) types.Add(tp); } } } views.AddRange(tviews); procedures.AddRange(tprocs); triggers.AddRange(ttriggers); foreach (Type t in ttypes) { if (!types.Contains(t) && !_createdTypes.Contains(t)) types.Add(t); } ClassViewAttribute cva = _pool[type]; if (cva!=null) { View vw = new View(_pool.Translator.GetViewName(type), cva.Query); views.Add(vw); if (vw.RequiredTypes != null) { while (vw.RequiredTypes.Count > 0) { Type tp = vw.RequiredTypes.Dequeue(); if (!types.Contains(tp)) types.Add(tp); } } } else { sTable tm = _pool.Mapping[type]; ExtractedTableMap etm = new ExtractedTableMap(tm.Name); if (Utility.IsEnum(type)) { foreach (sTableField f in tm.Fields) etm.Fields.Add(new ExtractedFieldMap(f.Name, conn.TranslateFieldType(f.Type, f.Length), f.Length, tm.AutoGenField == f.Name, false, tm.AutoGenField == f.Name,f.ComputedCode)); } else { Table tbl = (Table)type.GetCustomAttributes(typeof(Table), false)[0]; foreach (TableIndex ti in type.GetCustomAttributes(typeof(TableIndex), false)) { List<string> tfields = new List<string>(); foreach (string str in ti.Fields) { if (str.Contains(".")) { if (!new List<string>(tm.PrimaryKeyProperties).Contains(str.Substring(0, str.IndexOf(".")))) throw new Exception("Unable to create table index for non-table field"); else { foreach (sTableField fld in tm[str.Substring(0, str.IndexOf("."))]) { if (_IsTracedProperty(tm, str.Substring(str.IndexOf(".") + 1), fld, str.Substring(0, str.IndexOf(".")))) tfields.Add(fld.Name); } } } else { sTableField[] flds = tm[str]; if (flds.Length == 0) tfields.Add(str); else { foreach (sTableField f in flds) tfields.Add(f.Name); } } } etm.Indices.Add(new Index(_pool.Translator.GetIndexName(type, ti.Name, conn), tfields.ToArray(), ti.Unique, ti.Ascending)); } List<string> pProps = new List<string>(tm.PrimaryKeyProperties); foreach (string prop in tm.Properties) { PropertyInfo pi = type.GetProperty(prop, Utility._BINDING_FLAGS_WITH_INHERITANCE); if (!tm.ArrayProperties.Contains(prop)) { sTableRelation? rel = tm.GetRelationForProperty(prop); foreach (sTableField f in tm[prop]) { etm.Fields.Add(new ExtractedFieldMap(f.Name, conn.TranslateFieldType(f.Type, f.Length), f.Length, pProps.Contains(prop), (rel.HasValue ? rel.Value.Nullable : f.Nullable), (tm.AutoGenField != null ? f.Name == tm.AutoGenField : false),f.ComputedCode)); if (rel.HasValue) etm.ForeignFields.Add(new ForeignRelationMap(type.Name + "_" + prop, f.Name, rel.Value.ExternalTable, f.ExternalField, rel.Value.OnDelete.ToString(), rel.Value.OnUpdate.ToString())); } } else { sTable iMap = _pool.Mapping[type, prop]; ExtractedTableMap ietm = new ExtractedTableMap(iMap.Name); string extTable = (_pool.Mapping.IsMappableType(pi.PropertyType.GetElementType()) ? _pool.Mapping[pi.PropertyType.GetElementType()].Name : null); List<string> piKeys = new List<string>(iMap.PrimaryKeyFields); foreach (sTableField f in iMap.Fields) { ietm.Fields.Add(new ExtractedFieldMap(f.Name, conn.TranslateFieldType(f.Type, f.Length), f.Length, piKeys.Contains(f.Name), false, (iMap.AutoGenField != null ? iMap.AutoGenField == f.Name : false),f.ComputedCode)); if (Utility.StringsEqual("PARENT", f.ClassProperty)) ietm.ForeignFields.Add(new ForeignRelationMap(type.Name + "_" + prop + (_pool.Mapping.IsMappableType(pi.PropertyType.GetElementType()) ? "_intermediate" : ""), f.Name, etm.TableName, f.ExternalField, ForeignField.UpdateDeleteAction.CASCADE.ToString(), ForeignField.UpdateDeleteAction.CASCADE.ToString())); if (Utility.StringsEqual("CHILD", f.ClassProperty)) ietm.ForeignFields.Add(new ForeignRelationMap(type.Name + "_" + prop, f.Name, extTable, f.ExternalField, ForeignField.UpdateDeleteAction.CASCADE.ToString(), ForeignField.UpdateDeleteAction.CASCADE.ToString())); } tables.Add(ietm); } } if (_pool.Mapping.IsMappableType(type.BaseType)) { sTable pMap = _pool.Mapping[type.BaseType]; foreach (string str in pMap.PrimaryKeyFields) etm.ForeignFields.Add(new ForeignRelationMap(type.Name + "_parent", str, pMap.Name, str, ForeignField.UpdateDeleteAction.CASCADE.ToString(), ForeignField.UpdateDeleteAction.CASCADE.ToString())); if (tbl.AutoDeleteParent) AutoDeleteParentTables.Add(tm.Name, pMap.Name); } if (_pool.Mapping.HasVersionTable(type)) { Org.Reddragonit.Dbpro.Structure.Attributes.VersionField.VersionTypes vt; sTable vtm = _pool.Mapping.GetVersionTable(type, out vt); ExtractedTableMap vetm = new ExtractedTableMap(vtm.Name); List<string> vpkeys = new List<string>(tm.PrimaryKeyFields); foreach (sTableField f in vtm.Fields) { vetm.Fields.Add(new ExtractedFieldMap(f.Name, conn.TranslateFieldType(f.Type, f.Length), f.Length, vpkeys.Contains(f.Name), !vpkeys.Contains(f.Name), vtm.AutoGenField == f.Name,f.ComputedCode)); if (vpkeys.Contains(f.Name)) vetm.ForeignFields.Add(new ForeignRelationMap(type.Name + "_version", f.Name, tm.Name, f.Name, ForeignField.UpdateDeleteAction.CASCADE.ToString(), ForeignField.UpdateDeleteAction.CASCADE.ToString())); } triggers.AddRange(conn.GetVersionTableTriggers(vetm, vt)); tables.Add(vetm); } } tables.Add(etm); bool keyDifferent = false; foreach (ExtractedTableMap e in _tables) { if (etm.TableName == e.TableName) { foreach (ExtractedFieldMap efm in etm.PrimaryKeys) { bool foundField = false; foreach (ExtractedFieldMap ee in e.PrimaryKeys) { if (ee.FieldName == efm.FieldName) { foundField = true; if ((ee.PrimaryKey != efm.PrimaryKey) || (ee.PrimaryKey && efm.PrimaryKey && ((ee.Type != efm.Type) || (ee.Size != efm.Size)))) keyDifferent = true; break; } } if (!foundField) keyDifferent = true; if (keyDifferent) break; } if (keyDifferent) break; } } if (keyDifferent) { foreach (ExtractedTableMap e in _tables) { if (e.RelatedTables.Contains(etm.TableName)) { Type t = _pool.Mapping[e.TableName]; if (t != null) { if (!types.Contains(t) && !_createdTypes.Contains(t)) types.Add(t); } } } } } } for(int x=0;x<tables.Count;x++) { ExtractedTableMap etm = tables[x]; Logger.LogLine(etm.TableName + ":"); foreach (ExtractedFieldMap efm in etm.Fields) Logger.LogLine("\t" + efm.FieldName + " - " + efm.PrimaryKey.ToString()); if (!_pool.Mapping.IsVersionTable(etm.TableName)) { foreach (ExtractedFieldMap efm in etm.PrimaryKeys) { if (efm.AutoGen) { conn.GetAddAutogen(etm, out tmpIdentities, out tmpGenerators, out tmpTriggers, out tmpProcedures); if (tmpGenerators != null) generators.AddRange(tmpGenerators); if (tmpTriggers != null) triggers.AddRange(tmpTriggers); if (tmpIdentities != null) identities.AddRange(tmpIdentities); if (tmpProcedures != null) procedures.AddRange(tmpProcedures); break; } } if (AutoDeleteParentTables.ContainsKey(etm.TableName)) { ExtractedTableMap ptm = new ExtractedTableMap(); foreach (ExtractedTableMap m in tables) { if (AutoDeleteParentTables[etm.TableName] == m.TableName) { ptm = m; break; } } if (ptm.TableName == null) { sTable ptbl = _pool.Mapping[_pool.Mapping[AutoDeleteParentTables[etm.TableName]]]; ptm = new ExtractedTableMap(ptbl.Name); List<string> pkeys = new List<string>(ptbl.PrimaryKeyFields); foreach (sTableField f in ptbl.Fields) ptm.Fields.Add(new ExtractedFieldMap(f.Name, conn.TranslateFieldType(f.Type, f.Length), f.Length, pkeys.Contains(f.Name), f.Nullable,f.ComputedCode)); } triggers.AddRange(conn.GetDeleteParentTrigger(etm, ptm)); } } if (conn is MsSqlConnection) ((MsSqlConnection)conn).MaskComplicatedRelations(x, ref tables, ref triggers, AutoDeleteParentTables); } for (int x = 0; x < procedures.Count; x++) { for (int y = x + 1; y < procedures.Count; y++) { if (procedures[x].Equals(procedures[y])) { procedures.RemoveAt(y); y--; } } } }