Does EOS DB support text-based queries?

Question

Can I query a table with an indexed string column a la SQL?

Is something like this possible for example:

SELECT * from <table_name> where <my_string_column> LIKE '%test%';

Answer 1

You would need to use an iterator and go row by row using custom code to do the check. Something like this:

string test = "test";
for (auto itr = idx.begin(); itr != idx.end(); ++itr) {
    if (itr->username.find(test) != std::string::npos) {
        // Do something with that user and then exit the loop
        break;
    }
}

Since the database tables are stored on RAM, this should run pretty quickly, but if there are too many rows you could end up hitting the transaction time limit, so I would recommend filtering first using the index to reduce the amount of rows that you would iterate through, like limiting it to only users of a certain type, or something like that. You can use lower_bound for that, for example (documentation).

Besides that, EOSIO allows separating the stored data in different scopes, so you might want to have users of one particular type or country or some other property stored under separate scopes, and that way each table would be smaller as well.