Foreign key support

[natefinch]

This one needs a little design work before implementation. Thoughts on the best way to represent foreign keys between tables? Probably metadata on a column... but maybe also metadata on a table? How to represent it in the most usable way?

[joeblew99]

Gen to an agnostic format like json and then run generator. This decoupling allows people to get it the middle and so the schema of the json becomes less critical.

It gives others room to move.

Also boltdb would be super btw Bolthold has a nice abstraction layer for it to make it feel more dbish :)

[natefinch]

So, the agnostic format is gnorm/database.Info. That could easily be rendered out to JSON or whatever (in fact, gnorm preview has a --yaml mode.... I and intend to add json to the output modes, which would do what you want). It's less powerful than the database.Info value, because that can have recursive data types and specialized functions etc.... but it is easy enough to print out json if you want... but that's not going to be used directly by Gnorm, because it's just too limiting.

[joeblew99]

@natefinch thanks thats awesome. The JSON will really help in general.

I think i can then code gen and treat bolt as a normal db with your code. github.com/timshannon/bolthold

[natefinch]

I'll definitely look at bolthold.

On Fri, Sep 15, 2017, 4:04 AM jow blew notifications@github.com wrote:

@natefinch https://github.com/natefinch thanks thats awesome. The JSON will really help in general.

I think i can then code gen and treat bolt as a normal db with your code. github.com/timshannon/bolthold

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/gnormal/gnorm/issues/1#issuecomment-329711485, or mute the thread https://github.com/notifications/unsubscribe-auth/ADCcyKfX0Kt-aGvx7Ilng_2xLg2cIImuks5sii8tgaJpZM4PM-1B .

[gernest]

@joeblew99 FYI now you can get json gnorm preview --format json

[joeblew99]

it works !!

Super useful. Thanks @gernest

Wow this is going to be really useful for Key Value DBS using this same code base. I will play around and see what i can do with Badger and Boltd (boltDB). An integration using storm should not be easy.

[daniel-reed]

Database Info

// Foreign Key contains the definition of a database foreign key
type ForeignKey struct {
    SchemaName string // the original name of the schema in the db
    TableName string // the original name of the table in the db
    ColumnName string // the original name of the column in the db
    ForeignKeyConstraintName string // the original name of the foreign key constraint in the db
    PositionInUniqueConstraint int // the position of the unique constraint in the db
    ForeignTableName string // the original name of the table in the db for the referenced table
    ForeignColumnName string // the original name of the column in the db for the referenced column
    UniqueConstraintName string // the original name of the unique key constraint in the db
}

Queries

Postgres

SELECT
  rc.constraint_schema, lkc.table_name, lkc.column_name, lkc.constraint_name, lkc.position_in_unique_constraint, fkc.table_name, fkc.column_name, fkc.constraint_name
FROM information_schema.referential_constraints rc
  LEFT JOIN information_schema.key_column_usage lkc
    ON lkc.table_schema = rc.constraint_schema
      AND lkc.constraint_name = rc.constraint_name
  LEFT JOIN information_schema.key_column_usage fkc
    ON fkc.table_schema = rc.constraint_schema
      AND fkc.ordinal_position = lkc.position_in_unique_constraint
      AND fkc.constraint_name = rc.unique_constraint_name

Mysql

SELECT lkc.TABLE_SCHEMA, lkc.TABLE_NAME, lkc.COLUMN_NAME, lkc.CONSTRAINT_NAME, lkc.POSITION_IN_UNIQUE_CONSTRAINT, lkc.REFERENCED_TABLE_NAME, lkc.REFERENCED_COLUMN_NAME, rc.UNIQUE_CONSTRAINT_NAME
FROM `information_schema`.`REFERENTIAL_CONSTRAINTS` as rc
  LEFT JOIN `information_schema`.`KEY_COLUMN_USAGE` as lkc
    ON lkc.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
      AND lkc.CONSTRAINT_NAME = rc.CONSTRAINT_NAME

Expose

Option 1

For simplicity of implementation, we could use the same structure as the ForeignKey struct above and expose it on the column as Column.ForeignKey. It contains some redundant information in this context but we could again use the same structure on the Table where all that context would be wanted.

On the table, it should probably be grouped in a map using the constrain name as the key. The map would contain slices of Foreign Key definitions.

The foreign key struct would provide enough information to look up the actual column definition to use in templating

// Table is the data about a DB Table.
type Table struct {
    Name          string             // the converted name of the table
    DBName        string             // the original name of the table in the DB
    Schema        *Schema            `yaml:"-" json:"-"` // the schema this table is in
    Columns       Columns            // Database columns
    ColumnsByName map[string]*Column `yaml:"-" json:"-"` // dbname to column
    PrimaryKeys   Columns            // Primary Key Columns
    ForeignKeys   map[string][]*ForeignKey
}

// Column is the data about a DB column of a table.
type Column struct {
    Name        string      // the converted name of the column
    DBName      string      // the original name of the column in the DB
    Type        string      // the converted name of the type
    DBType      string      // the original type of the column in the DB
    IsArray     bool        // true if the column type is an array
    Length      int         // non-zero if the type has a length (e.g. varchar[16])
    UserDefined bool        // true if the type is user-defined
    Nullable    bool        // true if the column is not NON NULL
    HasDefault  bool        // true if the column has a default
    PrimaryKey  bool        // true if the column is a primary key
    ForeignKey *ForeignKey
    Orig        interface{} `yaml:"-" json:"-"` // the raw database column data
}

// Foreign Key contains the definition of a database foreign key
type ForeignKey struct {
    SchemaName string // the original name of the schema in the db
    TableName string // the original name of the table in the db
    ColumnName string // the original name of the column in the db
    ForeignKeyConstraintName string // the original name of the foreign key constraint in the db
    PositionInUniqueConstraint int // the position of the unique constraint in the db
    ForeignTableName string // the original name of the table in the db for the referenced table
    ForeignColumnName string // the original name of the column in the db for the referenced column
    UniqueConstraintName string // the original name of the unique key constraint in the db
}

Option 2

We could change the ForeignKey struct to contain references to the schema, table, and column for each side of the relationship. This would be a lot easier to work with inside go and templating. However, I am not quite sure how we would handle recursion in this scenario for the serialized output.

// Table is the data about a DB Table.
type Table struct {
    Name          string             // the converted name of the table
    DBName        string             // the original name of the table in the DB
    Schema        *Schema            `yaml:"-" json:"-"` // the schema this table is in
    Columns       Columns            // Database columns
    ColumnsByName map[string]*Column `yaml:"-" json:"-"` // dbname to column
    PrimaryKeys   Columns            // Primary Key Columns
    ForeignKeys   map[string][]*ForeignKey
}

// Column is the data about a DB column of a table.
type Column struct {
    Name        string      // the converted name of the column
    DBName      string      // the original name of the column in the DB
    Type        string      // the converted name of the type
    DBType      string      // the original type of the column in the DB
    IsArray     bool        // true if the column type is an array
    Length      int         // non-zero if the type has a length (e.g. varchar[16])
    UserDefined bool        // true if the type is user-defined
    Nullable    bool        // true if the column is not NON NULL
    HasDefault  bool        // true if the column has a default
    PrimaryKey  bool        // true if the column is a primary key
    ForeignKey *ForeignKey
    Orig        interface{} `yaml:"-" json:"-"` // the raw database column data
}

// Foreign Key contains the definition of a database foreign key
type ForeignKey struct {
    Schema *Schema
    Table *Table
    Column *Column
    ForeignTable *Table
    ForeignColumn *Column
}

Feedback on anything above would be appreciated, specifically how to handle recursion in option 2. I'm also open to different approaches if anyone has some ideas.

[natefinch]

really nice work, thank you, Daniel.

option 2 probably works better, as you've noted. I would put the name in the foreign key struct for option 2, then at least there's something for the serialized output. we could also just duplicate some info in those structs if we really wanted to.

[natefinch]

closed by #63