{"id":15,"date":"2007-11-30T12:51:13","date_gmt":"2007-11-30T12:51:13","guid":{"rendered":"http:\/\/muratyaman.co.uk\/wp\/?p=15"},"modified":"2020-04-01T13:09:49","modified_gmt":"2020-04-01T12:09:49","slug":"firebird-sql-the-power-of-simplicity","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2007\/11\/firebird-sql-the-power-of-simplicity\/","title":{"rendered":"Firebird SQL: The Power of Simplicity"},"content":{"rendered":"

Firebird SQL Server<\/a> has always been a “proper” database system since it forked <\/a>from Borland’s famous Interbase<\/a>. I just love<\/b> it!<\/p>\n

In some big database applications, I found that the developers preferred to duplicate a set of tables for probably performance reasons.<\/p>\n

For example, in most accounting databases, there is a notion of company<\/b> so that you can create multiple companies and manage its clients, suppliers, transactions etc. separately. Also, for critical changes in the way you do business or because of the changes in the whole country, you can leave it aside as historical data, and create a new company to start afresh.<\/p>\n

So, if we have tables for client and supplier accounts, invoices, invoice details (items, products) and so on and so forth, they are duplicated for each company. For instance, the table “TBL_ACCOUNT_AB<\/b>” is for company ABC (just a reference code for your company) and the table “TBL_ACCOUNT_XY<\/b>” is for another company (XYZ). Likewise, you may have 50 tables, if you have 5 companies and 10 tables in your set.<\/p>\n

You may say, I thought as well: why do we not duplicate the whole database? Well, we may not need the whole database to be duplicated and it has other implications, such as managing the user list, etc.<\/p>\n

Here comes the power of Firebird:<\/u><\/b>
\n\"Firebird<\/p>\n

Let’s define a simple set of tables for company ABC:<\/p>\n

\r\nCREATE TABLE TBL_ACCOUNT_AB (\r\n  ACC_ID          INTEGER NOT NULL\r\n, ACC_NAME     VARCHAR(50)\r\n, CONSTRAINT   PK_ACCOUNT_AB PRIMARY KEY (ACC_ID)\r\n);\r\n<\/pre>\n
\r\nCREATE TABLE TBL_INVOICE_AB (\r\n  ACC_ID          INTEGER NOT NULL\r\n, INV_ID           INTEGER NOT NULL\r\n, OTHER_DATA  VARCHAR(50)\r\n, CONSTRAINT   PK_INVOICE_AB PRIMARY KEY (INV_ID)\r\n, CONSTRAINT   FK_INVOICE_AB FOREIGN KEY (ACC_ID)\r\n                      REFERENCES TBL_ACCOUNT_AB (ACC_ID) \r\n);\r\n<\/pre>\n
..and duplicate these for company XYZ:<\/p>\n
\r\nCREATE TABLE TBL_ACCOUNT_XY (\r\n  ACC_ID          INTEGER NOT NULL\r\n, ACC_NAME     VARCHAR(50)\r\n, CONSTRAINT   PK_ACCOUNT_XY PRIMARY KEY (ACC_ID)\r\n);\r\n\r\nCREATE TABLE TBL_INVOICE_XY (\r\n  ACC_ID          INTEGER NOT NULL\r\n, INV_ID           INTEGER NOT NULL\r\n, OTHER_DATA  VARCHAR(50)\r\n, CONSTRAINT   PK_INVOICE_XY PRIMARY KEY (INV_ID)\r\n, CONSTRAINT   FK_INVOICE_XY FOREIGN KEY (ACC_ID)\r\n                      REFERENCES TBL_ACCOUNT_XY (ACC_ID) \r\n);\r\n<\/pre>\n
To track the current company record, let’s have a simple parameter\/settings table:<\/p>\n
\r\nCREATE TABLE TBL_SETTING(\r\n  ID       INTEGER DEFAULT 0 NOT NULL   \/* Primary Key, always=0 *\/\r\n, CMP    VARCHAR(2) DEFAULT 'AB'         \/* Company Abbreviation *\/\r\n, CONSTRAINT PK_SETTING PRIMARY KEY (ID)\r\n);\r\n<\/pre>\n
It’s very simple to create a stored procedure to get data from our tables. But, on this one, the trick is automatically switch between the sets and get data from that set. Here are 2 samples:<\/p>\n
1. For the main table:<\/p>\n
\r\nSET TERM ^ ;\r\nCREATE PROCEDURE SP_ACCOUNT\r\nRETURNS (\r\n    ACC_ID    INTEGER,\r\n    NAME      VARCHAR(50))\r\nAS\r\nDECLARE VARIABLE CMP VARCHAR(2);\r\nDECLARE VARIABLE SQL VARCHAR(255);\r\nBEGIN\r\n    SELECT CMP FROM \"TBL_PARAM\" INTO :CMP;\r\n    SQL = 'SELECT ACC_ID, NAME FROM TBL_ACCOUNT_' || CMP;\r\n    FOR EXECUTE STATEMENT SQL\r\n    INTO :ACC_ID, :NAME\r\n    DO\r\n        SUSPEND;\r\nEND ^\r\nSET TERM ; ^\r\n<\/pre>\n
2. For the subtable:<\/p>\n
\r\nSET TERM ^ ;\r\nCREATE PROCEDURE SP_INVOICE\r\nRETURNS (\r\n    ACC_ID         INTEGER,\r\n    INV_ID          INTEGER,\r\n    OTHER_DATA VARCHAR(50)\r\n)\r\nAS\r\nDECLARE VARIABLE CMP VARCHAR(2);\r\nDECLARE VARIABLE SQL VARCHAR(255);\r\nbegin\r\n    SELECT CMP FROM TBL_PARAM INTO :CMP;\r\n    SQL = 'SELECT ACC_ID, INV_ID, OTHER_DATA from TBL_INVOICE_' || CMP;\r\n    FOR\r\n        EXECUTE STATEMENT SQL\r\n        INTO :ACC_ID, :INV_ID, :OTHER_DATA\r\n    DO\r\n        SUSPEND;\r\nend ^\r\nSET TERM ; ^\r\n<\/pre>\n
Now, all this is for not duplicating the effort whenever we need these 2 tables:<\/p>\n
WRITE IT ONCE, REUSE IT!<\/p>\n
Another excellent feature of Firebird is to be able to use stored procedures in SELECT statements:<\/p>\n
\r\nSET TERM ^ ;\r\nCREATE PROCEDURE SP_ACCOUNT_INVOICE (\r\n    CRIT VARCHAR(50)\r\n)\r\nRETURNS (\r\n    ACC_ID         INTEGER,\r\n    NAME           VARCHAR(50),\r\n    INV_ID          INTEGER,\r\n    OTHER_DATA VARCHAR(50)\r\n)\r\nAS\r\nBEGIN\r\nFOR\r\n  EXECUTE STATEMENT\r\n    'SELECT A.ID, A.NAME, I.INV_ID, I.OTHER_DATA\r\n     FROM SP_ACCOUNT A\r\n     INNER JOIN SP_INVOICE I ON A.ACC_ID = I.ACC_ID\r\n     WHERE A.NAME LIKE ''%' || :CRIT || '%''  '\r\nINTO :ACC_ID, :NAME, :INV_ID, :OTHER_DATA\r\nDO\r\n  SUSPEND;\r\nEND ^\r\nSET TERM ; ^\r\n<\/pre>\n
Can you do this in M$ SQL Server? NO!<\/u><\/b><\/p>\n
You have to spend so much time to get the similar effect. If you want to reuse your stored procedures, you must<\/b> create FUNCTIONS (returning tables) in M$ SQL, in which you can NOT use dynamic SQL (using EXEC or similar command). This means you have to stick to stored procedures ALL the time and you must duplicate what we did on SP_TEST whenever you need.<\/p>\n
For example, you may want to create 10 reports based on your client accounts table. This means: you must write 10 stored procedures and repeat<\/b> the same block of statements to get the account details!<\/p>\n
Sometimes, in M$ SQL, I use VIEWS and I must fix the table names:<\/p>\n
\r\ncreate view VW_TEST\r\nAS\r\nselect *\r\nfrom TBL_TEST_AB\r\n<\/pre>\n
and when the change comes, it IS a waste of time to go through all the VIEWS and recompile them with the new extension:<\/p>\n
\r\ncreate view VW_TEST\r\nAS\r\nselect *\r\nfrom TBL_TEST_XY\r\n<\/pre>\n
THIS IS TERRIBLE!<\/p>\n
\n
\"Firebird
\nUse Firebird SQL Server<\/a>, if you have the luxury to choose among many database systems.
\nhttp:\/\/www.firebirdsql.org\/<\/a><\/p>\n
\n
\"PostgreSQL
\nIf you want to deal with the COMPLEXITY, try PostgreSQL Server<\/a>.
\nhttp:\/\/www.postgresql.org\/<\/a><\/p>\n
\n
MySQL is trying to become a “proper” database system on VERSION 5<\/b> (For God’s sake!).
\nhttp:\/\/www.mysql.com\/<\/a><\/p>\n
\n
If you fancy a POSH database system, try Oracle<\/a>. It has express version available for free; check the licence details.
\nhttp:\/\/www.oracle.com\/<\/a><\/p>\n
\n
If you are walking on the road, bricks of which are laid by Microsoft, I need not say anything; you are already using M$ SQL Server 200x<\/a>.
\nhttp:\/\/www.microsoft.com\/sql\/<\/a><\/p>\n
\n
..clearly, the links show which one is profit-seeking company, which one is non-profit organisation :p<\/p>\n","protected":false},"excerpt":{"rendered":"
Firebird SQL Server has always been a “proper” database system since it forked from Borland’s famous Interbase. I just love it!<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[55,33],"class_list":["post-15","post","type-post","status-publish","format-standard","hentry","category-technology","tag-firebird","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/15","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=15"}],"version-history":[{"count":3,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/15\/revisions"}],"predecessor-version":[{"id":1016,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/15\/revisions\/1016"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=15"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=15"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=15"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}