Feb. 11, 2011, 1:08 a.m.
posted by dver
Define Personalized Parameters
If you need every user to have a different set of global variables to use as query parameters and the like, you can create a view of your parameter table such that each user sees a different row of the underlying table.
It is easy to create query parameters that are persistent when there's only one user. But if there are several users you have a problem: Janet may not want to see John's parameters. Worse still, there is a danger that John will change the parameters after Janet has set them but before she gets to run her query.
You can deal with this by creating a table that provides one row per user, and replacing the param table shown in "Store Parameters in the Database" [Hack #58] with a view that returns only the row from the param table that is associated with the current user. What follows is the standard SQL syntax needed to create the table and the view. Oracle, SQL Server, MySQL, and Access require a slight variation to the pattern, but the differences are trivial:
CREATE TABLE userparam ( who VARCHAR(20) DEFAULT CURRENT_USER , prmStart DATE , PRIMARY KEY (who) ); CREATE VIEW param AS SELECT prmStart FROM userparam WHERE who=CURRENT_USER;
You can use precisely the same code for the query and for updating the parameter, as shown in "Store Parameters in the Database" [Hack #58] (the param VIEW can be updated):
UPDATE param SET prmStart = DATE '2006-05-27';
If john issues this UPDATE, only his row will change.
Similarly, if john issues the SELECT statement involving the param table, only his row will be accessed.
Adding New Users
Each user on the system must have an entry in the userParam table. If you have set up the primary key correctly you can issue the INSERT command at the start of every session. When an existing user initiates subsequent sessions, this will generate an error that can be safely ignored:
INSERT INTO userParam(who) VALUES (CURRENT_USER)
You can modify this to avoid the error messagethis will not generate an error for existing users:
INSERT INTO userParam(who) SELECT (CURRENT_USER) FROM (SELECT 1) x WHERE NOT EXISTS (SELECT * FROM param);
The outer SELECT statement gives zero rows if the user has already been added to the userParam table and one row otherwise.
Some minor variations are required for each platform. With the changes, the hack works well in MySQL, SQL Server, Access, and Oracle. The changed portions appear in bold text:
Apart from PostgreSQL (where you need to update the underlying table directly as shown above), the following code can be used to update param: