SQLite3: programatically determine whether a column is sorted in ascending or descending order

Question

Suppose I have an index on a table in SQLite3:

CREATE TABLE Person (id integer primary key, firstName varchar(20), lastName varchar(20), address varchar(200));
CREATE INDEX IX_Person ON Person (lastName ASC, firstName ASC);

I can discover which columns are in the index like this:

sqlite> pragma index_info('ix_person');
0|2|lastName
1|1|firstName

But this does not tell me whether the columns sorted are in ascending or descending order.

Is there a way to determine this programatically without re-parsing the CREATE INDEX statement?

Answer 1

I do not think that the sort-order has conceptually anything to do with indices. AFAIK it is part of ANSI-SQL, but is mostly ignored by most RDBMSs (like Oracle).

This seems to be the case for older versions of SQLite as well (from the SQLite documentation):

Each column name can be followed by one of the "ASC" or "DESC" keywords to indicate sort order. The sort order may or may not be ignored depending on the database file format. The "legacy" file format ignores index sort order.

At least according to the documentation, the index-info pragma does not help you in determining the sort order within the index. So it looks as if you are out of luck ...

Answer 2

"without re-parsing the CREATE INDEX statement" it's really difficult; but getting the CREATE INDEX sql code from the sqlite_master table and doing lightweight parsing seems reasonably easy.

Since you can get the column names from the PRAGMA you have already identified, all you need to do is tokenize that SQL and check token(s) immediately-following (in the CREATE INDEX sql code) the token that's the column name.

There will be a possible COLLATE somecollation (which you should probably also record, since what collation is in use is pretty important after all), and, possibly after those two optional tokens, the very next one will be ASC, DESC, or something else (meaning no order is specified in the SQL).

((BTW, you no doubt also want the info on whether the index is unique -- that one you can get either by parsing, or, probably better, with "PRAGMA index_list" on the table, which I imagine you're already doing anyway to get the index names)).

I don't think you should make assumptions about whether index files take ordering into account (the newer "descending file" format) or not (the legacy format) -- that depends on PRAGMA legacy_file_format ...and querying it tells you what will happen for new databases, not what's happening for the current DB. So, faithfully recording the ordering information from the schema seems a much more robust strategy to me.