A SQLite Tidbit: Quick Numbers Table Generation

Introduction

If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later by BrandonGalderisi or Fun with MS SQL spt_values for delimited strings and virtual calendars by mark_wills. Both the numbers view and spt_values in T-SQL could be used in cases where a full table of dates (one row for each day) is needed (e.g., see my Day of the n-th Day in a Month article).

These kinds of needs tend to be SQL platform independent; therefore, the only thing that really is different is what options you have in a particular system to quickly generate a sequence or to create a permanent numbers table that is indexed. Subsequently, this article is to show a neat trick SQLite users (tested in SQLite3 but approach should be version agnostic) can use to generate a numbers table for use in their SQLite database(s).

For those just wanting the goods, here we go ... explanation will follow.

Utility: Numbers Table

-- clear old numbers structure (careful here)
drop table if exists numbers;
-- create numbers utility structure, very basic integer primary key column n
create table numbers(n integer not null, primary key(n asc));
-- insert generated sequential integers
insert into numbers(n)
select rowid 
from (

-- build 1,000,000 rows using Cartesian product
select 1
from (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) a, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) b, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) c, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) d, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) e, (
   select 0 union select 1 union select 2 union select 3 
   union select 4 union select 5 union select 6 
   union select 7 union select 8 union select 9
) f

) derived;

Select all Open in new window

Voilà !

Hopefully the above speaks for itself, but here is a look at what it is doing:

Reusing Numbers Utility

This is a quick trick to implement, but, as you may have read in Brandon's article, there are many good reasons, like reducing overall size of database(s), to having just on copy of our numbers table and other utilities in a separate database we can call util. Then we can use our numbers table in other databases by querying our util database.

Consequently, SQLite allows querying intra databases through attaching.

attach database '{file name}' as {database-name};

Select all Open in new window

The single quotes are not required if your file name doesn't include an extension, but as a convention I have created mine as {database-name}.db so quotes are needed in my example.

attach database 'util.db' as util;

Select all Open in new window

Note that the {database-name} here can be however you want to reference the attached database and doesn't restrict you to having to be the same name as the file. A good example of this is that the primary database connection has a name of 'main' by default. To take a look at the attached databases, you would use the following command:

.databases

Select all Open in new window

Aside from being a useful reminder of the schema name given to your database file(s) you have attached or double-checking a given database is attached before you query, it may be a necessary exercise if you already have connections equal to SQLITE_MAX_ATTACHED. If you need to detach a database, you would use:

detach database  {database-name};

Select all Open in new window

Where {database-name} is the same schema name you assigned during attachment which you will see displayed as name when viewing the results of .databases.

Once attached, we can now take advantage of our numbers table and for that matter any utilities we have stored in util. As test this worked and example of syntax:

select n from util.numbers limit 10;

Select all Open in new window

Numbers Utility In Action

If you are still not convinced this can be used in the real world of SQLite since the referenced articles are T-SQL oriented, here is an example of the numbers table in action when mindwarpltd asked the question: Can I create a loop in an sqlite query, maybe loop is the wrong word...

The request:

I want to produce a list of recurring items from a table where one record exists.

e.g. keep adding records which accumulate and increase the due date

The pertinent piece of the ultimate solution:

select a.ID, a.desc
     , date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) as due
     , a.[price]
from billsndeposits a
inner join util_nums b -- <<<< this would be util.numbers
   on date(a.startdate, '+'||(b.n*a.interval)||' '||a.intervaltype) < date('2011-01-01')
where not (intervaltype = 'once' or interval = 0)
order by due, id;

Select all Open in new window

Using the date() function, this simulates a loop by replicating rows of the bills and deposits table by a number (n) from our numbers table that when added to the start date in a specific interval of time resulted in a date that fell within a given time period. This select was then used to create a new table, but as you can see was useful in putting into a single statement what would have normally been a multi-line statement with a loop.

In the question, I used a quick and dirty approach to creating the util_nums table.  The technique used there is another option for you. It involved creating the table with an INTEGER PRIMARY KEY AUTOINCREMENT column and then using the same Cartesian product query but selecting null which, when inserted will result in a default, incremented, value in the primary key column.

Conclusion

We now have a nice quick way to create a useful numbers utility in SQLite. In addition, we have been introduced to rowid | _rowid_ | oid which can be useful for other things in its own right. Think if we had a table with 1,000 rows already in our database we could have simply cross joined two queries that each selected rowid from our table to get our 1,000,000 rows.

Enjoy...

Thanks for reading!

Best regards and happy coding,

Kevin C. Cross, Sr. (mwvisa1)

References

Related Articles
http://sqlservernation.com/blogs/tipweek/archive/2009/04/27/the-numbers-table-round-2.aspx
http://sqlservernation.com/blogs/tipweek/archive/2010/06/27/quickly-generate-a-number-sequence-in-sql-server.aspx
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_1221-Fun-with-MS-SQL-spt-values-for-delimited-strings-and-virtual-calendars.html

Related Q&A
http://www.experts-exchange.com/Database/Miscellaneous/Q_26397603.html

Related SQLite Documentation
http://www.sqlite.org/autoinc.html
http://www.sqlite.org/lang_createtable.html#rowid

http://www.sqlite.org/lang_attach.html
http://www.sqlite.org/lang_detach.html
http://www.sqlite.org/limits.html#max_attached

Resources
https://addons.mozilla.org/en-US/firefox/addon/5817/

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you found this article helpful, please click the Yes button after the question just below. This will give me (the author) a few points and might encourage me to write more articles.

If you didn't or otherwise feel the need to vote No, please first leave a comment to give me a chance to answer and perhaps to improve this article.

Thank you!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=