Thread: [SQLObject] Recommended backend.
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Alan K. <sql...@xh...> - 2005-02-28 22:38:22
|
Greetings all, I'd like to solicit opinions on which database is the best backend to use for sqlobject. My primary requirements are, in order of importance 1. Robust transaction support 2. Robust concurrency support 3. Support for schema modification I've been using SQLite up to now, but I've found that the concurrency problems are a lot of hassle. Also, it's a hassle to modify the schema after creation. I read up on MySQL, but am concerned by the warnings in the documentation, i.e. "MySQLConnection supports all the features, though MySQL only supports transactions when using the InnoDB backend, and SQLObject currently does not have support for explicitly defining the backend when using createTable". I haven't used PostGres before, but it does look promising. General opinion seems to be that it is a very robust database. And I'm encouraged by the doc comment: "PostgresConnection supports transactions and all other features." Lastly, I have never even looked at FireBird. Is the comment in the documentation about Firebird still applicable, i.e. "Support is still young, so there may be some issues, especially with concurrent access, and especially using lazy selects". Thanks in advance for any info and opinions. Regards, Alan. |
From: Jamieson B. <jam...@ja...> - 2005-03-01 02:57:48
|
Alan, This question could easily start a holy war. ;-) That said, I think the following is generally agreed upon: 1) MySQL is one of the fastest databases around, particularly is you do not need transactions. (http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html) MySQL does support transactions as you note, and has for years -- since 3.23. Innodb is required, but you could certainly run debug on the SQLObject table definition and then create the table yourself using innodb. I don't know about SQLObject's specific support for MySQL transactions, but MySQL supports many table handlers and both BDB and InnoDB support transactions. (As does NDB with clustering.) Also, MySQL 5.0 Beta/5.1 supports the most important features, such as triggers, views, stored procedures, etc. Most versions of MySQL currently included with distributions can support things like subselects, foreign keys, etc. Most of the more advanced features require Innodb. If you go this route -- mix MyISAM for tables that don't need transactional support (transactions, row-level locks, and foreign keys/cascade deletes will slow down any database) and InnoDB for tables that do. MyISAM is very fast, fast, fast. Another nice feature of MySQL is that it's basically bulletproof and rarely requires any maintenance or administration. MySQL is also used in very large installations (i.e., Yahoo! Finance is completely run on MySQL, including real-time calculation of stock views, craigslist.org, livejournal.com, etc.) 2) That said, MySQL doesn't support even close to the full SQL command set, and PostgreSQL supports an incredibly large subset -- in many cases surpassing even the 800 lb gorilla Oracle. PostgreSQL is a very powerful and robust database. Another great thing about PostgreSQL is that there are solutions available from multiple vendors for things like load-balancing clustering and replication. For new applications, I'm heavily biased toward MySQL for speed and simplicity, but for rewrites/ports of existing applications from other databases, PostgreSQL is always the best solution. I don't know enough about Firebird or SAP-DB (now co-developed by SAP and MySQL) to be dangerous. These pages might be helpful: http://dev.mysql.com/tech-resources/crash-me.php comparing various databases; highlights weaknesses and strengths of MySQL. From SQLite, I'd definitely consider MySQL first.. but do remember this: it's not too painful to go from MySQL to Postgresql, but can be much more painful to go the other direction, especially if you use some of PostgreSQL's more advanced SQL features. -Jamie Alan Kennedy wrote: > Greetings all, > > I'd like to solicit opinions on which database is the best backend to > use for sqlobject. > > My primary requirements are, in order of importance > > 1. Robust transaction support > 2. Robust concurrency support > 3. Support for schema modification > > I've been using SQLite up to now, but I've found that the concurrency > problems are a lot of hassle. Also, it's a hassle to modify the schema > after creation. > > I read up on MySQL, but am concerned by the warnings in the > documentation, i.e. "MySQLConnection supports all the features, though > MySQL only supports transactions when using the InnoDB backend, and > SQLObject currently does not have support for explicitly defining the > backend when using createTable". > > I haven't used PostGres before, but it does look promising. General > opinion seems to be that it is a very robust database. And I'm > encouraged by the doc comment: "PostgresConnection supports > transactions and all other features." > > Lastly, I have never even looked at FireBird. Is the comment in the > documentation about Firebird still applicable, i.e. "Support is still > young, so there may be some issues, especially with concurrent access, > and especially using lazy selects". > > Thanks in advance for any info and opinions. > > Regards, > > Alan. > > > ------------------------------------------------------- > SF email is sponsored by - The IT Product Guide > Read honest & candid reviews on hundreds of IT Products from real users. > Discover which products truly live up to the hype. Start reading now. > http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click > _______________________________________________ > sqlobject-discuss mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss |
From: Ian B. <ia...@co...> - 2005-03-01 03:16:45
|
Jamieson Becker wrote: > From SQLite, I'd definitely consider MySQL first.. but do remember > this: it's not too painful to go from MySQL to Postgresql, but can be > much more painful to go the other direction, especially if you use some > of PostgreSQL's more advanced SQL features. Though if you're going through SQLObject, there's a good chance you won't use those advanced features since SQLObject doesn't support them ;) Generally if you stick to what SQLObject makes easy, you'll be using a lowest common denominator of database functionality. Incidentally, I think you could support stored procedures and some other things in SQLObject fairly easily, but I don't care to do it (I don't personally like stored procedures) and no one else seems to care enough to implement it. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Jonathan E. <jon...@si...> - 2005-03-01 15:23:02
|
Some things don't need OR support to be useful, though, like triggers. -Jonathan Ian Bicking wrote: > Though if you're going through SQLObject, there's a good chance you > won't use those advanced features since SQLObject doesn't support them |
From: Ian B. <ia...@co...> - 2005-03-01 03:12:51
|
Alan Kennedy wrote: > Greetings all, > > I'd like to solicit opinions on which database is the best backend to > use for sqlobject. > > My primary requirements are, in order of importance > > 1. Robust transaction support > 2. Robust concurrency support > 3. Support for schema modification I'd go for PostgreSQL. There's reasons to choose MySQL, but if transactions are at the top then PostgreSQL is going to be a better match -- not just because of its transaction support (which I believe is still better than MySQL, being more atomic), but because you probably care about the integrity of your data, and MySQL isn't very good there. Of course, you can implement some of that integrity checking in SQLObject, getting around some of MySQL's flaws. > Lastly, I have never even looked at FireBird. Is the comment in the > documentation about Firebird still applicable, i.e. "Support is still > young, so there may be some issues, especially with concurrent access, > and especially using lazy selects". I'd consider it still young, as it's not that widely used. I don't even have a testing environment for it anymore since I reinstalled my operating system (though I'll probably put that back at some point; maybe I'll even test MaxDB now that there's a debian package for it). Anyway, I find the configuration quite obtuse, and the environment generally challenging. It's supposed to have good Windows support; but at this point MySQL has very mature Windows support, and PostgreSQL has native Windows support with version 8, so that's where the future really is. As for MaxDB, I don't know... it's formerly proprietary and probably wonky (like Firebird), and I'd wait to see how the MaxDB/MySQL merge goes -- maybe the MySQL people can add some sanity to MaxDB. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |
From: Alan K. <sql...@xh...> - 2005-03-02 12:43:55
|
[Alan Kennedy] >> I'd like to solicit opinions on which database is the best backend to >> use for sqlobject. [Ian Bicking] > I'd go for PostgreSQL. There's reasons to choose MySQL, but if > transactions are at the top then PostgreSQL is going to be a better > match -- not just because of its transaction support (which I believe is > still better than MySQL, being more atomic), but because you probably > care about the integrity of your data, and MySQL isn't very good there. > Of course, you can implement some of that integrity checking in > SQLObject, getting around some of MySQL's flaws. Thanks to Ian, Jamieson and Jonathon for replying: your input was most valuable. I think it's time I bit the bullet and went for PostGres. I've been intrigued by it for a long time, particularly the ability to write server-side functionality in python. The only thing that ever held me back, and it wasn't a showstopper, was the lack of native Windows support. But that's been resolved now. So I'm going to give PostGres a try. Thanks again to those who replied. Cheers, Alan. |
From: Dave W. <da...@su...> - 2005-03-02 18:10:43
|
Ian, >> Lastly, I have never even looked at FireBird. Is the comment in the >> documentation about Firebird still applicable, i.e. "Support is still >> young, so there may be some issues, especially with concurrent access, >> and especially using lazy selects". > > I'd consider it still young, as it's not that widely used. I don't even > have a testing environment for it anymore since I reinstalled my > operating system (though I'll probably put that back at some point; > maybe I'll even test MaxDB now that there's a debian package for it). > > Anyway, I find the configuration quite obtuse, and the environment > generally challenging. It's supposed to have good Windows support; but > at this point MySQL has very mature Windows support, and PostgreSQL has > native Windows support with version 8, so that's where the future really > is. The SQLObject support for Firebird may be young. Firebird as a dbms is very mature, yet developing nicely. It is particularly strong in the following ways - zero management needed (essentially fit and forget) - uses a record versioning engine (postgres does too but is a much later implementation, Firebird has always worked that way). Good for balancing read only queries while doing updates. - triggers, stored procedures - "proper" transactions, full ACID support - great support for unicode and many other character sets - windows and linux versions for many years I have used extensively with Java for about 5 years and never had any data corruption or stability problems and generally good performance. Regards Dave -- Dave Warnock: http://42.blogs.warnock.me.uk |
From: Jonathan E. <jon...@si...> - 2005-03-02 18:30:12
|
Dave Warnock wrote: > The SQLObject support for Firebird may be young. Firebird as a dbms is > very mature, yet developing nicely. It is particularly strong in the > following ways > > - zero management needed (essentially fit and forget) Management requirements aren't a significant factor with MySQL or PostgreSQL, either. Oracle, maybe. :) > - uses a record versioning engine (postgres does too but is a much later > implementation, Firebird has always worked that way). I don't know about firebird, but postgresql's MVCC (multi-versioning concurrency control) system has been in place at least since version 6.5 circa 1996. Maybe firebird's is older still, but arguing that 9 years isn't enough to achieve stability doesn't hold water with me. -Jonathan |
From: David W. <da...@su...> - 2005-03-02 18:44:18
|
Jonathan, > > - zero management needed (essentially fit and forget) > > Management requirements aren't a significant factor with MySQL or > PostgreSQL, either. Oracle, maybe. :) PostgreSQL has typically needed more than Firebird with Vaccuum etc. But I agree with you. > > - uses a record versioning engine (postgres does too but is a much later > > implementation, Firebird has always worked that way). > > I don't know about firebird, but postgresql's MVCC (multi-versioning > concurrency control) system has been in place at least since version 6.5 > circa 1996. Maybe firebird's is older still, but arguing that 9 years > isn't enough to achieve stability doesn't hold water with me. I was not questioning PostgreSQL's stability. I have used it a lot with no problems. One could argue that a more recent implementation of MVCC is better. But Firebird (ex Interbase, ex something else) has had it a lot longer than 1996. Until recently Postgres did not have very good windows support and Mysql did not have any tranbsaction options therefore Firebird had a place nothing else could fill. Now ... Regards Dave |
From: Alan K. <sql...@xh...> - 2005-03-02 19:34:22
|
[Dave Warnock] >> The SQLObject support for Firebird may be young. Firebird as a dbms is >> very mature, yet developing nicely. It is particularly strong in the >> following ways [snip] >> - great support for unicode and many other character sets >> - windows and linux versions for many years [Jonathan Ellis] > Management requirements aren't a significant factor with MySQL or > PostgreSQL, either. Oracle, maybe. :) Just following up with an important detail I've uncovered now that I've gone to install PostGres 8 on Windows 2000: it doesn't support UTF-8 encoding! Or any other Unicode-based encoding, it appears. http://pginstaller.projects.postgresql.org/FAQ_windows.html#2.6 Which sounds to me like exactly the kind of omission I want to avoid: I'm uncertain if that means that SQLObject.UnicodeCol would not work on Windows+PG8. If it doesn't, I'm not even sure that it would be possible to work around. The list of encodings offered on PG8 installation is EUC_JP (*) EUC_KR (*) ISO-8859-5 (*) ISO-8859-6 (*) ISO-8859-7 (*) ISO-8859-8 (*) JOHAB KOI-8R LATIN-1 (*) LATIN-2 (*) LATIN-3 (*) LATIN-4 (*) LATIN-5 (*) LATIN-9 (*) MULE_INTERNAL SQL_ASCII (*) WIN866 WIN874 WIN1250 (*) WIN1251 (*) WIN1256 WIN1258 I have asterisked the encodings that I know will *not* support the full range of characters that I need, which includes names from many continents and cultures: North and South America, Asia (including Korea, Japan, Thailand, etc), Middle-East (Arabic), Europe and Africa. The audience is scientific, and thus mostly English speaking. Indeed many have "Westernised" their names, i.e. spell their names using 7-bit ASCII, for precisely the reason of poor or buggy encoding support on email, WWW, etc. But that still doesn't solve my problem with addresses, organisation names, etc. My gut feeling tells me that none of the above will support the full range of characters I will need. And I certainly don't want to spend several days researching the subject to find out. Maybe I'll take a good look at FireBird: it certainly sounds stable enough to not present any problems. I like that there's been a Windows version for years, and I like the sound of "Great support for Unicode". What I really need to do is have a comprehensive test suite for my SQLOject schema/database and run it against multiple database backends. Regards, Alan. |
From: Ian B. <ia...@co...> - 2005-03-02 21:01:48
|
Alan Kennedy wrote: > Just following up with an important detail I've uncovered now that I've > gone to install PostGres 8 on Windows 2000: it doesn't support UTF-8 > encoding! Or any other Unicode-based encoding, it appears. > > http://pginstaller.projects.postgresql.org/FAQ_windows.html#2.6 > > Which sounds to me like exactly the kind of omission I want to avoid: > I'm uncertain if that means that SQLObject.UnicodeCol would not work on > Windows+PG8. If it doesn't, I'm not even sure that it would be possible > to work around. FWIW, UnicodeCol doesn't use the database's encodings, instead it encodes values before putting them in the database. There was some debate before about how exactly it should work -- one person instead proposed that all queries should be composed in unicode and then encoded just before being sent to the database, which would resolve some additional problems. Or... if SQLObject supported db-api parameters, *then* the database could use native encoding (but that's exactly what you can't do on Windows with PostgreSQL). But that's a significant change to SQLObject, unless there's a way of indicating what encoding is being used in the SQL, which would be a database-specific setting. Of course if the database isn't aware of the encoding, some database functions won't work properly on UTF-8 (e.g., case-changing functions). Maybe that's a mixed message: SQLObject does its own encoding, ignoring the database encoding whether you like it or not; and there are good reasons to like it and to not like it. Also, I would expect this issue to be resolved before too long, as PostgreSQL on Windows is still very young. -- Ian Bicking / ia...@co... / http://blog.ianbicking.org |