Etora |
Easy tables for Oracle.
Download the latestEtora 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
- 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.
- Sets foreign key columns to the right type.
- Uses meaningful names for entities like triggers and constraints. No more SYS_C001.
- 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).
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:
- Every column is NOT NULL, because none were specified as nullable.
- ID is a NUMBER(10), the default type for a primary key. This default can be changed.
- 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.
- Etora created a sequence and trigger for the autoincrementing primary key. i++ means increment on insert.
- 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.
by asher@wildsparx.com