Scripting: stored procedure dependencies not taken into account

[reiniero]

When scripting the FB 2.5 Employee database, dependency order between stored procedures is not followed; instead SPs are created alphabetically

Stored procedure section of script demonstrating the problem: procedure ALL_LANGS needs procedure show_langs which has not been created yet at that time.

-- Stored Procedures

SET TERM ^ ; CREATE Procedure ADD_EMP_PROJ ( EMP_NO SMALLINT, PROJ_ID CHAR(5) )

AS BEGIN BEGIN INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id); WHEN SQLCODE -530 DO EXCEPTION unknown_emp_id; END SUSPEND; END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure ALL_LANGS RETURNS ( CODE VARCHAR(5), GRADE VARCHAR(5), COUNTRY VARCHAR(15), LANG VARCHAR(15) )

AS BEGIN FOR SELECT job_code, job_grade, job_country FROM job INTO :code, :grade, :country

DO
BEGIN
    FOR SELECT languages FROM show_langs 
        (:code, :grade, :country) INTO :lang DO
        SUSPEND;
    /* Put nice separators between rows */
    code = '=====';
    grade = '=====';
    country = '===============';
    lang = '==============';
    SUSPEND;
END
END

^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure DELETE_EMPLOYEE ( EMP_NUM INTEGER )

AS DECLARE VARIABLE any_sales INTEGER; BEGIN any_sales = 0;

/*
 *  If there are any sales records referencing this employee,
 *  can't delete the employee until the sales are re-assigned
 *  to another employee or changed to NULL.
 */
SELECT count(po_number)
FROM sales
WHERE sales_rep = :emp_num
INTO :any_sales;

IF (any_sales > 0) THEN
BEGIN
    EXCEPTION reassign_sales;
    SUSPEND;
END

/*
 *  If the employee is a manager, update the department.
 */
UPDATE department
SET mngr_no = NULL
WHERE mngr_no = :emp_num;

/*
 *  If the employee is a project leader, update project.
 */
UPDATE project
SET team_leader = NULL
WHERE team_leader = :emp_num;

/*
 *  Delete the employee from any projects.
 */
DELETE FROM employee_project
WHERE emp_no = :emp_num;

/*
 *  Delete old salary records.
 */
DELETE FROM salary_history
WHERE emp_no = :emp_num;

/*
 *  Delete the employee.
 */
DELETE FROM employee
WHERE emp_no = :emp_num;

SUSPEND;

END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure DEPT_BUDGET ( DNO CHAR(3) )

RETURNS ( TOT Decimal(18,2) )

AS DECLARE VARIABLE sumb DECIMAL(12, 2); DECLARE VARIABLE rdno CHAR(3); DECLARE VARIABLE cnt INTEGER; BEGIN tot = 0;

SELECT budget FROM department WHERE dept_no = :dno INTO :tot;

SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;

IF (cnt = 0) THEN
    SUSPEND;

FOR SELECT dept_no
    FROM department
    WHERE head_dept = :dno
    INTO :rdno
DO
    BEGIN
        EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;
        tot = tot + sumb;
    END

SUSPEND;

END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure GET_EMP_PROJ ( EMP_NO SMALLINT )

RETURNS ( PROJ_ID CHAR(5) )

AS BEGIN FOR SELECT proj_id FROM employee_project WHERE emp_no = :emp_no INTO :proj_id DO SUSPEND; END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure MAIL_LABEL ( CUST_NO INTEGER )

RETURNS ( LINE1 CHAR(40), LINE2 CHAR(40), LINE3 CHAR(40), LINE4 CHAR(40), LINE5 CHAR(40), LINE6 CHAR(40) )

AS DECLARE VARIABLE customer VARCHAR(25); DECLARE VARIABLE first_name VARCHAR(15); DECLARE VARIABLE last_name VARCHAR(20); DECLARE VARIABLE addr1 VARCHAR(30); DECLARE VARIABLE addr2 VARCHAR(30); DECLARE VARIABLE city VARCHAR(25); DECLARE VARIABLE state VARCHAR(15); DECLARE VARIABLE country VARCHAR(15); DECLARE VARIABLE postcode VARCHAR(12); DECLARE VARIABLE cnt INTEGER; BEGIN line1 = ''; line2 = ''; line3 = ''; line4 = ''; line5 = ''; line6 = '';

SELECT customer, contact_first, contact_last, address_line1,
    address_line2, city, state_province, country, postal_code
FROM CUSTOMER
WHERE cust_no = :cust_no
INTO :customer, :first_name, :last_name, :addr1, :addr2,
    :city, :state, :country, :postcode;

IF (customer IS NOT NULL) THEN
    line1 = customer;
IF (first_name IS NOT NULL) THEN
    line2 = first_name || ' ' || last_name;
ELSE
    line2 = last_name;
IF (addr1 IS NOT NULL) THEN
    line3 = addr1;
IF (addr2 IS NOT NULL) THEN
    line4 = addr2;

IF (country = 'USA') THEN
BEGIN
    IF (city IS NOT NULL) THEN
        line5 = city || ', ' || state || '  ' || postcode;
    ELSE
        line5 = state || '  ' || postcode;
END
ELSE
BEGIN
    IF (city IS NOT NULL) THEN
        line5 = city || ', ' || state;
    ELSE
        line5 = state;
    line6 = country || '    ' || postcode;
END

SUSPEND;

END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure ORG_CHART RETURNS ( HEAD_DEPT CHAR(25), DEPARTMENT CHAR(25), MNGR_NAME CHAR(20), TITLE CHAR(5), EMP_CNT INTEGER )

AS DECLARE VARIABLE mngr_no INTEGER; DECLARE VARIABLE dno CHAR(3); BEGIN FOR SELECT h.department, d.department, d.mngr_no, d.dept_no FROM department d LEFT OUTER JOIN department h ON d.head_dept = h.dept_no ORDER BY d.dept_no INTO :head_dept, :department, :mngr_no, :dno DO BEGIN IF (:mngr_no IS NULL) THEN BEGIN mngr_name = '--TBH--'; title = ''; END

    ELSE
        SELECT full_name, job_code
        FROM employee
        WHERE emp_no = :mngr_no
        INTO :mngr_name, :title;

    SELECT COUNT(emp_no)
    FROM employee
    WHERE dept_no = :dno
    INTO :emp_cnt;

    SUSPEND;
END

END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure SHIP_ORDER ( PO_NUM CHAR(8) )

AS DECLARE VARIABLE ord_stat CHAR(7); DECLARE VARIABLE hold_stat CHAR(1); DECLARE VARIABLE cust_no INTEGER; DECLARE VARIABLE any_po CHAR(8); BEGIN SELECT s.order_status, c.on_hold, c.cust_no FROM sales s, customer c WHERE po_number = :po_num AND s.cust_no = c.cust_no INTO :ord_stat, :hold_stat, :cust_no;

/* This purchase order has been already shipped. */
IF (ord_stat = 'shipped') THEN
BEGIN
    EXCEPTION order_already_shipped;
    SUSPEND;
END

/*  Customer is on hold. */
ELSE IF (hold_stat = '*') THEN
BEGIN
    EXCEPTION customer_on_hold;
    SUSPEND;
END

/*
 *  If there is an unpaid balance on orders shipped over 2 months ago,
 *  put the customer on hold.
 */
FOR SELECT po_number
    FROM sales
    WHERE cust_no = :cust_no
    AND order_status = 'shipped'
    AND paid = 'n'
    AND ship_date < CAST('NOW' AS TIMESTAMP) - 60
    INTO :any_po
DO
BEGIN
    EXCEPTION customer_check;

    UPDATE customer
    SET on_hold = '*'
    WHERE cust_no = :cust_no;

    SUSPEND;
END

/*
 *  Ship the order.
 */
UPDATE sales
SET order_status = 'shipped', ship_date = 'NOW'
WHERE po_number = :po_num;

SUSPEND;

END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure SHOW_LANGS ( CODE VARCHAR(5), GRADE SMALLINT, CTY VARCHAR(15) )

RETURNS ( LANGUAGES VARCHAR(15) )

AS DECLARE VARIABLE i INTEGER; BEGIN i = 1; WHILE (i <= 5) DO BEGIN SELECT language_req[:i] FROM joB WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty) AND (language_req IS NOT NULL)) INTO :languages; IF (languages = ' ') THEN /* Prints 'NULL' instead of blanks */ languages = 'NULL';
i = i +1; SUSPEND; END END ^ SET TERM ; ^

SET TERM ^ ; CREATE Procedure SUB_TOT_BUDGET ( HEAD_DEPT CHAR(3) )

RETURNS ( TOT_BUDGET Decimal(18,2) , AVG_BUDGET Decimal(18,2) , MIN_BUDGET Decimal(18,2) , MAX_BUDGET Decimal(18,2) )

AS BEGIN SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget) FROM department WHERE head_dept = :head_dept INTO :tot_budget, :avg_budget, :min_budget, :max_budget; SUSPEND; END ^ SET TERM ; ^

/* Views */

CREATE VIEW "PHONE_LIST" (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO) AS SELECT emp_no, first_name, last_name, phone_ext, location, phone_no FROM employee, department WHERE employee.dept_no = department.dept_no ;

-- Triggers

SET TERM ^ ; Create Trigger POST_NEW_ORDER for SALES ACTIVE After Insert Position 0 AS BEGIN POST_EVENT 'new_order'; END ^ SET TERM ; ^

SET TERM ^ ; Create Trigger SAVE_SALARY_CHANGE for EMPLOYEE ACTIVE After Update Position 0 AS BEGIN IF (old.salary <> new.salary) THEN INSERT INTO salary_history (emp_no, change_date, updater_id, old_salary, percent_change) VALUES ( old.emp_no, 'NOW', user, old.salary, (new.salary - old.salary) * 100 / old.salary); END ^ SET TERM ; ^

SET TERM ^ ; Create Trigger SET_CUST_NO for CUSTOMER ACTIVE Before Insert Position 0 AS BEGIN if (new.cust_no is null) then new.cust_no = gen_id(cust_no_gen, 1); END ^ SET TERM ; ^

SET TERM ^ ; Create Trigger SET_EMP_NO for EMPLOYEE ACTIVE Before Insert Position 0 AS BEGIN if (new.emp_no is null) then new.emp_no = gen_id(emp_no_gen, 1); END ^ SET TERM ; ^

[motaz]

I have noticed that FlameRobin is generating empty stored procedures when scripting database to overcome this issue, then at last update them by the actual procedure body. Actually I haven't used FireBird stored procedures, and I don't like to use stored procedures in my applications, so that I can't do any help in this area

[reiniero]

On 01/05/2014 20:00, Motaz Abdel Azeem wrote:

I have noticed that FlameRobin is generating empty stored procedures when scripting database to overcome this issue, then at last update them by the actual procedure body. Yes, and FlameRobin's support for dependencies still isn't ok (at least their views still don't sort correctly).

Actually I haven't used FireBird stored procedures, and I don't like to use stored procedures in my applications, so that I can't do any help in this area No problem, I'll probably figure it out ;)

[motaz]

Strored procedures are Evil :) http://c2.com/cgi/wiki?StoredProceduresAreEvil

[reiniero]

On 01/05/2014 20:13, Motaz Abdel Azeem wrote:

Strored procedures are Evil: http://c2.com/cgi/wiki?StoredProceduresAreEvil

Regardless of my personal opinion about stored procedures (which varies between love and hate, depending on the lunar cycle) ;) it would be nice if TurboBird could at least properly script & restore the standard sample database provided by Firebird, don't you think ;)

That said, I suspect a lot of the dependency trouble I'm seeing in SPs will also pop up in views... which you perhaps also don't use (but I do) ;)

[motaz]

Yes, you are right, we should be neutral, and let the user decide. for views, yes I didn't use also, but I have nothing against it this time :) one of "Views" inventors is the president of my university (Sudan university) he is a very famous here in Sudan.

Actually I have concentrated on things that I need in FireBird in my various commercial projects, and I'm almost done with it, now it covers most of my needs, and it is time to other developers to participate, so that you have appear just in right time.

[reiniero]

Ok ;) Stored procedures sorting internally (i.e. stored procs depending on other stored procs) has been committed in e4c666421c3ed8e80a5bf9aa23b546d18512e25a. Future enhancement could be splitting out generating stored proc header and body as motaz indicated, and putting views in between (which may depend on stored procs). FYI, views sorting (internally) has been implemented in commit 19f5e8a3ba3eb33b493a0df7f4149197b28e7146

Will reopen this bug (or another one) if further sorting/splitting out needs to occur.