search

glerchundi / sqlboiler-crdb

23 stars 20 forks source link

fix foreign key unique constraint bug #7

Closed stephenafamo closed 6 years ago

stephenafamo commented 6 years ago

All foreign keys were being marked as unique columns which broke the code generation for relationships.

This should fix it.

glerchundi commented 6 years ago

Thanks for contributing.

Can you provide a simplified example schema which illustrates the problem and the generated code before and after applying this patch so that I can quickly review this tomorrow?

On Sun, 19 Aug 2018 at 19:48, Stephen Afam-Osemene notifications@github.com wrote:

All foreign keys were being marked as unique columns which broke the code generation for relationships.

This should fix it.

You can view, comment on, or merge this pull request online at:

https://github.com/glerchundi/sqlboiler-crdb/pull/7 Commit Summary

  • fix foreign key unique constraint bug

File Changes

Patch Links:

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/glerchundi/sqlboiler-crdb/pull/7, or mute the thread https://github.com/notifications/unsubscribe-auth/ACIPllCFoBLZy3gD07HskTGThc_LKnDxks5uSaTagaJpZM4WDDb_ .

stephenafamo commented 6 years ago

sample schema

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING NULL
    FAMILY "primary" (id, email)
);

CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NULL REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE,
    content STRING NULL,
    FAMILY "primary" (id, user_id, content)
);

In the posts table, the user_id column is not supposed to be unique, but using the previous query in the driver:

select
distinct c.column_name,
c.data_type,
c.column_default,
(case when c.is_nullable = 'NO' then FALSE else TRUE end) as is_nullable,
(case when kcu.constraint_name is not null then TRUE else False END) as is_unique
from information_schema.columns as c
left join information_schema.key_column_usage kcu on c.table_name = kcu.table_name
and c.table_schema = kcu.table_schema and c.column_name = kcu.column_name
where c.table_schema = 'public' and c.table_name = 'posts';

You'd get this:

+-------------+-----------+-------------------+-------------+-----------+
| column_name | data_type |  column_default   | is_nullable | is_unique |
+-------------+-----------+-------------------+-------------+-----------+
| id          | UUID      | gen_random_uuid() |    false    |   true    |
| user_id     | UUID      | NULL              |    true     |   true    |
| content     | STRING    | NULL              |    true     |   false   |
+-------------+-----------+-------------------+-------------+-----------+

Now, if you run this command instead (my fix):

select
distinct c.column_name,
c.data_type,
c.column_default,
(case when c.is_nullable = 'NO' then FALSE else TRUE end) as is_nullable,
(case when pgc.contype IS NOT NULL AND pgc.contype IN ('p', 'u') then TRUE else FALSE end) as is_unique
from information_schema.columns as c
left join information_schema.key_column_usage kcu on c.table_name = kcu.table_name
and c.table_schema = kcu.table_schema and c.column_name = kcu.column_name
left join pg_constraint pgc on kcu.constraint_name = pgc.conname
where c.table_schema = 'public' and c.table_name = 'posts';

You'd get this:

+-------------+-----------+-------------------+-------------+-----------+
| column_name | data_type |  column_default   | is_nullable | is_unique |
+-------------+-----------+-------------------+-------------+-----------+
| id          | UUID      | gen_random_uuid() |    false    |   true    |
| user_id     | UUID      | NULL              |    true     |   false   |
| content     | STRING    | NULL              |    true     |   false   |
+-------------+-----------+-------------------+-------------+-----------+
glerchundi commented 6 years ago

Posting generated code differences, thanks for catching this up.

users.go diff

```diff 43c43 < Post string --- > Posts string 45c45 < Post: "Post", --- > Posts: "Posts", 50c50 < Post *Post --- > Posts PostSlice 155,158c155,159 < // Post pointed to by the foreign key. < func (o *User) Post(mods ...qm.QueryMod) postQuery { < queryMods := []qm.QueryMod{ < qm.Where("user_id=?", o.ID), --- > // Posts retrieves all the post's Posts with an executor. > func (o *User) Posts(mods ...qm.QueryMod) postQuery { > var queryMods []qm.QueryMod > if len(mods) != 0 { > queryMods = append(queryMods, mods...) 161c162,164 < queryMods = append(queryMods, mods...) --- > queryMods = append(queryMods, > qm.Where("\"posts\".\"user_id\"=?", o.ID), > ) 165a169,172 > if len(queries.GetSelect(query.Query)) == 0 { > queries.SetSelect(query.Query, []string{"\"posts\".*"}) > } > 169,171c176,178 < // LoadPost allows an eager lookup of values, cached into the < // loaded structs of the objects. This is for a 1-1 relationship. < func (userL) LoadPost(ctx context.Context, e boil.ContextExecutor, singular bool, maybeUser interface{}, mods queries.Applicator) error { --- > // LoadPosts allows an eager lookup of values, cached into the > // loaded structs of the objects. This is for a 1-M or N-M relationship. > func (userL) LoadPosts(ctx context.Context, e boil.ContextExecutor, singular bool, maybeUser interface{}, mods queries.Applicator) error { 211c218 < return errors.Wrap(err, "failed to eager load Post") --- > return errors.Wrap(err, "failed to eager load posts") 216c223 < return errors.Wrap(err, "failed to bind eager loaded slice Post") --- > return errors.Wrap(err, "failed to bind eager loaded slice posts") 220c227 < return errors.Wrap(err, "failed to close results of eager load for posts") --- > return errors.Wrap(err, "failed to close results in eager load on posts") 226,229d232 < if len(resultSlice) == 0 { < return nil < } < 231,234c234,239 < foreign := resultSlice[0] < object.R.Post = foreign < if foreign.R == nil { < foreign.R = &postR{} --- > object.R.Posts = resultSlice > for _, foreign := range resultSlice { > if foreign.R == nil { > foreign.R = &postR{} > } > foreign.R.User = object 236c241 < foreign.R.User = object --- > return nil 239,240c244,245 < for _, local := range slice { < for _, foreign := range resultSlice { --- > for _, foreign := range resultSlice { > for _, local := range slice { 242c247 < local.R.Post = foreign --- > local.R.Posts = append(local.R.Posts, foreign) 255,258c260,264 < // SetPost of the user to the related item. < // Sets o.R.Post to related. < // Adds o to related.R.User. < func (o *User) SetPost(ctx context.Context, exec boil.ContextExecutor, insert bool, related *Post) error { --- > // AddPosts adds the given related objects to the existing relationships > // of the user, optionally inserting them as new records. > // Appends related to o.R.Posts. > // Sets related.R.User appropriately. > func (o *User) AddPosts(ctx context.Context, exec boil.ContextExecutor, insert bool, related ...*Post) error { 259a266,283 > for _, rel := range related { > if insert { > queries.Assign(&rel.UserID, o.ID) > if err = rel.Insert(ctx, exec, boil.Infer()); err != nil { > return errors.Wrap(err, "failed to insert into foreign table") > } > } else { > updateQuery := fmt.Sprintf( > "UPDATE \"posts\" SET %s WHERE %s", > strmangle.SetParamNames("\"", "\"", 1, []string{"user_id"}), > strmangle.WhereClause("\"", "\"", 2, postPrimaryKeyColumns), > ) > values := []interface{}{o.ID, rel.ID} > > if boil.DebugMode { > fmt.Fprintln(boil.DebugWriter, updateQuery) > fmt.Fprintln(boil.DebugWriter, values) > } 261,278c285,287 < if insert { < queries.Assign(&related.UserID, o.ID) < < if err = related.Insert(ctx, exec, boil.Infer()); err != nil { < return errors.Wrap(err, "failed to insert into foreign table") < } < } else { < updateQuery := fmt.Sprintf( < "UPDATE \"posts\" SET %s WHERE %s", < strmangle.SetParamNames("\"", "\"", 1, []string{"user_id"}), < strmangle.WhereClause("\"", "\"", 2, postPrimaryKeyColumns), < ) < values := []interface{}{o.ID, related.ID} < < if boil.DebugMode { < fmt.Fprintln(boil.DebugWriter, updateQuery) < fmt.Fprintln(boil.DebugWriter, values) < } --- > if _, err = exec.ExecContext(ctx, updateQuery, values...); err != nil { > return errors.Wrap(err, "failed to update foreign table") > } 280,281c289 < if _, err = exec.ExecContext(ctx, updateQuery, values...); err != nil { < return errors.Wrap(err, "failed to update foreign table") --- > queries.Assign(&rel.UserID, o.ID) 283,284d290 < < queries.Assign(&related.UserID, o.ID) 289c295 < Post: related, --- > Posts: related, 292c298 < o.R.Post = related --- > o.R.Posts = append(o.R.Posts, related...) 295,297c301,307 < if related.R == nil { < related.R = &postR{ < User: o, --- > for _, rel := range related { > if rel.R == nil { > rel.R = &postR{ > User: o, > } > } else { > rel.R.User = o 299,300d308 < } else { < related.R.User = o 305,309c313,337 < // RemovePost relationship. < // Sets o.R.Post to nil. < // Removes o from all passed in related items' relationships struct (Optional). < func (o *User) RemovePost(ctx context.Context, exec boil.ContextExecutor, related *Post) error { < var err error --- > // SetPosts removes all previously related items of the > // user replacing them completely with the passed > // in related items, optionally inserting them as new records. > // Sets o.R.User's Posts accordingly. > // Replaces o.R.Posts with related. > // Sets related.R.User's Posts accordingly. > func (o *User) SetPosts(ctx context.Context, exec boil.ContextExecutor, insert bool, related ...*Post) error { > query := "update \"posts\" set \"user_id\" = null where \"user_id\" = $1" > values := []interface{}{o.ID} > if boil.DebugMode { > fmt.Fprintln(boil.DebugWriter, query) > fmt.Fprintln(boil.DebugWriter, values) > } > > _, err := exec.ExecContext(ctx, query, values...) > if err != nil { > return errors.Wrap(err, "failed to remove relationships before set") > } > > if o.R != nil { > for _, rel := range o.R.Posts { > queries.SetScanner(&rel.UserID, nil) > if rel.R == nil { > continue > } 311,313c339,342 < queries.SetScanner(&related.UserID, nil) < if err = related.Update(ctx, exec, boil.Whitelist("user_id")); err != nil { < return errors.Wrap(err, "failed to update local table") --- > rel.R.User = nil > } > > o.R.Posts = nil 314a344,345 > return o.AddPosts(ctx, exec, insert, related...) > } 316,317c347,361 < o.R.Post = nil < if related == nil || related.R == nil { --- > // RemovePosts relationships from objects passed in. > // Removes related items from R.Posts (uses pointer comparison, removal does not keep order) > // Sets related.R.User. > func (o *User) RemovePosts(ctx context.Context, exec boil.ContextExecutor, related ...*Post) error { > var err error > for _, rel := range related { > queries.SetScanner(&rel.UserID, nil) > if rel.R != nil { > rel.R.User = nil > } > if err = rel.Update(ctx, exec, boil.Whitelist("user_id")); err != nil { > return err > } > } > if o.R == nil { 321c365,379 < related.R.User = nil --- > for _, rel := range related { > for i, ri := range o.R.Posts { > if rel != ri { > continue > } > > ln := len(o.R.Posts) > if ln > 1 && i < ln-1 { > o.R.Posts[i] = o.R.Posts[ln-1] > } > o.R.Posts = o.R.Posts[:ln-1] > break > } > } > ```

posts.go diff

```diff 239c239 < foreign.R.Post = object --- > foreign.R.Posts = append(foreign.R.Posts, object) 250c250 < foreign.R.Post = local --- > foreign.R.Posts = append(foreign.R.Posts, local) 261c261 < // Adds o to related.R.Post. --- > // Adds o to related.R.Posts. 297c297 < Post: o, --- > Posts: PostSlice{o}, 300c300 < related.R.Post = o --- > related.R.Posts = append(related.R.Posts, o) 322c322,333 < related.R.Post = nil --- > for i, ri := range related.R.Posts { > if queries.Equal(o.UserID, ri.UserID) { > continue > } > > ln := len(related.R.Posts) > if ln > 1 && i < ln-1 { > related.R.Posts[i] = related.R.Posts[ln-1] > } > related.R.Posts = related.R.Posts[:ln-1] > break > } ```