Issue: pwiz.py and postgres - invalid field types
Author: appetito
State: closed
Created: 2014-01-13 19:46:07

## Db schema is:

```
                                  Table "public.categories"
  Column   |          Type          |                        Modifiers                        
-----------+------------------------+---------------------------------------------------------
 id        | integer                | not null default nextval('categories_id_seq'::regclass)
 parent_id | integer                | 
 name      | character varying(500) | 

                                   Table "public.products"
   Column    |          Type          |                       Modifiers                       
-------------+------------------------+-------------------------------------------------------
 id          | bigint                 | not null default nextval('products_id_seq'::regclass)
 name        | character varying(500) | 
 category_id | integer                | 
 type        | character varying(10)  | 
 price       | integer                | 
 bid         | integer                | 
 available   | boolean                | default false
 description | text                   | 
 picture     | character varying(500) | 
 author      | character varying(500) | 
 publisher   | character varying(500) | 
 year        | integer                | 
 isbn        | character varying(50)  | 
 binding     | character varying(500) | 
 page_extent | integer                | 
```
## Models  made by pwiz

```
class Categories(BaseModel):
    name = IntegerField(null=True)
    parent = ForeignKeyField(null=True, db_column='parent_id', rel_model='self')

    class Meta:
        db_table = 'categories'

class Products(BaseModel):
    author = IntegerField(null=True)
    available = CharField(null=True)
    bid = CharField(null=True)
    binding = CharField(max_length=500, null=True)
    category = ForeignKeyField(null=True, db_column='category_id', rel_model=Categories)
    description = TextField(null=True)
    isbn = IntegerField(null=True)
    name = CharField(max_length=500, null=True)
    page_extent = BigIntegerField(null=True)
    picture = BooleanField(null=True)
    price = CharField(null=True)
    publisher = IntegerField(null=True)
    type = IntegerField(null=True)
    year = CharField(null=True)

    class Meta:
        db_table = 'products'
```

bloody mess
peewee==2.1.7


I tested this by hand.  I typed in some models to recreate your schema (what pwiz _should_ have generated), then created the tables and ran pwiz against them.

``` python
# Models I typed in by hand:
class Category(BaseModel):
    name = CharField(max_length=500)
    parent = ForeignKeyField('self', null=True)

    class Meta:
        db_table = 'categories'

class Product(BaseModel):
    name = CharField(max_length=500)
    category = ForeignKeyField(Category)
    type = CharField(max_length=10)
    price = IntegerField()
    bid = IntegerField()
    available = BooleanField()
    description = TextField()
    picture = CharField(max_length=500)
    author = CharField(max_length=500)
    publisher = CharField(max_length=500)
    year = IntegerField()
    isbn = CharField(max_length=50)
    binding = CharField(max_length=500)
    page_extent = IntegerField()

    class Meta:
        db_table = 'products'
```

Here is what postgres shows me:

```
                                  Table "public.categories"
  Column   |          Type          |                        Modifiers                        
-----------+------------------------+---------------------------------------------------------
 id        | integer                | not null default nextval('categories_id_seq'::regclass)
 name      | character varying(500) | not null
 parent_id | integer                | 
Indexes:
    "categories_pkey" PRIMARY KEY, btree (id)
    "categories_parent_id" btree (parent_id)
Foreign-key constraints:
    "categories_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES categories(id)
Referenced by:
    TABLE "categories" CONSTRAINT "categories_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES categories(id)
    TABLE "products" CONSTRAINT "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

                                   Table "public.products"
   Column    |          Type          |                       Modifiers                       
-------------+------------------------+-------------------------------------------------------
 id          | integer                | not null default nextval('products_id_seq'::regclass)
 name        | character varying(500) | not null
 category_id | integer                | not null
 type        | character varying(10)  | not null
 price       | integer                | not null
 bid         | integer                | not null
 available   | boolean                | not null
 description | text                   | not null
 picture     | character varying(500) | not null
 author      | character varying(500) | not null
 publisher   | character varying(500) | not null
 year        | integer                | not null
 isbn        | character varying(50)  | not null
 binding     | character varying(500) | not null
 page_extent | integer                | not null
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
    "products_category_id" btree (category_id)
Foreign-key constraints:
    "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)
```

Here is what I get when I run pwiz against the generated tables:

``` python
class Categories(BaseModel):
    name = CharField(max_length=500)
    parent = ForeignKeyField(null=True, db_column='parent_id', rel_model='self')

    class Meta:
        db_table = 'categories'

class Products(BaseModel):
    author = CharField(max_length=500)
    available = BooleanField()
    bid = IntegerField()
    binding = CharField(max_length=500)
    category = ForeignKeyField(db_column='category_id', rel_model=Categories)
    description = TextField()
    isbn = CharField(max_length=50)
    name = CharField(max_length=500)
    page_extent = IntegerField()
    picture = CharField(max_length=500)
    price = IntegerField()
    publisher = CharField(max_length=500)
    type = CharField(max_length=10)
    year = IntegerField()

    class Meta:
        db_table = 'products'
```

As you can see, the generated code looks correct.  Can you dump your schema?  I'd like to see in more detail what CREATE statements were used to generate those models.  Also could you include your postgres and psycopg2 versions?


Also, could you run the following query and paste the output?

``` sql
select oid, typname from pg_type where typname not like '\_%' order by oid limit 50;
```


```
CREATE TABLE categories(
    id SERIAL PRIMARY KEY,
    parent_id INTEGER,
    name VARCHAR(500),
    FOREIGN KEY (parent_id) REFERENCES categories
);


CREATE TABLE products(
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(500),
    category_id INTEGER,
    type VARCHAR(10),
    price INTEGER,
    bid INTEGER,
    available BOOLEAN DEFAULT false,
    description TEXT,
    picture VARCHAR(500),
    author VARCHAR(500),
    publisher VARCHAR(500),
    year INTEGER,
    isbn VARCHAR(50),
    binding VARCHAR(500),
    page_extent INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories
);
```

```
select oid, typname from pg_type where typname not like '\_%' order by oid limit 50;

 oid  |   typname    
------+--------------
   16 | bool
   17 | bytea
   18 | char
   19 | name
   20 | int8
   21 | int2
   22 | int2vector
   23 | int4
   24 | regproc
   25 | text
   26 | oid
   27 | tid
   28 | xid
   29 | cid
   30 | oidvector
   71 | pg_type
   75 | pg_attribute
   81 | pg_proc
   83 | pg_class
  142 | xml
  194 | pg_node_tree
  210 | smgr
  600 | point
  601 | lseg
  602 | path
  603 | box
  604 | polygon
  628 | line
  650 | cidr
  700 | float4
  701 | float8
  702 | abstime
  703 | reltime
  704 | tinterval
  705 | unknown
  718 | circle
  790 | money
  829 | macaddr
  869 | inet
 1033 | aclitem
 1042 | bpchar
 1043 | varchar
 1082 | date
 1083 | time
 1114 | timestamp
 1184 | timestamptz
 1186 | interval
 1248 | pg_database
 1266 | timetz
 1560 | bit
(50 rows)
```

psycopg2==2.5.2
postgres (PostgreSQL) 9.1.11


My list matches yours (with the exception of the JSON type since I'm using 9.3).  Not sure, do you have any ideas what might be going on?  It's extra strange that some of the varchar() columns are recognized correctly (products.name) while others are not (products.author).

Do you have any other tables in this database?  Is there any chance you could give me a dump of the schema only?


Using the `CREATE TABLE` statements you provided, I created the tables locally and ran pwiz -- the output looked correct:

``` python
class Categories(BaseModel):
    name = CharField(max_length=500, null=True)
    parent = ForeignKeyField(null=True, db_column='parent_id', rel_model='self')

    class Meta:
        db_table = 'categories'

class Products(BaseModel):
    author = CharField(max_length=500, null=True)
    available = BooleanField(null=True)
    bid = IntegerField(null=True)
    binding = CharField(max_length=500, null=True)
    category = ForeignKeyField(null=True, db_column='category_id', rel_model=Categories)
    description = TextField(null=True)
    isbn = CharField(max_length=50, null=True)
    name = CharField(max_length=500, null=True)
    page_extent = IntegerField(null=True)
    picture = CharField(max_length=500, null=True)
    price = IntegerField(null=True)
    publisher = CharField(max_length=500, null=True)
    type = CharField(max_length=10, null=True)
    year = IntegerField(null=True)

    class Meta:
        db_table = 'products'
```


i must look closer, thanks


Please reopen if you find anything or have any new info.