You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tuscany.apache.org by Willian Yabusame Maja <wi...@hotmail.com> on 2006/11/12 19:32:20 UTC
CompanyWeb SQL
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
Re: CompanyWeb SQL
Posted by Jim Marino <jm...@myromatours.com>.
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 <wi...@hotmail.com> 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: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org
Re: CompanyWeb SQL
Posted by Luciano Resende <lu...@gmail.com>.
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.getNamedCompany'
PARAMETER STYLE JAVA;
CREATE PROCEDURE GETALLCOMPANIES ()
LANGUAGE JAVA
EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getAllCompanies'
PARAMETER STYLE JAVA;
CREATE PROCEDURE GETCUSTOMERANDORDERS ()
LANGUAGE JAVA
EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.getCustomerAndOrders
'
PARAMETER STYLE JAVA;
CREATE PROCEDURE DELETECUSTOMER ()
LANGUAGE JAVA
EXTERNAL NAME '
org.apache.tuscany.das.rdb.test.framework.JavaStoredProcs.deleteCustomer'
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 <wi...@hotmail.com> 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
>
Re: CompanyWeb SQL
Posted by Brent Daniel <br...@gmail.com>.
You can find the "create table" statements for all of the tables used
in DAS testing, including the company table used by companyweb, in
org.apache.tuscany.das.rdb.test.framework.MySQLSetup.
Brent
On 11/12/06, Willian Yabusame Maja <wi...@hotmail.com> 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: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org