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.
