On 28 Mar 2016, at 1:27am, David Rayna wrote:
> I got excited when I saw the "indexed by" clause.
> A technique I used years ago with FoxPro & etc was to allow each user to
> create his own personal index of the same data.
> FoxPro allowed storing index files separately and on each user's own local C:
> drive.
> The index was on a complex computed "quality" value based on multiple table
> fields allowing each user to create his own ranking of the data based on his
> own input terms.
>
> In sqlite, would I give each user their own name for the index which he could
> create, drop and redefine as desired?
> How complex can the index value computation be? Would I need to implement it
> in C and access via sqlite source code?
> Can select ... indexed by work as a pre-filtering and sorting of the data
> for each user's own view which could be filtered more if needed?
There are numerous differences between FoxPro and SQL, and although I can see
why you would want these things in FoxPro your approach isn't useful in SQLite.
SQLite doesn't have a user model. If you have OS access to the database file,
you can read the database. If you have OS write privs to the database file,
you can make changes to the databases.
In SQL you can make many indexes (including partial indexes) to a table. You
could come up with a naming convention for these indexes that includes a
pretend 'user name' in the index name, but any 'user' could have thousands of
indexes.
In SQLite an index is a series of column names, with a direction (ascending or
descending) and a collation method for each column. A collation method is
usually something like NOCASE (indicating that upper and lower case don't
matter when constructing the index) but you can write custom collation methods
in C, and sometimes in whatever your programming language is (depending on how
its interface to SQLite works).
Simon.
