Skip to content

Commit 610ac9b

Browse files
rspeelerspeele
committed
Make default constraint names table-prefixed for all backends.
Previously I had thought that SQL Server was weird having constraints be global to the database. Indeed, Postgres technically has constraint names local to the table. But, it seems that constraints that require an associated index like UNIQUE and PRIMARY KEY constraints will get that index named matching the constraint, and the index name is scoped to the database, so this leads to collisions. This will be a cleaner approach overall anyway, since it leads to less surprises on TSQL when you use a custom constraint name and the translator hackily adds a table prefix to it. We still need to check constraint uniqueness in ModelOps so we can error when it is violated.
1 parent f9c9801 commit 610ac9b

File tree

10 files changed

+58
-52
lines changed

10 files changed

+58
-52
lines changed

‎src/Rezoom.SQL.Compiler/AST.fs‎

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -503,7 +503,8 @@ type [<NoComparison>] ColumnConstraintType<'t, 'e> =
503503
| PrimaryKeyConstraint of PrimaryKeyClause
504504
| UniqueConstraint
505505
| ForeignKeyConstraint of ForeignKeyClause<'t>
506-
member this.DefaultName(columnName : Name) =
506+
member this.DefaultName(tableName : Name, columnName : Name) =
507+
tableName + "_" +
507508
match this with
508509
| PrimaryKeyConstraint _ -> columnName + "_PK"
509510
| UniqueConstraint -> columnName + "_UNIQUE"
@@ -547,7 +548,8 @@ type [<NoComparison>] TableConstraintType<'t, 'e> =
547548
| TableIndexConstraint of TableIndexConstraintClause<'t, 'e>
548549
| TableForeignKeyConstraint of Name WithSource array * ForeignKeyClause<'t>
549550
| TableCheckConstraint of Expr<'t, 'e>
550-
member this.DefaultName() =
551+
member this.DefaultName(tableName : Name) =
552+
tableName + "_" +
551553
match this with
552554
| TableIndexConstraint con ->
553555
String.concat "_" [ for { Value = name, _ } in con.IndexedColumns -> name.Value ]

‎src/Rezoom.SQL.Compiler/CoreParser.fs‎

Lines changed: 27 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -866,8 +866,8 @@ let private columnConstraint =
866866
%% +.(zeroOrOne * constraintName)
867867
-- +.constraintType
868868
-- ws
869-
-|> fun name cty columnName ->
870-
{ Name = name |? cty.DefaultName(columnName)
869+
-|> fun name cty columnName tblName ->
870+
{ Name = name |? cty.DefaultName(tblName, columnName)
871871
ColumnConstraintType = cty
872872
}
873873

@@ -881,13 +881,13 @@ let private columnDef =
881881
-- +.(zeroOrOne * collation)
882882
-- +.(zeroOrOne * defaultValue)
883883
-- +.(columnConstraint * qty.[0..])
884-
-|> fun name typeName nullable collation defaultVal constraints ->
884+
-|> fun name typeName nullable collation defaultVal constraints tblName ->
885885
{ Name = name
886886
Type = typeName
887887
Nullable = Option.isSome nullable
888888
Collation = collation
889889
DefaultValue = defaultVal
890-
Constraints = constraints |> Seq.map ((|>) name) |> Seq.toArray
890+
Constraints = constraints |> Seq.map (fun f -> f name tblName) |> Seq.toArray
891891
}
892892

893893
let private tableIndexConstraintType =
@@ -927,8 +927,8 @@ let private tableConstraint =
927927
%% +.(zeroOrOne * constraintName)
928928
-- +.tableConstraintType
929929
-- ws
930-
-|> fun name cty ->
931-
{ Name = match name with | Some name -> name | None -> Name(cty.DefaultName())
930+
-|> fun name cty tblName ->
931+
{ Name = match name with | Some name -> name | None -> cty.DefaultName(tblName)
932932
TableConstraintType = cty
933933
}
934934

@@ -939,10 +939,13 @@ let private alterTableStmt =
939939
-- +.name
940940
-|> RenameTo
941941
let add =
942-
let addColumn = %% kw "COLUMN" -- +.withSource columnDef -|> AddColumn
942+
let addColumn =
943+
%% kw "COLUMN" -- +.withSource columnDef
944+
-|> fun cdef tblName -> AddColumn (applySource cdef tblName)
943945
let addDefault =
944-
%% kw "DEFAULT" -- kw "FOR" -- +.name -- ws -- +.expr -|> fun name expr -> AddDefault (name, expr)
945-
let addConstraint = withSource tableConstraint |>> AddConstraint
946+
%% kw "DEFAULT" -- kw "FOR" -- +.name -- ws -- +.expr
947+
-|> fun name expr _ -> AddDefault (name, expr)
948+
let addConstraint = withSource tableConstraint |>> fun cstr tblName -> AddConstraint (applySource cstr tblName)
946949
%% kw "ADD"
947950
-- +.[ addColumn
948951
addDefault
@@ -986,15 +989,16 @@ let private alterTableStmt =
986989
changeType
987990
]
988991
-|> (|>)
992+
let ignoreTblName parser = parser |>> fun x _ -> x
989993
%% kw "ALTER"
990994
-- kw "TABLE"
991995
-- +.objectName
992-
-- +.[ renameTo
996+
-- +.[ ignoreTblName renameTo
993997
add
994-
drop
995-
alterColumn
998+
ignoreTblName drop
999+
ignoreTblName alterColumn
9961000
]
997-
-|> fun table alteration -> { Table = table; Alteration = alteration }
1001+
-|> fun table alteration -> { Table = table; Alteration = alteration table.ObjectName }
9981002

9991003
let private createTableDefinition =
10001004
let part =
@@ -1007,16 +1011,20 @@ let private createTableDefinition =
10071011
-- +.(qty.[0..] /. tws ',' * part)
10081012
-- ')'
10091013
-- ws
1010-
-|> fun parts ->
1014+
-|> fun parts tblName ->
10111015
{ Columns =
1012-
parts |> Seq.choose (function | Choice2Of2 cdef -> Some cdef | Choice1Of2 _ -> None) |> Seq.toArray
1016+
parts
1017+
|> Seq.choose (function | Choice2Of2 cdef -> Some (applySource cdef tblName) | Choice1Of2 _ -> None)
1018+
|> Seq.toArray
10131019
Constraints =
1014-
parts |> Seq.choose (function | Choice1Of2 ct -> Some ct | Choice2Of2 _ -> None) |> Seq.toArray
1020+
parts
1021+
|> Seq.choose (function | Choice1Of2 ct -> Some (applySource ct tblName) | Choice2Of2 _ -> None)
1022+
|> Seq.toArray
10151023
}
10161024

10171025
let private createTableAs =
1018-
%[ %% kw "AS" -- +.selectStmt -|> CreateAsSelect
1019-
%% +.createTableDefinition -|> CreateAsDefinition
1026+
%[ %% kw "AS" -- +.selectStmt -|> fun select _ -> CreateAsSelect select
1027+
%% +.createTableDefinition -|> fun def tblName -> CreateAsDefinition (def tblName)
10201028
]
10211029

10221030
let private temporary = %(zeroOrOne * [kw "TEMPORARY"; kw "TEMP"])
@@ -1030,7 +1038,7 @@ let private createTableStmt =
10301038
-|> fun temp name createAs ->
10311039
{ Temporary = Option.isSome temp
10321040
Name = name
1033-
As = createAs
1041+
As = createAs name.ObjectName
10341042
}
10351043

10361044
let private createIndexStmt =

‎src/Rezoom.SQL.Compiler/DefaultStatementTranslator.fs‎

Lines changed: 4 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -255,8 +255,6 @@ type DefaultStatementTranslator(expectedVendorName : Name, indexer : IParameterI
255255
yield ws
256256
yield! this.ForeignKeyOnDelete(onDelete)
257257
}
258-
abstract member ConstraintName : TObjectName * Name -> Name
259-
default __.ConstraintName(_, name) = name
260258
abstract member IndexedColumn : name : Name * dir : OrderDirection -> Fragments
261259
default this.IndexedColumn(name, dir) =
262260
seq {
@@ -274,11 +272,11 @@ type DefaultStatementTranslator(expectedVendorName : Name, indexer : IParameterI
274272
yield ws
275273
yield text "AUTOINCREMENT"
276274
}
277-
override this.ColumnConstraint(table, constr) =
275+
override this.ColumnConstraint(_, constr) =
278276
seq {
279277
yield text "CONSTRAINT"
280278
yield ws
281-
yield this.Expr.Name(this.ConstraintName(table, constr.Name))
279+
yield this.Expr.Name(constr.Name)
282280
yield ws
283281
match constr.ColumnConstraintType with
284282
| PrimaryKeyConstraint pk ->
@@ -288,11 +286,11 @@ type DefaultStatementTranslator(expectedVendorName : Name, indexer : IParameterI
288286
| ForeignKeyConstraint fk ->
289287
yield! this.ForeignKeyClause(fk)
290288
}
291-
override this.TableConstraint(table, constr) =
289+
override this.TableConstraint(_, constr) =
292290
seq {
293291
yield text "CONSTRAINT"
294292
yield ws
295-
yield this.Expr.Name(this.ConstraintName(table, constr.Name))
293+
yield this.Expr.Name(constr.Name)
296294
yield ws
297295
match constr.TableConstraintType with
298296
| TableIndexConstraint indexClause ->

‎src/Rezoom.SQL.Compiler/Postgres.fs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -148,7 +148,7 @@ type private PostgresStatement(indexer : IParameterIndexer) as this =
148148
| DropConstraint constr ->
149149
yield text "DROP CONSTRAINT"
150150
yield ws
151-
yield this.Expr.Name(this.ConstraintName(alter.Table, constr))
151+
yield this.Expr.Name(constr)
152152
yield ws
153153
yield text "RESTRICT"
154154
| DropDefault col ->

‎src/Rezoom.SQL.Compiler/SourceTypes.fs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -133,6 +133,7 @@ type SourceException(msg : string, pos : SourceInfo, source, fileName) =
133133

134134
[<AutoOpen>]
135135
module SourceInfoModule =
136+
let applySource src x = { Source = src.Source; Value = src.Value x }
136137
let atSource src x = { Source = src; Value = x }
137138
let nearSourceOf (ws : _ WithSource) x = x |> atSource ws.Source
138139
let artificialSource x = atSource SourceInfo.Invalid x

‎src/Rezoom.SQL.Compiler/TSQL.Statement.fs‎

Lines changed: 1 addition & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -120,9 +120,6 @@ type private TSQLStatement(indexer : IParameterIndexer) as this =
120120
yield ws
121121
yield text "ROWS ONLY"
122122
}
123-
override this.ConstraintName(table, constr) =
124-
// constraint names must be unique across the db, so qualify w/ table name
125-
table.ObjectName + "_" + constr
126123
override this.PrimaryKeyClause(pk) =
127124
seq {
128125
yield text "PRIMARY KEY"
@@ -261,7 +258,7 @@ type private TSQLStatement(indexer : IParameterIndexer) as this =
261258
| DropConstraint constr ->
262259
yield text "DROP CONSTRAINT"
263260
yield ws
264-
yield this.Expr.Name(this.ConstraintName(alter.Table, constr))
261+
yield this.Expr.Name(constr)
265262
| DropDefault col ->
266263
yield text "DROP CONSTRAINT"
267264
yield ws

‎src/Rezoom.SQL.Test/TestPostgres.fs‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -44,29 +44,29 @@ create index IX_ArticleComments_AuthorId on ArticleComments(AuthorId);
4444
OutputCommand =
4545
"""
4646
CREATE TABLE "pictures"
47-
( "sha256" BYTEA NOT NULL CONSTRAINT "sha256_pk" PRIMARY KEY
47+
( "sha256" BYTEA NOT NULL CONSTRAINT "pictures_sha256_pk" PRIMARY KEY
4848
, "pngdata" BYTEA NOT NULL
4949
);
5050
CREATE TABLE "users"
51-
( "id" BIGSERIAL NOT NULL CONSTRAINT "id_pk" PRIMARY KEY
51+
( "id" BIGSERIAL NOT NULL CONSTRAINT "users_id_pk" PRIMARY KEY
5252
, "name" VARCHAR(80) NOT NULL
5353
, "email" VARCHAR(254) NOT NULL
54-
, "profilepicturesha256" BYTEA CONSTRAINT "profilepicturesha256_fk_pictures_sha256"
54+
, "profilepicturesha256" BYTEA CONSTRAINT "users_profilepicturesha256_fk_pictures_sha256"
5555
REFERENCES "pictures" ("sha256")
5656
, "created" TIMESTAMPTZ NOT NULL
5757
, "randomid" UUID NOT NULL DEFAULT (CAST('a8078caeae944136ade0f2bf06792a92' AS UUID))
5858
);
5959
CREATE TABLE "articles"
60-
( "id" BIGSERIAL NOT NULL CONSTRAINT "id_pk" PRIMARY KEY
61-
, "authorid" BIGINT NOT NULL CONSTRAINT "authorid_fk_users_id" REFERENCES "users" ("id")
60+
( "id" BIGSERIAL NOT NULL CONSTRAINT "articles_id_pk" PRIMARY KEY
61+
, "authorid" BIGINT NOT NULL CONSTRAINT "articles_authorid_fk_users_id" REFERENCES "users" ("id")
6262
, "articletitle" VARCHAR(80) NOT NULL
6363
, "articletext" VARCHAR(4096) NOT NULL
6464
);
6565
CREATE INDEX "ix_articles_authorid" ON "articles" ( "authorid" ASC );
6666
CREATE TABLE "articlecomments"
67-
( "id" BIGSERIAL NOT NULL CONSTRAINT "id_pk" PRIMARY KEY
68-
, "articleid" BIGINT NOT NULL CONSTRAINT "articleid_fk_articles_id" REFERENCES "articles" ("id")
69-
, "authorid" BIGINT NOT NULL CONSTRAINT "authorid_fk_users_id" REFERENCES "users" ("id")
67+
( "id" BIGSERIAL NOT NULL CONSTRAINT "articlecomments_id_pk" PRIMARY KEY
68+
, "articleid" BIGINT NOT NULL CONSTRAINT "articlecomments_articleid_fk_articles_id" REFERENCES "articles" ("id")
69+
, "authorid" BIGINT NOT NULL CONSTRAINT "articlecomments_authorid_fk_users_id" REFERENCES "users" ("id")
7070
, "commenttext" VARCHAR(512) NOT NULL
7171
);
7272
CREATE INDEX "ix_articlecomments_authorid" ON "articlecomments" ( "authorid" ASC );

‎src/Rezoom.SQL.Test/TestRoundTrip.fs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -282,7 +282,7 @@ let ``alter add constraint with CONSTRAINT keyword`` () =
282282
let ``alter add/drop check constraint with expected default name`` () =
283283
roundtrip """
284284
alter table Users add check(Name <> '');
285-
alter table Users drop constraint check;
285+
alter table Users drop constraint Users_check;
286286
"""
287287

288288
[<Test>]

‎src/Rezoom.SQL.Test/TestSQLite.fs‎

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -103,27 +103,27 @@ create index IX_ArticleComments_AuthorId on ArticleComments(AuthorId);
103103
OutputCommand =
104104
"""
105105
CREATE TABLE "Pictures"
106-
( "SHA256" BLOB NOT NULL CONSTRAINT "SHA256_PK" PRIMARY KEY ASC
106+
( "SHA256" BLOB NOT NULL CONSTRAINT "Pictures_SHA256_PK" PRIMARY KEY ASC
107107
, "PNGData" BLOB NOT NULL
108108
);
109109
CREATE TABLE "Users"
110-
( "Id" INTEGER NOT NULL CONSTRAINT "Id_PK" PRIMARY KEY ASC AUTOINCREMENT
110+
( "Id" INTEGER NOT NULL CONSTRAINT "Users_Id_PK" PRIMARY KEY ASC AUTOINCREMENT
111111
, "Name" VARCHAR NOT NULL , "Email" VARCHAR NOT NULL
112-
, "ProfilePictureSHA256" BLOB CONSTRAINT "ProfilePictureSHA256_FK_Pictures_SHA256" REFERENCES "Pictures" ("SHA256")
112+
, "ProfilePictureSHA256" BLOB CONSTRAINT "Users_ProfilePictureSHA256_FK_Pictures_SHA256" REFERENCES "Pictures" ("SHA256")
113113
, "Created" VARCHAR NOT NULL
114114
, "RandomId" BLOB NOT NULL DEFAULT (CAST(randomblob(16) AS BLOB))
115115
);
116116
CREATE TABLE "Articles"
117-
( "Id" INTEGER NOT NULL CONSTRAINT "Id_PK" PRIMARY KEY ASC AUTOINCREMENT
118-
, "AuthorId" INT NOT NULL CONSTRAINT "AuthorId_FK_Users_Id" REFERENCES "Users" ("Id")
117+
( "Id" INTEGER NOT NULL CONSTRAINT "Articles_Id_PK" PRIMARY KEY ASC AUTOINCREMENT
118+
, "AuthorId" INT NOT NULL CONSTRAINT "Articles_AuthorId_FK_Users_Id" REFERENCES "Users" ("Id")
119119
, "ArticleTitle" VARCHAR NOT NULL
120120
, "ArticleText" VARCHAR NOT NULL
121121
);
122122
CREATE INDEX "IX_Articles_AuthorId" ON "Articles" ( "AuthorId" ASC );
123123
CREATE TABLE "ArticleComments"
124-
( "Id" INTEGER NOT NULL CONSTRAINT "Id_PK" PRIMARY KEY ASC AUTOINCREMENT
125-
, "ArticleId" INT NOT NULL CONSTRAINT "ArticleId_FK_Articles_Id" REFERENCES "Articles" ("Id")
126-
, "AuthorId" INT NOT NULL CONSTRAINT "AuthorId_FK_Users_Id" REFERENCES "Users" ("Id")
124+
( "Id" INTEGER NOT NULL CONSTRAINT "ArticleComments_Id_PK" PRIMARY KEY ASC AUTOINCREMENT
125+
, "ArticleId" INT NOT NULL CONSTRAINT "ArticleComments_ArticleId_FK_Articles_Id" REFERENCES "Articles" ("Id")
126+
, "AuthorId" INT NOT NULL CONSTRAINT "ArticleComments_AuthorId_FK_Users_Id" REFERENCES "Users" ("Id")
127127
, "CommentText" VARCHAR NOT NULL
128128
);
129129
CREATE INDEX "IX_ArticleComments_AuthorId" ON "ArticleComments" ( "AuthorId" ASC );

‎src/Rezoom.SQL.Test/TestTSQL.fs‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -130,7 +130,7 @@ let ``alter table scenarios`` () =
130130
"""
131131
create table Foo(x int primary key, y int);
132132
alter table Foo add default for y 1;
133-
alter table Foo drop constraint x_PK;
133+
alter table Foo drop constraint Foo_x_PK;
134134
alter table Foo add constraint namedpk primary key(x, y);
135135
alter table Foo drop constraint namedpk;
136136
alter table Foo drop default for y;
@@ -143,8 +143,8 @@ alter table Foo add column z string(80) null collate SQL_Latin1_General_CP1_CI_A
143143
CREATE TABLE [Foo] ( [x] INT NOT NULL CONSTRAINT [Foo_x_PK] PRIMARY KEY , [y] INT NOT NULL );
144144
ALTER TABLE [Foo] ADD CONSTRAINT [Foo_y_DEFAULT_CONSTRAINT] DEFAULT 1 FOR [y];
145145
ALTER TABLE [Foo] DROP CONSTRAINT [Foo_x_PK];
146-
ALTER TABLE [Foo] ADD CONSTRAINT [Foo_namedpk] PRIMARY KEY([x] ASC,[y] ASC);
147-
ALTER TABLE [Foo] DROP CONSTRAINT [Foo_namedpk];
146+
ALTER TABLE [Foo] ADD CONSTRAINT [namedpk] PRIMARY KEY([x] ASC,[y] ASC);
147+
ALTER TABLE [Foo] DROP CONSTRAINT [namedpk];
148148
ALTER TABLE [Foo] DROP CONSTRAINT [Foo_y_DEFAULT_CONSTRAINT];
149149
ALTER TABLE [Foo] ALTER COLUMN [y] NVARCHAR(12) NOT NULL;
150150
ALTER TABLE [Foo] ALTER COLUMN [y] NVARCHAR(12) NULL;

0 commit comments

Comments
 (0)