Thread: Re: [SQLObject] two postgres questions (Page 3)
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
Menu
▾
▴
sqlobject-discuss
|
From: Bud P. B. <bu...@si...> - 2003-06-02 21:13:07
|
On Mon, 02 Jun 2003 12:46:44 +0200 Magnus Lyckå <ma...@th...> wrote: > At 12:09 2003-06-02 +0200, Bud P. Bruegger wrote: > >Magnus, > > > >I very much agree with your approach to primary keys. This is why I > >suggested to use GUID or UUID in SQLObject some time ago and voted > >against the possibility to use multi-column primary (and foreign) > >keys. > > On the other hand, I can understand the need to intergrate with > Legacy databases, so allowing multi-column keys could certainly > be useful for *that*. Agreed. I tend to forget about pre-existing schemas since I decided I don't have a need for this myself. > Another issue to consider is that SQL tables are much more limited > than Python classes. If we have a class X with an attribute Y which > is a dict containing string => string, that can obviously not be a > column in table X, but on the other hand, we would not assign an object > identity in Python for each dict element. Well, I understand you philosophically, but what do you mean by object identity? Every instance in python has an OID which is basically it's menory address... ...and I don't believe anyone assigns OIDs in python. > The most straight forward solution seems for me to be a table > > X_Y with columns x_id int, key varchar(...), value varchar(...). > > Now, it seems I neet to either obscure X.Y as a pickle etc, or to > give each dict element an object identity. One makes the database > less clear and searchable, the other makes the db much heavier than > the pure Python implementation. Yes, I also see these two possibilities. You haven't been explicit about how this relates to GUID or other primary key, so I state is more explicitly: Tables that manage values (as opposed to objects) don't necessarily need an OID as shadow information. Your example is convincing; the key of the dictionary is a good solution for a primary key. I'm not overly concerned with additional overhead introduced by a ORM middleware layer, so I could probably live with an unnecessary GUID. But thinking aloud, maybe there is some systematics to it since also breakup tables used in many:many relationships don't need guids... Hmmm. just an intuition but I haven't understood the systematics of it all the way... > >I believe I was originally convinced of this by a paper by Scott > >Ambler--but I didn't find the refernece anymore.. > > Me too. They are in my wiki I think. > http://www.thinkware.se/cgi-bin/thinki.cgi/DatabaseDesign I had a paper in mind that wasn't listed there, only on OIDs.. But thanks for the links.. --b |
|
From: Magnus <ma...@th...> - 2003-06-02 23:45:44
|
At 15:24 2003-06-02 +0200, Bud P. Bruegger wrote:
>Agreed. I tend to forget about pre-existing schemas since I decided
>I don't have a need for this myself.
You probably will one day...
>Well, I understand you philosophically, but what do you mean by object
>identity?
I mean that the object represents something unique in the
problem domain, or in the technical solution. This would
probably also be a design class that I would model in UML
if I choose to use UML. Instances of classes in the code
might not be objects with a unique (logical) identity, for
instance an integer instance in Smalltalk. My idea is that
there should be a one-to-one mapping between this kind of
object identity, and IDs in the database.
Philosophically I mean that there is a distinction between
an object, such as a person or a GUI windows, and a simple
value such as 42 meters or 3612 seconds. Martin Fowler calls
the latter "Value Objects" in his new book. Often, a value
object is simply a scalar like a string or an integer, but it
might certainly be something that won't fit in a simple scalar.
But what I'm after technincally is to map the kind of Python
classes I typically work with in a simple way. Uniform Python
lists and dictionaries are certainly common as attributes. So,
unless I am to contrieve my python object model, I have to map
a logic layer class to several SQL tables.
I guess you could say that if it makes sense to have a foreign
key that links with an object, it *can't* be a value object. I'm
not philosophically certain that this is right, but it certainly
seems reasonable, that anything that we would like to specifically
point out from another table would be more than a mere value. It's
not anonymous any more...
>Yes, I also see these two possibilities. You haven't been explicit
>about how this relates to GUID or other primary key, so I state is
>more explicitly:
>
>Tables that manage values (as opposed to objects) don't necessarily
>need an OID as shadow information. Your example is convincing; the
>key of the dictionary is a good solution for a primary key.
Together with the oid of the main object, yes. Like this:
class X so that x = X(), x.a = ['brown, 'green'], x.b = {'a': 42, 'b': 17}
x.c = 'Tree', x.id = 123, I would have
Table x
|id | c |
|123 |Tree|
Table x_a
|x_id|index|value|
|123 |0 |brown|
|123 |1 |green|
Table x_b
|x_id|key|value|
|123 |a |42 |
|123 |b |17 |
I never want to have a situation where a foreign key is anything
but a single oid field though. If I want to have a foreign key to
a row in a "value table", I would elevate the valur to an "full"
object. This is also aligned with normal object oriented modeling
in UML etc. I can't model a relationship to a plain value in UML.
I can only have a relationshiop with an instance of a class.
If we acually implement these attributes as simple lists and
dictionaries in Python, there will no simple way to keep track
of any ID-fields in the x_a or x_b tables anyway. It seems they
would just be dead weight.
Let's say we do, x.a[1] = 'black', x.a.sort(), and then we want to
save the instance. Now we should have
Table x_a
|x_id|index|value|
|123 |0 |black|
|123 |1 |brown|
I guess I'd just do "DELETE FROM x_a WHERE x_id = 123" and INSERT
the current values. If I had had something like...
Table x_a
| id|x_id|index|value|
|101|123 |0 |brown|
|102|123 |1 |green|
I can't see how this would have helped me... The simple python
list object can't be kept in sync with those id fields anyway.
This id field would just be dead weight, and I'd have to run
the DELETE/INSERT anyway. Right?
>I'm not overly concerned with additional overhead introduced by a ORM
>middleware layer, so I could probably live with an unnecessary GUID.
But it seems it would be dead weight in this case.
>But thinking aloud, maybe there is some systematics to it since also
>breakup tables used in many:many relationships don't need guids...
Right. This is something similar.
>Hmmm. just an intuition but I haven't understood the systematics of
>it all the way...
There are many ways to skin a cat... :)
I do tend to feel that my logic objects should have an SQLObject
derivate as an attribute, rather than to inherit DQLObject themselves.
I.e. my business objects use some kind of storage objects, they aren't
storage objects. But I might skip this for very small systems.
How do other SQLObject users handle this?
>I had a paper in mind that wasn't listed there, only on OIDs.. But
>thanks for the links..
It's probably among the others at AmbySoft though.
--
Magnus Lycka (It's really Lyckå), ma...@th...
Thinkware AB, Sweden, www.thinkware.se
I code Python ~ The shortest path from thought to working program
|
|
From: Nick <ni...@dd...> - 2003-05-31 05:57:17
|
Ian Bicking wrote: > categoryId is '3', not the integer 3. Why is this happening? That > seems to be the problem, but it shouldn't be possible to get a > non-integer in there. As I recall, his original definition was a StringCol. Nick |
|
From: Edmund L. <el...@in...> - 2003-05-31 06:21:50
|
Nick wrote: > Ian Bicking wrote: > >> categoryId is '3', not the integer 3. Why is this happening? That >> seems to be the problem, but it shouldn't be possible to get a >> non-integer in there. > > > As I recall, his original definition was a StringCol. Yes it was. I could not just specify Col because the scheme could not then be autogenerated. ...Edmund. |
|
From: Ian B. <ia...@co...> - 2003-05-31 06:24:02
|
On Sat, 2003-05-31 at 00:56, Nick wrote: > Ian Bicking wrote: > > categoryId is '3', not the integer 3. Why is this happening? That > > seems to be the problem, but it shouldn't be possible to get a > > non-integer in there. > > As I recall, his original definition was a StringCol. That would do it. I should just take foreignKey the keyword argument out. Or, rather, it could be used directly with Col (maybe using sqlType), or with KeyCol or ForeignKey (ForeignKey being the prefered technique). Well... really integer IDs aren't that essential. The only time I've really noticed it is with the %i in __repr__. So maybe that could also be relaxed. Of course, in this case it actually was a bug, so... Ian |
|
From: Edmund L. <el...@in...> - 2003-06-01 04:37:11
|
Luke Opperman wrote: > Actually, notNull and unique are both currently supported by columns. Ian's a > good documenter, there's just been a lot of changes since the last cut. :) I stumbled across the unique option about the same time as you were emailing me! A suggestion: remove the notNull option, and force people to use notNone. The former is really a SQL thing, and the latter a Python thing. On the theory that people who use ORMs don't want to know about SQL, having notNull is a bit unnecessary. > Yep. Big problem holding this back is that any current implementation of a > BooleanCol doesn't work in Postgres, because saying "obj.boolCol = True" fails > during SQL conversion, since PG won't accept integer literals for boolean > values. Grr.. (I'm a happy PG user, but this always bothers me...) Do a behind-the-scenes conversion to the strings "1" and "0" or "t" and "f". This way, boolean support in DBs that don't have booleans (e.g., Oracle) is easier. > It specifies results that are neither Cars nor Persons. How would you propose > to represent the return values from such a function as objects? > > I think in the O-R world there is no way to do this without loops or Set > operations. But I'd happily be proven wrong. I see the problem. And yet, this is such a common thing to want to do... Hmmm... returning a list would be best, but these certainly aren't schema-derived objects... Hmmmm... > We use a global pool too, so we have a simple subclass of PostgresConnection > that imports the pool and overrides getConnection (return > globalPool.getConnection()), releaseConnection (conn.close()), and > makeConnection (a no-op). _connection (and DBConnection etc) have a confusing > name, as they define a lot more than just the connection... they also do all > the SQL access generation, so you can't just set it to your current pool's > connections. Hmmm... so if multiple SQLObject modules are imported, will they all use the same global pool? What if they have differentt connection strings? Nuts, I guess I'll have to take a closer look at the DB connection code... I just want a single place to specify the connection string for all modules, and have them use the same pool. BTW, I'm really banging away at SQLObject, and had to rewrite my data model to suit it. However, layering SQLObj on to it has been quite painless, and it does seem to be very pleasant to use. Being able to control the names of the underlying columns and tables is _really_ critical. ...Edmund. |
|
From: Ian B. <ia...@co...> - 2003-06-03 07:23:53
|
On Sat, 2003-05-31 at 23:36, Edmund Lian wrote: > > Yep. Big problem holding this back is that any current implementation of a > > BooleanCol doesn't work in Postgres, because saying "obj.boolCol = True" fails > > during SQL conversion, since PG won't accept integer literals for boolean > > values. Grr.. (I'm a happy PG user, but this always bothers me...) > > Do a behind-the-scenes conversion to the strings "1" and "0" or "t" and > "f". This way, boolean support in DBs that don't have booleans (e.g., > Oracle) is easier. Yes, I'd want to be able to do conversion based on the backend. Like ENUM is simulated, BOOL should be simulated too. I guess I'll have to keep in mind that the validation/conversion has to be potentially database-specific. Ian |
|
From: Edmund L. <el...@in...> - 2003-06-02 08:56:02
|
Bud P.Bruegger wrote: > * Looking at other ORMs, I think we can't give enough credit to Ian > for having found a way of doing things EASY and intuitive. Some of > that stuff looks to complex and verbose that it would be great for > Enterprise Java... Thanks, Ian! One really starts to appreciate > this more and more when one looks at alternatives (which I haven't > done much before..) Yes, I do agree... I'm exercising SQLObject (and myself) quite a bit, and it is proving to be very pleasant to use. Thank you Ian! I've been making a lot of comments, not because I'm complaining, but because I think SQLObject has a good chance at being the best Python ORM out there. I find that Ian's philosophy and desire for clean interfaces (e.g., the choice to use attribute style access for column data) to be dead-on. ...Edmund. |
|
From: Ian B. <ia...@co...> - 2003-06-03 07:10:23
|
On Mon, 2003-06-02 at 03:55, Edmund Lian wrote: > Bud P.Bruegger wrote: > > > * Looking at other ORMs, I think we can't give enough credit to Ian > > for having found a way of doing things EASY and intuitive. Some of > > that stuff looks to complex and verbose that it would be great for > > Enterprise Java... Thanks, Ian! One really starts to appreciate > > this more and more when one looks at alternatives (which I haven't > > done much before..) > > Yes, I do agree... I'm exercising SQLObject (and myself) quite a bit, > and it is proving to be very pleasant to use. Thank you Ian! Thanks, I'm glad you both like SQLObject. > I've been making a lot of comments, not because I'm complaining, but > because I think SQLObject has a good chance at being the best Python ORM > out there. I find that Ian's philosophy and desire for clean > interfaces (e.g., the choice to use attribute style access for column > data) to be dead-on. I don't mind comments at all -- thinking back on them, I don't actually take a whole lot of the suggestions that are given, but in the end I at least have a better idea of why I'm doing one thing and not the other, even if at the root it's mostly based on intuition. Either way it just brings me one step closer to WORLD DOMINATION! Ian |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X