From: Ian B. <ia...@co...> - 2003-04-10 05:22:21
|
I hope you don't mind me copying you to the list. On Wed, 2003-04-09 at 04:15, Steve Holden wrote: > Ian: > > I've just spent a little time looking at SQLObject, as I'm always interested > in object-relational mapping frameworks. It's a nice idea, and please bear > in mind these remarks relate to my own use of it. Whether you take them > seriously enough to think about modifying something that clearly works for > you is entirely up to you, and this isn't intended as criticism of your > efforts. At leat you know that someone's taken ther trouble to review > SQLObject. Specifically: > > 1. You document the magic _idName, but people like me who only read the code > samples end up using _id instead, and getting and "Unknown column id" SQL > error. Yes, you weren't the only one caught by this. > 2. I found the name translations imposed by your scheme a little confusing. > Is this because you've had a lot of experience with bizarre databases where > people have taken liberties with the names (like including spaces in table > and/or attribute names)? No, I haven't particularly. Right now reasonable names should all work, but spaces in attribute names won't. I suppose it wouldn't be hard to add the proper quotes in to make that possible (just backquotes, right?), but I resist such forcefully bad names :-/ > Perhaps it might be better to avoid discussion of > these issues until after the simple examples. Personally I think I'd prefer > it if you used some other mechanism to map object names to database names > (the one you use for columns is better than the one you use for tables) in > those cases where python names can't be used for database objects. Yes, it would probably make sense to start with automatic schema generation (which is fast to get working with), and then have a section on ways to adapt your class to a pre-existing table. > 3. You talk about "relations" when I suspect you really mean > *relationships*. Formally, a relation is what a table in a relational > database represents, and it contains occurrences of a single entity-type > identified by primary key value. A relationship is a mapping from > occurrences of one entity-type to occurrences of another entity-type. Noted and changed. > 4. The whole business of using "id" as a default name for a primary key is > somewhat contrived, and increases the difficulty of mapping SQLObject onto > existing database schemas. I don't have a lot of experience with pre-existing schemas, and I've just adopted the style that I was first introduced to. I realize it's common to use tablename_id or tablenameid for the primary key, though I don't see any particular advantage besides some sort of implicit column matching between tables. At some point I'll probably make some sort of Style object which you can use to give your default method/database mapping functions. Then people who use different conventions don't have to go through too much trouble. > 5. Your assertion that MySQL does not support transactions is out of date, > since they can be supported by databases built from InnoDB tables, I believe > (or have I imagined this?). That's true. But I've yet to meet someone actually using transactions, and my own (admittedly brief) attempts to get transactions working were unsuccessful. I suppose it's possible, and there's no particular barriers in place to keep someone from using transactions with MySQL (if MySQLdb supports them...?), but until someone does it I find MySQL's claim less than convincing. > 6. Under "Creating and Dropping Tables" you discuss creating a *database* > rather than a *table*. FileMaker has infected me with its nefarious terminology! > 7. I don't see any support for tables with composite primary keys. Is this > an omission you plan to rectify? I don't know. I like single, simple primary keys. They work well. If the table is really so minimal that it can't stand to have an extra field for the id, then I suspect a SQLObject class is too heavy for it (like with the join tables). Of course, when adapting to a pre-existing schema you might not have as many options. Maybe it wouldn't be that hard to fudge it (maybe make the ID into a tuple), but SQLObject really expects for each object to have a single ID. > Anyway, thanks for an interesting piece of software! And thank you for your careful reading of the documentation. Ian |
From: Bud P. B. <bu...@si...> - 2003-04-10 13:32:33
|
On 10 Apr 2003 00:23:11 -0500 Ian Bicking <ia...@co...> wrote: > I hope you don't mind me copying you to the list. > > On Wed, 2003-04-09 at 04:15, Steve Holden wrote: .... > > 4. The whole business of using "id" as a default name for a primary key is > > somewhat contrived, and increases the difficulty of mapping SQLObject onto > > existing database schemas. For what it's worth, here are some thoughts on the topic. I personally believe having id's is a great thing. Using primary keys with any sort of business meaning will get you into trouble sooner or later. So if SQLObject didn't add it, I would... (BTW, this argument is also relevant for composite keys--they usually have business meaning...). The only problem may occur if one uses SQLObject with pre-existing databases that don't have a comparable field... Correct me if I'm wrong, but it seems that SQLObject needs an id column of type integer. Could that be relaxed to be another type (a string). In this case, one possible solution would be to use an option "primaryKey=True" for Col. If a primary key is defined in _columns, then it is used, else, id is implied as is currently the case. Another thought in this context is that PostgreSQL already inserts an object id column for every table. But maybe it's not a good idea to use since other dbms don't have equivalents... One thing on my wishlist would be to (optionally) create a globally unique id for the object. That could be controlled by an additional option "globallyUnique=True" that would go together with PrimaryKey, Unique, or alternateID (i.e., everything that implies Unique). > > 7. I don't see any support for tables with composite primary keys. Is this > > an omission you plan to rectify? > > I don't know. I like single, simple primary keys. They work well. If > the table is really so minimal that it can't stand to have an extra > field for the id, then I suspect a SQLObject class is too heavy for it > (like with the join tables). Of course, when adapting to a pre-existing > schema you might not have as many options. Maybe it wouldn't be that > hard to fudge it (maybe make the ID into a tuple), but SQLObject really > expects for each object to have a single ID. For the above considerations, I think a single object id is usually a good idea. In one project I started out with composite keys and as the data structure evolved added an autoincrement key to keep it simpler and more changeable. However, it may be nice to have composite field indices on tables and have an equivalent to alternateID that creates a method with multiple arguments. cheers -bud |
From: Steve H. <sh...@ho...> - 2003-04-10 21:36:00
|
[Ian Bicking] > I hope you don't mind me copying you to the list. > Not really, it's not like we were discussing my medical record or anything :-) > On Wed, 2003-04-09 at 04:15, Steve Holden wrote: > > Ian: > > > > I've just spent a little time looking at SQLObject, as I'm always interested > > in object-relational mapping frameworks. It's a nice idea, and please bear > > in mind these remarks relate to my own use of it. Whether you take them > > seriously enough to think about modifying something that clearly works for > > you is entirely up to you, and this isn't intended as criticism of your > > efforts. At leat you know that someone's taken ther trouble to review > > SQLObject. Specifically: > > > > 1. You document the magic _idName, but people like me who only read the code > > samples end up using _id instead, and getting and "Unknown column id" SQL > > error. > > Yes, you weren't the only one caught by this. > Just something that needed fixing: no biggie. > > 2. I found the name translations imposed by your scheme a little confusing. > > Is this because you've had a lot of experience with bizarre databases where > > people have taken liberties with the names (like including spaces in table > > and/or attribute names)? > > No, I haven't particularly. Right now reasonable names should all work, > but spaces in attribute names won't. I suppose it wouldn't be hard to > add the proper quotes in to make that possible (just backquotes, > right?), but I resist such forcefully bad names :-/ > Right. I think you should perhaps in that case state that normal non-tranlsated names are the default. I was fooled into thinking I *had* to use a single intial cap to access the existing schema, which is probably wrong. > > Perhaps it might be better to avoid discussion of > > these issues until after the simple examples. Personally I think I'd prefer > > it if you used some other mechanism to map object names to database names > > (the one you use for columns is better than the one you use for tables) in > > those cases where python names can't be used for database objects. > > Yes, it would probably make sense to start with automatic schema > generation (which is fast to get working with), and then have a section > on ways to adapt your class to a pre-existing table. > There you go. Concentrate on benefits, not features ;-) > > 3. You talk about "relations" when I suspect you really mean > > *relationships*. Formally, a relation is what a table in a relational > > database represents, and it contains occurrences of a single entity-type > > identified by primary key value. A relationship is a mapping from > > occurrences of one entity-type to occurrences of another entity-type. > > Noted and changed. > > > 4. The whole business of using "id" as a default name for a primary key is > > somewhat contrived, and increases the difficulty of mapping SQLObject onto > > existing database schemas. > > I don't have a lot of experience with pre-existing schemas, and I've > just adopted the style that I was first introduced to. I realize it's > common to use tablename_id or tablenameid for the primary key, though I > don't see any particular advantage besides some sort of implicit column > matching between tables. > I see. Generally you might find that many database designers and users aren't terribly (as) methodical (as you seem to be) in naming their data structures, so the more flexibility the better here. Would it be possible (e.g.) to define a subclass of Col called PK that had the additional semantics of identifying the named column as key. Alternatively an extra Col.__init__() argument PK=False that flags a Col as being a key column? > At some point I'll probably make some sort of Style object which you can > use to give your default method/database mapping functions. Then people > who use different conventions don't have to go through too much trouble. > Style is a nice idea, but again this implies method in the DB madness. I personally would be OK with some designs, since I do have naming conventions, but I'm not rigorous about their use (yet). Let's see, a 3-character table prefix, hmmm, ... > > 5. Your assertion that MySQL does not support transactions is out of date, > > since they can be supported by databases built from InnoDB tables, I believe > > (or have I imagined this?). > > That's true. But I've yet to meet someone actually using transactions, > and my own (admittedly brief) attempts to get transactions working were > unsuccessful. I suppose it's possible, and there's no particular > barriers in place to keep someone from using transactions with MySQL (if > MySQLdb supports them...?), but until someone does it I find MySQL's > claim less than convincing. > I guess it's a YAGNI then. > > 6. Under "Creating and Dropping Tables" you discuss creating a *database* > > rather than a *table*. > > FileMaker has infected me with its nefarious terminology! > Wash your mouth out with SOAP :-) > > 7. I don't see any support for tables with composite primary keys. Is this > > an omission you plan to rectify? > > I don't know. I like single, simple primary keys. They work well. If > the table is really so minimal that it can't stand to have an extra > field for the id, then I suspect a SQLObject class is too heavy for it > (like with the join tables). Of course, when adapting to a pre-existing > schema you might not have as many options. Maybe it wouldn't be that > hard to fudge it (maybe make the ID into a tuple), but SQLObject really > expects for each object to have a single ID. > Well, I think I'd like the id to be a tuple, please. Refactoring would presumably consist of bracketing all expressions assigned to the ID value in the existing code? > > Anyway, thanks for an interesting piece of software! > > And thank you for your careful reading of the documentation. > Well, not careful enough, since I still haven' treally delved into many-to-many relationships. I'd also like to hear ideas about a possible "web form rendering" mixin that allows a collection of columns to be input from, or populate, a form in a web page. regards -- Steve Holden http://www.holdenweb.com/ Python Web Programming http://pydish.holdenweb.com/pwp/ Did you miss PyCon DC 2003? Would you come to PyCOn DC 2004? |
From: Ian B. <ia...@co...> - 2003-04-11 05:50:28
|
On Thu, 2003-04-10 at 16:10, Steve Holden wrote: > > > 4. The whole business of using "id" as a default name for a primary key > is > > > somewhat contrived, and increases the difficulty of mapping SQLObject > onto > > > existing database schemas. > > > > I don't have a lot of experience with pre-existing schemas, and I've > > just adopted the style that I was first introduced to. I realize it's > > common to use tablename_id or tablenameid for the primary key, though I > > don't see any particular advantage besides some sort of implicit column > > matching between tables. > > > I see. Generally you might find that many database designers and users > aren't terribly (as) methodical (as you seem to be) in naming their data > structures, so the more flexibility the better here. Would it be possible > (e.g.) to define a subclass of Col called PK that had the additional > semantics of identifying the named column as key. Alternatively an extra > Col.__init__() argument PK=False that flags a Col as being a key column? There is a KeyCol which I need to fill out a bit more, which will fill that role. As far as methodology in naming, I'd rather everyone was methodical. Having a style object could help that, because then you could make explicit your style and when you create new tables or new columns it'll be easier to stick with that style than otherwise. > > At some point I'll probably make some sort of Style object which you can > > use to give your default method/database mapping functions. Then people > > who use different conventions don't have to go through too much trouble. > > > Style is a nice idea, but again this implies method in the DB madness. I > personally would be OK with some designs, since I do have naming > conventions, but I'm not rigorous about their use (yet). Let's see, a > 3-character table prefix, hmmm, ... As far as methodology in naming, I'd rather everyone was methodical. Having a style object could help that, because then you could make explicit your style and when you create new tables or new columns it'll be easier to stick with that style than otherwise. > > > 7. I don't see any support for tables with composite primary keys. Is > this > > > an omission you plan to rectify? > > > > I don't know. I like single, simple primary keys. They work well. If > > the table is really so minimal that it can't stand to have an extra > > field for the id, then I suspect a SQLObject class is too heavy for it > > (like with the join tables). Of course, when adapting to a pre-existing > > schema you might not have as many options. Maybe it wouldn't be that > > hard to fudge it (maybe make the ID into a tuple), but SQLObject really > > expects for each object to have a single ID. > > > Well, I think I'd like the id to be a tuple, please. Refactoring would > presumably consist of bracketing all expressions assigned to the ID value in > the existing code? On the Python side a tuple wouldn't be that big a deal, dynamic typing and all, but the SQL generation would have to change. Maybe it wouldn't be that hard, _idName becomes a tuple too and you're WHERE clauses just become slightly more complex. I dunno... Ian |
From: Ian B. <ia...@co...> - 2003-04-11 08:44:50
|
On Thu, 2003-04-10 at 16:10, Steve Holden wrote: > I'd also like to hear ideas about a possible "web form rendering" mixin that > allows a collection of columns to be input from, or populate, a form in a > web page. Oh, I forgot to reply to this too. This is definitely on my mind. I also wrote FunFormKit (funformkit.sf.net), which is a form rendering/validation library. I'll be the first to admit it's too complex, and I've wanted to reimplement it for some time... now my focus is on reimplementing it with SQLObject in mind (though without any actual ties). It probably wouldn't be that hard to do this rendering right now with FFK -- Luke has mentioned at least using some stuff from FFK with SQLObject -- but SQLObject's design makes me happy, and I want the entire system to make me happy, and FFK doesn't do that for me. It's hard, though, because FFK is fairly advanced -- more advanced than any other form processor I've seen (though I haven't done a survey of alternatives in some time). I want my new system to be just as advanced, but a high bar can feel stifling (excuse the mixed metaphor). Ian |
From: Ian B. <ia...@co...> - 2003-04-11 06:36:21
|
On Thu, 2003-04-10 at 08:31, Bud P.Bruegger wrote: > > > 4. The whole business of using "id" as a default name for a primary key is > > > somewhat contrived, and increases the difficulty of mapping SQLObject onto > > > existing database schemas. > > For what it's worth, here are some thoughts on the topic. > > I personally believe having id's is a great thing. Using primary keys > with any sort of business meaning will get you into trouble sooner or > later. So if SQLObject didn't add it, I would... (BTW, this argument > is also relevant for composite keys--they usually have business > meaning...). The only problem may occur if one uses SQLObject with > pre-existing databases that don't have a comparable field... > > Correct me if I'm wrong, but it seems that SQLObject needs an id > column of type integer. Could that be relaxed to be another type (a > string). Right now I'm sure I have made the assumption it is an integer in several places (though probably all such assumptions have been moved to DBConnection now, which makes it easier). It wouldn't be hard to relax that. > In this case, one possible solution would be to use an option > "primaryKey=True" for Col. If a primary key is defined in _columns, > then it is used, else, id is implied as is currently the case. I dunno... the object ID is special, and SQLObject treats it as special. It is assumed to be immutable, for instance, as any good ID should be. It's not a column like the others are, and I'm comfortable with it being a special case. > Another thought in this context is that PostgreSQL already inserts an > object id column for every table. But maybe it's not a good idea to > use since other dbms don't have equivalents... > > One thing on my wishlist would be to (optionally) create a globally > unique id for the object. That could be controlled by an additional > option "globallyUnique=True" that would go together with PrimaryKey, > Unique, or alternateID (i.e., everything that implies Unique). You mean add OIDs onto the databases that don't have it. MiddleKit does this, and it's one thing that annoyed me about it, though I can understand the motivation. It's an interesting idea, but I'm not sure how to make it work within the bounds of SQL, and in a way that's both useful and transparent. > > > 7. I don't see any support for tables with composite primary keys. Is this > > > an omission you plan to rectify? > > > > I don't know. I like single, simple primary keys. They work well. If > > the table is really so minimal that it can't stand to have an extra > > field for the id, then I suspect a SQLObject class is too heavy for it > > (like with the join tables). Of course, when adapting to a pre-existing > > schema you might not have as many options. Maybe it wouldn't be that > > hard to fudge it (maybe make the ID into a tuple), but SQLObject really > > expects for each object to have a single ID. > > For the above considerations, I think a single object id is usually a > good idea. In one project I started out with composite keys and as > the data structure evolved added an autoincrement key to keep it > simpler and more changeable. > > However, it may be nice to have composite field indices on tables and > have an equivalent to alternateID that creates a method with multiple > arguments. If it's just the method, that's trivial enough to create on your own that it doesn't seem like a big deal. For instance, if a and b are your composite fields, the method would look like: def selectAB(self, a, b): return self.selectBy(a=a, b=b)[0] Ian |
From: Bud P. B. <bu...@si...> - 2003-04-11 07:38:06
|
On 11 Apr 2003 01:37:10 -0500 Ian Bicking <ia...@co...> wrote: > > Another thought in this context is that PostgreSQL already inserts an > > object id column for every table. But maybe it's not a good idea to > > use since other dbms don't have equivalents... > > > > One thing on my wishlist would be to (optionally) create a globally > > unique id for the object. That could be controlled by an additional > > option "globallyUnique=True" that would go together with PrimaryKey, > > Unique, or alternateID (i.e., everything that implies Unique). > > You mean add OIDs onto the databases that don't have it. MiddleKit does > this, and it's one thing that annoyed me about it, though I can > understand the motivation. It's an interesting idea, but I'm not sure > how to make it work within the bounds of SQL, and in a way that's both > useful and transparent. Nope, the second paragraph I wrote above is totally independent of the first... To reword it in a more clear way (hopefully), I thought that instead of a relatively short integer for id that makes it unique in the table (or maybe the database?), to use a much longer id that is globally unique (at least with a very high probability). This is called different names by different people... There are algorithms around to do it. They typically take things like the MAC address of the machine and the time into account... The benefit of globally unique id's is that it makes future database mergers and restructuring in organizations MUCH easier... --b /----------------------------------------------------------------- | Bud P. Bruegger, Ph.D. | Sistema (www.sistema.it) | Via U. Bassi, 54 | 58100 Grosseto, Italy | +39-0564-411682 (voice and fax) \----------------------------------------------------------------- |
From: Ian B. <ia...@co...> - 2003-09-16 15:49:26
|
On Tuesday, September 16, 2003, at 08:20 AM, Mark Mueller wrote: > Ian -- > > Very nice job with the SQLObject project. I am > considering using it for several projects. > > I like to model problems using the BON technique, > however, most middleware does not map to the program > space in a way that allows me to go easily from the > BON diagram to the program. SQLObject help my by > hiding the details. > > Thanks for all your work. I'm glad you enjoy it. You should post questions to the mailing list so that everyone can participate, answer, or hear the answer. > I do have a question. I have two classes arranged in > a parent-child (one-to-many) relationship. I need to > be able to have my collection in the parent object > include only a subset of the child items. (For > example, a customer's account object that has a > transactions collection that includes transactions for > a date range.) My problem is that the account object, > automatically, gives me all the transations. Is there > a way to filter the child objects? You have to construct the select yourself, like (untested): class Account(SQLObject): transactions = ForeignKey('Trans') def transactionsDuring(self, start=None, end=None): query = Trans.q.accountID == self.id if start: query = AND(query, Trans.q.eventDate > start) if end: query = AND(query, Trans.q.eventDate < end) return Trans.select(query) Ian |
From: Ian B. <ia...@co...> - 2004-06-24 05:55:22
|
Hi. Sorry about not being involved the last couple weeks. It's been hard to keep up with SQLObject, mostly because I'm not actually using it myself at the moment. I'm trying to change that, but I have a hard time keeping motivated when I don't have any direct needs. But I don't mind if people hassle me about things like the wiki or repository. Those are both back up. Please email me directly if there are things that need to be done. I'll try to catch up, though I'm about to go out of town for a week and I won't be able to look at anything before then. On Jun 23, 2004, at 12:06 PM, Justus Pendleton wrote: > Just wondering if you're still maintaining SQLObject? The subversion > respository has been down for a while and the wiki for even longer. I > haven't seen you post anything on the mailing list in the past couple > of weeks, either. |
From: Ivo v. d. W. <iv...@am...> - 2004-06-24 08:07:56
|
Ian Bicking wrote: > Hi. Sorry about not being involved the last couple weeks. It's been > hard to keep up with SQLObject, mostly because I'm not actually using it > myself at the moment. I'm trying to change that, but I have a hard time > keeping motivated when I don't have any direct needs. > > But I don't mind if people hassle me about things like the wiki or > repository. Those are both back up. Please email me directly if there > are things that need to be done. I'll try to catch up, though I'm about > to go out of town for a week and I won't be able to look at anything > before then. > If anyone sends improvements/patches, would you still be available (timely) to check/add them? Or would that have to wait as well? I now onderstand you don't have the time / motivation to add my suggestions - I could give it a try myself. Cheers Ivo -- Drs. I.R. van der Wijk -=- Korte Leidsedwarsstraat 12 Amaze 1017 RC Amsterdam, NL -=- T +31-20-4688336 F +31-20-4688337 Zope/Plone/Content Management W http://www.amaze.nl E in...@am... Open Source Solutions W http://vanderwijk.info E iv...@am... Consultancy PGP http://vanderwijk.info/pgp |
From: Ian B. <ia...@co...> - 2004-06-24 20:49:12
|
Ivo van der Wijk wrote: > Ian Bicking wrote: > >> Hi. Sorry about not being involved the last couple weeks. It's been >> hard to keep up with SQLObject, mostly because I'm not actually using >> it myself at the moment. I'm trying to change that, but I have a hard >> time keeping motivated when I don't have any direct needs. >> >> But I don't mind if people hassle me about things like the wiki or >> repository. Those are both back up. Please email me directly if >> there are things that need to be done. I'll try to catch up, though >> I'm about to go out of town for a week and I won't be able to look at >> anything before then. >> > > If anyone sends improvements/patches, would you still be available > (timely) to check/add them? Or would that have to wait as well? > > I now onderstand you don't have the time / motivation to add my > suggestions - I could give it a try myself. Several people on the list have commit access, and I'm willing to give access to other people as well. People should feel particularly free to develop in a branch on the repository if they have ideas they want to experiment with or share. Anyone who is interested should email me off-list. Ian |
From: Ian B. <ia...@co...> - 2005-04-01 16:55:55
|
Thomas Lee wrote: > I=92m not sure if this is the proper channel to address this question = so=20 > my apologies if not. Questions should go to the discussion list. > My company has been working with SQLObject with great benefit but now=20 > we=92re encountering some performance issues. >=20 > Are there any plans to update SQLObject in the near future? If yes, doe= s=20 > it involve enhancing the caching feature? I don't have any plans to change anything related to performance. There=20 are updates in the works, and a release shouldn't be too far away. If=20 you can describe the problems you are having maybe someone will have an=20 idea. > Thanks for your time and thanks for a cool product. --=20 Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Ian B. <ia...@co...> - 2007-08-02 21:08:48
|
Pablo Fernandez Mantecon wrote: > Hello Ian, I am an spanish student of computer engineering and I am > working on my final graduate work. I have to make an ORM and I had > chosen the SQLObject (I have to use MySQL and pyhton), but I am working > on windows. Exist any version of SQLObject to work in windows? It should work fine, but I haven't tried it. Maybe someone on the discussion list can offer more practical experience. -- Ian Bicking : ia...@co... : http://blog.ianbicking.org : Write code, do good : http://topp.openplans.org/careers |
From: Oleg B. <ph...@ph...> - 2007-08-02 21:21:02
|
On Thu, Aug 02, 2007 at 04:08:39PM -0500, Ian Bicking wrote: > Pablo Fernandez Mantecon wrote: > Exist any version of SQLObject to work in windows? > > It should work fine, but I haven't tried it. Maybe someone on the > discussion list can offer more practical experience. I use SQLObject in commercial programs with Postgres and SQLite; I develop them in Linux and debug in w32; w32 distribution is built using py2exe and InnoSetup. SQLObject works fine on w32, the only thing you need is a DB API driver, so search the net for MySQLdb.pyd or such. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |