search

glerchundi / sqlboiler-crdb

23 stars 20 forks source link

Fix several bugs that when getting columns info #8

Closed stephenafamo closed 6 years ago

stephenafamo commented 6 years ago

After my previous PR, I noticed some extra edge cases that were not working as expected. This PR should fix them.

  1. When a column has multiple constraints, it is listed multiple times (fixed)
  2. With a composite unique or PK index, it will mark both columns as unique (fixed)

EXAMPLE 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)
);

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

Running the previous query for the comments table:

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 = 'comments';

Gives:

+-------------+-----------+----------------+-------------+-----------+
| column_name | data_type | column_default | is_nullable | is_unique |
+-------------+-----------+----------------+-------------+-----------+
| user_id     | UUID      | NULL           |    false    |   true    |
| user_id     | UUID      | NULL           |    false    |   false   |
| post_id     | UUID      | NULL           |    false    |   true    |
| post_id     | UUID      | NULL           |    false    |   false   |
| content     | STRING    | NULL           |    true     |   false   |
+-------------+-----------+----------------+-------------+-----------+

The modified query:

select
distinct c.column_name,
max(c.data_type) as data_type,
max(c.column_default) as column_default,
bool_or(case when c.is_nullable = 'NO' then FALSE else TRUE end) as is_nullable,
bool_or(case when pc.count < 2 AND pgc.contype IN ('p', 'u') then TRUE else FALSE end) as is_unique
from information_schema.columns as c
LEFT JOIN (
    select distinct c.column_name,
    pgc.conname as conname,
    pgc.contype as contype
    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
) pgc on c.column_name = pgc.column_name
LEFT JOIN (
    select kcu.table_schema, kcu.table_name, kcu.constraint_name, count(*)
    from information_schema.key_column_usage kcu
    group by kcu.table_schema, kcu.table_name, kcu.constraint_name
) pc on c.table_schema = pc.table_schema and c.table_name = pc.table_name and pgc.conname = pc.constraint_name
where c.table_schema = 'public' and c.table_name = 'comments'
group by c.column_name;

Gives:

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

LGTM, thanks.

Posting generated code before and after diff's:

posts.go

```diff 46,47c46,47 < User string < Comments string --- > User string > Comment string 49,50c49,50 < User: "User", < Comments: "Comments", --- > User: "User", > Comment: "Comment", 55,56c55,56 < User *User < Comments CommentSlice --- > User *User > Comment *Comment 175,179c175,178 < // Comments retrieves all the comment's Comments with an executor. < func (o *Post) Comments(mods ...qm.QueryMod) commentQuery { < var queryMods []qm.QueryMod < if len(mods) != 0 { < queryMods = append(queryMods, mods...) --- > // Comment pointed to by the foreign key. > func (o *Post) Comment(mods ...qm.QueryMod) commentQuery { > queryMods := []qm.QueryMod{ > qm.Where("post_id=?", o.ID), 182,184c181 < queryMods = append(queryMods, < qm.Where("\"comments\".\"post_id\"=?", o.ID), < ) --- > queryMods = append(queryMods, mods...) 189,192d185 < if len(queries.GetSelect(query.Query)) == 0 { < queries.SetSelect(query.Query, []string{"\"comments\".*"}) < } < 283,285c276,278 < // LoadComments 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 (postL) LoadComments(ctx context.Context, e boil.ContextExecutor, singular bool, maybePost interface{}, mods queries.Applicator) error { --- > // LoadComment allows an eager lookup of values, cached into the > // loaded structs of the objects. This is for a 1-1 relationship. > func (postL) LoadComment(ctx context.Context, e boil.ContextExecutor, singular bool, maybePost interface{}, mods queries.Applicator) error { 325c318 < return errors.Wrap(err, "failed to eager load comments") --- > return errors.Wrap(err, "failed to eager load Comment") 330c323 < return errors.Wrap(err, "failed to bind eager loaded slice comments") --- > return errors.Wrap(err, "failed to bind eager loaded slice Comment") 334c327 < return errors.Wrap(err, "failed to close results in eager load on comments") --- > return errors.Wrap(err, "failed to close results of eager load for comments") 339a333,336 > if len(resultSlice) == 0 { > return nil > } > 341,346c338,341 < object.R.Comments = resultSlice < for _, foreign := range resultSlice { < if foreign.R == nil { < foreign.R = &commentR{} < } < foreign.R.Post = object --- > foreign := resultSlice[0] > object.R.Comment = foreign > if foreign.R == nil { > foreign.R = &commentR{} 348c343 < return nil --- > foreign.R.Post = object 351,352c346,347 < for _, foreign := range resultSlice { < for _, local := range slice { --- > for _, local := range slice { > for _, foreign := range resultSlice { 354c349 < local.R.Comments = append(local.R.Comments, foreign) --- > local.R.Comment = foreign 445,449c440,443 < // AddComments adds the given related objects to the existing relationships < // of the post, optionally inserting them as new records. < // Appends related to o.R.Comments. < // Sets related.R.Post appropriately. < func (o *Post) AddComments(ctx context.Context, exec boil.ContextExecutor, insert bool, related ...*Comment) error { --- > // SetComment of the post to the related item. > // Sets o.R.Comment to related. > // Adds o to related.R.Post. > func (o *Post) SetComment(ctx context.Context, exec boil.ContextExecutor, insert bool, related *Comment) error { 451,468d444 < for _, rel := range related { < if insert { < rel.PostID = 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 \"comments\" SET %s WHERE %s", < strmangle.SetParamNames("\"", "\"", 1, []string{"post_id"}), < strmangle.WhereClause("\"", "\"", 2, commentPrimaryKeyColumns), < ) < values := []interface{}{o.ID, rel.UserID, rel.PostID} < < if boil.DebugMode { < fmt.Fprintln(boil.DebugWriter, updateQuery) < fmt.Fprintln(boil.DebugWriter, values) < } 470,472c446,458 < if _, err = exec.ExecContext(ctx, updateQuery, values...); err != nil { < return errors.Wrap(err, "failed to update foreign table") < } --- > if insert { > related.PostID = 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 \"comments\" SET %s WHERE %s", > strmangle.SetParamNames("\"", "\"", 1, []string{"post_id"}), > strmangle.WhereClause("\"", "\"", 2, commentPrimaryKeyColumns), > ) > values := []interface{}{o.ID, related.UserID, related.PostID} 474c460,462 < rel.PostID = o.ID --- > if boil.DebugMode { > fmt.Fprintln(boil.DebugWriter, updateQuery) > fmt.Fprintln(boil.DebugWriter, values) 475a464,470 > > if _, err = exec.ExecContext(ctx, updateQuery, values...); err != nil { > return errors.Wrap(err, "failed to update foreign table") > } > > related.PostID = o.ID > 480c475 < Comments: related, --- > Comment: related, 483c478 < o.R.Comments = append(o.R.Comments, related...) --- > o.R.Comment = related 486,492c481,483 < for _, rel := range related { < if rel.R == nil { < rel.R = &commentR{ < Post: o, < } < } else { < rel.R.Post = o --- > if related.R == nil { > related.R = &commentR{ > Post: o, 493a485,486 > } else { > related.R.Post = o ```

comments.go

```diff 349c349 < foreign.R.Comments = append(foreign.R.Comments, object) --- > foreign.R.Comment = object 360c360 < foreign.R.Comments = append(foreign.R.Comments, local) --- > foreign.R.Comment = local 418c418 < // Adds o to related.R.Comments. --- > // Adds o to related.R.Comment. 454c454 < Comments: CommentSlice{o}, --- > Comment: o, 457c457 < related.R.Comments = append(related.R.Comments, o) --- > related.R.Comment = o ```