Re: CompanyWeb SQL

Sun, 12 Nov 2006 12:53:21 -0800

I'm not sure if you need to use transactions (e.g. Connection.rollback ()) but if you do you will also need to set MySQL up to use InnoDB. After you create the tables, you may need to do ALTER TABLE, setting the type to InnoDB: 

ALTER TABLE SOME_TABLE  TYPE = InnoDB

Jim

On Nov 12, 2006, at 11:33 AM, Luciano Resende wrote:


Hi William
It's woul dbe really great to have a better generic way, not derby centric, 
to create and populate our DAS samples canned database (
http://issues.apache.org/jira/browse/TUSCANY-863), and I think that trying to use it in a different DB environmet would help to make this happen...
If you are using Eclipse as your IDE, there is a Database Explorer view that allows you to create a embedded derby connection that you can point to the companyweb derby db : X:\java\das\samples\companyweb\dastest... This also generate the SQL for creating the tables, stored procedures, etc... and I 
have attached the generated SQL below...
Note: You will need to update this, to make the creation of the index inside 
the create table command... and you will need to populate the db later
on....

To create the tables in MySQL, you will need something like :

  mysql -u root -p < setup.sql
Please let me know if this helps, and don't hesitate in asking questions... 
If you need help finishing the SQL, let me know...

<<<<< Companyweb SQL >>>>>

CREATE PROCEDURE GETNAMEDCOMPANY ()
   LANGUAGE JAVA
   EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getNamedComp any' 
   PARAMETER STYLE JAVA;

CREATE PROCEDURE GETALLCOMPANIES ()
   LANGUAGE JAVA
   EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCompan ies' 
   PARAMETER STYLE JAVA;

CREATE PROCEDURE GETCUSTOMERANDORDERS ()
   LANGUAGE JAVA
   EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getCustomerA ndOrders 
'
   PARAMETER STYLE JAVA;

CREATE PROCEDURE DELETECUSTOMER ()
   LANGUAGE JAVA
   EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.deleteCustom er' 
   PARAMETER STYLE JAVA;




CREATE TABLE STATES (
       ID INTEGER NOT NULL,
       NAME VARCHAR(2)
   );

CREATE TABLE ITEM (
       ID INTEGER NOT NULL,
       NAME VARCHAR(30)
   );

CREATE TABLE EMPLOYEE (
       ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY  (START WITH 1
,INCREMENT BY 9),
       NAME VARCHAR(30),
       SN VARCHAR(10),
       MANAGER SMALLINT,
       DEPARTMENTID INTEGER
   );

CREATE TABLE ORDERDETAILS (
       ORDERID INTEGER NOT NULL,
       PRODUCTID INTEGER NOT NULL,
       PRICE DOUBLE
   );

CREATE TABLE CITIES (
       ID INTEGER NOT NULL,
       NAME VARCHAR(50),
       STATE_ID INTEGER
   );

CREATE TABLE DEPARTMENT (
       ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY  (START WITH 1
,INCREMENT BY 79),
       NAME VARCHAR(30),
       LOCATION VARCHAR(30),
       NUMBER VARCHAR(10),
       COMPANYID INTEGER,
       EOTM INTEGER
   );

CREATE TABLE PART (
       ID INTEGER NOT NULL,
       NAME VARCHAR(50),
       QUANTITY INTEGER,
       PARENT_ID INTEGER
   );

CREATE TABLE CUSTOMER (
       ID INTEGER NOT NULL,
       LASTNAME VARCHAR(30) DEFAULT 'Garfugengheist',
       ADDRESS VARCHAR(30)
   );

CREATE TABLE ANORDER (
       ID INTEGER NOT NULL,
       PRODUCT VARCHAR(30),
       QUANTITY INTEGER,
       CUSTOMER_ID INTEGER
   );

CREATE TABLE BOOK (
       ID INTEGER NOT NULL,
       NAME VARCHAR(50),
       AUTHOR VARCHAR(30),
       QUANTITY INTEGER,
       OCC INTEGER
   );

CREATE TABLE TYPETEST (
       ID INTEGER NOT NULL,
       ATIMESTAMP TIMESTAMP,
       ADECIMAL DECIMAL(9 , 2),
       AFLOAT DOUBLE
   );

CREATE TABLE COMPANY (
       ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY  (START WITH 1
,INCREMENT BY 54),
       NAME VARCHAR(30)
   );

CREATE UNIQUE INDEX SQL060217085532650 ON TYPETEST (ID ASC);

CREATE UNIQUE INDEX SQL060217085530980 ON COMPANY (ID ASC);

CREATE UNIQUE INDEX SQL060217085532010 ON BOOK (ID ASC);

CREATE UNIQUE INDEX SQL060217085530700 ON ITEM (ID ASC);

CREATE UNIQUE INDEX SQL060217085532360 ON PART (ID ASC);

CREATE UNIQUE INDEX SQL060217085531390 ON EMPLOYEE (ID ASC);

CREATE UNIQUE INDEX SQL060217085532910 ON STATES (ID ASC);

CREATE UNIQUE INDEX SQL060217085529620 ON CUSTOMER (ID ASC);

CREATE UNIQUE INDEX SQL060217085533290 ON CITIES (ID ASC);

CREATE UNIQUE INDEX SQL060217085531710 ON DEPARTMENT (ID ASC);

CREATE INDEX SQL060217085533291 ON CITIES (STATE_ID ASC);

CREATE UNIQUE INDEX SQL060217085530020 ON ANORDER (ID ASC);

CREATE UNIQUE INDEX SQL060217085530330 ON ORDERDETAILS (ORDERID ASC,
PRODUCTID ASC);

ALTER TABLE ORDERDETAILS ADD CONSTRAINT SQL060217085530330 PRIMARY KEY
(ORDERID, PRODUCTID);

ALTER TABLE BOOK ADD CONSTRAINT SQL060217085532010 PRIMARY KEY (ID);

ALTER TABLE STATES ADD CONSTRAINT SQL060217085532910 PRIMARY KEY (ID);
ALTER TABLE DEPARTMENT ADD CONSTRAINT SQL060217085531710 PRIMARY KEY (ID);
ALTER TABLE EMPLOYEE ADD CONSTRAINT SQL060217085531390 PRIMARY KEY (ID);
ALTER TABLE ANORDER ADD CONSTRAINT SQL060217085530020 PRIMARY KEY (ID);
ALTER TABLE COMPANY ADD CONSTRAINT SQL060217085530980 PRIMARY KEY (ID);
ALTER TABLE CUSTOMER ADD CONSTRAINT SQL060217085529620 PRIMARY KEY (ID);
ALTER TABLE TYPETEST ADD CONSTRAINT SQL060217085532650 PRIMARY KEY (ID); 

ALTER TABLE PART ADD CONSTRAINT SQL060217085532360 PRIMARY KEY (ID);

ALTER TABLE CITIES ADD CONSTRAINT SQL060217085533290 PRIMARY KEY (ID);

ALTER TABLE ITEM ADD CONSTRAINT SQL060217085530700 PRIMARY KEY (ID);

ALTER TABLE CITIES ADD CONSTRAINT FK1 FOREIGN KEY (STATE_ID)
   REFERENCES STATES (ID);



On 11/12/06, Willian Yabusame Maja <[EMAIL PROTECTED]> wrote:


Hi!
I'm doing a basic "How To" and I'm using the CompanyWeb sample. But I would like to use Mysql instead of Derby. Does anyone has the tables Sql? 

Willian




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to