Git Product home page Git Product logo

Comments (9)

joeblew99 avatar joeblew99 commented on August 16, 2024

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 :)

from gnorm.

natefinch avatar natefinch commented on August 16, 2024

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.

from gnorm.

joeblew99 avatar joeblew99 commented on August 16, 2024

@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

from gnorm.

natefinch avatar natefinch commented on August 16, 2024

from gnorm.

gernest avatar gernest commented on August 16, 2024

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

from gnorm.

joeblew99 avatar joeblew99 commented on August 16, 2024

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.

from gnorm.

daniel-reed avatar daniel-reed commented on August 16, 2024

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.

from gnorm.

natefinch avatar natefinch commented on August 16, 2024

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.

from gnorm.

natefinch avatar natefinch commented on August 16, 2024

closed by #63

from gnorm.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.