/// <summary> /// This function checks if there is already a similar constraint that connects the two tables on the same fields /// </summary> /// <returns>true if other constraint exists</returns> private static Boolean LoadViaHasAlreadyBeenImplemented(TConstraint AConstraint) { return(DirectReferences.Contains( AConstraint.strOtherTable + "," + StringHelper.StrMerge(AConstraint.strThisFields, ',') + "," + StringHelper.StrMerge(AConstraint.strOtherFields, ','))); }
private static void WriteConstraint(StreamWriter ASw, TTable ATable, TConstraint constr, Boolean onlyForeign, Boolean AAdd) { if (!onlyForeign && (constr.strType == "primarykey")) { ASw.WriteLine(","); ASw.WriteLine(" CONSTRAINT {0}", constr.strName); ASw.Write(" PRIMARY KEY ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); } if (!onlyForeign && (constr.strType == "uniquekey")) { ASw.WriteLine(","); ASw.WriteLine(" CONSTRAINT {0}", constr.strName); ASw.Write(" UNIQUE ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); } if (onlyForeign && (constr.strType == "foreignkey")) { ASw.WriteLine("ALTER TABLE {0}", ATable.strName); if (AAdd) { ASw.WriteLine(" ADD CONSTRAINT {0}", constr.strName); ASw.WriteLine(" FOREIGN KEY ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); ASw.WriteLine(" REFERENCES {0}({1});", constr.strOtherTable, StringHelper.StrMerge(constr.strOtherFields, ',')); } else { ASw.WriteLine(" DROP CONSTRAINT IF EXISTS {0};", constr.strName); } } }
/// <summary> /// This function checks if there is already a similar constraint that connects the two tables on the same fields /// </summary> /// <returns>true if other constraint exists</returns> private static Boolean LoadViaHasAlreadyBeenImplemented(TConstraint AConstraint) { return DirectReferences.Contains( AConstraint.strOtherTable + "," + StringHelper.StrMerge(AConstraint.strThisFields, ',') + "," + StringHelper.StrMerge(AConstraint.strOtherFields, ',')); }
public static TConstraint Map(this T_CONSTRAINT model) { var tConstraint = new TConstraint(); foreach (var attribute in model.attributes) { tConstraint.Attributes.Add(attribute.Map()); } return(tConstraint); }
/// <summary> /// this is for foreign keys, eg load all countries with currency EUR /// </summary> /// <param name="AStore"></param> /// <param name="ACurrentTable"></param> /// <param name="ATemplate"></param> /// <param name="ASnippet"></param> private static void InsertViaOtherTable(TDataDefinitionStore AStore, TTable ACurrentTable, ProcessTemplate ATemplate, ProcessTemplate ASnippet) { foreach (TConstraint myConstraint in ACurrentTable.grpConstraint) { if (ValidForeignKeyConstraintForLoadVia(myConstraint)) { TTable OtherTable = AStore.GetTable(myConstraint.strOtherTable); if (!LoadViaHasAlreadyBeenImplemented(myConstraint)) { InsertViaOtherTableConstraint(AStore, myConstraint, ACurrentTable, OtherTable, ATemplate, ASnippet); } // AccountHierarchy: there is no constraint that references Ledger directly, but constraint referencing the Account table with a key that contains the ledger reference // but because the key in Ledger is already the primary key, a LoadViaLedger is required. // other way round: p_foundation_proposal_detail has 2 constraints for foundation and foundationproposal foreach (string field in myConstraint.strOtherFields) { // get a constraint that is only based on that field TConstraint OtherLinkConstraint = OtherTable.GetConstraint(StringHelper.StrSplit(field, ",")); if ((OtherLinkConstraint != null) && ValidForeignKeyConstraintForLoadVia(OtherLinkConstraint)) { TConstraint NewConstraint = new TConstraint(); NewConstraint.strName = OtherLinkConstraint.strName + "forLoadVia"; NewConstraint.strType = "foreignkey"; NewConstraint.strThisTable = myConstraint.strThisTable; NewConstraint.strThisFields = StringHelper.StrSplit(myConstraint.strThisFields[myConstraint.strOtherFields.IndexOf( field)], ","); NewConstraint.strOtherTable = OtherLinkConstraint.strOtherTable; NewConstraint.strOtherFields = OtherLinkConstraint.strOtherFields; if (!LoadViaHasAlreadyBeenImplemented(NewConstraint)) { InsertViaOtherTableConstraint(AStore, NewConstraint, ACurrentTable, AStore.GetTable(OtherLinkConstraint.strOtherTable), ATemplate, ASnippet); } } } } } }
/// <summary> /// parse the definition of one constraint (unique, primary or foreign key) /// </summary> /// <param name="cur2">the current node</param> /// <param name="AThisTableName">the name of the table that this constraint belongs to</param> /// <returns>the constraint</returns> protected TConstraint ParseConstraint(XmlNode cur2, String AThisTableName) { TConstraint element; element = new TConstraint(); element.strName = GetAttribute(cur2, "name"); // foreignkey, uniquekey, primarykey element.strType = cur2.Name; element.strThisTable = AThisTableName; element.strThisFields = StringHelper.StrSplit(GetAttribute(cur2, "thisFields"), ","); element.strOtherTable = GetAttribute(cur2, "otherTable"); element.strOtherFields = StringHelper.StrSplit(GetAttribute(cur2, "otherFields"), ","); return(element); }
private static void PrepareCodeletsForeignKey( TTable AOtherTable, TConstraint AConstraint, out string whereClauseForeignKey, out string whereClauseViaOtherTable, out string odbcParametersForeignKey, out int numberFields, out string namesOfThisTableFields) { whereClauseForeignKey = ""; whereClauseViaOtherTable = ""; numberFields = AConstraint.strThisFields.Count; namesOfThisTableFields = ""; odbcParametersForeignKey = "OdbcParameter[] ParametersArray = new OdbcParameter[" + AConstraint.strThisFields.Count.ToString() + "];" + Environment.NewLine; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { whereClauseForeignKey += " AND "; whereClauseViaOtherTable += " AND "; namesOfThisTableFields += ", "; } namesOfThisTableFields += '"' + AConstraint.strThisFields[counterKeyField] + '"'; string otherfield = AConstraint.strOtherFields[counterKeyField]; TTableField otherTypedField = AOtherTable.GetField(otherfield); whereClauseForeignKey += field + " = ?"; whereClauseViaOtherTable += "PUB_" + AConstraint.strThisTable + "." + field + " = PUB_" + AConstraint.strOtherTable + "." + otherfield; odbcParametersForeignKey += "ParametersArray[" + counterKeyField.ToString() + "] = " + "new OdbcParameter(\"\", " + otherTypedField.ToOdbcTypeString() + (otherTypedField.iLength != -1 ? ", " + otherTypedField.iLength.ToString() : "") + ");" + Environment.NewLine; odbcParametersForeignKey += "ParametersArray[" + counterKeyField.ToString() + "].Value = " + "((object)(A" + TTable.NiceFieldName(otherfield) + "));" + Environment.NewLine; counterKeyField++; } }
/// <summary> /// prepare some code for the via other linking table (bridge) /// </summary> /// <param name="AConstraint">the constraint between current and linking table</param> /// <param name="AConstraintOther">the constraint between linking table and other</param> /// <param name="whereClauseViaLinkTable"></param> /// <param name="whereClauseAllViaTables"></param> private static void PrepareCodeletsViaLinkTable( TConstraint AConstraint, TConstraint AConstraintOther, out string whereClauseViaLinkTable, out string whereClauseAllViaTables) { whereClauseViaLinkTable = ""; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { whereClauseViaLinkTable += " AND "; } string otherfield = AConstraint.strOtherFields[counterKeyField]; whereClauseViaLinkTable += "PUB_" + AConstraint.strThisTable + "." + field + " = PUB_" + AConstraint.strOtherTable + "." + otherfield; counterKeyField++; } whereClauseViaLinkTable += " AND "; whereClauseAllViaTables = whereClauseViaLinkTable; counterKeyField = 0; foreach (string field in AConstraintOther.strThisFields) { if (counterKeyField > 0) { whereClauseViaLinkTable += " AND "; whereClauseAllViaTables += " AND "; } string otherfield = AConstraintOther.strOtherFields[counterKeyField]; whereClauseViaLinkTable += "PUB_" + AConstraintOther.strThisTable + "." + field + " = ?"; whereClauseAllViaTables += "PUB_" + AConstraintOther.strThisTable + "." + field + " = PUB_" + AConstraintOther.strOtherTable + "." + otherfield; counterKeyField++; } }
protected TConstraint OptimizeAs <TConstraint>() where TConstraint : CompositeJsonConstraint, new() { return(Constraints .Select(c => c.Optimize()) .Aggregate(new TConstraint(), (c, next) => { TConstraint and = next as TConstraint; if (and != null) { c.Constraints.AddRange(and.Constraints); } else { c.Constraints.Add(next); } return c; })); }
public virtual void testAddRefernceColumnInAlterTable2() { TGSqlParser lcparser = new TGSqlParser(EDbVendor.dbvoracle); lcparser.sqltext = "ALTER TABLE P_CAP \n" + "ADD CONSTRAINT FK_P_CAP_R_PH_111_P_CEL \n" + "FOREIGN KEY (CAP_CEL) REFERENCES P_CEL (CEL_COD);"; Assert.IsTrue(lcparser.parse() == 0); TAlterTableStatement at = (TAlterTableStatement)lcparser.sqlstatements.get(0); TAlterTableOption alterTableOption = at.AlterTableOptionList.getAlterTableOption(0); TConstraint constraint = alterTableOption.ConstraintList.getConstraint(0); Assert.IsTrue(constraint.Constraint_type == EConstraintType.foreign_key); constraint.ReferencedColumnList.insertElementAt(parser.parseObjectName("cel_newid"), 0); Assert.IsTrue(testScriptGenerator.verifyScript(EDbVendor.dbvoracle , at.ToScript() , "ALTER TABLE p_cap \n" + " ADD CONSTRAINT fk_p_cap_r_ph_111_p_cel FOREIGN KEY (cap_cel) REFERENCES p_cel(cel_newid,cel_cod)" )); }
/// <summary> /// get formal and actual parameters for a unique or primary key /// </summary> /// <param name="ACurrentTable"></param> /// <param name="AConstraint"></param> /// <param name="formalParametersKey"></param> /// <param name="actualParametersKey"></param> /// <param name="numberKeyColumns"></param> /// <param name="actualParametersToString"></param> private static void PrepareCodeletsKey( TTable ACurrentTable, TConstraint AConstraint, out string formalParametersKey, out string actualParametersKey, out string actualParametersToString, out int numberKeyColumns) { formalParametersKey = ""; actualParametersKey = ""; actualParametersToString = ""; numberKeyColumns = AConstraint.strThisFields.Count; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { formalParametersKey += ", "; actualParametersKey += ", "; actualParametersToString += " + \" \" + "; } TTableField typedField = ACurrentTable.GetField(field); formalParametersKey += typedField.GetDotNetType() + " A" + TTable.NiceFieldName(field); actualParametersKey += "A" + TTable.NiceFieldName(field); actualParametersToString += "A" + TTable.NiceFieldName(field) + ".ToString()"; counterKeyField++; } // for partners, show their names as well. This is used in function AddOrModifyRecord to show the users which values are different foreach (TTableField column in ACurrentTable.grpTableField) { if (column.strName.Contains("_name_")) { actualParametersToString += " + ExistingRecord[0]." + TTable.NiceFieldName(column) + ".ToString()"; } } }
/// <summary> /// situation 2: bridging tables /// for example p_location and p_partner are connected through p_partner_location /// it would be helpful, to be able to do: /// location.LoadViaPartner(partnerkey) to get all locations of the partner /// partner.loadvialocation(locationkey) to get all partners living at that location /// general solution: pick up all foreign keys from other tables (B) to the primary key of the current table (A), /// where the other table has a foreign key to another table (C), using other fields in the primary key of (B) than the link to (A). /// get all tables that reference the current table /// </summary> /// <param name="AStore"></param> /// <param name="ACurrentTable"></param> /// <param name="ATemplate"></param> /// <param name="ASnippet"></param> private static void InsertViaLinkTable(TDataDefinitionStore AStore, TTable ACurrentTable, ProcessTemplate ATemplate, ProcessTemplate ASnippet) { // step 1: collect all the valid constraints of such link tables ArrayList OtherLinkConstraints = new ArrayList(); ArrayList References = new ArrayList(); foreach (TConstraint Reference in ACurrentTable.GetReferences()) { TTable LinkTable = AStore.GetTable(Reference.strThisTable); try { TConstraint LinkPrimaryKey = LinkTable.GetPrimaryKey(); if (StringHelper.Contains(LinkPrimaryKey.strThisFields, Reference.strThisFields)) { // check how many constraints from the link table are from fields in the primary key // a link table only should have 2 // so find the other one TConstraint OtherLinkConstraint = null; bool IsLinkTable = false; foreach (TConstraint LinkConstraint in LinkTable.grpConstraint) { // check if there is another constraint for the primary key of the link table. if (ValidForeignKeyConstraintForLoadVia(LinkConstraint) && (LinkConstraint != Reference) && (StringHelper.Contains(LinkPrimaryKey.strThisFields, LinkConstraint.strThisFields))) { if (OtherLinkConstraint == null) { OtherLinkConstraint = LinkConstraint; IsLinkTable = true; } else { IsLinkTable = false; } } else if (ValidForeignKeyConstraintForLoadVia(LinkConstraint) && (LinkConstraint != Reference) && (!StringHelper.Contains(LinkPrimaryKey.strThisFields, LinkConstraint.strThisFields)) && StringHelper.ContainsSome(LinkPrimaryKey.strThisFields, LinkConstraint.strThisFields)) { // if there is a key that partly is in the primary key, then this is not a link table. OtherLinkConstraint = LinkConstraint; IsLinkTable = false; } } if ((IsLinkTable) && (OtherLinkConstraint.strOtherTable != Reference.strOtherTable)) { // collect the links. then we are able to name them correctly, once we need them OtherLinkConstraints.Add(OtherLinkConstraint); References.Add(Reference); } } } catch (Exception) { // Console.WriteLine(e.Message); } } // step2: implement the link tables, using correct names for the procedures int Count = 0; foreach (TConstraint OtherLinkConstraint in OtherLinkConstraints) { TTable OtherTable = AStore.GetTable(OtherLinkConstraint.strOtherTable); string ProcedureName = "Via" + TTable.NiceTableName(OtherTable.strName); // check if other foreign key exists that references the same table, e.g. // PPartnerAccess.LoadViaSUserPRecentPartners // PPartnerAccess.LoadViaSUserPCustomisedGreeting // also PFoundationProposalAccess.LoadViaPFoundationPFoundationProposalDetail and // TODO AlreadyExistsProcedureSameName necessary for PPersonAccess.LoadViaPUnit (p_field_key_n) and PPersonAccess.LoadViaPUnitPmGeneralApplication // Question: does PPersonAccess.LoadViaPUnitPmGeneralApplication make sense? if (FindOtherConstraintSameOtherTable2(OtherLinkConstraints, OtherLinkConstraint) || LoadViaHasAlreadyBeenImplemented(OtherLinkConstraint) // TODO || AlreadyExistsProcedureSameName(ProcedureName) || ((ProcedureName == "ViaPUnit") && (OtherLinkConstraint.strThisTable == "pm_general_application")) ) { ProcedureName += TTable.NiceTableName(OtherLinkConstraint.strThisTable); } ATemplate.AddToCodelet("USINGNAMESPACES", GetNamespace(OtherTable.strGroup), false); ProcessTemplate snippetLinkTable = ATemplate.GetSnippet("VIALINKTABLE"); snippetLinkTable.SetCodelet("VIAPROCEDURENAME", ProcedureName); snippetLinkTable.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(OtherTable.strName)); snippetLinkTable.SetCodelet("SQLOTHERTABLENAME", OtherTable.strName); snippetLinkTable.SetCodelet("SQLLINKTABLENAME", OtherLinkConstraint.strThisTable); string notUsed; int notUsedInt; string formalParametersOtherPrimaryKey; string actualParametersOtherPrimaryKey; PrepareCodeletsPrimaryKey(OtherTable, out formalParametersOtherPrimaryKey, out actualParametersOtherPrimaryKey, out notUsed, out notUsedInt); PrepareCodeletsPrimaryKey(ACurrentTable, out notUsed, out notUsed, out notUsed, out notUsedInt); string whereClauseForeignKey; string whereClauseViaOtherTable; string odbcParametersForeignKey; PrepareCodeletsForeignKey( OtherTable, OtherLinkConstraint, out whereClauseForeignKey, out whereClauseViaOtherTable, out odbcParametersForeignKey, out notUsedInt, out notUsed); string whereClauseViaLinkTable; string whereClauseAllViaTables; PrepareCodeletsViaLinkTable( (TConstraint)References[Count], OtherLinkConstraint, out whereClauseViaLinkTable, out whereClauseAllViaTables); snippetLinkTable.SetCodelet("FORMALPARAMETERSOTHERPRIMARYKEY", formalParametersOtherPrimaryKey); snippetLinkTable.SetCodelet("ACTUALPARAMETERSOTHERPRIMARYKEY", actualParametersOtherPrimaryKey); snippetLinkTable.SetCodelet("ODBCPARAMETERSFOREIGNKEY", odbcParametersForeignKey); snippetLinkTable.SetCodelet("WHERECLAUSEVIALINKTABLE", whereClauseViaLinkTable); snippetLinkTable.SetCodelet("WHERECLAUSEALLVIATABLES", whereClauseAllViaTables); ASnippet.InsertSnippet("VIALINKTABLE", snippetLinkTable); Count = Count + 1; } }
/// <summary> /// code for generating typed datasets /// </summary> /// <param name="AInputXmlfile"></param> /// <param name="AOutputPath"></param> /// <param name="ANameSpace"></param> /// <param name="store"></param> /// <param name="groups"></param> /// <param name="AFilename"></param> public static void CreateTypedDataSets(String AInputXmlfile, String AOutputPath, String ANameSpace, TDataDefinitionStore store, string[] groups, string AFilename) { Console.WriteLine("processing dataset " + ANameSpace); string templateDir = TAppSettingsManager.GetValue("TemplateDir", true); ProcessTemplate Template = new ProcessTemplate(templateDir + Path.DirectorySeparatorChar + "ORM" + Path.DirectorySeparatorChar + "DataSet.cs"); Template.AddSnippetsFromOtherFile(templateDir + Path.DirectorySeparatorChar + "ORM" + Path.DirectorySeparatorChar + "DataTable.cs"); DataSetTableIdCounter = Convert.ToInt16(TAppSettingsManager.GetValue("StartTableId")); // load default header with license and copyright Template.SetCodelet("GPLFILEHEADER", ProcessTemplate.LoadEmptyFileComment(templateDir)); Template.SetCodelet("NAMESPACE", ANameSpace); // if no dataset is defined yet in the xml file, the following variables can be empty Template.AddToCodelet("USINGNAMESPACES", ""); Template.AddToCodelet("CONTENTDATASETSANDTABLESANDROWS", ""); TXMLParser parserDataSet = new TXMLParser(AInputXmlfile, false); XmlDocument myDoc = parserDataSet.GetDocument(); XmlNode startNode = myDoc.DocumentElement; if (startNode.Name.ToLower() == "petradatasets") { XmlNode cur = TXMLParser.NextNotBlank(startNode.FirstChild); while ((cur != null) && (cur.Name.ToLower() == "importunit")) { Template.AddToCodelet("USINGNAMESPACES", "using " + TXMLParser.GetAttribute(cur, "name") + ";" + Environment.NewLine); cur = TXMLParser.GetNextEntity(cur); } while ((cur != null) && (cur.Name.ToLower() == "dataset")) { ProcessTemplate snippetDataset = Template.GetSnippet("TYPEDDATASET"); string datasetname = TXMLParser.GetAttribute(cur, "name"); snippetDataset.SetCodelet("DATASETNAME", datasetname); // INITCONSTRAINTS and INITRELATIONS can be empty snippetDataset.AddToCodelet("INITCONSTRAINTS", ""); snippetDataset.AddToCodelet("INITRELATIONS", ""); SortedList <string, TDataSetTable>tables = new SortedList <string, TDataSetTable>(); XmlNode curChild = cur.FirstChild; while (curChild != null) { if ((curChild.Name.ToLower() == "table") && TXMLParser.HasAttribute(curChild, "sqltable")) { bool OverloadTable = false; string tabletype = TTable.NiceTableName(TXMLParser.GetAttribute(curChild, "sqltable")); string variablename = (TXMLParser.HasAttribute(curChild, "name") ? TXMLParser.GetAttribute(curChild, "name") : tabletype); TDataSetTable table = new TDataSetTable( TXMLParser.GetAttribute(curChild, "sqltable"), tabletype, variablename, store.GetTable(tabletype)); XmlNode tableNodes = curChild.FirstChild; while (tableNodes != null) { if (tableNodes.Name.ToLower() == "customfield") { // eg. BestAddress in PartnerEditTDS.PPartnerLocation TTableField customField = new TTableField(); customField.strName = TXMLParser.GetAttribute(tableNodes, "name"); customField.strTypeDotNet = TXMLParser.GetAttribute(tableNodes, "type"); customField.strDescription = TXMLParser.GetAttribute(tableNodes, "comment"); customField.strDefault = TXMLParser.GetAttribute(tableNodes, "initial"); table.grpTableField.Add(customField); OverloadTable = true; } if (tableNodes.Name.ToLower() == "field") { // eg. UnitName in PartnerEditTDS.PPerson TTableField field = new TTableField(store.GetTable(TXMLParser.GetAttribute(tableNodes, "sqltable")). GetField(TXMLParser.GetAttribute(tableNodes, "sqlfield"))); if (TXMLParser.HasAttribute(tableNodes, "name")) { field.strNameDotNet = TXMLParser.GetAttribute(tableNodes, "name"); } if (TXMLParser.HasAttribute(tableNodes, "comment")) { field.strDescription = TXMLParser.GetAttribute(tableNodes, "comment"); } table.grpTableField.Add(field); OverloadTable = true; } if (tableNodes.Name.ToLower() == "primarykey") { TConstraint primKeyConstraint = table.GetPrimaryKey(); primKeyConstraint.strThisFields = StringHelper.StrSplit(TXMLParser.GetAttribute(tableNodes, "thisFields"), ","); OverloadTable = true; } tableNodes = tableNodes.NextSibling; } if (OverloadTable) { tabletype = datasetname + TTable.NiceTableName(table.strName); if (TXMLParser.HasAttribute(curChild, "name")) { tabletype = datasetname + TXMLParser.GetAttribute(curChild, "name"); } table.strDotNetName = tabletype; table.strVariableNameInDataset = variablename; // set tableid table.iOrder = DataSetTableIdCounter++; // TODO: can we derive from the base table, and just overload a few functions? CodeGenerationTable.InsertTableDefinition(snippetDataset, table, store.GetTable(table.tableorig), "TABLELOOP"); CodeGenerationTable.InsertRowDefinition(snippetDataset, table, store.GetTable(table.tableorig), "TABLELOOP"); } tables.Add(variablename, table); AddTableToDataset(tabletype, variablename, snippetDataset); } else if ((curChild.Name.ToLower() == "table") && TXMLParser.HasAttribute(curChild, "customtable")) { // this refers to a custom table of another dataset, eg. BestAddressTDSLocation // for the moment, such a table cannot have additional fields if (curChild.HasChildNodes) { throw new Exception( String.Format( "CreateTypedDataSets(): At the moment, a custom table referenced from another dataset cannot have additional fields. Dataset: {0}, Table: {1}", datasetname, TXMLParser.HasAttribute(curChild, "customtable"))); } // customtable has to contain the name of the dataset, eg. BestAddressTDSLocation string tabletype = TXMLParser.GetAttribute(curChild, "customtable"); string variablename = (TXMLParser.HasAttribute(curChild, "name") ? TXMLParser.GetAttribute(curChild, "name") : tabletype); AddTableToDataset(tabletype, variablename, snippetDataset); } if (curChild.Name.ToLower() == "customrelation") { ProcessTemplate tempSnippet = Template.GetSnippet("INITRELATIONS"); tempSnippet.SetCodelet("RELATIONNAME", TXMLParser.GetAttribute(curChild, "name")); tempSnippet.SetCodelet("TABLEVARIABLENAMEPARENT", TXMLParser.GetAttribute(curChild, "parentTable")); tempSnippet.SetCodelet("TABLEVARIABLENAMECHILD", TXMLParser.GetAttribute(curChild, "childTable")); tempSnippet.SetCodelet("COLUMNNAMESPARENT", StringCollectionToValuesFormattedForArray(tables, TXMLParser.GetAttribute(curChild, "parentTable"), StringHelper.StrSplit(TXMLParser.GetAttribute(curChild, "parentFields"), ","))); tempSnippet.SetCodelet("COLUMNNAMESCHILD", StringCollectionToValuesFormattedForArray(tables, TXMLParser.GetAttribute(curChild, "childTable"), StringHelper.StrSplit(TXMLParser.GetAttribute(curChild, "childFields"), ","))); tempSnippet.SetCodelet("CREATECONSTRAINTS", TXMLParser.GetBoolAttribute(curChild, "createConstraints") ? "true" : "false"); snippetDataset.InsertSnippet("INITRELATIONS", tempSnippet); } if (curChild.Name.ToLower() == "customtable") { string variablename = TXMLParser.GetAttribute(curChild, "name"); string tabletype = datasetname + TXMLParser.GetAttribute(curChild, "name"); XmlNode customTableNodes = curChild.FirstChild; TDataSetTable customTable = new TDataSetTable( tabletype, tabletype, variablename, null); // set TableId customTable.iOrder = DataSetTableIdCounter++; customTable.strDescription = TXMLParser.GetAttribute(curChild, "comment"); customTable.strName = tabletype; customTable.strDotNetName = tabletype; customTable.strVariableNameInDataset = variablename; while (customTableNodes != null) { if (customTableNodes.Name.ToLower() == "customfield") { TTableField customField = new TTableField(); customField.strName = TXMLParser.GetAttribute(customTableNodes, "name"); customField.strTypeDotNet = TXMLParser.GetAttribute(customTableNodes, "type"); customField.strDescription = TXMLParser.GetAttribute(customTableNodes, "comment"); customField.strDefault = TXMLParser.GetAttribute(customTableNodes, "initial"); customTable.grpTableField.Add(customField); } if (customTableNodes.Name.ToLower() == "field") { // eg. SelectedSiteKey in PartnerEditTDS.MiscellaneousData TTableField field = new TTableField(store.GetTable(TXMLParser.GetAttribute(customTableNodes, "sqltable")). GetField(TXMLParser.GetAttribute(customTableNodes, "sqlfield"))); if (TXMLParser.HasAttribute(customTableNodes, "name")) { field.strNameDotNet = TXMLParser.GetAttribute(customTableNodes, "name"); } if (TXMLParser.HasAttribute(customTableNodes, "comment")) { field.strDescription = TXMLParser.GetAttribute(customTableNodes, "comment"); } customTable.grpTableField.Add(field); } if (customTableNodes.Name.ToLower() == "primarykey") { TConstraint primKeyConstraint = new TConstraint(); primKeyConstraint.strName = "PK"; primKeyConstraint.strType = "primarykey"; primKeyConstraint.strThisFields = StringHelper.StrSplit(TXMLParser.GetAttribute(customTableNodes, "thisFields"), ","); customTable.grpConstraint.Add(primKeyConstraint); } customTableNodes = customTableNodes.NextSibling; } tables.Add(tabletype, customTable); AddTableToDataset(tabletype, variablename, snippetDataset); CodeGenerationTable.InsertTableDefinition(snippetDataset, customTable, null, "TABLELOOP"); CodeGenerationTable.InsertRowDefinition(snippetDataset, customTable, null, "TABLELOOP"); } curChild = curChild.NextSibling; } foreach (TDataSetTable table in tables.Values) { // todo? also other constraints, not only from original table? foreach (TConstraint constraint in table.grpConstraint) { if ((constraint.strType == "foreignkey") && tables.ContainsKey(constraint.strOtherTable)) { TDataSetTable otherTable = (TDataSetTable)tables[constraint.strOtherTable]; ProcessTemplate tempSnippet = Template.GetSnippet("INITCONSTRAINTS"); tempSnippet.SetCodelet("TABLEVARIABLENAME1", table.tablealias); tempSnippet.SetCodelet("TABLEVARIABLENAME2", otherTable.tablealias); tempSnippet.SetCodelet("CONSTRAINTNAME", TTable.NiceKeyName(constraint)); tempSnippet.SetCodelet("COLUMNNAMES1", StringCollectionToValuesFormattedForArray(constraint.strThisFields)); tempSnippet.SetCodelet("COLUMNNAMES2", StringCollectionToValuesFormattedForArray(constraint.strOtherFields)); snippetDataset.InsertSnippet("INITCONSTRAINTS", tempSnippet); } } } Template.InsertSnippet("CONTENTDATASETSANDTABLESANDROWS", snippetDataset); cur = TXMLParser.GetNextEntity(cur); } } Template.FinishWriting(AOutputPath + Path.DirectorySeparatorChar + AFilename + "-generated.cs", ".cs", true); }
/// <summary> /// This function checks if there is another constraint from the same table /// that references the current table through a link table /// </summary> /// <param name="AConstraints"></param> /// <param name="AConstraint"></param> /// <returns>true if other constraint exists</returns> public static Boolean FindOtherConstraintSameOtherTable2(ArrayList AConstraints, TConstraint AConstraint) { foreach (TConstraint myConstraint in AConstraints) { if ((myConstraint != AConstraint) && (myConstraint.strOtherTable == AConstraint.strOtherTable)) { return(true); } } return(false); }
/// <summary> /// This function checks if there is another constraint in this ATable, /// that references the same other table /// It will find the field that has a different name, so that names can be unique /// </summary> /// <param name="AConstraints"></param> /// <param name="AConstraint"></param> /// <returns>the field that is different in the two keys; empty string if there is no other constraint or no different field</returns> public static String FindOtherConstraintSameOtherTable(List <TConstraint> AConstraints, TConstraint AConstraint) { foreach (TConstraint myConstraint in AConstraints) { if (ValidForeignKeyConstraintForLoadVia(myConstraint) && (myConstraint != AConstraint) && (myConstraint.strOtherTable == AConstraint.strOtherTable)) { // find the field that is different in AConstraint and myConstraint foreach (string s in AConstraint.strThisFields) { if (!myConstraint.strThisFields.Contains(s)) { return(s); } } } } return(""); }
/// <summary> /// This function checks if there is another constraint from the same table /// that references the current table through a link table /// </summary> /// <param name="AConstraints"></param> /// <param name="AConstraint"></param> /// <returns>true if other constraint exists</returns> public static Boolean FindOtherConstraintSameOtherTable2(ArrayList AConstraints, TConstraint AConstraint) { foreach (TConstraint myConstraint in AConstraints) { if ((myConstraint != AConstraint) && (myConstraint.strOtherTable == AConstraint.strOtherTable)) { return true; } } return false; }
private static void PrintConstraint(TConstraint constraint, bool outline) { if (constraint.ConstraintName != null) { Console.WriteLine("\t\tconstraint name: {0}", constraint.ConstraintName.ToString()); } switch (constraint.Constraint_type) { case EConstraintType.notnull: Console.WriteLine("\t\tnot null"); break; case EConstraintType.primary_key: Console.WriteLine("\t\tprimary key"); if (outline) { string setstr = string.Empty; if (constraint.ColumnList != null) { for (int i = 0; i < constraint.ColumnList.Count; i++) { if (i != 0) { setstr += ","; } setstr += constraint.ColumnList.getObjectName(i).ToString(); } Console.WriteLine("\t\tprimary key columns: {0}", setstr); } } break; case EConstraintType.unique: Console.WriteLine("\t\tunique key"); if (outline) { string setstr = string.Empty; if (constraint.ColumnList != null) { for (int i = 0; i < constraint.ColumnList.Count; i++) { if (i != 0) { setstr += ","; } setstr += constraint.ColumnList.getObjectName(i).ToString(); } Console.WriteLine("\t\tcolumns: {0}", setstr); } } break; case EConstraintType.check: Console.WriteLine("\t\tcheck: {0}", constraint.CheckCondition.ToString()); break; case EConstraintType.foreign_key: case EConstraintType.reference: Console.WriteLine("\t\tforeign key"); if (outline) { string setstr = string.Empty; if (constraint.ColumnList != null) { for (int i = 0; i < constraint.ColumnList.Count; i++) { if (i != 0) { setstr += ","; } setstr += constraint.ColumnList.getObjectName(i).ToString(); } Console.WriteLine("\t\tcolumns: {0}", setstr); } } Console.WriteLine("\t\treferenced table: {0}", constraint.ReferencedObject.ToString()); if (constraint.ReferencedColumnList != null) { string setstr = string.Empty; for (int i = 0; i < constraint.ReferencedColumnList.Count; i++) { if (i != 0) { setstr += ","; } setstr += constraint.ReferencedColumnList.getObjectName(i).ToString(); } Console.WriteLine("\t\treferenced columns: {0}", setstr); } break; default: break; } }
/// <summary> /// insert the viaothertable functions for one specific constraint /// </summary> /// <param name="AStore"></param> /// <param name="AConstraint"></param> /// <param name="ACurrentTable"></param> /// <param name="AOtherTable"></param> /// <param name="ATemplate"></param> /// <param name="ASnippet"></param> private static void InsertViaOtherTableConstraint(TDataDefinitionStore AStore, TConstraint AConstraint, TTable ACurrentTable, TTable AOtherTable, ProcessTemplate ATemplate, ProcessTemplate ASnippet) { ATemplate.AddToCodelet("USINGNAMESPACES", GetNamespace(AOtherTable.strGroup), false); ProcessTemplate snippetViaTable = ATemplate.GetSnippet("VIAOTHERTABLE"); snippetViaTable.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(AOtherTable.strName)); snippetViaTable.SetCodelet("SQLOTHERTABLENAME", AOtherTable.strName); string ProcedureName = "Via" + TTable.NiceTableName(AOtherTable.strName); // check if other foreign key exists that references the same table, e.g. // PBankAccess.CountViaPPartnerPartnerKey // PBankAccess.CountViaPPartnerContactPartnerKey string DifferentField = FindOtherConstraintSameOtherTable(ACurrentTable.grpConstraint, AConstraint); if (DifferentField.Length != 0) { ProcedureName += TTable.NiceFieldName(DifferentField); } int notUsedInt; string formalParametersOtherPrimaryKey; string actualParametersOtherPrimaryKey; string dummy; PrepareCodeletsPrimaryKey(AOtherTable, out formalParametersOtherPrimaryKey, out actualParametersOtherPrimaryKey, out dummy, out notUsedInt); int numberFields; string namesOfThisTableFields; string notUsed; PrepareCodeletsForeignKey( AOtherTable, AConstraint, out notUsed, out notUsed, out notUsed, out numberFields, out namesOfThisTableFields); snippetViaTable.SetCodelet("VIAPROCEDURENAME", ProcedureName); snippetViaTable.SetCodelet("FORMALPARAMETERSOTHERPRIMARYKEY", formalParametersOtherPrimaryKey); snippetViaTable.SetCodelet("ACTUALPARAMETERSOTHERPRIMARYKEY", actualParametersOtherPrimaryKey); snippetViaTable.SetCodelet("NUMBERFIELDS", numberFields.ToString()); snippetViaTable.SetCodelet("NUMBERFIELDSOTHER", (actualParametersOtherPrimaryKey.Split(',').Length).ToString()); snippetViaTable.SetCodelet("THISTABLEFIELDS", namesOfThisTableFields); AddDirectReference(AConstraint); ASnippet.InsertSnippet("VIAOTHERTABLE", snippetViaTable); }
/// <summary> /// prepare some code for the via other linking table (bridge) /// </summary> /// <param name="AConstraint">the constraint between current and linking table</param> /// <param name="AConstraintOther">the constraint between linking table and other</param> /// <param name="whereClauseViaLinkTable"></param> /// <param name="whereClauseAllViaTables"></param> private static void PrepareCodeletsViaLinkTable( TConstraint AConstraint, TConstraint AConstraintOther, out string whereClauseViaLinkTable, out string whereClauseAllViaTables) { whereClauseViaLinkTable = ""; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { whereClauseViaLinkTable += " AND "; } string otherfield = AConstraint.strOtherFields[counterKeyField]; whereClauseViaLinkTable += "PUB_" + AConstraint.strThisTable + "." + field + " = PUB_" + AConstraint.strOtherTable + "." + otherfield; counterKeyField++; } whereClauseViaLinkTable += " AND "; whereClauseAllViaTables = whereClauseViaLinkTable; counterKeyField = 0; foreach (string field in AConstraintOther.strThisFields) { if (counterKeyField > 0) { whereClauseViaLinkTable += " AND "; whereClauseAllViaTables += " AND "; } string otherfield = AConstraintOther.strOtherFields[counterKeyField]; whereClauseViaLinkTable += "PUB_" + AConstraintOther.strThisTable + "." + field + " = ?"; whereClauseAllViaTables += "PUB_" + AConstraintOther.strThisTable + "." + field + " = PUB_" + AConstraintOther.strOtherTable + "." + otherfield; counterKeyField++; } }
/// <summary> /// get formal and actual parameters for a unique or primary key /// </summary> /// <param name="ACurrentTable"></param> /// <param name="AConstraint"></param> /// <param name="formalParametersKey"></param> /// <param name="actualParametersKey"></param> /// <param name="numberKeyColumns"></param> /// <param name="actualParametersToString"></param> private static void PrepareCodeletsKey( TTable ACurrentTable, TConstraint AConstraint, out string formalParametersKey, out string actualParametersKey, out string actualParametersToString, out int numberKeyColumns) { formalParametersKey = ""; actualParametersKey = ""; actualParametersToString = ""; numberKeyColumns = AConstraint.strThisFields.Count; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { formalParametersKey += ", "; actualParametersKey += ", "; actualParametersToString += " + \" \" + "; } TTableField typedField = ACurrentTable.GetField(field); formalParametersKey += typedField.GetDotNetType() + " A" + TTable.NiceFieldName(field); actualParametersKey += "A" + TTable.NiceFieldName(field); actualParametersToString += "A" + TTable.NiceFieldName(field) + ".ToString()"; counterKeyField++; } // for partners, show their names as well. This is used in function AddOrModifyRecord to show the users which values are different foreach (TTableField column in ACurrentTable.grpTableField) { if (column.strName.Contains("_name_")) { actualParametersToString += " + ExistingRecord[0]." + TTable.NiceFieldName(column) + ".ToString()"; } } }
/// <summary> /// parse the definition of one constraint (unique, primary or foreign key) /// </summary> /// <param name="cur2">the current node</param> /// <param name="AThisTableName">the name of the table that this constraint belongs to</param> /// <returns>the constraint</returns> protected TConstraint ParseConstraint(XmlNode cur2, String AThisTableName) { TConstraint element; element = new TConstraint(); element.strName = GetAttribute(cur2, "name"); // foreignkey, uniquekey, primarykey element.strType = cur2.Name; element.strThisTable = AThisTableName; element.strThisFields = StringHelper.StrSplit(GetAttribute(cur2, "thisFields"), ","); element.strOtherTable = GetAttribute(cur2, "otherTable"); element.strOtherFields = StringHelper.StrSplit(GetAttribute(cur2, "otherFields"), ","); return element; }
/// <summary> /// do we want a special load via function for this foreign key? /// </summary> /// <param name="AConstraint"></param> /// <returns></returns> public static Boolean ValidForeignKeyConstraintForLoadVia(TConstraint AConstraint) { return (AConstraint.strType == "foreignkey") && (!AConstraint.strThisFields.Contains("s_created_by_c")) && (!AConstraint.strThisFields.Contains("s_modified_by_c")) && (AConstraint.strThisTable != "a_ledger"); }
private static void AddDirectReference(TConstraint AConstraint) { DirectReferences.Add(AConstraint.strOtherTable + "," + StringHelper.StrMerge(AConstraint.strThisFields, ',') + "," + StringHelper.StrMerge(AConstraint.strOtherFields, ',')); }
/// <summary> /// this is for foreign keys, eg load all countries with currency EUR /// </summary> /// <param name="AStore"></param> /// <param name="ACurrentTable"></param> /// <param name="ATemplate"></param> /// <param name="ASnippet"></param> private static void InsertViaOtherTable(TDataDefinitionStore AStore, TTable ACurrentTable, ProcessTemplate ATemplate, ProcessTemplate ASnippet) { foreach (TConstraint myConstraint in ACurrentTable.grpConstraint) { if (ValidForeignKeyConstraintForLoadVia(myConstraint)) { TTable OtherTable = AStore.GetTable(myConstraint.strOtherTable); if (!LoadViaHasAlreadyBeenImplemented(myConstraint)) { InsertViaOtherTableConstraint(AStore, myConstraint, ACurrentTable, OtherTable, ATemplate, ASnippet); } // AccountHierarchy: there is no constraint that references Ledger directly, but constraint referencing the Account table with a key that contains the ledger reference // but because the key in Ledger is already the primary key, a LoadViaLedger is required. // other way round: p_foundation_proposal_detail has 2 constraints for foundation and foundationproposal foreach (string field in myConstraint.strOtherFields) { // get a constraint that is only based on that field TConstraint OtherLinkConstraint = OtherTable.GetConstraint(StringHelper.StrSplit(field, ",")); if ((OtherLinkConstraint != null) && ValidForeignKeyConstraintForLoadVia(OtherLinkConstraint)) { TConstraint NewConstraint = new TConstraint(); NewConstraint.strName = OtherLinkConstraint.strName + "forLoadVia"; NewConstraint.strType = "foreignkey"; NewConstraint.strThisTable = myConstraint.strThisTable; NewConstraint.strThisFields = StringHelper.StrSplit(myConstraint.strThisFields[myConstraint.strOtherFields.IndexOf( field)], ","); NewConstraint.strOtherTable = OtherLinkConstraint.strOtherTable; NewConstraint.strOtherFields = OtherLinkConstraint.strOtherFields; if (!LoadViaHasAlreadyBeenImplemented(NewConstraint)) { InsertViaOtherTableConstraint(AStore, NewConstraint, ACurrentTable, AStore.GetTable(OtherLinkConstraint.strOtherTable), ATemplate, ASnippet); } } } } } }
private static void AddDirectReference(TConstraint AConstraint) { DirectReferences.Add(AConstraint.strOtherTable + "," + StringHelper.StrMerge(AConstraint.strThisFields, ',') + "," + StringHelper.StrMerge(AConstraint.strOtherFields, ',')); }
private static void PrepareCodeletsForeignKey( TTable AOtherTable, TConstraint AConstraint, out string whereClauseForeignKey, out string whereClauseViaOtherTable, out string odbcParametersForeignKey, out int numberFields, out string namesOfThisTableFields) { whereClauseForeignKey = ""; whereClauseViaOtherTable = ""; numberFields = AConstraint.strThisFields.Count; namesOfThisTableFields = ""; odbcParametersForeignKey = "OdbcParameter[] ParametersArray = new OdbcParameter[" + AConstraint.strThisFields.Count.ToString() + "];" + Environment.NewLine; int counterKeyField = 0; foreach (string field in AConstraint.strThisFields) { if (counterKeyField > 0) { whereClauseForeignKey += " AND "; whereClauseViaOtherTable += " AND "; namesOfThisTableFields += ", "; } namesOfThisTableFields += '"' + AConstraint.strThisFields[counterKeyField] + '"'; string otherfield = AConstraint.strOtherFields[counterKeyField]; TTableField otherTypedField = AOtherTable.GetField(otherfield); whereClauseForeignKey += field + " = ?"; whereClauseViaOtherTable += "PUB_" + AConstraint.strThisTable + "." + field + " = PUB_" + AConstraint.strOtherTable + "." + otherfield; odbcParametersForeignKey += "ParametersArray[" + counterKeyField.ToString() + "] = " + "new OdbcParameter(\"\", " + CodeGenerationPetra.ToOdbcTypeString(otherTypedField) + (otherTypedField.iLength != -1 ? ", " + otherTypedField.iLength.ToString() : "") + ");" + Environment.NewLine; odbcParametersForeignKey += "ParametersArray[" + counterKeyField.ToString() + "].Value = " + "((object)(A" + TTable.NiceFieldName(otherfield) + "));" + Environment.NewLine; counterKeyField++; } }
/// <summary> /// do we want a special load via function for this foreign key? /// </summary> /// <param name="AConstraint"></param> /// <returns></returns> public static Boolean ValidForeignKeyConstraintForLoadVia(TConstraint AConstraint) { return((AConstraint.strType == "foreignkey") && (!AConstraint.strThisFields.Contains("s_created_by_c")) && (!AConstraint.strThisFields.Contains("s_modified_by_c")) && (AConstraint.strThisTable != "a_ledger")); }
/// <summary> /// insert the viaothertable functions for one specific constraint /// </summary> /// <param name="AStore"></param> /// <param name="AConstraint"></param> /// <param name="ACurrentTable"></param> /// <param name="AOtherTable"></param> /// <param name="ATemplate"></param> /// <param name="ASnippet"></param> private static void InsertViaOtherTableConstraint(TDataDefinitionStore AStore, TConstraint AConstraint, TTable ACurrentTable, TTable AOtherTable, ProcessTemplate ATemplate, ProcessTemplate ASnippet) { ATemplate.AddToCodelet("USINGNAMESPACES", GetNamespace(AOtherTable.strGroup), false); ProcessTemplate snippetViaTable = ATemplate.GetSnippet("VIAOTHERTABLE"); snippetViaTable.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(AOtherTable.strName)); snippetViaTable.SetCodelet("SQLOTHERTABLENAME", AOtherTable.strName); string ProcedureName = "Via" + TTable.NiceTableName(AOtherTable.strName); // check if other foreign key exists that references the same table, e.g. // PBankAccess.CountViaPPartnerPartnerKey // PBankAccess.CountViaPPartnerContactPartnerKey string DifferentField = FindOtherConstraintSameOtherTable(ACurrentTable.grpConstraint, AConstraint); if (DifferentField.Length != 0) { ProcedureName += TTable.NiceFieldName(DifferentField); } int notUsedInt; string formalParametersOtherPrimaryKey; string actualParametersOtherPrimaryKey; string dummy; PrepareCodeletsPrimaryKey(AOtherTable, out formalParametersOtherPrimaryKey, out actualParametersOtherPrimaryKey, out dummy, out notUsedInt); int numberFields; string namesOfThisTableFields; string notUsed; PrepareCodeletsForeignKey( AOtherTable, AConstraint, out notUsed, out notUsed, out notUsed, out numberFields, out namesOfThisTableFields); snippetViaTable.SetCodelet("VIAPROCEDURENAME", ProcedureName); snippetViaTable.SetCodelet("FORMALPARAMETERSOTHERPRIMARYKEY", formalParametersOtherPrimaryKey); snippetViaTable.SetCodelet("ACTUALPARAMETERSOTHERPRIMARYKEY", actualParametersOtherPrimaryKey); snippetViaTable.SetCodelet("NUMBERFIELDS", numberFields.ToString()); snippetViaTable.SetCodelet("NUMBERFIELDSOTHER", (actualParametersOtherPrimaryKey.Split(',').Length).ToString()); snippetViaTable.SetCodelet("THISTABLEFIELDS", namesOfThisTableFields); AddDirectReference(AConstraint); ASnippet.InsertSnippet("VIAOTHERTABLE", snippetViaTable); }
/// <summary> /// code for generating typed datasets /// </summary> /// <param name="AInputXmlfile"></param> /// <param name="AOutputPath"></param> /// <param name="ANameSpace"></param> /// <param name="store"></param> /// <param name="groups"></param> /// <param name="AFilename"></param> public static void CreateTypedDataSets(String AInputXmlfile, String AOutputPath, String ANameSpace, TDataDefinitionStore store, string[] groups, string AFilename) { Console.WriteLine("processing dataset " + ANameSpace); string templateDir = TAppSettingsManager.GetValue("TemplateDir", true); ProcessTemplate Template = new ProcessTemplate(templateDir + Path.DirectorySeparatorChar + "ORM" + Path.DirectorySeparatorChar + "DataSet.cs"); Template.AddSnippetsFromOtherFile(templateDir + Path.DirectorySeparatorChar + "ORM" + Path.DirectorySeparatorChar + "DataTable.cs"); DataSetTableIdCounter = Convert.ToInt16(TAppSettingsManager.GetValue("StartTableId")); // load default header with license and copyright Template.SetCodelet("GPLFILEHEADER", ProcessTemplate.LoadEmptyFileComment(templateDir)); Template.SetCodelet("NAMESPACE", ANameSpace); // if no dataset is defined yet in the xml file, the following variables can be empty Template.AddToCodelet("USINGNAMESPACES", ""); Template.AddToCodelet("CONTENTDATASETSANDTABLESANDROWS", ""); TXMLParser parserDataSet = new TXMLParser(AInputXmlfile, false); XmlDocument myDoc = parserDataSet.GetDocument(); XmlNode startNode = myDoc.DocumentElement; if (startNode.Name.ToLower() == "petradatasets") { XmlNode cur = TXMLParser.NextNotBlank(startNode.FirstChild); while ((cur != null) && (cur.Name.ToLower() == "importunit")) { Template.AddToCodelet("USINGNAMESPACES", "using " + TXMLParser.GetAttribute(cur, "name") + ";" + Environment.NewLine); cur = TXMLParser.GetNextEntity(cur); } while ((cur != null) && (cur.Name.ToLower() == "dataset")) { ProcessTemplate snippetDataset = Template.GetSnippet("TYPEDDATASET"); string datasetname = TXMLParser.GetAttribute(cur, "name"); snippetDataset.SetCodelet("DATASETNAME", datasetname); // INITCONSTRAINTS and INITRELATIONS can be empty snippetDataset.AddToCodelet("INITCONSTRAINTS", ""); snippetDataset.AddToCodelet("INITRELATIONS", ""); SortedList <string, TDataSetTable> tables = new SortedList <string, TDataSetTable>(); XmlNode curChild = cur.FirstChild; while (curChild != null) { if ((curChild.Name.ToLower() == "table") && TXMLParser.HasAttribute(curChild, "sqltable")) { bool OverloadTable = false; string tabletype = TTable.NiceTableName(TXMLParser.GetAttribute(curChild, "sqltable")); string variablename = (TXMLParser.HasAttribute(curChild, "name") ? TXMLParser.GetAttribute(curChild, "name") : tabletype); TDataSetTable table = new TDataSetTable( TXMLParser.GetAttribute(curChild, "sqltable"), tabletype, variablename, store.GetTable(tabletype)); XmlNode tableNodes = curChild.FirstChild; while (tableNodes != null) { if (tableNodes.Name.ToLower() == "customfield") { // eg. BestAddress in PartnerEditTDS.PPartnerLocation TTableField customField = new TTableField(); customField.strName = TXMLParser.GetAttribute(tableNodes, "name"); customField.strTypeDotNet = TXMLParser.GetAttribute(tableNodes, "type"); customField.strDescription = TXMLParser.GetAttribute(tableNodes, "comment"); customField.strDefault = TXMLParser.GetAttribute(tableNodes, "initial"); table.grpTableField.Add(customField); OverloadTable = true; } if (tableNodes.Name.ToLower() == "field") { // eg. UnitName in PartnerEditTDS.PPerson TTableField field = new TTableField(store.GetTable(TXMLParser.GetAttribute(tableNodes, "sqltable")). GetField(TXMLParser.GetAttribute(tableNodes, "sqlfield"))); if (TXMLParser.HasAttribute(tableNodes, "name")) { field.strNameDotNet = TXMLParser.GetAttribute(tableNodes, "name"); } if (TXMLParser.HasAttribute(tableNodes, "comment")) { field.strDescription = TXMLParser.GetAttribute(tableNodes, "comment"); } table.grpTableField.Add(field); OverloadTable = true; } if (tableNodes.Name.ToLower() == "primarykey") { TConstraint primKeyConstraint = table.GetPrimaryKey(); primKeyConstraint.strThisFields = StringHelper.StrSplit(TXMLParser.GetAttribute(tableNodes, "thisFields"), ","); OverloadTable = true; } tableNodes = tableNodes.NextSibling; } if (OverloadTable) { tabletype = datasetname + TTable.NiceTableName(table.strName); if (TXMLParser.HasAttribute(curChild, "name")) { tabletype = datasetname + TXMLParser.GetAttribute(curChild, "name"); } table.strDotNetName = tabletype; table.strVariableNameInDataset = variablename; // set tableid table.iOrder = DataSetTableIdCounter++; // TODO: can we derive from the base table, and just overload a few functions? CodeGenerationTable.InsertTableDefinition(snippetDataset, table, store.GetTable(table.tableorig), "TABLELOOP", true); CodeGenerationTable.InsertRowDefinition(snippetDataset, table, store.GetTable(table.tableorig), "TABLELOOP"); } tables.Add(variablename, table); AddTableToDataset(tabletype, variablename, snippetDataset); } else if ((curChild.Name.ToLower() == "table") && TXMLParser.HasAttribute(curChild, "customtable")) { // this refers to a custom table of another dataset, eg. BestAddressTDSLocation // for the moment, such a table cannot have additional fields if (curChild.HasChildNodes) { throw new Exception( String.Format( "CreateTypedDataSets(): At the moment, a custom table referenced from another dataset cannot have additional fields. Dataset: {0}, Table: {1}", datasetname, TXMLParser.HasAttribute(curChild, "customtable"))); } // customtable has to contain the name of the dataset, eg. BestAddressTDSLocation string tabletype = TXMLParser.GetAttribute(curChild, "customtable"); string variablename = (TXMLParser.HasAttribute(curChild, "name") ? TXMLParser.GetAttribute(curChild, "name") : tabletype); AddTableToDataset(tabletype, variablename, snippetDataset); } if (curChild.Name.ToLower() == "customrelation") { ProcessTemplate tempSnippet = Template.GetSnippet("INITRELATIONS"); tempSnippet.SetCodelet("RELATIONNAME", TXMLParser.GetAttribute(curChild, "name")); tempSnippet.SetCodelet("TABLEVARIABLENAMEPARENT", TXMLParser.GetAttribute(curChild, "parentTable")); tempSnippet.SetCodelet("TABLEVARIABLENAMECHILD", TXMLParser.GetAttribute(curChild, "childTable")); tempSnippet.SetCodelet("COLUMNNAMESPARENT", StringCollectionToValuesFormattedForArray(tables, TXMLParser.GetAttribute(curChild, "parentTable"), StringHelper.StrSplit(TXMLParser.GetAttribute(curChild, "parentFields"), ","))); tempSnippet.SetCodelet("COLUMNNAMESCHILD", StringCollectionToValuesFormattedForArray(tables, TXMLParser.GetAttribute(curChild, "childTable"), StringHelper.StrSplit(TXMLParser.GetAttribute(curChild, "childFields"), ","))); tempSnippet.SetCodelet("CREATECONSTRAINTS", TXMLParser.GetBoolAttribute(curChild, "createConstraints") ? "true" : "false"); snippetDataset.InsertSnippet("INITRELATIONS", tempSnippet); } if (curChild.Name.ToLower() == "customtable") { string variablename = TXMLParser.GetAttribute(curChild, "name"); string tabletype = datasetname + TXMLParser.GetAttribute(curChild, "name"); XmlNode customTableNodes = curChild.FirstChild; TDataSetTable customTable = new TDataSetTable( tabletype, tabletype, variablename, null); // set TableId customTable.iOrder = DataSetTableIdCounter++; customTable.strDescription = TXMLParser.GetAttribute(curChild, "comment"); customTable.strName = tabletype; customTable.strDotNetName = tabletype; customTable.strVariableNameInDataset = variablename; while (customTableNodes != null) { if (customTableNodes.Name.ToLower() == "customfield") { TTableField customField = new TTableField(); customField.strName = TXMLParser.GetAttribute(customTableNodes, "name"); customField.strTypeDotNet = TXMLParser.GetAttribute(customTableNodes, "type"); customField.strDescription = TXMLParser.GetAttribute(customTableNodes, "comment"); customField.strDefault = TXMLParser.GetAttribute(customTableNodes, "initial"); customTable.grpTableField.Add(customField); } if (customTableNodes.Name.ToLower() == "field") { // eg. SelectedSiteKey in PartnerEditTDS.MiscellaneousData TTableField field = new TTableField(store.GetTable(TXMLParser.GetAttribute(customTableNodes, "sqltable")). GetField(TXMLParser.GetAttribute(customTableNodes, "sqlfield"))); if (TXMLParser.HasAttribute(customTableNodes, "name")) { field.strNameDotNet = TXMLParser.GetAttribute(customTableNodes, "name"); } if (TXMLParser.HasAttribute(customTableNodes, "comment")) { field.strDescription = TXMLParser.GetAttribute(customTableNodes, "comment"); } customTable.grpTableField.Add(field); } if (customTableNodes.Name.ToLower() == "primarykey") { TConstraint primKeyConstraint = new TConstraint(); primKeyConstraint.strName = "PK"; primKeyConstraint.strType = "primarykey"; primKeyConstraint.strThisFields = StringHelper.StrSplit(TXMLParser.GetAttribute(customTableNodes, "thisFields"), ","); customTable.grpConstraint.Add(primKeyConstraint); } customTableNodes = customTableNodes.NextSibling; } tables.Add(tabletype, customTable); AddTableToDataset(tabletype, variablename, snippetDataset); CodeGenerationTable.InsertTableDefinition(snippetDataset, customTable, null, "TABLELOOP", true); CodeGenerationTable.InsertRowDefinition(snippetDataset, customTable, null, "TABLELOOP"); } curChild = curChild.NextSibling; } foreach (TDataSetTable table in tables.Values) { // todo? also other constraints, not only from original table? foreach (TConstraint constraint in table.grpConstraint) { if ((constraint.strType == "foreignkey") && tables.ContainsKey(constraint.strOtherTable)) { TDataSetTable otherTable = (TDataSetTable)tables[constraint.strOtherTable]; ProcessTemplate tempSnippet = Template.GetSnippet("INITCONSTRAINTS"); tempSnippet.SetCodelet("TABLEVARIABLENAME1", table.tablealias); tempSnippet.SetCodelet("TABLEVARIABLENAME2", otherTable.tablealias); tempSnippet.SetCodelet("CONSTRAINTNAME", TTable.NiceKeyName(constraint)); tempSnippet.SetCodelet("COLUMNNAMES1", StringCollectionToValuesFormattedForArray(constraint.strThisFields)); tempSnippet.SetCodelet("COLUMNNAMES2", StringCollectionToValuesFormattedForArray(constraint.strOtherFields)); snippetDataset.InsertSnippet("INITCONSTRAINTS", tempSnippet); } } } Template.InsertSnippet("CONTENTDATASETSANDTABLESANDROWS", snippetDataset); cur = TXMLParser.GetNextEntity(cur); } } Template.FinishWriting(AOutputPath + Path.DirectorySeparatorChar + AFilename + "-generated.cs", ".cs", true); }
private static void WriteConstraint(StreamWriter ASw, TTable ATable, TConstraint constr, Boolean onlyForeign, Boolean AAdd) { if (!onlyForeign && (constr.strType == "primarykey")) { ASw.WriteLine(","); ASw.WriteLine(" CONSTRAINT {0}", constr.strName); ASw.Write(" PRIMARY KEY ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); } if (!onlyForeign && (constr.strType == "uniquekey")) { ASw.WriteLine(","); ASw.WriteLine(" CONSTRAINT {0}", constr.strName); ASw.Write(" UNIQUE ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); } if (onlyForeign && (constr.strType == "foreignkey")) { ASw.WriteLine("ALTER TABLE {0}", ATable.strName); if (AAdd) { ASw.WriteLine(" ADD CONSTRAINT {0}", constr.strName); ASw.WriteLine(" FOREIGN KEY ({0})", StringHelper.StrMerge(constr.strThisFields, ',')); ASw.WriteLine(" REFERENCES {0}({1});", constr.strOtherTable, StringHelper.StrMerge(constr.strOtherFields, ',')); } else { ASw.WriteLine(" DROP CONSTRAINT IF EXISTS {0};", constr.strName); } } }
/// <summary> /// create the code for the definition of a typed table /// </summary> /// <param name="Template"></param> /// <param name="currentTable"></param> /// <param name="origTable"></param> /// <param name="WhereToInsert"></param> /// <param name="CalledFromDataSet"></param> public static void InsertTableDefinition(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert, Boolean CalledFromDataSet) { ProcessTemplate snippet = Template.GetSnippet("TYPEDTABLE"); string derivedTable = ""; if (origTable != null) { snippet.SetCodelet("BASECLASSTABLE", TTable.NiceTableName(currentTable.strName) + "Table"); derivedTable = "new "; snippet.SetCodelet("TABLEID", origTable.iOrder.ToString()); } else { snippet.SetCodelet("BASECLASSTABLE", "TTypedDataTable"); snippet.SetCodelet("TABLEID", currentTable.iOrder.ToString()); } snippet.SetCodelet("NEW", derivedTable); snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription); snippet.SetCodelet("TABLENAME", currentTable.strDotNetName); if (CalledFromDataSet) { snippet.SetCodelet("TABLEINTDS", "TableInTDS"); } else { snippet.SetCodelet("TABLEINTDS", ""); } if (currentTable.AvailableForCustomReport) { snippet.SetCodelet("AVAILABLEFORCUSTOMREPORT", "true"); } else { snippet.SetCodelet("AVAILABLEFORCUSTOMREPORT", "false"); } snippet.SetCodelet("CUSTOMREPORTPERMISSION", currentTable.CustomReportPermission); if (currentTable.strVariableNameInDataset != null) { snippet.SetCodelet("TABLEVARIABLENAME", currentTable.strVariableNameInDataset); } else { snippet.SetCodelet("TABLEVARIABLENAME", currentTable.strDotNetName); } snippet.SetCodelet("DBTABLENAME", currentTable.strName); snippet.SetCodelet("DBTABLELABEL", currentTable.strLabel); if (currentTable.HasPrimaryKey()) { TConstraint primKey = currentTable.GetPrimaryKey(); bool first = true; string primaryKeyColumns = ""; int prevIndex = -1; // the fields in the primary key should be used in the same order as in the table. // otherwise this is causing confusion. eg. a_processed_fee foreach (TTableField column in currentTable.grpTableField) { int newIndex = -1; if (primKey.strThisFields.Contains(column.strName)) { newIndex = primKey.strThisFields.IndexOf(column.strName); } else if (primKey.strThisFields.Contains(TTable.NiceFieldName(column))) { newIndex = primKey.strThisFields.IndexOf(TTable.NiceFieldName(column)); } if (newIndex != -1) { if (newIndex < prevIndex) { throw new Exception("Please fix the order of the fields in the primary key of table " + currentTable.strName); } prevIndex = newIndex; } } // the fields in the primary key should be used in the same order as in the table. // otherwise this is causing confusion. eg. a_processed_fee foreach (TTableField column in currentTable.grpTableField) { if (primKey.strThisFields.Contains(column.strName) || primKey.strThisFields.Contains(TTable.NiceFieldName(column))) { string columnName = column.strName; string toAdd = currentTable.grpTableField.IndexOf(currentTable.GetField(columnName)).ToString(); if (!first) { toAdd = ", " + toAdd; primaryKeyColumns += ","; } first = false; snippet.AddToCodelet("COLUMNPRIMARYKEYORDER", toAdd); primaryKeyColumns += "Column" + TTable.NiceFieldName(currentTable.GetField(columnName)); } } if (primaryKeyColumns.Length > 0) { snippet.SetCodelet("PRIMARYKEYCOLUMNS", primaryKeyColumns); snippet.SetCodelet("PRIMARYKEYCOLUMNSCOUNT", primKey.strThisFields.Count.ToString()); } } else { snippet.AddToCodelet("COLUMNPRIMARYKEYORDER", ""); } if (currentTable.HasUniqueKey()) { TConstraint primKey = currentTable.GetFirstUniqueKey(); bool first = true; foreach (string columnName in primKey.strThisFields) { string toAdd = currentTable.grpTableField.IndexOf(currentTable.GetField(columnName)).ToString(); if (!first) { toAdd = ", " + toAdd; } first = false; snippet.AddToCodelet("COLUMNUNIQUEKEYORDER", toAdd); } } else { snippet.AddToCodelet("COLUMNUNIQUEKEYORDER", ""); } int colOrder = 0; Boolean CustomReportFieldAdded = false; ProcessTemplate tempTemplate = null; foreach (TTableField col in currentTable.grpTableField) { col.strTableName = currentTable.strName; string columnOverwrite = ""; bool writeColumnProperties = true; if ((origTable != null) && (origTable.GetField(col.strName, false) != null)) { columnOverwrite = "new "; if (origTable.GetField(col.strName).iOrder == colOrder) { // same order number, save some lines of code by not writing them writeColumnProperties = false; } } if (writeColumnProperties && (columnOverwrite.Length == 0)) { tempTemplate = Template.GetSnippet("DATACOLUMN"); tempTemplate.SetCodeletComment("COLUMN_DESCRIPTION", col.strDescription); tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col)); snippet.InsertSnippet("DATACOLUMNS", tempTemplate); } if (writeColumnProperties) { tempTemplate = Template.GetSnippet("COLUMNIDS"); tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col)); tempTemplate.SetCodelet("COLUMNORDERNUMBER", colOrder.ToString()); tempTemplate.SetCodelet("NEW", columnOverwrite); snippet.InsertSnippet("COLUMNIDS", tempTemplate); } if (origTable == null) { tempTemplate = Template.GetSnippet("COLUMNINFO"); tempTemplate.SetCodelet("COLUMNORDERNUMBER", colOrder.ToString()); tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col)); tempTemplate.SetCodelet("COLUMNDBNAME", col.strName); tempTemplate.SetCodelet("COLUMNLABEL", col.strLabel); tempTemplate.SetCodelet("COLUMNODBCTYPE", CodeGenerationPetra.ToOdbcTypeString(col)); tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString()); tempTemplate.SetCodelet("COLUMNNOTNULL", col.bNotNull.ToString().ToLower()); tempTemplate.SetCodelet("COLUMNCOMMA", colOrder + 1 < currentTable.grpTableField.Count ? "," : ""); snippet.InsertSnippet("COLUMNINFO", tempTemplate); } tempTemplate = Template.GetSnippet("INITCLASSADDCOLUMN"); tempTemplate.SetCodelet("COLUMNDBNAME", col.strName); tempTemplate.SetCodelet("COLUMNDOTNETTYPE", col.GetDotNetType()); tempTemplate.SetCodelet("COLUMNDOTNETTYPENOTNULLABLE", col.GetDotNetType().Replace("?", "")); snippet.InsertSnippet("INITCLASSADDCOLUMN", tempTemplate); tempTemplate = Template.GetSnippet("INITVARSCOLUMN"); tempTemplate.SetCodelet("COLUMNDBNAME", col.strName); tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col)); snippet.InsertSnippet("INITVARSCOLUMN", tempTemplate); if (col.bAvailableForCustomReport) { tempTemplate = Template.GetSnippet("INITVARSCUSTOMREPORTFIELDLIST"); if (CustomReportFieldAdded) { tempTemplate.SetCodelet("LISTDELIMITER", ","); } else { tempTemplate.SetCodelet("LISTDELIMITER", ""); } tempTemplate.SetCodelet("COLUMNDBNAME", col.strName); snippet.InsertSnippet("INITVARSCUSTOMREPORTFIELDLIST", tempTemplate); CustomReportFieldAdded = true; } if (writeColumnProperties) { tempTemplate = Template.GetSnippet("STATICCOLUMNPROPERTIES"); tempTemplate.SetCodelet("COLUMNDBNAME", col.strName); tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col)); tempTemplate.SetCodelet("COLUMNHELP", col.strHelp.Replace("\"", "\\\"")); tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString()); tempTemplate.SetCodelet("NEW", columnOverwrite); snippet.InsertSnippet("STATICCOLUMNPROPERTIES", tempTemplate); } colOrder++; } if (!CustomReportFieldAdded) { // fill snippet if nothing was added yet tempTemplate = Template.GetSnippet("INITVARSCUSTOMREPORTFIELDLISTEMPTY"); tempTemplate.SetCodelet("EMPTY", ""); snippet.InsertSnippet("INITVARSCUSTOMREPORTFIELDLIST", tempTemplate); } Template.InsertSnippet(WhereToInsert, snippet); }
/// <summary> /// This function checks if there is another constraint in this ATable, /// that references the same other table /// It will find the field that has a different name, so that names can be unique /// </summary> /// <param name="AConstraints"></param> /// <param name="AConstraint"></param> /// <returns>the field that is different in the two keys; empty string if there is no other constraint or no different field</returns> public static String FindOtherConstraintSameOtherTable(List <TConstraint>AConstraints, TConstraint AConstraint) { foreach (TConstraint myConstraint in AConstraints) { if (ValidForeignKeyConstraintForLoadVia(myConstraint) && (myConstraint != AConstraint) && (myConstraint.strOtherTable == AConstraint.strOtherTable)) { // find the field that is different in AConstraint and myConstraint foreach (string s in AConstraint.strThisFields) { if (!myConstraint.strThisFields.Contains(s)) { return s; } } } } return ""; }