[odb-users] Index Pragmas DO NOT support index on expressions

[Reilly He]

Hi Boris,

Thanks a lot for your quick response.

So there is still a possibility of, say, renaming the id member but
> forgetting to update the index definition.
>

I got to say I am a little bit confused about this statement, since I take
a look at the docs of SQLite Alter Table
<https://www.sqlite.org/lang_altertable.html>,
and here I quote:

The RENAME COLUMN TO syntax changes the column-name of table table-name
> into new-column-name. The column name is changed both within the table
> definition itself *and also within all indexes*, triggers, and views that
> reference the column. If the column name change would result in a semantic
> ambiguity in a trigger or view, then the RENAME COLUMN fails with an error
> and no changes are applied.
>

Actually I`ve done my experiment today, and the saying of SQLite was *TRUE*,
so things like changing the column name actually will not fail any index
including the changed column.

but according to your statement, are you saying that if we already add an
index using ODB`s index pragma, and we somehow *changed the member name*
(column name), the compiling process of ODB will *fail*?

If you would like, we can add this support to the index pragma.
>

I`m not in a rush, since we decided to implement this index using the
SQLite way, but it is always good to have it LOL, since some optimization
will be pretty tricky if we do not have this.

Best Regards,
Reilly He

On Fri, Feb 17, 2023 at 2:40 PM Boris Kolpackov <boris at codesynthesis.com>
wrote:

> Reilly He <reillyhekazusa at gmail.com> writes:
>
> > I found it really hard to create this index in the context of ODB index
> > pragmas. I looked through the manual:
> > https://www.codesynthesis.com/products/odb/doc/manual.xhtml#14.7
> > And I did not find any solution.
>
> No, I don't think there is way to achieve this via the pragma currently.
>
>
> > So if ODB does not support creating indexes on an expression, I can do
> that
> > on my own using SQLite CREATE INDEX pragma.
>
> Yes, this is probably the only way, currently.
>
>
> > But, this is not included in the ODB migration, which leaves a very
> > unstable point in our project. Since if somebody in the future changes
> the
> > schema in the Post table, this part might fail.
>
> Yes, that's a valid concern. However, even if we were to add support
> for this in the pragma, it will be just as a raw column name (or column
> expression). Something along these lines:
>
> class Post
> {
>   int id;
>
>   #pragma db index("id_i") column("abs(id)")
> };
>
> So there is still a possibility of, say, renaming the id member but
> forgetting to update the index definition. Though having the two
> next to each other definitely reduces this likelihood compared to
> creating the index manually.
>
> If you would like, we can add this support to the index pragma.
>