Saturday, February 28, 2009

One Row Only

Captain Ramius: Re-verify our range to target... one ping only.

Capt. Vasili Borodin: Captain, I - I - I just...

Captain Ramius: Give me a ping, Vasili. One ping only, please.

Capt. Vasili Borodin: Aye, Captain.
Here's a snippet of code from the SQL Anywhere database setup script for a small application. The settings table is a place to store global configuration parameters separate from the application code:
CREATE TABLE settings (
one_row_only INTEGER NOT NULL DEFAULT 1
CHECK ( one_row_only = 1 )
PRIMARY KEY,
no_picture_yet_image_folder LONG VARCHAR NOT NULL,
no_picture_yet_image_file_name LONG VARCHAR NOT NULL );

CREATE TRIGGER one_row_only BEFORE DELETE ON settings
FOR EACH ROW
BEGIN
ROLLBACK TRIGGER WITH RAISERROR 99999
'Do not attempt to delete from settings';
END;

INSERT settings VALUES (
DEFAULT,
'C:\\Inetpub\\images',
'no_picture_yet.jpg' );
COMMIT;
Once this code's been run, the settings table is available for SELECT and UPDATE but the following operations are impossible:
  • inserting another row (the CHECK and PRIMARY KEY constraints),

  • changing the primary key value (the CHECK constraint), or

  • deleting the row (the TRIGGER).
INSERT settings VALUES ( 1, 'Hello', 'World' ); -- primary key violation
INSERT settings VALUES ( 2, 'Hello', 'World' ); -- check condition is false
UPDATE settings SET one_row_only = 3; -- check condition is false
DELETE settings; -- trigger catches it

SELECT * FROM settings;

one_row_only,no_picture_yet_image_folder,no_picture_yet_image_file_name
1,'C:\\Inetpub\\images','no_picture_yet.jpg'

UPDATE settings SET no_picture_yet_image_file_name = 'future.jpg';

one_row_only,no_picture_yet_image_folder,no_picture_yet_image_file_name
1,'C:\\Inetpub\\images','future.jpg'
In other words, there will always be exactly one row in this table, and that fact is guaranteed by the the database itself... perfect for a global configuration settings table.

No comments: