Firebird Pack uses library fbcore build on top of KInterbasDB database driver, that defines set of higher-level objects to work with Firebird databases instead classic Python Database API, and above all, represents database metadata as Python objects.
Each connected database (represented by Database object) is also accessible in PowerConsole’s user space for direct use. You may specify the name under which it would be available using AS clause of CONECT or CREATE DATABASE statements, otherwise it would get the default name “db”.
You may use these objects directly at command propt, in your PowerConsole scripts or pass them as parameters to PowerConsole commands. Here are some examples:
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
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)
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)
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
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
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.
This class represents a database.
It contains methods to attach, create or drop database, properties and methods to access metadata or create new metadata objects.
Represents database extension or shadow file.
Represents character set.
Represents collation.
Represents Table in database.
Represents table column.
Represents table or column constraint.
Represents database index.
Represents database View.
Represents view column.
Represents trigger.
Represents stored procedure.
Represents procedure parameter.
Represets UDF argument.