SaadaDBs can use one of the following database management systems : Postgres, MySQL or SQLite. This topic gives some keys to choose an appropriate one.

 There are 2 categories of DBMS in the 3 proposed

• Both Postgres and MySQLrun in client server/mode
• SQLite is embedded. The database is a simple file attached to the application.

 Installation

• Postgres/MySQL must be installed as a service on the host machine.
  • Accounts must be created and setup.
  • Servers must be able to read files in the repository. That can be tricky with windows or even with Linux.
  • The Saada administrator must have root privileges in the database system. All of that seems to be easy, but the experience shows that number of Saada users encounter difficulties with this step.
• SQLIte doesn’t require any setup. Libraries come with Saada and nothing more has to be done.

 Security

• Postgres/MySQL
  • Use specific sockets for clientr/server communication. Than can require firewall setup in case of publicly open databases. This particular point must be discussed with your system manager.
  • User accounts are created at installation time. These user names (postgres or mysql) are very well known by any hacker. These accounts must therefore be well protected
  • Saada suggests to use 2 different accounts. One with just read-only privileges for the Web interface and another with administrator privileges for the administration. This strategy prevents against SQL code injection from Web pages.

• SQLite
  • No accounting issue but users connected from the WEB interface have all privileges on the database. Although the Web interface does not contain any query allowed to write into the DBm that looks like a potential security hole . We suggest to do copies of the DB file... just in case.

 Database Size

• Postgres/MySQL support tables with ten of millions of rows. Just be careful with the indexing time which is quite longer with MySQL than with POstgresQL. * SQLite also support tables with ten of millions of rows but with dramatic restrictions.
  • No multi-criteria search on large tables.
  • No query returning too much rows on large tables.
  • Indexing is very slow (10 minutes per column on 5,000,000 rows e.g.)

As the above conditions can not be guaranteed, we suggest not to use SQLIte if the biggest table has more than 500,000 rows.

  Summary