SQLite3：以编程方式确定列是按升序还是降序排序 [英] SQLite3: programatically determine whether a column is sorted in ascending or descending order

问题描述

假设我在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）; 
  

我可以发现索引中的哪些列如下：

  sqlite> pragma index_info（'ix_person'）; 
 0 | 2 | lastName 
 1 | 1 | firstName 
  

但这样做不要告诉我排序的列是按升序还是按降序排列。

有没有办法以编程方式确定这一点而无需重新解析 CREATE INDEX 声明？

解决方案

没有重新解析CREATE INDEX语句这真的很难;但是从 sqlite_master 表中获取CREATE INDEX sql代码并进行轻量级解析似乎相当容易。

既然你可以从您已经识别的 PRAGMA 中获取列名称，您需要做的就是 tokenize 该SQL并立即检查令牌（在CREATE INDEX sql代码中）是列名的标记。

可能会有 COLLATE somecollat​​ion （您可能也应该记录，因为正在使用的排序规则 毕竟非常重要），并且，可能在这两个可选标记之后，下一个将是ASC，DESC或其他（意思是在SQL中没有指定顺序）。

（（顺便说一下，你肯定也想知道索引是否是唯一的信息 - 你可以得到的那个要么解析，要么可能更好，使用表中的PRAGMA index_list，我想你无论如何都要做到获取索引名称）。 / p>

我认为您不应该假设索引文件是否考虑了排序（较新的降序文件格式）或不（遗留格式） - 取决于 PRAGMA legacy_file_format 。 ..并查询它告诉你新数据库会发生什么，而不是当前数据库发生了什么。所以，忠实地记录模式中的订购信息对我来说似乎是一个更强大的策略。

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?

解决方案

"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.

这篇关于SQLite3：以编程方式确定列是按升序还是降序排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！