Quick SQL Builder

Create a database in several minutes using Oracle’s Quick SQL

Monday, October 9, 2017

As a software developer we are supposed to implement the newest technology and play around with new features every day. In order to test and analyze those technologies most of the time very little POCs are build. One of the first things you need is data. Whether you want to test new technologies, show an implementation of a certain framework or play around with the efficiency of your newly made application, a data source is what you need.

Quick SQL?

Building an hierarchical structure of tables, relations, keypairs and other very fancy-looking features is often very repetitive and takes a lot of time just to throw away once you want to start with the real deal. Therefore Oracle came up with a new application called: Quick SQL. As the name says, the application helps you to quickly set up some mock data which can be used straight into your application.

Quick SQL is an application made by Oracle itself in an Apex framework. The main view looks like the picture below. There are 2 general components: a worksheet, where a dedicated ‘Quick SQL syntax’ is written, and a preview window which shows a preview of the SQL script extracted from the worksheet. “So, we have to learn a new programming language to simplify a programming language we’ve already know for years?” Well, that’s true, but the results are astonishing, and the new syntax is very intuitive. 

Let's hit the bar

Suppose we want to make a very ‘refreshing’ application which offers different bars in your surroundings. Typically bars are offering different drinks. Our bar certainly has some drinks and snacks, which also contains ingredients. Let’s make a database structure for our bar using Quick SQL. 

The picture above is the syntax for Quick SQL. The editor itself is extremely user-friendly and space based. If you would like to go deeper into the hierarchical structure of your database, just start with another space in front. This way Quick SQL realizes the structure and starts building a SQL-script on the fly.

By adding extra parameters like ‘/vc’ or ‘/values’ we can add extra information for the compiler to design more specific database tables. Quick SQL itself has already build in some text recognition. If the column name contains words like ‘street’, ‘city’, ‘number’, ‘date’, … the compiler auto-inserts pseudo-random values according to those names.

For the people who are still not convinced, this is the output of the program. Only 13 lines of input creates 700 lines of output ready to be copy-pasted into your favorite SQL tool. In just under 10 minutes, the whole database structure is formed and more time can be spend on even more important work-related procedures, like drinking coffee …

If you would like to give it a try, Quick SQL is available for free at the oracle website: https://apex.oracle.com/en/quicksql/

 

Good luck, and happy time saves.

Jonas 

 

 

Output: 

-- create tables
create table bar (
    id                             number not null constraint bar_id_pk primary key,
    name                           varchar2(255) not null,
    street                         varchar2(255),
    city                           varchar2(255),
    rating                         varchar2(4000),
    creationdate                   date
)
;

create table consumable (
    id                             number not null constraint consumable_id_pk primary key,
    bar_id                         number
                                   constraint consumable_bar_id_fk
                                   references bar on delete cascade,
    name                           varchar2(255),
    type                           varchar2(4000),
    price                          varchar2(4000)
)
;

create table ingredients (
    id                             number not null constraint ingredients_id_pk primary key,
    consumable_id                  number
                                   constraint ingredients_consumable_id_fk
                                   references consumable on delete cascade,
    name                           varchar2(255),
    type                           varchar2(4000)
)
;


-- triggers
create or replace trigger bar_biu
    before insert or update 
    on bar
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end bar_biu;
/

create or replace trigger consumable_biu
    before insert or update 
    on consumable
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end consumable_biu;
/

create or replace trigger ingredients_biu
    before insert or update 
    on ingredients
    for each row
begin
    if :new.id is null then
        :new.id := to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
    end if;
end ingredients_biu;
/


-- indexes
create index consumable_i1 on consumable (bar_id);
create index ingredients_i1 on ingredients (consumable_id);
-- load data
 
insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149729719217454616627185983628,
    'Supplier Change',
    '275 Little Browning Street',
    'Tanquecitos',
    '4',
    sysdate - 36
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149730928143274231256360689804,
    'Corporate Citizenship Efforts',
    '729 Cogullos Ave',
    'Sugarloaf',
    '3',
    sysdate - 39
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149732137069093845885535395980,
    'New Feature Verification',
    '574 Castana Street',
    'Dale City',
    '4',
    sysdate - 64
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149733345994913460514710102156,
    'Database Implementation',
    '85 Fortville Blvd',
    'Grosvenor',
    '4',
    sysdate - 70
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149734554920733075143884808332,
    'Overseas Relocation',
    '613 Vinings Ave',
    'Riverside',
    '2',
    sysdate - 79
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149735763846552689773059514508,
    'Cloud Sales Planning',
    '541 Campbelltown Street',
    'Ridgeley',
    '5',
    sysdate - 60
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149736972772372304402234220684,
    'Course Development',
    '787 Pendueles Ave',
    'Ashley Heights',
    '1',
    sysdate - 43
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149738181698191919031408926860,
    'Energy Efficiency',
    '162 Luxora Blvd',
    'Monfort Heights',
    '0',
    sysdate - 97
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149739390624011533660583633036,
    'Data Loss Prevention',
    '897 Dale City Place',
    'Point Marion',
    '4',
    sysdate - 4
);

insert into bar (
    id,
    name,
    street,
    city,
    rating,
    creationdate
) values (
    120997951149740599549831148289758339212,
    'Corporate Citizenship Efforts',
    '901 Houlton Place',
    'Eldon',
    '3',
    sysdate - 30
);

commit;
-- load data
-- load data
 
insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149741808475650762918933045388,
    120997951149740599549831148289758339212,
    'Inventory Optimization',
    'SNACK',
    'Pharetra, id.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149743017401470377548107751564,
    120997951149730928143274231256360689804,
    'Service Level Evaluation',
    'DRINK',
    'Ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149744226327289992177282457740,
    120997951149738181698191919031408926860,
    'Build Automation',
    'DRINK',
    'Primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149745435253109606806457163916,
    120997951149739390624011533660583633036,
    'Supplier Change',
    'DRINK',
    'Ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149746644178929221435631870092,
    120997951149730928143274231256360689804,
    'Release Cycle Change',
    'DRINK',
    'Ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149747853104748836064806576268,
    120997951149736972772372304402234220684,
    'Release Cycle Change',
    'DRINK',
    'Amet lectus. Nulla placerat iaculis aliquam. Vestibulum lacinia arcu in massa pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit amet massa eu lorem commodo ullamcorper.  Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut id nulla ac sapien.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149749062030568450693981282444,
    120997951149729719217454616627185983628,
    'Corporate Citizenship Efforts',
    'DRINK',
    'Cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia  lectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere blandit leo  eget malesuada. Vivamus efficitur ipsum tellus, quis posuere.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149750270956388065323155988620,
    120997951149734554920733075143884808332,
    'Night Call Reduction',
    'DRINK',
    'Non,  molestie sit amet.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149751479882207679952330694796,
    120997951149732137069093845885535395980,
    'Availability Optimization',
    'SNACK',
    'Molestie sit amet lectus. Nulla.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149752688808027294581505400972,
    120997951149733345994913460514710102156,
    'Night Call Reduction',
    'DRINK',
    'Lorem sit amet, imperdiet ex. Etiam cursus.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149753897733846909210680107148,
    120997951149733345994913460514710102156,
    'Release Cycle Change',
    'SNACK',
    'Id nulla ac sapien suscipit tristique ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149755106659666523839854813324,
    120997951149732137069093845885535395980,
    'New Graduate Training',
    'DRINK',
    'Ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia  lectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149756315585486138469029519500,
    120997951149739390624011533660583633036,
    'Availability Optimization',
    'SNACK',
    'Ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia  lectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149757524511305753098204225676,
    120997951149732137069093845885535395980,
    'Overseas Relocation',
    'SNACK',
    'Nulla placerat iaculis aliquam. Vestibulum lacinia arcu in massa pharetra.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149758733437125367727378931852,
    120997951149738181698191919031408926860,
    'Evaluation Of Compiler Performance',
    'SNACK',
    'Interdum et malesuada fames ac ante ipsum primis in faucibus. Ut id nulla ac sapien suscipit tristique ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149759942362944982356553638028,
    120997951149738181698191919031408926860,
    'Participation Rate Improvement',
    'SNACK',
    'Curae; Proin vulputate placerat pellentesque.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149761151288764596985728344204,
    120997951149740599549831148289758339212,
    'Selection Of Construction Site',
    'SNACK',
    'Pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149762360214584211614903050380,
    120997951149735763846552689773059514508,
    'Service Level Evaluation',
    'SNACK',
    'Id nulla ac sapien suscipit tristique ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149763569140403826244077756556,
    120997951149734554920733075143884808332,
    'Overseas Relocation',
    'DRINK',
    'Iaculis aliquam. Vestibulum lacinia arcu in massa pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit amet massa eu lorem commodo ullamcorper.  Interdum et malesuada fames ac ante.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149764778066223440873252462732,
    120997951149738181698191919031408926860,
    'Manual Review',
    'SNACK',
    'Vestibulum lacinia arcu in massa pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper diam suscipit elementum sodales. Proin sit amet massa eu lorem commodo ullamcorper.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149765986992043055502427168908,
    120997951149733345994913460514710102156,
    'Overseas Relocation',
    'DRINK',
    'Tristique ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149767195917862670131601875084,
    120997951149729719217454616627185983628,
    'Service Level Evaluation',
    'DRINK',
    'Suscipit tristique ac volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate placerat pellentesque. Proin viverra lacinia  lectus, quis consectetur mi venenatis nec. Donec convallis sollicitudin elementum. Nulla facilisi. In posuere.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149768404843682284760776581260,
    120997951149734554920733075143884808332,
    'Fault Rate Reduction',
    'DRINK',
    'Vestibulum eget rhoncus non,  molestie sit amet lectus. Nulla placerat iaculis aliquam. Vestibulum lacinia arcu in massa pharetra, id mattis risus rhoncus.  Cras vulputate porttitor ligula. Nam semper.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149769613769501899389951287436,
    120997951149734554920733075143884808332,
    'New Feature Verification',
    'DRINK',
    'Porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci.'
);

insert into consumable (
    id,
    bar_id,
    name,
    type,
    price
) values (
    120997951149770822695321514019125993612,
    120997951149740599549831148289758339212,
    'Claim Reduction Plan',
    'SNACK',
    'Volutpat risus.  Phasellus vitae ligula commodo, dictum lorem sit amet, imperdiet ex. Etiam cursus porttitor tincidunt. Vestibulum ante ipsum  primis in faucibus orci luctus et ultrices posuere cubilia Curae; Proin vulputate.'
);

commit;
-- load data
 
insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149772031621141128648300699788,
    120997951149744226327289992177282457740,
    'Machine Translation Evaluation',
    'FISH'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149773240546960743277475405964,
    120997951149767195917862670131601875084,
    'Customer Satisfaction Improvement',
    'FISH'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149774449472780357906650112140,
    120997951149756315585486138469029519500,
    'Competitive Analysis',
    'VEGETABLE'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149775658398599972535824818316,
    120997951149759942362944982356553638028,
    'Cloud Sales Planning',
    'FISH'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149776867324419587164999524492,
    120997951149751479882207679952330694796,
    'Machine Translation Evaluation',
    'MEAT'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149778076250239201794174230668,
    120997951149747853104748836064806576268,
    'Cloud Sales Planning',
    'MEAT'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149779285176058816423348936844,
    120997951149759942362944982356553638028,
    'Git Migration',
    'MEAT'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149780494101878431052523643020,
    120997951149741808475650762918933045388,
    'Reduce Development Cost',
    'VEGETABLE'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149781703027698045681698349196,
    120997951149757524511305753098204225676,
    'Inventory Optimization',
    'VEGETABLE'
);

insert into ingredients (
    id,
    consumable_id,
    name,
    type
) values (
    120997951149782911953517660310873055372,
    120997951149765986992043055502427168908,
    'Buying Activity Analysis',
    'VEGETABLE'
);

commit;
 
-- Generated by Quick SQL Sunday October 08, 2017  10:15:48
 
/*
BAR /insert 10
 name /nn
 street
 city
 rating /values 0,1,2,3,4,5
 creationDate
 CONSUMABLE /insert 25
  name /vc
  type /values DRINK, SNACK
  price /number
  INGREDIENTS /insert 10
   name 
   type /values meat, vegetable, fish

# settings = { PK: "TRIG", language: "EN" }
*/