Issue: pwiz not generating foreign key fields for postgres
Author: Mak-NOAA
State: closed
Created: 2016-07-21 03:23:24

Hi @coleifer 

I have the same issue as https://github.com/coleifer/peewee/issues/207
with postgres however

I used the command
python -m pwiz -e postgresql -u USERNAME -P DATABASE -H HOST-p PORT-s SCHEMA> model.py

My model did not contain any "ForeignKeyField" 

Any thoughts? Does pwiz create foreign key for postgres?


Can you please tell me a bit more about your schema?  How are your foreign keys defined? 

If you're able to create a connection using peewee, e.g.:

``` python
db = PostgresqlDatabase('my_db', user='postgres')
db.connect()
```

Then you can try running the following to see what peewee is able to introspect:

``` python
for table in db.get_tables():
    print db.get_foreign_keys(table)
```


I ran the for loop and here's what I see:
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]

The schema was created in Postgres with foreign key constrains not sure how much to add on theh schema.


OK, so that's the issue (the empty lists)... What do you get when you open up a postgres shell and do a describe on the tables?

```
$ psql your_db
psql> \d+ my_table
```


Here's a sample of our schema with the foreign key definition

``` sql
-- ----------------------------
-- Sequence structure for PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "budget"."PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq";
CREATE SEQUENCE "budget"."PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 24
 CACHE 1;
SELECT setval('"budget"."PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq"', 24, true);

-- ----------------------------
-- Sequence structure for PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "budget"."PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq";
CREATE SEQUENCE "budget"."PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq"
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 3
 CACHE 1;
SELECT setval('"budget"."PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq"', 3, true);

;


-- ----------------------------
-- Table structure for PROJECT_TASK_CODES_LU
-- ----------------------------
DROP TABLE IF EXISTS "budget"."PROJECT_TASK_CODES_LU";
CREATE TABLE "budget"."PROJECT_TASK_CODES_LU" (
"PROJECT_TASK_CODE_ID" int4 DEFAULT nextval('"budget"."PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq"'::regclass) NOT NULL,
"FISCAL_YEAR" timestamp(6),
"PROJECT_CODE_LU_ID" int4,
"TASK_CODE" varchar(3) COLLATE "default",
"MONEY_TYPE_LU_ID" int4
)
WITH (OIDS=FALSE)

;
COMMENT ON COLUMN "budget"."PROJECT_TASK_CODES_LU"."PROJECT_TASK_CODE_ID" IS 'Get approximately 25 of these codes in a given year';
COMMENT ON COLUMN "budget"."PROJECT_TASK_CODES_LU"."FISCAL_YEAR" IS 'Fiscal year of the project task code in YYYY format';
COMMENT ON COLUMN "budget"."PROJECT_TASK_CODES_LU"."MONEY_TYPE_LU_ID" IS 'Type of Money - No Year Money, Two Year Money';

-- ----------------------------
-- Table structure for PROJECT_TASK_CODES_PROGRAMS
-- ----------------------------
DROP TABLE IF EXISTS "budget"."PROJECT_TASK_CODES_PROGRAMS";
CREATE TABLE "budget"."PROJECT_TASK_CODES_PROGRAMS" (
"PROJECT_TASK_CODES_PROGRAMS_ID" int4 DEFAULT nextval('"budget"."PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq"'::regclass) NOT NULL,
"PROJECT_TASK_CODE_ID" int4,
"PROGRAM_ID" int4,
"PROJECT_CODE_DESCRIPTION" varchar(255) COLLATE "default"
)
WITH (OIDS=FALSE)

;

-- ----------------------------
-- Alter Sequences Owned By 
-- ----------------------------
ALTER SEQUENCE "budget"."PROJECT_TASK_CODES_LU_PROJECT_TASK_CODE_ID_seq" OWNED BY "PROJECT_TASK_CODES_LU"."PROJECT_TASK_CODE_ID";
ALTER SEQUENCE "budget"."PROJECT_TASK_CODES_PROGRAMS_PROJECT_TASK_CODES_PROGRAMS_ID_seq" OWNED BY "PROJECT_TASK_CODES_PROGRAMS"."PROJECT_TASK_CODES_PROGRAMS_ID";


-- ----------------------------
-- Uniques structure for table PROJECT_TASK_CODES_LU
-- ----------------------------
ALTER TABLE "budget"."PROJECT_TASK_CODES_LU" ADD UNIQUE ("FISCAL_YEAR", "PROJECT_CODE_LU_ID", "TASK_CODE");

-- ----------------------------
-- Primary Key structure for table PROJECT_TASK_CODES_LU
-- ----------------------------
ALTER TABLE "budget"."PROJECT_TASK_CODES_LU" ADD PRIMARY KEY ("PROJECT_TASK_CODE_ID");

-- ----------------------------
-- Primary Key structure for table PROJECT_TASK_CODES_PROGRAMS
-- ----------------------------
ALTER TABLE "budget"."PROJECT_TASK_CODES_PROGRAMS" ADD PRIMARY KEY ("PROJECT_TASK_CODES_PROGRAMS_ID");


-- ----------------------------
-- Foreign Key structure for table "budget"."PROJECT_TASK_CODES_PROGRAMS"
-- ----------------------------
ALTER TABLE "budget"."PROJECT_TASK_CODES_PROGRAMS" ADD FOREIGN KEY ("PROJECT_TASK_CODE_ID") REFERENCES "budget"."PROJECT_TASK_CODES_LU" ("PROJECT_TASK_CODE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "budget"."PROJECT_TASK_CODES_PROGRAMS" ADD FOREIGN KEY ("PROGRAM_ID") REFERENCES "budget"."PROGRAMS_LU" ("PROGRAM_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
```


we didn't issue a psql command but rather dumped it via navicat 


+1


My best guess at the moment is that perhaps the postgresql schema name is not getting passed to the method that introspects foreign keys. I don't have a postgres server handy at the moment so I cannot test this myself, but if you were to try re-creating your tables in the "public" schema and running pwiz, I could quickly determine if my hypothesis is correct.


Yes I think you are right.....but I can't really create tables in my public schema. I'm working w/ an Admin to see if he can try it.

Strangely with peewee, without specifying a schema, queries and the model work great. 


@coleifer when hitting the public schema the FKs appeared in the model. Could you please check the methods in pwiz and see if the postgres schema can be passed to the method that introspects FKs? Would definitely help us out here :)


Yes! Thank you so much for testing my hypothesis out. I'll get a patch in today.


Well, now I'm confused...Are you all passing the schema in as a command-line argument when you run `pwiz`?

Locally, I created a schema named "nuggie" with two tables, one of which has a foreign key. Here's how they look:

```
peewee_test=# set search_path='nuggie';
peewee_test=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner   
--------+--------------+----------+----------
 nuggie | notes        | table    | postgres
 nuggie | notes_id_seq | sequence | postgres
 nuggie | users        | table    | postgres
 nuggie | users_id_seq | sequence | postgres
(4 rows)

peewee_test=# \d notes
                          Table "nuggie.notes"
 Column  |  Type   |                     Modifiers                      
---------+---------+----------------------------------------------------
 id      | integer | not null default nextval('notes_id_seq'::regclass)
 message | text    | 
 user_id | integer | not null
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
```

When I run pwiz like this:

``` console
$ pwiz -e postgresql -s nuggie peewee_test
```

Then the output contains the foreign key, even though it is on a different schema than "public":

``` python
class Users(BaseModel):
    username = CharField()

    class Meta:
        db_table = 'users'
        schema = 'nuggie'

class Notes(BaseModel):
    message = TextField(null=True)
    user = ForeignKeyField(db_column='user_id', rel_model=Users, to_field='id')

    class Meta:
        db_table = 'notes'
        schema = 'nuggie'
```

Are you sure there is a bug here?


Using your above SQL, I created the "budget" schema and ran the statements. When I ran pwiz on them, the foreign key was present:

``` console
$ pwiz -e postgresql -s budget peewee_test
```

``` python
class ProjectTaskCodesPrograms(BaseModel):
    program = IntegerField(db_column='PROGRAM_ID', null=True)
    project_code_description = CharField(db_column='PROJECT_CODE_DESCRIPTION', null=True)
    project_task_codes_programs = PrimaryKeyField(db_column='PROJECT_TASK_CODES_PROGRAMS_ID')
    project_task_code = ForeignKeyField(db_column='PROJECT_TASK_CODE_ID', null=True, rel_model=ProjectTaskCodesLu, to_field='project_task_code')

    class Meta:
        db_table = 'PROJECT_TASK_CODES_PROGRAMS'
        schema = 'budget'
```

I'm closing this, but please re-open if you find that my assessment was incorrect and there is a demonstrable bug.


I just ran 
"python -m pwiz -e postgresql -u Username -P Database -H Host -p Port -s budget > model.py"

again and it still doesn't produce the FKs.  Is there something wrong with my statement? The schema is in it


Umm, it looks right assuming you're passing in actual values for the username, password, host, and port...but you might try running it instead like this:

"pwiz.py -e postgresql -u postgres -P mypassword -s budget > models.py"

You don't need to specify the host and port if they are the default / running locally. If you don't need a password, you also don't need to specify that.


Yes I'm passing in actual values for username, password, host and port. I am VPNed and not running postgres locally. I tried removing Port but that didn't help. 

I tried running it like 

"pwiz.py -e postgresql -u USERNAME -P DATABASE -H HOST -p PORT-s SCHEMA> model.py"
but that just produces a blank file.

I only get data when I run it with python at the start
"python -m pwiz -e postgresql -u USERNAME -P DATABASE -H HOST -p Port -s SCHEMA > model.py"

Not sure what else could be the problem now


When you are passing "-P DATABASE" -- the "-P" is for password. Just want to double-check you are aware of that.

Can you try just running it with "pwiz.py" and _not_ pipe it to a script, but let it print to stdout in case there's an error or something?


Even with pwiz.py I get nothing, it just doesn't spit out anything.

So regarding the -P, I moved the DATABASE to the end of the command so ran it like this
python -m pwiz -e postgresql -u USERNAME -P -H HOST -p PORT -s SCHEMA DATABASE

and that didn't bring back the FKs either. Just wanted to add that whether I enter password after -P or not, I get asked to enter password when I run the command.  Meaning

python -m pwiz -e postgresql -u USERNAME -P PASSWORD -H HOST -p PORT -s SCHEMA DATABASE
results in being asked for password again.


I had the owner of the schema run the command and it worked for him but still doesn't work for me.

what permissions are needed on the schema to properly run pwiz?


I would note that Mak has full permissions on this schema, although he is not the owner (he and I are working together on this effort).


I'm sorry it's been a while since I replied. I am not familiar with Postgres enough to be able to answer your questions. Peewee queries `information_schema`, so I would assume the user running `pwiz` would need to be able to query those tables. Have you taken a look at peewee's `PostgresqlDatabase.get_tables()` and other introspection functions? Those contain the SQL used, which you could try running from a postgres shell.


@coleifer , does pwiz work on a foreign key that references a Postgres table in a **different** schema?
I'm having trouble not only generating a script with pwiz, but even getting the models to work.
How do you use ForeignKeyField on a table in a different schema in Postgres?

pwiz may not pick that up. However, fks to tables in different schema should not present a problem. The FK constraint will be created referencing the schema + table name. I've added a test-case: d6087e1effe46d3959be81e869cfab625ba763ac