PC World 1998 December/PCWorld_1998-12_cd.iso/software/sybase/ASA/asa60.exe/data1.cab/jxmp_files/jdemo.sql

Text File | 1998-07-27 | 6KB | 284 lines

SET TEMPORARY OPTION ON_ERROR = 'PROMPT';


-------------------------------------------------
-- Install JDBC example classes
-------------------------------------------------
INSTALL JAVA NEW
FROM FILE '../jxmp/JDBCExamples.class'
go

-------------------------------------------------
-- The remainder of the script builds a
-- set of database tables for the sample
-- database, under the user ID jdba, that use
-- Java data types and hold much of the information
-- in the sample database tables owned by DBA
-------------------------------------------------

-- Add Java database administrator
-- and use that ID
GRANT CONNECT TO jdba
IDENTIFIED BY sql
go
GRANT dba TO jdba
go
SETUSER jdba
go

-------------------------------------------------
-- Install required Java classes
-------------------------------------------------
INSTALL JAVA NEW JAR 'asademo'
FROM FILE '../jxmp/asademo.jar'
go

-------------------------------------------------
-- Create tables
-------------------------------------------------
CREATE TABLE "jdba"."product"
(
    "id"    INTEGER NOT NULL,
    "JProd" asademo.Product NOT NULL,
    "name"  CHAR(15) COMPUTE ( JProd>>name ),
    PRIMARY KEY ("id")
)
go

CREATE TABLE "jdba"."contact"
(
    "id"    integer NOT NULL,
    "title" CHAR(2) NOT NULL,
    "JName" asademo.Name NOT NULL,
    "JContactInfo" asademo.ContactInfo NOT NULL,
    PRIMARY KEY ("id")
)
go

CREATE TABLE "jdba"."customer"
(
    "id"    integer NOT NULL,
    "company_name" CHAR(35) NOT NULL,
    "JName" asademo.Name NOT NULL,
    "JContactInfo" asademo.ContactInfo NOT NULL,
    PRIMARY KEY ("id")
)
go

CREATE TABLE "jdba".employee
(
    "emp_id" INTEGER NOT NULL,
    "JName" asademo.Name NOT NULL,
    "dept_id" INTEGER NOT NULL,
    "manager_id" INTEGER NOT NULL,
    "sex" CHAR(1) NOT NULL,
    "JEmpInfo" asademo.EmployeeInfo NOT NULL,
    PRIMARY KEY ("emp_id")
)
go

CREATE TABLE "jdba".sales_order
(
    "id" INTEGER NOT NULL,
    "cust_id" INTEGER NOT NULL,
    "sales_rep" INTEGER NOT NULL,
    "region" CHAR(7) NOT NULL,
    "order_date" DATE NOT NULL,
    PRIMARY KEY ("id")
)
go

CREATE TABLE "jdba".sales_order_items
(
    "id" INTEGER NOT NULL,
    "line_id" INTEGER NOT NULL,
    JItem asademo.OrderItem NOT NULL,
    "prod_id" INTEGER COMPUTE( JItem>>prod_id ),
    PRIMARY KEY ( "id", "line_id")
)

-------------------------------------------------
--   Add Data
-------------------------------------------------
INSERT INTO product( id, JProd )
SELECT id,
    NEW asademo.Product( color,
        description,
        name,
        quantity,
        size,
        unit_price )
FROM dba.product
WHERE NOT ( LOCATE ( name, 'cap' ) > 0
OR LOCATE ( name, 'visor' ) > 0 )
go
COMMIT
go

INSERT INTO product( id, JProd )
SELECT id,
    NEW asademo.Hat( color,
        description,
        name,
        quantity,
        size,
        unit_price )
FROM dba.product
WHERE ( LOCATE ( name, 'cap' ) > 0
OR LOCATE ( name, 'visor' ) > 0 )
go
COMMIT
go

INSERT INTO contact
SELECT id,
    title,
    NEW asademo.Name(
        first_name,
        last_name ),
    NEW asademo.ContactInfo (
        phone,
        fax,
        zip,
        street,
        city,
        state )
FROM dba.contact
go
COMMIT
go

INSERT INTO customer
SELECT id,
    company_name,
    NEW asademo.Name( fname, lname ),
    NEW asademo.ContactInfo (
        phone,
        zip,
        address,
        city,
        state )
FROM dba.customer
go
COMMIT
go

INSERT INTO employee
SELECT     emp_id,
    NEW asademo.Name( emp_fname, emp_lname ),
    dept_id,
    manager_id,
    sex,
    NEW asademo.EmployeeInfo (
        phone,
        zip_code,
        street,
        city,
        state,
        ss_number,
        salary,
        start_date,
        termination_date,
        birth_date,
        bene_health_ins,
        bene_life_ins,
        bene_day_care
 )
FROM dba.employee
go
COMMIT
go

INSERT INTO "jdba".sales_order
SELECT     id,
    cust_id,
    sales_rep,
    region,
    order_date
FROM "dba".sales_order
go
COMMIT
go

INSERT INTO "jdba".sales_order_items( id, line_id, JItem)
SELECT     id,
    line_id,
    NEW asademo.OrderItem(
        prod_id,
        quantity,
        ship_date
    )
FROM "dba".sales_order_items
go
COMMIT
go


// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
//     Add foreign keys definitions
// %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


ALTER TABLE "jdba"."sales_order"
ADD FOREIGN KEY "ky_so_employee_id" ("sales_rep")
REFERENCES "jdba"."employee" ("emp_id");

ALTER TABLE "jdba"."sales_order"
ADD FOREIGN KEY "ky_so_customer" ("cust_id")
REFERENCES "jdba"."customer" ("id");

CREATE INDEX "ix_sales_cust" ON "jdba"."sales_order"
(
    "cust_id" ASC
);

ALTER TABLE "jdba"."sales_order_items"
ADD FOREIGN KEY "ky_prod_id" ("prod_id")
REFERENCES "jdba"."product" ("id");

ALTER TABLE "jdba"."sales_order_items"
ADD FOREIGN KEY "id_fk" ("id")
REFERENCES "jdba"."sales_order" ("id") on delete cascade;

CREATE INDEX "ix_item_prod" ON "jdba"."sales_order_items"
(
    "prod_id" ASC
);

CREATE INDEX "ix_prod_name" ON "jdba"."product"
(
    "name" ASC
);
go

/* From here on commented out
CREATE INDEX "ix_cust_name" ON "jdba"."customer"
(
    "lname" ASC,
    "fname" ASC
);


CREATE INDEX "ix_prod_desc" ON "jdba"."product"
(
    "description" ASC
);
CREATE INDEX "ix_prod_size" ON "jdba"."product"
(
    "size" ASC);
CREATE INDEX "ix_prod_color" ON "jdba"."product"
(
    "color" ASC
);


ALTER TABLE "jdba"."department"
ADD FOREIGN KEY "ky_dept_head" ("dept_head_id")
REFERENCES "jdba"."employee" ("emp_id") on delete set null;

ALTER TABLE "jdba"."employee"
ADD FOREIGN KEY "ky_dept_id" ("dept_id")
REFERENCES "jdba"."department" ("dept_id");
commit work;
*/