Etora

Easy tables for Oracle.

Download the latest

Etora is a preprocessor that translates a simple data definition langauge into SQL. Etora grew out of a simplified notation I was using to discuss data modelling choices with business development people. I could not inflict the actual SQL syntax on these people - that would be like feeding your dog a bowl of nuts and bolts. When it came time to write the actual table creation code, I realized that it would be smarter to automate the process. Therefore I cleaned up and standardized my "high level description" and wrote a processor to translate it.

Benefits include

1. Handles autoincrementing columns, including autoincrement integer primary key, date of insertion and date of last update. Etora transparently creates the needed triggers and sequences and names them properly.
2. Sets foreign key columns to the right type.
3. Uses meaningful names for entities like triggers and constraints. No more SYS_C001.
4. Allows abstract data types. For example, you could use type A for financial amounts, defining it as NUMBER(8,2). Later you could redefine A as NUMBER(10,4).

Because Etora allows arbitrary SQL to be included and passed through, there is no reason to hand edit the output of Etora. This means that as a project develops, you can continue to maintain the compact, legible Etora code and treat the SQL table definitions as compiled, executable code.
Check out the man page.

Tiny example

Here's an Etora table definition:

pet:
id          pk i++
name        CHAR(40)
born        D
entered     D i++

and here's the result of feeding it through Etora:

DROP TABLE PET;
CREATE TABLE PET (
    ID           NUMBER(10) NOT NULL    ,
    NAME         CHAR(40) NOT NULL      ,
    BORN         DATE NOT NULL          ,
    ENTERED      DATE NOT NULL          ,
CONSTRAINT PET_PK PRIMARY KEY (id)
);

DROP SEQUENCE PET_ID;
CREATE SEQUENCE PET_ID CACHE 20;

CREATE OR REPLACE TRIGGER PET_ID BEFORE INSERT ON PET
FOR EACH ROW
BEGIN
SELECT PET_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
CREATE OR REPLACE TRIGGER PET_ENTERED BEFORE INSERT ON PET
FOR EACH ROW
BEGIN
:new.ENTERED := SYSDATE;
END;
/

What Etora did:

1. Every column is NOT NULL, because none were specified as nullable.
2. ID is a NUMBER(10), the default type for a primary key. This default can be changed.
3. The Primary Key constraint is given a sensible name PET_PK so that when it shows up in an error message the meaning will be clear.
4. Etora created a sequence and trigger for the autoincrementing primary key. i++ means increment on insert.
5. Etora created a trigger to set the ENTERED date. In date context, i++ means set to current date on insert. u++ means set to current date on update. Guess what ui++ does.

Get the experimental version.

---

by asher@wildsparx.com