Page 1 of 1

[Solved]Which Would Make a Better Back-End, MySQL or HSQLDB?

PostPosted: Sat Jul 13, 2013 9:36 pm
by Steve R.
Currently I am working on using Base as the front-end with MySQL as a back-end. As I have been exploring the posts here, it turns out that HSQLDB can also be used as a back-end with Base. A possible new approach. Any thoughts on which database would make for a "better" back-end (that may be distributed to others who may not be computer literate.)?

Re: Which Would Make for a Better Back-End, MySQL or HSQLDB?

PostPosted: Sun Jul 14, 2013 9:47 am
What do you mean by distributed?

If you mean that the end-user will be required to install and manage (start-up/shutdown) MySQL in order to use a database, then it's definitely time to consider the advantages of HSQLDB.

Perhaps see:

As a general rule, HSQLDB 2.x is the all-around best choice with Base when:
    (1) a flat-file database is insufficient (dBase option)
    (2) ease-of-use is a major factor; HSQLDB is a zero-admin, ANSI/ISO-SQL, full-featured, compact (2MB), cross-platform engine
    (3) using the database in portable computing environments (such as Dropbox or USB flash drive) where installation isn't required/desired/allowable
    (4) automatic engine start-up/shutdown with Base is desirable (available only when running in single-user mode)
    (5) top-tier performance is desired; HSQLDB itself is generally much faster in a desktop environment than MySQL, PostgreSQL, Firebird, Oracle, etc.
    (6) single-user access is sufficient; or a computer-savvy person sets-up HSQLDB to run in server-mode for multi-user access
    (7) limited-scalability is not an overriding factor; HSQLDB 2.x will support perhaps 50+ concurrent LAN users, but unlike a "full-scale" engine it's not designed/tuned/tested from the ground-up for large-scale deployment (100+ user-count)
    (8) the end-user is able to install AOO / LibO / portable including the Java Runtime Environment (JRE / portable) option
    (9) the end-user is able to enable macros in AOO/LibO as necessary for the recommended split-database support
    (10) you have sufficient RAM for query results; while disk-based tables may include billions of records, query results are generated in RAM by default with HSQLDB for maximum performance; the default 64MB Java RAM allocation will accommodate about ~50,000 records in a query result-set, assuming average-size records; an "average size record" is typically 1024 bytes (1KB) or less, which further assumes proper data-types or table structures when storing Large Objects (BLOB/CLOB); otherwise the user can follow step-by-step instructions to increase the Java 'heap' setting in AOO/LibO (for example, 1GB RAM supports ~1 million records per query result-set); if necessary a computer-savvy person can setup disk-based result-sets in HSQLDB to handle queries that exceed available RAM

As a general rule, MySQL is a better choice than HSQLDB 2.x when:
    (1) working with Base in a non-Java environment (and PostgreSQL is somehow overlooked :P )
    (2) the MySQL licencing is acceptable (not a factor with PostgreSQL ;) )
    (3) planning to exceed ~50 concurrent users in a multi-user LAN environment (with the required computer-savvy setup admin)
    (4) planning to exceed a quarter-trillion records per database (HSQLDB 2.3 limit = 270 billion records = 100 new records per second for 85 years)
    (5) the database is hosted on a remote web server (with tech-support willing to setup MySQL and a firewall port for 'remote database access' from Base)
    (6) planning to implement enterprise-level database features such as replication, clustering, etc. (for mission-critical, 24/7 access requirements)
    (7) planning to move beyond Base to an in-house solution using other RAD tools or framework-based front-ends (requires programming expertise)

As a general rule, you can skip the in-house database mind-set altogether and adopt a web form builder service when:
    (1) *Office (OOo/AOO/LibO) document integration is unnecessary
    (2) you prefer a super-easy solution with form templates and/or drag-&-drop form creation (which automatically creates the necessary online database and services)
    (3) you like the idea of a professionally-managed, web-hosted database with distributable form links, often mobile web-browser friendly
    (4) you can live with limited design, query and reporting capabilities
    (5) you can live with some data-privacy issues (no local-key, client-side encryption options...yet) and the rudimentary data import/export models
    (6) it proves cost-effective
      (a) simple web-form services can be free for such things as surveys or relatively simple contact management/CRM
      (b) advanced web-form services are just as easy (drag-&-drop), but rarely free (for example: invoicing or eCommerce forms can include online payments through PayPal, Google Checkout, etc)
      (c) pricing is often based on the number of forms, submissions (results; records), users, and/or storage allocation

Re: Which Would Make foe a Better Back-End, MySQL or HSQLDB?

PostPosted: Sun Jul 14, 2013 3:25 pm
by Steve R.
The database I have been fiddling with lists science fiction magazines and the stories contained in each issue. Base on the criteria that you listed, MySQL would be overkill for my project. So far, that is the way it is set-up, as I was unaware of the ability to split HSQL from Base at the time. I've also appreciated the ability to use PhpMyAdmin to work directly with the MySQL datasets.

By "distributed", I meant "give-away" to others who may like to have a personal copy. One reason for asking this question is the very issue that you pointed out: "If you mean that the end-user will be required to install and manage (start-up/shutdown) MySQL in order to use a database, then it's definitely time to consider the advantages of HSQLDB." This project is really more of a learning experience since it would be somewhat redundant to the existing Internet Speculative Fiction Database.

Since posting, I recall one major reason for using MySQL. I am planning to use this database as a platform to learn PHP and access the database through a browser (Firefox) on the home network. At this time, I've only gotten as far a doing a simple dump of the Authors Table.

Using HSQLDB is a definite option for some future (small scale) projects that I have in mind. Thanks for providing links. It's good to know that this approach can be used.

Re: Which Would Make foe a Better Back-End, MySQL or HSQLDB?

PostPosted: Sun Jul 14, 2013 6:53 pm
Steve R. wrote:MySQL would be overkill for my project...[although]...I am planning to use this database as a platform to learn PHP and access the database through a browser (Firefox) on the home network.

That's largely my point. Once you've exhausted the scalability and natural realm of of a full-featured desktop/LAN engine like HSQLDB 2.x, you've also left the typical/appropriate realm of Base as the front-end -- as a general rule. In other words, if you actually need a full-scale engine, Base is no longer relevant for the project.

You don't need PHP or a browser for concurrent database access on a home network (LAN). A Base connection file (.odb) can be configured for local, LAN or Web database access. So you can access the database using Base as the client-application, while running the database in server-mode from a shared folder on any networked (LAN) computer.

However, if you need both LAN and internet access to the database, then you'll need to run the RDBMS in web-server/servlet mode using a web application server. You can configure Base using a web protocol (http: or https:) as necessary. You can run HSQLDB on a web-server using Tomcat, Jetty, etc. -- but that's generally the point at which I would move on to PostgreSQL or MySQL during the initial planning phase. If you've already begun development with HSQLDB, then HSQLDB 2.x should suffice as a web-hosted database given the "wide range of enterprise deployment options" designed for high-traffic concurrency.

It takes some know-how to setup both local LAN database access and remote web access to a database. But in either case, it's much easier to configure Base to serve as the front-end (GUI user-interface), than it would be to adopt an internet-browser as the front-end since that involves learning PHP and JavaScript (or HTML5). Certainly in the long-run, it will be advantageous to learn browser-based data-access (PHP/JavaScript or HTML5) and/or framework-based forms. But in the short-run, it might be satisfying to get these data-access options working with a RAD tool like Base first.

So when creating a new relational database for use primarily within the scope of the AOO/LibO environment, HSQLDB 2.x is not only up to the task, but also the most practical choice for a variety of reasons including:
    ease of setup and daily use
    database portability to include seamless cross-platform support
    superior desktop performance
    best-of-breed ANSI/ISO SQL support
    all the functions you could imagine plus stored-procedures to create your own
    second-to-none LOB support
    remote authentication with role-based security and encryption
    reasonable scalability
    free with excellent licensing
    ...and perhaps more...