Item selection is case-sensitive

[jedmund]

When querying items with Siero (e.g. to set rate ups, spark targets, etc), Siero looks for exact matches in the database. As such, these commands are case-sensitive. This is inconvenient for most users, especially those on mobile.

[mafdoesthings]

So I wrote a better SQL query generation function to generate fuzzy matched queries, but I can't implement it into spark.js and gacha.js without some weird bugs cropping up. (running queries twice, mishandling unsanitized inputs.)

Features of the improved query/builder.

1. Improved accuracy of results. No more 30+ results for "io grand"
2. Hard cap at 9 results.
3. Input sanitization and case insensitivity
4. Sorting of most confident matches.

Things to be aware of:

ORDER BY is not compatible with the COUNT() function by itself. As such, when passing queries to the array to count rows, the first index in the array ( sql[0] ) needs to be 'SELECT COUNT(*)'

This can be improved by additional logic on the query inputs towards the last if/else statement. but wasn't necessary for current implementation.

Here's the function:

  buildQuery(name, sql) {

        var san = name.replace(/[^a-zA-z ]/g, "")

        var words = san.split(' ')
        for (var i in words) {
                if (i == 0) {
                    if (words[i].length >= 6) {
                        var op = '%'
                    } else {
                        var op = '~*'
                    }
                    sql.push(`(name ${op} '${words[i]}' OR recruits ${op} '${words[i]}')`)

                } else {
                    if (words[i].length >= 6) {
                        var op = '%'
                    } else {
                        var op = '~*'
                    }
                    sql.push(`AND (name ${op} '${words[i]}' OR recruits ${op} '${words[i]}')`)
                }
        }
        if (sql[0] != "SELECT COUNT(*)") {
            sql.push(`ORDER BY recruits <-> '${san}', name <-> '${san}'`)
            sql.push("LIMIT 9")
        } else {
            sql.push("LIMIT 9")
        }
        console.log(sql.join(" "))
        return sql.join(" ")
    }

And a usage example:

async fetchSuppliedTarget(name) {

        let sql = [
            "SELECT *",
            "FROM gacha WHERE"
        ]

        let query = this.buildQuery(name, sql)

        return await Client.one(query, [name])
            .then(res => {
                return res
            })
            .catch(error => {
                let text = 'Sorry, there was an error communicating with the database for your last request.'
                common.reportError(this.message, this.userId, this.context, error, text)
            })
    }

[mafdoesthings]

Ideally i'd like to wrap all query generation into this function, including a flag to run SELECT for specific columns (name, id ...) , and builtlins for common SQL functions like COUNT(). But until we know how it'll be implemented it gets a little more awkward to write.