This is the progress information regarding the PRADO Commerce project that I am working on.

I spent the last three hours looking and Googling to solve a very annoying problem in order to compile PostgreSQL drivers natively with Qt 4.4.0 framework. After a lot of frustration I discovered that one has to provide the path to libpq.lib and the PostgreSQL include dir in UNIX style or 8.3 short filenames to get the qmake.exe to read them correctly.

After running configure.exe, I had to modify .qmake.cache like to following to make it work:

QMAKE_QT_VERSION_OVERRIDE = 4
LIBS           += C:\PROGRA~1\POSTGR~1\8.3\lib\libpq.lib
LIBPATH        += C:\PROGRA~1\POSTGR~1\8.3\lib
INCLUDEPATH    += C:\PROGRA~1\POSTGR~1\8.3\include
OBJECTS_DIR     = tmp\obj\debug_shared
MOC_DIR         = tmp\moc\debug_shared
RCC_DIR         = tmp\rcc\debug_shared
sql-drivers    += odbc psql
sql-plugins    += sqlite
....
....
....
QMAKE_LIBDIR_QT = $$QT_BUILD_TREE\lib

In this post, there is a nice explanation about executing conditional DDL. Here are my two functions which I use when I have to recreate a database schema.

create or replace function table_drop_if_exist(tbl varchar(255)) returns void as
$$
begin
	if (select count(relname) from pg_class where relname=tbl) <> 0 then
		raise notice ‘dropping [%] with cascade’,tbl;
		execute ‘drop table ‘ || tbl || ‘ cascade’;
	end if;
end;
$$
language plpgsql;

and of course

create or replace function type_drop_if_exist(typ varchar(255)) returns void as
$$
begin
	if (select count(relname) from pg_class where relname=typ and relkind='c') <> 0 then
		raise notice ‘dropping [%] with cascade’,typ;
		execute ‘drop type ‘ || typ || ‘ cascade’;
	end if;
end;
$$
language plpgsql;

Usage:

select table_drop_if_exist('tblregion');
create table tblregion
(
	regionid integer primary key not null,
	region varchar(50),
	code varchar(2)
);

Using sequences are common practice in order to have auto numbering in a PostgreSQL database. The easiest way to create an auto numbered identity field is to use the SERIAL data type macro.

Here is an example:

CREATE  TABLE customer
(
	customer_id SERIAL NOT NULL PRIMARY KEY,
	name varchar(50)
);

When running this script, PostgreSQL automatically will create a SEQUENCE called customer_id_seq and assign the default value of the customer_id field to the next available value from the sequence. This is done by calling nextval(’customer_id_seq’::regclass).

Upon inserting new records in the customer table, the sequence will work just fine as long as you leave the customer_id field alone. In this the sequence will be incremented twice, generating numbers 1 and 2.

INSERT INTO customer (name) values('Customer 1');
INSERT INTO customer (name) values('Customer 2');

But what will happen to the next value on our sequence when we explicitly present data to the customer_id field? For example when we are loading data from an external source.

INSERT INTO customer (customer_id,name) values(3,'Customer 3');
INSERT INTO customer (customer_id,name) values(4,'Customer 4');
INSERT INTO customer (customer_id,name) values(5,'Customer 5');

As expected the value of customer_id_seq will not increment as we insert data with the example above. This is because nextval(‘customer_id_seq’) will only be triggered when customer_id is NULL.
As expected the value of customer_id_seq will not increment as we explicitly insert data with the example above. It is because nextval(‘customer_id_seq’) will only be triggered when customer_id is NULL. This means when you try to insert another customer record without explicitly providing a unique customer_id, the nextval(‘customer_id_seq’) will incremented to 3 which is already used, resulting a nice duplicate key exception:

INSERT INTO customer (name) values('Customer 6');

----------------------------------------------------------------------
ERROR:  duplicate key value violates unique constraint "customer_pkey"

********** Error **********

ERROR: duplicate key value violates unique constraint "customer_pkey"
SQL state: 23505

This can easily break your application unexpectedly. In In order to solve this problem we have to synchronize the value of customer_id_seq with the latest value existing in the customer_id column: The following function provides just the functionality needed. Just provide the table name and the the name of the identity column which the sequence is bound to.

CREATE OR REPLACE FUNCTION sync_sequence(
		table_name VARCHAR,idfield VARCHAR)
		RETURNS VOID AS
$$
DECLARE
	max_id BIGINT;
BEGIN
	-- retriving max id from table_name
	EXECUTE 'select max(' || idfield || ') from '
		|| table_name into max_id;
	-- resetting the default sequence on table_name
	EXECUTE 'alter sequence ' || table_name || '_' ||
		idfield || '_seq restart with ' || (max_id+1);
END;
$$
LANGUAGE PLPGSQL;

SELECT sync_sequence('customer','customer_id');

Have fun with PostgreSQL

On of the greatest developments in the world of PHP programming, is the evolution of PRADO Framework. I stumbled on this unbelievably powerful and complete framework in 2005 when I was looking for an application development framework on top of PHP.

PRADO unlike other PHP frameworks out there is very much like the good old ASP.NET. It is very easy to learn. Most importantly, it is supremely efficient in contrast to raw PHP. Imagine building a DataGrid component with raw PHP, or creating a form that needs to validate user input both when submit button is clicked.

PRADO makes PHP scripts, PHP applications.

<html>
  <body>
    <com:TForm>
      <com:TButton Text="Click me" OnClick="buttonClicked" />
    </com:TForm>
  </body>
</html>

class Home extends TPage
{
    public function buttonClicked($sender,$param)
    {
        // $sender refers to the button component
        $sender->Text="Hello World!";
    }
}

With a nice separation of markup and code and a resemblance to ASP.NET, having the power of PHP combined, makes PRADO the choice to develop application using
PHP language.

At the time of writing this entry PRADO version 3.1.2 released. As you might except in a modern application programming framework for web, PRADO has built-in AJAX support. Most of the standard controls (like Button, DropDown, List) are also AJAX aware.

Checkoput the PRADO website for more information.

I was looking for a solution to display source code in way that was readable when posting on my blog. After several failed attempts to format some PHP and SQL code with the PRE tag and CSS styles, I found this nice tool provided by google witch helps you to format/highlight source code on your web pages.

Google SyntaxHighlighter

-- Function: new_expiredate()

-- DROP FUNCTION new_expiredate();

CREATE OR REPLACE FUNCTION new_expiredate()
  RETURNS timestamp without time zone AS
$BODY$
	select (date_trunc('month',now()) + interval '7 months')::timestamp without time zone;
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;
ALTER FUNCTION new_expiredate() OWNER TO postgres;

One of the cool new features in PostgreSQL in the connect privilege. The CONNECT privilege enables you to grant connection rights from a user to a database, avoiding the need to configure user to database access in the pg_hba.conf.
The information in the pg_hba.conf could exclusively be used to administer the authentication (Are you allowed on my server?) process and the CONNECT privilege could be used for authorization (What are you allowed to do on my server?)

Let us look at the following scenario:

Imagine you want to enable different users on your network to access different databases on your server. Configuring every user for every permitted database in pg_hba.conf would be time consuming and less administer-friendly. The process could be simplified by allowing a certain network (or subnet) to authenticate and handle the database connection by CONNECT privilege.

In pg_hba.conf:
# Allow all network users to connect to by server.
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.1.0/24 password

Then secure the databases by:
-- Revoke connection right from all users to main database.
-- Dont worry, the superuser can still connect.
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;

-- create a new user called joe
CREATE ROLE joe LOGIN;

-- create a new database owned by joe.
CREATE DATABASE customers OWNER joe ENCODING UTF8

-- Revoke connection from everybody on customers database
REVOKE CONNECT ON DATABASE customers FROM PUBLIC;

-- Authorize Joe exclusively to connect to customers database
GRANT CONNECT ON DATABASE customers TO joe;

This way only Joe or anyone in that role can connect customers database.

This maybe trivial but I think it is worth sharing anyway
Consider using LIMIT/OFFSET in a function where as parameter you have to specify values for both.
In the following function both limit and offset parameters are integers. This will cause an error when you want to pass ALL (for LIMIT ALL) as argument.

PostgreSQL function:

create or replace function sp_category_getlist
	(plimit integer,poffset integer) returns setof category as
$$
	select *
	from
		category
        order by
		caption
	limit
		$1 offset $2
$$
language sql;

Fortunately PostgreSQL accepts NULL function arguments which in this case is treated as ALL

Example in PHP:

/* sp_filesystem_category_getlist */
public function sp_filesystem_category_getlist
	($puserid,$plimit=null,$poffset='0')
{
	$args = array($plimit,$poffset);
	$sql = "select * from sp_filesystem_category_getlist($1,$2)";
	$result=$this->query($sql,$args);
	return pg_fetch_all($result);
}