search

volatiletech / sqlboiler

Generate a Go ORM tailored to your database schema.
BSD 3-Clause "New" or "Revised" License
6.66k stars 539 forks source link

Add support for composite foreign keys #698

Open kop opened 4 years ago

kop commented 4 years ago

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v3.6.1

What is your database and version (eg. Postgresql 10)

CockroachDB v19.1.8

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

go run github.com/volatiletech/sqlboiler -d --wipe crdb

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Click to expand ```json { "config": { "driver_name": "crdb", "driver_config": { "blacklist": null, "dbname": "packboard", "host": "127.0.0.1", "port": 26257, "user": "root", "whitelist": [ "iam_policies", "iam_policy_assignments", "iam_policy_assignments_to_permissions", "iam_policy_assignments_to_roles", "iam_policy_subjects_to_users", "iam_roles", "iam_roles_to_permissions" ] }, "pkg_name": "models", "out_folder": "models", "debug": true, "wipe": true, "struct_tag_casing": "snake", "imports": { "all": { "Standard": [ "\"database/sql\"", "\"fmt\"", "\"reflect\"", "\"strconv\"", "\"strings\"", "\"sync\"", "\"time\"", "\"context\"" ], "ThirdParty": [ "\"github.com/friendsofgo/errors\"", "\"github.com/volatiletech/sqlboiler/boil\"", "\"github.com/volatiletech/sqlboiler/queries\"", "\"github.com/volatiletech/sqlboiler/queries/qm\"", "\"github.com/volatiletech/sqlboiler/queries/qmhelper\"", "\"github.com/volatiletech/sqlboiler/strmangle\"" ] }, "test": { "Standard": [ "\"bytes\"", "\"reflect\"", "\"testing\"", "\"context\"" ], "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/friendsofgo/errors\"", "\"github.com/volatiletech/sqlboiler/boil\"", "\"github.com/volatiletech/sqlboiler/strmangle\"" ] }, "crdb_upsert": { "Standard": [ "\"fmt\"", "\"strings\"" ], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/drivers\"", "\"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 }, "crdb_main_test": { "Standard": [ "\"bytes\"", "\"database/sql\"", "\"fmt\"", "\"io\"", "\"os\"", "\"os/exec\"", "\"regexp\"", "\"strings\"" ], "ThirdParty": [ "_ \"github.com/lib/pq\"", "\"github.com/pkg/errors\"", "\"github.com/spf13/viper\"", "\"github.com/volatiletech/sqlboiler/randomize\"" ] }, "crdb_suites_test": { "Standard": [ "\"testing\"" ], "ThirdParty": [] } }, "based_on_type": { "null.Bool": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Bytes": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Float32": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Float64": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Int": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Int16": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Int32": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Int64": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Int8": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.JSON": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.String": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Time": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Uint": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Uint16": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Uint32": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Uint64": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "null.Uint8": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/null\"" ] }, "time.Time": { "Standard": [ "\"time\"" ], "ThirdParty": [] }, "types.BoolArray": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.BytesArray": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.Decimal": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.DecimalArray": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.Float64Array": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.Int64Array": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.JSON": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.NullDecimal": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] }, "types.StringArray": { "Standard": [], "ThirdParty": [ "\"github.com/volatiletech/sqlboiler/types\"" ] } } }, "aliases": { "tables": { "iam_policies": { "up_plural": "IamPolicies", "up_singular": "IamPolicy", "down_plural": "iamPolicies", "down_singular": "iamPolicy", "columns": { "account_id": "AccountID", "description": "Description", "effect": "Effect", "id": "ID", "subject_account_member_id": "SubjectAccountMemberID", "subject_guest": "SubjectGuest", "title": "Title" } }, "iam_policy_assignments": { "up_plural": "IamPolicyAssignments", "up_singular": "IamPolicyAssignment", "down_plural": "iamPolicyAssignments", "down_singular": "iamPolicyAssignment", "columns": { "id": "ID", "policy_account_id": "PolicyAccountID", "policy_id": "PolicyID", "resource": "Resource" }, "relationships": { "fk_policy_account_id_ref_iam_policies": { "local": "PolicyAccountIamPolicyAssignments", "foreign": "PolicyAccount" } } }, "iam_policy_assignments_to_permissions": { "up_plural": "IamPolicyAssignmentsToPermissions", "up_singular": "IamPolicyAssignmentsToPermission", "down_plural": "iamPolicyAssignmentsToPermissions", "down_singular": "iamPolicyAssignmentsToPermission", "columns": { "permission_id": "PermissionID", "policy_account_id": "PolicyAccountID", "policy_assignment_id": "PolicyAssignmentID", "policy_id": "PolicyID" }, "relationships": { "fk_policy_account_id_ref_iam_policy_assignments": { "local": "PolicyAccountIamPolicyAssignmentsToPermissions", "foreign": "PolicyAccount" } } }, "iam_policy_assignments_to_roles": { "up_plural": "IamPolicyAssignmentsToRoles", "up_singular": "IamPolicyAssignmentsToRole", "down_plural": "iamPolicyAssignmentsToRoles", "down_singular": "iamPolicyAssignmentsToRole", "columns": { "policy_account_id": "PolicyAccountID", "policy_assignment_id": "PolicyAssignmentID", "policy_id": "PolicyID", "role_account_id": "RoleAccountID", "role_id": "RoleID" }, "relationships": { "fk_policy_account_id_ref_iam_policy_assignments": { "local": "PolicyAssignmentIamPolicyAssignmentsToRoles", "foreign": "PolicyAssignment" }, "fk_role_account_id_ref_iam_roles": { "local": "RoleAccountIamPolicyAssignmentsToRoles", "foreign": "RoleAccount" } } }, "iam_policy_subjects_to_users": { "up_plural": "IamPolicySubjectsToUsers", "up_singular": "IamPolicySubjectsToUser", "down_plural": "iamPolicySubjectsToUsers", "down_singular": "iamPolicySubjectsToUser", "columns": { "policy_account_id": "PolicyAccountID", "policy_id": "PolicyID", "subject_user_id": "SubjectUserID" }, "relationships": { "fk_policy_account_id_ref_iam_policies": { "local": "PolicyAccountIamPolicySubjectsToUsers", "foreign": "PolicyAccount" } } }, "iam_roles": { "up_plural": "IamRoles", "up_singular": "IamRole", "down_plural": "iamRoles", "down_singular": "iamRole", "columns": { "account_id": "AccountID", "description": "Description", "id": "ID", "title": "Title" } }, "iam_roles_to_permissions": { "up_plural": "IamRolesToPermissions", "up_singular": "IamRolesToPermission", "down_plural": "iamRolesToPermissions", "down_singular": "iamRolesToPermission", "columns": { "permission_id": "PermissionID", "role_account_id": "RoleAccountID", "role_id": "RoleID" }, "relationships": { "fk_role_account_id_ref_iam_roles": { "local": "RoleAccountIamRolesToPermissions", "foreign": "RoleAccount" } } } } }, "version": "3.6.1" }, "driver_config": { "blacklist": null, "dbname": "packboard", "host": "127.0.0.1", "port": 26257, "user": "root", "whitelist": [ "iam_policies", "iam_policy_assignments", "iam_policy_assignments_to_permissions", "iam_policy_assignments_to_roles", "iam_policy_subjects_to_users", "iam_roles", "iam_roles_to_permissions" ] }, "schema": "public", "dialect": { "lq": 34, "rq": 34, "use_index_placeholders": true, "use_last_insert_id": false, "use_schema": false, "use_default_keyword": true, "use_auto_columns": false, "use_top_clause": false, "use_output_clause": false, "use_case_when_exists_clause": false }, "tables": [ { "name": "iam_policies", "schema_name": "", "columns": [ { "name": "account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "id", "type": "int64", "db_type": "int8", "default": "unique_rowid()", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "title", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "description", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "effect", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "subject_guest", "type": "bool", "db_type": "bool", "default": "false", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "subject_account_member_id", "type": "int64", "db_type": "int8", "default": "0:::INT8", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "account_id", "id" ] }, "f_keys": null, "is_join_table": false, "to_one_relationships": null, "to_many_relationships": [ { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_subjects_to_users", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_subjects_to_users", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_subjects_to_users", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policies", "table": "iam_policies", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_subjects_to_users", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false } ] }, { "name": "iam_policy_assignments", "schema_name": "", "columns": [ { "name": "policy_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "id", "type": "int64", "db_type": "int8", "default": "unique_rowid()", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "resource", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "policy_account_id", "policy_id", "id" ] }, "f_keys": [ { "table": "iam_policy_assignments", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false } ], "is_join_table": false, "to_one_relationships": null, "to_many_relationships": [ { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_permissions", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_policy_account_id_ref_iam_policy_assignments", "table": "iam_policy_assignments", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "policy_assignment_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false } ] }, { "name": "iam_policy_assignments_to_permissions", "schema_name": "", "columns": [ { "name": "policy_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_assignment_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "permission_id", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "policy_account_id", "policy_id", "policy_assignment_id", "permission_id" ] }, "f_keys": [ { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_permissions", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false } ], "is_join_table": false, "to_one_relationships": null, "to_many_relationships": null }, { "name": "iam_policy_assignments_to_roles", "schema_name": "", "columns": [ { "name": "policy_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_assignment_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "role_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "role_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "policy_account_id", "policy_id", "policy_assignment_id", "role_account_id", "role_id" ] }, "f_keys": [ { "table": "iam_policy_assignments_to_roles", "name": "fk_role_account_id_ref_iam_roles", "column": "role_account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_role_account_id_ref_iam_roles", "column": "role_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_role_account_id_ref_iam_roles", "column": "role_account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_role_account_id_ref_iam_roles", "column": "role_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "policy_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_assignments_to_roles", "name": "fk_policy_account_id_ref_iam_policy_assignments", "column": "policy_assignment_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false } ], "is_join_table": false, "to_one_relationships": null, "to_many_relationships": null }, { "name": "iam_policy_subjects_to_users", "schema_name": "", "columns": [ { "name": "policy_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "policy_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "subject_user_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "policy_account_id", "policy_id", "subject_user_id" ] }, "f_keys": [ { "table": "iam_policy_subjects_to_users", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_subjects_to_users", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_subjects_to_users", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_account_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_policy_subjects_to_users", "name": "fk_policy_account_id_ref_iam_policies", "column": "policy_id", "nullable": false, "unique": false, "foreign_table": "iam_policies", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false } ], "is_join_table": false, "to_one_relationships": null, "to_many_relationships": null }, { "name": "iam_roles", "schema_name": "", "columns": [ { "name": "account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "id", "type": "int64", "db_type": "int8", "default": "unique_rowid()", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "title", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "description", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "account_id", "id" ] }, "f_keys": null, "is_join_table": false, "to_one_relationships": null, "to_many_relationships": [ { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "role_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "role_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "role_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_policy_assignments_to_roles", "foreign_column": "role_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles_to_permissions", "foreign_column": "role_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles_to_permissions", "foreign_column": "role_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_roles_to_permissions", "foreign_column": "role_account_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false }, { "name": "fk_role_account_id_ref_iam_roles", "table": "iam_roles", "column": "id", "nullable": false, "unique": false, "foreign_table": "iam_roles_to_permissions", "foreign_column": "role_id", "foreign_column_nullable": false, "foreign_column_unique": false, "to_join_table": false, "join_table": "", "join_local_fkey_name": "", "join_local_column": "", "join_local_column_nullable": false, "join_local_column_unique": false, "join_foreign_fkey_name": "", "join_foreign_column": "", "join_foreign_column_nullable": false, "join_foreign_column_unique": false } ] }, { "name": "iam_roles_to_permissions", "schema_name": "", "columns": [ { "name": "role_account_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "role_id", "type": "int64", "db_type": "int8", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false }, { "name": "permission_id", "type": "string", "db_type": "string", "default": "", "nullable": false, "unique": false, "validated": false, "arr_type": null, "udt_name": "", "domain_name": null, "full_db_type": "", "auto_generated": false } ], "p_key": { "name": "primary", "columns": [ "role_account_id", "role_id", "permission_id" ] }, "f_keys": [ { "table": "iam_roles_to_permissions", "name": "fk_role_account_id_ref_iam_roles", "column": "role_account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_roles_to_permissions", "name": "fk_role_account_id_ref_iam_roles", "column": "role_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "account_id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_roles_to_permissions", "name": "fk_role_account_id_ref_iam_roles", "column": "role_account_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false }, { "table": "iam_roles_to_permissions", "name": "fk_role_account_id_ref_iam_roles", "column": "role_id", "nullable": false, "unique": false, "foreign_table": "iam_roles", "foreign_column": "id", "foreign_column_nullable": false, "foreign_column_unique": false } ], "is_join_table": false, "to_one_relationships": null, "to_many_relationships": null } ], "templates": [ { "name": "templates/00_struct.go.tpl", "loader": "asset:templates/00_struct.go.tpl" }, { "name": "templates/01_types.go.tpl", "loader": "asset:templates/01_types.go.tpl" }, { "name": "templates/02_hooks.go.tpl", "loader": "asset:templates/02_hooks.go.tpl" }, { "name": "templates/03_finishers.go.tpl", "loader": "asset:templates/03_finishers.go.tpl" }, { "name": "templates/04_relationship_to_one.go.tpl", "loader": "asset:templates/04_relationship_to_one.go.tpl" }, { "name": "templates/05_relationship_one_to_one.go.tpl", "loader": "asset:templates/05_relationship_one_to_one.go.tpl" }, { "name": "templates/06_relationship_to_many.go.tpl", "loader": "asset:templates/06_relationship_to_many.go.tpl" }, { "name": "templates/07_relationship_to_one_eager.go.tpl", "loader": "asset:templates/07_relationship_to_one_eager.go.tpl" }, { "name": "templates/08_relationship_one_to_one_eager.go.tpl", "loader": "asset:templates/08_relationship_one_to_one_eager.go.tpl" }, { "name": "templates/09_relationship_to_many_eager.go.tpl", "loader": "asset:templates/09_relationship_to_many_eager.go.tpl" }, { "name": "templates/10_relationship_to_one_setops.go.tpl", "loader": "asset:templates/10_relationship_to_one_setops.go.tpl" }, { "name": "templates/11_relationship_one_to_one_setops.go.tpl", "loader": "asset:templates/11_relationship_one_to_one_setops.go.tpl" }, { "name": "templates/12_relationship_to_many_setops.go.tpl", "loader": "asset:templates/12_relationship_to_many_setops.go.tpl" }, { "name": "templates/13_all.go.tpl", "loader": "asset:templates/13_all.go.tpl" }, { "name": "templates/14_find.go.tpl", "loader": "asset:templates/14_find.go.tpl" }, { "name": "templates/15_insert.go.tpl", "loader": "asset:templates/15_insert.go.tpl" }, { "name": "templates/16_update.go.tpl", "loader": "asset:templates/16_update.go.tpl" }, { "name": "templates/17_upsert.go.tpl", "loader": "base64:(sha256 of content): 9af83ccfff47c473a3d38c39180dedbf17bffd7c61c4eb4d10e62acf8e1e5c6d" }, { "name": "templates/18_delete.go.tpl", "loader": "asset:templates/18_delete.go.tpl" }, { "name": "templates/19_reload.go.tpl", "loader": "asset:templates/19_reload.go.tpl" }, { "name": "templates/20_exists.go.tpl", "loader": "asset:templates/20_exists.go.tpl" }, { "name": "templates/21_auto_timestamps.go.tpl", "loader": "asset:templates/21_auto_timestamps.go.tpl" }, { "name": "templates/singleton/boil_queries.go.tpl", "loader": "asset:templates/singleton/boil_queries.go.tpl" }, { "name": "templates/singleton/boil_table_names.go.tpl", "loader": "asset:templates/singleton/boil_table_names.go.tpl" }, { "name": "templates/singleton/boil_types.go.tpl", "loader": "asset:templates/singleton/boil_types.go.tpl" }, { "name": "templates/singleton/crdb_upsert.go.tpl", "loader": "base64:(sha256 of content): 0d860746baad9cf28763f2398f91f2045c2991f9de8cea84e2a1fae4d4fe7599" }, { "name": "templates_test/00_types.go.tpl", "loader": "asset:templates_test/00_types.go.tpl" }, { "name": "templates_test/all.go.tpl", "loader": "asset:templates_test/all.go.tpl" }, { "name": "templates_test/delete.go.tpl", "loader": "asset:templates_test/delete.go.tpl" }, { "name": "templates_test/exists.go.tpl", "loader": "asset:templates_test/exists.go.tpl" }, { "name": "templates_test/find.go.tpl", "loader": "asset:templates_test/find.go.tpl" }, { "name": "templates_test/finishers.go.tpl", "loader": "asset:templates_test/finishers.go.tpl" }, { "name": "templates_test/hooks.go.tpl", "loader": "asset:templates_test/hooks.go.tpl" }, { "name": "templates_test/insert.go.tpl", "loader": "asset:templates_test/insert.go.tpl" }, { "name": "templates_test/relationship_one_to_one.go.tpl", "loader": "asset:templates_test/relationship_one_to_one.go.tpl" }, { "name": "templates_test/relationship_one_to_one_setops.go.tpl", "loader": "asset:templates_test/relationship_one_to_one_setops.go.tpl" }, { "name": "templates_test/relationship_to_many.go.tpl", "loader": "asset:templates_test/relationship_to_many.go.tpl" }, { "name": "templates_test/relationship_to_many_setops.go.tpl", "loader": "asset:templates_test/relationship_to_many_setops.go.tpl" }, { "name": "templates_test/relationship_to_one.go.tpl", "loader": "asset:templates_test/relationship_to_one.go.tpl" }, { "name": "templates_test/relationship_to_one_setops.go.tpl", "loader": "asset:templates_test/relationship_to_one_setops.go.tpl" }, { "name": "templates_test/reload.go.tpl", "loader": "asset:templates_test/reload.go.tpl" }, { "name": "templates_test/select.go.tpl", "loader": "asset:templates_test/select.go.tpl" }, { "name": "templates_test/singleton/boil_main_test.go.tpl", "loader": "asset:templates_test/singleton/boil_main_test.go.tpl" }, { "name": "templates_test/singleton/boil_queries_test.go.tpl", "loader": "asset:templates_test/singleton/boil_queries_test.go.tpl" }, { "name": "templates_test/singleton/boil_suites_test.go.tpl", "loader": "asset:templates_test/singleton/boil_suites_test.go.tpl" }, { "name": "templates_test/singleton/crdb_main_test.go.tpl", "loader": "base64:(sha256 of content): 782d5b41f53992f1c865f5020beaa84b94c9aaabf934de35b50016243b9edae2" }, { "name": "templates_test/singleton/crdb_suites_test.go.tpl", "loader": "base64:(sha256 of content): 3cee2e96b6688ee6135b190c3f8dbd45f691d74515dd12152a561f17b9b8b399" }, { "name": "templates_test/types.go.tpl", "loader": "asset:templates_test/types.go.tpl" }, { "name": "templates_test/update.go.tpl", "loader": "asset:templates_test/update.go.tpl" }, { "name": "templates_test/upsert.go.tpl", "loader": "base64:(sha256 of content): 585ecb8ad43125327a7f06254b8d873b7044984d0712ce7438365821785cd641" } ] } ```

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

Click to expand ```sql CREATE TABLE iam_roles ( account_id int8 NOT NULL, id int8 NOT NULL DEFAULT unique_rowid(), title text NOT NULL, description text NOT NULL, PRIMARY KEY (account_id, id) ); CREATE TABLE iam_permissions ( id text NOT NULL, PRIMARY KEY (id) ); CREATE TABLE iam_roles_to_permissions ( role_account_id int8 NOT NULL, role_id int8 NOT NULL, permission_id text NOT NULL, PRIMARY KEY (role_account_id, role_id, permission_id), FOREIGN KEY (role_account_id, role_id) REFERENCES iam_roles (account_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (permission_id) REFERENCES iam_permissions (id) ON DELETE CASCADE ON UPDATE NO ACTION ) INTERLEAVE IN PARENT iam_roles (role_account_id, role_id); CREATE TABLE iam_policies ( account_id int8 NOT NULL, id int8 NOT NULL DEFAULT unique_rowid(), title text NOT NULL, description text NOT NULL, effect text NOT NULL, subject_guest boolean NOT NULL DEFAULT FALSE, PRIMARY KEY (account_id, id) ); CREATE TABLE iam_policy_subjects_to_users ( policy_account_id int8 NOT NULL, policy_id int8 NOT NULL, subject_user_id int8 NOT NULL, PRIMARY KEY (policy_account_id, policy_id, subject_user_id), FOREIGN KEY (policy_account_id, policy_id) REFERENCES iam_policies (account_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION ) INTERLEAVE IN PARENT iam_policies (policy_account_id, policy_id); CREATE TABLE iam_policy_assignments ( policy_account_id int8 NOT NULL, policy_id int8 NOT NULL, id int8 NOT NULL DEFAULT unique_rowid(), resource text NOT NULL, PRIMARY KEY (policy_account_id, policy_id, id), FOREIGN KEY (policy_account_id, policy_id) REFERENCES iam_policies (account_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION ) INTERLEAVE IN PARENT iam_policies (policy_account_id, policy_id); CREATE TABLE iam_policy_assignments_to_roles ( policy_account_id int8 NOT NULL, policy_id int8 NOT NULL, policy_assignment_id int8 NOT NULL, role_account_id int8 NOT NULL, role_id int8 NOT NULL, PRIMARY KEY (policy_account_id, policy_id, policy_assignment_id, role_account_id, role_id), FOREIGN KEY (policy_account_id, policy_id, policy_assignment_id) REFERENCES iam_policy_assignments (policy_account_id, policy_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (role_account_id, role_id) REFERENCES iam_roles (account_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION ) INTERLEAVE IN PARENT iam_policy_assignments (policy_account_id, policy_id, policy_assignment_id); CREATE TABLE iam_policy_assignments_to_permissions ( policy_account_id int8 NOT NULL, policy_id int8 NOT NULL, policy_assignment_id int8 NOT NULL, permission_id text NOT NULL, PRIMARY KEY (policy_account_id, policy_id, policy_assignment_id, permission_id), FOREIGN KEY (policy_account_id, policy_id, policy_assignment_id) REFERENCES iam_policy_assignments (policy_account_id, policy_id, id) MATCH FULL ON DELETE CASCADE ON UPDATE NO ACTION, FOREIGN KEY (permission_id) REFERENCES iam_permissions (id) ON DELETE CASCADE ON UPDATE NO ACTION ) INTERLEAVE IN PARENT iam_policy_assignments (policy_account_id, policy_id, policy_assignment_id); ```

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

Similar issues have been created here several times already. The problem is that given such schema, SQLBoiler generates multiple relations with the same names. For example:

// IamPolicyRels is where relationship names are stored.
var IamPolicyRels = struct {
    PolicyAccountIamPolicyAssignments     string
    PolicyAccountIamPolicyAssignments     string
    PolicyAccountIamPolicyAssignments     string
    PolicyAccountIamPolicyAssignments     string
    PolicyAccountIamPolicySubjectsToUsers string
    PolicyAccountIamPolicySubjectsToUsers string
    PolicyAccountIamPolicySubjectsToUsers string
    PolicyAccountIamPolicySubjectsToUsers string
}{
    PolicyAccountIamPolicyAssignments:     "PolicyAccountIamPolicyAssignments",
    PolicyAccountIamPolicyAssignments:     "PolicyAccountIamPolicyAssignments",
    PolicyAccountIamPolicyAssignments:     "PolicyAccountIamPolicyAssignments",
    PolicyAccountIamPolicyAssignments:     "PolicyAccountIamPolicyAssignments",
    PolicyAccountIamPolicySubjectsToUsers: "PolicyAccountIamPolicySubjectsToUsers",
    PolicyAccountIamPolicySubjectsToUsers: "PolicyAccountIamPolicySubjectsToUsers",
    PolicyAccountIamPolicySubjectsToUsers: "PolicyAccountIamPolicySubjectsToUsers",
    PolicyAccountIamPolicySubjectsToUsers: "PolicyAccountIamPolicySubjectsToUsers",
}

As far as I understand, this is caused by the invalid relation discovery. For FOREIGN KEY (policy_account_id, policy_id) REFERENCES iam_policies (account_id, id) we get:

    {
      "name": "iam_policies",
      "to_many_relationships": [
        {
          "name": "fk_policy_account_id_ref_iam_policies",
          "table": "iam_policies",
          "column": "account_id",
          "foreign_table": "iam_policy_assignments",
          "foreign_column": "policy_account_id"
        },
        {
          "name": "fk_policy_account_id_ref_iam_policies",
          "table": "iam_policies",
          "column": "account_id",
          "foreign_table": "iam_policy_assignments",
          "foreign_column": "policy_id"
        }
      ]
}

While composite foreign keys may not be very useful in traditional MySQL or PostgreSQL, they have a big performance impact on multi-node NewSQL databases, like CockroachDB.

Having full ownership path encoded in a primary key increases chances that records that share the same prefix will be stored on the same node, decreasing the latency needed to fetch data that belongs to the same owner.

Such design is also needed for Interleaved Tables that is another valuable performance optimization in CockroachDB.

aarondl commented 4 years ago

This sounds interesting.

Unfortunately I don't have a need for this and it's also an earth-shattering amount of work to pipe this change through the entire system but I wouldn't say no to a well-functioning non-breaking PR! Thank you for the suggestion and I hope one day someone does pick this up.

vhakulinen commented 3 years ago

Is there any recommended workaround for this limitation?

vhakulinen commented 3 years ago

With this change, the generated code at least compiles. It basically ignores the fact that the fkeys are on multiple columns. Is that information relevant for sqlboiler?

diff --git a/drivers/sqlboiler-psql/driver/psql.go b/drivers/sqlboiler-psql/driver/psql.go
index 09a3afc..299d495 100644
--- a/drivers/sqlboiler-psql/driver/psql.go
+++ b/drivers/sqlboiler-psql/driver/psql.go
@@ -383,7 +383,7 @@ func (p *PostgresDriver) ForeignKeyInfo(schema, tableName string) ([]drivers.For

    query := `
    select
-       pgcon.conname,
+       DISTINCT ON (pgcon.conname) pgcon.conname,
        pgc.relname as source_table,
        pgasrc.attname as source_column,
        dstlookupname.relname as dest_table,
aarondl commented 3 years ago

There's currently no workaround for this limitation. Ignoring the fkeys on multiple columns will allow bad things to happen in the relationship and set helpers so it's not that helpful, but thanks for putting it out there :)

vhakulinen commented 3 years ago

I'm exploring my options related to this and hit #695 too. I'm considering to write the generated model.Things interface by hand, but thats a bit scary without digging deep into sqlboiler. Which brought me back to this issue: would the mentioned change through the entire system start from changing this struct field on the driver's ForeignKey from a string to slice of strings?

https://github.com/volatiletech/sqlboiler/blob/475493476401b3fd8fd0801eece058e514d284d7/drivers/keys.go#L20

tonyhb commented 3 years ago

@vhakulinen @aarondl the slice of strings method is the approach i started today here - https://github.com/tonyhb/sqlboiler/tree/feature/composite-fks. Here's a diff: https://github.com/volatiletech/sqlboiler/compare/master...tonyhb:feature/composite-fks?expand=1

It's going to take me a while to get through the templates and test, though. And to be honest... I changed to use a surrogate key to make progress on my project. A composite FK would be more usable, but I don't have the time right now to make this work.

MeanSquaredError commented 2 years ago

@tonyhb Thank you for providing a sample implementation of a fix. What is its status and does it actually work? Are you planning to open a PR and merge it into sqlboiler ?

MeanSquaredError commented 2 years ago

OK, I used @tonyhb 's work and rebased and adjusted it against the latest sqlboiler codebase. I also fixed some of the templates. There is still work to be done on the templates, but hopefully it will be ready for a PR soon.

The WIP can be found here: https://github.com/MeanSquaredError/sqlboiler/tree/add-composite-fks

stephenafamo commented 2 years ago

This is really appreciated @MeanSquaredError

stephenafamo commented 2 years ago

How's the PR coming along @MeanSquaredError ?

MeanSquaredError commented 2 years ago

@stephenafamo

The main and driver's code compiles and builds. I had to rewrite the patch in order to get correct handling of nullable and unique foreign keys.

Most of the templates are working too, but some of the eager loading templates still need fixes.

Overall it seems to work correctly for my own project(s) but it needs a few test cases for the composite keys before we can say that it works correctly.

I will prepare and submit the final version of the patch this week (most likely this weekend).

MJacred commented 2 years ago

Sounds terrific! How is the final version of the PR going, @MeanSquaredError?

xaqbr commented 1 year ago

Any update on the PR @MeanSquaredError? Great work by the way.