How to generate SQL DDL

General discussions about code engineering

Moderator: Moderators

How to generate SQL DDL

Postby ignas.gaubas@gmail.com » Mon Nov 20, 2017 1:19 pm

Hello,
I can not generate simple SQL code from my DB schema model. I suspect it may be problem that I am using types such varchar(50) varchar(30) etc... but I can not avoid them. Only similar types i find in MagicDraw are char... But i need at least char(30), char(50) or varchar(...). Something closer to usual SQL system. Particularly I am going to use MySQL. When I try generating DDL i get empty script file where it should be filled with SQL code.
I attached screenshots to show what it looks like. Hope you can solve this problem.
You do not have the required permissions to view the files attached to this post.
ignas.gaubas@gmail.com
Forum Newbie
Forum Newbie
 
Posts: 1
Posts Rating:0
Joined: Mon Nov 20, 2017 1:11 pm

Re: How to generate SQL DDL

Postby gedkvi » Tue Nov 21, 2017 1:49 am

Hello,

The standard SQL types are defined in profile ANSI SQL Type Library. Every time you need a standard type, please use it from that profile. Please remove the ones that you created. The generator will not understand them correctly.
Column Specification dialog should look something like this:
1.png

2.png


I highly recommend taking a look at sample project in <product.install.dir>\samples\Data Modeling\SQL Modeling.mdzip.
Also, I've attached a project for you to look into. Its an excerpt of the sample project that you can reverse without doing anything. The result is this:

Code: Select all
#@(#) script.ddl

DROP VIEW IF EXISTS SalesReport;
DROP TABLE IF EXISTS Purchase;
DROP TABLE IF EXISTS Product;
DROP TABLE IF EXISTS QuotaFulfilment;
DROP TABLE IF EXISTS Salesman;

CREATE TABLE Salesman
(
   id integer PRIMARY KEY,
   name varchar (120),
   quota numeric (10, 2)
);

CREATE TABLE QuotaFulfilment
(
   year smallint PRIMARY KEY,
   month smallint PRIMARY KEY,
   fk_Salesman integer,
   quota numeric (10, 2),
   sales numeric (10, 2)
);

CREATE TABLE Product
(
   id integer PRIMARY KEY,
   unitPrice numeric (10, 2)
);

CREATE TABLE Purchase
(
   id PRIMARY KEY,
   fk_Salesman integer,
   fk_Product integer,
   quantity integer,
   priceWithDiscount numeric (10, 2),
   totalPrice numeric (10, 2),
   year smallint,
   month smallint,
   day smallint,
   CONSTRAINT salesman FOREIGN KEY(fk_Salesman) REFERENCES Salesman (id),
   CONSTRAINT product FOREIGN KEY(fk_Product) REFERENCES Product (id)
);


Sincerely,
No Magic Customer Support
You do not have the required permissions to view the files attached to this post.
gedkvi
Customer Support
Customer Support
 
Posts: 55
Posts Rating:7
Joined: Wed Sep 16, 2015 11:25 am


Return to Code engineering

Who is online

Users browsing this forum: No registered users and 0 guests