#3 PR brought new problem. Now fields with unique constraints are duplicated in some cases.

I'm trying to fix it but also want to cover this functionality with tests.

Trying to run tests gives me:

test/model/users.go:4:2: cannot find package "github.com/dizzyfool/genna/test/geo" in any of:

	/usr/local/Cellar/go/1.9.2/libexec/src/github.com/dizzyfool/genna/test/geo (from $GOROOT)

	/Users/kuzma/Documents/Workspace/go/src/github.com/dizzyfool/genna/test/geo (from $GOPATH)

Process finished with exit code 1

Please add cast UUID type for column.
Example

CREATE TABLE test_table
(
    "test_uuid" uuid    NOT NULL,
    primary key (test_uuid)
);

TO

type TestTable struct {
      tableName struct{} `pg:"test_table,alias:t,,discard_unknown_columns"`
      
      ID uuid.UUID `pg:"test_uuid,pk,type:uuid"`
}

Hello,

Thx for this usefull generator.
Is it possible to desactivate the need of SSL.

I got this error:

generate error {"error": "read database error: getting tables info error: pg: SSL is not enabled on the server", "errorVerbose": "read database error:\n
github.com/dizzyfool/genna/generators/base.Generator.Generate\n

I have a schema and relationship defined as follows:

Table chat_event:
id (bigint)

Table message:

id   (bigint)
id_chat_event   (bigint, fk, uq, nn)

So a chat_event Belongs To a message, at least in the way I've defined my schema. When I run Genna, it outputs a struct for message that includes a reference to ChatEvent like so:

IdChatEventRel *ChatEvent `pg:"fk:id_chat_event"`

However, ChatEvent does not have any reference to message.

This is causing a problem, because I am trying to query using go-pg all chat_events in a conversation, and ideally each chat_event would have a *Message field that is populated. However, ChatEvent structs as generated by genna currently do not seem to contain a *Message field.

The command I am using is:
~/go/bin/genna model -c postgresql://localhost/mydb?sslmode=disable -f -g 9 -o model/schema.go

If you need more details, I may be able to provide them over a private channel, or maybe create a generic example.

Thank you for your work, Genna has been very helpful to my projects.

Please add print version to cmd, like -v or version

$ go get github.com/dizzyfool/genna
go: finding github.com/dizzyfool/genna latest
# golang.org/x/xerrors
go/pkg/mod/golang.org/x/[email protected]/adaptor_go1_13.go:16:14: undefined: errors.Frame
go/pkg/mod/golang.org/x/[email protected]/format_go1_13.go:12:18: undefined: errors.Formatter

Probably needs an xerrors package bump

go-pg v9 has migrated all struct field tags with sql: to pg: [ref]

But the current example at https://github.com/dizzyfool/genna/blob/master/generators/model/README.md still shows sql:. Is it just the example wrong or genna hasn't fully migrated yet? I am currently unable to test this out due to #25 Thanks.

I have a status column of type status, which can be "pass" or "fail".

genna validation is creating

func (m SomeTable) Validate() (errors map[string]string, valid bool) {
	errors = map[string]string{}



	if m.Uuid == 0 {
		errors[Columns.SomeTable.Uuid] = ErrEmptyValue
	}



	if m.Status != nil {
		switch *m.Status {
			case "fail", "pass":
			default:
				errors[Columns.SomeTable.Status] = ErrWrongValue
		}
	}



	return errors, len(errors) == 0
}

instead of

func (m SomeTable) Validate() (errors map[string]string, valid bool) {
	errors = map[string]string{}

	if m.Uuid == "" {
		errors[Columns.SomeTable.Uuid] = ErrEmptyValue
	}

	if m.Status != nil {
		switch *m.Status {
		case "fail", "pass":
		default:
			errors[Columns.SomeTable.Status] = ErrWrongValue
		}
	}

	return errors, len(errors) == 0
}

Hi, I have a table with a jsonb[] column but could not currently generate the right type and tag

Simplified table:

                                   Table "public.assets"
           Column            |            Type             | Collation | Nullable | Default
-----------------------------+-----------------------------+-----------+----------+---------
 frozen_supply               | jsonb[]                     |           |          |

Generated model:

type Asset struct {
    tableName struct{} `sql:"assets,alias:t" pg:",discard_unknown_columns"`

    FrozenSupply            interface{} `sql:"-"`
}

The right type for auto marshal/unmarshal is

type Asset struct {
    tableName struct{} `sql:"assets,alias:t" pg:",discard_unknown_columns"`

    FrozenSupply            []map[string]interface{} `sql:"frozen_supply,array"`
}

Plain jsonb is fine, just not jsonb[]. Any chance to look into this? Thanks.

If you have table with dash symbol in it's name you will get this error:

getting info from database ...
running generator ...
panic: 3:15: expected expression (and 4 more errors)

goroutine 1 [running]:
github.com/dizzyfool/genna/cmd.glob..func1(0x17cf000, 0xc420112d20, 0x0, 0x6)
	/Users/kuzma/Documents/Workspace/go/src/github.com/dizzyfool/genna/cmd/root.go:94 +0x69e
github.com/spf13/cobra.(*Command).execute(0x17cf000, 0xc420010080, 0x6, 0x6, 0x17cf000, 0xc420010080)
	/Users/kuzma/Documents/Workspace/go/src/github.com/spf13/cobra/command.go:766 +0x2c1
github.com/spf13/cobra.(*Command).ExecuteC(0x17cf000, 0xa, 0x151e300, 0x15311f5)
	/Users/kuzma/Documents/Workspace/go/src/github.com/spf13/cobra/command.go:852 +0x334
github.com/spf13/cobra.(*Command).Execute(0x17cf000, 0x0, 0xc42010f150)
	/Users/kuzma/Documents/Workspace/go/src/github.com/spf13/cobra/command.go:800 +0x2b
github.com/dizzyfool/genna/cmd.Execute()
	/Users/kuzma/Documents/Workspace/go/src/github.com/dizzyfool/genna/cmd/root.go:106 +0x31
main.main()
	/Users/kuzma/Documents/Workspace/go/src/github.com/dizzyfool/genna/main.go:20 +0x20

Can not be used, pg revision changed the structure of several files

C:\Users\Dev1>go get github.com/dizzyfool/genna

github.com/dizzyfool/genna/database

E:\Work\GO\package\src\github.com\dizzyfool\genna\database\database.go:24:7: event.Data undefined (type *pg.QueryEvent has no field or method Data)
E:\Work\GO\package\src\github.com\dizzyfool\genna\database\database.go:35:10: event.Data undefined (type *pg.QueryEvent has no field or method Data)
E:\Work\GO\package\src\github.com\dizzyfool\genna\database\database.go:36:20: event.Data undefined (type *pg.QueryEvent has no field or method Data)
E:\Work\GO\package\src\github.com\dizzyfool\genna\database\database.go:54:36: cannot use NewQueryLogger(logger) (type QueryLogger) as type pg.QueryHook in argument to client.baseDB.AddQueryHook:
QueryLogger does not implement pg.QueryHook (wrong type for AfterQuery method)
have AfterQuery(*pg.QueryEvent)
want AfterQuery(context.Context, *pg.QueryEvent) (context.Context, error)

Problem:

go: downloading github.com/go-pg/pg/v9 v9.0.0-beta.2
go: extracting github.com/go-pg/pg/v9 v9.0.0-beta.2
# github.com/dizzyfool/genna/lib
../../Go/pkg/mod/github.com/dizzyfool/[email protected]/lib/database.go:60:37: cannot use newQueryLogger(logger) (type queryLogger) as type pg.QueryHook in argument to client.baseDB.AddQueryHook:
        queryLogger does not implement pg.QueryHook (wrong type for AfterQuery method)
                have AfterQuery(context.Context, *pg.QueryEvent) (context.Context, error)
                want AfterQuery(context.Context, *pg.QueryEvent) error

Is this changed to use *time.Time instead of pg.NullTime?

For a very large existing database, it will make more sense if the generated structs are not all lumped into one single models.go file, but instead, written out as separate files.

For this scenario, we can provide an option for the user to provide an output directory, so all these golang files can be written into this directory.

Hello, I am trying to map a third-party postres 9.5 database with more than 22000 (yes, twenty-two thousands) tables.
My goal is to fetch some data in my Go application.
Obviously I am interested in a couple of tables, the command line I am using is this:

genna model-named -c postgres://postgres:postgres@hostname/database?sslmode=disable -t hrdd_001employee00,hrdd_001employee02,hrdd_001employee11,codd_001subject03_m,codd_001subject04 -o models.go

Unfortunately the command never finishes processing.
So I debugged the application and found that the query that is taking forever is at dizzyfool/genna/lib/store.go@180

In particular, the offending subquery is

select distinct
				 	kcu.table_schema as table_schema,
					kcu.table_name   as table_name,
					kcu.column_name  as column_name,
					array_agg((
						select constraint_type::text 
						from information_schema.table_constraints tc 
						where tc.constraint_name = kcu.constraint_name 
							and tc.constraint_schema = kcu.constraint_schema 
							and tc.constraint_catalog = kcu.constraint_catalog
						limit 1
					)) as constraint_types
				from information_schema.key_column_usage kcu
				group by kcu.table_schema, kcu.table_name, kcu.column_name

If I execute this statement on my database, this query never finishes
If I add a where condition with table names filter, it is very slow but it completes.

What do you think about this change?

Hi, amazing tool.

I am probably missing a config setting or just not using this right. I have auto generated models from my Postgres database. Everything works as expected retrieving models but when I go to update them I get an error operator does not exist: uuid = text.

I did some googling and found https://github.com/go-pg/pg/issues/622. Adding the type manually here as suggested worked.

I am using go-pg v8.0.4 and pulled down the latest version of genna.

Is there away to have genna generate the UUID type?

README doesn't agree with CLI.

Please fix depricated pg notice "rel:has-one"

• Golang 1.16
• Library versions
• • github.com/go-pg/pg/v10 v10.9.0
• Genna version v1.1.2

DEPRECATED MESSAGE

DEPRECATED: pg: 2021/04/09 21:21:01 add pg:"rel:has-one" to Wallet.UserFkUuidRel field tag

problem line of code

UserFkUuidRel       *User       `pg:"fk:user_fk_uuid"`

correct code

UserFkUuidRel       *User       `pg:"fk:user_fk_uuid,rel:has-one"`

Any reason why numeric is float32, not float64? https://github.com/dizzyfool/genna/blob/master/model/types.go#L224

Even though float64 cannot cover the full range of numeric types, it is still better than float32.

is v10 compatible with this lib? thank you!

I have table with regulator_id column. This column can be NULL but can't be 0

CREATE TABLE api.ai_class_vehicle_models (
	gid varchar NOT NULL,
	"name" varchar NOT NULL,
	regulator_id int4 NULL,
	vehicle_brand_gid varchar NOT NULL,
	CONSTRAINT ai_class_vehicle_models_check CHECK ((regulator_id <> 0)),
	CONSTRAINT ai_class_vehicle_models_pk PRIMARY KEY (gid),
	CONSTRAINT ai_class_vehicle_models_un UNIQUE (gid, vehicle_brand_gid),
	CONSTRAINT ai_class_vehicle_models_ai_class_vehicle_brands_fk FOREIGN KEY (vehicle_brand_gid) REFERENCES api.ai_class_vehicle_brands(gid)
);

When I use genna to generate a model here is what I get:

type AiClassVehicleModel struct {
	tableName struct{} `sql:"api.ai_class_vehicle_models,alias:t" pg:",discard_unknown_columns"`

	ID              string `sql:"gid,pk"`
	Name            string `sql:"name,notnull"`
	RegulatorID     *int   `sql:"regulator_id"`
	VehicleBrandGid string `sql:"vehicle_brand_gid,notnull"`

	VehicleBrandGidRel *AiClassVehicleBrand `pg:"fk:vehicle_brand_gid"`
}

As you can see RegulatorID type is pointer type *int. When I try to save this model to database I get 0 instead of NULL. If I manually change *int to int everything works fine.

Hello, I have two tables with one composite foreign key referring to another

The simplified pg schema is

                      Table "public.accounts"
    Column     |     Type      | Collation | Nullable |   Default
---------------+---------------+-----------+----------+-------------
 address       | character(34) |           | not null |
 confirmed     | boolean       |           | not null | false
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (address, confirmed)
Referenced by:
    TABLE "assets" CONSTRAINT "assets_owner_address_fkey" FOREIGN KEY (owner_address, confirmed) REFERENCES accounts(address, confirmed)

                            Table "public.assets"
    Column     |            Type             | Collation | Nullable | Default
---------------+-----------------------------+-----------+----------+---------
 id            | character(7)                |           | not null |
 confirmed     | boolean                     |           | not null | false
 owner_address | character(34)               |           | not null |
Indexes:
    "assets_pkey" PRIMARY KEY, btree (id, confirmed)
Foreign-key constraints:
    "assets_owner_address_fkey" FOREIGN KEY (owner_address, confirmed) REFERENCES accounts(address, confirmed)

When using genna to generate separated models, I got one for accounts and one for assets

type Account struct {
    tableName struct{} `sql:"accounts,alias:t" pg:",discard_unknown_columns"`

    Address     string                 `sql:"address,pk"`
    Confirmed   bool                   `sql:"confirmed,pk"`
}

type Asset struct {
    tableName struct{} `sql:"assets,alias:t" pg:",discard_unknown_columns"`

    Confirmed    bool      `sql:"confirmed,pk"`
    ID           string    `sql:"id,pk"`
    OwnerAddress string    `sql:"owner_address,notnull"`

    ConfirmedRel    *Account     `pg:"fk:confirmed"`
    OwnerAddressRel *Account     `pg:"fk:owner_address"`
}

The issue is that if I insert Asset into pg using

var asset Asset
// Initialize asset ...
db.Insert(&asset)

I would get an error trying to insert confirmed_rel and owner_address_rel: ERROR #42703 column "confirmed_rel" of relation "assets" does not exist

If it was a single foreign key, go-pg/pg ignores the foreign key field and only inserts the valid confirmed and owner_address. The specific check to deny filtering composite key is at https://github.com/go-pg/pg/blob/master/orm/table.go#L922

So my question is whether genna should generate composite foreign keys in a different way so inserting with the auto-generated data works out-of-box. For now I am explicitly adding sql:"-" so it doesn't save. Alternatively, maybe not generate at all, or generate them in a different way for go-pg/pg to recognize? Thanks for any suggestions.

When it comes to fields with UNIQUE constraints model generation ignores them.

Here are some steps to reproduce the bug

First let's run a clear database in docker:

$ docker run --name test -p 15432:5432 -e POSTGRES_PASSWORD=test -d postgres

Then let's add some sample data from this file: dump.txt

docker exec -i test psql -U postgres -d postgres < ./dump.txt

Let's check the structure of PostgreSQL table. redmine_project_id is definitely there:

$ docker exec -it test psql -U postgres -d postgres -c '\d api.contracts_fixed'
                  Table "api.contracts_fixed"
       Column       |  Type   | Collation | Nullable | Default
--------------------+---------+-----------+----------+---------
 id                 | integer |           | not null |
 redmine_project_id | integer |           |          |
Indexes:
    "contracts_fixed_un" UNIQUE CONSTRAINT, btree (redmine_project_id)

Generating model:

$ genna -c postgres://postgres:test@localhost:15432/postgres?sslmode=disable -o ./ -t 'api.*' -s -m
getting info from database ...
running generator ...
generated 1 models from 1 tables in total. saved in 1 packages in 2 files

When we check generated model there is no RedmineProjectID field there:

$ cat api/api_contracts_fixed.go
package api

type ContractFixed struct {
	tableName struct{} `sql:"api.contracts_fixed,alias:t" pg:",discard_unknown_columns"`

	ID int `sql:"id,notnull"`
}

How do I generate an Int slice or String slice instead of map[string]interface{} ?