Issue: pwiz.py SqliteIntrospector.get_foreign_keys
Author: mattjvincent
State: closed
Created: 2014-09-12 15:34:16

Some SQLite databases will return EOL characters in the following query:

```
SELECT sql
            FROM sqlite_master
            WHERE (tbl_name = ? AND type = ?)
```

This makes it so foreign keys don't work.  To fix change line **442 in pwiz.py**.  

```
def get_foreign_keys(self, table, schema=None):
    query = """
        SELECT sql
        FROM sqlite_master
        WHERE (tbl_name = ? AND type = ?)"""
    cursor = self.execute(query, table, 'table')
    #LINE 442 table_definition = cursor.fetchone()[0].strip()
    table_definition = ''.join(map(lambda x: str(x[0]).replace('\r\n', ' ').replace('\n', ' ').strip(), cursor.fetchall()))
```

My test was:

File: player.sql     

```
DROP TABLE IF EXISTS player;
CREATE TABLE player (
    _key INTEGER PRIMARY KEY,
    player_id TEXT NOT NULL,
    name TEXT NOT NULL
);
CREATE UNIQUE INDEX player_idx_id ON player (player_id ASC);

DROP TABLE IF EXISTS player_options;
CREATE TABLE player_options (
    _key INTEGER PRIMARY KEY,
    player_id TEXT NOT NULL,
    option_key TEXT NOT NULL,
    option_value TEXT NOT NULL,
    FOREIGN KEY (player_id) REFERENCES player (player_id)
);
CREATE INDEX player_options_idx_player ON player_options (player_id ASC);
```

Than from the command line:

```
sqlite3 player.db3 < player.sql

pwiz.py -e sqlite player.db3 > PlayerModels.py
```

Using the old version of line 442 gives the following output:

**Unable to read table definition for "player"**

```
from peewee import *

database = SqliteDatabase('player.db3', **{})

class UnknownFieldType(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Player(BaseModel):
    _key = PrimaryKeyField(null=True)
    name = TextField()
    player = TextField(db_column='player_id')

    class Meta:
        db_table = 'player'

class Player_Options(BaseModel):
    _key = PrimaryKeyField(null=True)
    option_key = TextField()
    option_value = TextField()
    player = TextField(db_column='player_id')

    class Meta:
        db_table = 'player_options'
```

However, the new version gives:

```
from peewee import *

database = SqliteDatabase('player.db3', **{})

class UnknownField(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Player(BaseModel):
    _key = PrimaryKeyField(null=True)
    name = TextField()
    player = TextField(db_column='player_id')

    class Meta:
        db_table = 'player'

class PlayerOptions(BaseModel):
    _key = PrimaryKeyField(null=True)
    option_key = TextField()
    option_value = TextField()
    player = ForeignKeyField(db_column='player_id', rel_model=Player)

    class Meta:
        db_table = 'player_options'
```


Hmmm... it seems also that ForeignKeyField have to be integers?  I usually design my databases with keys being integers, but that's not always going to be the case.  See above example.  When populated and trying to use, errors occur trying to coerce to be ints.

```
/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in get(self)
   2381         clone = self.paginate(1, 1)
   2382         try:
-> 2383             return clone.execute().next()
   2384         except StopIteration:
   2385             raise self.model_class.DoesNotExist(

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in next(self)
   1684             return inst
   1685 
-> 1686         obj = self.iterate()
   1687         self._result_cache.append(obj)
   1688         self.__ct += 1

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in iterate(self)
   1672             self.initialize(self.cursor.description)
   1673             self._initialized = True
-> 1674         return self.process_row(row)
   1675 
   1676     def iterator(self):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in process_row(self, row)
   1744         instance = self.model()
   1745         for i, column, func in self.conv:
-> 1746             setattr(instance, column, func(row[i]))
   1747         instance.prepared()
   1748         return instance

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in python_value(self, value)
    676     def python_value(self, value):
    677         """Convert the database value to a pythonic value."""
--> 678         return value if value is None else self.coerce(value)
    679 
    680     def _as_entity(self, with_table=False):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in coerce(self, value)
   1045 
   1046     def coerce(self, value):
-> 1047         return self.to_field.coerce(value)
   1048 
   1049     def db_value(self, value):

ValueError: invalid literal for int() with base 10: 'example_player_1'
```


> it seems also that ForeignKeyField have to be integers?

No, but if your foreign key references a column other than the primary key, you need to specify it using `to_field`: http://peewee.readthedocs.org/en/latest/peewee/api.html#ForeignKeyField


Yes, but doesn't ForeignKeyField inherit from IntegerField?


It does, but it will derive its behavior (and column definition) from the column it relates to. So if it points to a `TEXT` column, it will behave like one.


Ok, but pwiz.py should still be changed to use the `to_field`...

```
sqlite3 player.db "insert into player values (1, 'matt', 'matt v');"
sqlite3 player.db "insert into player values (2, 'zach', 'zach v');"
sqlite3 player.db "insert into player options(1, 'matt', 'foo', 'bar');"
sqlite3 player.db "insert into player options(1, 'matt', 'foo2', 'bar2');"
sqlite3 player.db "insert into player options(3, 'matt', 'foo3', 'bar3');"
sqlite3 player.db "insert into player options(4, 'zach', 'foo', 'bar');"
```

pwiz.py -e sqlite player.db >player.py

```
from peewee import *

database = SqliteDatabase('player.db3', **{})

class UnknownField(object):
    pass

class BaseModel(Model):
    class Meta:
        database = database

class Player(BaseModel):
    _key = PrimaryKeyField(null=True)
    name = TextField()
    player = TextField(db_column='player_id')

    class Meta:
        db_table = 'player'

class PlayerOptions(BaseModel):
    _key = PrimaryKeyField(null=True)
    option_key = TextField()
    option_value = TextField()
    player = ForeignKeyField(db_column='player_id', rel_model=Player)

    class Meta:
        db_table = 'player_options'
```

Than ipython:

```

In [1]: import player

In [2]: player.PlayerOptions.select().where((player.PlayerOptions.player == 'matt')).get()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-2-54331d4f3326> in <module>()
----> 1 player.PlayerOptions.select().where((player.PlayerOptions.player == 'matt')).get()

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in get(self)
   2381         clone = self.paginate(1, 1)
   2382         try:
-> 2383             return clone.execute().next()
   2384         except StopIteration:
   2385             raise self.model_class.DoesNotExist(

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in execute(self)
   2422             else:
   2423                 ResultWrapper = ModelQueryResultWrapper
-> 2424             self._qr = ResultWrapper(model_class, self._execute(), query_meta)
   2425             self._dirty = False
   2426             return self._qr

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in _execute(self)
   2117 
   2118     def _execute(self):
-> 2119         sql, params = self.sql()
   2120         return self.database.execute_sql(sql, params, self.require_commit)
   2121 

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in sql(self)
   2396 
   2397     def sql(self):
-> 2398         return self.compiler().generate_select(self)
   2399 
   2400     def verify_naive(self):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in generate_select(self, query, alias_map)
   1458             clauses.append(SQL(stmt))
   1459 
-> 1460         return self.build_query(clauses, alias_map)
   1461 
   1462     def generate_update(self, query):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in build_query(self, clauses, alias_map)
   1355 
   1356     def build_query(self, clauses, alias_map=None):
-> 1357         return self.parse_node(Clause(*clauses), alias_map)
   1358 
   1359     def generate_joins(self, joins, model_class, alias_map):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in parse_node(self, node, alias_map, conv)
   1316 
   1317     def parse_node(self, node, alias_map=None, conv=None):
-> 1318         sql, params, unknown = self._parse(node, alias_map, conv)
   1319         if unknown and conv and params:
   1320             params = [conv.db_value(i) for i in params]

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in _parse(self, node, alias_map, conv)
   1291         unknown = False
   1292         if node_type in self._parse_map:
-> 1293             sql, params = self._parse_map[node_type](node, alias_map, conv)
   1294             unknown = node_type in self._unknown_types
   1295         elif isinstance(node, (list, tuple)):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in _parse_clause(self, node, alias_map, conv)
   1244     def _parse_clause(self, node, alias_map, conv):
   1245         sql, params = self.parse_node_list(
-> 1246             node.nodes, alias_map, conv, node.glue)
   1247         if node.parens:
   1248             sql = '(%s)' % self._clean_extra_parens(sql)

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in parse_node_list(self, nodes, alias_map, conv, glue)
   1333         params = []
   1334         for node in nodes:
-> 1335             node_sql, node_params = self.parse_node(node, alias_map, conv)
   1336             sql.append(node_sql)
   1337             params.extend(node_params)

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in parse_node(self, node, alias_map, conv)
   1316 
   1317     def parse_node(self, node, alias_map=None, conv=None):
-> 1318         sql, params, unknown = self._parse(node, alias_map, conv)
   1319         if unknown and conv and params:
   1320             params = [conv.db_value(i) for i in params]

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in _parse(self, node, alias_map, conv)
   1291         unknown = False
   1292         if node_type in self._parse_map:
-> 1293             sql, params = self._parse_map[node_type](node, alias_map, conv)
   1294             unknown = node_type in self._unknown_types
   1295         elif isinstance(node, (list, tuple)):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in _parse_expression(self, node, alias_map, conv)
   1225             conv = node.lhs
   1226         lhs, lparams = self.parse_node(node.lhs, alias_map, conv)
-> 1227         rhs, rparams = self.parse_node(node.rhs, alias_map, conv)
   1228         template = '%s %s %s' if node.flat else '(%s %s %s)'
   1229         sql = template % (lhs, self.get_op(node.op), rhs)

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in parse_node(self, node, alias_map, conv)
   1318         sql, params, unknown = self._parse(node, alias_map, conv)
   1319         if unknown and conv and params:
-> 1320             params = [conv.db_value(i) for i in params]
   1321 
   1322         if isinstance(node, Node):

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in db_value(self, value)
   1050         if isinstance(value, self.rel_model):
   1051             value = value.get_id()
-> 1052         return self.to_field.db_value(value)
   1053 
   1054 

/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/peewee.pyc in db_value(self, value)
    672     def db_value(self, value):
    673         """Convert the python value for storage in the database."""
--> 674         return value if value is None else self.coerce(value)
    675 
    676     def python_value(self, value):

ValueError: invalid literal for int() with base 10: 'matt'

In [3]: 
```

So coercing takes place since pwiz.py does not use the fact that Player.player is a `TEXT` field when creating the "model".

Does that make sense?

Did the change on line 442 also make sense?

Thanks! Love the library thus far!


Seems like adding a `to_field` key and value where the if condition on line would work.

```
if self.field_class is ForeignKeyField:
             params['rel_model'] = self.rel_model
```

Just have to run and don't have time to see what's available to grab from right now. I'm sure you know how better than I.


75981c4 fixes both the multi-line issue and the `to_field` issue.


What's the rationale for removing the '_id' in the column_names?  I'm having breakage with the following...

game.sql

```
CREATE TABLE ball_player (
    _key INTEGER PRIMARY KEY,
    ball_player_id TEXT NOT NULL,
    name TEXT NOT NULL
);
CREATE UNIQUE INDEX ball_player_idx ON ball_player (ball_player_id ASC);

CREATE TABLE game_option (
    _key INTEGER PRIMARY KEY,
    game_option_id TEXT NOT NULL,
    name TEXT NOT NULL
);
CREATE UNIQUE INDEX game_option_idx ON game_option (game_option_id ASC);

CREATE TABLE ball_player_game_options (
    _key INTEGER PRIMARY KEY,
    ball_player_id TEXT NOT NULL,
    game_option_id TEXT NOT NULL,
    value TEXT NOT NULL,
    FOREIGN KEY (ball_player_id) REFERENCES ball_player (ball_player_id),
    FOREIGN KEY (game_option_id) REFERENCES game_option (game_option_id)
);
CREATE UNIQUE INDEX ball_player_game_options_idx ON ball_player_game_options (ball_player_id, game_option_id ASC);
```

Next:

```
sqlite3 game.db3 < game.sql
```

Than

```
ipython

Python 2.7.6 (v2.7.6:3a1db0d2747e, Nov 10 2013, 00:42:54) 
Type "copyright", "credits" or "license" for more information.

IPython 2.1.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: import game.py

AttributeError: type object 'GameOption' has no attribute 'game_option_id'
```


This appears to be a bug.

On Mon, Sep 22, 2014 at 11:12 AM, Matt Vincent notifications@github.com
wrote:

> What's the rational for removing the '_id' in the column_names? I'm having
> breakage with the following...
> 
> game.sql
> 
> CREATE TABLE ball_player (
>     _key INTEGER PRIMARY KEY,
>     ball_player_id TEXT NOT NULL,
>     name TEXT NOT NULL
> );
> CREATE UNIQUE INDEX ball_player_idx ON ball_player (ball_player_id ASC);
> 
> CREATE TABLE game_option (
>     _key INTEGER PRIMARY KEY,
>     game_option_id TEXT NOT NULL,
>     name TEXT NOT NULL
> );
> CREATE UNIQUE INDEX game_option_idx ON game_option (game_option_id ASC);
> 
> CREATE TABLE ball_player_game_options (
>     _key INTEGER PRIMARY KEY,
>     ball_player_id TEXT NOT NULL,
>     game_option_id TEXT NOT NULL,
>     value TEXT NOT NULL,
>     FOREIGN KEY (ball_player_id) REFERENCES ball_player (ball_player_id),
>     FOREIGN KEY (game_option_id) REFERENCES game_option (game_option_id)
> );
> CREATE UNIQUE INDEX ball_player_game_options_idx ON ball_player_game_options (ball_player_id, game_option_id ASC);
> 
> Next:
> 
> sqlite3 game.db3 < game.sql
> 
> Than
> 
> ipython
> 
> Python 2.7.6 (v2.7.6:3a1db0d2747e, Nov 10 2013, 00:42:54)
> Type "copyright", "credits" or "license" for more information.
> 
> IPython 2.1.0 -- An enhanced Interactive Python.
> ?         -> Introduction and overview of IPython's features.
> %quickref -> Quick reference.
> help      -> Python's own help system.
> object?   -> Details about 'object', use 'object??' for extra details.
> 
> In [1]: import game.py
> 
> AttributeError: type object 'GameOption' has no attribute 'game_option_id'
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/426#issuecomment-56397634.


Fixed for real in c7d538e216c4c896c95a8cfed9fbd5454506dacc