Merge pull request lerocha#35 from 0xced/improve-postgres

Improve the PostgreSQL scripts
YavinOwens · Jan 27, 2024 · 6c71228 · 6c71228
2 parents cc33fac + 138c208
commit 6c71228
Show file tree

Hide file tree

Showing 10 changed files with 31,733 additions and 15,837 deletions.
diff --git a/ChinookDatabase/DataSources/ChinookDatabase.tt b/ChinookDatabase/DataSources/ChinookDatabase.tt
@@ -22,6 +22,7 @@ var options = new
 								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 }
 						   	},
 	OutputFiles = new List<OutputFile>()
@@ -160,7 +161,7 @@ CREATE TABLE <#= strategy.GetFullyQualifiedName(tableName) #>
 	if (strategy.PrimaryKeyDef==KeyDefinition.OnCreateTableBottom ||
 		(strategy.PrimaryKeyDef==KeyDefinition.OnCreateTableColumn && keys.Count() > 1))
 	{
-		string pkName = strategy.FormatName("PK_" + tableName);#>,
+		string pkName = strategy.FormatPrimaryKey(tableName);#>,
     CONSTRAINT <#= pkName #> PRIMARY KEY <#= strategy.GetClustered(table)#> (<#= strategy.GetColumns(keys, ',')#>)<#
 	}
 
@@ -202,7 +203,7 @@ CREATE TABLE <#= strategy.GetFullyQualifiedName(tableName) #>
         // Create Primary Key Constraint.
         if (strategy.PrimaryKeyDef==KeyDefinition.OnAlterTable)
         {
-            string pkName = strategy.FormatName("PK_" + tableName);
+            string pkName = strategy.FormatPrimaryKey(tableName);
 #>
 ALTER TABLE <#= tableName #> ADD CONSTRAINT <#= pkName #> PRIMARY KEY (<#= strategy.GetColumns(keys, ',')#>);
 <#= strategy.WriteExecuteCommand() #>
@@ -234,9 +235,8 @@ CREATE UNIQUE INDEX <#= pkIndexName #> ON <#= tableName #>(<#= strategy.GetColum
         {
 			var toTableName = fk.RelatedTable.TableName;
 			var fromTableName = table.TableName;
-			var constraintName = "FK_" + fromTableName + fk.Columns[0].ColumnName;
-			var fkName = strategy.FormatName(constraintName);
-			var ifkName = strategy.FormatName("I" + constraintName);
+			var fkName = strategy.FormatForeignKey(fromTableName, fk.Columns[0].ColumnName);
+			var ifkName = strategy.FormatForeignKeyIndex(fromTableName, fk.Columns[0].ColumnName);
 
 			if (strategy.ForeignKeyDef==KeyDefinition.OnAlterTable)
 			{

diff --git a/ChinookDatabase/DataSources/ChinookDatabase.txt b/ChinookDatabase/DataSources/ChinookDatabase.txt
@@ -1,4 +1,3 @@
-
 Chinook Database 1.4.2
 * This is a sample database available in multiple formats: SQL scripts for multiple database vendors, embeded database files, and XML format.
 * The Chinook data model is available [here|Chinook_Schema].
@@ -29,6 +28,7 @@ This is the list of files included in the complete version.
 | Oracle | {"Chinook_Oracle.sql"} | SQL script to create the Chinook database.  |
 | Oracle | {"CreateOracle.bat"} | Batch file to create the Chinook database. |
 | PostgreSql | {"Chinook_PostgreSql.sql"} | SQL script to create the Chinook database.  |
+| PostgreSql | {"Chinook_PostgreSql_AutoIncrementPKs.sql"} | SQL script to create the Chinook database. Auto incremented primary keys. |
 | PostgreSql | {"CreatePostgreSql.bat"} | Batch file to create the Chinook database. |
 | Sqlite | {"Chinook_Sqlite.sql"} | SQL script to create the Chinook database.  |
 | Sqlite | {"Chinook_Sqlite.sqlite"} | Chinook database file.  |

diff --git a/ChinookDatabase/DataSources/Chinook_PostgreSql.sql b/ChinookDatabase/DataSources/Chinook_PostgreSql.sql