On Aug 24, 2005, at 1:03 AM, Oleg Broytmann wrote:

On Tue, Aug 23, 2005 at 03:42:10PM -0700, Ben Bangert wrote:

AssertionError: No primary key found in table 'college.signups'


   The error message says it pretty clear - make your id column a PRIMARY
KEY!

I thought I was clear, its the same EXACT table def, only under a postgresql database schema, vs under the "public" schema. It works under the "public" schema but not a user-defined schema. There is a primary key present, but SQLObject is not capable of figuring out it should be looking under a schema and table for the primary key, vs just the table. I'm sorry the term schema is getting so confused here, when I say Postgresql Schema, I'm referring to this ability of Postgres for organizing multiple sets of tables under "Schemas" under the same database:
http://www.postgresql.org/docs/8.0/static/ddl-schemas.html

I in fact just tested this with actual stuff to be sure. I'm reasonably positive its a SQLObject bug at this point regarding locating a primary key ID under Postgres schemas.

Here are two schemas, the sole difference being that one of them uses a Postgresql schema organization. I will assume someone testing these can setup their own postgres database.

SQLObject files:
#author schema WITHOUT postgres schema
class Author(SQLObject):
    _fromDatabase = True

#author schema WITH postgres schema
class Author(SQLObject):
    _table = 'boo.author'
    _fromDatabase = True

Now here are the two different SQL statements needed to create each:
#SQL WITHOUT postgres schema
CREATE TABLE author (
       id serial PRIMARY KEY,
       login varchar(8) NOT NULL,
       password varchar(40) NOT NULL,
       name varchar(30) NOT NULL
);

#SQL WITH postgres schema
CREATE SCHEMA boo;
CREATE TABLE boo.author (
       id serial PRIMARY KEY,
       login varchar(8) NOT NULL,
       password varchar(40) NOT NULL,
       name varchar(30) NOT NULL
);

Please note, they have primary keys, regardless to what SQLObject knows or doesn't know. Technically, both of these should be able to be run on a database, without any problems, because the first one puts the tables under the default, aka "public" schema in Postgresql, whereas the second one puts the table under the boo schema.

Now, if you run the 1st SQL into a db, and load the first SQLObject schema, there's no issues, it looks like this:
>>> from odb import *
>>>
Looks good, no problems.

Now I clear out the database, and load the second SQL set, and load up the second SQLObject schema, to get this:
>>> from schemadb import *
schemadb.py:5: DeprecationWarning: '_table' is deprecated; please set the 'table' attribute in sqlmeta instead
  class Author(SQLObject):
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "schemadb.py", line 5, in ?
    class Author(SQLObject):
  File "/usr/local/lib/python2.3/site-packages/sqlobject/declarative.py", line 94, in __new__
    cls.__classinit__(cls, new_attrs)
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 468, in __classinit__
    cls.addColumnsFromDatabase()
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 674, in addColumnsFromDatabase
    for columnDef in conn.columnsFromSchema(cls.sqlmeta.table, cls):
  File "/usr/local/lib/python2.3/site-packages/sqlobject/postgres/pgconnection.py", line 196, in columnsFromSchema
    assert primaryKey, "No primary key found in table %r" % tableName
AssertionError: No primary key found in table 'boo.author'

So it's obviously not looking for boo.id, its looking for id which of course doesn't exist. I am 100% positive there is a primary key, as this is what Postgresql prompt tells me:
pyblog=> \d boo.
                                   Table "boo.author"
  Column  |         Type          |                      Modifiers                     
----------+-----------------------+-----------------------------------------------------
id       | integer               | not null default nextval('boo.author_id_seq'::text)
login    | character varying(8)  | not null
password | character varying(40) | not null
name     | character varying(30) | not null
Indexes:
    "author_pkey" PRIMARY KEY, btree (id)

Sequence "boo.author_id_seq"
    Column     |  Type  
---------------+---------
sequence_name | name
last_value    | bigint
increment_by  | bigint
max_value     | bigint
min_value     | bigint
cache_value   | bigint
log_cnt       | bigint
is_cycled     | boolean
is_called     | boolean

Index "boo.author_pkey"
Column |  Type  
--------+---------
id     | integer
primary key, btree, for table "boo.author"


Now, on top of this all, SQLObject also throws up if you should load both of these. If you load the first SQL, and the second, importing the first SQLObject schema gets you to this odd message:

>>> from odb import *
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
  File "odb.py", line 5, in ?
    class Author(SQLObject):
  File "/usr/local/lib/python2.3/site-packages/sqlobject/declarative.py", line 94, in __new__
    cls.__classinit__(cls, new_attrs)
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 468, in __classinit__
    cls.addColumnsFromDatabase()
  File "/usr/local/lib/python2.3/site-packages/sqlobject/main.py", line 674, in addColumnsFromDatabase
    for columnDef in conn.columnsFromSchema(cls.sqlmeta.table, cls):
  File "/usr/local/lib/python2.3/site-packages/sqlobject/postgres/pgconnection.py", line 194, in columnsFromSchema
    assert primaryKey is None, "Already found primary key (%r), then found: %r" % (primaryKey, indexDef)
AssertionError: Already found primary key ('id'), then found: 'CREATE UNIQUE INDEX author_pkey ON boo.author USING btree (id)'

I'll be submitting some patches containing unit tests exposing this bug tonight hopefully if I have some time.

- Ben