/// <summary> /// Generate the CREATE TYPE and DROP TYPE statements. /// </summary> /// <param name="dtTypes"></param> /// <param name="dtSeq"></param> private static void GenerateTypeScripts(DataTable dtTypes, DataTable dtSeq) { var createPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateTypes); var dropPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateDropTypes); StreamWriter swCreate = null; StreamWriter swDrop = null; try { swCreate = new StreamWriter(createPath, false, Encoding.Default); swDrop = new StreamWriter(dropPath, false, Encoding.Default); swCreate.WriteBeginTrans(); swDrop.WriteBeginTrans(); // Write CREATE TYPE statements. foreach (DataRow row in dtTypes.Rows) { var schema = row["schema_name"].ToString().Equals(Constants.PgDefaultSchema) ? Constants.SsDefaultSchema : row["schema_name"].ToString(); var typeName = "[" + schema + "].[" + row["type_name"] + "]"; swCreate.WriteLine("CREATE TYPE " + typeName); var dataType = Postgres.SsDataType(row["regtype"].ToString()); swCreate.WriteLine( "FROM [" + dataType + "]" + GenerateColumnDimDef(row, dataType) + ";"); swCreate.WriteLine(); swDrop.WriteLine("DROP TYPE " + typeName + ";"); } // Write CREATE SEQUENCE statements. foreach (DataRow row in dtSeq.Rows) { var schema = row["schema_name"].ToString().Equals(Constants.PgDefaultSchema) ? Constants.SsDefaultSchema : row["schema_name"].ToString(); var seqName = "[" + schema + "].[" + row["seq_name"] + "]"; var dataType = Postgres.SsDataType(row["regtype"].ToString()); var maxValue = Postgres.GetMaxValByType(dataType); var maxValueRec = (long) row["max_value"]; maxValue = maxValueRec > maxValue ? maxValue : maxValueRec; swCreate.WriteLine("CREATE SEQUENCE " + seqName); swCreate.WriteLine(Constants.Tab + "AS [" + dataType + "]"); swCreate.Write(Constants.Tab + "START WITH "); swCreate.WriteLine(row["start_value"]); swCreate.Write(Constants.Tab + "INCREMENT BY "); swCreate.WriteLine(row["increment_by"]); swCreate.Write(Constants.Tab + "MINVALUE "); swCreate.WriteLine(row["min_value"]); swCreate.Write(Constants.Tab + "MAXVALUE "); swCreate.WriteLine(maxValue); swCreate.WriteLine( Constants.Tab + ((bool) row["is_cycled"] ? "CYCLE" : "NO CYCLE")); swCreate.Write(Constants.Tab + "CACHE "); swCreate.WriteLine(row["cache_value"]); swCreate.WriteLine(";"); swCreate.WriteLine(); swDrop.WriteLine("DROP SEQUENCE " + seqName + ";"); } swCreate.WriteCommitTrans(); swDrop.WriteCommitTrans(); } catch (Exception ex) { _log.WriteEx('E', Constants.LogTsType, ex); } finally { swCreate?.Dispose(); swDrop?.Dispose(); } }
/// <summary> /// /// </summary> /// <param name="dt"></param> private static void GenerateFkConstraints(DataTable dt) { var path = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateForeignKeys); using (var sw = new StreamWriter(path, false, Encoding.Default)) { var saveFkName = ""; var cnt = 0; sw.WriteBeginTrans(); foreach (DataRow row in dt.Rows) { if (saveFkName != row["fk_name"].ToString()) { saveFkName = row["fk_name"].ToString(); cnt = 0; } else cnt++; sw.WriteFkStatement(row, cnt); } sw.WriteCommitTrans(); } }
/// <summary> /// Generate the SQL Server scripts. /// </summary> /// <param name="schemaTable">DataTable with all the PostgreSQL schema/table/column info.</param> /// <param name="seqTable"></param> /// <param name="conn">PG connection.</param> private static void GenerateTableScripts( DataTable schemaTable, DataTable seqTable, NpgsqlConnection conn) { var createPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateTables); var dropPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateDropTables); var truncPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateSTruncateTables); StreamWriter swCreate = null; StreamWriter swDrop = null; StreamWriter swTrunc = null; try { swCreate = new StreamWriter(createPath, false, Encoding.Default); swDrop = new StreamWriter(dropPath, false, Encoding.Default); swTrunc = new StreamWriter(truncPath, false, Encoding.Default); swCreate.WriteBeginTrans(); swDrop.WriteBeginTrans(); swTrunc.WriteBeginTrans(); swCreate.PrepCreateTable(); var savedSchema = ""; var savedTable = ""; var colInfo = new List<ColumnInfo>(); foreach (DataRow row in schemaTable.Rows) { var schema = row["schema_name"].ToString(); var table = row["table_name"].ToString(); // Schema or table changed: close table defenition. if (!savedSchema.Equals(schema) || !savedTable.Equals(table)) { if (!string.IsNullOrEmpty(savedTable)) { CloseCreateTable(swCreate, colInfo); UpdateSeqTable(colInfo, seqTable); colInfo.Clear(); } savedSchema = schema; savedTable = table; swCreate.OpenCreateTable(schema, table); swDrop.WriteDropCommand(schema, table); swTrunc.WriteTruncateCommand(schema, table); } else swCreate.WriteLine(","); // Generate column definition. ColumnInfo tmpColInfo; swCreate.GenerateColumn(row, out tmpColInfo); if (!string.IsNullOrEmpty(tmpColInfo.Schema)) { colInfo.Add(tmpColInfo); } } if (string.IsNullOrEmpty(savedSchema)) return; // Complete last writes. swCreate.CloseCreateTable(colInfo); swCreate.WriteTableDesc(conn); swCreate.WriteCommitTrans(); swDrop.WriteCommitTrans(); swTrunc.WriteCommitTrans(); } catch (Exception ex) { _log.WriteEx('E', Constants.LogTsType, ex); } finally { swCreate?.Dispose(); swDrop?.Dispose(); swTrunc?.Dispose(); } }
/// <summary> /// Write the different create indeces/key constraint statements. /// </summary> /// <param name="conn"></param> private static void GenerateBuildIndexes(NpgsqlConnection conn) { var indexPath = Path.Combine( _params[Parameters.OtherWorkPath].ToString(), Constants.CreateIndexesEtAl); // option column values: // INDOPTION_DESC 0x0001 = values are in reverse order (DESC) // INDOPTION_NULLS_FIRST 0x0002 = NULLs are first instead of last const string sql = @"SELECT n.nspname AS schema_name, c.relname AS table_name, d.relname AS index_name, i.indisprimary, i.indisunique, a.attname AS column_name, i.indoption[a.attnum - 1] as option FROM pg_index i LEFT JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY ( i.indkey ) LEFT JOIN pg_class c ON c.oid = i.indrelid LEFT JOIN pg_class d ON d.oid = i.indexrelid LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN('pg_toast', 'pg_catalog', 'information_schema', 'public') ORDER BY n.nspname ASC, c.relname ASC, d.relname ASC, a.attnum"; using (var cmd = new NpgsqlCommand(sql, conn)) using (var sw = new StreamWriter(indexPath, false, Encoding.Default)) { sw.WriteBeginTrans(); var savedSchema = ""; var savedTable = ""; var savedIndex = ""; var savedType = ' '; using (var reader = cmd.ExecuteReader()) { var sb = new StringBuilder(); while (reader.Read()) { var schema = reader["schema_name"].ToString(); var table = reader["table_name"].ToString(); var index = reader["index_name"].ToString(); // Schema, table or index changed: close index defenition. if (!savedSchema.Equals(schema) || !savedTable.Equals(table) || !savedIndex.Equals(index)) { if (sb.Length > 0) sw.WriteIndex( savedSchema, savedTable, savedIndex, savedType, sb.ToString()); sb.Clear(); savedSchema = schema; savedTable = table; savedIndex = index; if ((bool) reader["indisprimary"]) { savedType = 'P'; } else if ((bool) reader["indisunique"]) { savedType = 'U'; } else savedType = 'I'; } else sb.Append(", "); sb.Append("[" + reader["column_name"] + "]"); if (reader["option"] == DBNull.Value) continue; switch ((short) reader["option"]) { case 0: sb.Append(" ASC"); break; case 1: sb.Append(" DESC"); break; } } if (sb.Length > 0) sw.WriteIndex( savedSchema, savedTable, savedIndex, savedType, sb.ToString()); sw.WriteCommitTrans(); } } }