> From: Michael Peligro <[EMAIL PROTECTED]> > > I'm also looking for an open-source database replacement for Progress > database. > > I, too, have been looking at MySQL and PostgreSQL and I'd appreciate more > inputs on what differentiates the two, and how each can address my database > requirements and deployments.
Don't forget to check out Firebird / Interbase. I'm currently using it together with the Python binding kinterbasdb. Not as friendly as MySQL (initially) but feature-wise it Rocks! I wouldn't even consider building an enterprise-wide system with MySQL, InnoDB notwithstanding. > 1. Does MySQL and PostgreSQL support table-locking and record-locking? This > feature is important in multi-user record resolution. I know MySQL supports table locking and I believe so should PostgreSQL and Firebird. MySQL can support row-level locking using InnoDB tables. With BDB tables, you get page-level locking. MySQL MyISAM tables, the simplest of the MySQL table handlers, only support table locking. PostgreSQL and Firebird both should have at least page-level locking, and I'm almost 99.99% sure they should have record locking since they support multiversioning concurrency. > With Progress database, I code these statements if I simply want to search and > read the record, and display the result onscreen: > > FIND record WHERE criteria = "" NO-LOCK, NO-ERROR. > IF NOT AVAILABLE record THEN > DO: > MESSAGE "Record not found" VIEW-AS ALERT-BOX INFO. > RETURN NO-APPLY. > END. > ELSE > DISPLAY record WITH FRAME frame-name. > > If I want to lock the record and update the desired fields. It's as simple as > changing the NO-LOCK to EXCLUSIVE-LOCK: > > DO: > FIND record WHERE criteria = "" EXCLUSIVE-LOCK, NO-ERROR. > UPDATE record/fields. > DISPLAY record WITH FRAME frame-name. > END. > > Are there similar, simple, equivalent SQL queries like this in MySQL or > PostgreSQL? How is table-locking and record-locking implemented? What does > MySQL mean by atomic transactions? Does MySQL and PostgreSQL support > transactions (in database parlance)? Yes, I believe there is similar functionality in the three. PostgreSQL and Firebird, in particular, should support even more sophisticated options. > 3. Can a MySQL or PostgreSQL database grow from 500kb to gigabytes without any > administrative conversion and maintenance? Progress database is a > deploy-once-then-forget type of database. It grows to any size without > needing any administrative tweaking at all. More or less. I know that with Interbase, if you go beyond 2GB, you will need to span your database across multiple files. (Not sure if the 2GB limit is still present in the latest Firebird). Don't know about PostgreSQL, but 2GB or 4GB is often an OS filesystem (at least under NTFS and earlier Linux filesytems) imposed limit even if the DBMS can support larger sizes. > 4. Does MySQL and PostgreSQL support database triggers? Stored procedures? MySQL no. PostgreSQL and Firebird, yes. > 5. Does MySQL and PostgreSQL enable before-imaging and after-imaging? Are > there any equivalent technologies that ensures transaction/database integrity > in these databases? > > This is also a very important feature. In Progress, if the machine > accidentally shuts down and the client has open transactions, Progress will > automatically roll-back the uncommitted transactions upon startup through > before-imaging. In this way, database write/update integrity is taken cared > off automatically without scripting lengthy resolution commands. Firebird has this. PostgreSQL afaik should also. MySQL supports transactions for BDB and InnoDB tables but not MyISAM. > 6. Can I issue SQL commands through ODBC in the client GUI to MySQL or > PostgreSQL Linux databases, so that the client can dynamically open and close > database connections? Not sure about this... > 7. Does MySQL and PostgreSQL support multiple database connections? Yes, of course. I think that's the whole point of client/server. > 8. Can MySQL and PostgreSQL store BLOBS (binary, large objects)? AFAIK, yes for all three. I can say with certainty only for MySQL and Firebird. For Firebird vs. PostgreSQL, the main difference I've seen is that PostgreSQL has Object-Relational features which Firebird lacks. However, my impression is that Firebird is much easier to deal with than PostgreSQL and it has a lot of great GUI tools (largely Windows-based but I know of at least one Linux based one) . Firebird is a bit less friendly than MySQL owing to its sophistication. But the amazing thing about it is the extremely small footprint. I believe it's smaller than MySQL-Max even though *it provides the full array of SQL features the latter sorely lacks* - everything from triggers to views to stored procedures. Firebird also has array datatypes. MySQL doesn't. PostgreSQL, not sure. For me, MySQL's primary strength is its really, really friendly SQL dialect. However, the lack of many standard SQL features is a big, big handicap that, for me, cannot be overcome by the nice SQL dialect. MySQL is totally ideal for learning basic SQL (or simple web databases) after which you will want to move up to the more heavyweight DBMSes. You will stick to MySQL only if you like reinventing the wheel - making up for the missing functionality by coding extra hard to emulate them. _ Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph To leave: send "unsubscribe" in the body to [EMAIL PROTECTED] Fully Searchable Archives With Friendly Web Interface at http://marc.free.net.ph To subscribe to the Linux Newbies' List: send "subscribe" in the body to [EMAIL PROTECTED]
