Using COLLATE on peewee queries

Question

I found in the peewee documentation how to create custom collations, but I wasn't able to find how to use the built-in sqlite collating sequences.

Ho do I create the following query in peewee (it is the last one in the above mentioned sqlite documentation page)?

SELECT x FROM t1 ORDER BY c COLLATE NOCASE, x;

And how do I specify the collation for an index?

CREATE INDEX i1 ON t1(f1 COLLATE NOCASE);

EDIT

The answer from coleifer addresses the question about the query.

For the index creation I am using the following trick, which works well when you create the indexes only once at startup (like in my app).

The case insensitive unique index on two columns on the table LockedFiles prevents duplicated entries.

class LockedFiles(PeeweeModel):
    folder = peewee.CharField(index=True)
    file   = peewee.CharField(index=True)

    @classmethod
    def custom_init(cls):
        db.execute_sql('create unique index if not exists lockedfiles_unique '
                       'on lockedfiles(folder collate nocase, file collate nocase)', {})

def create_tables(drop_existing_tables):

    for table in [LockedFiles, Model2, Model3]:

        if drop_existing_tables:
            table.drop_table(True)

        table.create_table(True)

        try:
            table.custom_init()
        except:
            pass

create_tables(drop_existing_tables=False)

Answer 1

You can specify a collation by building up the SQL clause and passing it to order_by().

For example:

collated = Clause(MyModel.field, SQL('COLLATE NOCASE'))
MyModel.select().order_by(collated, MyModel.other_field)

For the index unfortunately you will need to create that by hand as peewee does not know how to add collation information to the CREATE INDEX SQL. If you'd like to open a pull-request I would definitely consider merging that feature, though.