Eight Queens in SQL [1]

The puzzle is to place eight queens on a chessboard so that no two queens attack each other. The problem is old and has been well studied, the Rosetta Code has solutions for more than 80 programming languages, SQL included.

However, the Rosetta’s SQL solution is implementing a recursive algorithm in SQL, as opposed to a relational approach. In this article I will use a relational design approach to the problem, and then translate that to SQL. The main idea is to design using natural language and concepts of domains, predicates, constraints, sets, set operations, and relations.

The logical design process is database agnostic; for the initial code samples I will use PostgreSQL, which can later be translated to other SQL dialects.

Domains

A domain is simply a set of all possible values over which a variable of interest may range.

The board is composed of 64 named squares, organised in eight rows and eight columns; rows and columns are labelled.

square_name = {'a1' .. 'h8'} -- set of square names
row_lbl     = {'1'  .. '8'}  -- set of row labels
col_lbl     = {'a'  .. 'h'}  -- set of column labels

chessboard

It is convenient to refer to a square by Continue reading “Eight Queens in SQL [1]”

PostgreSQL, Send More Money

It is often forgotten that the original idea which led to the development of SQL is the relational model and that it stems from (first-order) logic. The idea is based on concepts of predicates, facts, constraints, sets, set operations, logical operators, relations, relational operators, etc.

Hence, most problems that can be reasoned about in these terms can be easily implemented in SQL. The implementation from the reasoning to the code is often straightforward.

Consider a paradigm of constraint programming over finite domains: the common example for this is the puzzle SEND + MORE = MONEY where each letter represents a different digit.

   SEND
+  MORE
= MONEY

given S <> 0, M <> 0

It is fairly easy to reason about this problem in terms of sets, set operations, constraints, predicates, and logical operations; hence the translation from the reasoning to the SQL code should be straightforward.
Continue reading “PostgreSQL, Send More Money”

Bulk change post categories in WordPress

The following SQL code moves all WordPress posts from one category to another. Both categories must already exists in the database. The from_category is not deleted from the database, only posts are moved. Needless to say, do backup your database before trying this.
Make sure to specify category slugs (not names) in the first two lines of the code. Comments tell the story; you may also want to look at the database model.

Coral8 – Randomly Sampling a Stream

The example below randomly samples an input stream and inserts samples into a named window for later processing. A variable named Dice is “rolled” each time a new row enters the input stream. A row is copied into the window if Dice = 1.

-- Variable for random number
CREATE VARIABLE INTEGER Dice;
CREATE VARIABLE INTEGER DiceSides = 6;
 
-- Input stream
CREATE INPUT STREAM StreamIn
SCHEMA (
    Val INTEGER
);
 
-- Each time a row enters the input stream,
-- roll the Dice
ON StreamIn
SET Dice =
    TO_INTEGER(RANDOM() * TO_FLOAT(DiceSides)) + 1;
 
-- Some data entering the input stream
ATTACH INPUT ADAPTER RMG
    TYPE RandomTuplesGeneratorAdapterType
TO STREAM StreamIn
PROPERTIES
    RATE     = "20"
    ,ROWCOUNT = "600"
;
 
-- Named window for samples
CREATE WINDOW WR
SCHEMA(
    Val INTEGER
    ,Dice INTEGER
)
KEEP 150 ROWS
;
 
-- Randomly sample the input stream
INSERT
    WHEN Dice = 1 THEN WR
SELECT Val, Dice
    FROM StreamIn
;


Here is a result, out of 600 messages that entered the input stream, 96 ended in the window; I was using a six-sided die — not bad.