Custom comparison operators in SQL

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel E. Macks - 29 Oct 2004 00:22 GMT

I'm trying to set up a database (preferably SQLite, but otherwise
MySQL or postgresql) eferably containing software products, and one
field I need is "version". My goal is to be able to do things like:

SELECT title,version WHERE title='something' AND version<'something'

and get everything I have that is older than a given version. But
while each product has a self-consistent versioning scheme, version
strings are nowhere near standardized between different products.
(1.0.5, 1.4.0.2, 3.0.4a, 0.9rc1, 2004g.2a, etc.)

I have an alogrithm I could could implement very simply in perl or C
or PHP, but it seems like it would be very hard to do with SQL string
operators and string/number comparisons (since there are so many
version-string formats) either directly in the client's SELECT or with
stored functions. The current solution, fetching *all* versions:

SELECT title,version WHERE title='something'

and then checking the version fields in the client seems silly--
wasteful of bandwidth, and strange for the client to have to perform
this database-like functionality. Is there a way to "override" the
comparison operators for a given column? I'm coming at this as a
programmer, so I'm not sure if I'm thinking about this completely
incorrectly and missing some obvious database tricks here.

dan

Signature

Daniel Macks
dmacks@netspace.org
http://www.netspace.org/~dmacks

Reply to this Message

D. Richard Hipp - 29 Oct 2004 11:12 GMT

> I'm trying to set up a database (preferably SQLite, but otherwise
> MySQL or postgresql) eferably containing software products, and one
[quoted text clipped - 4 lines]
> Is there a way to "override" the
> comparison operators for a given column?

In SQLite version 3, you can define your on collating functions
using the sqlite3_create_collation() API. When you create your
table, add "COLLATE my_version_comparison" to the definition of
the "version" column and then the comparison function you register
as my_version_comparison will be used for all comparision operations
involving the version column. It will also be used to build all
indices.

Another approach would be to register the comparision function
as a new SQL function using sqlite3_create_function(). Then
you could say things like:

SELECT * WHERE compare_version(version,'something')<0;

You won't be able to take advantage of indices on the version
column with the second approach. The advantage of the second
approach is that no special collating functions are required.
This means that the database can still be manipulated by clients
(such as the sqlite3.exe command-line tool) that do not know
about the special collating function.

Reply to this Message