Business logic is supposed to be the part of the application where you deal with customer or user facing decisions and computations. It is often argued that this part should be well separated from the rest of the technical infrastructure of your code. Of course, SQL and relational database design is meant to support your business cases (or user stories), so then we can ask ourselves if SQL should be part of your business logic implementation. Or actually, how much of your business logic should be SQL?
Category “Yesql” — 31 articles
Sometimes you need to dive in an existing data set that you know very little about. Let’s say we’ve been lucky to have had a high level description of the business case covered by a database, and then access to it. Our next step is figuring out data organisation, content and quality. Our tool box: the world’s most advanced open source database, PostgreSQL, and its Structured Query Language, SQL.
Kris Jenkins cooked up a very nice way
to embed SQL in your
code: YeSQL for Clojure. The main
idea is that you should be writing your SQL queries in
.sql files in your
code repository and maintain them there.
The idea is very good and it is now possible to find alternative
implementations of the Clojure yesql library in
other languages. Today, we are going to have a look at one of them for
the python programming
A recent interview question that I had to review was spelled like this:
Find missing int element into array 1..100
Of course at first read I got it wrong, you have only one integer to look
for into the array. So while the obvious idea was to apply classic sorting
techniques and minimize array traversal to handle complexity (time and
space), it turns out there’s a much simpler way to do it if you remember
your math lessons from younger. But is it that much simpler?
In our previous article
Aggregating NBA data, PostgreSQL vs MongoDB we spent
time comparing the pretty new
MongoDB Aggregation Framework with the decades
old SQL aggregates. Today, let’s showcase more of those SQL aggregates,
producing a nice
histogram right from our SQL console.
When reading the article Crunching 30 Years of NBA Data with MongoDB Aggregation I coulnd’t help but think that we’ve been enjoying aggregates in SQL for 3 or 4 decades already. When using PostgreSQL it’s even easy to actually add your own aggregates given the SQL command create aggregate.
In our Tour of Extensions today’s article is about advanced tag indexing. We have a great data collection to play with and our goal today is to be able to quickly find data matching a complex set of tags. So, let’s find out those lastfm tracks that are tagged as blues and rhythm and blues, for instance.
In this article, we’re going to play with music related tags
PostgreSQL is an all round impressive Relational DataBase Management System which implements the SQL standard (see the very useful reference page Comparison of different SQL implementations for details). PostgreSQL also provides with unique solutions in the database market and has been leading innovation for some years now. Still, there’s no support for Autonomous Transactions within the server itself. Let’s have a look at how to easily implement them with PL/Proxy.
Let’s get back to our Tour of Extensions that had to be kept aside for awhile with other concerns such as last chance PostgreSQL data recovery. Now that we have a data loading tool up to the task (read about it in the Loading Geolocation Data article) we’re going to be able to play with the awesome ip4r extension from RhodiumToad.
In our ongoing
Tour of Extensions we played with
earth distance in
How far is the nearest pub? then with
hstore in a series about trigger,
first to generalize
Trigger Parameters then to enable us to
Auditing Changes with Hstore. Today we are going to work with
trigrams PostgreSQL extension: its usage got seriously enhanced in
recent PostgreSQL releases and it’s now a poor’s man
Full Text Search