Question: how to add an index?

[AndroidDeveloperLB]

I've noticed this function:

Index[] getIndexes()

But how do I set it?

Suppose I have this spec:

@TableModelSpec(className = "SomeSpec ", tableName = "someTable")
public class SomeSpec {
    public long time;
    public String name, number, key;
}

how do I set an index for the "number" field? And how do I set the field to be unique?

[sbosley]

One of two ways. You can either create an implicit index using a table constraint, like so:

@TableModelSpec(className = "SomeSpec", tableName = "someTable",
    tableConstraints = "UNIQUE(number)") // Add optional conflict resolution clause

The tableConstraints field is a clause of raw SQL you want your table to be created with, so you can refer to the SQLite documentation for what kinds of constraints you can specify.

Or, you can create an explicit index using the function you mentioned:

// In your SquidDatabase subclass:
private static final Index someSpecNumberIdx = SomeSpec.TABLE.uniqueIndex("indexName", SomeSpec.NUMBER);

protected Index[] getIndexes() {
    return new Index[] {
        someSpecNumberIdx
    };
}

Personally I'd go for the implicit one using the table constraint, but the choice is yours!

[AndroidDeveloperLB]

Thank you. About unique fields, you mean that in order to set it, I don't really need to specify it in the spec file, with "TableModelSpec" ? I can put it in the index-creation function instead?

And, add it to the "TableModelSpec", will automatically create an index?

[jdkoren]

If you use tableConstraints = "UNIQUE(number)", this adds a uniqueness constraint on the column named number, SQLite will automatically index it, but this is an internal index that you cannot reference by name in any of your SQLite commands. If instead you use SomeSpec.TABLE.uniqueIndex(...) and add it to your SquidDatabase subclass, it will create an index that you can reference by name, if that's something you desire.

[AndroidDeveloperLB]

I don't think I need to use the index by reference, because as I remember, it's supposed to be used automatically when performing DB operations that involve it (query, update, delete).

I'd like to ask though: what's the difference between the "uniqueIndex" function you mentioned, and adding this instead:

    @Override
    protected Index[] getIndexes() {
        return new Index[]{
                new Index("number", SomeSpecEntity.TABLE, true, SomeSpecEntity.NUMBER)};
    }

As I see "true" is for the uniqueness. BTW, you have a typo for the first method of solving this : supposed to be "tableConstraint" .

[sbosley]

There's not really any difference. The uniqueIndex function simply calls that constructor.

Sounds like you're leaning towards using a table constraint, but if you do decide to declare the index yourself, one thing to note is that I'd recommend declaring any indexes you make manually as their own static constant objects outside the getIndexes() method. This would make the indexes easier to create in database migrations (getIndexes() is like getTables() -- it tells the db what things to create for a new database but you still need to write migrations for existing ones).

[AndroidDeveloperLB]

You mean it's better to use "tableConstraint" ?

[sbosley]

It's not always "better", it just depends entirely on your use case. But yes, to me it sounds like in this case you probably would be better off using the tableConstraint.

[AndroidDeveloperLB]

ok, thank you. Maybe you should add those things into the wiki.