Tuesday, July 29, 2008

Setting up ColdFusion and Firebird

After a long struggle on Devshed forums I decided to try on my own, after all I know Firebird and don't see why it and ColdFusion can't play nice together ... infact they did, and easily.
Already having installed on my PC the database (Firebird 2.5 Alpha1) and a client tool (Flamerobin 0.8.6) I went for the server, downloading and installing the free developer version of ColdFusion 8 from Adobe's site.
Install went smooth and I just confirmed every default option but the drive D: in my case instead of the standard C:
After the install finished I logged into the admin console at the default address and opened the Server Setting Summary:

The important things are highlighted:
  1. ColdFusion server version: 8,0,1,195765
  2. Edition: Developer
  3. Java VM Version: 1.6.0_04
With this infos at hand I went to Firebird site to download the appropriate driver version:

I chose the lates driver available, JayBird 2.1.6, for my JVM which is 1.6:


The driver downloads as a zip file, unzip it, extract the driver:

And place it along the other JDBC drivers in the ColdFusion server (the server config can tell you where they are), in my case D:\ColdFusion8\lib and restarted the server:


A quick check confirmed that the driver is in the server classpath:

Now I set up an alias for my Firebird database, just to have a simpler connectionstring, open up the file aliases.conf and add a row for your db, from now on you'll be able to refer to the alias instead of the full path to the db:


Don't forget to restart the Firebird server.

Ok, time to go back to ColdFusion and set up a Data Source:


Name it "LocalFirebird" and chose driver "other" from the drop down list, the click "Add".
After clicking "Add" a new form opens, asking for some other details:

Values are:

  1. JDBC URL: jdbc:firebirdsql:127.0.0.1/3050:pippo (note the alias, no need for the full path)
  2. Driver Class: org.firebirdsql.jdbc.FBDriver
  3. Driver Name: JayBird
  4. Username: your user, standard admin name is SYSDBA
  5. Password: your password, standard password for SYSDBA is masterkey
Now click "Submit" and you'll go back to the Data Sources list, click "Verify all connection" and wait to see if an "OK" appears for the new connection:

After confirming the datasource is OK you can go on and put up a quick test page.

A Devshed forum use has put up a quick tutorial some time ago, kudos to him but it looks like it's a bit old (2003) and "poor in graphics" ;), you can find it here, I added some notes there which correspond to the content of this post.

Sunday, July 27, 2008

The new MINI Clubman

I'm the happy owner of a New MINI ONE and had a chance to try the New MINI Cooper Clubman, which, apart being the bigger sister of my car, is also a second generation New Mini, which means new engine, new steering and some other things ... unfortunately not all of them are nice.
First of all, the new engine seems to be better than the old one, with a nice sound and good power.
The new steering is good, very light when parking and with a nice, heavy feeling at speed.
Car dynamics are great as usual, a pleasure to drive.
Now for the "not nice" things, rear visibility is close to zero:











The rear booth is bigger than the standard Mini (not too difficult ;)) but overall smaller than what I expected:











and the rear doors don't help when parked in tight quarters.










Other than that I didn't like some of the plastics used inside the car

and the awful "Goldrake" look of the central instruments, see it for
yourself:














Basically the Mini didn't change, so nice to drive that you'll forget it's price tag and everything else, but I must say that I like the first generation new Mini better than it's successor.

Tuesday, July 22, 2008

PostgreSQL - Firebird comparison

Google led me to a comparison sheet about PostgreSQL and Firebird by AMSoftwareDesign, as it looks a bit outdated I decided to add some infos about the latest Firebird release, see it in action:


PostgreSQL vs Firebird feature comparison
Feature PostgreSQL 8.2.x Firebird 2.0.x Firebird 2.5 Alpha
MVCC Yes Yes Yes
Row level Locking
Available
Yes Yes Yes
Max
Database Size
Unlimited* Unlimited* Unlimited*
Max
table Size
32 TB ~32 TB ~32 TB
Max
Row Size
1.6 TB 64 KB 65 KB
Max
Rows per Table
Unlimited* > 16 Billion > 16 Billion
Max
Columns Per Table
50 - 1600 depending on column types Depends on data types used. Depends on data types used.
Max
Indexes Per Table
Unlimited* 65,535 66,535
Max
SQL statement size
Unlimited* 64kb 64kb
Multi Threaded
Architecture Available?
No (see "Features we do NOT want" in the TODO list) Yes (super server) and No (classic server)
Yes (super server), architectures will be unified with full SMP support in 3.0
Ability to re-order
table columns
without re-creating
No Yes Yes
Stores
Transaction Information
in same file as data
No Yes Yes
Auto Increment
Columns
Yes (serial type that uses sequences) Yes (must use a generator and a trigger) Yes (must use a generator and a trigger)
True Boolean
column type
Yes No No
Table Inheritance Yes No No
Domains Yes Yes Yes
Table Partitioning Yes (basic) No No
Updateable Views No (workaround available via rules system) Yes Yes
Event/Notification System Yes Yes Yes
Temporary Tables Yes No Yes
Rich Built in Functions Yes Yes
Yes
Multi Lang Stored Procedures Yes (PLPGSQL,PLPerl,PlJava etc) No No (support for Java stored procedures is scheduled for 3.0)
Compiled External Function
(UDF) Support
Yes Yes
Yes
Exception handling
in stored procedures
Yes Yes Yes
2 Phased Commit Yes Yes Yes
Native SSL support Yes No No
Multiple auth methods
(i.e. LDAP)
Yes No Yes (database auth or integrated windows auth)
Compound Indexes Yes Yes Yes
Unique Indexes Yes Yes Yes
Partial Indexes Yes Yes Yes
Functional Indexes Yes Yes Yes
Multiple Index Storage Types Yes (btree,hash etc) No No
Point in Time Recovery Yes No No
Schema Support Yes No No
Conforms to ANSI-SQL 92/99 Yes Yes Yes
Limit/Offset support Yes Yes Yes
Create user defined types Yes No No
Create user defined operators Yes No No
Create user defined Aggregates Yes No No
Log Shipping (for Point In Time Recovery and Log Shipping) Partial No No
Write ahead logging Yes No No
Tablespaces Yes No No
Save Points Yes Yes Yes
Open Source
Async Replication
Yes (Slony ) No (Commercial solutions available. Database shadowing is also present.) No (Commercial solutions available. Database shadowing is also present.)
Online/Hot Backups Yes Yes Yes
File System based
backups possible
Yes (Postmaster must be stopped) Yes Yes
Require backup/restore to compact No Yes Yes
Fully ACID Compliant Yes Yes Yes
Native Win32 Port Yes Yes Yes
Text/Memo field type Yes Yes
Yes
BLOB support Yes (limited to the max field size of 1 GB) Yes (Can be up to 32GB) Yes (Can be up to 32GB)
UTF8 support Yes Yes Yes
Define charactersets/collations per database (default) Partial (PostgreSQL can also define a characterset for the entire database cluster during the initdb process, it is not recommend to run databases in different
encodings than the encoding chosen at initdb time
)
Yes Yes
Define charactersets and collations on a per column level No Yes Yes
Foreign Keys Yes Yes Yes
Check Constraints Yes Yes Yes
Unique Constraints Yes Yes Yes
Not Null Constraints Yes Yes Yes
Multiple Transaction Isolation levels Yes Yes Yes
Fully relational System Catalogs Yes Yes Yes
Information Schema Yes No (no schema support) but equivalent system tables No (no schema support) but equivalent system tables
Native GIS support via GIST or other native means Yes (PostGIS ) No No
Open Source Full Text Search Yes No No
Use POSIX Regular Expressions in queries Yes No Yes, through standard predicate SIMILAR TO
Database Monitoring Yes No Yes, through system tables and triggers
Ability to query databases on other servers local or remote. Yes (Dblink ) No Yes (through EXECUTE STATEMENT)
Ability to query other databases Yes (DBI-Link ,DBLink-TDS ) No No
Read Only Databases No Yes Yes
Regular Version Updates Yes Yes Yes




* Unlimited but still restricted by system resources.

Has them all

A question that pops up frequently on Devshed forums is "How can I get all products that are available in Red and Green colors?" or "How can I find out which customers bought this book and that CD?", solution is simple and I'll provide an example here, it can be made more complicate at your option, but it all boils down to a where and an having condition.
Say we have a table that lists all products and the colors in which those products are available:

  1. CREATE TABLE PRODUCT_COLORS(
  2. PRODUCT_CODE CHAR(5) NOT NULL,
  3. COLOR_CODE CHAR(1) NOT NULL,
  4. CONSTRAINT PRODUCT_COLORS_PK PRIMARY KEY (PRODUCT_CODE,COLOR_CODE)
  5. );

Data looks like:

Code:
XXXXX                R
XXXXX B
YYYYY Y
YYYYY G
ZZZZZ G
ZZZZZ R
First column is the product code, second column is the color code.
Say we want to know which products are available in G(reen) and R(ed), the query is simple, we'll list all products which do have the Red or Green option and then filter out all those that don't have both, getting the desired result (in this case product 'ZZZZZ')
See it in action:

  1. SELECT a.PRODUCT_CODE
  2. FROM PRODUCT_COLORS a
  3. WHERE a.COLOR_CODE IN ('R', 'G')
  4. GROUP BY a.PRODUCT_CODE
  5. HAVING COUNT(a.PRODUCT_CODE) = 2

See where I implemented the two conditions? One in the where clause and the second pass to filter out all products which don't have both in the having clause.
Example is built on Firebird, but should work in MySQL, PostgreSQL or any other mainstream database too.

Monday, July 21, 2008

Another great new feature of Firebird 2.5

Other than CREATE USER something really valuable has been added, the ability to query other databases.
Using a table structure like my previous post what follows is an example of a query running on an external database:

SET TERM ^ ;
CREATE PROCEDURE GET_MASTER_PROD_ALL_EXT
RETURNS (
P_CODE Char(5),
I_ENABLED Char(1),
P_DESCR Varchar(50) )
AS
declare variable qry varchar(5000);
BEGIN
qry = 'SELECT pm.PRODUCT_CODE, pm.IS_ENABLED, pm.PRODUCT_DESCR
FROM product_master pm';
EXECUTE STATEMENT qry ON EXTERNAL DATA SOURCE 'localhost:c:\pippo2.fdb'
AS USER 'sysdba' PASSWORD 'masterkey'
INTO :p_code, :i_enabled, :p_descr;
SUSPEND;
END^
SET TERM ; ^

The trick is the ON EXTERNAL DATA SOURCE clause, which allows you to specify a target database ("localhost:c:\pippo2.fdb" in my case, generally that's time to start using aliases) and the user/password to be used
As you can see , no "dblinks" yet, but of course you can embed it into a view and join it (the stored proc or the view) with other local or remote objects.
The querystring can also be built dynamically passing parameters.
Isn't it great?

Tuesday, July 15, 2008

Controlling user access to data

A straight and simple question on Devshed prompted me to post this example about using updateable views to limit the ability of users to read and manipulate data both horizontally and vertically.
Before you ask, with "horizontally" I mean restricting access to a subset of rows in a table, with "vertically" I mean restricting access to a subset of columns in a table.
See this scenario, a table holding product data, named product_master and two users, one with full access and another one which we want to limit.
Specifically we want to allow this second user to see only products flagged as "enabled" (horizontal limitation, only a subset of rows) and, for those products, we want to be shure that he can only change (update) the product description.
How can we achieve this?
With a mix of grant management and updateable views, let's see this setup in action:
Structure for table product_master is:

  1. CREATE TABLE PRODUCT_MASTER(
  2. PRODUCT_CODE CHAR(5) NOT NULL,
  3. IS_ENABLED CHAR(1),
  4. PRODUCT_DESCR Varchar(50),
  5. CONSTRAINT PRODUCT_MASTER_PK PRIMARY KEY (PRODUCT_CODE)
  6. );
  7. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
  8. ON PRODUCT_MASTER TO SYSDBA WITH GRANT OPTION;

As you can see only sysdba has access to this table, nothing is granted to user pippo or public role.
We said that pippo should be able to update the product description, so I'll give him appropriate grants for this:

GRANT UPDATE ON PRODUCT_MASTER TO PIPPO; 

Ok, right now pippo can update the product table, then comes the hard part, allowing him to see and update only enabled products, those rows of the product master table that have the enabled field set to 'Y'.

This will be accomplished with an updateable view and proper grants, let's see it in action:

  1. CREATE VIEW ENABLED_PRODUCTS (PRODUCT_CODE, PRODUCT_DESCR)AS
  2. /* write select statement here */
  3. SELECT pm.PRODUCT_CODE, pm.PRODUCT_DESCR
  4. FROM product_master pm
  5. WHERE IS_ENABLED = 'Y'
  6. WITH CHECK OPTION;

The two important things here are the where condition in the select and the "with check option" clause.
The related grants to complete the magic are:

  1. GRANT SELECT, UPDATE ON ENABLED_PRODUCTS TO PIPPO;
  2. GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
  3. ON ENABLED_PRODUCTS TO SYSDBA WITH GRANT OPTION;

Hope this helps