On Wednesday 09 October 2002 8:54 pm, Andy Sy wrote: > I wouldn't even consider building an enterprise-wide system > with MySQL, InnoDB notwithstanding.
I'm also beginning to see that based on the responses to my post. Thanks to everyone who responded. MySQL needs to mature first and integrate it with InnoDB, plus a lot of benchmarks and load-testing before they cut it loose for rock-solid, dependable enterprise use. > 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. Can Firebird and PostgreSQL handle transactions with fine-granularity? In Progress, we can get as deep as we want in a transaction and undo the whole thing - hundreds of modules and thousands of records, if we want. We can nest transactions and decide what gets undone by enclosing the code block in 4GL statements such as DO TRANSACTION ON ERROR UNDO, RETRY: This feature is important since we update lots of records (like sensitive financial records, supplier accounts, copies of bank transaction records, etc..) and the ability to undo transactions is a safety net for fail-safe reliability and good error-handling. > 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. It's funny how Progress database can grow beyond 2GB in NT4.0. We'd love to span the database across multiple files in a RAID setup, but we haven't figured out how to do it before with NT. We need to study this filesize limit in Firebird on Linux extensively. Is it safe to assume that Firebird can be spanned and RAIDed at the same time in Linux? (There goes my awkward English again.) We would appreciate if we can grow the Firebird database to any size without running into filesize limits. The reason for this is that we need to reasonably predict future customer demand/stock levels using previous years' stock levels, so I guess we need to keep at least 2 years of historical data in the database. Example: calculating how much apples to order for this year's Christmas stock level by referring to last year's order minus last year's actual demand/sales, or something like that. What we do now is summarize last year's data and make it available for the current databases. But we'd also like to analyze current item traffic/turnover in relation to previous years', and we can't do this if we summarize previous years' item movements. So an uncompressed, unsummarized database is needed for this type of realtime analysis/queries. > 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. Views, triggers, and stored procedures are important functionalities that we use. In Firebird, is there a way to temporarily prevent triggers and stored procedures from firing? In Progress, we sometimes suspend triggers and stored procedures on special occassions (with a 4GL environment statement DISABLE-TRIGGERS) such as during dumping and reloading data from corrupted databases? Dumping and reloading is easy to do in Progress. A few mouse clicks in the Data Administration GUI, leave it for a while, then I'm done. We also sometimes disable stored procedures and triggers during application development. It is nice to do this once in a while to test the application. Since stored procedures and triggers are database-dependent, are there generic SQL-92 statements that can control stored procedures and triggers in any database? It's nice to hear Firebird has an extremely small footprint, and I presume it can run comfortably on the Proliant. What application development tool works well with Firebird, or provides a somewhat nice and tight compatible integration with Firebird? Since Firebird is from Borland, I presume the RAD IDE to use is Kylix3? > 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. I almost forgot that we use array datatypes extensively in Progress. We use array datatypes for multi-level pricing schemes (wholesale customers, card privilege customers, etc..). It's nice to know Firebird has one. > 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. I'm also beginning to see how other open-source database stacks up to our Progress database. In my opinion, Progress is truly a heavyweight database already, considering that the mature database technology I'm speaking of harks back to 1998. We haven't upgraded the database to newer versions because the old database perfectly serves our needs. Plus the relative simplicity of implementing multiversioning concurrency in three simple, easy-to-understand 4GL locking statements NO-LOCK, SHARE-LOCK, and EXCLUSIVE-LOCK are a breeze to work and code. The ease of working in Progress 4GL speeds up development time tremendously. The robust, multi-user database resolution it provides running atop modest hardware are things we take for granted because of the simplicity. Newer versions of the Progress AppServer has push and pull features (new 4GL statements like SUBSCRIBE and PUBLISH) similar to messaging systems, plus support for safe manipulation by PHP. Progress even has a RAD PHP IDE for developing web extension modules that interfaces to Apache plus Tomcat. The new Progress Application Development Model supports distributed 4GL objects, simplifying communication between distributed components between an application. But we're not closing our eyes to open-source databases and open-source scripting languages. We'd test them extensively, and decide whether to wait for these technology to mature, or jump ship if we see a good one. But for now, the proprietary technology in Progress outshines open-source alternatives on all benchmarks: reliability, ease-of-use, feature-set. Too bad the MySQL and Progress/Nusphere partnership didn't turn out fine. Thanks for all the valuable input everyone has given. -- mikol "There is no concept more closer to intellectual emancipation than free software. Freedom to responsibly code and share in its most free and pure form." -- Floyd Robinson, September 24, 2002 _ 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]
