At the risk of getting run off this site... Jira's search query widget, which allows in some sense nearly arbitrary SQL while providing syntax completion, making sure you don't run off the rails with actual arbitrary SQL, and supporting very deeply nested conditionals correctly is probably one of the most impressive things to me about that system. I just wish it was not such a staggeringly large pile of work to get to such a thing in other systems. Even if someone cites some sort of open source library that supports this, simply defining to that library what columns and operations you support would be a lengthy task of specification, refinement, and testing.

It'd be neat if you could let more users just have SQL but there's so many ways for that to go terribly wrong nowadays, with all those nice juicy SQL features that so many engines now support.

▲

sublinear 3 days ago | parent | next [-]

I've been using and frustrated by Jira for a long time, so I'd love to be pointed in the right direction if I'm wrong here, but JQL only lets you write severely limited "where" clauses that plug into something that probably isn't SQL at all.

You cannot use JQL to pull up anything but a list of issues. You cannot aggregate fields within those issues. Many fields aren't even an option. The built-in functions are garbage. Some are redundant. Some are named similarly but do wildly different things. You can't even use subqueries or regex. Is there documentation disproving me somewhere that I'm not aware of?

These are all huge problems with endless unresolved tickets on Atlassian's support pages that point out very good and very common use cases that people have desperately wanted for over a decade. The answer is always yet another plugin. Security and data integrity can't be the reason Jira search sucks since many of those plugins are buggy and have more access to the backend.

As far as I can tell "JQL" was a name devised by their marketing department to make it seem more powerful than it really is and otherwise prop up a nasty plugin ecosystem that nobody wants. I sometimes feel like my outlook inbox rules are more sophisticated and that's pretty sad.

▲

jerf 3 days ago | parent | next [-]

Count how many places have a search on a website that let you run "((A and B) or (C and D) XOR E) AND NOT F". It's not many.

The very complaints you are making are kind of my point; that you can even consider making them means they've put a lot of work into a search interface that few other search interfaces have. The vast majority of "search fields" I run into can't even do a single AND or OR; one does not get so far as to wish for those things because clearly we're miles away from them.

	▲	sublinear 3 days ago \| parent [-]
		It doesn't matter how many. Jira should be on the same level as the other tools it's commonly used alongside. GitLab has GLQL[1]. ServiceNow has CMDB[2]. Both offer more flexible queries on any data the users can see and can return arbitrary tables. The dashboards, reports, APIs, etc. are actually useful because of that. On Jira, all you get are lists of issues and dashboard widgets that are barely more helpful that the list of issues they obscure. This is expected to be all available out-of-the-box without plugins or other gotchas. Nobody should have to rewrite their scripts to run the same basic recurring jobs on another project or part of the company just because they're on a different instance and don't want to buy a plugin that is seldom used. The only reason anyone tracks projects with Jira is its familiarity. Hardly anyone uses the newer features that Atlassian has shifted focus towards. All anyone wants is the core UI, for that UI to not be so bloated and slow (and it's only getting worse), and a better JQL. [1]: https://docs.gitlab.com/user/glql/ [2]: https://www.servicenow.com/products/servicenow-platform/conf...

▲

jraph 3 days ago | parent | prev [-]

> JQL only lets you write severely limited "where" clauses that plug into something that probably isn't SQL at all.

That's right. JQL (Jira Query Language) is based on AQL (Atlassian Query Language), same as CQL (Confluence Query Language), and targets Lucene (IIRC), not SQL, and CQL and JQL are (I suppose!) translated to Lucene queries.

These query languages are a subset of what you can do with a Solr / Lucene query string (basically the same thing as Solr is a fork of Lucene; Lucene is what is behind Atlassian products' search features IIRC). If you squint a little, you can liken it to some limited SQL where clause without the joins and the sub queries, but the target is a search platform, not an SQL database.

AQL fields map to Lucene fields, and there are functions that are also (likely) translated to Lucene expressions or replaced with some stuff from the context (currentContent() for instance in CQL is replaced to the id of the current Confluence page, and parentContent() to the id of the parent of the current page - again, this is a guess).

I suppose they invented this family or query languages to provide something more high level / user-friendly than raw lucene query strings, or possibly didn't want to give full access to the Lucene search capabilities for some reason.

There are Maven packages provided by Atlassian to deal with AQL [1], but they are not open source (and I have not studied their code, disassembled or otherwise).

I have reimplemented an AQL parser [2] from scratch using their limited docs and guesses, and, with this, implemented a CQL to Solr query translator for XWiki, mapping Confluence features to corresponding XWiki features [3]. Because people are migrating from Confluence to XWiki and their content is sprinkled with CQL-based macros that we needed to make work.

If you are curious about how a CQL statement can be translated to a Solr query (although querying fields in XWiki, not Confluence), you can look at the tests [4].

I actually find AQL pretty elegant, although I'll admit I haven't used it much.

[1] https://mvnrepository.com/artifact/com.atlassian.querylang

[2] https://github.com/xwiki-contrib/cql/tree/master/aqlparser

[3] https://github.com/xwiki-contrib/cql/tree/master/query

[4] https://github.com/xwiki-contrib/cql/blob/master/query/src/t...

▲

pas 3 days ago | parent [-]

thanks for the links, and ... uhh, mad respect on the xwiki CQL feature! :)

a bit tangential, but isn't Solr (and ElasticSearch and probably others) all use Lucerne "indexes" (as in Lucene is a library like LevelDB)?

what's strange is that most JQL that people want would translate to the most boring Hibernate query (or whatever Jira uses for relational DB access)

	▲	jraph 3 days ago \| parent [-]
		You are totally right, I thought Solr was a Lucene fork, but it's actually built on top of Lucene, thanks for correcting me. Both are Apache projects.

▲

crabmusket 3 days ago | parent | prev | next [-]

Something I have been considering is a ETL pipeline that, for each customer in our system, writes only their data to a SQLite file. Then, just expose a full SQLite query facility on that file.

This only works when your customers are of a reasonable size (e.g. small businesses or individuals) but could provide arbitrary analytics power.

It's also a safe target for AIs to write sql against, if you're into that sort of thing.

▲

klysm 3 days ago | parent | next [-]

We do this with DuckDB, and leave the connection in read only mode, exposing the database over HTTP. SQL injection by design?

▲

crabmusket 3 days ago | parent [-]

Cool to hear I'm not off the deep end. Have you written anything up on this, or were you inspired by anything else? How has it worked out for you?

	▲	klysm 3 days ago \| parent [-]
		haven’t written anything up on it, but I wouldn’t use my anecdote as an indication we aren’t off the deep end together! At a high level, we use DuckDB as an in-memory OLAP cache that’s invalidated via Postgres logical replication. We compute a lot of derived data to offer a read-heavy workload as a product. Possibly the most dubious choice I tried out recently was letting the front end just execute SQL directly against DuckDB with a thin serialization layer in the backend. The best apart about it is the hot reload feedback loop. The backend doesn’t have to rebuild to iterate quickly.

▲

sixdimensional 3 days ago | parent | prev | next [-]

If you're writing out data for analytical read only use - go with DuckDb all the way, over SQLite.

	▲	crabmusket 3 days ago \| parent [-]
		Thanks, I will look into it!

▲

m3047 2 days ago | parent | prev | next [-]

I use a desktop system which has python as its scripting layer, and exposes data to python nodes using pandas.

(In case you're wondering, https://www.knime.com/)

▲

mschuster91 3 days ago | parent | prev | next [-]

alternatively, why not use database views? That way you can make sure that customers can only see their own data. Permissions can be handled with normal database users by only granting them SELECT rights on the views.

If you're afraid of users tanking performance, read replicas. As instantaneous as it gets, and no customer can tank others.

▲

crabmusket 2 days ago | parent [-]

I think my gut would feel safer having a serialisation boundary between the customer's queries and the primary data store. But yes in principle I don't see why this approach wouldn't work just as well. Food for thought.

	▲	mschuster91 2 days ago \| parent [-]
		The boundary is the read-replica, it cannot write back to the primary DB cluster. But yeah if you require absolute isolation of tenant data, aka even a compromise / local privilege escalation on the read-replica node shouldn't allow an attacker to read other tenants' data, no way around. Or... depending if your database layout allows, you might be able to achieve that with a per-tenant read replica server and MySQL replication filters [1] or Postgres row filters [2]. A sqlite db is effectively the safest option because there is no way to bypass an export step... but it might also end up seriously corrupting your data (e.g. columns with native timezones) or lack features like postgre's spatial stuff. [1] https://dev.mysql.com/doc/refman/8.4/en/change-replication-f... [2] https://www.postgresql.org/docs/current/logical-replication-...

▲

nijave 3 days ago | parent | prev [-]

Zuora (billing and payment SaaS) has a user facing SQL interface backed by Trino that works pretty well

▲

perching_aix 3 days ago | parent | prev | next [-]

I didn't know JIRA can assist you with writing JQL, looks like I'm lost in the UI hell. I always just click Advanced and then click what I want together. Although I only need this kind of functionality very rarely, to make up for other UI grievances, so w/e.

▲

giveita 3 days ago | parent | prev [-]

JQL is a very powerful tool. No one sets up Jira perfectly. Not at first. People use anything like a label: the epic, the release version. etc. And JQL let's you get around that in the short term and find stuff.