Thread: [SQLObject] two postgres questions
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Bud P. B. <bu...@si...> - 2003-05-14 16:44:27
|
I would like to bring up two Postgres questions/issues: Q1: Table Dropping and left-over sequences: -------------------------------------------- I found the following <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> At this point, I ran into another small issue with Postgres - its "serial" data type (the equivalent of MySQL's auto_increment) creates a "sequence" which does not get dropped when its parent table is dropped. So if you try to re-create the table, you'll get a name conflict for this sequence. A lot of new users would be confused by this, so Postgres loses a couple points for that. Also, MySQL is "smart" enough to increment its auto_increment value when you import data, whereas Postgres' sequence does not get reset when you import data, causing all new inserts to fail. </quote> Does that mean that the dropTable method of SQLObject should also drop the sequences? Q2: Global sequence possible? ----------------------------- I was thinking of having IDs that are unique across tables--something useful when thinking of implementing inheritance etc. So I tried to create a global sequence and have the id columns of several tables use it for their default values (DEFAULT nextval('global_oid_seq')). But that seems to fail. Can there be only a single column (of a single table) associated with a sequence??? many thanks --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Bud P. B. <bu...@si...> - 2003-05-14 16:49:00
|
On the first issue, I just verified and PostgreSQL 7.3 also drops the sequence when dropping the table. On Wed, 14 May 2003 18:43:17 +0200 "Bud P. Bruegger" <bu...@si...> wrote: > I would like to bring up two Postgres questions/issues: > > Q1: Table Dropping and left-over sequences: > -------------------------------------------- > > I found the following > > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> > At this point, I ran into another small issue with Postgres - its > "serial" data type (the equivalent of MySQL's auto_increment) creates > a "sequence" which does not get dropped when its parent table is > dropped. So if you try to re-create the table, you'll get a name > conflict for this sequence. A lot of new users would be confused by > this, so Postgres loses a couple points for that. Also, MySQL is > "smart" enough to increment its auto_increment value when you import > data, whereas Postgres' sequence does not get reset when you import > data, causing all new inserts to fail. > </quote> > > Does that mean that the dropTable method of SQLObject should also drop > the sequences? > > Q2: Global sequence possible? > ----------------------------- > > I was thinking of having IDs that are unique across tables--something > useful when thinking of implementing inheritance etc. So I tried to > create a global sequence and have the id columns of several tables use > it for their default values (DEFAULT nextval('global_oid_seq')). But > that seems to fail. Can there be only a single column (of a single > table) associated with a sequence??? > > many thanks > > --b > > /----------------------------------------------------------------- > | Bud P. Bruegger, Ph.D. > | Sistema (www.sistema.it) > | Via U. Bassi, 54 > | 58100 Grosseto, Italy > | +39-0564-411682 (voice and fax) > \----------------------------------------------------------------- > > > ------------------------------------------------------- > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara > The only event dedicated to issues related to Linux enterprise solutions > www.enterpriselinuxforum.com > > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Bud P. B. <bu...@si...> - 2003-05-14 17:05:01
|
And the answer to my second question: A global (db-wide) SEQUENCE is possible. I made a mistake and didn't give the column type as bigint... --b On Wed, 14 May 2003 18:47:56 +0200 "Bud P. Bruegger" <bu...@si...> wrote: > On the first issue, I just verified and PostgreSQL 7.3 also drops the > sequence when dropping the table. > > On Wed, 14 May 2003 18:43:17 +0200 > "Bud P. Bruegger" <bu...@si...> wrote: > > > I would like to bring up two Postgres questions/issues: > > > > Q1: Table Dropping and left-over sequences: > > -------------------------------------------- > > > > I found the following > > > > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> > > At this point, I ran into another small issue with Postgres - its > > "serial" data type (the equivalent of MySQL's auto_increment) creates > > a "sequence" which does not get dropped when its parent table is > > dropped. So if you try to re-create the table, you'll get a name > > conflict for this sequence. A lot of new users would be confused by > > this, so Postgres loses a couple points for that. Also, MySQL is > > "smart" enough to increment its auto_increment value when you import > > data, whereas Postgres' sequence does not get reset when you import > > data, causing all new inserts to fail. > > </quote> > > > > Does that mean that the dropTable method of SQLObject should also drop > > the sequences? > > > > Q2: Global sequence possible? > > ----------------------------- > > > > I was thinking of having IDs that are unique across tables--something > > useful when thinking of implementing inheritance etc. So I tried to > > create a global sequence and have the id columns of several tables use > > it for their default values (DEFAULT nextval('global_oid_seq')). But > > that seems to fail. Can there be only a single column (of a single > > table) associated with a sequence??? > > > > many thanks > > > > --b > > > > /----------------------------------------------------------------- > > | Bud P. Bruegger, Ph.D. > > | Sistema (www.sistema.it) > > | Via U. Bassi, 54 > > | 58100 Grosseto, Italy > > | +39-0564-411682 (voice and fax) > > \----------------------------------------------------------------- > > > > > > ------------------------------------------------------- > > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara > > The only event dedicated to issues related to Linux enterprise solutions > > www.enterpriselinuxforum.com > > > > _______________________________________________ > > sqlobject-discuss mailing list > > sql...@li... > > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > > > > > /----------------------------------------------------------------- > | Bud P. Bruegger, Ph.D. > | Sistema (www.sistema.it) > | Via U. Bassi, 54 > | 58100 Grosseto, Italy > | +39-0564-411682 (voice and fax) > \----------------------------------------------------------------- > > > ------------------------------------------------------- > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara > The only event dedicated to issues related to Linux enterprise solutions > www.enterpriselinuxforum.com > > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss > /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Edmund L. <el...@in...> - 2003-05-14 18:19:42
|
Bud P.Bruegger wrote: > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> > At this point, I ran into another small issue with Postgres - its > "serial" data type (the equivalent of MySQL's auto_increment) creates > a "sequence" which does not get dropped when its parent table is > dropped. So if you try to re-create the table, you'll get a name > conflict for this sequence. A lot of new users would be confused by > this, so Postgres loses a couple points for that. This depends on which version of PostgreSQL you are using. The latest versions drop this automatically. > Also, MySQL is > "smart" enough to increment its auto_increment value when you import > data, whereas Postgres' sequence does not get reset when you import > data, causing all new inserts to fail. This depends on how you import the data. Are you using the copy command? If so, then rather than importing every column of a table that has an autosequence, you should just import the columns you really want. Leave the autoincrementing column out of the import statement, and it will be autoincremented as you expect. > I was thinking of having IDs that are unique across tables--something > useful when thinking of implementing inheritance etc. So I tried to > create a global sequence and have the id columns of several tables use > it for their default values (DEFAULT nextval('global_oid_seq')). But > that seems to fail. Can there be only a single column (of a single > table) associated with a sequence??? You should be using triggers for this. I do this now and then, no problems. ...Edmund. |
From: Bud P. B. <bu...@si...> - 2003-05-15 07:26:39
|
Dear Edmund, many thanks for the help. Below some comments.. On Wed, 14 May 2003 14:19:23 -0400 Edmund Lian <el...@in...> wrote: > Bud P.Bruegger wrote: > > > <quote src=http://www.phpbuilder.com/columns/tim20000705.php3:> > > At this point, I ran into another small issue with Postgres - its > > "serial" data type (the equivalent of MySQL's auto_increment) creates > > a "sequence" which does not get dropped when its parent table is > > dropped. So if you try to re-create the table, you'll get a name > > conflict for this sequence. A lot of new users would be confused by > > this, so Postgres loses a couple points for that. > > This depends on which version of PostgreSQL you are using. The latest > versions drop this automatically. So you seem to confirm that SQLObject is fine as long as it runs with Postgres7.3 or higher... > > Also, MySQL is > > "smart" enough to increment its auto_increment value when you import > > data, whereas Postgres' sequence does not get reset when you import > > data, causing all new inserts to fail. > > This depends on how you import the data. Are you using the copy command? > If so, then rather than importing every column of a table that has an > autosequence, you should just import the columns you really want. Leave > the autoincrementing column out of the import statement, and it will be > autoincremented as you expect. I actually just quoted the whole paragraph, I'm not currently in need for this. But thanks for clarifying the issue. Surely I'll use it one day soon. > > I was thinking of having IDs that are unique across tables--something > > useful when thinking of implementing inheritance etc. So I tried to > > create a global sequence and have the id columns of several tables use > > it for their default values (DEFAULT nextval('global_oid_seq')). But > > that seems to fail. Can there be only a single column (of a single > > table) associated with a sequence??? > > You should be using triggers for this. I do this now and then, no problems. My test didn't work since I used the type BIGSERIAL for the id columns and set the DEFAULT to nextval of my global seqence. This way, BIGSERIAL implied a local sequence and default value and I explicitly also set a global one: two values assigned as defaults and an obvious problem. When I used BIGINT instead as type, it all worked as expected... [I did other funny things yesterday to make my own life harder--I'm hoping for a better day today..] many thanks -b |
From: Edmund L. <el...@in...> - 2003-05-15 20:20:43
|
Bud P.Bruegger wrote: >>This depends on which version of PostgreSQL you are using. The latest >>versions drop this automatically. > > So you seem to confirm that SQLObject is fine as long as it runs with > Postgres7.3 or higher... I cannot confirm this--I do not use SQLObject. I'm following discussions off and on, but have not tried to use it since it cannot support what I do (multi-table joins, extensive referential integrity checks, etc.) yet. This is not to knock SQLObject. So far, I haven't seen an ORM that can support complex schema, and does not get in the way of exploiting the relational model to the max. ...Edmund. |
From: Bud P. B. <bu...@si...> - 2003-05-16 07:28:54
|
On Thu, 15 May 2003 16:20:30 -0400 Edmund Lian <el...@in...> wrote: > I cannot confirm this--I do not use SQLObject. I'm following discussions > off and on, but have not tried to use it since it cannot support what I > do (multi-table joins, extensive referential integrity checks, etc.) yet. > > This is not to knock SQLObject. So far, I haven't seen an ORM that can > support complex schema, and does not get in the way of exploiting the > relational model to the max. Can you tell us more about it? Maybe an example? My experimental code is almost there to support foreign keys all the way. I thought of adding arbitrary table constraints later (with those, you can do things such as Check constraints, Unique constraints etc that involve a single or multiple fields). I have been thinking about constraints etc a bit and my impression is so far that this is better dealth with in python objects than in the dbms. I see some advantages by doing it in Python: * the app programmer doesn't need to worry about SQL. [The difficulty here is particularly the marshalling that separates the two worlds that either the app programmer or the backend programmer has to deal with] * it is more portable across dmbs (particularly those that don't do constraints well) * the backend developer has to worry about less things * SQL constraints may not be expressive enough to do full consistency checking, so you need python code in any case. Just thoughts and if anyone has oppinions, I'd be very interested to hearing them... -b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Edmund L. <el...@in...> - 2003-05-19 04:17:59
|
Bud P.Bruegger wrote: > I have been thinking about constraints etc a bit and my impression is > so far that this is better dealth with in python objects than in the > dbms. I see some advantages by doing it in Python: > > * the app programmer doesn't need to worry about SQL. [The difficulty > here is particularly the marshalling that separates the two worlds > that either the app programmer or the backend programmer has to deal > with] > > * it is more portable across dmbs (particularly those that don't do > constraints well) > > * the backend developer has to worry about less things > > * SQL constraints may not be expressive enough to do full consistency > checking, so you need python code in any case. Constraints are essential for data integrity, but they are often incomplete or poorly utilized--because good data modellers are hard to find, and because RDBMS theory is poorly taught/learnt in schools. A good data model uses extensive constraints to ensure that it is never possible to create invalid combinations of data in the DB via invalid insertions, deletions or modifications. But where to add them? To my mind, the constraints should really be in the DB, because it is the DB that should enforce the constraints. The problem is that it is then very hard to port the data model to a different DB. On the other hand, other applications (in possibly other languages) that use the data will be subject to the same checks and constraints. To improve portability, one can move the constraints to some middleware layer. But now, you have more complexity, and potentially just as many portability issues (does the middleware layer run on my platform? Can I call it from my chosen language? etc.) If you write your own middleware, you have more APIs/code to maintain, etc. And, writing a decent transaction processing layer is non-trivial (because if we care enough to use constraints, we care about having robust transactions). I've seen these issues again and again. I've chosen, in my latest work, to forgo a third-party TP layer and just put the constraints in the DB. This way, I don't have to deal with API and cross language issues since the data is accessed from Python, VB, Access, Crystal Reports, etc. A middleware layer would not have worked in this situation, since things like Crystal Reports and Access would not have interfaced to it. But what if the DB doesn't support constraints or transactions well? If you care about data integrity, change your DB. ACIDity is mandatory for reliability. ...Edmund. |
From: Bud P. B. <bu...@si...> - 2003-05-19 12:39:18
|
On Mon, 19 May 2003 00:17:32 -0400 Edmund Lian <el...@in...> wrote: > I've seen these issues again and again. I've chosen, in my latest work, > to forgo a third-party TP layer and just put the constraints in the DB. > This way, I don't have to deal with API and cross language issues since > the data is accessed from Python, VB, Access, Crystal Reports, etc. thanks for your insight. I'm still digesting but it seems that there is a considerable range of needs. People (like you) who need a single DB model accessed by multiple languages and apps and people who do just python and single app (like possibly me at the moment) and don't do legacy db models... I see three ways of doing constraint kind of stuff: * all in python, dbms doesn't know about constraints. This works if everything that accesses the data goes through the same middleware layer (not directly to dbms) * specification of constraints in python/middleware that translates it to SQL statements to send to the dbms (such as check constraints). As you already say, there is a lot of diversity with dbms capabilities here (I've only looked at Postgres so far) * doing everything in the dbms. It seems that the first and last are easy, the middle one more difficult (as you mention). What about a middleware layer who usually does the first solution but doesn't block you from doing the third? Would that be useful or does anyone see a strong need for the middle solution? --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Edmund L. <el...@in...> - 2003-05-19 17:52:06
|
Bud P.Bruegger wrote: > thanks for your insight. I'm still digesting but it seems that there > is a considerable range of needs. People (like you) who need a single > DB model accessed by multiple languages and apps and people who do > just python and single app (like possibly me at the moment) and don't > do legacy db models... Actually, I don't consider my db models to be legacy models... "legacy" kind of implies "out of date", "mainframe", etc. In reality however, they are just data models that exploit the strengths of the relational model--data integrity, flexibility, etc. But your observation (and Luke's) does raise a thought... perhaps there are three broad classes of database user: 1. Someone who wants an object store and isn't especially concerned about data integrity, flexibility, etc. 2. Someone who wants an object store but does want some measure of data integrity, has simple data models, and wants some ability to access the data via third party apps/tools. 3. Some who wants flexibility, ability to access the data via third party apps/tools, is concerned about data integrity, etc. Is willing trade pure object orientation for these. It seems to me that (1) is best served by ZODB, (2) is where the ORMs and SQLObject are, and (3) is where people like me are, but would prefer not to be. > I see three ways of doing constraint kind of stuff: > > * all in python, dbms doesn't know about constraints. This works > if everything that accesses the data goes through the same > middleware layer (not directly to dbms) > > * specification of constraints in python/middleware that translates it > to SQL statements to send to the dbms (such as check constraints). > As you already say, there is a lot of diversity with dbms > capabilities here (I've only looked at Postgres so far) > > * doing everything in the dbms. > > It seems that the first and last are easy, the middle one more > difficult (as you mention). This is what I've been forced to do. I have a thin API that the upper layers use to access the DB. Maybe 97% of the constraints are in the DB, and the remaining 3% are outside it because it was easier to debug this code as external Python code than horrid PL/PgSql. Isolating all the SQL in this layer also makes it easier to port to a different DB if I need to. > What about a middleware layer who usually does the first solution but > doesn't block you from doing the third? Would that be useful or does > anyone see a strong need for the middle solution? I would have loved to have used SQLObject or some other alternative. I looked at MiddleKit, dbObj, etc. But, none of these tools made it easy to do complex data models. ...Edmund. |
From: Luke O. <lu...@me...> - 2003-05-19 18:09:40
|
> But your observation (and Luke's) does raise a thought... perhaps there > are three broad classes of database user: > > 1. Someone who wants an object store and isn't especially concerned > about data integrity, flexibility, etc. > > 2. Someone who wants an object store but does want some measure of data > integrity, has simple data models, and wants some ability to access the > data via third party apps/tools. > > 3. Some who wants flexibility, ability to access the data via third > party apps/tools, is concerned about data integrity, etc. Is willing > trade pure object orientation for these. > > It seems to me that (1) is best served by ZODB, (2) is where the ORMs > and SQLObject are, and (3) is where people like me are, but would prefer > not to be. Mostly agree with your taxonomy. I think it can be transitively restated as: 1. All data integrity / relational design in via object design. Database is generic persistence. 2. Data design is via Relational concepts, but constraints and complexity are still dealt with on the object side: they want to access their data models as objects, but the data model is more important. 3. Recognizing that constraints are *part* of the data model, this is just a clarification of (2), a desire to model everything via relational theory but to access this model via objects. I don't see anything inherent in (3) that requires you to "trade pure object orientation", the problem is that we're not aware of an object mapper that supports as much as we'd like. ... > But, none of these tools made it easy to do complex data models. Since I'm interested in making SQLObject (or a byproduct of) able to support what I consider to be all of relational theory in an object-accessible way, I'm curious what you consider to be the too-complex parts today. So far I've heard multi-way joins and constraints. Let me see if I can elaborate these, then you do the same? 1. Multi-way joins, meaning n-way intersections. I've had to hack with SQLObject to handle these in a reasonable way, but conceivable. 2. Multi-way joins, meaning join traversal. Seems to be supported, although not necessarily efficiently. So I assume you mean (1)? 3. Date's Contraint taxonomy: Domain/type constraints, column constraints, table constraints, and multi-table constraints (database constraints). I'll probably expand on these wrt SQLObject soon, but wanted to put them all up here for now. 4. Transition constraints (column, table, multi-table) are somewhat different, and will almost certainly be messier. :) - Luke |
From: Bud P. B. <bu...@si...> - 2003-05-20 08:48:24
|
This is a great discussion from which I learn a lot about requirements. > I don't see anything inherent in (3) that requires you to "trade pure object > orientation", the problem is that we're not aware of an object mapper that > supports as much as we'd like. I suppose the biggest difference between object and relational philosophy is what the unit of retrieval is: while in relational DBs you can select just a subset of columns, usually objects with a subset of attributes don't seem to make much sense. What I imagine at the moment is the possibility to write an arbitrary SQL query (or maybe better a VIEW) and relate it to a class such that the result set can be easily used. I would think that using this would be an exception but that middleware layers should make this possible. > > ... > > But, none of these tools made it easy to do complex data models. > > Since I'm interested in making SQLObject (or a byproduct of) able to support > what I consider to be all of relational theory in an object-accessible way, > I'm curious what you consider to be the too-complex parts today. So far I've > heard multi-way joins and constraints. Let me see if I can elaborate these, > then you do the same? It seems I have the same need to understand better.. > 1. Multi-way joins, meaning n-way intersections. I've had to hack with > SQLObject to handle these in a reasonable way, but conceivable. > > 2. Multi-way joins, meaning join traversal. Seems to be supported, although > not necessarily efficiently. So I assume you mean (1)? Could you give examples here for what exactly you mean and maybe how a nice API would look like? > 3. Date's Contraint taxonomy: I don't have Date's book, but believe to understand... >Domain/type constraints, In an OO approach, I suppose you would express this by a class. The class must be concerned that only valid instances can be created. A python class is definitely the prefered way for me to express this. I see that SQL offers some constraints (Check, foreign key, unique, etc) that can express a subset of consistency requirements. When it gets more complex, one needs a real programming language to express the requirements. While in DBMS you can do this with triggers and stored procedures, it really becomes complex, brings portability problems, etc. So maybe a middleware layer should support a declarative spec of constraints that can automatically be propagated to the dbms and also used from python (since you may want to know about consistency also for objects that don't go in the db...). But I don't see that it is easy to add consistency checks that go into the programming domain beyond what you can do declaratively with a pre-defined set of constraints. > column constraints, > table constraints, I thought I understand the differences in your taxonomy--but I'm getting foggy again. In my thinking, either an attribute or an instance is of a certain type(/domain) and its constraints accordingly refer either to a column or a table. But aren't these really type constaints in different incarnations? > and multi-table constraints (database constraints). I'll > probably expand on these wrt SQLObject soon, but wanted to put them all up > here for now. Here I have the same kind of intuition. Wouldn't this mean that I create a higher-level object that contains multiple lower-level ones, each of which is physically represented in a table... > 4. Transition constraints (column, table, multi-table) are somewhat different, > and will almost certainly be messier. :) What are these? cheers --b |
From: Edmund L. <el...@in...> - 2003-05-28 23:37:01
|
Luke Opperman wrote: > 3. Recognizing that constraints are *part* of the data model, this is just a > clarification of (2), a desire to model everything via relational theory but > to access this model via objects. > > I don't see anything inherent in (3) that requires you to "trade pure object > orientation", the problem is that we're not aware of an object mapper that > supports as much as we'd like. Yes, you're right of course. > Since I'm interested in making SQLObject (or a byproduct of) able to support > what I consider to be all of relational theory in an object-accessible way, > I'm curious what you consider to be the too-complex parts today. So far I've > heard multi-way joins and constraints. Let me see if I can elaborate these, > then you do the same? I'll do this soon... sorry (to you, Bud, and Ian) for taking such a long time to reply. Lot's of things happening with family and work right now. One thing that I want to do is sit down and try using SQLObject in its current form for a small portion of a real project that does use a lot of relational integrity, and then report where I get stuck. This would be a good way to stretch it. I've just spent a couple of days going over MiddleKit, Object Relational Membrane, Modeling, and SQLObject. I keep coming back to SQLObject for a variety of reasons, mostly to do with the documentation, user community, and interface aesthetics. When I have time, I'll post my impressions (worth $0.02) of each. ...Edmund. |
From: Bud P. B. <bu...@si...> - 2003-05-20 08:48:38
|
> Actually, I don't consider my db models to be legacy models... "legacy" > kind of implies "out of date", "mainframe", etc. I agree that "legacy" isn't a good term. What I meant is really a pre-existing data schema that has to be accesses (in contrast to one that is newly created following the needs of the middleware). I believe that the biggest issue I see here is that of the object ID. Both SQLObject and my experiments create an additional OID that was not declared in the mapping. Both allow to chose the name, but (I believe???) not the type of that field. Do you see a strong need to live without such an OID? Is it possible to retrofit pre-existing schemas with such an OID field? > It seems to me that (1) is best served by ZODB, (2) is where the ORMs > and SQLObject are, and (3) is where people like me are, but would prefer > not to be. I'm wondering what can (theoretically) be done and where, if they exist, are difficulties. OID is one that I see. Besides this, I believe that (what Postgresql calls) constraints should be quite straight forward to add. I already declare foreign keys all the way. While I haven't implemented it yet, I believe it to be easy to add things such as CHECK constraints, multi-field UNIQUE constraints, etc. I also plan to add the declaration of INDICES. I haven't thought of constraints that involve multiple tables. But off hand it doesn't seem difficult either. Is this what you need or is there more to it? > > What about a middleware layer who usually does the first solution but > > doesn't block you from doing the third? Would that be useful or does > > anyone see a strong need for the middle solution? > > I would have loved to have used SQLObject or some other alternative. I > looked at MiddleKit, dbObj, etc. But, none of these tools made it easy > to do complex data models. I would believe that a concrete example would be the best way of understanding each other. I admit to have difficulties to fully understand the situation so far. I tried to make it more concrete above what I have understood about constraints. I still have only a vague understanding of the multiple joins... Can you enlightern me a little more? best cheers --bud |
From: Ian B. <ia...@co...> - 2003-05-20 09:00:39
|
On Tue, 2003-05-20 at 03:27, Bud P.Bruegger wrote: > > Actually, I don't consider my db models to be legacy models... "legacy" > > kind of implies "out of date", "mainframe", etc. > I agree that "legacy" isn't a good term. What I meant is really a > pre-existing data schema that has to be accesses (in contrast to one > that is newly created following the needs of the middleware). You know, outside of programming "legacy" doesn't imply anything bad :) > I'm wondering what can (theoretically) be done and where, if they > exist, are difficulties. OID is one that I see. Besides this, I > believe that (what Postgresql calls) constraints should be quite > straight forward to add. I already declare foreign keys all the way. > While I haven't implemented it yet, I believe it to be easy to add > things such as CHECK constraints, multi-field UNIQUE constraints, etc. > I also plan to add the declaration of INDICES. I haven't thought of > constraints that involve multiple tables. But off hand it doesn't > seem difficult either. If you really need a sophisticated data definition, you can always create the table yourself, and allow the database to handle much of the logic (like constraints). That seems acceptable to me. What I suspect Edmund's problem is, is that his tables don't map well to classes. Or maybe more accurately, his *usage* doesn't map well to instances and attribute access. I'm imagining that his applications are phrased with rows being relations, not so much structured data. Or at least some of the data. Ian |
From: Edmund L. <el...@in...> - 2003-05-28 23:53:33
|
Ian, Apologies for the delay in replying... You wrote: > If you really need a sophisticated data definition, you can always > create the table yourself, and allow the database to handle much of the > logic (like constraints). That seems acceptable to me. Yes, this is a good solution. > What I suspect Edmund's problem is, is that his tables don't map well to > classes. Or maybe more accurately, his *usage* doesn't map well to > instances and attribute access. I'm imagining that his applications are > phrased with rows being relations, not so much structured data. Or at > least some of the data. Not to mention that in a fully normalized database, there can be a ton of joins to get what you want (or else views are used, but it's the same thing). My usage is certainly very traditional in that I use fully normalized data models. Getting what I want does require the use of subselects, multiway joins and outer joins, transactions and rollbacks, etc. I still can't see how to do these, or else change the data model to avoid these so that I can use SQLObject. But, I'll try and report back what I find. ...Edmund. |
From: Ian B. <ia...@co...> - 2003-05-29 01:55:50
|
On Wed, 2003-05-28 at 18:53, Edmund Lian wrote: > Apologies for the delay in replying... No problem, we all have other lives too... > > What I suspect Edmund's problem is, is that his tables don't map well to > > classes. Or maybe more accurately, his *usage* doesn't map well to > > instances and attribute access. I'm imagining that his applications are > > phrased with rows being relations, not so much structured data. Or at > > least some of the data. > > Not to mention that in a fully normalized database, there can be a ton > of joins to get what you want (or else views are used, but it's the same > thing). I think views don't jive well with SQLObject -- they create an opaque structure. But yes, normalization can lead to a whole bunch of tables. I'm interested extending the power of joins so that some of these can be handled more easily, without having to have too many classes involved. > My usage is certainly very traditional in that I use fully normalized > data models. Getting what I want does require the use of subselects, > multiway joins and outer joins, transactions and rollbacks, etc. I still > can't see how to do these, or else change the data model to avoid these > so that I can use SQLObject. But, I'll try and report back what I find. Any ORM involves moving logic into Python (or whatever programming language). That's a compromise you'll have to deal with. But you don't have to use complicated queries anyway. There's nothing intrinsically Wrong about doing the logic in Python. List comprehension even makes set operations look nicer, and caching can provide performance improvements. It could probably be argued that those complicated queries are a form of premature optimization. Like most optimization, complex queries usually are based on the requirements of pieces of code that would otherwise be considered factored. Those queries may be an important form of optimization, but I think that should still be accessible even if it won't be as natural as using normal object access. Ian |
From: Edmund L. <el...@in...> - 2003-05-29 02:16:56
|
Ian Bicking wrote: > I think views don't jive well with SQLObject -- they create an opaque > structure. Why not have views result in read-only objects? Viz: class roObject(SQLObject): _view = 'my_view' ... ... > But yes, normalization can lead to a whole bunch of tables. > I'm interested extending the power of joins so that some of these can be > handled more easily, without having to have too many classes involved. And this is where every ORM falls down right now. When you're starting from scratch, every ORM is more or less OK. But if you have to use it with a pre-existing schema, oop... > But you don't have to use complicated queries anyway. There's nothing > intrinsically Wrong about doing the logic in Python. List comprehension > even makes set operations look nicer, and caching can provide > performance improvements. But this means writing an algorithm instead of describing what you want (declaratively, with SQL). I haven't done any benchmarking, but given all the indexing that can go on in an RDBMS, it seems to me that the iterative Python version of: select * from table_one where col_one in ( select col_two from table_two where blah) Is going to be slower since there isn't going to be the benefit of indexing. Caching won't make anything faster since the RDBMS is caching too. OTOH, the overhead of the RDBMS parsing, planning, etc. might be quite high. Hmmm... anybody care to speculate on this? I must benchmark this one day... > It could probably be argued that those complicated queries are a form of > premature optimization. Like most optimization, complex queries usually > are based on the requirements of pieces of code that would otherwise be > considered factored. Those queries may be an important form of > optimization, but I think that should still be accessible even if it > won't be as natural as using normal object access. I don't know if I agree with you about this... I'll have to think about it more. The point about using SQL is that you aren't thinking about optimization or algorithms at all. You're thinking of the data and relationships embodied therein--or at least you should be. In a perfect world, you figure out what data you're looking for, then describe what you want. It is the RDBMS that does the optimization to give you what you describe. It is only when it isn't performing well that one would even think of optimizing your SQL, adding more indices, etc. ...Edmund. |
From: Edmund L. <el...@in...> - 2003-05-30 00:35:35
|
I'm fiddling with SQLObject from CVS now, and I don't seem to be understanding the documentation about foreignKey. The manual says: """ foreignKey: You can give a class name (as a string) for foreignKey, which means that the column references another class (which is another row in another table). The column name should end with ID, and the database column is expected to end with _id. This will create another method in your class. For instance, if the personID column points to the Person class, then in addition to a personID attribute your class will have a person attribute which will return the corresponding Person instance. """ Base on this, I would expect: class ComponentCategory(SQLObject): _columns = [ StringCol("name", length=100), StringCol("description", default=None), IntCol("sequenceNum", default=None)] class Component(SQLObject): _columns = [ StringCol("name"), StringCol("categoryId", foreignKey="ComponentCategory", default=None), IntCol("sequenceNum", default=None)] To behave as follows: Instantiating: c = Component.new(name="test") And c should have an attribute "componentCategory" that returns either None, or an instance of class ComponentCategory. In fact, c does not have an attribute called "componentCategory". Am I not understanding something? ...Edmund. |
From: Ian B. <ia...@co...> - 2003-05-30 01:51:57
|
On Thu, 2003-05-29 at 19:35, Edmund Lian wrote: > class Component(SQLObject): > _columns = [ > StringCol("name"), > StringCol("categoryId", foreignKey="ComponentCategory", > default=None), > IntCol("sequenceNum", default=None)] > > To behave as follows: > Instantiating: > c = Component.new(name="test") > > And c should have an attribute "componentCategory" that returns either > None, or an instance of class ComponentCategory. > > In fact, c does not have an attribute called "componentCategory". Am I > not understanding something? It should be an attribute category, which is categoryId with the "Id" removed. Ian |
From: Edmund L. <el...@in...> - 2003-05-30 02:08:08
|
Ian Bicking wrote: > It should be an attribute category, which is categoryId with the "Id" > removed. Hmmm... I think there's a bug then. Here's what I'm seeing... My SQLObject module is: from SQLObject import * __connection__ = PostgresConnection(host="localhost", db="sqlobj", user="sqlobj", passwd="sqlobj") class ComponentCategory(SQLObject): _table = "st_component_category" _columns = [ StringCol("name", length=100), StringCol("description", default=None), IntCol("sequenceNum", default=None)] class Component(SQLObject): _table = "st_component" _columns = [ StringCol("name"), StringCol("categoryId", foreignKey="ComponentCategory", default=None), IntCol("sequenceNum", default=None)] ComponentCategory.createTable(ifNotExists=1) Component.createTable(ifNotExists=1) My test run and output is: Python 2.2.2 (#1, Mar 21 2003, 23:01:54) [GCC 3.2.3 20030316 (Debian prerelease)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> from test import * >>> cc = ComponentCategory.new(name="Test Cat") >>> cc <ComponentCategory 1 name='Test Cat' description=None sequenceNum=None> >>> comp = Component.new(name='Test Comp') >>> comp <Component 1 name='Test Comp' categoryId=None sequenceNum=None> >>> comp.__dict__ {'_SO_val_categoryId': None, '_SO_writeLock': <thread.lock object at 0x821c728>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test Comp', 'id': 1} >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 915, in __repr__ return '<%s %i %s>' \ AttributeError: 'ComponentCategory' object has no attribute 'id' Note that there is no category attribute. Unless I'm misreading the docs, I would expect that instantiating a new Component but not specifying a category or category ID should result in an instance with an attribute "category" with value None. Also, I would actually expect to instantiate Component and specify a category by name (or more inconveniently, pass in an instance of it I guess) rather than ID. Passing in a string gives: >>> comp = Component.new(name='Test Comp2', category='Test Cat') Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 747, in new kw[column.name] = getID(kw[column.foreignName]) File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 1059, in getID return int(obj) ValueError: invalid literal for int(): Test Cat Pass in an instance of ComponentCategory gives: >>> cc <ComponentCategory 1 name='Test Cat' description=None sequenceNum=None> >>> comp = Component.new(name='Test comp 3', category=cc) >>> comp <Component 3 name='Test comp 3' categoryId='1' sequenceNum=None> >>> comp.__dict__ {'_SO_val_categoryId': '1', '_SO_writeLock': <thread.lock object at 0x82db400>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test comp 3', 'id': 3} >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line 918, in __repr__ ' '.join(['%s=%s' % (name, repr(value)) for name, value in self._reprItems()])) TypeError: an integer is required Here is an example of what users probably don't expect to do--deal with object IDs: >>> comp = Component.new(name='Test comp 3', categoryId=1) >>> comp <Component 2 name='Test comp 3' categoryId='1' sequenceNum=None> Notice that the attribute "category" is still missing: >>> comp.__dict__ {'_SO_val_categoryId': '1', '_SO_writeLock': <thread.lock object at 0x8210ee8>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test comp 3', 'id': 2} ...Edmund. |
From: Ian B. <ia...@co...> - 2003-05-30 02:25:14
|
On Thu, 2003-05-29 at 21:07, Edmund Lian wrote: > Ian Bicking wrote: > > > It should be an attribute category, which is categoryId with the "Id" > > removed. > > Hmmm... I think there's a bug then. Here's what I'm seeing... > > My SQLObject module is: > > from SQLObject import * > > __connection__ = PostgresConnection(host="localhost", db="sqlobj", > user="sqlobj", passwd="sqlobj") > > class ComponentCategory(SQLObject): > _table = "st_component_category" > _columns = [ > StringCol("name", length=100), > StringCol("description", default=None), > IntCol("sequenceNum", default=None)] > > class Component(SQLObject): > _table = "st_component" > _columns = [ > StringCol("name"), > StringCol("categoryId", foreignKey="ComponentCategory", > default=None), > IntCol("sequenceNum", default=None)] > > ComponentCategory.createTable(ifNotExists=1) > Component.createTable(ifNotExists=1) > > > My test run and output is: > > Python 2.2.2 (#1, Mar 21 2003, 23:01:54) > [GCC 3.2.3 20030316 (Debian prerelease)] on linux2 > Type "help", "copyright", "credits" or "license" for more information. > >>> from test import * > >>> cc = ComponentCategory.new(name="Test Cat") > >>> cc > <ComponentCategory 1 name='Test Cat' description=None sequenceNum=None> > >>> comp = Component.new(name='Test Comp') > >>> comp > <Component 1 name='Test Comp' categoryId=None sequenceNum=None> > >>> comp.__dict__ > {'_SO_val_categoryId': None, '_SO_writeLock': <thread.lock object at > 0x821c728>, '_SO_val_sequenceNum': None, '_SO_val_name': 'Test Comp', > 'id': 1} > >>> comp.category > Traceback (most recent call last): > File "<stdin>", line 1, in ? > File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line > 915, in __repr__ > return '<%s %i %s>' \ > AttributeError: 'ComponentCategory' object has no attribute 'id' That's a weird error. It looks like it's instantiating a ComponentCategory object, but hitting an excepting when the object is printed. Hmm... looks like I wasn't handling None there properly -- instead of returning None it was returning a broken ComponentCategory object. Try CVS again. Note that the actual ComponentCategory instance will never be in the __dict__ of the category object -- only the id is stored. > Passing in a string gives: > > >>> comp = Component.new(name='Test Comp2', category='Test Cat') > Traceback (most recent call last): > File "<stdin>", line 1, in ? > File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line > 747, in new > kw[column.name] = getID(kw[column.foreignName]) > File "/usr/lib/python2.2/site-packages/SQLObject/SQLObject.py", line > 1059, in getID > return int(obj) > ValueError: invalid literal for int(): Test Cat It expects an id or a ComponentCategory object -- since you didn't pass an object, it tried to treat it like an integer id. Anyway, try it again from CVS and see if it works for you now. Ian |
From: Edmund L. <el...@in...> - 2003-05-30 03:40:45
|
Ian Bicking wrote: > It expects an id or a ComponentCategory object -- since you didn't pass > an object, it tried to treat it like an integer id. > > Anyway, try it again from CVS and see if it works for you now. Better, but still no joy... with the same data model, I get: Create category: >>> from test import * >>> compCat = ComponentCategory.new(name="Test Cat") >>> compCat <ComponentCategory 1 name='Test Cat' description=None sequenceNum=None> Create component with default category of None: >>> comp = Component.new(name="Test Comp") >>> comp <Component 1 name='Test Comp' categoryId=None sequenceNum=None> >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "<string>", line 1, in <lambda> AttributeError: 'Component' object has no attribute 'ComponentCategory' Create component with instance of ComponentCategory as argument for category: >>> comp = Component.new(name="Test Comp2", category=compCat) >>> comp <Component 2 name='Test Comp2' categoryId='1' sequenceNum=None> >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "<string>", line 1, in <lambda> AttributeError: 'Component' object has no attribute 'ComponentCategory' ...Edmund. |
From: Ian B. <ia...@co...> - 2003-05-30 03:49:44
|
On Thu, 2003-05-29 at 22:18, Edmund Lian wrote: > Ian Bicking wrote: > > > It expects an id or a ComponentCategory object -- since you didn't pass > > an object, it tried to treat it like an integer id. > > > > Anyway, try it again from CVS and see if it works for you now. > > Better, but still no joy... with the same data model, I get: Okay, my fix was just all wrong, I mixed up the argument order. CVS fixed this time, maybe. I really have to look at my unit tests again, because they really should be catching these things for me. I think I'm missing tests for all the easy things... |
From: Edmund L. <el...@in...> - 2003-05-30 03:59:03
|
Ian Bicking wrote: > Okay, my fix was just all wrong, I mixed up the argument order. CVS > fixed this time, maybe. Sorry, not really. My tests: >>> from test import * >>> cc = ComponentCategory(1) >>> comp = Component.new(name="Test Comp5") >>> comp <Component 5 name='Test Comp5' categoryId=None sequenceNum=None> >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "<string>", line 1, in <lambda> TypeError: _SO_foreignKey() takes exactly 3 arguments (2 given) >>> comp = Component.new(name="Test Comp6", category=cc) >>> comp <Component 6 name='Test Comp6' categoryId='1' sequenceNum=None> >>> comp.category Traceback (most recent call last): File "<stdin>", line 1, in ? File "<string>", line 1, in <lambda> TypeError: _SO_foreignKey() takes exactly 3 arguments (2 given) ...Edmund. |