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.
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.
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" }
*/