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 ncreate table numbers(n integer not null, primary key(n asc));-- insert generated sequential integersinsert into numbers(n)select rowid from (-- build 1,000,000 rows using Cartesian productselect 1from ( 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;
Hopefully the above speaks for itself, but here is a look at what it is doing:
The innermost query uses the same principles chapmandew applied in his Quickly Generate a Number Sequence in SQL Server article which is a cross between Mark's article on the undocumented master..spt_values in MS SQL and Brandon's write up on creating numbers utility: Cartesian product. Not having a table like spt_values (afaik) in SQLite nor recursive common table expressions at my disposal in concert with my aversion to writing out 1,000 union selects, 6 derived tables of 10 rows each are cross joined. If you have a table in your system with a small series of numbers, that can be used here instead, but this works and performance not really an issue since doing this one time.
Using the Cartesian product as a derived table itself, the outer select utilizes a special 64-bit signed integer column rowid available to all tables in SQLite. When selecting from a standard table, you can simply "select rowid from your_table_name", but here we had to use a derived table since our original query wasn't just a simple select but instead a cross join of multiple tables.
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.
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.
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:
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:
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:
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 ainner 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;
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.
Note: At the time of writing this, SQLite doesn't seem to have any loop capabilities outside of scripting using shell or other languages capability of using its C-based libraries. Therefore, this is a bigger advantage as it allows what may not otherwise be possible in pure SQLite query syntax.
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.
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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.
The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…