Zen-Dreams

When I decided to re-write the DNA-Framework, I didn’t know where to start first. I was thinking about creating all the security structure first, but I then realised that mostly the security infrastructure will use the dabatase to store information.

So I first needed to create the DAL.

The DAL must stay : easy to use, coherent (meaning no differences between different DBMS) and with no useless features.

So the first thing to do was to create a generic class encapsulating every aspect that will be generic enough to be DBMS not specific. This class is dna.database.class. It also allowed me to define the behavior and structure of inheriting classes.

Talking about inheritance, for now there are only four classes. MySQL, PostgreSQL, SQLite 2 and Oracle. Yes this means that the DNA-Framework will have support for all of these systems and probably more when I’ll create an ODBC class ;)

So, briefly, I started the creation of the class system monday and today, the four classes exists and are able to :

• Execute SQL queries (anything)
• Select and browse through results
• Export as SQL or CSV result of a query
• Import from SQL and CSV
• Fetch various states (queries / dbms)

Below is an example of the same code executed on 4 different DBMS.

MySQL

SELECT * FROM MS_User
    -> anthony@mysql.com
    -> toto@mysql.com
INSERT INTO MS_User (Email) VALUES ('test@MySQL.com')
SELECT * FROM MS_User
    -> anthony@mysql.com
    -> toto@mysql.com
    -> test@MySQL.com
DELETE FROM MS_User WHERE Email='test@MySQL.com'
SELECT * FROM MS_User
    -> anthony@mysql.com
    -> toto@mysql.com
PostgreSQL

SELECT * FROM "MS_User"
    -> Anthony@PostgresQL.com
    -> toto@postgresql.com
INSERT INTO "MS_User" ("Email") VALUES ('test@PostgreSQL.com')
SELECT * FROM "MS_User"
    -> Anthony@PostgresQL.com
    -> toto@postgresql.com
    -> test@PostgreSQL.com
DELETE FROM "MS_User" WHERE "Email"='test@PostgreSQL.com'
SELECT * FROM "MS_User"
    -> Anthony@PostgresQL.com
    -> toto@postgresql.com
SQLite

SELECT * FROM MS_User
    -> anthony@sqlite.org
    -> toto@sqlite.org
INSERT INTO MS_User (Email) VALUES ('test@SQLite.com')
SELECT * FROM MS_User
    -> anthony@sqlite.org
    -> toto@sqlite.org
    -> test@SQLite.com
DELETE FROM MS_User WHERE Email='test@SQLite.com'
SELECT * FROM MS_User
    -> anthony@sqlite.org
    -> toto@sqlite.org
Oracle OCI

SELECT * FROM MS_User
    -> toto@oracle.com
    -> Anthony@oracle.com
INSERT INTO MS_User (EMAIL) VALUES ('test@Oracle OCI.com')
SELECT * FROM MS_User
    -> toto@oracle.com
    -> Anthony@oracle.com
    -> test@Oracle OCI.com
DELETE FROM MS_User WHERE EMAIL='test@Oracle OCI.com'
SELECT * FROM MS_User
    -> toto@oracle.com
    -> Anthony@oracle.com