At 04:01 PM 4/5/2001 -0700, Graham Hughes wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>I've been looking through MiddleKit in the 0.5.1rc2 release
>(incidentally, very easy to follow code; thank you!), and have a list
>of things that would need to be changed to have a PostgreSQL backend,
>something I would need to be able to use MK. I'm willing to implement
>this stuff, incidentally.
That would be great. Postgres support for MK comes up quite a bit.
>o You use /* a lot. /*, despite being recognized by Postgres and
> MySQL both, isn't really proper SQL syntax: real SQL comments match
> the regexp `--.*$'. I'm not going to change this, as Pg supports
> the C style block comments.
The SQL classes are meant more to be "commonly accepted" instead of "ANSI".
Inevitably, I find "what works for all products" is a little more important
than "what the standards committee said".
Pg, MySQL and MSSQL all support /* */, so we'll just go with that for now.
>o Postgres has different syntax for drop database (no if exists
> clause, mostly) and changing to a new database, as well as IDs and a
> couple other minor things. Not a big deal.
Also connecting to a database. You have to specify this up front with Pg
whereas other DBs will let you send SQL to connect (which is the idea that
SQLObjectStore currently embraces).
>o You use enum (...), which isn't in ANSI; I assume this is a MySQL
> thing, as the normal way to do it in ANSI is `varchar (<max enum
> length>) check (<column_name> in (...))' which requires some sort of
> referential integrity checking.
Yeah, I wasn't sure how to do enum's in ANSI, so I went with this.
Unfortunately, I don't have my "SQL in a Nutshell" book with me as I'm
travelling. That book covers SQL including real life differences in MySQL,
Pg, Oracle and Microsoft.
>o You use `bigint unsigned /* Video */' for foreign key referencing;
> ANSI doesn't specify, but assuming your IDs fit in an int4, `int4
> references Video (videoId)' will do the checking correctly. If they
> don't, then I suppose an int8 would do the trick, but that's a lot
> of rows.
MySQL doesn't support explicit references which is how I arrived at this
code. In practice, pretty much every specific DB subclass will need to
provide it's own ObjRefAttr.sqlType().
BTW It's 64-bits because 32 are the class identifier and 32 are the object
identifier within that class.
I'll check if "int8 unsigned" or something similar is supported by both
ANSI and the popular databases. If so, I'd have no problem switching to that.
>o Generate.py would of course need to be hacked slightly, but only
> slightly.
Yeah, I sectioned off the "mysql" stuff into methods that can be gutted and
worked off a config file.
>o MySQLObjectStore would need to be hacked but that's easy enough.
> Except: you have retrieveLastInsertID taking no arguments, assuming
> that the database will keep track of the last inserted ID on a per
> connection basis, I assume? Postgres can do this through one of two
> methods: get the OID from the insert statement and look at that row,
> or get the last returned value from the sequence corresponding to
> that class. But in one case I would need the return value for the
> statement and in the other I would need the object inserted.
>
> While I could just override commitInserts and do it myself, and I
> will have to end up doing that if I want this to work with the 0.5.1
> release, it would seem better to make the database layer a little
> more flexible.
We'll have to refactor some of the methods and behavior of SQLObjectStore
to support Pg. Even the simple lack of "if exists" and "use" in Pg creates
this need.
I wouldn't worry about 0.5.1 compatibility. I would worry about the
mainline CVS.
>o I'm pretty sure I know how to retrofit transaction support onto the
> object store, which speaks well for the design; I can even get
> revertChanges in and working. But as revertChanges isn't
> implemented I'm a little unclear about how it is intended to work.
> If I work with a store and saveChanges a dozen times, and then
> revertChanges once, what does reverting mean? Does it mean `go back
> to the state things were in when I started working with the store'
> or does it mean `don't do what I've been working on since the last
> save' or does it mean `undo what I did last save' or what?
So the choices are:
- revert to time 0 of store usage
- revert the effects of last save
- revert changes since last save
I was thinking the last one.
>o How do you run the tests? I'd like to be able to make my changes
> and then run all those tests to make sure I didn't break something,
> but I'm not entirely sure how to get them to go.
python test.py
python test.py MKSpecificTest
You'll have to tweak the code a little where it refers to "mysql". These
are all collected in specifc methods that are used, so the changes are small.
cd Tests
grep -ni mysql *.py
In a run where I'm expecting things to work, I redirect stdout and then see
if any stderr Python exception messages get printed:
python Test.py > results.text
-Chuck
|