SQL script generation


Back to XTG Data Modeller          

Application is able to generate the SQL script to create the designed data structure for certain database backends.
(InterBase, Oracle, Microsoft SQL Server, Centura SQLBase, MySQL, PostgreSQL, ...)

- You can check off what to generate (script appendix, drop index, drop table, UDD, create table, primary key, table appendix, foreign key, on delete, create index)
- You can select entites in the workspace
- You can define script appendix (beginning of the script, end of the script)
- You can define Table appendix to script for each table (e.g. create generator, insert into table, additional constraints, ...)

Sample of the output SQL script for InterBase 6

DROP TABLE SALE;
DROP TABLE STORE;
DROP TABLE PRODUCT;
DROP TABLE MONTH;

create domain TPrice as DECIMAL(8,2) CHECK (VALUE>0);
create domain TCustNo as INTEGER DEFAULT 9999 CHECK (VALUE>1000);

CREATE TABLE SALE (
   PRODUCT_ID INTEGER NOT NULL,
   STORE_ID TCustNo NOT NULL,
   YEAR_ID SMALLINT NOT NULL,
   MONTH_ID DECIMAL (6,0) NOT NULL,
   PCS INTEGER DEFAULT 1,
   PRICE TPrice,
   REVENUE TPrice,
   FLAG SMALLINT,
   REGION_ID SMALLINT NOT NULL,
 PRIMARY KEY (PRODUCT_ID,STORE_ID,YEAR_ID,MONTH_ID)
);

CREATE TABLE STORE (
   STORE_ID TCustNo NOT NULL,
   REGION_ID SMALLINT NOT NULL,
   STORE_NAME VARCHAR (30),
   STREET VARCHAR (30),
 PRIMARY KEY (STORE_ID)
);

CREATE TABLE PRODUCT (
   PRODUCT_ID INTEGER NOT NULL,
   CATEGORY_ID SMALLINT NOT NULL,
   PRODUCT_NAME VARCHAR (30),
 PRIMARY KEY (PRODUCT_ID)
);

CREATE TABLE MONTH (
   YEAR_ID SMALLINT NOT NULL,
   MONTH_ID DECIMAL (6,0) NOT NULL,
   MONTH_NAME VARCHAR (20),
 PRIMARY KEY (YEAR_ID,MONTH_ID)
);

ALTER TABLE MONTH ADD CONSTRAINT year_mon FOREIGN KEY (YEAR_ID)
   REFERENCES YEAR ON DELETE NO ACTION;
ALTER TABLE SALE ADD CONSTRAINT mon_sale FOREIGN KEY (YEAR_ID,MONTH_ID)
   REFERENCES MONTH ON DELETE NO ACTION;
ALTER TABLE SALE ADD CONSTRAINT store_sale FOREIGN KEY (STORE_ID)
   REFERENCES STORE ON DELETE NO ACTION;
ALTER TABLE SALE ADD CONSTRAINT prod_sale FOREIGN KEY (PRODUCT_ID)
   REFERENCES PRODUCT ON DELETE NO ACTION;
ALTER TABLE STORE ADD CONSTRAINT reg_store FOREIGN KEY (REGION_ID)
   REFERENCES REGION ON DELETE NO ACTION;
ALTER TABLE PRODUCT ADD CONSTRAINT cat_prod FOREIGN KEY (CATEGORY_ID)
   REFERENCES CATEGORY ON DELETE NO ACTION;
ALTER TABLE SALE ADD CONSTRAINT reg_sale FOREIGN KEY (REGION_ID)
   REFERENCES REGION ON DELETE NO ACTION;

CREATE INDEX xProductName ON PRODUCT (PRODUCT_NAME);
CREATE INDEX xsale_store ON SALE (STORE_ID);
CREATE INDEX xsale_reg ON SALE (REGION_ID);
CREATE INDEX xsale_prod ON SALE (PRODUCT_ID);
CREATE INDEX xStoreName ON STORE (STORE_NAME);

Back to XTG Data Modeller          


  Copyright © 1999-2008 XTG Systems, s.r.o.