PowerConsole

  • You may use SQL statements in Python code. For example next function deactivates indices:

    >>> def deactivate(indices):
    ...   for index in indices:
    ...      alter index @index.name inactive;
    ...
    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> deactivate(db.indices)
    
  • You can get access to executed statements (cursor() object) from Python code. Next statement will execute and store the openned cursor() object in user namespace for further manipulation:

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> select * from country >> c;

    Now you can use the cursor stored under name “c” (see FDB documentation for details about cursor object):

    >>> for row in c:
    >>>   print row
    
  • You can use parametrized SQL statemets and feed values to them from any Python iterable (including open cursor() objects!):

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> rows = [('Utopia','none'),('Sirius','credits')]
    >>> insert into country (country,currency) values (?,?) << rows

Tip

All attached databases are also accessible through Python Database objects installed into user namespace. You may access them directly from your Python code or use them as parameters to many PowerConsole commands.

Using Python expressions in SQL

Here are some examples how to fill in a table name into SELECT statement:

select * from @string ;
select * from @module.member ;
select * from @object.member ;
select * from @functioncall(with, arguments) ;
select * from @{arbitrary + expression} ;
select * from @array[3] ;
select * from @dictionary['member'] ;

Using database objects in PowerConsole

  1. List names and owners of all user tables

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> list name,owner_name in db.tables
    name             owner_name
    ================ ==========
    COUNTRY          SYSDBA
    JOB              SYSDBA
    DEPARTMENT       SYSDBA
    EMPLOYEE         SYSDBA
    PROJECT          SYSDBA
    CUSTOMER         SYSDBA
    EMPLOYEE_PROJECT SYSDBA
    PROJ_DEPT_BUDGET SYSDBA
    SALARY_HISTORY   SYSDBA
    SALES            SYSDBA
    
  2. List all indices on JOB table

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> t = db.getTable('JOB')
    >>> list t.indices
    RDB$PRIMARY2 unique system index on JOB(JOB_CODE,JOB_GRADE,JOB_COUNTRY)
    RDB$FOREIGN3 system(FK) index on JOB(JOB_COUNTRY)
    MINSALX index on JOB(JOB_COUNTRY,MIN_SALARY)
    MAXSALX index on JOB(JOB_COUNTRY,MAX_SALARY)
    
  3. Because Firebird Pack installs object renderers to your PowerConsole environment, you may see enhanced output for these objects when they’re evaluated on command prompt:

    >>> t = db.getTable('JOB')
    >>> t
    Table JOB (129) owned by SYSDBA
    JOB_CODE                          (JOBCODE) VARCHAR(5) Not Null
                                      CHECK (VALUE > '99999')
    JOB_GRADE                         (JOBGRADE) SMALLINT Not Null
                                      CHECK (VALUE BETWEEN 0 AND 6)
    JOB_COUNTRY                       (COUNTRYNAME) VARCHAR(15) Not Null
    JOB_TITLE                         VARCHAR(25) Not Null
    MIN_SALARY                        (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
                                      CHECK (VALUE > 0)
    MAX_SALARY                        (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
                                      CHECK (VALUE > 0)
    JOB_REQUIREMENT                   BLOB segment 400, subtype TEXT Nullable
    LANGUAGE_REQ                      ARRAY OF [5] VARCHAR(15) Nullable
    
    Constraints:
    PRIMARY KEY INTEG_10:
    Index RDB$PRIMARY2(JOB_CODE,JOB_GRADE,JOB_COUNTRY)
    FOREIGN KEY INTEG_11:
    Index RDB$FOREIGN3(JOB_COUNTRY)
    References COUNTRY RDB$PRIMARY1(COUNTRY)
    CHECK INTEG_12:
    CHECK (min_salary < max_salary)
    
    Indices:
    RDB$PRIMARY2 unique system index on (JOB_CODE,JOB_GRADE,JOB_COUNTRY)
    RDB$FOREIGN3 system(FK) index on (JOB_COUNTRY)
    MINSALX index on (JOB_COUNTRY,MIN_SALARY)
    MAXSALX index on (JOB_COUNTRY,MAX_SALARY)
    
  4. List all triggers that post events

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> list name in (t for t in db.triggers if 'POST_EVENT' in t.source)
    name
    ==============
    POST_NEW_ORDER
    
  5. Show procedures that are not in another database

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> connect '/data/db/employee-copy.fdb' user 'sysdba' password 'masterkey' as db2
    Database /data/db/employee-copy.fdb as db2, user sysdba
    >>> ready db
    Main database set to db
    >>> list name in (p for p in db.procedures if db2.getProcedure(p.name) is None)
    name
    ==========
    MAIL_LABEL
    >>> list name in (p for p in db.procedures if not db2.getProcedure(p.name))
    name
    ==========
    MAIL_LABEL
    
  6. Find out what procedures differ in source code and then show the differences:

    >>> connect 'employee' user 'sysdba' password 'masterkey'
    Database employee as db, user sysdba
    >>> connect '/data/db/employee-copy.fdb' user 'sysdba' password 'masterkey' as db2
    Database /data/db/employee-copy.fdb as db2, user sysdba
    >>> ready db
    Main database set to db
    >>> common_proc = [p for p in db.procedures if db2.getProcedure(p.name)]
    >>> list name in common_proc
    name
    ===============
    GET_EMP_PROJ
    ADD_EMP_PROJ
    SUB_TOT_BUDGET
    DELETE_EMPLOYEE
    DEPT_BUDGET
    ORG_CHART
    SHIP_ORDER
    SHOW_LANGS
    ALL_LANGS
    >>> different = [p for p in common_proc if p.source != db2.getProcedure(p.name).source]
    >>> list name in different
    name
    ===========
    DEPT_BUDGET
    >>> params = zip(different,map(db2.getProcedure,(p.name for p in different)))
    >>> import difflib
    >>> list difflib.ndiff(params[0][0].source.split('\n'),params[0][1].source.split('\n'))
    -       DECLARE VARIABLE sumb DECIMAL(12, 2);
    ? -
    
    + DECLARE VARIABLE sumb DECIMAL(12, 2);
            DECLARE VARIABLE rdno CHAR(3);
            DECLARE VARIABLE cnt INTEGER;
    BEGIN
    -       tot = 0;
    +       total = 0;
    ?          ++
    
    
    -       SELECT budget FROM department WHERE dept_no = :dno INTO :tot;
    +       SELECT budget FROM department WHERE dept_no = :dno INTO :total;
    ?                                                                   ++
    
    
            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;
    +                       total = total + sumb;
    ?                          ++      ++
    
                    END
    
            SUSPEND;
    END
    

    If you want to use it more often, you can save a lot of typing with handy function:

    >>> def source_diff(a,b):
    ...   result = ['Procedure %s' % a.name]
    ...   result.extend(difflib.ndiff(a.source.split('\n'),b.source.split('\n')))
    ...   return '\n'.join(result)
    ...
    >>> list map(source_diff,different,map(db2.getProcedure,(p.name for p in different)))
    Procedure DEPT_BUDGET
    -       DECLARE VARIABLE sumb DECIMAL(12, 2);
    ? -
    
    + DECLARE VARIABLE sumb DECIMAL(12, 2);
            DECLARE VARIABLE rdno CHAR(3);
            DECLARE VARIABLE cnt INTEGER;
    BEGIN
    -       tot = 0;
    +       total = 0;
    ?          ++
    
    
    -       SELECT budget FROM department WHERE dept_no = :dno INTO :tot;
    +       SELECT budget FROM department WHERE dept_no = :dno INTO :total;
    ?                                                                   ++
    
    
            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;
    +                       total = total + sumb;
    ?                          ++      ++
    
                    END
    
            SUSPEND;
    END
    

    If you know that both databases have the same procedures that just differ in source, you may write:

    >>> list map(source_diff,db.procedures,db2.procedures)
    

    to list all procedures that differ in source including the diff report.

    Tip

    You can create your own library from these functions and import them any time to the PowerConsole. You can even use PowerConsole commands in these libraries, but in that case you have to install them to your interactive session via PowerConsole’s RUN command.

Table Of Contents

Previous topic

fbtracein.py