#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    3
    Rep Power
    0

    How Can I Change These Database From Postgresql To Mysql? Pliz Help!


    bank_sql_functions.sql


    --FUNCTIONS TO USE
    --PARAM updateAccount(ACCOUNT,AMOUNT)
    CREATE FUNCTION updateAccount(INTEGER,NUMERIC) RETURNS INTEGER AS '
    BEGIN
    IF $2 IS NULL THEN
    RAISE EXCEPTION ''% cannot have null amount'', $2;
    END IF;
    IF ($2 < 0) THEN
    IF (SELECT true
    FROM account
    WHERE account=$2
    AND amount<abs($2))
    THEN
    RAISE EXCEPTION ''% not enough money'', $2;
    END IF;
    END IF;
    UPDATE account SET amount=amount+$2 WHERE account=$1;
    RETURN 0;
    END;' LANGUAGE plpgsql;

    -- PARAM payBill(AMOUNT,ACCOUNT,PAYEE,CUSTOMER,DATE)
    CREATE FUNCTION payBill(NUMERIC,INTEGER,INTEGER,INTEGER,DATE) RETURNS INTEGER AS '
    DECLARE v_desc VARCHAR;
    DECLARE v_payee VARCHAR;
    DECLARE v_remaining NUMERIC;
    DECLARE v_amount NUMERIC;
    BEGIN
    IF ($1 < 0 ) THEN
    RAISE EXCEPTION ''% cannot have negative amount'', $1;
    END IF;

    v_amount := -1 * $1;
    IF v_overdraft IS NULL THEN
    v_overdraft := 0;
    END IF;

    SELECT (amount-abs($1)) INTO v_remaining FROM account WHERE account=$2;
    SELECT payee INTO v_payee FROM payee WHERE pid=$3;

    v_desc := ''Bill payment - '' || v_payee;

    IF (v_remaining < 0) THEN
    RAISE EXCEPTION ''Insuffient funds, % does not exist in your account'', $1;
    END IF;

    INSERT INTO bill(pid,date,account,cid,amount) VALUES($3,current_date,$2,$4,$1);

    INSERT INTO activity(descr,account,cid,date,rem_bal,amount) VALUES(v_desc,$2,$4,$5,v_remaining,v_amount);

    PERFORM updateaccount($2,v_amount);
    RETURN 0;
    END;' LANGUAGE plpgsql;

    -- PARAM (AMOUNT,ACCOUNTFROM,ACCOUNTTO,CUSTOMER)
    CREATE FUNCTION transferMoney(NUMERIC,INTEGER,INTEGER,INTEGER) RETURNS INTEGER AS '
    DECLARE v_desc VARCHAR;
    DECLARE v_remaining NUMERIC;
    DECLARE v_amount NUMERIC;
    BEGIN
    IF ($1 < 0 ) THEN
    RAISE EXCEPTION ''% cannot have negative amount'', $1;
    END IF;

    -- INSERT MONEY TO THE TO
    v_amount := -1 * $1;
    SELECT (amount-abs($1)) INTO v_remaining FROM account WHERE account=$2;
    v_desc := ''Transfer to - '' || to_char($3,'000000000000');

    IF (v_remaining < 0) THEN
    RAISE EXCEPTION ''Insuffient funds, % does not exist in your account'', $1;
    END IF;

    INSERT INTO activity(descr,account,cid,date,rem_bal,amount) VALUES(v_desc,$2,$4,current_date,v_remaining,v_amount);
    PERFORM updateaccount($2,v_amount);

    -- INSERT MONEY TO THE FROM
    SELECT (amount+abs($1)) INTO v_remaining FROM account WHERE account=$3;

    IF (v_remaining < 0) THEN
    RAISE EXCEPTION ''Insuffient funds, % does not exist in your account'', $1;
    END IF;

    v_desc := ''Transfer from - '' || to_char($2,'000000000000');

    INSERT INTO activity(descr,account,cid,date,rem_bal,amount) VALUES(v_desc,$3,$4,current_date,v_remaining,$1);
    PERFORM updateaccount($3,$1);

    RETURN 0;
    END;' LANGUAGE plpgsql;


    bank_sql_populate.sql



    --INSERTS
    --ACT1
    INSERT INTO customer VALUES(1,'111','Mansour','Mohamed','1339 Meadowlands Drive','Ottawa','6132554503','m0.interactive@gmail.com','Evening');
    INSERT INTO customer VALUES(2,'111','Abbadi','Laith','Candaa','Ottawa','6132554503','Laith@hotmail.com','Morning');
    INSERT INTO customer VALUES(3,'111','Tadesse','Yohannese','1339 Meadowlands Drive','Toronto','6132554503','m0.interactive@gmail.com','Evening');
    INSERT INTO customer VALUES(4,'111','Hamdan','Tima','Some neighborhoos','Ottawa','6132554503','tima@gmail.com','Morning');

    INSERT INTO account VALUES(1,104.5);
    INSERT INTO account VALUES(2,204.5);
    INSERT INTO account VALUES(3,176.5);
    INSERT INTO account VALUES(4,304.5);
    INSERT INTO account VALUES(5,1134.5);
    INSERT INTO account VALUES(6,435.5);
    INSERT INTO account VALUES(7,32.5);
    INSERT INTO account VALUES(8,162.5);
    INSERT INTO account VALUES(9,128.5);

    INSERT INTO savings VALUES (1,32);
    INSERT INTO savings VALUES (2,12);
    INSERT INTO savings VALUES (3,42);
    INSERT INTO savings VALUES (4,62);
    INSERT INTO savings VALUES (5,12);
    INSERT INTO savings VALUES (6,33);
    INSERT INTO savings VALUES (7,3);
    INSERT INTO savings VALUES (8,5.1);
    INSERT INTO savings VALUES (9,32);

    INSERT INTO holds VALUES (1,1,1);
    INSERT INTO holds VALUES (1,2,1);
    INSERT INTO holds VALUES (1,3,1);
    INSERT INTO holds VALUES (2,4,1);
    INSERT INTO holds VALUES (2,5,1);
    INSERT INTO holds VALUES (2,6,1);
    INSERT INTO holds VALUES (3,7,1);
    INSERT INTO holds VALUES (4,8,1);
    INSERT INTO holds VALUES (4,9,1);


    --BILL PAYEES
    INSERT INTO payee(payee) VALUES('Rogers Cable');
    INSERT INTO payee(payee) VALUES('Rogers Cell');
    INSERT INTO payee(payee) VALUES('Bell Sympatico');
    INSERT INTO payee(payee) VALUES('Environment Canada');
    INSERT INTO payee(payee) VALUES('Xbox Live');
    INSERT INTO payee(payee) VALUES('Fido');
    INSERT INTO payee(payee) VALUES('Minto Rent');
    INSERT INTO payee(payee) VALUES('Blockbuster');
    INSERT INTO payee(payee) VALUES('Futureshop');
    INSERT INTO payee(payee) VALUES('Godaddy Service IP');


    INSERT INTO employee VALUES(1,'password','Yohannes','Tadesse','613-834-7487',current_date);
    INSERT INTO employee VALUES(2,'123','Iluju','Kiringa','562-5800',current_date);

    INSERT INTO per_banker VALUES(1);
    INSERT INTO per_banker VALUES(2);

    INSERT INTO acc_sup VALUES(1,1,2);
    INSERT INTO acc_sup VALUES(2,1,2);
    INSERT INTO acc_sup VALUES(3,1,1);
    INSERT INTO acc_sup VALUES(4,2,2);
    INSERT INTO acc_sup VALUES(5,2,2);
    INSERT INTO acc_sup VALUES(6,2,1);
    INSERT INTO acc_sup VALUES(7,3,2);
    INSERT INTO acc_sup VALUES(8,4,2);
    INSERT INTO acc_sup VALUES(9,4,1);

    INSERT INTO account VALUES(10,1128.5);
    INSERT INTO checking VALUES (10,50);
    INSERT INTO holds VALUES (1,10,2);
    INSERT INTO acc_sup VALUES(10,1,1);


    bank_sql_tables.sql


    CREATE TABLE Loan (
    loanid SERIAL,
    amount NUMERIC,
    PRIMARY KEY(loanid)
    );

    CREATE TABLE Branch (
    name VARCHAR(30),
    city VARCHAR(30),
    asset NUMERIC,
    PRIMARY KEY(name)
    );

    CREATE TABLE Account (
    account SERIAL,
    amount NUMERIC,
    PRIMARY KEY(account)
    );

    CREATE TABLE Customer (
    cid SERIAL,
    pass VARCHAR(20),
    lname VARCHAR(20),
    fname VARCHAR(20),
    address VARCHAR(50),
    city VARCHAR(20),
    phone VARCHAR(20),
    email VARCHAR(50),
    best_time VARCHAR(20),
    PRIMARY KEY(cid)
    );

    CREATE TABLE Employee (
    eid SERIAL,
    pass VARCHAR(20),
    fname VARCHAR(20),
    lname VARCHAR(20),
    phone VARCHAR(20),
    sdate DATE,
    PRIMARY KEY(eid)
    );



    CREATE TABLE Payee (
    pid SERIAL,
    payee VARCHAR(50),
    PRIMARY KEY(pid)
    );

    CREATE TABLE Mgr (
    supervisee_eid INTEGER,
    supervisor_eid INTEGER,
    PRIMARY KEY (supervisee_eid, supervisor_eid),
    FOREIGN KEY (supervisor_eid) REFERENCES Employee(eid),
    FOREIGN KEY (supervisee_eid) REFERENCES Employee(eid)
    );

    CREATE TABLE Activity (
    activity_id SERIAL,
    descr VARCHAR(100),
    account INTEGER,
    cid INTEGER,
    date DATE,
    rem_bal REAL,
    amount NUMERIC,
    PRIMARY KEY(activity_id),
    FOREIGN KEY(account) REFERENCES Account(account),
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    );

    CREATE TABLE Bill (
    bid SERIAL,
    pid INTEGER,
    date DATE,
    account INTEGER,
    cid INTEGER,
    amount NUMERIC,
    PRIMARY KEY(bid),
    FOREIGN KEY(account) REFERENCES Account(account),
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    ON DELETE CASCADE
    );


    CREATE TABLE Customer_Payee (
    cid INTEGER,
    pid INTEGER,
    cpdate DATE,
    PRIMARY KEY(cid,pid),
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    ON DELETE CASCADE,
    FOREIGN KEY(pid) REFERENCES Payee(pid)
    );

    CREATE TABLE Holds (
    cid INTEGER,
    account INTEGER,
    acttype SMALLINT DEFAULT 0,
    PRIMARY KEY(cid,account),
    FOREIGN KEY(account) REFERENCES Account(account)
    ON DELETE CASCADE,
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    ON DELETE CASCADE
    );

    CREATE TABLE Per_Banker (
    eid INTEGER,
    PRIMARY KEY(eid),
    FOREIGN KEY(eid) REFERENCES Employee(eid)
    ON DELETE CASCADE
    );

    CREATE TABLE Payments (
    pid INTEGER,
    loanid INTEGER,
    date DATE,
    amount NUMERIC,
    PRIMARY KEY(pid,loanid),
    FOREIGN KEY(loanid) REFERENCES Loan(loanid)
    ON DELETE CASCADE
    );
    CREATE TABLE Loan_Officer (
    eid INTEGER,
    PRIMARY KEY(eid),
    FOREIGN KEY(eid) REFERENCES Employee(eid)
    ON DELETE CASCADE
    );

    CREATE TABLE Acc_Sup (
    account INTEGER,
    cid INTEGER,
    eid INTEGER,
    PRIMARY KEY(account,cid,eid),
    FOREIGN KEY(account) REFERENCES Account(account)
    ON DELETE CASCADE,
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    ON DELETE CASCADE,
    FOREIGN KEY(eid) REFERENCES Per_Banker(eid)
    );

    CREATE TABLE Loan_Sup (
    loanid INTEGER,
    cid INTEGER,
    eid INTEGER,
    PRIMARY KEY(loanid,cid,eid),
    FOREIGN KEY(loanid) REFERENCES Loan(loanid),
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    ON DELETE CASCADE,
    FOREIGN KEY(eid) REFERENCES Loan_Officer(eid)
    );

    CREATE TABLE Visit (
    date DATE,
    account INTEGER,
    cid INTEGER,
    PRIMARY KEY(date,account,cid),
    FOREIGN KEY(account) REFERENCES Account(account),
    FOREIGN KEY(cid) REFERENCES Customer(cid)
    );

    CREATE TABLE Offers (
    name VARCHAR(20),
    loanid INTEGER,
    PRIMARY KEY(name,loanid),
    FOREIGN KEY(name) REFERENCES Branch(name),
    FOREIGN KEY(loanid) REFERENCES Loan(loanid)
    );

    CREATE TABLE Hosts (
    name VARCHAR(20),
    account INTEGER,
    PRIMARY KEY(name,account),
    FOREIGN KEY(name) REFERENCES Branch(name),
    FOREIGN KEY(account) REFERENCES Account(account)
    );


    CREATE TABLE Savings (
    account INTEGER,
    rate REAL,
    PRIMARY KEY(account),
    FOREIGN KEY(account) REFERENCES Account(account)
    ON DELETE CASCADE
    );

    CREATE TABLE Checking (
    account INTEGER,
    overdraft NUMERIC,
    PRIMARY KEY(account),
    FOREIGN KEY(account) REFERENCES Account(account)
    ON DELETE CASCADE
    );



    PLIZ HELP
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Please post this in the MySQL forum, as such a transation requires knowledge of MySQL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo