Reverse engineer existing Oracle tables to Quick SQL

If you didn't hear about Oracle Quick SQL, it's time to read about it as it's something you have without knowing (it's a packaged app in Oracle APEX) and I believe you should start using :)

Quick SQL enables you to rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL. You can easily create master detail relationships, check constraints, and even generate sample data.

In my blog post Create the Oracle database objects I go over the history how I created database objects and why I think Quick SQL is great and why I use it.

I guess most people typically use Quick SQL at the start of a new project, as it's the quickest way to create your data model and Oracle database objects. That is my primary use case too, but I started to use Quick SQL even on projects where database objects already exist.

In the project I'm currently involved in, the datamodel was generated by another tool, but as we iterate through the project, tables change, columns get renamed and added, row version were requested, triggers need to be made Oracle APEX aware...

Now we could do those changes manually, but I thought it made much more sense to create the data model in Quick SQL and use the features that come with Quick SQL. By clicking a checkbox we can include a Row version, Quick SQL generates the triggers automatically in an APEX aware form, we can generate as much sample data as we want by adding /insert and we can use all the other features that come with Quick SQL. For example when you want to include a history table in the future it's just another checkbox to click.

It's also easy to check-in the Quick SQL script into source control, together with the generated DDL.
If changes need to be done, we can adapt in Quick SQL and generate the DDL again and we see the changes immediately. It would be nice if Quick SQL could generate the ALTER statements too, but that's not the case yet. But it's easy enough to see the changes that were done by comparing the scripts in source control.

If you also want to reverse engineer an existing model into Quick SQL, here's a script that gives you a head start generating the markdown style format.

	declare
	l_prefix varchar2(15) := 'EBA_DBTOOLS';
	l_spaces varchar2(10) := ' '; -- use ' ' in web
	l_enter varchar2(10) := CHR(10); -- use '<br/>' in web
	l_constraint varchar2(500);
	begin
	for t in (select t.table_name,
	case when m.comments is not null then ' [' || replace(replace(replace(m.comments,'[','{'),']','}'),CHR(10)) ||']' end as comments
	from user_tables t, user_tab_comments m,
	(select max(lvl) as lvl, table_name
	from
	(select level as lvl, table_name, rpad('_', (level-1)*2, '_') || table_name as tbl
	from (
	select a.table_name, a.constraint_name pkey_constraint, b.constraint_name fkey_constraint, b.r_constraint_name
	from user_constraints a, user_constraints b
	where a.table_name = b.table_name (+)
	and a.constraint_type = 'P'
	and b.constraint_type (+) = 'R'
	and a.table_name like l_prefix || '%'
	)
	start with fkey_constraint is null
	connect by prior pkey_constraint = r_constraint_name
	)
	group by table_name
	) o
	where t.table_name = m.table_name (+)
	and t.table_name = o.table_name (+)
	and t.table_name like l_prefix || '%'
	order by o.lvl, t.table_name
	)
	loop
	sys.htp.prn(l_enter || l_enter || lower(replace(t.table_name,l_prefix||'_')) || t.comments );
	for c in (select c.column_name, c.data_type, c.data_length, c.data_precision, c.data_scale,
	c.nullable, c.default_length, c.data_default,
	case when to_char(c.data_length) is not null and (c.data_type = 'NUMBER')
	then c.data_type || '(' || to_char(c.data_length) || ',' || nvl(c.data_precision,0) ||')'
	when c.data_type in ('BLOB','CLOB','DATE')
	then c.data_type
	when to_char(c.data_length) is not null
	then c.data_type || '(' || to_char(c.data_length) ||')'
	else c.data_type
	end as data_type_length,
	case when m.comments is not null then ' [' || replace(replace(replace(m.comments,'[','{'),']','}'),CHR(10)) ||']' end as comments
	from user_tab_columns c, user_col_comments m
	where c.table_name = t.table_name
	and c.column_name not in ('ID', 'CREATED_BY', 'CREATION_DATE', 'UPDATED_BY', 'UPDATE_DATE')
	and c.table_name = m.table_name (+)
	and c.column_name = m.column_name (+)
	order by c.column_id
	)
	loop
	l_constraint := '';
	for u in (select b.table_name, b.column_name, a.constraint_type, a.search_condition,
	replace(a.r_constraint_name,'_PK') as r_constraint_name,
	(select replace(i.table_name,l_prefix||'_','') from user_constraints i where i.constraint_name = a.r_constraint_name) as pk_table_name
	from user_constraints a, user_cons_columns b
	where a.constraint_name = b.constraint_name
	and b.table_name = t.table_name
	and b.column_name = c.column_name
	and a.constraint_type in ('U','R','C')
	)
	loop
	if u.constraint_type = 'U'
	then
	l_constraint := l_constraint || ' /unique';
	elsif u.constraint_type = 'R'
	then
	l_constraint := l_constraint || ' /fk ' || u.pk_table_name;
	elsif u.constraint_type = 'C' and u.search_condition like '%NOT NULL%'
	then
	l_constraint := l_constraint || ' /nn';
	elsif u.constraint_type = 'C'
	then
	l_constraint := l_constraint || ' /check ' || replace(substr(replace(replace(replace(u.search_condition,CHR(10),' '),CHR(13),' '),' ', ' '),instr(replace(replace(replace(u.search_condition,CHR(10),' '),CHR(13),' '),' ', ' '),'(')+1),')','');
	end if;
	end loop;
	sys.htp.prn(l_enter || l_spaces || rpad(lower(c.column_name),30, ' ') || ' ' || lower(c.data_type_length) || ' ' || l_constraint || c.comments);
	end loop;
	end loop;
	end;

view raw model_to_quicksql.sql hosted with ❤ by GitHub

I tried the script on the Quick SQL data model itself - the result you see below:

Hopefully you see the benefit of using Quick SQL in existing projects too and the script helps you get there. Also Quick SQL gets frequent updates - in the upcoming release (17.3.4), which is already online, you can add a Security Group ID to every table (to make your app multi-tenant) and you can rename the audit columns to your own naming conventions.