APEX: Creating Tables and Views with Quicksql

If you’re creating an application in Oracle Database, Oracle Quick SQL can be a very  useful tool. This  application provides a quick way to generate the SQL required to create a relational data model from an indented text document. However It’s not designed to be a replacement for data modeling, it is simply a quick start. Once the SQL is generated it can be tweaked and expanded upon.

But which is the best start? Here 3 examples of creating 2 related tables and 1 view, I’ll try to show you pro and cons and I’d like to know your opinion.

So, let’s start!

Example1:

# settings = { PK: "TRIG", language: "EN", prefix:"ex1", apex:true, auditcols:true  }
table1
  cod
  des1
  table2
    cod
    des2

view table_vw table1 table2

This is the classic way, the shortest and the most elegant. It create the following code for the tables:

create table ex1_table1 (
    id                             number not null constraint ex1_table1_id_pk primary key,
    cod                            varchar2(4000),
    des1                           varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table ex1_table2 (
    id                             number not null constraint ex1_table2_id_pk primary key,
    table1_id                      number
                                   constraint ex1_table2_table1_id_fk
                                   references ex1_table1 on delete cascade,
    cod                            varchar2(4000),
    des2                           varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

Note: you can have same column name in different tables (q.e. cod or the audit cols names).

The view code is the following:

create or replace view ex1_table_vw as 
select 
 table1.id             table1_id,
 table1.cod            table1_cod,
 table1.des1           des1,
 table1.created        table1_created,
 table1.created_by     table1_created_by,
 table1.updated        table1_updated,
 table1.updated_by     table1_updated_by,
 table2.id             table2_id,
 table2.cod            table2_cod,
 table2.des2           des2,
 table2.created        table2_created,
 table2.created_by     table2_created_by,
 table2.updated        table2_updated,
 table2.updated_by     table2_updated_by
from 
 ex1_table1 table1,
 ex1_table2 table2
where
 table2.table1_id(+) = table1.id;

Note: the column with the same name in the 2 tables have a prefix in the alias name (id, cod), the others don’t (des1, des2).I don’t like it, I’d like alle the column had a table prefix in the alias name. There are no foreign keys. There is also a (+) in the foreign key column that I want to delete.

Example2:

# settings = { PK: "TRIG", language: "EN", prefix:"ex2", apex:true, auditcols:true  }
table1
  t1 cod
  t1 des1
  table2
    t2 cod
    t2 des2

view table_vw table1 table2

In this example, we prefix all the column with a table alias (t1,t2).

In the code, all the columns have the alias prefix except the primary key, the foreign key and the audit columns:

create table ex2_table1 (
    id                             number not null constraint ex2_table1_id_pk primary key,
    t1_cod                         varchar2(4000),
    t1_des1                        varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table ex2_table2 (
    id                             number not null constraint ex2_table2_id_pk primary key,
    table1_id                      number
                                   constraint ex2_table2_table1_id_fk
                                   references ex2_table1 on delete cascade,
    t2_cod                         varchar2(4000),
    t2_des2                        varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

In the view we have some columns with alias prefix (normal columns), some with table prefix(primary keys and audit columns). There are no foreign keys. There is always an unwanted (+):

create or replace view ex2_table_vw as 
select 
    table1.id          table1_id,
    table1.t1_cod      t1_cod,
    table1.t1_des1     t1_des1,
    table1.created     table1_created,
    table1.created_by  table1_created_by,
    table1.updated     table1_updated,
    table1.updated_by  table1_updated_by,
    table2.id          table2_id,
    table2.t2_cod      t2_cod,
    table2.t2_des2     t2_des2,
    table2.created     table2_created,
    table2.created_by  table2_created_by,
    table2.updated     table2_updated,
    table2.updated_by  table2_updated_by
from 
    ex2_table1 table1,
    ex2_table2 table2
where
    table2.table1_id(+) = table1.id

Example3:

# settings = { PK: "TRIG", language: "EN", prefix:"ex3", apex:true, auditcols:true  }
table1
  t1 id /pk
  t1 cod
  t1 des1
table2
  t2 id /pk
  t2 t1 id /fk table1
  t2 cod
  t2 des2

view table_vw table1 table2

This case is the most verbose but create an alias prefix also for primary and foreign keys:

create table ex3_table1 (
    t1_id                          number not null constraint ex3_table1_t1_id_pk primary key,
    t1_cod                         varchar2(4000),
    t1_des1                        varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

create table ex3_table2 (
    t2_id                          number not null constraint ex3_table2_t2_id_pk primary key,
    t2_t1_id                       number
                                   constraint ex3_table2_t2_t1_id_fk
                                   references ex3_table1 on delete cascade,
    t2_cod                         varchar2(4000),
    t2_des2                        varchar2(4000),
    created                        date not null,
    created_by                     varchar2(255) not null,
    updated                        date not null,
    updated_by                     varchar2(255) not null
)
;

In the view all the columns have an alis prefix except the audit ones that have the table prefix. There are also foreign keys. The query is also incomplete (a bug??) and the where condition is missing we have to complete with:

t2_t1_id = t1_id

create or replace view ex3_table_vw as 
select 
    table1.t1_id       t1_id,
    table1.t1_cod      t1_cod,
    table1.t1_des1     t1_des1,
    table1.created     table1_created,
    table1.created_by  table1_created_by,
    table1.updated     table1_updated,
    table1.updated_by  table1_updated_by,
    table2.t2_id       t2_id,
    table2.t2_t1_id    t2_t1_id,
    table2.t2_cod      t2_cod,
    table2.t2_des2     t2_des2,
    table2.created     table2_created,
    table2.created_by  table2_created_by,
    table2.updated     table2_updated,
    table2.updated_by  table2_updated_by
from 
    ex3_table1 table1,
    ex3_table2 table2
where

/

So, what example do you prefer?

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *