psql: code generation: INHERITS support

[mdouchement]

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]

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

[gnvk]

@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]

I would review a PR 🤷🏾