sqlite PK autoincrement not working when you do a composite PK?
Russell Warren
works great (and the PK auto increments nicely):
users_table = sa.Table("users", metadata,
sa.Column("id", sa.Integer, primary_key = True),
sa.Column("login", sa.String(40), nullable = False),
sa.Column("password", sa.String(40), nullable = False),
<etc>
but then I realized I want that to be a composite PK with id and login
together, so I change the definition to this:
users_table = sa.Table("users", metadata,
sa.Column("id", sa.Integer, primary_key = True, autoincrement =
True),
sa.Column("login", sa.String(40), primary_key = True),
sa.Column("password", sa.String(40), nullable = False),
<etc>
and now the autoincrement on id no longer works. With the latter,
when I try and add a simple record that worked before I now get:
"IntegrityError: (IntegrityError) users.id may not be NULL u'INSERT
INTO users (login, password, <etc>"
I've tried sifting through the sqlite dialect to figure out what is
going on and have even tried forcing supports_pk_autoincrement to be
true, but it rapidly became clear I hadn't a clue what I was doing in
the sqlalchemy guts.
Does anyone know why autoincrement on "id" stopped working, and how I
can fix it?
Michael Bayer
On May 31, 2008, at 12:38 AM, Russell Warren wrote:
>
> I've tried sifting through the sqlite dialect to figure out what is
> going on and have even tried forcing supports_pk_autoincrement to be
> true, but it rapidly became clear I hadn't a clue what I was doing in
> the sqlalchemy guts.
>
> Does anyone know why autoincrement on "id" stopped working, and how I
> can fix it?
this is not a SQLA behavior, its a known behavior of sqlite3 - its
"autoincrement" feature ceases to work with a composite primary key.
I've never known any workaround for it (with the possible exception of
declaring the second column in the PK as "unique", instead of it
actually being part of the PK).
Michael Bayer
this behavior in SQLite will never change:
http://www.sqlite.org/cvstrac/tktview?tn=2553
Russell Warren
> this behavior in SQLite will never change:
>
Yow - that is a pretty terse slapdown! It doesn't seem like sqlite
will ever support it.
I keep hoping that sqlalchemy can be abstract enough that it will
enable the use of any database backend. Stuff like this sqlite
composite PK hiccup is discouraging, but I'm convinced there is a
workaround to make this work! I don't care if it is the fastest way,
it just has to work.
I'm trying to get this to work with logic along these lines:
1. Identify in SQLiteCompiler.visit_insert when a primary key is
missing from the insert (insert_stmt)
2. If the missing key is tagged as autoincrement, auto-add it to the
insert object with a bogus value before the insert is processed (and
flag it for use later when working with the execution context)
3. When subbing the variables into the INSERT statement later, replace
the bogus value with something like: "(SELECT max(id) FROM user)+1"
It seems somewhat reasonable in principle to me, but the problems I'm
having in reality are:
1. How do I override SQLiteCompiler.visit_insert without modifying
SQLA's sqlite.py? I of course want to avoid trashing the base SQLA
install, but can't find an override location in the object tree from
my session or engine or anything.
2. Even if I could find a way to override visit_insert, I'm having
trouble locating a location to stuff the "select max" code in place.
Tweaking the statement by creating an SQLiteDialect.do_execute
implementation seems like it might work, but it also doesn't seem lke
the cleanest way.
3. What internal SQLA structures can I count on staying fixed through
revisions? eg: in visit_insert I can use self._get_colparams to
figure out what columns have been requested, and I can use
insert_stmt.table._columns to figure out what primary key is missing
(and if it is supposed to be autoincrement). But I don't know which
of those I can actually count on being there in the future! Plus,
crawling around internal objects like this just seems like a bad idea.
Any help is appreciated. I expect I'm in over my head trying to mess
with a dialect implementation. I'm also worried that this will just
be the first of many things like this I'll be trying to overcome to
get SQLA to truly abstract the database implementations away...
And a related question: What is the general feeling on how well SQLA
abtstracts the underlying database away? Am I expecting too much to
be able to write my application using SQLA-only from the beginning and
have it work on any of the popular databases without much tweaking?
a...@svilendobrev.com
> SQLA's sqlite.py? I of course want to avoid trashing the base SQLA
> install, but can't find an override location in the object tree
> from my session or engine or anything.
a) inherit the class, replace whatever, use the new version - works
if it is just you using the new-stuff
b) complete replacement: import thatclass; thatclass.method =
your-own-version
c) partial hacks: inspect.get_source( that method); replace some
lines in that with yours; compile; replace the method with the new
version. this works if u have sources; if its just *.pyc, sorry.
i have all of these applied to various pieces of SAcode;
as for c) i have one to niceify the select-echos and another to
replace dicts with ordered ones for repeatability - as michael has
not accepted patches/idea of these, i'm patching them myself. The
echoing one is really useful. see dbcook.util.hacksrc and it's usage
in dbcook.usage.sa_hack4repeatability and sa_hack4echo
https://dbcook.svn.sourceforge.net/svnroot/dbcook/trunk/dbcook/
http://pypi.python.org/pypi/dbcook/0.2
a...@svilendobrev.com
> SQLA abtstracts the underlying database away? Am I expecting too
> much to be able to write my application using SQLA-only from the
> beginning and have it work on any of the popular databases without
> much tweaking?
specific SQldialect notions, or better, on specific SQL notions,
you'r settled.
i've made dbcook over SA and ever since the team have forgotten about
what SQL is, except some veeeeery tricky things which has to be SQL
aware, as they rely on DB-structure being what it is. But dialects...
only come to play when something is not supported, and my way of
handling this so far is to avoid using any stuff that is not
supported everywhere - workaround on lowe or higher level, including
model refactoring.
a...@svilendobrev.com
> through revisions?
so just do it, and keep doors opened for being version-aware (or
actualy make them later).
i have a lot of this stuff, look in the dbcook sources. e.g. after
rev260 i've whacked 0.3/0.4beta1 support out.
Egil Möller
> YMMV. it is actualy you who break things. e.g. if u dont rely much on
> specific SQldialect notions, or better, on specific SQL notions,
> you'r settled.
> i've made dbcook over SA and ever since the team have forgotten about
> what SQL is, except some veeeeery tricky things which has to be SQL
> aware, as they rely on DB-structure being what it is. But dialects...
> only come to play when something is not supported, and my way of
> handling this so far is to avoid using any stuff that is not
> supported everywhere - workaround on lowe or higher level, including
> model refactoring
I don't think that is a very workable strategy in the long run :( There
are far to many bogus restrictions in some databases, e.g. Oracle, for
any meaningful program to
be written to work on all platforms w/o support/wrapping/hiding of ugly
details by SA.
I and a coworker are currently working on a patch-set to the oracle
driver for SA for this very reason, fixing issues like:
* broken mangling of forbidden/to long table/column names
* missing support for the BOOL data type
* missing support for boolean expressions in the column list (
select([tbl.c.col1 == tbl.c.col2]) ) (related to the last one above)
You might think that you could easily get around the name-length barrier
using the shortnames-option. But SA combines table names with column
names to form aliases in select column lists, and the length quickly
exceeds 32 characters (Oracles limit). In addition, do _you_ know which
words are forbidden as column names in Oracle? I can assure you that
there is at least a few you don't remember (and I wouldn't remember
either :P)...
Just a point of measurement (ok, oracle is the worst one...)
Best regards,
Egil
Paul Johnston
I don't think that is a very workable strategy in the long run :( There
are far to many bogus restrictions in some databases, e.g. Oracle, for
any meaningful program to
be written to work on all platforms w/o support/wrapping/hiding of ugly
details by SA.
1) Only expose functionality that exists on all the implementations
2) Expose the user to the slight differences between implementations
3) Expose consistent functionality, and where an implementation lacks support, fake it
In practice, (1) is usually a poor option as it's too restrictive. SQLAlchemy currently takes approach (2). There is definitely consistency merit for approach (3), but it comes at a cost - there's more "magic" going on, which could be confusing in some circumstances.
Paul
a...@svilendobrev.com
support/settings, let him take the decision - preferences-like; e.g.
a widget with a set of general attributes and several sets of
implementation-dependent extra attributes, switched depending on
implementation - the user can setup them all.
well maybe i got a mix of all them 3, for different aspects.
e.g. if oracle db will disallow me to use my 50-long names, i'll
mangle them somewhere in the middle, but will not allow such
meaningless restriction to cripple all the model above.
while for other things i just surrender and dont use the features...
Michael Bayer
On Jun 13, 2008, at 1:00 AM, Russell Warren wrote:
>
> Any help is appreciated. I expect I'm in over my head trying to mess
> with a dialect implementation. I'm also worried that this will just
> be the first of many things like this I'll be trying to overcome to
> get SQLA to truly abstract the database implementations away...
>
> And a related question: What is the general feeling on how well SQLA
> abtstracts the underlying database away? Am I expecting too much to
> be able to write my application using SQLA-only from the beginning and
> have it work on any of the popular databases without much tweaking?
>
if you'd like to specify a value generator for the columns, just use a
ColumnDefault. Whatever function or SQL you like will be called if no
value is present - its just in this case we can't rely upon SQLite's
OID generation.
I wouldn't favor a built in system of "guessing" within the sqlite
dialect how to autoincrement a composite PK field without explicit
user intervention. The dialects don't intend to build a completely
uniform layer over all database backends (for example, when using
Oracle, you are required to set up a default generator, usually a
Sequence, in all cases) - the idea of a dialect's default behavior is
that it uses what the database backend provides by default and that's
it.
Michael Bayer
On Jun 13, 2008, at 3:58 AM, Egil Möller wrote:
> I and a coworker are currently working on a patch-set to the oracle
> driver for SA for this very reason, fixing issues like:
>
> * broken mangling of forbidden/to long table/column names
really ? we have a lot of tests which pass fine for that, including
when aliases are created, etc. In compiler.py, all names go through
the same "length" filter no matter how they got generated (the only
exception to this is the "too long index names" ticket which is
strictly a schema thing). We have a "long labels" test specifically
for this, and lots of ORM tests generate very long names as well (all
of which work fine with Oracle). We did a tremendous amount of
development on this a few years back and noone has had issues since.
can you post a ticket with an example ? Also if producing fixes,
keep in mind theres some compiler differences between 0.4 and 0.5, 0.5
is the direction we're heading....
>
> * missing support for the BOOL data type
there may or may not be a ticket for this (please post one if not)
>
> * missing support for boolean expressions in the column list (
> select([tbl.c.col1 == tbl.c.col2]) ) (related to the last one above)
ditto
Michael Bayer
python test/orm/inheritance/query.py --log-debug=sqlalchemy.engine --
db oracle PolymorphicUnionsTest.test_primary_eager_aliasing
When you run on SQLite, one of the queries is:
SELECT anon_1.people_person_id AS anon_1_people_person_id,
anon_1.people_company_id AS anon_1_people_company_id,
anon_1.people_name AS anon_1_people_name, anon_1.people_type AS
anon_1_people_type, anon_1.engineers_person_id AS
anon_1_engineers_person_id, anon_1.engineers_status AS
anon_1_engineers_status, anon_1.engineers_engineer_name AS
anon_1_engineers_engineer_name, anon_1.engineers_primary_language AS
anon_1_engineers_primary_language, anon_1.managers_person_id AS
anon_1_managers_person_id, anon_1.managers_status AS
anon_1_managers_status, anon_1.managers_manager_name AS
anon_1_managers_manager_name, anon_1.boss_boss_id AS
anon_1_boss_boss_id, anon_1.boss_golf_swing AS anon_1_boss_golf_swing,
machines_1.machine_id AS machines_1_machine_id, machines_1.name AS
machines_1_name, machines_1.engineer_id AS machines_1_engineer_id
FROM (SELECT people.person_id AS people_person_id, people.company_id
AS people_company_id, people.name AS people_name, people.type AS
people_type, engineers.person_id AS engineers_person_id,
engineers.status AS engineers_status, engineers.engineer_name AS
engineers_engineer_name, engineers.primary_language AS
engineers_primary_language, managers.person_id AS managers_person_id,
managers.status AS managers_status, managers.manager_name AS
managers_manager_name, boss.boss_id AS boss_boss_id, boss.golf_swing
AS boss_golf_swing
FROM people LEFT OUTER JOIN engineers ON people.person_id =
engineers.person_id LEFT OUTER JOIN managers ON people.person_id =
managers.person_id LEFT OUTER JOIN boss ON managers.person_id =
boss.boss_id ORDER BY people.person_id
LIMIT 2 OFFSET 1) AS anon_1 LEFT OUTER JOIN machines AS machines_1
ON anon_1.engineers_person_id = machines_1.engineer_id ORDER BY
anon_1.people_person_id, machines_1.oid
of note is the anonymous label "anon_1_engineers_primary_language", 34
characters. This label is generated from an anonymous alias name
combined with a column name, which is itself a combination of the
original table name and column name. So theres three stages of name
generation represented here.
Here it is on oracle, including the result rows:
SELECT anon_1.people_person_id AS anon_1_people_person_id,
anon_1.people_company_id AS anon_1_people_company_id,
anon_1.people_name AS anon_1_people_name, anon_1.people_type AS
anon_1_people_type, anon_1.managers_person_id AS
anon_1_managers_person_id, anon_1.managers_status AS
anon_1_managers_status, anon_1.managers_manager_name AS
anon_1_managers_manager_name, anon_1.boss_boss_id AS
anon_1_boss_boss_id, anon_1.boss_golf_swing AS anon_1_boss_golf_swing,
anon_1.engineers_person_id AS anon_1_engineers_person_id,
anon_1.engineers_status AS anon_1_engineers_status,
anon_1.engineers_engineer_name AS anon_1_engineers_engineer_name,
anon_1.engineers_primary_language AS anon_1_engineers_primary_1,
machines_1.machine_id AS machines_1_machine_id, machines_1.name AS
machines_1_name, machines_1.engineer_id AS machines_1_engineer_id
FROM (SELECT people_person_id, people_company_id, people_name,
people_type, managers_person_id, managers_status,
managers_manager_name, boss_boss_id, boss_golf_swing,
engineers_person_id, engineers_status, engineers_engineer_name,
engineers_primary_language
FROM (SELECT people.person_id AS people_person_id, people.company_id
AS people_company_id, people.name AS people_name, people.type AS
people_type, managers.person_id AS managers_person_id, managers.status
AS managers_status, managers.manager_name AS managers_manager_name,
boss.boss_id AS boss_boss_id, boss.golf_swing AS boss_golf_swing,
engineers.person_id AS engineers_person_id, engineers.status AS
engineers_status, engineers.engineer_name AS engineers_engineer_name,
engineers.primary_language AS engineers_primary_language, ROW_NUMBER()
OVER (ORDER BY people.person_id) AS ora_rn
FROM people LEFT OUTER JOIN managers ON people.person_id =
managers.person_id LEFT OUTER JOIN boss ON managers.person_id =
boss.boss_id LEFT OUTER JOIN engineers ON people.person_id =
engineers.person_id)
WHERE ora_rn>1 AND ora_rn<=3) anon_1 LEFT OUTER JOIN machines
machines_1 ON anon_1.engineers_person_id = machines_1.engineer_id
ORDER BY anon_1.people_person_id, machines_1.machine_id
INFO:sqlalchemy.engine.base.Engine.0x..4c:{}
DEBUG:sqlalchemy.engine.base.Engine.0x..4c:Col
('ANON_1_PEOPLE_PERSON_ID', 'ANON_1_PEOPLE_COMPANY_ID',
'ANON_1_PEOPLE_NAME', 'ANON_1_PEOPLE_TYPE',
'ANON_1_MANAGERS_PERSON_ID', 'ANON_1_MANAGERS_STATUS',
'ANON_1_MANAGERS_MANAGER_NAME', 'ANON_1_BOSS_BOSS_ID',
'ANON_1_BOSS_GOLF_SWING', 'ANON_1_ENGINEERS_PERSON_ID',
'ANON_1_ENGINEERS_STATUS', 'ANON_1_ENGINEERS_ENGINEER_NAME',
'ANON_1_ENGINEERS_PRIMARY_1', 'MACHINES_1_MACHINE_ID',
'MACHINES_1_NAME', 'MACHINES_1_ENGINEER_ID')
DEBUG:sqlalchemy.engine.base.Engine.0x..4c:Row (2, 1, 'wally',
'engineer', None, None, None, None, None, 2, 'regular engineer',
'wally', 'c++', 3, 'Commodore 64', 2)
DEBUG:sqlalchemy.engine.base.Engine.0x..4c:Row (3, 1, 'pointy haired
boss', 'boss', 3, 'da boss', 'pointy', 3, 'fore', None, None, None,
None, None, None, None)
Where you can see that "anon_1_engineers_primary_language" becomes
"anon_1_engineers_primary_1" (27 characters) - this translation occurs
as late as possible. It shows up as that name in cursor.description,
and the ResultProxy translates that truncated name back to a name
matching the original column object.
Russell Warren
> ColumnDefault. Whatever function or SQL you like will be called if no
> value is present - its just in this case we can't rely upon SQLite's
> OID generation.
ColumnDefault dialect dependent.
> I wouldn't favor a built in system of "guessing" within the sqlite
> dialect how to autoincrement a composite PK field without explicit
> user intervention.
precisely within SQLA? If you have a Column that has been defined to
be an Integer primary key that is supposed to autoincrement, and you
are using sqlite... how could you be wrong? The worst case I can
think of is if sqlite changes in the future to actually support it, in
which case you'd either change the dialect or get an error. No?
> The dialects don't intend to build a completely
> uniform layer over all database backends (for example, when using
> Oracle, you are required to set up a default generator, usually a
> Sequence, in all cases) - the idea of a dialect's default behavior is
> that it uses what the database backend provides by default and that's
> it.
annoying backend type differences. I thought that a big part of SQLA
was going to be allowing the use of any back end. Maybe I'll have to
re-evaluate my approach... more likely I'll just keep plugging away
and see what obstacles I hit!
Russell Warren
> a) inherit the class, replace whatever, use the new version - works
> if it is just you using the new-stuff
> b) complete replacement: import thatclass; thatclass.method =
> your-own-version
> c) partial hacks: inspect.get_source( that method); replace some
> lines in that with yours; compile; replace the method with the new
> version. this works if u have sources; if its just *.pyc, sorry.
locate the right replacement location underneath the SQLA classes I'm
using (Session, Engine, Metadata, etc). Where the heck is the
Compiler?
a...@svilendobrev.com
which is used when u do not have an engine yet (meta =Metadata()).
and all dialects inherit from it.
see dbcook/misc/aggregator/tests/convertertest.py for another hack for
better visibility/str() of bindparams.
also dbcook/misc/metadata/autoload is fiddling a bit with dialects.
"grep" - and your very own eyes - are your friends.
and of course trial+error...
a...@svilendobrev.com
> > if you'd like to specify a value generator for the columns, just
> > use a ColumnDefault. Whatever function or SQL you like will be
> > called if no value is present - its just in this case we can't
> > rely upon SQLite's OID generation.
>
> Thanks - I'll look into that. I just have to figure out how to
> make ColumnDefault dialect dependent.
>
> > I wouldn't favor a built in system of "guessing" within the
> > sqlite dialect how to autoincrement a composite PK field without
> > explicit user intervention.
>
> Why not? Is it really guessing when the table has been defined
> precisely within SQLA? If you have a Column that has been defined
> to be an Integer primary key that is supposed to autoincrement, and
> you are using sqlite... how could you be wrong? The worst case I
> can think of is if sqlite changes in the future to actually support
> it, in which case you'd either change the dialect or get an error.
> No?
dialect-preferences. e.g. Column may mantain an attribute
extras4sqlite, eventualy containing a dict of sqlite-specific
settings; same for postgres etc. Then the specific dialect can look
the column and get his own extra-settings - if any. This gives
explicitness, separation of concerns (generic vs specific), and
flexibility together. And this can be applied to other objects, e.g.
tables or whatever. i'm sure thre are things u can do with tables in
postgress and u cannot do in sqlite.
Once some feature/attribute is considered generic/unified, it is moved
from those extra* settings into the usual place -- or vice versa if
stops being generic.
Michael Bayer
>
> Why not? Is it really guessing when the table has been defined
> precisely within SQLA? If you have a Column that has been defined to
> be an Integer primary key that is supposed to autoincrement, and you
> are using sqlite... how could you be wrong?
autoincrement is very difficult to implement in application code in an
atomic and high performing fashion. If we're not using what the DB engine
provides natively, then the user has to pick the method he or she wishes
to use, since each would have caveats the user needs to be aware of. I'm
not opposed to having a catalog of "id generator" tools within the distro
but as of yet nobody has offered any. A key philosophy of SQLA is that we
don't make choices for the user without explicit statement.
> But at the same time the dialect is also abstracting out many of the
> annoying backend type differences. I thought that a big part of SQLA
> was going to be allowing the use of any back end.
We abstract as much as is reasonably possible. But we also honor and make
explicit the differences between those databases so that each backend can
be used to its fullest potential. Another big philosophy of ours is to
not pretend the database doesn't exist by forcing all the various vendors
into the lowest denominator of functionality. Practices like that are
what give object relational tools as well as RDBMS overall a bad name.