Getting first unused integer from a sqlite integer column

mhearse has asked for the wisdom of the Perl Monks concerning the following question:

I have a sqlite table:

create table natRules (
    id integer primary key asc,
    description varchar(255),
    externalPort integer not null,
    unique(externalPort)
)
[download]

I have a web page which I used to add values to the table. I'd like to use ajax to retrieve the first unused externalPort integer. Not the max, the first unused integer. For example, assume the table has the following rows:

1|value1|3221
2|value2|78
3|value3|32
4|value4|1114
5|value5|5
6|value6|1
[download]

So, in this case the first unused integer would be 2. How do I programatically retrieve that value. I'd like to use sql if possible.

I found my answer. Probably shouldn't have posted this in a Perl forum, but what the heack...

SELECT  id
FROM    (
        SELECT  1 AS id
        ) q1
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    unused
        WHERE   id = 1
        )
UNION ALL
SELECT  *
FROM    (
        SELECT  id + 1
        FROM    unused t
        WHERE   NOT EXISTS
                (
                SELECT  1
                FROM    unused ti
                WHERE   ti.id = t.id + 1
                )
        ORDER BY
                id
        LIMIT 1
        ) q2
ORDER BY
        id
LIMIT 1
[download]

Comment on Getting first unused integer from a sqlite integer column Select or Download Code

Replies are listed 'Best First'.
Re: Getting first unused integer from a sqlite integer column by afoken (Chancellor) on Oct 21, 2010 at 19:08 UTC
You don't want to do that, that would cause race conditions. Use a sequence or an automatically incrementing column, for the latter in SQLite, see http://www.sqlite.org/autoinc.html. Alexander -- Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)	[reply]
Re^2: Getting first unused integer from a sqlite integer column by mhearse (Chaplain) on Oct 21, 2010 at 19:32 UTC
I agree about the race condition. I like the idea of using an autoincrement for the externalPort. Now I just need to figure out if I can set min and max bounds `min: 1024 max: 65535` [download] Wait a minute... wouldn't redefining the table, making externalPort autoincrement, then creating a dummy record with externalPort = 1023 work? I'll give it a try.	[reply] [d/l]
Re: Getting first unused integer from a sqlite integer column by locked_user sundialsvc4 (Abbot) on Oct 22, 2010 at 00:52 UTC
You almost never want to “re-use” such a number ... even though it is technically possible. In fact, I don’t even (typically) use autoincrement fields any more. I use randomly-generated strings, long enough to allow me to basically say that every value will be unique. No matter what. No matter many times I run the program or how I slog-up my database. “All integers look alike,” and y’know, that’s really not such a good thing, after all. But if you now attempt to re-use them, you have just introduced an inherent ambiguity that can never be proofed. The data may become inconsistent, through programmer-error or by any other means, and you can never explain how or when it happened. The data may be incorrect, and it is impossible to (algorithmically) detect it. These are not wise properties for a production system to have. `“Each town ... is very like another when your head’s down over the pieces, brother ...”` -- One Night In Bangkok (from “Chess”)
Re^2: Getting first unused integer from a sqlite integer column by aquarium (Curate) on Oct 22, 2010 at 03:59 UTC
totally agree with that sort of position. you probably want to stat the interface/IP for what ports are currently in use, and then pick a random one that's not in well-known ranges, e.g. port 8000, 8005 or 8080 etc can typically be used. you can dig up the table of unassigned port numbers that aren't going to interfere with most apps. keeping track of live connections (port numbers used) in a database, as a way of picking next ones to use, is probably not a great thing. the hardest line to type correctly is: stty erase ^H	[reply]
Re: Getting first unused integer from a sqlite integer column by aquarium (Curate) on Oct 21, 2010 at 22:49 UTC
how about just `select min(port_num)+1 from table_name` [download] if you need the values of other columns, since this is a aggregate select statement, you'd use a "first" or "max" or similar to get the desired values from those other columns. in other words if you use any aggregate sql function on any column, you'll need to use aggregate functions on all columns, which provides a proper set of rows that makes sense. the hardest line to type correctly is: stty erase ^H	[reply] [d/l]