private static void PrepareCodeletsPrimaryKey(
TTable ACurrentTable,
out string csvListPrimaryKeyFields,
out string formalParametersPrimaryKey,
out string actualParametersPrimaryKey,
out Tuple <string, string, string>[] formalParametersPrimaryKeySeparate)
{
csvListPrimaryKeyFields = "";
formalParametersPrimaryKey = "";
actualParametersPrimaryKey = "";
int counterPrimaryKeyField = 0;
string PrimaryKeyLabel;
List <string>PrimaryKeyLabels = new List <string>();
if (!ACurrentTable.HasPrimaryKey())
{
formalParametersPrimaryKeySeparate = new Tuple <string, string, string>[0];
return;
}
formalParametersPrimaryKeySeparate = new Tuple <string, string, string>[ACurrentTable.GetPrimaryKey().strThisFields.Count];
foreach (string field in ACurrentTable.GetPrimaryKey().strThisFields)
{
if (counterPrimaryKeyField > 0)
{
csvListPrimaryKeyFields += ",";
formalParametersPrimaryKey += ", ";
actualParametersPrimaryKey += ", ";
}
TTableField typedField = ACurrentTable.GetField(field);
PrimaryKeyLabel = typedField.strLabel;
// Ensure that a table definition doesn't contain Primary Key columns with identical Labels as this would lead
// to generated cascading count code that would fail at runtime as it would want to add items with the same
// name to the 'PKInfo' dictionary! (Those labels would be wrong anyhow!)
if (!PrimaryKeyLabels.Contains(PrimaryKeyLabel))
{
PrimaryKeyLabels.Add(PrimaryKeyLabel);
}
else
{
throw new Exception(
String.Format(
"The DB table definition of Table '{0}' in petra.xml contains two Columns with the same Label, '{1}'. That is not allowed!",
ACurrentTable.strName, PrimaryKeyLabel));
}
csvListPrimaryKeyFields += field;
formalParametersPrimaryKey += typedField.GetDotNetType() + " A" + TTable.NiceFieldName(field);
actualParametersPrimaryKey += "A" + TTable.NiceFieldName(field);
formalParametersPrimaryKeySeparate[counterPrimaryKeyField] = new Tuple <string, string, string>(
typedField.GetDotNetType(), " A" + TTable.NiceFieldName(field), PrimaryKeyLabel);
counterPrimaryKeyField++;
}
}
private void DumpTable(ref StreamWriter sw, TTable oldTable)
{
// check for parameter, only dump table if parameter empty or equals the table name
sw.WriteLine("IF WantThisTable(\"" + oldTable.strName + "\") THEN DO:");
sw.WriteLine("OUTPUT STREAM OutFile TO fulldump/" + oldTable.strName + ".d.");
sw.WriteLine("REPEAT FOR " + oldTable.strName + ':');
sw.WriteLine(" FIND NEXT " + oldTable.strName + " NO-LOCK.");
sw.WriteLine(" EXPORT STREAM OutFile DELIMITER \" \" ");
foreach (TTableField field in oldTable.grpTableField)
{
if (field.strName == "s_modification_id_t")
{
// improve readability, safe space
sw.Write(" ? ");
}
else
{
sw.Write(" " + field.strName + " ");
}
}
sw.WriteLine(".");
sw.WriteLine("END.");
sw.WriteLine("PUT STREAM OutFile UNFORMATTED '.'.");
sw.WriteLine("OUTPUT STREAM OutFile CLOSE.");
// now if we are on Linux, gzip that file
sw.WriteLine("RUN ZipThisTable(\"" + oldTable.strName + "\").");
sw.WriteLine("END.");
}
/// <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 InsertMainProcedures(TDataDefinitionStore AStore,
TTable ACurrentTable,
ProcessTemplate ATemplate,
ProcessTemplate ASnippet)
{
ASnippet.SetCodelet("TABLENAME", TTable.NiceTableName(ACurrentTable.strName));
ASnippet.SetCodeletComment("TABLE_DESCRIPTION", ACurrentTable.strDescription);
ASnippet.SetCodelet("SQLTABLENAME", ACurrentTable.strName);
ASnippet.SetCodelet("VIAOTHERTABLE", "");
ASnippet.SetCodelet("VIALINKTABLE", "");
string formalParametersPrimaryKey;
string actualParametersPrimaryKey;
string actualParametersPrimaryKeyToString;
int numberPrimaryKeyColumns;
PrepareCodeletsPrimaryKey(ACurrentTable,
out formalParametersPrimaryKey,
out actualParametersPrimaryKey,
out actualParametersPrimaryKeyToString,
out numberPrimaryKeyColumns);
ASnippet.SetCodelet("FORMALPARAMETERSPRIMARYKEY", formalParametersPrimaryKey);
ASnippet.SetCodelet("ACTUALPARAMETERSPRIMARYKEY", actualParametersPrimaryKey);
ASnippet.SetCodelet("ACTUALPARAMETERSPRIMARYKEYTOSTRING", actualParametersPrimaryKeyToString);
ASnippet.SetCodelet("PRIMARYKEYNUMBERCOLUMNS", numberPrimaryKeyColumns.ToString());
string formalParametersUniqueKey;
string actualParametersUniqueKey;
int numberUniqueKeyColumns;
PrepareCodeletsUniqueKey(ACurrentTable,
out formalParametersUniqueKey,
out actualParametersUniqueKey,
out numberUniqueKeyColumns);
ASnippet.SetCodelet("FORMALPARAMETERSUNIQUEKEY", formalParametersUniqueKey);
ASnippet.SetCodelet("ACTUALPARAMETERSUNIQUEKEY", actualParametersUniqueKey);
ASnippet.SetCodelet("UNIQUEKEYNUMBERCOLUMNS", numberUniqueKeyColumns.ToString());
ASnippet.SetCodelet("SEQUENCENAMEANDFIELD", "");
foreach (TTableField tablefield in ACurrentTable.grpTableField)
{
// is there a field filled by a sequence?
// yes: get the next value of that sequence and assign to row
if (tablefield.strSequence.Length > 0)
{
ASnippet.SetCodelet("SEQUENCENAMEANDFIELD", ", \"" + tablefield.strSequence + "\", \"" + tablefield.strName + "\"");
// assume only one sequence per table
break;
}
}
}
private static void PrepareCodeletsPrimaryKey(
TTable ACurrentTable,
out string formalParametersPrimaryKey,
out string actualParametersPrimaryKey,
out string actualParametersPrimaryKeyToString,
out int numberPrimaryKeyColumns)
{
formalParametersPrimaryKey = "";
actualParametersPrimaryKey = "";
actualParametersPrimaryKeyToString = "";
numberPrimaryKeyColumns = 0;
if (!ACurrentTable.HasPrimaryKey())
{
return;
}
PrepareCodeletsKey(ACurrentTable,
ACurrentTable.GetPrimaryKey(),
out formalParametersPrimaryKey,
out actualParametersPrimaryKey,
out actualParametersPrimaryKeyToString,
out numberPrimaryKeyColumns);
}
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++;
}
}
private void modifyTableName(TCustomSqlStatement stmt)
{
for (int k = 0; k < stmt.tables.size(); k++)
{
TTable table = stmt.tables.getTable(k);
bool isfound = false;
for (int m = 0; m < table.LinkedColumns.size(); m++)
{
TObjectName column = table.LinkedColumns.getObjectName(m);
isfound = false;
if (column.TableString != null && column.TableString.Equals(this.sourceTable, StringComparison.OrdinalIgnoreCase))
{
isfound = true;
if (!string.ReferenceEquals(this.sourceSchema, null))
{ // check schema of this
// table
if (column.SchemaString != null)
{
isfound = this.sourceSchema.Equals(column.SchemaString, StringComparison.OrdinalIgnoreCase);
}
else
{
isfound = false;
}
}
}
if (isfound)
{
// rename this table
TSourceToken st = column.ColumnToken;
if (column.TableString != null)
{
column.TableToken.String = this.targetTable;
}
else
{
st.String = this.targetTable + "." + st.astext;
}
if (!string.ReferenceEquals(this.targetSchema, null))
{
if (column.SchemaString != null)
{
column.SchemaToken.String = this.targetSchema;
}
else if (column.TableString != null)
{
column.TableToken.String = this.targetSchema + "." + column.TableString;
}
else
{
st.String = this.targetSchema + "." + st.astext;
}
}
this.renamedObjectsNum++;
}
}
if (table.TableName != null && table.TableName.TableString != null && table.TableName.TableString.Equals(this.sourceTable, StringComparison.OrdinalIgnoreCase))
{
isfound = true;
if (!string.ReferenceEquals(this.sourceSchema, null))
{ // check schema of this table
if (table.TableName.SchemaString != null)
{
isfound = this.sourceSchema.Equals(table.TableName.SchemaString, StringComparison.OrdinalIgnoreCase);
}
else
{
isfound = false;
}
}
}
if (isfound)
{
if (table.TableName != null)
{
// rename this table
TSourceToken st = table.TableName.TableToken;
st.String = this.targetTable;
if (!string.ReferenceEquals(this.targetSchema, null))
{
if (table.PrefixSchema != null)
{
table.TableName.SchemaToken.String = this.targetSchema;
}
else
{
st.String = this.targetSchema + "." + st.astext;
}
}
this.renamedObjectsNum++;
}
}
}
for (int j = 0; j < stmt.Statements.size(); j++)
{
modifyTableName(stmt.Statements.get(j));
}
}
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);
}
}
}
static private void ProcessLine(SqliteCommand cmd, string line, TTable table, StringCollection AColumnNames)
{
int count = 0;
string Separator = "\t";
line = line.Replace("\\N", "?");
if (!line.Contains(Separator))
{
Separator = ",";
}
foreach (string columnname in AColumnNames)
{
TTableField field = table.GetField(columnname);
Object val = StringHelper.GetNextCSV(ref line, Separator);
if (val.ToString() == "?")
{
val = null;
}
else if ((field.strType == "date") && (val.ToString().Length != 0))
{
if (val.ToString().Contains("-"))
{
StringCollection dateString = StringHelper.StrSplit(val.ToString(), "-");
val = new DateTime(Convert.ToInt16(dateString[0]),
Convert.ToInt16(dateString[1]),
Convert.ToInt16(dateString[2]));
}
else
{
try
{
val = new DateTime(Convert.ToInt16(val.ToString().Substring(0, 3)),
Convert.ToInt16(val.ToString().Substring(4, 2)),
Convert.ToInt16(val.ToString().Substring(6, 2)));
}
catch (Exception e)
{
TLogging.Log(e.ToString());
throw new Exception("error parsing date time " + val);
}
}
}
else if (field.strType == "bit")
{
val = (val.ToString() == "true") || (val.ToString() == "t");
}
cmd.Parameters[count].Value = val;
count++;
}
}
/// <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);
}
public static void bindCreateModel(TTable table, Table tableModel)
{
createModelBindingMap[table] = tableModel;
}
public static TObjectName[] getTableColumns(TTable table)
{
Table createTable = ModelBindingManager.getCreateTable(table);
if (createTable != null)
{
IList <TableColumn> columnList = createTable.Columns;
TObjectName[] columns = new TObjectName[columnList.Count];
for (int i = 0; i < columns.Length; i++)
{
columns[i] = columnList[i].ColumnObject;
}
Array.Sort(columns, new ComparatorAnonymousInnerClass());
return(columns);
}
else
{
TObjectNameList list = table.ObjectNameReferences;
List <TObjectName> columns = new List <TObjectName>();
if (list.size() == 0 && table.Subquery != null)
{
ResultSet resultSet = (ResultSet)ModelBindingManager.getModel(table.Subquery);
if (resultSet != null)
{
IList <ResultColumn> columnList = resultSet.Columns;
for (int i = 0; i < columnList.Count; i++)
{
ResultColumn resultColumn = columnList[i];
if (resultColumn.ColumnObject is TResultColumn)
{
TResultColumn columnObject = ((TResultColumn)resultColumn.ColumnObject);
TAliasClause alias = columnObject.AliasClause;
if (alias != null && alias.AliasName != null)
{
columns.Add(alias.AliasName);
}
else
{
if (columnObject.FieldAttr != null)
{
columns.Add(columnObject.FieldAttr);
}
else
{
continue;
}
}
}
else if (resultColumn.ColumnObject is TObjectName)
{
columns.Add((TObjectName)resultColumn.ColumnObject);
}
}
}
}
else
{
for (int i = 0; i < list.size(); i++)
{
columns.Add(list.getObjectName(i));
}
}
TObjectName[] columnArray = columns.ToArray();
Array.Sort(columnArray, new ComparatorAnonymousInnerClass());
return(columnArray);
}
}
public static Table getCreateModel(TTable table)
{
return((Table)createModelBindingMap[table]);
}
internal virtual string remove(TCustomSqlStatement stat)
{
if (stat.ResultColumnList != null)
{
for (int j = 0; j < stat.ResultColumnList.size(); j++)
{
TResultColumn column = stat.ResultColumnList.getResultColumn(j);
if (column.Expr != null && column.Expr.SubQuery is TCustomSqlStatement)
{
TCustomSqlStatement query = (TCustomSqlStatement)column.Expr.SubQuery;
getParserString(query);
}
}
}
if (stat.CteList != null)
{
for (int i = 0; i < stat.CteList.size(); i++)
{
TCTE cte = stat.CteList.getCTE(i);
if (cte.Subquery != null)
{
getParserString(cte.Subquery);
}
if (cte.InsertStmt != null)
{
getParserString(cte.InsertStmt);
}
if (cte.UpdateStmt != null)
{
getParserString(cte.UpdateStmt);
}
if (cte.PreparableStmt != null)
{
getParserString(cte.PreparableStmt);
}
if (cte.DeleteStmt != null)
{
getParserString(cte.DeleteStmt);
}
}
}
if (stat is TSelectSqlStatement && ((TSelectSqlStatement)stat).SetOperator != TSelectSqlStatement.setOperator_none)
{
TSelectSqlStatement select = ((TSelectSqlStatement)stat);
getParserString(select.LeftStmt);
getParserString(select.RightStmt);
return(select.ToScript());
}
if (stat.Statements != null && stat.Statements.size() > 0)
{
for (int i = 0; i < stat.Statements.size(); i++)
{
getParserString(stat.Statements.get(i));
}
}
if (stat.ReturningClause != null)
{
if (stat.ReturningClause.ColumnValueList != null)
{
for (int i = 0; i < stat.ReturningClause.ColumnValueList.size(); i++)
{
if (stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.ColumnValueList.getExpression(i).SubQuery);
}
}
}
if (stat.ReturningClause.VariableList != null)
{
for (int i = 0; i < stat.ReturningClause.VariableList.size(); i++)
{
if (stat.ReturningClause.VariableList.getExpression(i).SubQuery != null)
{
getParserString(stat.ReturningClause.VariableList.getExpression(i).SubQuery);
}
}
}
}
if (stat is TSelectSqlStatement)
{
TTableList list = ((TSelectSqlStatement)stat).tables;
for (int i = 0; i < list.size(); i++)
{
TTable table = list.getTable(i);
if (table.Subquery != null)
{
getParserString(table.Subquery);
}
if (table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(table.FuncCall);
}
}
}
if (stat is TSelectSqlStatement)
{
TJoinList list = ((TSelectSqlStatement)stat).joins;
for (int i = 0; i < list.size(); i++)
{
TJoin join = list.getJoin(i);
for (int j = 0; j < join.JoinItems.size(); j++)
{
TJoinItem joinItem = join.JoinItems.getJoinItem(j);
if (joinItem.Table != null)
{
if (joinItem.Table.Subquery != null)
{
getParserString(joinItem.Table.Subquery);
}
if (joinItem.Table.FuncCall != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkFunctionCall(joinItem.Table.FuncCall);
}
}
if (joinItem.OnCondition != null)
{
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(joinItem.OnCondition);
}
}
}
}
if (stat is TSelectSqlStatement)
{
TSelectSqlStatement select = (TSelectSqlStatement)stat;
for (int i = 0; i < select.ResultColumnList.size(); i++)
{
TResultColumn field = select.ResultColumnList.getResultColumn(i);
TExpression expr = field.Expr;
if (expr != null && expr.ExpressionType == EExpressionType.subquery_t)
{
getParserString(expr.SubQuery);
}
}
}
if (stat.WhereClause != null && stat.WhereClause.Condition != null && stat.WhereClause.Condition.ToScript().Trim().Length > 0)
{
TExpression whereExpression = stat.Gsqlparser.parseExpression(stat.WhereClause.Condition.ToScript());
if (string.ReferenceEquals(whereExpression.ToString(), null))
{
removevars removevars = new removevars(stat.ToString(), stat.dbvendor);
return(removevars.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(whereExpression);
stat.WhereClause.Condition = stat.Gsqlparser.parseExpression(whereExpression.ToScript());
}
}
if ((stat is TSelectSqlStatement) && ((TSelectSqlStatement)stat).GroupByClause != null && ((TSelectSqlStatement)stat).GroupByClause.HavingClause != null)
{
TExpression havingExpression = ((TSelectSqlStatement)stat).GroupByClause.HavingClause;
if (havingExpression == null)
{
removevars removeCondition = new removevars(stat.ToScript(), stat.dbvendor);
return(removeCondition.result);
}
else
{
string oldString = stat.ToScript();
conditionBuffer.Remove(0, conditionBuffer.Length);
ExpressionChecker w = new ExpressionChecker(this);
w.checkExpression(havingExpression);
string newString = stat.ToScript();
if (!oldString.Equals(newString))
{
if (havingExpression != null && havingExpression.ToScript().Trim().Length == 0)
{
((TSelectSqlStatement)stat).GroupByClause = null;
}
}
}
}
return(stat.ToScript());
}
/// <summary>
/// write the definition for the code of validation of a typed table
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
public static void InsertTableValidation(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
ProcessTemplate snippet = Template.GetSnippet("TABLEVALIDATION");
ProcessTemplate deletableRowSnippet = Template.GetSnippet("SNIPDELETABLEROWVALIDATION");
string ReasonForAutomValidation;
bool CheckForEmptyDateGenerated;
bool FoundDeletableRowValidation = false;
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
foreach (TTableField col in currentTable.grpTableField)
{
ProcessTemplate columnTemplate;
ProcessTemplate validateColumnTemplate;
if ((col.strNameDotNet == "Deletable") || (col.strNameDotNet == "DeletableFlag") || (col.strNameDotNet == "TypeDeletable"))
{
deletableRowSnippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
deletableRowSnippet.SetCodelet("COLUMNNAME", col.strNameDotNet);
FoundDeletableRowValidation = true;
}
CheckForEmptyDateGenerated = false;
// NOT NULL checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsNotNullChecks,
currentTable.grpConstraint, out ReasonForAutomValidation))
{
if (col.GetDotNetType().Contains("DateTime"))
{
// CHECKEMPTYDATE has NULL as invalid so we use this test with VALIDATECOLUMN2 (test not enclosed in 'if')
validateColumnTemplate = Template.GetSnippet("CHECKEMPTYDATE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
CheckForEmptyDateGenerated = true;
}
else
{
// Check all other types with a general NOT NULL check - again using VALIDATECOLUMN2 (test not enclosed in 'if')
validateColumnTemplate = Template.GetSnippet("CHECKGENERALNOTNULL");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
// Additionally we do not allow empty string in primary keys or columns that are foreign keys
if (col.GetDotNetType().Contains("String") && ReasonForAutomValidation.Contains(" and "))
{
validateColumnTemplate = Template.GetSnippet("CHECKEMPTYSTRING");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
if (!CheckForEmptyDateGenerated)
{
// Date checks
// If a NULL date is not allowed we will have already tested for that above
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsDateChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN2");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
// CHECKVALIDDATE allows NULL to be valid but ensures that otherwise the date is correctly formed
validateColumnTemplate = Template.GetSnippet("CHECKVALIDDATE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("COLUMNLENGTH", (col.iCharLength * 2).ToString());
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
// String Length checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsStringLengthChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate = Template.GetSnippet("CHECKSTRINGLENGTH");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("COLUMNLENGTH", (col.iCharLength * 2).ToString());
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
// Number Range checks
if (TDataValidation.GenerateAutoValidationCodeForDBTableField(col, TDataValidation.TAutomDataValidationScope.advsNumberRangeChecks,
null, out ReasonForAutomValidation))
{
columnTemplate = Template.GetSnippet("VALIDATECOLUMN");
columnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate = Template.GetSnippet("CHECKNUMBERRANGE");
validateColumnTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
validateColumnTemplate.SetCodelet("NUMBEROFDECIMALDIGITS", col.iLength.ToString());
validateColumnTemplate.SetCodelet("NUMBEROFFRACTIONALDIGITS", col.iDecimals > 0 ? col.iDecimals.ToString() : "0");
columnTemplate.InsertSnippet("COLUMNSPECIFICCHECK", validateColumnTemplate);
columnTemplate.SetCodelet("COLUMNSPECIFICCOMMENT", "'" + col.strNameDotNet + "' " + ReasonForAutomValidation);
snippet.InsertSnippet("VALIDATECOLUMNS", columnTemplate);
}
}
if (FoundDeletableRowValidation)
{
snippet.InsertSnippet("DELETABLEROWVALIDATION", deletableRowSnippet);
}
else
{
snippet.SetCodelet("DELETABLEROWVALIDATION", String.Empty);
}
Template.InsertSnippet(WhereToInsert, snippet);
}
private static void FixRow(string[] OldRow,
ref string[] NewRow,
StringCollection AOldColumnNames,
StringCollection ANewColumnNames,
TTable ANewTable,
List <TTableField>AMappingOfFields,
List <string>ADefaultValues)
{
int fieldCounter = 0;
foreach (TTableField newField in ANewTable.grpTableField)
{
TTableField oldField = AMappingOfFields[fieldCounter];
if (oldField != null)
{
string value = GetValue(AOldColumnNames, OldRow, oldField.strName);
value = FixValue(value, newField);
SetValue(ANewColumnNames, ref NewRow, newField.strName, value);
}
else
{
SetValue(ANewColumnNames, ref NewRow, newField.strName, ADefaultValues[fieldCounter]);
}
fieldCounter++;
}
}
private static Boolean WriteTable(eDatabaseType ATargetDatabase, StreamWriter ASw, TTable ATable)
{
// Show the table info
ASw.WriteLine("-- {0}", ATable.strDescription.Replace("\r", " ").Replace("\n", " "));
ASw.WriteLine("-- GROUP: {0}", ATable.strGroup);
ASw.WriteLine("CREATE TABLE {0} (", ATable.strName);
// Dump fields
DumpFields(ATargetDatabase, ASw, ATable);
DumpConstraints(ASw, ATable, false, true);
ASw.WriteLine();
ASw.Write(")");
if (ATargetDatabase == eDatabaseType.MySQL)
{
// use InnoDB, otherwise there are no constraints
ASw.WriteLine(" ENGINE=InnoDB");
}
ASw.WriteLine(";");
ASw.WriteLine();
return true;
}
/// <summary>
/// fix data that would cause problems for PostgreSQL constraints
/// </summary>
public static int MigrateData(TParseProgressCSV AParser, StreamWriter AWriter, StreamWriter AWriterTest, TTable AOldTable, TTable ANewTable)
{
StringCollection OldColumnNames = null;
StringCollection NewColumnNames;
int RowCounter = 0;
if (AOldTable != null)
{
OldColumnNames = GetColumnNames(AOldTable);
}
NewColumnNames = GetColumnNames(ANewTable);
string[] NewRow = CreateRow(NewColumnNames);
// This existing and populated table's data is completely changed. We do not want to import any of it's contents.
if ((ANewTable.strName == "pt_language_level")
|| (ANewTable.strName == "p_partner_attribute_category"))
{
RowCounter += FixData(ANewTable.strName, NewColumnNames, ref NewRow, AWriter, AWriterTest);
return RowCounter;
}
List <TTableField>MappingOfFields = new List <TTableField>();
List <string>DefaultValues = new List <string>();
PrepareTable(AOldTable, ANewTable, ref MappingOfFields, ref DefaultValues);
while (true)
{
string[] OldRow = AParser.ReadNextRow();
if (OldRow == null)
{
break;
}
FixRow(OldRow, ref NewRow, OldColumnNames, NewColumnNames, ANewTable, MappingOfFields, DefaultValues);
if (FixData(ANewTable.strName, NewColumnNames, ref NewRow))
{
RowCounter++;
AWriter.WriteLine(CSVFile.StrMergeSpecial(NewRow));
if (AWriterTest != null)
{
AWriterTest.WriteLine("BEGIN; " + "COPY " + ANewTable.strName + " FROM stdin;");
AWriterTest.WriteLine(CSVFile.StrMergeSpecial(NewRow));
AWriterTest.WriteLine("\\.");
AWriterTest.WriteLine("ROLLBACK;");
}
}
}
RowCounter += FixData(ANewTable.strName, NewColumnNames, ref NewRow, AWriter, AWriterTest);
return RowCounter;
}
/// <summary>
/// write the part of the sql creation script for a specific column
/// </summary>
/// <param name="ATargetDatabase"></param>
/// <param name="ATable"></param>
/// <param name="field"></param>
/// <param name="first"></param>
/// <param name="AWithComments"></param>
/// <returns></returns>
public static string WriteField(eDatabaseType ATargetDatabase, TTable ATable, TTableField field, bool first, bool AWithComments)
{
string result = "";
if (!first)
{
result += ",";
}
if (AWithComments)
{
result += Environment.NewLine;
if (field.strDescription.Length != 0)
{
result += String.Format(" -- {0}", field.strDescription.Replace("\r", " ").Replace("\n", " ")) + Environment.NewLine;
}
}
#if IMPORTFROMLEGACYDB
// this is useful when converting from legacy database, with columns that contain too long strings
if ((field.strType == "varchar") && (field.iLength >= 20) && (!field.strName.Contains("_code_")))
{
field.strType = "text";
}
#endif
if (field.strType == "bit")
{
result += String.Format(" {0} boolean", field.strName);
}
else if ((field.strType == "number") && (field.iLength == 10) && (field.iDecimals == -1))
{
result += String.Format(" {0} bigint", field.strName);
}
else if (field.strType == "number")
{
result += String.Format(" {0} numeric", field.strName);
}
else if (field.bAutoIncrement)
{
if (ATargetDatabase == eDatabaseType.PostgreSQL)
{
result += String.Format(" {0} SERIAL", field.strName);
}
else if (ATargetDatabase == eDatabaseType.Sqlite)
{
result += String.Format(" {0} INTEGER PRIMARY KEY AUTOINCREMENT ", field.strName);
}
else if (ATargetDatabase == eDatabaseType.MySQL)
{
result += String.Format(" {0} INTEGER AUTO_INCREMENT UNIQUE ", field.strName);
}
}
else
{
result += String.Format(" {0} {1}", field.strName, field.strType);
}
// According to the type we will add parameters
if ((field.strType == "number") && (field.iLength == 10) && (field.iDecimals == -1))
{
// no parameter for bigints
}
else if ((field.strType == "varchar") || (field.strType == "number"))
{
if (field.iLength >= 0)
{
result += String.Format("({0}", field.iLength.ToString());
}
if (field.strType == "number")
{
result += String.Format(", {0}", (field.iDecimals > 0 ? field.iDecimals.ToString() : "0"));
}
result += String.Format(")");
}
if (field.strDefault.Length > 0)
{
if (field.strDefault == "NULL")
{
result += String.Format(" DEFAULT {0}", field.strDefault);
}
else if ((field.strType == "varchar") || (field.strType == "text"))
{
result += String.Format(" DEFAULT '{0}'", field.strDefault);
}
else if (field.strType == "bit")
{
if (ATargetDatabase == eDatabaseType.MySQL)
{
result += String.Format(" DEFAULT {0}", field.strDefault);
}
else
{
result += String.Format(" DEFAULT '{0}'", field.strDefault);
}
}
else if (field.strDefault == "SYSDATE")
{
// MySql cannot have a function for the default value
// see http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
if (ATargetDatabase != eDatabaseType.MySQL)
{
result += String.Format(" DEFAULT CURRENT_DATE");
}
}
else
{
result += String.Format(" DEFAULT {0}", field.strDefault);
}
}
#if (!IMPORTFROMLEGACYDB)
if (field.bNotNull)
{
result += String.Format(" NOT NULL");
}
if ((field.strCheck != null) && (field.strCheck.Length != 0))
{
result += String.Format(" CHECK {0}", field.strCheck);
}
#endif
return result;
}
private void LoadTable(TTable newTable)
{
bool IgnoreFile = false;
TLogging.Log(newTable.strName);
string oldTableName = DataDefinitionDiff.GetOldTableName(newTable.strName);
TTable oldTable = storeOld.GetTable(oldTableName);
// if this is a new table in OpenPetra, do not dump anything. the table will be empty in OpenPetra
// (except p_partner_attribute_category and p_partner_attribute_type which are populated here)
if ((oldTable == null) && (newTable.strName != "p_partner_attribute_category") && (newTable.strName != "p_partner_attribute_type"))
{
return;
}
// the file has already been stored in fulldump, tablename.d.gz
string dumpFile = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + oldTableName;
if (!File.Exists(dumpFile + ".d.gz"))
{
TLogging.Log("cannot find file " + dumpFile + ".d.gz");
return;
}
FileInfo info = new FileInfo(dumpFile + ".d.gz");
// ignore empty files (with one exception)
if ((oldTableName == "p_partner_gift_destination")
|| (oldTableName == "p_partner_attribute_category")
|| (oldTableName == "p_partner_attribute_type"))
{
IgnoreFile = true;
}
if ((info.Length == 0) && !IgnoreFile)
{
TLogging.Log("ignoring " + dumpFile + ".d.gz");
return;
}
if (TAppSettingsManager.HasValue("table") || !File.Exists(dumpFile + ".sql.gz") || ((new FileInfo(dumpFile + ".sql.gz")).Length == 0))
{
if (((long)info.Length > MAX_SIZE_D_GZ_SEPARATE_PROCESS) && !TAppSettingsManager.HasValue("table"))
{
if (TAppSettingsManager.GetValue("IgnoreBigTables", "false", false) == "false")
{
ProcessAndWritePostgresqlFile(dumpFile, newTable);
}
}
else
{
ProcessAndWritePostgresqlFile(dumpFile, newTable);
}
}
}
/// <summary>
/// Populate the empty table PPartnerGiftDestination using PmStaffData
/// </summary>
public static int PopulatePPartnerGiftDestination(StringCollection AColumnNames,
ref string[] ANewRow,
StreamWriter AWriter,
StreamWriter AWriterTest)
{
List <string[]> ActiveCommitments = new List <string[]>();
List <string[]> Persons = new List <string[]>();
int RowCounter = 0;
// default for all new records
SetValue(AColumnNames, ref ANewRow, "p_active_l", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_default_gift_destination_l", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_partner_class_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_commitment_site_key_n", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_commitment_key_n", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_date_created_d", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_created_by_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_date_modified_d", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_modified_by_c", "\\N");
SetValue(AColumnNames, ref ANewRow, "s_modification_id_t", "\\N");
// load the file pm_staff_data.d.gz
TTable StaffDataTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("pm_staff_data");
TParseProgressCSV StaffDataParser = new TParseProgressCSV(
TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "pm_staff_data.d.gz",
StaffDataTable.grpTableField.Count);
StringCollection StaffDataColumnNames = GetColumnNames(StaffDataTable);
// find which records are currently active
while (true)
{
string[] StaffDataRow = StaffDataParser.ReadNextRow();
if (StaffDataRow == null)
{
break;
}
string strStartOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_start_of_commitment_d");
string strEndOfCommitment = GetValue(StaffDataColumnNames, StaffDataRow, "pm_end_of_commitment_d");
try
{
// if commitment is currently active
if ((DateTime.ParseExact(strStartOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) <= DateTime.Today) &&
((strEndOfCommitment == "\\N") ||
(DateTime.ParseExact(strEndOfCommitment, "dd/mm/yyyy", CultureInfo.InvariantCulture) >= DateTime.Today)) &&
(strStartOfCommitment != strEndOfCommitment))
{
ActiveCommitments.Add(StaffDataRow);
}
}
catch
{
TLogging.Log("WARNING: Invalid date in commitment: " +
strStartOfCommitment + " or " + strEndOfCommitment);
}
}
// load the file p_person.d.gz
TTable PersonTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_person");
TParseProgressCSV PersonParser = new TParseProgressCSV(
TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_person.d.gz",
PersonTable.grpTableField.Count);
StringCollection PersonColumnNames = GetColumnNames(PersonTable);
SortedList <string, List <PersonKeyAndRow> > FamilyKeysWithPersons =
new SortedList <string, List <PersonKeyAndRow> >();
// add all Persons to a list
while (true)
{
string[] PersonRow = PersonParser.ReadNextRow();
if (PersonRow == null)
{
break;
}
string familyKey = GetValue(PersonColumnNames, PersonRow, "p_family_key_n");
if (!FamilyKeysWithPersons.ContainsKey(familyKey))
{
FamilyKeysWithPersons.Add(familyKey, new List <PersonKeyAndRow>());
}
FamilyKeysWithPersons[familyKey].Add(
new PersonKeyAndRow(
GetValue(PersonColumnNames, PersonRow, "p_partner_key_n"), PersonRow));
Persons.Add(PersonRow);
}
// load the file p_family.d.gz
TTable FamilyTable = TDumpProgressToPostgresql.GetStoreOld().GetTable("p_family");
TParseProgressCSV FamilyParser = new TParseProgressCSV(
TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar + "p_family.d.gz",
FamilyTable.grpTableField.Count);
StringCollection FamilyColumnNames = GetColumnNames(FamilyTable);
// read through each family
while (true)
{
string[] FamilyRow = FamilyParser.ReadNextRow();
if (FamilyRow == null)
{
break;
}
string familykey = GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n");
// find Person partners belonging to the family
bool CommitmentFound = false;
int MinimumFamilyId = int.MaxValue;
// if family contains Persons
if (FamilyKeysWithPersons.ContainsKey(familykey))
{
// read through each of the Family's Persons
foreach (PersonKeyAndRow PersonRecord in FamilyKeysWithPersons[familykey])
{
// find if the Person has a currently active commitment
string[] Commitment = ActiveCommitments.Find(e => GetValue(StaffDataColumnNames, e, "p_partner_key_n") ==
PersonRecord.PersonKey);
// if currently active commitment exists create a new Gift Destination record
if (Commitment != null)
{
int CurrentFamilyId = Convert.ToInt32(GetValue(PersonColumnNames, PersonRecord.PersonRow, "p_old_omss_family_id_i"));
if (CurrentFamilyId < MinimumFamilyId)
{
SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString());
SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"));
SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(StaffDataColumnNames, Commitment, "pm_target_field_n"));
SetValue(AColumnNames, ref ANewRow, "p_comment_c", "\\N");
TTableField tf = new TTableField();
tf.strName = "pm_start_of_commitment_d";
tf.strType = "DATE";
SetValue(AColumnNames, ref ANewRow, "p_date_effective_d",
TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_start_of_commitment_d"), tf));
tf.strName = "pm_end_of_commitment_d";
SetValue(AColumnNames, ref ANewRow, "p_date_expires_d",
TFixData.FixValue(GetValue(StaffDataColumnNames, Commitment, "pm_end_of_commitment_d"), tf));
CommitmentFound = true;
MinimumFamilyId = CurrentFamilyId;
// there can only be one active gift destination per family
break;
}
}
}
}
// if no active commitment is found then search for a "p_om_field_key_n" and use that to create a gift destination
if (!CommitmentFound)
{
string OMFieldKey = GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n");
if ((OMFieldKey != "\\N") && (OMFieldKey != "0"))
{
SetValue(AColumnNames, ref ANewRow, "p_key_i", RowCounter.ToString());
SetValue(AColumnNames, ref ANewRow, "p_partner_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_partner_key_n"));
SetValue(AColumnNames, ref ANewRow, "p_field_key_n", GetValue(FamilyColumnNames, FamilyRow, "p_om_field_key_n"));
DateTime LastYear = DateTime.Today.AddYears(-1);
SetValue(AColumnNames, ref ANewRow, "p_date_effective_d",
string.Format("{0}-{1}-{2}", LastYear.Year, LastYear.Month, LastYear.Day));
SetValue(AColumnNames, ref ANewRow, "p_date_expires_d", "\\N");
SetValue(AColumnNames, ref ANewRow, "p_comment_c", Catalog.GetString("Copied from Petra's OM Field Key."));
CommitmentFound = true;
}
}
// write gift destination to file
if (CommitmentFound)
{
AWriter.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
if (AWriterTest != null)
{
AWriterTest.WriteLine("BEGIN; " + "COPY p_partner_gift_destination FROM stdin;");
AWriterTest.WriteLine(StringHelper.StrMerge(ANewRow, '\t').Replace("\\\\N", "\\N").ToString());
AWriterTest.WriteLine("\\.");
AWriterTest.WriteLine("ROLLBACK;");
}
RowCounter++;
}
}
return(RowCounter);
}
private static void DumpFields(eDatabaseType ATargetDatabase, StreamWriter ASw, TTable ATable)
{
// Run over all fields
bool first = true;
foreach (TTableField field in ATable.grpTableField)
{
ASw.Write(WriteField(ATargetDatabase, ATable, field, first, true));
first = false;
}
}
/// <summary>
/// process the data from the Progress dump file, so that Postgresql can read the result
/// </summary>
public void ProcessAndWritePostgresqlFile(string dumpFile, TTable newTable)
{
string oldTableName = DataDefinitionDiff.GetOldTableName(newTable.strName);
TTable oldTable = storeOld.GetTable(oldTableName);
// if this is a new table in OpenPetra, do not dump anything. the table will be empty in OpenPetra
// (except p_postcode_region_range, a_budget_revision and p_partner_gift_destination which are populated here)
if ((oldTable == null) && (newTable.strName != "p_postcode_region_range") && (newTable.strName != "a_budget_revision")
&& (newTable.strName != "p_partner_gift_destination") && (newTable.strName != "p_partner_attribute_category")
&& (newTable.strName != "p_partner_attribute_type"))
{
return;
}
string NewFileName = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar +
newTable.strName + ".sql.gz";
// if (File.Exists(NewFileName))
// {
// // for debugging: ignore files that have been written already
// return;
// }
StreamWriter MyWriterCount = null;
StreamWriter MyWriterTest = null;
FileStream outStreamTest;
Stream gzoStreamTest;
TParseProgressCSV Parser = null;
try
{
if (oldTable != null)
{
Parser = new TParseProgressCSV(
dumpFile + ".d.gz",
oldTable.grpTableField.Count);
}
FileStream outStream = File.Create(NewFileName);
Stream gzoStream = new GZipOutputStream(outStream);
StreamWriter MyWriter = new StreamWriter(gzoStream, Encoding.UTF8);
if (TAppSettingsManager.GetValue("create_test_files", "true", false) == "true")
{
outStreamTest = File.Create(
TAppSettingsManager.GetValue("fulldumpPath", "fulldump") + Path.DirectorySeparatorChar +
newTable.strName + "_test.sql.gz");
gzoStreamTest = new GZipOutputStream(outStreamTest);
MyWriterTest = new StreamWriter(gzoStreamTest, Encoding.UTF8);
}
string rowCountDir = TAppSettingsManager.GetValue("fulldumpPath", "fulldump") +
Path.DirectorySeparatorChar + "_row_count.txt";
if (File.Exists(rowCountDir))
{
MyWriterCount = File.AppendText(rowCountDir);
}
else
{
FileStream outStreamCount = File.Create(rowCountDir);
MyWriterCount = new StreamWriter(outStreamCount);
}
// The p_partner_contact table needs to write p_contact_log records first, so handle the COPY manually
if (newTable.strName != "p_partner_contact")
{
MyWriter.WriteLine("COPY " + newTable.strName + " FROM stdin;");
}
int ProcessedRows = TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
ProcessedRows += MoveTables(newTable.strName, dumpFile, MyWriter, MyWriterTest, newTable);
MyWriter.WriteLine("\\.");
MyWriter.WriteLine();
if (MyWriterTest != null)
{
MyWriterTest.WriteLine();
}
MyWriterCount.WriteLine(newTable.strName);
MyWriterCount.WriteLine(ProcessedRows);
MyWriter.Close();
TLogging.Log(" after processing file, rows: " + ProcessedRows.ToString());
}
catch (Exception e)
{
TLogging.Log("Memory usage: " + (GC.GetTotalMemory(false) / 1024 / 1024).ToString() + " MB");
TLogging.Log("WARNING Problems processing file " + dumpFile + ": " + e.ToString());
if (File.Exists(dumpFile + ".sql.gz"))
{
File.Delete(dumpFile + ".sql.gz");
}
}
finally
{
if (MyWriterCount != null)
{
MyWriterCount.Close();
}
if (MyWriterTest != null)
{
MyWriterTest.Close();
}
}
}
private static void DumpConstraints(StreamWriter ASw, TTable ATable, Boolean onlyForeign, Boolean AAdd)
{
foreach (TConstraint constr in ATable.grpConstraint)
{
WriteConstraint(ASw, ATable, constr, onlyForeign, AAdd);
}
}
private int MoveTables(string ANewTableName, string dumpFile, StreamWriter MyWriter, StreamWriter MyWriterTest, TTable newTable)
{
int ProcessedRows = 0;
if (ANewTableName == "a_batch")
{
TLogging.Log("a_this_year_old_batch");
TTable oldTable = storeOld.GetTable("a_this_year_old_batch");
TParseProgressCSV Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_this_year_old_batch") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
TLogging.Log("a_previous_year_batch");
oldTable = storeOld.GetTable("a_previous_year_batch");
Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_previous_year_batch") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
}
else if (ANewTableName == "a_journal")
{
TLogging.Log("a_this_year_old_journal");
TTable oldTable = storeOld.GetTable("a_this_year_old_journal");
TParseProgressCSV Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_this_year_old_journal") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
TLogging.Log("a_previous_year_journal");
oldTable = storeOld.GetTable("a_previous_year_journal");
Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_previous_year_journal") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
}
else if (ANewTableName == "a_transaction")
{
TLogging.Log("a_this_year_old_transaction");
TTable oldTable = storeOld.GetTable("a_this_year_old_transaction");
TParseProgressCSV Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_this_year_old_transaction") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
TLogging.Log("a_previous_year_transaction");
oldTable = storeOld.GetTable("a_previous_year_transaction");
Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_previous_year_transaction") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
}
else if (ANewTableName == "a_trans_anal_attrib")
{
TLogging.Log("a_thisyearold_trans_anal_attrib");
TTable oldTable = storeOld.GetTable("a_thisyearold_trans_anal_attrib");
TParseProgressCSV Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_thisyearold_trans_anal_attrib") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
TLogging.Log("a_prev_year_trans_anal_attrib");
oldTable = storeOld.GetTable("a_prev_year_trans_anal_attrib");
Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_prev_year_trans_anal_attrib") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
}
else if (ANewTableName == "a_corporate_exchange_rate")
{
TLogging.Log("a_prev_year_corp_ex_rate");
TTable oldTable = storeOld.GetTable("a_prev_year_corp_ex_rate");
TParseProgressCSV Parser = new TParseProgressCSV(
dumpFile.Replace(ANewTableName, "a_prev_year_corp_ex_rate") + ".d.gz",
oldTable.grpTableField.Count);
ProcessedRows += TFixData.MigrateData(Parser, MyWriter, MyWriterTest, oldTable, newTable);
}
return ProcessedRows;
}
private static void DumpIndexes(StreamWriter ASw, TTable ATable, Boolean AAdd)
{
for (System.Int32 implicit_ = 0; implicit_ <= 1; implicit_ += 1)
{
countGeneratedIndex = 0;
foreach (TIndex index in ATable.grpIndex)
{
// first the automatically generated Indexes
if (index.bImplicit == (implicit_ != 1))
{
string indexName = index.strName;
if ((indexName.IndexOf("_fkcr_key") != 0)
|| (indexName.IndexOf("_fkmd_key") != 0)
|| (indexName.IndexOf("inx_s_group_gift") != 0))
{
indexName += countGeneratedIndex++;
}
if (AAdd)
{
ASw.Write("CREATE ");
if (index.bUnique)
{
ASw.Write("UNIQUE ");
}
ASw.WriteLine("INDEX {0} ", indexName);
ASw.WriteLine(" ON {0}", ATable.strName);
string fields = "";
foreach (TIndexField indfield in index.grpIndexField)
{
if (fields.Length > 0)
{
fields += ",";
}
fields += indfield.strName;
}
ASw.WriteLine(" ({0});", fields);
}
else
{
ASw.WriteLine("DROP INDEX IF EXISTS {0} CASCADE;", indexName);
}
}
}
}
}
/// <summary>
/// Parse the definition of one database table
/// </summary>
/// <param name="cur2">the current node</param>
/// <returns></returns>
protected TTable ParseTable(XmlNode cur2)
{
XmlNode cur;
TTableField tableField;
TConstraint myConstraint;
TIndex myIndex;
TTable table;
int groupId;
cur = cur2;
table = new TTable();
table.strName = GetAttribute(cur, "name");
table.strDotNetName = TTable.NiceTableName(table.strName);
table.strDumpName = GetAttribute(cur, "dumpname");
table.strDescription = GetAttribute(cur, "descr");
table.strArea = GetAttribute(cur, "area");
table.ExistsStrLabel = HasAttribute(cur, "label");
table.strLabel = GetAttribute(cur, "label");
if (!table.ExistsStrLabel)
{
table.strLabel = table.strName;
}
if ((table.strDumpName.Length == 0) && FDoValidation)
{
throw new Exception("Table " + table.strName + " has no dumpname!");
}
// created / modified fields
table.bWithoutCRMDFields = GetAttribute(cur, "withoutcrmd") == "yes";
table.bCatchUpdateException = GetAttribute(cur, "catchupdateexception") == "yes";
table.strGroup = GetAttribute(cur, "group");
if (table.strGroup.Length == 0)
{
Console.WriteLine("Missing group name for table " + table.strName);
Environment.Exit(1);
}
cur = cur.FirstChild;
while (cur != null)
{
groupId = -1;
tableField = (TTableField)Parse(cur, ref groupId, "tablefield");
if (tableField != null)
{
tableField.strTableName = table.strName;
tableField.iOrder = table.grpTableField.Count;
table.grpTableField.Add(tableField);
}
myConstraint = (TConstraint)Parse(cur, ref groupId, "foreignkey");
if (myConstraint == null)
{
myConstraint = (TConstraint)Parse(cur, ref groupId, "primarykey");
}
if (myConstraint == null)
{
myConstraint = (TConstraint)Parse(cur, ref groupId, "uniquekey");
}
if (myConstraint != null)
{
table.grpConstraint.Add(myConstraint);
}
myIndex = (TIndex)Parse(cur, ref groupId, "index");
if (myIndex != null)
{
table.AddIndex(myIndex);
}
cur = GetNextEntity(cur);
}
if (table.HasPrimaryKey())
{
foreach (string primKeyField in table.GetPrimaryKey().strThisFields)
{
if (!table.GetField(primKeyField).bNotNull)
{
if (!SupportPetra2xLegacyStandard)
{
throw new Exception(String.Format(
"Primary Key field '{0}' of DB Table '{1}' is not marked as NOT NULL in petra.xml, but this is mandatory!",
table.GetField(primKeyField).strName, table.strName));
}
else
{
TLogging.Log(String.Format(
"Warning: Primary Key field '{0}' of DB Table '{1}' is not marked as NOT NULL in petra.xml, but this is mandatory!",
table.GetField(primKeyField).strName, table.strName));
}
}
table.GetField(primKeyField).bPartOfPrimKey = true;
}
}
if (table.HasUniqueKey())
{
foreach (string uniqueKeyField in table.GetFirstUniqueKey().strThisFields)
{
table.GetField(uniqueKeyField).bPartOfFirstUniqueKey = true;
}
}
return table;
}
private static void PrepareCodeletsUniqueKey(
TTable ACurrentTable,
out string formalParametersUniqueKey,
out string actualParametersUniqueKey,
out int numberUniqueKeyColumns)
{
formalParametersUniqueKey = "";
actualParametersUniqueKey = "";
numberUniqueKeyColumns = 0;
string dummy = "";
if (!ACurrentTable.HasUniqueKey())
{
return;
}
PrepareCodeletsKey(ACurrentTable,
ACurrentTable.GetFirstUniqueKey(),
out formalParametersUniqueKey,
out actualParametersUniqueKey,
out dummy,
out numberUniqueKeyColumns);
}
/// <summary>
/// load the dataset tables
/// </summary>
public static SortedList <string, TTable>LoadDatasetTables(string AICTPath, string ADataSetTypeWithNamespace, TCodeStorage ACodeStorage,
string APluginPath)
{
if (FDatasetTables == null)
{
FDatasetTables = new SortedList <string, SortedList <string, TTable>>();
}
FCodeStorage = ACodeStorage;
if (!ADataSetTypeWithNamespace.StartsWith("Ict.Petra.Shared") && !ADataSetTypeWithNamespace.StartsWith("Ict.Petra.Plugins"))
{
throw new Exception("the DatasetType must contain the full namespace, starting with Ict.Petra.Shared or Ict.Petra.Plugins");
}
if (FDatasetTables.ContainsKey(ADataSetTypeWithNamespace))
{
FCurrentDataset = FDatasetTables[ADataSetTypeWithNamespace];
return FCurrentDataset;
}
string[] datasetTypeSplit = ADataSetTypeWithNamespace.Split(new char[] { '.' });
string module = datasetTypeSplit[3];
string datasetName = datasetTypeSplit[datasetTypeSplit.Length - 1];
// find the correct xml file for the dataset.
// look in Ict/Petra/Shared/lib/MODULE/data
string dataPath = AICTPath + "/Petra/Shared/lib/" + module + "/data/";
if (ADataSetTypeWithNamespace.StartsWith("Ict.Petra.Plugins"))
{
int start = "Ict.Petra.Plugins.".Length;
int end = ADataSetTypeWithNamespace.IndexOf(".", start);
string PluginName = ADataSetTypeWithNamespace.Substring(start, end - start);
dataPath = AICTPath + "/Petra/Plugins/" + PluginName + "/data/";
}
DirectoryInfo directory = new DirectoryInfo(dataPath);
FileInfo[] xmlFiles = directory.GetFiles("*.xml");
XmlNode datasetNode = null;
foreach (FileInfo fileinfo in xmlFiles)
{
if (datasetNode == null)
{
TXMLParser parser = new TXMLParser(dataPath + "/" + fileinfo.Name, false);
datasetNode = parser.GetDocument().SelectSingleNode(String.Format("//DataSet[@name='{0}']", datasetName));
}
}
if ((datasetNode == null) && File.Exists(APluginPath))
{
// also check the plugin directory of the yaml file, for plugins can have a file TypedDataSets.xml
TXMLParser parser = new TXMLParser(APluginPath, false);
datasetNode = parser.GetDocument().SelectSingleNode(String.Format("//DataSet[@name='{0}']", datasetName));
}
if (datasetNode == null)
{
throw new Exception("cannot find the xml file for dataset " + ADataSetTypeWithNamespace);
}
SortedList <string, TTable>result = new SortedList <string, TTable>();
XmlNodeList tables = datasetNode.SelectNodes("Table|CustomTable");
foreach (XmlNode tableNode in tables)
{
TTable table = new TTable();
string tablename;
if ((tableNode.Name == "Table") && TXMLParser.HasAttribute(tableNode, "sqltable"))
{
tablename = TTable.NiceTableName(tableNode.Attributes["sqltable"].Value);
table.Assign(FPetraXMLStore.GetTable(tablename));
table.strVariableNameInDataset = TXMLParser.HasAttribute(tableNode, "name") ? tableNode.Attributes["name"].Value : tablename;
if ((tableNode.SelectNodes("CustomField").Count > 0) || (tableNode.SelectNodes("Field").Count > 0))
{
table.strDotNetName = datasetName + tablename;
}
}
else if ((tableNode.Name == "Table") && TXMLParser.HasAttribute(tableNode, "customtable"))
{
table = new TTable();
tablename = tableNode.Attributes["customtable"].Value;
table.strName = tablename;
table.strDotNetName = tablename;
table.strVariableNameInDataset = TXMLParser.HasAttribute(tableNode, "name") ? tableNode.Attributes["name"].Value : tablename;
}
else
{
table = new TTable();
tablename = tableNode.Attributes["name"].Value;
table.strName = tablename;
table.strDotNetName = datasetName + tablename;
table.strVariableNameInDataset = tablename;
}
// add the custom fields if there are any
XmlNodeList customFields = tableNode.SelectNodes("CustomField");
foreach (XmlNode customField in customFields)
{
TTableField newField = new TTableField();
newField.strName = customField.Attributes["name"].Value;
newField.strNameDotNet = newField.strName;
newField.strType = customField.Attributes["type"].Value;
newField.strTypeDotNet = customField.Attributes["type"].Value;
newField.strTableName = tablename;
newField.strDescription = "";
newField.bNotNull =
TXMLParser.HasAttribute(customField, "notnull") && TXMLParser.GetAttribute(customField, "notnull").ToLower() == "true";
table.grpTableField.Add(newField);
}
// add other fields from other tables that are defined in petra.xml
XmlNodeList otherFields = tableNode.SelectNodes("Field");
foreach (XmlNode otherField in otherFields)
{
TTable otherTable = FPetraXMLStore.GetTable(otherField.Attributes["sqltable"].Value);
TTableField newField = new TTableField(otherTable.GetField(otherField.Attributes["sqlfield"].Value));
if (TXMLParser.HasAttribute(otherField, "name"))
{
newField.strNameDotNet = otherField.Attributes["name"].Value;
}
newField.strTableName = tablename;
table.grpTableField.Add(newField);
}
result.Add(table.strVariableNameInDataset, table);
}
FDatasetTables.Add(ADataSetTypeWithNamespace, result);
FCurrentDataset = result;
return result;
}
/// <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>
/// create code for a table that is part of a dataset
/// </summary>
/// <param name="ATables"></param>
/// <param name="ASqltable"></param>
/// <param name="tabletype"></param>
/// <param name="variablename"></param>
/// <param name="snippetDataset"></param>
/// <param name="ASnippetSubmitChanges"></param>
private static void AddTableToDataset(
List <TDataSetTable>ATables,
TTable ASqltable,
string tabletype,
string variablename,
ProcessTemplate snippetDataset,
ProcessTemplate ASnippetSubmitChanges)
{
ProcessTemplate tempSnippet;
if (ASqltable != null)
{
string SequenceColumn = "";
string SequenceName = "";
foreach (TTableField tablefield in ASqltable.grpTableField)
{
// is there a field filled by a sequence?
// yes: get the next value of that sequence and assign to row
if (tablefield.strSequence.Length > 0)
{
SequenceName = tablefield.strSequence;
SequenceColumn = tablefield.strName;
// assume only one sequence per table
break;
}
}
tempSnippet = snippetDataset.GetSnippet("SUBMITCHANGES");
tempSnippet.SetCodelet("ORIGTABLENAME", TTable.NiceTableName(ASqltable.strName));
tempSnippet.SetCodelet("TABLETYPENAME", tabletype);
tempSnippet.SetCodelet("TABLEVARIABLENAME", variablename);
tempSnippet.SetCodelet("SQLOPERATION", "eDelete");
tempSnippet.SetCodelet("SEQUENCENAMEANDFIELD", "");
ASnippetSubmitChanges.InsertSnippetPrepend("SUBMITCHANGESDELETE", tempSnippet);
tempSnippet = snippetDataset.GetSnippet("SUBMITCHANGES");
tempSnippet.SetCodelet("ORIGTABLENAME", TTable.NiceTableName(ASqltable.strName));
tempSnippet.SetCodelet("TABLETYPENAME", tabletype);
tempSnippet.SetCodelet("TABLEVARIABLENAME", variablename);
tempSnippet.SetCodelet("SQLOPERATION", "eInsert | TTypedDataAccess.eSubmitChangesOperations.eUpdate");
tempSnippet.SetCodelet("SEQUENCENAMEANDFIELD", "");
if (SequenceName.Length > 0)
{
tempSnippet.SetCodelet("SEQUENCENAMEANDFIELD", ", \"" + SequenceName + "\", \"" + SequenceColumn + "\"");
// look for other tables in the dataset that have a foreign key on the sequenced column
// eg. p_location_key_i is set when storing p_location.
// now we should update p_partner_location, which still has negative values in p_location_key_i
foreach (TDataSetTable table in ATables)
{
foreach (TConstraint constraint in table.grpConstraint)
{
if ((constraint.strType == "foreignkey")
&& (constraint.strOtherTable == ASqltable.strName)
&& constraint.strOtherFields.Contains(SequenceColumn))
{
tempSnippet.SetCodelet("TABLEROWTYPE", TTable.NiceTableName(ASqltable.strName) + "Row");
tempSnippet.SetCodelet("SEQUENCEDCOLUMNNAME", TTable.NiceFieldName(SequenceColumn));
ProcessTemplate updateSnippet = snippetDataset.GetSnippet("UPDATESEQUENCEINOTHERTABLES");
bool canbeNull =
!table.GetField(constraint.strThisFields[constraint.strOtherFields.IndexOf(SequenceColumn)]).bNotNull;
updateSnippet.SetCodelet("TESTFORNULL", canbeNull ? "!otherRow.Is{#REFCOLUMNNAME}Null() && " : "");
updateSnippet.SetCodelet("REFERENCINGTABLEROWTYPE", TTable.NiceTableName(table.tablename) + "Row");
updateSnippet.SetCodelet("REFERENCINGTABLENAME", table.tablealias);
updateSnippet.SetCodelet("REFCOLUMNNAME",
TTable.NiceFieldName(constraint.strThisFields[constraint.strOtherFields.IndexOf(SequenceColumn)]));
updateSnippet.SetCodelet("SEQUENCEDCOLUMNNAME", TTable.NiceFieldName(SequenceColumn));
tempSnippet.InsertSnippet("UPDATESEQUENCEINOTHERTABLES", updateSnippet);
}
}
}
}
ASnippetSubmitChanges.InsertSnippet("SUBMITCHANGESINSERT", tempSnippet);
ASnippetSubmitChanges.AddToCodelet("SUBMITCHANGESUPDATE", "");
}
}
/// <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>
/// write the definition for the code of a typed row
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
public static void InsertRowDefinition(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
ProcessTemplate snippet = Template.GetSnippet("TYPEDROW");
if (origTable != null)
{
snippet.SetCodelet("BASECLASSROW", TTable.NiceTableName(currentTable.strName) + "Row");
snippet.SetCodelet("OVERRIDE", "override ");
}
else
{
snippet.SetCodelet("BASECLASSROW", "System.Data.DataRow");
snippet.SetCodelet("OVERRIDE", "virtual ");
}
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
foreach (TTableField col in currentTable.grpTableField)
{
ProcessTemplate tempTemplate = null;
string columnOverwrite = "";
if ((origTable != null) && (origTable.GetField(col.strName, false) != null))
{
columnOverwrite = "new ";
}
if (columnOverwrite.Length == 0)
{
tempTemplate = Template.GetSnippet("ROWCOLUMNPROPERTY");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
tempTemplate.SetCodelet("COLUMNHELP", col.strDescription.Replace(Environment.NewLine, " "));
tempTemplate.SetCodelet("COLUMNLABEL", col.strLabel);
tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString());
tempTemplate.SetCodelet("COLUMNDOTNETTYPE", col.GetDotNetType());
if (!col.bNotNull)
{
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("TESTFORNULL", "!value.HasValue");
}
else if (col.GetDotNetType().Contains("String"))
{
tempTemplate.SetCodelet("TESTFORNULL", "(value == null) || (value.Length == 0)");
}
}
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return null;");
}
else if (col.GetDotNetType().Contains("DateTime"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return DateTime.MinValue;");
}
else if (col.GetDotNetType().ToLower().Contains("string"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return String.Empty;");
}
else
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "throw new System.Data.StrongTypingException(\"Error: DB null\", null);");
}
tempTemplate.SetCodeletComment("COLUMN_DESCRIPTION", col.strDescription);
snippet.InsertSnippet("ROWCOLUMNPROPERTIES", tempTemplate);
tempTemplate = Template.GetSnippet("FUNCTIONSFORNULLVALUES");
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
snippet.InsertSnippet("FUNCTIONSFORNULLVALUES", tempTemplate);
}
if ((col.strDefault.Length > 0) && (col.strDefault != "NULL"))
{
string defaultValue = col.strDefault;
if (defaultValue == "SYSDATE")
{
defaultValue = "DateTime.Today";
}
else if ((col.strType == "bit") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("bool")))
{
defaultValue = (defaultValue == "1" || defaultValue.ToLower() == "true").ToString().ToLower();
}
else if ((col.strType == "varchar") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("string")))
{
defaultValue = '"' + defaultValue + '"';
}
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this[this.myTable.Column" + TTable.NiceFieldName(
col) + ".Ordinal] = " + defaultValue + ";" + Environment.NewLine);
}
else
{
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this.SetNull(this.myTable.Column" + TTable.NiceFieldName(
col) + ");" + Environment.NewLine);
}
}
Template.InsertSnippet(WhereToInsert, snippet);
}
/// <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>
/// 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>
public static void InsertTableDefinition(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
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 (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;
foreach (TTableField col in currentTable.grpTableField)
{
col.strTableName = currentTable.strName;
ProcessTemplate tempTemplate = null;
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 (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++;
}
Template.InsertSnippet(WhereToInsert, snippet);
}
private static void PrepareTable(TTable AOldTable, TTable ANewTable, ref List <TTableField>AMappingOfFields, ref List <string>ADefaultValues)
{
foreach (TTableField newField in ANewTable.grpTableField)
{
string oldname = "";
TTableField oldField = AOldTable.GetField(newField.strName);
if ((oldField == null) && (DataDefinitionDiff.GetNewFieldName(ANewTable.strName, ref oldname, ref newField.strName)))
{
oldField = AOldTable.GetField(oldname);
}
AMappingOfFields.Add(oldField);
// prepare the default values once
// this is a new field. insert default value
string defaultValue = "\\N";
if ((newField.strInitialValue != null) && (newField.strInitialValue.Length > 0))
{
if (newField.strInitialValue.ToUpper() == "TODAY")
{
// it does not make sense to set s_date_created_d to today during conversion.
// so no change to defaultValue.
}
else if (newField.strType.ToUpper() == "VARCHAR")
{
defaultValue = '"' + newField.strInitialValue + "\"";
}
else if (newField.strType.ToUpper() == "BIT")
{
defaultValue = newField.strInitialValue;
if (newField.strFormat.Contains(newField.strInitialValue))
{
defaultValue = newField.strFormat.StartsWith(newField.strInitialValue) ? "0" : "1";
}
}
else
{
defaultValue = newField.strInitialValue;
}
}
ADefaultValues.Add(defaultValue);
}
}
/// <summary>
/// code for cascading functions
/// </summary>
/// <param name="AStore"></param>
/// <param name="ACurrentTable"></param>
/// <param name="ATemplate"></param>
/// <param name="ASnippet"></param>
/// <returns>false if no cascading available</returns>
private static bool InsertMainProcedures(TDataDefinitionStore AStore,
TTable ACurrentTable,
ProcessTemplate ATemplate,
ProcessTemplate ASnippet)
{
string csvListPrimaryKeyFields;
string formalParametersPrimaryKey;
string actualParametersPrimaryKey;
Tuple <string, string, string>[] formalParametersPrimaryKeySeparate;
string actualParametersPrimaryKeyFromPKArray = String.Empty;
ASnippet.AddToCodelet("TABLENAME", TTable.NiceTableName(ACurrentTable.strName));
ASnippet.AddToCodelet("THISTABLELABEL", ACurrentTable.strLabel);
PrepareCodeletsPrimaryKey(ACurrentTable,
out csvListPrimaryKeyFields,
out formalParametersPrimaryKey,
out actualParametersPrimaryKey,
out formalParametersPrimaryKeySeparate);
for (int Counter = 0; Counter < formalParametersPrimaryKeySeparate.Length; Counter++)
{
actualParametersPrimaryKeyFromPKArray +=
"(" + formalParametersPrimaryKeySeparate[Counter].Item1 + ")" +
"APrimaryKeyValues[" + Counter.ToString() + "], ";
}
// Strip off trailing ", "
actualParametersPrimaryKeyFromPKArray = actualParametersPrimaryKeyFromPKArray.Substring(0,
actualParametersPrimaryKeyFromPKArray.Length - 2);
ASnippet.AddToCodelet("CSVLISTPRIMARYKEYFIELDS", csvListPrimaryKeyFields);
ASnippet.AddToCodelet("FORMALPARAMETERSPRIMARYKEY", formalParametersPrimaryKey);
ASnippet.AddToCodelet("ACTUALPARAMETERSPRIMARYKEY", actualParametersPrimaryKey);
ASnippet.AddToCodelet("ACTUALPARAMETERSPRIMARYKEYFROMPKARRAY", actualParametersPrimaryKeyFromPKArray);
for (int Counter = 0; Counter < ACurrentTable.GetPrimaryKey().strThisFields.Count; Counter++)
{
ProcessTemplate PKInfoDictBuilding = ASnippet.GetSnippet("PRIMARYKEYINFODICTBUILDING");
PKInfoDictBuilding.SetCodelet("PKCOLUMNLABEL", formalParametersPrimaryKeySeparate[Counter].Item3);
PKInfoDictBuilding.SetCodelet("PKCOLUMNCONTENT", formalParametersPrimaryKeySeparate[Counter].Item2);
ASnippet.InsertSnippet("PRIMARYKEYINFODICTBUILDING", PKInfoDictBuilding);
}
ASnippet.AddToCodelet("PRIMARYKEYCOLUMNCOUNT", ACurrentTable.GetPrimaryKey().strThisFields.Count.ToString());
foreach (TConstraint constraint in ACurrentTable.FReferenced)
{
if (AStore.GetTable(constraint.strThisTable).HasPrimaryKey())
{
string csvListOtherPrimaryKeyFields;
string notUsed;
Tuple <string, string, string>[] formalParametersPrimaryKeySeparate2;
TTable OtherTable = AStore.GetTable(constraint.strThisTable);
PrepareCodeletsPrimaryKey(OtherTable,
out csvListOtherPrimaryKeyFields,
out notUsed,
out notUsed,
out formalParametersPrimaryKeySeparate2);
// check if other foreign key exists that references the same table, e.g.
// PBankAccess.LoadViaPPartnerPartnerKey
// PBankAccess.LoadViaPPartnerContactPartnerKey
string DifferentField = CodeGenerationAccess.FindOtherConstraintSameOtherTable(
OtherTable.grpConstraint,
constraint);
string LoadViaProcedureName = TTable.NiceTableName(ACurrentTable.strName);
string MyOtherTableName = "My" + TTable.NiceTableName(constraint.strThisTable);
if (DifferentField.Length != 0)
{
LoadViaProcedureName += TTable.NiceFieldName(DifferentField);
MyOtherTableName += TTable.NiceFieldName(DifferentField);
}
// for the moment, don't implement it for too big tables, e.g. s_user)
if ((ACurrentTable.HasPrimaryKey() || (ACurrentTable.FReferenced.Count <= CASCADING_DELETE_MAX_REFERENCES))
&& ((constraint.strThisTable != "a_ledger")
&& (!LoadViaProcedureName.StartsWith("SUser"))))
{
ProcessTemplate snippetDelete = ASnippet.GetSnippet("DELETEBYPRIMARYKEYCASCADING");
snippetDelete.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(constraint.strThisTable));
snippetDelete.SetCodelet("MYOTHERTABLENAME", MyOtherTableName);
snippetDelete.SetCodelet("VIAPROCEDURENAME", "Via" + LoadViaProcedureName);
snippetDelete.SetCodelet("CSVLISTOTHERPRIMARYKEYFIELDS", csvListOtherPrimaryKeyFields);
snippetDelete.SetCodelet("OTHERTABLEALSOCASCADING", "true");
ASnippet.InsertSnippet("DELETEBYPRIMARYKEYCASCADING", snippetDelete);
snippetDelete = ASnippet.GetSnippet("DELETEBYTEMPLATECASCADING");
snippetDelete.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(constraint.strThisTable));
snippetDelete.SetCodelet("MYOTHERTABLENAME", MyOtherTableName);
snippetDelete.SetCodelet("VIAPROCEDURENAME", "Via" + LoadViaProcedureName);
snippetDelete.SetCodelet("CSVLISTOTHERPRIMARYKEYFIELDS", csvListOtherPrimaryKeyFields);
snippetDelete.SetCodelet("OTHERTABLEALSOCASCADING", "true");
ASnippet.InsertSnippet("DELETEBYTEMPLATECASCADING", snippetDelete);
}
if ((constraint.strThisTable != "a_ledger")
&& (!LoadViaProcedureName.StartsWith("SUser")))
{
ProcessTemplate snippetCount = ASnippet.GetSnippet("COUNTBYPRIMARYKEYCASCADING");
snippetCount.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(constraint.strThisTable));
snippetCount.SetCodelet("MYOTHERTABLENAME", MyOtherTableName);
snippetCount.SetCodelet("VIAPROCEDURENAME", "Via" + LoadViaProcedureName);
snippetCount.SetCodelet("CSVLISTOTHERPRIMARYKEYFIELDS", csvListOtherPrimaryKeyFields);
snippetCount.SetCodelet("OTHERTABLEALSOCASCADING", "true");
for (int Counter = 0; Counter < OtherTable.GetPrimaryKey().strThisFields.Count; Counter++)
{
ProcessTemplate PKInfoDictBuilding2 = ASnippet.GetSnippet("PRIMARYKEYINFODICTBUILDING");
PKInfoDictBuilding2.SetCodelet("PKCOLUMNLABEL", formalParametersPrimaryKeySeparate2[Counter].Item3);
PKInfoDictBuilding2.SetCodelet("PKCOLUMNCONTENT", "\"\"");
snippetCount.InsertSnippet("PRIMARYKEYINFODICTBUILDING2", PKInfoDictBuilding2);
}
snippetCount.SetCodelet("PRIMARYKEYCOLUMNCOUNT2", OtherTable.GetPrimaryKey().strThisFields.Count.ToString());
ASnippet.InsertSnippet("COUNTBYPRIMARYKEYCASCADING", snippetCount);
snippetCount = ASnippet.GetSnippet("COUNTBYTEMPLATECASCADING");
snippetCount.SetCodelet("OTHERTABLENAME", TTable.NiceTableName(constraint.strThisTable));
snippetCount.SetCodelet("OTHERTABLELABEL", OtherTable.strLabel);
snippetCount.SetCodelet("MYOTHERTABLENAME", MyOtherTableName);
snippetCount.SetCodelet("VIAPROCEDURENAME", "Via" + LoadViaProcedureName);
snippetCount.SetCodelet("CSVLISTOTHERPRIMARYKEYFIELDS", csvListOtherPrimaryKeyFields);
snippetCount.SetCodelet("OTHERTABLEALSOCASCADING", "true");
for (int Counter = 0; Counter < OtherTable.GetPrimaryKey().strThisFields.Count; Counter++)
{
ProcessTemplate PKInfoDictBuilding2 = ASnippet.GetSnippet("PRIMARYKEYINFODICTBUILDING");
PKInfoDictBuilding2.SetCodelet("PKCOLUMNLABEL", formalParametersPrimaryKeySeparate2[Counter].Item3);
PKInfoDictBuilding2.SetCodelet("PKCOLUMNCONTENT", "\"\"");
snippetCount.InsertSnippet("PRIMARYKEYINFODICTBUILDING2", PKInfoDictBuilding2);
}
snippetCount.SetCodelet("PRIMARYKEYCOLUMNCOUNT2", OtherTable.GetPrimaryKey().strThisFields.Count.ToString());
ASnippet.InsertSnippet("COUNTBYTEMPLATECASCADING", snippetCount);
}
}
}
return true;
}
/// <summary>
/// get the names of the columns for the given table
/// </summary>
/// <param name="ATable"></param>
/// <returns></returns>
protected static StringCollection GetColumnNames(TTable ATable)
{
StringCollection ColumnNames = new StringCollection();
foreach (TTableField field in ATable.grpTableField)
{
ColumnNames.Add(field.strName);
}
return ColumnNames;
}
/// <summary>
/// write the definition for the code of a typed row
/// </summary>
/// <param name="Template"></param>
/// <param name="currentTable"></param>
/// <param name="origTable"></param>
/// <param name="WhereToInsert"></param>
public static void InsertRowDefinition(ProcessTemplate Template, TTable currentTable, TTable origTable, string WhereToInsert)
{
ProcessTemplate snippet = Template.GetSnippet("TYPEDROW");
if (origTable != null)
{
snippet.SetCodelet("BASECLASSROW", TTable.NiceTableName(currentTable.strName) + "Row");
snippet.SetCodelet("OVERRIDE", "override ");
}
else
{
snippet.SetCodelet("BASECLASSROW", "System.Data.DataRow");
snippet.SetCodelet("OVERRIDE", "virtual ");
}
snippet.SetCodeletComment("TABLE_DESCRIPTION", currentTable.strDescription);
snippet.SetCodelet("TABLENAME", currentTable.strDotNetName);
foreach (TTableField col in currentTable.grpTableField)
{
ProcessTemplate tempTemplate = null;
string columnOverwrite = "";
if ((origTable != null) && (origTable.GetField(col.strName, false) != null))
{
columnOverwrite = "new ";
}
if (columnOverwrite.Length == 0)
{
tempTemplate = Template.GetSnippet("ROWCOLUMNPROPERTY");
tempTemplate.SetCodelet("COLUMNDBNAME", col.strName);
tempTemplate.SetCodelet("COLUMNNAME", col.strNameDotNet);
tempTemplate.SetCodelet("COLUMNHELP", col.strDescription.Replace(Environment.NewLine, " "));
tempTemplate.SetCodelet("COLUMNLABEL", col.strLabel);
tempTemplate.SetCodelet("COLUMNLENGTH", col.iLength.ToString());
tempTemplate.SetCodelet("COLUMNDOTNETTYPE", col.GetDotNetType());
if (!col.bNotNull)
{
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("TESTFORNULL", "!value.HasValue");
}
else if (col.GetDotNetType().Contains("String"))
{
tempTemplate.SetCodelet("TESTFORNULL", "(value == null) || (value.Length == 0)");
}
}
if (col.GetDotNetType().Contains("DateTime?"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return null;");
}
else if (col.GetDotNetType().Contains("DateTime"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return DateTime.MinValue;");
}
else if (col.GetDotNetType().ToLower().Contains("string"))
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "return String.Empty;");
}
else
{
tempTemplate.SetCodelet("ACTIONGETNULLVALUE", "throw new System.Data.StrongTypingException(\"Error: DB null\", null);");
}
tempTemplate.SetCodeletComment("COLUMN_DESCRIPTION", col.strDescription);
snippet.InsertSnippet("ROWCOLUMNPROPERTIES", tempTemplate);
tempTemplate = Template.GetSnippet("FUNCTIONSFORNULLVALUES");
tempTemplate.SetCodelet("COLUMNNAME", TTable.NiceFieldName(col));
snippet.InsertSnippet("FUNCTIONSFORNULLVALUES", tempTemplate);
}
if ((col.strDefault.Length > 0) && (col.strDefault != "NULL"))
{
string defaultValue = col.strDefault;
if (defaultValue == "SYSDATE")
{
defaultValue = "DateTime.Today";
}
else if ((col.strType == "bit") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("bool")))
{
defaultValue = (defaultValue == "1" || defaultValue.ToLower() == "true").ToString().ToLower();
}
else if ((col.strType == "varchar") || ((col.strTypeDotNet != null) && col.strTypeDotNet.ToLower().Contains("string")))
{
defaultValue = '"' + defaultValue + '"';
}
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this[this.myTable.Column" + TTable.NiceFieldName(
col) + ".Ordinal] = " + defaultValue + ";" + Environment.NewLine);
}
else
{
snippet.AddToCodelet("ROWSETNULLORDEFAULT", "this.SetNull(this.myTable.Column" + TTable.NiceFieldName(
col) + ");" + Environment.NewLine);
}
}
Template.InsertSnippet(WhereToInsert, snippet);
}
