Google+ Followers

zaterdag 28 februari 2015

Why I like Python better than PHP: Bingo!

From the real world: How do you create a 6x6 array where every row contains six random numbers  in  the range 1-19, 20-29, etc.

In Python you pretty much do what the problem description says:
for each set of ten numbers pick a sorted set of 6 random numbers:

import random
data=[]
for t in range(10, 70, 10):
data.append(sorted(random.sample(range(t, 9+t), 6)))


In PHP things are much more complicated because the endless list of inconsistent behaviour.
range() creates an array of values, that's ok.
array_rand() picks random values from the array? No, it picks random KEYS from the array, which means you have to loop through the keys to get the values, which is bull. You can flip the array, but wtf?
So you shuffle the array, but shuffle modifies an array, it does not return a new array, so the rnge() must be materialized into a variable.
Ditto for the sorting.

Put it all together and you get this:

<?php
$data = [];
foreach (range(10, 70, 10) as $t) {
$tmp = range($t, $t+9);
shuffle($tmp);
$tmp = array_slice($tmp, 0, 6);
sort($tmp);
$data[] = $tmp;
}
?>

Ouch.

vrijdag 13 februari 2015

A lecture on NoSQL, by Eric Redmond

A lengthy talk that explains the differences between NoSQL and SQL, and the FUD that exists around them.



donderdag 12 februari 2015

Why I like PostgreSQL more than MySQL: DELIMITER ;

MySQL supports triggers, and that's good.

One common use for triggers is to copy data from a table to an audit table to track changes made to the table. Something like:

CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END;


Great! Except, that doesn't work: syntax error at or near '' at line 6.

What that means is: "There is something missing on that line".

The missing bit is the END statement, but that's not  missing, it's right there on the next line. Yes, but the semicolon that ends the INSERT also ends the CREATE statement. MySQL can see the CREATE and the BEGIN, but it is not smart enough to look for the END.

So how can you solve this problem? Well it's simple really; you just have to change the default query delimiter from a semicolon to something else that doesn't appear anywhere in your query. A double dollar sign for example. (sound familiar?) Then you replace the semicolon that ends the CREATE with the new delimiter, and then reset the delimiter back to a semicolon:






DELIMITER $$
CREATE TRIGGER `trg_member_audit` AFTER UPDATE ON `member` FOR EACH ROW BEGIN
INSERT INTO member_audit
(sql_action, id, created, name, email)
VALUES
('UPDATE', NEW.id, NOW(), NEW.name, NEW.email);
END$$
DELIMITER ;

Note that the DELIMITER statement does not end with a delimiter and that the delimiter is not quoted, that would be crazy...

How does PostgreSQL do this?
In PostgreSQL a trigger always executes a stored function and the CREATE FUNCTION statement has solved the problem by simply forcing you to put a delimiter at the beginning and at the end of the SQL that is part of the function:

CREATE FUNCTION myfunc RETURNS trigger AS
$$
BEGIN
  RAISE EXCEPTION 'This triggerfunction is empty' ;
  RETURN NEW;
END;
$$
LANGUAGE plpgsql;

The delimiter is usually just two dollar signs, but if your code itself contains a double dolllar sign you can add text between the dollarsigns, the interpreter will read the first delimiter and keep processing until it find the exact same delimiter again.

CREATE FUNCTION myfunc RETURNS trigger AS
$my-code$
BEGIN
  RAISE EXCEPTION 'This trigger has no $$ sign!' ;
  RETURN NEW;
END;
$my-code$
LANGUAGE plpgsql;