psql: code generation: INHERITS support

mdouchement commented 5 years ago

What version of SQLBoiler are you using?

SQLBoiler v3.0.1

SQLBoiler command you used to generate your models?

$ sqlboiler --wipe --no-tests --no-context --add-global-variants psql
Error: unable to initialize tables: primary key missing in tables (turtles)

What is the output of the command above with the `-d` flag added to it?

sqlboiler --wipe --no-tests --no-context --add-global-variants -d psql

``` using driver: /Users/mdouchement/workspaces/golang/bin/sqlboiler-psql {"config":{"driver_name":"psql","driver_config":{"blacklist":null,"dbname":"test42","host":"localhost","pass":"postgres","port":5432,"sslmode":"disable","user":"postgres","whitelist":null},"pkg_name":"models","out_folder":"models","debug":true,"add_global":true,"no_context":true,"no_tests":true,"wipe":true,"struct_tag_casing":"snake","imports":{"all":{"Standard":["\"database/sql\"","\"fmt\"","\"reflect\"","\"strings\"","\"sync\"","\"time\""],"ThirdParty":["\"github.com/pkg/errors\"","\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/queries/qm\"","\"github.com/volatiletech/sqlboiler/strmangle\""]},"test":{"Standard":["\"bytes\"","\"reflect\"","\"testing\""],"ThirdParty":["\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/randomize\"","\"github.com/volatiletech/sqlboiler/strmangle\""]},"singleton":{"boil_queries":{"Standard":null,"ThirdParty":["\"github.com/volatiletech/sqlboiler/drivers\"","\"github.com/volatiletech/sqlboiler/queries\"","\"github.com/volatiletech/sqlboiler/queries/qm\""]},"boil_types":{"Standard":["\"strconv\""],"ThirdParty":["\"github.com/pkg/errors\"","\"github.com/volatiletech/sqlboiler/boil\"","\"github.com/volatiletech/sqlboiler/strmangle\""]}},"test_singleton":{"boil_main_test":{"Standard":["\"database/sql\"","\"flag\"","\"fmt\"","\"math/rand\"","\"os\"","\"path/filepath\"","\"strings\"","\"testing\"","\"time\""],"ThirdParty":["\"github.com/spf13/viper\"","\"github.com/volatiletech/sqlboiler/boil\""]},"boil_queries_test":{"Standard":["\"bytes\"","\"fmt\"","\"io\"","\"io/ioutil\"","\"math/rand\"","\"regexp\""],"ThirdParty":["\"github.com/volatiletech/sqlboiler/boil\""]},"boil_suites_test":{"Standard":["\"testing\""],"ThirdParty":null}}},"aliases":{}},"driver_config":{"blacklist":null,"dbname":"test42","host":"localhost","pass":"postgres","port":5432,"sslmode":"disable","user":"postgres","whitelist":null},"schema":"","dialect":{"lq":0,"rq":0,"use_index_placeholders":false,"use_last_insert_id":false,"use_schema":false,"use_default_keyword":false,"use_auto_columns":false,"use_top_clause":false,"use_output_clause":false,"use_case_when_exists_clause":false},"tables":null,"templates":null} Error: primary key missing in tables (turtles) github.com/volatiletech/sqlboiler/boilingcore.checkPKeys /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/boilingcore/boilingcore.go:566 github.com/volatiletech/sqlboiler/boilingcore.(*State).initDBInfo /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/boilingcore/boilingcore.go:367 github.com/volatiletech/sqlboiler/boilingcore.New /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/boilingcore/boilingcore.go:85 main.preRun /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/main.go:202 github.com/spf13/cobra.(*Command).execute /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:751 github.com/spf13/cobra.(*Command).ExecuteC /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:852 github.com/spf13/cobra.(*Command).Execute /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:800 main.main /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/main.go:117 runtime.main /Users/mdouchement/.gvm/gos/go1.10.3/src/runtime/proc.go:198 runtime.goexit /Users/mdouchement/.gvm/gos/go1.10.3/src/runtime/asm_amd64.s:2361 unable to initialize tables github.com/volatiletech/sqlboiler/boilingcore.New /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/boilingcore/boilingcore.go:87 main.preRun /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/main.go:202 github.com/spf13/cobra.(*Command).execute /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:751 github.com/spf13/cobra.(*Command).ExecuteC /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:852 github.com/spf13/cobra.(*Command).Execute /Users/mdouchement/workspaces/golang/src/github.com/spf13/cobra/command.go:800 main.main /Users/mdouchement/workspaces/golang/src/github.com/volatiletech/sqlboiler/main.go:117 runtime.main /Users/mdouchement/.gvm/gos/go1.10.3/src/runtime/proc.go:198 runtime.goexit /Users/mdouchement/.gvm/gos/go1.10.3/src/runtime/asm_amd64.s:2361 ```

Relevant database schema

CREATE DATABASE test42 WITH ENCODING = 'utf8';

CREATE TABLE animals (
  id BIGSERIAL PRIMARY KEY,
  name TEXT
);

CREATE TABLE turtles (
  subname TEXT
) INHERITS (animals);

Further information. What did you do, what did you expect?

Support Postgres' table inheritance.

aarondl commented 5 years ago

I would accept a PR for this but I won't be spending the effort to implement, sorry!

gnvk commented 1 year ago

@mdouchement The primary key constraint is not inherited! With your schema, you can run

INSERT INTO turtles VALUES (1, 'x', 'y');

any number of times, it will always create a new record. You need to add a unique / primary key constraint to the id in the turtles table.

All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited. https://www.postgresql.org/docs/current/ddl-inherit.html

If you add the primary key to the child table, sqlboiler works:

CREATE TABLE turtles (
  subname TEXT,
  CONSTRAINT turtles_pkey PRIMARY KEY (id)
) INHERITS (animals);

However, the generated code is suboptimal. It generates the model of the child table as if it was not inherited:

type Animal struct {
    ID   int64       `boil:"id" json:"id" toml:"id" yaml:"id"`
    Name null.String `boil:"name" json:"name,omitempty" toml:"name" yaml:"name,omitempty"`

    R *animalR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L animalL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

type Turtle struct {
    ID      int64       `boil:"id" json:"id" toml:"id" yaml:"id"`
    Name    null.String `boil:"name" json:"name,omitempty" toml:"name" yaml:"name,omitempty"`
    Subname null.String `boil:"subname" json:"subname,omitempty" toml:"subname" yaml:"subname,omitempty"`

    R *turtleR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L turtleL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

I would love to see (at least an option) to generate this instead:

type Turtle struct {
    Animal
    Subname null.String `boil:"subname" json:"subname,omitempty" toml:"subname" yaml:"subname,omitempty"`

    R *turtleR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L turtleL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

@aarondl Is there any change for this enhancement to be added?

stephenafamo commented 1 year ago

I would review a PR 🤷🏾

volatiletech / sqlboiler