I was recently wondering about some issue I had encountered between two tables. If tables undergo a lot of INSERT / UPDATE / DELETE statements, it may appear to be better to remove some constraints, at least temporarily for the loading of data. In this particular case, the foreign key relationship was permanently absent and … Continue reading How schema meta data impacts Oracle query transformations
Category: sql
This category contains posts about SQL in general or some RDBMS’s SQL dialect
jOOQ-meta. A “hard-core SQL” proof of concept
jOOQ-meta is more than just meta data navigation for your database schema. It is also a proof of concept for the more complex jOOQ queries. It is easy for you users to believe that the simple vanilla queries of this form will work: create.selectFrom(AUTHOR).where(LAST_NAME.equal("Cohen")); But jOOQ claims to be a "hard-core SQL library". A "hard-core … Continue reading jOOQ-meta. A “hard-core SQL” proof of concept
Development schema, production schema
Most of us separate development data from production data, physically or at least, logically (except maybe Chuck Norris (official website, no kidding!)). If you're lucky and you can afford multiple Oracle / other-expensive-database licenses, you might clone the same schema / owner name for every application instance on different servers. But sometimes, you can't do … Continue reading Development schema, production schema
SQL DSL’s in other languages
Like jOOQ, there are many other tools out there, that aim to implement SQL as an internal DSL in other languages. This one is particularly nice-looking. It's called sqlkorma, a SQL DSL for Clojure. A sample SQL statement taken from their documentation: (select users (with address) ;; include other entities based on ;; their relationship … Continue reading SQL DSL’s in other languages
The comprehensive SQL bitwise operations compatibility list
One of the rather nasty, off-the-beaten-track battlefields in SQL non-standards are bitwise operations. To my knowledge, they're not in any SQL standard (I've checked SQL:2008 drafts), but almost all databases support some form of bitwise operations. Here's the relevant MySQL documentation page, as an example: https://dev.mysql.com/doc/refman/5.5/en/bit-functions.html So we're talking about these operations: bit_count(), and (&), … Continue reading The comprehensive SQL bitwise operations compatibility list
Derby casting madness – the sequel
I have recently blogged about the general bind variable casting madness in SQL: https://blog.jooq.org/rdbms-bind-variable-casting-madness/ So this is the sequel of the above story, purely dedicated to Derby with its "conversion table from hell". One of jOOQ's goals is to make SQL as compatible as possible across various databases, in a way that you can re-use … Continue reading Derby casting madness – the sequel
Liquibase for DB Migrations
I have just now discovered a very nice-looking tool for database migrations: Liquibase https://www.liquibase.org/ With Liquibase, you can model your DB increments as XML files that will translate to as many as 13 different databases. A sample DB increment (taken from the Liquibase manual): property='vat' ...and so on. I guess it's about time to contact … Continue reading Liquibase for DB Migrations
jOOQ and Google Cloud SQL Example
This is all too simple. Here's how you can create an easy jOOQ / Google Cloud SQL integration example: Sign up with Google App Engine Sign up with Google Cloud SQL Create a Google App Engine project (preferably with Eclipse) Add jOOQ to your project Add your generated schema to your project Done Google Cloud … Continue reading jOOQ and Google Cloud SQL Example
Cloud Fever now also at Sybase
After SQL Server (SQL Azure) and MySQL (Google Cloud SQL), there is now also a SQL Anywhere database available in the cloud: http://www.sybase.ch/fujibeta It's called Sybase SQL Anywhere OnDemand or code name Fuji. I guess the connotation is that your data might as well be relocated to Fuji. Or your DBA might as well work … Continue reading Cloud Fever now also at Sybase
What are procedures and functions after all?
Many RDBMS support the concept of "routines", usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are: Ada BASIC Pascal etc... The general distinction between (stored) procedures and (stored) functions can be summarised like this: Procedures: Are … Continue reading What are procedures and functions after all?