private string GetFunctionalName(TableDef tableDef)
{
return(NameMapping.GetFunctionalName(tableDef));
}
public TableDefMap ExtractTableData(List <TableMapping> tableMappingList, bool cleanOracle)
{
SqlConnection conn = this.SqlConnection;
// Shared.Info("begin test");
// Test(conn);
// Shared.Info("Completed test");
var tableDefMap = new TableDefMap();
Shared.Info("getting tables schema");
/*
* Information Schema, GetSchema differences MSSQL from MySQL:
* Views data fails for MSSQL
* MSSQL does not have EXTRA for columns
* MySQL uses ulong for character field length, MSSQL uses int16
* MySQL uses STATISTICS for indexes
* MSSQL does not indicate unique or not unique for indexes
* MSSQL does not use the term "Foreign Key Collections", but "ForeignKeys"
* MSSQL foreignkeys collection does not contain most fields
*/
/* INFORMATION_SCHEMA is kinda crap truth be told. For SQL Server,
* it is just as easy to write queries for exactly what we want,
* with the added benefit that it actually works. :)
*
* We'll try to stay consistent with the field names output to match
* what information schema does, but we'll make our own queries
*/
string queryText = "SELECT A.name AS TableName, B.name AS SchemaName "
+ "FROM sys.tables AS A JOIN sys.schemas AS B "
+ "ON A.schema_id = B.schema_id;";
foreach (var row in MicrosoftSQL.DataAccess.GetRows(conn, queryText))
{
string tableName = (string)row["TableName"];
string schemaName = (string)row["SchemaName"];
string cleanName = NameMapping.MakeCleanTableName(tableMappingList, tableName, cleanOracle);
TableDef tableDef = new TableDef {
TableName = tableName,
CleanName = cleanName,
SchemaName = schemaName,
TableType = "TABLE",
ColumnDefMap = new Dictionary <string, ColumnDef>(),
IndexDefMap = new Dictionary <string, IndexDef>(),
ProcedureDefMap = new Dictionary <string, ProcedureDef>(),
FieldDefList = new List <FieldDef>(),
ForeignKeyList = new List <string>(),
ArgumentName = Char.ToLowerInvariant(tableName[0]) + tableName.Substring(1)
};
tableDefMap[schemaName + "." + tableName] = tableDef;
Shared.Info("Adding table " + tableName);
}
queryText = "SELECT A.name AS TableName, B.name AS SchemaName "
+ "FROM sys.views AS A JOIN sys.schemas AS B "
+ "ON A.schema_id = B.schema_id;";
foreach (var row in MicrosoftSQL.DataAccess.GetRows(conn, queryText))
{
string tableName = (string)row["TableName"];
string schemaName = (string)row["SchemaName"];
string cleanName = NameMapping.MakeCleanTableName(tableMappingList, tableName, cleanOracle);
TableDef tableDef = new TableDef {
TableName = tableName,
CleanName = cleanName,
SchemaName = schemaName,
TableType = "VIEW",
ColumnDefMap = new Dictionary <string, ColumnDef>(),
IndexDefMap = new Dictionary <string, IndexDef>(),
ProcedureDefMap = new Dictionary <string, ProcedureDef>(),
FieldDefList = new List <FieldDef>(),
ForeignKeyList = new List <string>()
};
tableDefMap[schemaName + "." + tableName] = tableDef;
Shared.Info("Adding view " + tableName);
}
queryText = @"SELECT TABLE_NAME = ct.name,
COLUMN_NAME = c.name,
SCHEMA_NAME = sch.name,
CHARACTER_MAXIMUM_LENGTH = c.max_length,
DATA_TYPE = typ.name,
IS_NULLABLE = c.is_nullable,
IS_IDENTITY = is_identity
FROM sys.columns c
INNER JOIN sys.objects ct
ON c.object_id = ct.object_id
INNER JOIN sys.types typ
ON c.system_type_id = typ.system_type_id
INNER JOIN sys.schemas sch
ON ct.schema_id = sch.schema_id
WHERE ct.type IN ('U', 'V')
ORDER BY TABLE_NAME, COLUMN_NAME;";
/* for MySQL: queryText = "SELECT COLUMN_NAME, TABLE_NAME, CHARACTER_MAXIMUM_LENGTH, DATA_TYPE, EXTRA, IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC;"; */
foreach (var row in MicrosoftSQL.DataAccess.GetRows(conn, queryText))
{
string columnName = (string)row["COLUMN_NAME"];
string tableName = (string)row["TABLE_NAME"];
string schemaName = (string)row["SCHEMA_NAME"];
Shared.Info("Adding column " + columnName + " from table " + tableName);
short?dataLength;
if (row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value)
{
dataLength = null;
}
else
{
dataLength = (short)row["CHARACTER_MAXIMUM_LENGTH"];
}
string modelName = NameMapping.MakeCleanTableName(tableMappingList, tableName, cleanOracle);
string cleanName = NameMapping.MakeCleanColumnName(tableMappingList, tableName, modelName, columnName, cleanOracle);
bool forceToBit = false;
if (cleanOracle == true)
{
forceToBit = IsForceToBit(tableMappingList, tableName, columnName);
}
/* MySQL provides IsIdentity = ((string)row["EXTRA"]).Contains("auto_increment"), */
ColumnDef columnDef = new ColumnDef {
ColumnName = columnName,
CleanName = cleanName,
ColumnType = (string)row["DATA_TYPE"],
DataLength = (ulong?)dataLength,
ForceToBit = forceToBit,
IsIdentity = (bool)row["IS_IDENTITY"],
IsNullable = (bool)row["IS_NULLABLE"]
};
Shared.Info(tableName);
tableDefMap[schemaName + "." + tableName].ColumnDefMap[columnName] = columnDef;
Shared.Info("Column " + columnName + " added.");
}
/* will get primary key and unique key, which is used for Read and ReadFor functions */
/* will get indexes for use in ListFor functions */
/* had QUOTENAME function for SCHEMA_NAME and TABLE_NAME which I removed,
* also removed SCHEMA_NAME = OBJECT_SCHEMA_NAME(i.[object_id]), */
queryText = @"SELECT
TABLE_NAME = ct.name,
SCHEMA_NAME = sch.name,
INDEX_NAME = i.name,
PRIMARY_KEY = i.is_primary_key,
[UNIQUE] = i.is_unique,
COLUMN_NAME = c.Name
FROM
sys.indexes AS i
INNER JOIN
sys.index_columns AS ic
ON i.[object_id] = ic.[object_id]
AND i.index_id = ic.index_id
INNER JOIN
sys.columns c
ON ic.column_id = c.column_id
AND ic.[object_id] = c.[object_id]
INNER JOIN sys.objects ct
ON i.object_id = ct.object_id
INNER JOIN sys.schemas sch
ON ct.schema_id = sch.schema_id
WHERE ct.type IN ('U', 'V')
ORDER BY TABLE_NAME, INDEX_NAME, ic.index_column_id;";
foreach (var row in MicrosoftSQL.DataAccess.GetRows(conn, queryText))
{
string tableName = (string)row["TABLE_NAME"];
string schemaName = (string)row["SCHEMA_NAME"];
string indexName = (string)row["INDEX_NAME"];
string columnName = (string)row["COLUMN_NAME"];
Shared.Info("Adding index column " + columnName + " from index " + indexName + " on table " + tableName);
// int ordinalPosition = (int)row["ORDINAL_POSITION"];
/* SORT_ORDER */
Shared.Info("looking for table " + tableName);
TableDef table = tableDefMap[schemaName + "." + tableName];
IndexDef indexDef = null;
if (table.IndexDefMap.TryGetValue(indexName, out indexDef) == false)
{
indexDef = new IndexDef {
IndexName = indexName,
// IsPrimary = (indexName == "PRIMARY"),
IsPrimary = (bool)row["PRIMARY_KEY"],
// IsUnique = ((bool)row["NON_UNIQUE"]!=true),
IsUnique = (bool)row["UNIQUE"],
ColumnDefList = new List <ColumnDef>()
};
table.IndexDefMap[indexName] = indexDef;
}
ColumnDef columnDef = table.ColumnDefMap[columnName];
indexDef.ColumnDefList.Add(columnDef);
}
queryText = @"SELECT TABLE_NAME = ct.name,
SCHEMA_NAME = sch.name,
COLUMN_NAME = c.name,
REFERENCED_TABLE_NAME = rct.name,
REFERENCED_COLUMN_NAME = rc.name,
REFERENCED_SCHEMA_NAME = refsch.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns c
ON fkc.parent_object_id = c.object_id
AND fkc.parent_column_id = c.column_id
INNER JOIN sys.objects ct
ON c.object_id = ct.object_id
INNER JOIN sys.columns rc
ON fkc.referenced_object_id = rc.object_id
AND fkc.referenced_column_id = rc.column_id
INNER JOIN sys.objects rct
ON rc.object_id = rct.object_id
INNER JOIN sys.schemas sch
ON ct.schema_id = sch.schema_id
INNER JOIN sys.schemas refsch
ON rct.schema_id = refsch.schema_id
WHERE ct.type IN ('U', 'V')
ORDER BY TABLE_NAME, COLUMN_NAME;";
foreach (var row in MicrosoftSQL.DataAccess.GetRows(conn, queryText))
{
TableDef tableDef = tableDefMap[row["SCHEMA_NAME"] + "." + row["TABLE_NAME"].ToString()];
ColumnDef columnDef = tableDef.ColumnDefMap[row["COLUMN_NAME"].ToString()];
columnDef.ReferencedTableDef = tableDefMap[row["REFERENCED_SCHEMA_NAME"].ToString() + "." + row["REFERENCED_TABLE_NAME"].ToString()];
columnDef.ReferencedColumnDef = columnDef.ReferencedTableDef.ColumnDefMap[row["REFERENCED_COLUMN_NAME"].ToString()];
Shared.Info("Adding foreign key for " + tableDef.TableName + "." + columnDef.ColumnName + " to " + columnDef.ReferencedTableDef.TableName + "." + columnDef.ReferencedColumnDef.ColumnName);
}
return(tableDefMap);
}
private string GetListForProcedureText(string moduleName, TableDef tableDef, List <ColumnDef> indexColumnDefList)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
List <ColumnDef> columnDefList = tableDef.ColumnDefMap.Values.ToList();
string storedProcName = moduleName + "_" + GetFunctionalName(tableDef) + "_ListFor";
bool first = true;
foreach (ColumnDef columnDef in indexColumnDefList)
{
if (first == true)
{
first = false;
}
else
{
storedProcName += "_";
}
storedProcName += columnDef.CleanName;
// storedProcName += StripKeySuffix(columnDef.ColumnName);
}
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
bool firstItem = true;
foreach (ColumnDef columnDef in indexColumnDefList)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
}
scriptText.Append("\t@" + columnDef.CleanName + " ");
OutputDataType(scriptText, columnDef);
}
scriptText.AppendLine();
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.Append("\tSELECT ");
/* bool */ firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t\t");
}
scriptText.Append(CleanName(columnDef.ColumnName));
if (columnDef.ColumnName != columnDef.CleanName)
{
scriptText.Append(" AS " + columnDef.CleanName);
}
}
scriptText.AppendLine("");
scriptText.Append("\t\tFROM ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.Append(CleanName(tableName));
/* bool */ firstItem = true;
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
scriptText.AppendLine("");
if (firstItem)
{
scriptText.Append("\t\tWHERE ");
firstItem = false;
}
else
{
scriptText.Append("\t\tAND ");
}
scriptText.Append(CleanName(indexColumnDef.ColumnName) + " = @" + indexColumnDef.CleanName);
}
scriptText.AppendLine(";");
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
private string GetUpdateProcedureText(string moduleName, TableDef tableDef, List <ColumnDef> primaryKeyColumnDefList)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
List <ColumnDef> columnDefList = tableDef.ColumnDefMap.Values.ToList();
var storedProcName = moduleName + "_" + GetFunctionalName(tableDef) + "_Update";
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
OutputPrimaryKeyArguments(scriptText, primaryKeyColumnDefList);
foreach (ColumnDef columnDef in columnDefList)
{
if (primaryKeyColumnDefList.Contains(columnDef) == false)
{
scriptText.AppendLine(",");
scriptText.Append("\t@" + columnDef.CleanName + " ");
OutputDataType(scriptText, columnDef);
}
}
scriptText.AppendLine();
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.Append("\tUPDATE ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.AppendLine(CleanName(tableName));
scriptText.Append("\t\tSET ");
bool firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (primaryKeyColumnDefList.Contains(columnDef) == false)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t");
}
scriptText.Append(CleanName(columnDef.ColumnName) + " = @" + columnDef.CleanName);
}
}
scriptText.AppendLine();
scriptText.Append("\t\tWHERE");
OutputPrimaryKeyWhereClause(scriptText, primaryKeyColumnDefList);
scriptText.AppendLine(";");
// SET @rowcount = ROW_COUNT();
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
private string GetReadProcedureText(string moduleName, TableDef tableDef, bool isPrimary, List <ColumnDef> indexColumnDefList)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
string functionalName = GetFunctionalName(tableDef);
string storedProcName = moduleName + "_" + functionalName + "_Read";
if (isPrimary == false)
{
storedProcName += "For";
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
storedProcName += StripKeySuffix(indexColumnDef.ColumnName);
}
}
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
bool firstItem = true;
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
}
scriptText.Append("\t@" + indexColumnDef.CleanName + " ");
OutputDataType(scriptText, indexColumnDef);
}
scriptText.AppendLine();
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.Append("\tSELECT ");
/* bool */ firstItem = true;
foreach (ColumnDef columnDef in tableDef.ColumnDefMap.Values)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t");
}
if (columnDef.ForceToBit == true)
{
if (columnDef.IsNullable)
{
scriptText.Append($"CASE WHEN {CleanName(columnDef.ColumnName)} IS NULL THEN CAST(NULL AS BIT) ELSE ");
}
scriptText.Append($"CASE {CleanName(columnDef.ColumnName)} WHEN 0 THEN CAST(0 AS BIT) ELSE CAST(1 AS BIT) END");
if (columnDef.IsNullable)
{
scriptText.Append($" END");
}
}
else
{
scriptText.Append(CleanName(columnDef.ColumnName));
}
if (columnDef.ColumnName != columnDef.CleanName)
{
scriptText.Append(" AS " + columnDef.CleanName);
}
}
scriptText.AppendLine("");
scriptText.Append("\tFROM ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.Append(CleanName(tableName));
/* bool */ firstItem = true;
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
scriptText.AppendLine("");
if (firstItem)
{
scriptText.Append("\tWHERE ");
firstItem = false;
}
else
{
scriptText.Append("\t\tAND ");
}
scriptText.Append("\t" + CleanName(indexColumnDef.ColumnName) + " = @" + indexColumnDef.CleanName);
}
scriptText.AppendLine(";");
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
private string GetListAllProcedureText(string moduleName, TableDef tableDef, int listAllLimit)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
List <ColumnDef> columnDefList = tableDef.ColumnDefMap.Values.ToList();
var storedProcName = moduleName + "_" + GetFunctionalName(tableDef) + "_ListAll";
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
scriptText.AppendLine("\t@allRows bit");
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.AppendLine("\tif (@allRows = 1)");
scriptText.Append("\t\tSELECT ");
bool firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t");
}
scriptText.Append(CleanName(columnDef.ColumnName));
if (columnDef.ColumnName != columnDef.CleanName)
{
scriptText.Append(" AS " + columnDef.CleanName);
}
}
scriptText.AppendLine("");
scriptText.Append("\t\t\tFROM ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.AppendLine(CleanName(tableName) + ";");
scriptText.AppendLine("\tELSE");
scriptText.Append("\t\tSELECT ");
scriptText.AppendLine("TOP " + listAllLimit + " ");
/* bool */ firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t");
}
scriptText.Append(CleanName(columnDef.ColumnName));
if (columnDef.ColumnName != columnDef.CleanName)
{
scriptText.Append(" AS " + columnDef.CleanName);
}
}
scriptText.AppendLine("");
scriptText.Append("\t\t\tFROM ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.AppendLine(CleanName(tableName) + ";");
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
private string GetDeleteForProcedureText(string moduleName, TableDef tableDef, List <ColumnDef> indexColumnDefList)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
string storedProcName = moduleName + "_" + GetFunctionalName(tableDef) + "_DeleteFor";
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
storedProcName += StripKeySuffix(indexColumnDef.ColumnName);
}
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
bool firstItem = true;
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
string indexColumnName = indexColumnDef.ColumnName;
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
}
scriptText.Append("\t@" + indexColumnName + " ");
OutputDataType(scriptText, indexColumnDef);
}
// OUT @rowcount int
scriptText.AppendLine();
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.Append("\tDELETE FROM ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.Append(CleanName(tableName));
/* bool */ firstItem = true;
foreach (ColumnDef indexColumnDef in indexColumnDefList)
{
scriptText.AppendLine("");
if (firstItem)
{
scriptText.Append("\t\tWHERE ");
firstItem = false;
}
else
{
scriptText.Append("\t\tAND ");
}
scriptText.Append(CleanName(indexColumnDef.ColumnName) + " = @" + indexColumnDef.CleanName);
}
scriptText.AppendLine(";");
//scriptText.AppendLine("SET @rowcount = ROW_COUNT();");
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
private string GetCreateProcedureText(string moduleName, TableDef tableDef)
{
StringBuilder scriptText = new StringBuilder();
string tableName = tableDef.TableName;
List <ColumnDef> columnDefList = tableDef.ColumnDefMap.Values.ToList();
string storedProcName = moduleName + "_" + GetFunctionalName(tableDef) + "_Create";
OutputDIEProcedureText(scriptText, storedProcName);
OutputCreateProcedureText(scriptText, storedProcName);
ColumnDef identityColumnDef = null;
bool firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (columnDef.IsIdentity == true)
{
identityColumnDef = columnDef;
}
else
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
}
scriptText.Append("\t@" + columnDef.CleanName + " ");
OutputDataType(scriptText, columnDef);
}
}
if (identityColumnDef != null)
{
if (firstItem == false)
{
scriptText.Append(",");
}
scriptText.AppendLine("");
scriptText.Append("\t@" + identityColumnDef.CleanName + " ");
OutputDataType(scriptText, identityColumnDef);
scriptText.AppendLine(" OUT");
}
else
{
scriptText.AppendLine();
}
scriptText.AppendLine("AS");
OutputTidalSignature(scriptText);
scriptText.AppendLine("\tSET NOCOUNT ON");
scriptText.Append("\tINSERT ");
if (tableDef.SchemaName != "dbo")
{
scriptText.Append(tableDef.SchemaName + ".");
}
scriptText.Append(CleanName(tableName) + " (");
/* bool */ firstItem = true;
foreach (ColumnDef columnDef in tableDef.ColumnDefMap.Values)
{
if (columnDef.IsIdentity == false)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t");
}
scriptText.Append(CleanName(columnDef.ColumnName));
}
}
scriptText.AppendLine(")");
scriptText.Append("\t\tVALUES (");
/* bool */ firstItem = true;
foreach (ColumnDef columnDef in columnDefList)
{
if (columnDef.IsIdentity == false)
{
if (firstItem)
{
firstItem = false;
}
else
{
scriptText.AppendLine(",");
scriptText.Append("\t\t\t\t");
}
scriptText.Append("@" + columnDef.CleanName);
}
}
scriptText.AppendLine(");");
if (identityColumnDef != null)
{
scriptText.AppendLine("\tSET @" + identityColumnDef.CleanName + " = SCOPE_IDENTITY();");
}
scriptText.AppendLine("GO");
scriptText.AppendLine();
return(scriptText.ToString());
}
public List <ProcedureDef> MakeProcedureDefList(string databaseName,
string moduleName,
Dictionary <string, TableDef> tableDefMap)
{
/* var tableMap = {};
* var functionMap = {};
* var tableArray = [];
*
*
*/
MySqlConnection conn = this.mySqlConnection;
var globalProcedureDefMap = new Dictionary <string, ProcedureDef>();
using (MySqlCommand command = new MySqlCommand()
{
CommandText = "SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='" + databaseName
+ "' AND ROUTINE_NAME LIKE '" + moduleName + "_%_%' AND ROUTINE_TYPE='PROCEDURE';",
CommandType = CommandType.Text,
Connection = conn
}) {
using (var reader = command.ExecuteReader()) {
while (reader.Read())
{
IDataRecord procRow = reader;
string procedureName = (string)procRow["ROUTINE_NAME"];
if ((string)procRow["SQL_DATA_ACCESS"] == "CONTAINS SQL")
{
/* TODO: I could envision a get server time function that doesn't read or write data but should be part of a DataAccess class. */
Shared.Info("Skipping procedure " + procedureName + " marked CONTAINS SQL, as this implies it does not read or write data.");
}
else
{
int lastUnderscoreIndex = procedureName.IndexOf("_", moduleName.Length + 2);
string tableName = procedureName.Substring(moduleName.Length + 1, lastUnderscoreIndex - moduleName.Length - 1);
string catalogName = (string)procRow["ROUTINE_CATALOG"];
if (catalogName != "def")
{
throw new ApplicationException("Unexpected catalog name found: " + catalogName);
}
if (tableDefMap.ContainsKey(tableName) == false)
{
throw new ApplicationException("Table " + tableName + " referenced in stored procedure " + procedureName + " was not found in table definitions.");
}
TableDef tableDef = tableDefMap[tableName];
ProcedureDef procedureDef = new ProcedureDef {
ProcedureName = procedureName,
TableDef = tableDef,
ParameterDefMap = new Dictionary <string, ParameterDef>(),
FieldDefMap = new Dictionary <string, FieldDef>()
/* ReadOnly, OutputsRows */
};
/* TODO: there may be some issues here if we require list and read functions to a data access level of reads sql data, i think
* it may change the transaction scope if you mix with modifies sql data (not sure) */
switch ((string)procRow["SQL_DATA_ACCESS"])
{
case "READS SQL DATA":
procedureDef.ReadOnly = true;
procedureDef.OutputsRows = true;
break;
case "MODIFIES SQL DATA":
procedureDef.ReadOnly = false;
procedureDef.OutputsRows = false;
break;
default:
throw new ApplicationException("Unrecognized SQL Data Access setting for procedure " + procedureName + ": " + procRow["SQL_DATA_ACCESS"]);
}
tableDef.ProcedureDefMap[procedureName] = procedureDef;
globalProcedureDefMap[procedureName] = procedureDef;
}
}
}
}
PopulateParameters(databaseName, moduleName, globalProcedureDefMap);
/*
* call each procedure to see the result set
*
*/
foreach (ProcedureDef procedureDef in globalProcedureDefMap.Values)
{
if (procedureDef.OutputsRows)
{
MySqlTransaction trans = this.mySqlConnection.BeginTransaction();
using (MySqlCommand command = new MySqlCommand()
{
CommandText = procedureDef.ProcedureName,
CommandType = CommandType.StoredProcedure,
Connection = this.mySqlConnection
}) {
foreach (ParameterDef parameterDef in procedureDef.ParameterDefMap.Values)
{
ParameterDirection direction;
if (parameterDef.IsOutParameter)
{
direction = ParameterDirection.Output;
}
else
{
direction = ParameterDirection.Input;
}
var parameter = new MySqlParameter {
ParameterName = parameterDef.ParameterName,
Direction = direction,
Size = parameterDef.ParameterSize,
MySqlDbType = GetMySqlDbType(parameterDef.ParameterDataTypeCode)
};
/* for (Parameter p : proc.parameters) {
* p.sqlType.setTestValue(cs, p.procParamName);
* }
*/
parameter.Value = DBNull.Value;
command.Parameters.Add(parameter);
}
/* alternatively for MSSQL at least:
*
* SELECT COLUMN_NAME
* FROM
* INFORMATION_SCHEMA.COLUMNS
* WHERE
* TABLE_NAME = 'vwGetData'
* ORDER BY
* ORDINAL_POSITION ASC;
*
*/
// cs.setMaxRows(0);
DataTable tableSchema;
Dictionary <string, string> fieldTypeLookup = new Dictionary <string, string>();
using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly)) {
reader.Read();
tableSchema = reader.GetSchemaTable();
for (int i = 0; i < reader.FieldCount; i++)
{
fieldTypeLookup[reader.GetName(i)] = reader.GetDataTypeName(i);
}
}
trans.Rollback();
foreach (DataRow row in tableSchema.Rows)
{
/* IsIdentity */
/*
* int columnSize = (int)row["ColumnSize"];
* string sqlTypeCode =
* bool isReadOnly = (bool)row["IsReadOnly"];
* bool isPrimaryKey = (bool)row["IsKey"];
* bool isAutoIncrement = (bool)row["IsAutoIncrement"];
*/
string fieldName = row["ColumnName"].ToString();
string baseTableName = row["BaseTableName"].ToString();
string baseColumnName = row["BaseColumnName"].ToString();
bool isNullable = (bool)row["AllowDBNull"];
string dataTypeCode = TypeConvertor.ConvertSQLToCSharp(fieldTypeLookup[fieldName].ToLowerInvariant());
/* TODO: This check wouldn't really be necessary if we could rely on GetSchemaTable's DataType field */
if (tableDefMap.ContainsKey(baseTableName))
{
if (tableDefMap[baseTableName].ColumnDefMap.ContainsKey(baseColumnName))
{
string newDataTypeCode = tableDefMap[baseTableName].ColumnDefMap[baseColumnName].ColumnType;
if (newDataTypeCode != dataTypeCode)
{
Shared.Warning(" GetTableSchema reported an incorrect data type of " + dataTypeCode + " from stored procedure " + procedureDef.ProcedureName + ", field " + fieldName + ", instead of the source table's column data type of " + newDataTypeCode + ".");
dataTypeCode = newDataTypeCode;
}
}
}
FieldDef fieldDef = new FieldDef {
FieldName = fieldName,
ProcedureDef = procedureDef,
DataTypeCode = dataTypeCode,
IsNullable = isNullable,
BaseTableName = baseTableName,
BaseColumnName = baseColumnName
};
procedureDef.FieldDefMap[fieldDef.FieldName] = fieldDef;
}
}
}
}
return(globalProcedureDefMap.Values.ToList <ProcedureDef>());
}
/* TODO: Combine commonality with MSSQL's, and include its name mapping functionality */
public TableDefMap ExtractTableData(List <TableMapping> tableMappingList, bool cleanOracle)
{
MySqlConnection conn = this.mySqlConnection;
var tableDefList = new TableDefMap();
// DataTable table = conn.GetSchema("MetaDataCollections");
DataTable tablesData = conn.GetSchema("tables", new string[] { null, null, null, null });
//DataTable table = conn.GetSchema("UDF");
// DisplayData(table);
Shared.Info("Table count: " + tablesData.Rows.Count);
foreach (System.Data.DataRow row in tablesData.Rows)
{
string tableName = (string)row["TABLE_NAME"];
string cleanName = tableName;
if (cleanOracle == true)
{
cleanName = DeOracle.Clean(tableName);
}
TableDef tableDef = new TableDef {
TableName = tableName,
TableType = "TABLE",
CleanName = cleanName,
ColumnDefMap = new Dictionary <string, ColumnDef>(),
IndexDefMap = new Dictionary <string, IndexDef>(),
ProcedureDefMap = new Dictionary <string, ProcedureDef>(),
FieldDefList = new List <FieldDef>(),
ForeignKeyList = new List <string>(),
ArgumentName = Char.ToLowerInvariant(tableName[0]) + tableName.Substring(1)
};
tableDefList[tableName] = tableDef;
Shared.Info("Adding table " + tableName);
}
DataTable viewsData = conn.GetSchema("views", new string[] { null, null, null, null });
//DataTable table = conn.GetSchema("UDF");
// DisplayData(table);
Shared.Info("View count: " + viewsData.Rows.Count);
/*
* foreach (System.Data.DataColumn col in viewsData.Columns) {
* Shared.Info(col.ColumnName);
* }
*/
foreach (System.Data.DataRow row in viewsData.Rows)
{
string tableName = (string)row["TABLE_NAME"];
string cleanName = tableName;
if (cleanOracle == true)
{
cleanName = DeOracle.Clean(tableName);
}
TableDef tableDef = new TableDef {
TableName = tableName,
CleanName = cleanName,
TableType = "VIEW",
ColumnDefMap = new Dictionary <string, ColumnDef>(),
IndexDefMap = new Dictionary <string, IndexDef>(),
ProcedureDefMap = new Dictionary <string, ProcedureDef>(),
FieldDefList = new List <FieldDef>(),
ForeignKeyList = new List <string>()
};
tableDefList[tableName] = tableDef;
Shared.Info("Adding view " + tableName);
}
// var columnRowArray = sql.getDataRows("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='" + databaseName + "' ORDER BY TABLE_NAME ASC, ORDINAL_POSITION ASC");
DataTable columnsData = conn.GetSchema("columns", new string[] { null, null, null, null });
foreach (System.Data.DataRow row in columnsData.Rows)
{
string columnName = (string)row["COLUMN_NAME"];
string tableName = (string)row["TABLE_NAME"];
Shared.Info("Adding column " + columnName + " from table " + tableName);
ulong?dataLength;
if (row["CHARACTER_MAXIMUM_LENGTH"] == DBNull.Value)
{
dataLength = null;
}
else
{
dataLength = (ulong)row["CHARACTER_MAXIMUM_LENGTH"];
}
string cleanName = columnName;
if (cleanOracle == true)
{
cleanName = DeOracle.Clean(columnName);
}
ColumnDef columnDef = new ColumnDef {
ColumnName = columnName,
CleanName = cleanName,
ColumnType = (string)row["DATA_TYPE"],
DataLength = dataLength,
IsIdentity = ((string)row["EXTRA"]).Contains("auto_increment"),
IsNullable = (string)row["IS_NULLABLE"] == "YES"
};
tableDefList[tableName].ColumnDefMap[columnName] = columnDef;
}
/* will get primary key and unique key, which is used for Read and ReadFor functions
* also foreign key constraints, but we don't really use those at this point (we could potentially, to map within our object structdefs
*/
// "select * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='" + databaseName + "';"
// var indexRowArray = sql.getDataRows("SELECT * FROM KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA='" + databaseName + "' ORDER BY CONSTRAINT_NAME ASC, ORDINAL_POSITION ASC");
/* will get indexes for use in ListFor functions */
// var indexRowArray = sql.getDataRows("SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema = '" + databaseName + "' ORDER BY table_name, index_name, seq_in_index;");
DataTable indexData = conn.GetSchema("Indexes", new string[] { null, null, null, null });
Shared.Info("Index count: " + indexData.Rows.Count);
foreach (System.Data.DataRow row in indexData.Rows)
{
string tableName = (string)row["TABLE_NAME"];
string indexName = (string)row["INDEX_NAME"];
Shared.Info("Adding index " + indexName + " from table " + tableName);
TableDef table = tableDefList[tableName];
table.IndexDefMap[indexName] = new IndexDef {
IndexName = indexName,
IsPrimary = (indexName == "PRIMARY"),
// IsUnique = ((bool)row["NON_UNIQUE"]!=true),
IsUnique = (bool)row["UNIQUE"],
ColumnDefList = new List <ColumnDef>()
};
}
DataTable indexColumnsData = conn.GetSchema("IndexColumns", new string[] { null, null, null, null });
foreach (System.Data.DataRow row in indexColumnsData.Rows)
{
string tableName = (string)row["TABLE_NAME"];
string indexName = (string)row["INDEX_NAME"];
string columnName = (string)row["COLUMN_NAME"];
Shared.Info("Adding index column " + columnName + " from index " + indexName + " on table " + tableName);
// int ordinalPosition = (int)row["ORDINAL_POSITION"];
/* SORT_ORDER */
TableDef tableDef = tableDefList[tableName];
ColumnDef columnDef = tableDef.ColumnDefMap[columnName];
IndexDef indexDef = tableDef.IndexDefMap[indexName];
indexDef.ColumnDefList.Add(columnDef);
}
DataTable foreignKeyColumnsData = conn.GetSchema("Foreign Key Columns");
foreach (System.Data.DataRow row in foreignKeyColumnsData.Rows)
{
TableDef tableDef = tableDefList[row["TABLE_NAME"].ToString()];
ColumnDef columnDef = tableDef.ColumnDefMap[row["COLUMN_NAME"].ToString()];
columnDef.ReferencedTableDef = tableDefList[row["REFERENCED_TABLE_NAME"].ToString()];
columnDef.ReferencedColumnDef = columnDef.ReferencedTableDef.ColumnDefMap[row["REFERENCED_COLUMN_NAME"].ToString()];
}
return(tableDefList);
}
public static string GetFunctionalName(TableDef tableDef)
{
var prefix = (tableDef.SchemaName == "dbo") ? "" : tableDef.SchemaName;
return(prefix + tableDef.CleanName);
}
