4

I'm making a command line tool that allows end users to query a statically-schemed database; however, I want users to be able to specify boolean matchers in their query (effectively things like "get rows where (field1=abcd && field2=efgh) || field3=1234"). I did Googling a solution, but I couldn't find anything suitable for end users--still, this seems like it would be a very common problem so I suspect there is a standard solution.

So:

  1. What (if any) standard query "languages" are there that might be appropriate for end users?
  2. What (if any) de facto standards are there (for example, Unix tools that solve similar problems).
  3. Failing the previous two options, can you suggest a syntax that would be simple, concise, and easy to validate?
6
  • 1: SQL; 2: ANSI standard SQL; 3: SQL will be validated by whatever RDBMS you submit it to; most likely you'll get a helpful message back saying exactly where the problem was. If the schema naming convention is not end-user friendly define views to present appropriately and grant rights accordingly. Commented Jul 27, 2014 at 11:25
  • Thanks for the comment @MichaelGreen. I naturally considered SQL, but I think I want a read-only subset thereof. If possible, I would like to avoid manually enforcing that rule. I suppose most SQL implementations support read-only configuration, but I would like to return to the user a friendlier message than what the database is likely to yield and translating each low-level database error to a useful high level user error sounds like it would require in-depth knowledge of each sql backend I intend to use. Perhaps this is still the best solution, however...
    – weberc2
    Commented Jul 27, 2014 at 14:08
  • this is an off topic comment wrt the commandline/language stuff, but im wondering if you have already looked at any (foss) BI reporting tools if they meet ur requirement
    – dbza
    Commented Aug 14, 2014 at 2:57
  • Just wondering, what is statically-schemed? Does it consist of 1 or more tables? Why not simply analyze the user entered SQL Query string and ensure it starts with a 'Select' if you only want queries only?
    – NoChance
    Commented Aug 25, 2014 at 22:10
  • @EmmadKareem "statically-schemed" means the schema won't change after the db has been initialized. I'm not sure, but I'm guessing your "SELECT" filter proposal doesn't give me the read-only guarantees I'm looking for. If you can prove (i.e., offer a credible link) that any SQL string beginning with SELECT is guaranteed read-only, then submit it as an answer.
    – weberc2
    Commented Aug 25, 2014 at 22:17

1 Answer 1

2

This is called faceted search. There are tools like Solr that can help you do it, but you don't want to use a language unless your users are highly technical (like consumers of a web service API), in which case you just use something SQL-like.

For inspiration on user interfaces, look at search results of companies with huge product lines, like Amazon or Digikey. Basically after you start a search you are presented with a list of parameters by which you can refine your search, allowing you to drill down until you get the products you want. This kind of interface is probably so familiar to you that you may not even have realized you were effectively querying specific fields of a database.

Another example of end user searching based on many fields is bug tracking websites. They generally have a general purpose GUI-based search, and also a SQL-like query language for advanced searches. If your users are technical or semi-technical, that might be a good model to follow.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.