Storing Logic In a Field (per row)
Posted by: David Ward
Date: January 21, 2008 12:25PM

I would like to have a table where one field contains logic -- an SQL expression. This SQL expression will be different for every row. I want to be able to perform a query in which the MySQL server evaluates this unique stored expression for every row and returns the result in another column.

For example, I have a table of users. In this table, I have two rows -- one for Carol and one for Michael. Carol should be able to access my website if the current date is between January 1, 2008 and July 30, 2008. However, Michael should be able to access my website on any day, but only between 8:00:00 and 17:00:00.

So based on the way I am approaching this problem, I would need to design a table like this:

CREATE TABLE Users (Name VARCHAR(15), AccessExpression VARCHAR(100));
INSERT INTO Users (Name, AccessExpression) VALUES ("Carol", "CURRENT_DATE() > '2008-01-01' AND CURRENT_DATE() <= '2008-06-30'");
INSERT INTO Users (Name, AccessExpression) VALUES ("Michael", "CURRENT_TIME() >= '08:00:00' AND CURRENT_TIME() <= '17:00:00'");

...but, how do I return a column that tells me whether or not each user should be able to access my website right now? I want to do this strictly in SQL, i.e., not performing any processing in PHP and then sending additional queries to the server.

This example is very simplified. Imagine that every row is going to contain a completely different expression based on different input functions, columns, subqueries, etc.

If there is a way to do this in MySQL 4.1, that would be ideal (since the webhost I am forced to use seems to be taking forever to upgrade their servers, despite my constant begging). However I don't even know how this is possible in MySQL 5 or later. Or for arguments sake is this even possible in other databases like PostgreSQL? I have searched on this for a long time and have not been able to find anything.

I do want to take appropriate measures to protect as much as reasonably possible against things like SQL injection attacks and would appreciate considerations in that respect, but only as much as it still allows me to have this functionality.



Edited 2 time(s). Last edit at 01/21/2008 12:53PM by David Ward.

Options: ReplyQuote


Subject
Written By
Posted
Storing Logic In a Field (per row)
January 21, 2008 12:25PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.