Skip to content

Commit

Permalink
feat: auto generate SQL scripts for serial primary keys (lerocha#43)
Browse files Browse the repository at this point in the history 
* Creating PrimaryKeyStrategy

* Adding support to primary key columns with serial type
  • Loading branch information
@lerocha
lerocha authored Feb 10, 2024
1 parent 554b65d commit 383c1f7
Show file tree
Hide file tree
Showing 6 changed files with 56 additions and 28 deletions.
37 changes: 21 additions & 16 deletions ChinookDatabase/DataSources/ChinookDatabase.tt
View file
Original file line number Diff line number Diff line change
Expand Up @@ -12,18 +12,18 @@ var options = new
DataSource = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), "_Xml", "ChinookData.xml"),
DdlStrategies = new IDdlStrategy[]
{
new SqlServerStrategy { IsIdentityEnabled = false, Encoding = Encoding.Unicode },
new SqlServerStrategy { IsIdentityEnabled = true, Encoding = Encoding.Unicode },
new SqlServerCompactStrategy { IsIdentityEnabled = false },
new SqlServerCompactStrategy { IsIdentityEnabled = true },
new SqliteStrategy { IsIdentityEnabled = false },
new SqliteStrategy { IsIdentityEnabled = true, PrimaryKeyDef = KeyDefinition.OnCreateTableColumn },
new MySqlStrategy { IsIdentityEnabled = false },
new MySqlStrategy { IsIdentityEnabled = true },
new OracleStrategy { IsIdentityEnabled = false, Encoding = Encoding.UTF8 },
new PostgreSqlStrategy { IsIdentityEnabled = false, Encoding = Encoding.Default },
new PostgreSqlStrategy { IsIdentityEnabled = true, Encoding = Encoding.Default },
new Db2Strategy { IsIdentityEnabled = false, Encoding = Encoding.Default }
new SqlServerStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None, Encoding = Encoding.Unicode },
new SqlServerStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.Identity, Encoding = Encoding.Unicode },
new SqlServerCompactStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None },
new SqlServerCompactStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.Identity },
new SqliteStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None },
new SqliteStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.Identity, PrimaryKeyDef = KeyDefinition.OnCreateTableColumn },
new MySqlStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None },
new MySqlStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.Identity },
new OracleStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None, Encoding = Encoding.UTF8 },
new PostgreSqlStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None, Encoding = Encoding.Default },
new PostgreSqlStrategy { PrimaryKeyStrategy = PrimaryKeyStrategy.Identity, Encoding = Encoding.Default },
new Db2Strategy { PrimaryKeyStrategy = PrimaryKeyStrategy.None, Encoding = Encoding.Default }
},
OutputFiles = new List<OutputFile>()
};
Expand All @@ -44,7 +44,7 @@ foreach (IDdlStrategy strategy in options.DdlStrategies)
var filename = GetFileName(strategy, strategy.ScriptFileExtension);
fileManager.StartNewFile(filename, strategy.Encoding);

var details = (strategy.IsIdentityEnabled ? "Auto incremented primary keys." : string.Empty);
var details = (strategy.PrimaryKeyStrategy == PrimaryKeyStrategy.Identity ? "Auto incremented primary keys." : string.Empty);

// Add the script file to the package list.
options.OutputFiles.Add(new OutputFile { Name = filename, Package = strategy.Name, Description = "SQL script to create the Chinook database. " + details });
Expand Down Expand Up @@ -285,7 +285,7 @@ CREATE INDEX <#= ifkName #> ON <#= strategy.GetFullyQualifiedName(fromTableName)
foreach (DataColumn col in table.Columns)
{
string value = row[col.ColumnName].ToString();
if ((col.AutoIncrement && strategy.IsIdentityEnabled) || value.Length==0) continue;
if ((col.AutoIncrement && strategy.PrimaryKeyStrategy != PrimaryKeyStrategy.None) || value.Length==0) continue;

if (col.DataType == typeof(DateTime))
{
Expand Down Expand Up @@ -453,8 +453,13 @@ public class OutputFile {

private static string GetFileName(IDdlStrategy strategy, string extension)
{
var suffix = (strategy.IsIdentityEnabled ? "_AutoIncrementPKs" : string.Empty);
return string.Format("Chinook_{0}{1}.{2}", strategy.Name, suffix, extension);
var suffix = strategy.PrimaryKeyStrategy switch {
PrimaryKeyStrategy.Identity => "_AutoIncrementPKs",
PrimaryKeyStrategy.Serial => "_SerialPKs",
_ => string.Empty
};

return $"Chinook_{strategy.Name}{suffix}.{extension}";
}

private static string GetValues(IEnumerable<string> values, char delimiter)
Expand Down
9 changes: 5 additions & 4 deletions ChinookDatabase/DdlStrategies/AbstractDdlStrategy.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -29,7 +29,7 @@ protected AbstractDdlStrategy()

public KeyDefinition PrimaryKeyDef { get; set; }
public KeyDefinition ForeignKeyDef { get; set; }
public bool IsIdentityEnabled { get; set; }
public PrimaryKeyStrategy PrimaryKeyStrategy { get; set; }
public bool IsReCreateDatabaseEnabled { get; set; }
public string CommandLineFormat { get; set; }
public Encoding Encoding { get; set; }
Expand Down Expand Up @@ -88,12 +88,13 @@ public virtual string GetColumns(IEnumerable<String> keys, char delimiter)

public virtual string WriteCreateColumn(DataColumn column)
{
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var isPrimaryKey = column.Table?.PrimaryKey.Length == 1 && column.Table?.PrimaryKey.Contains(column) == true;
var identity = IsIdentityEnabled && isPrimaryKey ? Identity : String.Empty;
var type = isPrimaryKey && (PrimaryKeyStrategy == PrimaryKeyStrategy.Serial) ? "SERIAL" : GetStoreType(column);
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var identity = (PrimaryKeyStrategy == PrimaryKeyStrategy.Identity) && isPrimaryKey ? Identity : String.Empty;
return string.Format("{0} {1} {2} {3}",
FormatName(column.ColumnName),
GetStoreType(column),
type,
notnull, identity).Trim();
}

Expand Down
2 changes: 1 addition & 1 deletion ChinookDatabase/DdlStrategies/IDdlStrategy.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -19,7 +19,7 @@ public interface IDdlStrategy

KeyDefinition PrimaryKeyDef { get; set; }
KeyDefinition ForeignKeyDef { get; set; }
bool IsIdentityEnabled { get; set; }
public PrimaryKeyStrategy PrimaryKeyStrategy { get; set; }
bool IsReCreateDatabaseEnabled { get; set; }
string CommandLineFormat { get; set; }
Encoding Encoding { get; set; }
Expand Down
9 changes: 9 additions & 0 deletions ChinookDatabase/DdlStrategies/PrimaryKeyStrategy.cs
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,9 @@
namespace ChinookDatabase.DdlStrategies
{
public enum PrimaryKeyStrategy
{
None,
Identity,
Serial
}
}
2 changes: 1 addition & 1 deletion ChinookDatabase/DdlStrategies/SqliteStrategy.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -34,7 +34,7 @@ public override string WriteCreateColumn(DataColumn column)
{
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var isPrimaryKey = column.Table?.PrimaryKey.Length == 1 && column.Table?.PrimaryKey.Contains(column) == true;
var identity = IsIdentityEnabled && isPrimaryKey ? Identity : String.Empty;
var identity = (PrimaryKeyStrategy == PrimaryKeyStrategy.Identity) && isPrimaryKey ? Identity : String.Empty;
return string.Format("{0} {1} {2} {3}",
FormatName(column.ColumnName),
GetStoreType(column),
Expand Down
25 changes: 19 additions & 6 deletions ChinookDatabase/_T4Templates/Chinook.ttinclude
View file
Original file line number Diff line number Diff line change
Expand Up @@ -42,6 +42,7 @@
// MySqlStrategy.cs
// OracleStrategy.cs
// PostgreSqlStrategy.cs
// PrimaryKeyStrategy.cs
// SqliteStrategy.cs
// SqlServerCompactStrategy.cs
// SqlServerStrategy.cs
Expand Down Expand Up @@ -6278,7 +6279,7 @@

public KeyDefinition PrimaryKeyDef { get; set; }
public KeyDefinition ForeignKeyDef { get; set; }
public bool IsIdentityEnabled { get; set; }
public PrimaryKeyStrategy PrimaryKeyStrategy { get; set; }
public bool IsReCreateDatabaseEnabled { get; set; }
public string CommandLineFormat { get; set; }
public Encoding Encoding { get; set; }
Expand Down Expand Up @@ -6337,12 +6338,13 @@

public virtual string WriteCreateColumn(DataColumn column)
{
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var isPrimaryKey = column.Table?.PrimaryKey.Length == 1 && column.Table?.PrimaryKey.Contains(column) == true;
var identity = IsIdentityEnabled && isPrimaryKey ? Identity : String.Empty;
var type = isPrimaryKey && (PrimaryKeyStrategy == PrimaryKeyStrategy.Serial) ? "SERIAL" : GetStoreType(column);
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var identity = (PrimaryKeyStrategy == PrimaryKeyStrategy.Identity) && isPrimaryKey ? Identity : String.Empty;
return string.Format("{0} {1} {2} {3}",
FormatName(column.ColumnName),
GetStoreType(column),
type,
notnull, identity).Trim();
}

Expand Down Expand Up @@ -6424,7 +6426,7 @@

KeyDefinition PrimaryKeyDef { get; set; }
KeyDefinition ForeignKeyDef { get; set; }
bool IsIdentityEnabled { get; set; }
public PrimaryKeyStrategy PrimaryKeyStrategy { get; set; }
bool IsReCreateDatabaseEnabled { get; set; }
string CommandLineFormat { get; set; }
Encoding Encoding { get; set; }
Expand Down Expand Up @@ -6635,6 +6637,17 @@
private static string ToSnakeCase(string text) => snakeCaseNamingStrategy.GetPropertyName(text, false);
}

//------------------------------------------------------------------------------
// Filename: PrimaryKeyStrategy.cs
//------------------------------------------------------------------------------

public enum PrimaryKeyStrategy
{
None,
Identity,
Serial
}

//------------------------------------------------------------------------------
// Filename: SqliteStrategy.cs
//------------------------------------------------------------------------------
Expand Down Expand Up @@ -6672,7 +6685,7 @@
{
var notnull = (column.AllowDBNull ? "" : "NOT NULL");
var isPrimaryKey = column.Table?.PrimaryKey.Length == 1 && column.Table?.PrimaryKey.Contains(column) == true;
var identity = IsIdentityEnabled && isPrimaryKey ? Identity : String.Empty;
var identity = (PrimaryKeyStrategy == PrimaryKeyStrategy.Identity) && isPrimaryKey ? Identity : String.Empty;
return string.Format("{0} {1} {2} {3}",
FormatName(column.ColumnName),
GetStoreType(column),
Expand Down

0 comments on commit 383c1f7

Please sign in to comment.