The first example I can think of is the way you set the size of the result set in for the different databases (TOP vs. LIMIT vs. ROWNUM). I mean having one client to rule them all is good, but in my experience the harder problem is to learn all the different dialects depending on what database you use.
One of the most difficult projects I've done, but it worked reasonably well, for most basic functions. We struggled with custom platform specific features of course, but even figured out workarounds for them. Of course, if a database didn't support a base level of the SQL standard (say SQL-92), for example, Cassandra - it was impossible for the translator to take a complex expression and handle it on its own - there was no target syntax to translate to.
Anyways, point being - been there tried this, and it is possible. An open source implementation of what I did would have been awesome, alas, it was for a proprietary project.
And ultimately, if you want one specific syntax, unless you find creative solutions around missing features between platforms (possible but much different problem that goes beyond syntax) - it is never quite as powerful for each platform.
I still think every day about starting some kind of open source SQL gateway client project to normalize it and sit in front of databases. It's a big project though, and like I said, I question the value sometimes.
Normalizing the syntax for databases that support SQL >=SQL99 (or even 2003 standard) would not be too bad though.
There is an international SQL standard from ISO, it's just not commonly followed.
However, sometimes the standard isn't very useful in itself. For this example, FETCH FIRST x ROWS ONLY is the syntax mandated by the standard. Although some databases accept this in the meanwhile, LIMIT might have been a better choice as it is supported by more databases.
Edit: ps.: Please don't use w3schools.com as a SQL reference. It's utterly outdated, prefers vendor syntax over standard and is sometimes just straight wrong.
You also have to keep in mind that ANSI SQL is a pure relational language. It's artificial in many senses. There are essentially no details of implementation in ANSI SQL. These implementation details include everything from a modular database engine like MySQL, multiple index algorithms or clustering options like PostgreSQL, deep features like external procedural languages extensions, and so on. None of these type of features are considered by the standards body. It's entirely left up to the vendors to create them. Well, a lot of features like these end up becoming standardized precisely because they become popular ways to solve problems. Statements like BACKUP DATABASE and RESTORE DATABASE are not a consideration of standard SQL because that is considered an implementation detail. Point in time recovery and log file handling are all different.
Other features like string aggregation, JSON and XML support, etc. all began as vendor extensions before they were standardized. Yes, sometimes the standard will create a new extension that no vendor supports or only minimally supports like the new MATCH_RECOGNIZE() expression. But for the most part it's the standard that lags behind and takes cues from the vendors.
Even then, the results of standard SQL sometimes are not adopted by vendors because nobody needs the features the standards body invents. Indeed, the standards body has sometimes standardized what would later be identified as bad practices. Elements like NATURAL JOIN or KEY JOIN (I think that's in the standard) are considered bad design since they result in ambiguous column joining when you start adding multiple tables or modifying the schema.
This is basically the exact same issue as XHTML vs HTML5 and browser vendors. It's not quite as bad since there's no W3C vs WHATWG political garbage (I can't imagine how bad it'd be if ANSI and ISO didn't cooperate) but it's the same kind of thing. Vendors are in competition and want to provide features that developers need. They're not going to wait for the ISO joint technical committee to decide what to do.
The real pain comes when you try comparing the old vendor specific OUTER JOIN syntax for Oracle and SQL Server. I guarantee you'll love JOIN ... ON ... over comma joins.
FROM T1, T2
WHERE T1.PK1 = T2.FK1(+)
FROM T1, T2
WHERE T1.PK1 *= T2.FK1
FROM T1, T2
WHERE T1.PK1(+) = T2.FK1
FROM T1, T2
WHERE T2.FK1(+) = T1.PK1
FROM T1, T2
WHERE T2.FK1 = T1.PK1(+)
Yeah. Fuck that. I'll take this any day:
LEFT JOIN T2
ON T1.PK1 = T2.FK1
Even Oracle's own documentation tells you to avoid the (+) syntax:
> Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to [several restrictions], which do not apply to the FROM clause OUTER JOIN syntax[.]
The doc itself lists all the issues, which is about 10 of them. None of these are opinions, either. They're actually technical limitations with (+), though they may not be ones that you encounter. The only advantage I know of for the (+) syntax is some uncommon issues with materialized view optimizations.
Hint: its not related!
for at in dir(object_name):
Definitely a project to follow, though.
> usql is a great tool if you're familiar with Postgres' psql client and wish you could use it for other databases like MySQL, Cassandra etc.
> dbcli tools are designed to preserve the usage semantics of the existing tools but improve on them by providing auto-completion. For instance you can use `\d` in pgcli and `SHOW TABLES` in mycli. This was a conscious decision to make pgcli and mycli drop in replacements for of MySQL and psql. I was also working under the assumption that people rarely use multiple databases, you're either a postgres shop or a MySQL shop. If you have a mix of both, there is a good chance that not a single person is interacting with both of them on a daily basis. You have different teams using different databases. But my reasoning there could be flawed.
> There is nothing stopping someone from adding an adapter to the usql tool to make it behave like MySQL (because they like the mysql client better) based on a command line argument, for instance.
Nobody would write "Universal C++ CLI ...".
However modern languages have a lot of trade offs in deployment strategies that are just easier to say by stating the language.
Golang is normally fully statically compiled so getting this tool on a host requires nothing other than coping the completed binary. It often requires nothing at all from the system it is running on. This is an implied feature of the language.
C/C++ code can be that simple, but static binaries are not always possible. And then you require the libaries on the remote machine.
There is a good chance Python will be on a remote machine, but deployment could be complicated. Psql support requires the the libaries to be on that machine for example.
And if the tool was written in node, a ton dependencies and work would be required for a normal user to use it.
I agree that the language should not be a feature, but it is. Containers/snap/flatpak sorta help with this, but they are even more work for the user currently.
A Java based tool requires installation of a JVM and likely has a non-trivial startup time. A Python / Ruby tool requires a runtime and probably some kind of mucking with virtualenv or rvm to work properly. A Node.js tool would require (haha!) a Node runtime and crossing your fingers that the NPM dependencies for what you're going to use to connect to your database haven't been compromised since the last build.
A Go based tool would be statically compiled, start up quick, and not have any external dependencies.
There will hopefully be a ripgrep-related blog post in the near future that will feature Rust quite a bit, but that will be about a library, not a tool.
 - https://blog.burntsushi.net/ripgrep/
I do encourage people in the Rust community to have the confidence to just be in Rust and let people find it out, like you did. There's good reason in the early days to wear the language on your sleeve, but there's also good reason as time wears on to project quiet confidence. Rust is, in my opinion, quite solidly there, at least relative to HN.
For me "in go" is useful information since I infer "easy to deploy" from it
If you look on the GH read me the Golang attributions are not quite as prominent.