Table

In UI Wizard, we told the system what kind of form we want and it created database table of our form in the background. Normally, we create database table first (after weeks or months of analysis and design) and then create a form from that table.

To create a table we need SQL DDL command for that table. To get it click on Version Control on main menu and then Synchronize SQL DDL Commands.

Go to SQL Scripts on Stage, select SQL command create table x_employee... and click on

This is SQL command that system created and ran when we said we wanted a form called employee in UI Wizard. You might be thinking "why are you showing this? I know SQL I could write create command for employee table from scratch".

We are showing you this because of these:

version_no integer NOT NULL DEFAULT 1,
insert_user_id integer NOT NULL DEFAULT 1,
insert_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
version_user_id integer NOT NULL DEFAULT 1,
version_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,

Every table has these fields in iCodeBetter. So, let's explain what they are for.

  • version_no - This is version number, like a git commit id.

  • insert_user_id - Id of the user that created this data.

  • insert_dttm - Date and time of creation.

  • version_user_id - Id of the user that created this version (like owner of git commit)

  • version_dttm - Date and time of the version.

Let's copy this SQL command and modify it to create a new table. (rather than writing those system related table fields from scratch)

We won't change the fields, just the name.

create table hr_employee (
employee_id integer not null,
name character varying(1024) not null,
surname character varying(1024) not null,
gender integer,
birthdate_dt date not null,
version_no integer NOT NULL DEFAULT 1,
insert_user_id integer NOT NULL DEFAULT 1,
insert_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
version_user_id integer NOT NULL DEFAULT 1,
version_dttm timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
CONSTRAINT pk_hr_employee PRIMARY KEY (employee_id));
create sequence seq_hr_employee;

Now, click on New Editor on main menu and then SQL DDL Command for VCS.

Paste our SQL command create table hr_employee... and click save.(Run Local? must be ticked)

To pull the table that we just created from database. Go to DB Tables and click on (burn button). Write table name and click ok.

There are catalog tables that keep table metadata. In PostgeSQL, it is information_schema schema. In Oracle, there are tables called tables and all_tab_columns. iCodebetter takes data from these catalog tables and stores that data in its own tables called w5_table, w5_table_field, and w5_table_param.