Generování SQL scriptu

Zpět na stránku XTG Data Modeller          

Syntaxe SQL scriptu závisí na vybraném typu databázového serveru
(InterBase, MySQL, Oracle, Microsoft SQL Server, Centura SQLBase, ...)

- Před generováním můžete zatrhnout, co chcete generovat (script appendix, drop index, drop table, UDD, create table, primary key, table appendix, foreign key, on delete, create index)
- Můžete označit entity na ploše nebo vybrat v gridu
- Můžete si nadefinovat vsuvky do scriptu (na začátek a na konec scriptu)
- Můžete také definovat vsuvky pro každou tabulku (např. create generator, insert into table, další constraints, ...)

Ukázka vygenerovaného skriptu pro 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);

Zpět na stránku XTG Data Modeller