#22 remove reserved words as database column names

[Mathias Kussinger]

Feedback on DatabaseStructure4.txt

1. On column names

Warning!
The following words are not reserved in SQLite, but reserved in other DBMS plus used here:
- date (reserved in ISO/ANSI SQL99, DB2, ODBC, SQL Server Future, PostgreSQL 8, Oracle 10g)
- type (reserved in DB2)
- locale (reserved in DB2)
- ignore (reserved in ISO/ANSI SQL99, DB2, SQL Server Future, MySQL 5.0, PostgreSQL 8)
- space (reserved in ISO/ANSI SQL99, DB2, ODBC, SQL Server Future, PostgreSQL 8)
- committed (reserved in PostgreSQL 8)

I discovered this flaw because IntelliJ IDEA knows a bit of SQL syntax and showed me.

There are three levels of SQL portability:
Level 0: Be completely dependent on a particular dbms.
Level 1: Using a database structure that's known to work on most/all common dbms.
Level 2: Using an SQL subset that's known to work on most/all common dbms.

Level 1 is the most common because level 2 is very hard, depending on the requirements sometimes even impossible to achieve.
Level 0 is rarly found and usually avoided.

Karatasi currently belongs to level 0 of SQL portability.
SQLite does not offer good performance for multithreaded applications.
If we provide advanced server-side services, we might require a different DBMS instead of SQLite, for example MySQL or PostgreSQL.
In that situation, the current database structure cannot be reused without modifications.
Even in case the database structure would theoretically be sufficient, it would be incompatible with other DBMS.

[Christa Runge]

Ticket moved from /p/karatasi/bugs/310/

Can't be converted:

• _milestone: database_later