/* 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 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);
}
public static void Main(string[] args)
{
try {
Parser.Default.ParseArguments <TidalOptions>(args)
.WithParsed <TidalOptions>(tidalOptions => {
bool proceed = tidalOptions.BuildFromArguments(args);
if (proceed == true)
{
Shared.Verbose = tidalOptions.Verbose;
Shared.Info("Starting Tidal " + DateTime.Now);
Shared.Info("looking for password");
if (tidalOptions.PasswordPrompt == true)
{
Console.Write("Password:"******"creating processor");
IProcessor processor = null;
switch (tidalOptions.DatabaseType)
{
case DatabaseTypeEnum.mssql:
processor = new MicrosoftSQLProcessor();
break;
case DatabaseTypeEnum.mysql:
processor = new MySQLProcessor();
break;
}
Shared.Info("creating connection string");
DbConnection conn = null;
string databaseName = null;
if (tidalOptions.ConnectionString != null)
{
DbConnectionStringBuilder dbcs = new DbConnectionStringBuilder();
dbcs.ConnectionString = tidalOptions.ConnectionString;
Shared.Info("checking for database name");
if (dbcs.ContainsKey("database"))
{
databaseName = (string)dbcs["database"];
}
else
{
if (dbcs.ContainsKey("Initial Catalog"))
{
databaseName = (string)dbcs["Initial Catalog"];
}
else
{
throw new ApplicationException("A database name is required in the connection string. Please use either Database=VALUE or Initial Catalog=VALUE.");
}
}
Shared.Info("making connection");
conn = processor.GetConnection(tidalOptions.ConnectionString, tidalOptions.Password);
}
Shared.Info("***" + tidalOptions.TranslationFileName);
List <TableMapping> tableMappingList = null;
if (tidalOptions.TranslationFileName != null)
{
tableMappingList = MappingFileReader.ReadFromFile(tidalOptions.TranslationFileName);
}
using (conn) {
if (conn != null)
{
Shared.Info("opening connection");
conn.Open();
}
List <TableDef> tableDefList = null;
TableDefMap tableDefMap = null;
if (tidalOptions.ShouldMakeTableDefMap == true)
{
Shared.Info("getting table extractor");
ITableExtractor extractor = processor.GetTableExtractor();
if (tidalOptions.ShouldMakeTableDefMap == true)
{
Shared.Info("extracting table data with " + extractor.GetType());
tableDefMap = extractor.ExtractTableData(tableMappingList, tidalOptions.CleanOracle);
if (tidalOptions.TableDefFileNameOut != null)
{
Shared.Info("writing table def file");
File.WriteAllText(tidalOptions.TableDefFileNameOut, tableDefMap.ToJSONString());
}
}
tableDefList = tableDefMap.Values.ToList <TableDef>();
if (tidalOptions.TableCreateScriptFileName != null)
{
Shared.Info("writing create table script");
ITableScriptWriter creationWriter = processor.GetTableScriptWriter();
string tableCreationScriptText = creationWriter.GetTableCreateScriptText(databaseName, tableDefList);
File.WriteAllText(tidalOptions.TableCreateScriptFileName, tableCreationScriptText);
}
if (tidalOptions.TableDropScriptFileName != null)
{
Shared.Info("writing drop table script");
ITableScriptWriter dropWriter = processor.GetTableScriptWriter();
string tableCreationScriptText = dropWriter.GetTableDropScriptText(databaseName, tableDefList);
File.WriteAllText(tidalOptions.TableDropScriptFileName, tableCreationScriptText);
}
if (tidalOptions.ModelsPathOut != null)
{
Shared.Info("making models");
ModelCreator.MakeModels(tableDefList,
tidalOptions.ModelsNamespace,
tidalOptions.ModelsPathOut,
tableMappingList,
tidalOptions.CleanOracle);
}
}
Dictionary <string, ModelDef> modelDefMap = null;
/* TODO: allow create model by only reading database */
if (tidalOptions.ModelsAssemblyFileNameList.Count > 0)
{
modelDefMap = new Dictionary <string, ModelDef> ();
foreach (string fileName in tidalOptions.ModelsAssemblyFileNameList)
{
Shared.Info("reading models from assembly " + fileName);
/* read object model */
ModelReader.AddToFromFile(modelDefMap, fileName, tidalOptions.ModelsNamespace);
}
}
if (tidalOptions.ModelDefFileNameOut != null)
{
Shared.Info("Writing model defs to file...");
ModelWriter.WriteDefsToFile(modelDefMap.Values.ToList(), tidalOptions.ModelDefFileNameOut);
Shared.Info("Model defs written.");
}
/* if we will create stored procs */
if (tidalOptions.ModuleName != null)
{
string storedProcedureSQLText = "";
if (tidalOptions.SQLScriptFileNameOut != null || tidalOptions.CreateProcedures == true)
{
Shared.Info("calcing stored proc script");
IProcedureCreator procCreator = processor.GetProcedureCreator();
storedProcedureSQLText = procCreator.GetStoredProcedureScriptText(tidalOptions.ModuleName, tableDefList, 1, tidalOptions.IgnoreTableNameList);
}
// Shared.Info(storedProcedureSQLText);
if (tidalOptions.SQLScriptFileNameOut != null)
{
Shared.Info("writing stored proc script file");
File.WriteAllText(tidalOptions.SQLScriptFileNameOut, storedProcedureSQLText);
}
if (tidalOptions.RemoveProcedures == true)
{
Shared.Info("removing old procedures");
/* remove Tidal generated procedures for this module (in case the tables are gone, e.g.) */
IProcedureRemover procRemover = processor.GetProcedureRemover();
procRemover.RemoveTidalStoredProcs(databaseName, tidalOptions.ModuleName);
}
if (tidalOptions.CreateProcedures == true)
{
Shared.Info("executing stored proc script");
/* execute Tidal sql script */
IScriptExecutor scriptExecutor = processor.GetScriptExecutor();
scriptExecutor.ExecuteTidalProcedureScript(storedProcedureSQLText);
}
}
List <ProcedureDef> procedureDefList = null;
if (tidalOptions.ShouldMakeProcedureDefList == true)
{
Shared.Info("absorbing stored proc definitions");
/* read stored procedures and generate stored procedure defs */
IProcedureReader procReader = processor.GetProcedureReader();
procedureDefList = procReader.MakeProcedureDefList(databaseName, tidalOptions.ModuleName, tableDefMap);
if (tidalOptions.StoredProcDefFileNameOut != null)
{
Shared.Info("writing stored proc definition json file");
var sbSPJson = new StringBuilder("[");
bool first = true;
foreach (var procedureDef in procedureDefList)
{
if (first == true)
{
first = false;
}
else
{
sbSPJson.AppendLine(",");
}
sbSPJson.Append(procedureDef.ToJSONString());
}
sbSPJson.Append("]");
File.WriteAllText(tidalOptions.StoredProcDefFileNameOut, sbSPJson.ToString());
}
}
if (tidalOptions.DataAccessFileNameOut != null)
{
/* convert the procedures, parameters, and outputs into function calls and arguments */
List <ModelDef> modelDefList = FunctionCreator.CreateModelDefList(
tidalOptions.ModelsNamespace,
tidalOptions.ModuleName,
modelDefMap,
procedureDefList,
tidalOptions.IgnoreTableNameList,
tableMappingList,
tidalOptions.CleanOracle);
/* combine with stored proc defs to create DataAccess class */
ClassCreatorBase classCreator = processor.GetClassCreator();
string allText = classCreator.GetAllText(tidalOptions.ProjectNamespace, modelDefList);
File.WriteAllText(tidalOptions.DataAccessFileNameOut, allText);
}
if (conn != null)
{
conn.Close();
}
}
}
});
}
catch (SqlException ex) {
Shared.Info("SQL Error: " + ex.ToString());
Shared.Info("Line number: " + ex.LineNumber);
throw;
}
}