Could we add search indexes?

[jappeace]

A client of mine has a bunch of constraints on the table that are simply 'key' (mysql). I wouldn't know how to model that in a migration, I believe they're just search indexes: https://stackoverflow.com/questions/924265/what-does-the-key-keyword-mean

I think postgress supports this too, by manually creating an index: https://devcenter.heroku.com/articles/postgresql-indexes And I know mongo does: https://docs.mongodb.com/manual/indexes/

So I'm not asking for a unique constraint, just a way to express an index in the migrations to make queries go faster.

[parsonsmatt]

This would be great to have! A rough implementation plan would look like:

• Create a new type IndexDef. This type would be exactly the same as a UniqueDef, but would not imply uniqueness.
• Create a new data type data Index = Unique | Search.
• Add entityIndexes :: ![IndexDef] to the EntityDef data type. This would be a breaking change.
• Update the QQ parser to recognize the Index data type after the fields of an index are listed. If nothing is present, default to Unique. It should output an Either UniqueDef IndexDef instead of a UniqueDef.
• Add functions in the various persistent-* libraries that do migrations to generate indexes for this.

[jappeace]

Seems pretty do-able, I may look into that once I'm ready to adopt the table in question fully.

[parsonsmatt]

You can do this manually with raw SQL and making your own migrations -

keyMigrations :: Migration
keyMigrations = do
  addMigration True "CREATE INDEX IF NOT EXISTS ON user (name)"
  -- etc

See the functions here for more info

You can probably even write mkSearchIndex :: PersistEntity entity => EntityField entity field -> Migration to make the transition easier, though I'd have to study the docs a bit to see how to get there.

[jappeace]

Hey that's a good idea! Especially since I don't expect my Haskell code to do anything with the fact there is an index or not, so we shouldn't need to model that in the QQ.

You can probably even write mkSearchIndex :: PersistEntity entity => EntityField entity field -> Migration

Do migrations 'know' about back ends? I could at least pull out the name from the fieldDef, but the rest of the query would be mysql only.

[parsonsmatt]

SqlBackend's migration is only based on an EntityDef at the moment, so it's not really sufficient for this right now.

[TomMD]

Related to this issue, the Haskell mongoDB index creation is broken so any attempt at building and testing this feature will be blocked at least on that backend.

[parsonsmatt]

FWIW: I am opposed to putting this in the QQ DSL. I'd much rather write some documentation and show how the Migration type is actually a monad and can be composed nicely.

share [mkMigrate "migrateAll", ...] [persistLowerCase| ... |]

runMigrations :: SqlPersistM ()
runMigrations = runMigration $ do
  migrateAll
  createSearchIndex UserName

Adding createSearchIndex :: (PersistEntity rec) => EntityField rec typ -> Migration should not be terribly difficult.

[Vlix]

This sounds like it'd be pretty nice to have. I might want to pick this up sometime soon...

[joeyh]

There may be something that prevents using the addMigration technique with persistent-sqlite. I'm getting:

Database migration: manual intervention required. The unsafe actions are prefixed by '***' below:

*** CREATE INDEX IF NOT EXISTS idx_cid ON content_identifiers (cid);

I used addMigration True so it's not marked unsafe and it seems it must be failing to parse it. Pasting the same statement into sqlite3 successfully creates the index.

[parsonsmatt]

Ah, the Boolean blindness bites again - True means unsafe.

[sorki]

Ah, the Boolean blindness bites again - True means unsafe.

Just got bitten by that as well and the docs state

Bool: Is the migration safe to run? (eg a non-destructive and idempotent update on the schema)

(EDIT: only the old version of docs, it is actually fixed in most recent one)

~So it seems that True should mean safe,~ anyway an enum like Safe | Unsafe would be easier to reason about I think.