XML to structure and back again.

Hi again.

After a year long pause, I have started with this again.
And so I now have my first test of the XML-to-SQL objects-to-XML.
And it works to both parse and generate it back from the object structure and getting a file identical to the first.

The XML looks like this:

<?xml version="1.0" ?>
<sql:Statement xmlns:sql="http://www.unifiedbpm.se/XMLschema/DAL/SQL"
xmlns:xsi="http://www.w3.org/2001/XMLSchema" xsi:schemaLocation="http://www.unifiedbpm.se/XMLschema/DAL/SQL ../SQL.xsd">
<sql:Verb_CREATE_TABLE>
<sql:Name>Table1</sql:Name>
<sql:Constraints>
<sql:Parameter_Constraint>
<sql:CheckConditions />
<sql:Constraint_Type>PRIMARY KEY</sql:Constraint_Type>
<sql:References>
 <sql:str>Table1ID</sql:str>
</sql:References>
<sql:Name>PK_Table1_Table1ID</sql:Name>
</sql:Parameter_Constraint>
<sql:Parameter_Constraint>
<sql:CheckConditions />
<sql:Constraint_Type>FOREIGN KEY</sql:Constraint_Type>
<sql:References>
 <sql:str>Table1ID</sql:str>
 <sql:str>Table2</sql:str>
 <sql:str>Table2ID</sql:str>
</sql:References>
<sql:Name>FK_Table1_Table1ID_Table2_Table2ID</sql:Name>
</sql:Parameter_Constraint>
<sql:Parameter_Constraint>
<sql:CheckConditions>
 <sql:Parameter_Conditions>
  <sql:Conditions>
   <sql:Parameter_Condition>
    <sql:Operator>></sql:Operator>
    <sql:ANDOR>AND</sql:ANDOR>
    <sql:Right>
     <sql:Parameter_Numeric>
      <sql:Numeric_Value>2.4</sql:Numeric_Value>
     </sql:Parameter_Numeric>
    </sql:Right>
    <sql:Left>
     <sql:Parameter_Numeric>
      <sql:Numeric_Value>1.3</sql:Numeric_Value>
     </sql:Parameter_Numeric>
    </sql:Left>
   </sql:Parameter_Condition>
   <sql:Parameter_Condition>
    <sql:Operator>LIKE</sql:Operator>
    <sql:ANDOR>AND</sql:ANDOR>
    <sql:Right>
     <sql:Parameter_String>
      <sql:String_Value>%</sql:String_Value>
     </sql:Parameter_String>
    </sql:Right>
    <sql:Left>
     <sql:Parameter_Identifier>
      <sql:Identifier>T1.firstname</sql:Identifier>
     </sql:Parameter_Identifier>
    </sql:Left>
   </sql:Parameter_Condition>
  </sql:Conditions>
 </sql:Parameter_Conditions>
</sql:CheckConditions>
<sql:Constraint_Type>CHECK</sql:Constraint_Type>
<sql:References>
 <sql:str>Table1ID</sql:str>
</sql:References>
<sql:Name>CK_Table1_Name</sql:Name>
</sql:Parameter_Constraint>
<sql:Parameter_Constraint>
<sql:CheckConditions />
<sql:Constraint_Type>UNIQUE</sql:Constraint_Type>
<sql:References>
 <sql:str>Table1ID</sql:str>
</sql:References>
<sql:Name>UQ_Table1_Name</sql:Name>
</sql:Parameter_Constraint>
</sql:Constraints>
<sql:Columns>
<sql:Parameter_ColumnDefinition>
<sql:Datatype>serial</sql:Datatype>
<sql:Default />
<sql:NOTNULL>True</sql:NOTNULL>
<sql:Name>Table1ID</sql:Name>
</sql:Parameter_ColumnDefinition>
<sql:Parameter_ColumnDefinition>
<sql:Datatype>string(400)</sql:Datatype>
<sql:Default />
<sql:NOTNULL>False</sql:NOTNULL>
<sql:Name>Table1Name</sql:Name>
</sql:Parameter_ColumnDefinition>
<sql:Parameter_ColumnDefinition>
<sql:Datatype>timestamp</sql:Datatype>
<sql:Default />
<sql:NOTNULL>False</sql:NOTNULL>
<sql:Name>Table1Changed</sql:Name>
</sql:Parameter_ColumnDefinition>
</sql:Columns>
<sql:postSQL />
</sql:Verb_CREATE_TABLE>
</sql:Statement>

As you see, it is basically an abstraction of the SQL language.
It should be pretty easy to write a GUI for generating the XML that would be similar to actually writing SQL.
With that as input, the following SQLs are generated when calling any of the SQL objects' AsSQL-method(note the oracle syntax):


SQL for the MySQL database:

CREATE TABLE Table1 (
`Table1ID` INTEGER AUTO_INCREMENT NOT NULL,
`Table1Name` VARCHAR(400),
`Table1Changed` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT `PK_Table1_Table1ID` PRIMARY KEY (Table1ID),
CONSTRAINT `FK_Table1_Table1ID_Table2_Table2ID` FOREIGN KEY (Table1ID) REFERENCES Table2(Table2ID),
CONSTRAINT `CK_Table1_Name` CHECK ((1.3 > 2.4) AND (T1.firstname LIKE '%ercedes')),
CONSTRAINT `UQ_Table1_Name` UNIQUE (Table1ID)
)


SQL for the PostgreSQL database:



CREATE TABLE Table1 (
"Table1ID" serial NOT NULL,
"Table1Name" VARCHAR(400),
"Table1Changed" DATETIME DEFAULT(NOW()),
CONSTRAINT "PK_Table1_Table1ID" PRIMARY KEY (Table1ID),
CONSTRAINT "FK_Table1_Table1ID_Table2_Table2ID" FOREIGN KEY (Table1ID) REFERENCES Table2(Table2ID),
CONSTRAINT "CK_Table1_Name" CHECK ((1.3 > 2.4) AND (T1.firstname ILIKE '%ercedes')),
CONSTRAINT "UQ_Table1_Name" UNIQUE (Table1ID)
)


SQL for the Oracle database:



CREATE TABLE Table1 (
"Table1ID" integer NOT NULL,
"Table1Name" VARCHAR2(400),
"Table1Changed" DATETIME DEFAULT(NOW()),
CONSTRAINT "PK_Table1_Table1ID" PRIMARY KEY (Table1ID),
CONSTRAINT "FK_Table1_Table1ID_Table2_Table2ID" FOREIGN KEY (Table1ID) REFERENCES Table2(Table2ID),
CONSTRAINT "CK_Table1_Name" CHECK ((1.3 > 2.4) AND (T1.firstname LIKE '%ercedes' escape 'C')),
CONSTRAINT "UQ_Table1_Name" UNIQUE (Table1ID)
)
CREATE SEQUENCE seq_Table1_Table1ID_DAL_auto_increment
start with 1
increment by 1
nomaxvalue;
CREATE TRIGGER tr_Table1_Table1ID_DAL_auto_increment
BEFORE INSERT ON Table1 FOR EACH ROW BEGIN
SELECT seq_Table1_Table1ID_DAL_auto_increment.nextval INTO :new.id FROM dual;
end;


SQL for the DB2 database:



CREATE TABLE Table1 (
"Table1ID" INT GENERATED ALWAYS AS IDENTITY NOT NULL,
"Table1Name" VARCHAR(400),
"Table1Changed" TIMESTAMP,
CONSTRAINT "PK_Table1_Table1ID" PRIMARY KEY (Table1ID),
CONSTRAINT "FK_Table1_Table1ID_Table2_Table2ID" FOREIGN KEY (Table1ID) REFERENCES Table2(Table2ID),
CONSTRAINT "CK_Table1_Name" CHECK ((1.3 > 2.4) AND (T1.firstname LIKE '%ercedes')),
CONSTRAINT "UQ_Table1_Name" UNIQUE (Table1ID)
)


SQL for the SQLserver database:



CREATE TABLE Table1 (
[Table1ID] int IDENTITY(1,1) NOT NULL,
[Table1Name] varchar(400),
[Table1Changed] DATETIME DEFAULT(NOW()),
CONSTRAINT [PK_Table1_Table1ID] PRIMARY KEY (Table1ID),
CONSTRAINT [FK_Table1_Table1ID_Table2_Table2ID] FOREIGN KEY (Table1ID) REFERENCES Table2(Table2ID),
CONSTRAINT [CK_Table1_Name] CHECK ((1.3 > 2.4) AND (T1.firstname LIKE '%ercedes')),
CONSTRAINT [UQ_Table1_Name] UNIQUE (Table1ID)
)


Small disclaimer: I haven't run these scripts on all database architectures, am currently rebuilding the testing environment.

Database Abstraction Layer - DAL

Hi!

The last month has been somewhat difficult with regards to time available.
But I've still managed to churn out an early version of what will be the core of the database abstraction layer, the classes that generates the SQL for each database.
The code is currently supporting MySQL, PostgreSQL, Oracle and SQL Server.
Since they will be sent as parameters into DAL, I call them parameters.
They do, however, have more functionality than being mere parameters.
There is a base class, Parameter_Base, that all parameters inherits from, that has four methods:

• AsMySQL()
• AsPostGreSQL()
• AsOracle()
• AsSQLServer()

Each of these methods returns a database specific SQL-representation of the parameters contents.
There are classes like Parameter_Field, Parameter_Expression, Parameter_CASE, Parameter_Function and Parameter_SELECT and so on.

They use each other to generate, for example, a SELECT-statement.
Consequently, Parameter_SELECT has (currently) two properties, fields(list of Parameter_Field) and sources (list of Parameter_Source), which in turn uses other classes.
So when one calls the Parameter_SELECT.AsMySQL function, it loops through the objects in its fields and sources list and calls their .AsMySQL functions. This way, a MySQL specific SQL-representation of the Parameter_SELECT-object is generated.

The parameters: parameter.py
Some tests: parameter_tests.py
Crude documentation: Doc_20100613.pdf

So far, most elements of typical SELECT statements are covered, like expressions and joins.
Stuff like ORDER BY, GROUP BY and HAVING aren't, but from now on, it should be much more easy since they merely reuse the concepts I have already defined.
I'd have to admit, maybe the structure looks pretty natural when you look at it, but it was really quite hard to generalise and simplify the structure while maintaining all the functionality of the language.

Well. I suppose that was it.