Re: [OT] SQL woes
On 22 Aug 2003 06:45:21 -0700, (Randal L. Schwartz) wrote: On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. Bah. Isn't ubiquity also Windows' only real selling point? -- Peter Haworth [EMAIL PROTECTED] I just don't want to commit to that--we're going to have enough fun getting perl 6 chewing through its native code [without translating perl 5 code]. I'd rather not take on more fun than one human being can withstand... -- Dan Sugalski
Re: [OT] SQL woes
Peter == Peter Haworth [EMAIL PROTECTED] writes: Peter On 22 Aug 2003 06:45:21 -0700, (Randal L. Schwartz) wrote: On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. Peter Bah. Isn't ubiquity also Windows' only real selling point? Right. I didn't say it was a good reason. I just said it's the only reason. :) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Re: [OT] SQL woes
Any real life experience with Firebird (the database) I'd love to hear about. Hi, I've used 'Firebird' in its previous incarnation as 'Interbase'. Although things have no doubt moved on, here are some of my experiences with Interbase. I was contracted to speed up an ad banner exchange system in late 2001. So high transactions, reliable reporting, and quick response times was the order of the day - at the heart of the system lay Interbase. The 'impression' counting subsystem was extremely slow. Banners on the network took two seconds longer than the competitors to display. The impression table was more than a million rows and starting to groan under the weight of too many indices and Interbase table locking. So off came most of the indices and table locking and things started to pick up speed. But I felt Interbase seemed uncomfortable handling large tables and high transactions. We stress tested the database server on the staging machine and it seemed to handle the hits. Unfortunately the single-CPU staging machine never equated to the dual-CPU linux production machine. Interbase would crash intermittently when run in dual-CPU mode on production. So we had to hobble the production machine to one-CPU for it to work reliably. Finally, every two weeks on average the database would become 'corrupted', requiring a full restore. This had something to do with Interbase's internal query logging/rollback subsystem. Needless to say I don't recommend using Interbase. Firebird on the other hand may have addressed all these problems - but caveat emptor - buyer beware! NIge Nigel Hamilton Turbo10 Metasearch Engine email: [EMAIL PROTECTED] tel:+44 (0) 207 987 5460 fax:+44 (0) 207 987 5468 http://turbo10.com Search Deeper. Browse Faster.
Re: [OT] SQL woes
Toby Corkindale wrote about SQLite: Transactions, sort-of, in that you get them within a single query; but there's poor support for simultaneous-request stuff. Hmm. To my mind that's more like not than sort-of. It's confusing enough evaluating packages, without people making stuff up, or being diabolically optimistic (yes, the parasol will also keep rain off - as long as it doesn't rain heavily). A transaction on a single statement will protect you from certain rogue statements that try and delete lots, or some uniqueness constraints. It will also protect you from sudden power failure or OS crash at that point (uh, maybe). Transactions on multiple statements are MUCH more useful, because a group of multiple statements is far more likely to screw up somewhere - and transactions are largely for catching stuff that screws up before it screws up your database, IME. I'm sure they're much easier for the DB designer to implement, but that's not the point. (: IMHO the really useful thing about SQLLite is that it'll go straight away, without any admin, because it's not using a client/server model and there's no server to set up. That way you can build some package that uses SQLite initially, and if/when/as it scales up, someone can plug in Orabble/SomeonesSQL/PostGrumble/whatever. Maybe. Cheers ti
Re: [OT] SQL woes
Dominic Mitchell [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: What about Firebird? http://firebird.sourceforge.net/ does anyone here have personal experience? On paper (well, online) it compares favorably to PostgreSQL, and it can be easily embedded. It's an excellent browser, much quicker than mozilla on it's own. Oh. Databases. Bother. Thats my problem - I'm supposed to be researching 'preferred' databases for my job, and while Firebird looks good no one got any experience with it. Currently I'm recommending SQLite for small, embedded work (single user CD-ROMs with a lot of data in very simple databases) and PostgreSQL for the big stuff. Any real life experience with Firebird (the database) I'd love to hear about. Richard -Dom
Re: [OT] SQL woes
[EMAIL PROTECTED] [EMAIL PROTECTED] wrote: What about Firebird? http://firebird.sourceforge.net/ does anyone here have personal experience? On paper (well, online) it compares favorably to PostgreSQL, and it can be easily embedded. It's an excellent browser, much quicker than mozilla on it's own. Oh. Databases. Bother. -Dom -- | Semantico: creators of major online resources | | URL: http://www.semantico.com/ | | Tel: +44 (1273) 72 | | Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: [OT] SQL woes
Tony Bowden [EMAIL PROTECTED] wrote: On Fri, Aug 22, 2003 at 03:30:48PM +0100, Andy Ford wrote: Well thanks for summing that up - it was an interesting read!! Currently I use mySQL only and don't really need transactional stuff. Now triggers I can see a need for!! Triggers at the db level will bite you hard at some point. If you're going to have multiple people building your application make sure you have VERY strict rules on adding triggers. Unchecked they lead to an unmaintainable mess quicker than most other things can ... Many people say the same thing about perl code in general. Like all tools you need to know when to say no. At the moment, we're just using triggers to put an mtime field onto all of our rows, and this works well. But I'd want to think carefully about more advanced uses. -Dom -- | Semantico: creators of major online resources | | URL: http://www.semantico.com/ | | Tel: +44 (1273) 72 | | Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: [OT] SQL woes
Roger Burton West [EMAIL PROTECTED] wrote: On Thu, Aug 21, 2003 at 08:51:49PM +0100, Colin Magee wrote: So this SQLite seems like a great idea IF it makes setting up and administering a dbase simple. Unfortunately I just looked up where I can download it, but didn't see any example code showing it working with Perl, or any documentation about how to use it with Perl. Please can you inform what do I have to do/install in order to start creating, updating and querying the database from Perl? DBD::SQLite. You don't even need to install a separate SQLite; the CPAN module includes everything you need. And once you've got that installed, you can use dbish from the DBI distribution to do adhoc queries / updates over it. Hmmm, looks like it's migrated to DBI-Shell. I'll have to install that. -Dom -- | Semantico: creators of major online resources | | URL: http://www.semantico.com/ | | Tel: +44 (1273) 72 | | Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: [OT] SQL woes
At 09:03 + 8/23/03, Dominic Mitchell wrote: Tony Bowden [EMAIL PROTECTED] wrote: Unchecked they lead to an unmaintainable mess quicker than most other things can ... Many people say the same thing about perl code in general. Like all tools you need to know when to say no. At the moment, we're just using triggers to put an mtime field onto all of our rows, and this works well. But I'd want to think carefully about more advanced uses. And in MySQL that's just adding an extra TIMESTAMP field and just don't update that field in an update: it will automatically get the mtime... ;-) *ducks* Liz
RE: [OT] SQL woes
You don't even need to install a separate SQLite; the CPAN module includes everything you need. Thanks - I did see the module on CPAN but wan't sure if I only needed the one download. What I can't see there (or at the SQLite site) is a nice example which shows creates, updates and queries a dbase. If I look at the books I've got which show the DBI working with MySQL, can I simply transpose these? What differences with SQLite would you advise I need to look out for? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Roger Burton West Sent: 22 August 2003 22:58 To: [EMAIL PROTECTED] Subject: Re: [OT] SQL woes On Thu, Aug 21, 2003 at 08:51:49PM +0100, Colin Magee wrote: So this SQLite seems like a great idea IF it makes setting up and administering a dbase simple. Unfortunately I just looked up where I can download it, but didn't see any example code showing it working with Perl, or any documentation about how to use it with Perl. Please can you inform what do I have to do/install in order to start creating, updating and querying the database from Perl? DBD::SQLite. You don't even need to install a separate SQLite; the CPAN module includes everything you need. Roger
Re: [OT] SQL woes
Thanks - I did see the module on CPAN but wan't sure if I only needed the one download. What I can't see there (or at the SQLite site) is a nice example which shows creates, updates and queries a dbase. If I look at the books I've got which show the DBI working with MySQL, can I simply transpose these? What differences with SQLite would you advise I need to look out for? Hi Colin, To interact with databases in Perl, you use the DBI (database interface) module. This talks to a DBD (database driver) module, such as DBD::SQLite. If you installed DBD::SQLite through one of the CPAN shells, it'll probably make sure you install DBI too. http://www.perladvent.org/2002/3rd/ Is an excellent introduction. +Pete
RE: [OT] SQL woes
On Thu, 21 Aug 2003, Colin Magee wrote: As a Perl beginner who has realised I need to set up a database, I have to add that I immediately went for MySQL due to all the attention it seems to have, and bought a few books to get me started. The whole thing - using installing etc etc looks hellish complicated AND I haven't even been able to install it. What Linux distro are you running? Pretty much all of them offer binary packages of MySQL so you should be able to use rpm or apt-get to install it. All I can see is where the files are installed in my copy of Linux. So it looks as if you already have to know what you're doing in order to use it which is useless for a beginner and teh antithesis it seems to me of what Perl is all about - because you don't already have to know what you're doing to get started/ use it. I disagree. Installing MySQL is very simple. Using it with perl is as simple as: perldoc DBI read and learn. The same is true for PostgreSQL. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ ADSL Broadband available now
RE: [OT] SQL woes
What Linux distro are you running? SuSE 8.1. As I say, I can see the files installed in usr/bin, as root, but when I try running it I get all sorts of error messages: 1. Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock. Try checking mysqld is running and that the socket exists 2. When I try to run mysqld independently, or add it to my .bashrc it doesn't work either. 3. Even if the above did work, I understand there are a number of other setup and admin tasks with MySQL. ie. you need to run mysql_install_db, set permissions, passwords and so forth. The database is only for my use, so (1 + 2 + 3) = not straightforward in my book. read and learn. As I say, I can see how I would start to use DBI from perl - that's not really the problem. The problem is setting up the database itself, and having a dbase to use and maintain that is as simple as possible. So that's the reason I'm giving up on MySQL and am looking at SQLite. Also, since all dbases vary, that's why I'd like ot understand with SQLite what I need to know that is different, since I have documentation about using perl with Mysql. Colin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jason Clifford Sent: 23 August 2003 12:02 To: [EMAIL PROTECTED] Subject: RE: [OT] SQL woes On Thu, 21 Aug 2003, Colin Magee wrote: As a Perl beginner who has realised I need to set up a database, I have to add that I immediately went for MySQL due to all the attention it seems to have, and bought a few books to get me started. The whole thing - using installing etc etc looks hellish complicated AND I haven't even been able to install it. What Linux distro are you running? Pretty much all of them offer binary packages of MySQL so you should be able to use rpm or apt-get to install it. All I can see is where the files are installed in my copy of Linux. So it looks as if you already have to know what you're doing in order to use it which is useless for a beginner and teh antithesis it seems to me of what Perl is all about - because you don't already have to know what you're doing to get started/ use it. I disagree. Installing MySQL is very simple. Using it with perl is as simple as: perldoc DBI read and learn. The same is true for PostgreSQL. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ ADSL Broadband available now
RE: [OT] SQL woes
On Fri, 22 Aug 2003, Colin Magee wrote: What Linux distro are you running? SuSE 8.1. As I say, I can see the files installed in usr/bin, as root, but when I try running it I get all sorts of error messages: 1.Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock. Try checking mysqld is running and that the socket exists 2.When I try to run mysqld independently, or add it to my .bashrc it doesn't work either. That's not what you need to do. First check that you have the mysql server package installed: rpm -qa | grep mysql will give you a list of all of the mysql packages installed. Like many others SuSE provide several packages and the server may not have been installed by default. Once you have the server package installed make sure it is started at boot: chkconfig --add [package] chkconfig --level [level] [package] on Where [package] is the name of the server package and level is the runlevel you start your system at. 3.Even if the above did work, I understand there are a number of other setup and admin tasks with MySQL. ie. you need to run mysql_install_db, set permissions, passwords and so forth. The rpm will initialist MySQL for you. The frist task will to set the root password as by default there isn't one. The database is only for my use, so (1 + 2 + 3) = not straightforward in my book. That's why the rpm does it for you. As I said previously PostgreSQL is pretty much the same. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ ADSL Broadband available now
Re: [OT] SQL woes
What Linux distro are you running? SuSE 8.1. As I say, I can see the files installed in usr/bin, as root, but when I try running it I get all sorts of error messages: 'it' being the server or the client? 1. Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock. Try checking mysqld is running and that the socket exists ps awx | grep mysqld is a good test for what it suggests 2. When I try to run mysqld independently, or add it to my .bashrc it doesn't work either. safe_mysqld / mysqld_safe is the usual way to start mysqld. running it from .bashrc is a little unusual. 3. Even if the above did work, I understand there are a number of other setup and admin tasks with MySQL. ie. you need to run mysql_install_db, set permissions, passwords and so forth. The database is only for my use, so (1 + 2 + 3) = not straightforward in my book. Have you read http://www.mysql.com/doc/en/Post-installation.html ? 1 and 2 won't work without 3. hth a bit, dave
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 05:59:01PM +0100, Colin Magee wrote: SuSE 8.1. As I say, I can see the files installed in usr/bin, as root, but when I try running it I get all sorts of error messages: 1.Can't connect to local MySQL server through socket /var/lib/mysql/mysql.sock. Try checking mysqld is running and that the socket exists 2.When I try to run mysqld independently, or add it to my .bashrc it doesn't work either. 3.Even if the above did work, I understand there are a number of other setup and admin tasks with MySQL. ie. you need to run mysql_install_db, set permissions, passwords and so forth. It runs as a service, so if installed, root can start it with /etc/init.d/mysqld start if it's not autmatically started. The init.d script does the first-time-startup actions (except setting a root password). HTH -- Chris Benson
Re: [OT] SQL woes
Dominic Mitchell wrote: Many people say the same thing about perl code in general. Like all tools you need to know when to say no. At the moment, we're just using triggers to put an mtime field onto all of our rows, and this works well. But I'd want to think carefully about more advanced uses. I've used a trigger in the past to enforce a constraint - I couldn't think of any other way to enforce must be unique or one of [a list of values]. Of course, having to use such a constraint probably means that my database design was wrong. -- David Cantrell | http://www.cantrell.org.uk/david We must get users past their misunderstandings of uptime. A reboot doesn't mean that anything broke, there is no hardware or software corrective action taken, so there wasn't any real downtime. -- overheard in an MS strategy meeting
Re: [OT] SQL woes
On Sat, 23 Aug 2003, David Cantrell wrote: Dominic Mitchell wrote: Many people say the same thing about perl code in general. Like all tools you need to know when to say no. At the moment, we're just using triggers to put an mtime field onto all of our rows, and this works well. But I'd want to think carefully about more advanced uses. I've used a trigger in the past to enforce a constraint - I couldn't think of any other way to enforce must be unique or one of [a list of Create a unique index? values]. Of course, having to use such a constraint probably means Create a not-null join to a table containing only those values? that my database design was wrong. S. -- Shevekhttp://www.anarres.org/ I am the Borg. http://www.gothnicity.org/
Re: [OT] SQL woes
What are the advantages of PostgreSQl over mySQL I currently use mySQL everywhere and it works well - at least for what I need it for!! Andy On Thu, 2003-08-21 at 16:12, Toby Corkindale wrote: On Sun, Aug 17, 2003 at 06:03:13PM +0100, Paul Makepeace wrote: On Sun, Aug 17, 2003 at 09:51:13AM -0700, Randal L. Schwartz wrote: (And people who use MySQL wonder what the value of subselects are! :) Subselects are in MySQL 4.1 (currently alpha). http://www.mysql.com/press/release_2003_05.html Ick. Have they got around to supporting transactions yet? :P I'd still say toss it and use PostgreSQL. Ditto. Don't understand the hype around MySQL, personally. tjc
Re: [OT] SQL woes
Toby Corkindale [EMAIL PROTECTED] wrote: On Sun, Aug 17, 2003 at 06:03:13PM +0100, Paul Makepeace wrote: On Sun, Aug 17, 2003 at 09:51:13AM -0700, Randal L. Schwartz wrote: (And people who use MySQL wonder what the value of subselects are! :) Subselects are in MySQL 4.1 (currently alpha). http://www.mysql.com/press/release_2003_05.html Ick. Have they got around to supporting transactions yet? :P Transactions have been supported for a while, apparently. Dunno as I don't use MySQL myself, but it's a frequent rebuff. I think you have to select the berkelydb backend table storage module. Anyway, If you want a high end database with the MySQL label, it looks like they've just gone a rebranding rampage with SAPDB and it's emerged as MySQL Max... http://www.mysql.com/press/release_2003_24.html http://www.mysql.com/maxdb/ That probably has more features that one would expect. -Dom -- | Semantico: creators of major online resources | | URL: http://www.semantico.com/ | | Tel: +44 (1273) 72 | | Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 09:54:22AM +0100, Andy Ford wrote: What are the advantages of PostgreSQl over mySQL The first few advantages that come to mind: It scales. :D It supports (several varieties of) transactions. It can write internally consistent backup dumps. It supports write-ahead-logging. (Basically the same deal as journalled filesystems, only in-database; and no, just because you're using ext3fs or reiserfs, doesn't mean you achieve the same thing) It supports stored procedures (in tcl, perl, and plpgsql). It has also supported all the above for quite a while now and has a good track record. MySQL supporters tend to point out things like oh, but mysql supports that now - in the latest alpha CVS snapshot. I currently use mySQL everywhere and it works well - at least for what I need it for!! MySQL is good for some stuff - it used to be a fairly lightweight SQL interface to a some basic backend storage. If you just want to go around inserting and selecting data for your home accounting or your webpage, it's fine. Doesn't use too much memory or CPU, works fine. It's just when you want to deal with large/commercial projects that it tends to fall down. But I tend to use it for everything, as well; on modern hardware the slightly larger footprint of Pg isn't a problem. tjc.
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 09:54:22AM +0100, Andy Ford wrote: What are the advantages of PostgreSQl over mySQL The first few advantages that come to mind: It scales. :D It supports (several varieties of) transactions. It can write internally consistent backup dumps. It supports write-ahead-logging. (Basically the same deal as journalled filesystems, only in-database; and no, just because you're using ext3fs or reiserfs, doesn't mean you achieve the same thing) It supports stored procedures (in tcl, perl, and plpgsql). On the MySQL plus side I've found HASH Tables to be invaluable for recording lots of transactions without the disk (i.e., in RAM buckets). And one day when I can afford a cluster I'm hoping to implement the MySQL DB replication Hack (outlined in Linux Server Hacks) to distribute parts of the database onto the nodes. On the negative side, MySQL's lack of subqueries has been a real bummer. NIge -- Nigel Hamilton Turbo10 Metasearch Engine email: [EMAIL PROTECTED] tel:+44 (0) 207 987 5460 fax:+44 (0) 207 987 5468 http://turbo10.com Search Deeper. Browse Faster.
Re: [OT] SQL woes
On Fri, 22 Aug 2003, Nigel Hamilton wrote: And one day when I can afford a cluster I'm hoping to implement the MySQL DB replication Hack (outlined in Linux Server Hacks) to distribute parts of the database onto the nodes. I'm using that and it works beautifully. It'd also very easy to recover when you do something stupid resulting in the database getting out of sync (on a test box - I try to keep away from production systems when I am on the stupid). Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ ADSL Broadband available now
Re: [OT] SQL woes
On Thu, 21 Aug 2003 16:12, Toby Corkindale wrote; TC Ick. Have they got around to supporting transactions yet? :P No, they're too dumb for that - but a company called InnoDB basically re-wrote the core parts of MySQL and called it a storage back-end. It's free and included in the MySQL distribution, except the hot backup license (very smart move). In fact, it's even compiled in by default with Debian stable, all you need to do is change some config options, bounce MySQL, re-create all your tables reload your data :-) and you're away laughing ... works very well using raw LVM partitions as tablespaces (I haven't yet found a way to add more table space online, but I may just be overlooking the obvious). http://www.innodb.com/features.html -- Sam Vilain, [EMAIL PROTECTED] UNIX, n: Anything which is subtly incompatible with everything else claming to be a UNIX.
Re: [OT] SQL woes
Andy Ford wrote: What are the advantages of PostgreSQl over mySQL I currently use mySQL everywhere and it works well - at least for what I need it for!! Hello Andy, and Perl M[ou]ngers. IMHO, MySQL is the fastest database engine on the Open-Source and Commercial markets. On the other hand, PostgreSQL have some interesting features, like support for a built-in scripting language and transactions. This makes PostgreSQL a quite interesting tool for Open-Source solutions development. AFAIK, PostgreSQL uses a PL/SQL-like scripting language, and this is quite interesting for portability questions: somebody could easily migrate his/her database from/to oracle faster. I use to see this as a good thing. The main advantage of using MySQL is speed -- web based applications suffers a lot from badly optimized database access. The bad thing about this is that speed comes from dropping important features that could be useful for other uses, like billing: nobody sane will use a MySQL to control billing transactions: it's too complex to protect data from failures and handle errors in a comprehensible way. That's it. Just my two pence. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Luis Campos de Carvalho is Computer Scientist, PerlMonk [SiteDocClan], Cascavel-pm Moderator, Unix Sys Admin Certified Oracle DBA http://br.geocities.com/monsieur_champs/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Re: [OT] SQL woes
From: Andy Ford [EMAIL PROTECTED] Date: 8/22/03 8:54:22 AM What are the advantages of PostgreSQl over mySQL I currently use mySQL everywhere and it works well - at least for what I need it for!! MySQL is just a file-based storage system with a pseudo-SQL interface. It doesn't support many of the things that you'd expect in a real SQL implementation, for example: * Transactions * Subselects * Referential integrity PostgreSQL has (I believe[1]) all of these things. hth, Dave... [1] I don't use it. I still use MySQL in most places :( -- http://www.dave.org.uk Let me see you make decisions, without your television - Depeche Mode (Stripped)
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 06:20:39AM -0700, Dave Cross wrote: MySQL is just a file-based storage system with a pseudo-SQL interface. It doesn't support many of the things that you'd expect in a real SQL implementation, for example: * Transactions * Referential integrity Innodb tables give you these in modern mysqls. * Subselects Not yet, scheduled for 4.1 though. Besides, people really don't expect those things. It's just the people who've used other RDBMSs before and enjoyed the bondage and discipline that expect them. Not that I'm against hating software, but I do have this preference for informed hate. -- Richard Clamp [EMAIL PROTECTED]
Re: [OT] SQL woes
Luis == Luis Campos de Carvalho [EMAIL PROTECTED] writes: LuisIMHO, MySQL is the fastest database engine on the Open-Source and Luis Commercial markets. Only for a loose definition of database. Certainly not one with transactions. When you move all your tables to InnoDB, the results actually favor PostgreSQL for what I've seen so far. LuisOn the other hand, PostgreSQL have some interesting features, like Luis support for a built-in scripting language and transactions. This makes Luis PostgreSQL a quite interesting tool for Open-Source solutions Luis development. And don't forget subselects, views, triggers, foreign keys, and all the other goodies from proper SQL 92 compliance. MySQL is a fine junior SQL engine. But it really doesn't provide a way to enforce business rules in the engine - that was not its initial goal. And when data integrity is important, you want to ensure good foreign keys and triggers that will keep your data from getting out of whack because of application programmer error, and MySQL says no, that stuff all belongs in the application, not the database. Fine decision to keep it simple, but in the real world, that's a dangerous decision. LuisAFAIK, PostgreSQL uses a PL/SQL-like scripting language, and this Luis is quite interesting for portability questions: somebody could easily Luis migrate his/her database from/to oracle faster. I use to see this as a Luis good thing. LuisThe main advantage of using MySQL is speed I think that's actually a myth now. Maybe in the past it was a clear winner, but more often than not, a well-tuned PostgreSQL install will beat a well-tuned MySQL install on the same hardware under some serious stress. Luis-- web based Luis applications suffers a lot from badly optimized database access. The Luis bad thing about this is that speed comes from dropping important Luis features that could be useful for other uses, like billing: nobody Luis sane will use a MySQL to control billing transactions: it's too Luis complex to protect data from failures and handle errors in a Luis comprehensible way. MySQL is *just* now getting transactions. PostgreSQL has had some very good experts working on transactions for years now, and they're much further along on the trial-and-error curve that MySQL is just now starting. In conclusion, if you want a speedy ACID-compliant enforced-business-rules database with full SQL support (not a subset), PostgreSQL wins, and MySQL is still two or three years away. On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
RE: [OT] SQL woes
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Cross Sent: 22 August 2003 14:21 To: [EMAIL PROTECTED] Subject: Re: [OT] SQL woes MySQL is just a file-based storage system with a pseudo-SQL interface. It doesn't support many of the things that you'd expect in a real SQL implementation, for example: * Transactions * Subselects * Referential integrity Not sure about the referential integrity but it does have transactions and subselects are coming in 4.1. (Currently 4.0.14) MySQL Server (version 3.23-max and all versions 4.0 and above) supports transactions with the InnoDB and BDB Transactional storage engines. InnoDB provides full ACID compliance [0] Andy [0] http://www.mysql.com/doc/en/ANSI_diff_Transactions.html
Re: [OT] SQL woes
Randal == Randal L Schwartz [EMAIL PROTECTED] writes: Randal MySQL is a fine junior SQL engine. But it really doesn't provide a Randal way to enforce business rules in the engine - that was not its initial Randal goal. And when data integrity is important, you want to ensure good Randal foreign keys and triggers that will keep your data from getting out of Randal whack because of application programmer error, and MySQL says no, Randal that stuff all belongs in the application, not the database. Fine Randal decision to keep it simple, but in the real world, that's a dangerous Randal decision. Let me explain this a bit better. Suppose you have a deployed app with many components. And now some accounting audit dude comes along and says OK, we need a trail of every time a billing record is added or changed. With MySQL, you have to go into every app in whatever language it has been written, and add the extra audit code. In PostgreSQL, it's trivial to add an after commit trigger to flash the old and new records into a seperate record for auditing, and that works with whatever way you are connecting to the database, because it's *in* the database. Heck, with PL/Perl, you can even page someone when a certain record is set. :) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 06:45:21AM -0700, Randal L. Schwartz wrote: In conclusion, if you want a speedy ACID-compliant enforced-business-rules database with full SQL support (not a subset), PostgreSQL wins, and MySQL is still two or three years away. On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. On the third hand, if you want _real_ speed, _and_ transactions, but none of the other neat stuff; and if your system doesn't have much in the way of concurrent writes; SQLite is a whole lot easier to set up and admin than MySQL. (I'm using it for most of my light-weight web apps these days.) Roger
Re: [OT] SQL woes
Richard == Richard Clamp [EMAIL PROTECTED] writes: Richard On Fri, Aug 22, 2003 at 06:20:39AM -0700, Dave Cross wrote: MySQL is just a file-based storage system with a pseudo-SQL interface. It doesn't support many of the things that you'd expect in a real SQL implementation, for example: * Transactions * Referential integrity Richard Innodb tables give you these in modern mysqls. At a speed cost that makes it slower than Pg, and an earlier spot on the design curve which Pg has already matured on. * Subselects Richard Not yet, scheduled for 4.1 though. Richard Besides, people really don't expect those things. It's just the Richard people who've used other RDBMSs before and enjoyed the bondage and Richard discipline that expect them. People coming from flat files and DBM don't expect them. Now that I've seen subselects and triggers and views and stored procedures in action, I don't think I can deploy anything with a dozen tables or more without them. MySQL - fine for people who want a light subset-SQL interface to structured data PostgreSQL - Oracle without the price -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 02:59:14PM +0100, Roger Burton West said: On the third hand, if you want _real_ speed, _and_ transactions, but none of the other neat stuff; and if your system doesn't have much in the way of concurrent writes; SQLite is a whole lot easier to set up and admin than MySQL. (I'm using it for most of my light-weight web apps these days.) But suffers, IIRC, from concurrency problems. So, to sum up this thread : 1. PostGres has some advantages 2. MySql has some advantages 3. Oracle has some advantages 4. SQLite has some advantages 5. All of the above have disadvantages. 6. There will be a film. At 11.
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 03:07:19PM +0100, Simon Wistow wrote: [SQLite] But suffers, IIRC, from concurrency problems. On writing, yes. Hence doesn't have much in the way of concurrent writes. R
Re: [OT] SQL woes
Randal L. Schwartz [EMAIL PROTECTED] wrote: MySQL is *just* now getting transactions. PostgreSQL has had some very good experts working on transactions for years now, and they're much further along on the trial-and-error curve that MySQL is just now starting. I'd just like to point out that postgresql doesn't have nested transactions. This annoyed me because I had to think about my design harder. :-) -Dom -- | Semantico: creators of major online resources | | URL: http://www.semantico.com/ | | Tel: +44 (1273) 72 | | Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |
Re: [OT] SQL woes
On Friday, August 22, 2003, at 11:28 am, Dominic Mitchell wrote: Toby Corkindale [EMAIL PROTECTED] wrote: On Sun, Aug 17, 2003 at 06:03:13PM +0100, Paul Makepeace wrote: On Sun, Aug 17, 2003 at 09:51:13AM -0700, Randal L. Schwartz wrote: (And people who use MySQL wonder what the value of subselects are! :) Subselects are in MySQL 4.1 (currently alpha). http://www.mysql.com/press/release_2003_05.html Ick. Have they got around to supporting transactions yet? :P Transactions have been supported for a while, apparently. Dunno as I don't use MySQL myself, but it's a frequent rebuff. I think you have to select the berkelydb backend table storage module. Yeah. You do get transactions if you use innodb tables, and have had for several years. The sub-select support is very beta at the moment and, annoyingly, doesn't work with innodb tables so you can't have transactions and sub-selects at the same time! MySQL can be a lot better than the worse-case description given by many but, given the choice, PostgreSQL would be my pick every time. A much more mature product. Adrian
Re: [OT] SQL woes
Well thanks for summing that up - it was an interesting read!! Currently I use mySQL only and don't really need transactional stuff. Now triggers I can see a need for!! I may take a look at Pg Thanks Andy On Fri, 2003-08-22 at 15:08, Randal L. Schwartz wrote: Richard == Richard Clamp [EMAIL PROTECTED] writes: Richard On Fri, Aug 22, 2003 at 06:20:39AM -0700, Dave Cross wrote: MySQL is just a file-based storage system with a pseudo-SQL interface. It doesn't support many of the things that you'd expect in a real SQL implementation, for example: * Transactions * Referential integrity Richard Innodb tables give you these in modern mysqls. At a speed cost that makes it slower than Pg, and an earlier spot on the design curve which Pg has already matured on. * Subselects Richard Not yet, scheduled for 4.1 though. Richard Besides, people really don't expect those things. It's just the Richard people who've used other RDBMSs before and enjoyed the bondage and Richard discipline that expect them. People coming from flat files and DBM don't expect them. Now that I've seen subselects and triggers and views and stored procedures in action, I don't think I can deploy anything with a dozen tables or more without them. MySQL - fine for people who want a light subset-SQL interface to structured data PostgreSQL - Oracle without the price
Re: [OT] SQL woes
On Fri, 22 Aug 2003, Simon Wistow wrote: So, to sum up this thread : 1. PostGres has some advantages 2. MySql has some advantages 3. Oracle has some advantages 4. SQLite has some advantages 5. All of the above have disadvantages. 6. There will be a film. At 11. Which will be delayed for a new advertising campaign promoting MS-SQL. Jason Clifford -- UKFSN.ORG Finance Free Software while you surf the 'net http://www.ukfsn.org/ ADSL Broadband available now
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 05:13:31PM +0100, Jason Clifford wrote: On Fri, 22 Aug 2003, Simon Wistow wrote: So, to sum up this thread : 1. PostGres has some advantages 2. MySql has some advantages 3. Oracle has some advantages 4. SQLite has some advantages 5. All of the above have disadvantages. 6. There will be a film. At 11. Which will be delayed for a new advertising campaign promoting MS-SQL. The only database written exclusively for MS Windows - so you know it must be good! (Although Sybase wasn't, was it?) tjc.
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 02:59:14PM +0100, Roger Burton West wrote: On Fri, Aug 22, 2003 at 06:45:21AM -0700, Randal L. Schwartz wrote: In conclusion, if you want a speedy ACID-compliant enforced-business-rules database with full SQL support (not a subset), PostgreSQL wins, and MySQL is still two or three years away. On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. On the third hand, if you want _real_ speed, _and_ transactions, but none of the other neat stuff; and if your system doesn't have much in the way of concurrent writes; SQLite is a whole lot easier to set up and admin than MySQL. (I'm using it for most of my light-weight web apps these days.) Transactions, sort-of, in that you get them within a single query; but there's poor support for simultaneous-request stuff. I looked at SQLLIte a while back, and the thing that scared me off was the complete lack of locking; it required you to do that at application level. OK, you say, so it's not too hard to implement one giant all-encompassing lock at application level with a CPAN module; but then you get right back the problems that were plagueing MySQL earlier on! (coarse grained locking). history repeats itself.. tjc. -- Turning and turning in the widening gyre The falcon cannot hear the falconer; Things fall apart, the centre cannot hold; Mere anarchy is loosed upon the world.
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 06:45:21AM -0700, Randal L. Schwartz wrote: MySQL is *just* now getting transactions. PostgreSQL has had some very good experts working on transactions for years now, and they're much further along on the trial-and-error curve that MySQL is just now starting. For values of *just* approaching 3 years... If you count the MySQL+SAP combo (not forgetting that SAP had an Oracle-compatability mode...) then that number gets a whole lot bigger. Tony
Re: [OT] SQL woes
On Fri, Aug 22, 2003 at 03:30:48PM +0100, Andy Ford wrote: Well thanks for summing that up - it was an interesting read!! Currently I use mySQL only and don't really need transactional stuff. Now triggers I can see a need for!! Triggers at the db level will bite you hard at some point. If you're going to have multiple people building your application make sure you have VERY strict rules on adding triggers. Unchecked they lead to an unmaintainable mess quicker than most other things can ... Tony
RE: [OT] SQL woes
Hi, As a Perl beginner who has realised I need to set up a database, I have to add that I immediately went for MySQL due to all the attention it seems to have, and bought a few books to get me started. The whole thing - using installing etc etc looks hellish complicated AND I haven't even been able to install it. All I can see is where the files are installed in my copy of Linux. So it looks as if you already have to know what you're doing in order to use it which is useless for a beginner and teh antithesis it seems to me of what Perl is all about - because you don't already have to know what you're doing to get started/ use it. So this SQLite seems like a great idea IF it makes setting up and administering a dbase simple. Unfortunately I just looked up where I can download it, but didn't see any example code showing it working with Perl, or any documentation about how to use it with Perl. Please can you inform what do I have to do/install in order to start creating, updating and querying the database from Perl? Thanks Colin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Roger Burton West Sent: 22 August 2003 14:59 To: [EMAIL PROTECTED] Subject: Re: [OT] SQL woes On Fri, Aug 22, 2003 at 06:45:21AM -0700, Randal L. Schwartz wrote: In conclusion, if you want a speedy ACID-compliant enforced-business-rules database with full SQL support (not a subset), PostgreSQL wins, and MySQL is still two or three years away. On the other hand, if you wanna be able to ask a question on a general mailing list, the MySQL community is probably ten times the size of the PostgreSQL community, and more applications have been written that presume MySQL's quirky SQL subset. That'd be the *only* reason in my mind to go with MySQL over Pg at this point. On the third hand, if you want _real_ speed, _and_ transactions, but none of the other neat stuff; and if your system doesn't have much in the way of concurrent writes; SQLite is a whole lot easier to set up and admin than MySQL. (I'm using it for most of my light-weight web apps these days.) Roger
Re: [OT] SQL woes
Dominic Mitchell [EMAIL PROTECTED] writes: Randal L. Schwartz [EMAIL PROTECTED] wrote: MySQL is *just* now getting transactions. PostgreSQL has had some very good experts working on transactions for years now, and they're much further along on the trial-and-error curve that MySQL is just now starting. I'd just like to point out that postgresql doesn't have nested transactions. This annoyed me because I had to think about my design harder. :-) It is rather annoying isn't it?
Re: [OT] SQL woes
What about Firebird? http://firebird.sourceforge.net/ does anyone here have personal experience? On paper (well, online) it compares favorably to PostgreSQL, and it can be easily embedded. richard __ Bibliocraft Ltd / www.bibliocraft.com
Re: [OT] SQL woes
On Sun, Aug 17, 2003 at 06:03:13PM +0100, Paul Makepeace wrote: On Sun, Aug 17, 2003 at 09:51:13AM -0700, Randal L. Schwartz wrote: (And people who use MySQL wonder what the value of subselects are! :) Subselects are in MySQL 4.1 (currently alpha). http://www.mysql.com/press/release_2003_05.html Ick. Have they got around to supporting transactions yet? :P I'd still say toss it and use PostgreSQL. Ditto. Don't understand the hype around MySQL, personally. tjc
Re: [OT] SQL woes
Toby Corkindale sent the following bits through the ether: Ditto. Don't understand the hype around MySQL, personally. People still use relational databases? Leon -- Leon Brocard.http://www.astray.com/ scribot.http://www.scribot.com/ ... We're sorry, but reality is not in service at this time
Re: [OT] SQL woes
On Sunday, August 17, 2003, 10:01:51 AM, Peter Sergeant wrote: PS It's at times like this I realise my SQL skills only cover the basics... PS I have two tables, 'user' and 'users_names'. I'm looking to deprecate PS 'users_names', so I've altered 'user' to now contain a 'user_realname' PS column. Both tables have a column 'user_id', which correspond to each PS other. I'd like to take the data from 'users_names.name' and put it into PS 'user.user_realname' where the 'user_id' column match. I just can't seem PS to find the SQL to make MySQL do this. is it not simply: UPDATE user SET user.user_realname = user_names.name WHERE user.user_id = user_names.user_id ? -- Iain | PGP mail preferred: pubkey @ www.deepsea.f9.co.uk/misc/iain.asc ($=,$,)=split m$13/$,qq;1313/tl\.rnh r HITtahkPctacriAneeeusaoJ;; for(@[EMAIL PROTECTED] m,,,$,){$..=$$[$=];$$=$=[$=];[EMAIL PROTECTED];[EMAIL PROTECTED] ]eq$$$==$?;$==$?;for(@$)[EMAIL PROTECTED] eq$_;;last if!$@;$=++}}print$..$/
Re: [OT] SQL woes
is it not simply: ? Apparently not. mysql UPDATE user SET user.user_realname = users_names.name WHERE user.user_id = users_names.user_id; ERROR 1109: Unknown table 'users_names' in where clause However, users_names definitely does exist. I'd speculate here, and, if I'm wrong I'd appreciate being corrected, that it assumes 'users_names' is an alias to a table, but that what it's an alias to hasn't been defined in the query... +Pete -- Do not be too moral. You may cheat yourself out of much life. Aim above morality. Be not simply good; be good for something. -- Henry David Thoreau
Re: [OT] SQL woes
On Sun, 17 Aug 2003, Iain Tatch wrote: On Sunday, August 17, 2003, 10:01:51 AM, Peter Sergeant wrote: PS It's at times like this I realise my SQL skills only cover the basics... PS I have two tables, 'user' and 'users_names'. I'm looking to deprecate PS 'users_names', so I've altered 'user' to now contain a 'user_realname' PS column. Both tables have a column 'user_id', which correspond to each PS other. I'd like to take the data from 'users_names.name' and put it into PS 'user.user_realname' where the 'user_id' column match. I just can't seem PS to find the SQL to make MySQL do this. is it not simply: UPDATE user UPDATE user, user_names SET user.user_realname = user_names.name WHERE user.user_id = user_names.user_id ? -- Shevekhttp://www.anarres.org/ I am the Borg. http://www.gothnicity.org/
Re: [OT] SQL woes
UPDATE user, user_names Leading, it would seem to: mysql UPDATE user, users_names SET user.user_realname = users_names.name WHERE user.user_id = users_names.user_id; ERROR 1064: You have an error in your SQL syntax near ' users_names SET user.user_realname = users_names.name WHERE user.user_id = user' at line 1 +Pete -- Lying is forbidden in Iraq. President Saddam Hussein will tolerate nothing but truthfulness as he is a man of great honor and integrity. Everyone is encouraged to speak freely of the truths evidenced in their eyes and hearts. -- Muhammed Saeed al-Sahaf (Iraqi Information Minister)
Re: [OT] SQL woes
Peter Sergeant wrote: is it not simply: ? Apparently not. mysql UPDATE user SET user.user_realname = users_names.name WHERE user.user_id = users_names.user_id; ERROR 1109: Unknown table 'users_names' in where clause However, users_names definitely does exist. I'd speculate here, and, if I'm wrong I'd appreciate being corrected, that it assumes 'users_names' is an alias to a table, but that what it's an alias to hasn't been defined in the query... +Pete You need to include user_names in the FROM clause, so something like; UPDATE user FROM user, user_names SET user.user_realname = user_names.name WHERE user.user_id = user_names.user_id NB - I haven't checked this but I have done this on other schema before N
Re: [OT] SQL woes
Iain == Iain Tatch [EMAIL PROTECTED] writes: Iain On Sunday, August 17, 2003, 10:01:51 AM, Peter Sergeant wrote: PS It's at times like this I realise my SQL skills only cover the basics... PS I have two tables, 'user' and 'users_names'. I'm looking to deprecate PS 'users_names', so I've altered 'user' to now contain a 'user_realname' PS column. Both tables have a column 'user_id', which correspond to each PS other. I'd like to take the data from 'users_names.name' and put it into PS 'user.user_realname' where the 'user_id' column match. I just can't seem PS to find the SQL to make MySQL do this. Iain is it not simply: Iain UPDATE user IainSET user.user_realname = user_names.name Iain WHERE user.user_id = user_names.user_id To do it in standard SQL, you need a subselect. UPDATE user SET user_realname = (SELECT name FROM user_names WHERE user.user_id = user_names.user_id); If your db doesn't offer subselects, you've got to create a temp table. (And people who use MySQL wonder what the value of subselects are! :) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 [EMAIL PROTECTED] URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!
Re: [OT] SQL woes
On Sun, Aug 17, 2003 at 09:51:13AM -0700, Randal L. Schwartz wrote: (And people who use MySQL wonder what the value of subselects are! :) Subselects are in MySQL 4.1 (currently alpha). http://www.mysql.com/press/release_2003_05.html I'd still say toss it and use PostgreSQL. P -- Paul Makepeace ... http://paulm.com/ If I went to bed right now, then you will be able to sleep. -- http://paulm.com/toys/surrealism/
Re: [OT] SQL woes
On Sun, 17 Aug 2003, Peter Sergeant wrote: is it not simply: ? Apparently not. mysql UPDATE user SET user.user_realname = users_names.name WHERE user.user_id = users_names.user_id; ERROR 1109: Unknown table 'users_names' in where clause However, users_names definitely does exist. I'd speculate here, and, if I'm wrong I'd appreciate being corrected, that it assumes 'users_names' is an alias to a table, but that what it's an alias to hasn't been defined in the query... At a guess, maybe it would be illuminating to send the output from: mysql describe user; mysql describe users_names; That might clarify what's what. -- Chris Devers [EMAIL PROTECTED] http://devers.homeip.net:8080/ network, n. v. trans. [from net reduced slightly from gross+work] 1 n. The antisynergetic interconnection of noncompatible nodal systems divided by a common protocol. 2 v. trans. To reduce [net] the work rate (of a computing resource) by adding it to a network. -- from _The Computer Contradictionary_, Stan Kelly-Bootle, 1995
Re: [OT] SQL woes
On Sun, Aug 17, 2003 at 11:23:32AM +0100, Peter Sergeant wrote: mysql UPDATE user, users_names SET user.user_realname = users_names.name WHERE user.user_id = users_names.user_id; ERROR 1064: You have an error in your SQL syntax near ' users_names SET user.user_realname = users_names.name WHERE user.user_id = user' at line 1 http://www.mysql.com/doc/en/UPDATE.html that syntax is only supposed to work in MySQL = 4.0.4 ... you're using something earlier? -- Phil Lanch0xD78D598DA6635CF32AB24593C98994B7D95B33E3 (though i can't remember the passphrase right now) Daddy, why are we hiding? We use vi, son. They use emacs.