Monday 30 December 2013

Libeay32.dll Error Messages

There are situations where a program would display an error of the type "Libeay32.dll Not Found" or "This application failed to start because libeay32.dll was not found. Re-installing the application may fix this problem." I had this problem with SpeedFan, for example, which would display this error on startup, but then start without a hitch after closing the popup. There are a lot of solutions on the net, but the safest and quickest (for Speedfan and probably most other programs) is to do the following:
  1. Obtain a copy of the libeay32.dll file
  2. Copy the file in the Speedfan folder in Program Files
  3. Restart Speedfan
. You might prefer to reinstall the offending program, which is probably the best general solution, but you could lose some custom configurations or data or, as in the case of Speedfan, it might not work!

As for the obtaining, you probably have several copies of the library on your computer anyway. Just search for it in Program Files, I had a bunch of them and chose the one that was newest. You could copy the file thus found in Windows\System32, but that would not be the best idea, as it might interfere with all programs looking for the library, so it is safer to copy it only in the folder of the program that causes the error to occur.

For an explanation, I understand libeay32 is an OpenSSL library, something that is free, open source and used for Secure Sockets Layer - a protocol widely used on the Internet. Therefore you will find all kinds of versions, in all kinds of bundles. It might even be a malware, in some cases, so make sure it isn't before copying it around ;) Also, the reason why Speedfan would start anyway, even without the library, is that it was used only in the context of sending secure mail, a feature I have never used.

Friday 27 December 2013

Dark Universe, by Daniel F. Galouye

Book cover I read about Dark Universe online, in a "best" sci-fi book list from somewhere. Richard Dawkins recommended it as a very good book and one of his favourites. I can see why the book would appeal to Dawkins, perhaps he even read it when he was a child. The idea is that the book is classical pulp fiction; the characters are simple and undeveloped, the logic strained and the science only consistent with the times in which it was written. At first, when I started reading, I was captivated by the world of people living underground after a nuclear apocalypse, but then I started getting more and more annoyed with the leaps of logic and superficial characterisation. I thought it was a book written by a teenager, like Eragon maybe, but instead it was written by a grown man in the 50s. When I learned about this I understood more of why the book existed at all and why people seemed so... stupid and onetracked. The ending, something that almost offended me, not by its quality - which wasn't good to begin with, but by its implications, is classic 1950 "scientific" thinking. The hope of humanity as small minded arrogant assholes.

Bottom line, it is a simple and easy to read book, in a bad way. The science for it is lacking, the characters are simplistic and the plot classic pulp (prince and princess kind of crap). Too bad that a good initial concept was wasted by a mediocre writer in a mediocre time.

Thursday 26 December 2013

The City and the City, by China Mievile

Book cover I have not read any of China Miéville's works, but I have received a positive recommendation from a friend and decided to read The City and The City, thinking that I would read a science-fiction story. Instead, it is a fiction filled police procedural - a detective story - placed in a city that is, at the same time, part of two different countries. There is a detective, set on finding the murderers of a young girl, who during his investigations takes the reader through the internal workings of this weird place and leaves us with the concept that we all, as city dwellers, are silently and cooperatively complicit in the evil perpetrated around us. Interesting, indeed, but quite a shallow concept to be transformed into a book, even one written by such an obvious talent as Miéville. There is also a sad reason why he wrote the book such, as a present to his terminally ill mother who enjoyed detective stories.

But back to the book. It is fascinating to observe a place where an establishment, a person or even an object are part of one city or another based on physical characteristics such as certain colours, a certain gait or a certain way to make a common gesture. This idea is the soul of the book and the rest just a pretext to explore it. Anyone breaking the boundaries between the two cities is immediately and absolutely punished by a shadow entity called Breach, which appears next to anyone even focusing too long on a place from the other city and maintains the "skin" between the two different nations. Miéville does not explain, really, what caused such a split, why Breach was formed and even how it does what it does (and indeed, how it did the same thing for thousands of years). The point of the book is not to root into reality the concept of this shattered place, only to explore its possibility. And it does this skilfully. The issue I had with the book is that, except for this brilliant and original idea, you are reading a police procedural, plain and simple. I was in the mood for something else, perhaps.

My conclusion is that it is a very well written book, one that is worth reading, but not something that could be considered brilliant except for the seed idea. Outside that idea, which has been pretty much detailed in this post, the plot is a standard detective story.

Tuesday 24 December 2013

Druid's Blood, by Esther M. Friesner

Book cover I was first directed to Esther Friesner by the excellent audio reading of The Shunned Trailer, a short story which humorously and skilfully combines Ivy League competitiveness with Lovecraftian mythos. You can (and should) listen to it on the also excellent podcast site EscapePod, which together with its sister sites Pseudopod and Podcastle provide free weekly audio stories from the genres of sci-fi, horror and fantasy, respectively. The story itself was so amusing to me that I actually laughed out loud, which - for those so blissfully unaware of it - is not the same thing as LOLing. I made a short foiree into some Lovecraft stories, then proceeded on reading something of Friesner's.

Druid's Blood is an alternate universe Sherlock Holmes story. The "alternate" in the universe is an England where the druids repelled the Romans by using magic and then went on protecting the British isles with a magical shield that prohibits the entry of - for lack of a better word - contraband. This includes, for example, steel. It is an interesting modern bronze age world in which the druids are the highest religious order, everything is run by magic, technology is pretty much forbidden, but the Brits still have their high ideals, the monarchy and Sherlock Holmes. The irony is thick when the work comes from an American author.

Anyway, I don't want to spoil the story; you have to read it for yourself, but I recommend it highly. The first chapter is not so good, so I suggest you go through it even if you are not terribly enthusiastic about it. The rest, though, made me not let the book out of my hands - to my wife's chagrin. Not as funny as The Shunned Trailer (after all, it was not intended as a parody) it combines several famous ideas and characters with this twisted history of a magical Britain. The book is not meant as an exploration of history, though, as the characters and references are not really meant to have been contemporary or explainable by small tweaks in the time stream. I liked the book, although I don't know if I want to read more of the author right now. She is certainly smart and funny, but even if I enjoyed the book tremendously, it couldn't reach the level of good fun and concentrated smarts that The Shunned Trailer seemed to be. As such, I recommend reading the book first, then listen to the podcast of The Shunned Trailer. Perhaps in this order, the pleasure level will be higher.

There has been a lot of discussion on the changing of the names of Sherlock Holmes and doctor Watson. Whether it was a form of respect to the original characters to change their names if you change their entire world or whether it was a copyrighting issue or some other motive, I see no reason to dwell on the matter. After all, the epilogue is a tip of the hat to doctor Sir Arthur Conan Doyle, who invented a character of such incredible skill and intelligence, only to relegate his own role to the faithful sidekick. Enjoy the book.

Sunday 22 December 2013

At the Mountains of Madness, by H.P. Lovecraft

Cover of the Astounding Stories science fiction magazine where the novella was first published It is difficult for me to not admire Lovecraft and at the same time just as difficult to fully enjoy his stories. He was admirable because he was a true horror writer, haunted by his dark visions so completely that a lot of his stories are set in the same world, undoubtedly filling his mind at all times. And it is difficult to enjoy his stories because the horror feeling in his books comes from a very subjective and I dare say outdated place in the human mind. Most of his characters attribute adjectives like "abhorrent" or "grotesque" to mere shapes or smells and they actively attempt to filter out anything that might challenge their peace of mind and established world order. That's a silly and disgusting thing to do, I think, and perhaps this is what repels me most from writings of "the master".

But let me tell you of At the Mountains of Madness. The first thing that came into mind when I started reading it was "The Thing". It is placed in Antarctica where an expedition finds some frozen aliens who then defrost. There is even mention of shapeless things that can assume any form or function. Of course, this is as much as the similarities go. First and foremost the story is told by a scientist, in the pompous and highly descriptive manner in which most Lovecraftian work is written. This geologist, one of the few survivors, decides to write a more detailed account of what happened in view of a new expedition organized to go in the same region. His motive, and here is where I scoff the most, is to dissuade people to ever go there again, as some things are too horrible and evil to be explored by man. Say what? How is that guy a scientist? Anyway, he writes this as to fill in the gaps that he consciously and deliberately left out when he returned from the expedition, all of its members sworn to secrecy on some aspects of the trip. He sounds more like the leader of a cult than a member of a scientific group, doesn't he?

The end is more satisfying, though, where even Lovecraft's roundabout and subjective exposition has to give when describing the things they actually discover, explore then run away from. Even if I cannot abide the motivations of his characters or enjoy the way things are made more or less horrible or grotesque by their close minded whims, I have to declare some affection to the universe Lovecraft describes and perhaps some lingering interest on what one could make of it.

And indeed, people have been trying to resurrect Lovecraft's work in various ways: board games, graphic novels, sequels and prequels, movies. I seem to remember a 2005 movie that I liked, made after The Call of Cthulhu, but even that was made as a silent black and white film using the exact text from the story. And even if it achieved its goals of bringing Lovecraft's work to the screen, it did nothing to make it less dated or more accessible to a modern audience. Guillermo del Toro wanted to make a movie after At the Mountains of Madness, but he was deflected by film studios and his other work, mainly Prometheus. Del Toro even said that he would not make the ATMOM adaptation because it would have the same premise and twist as Prometheus. I am not so sure they should have been similar, but hey, that's how he saw it. What I am trying to say here is that modernizing Lovecraft for the present audience takes most of the love out of the craft :) Even this novella, which was a little bit longer than a short story, had so much roundabout storytelling and filler descriptions that if you took them away you would remain with a skeleton idea that could mould over anything.

So, my conclusion is that At the Mountains of Madness is one of the most accessible Lovecraft writings. It seems less dated than others and actually brings some clear descriptions of what is going on, not just randomly used adjectives testifying to the bizarre mental state of the characters. I can see no way to modernize or take the story and bring it to a modern audience without breaking the plot and turning it into something else, so if you want to experience it, you need to read the novella. It's relatively short so it shouldn't take much, unless you find it hard to go past the most descriptive parts without falling asleep, as I have.

Friday 20 December 2013

Ripper Street - a series with both quality and unfulfilled potential

The three main characters Ripper Street appeared at the same approximate moment with another similar series, Copper, also a historical series about policemen made by the BBC. The Ripper Street inspector is a man of justice, but also one that believes in the process of finding proof and sending the guilty to trial, which is relatively the same with the guy in Copper, who was a little more violent, but basically good stock. He employs (not unlike Copper) a doctor that is using scientific methods to determine the cause of death and other details that help in the investigation. His love life is in shambles as he mourns the death of his daughter, just like in Copper. A lot of the script also revolves around a female brothel owner, beset by financial troubles, but that ultimately proves ruthless. And also both series have ended abruptly, just before the second season's finale. This similarity can't be coincidental and it bothers me to no end that they used the same scheme for two different markets, just to see how it will work, with TV viewers as lab rats for their experiment.

But there are also differences. While the Five Points violent stage for Copper was more appropriate for the genre than East London after Jack the Ripper's case, the lead was never terribly charismatic. He seemed like a nice good looking Irish boy who was way over his head as a policeman. Instead, in Ripper Street Matthew Macfadyen uses his deep baritone voice and intimidating presence to portray perfectly the driven inspector Edmund Reid. Also, while the support cast for Copper did their job rather well, none seem to reach the height of theatrical performance that Jerome Flynn brings to Ripper Street while playing the violent but good willing police sergeant. And the final nail in the coffin for Copper is that most of the issues of the lead character are personal in nature, while the inspector in Ripper Street is tortured by his personal life failings, but is loyal to his job as a policeman.

Perhaps Ripper Street started with a promise of bringing light on the good of people in a hellish corner of the world, but in the end managed to do the opposite. In truth, the last scene of the series (and you might want to skip this slight spoiler if you plan to watch the series) is with the woman who loved the inspector turning in disgust when she sees him order his sergeant to kill a man in order to bring justice outside the system. In fact the last scene shows him betray all at once everything he stood for during the entire series. It's a slip up, of course, driven by desperation and something that would have probably been thoroughly explored in the third season. Alas, there is no third season to be had, for reasons of decreasing audience. It is difficult to pretend to be the best television producer in the world and then only care about audiences, like the assholes across the ocean, BBC!

The inconsistency in the show's quality can be blamed for the cancellation of the show, as well, but overall Ripper Street was entertaining and thought provoking. It could have been better, but not by much, I think. Good cast, good atmosphere of the old East End, some pretty compelling scripts. I believe it a shame to stop the show now. It was clearly better than Copper and that show had two seasons as well. Ripper Street deserved better, as well as all the viewers hooked on it.

Monday 16 December 2013

Misfits, an underappreciated show

Series poster with the original crew I've just finished the eighth episode of the fifth season of Misfits, the last in the series, as the show finally reached its end. I am convinced that most of the people reading this post don't really know what Misfits is and I mean to rectify that. Be warned, this is not the epitome of television and the last seasons were rather disappointing compared with the first few, but those few were pretty refreshing for a TV show.

Misfits is a British TV show about superheroes. Unlike American heroes, who are all beautiful and good and perfect, the first batch of Misfits superheroes is a bunch of losers on community service that get caught in a freak storm that gives them "powers". They use those powers in the most outrageous ways, completely out of control and with a tendency to think only of themselves and accidentally kill probation workers. Unfortunately it is the most consistent trend through the series, as actors change from season to season and the mood and quality of the show oscillates wildly.

My point, though, is that the show has a very nice premise and its worst problem was that instead of focusing on character development, they tried to ramp it up, adding more people with powers and gradually increasing the danger and weirdness of the situations in which they were involved until it just became ridiculous.

I don't know (or care) about ratings, but for me a nice show would have pitted these few guys against the real world, not a weird (and ultimately meaningless) version of itself where a lot of people have superpowers, but nobody notices. I repeat myself, but the idea of normal blokes and girls having superpowers and acting like normal people while they have them was a great one and the show creators should have followed it through. Too bad they didn't.

To wrap this out, I highly recommend to sci-fi fans watching the first two seasons of Misfits. Its... britishness... brings a refreshing perspective to an already overloaded and tired genre of the superhero and not getting exposed, even a little, to Misfits makes you miss out on a nice and unfortunately shortlived slice of the mythos.

I really wanted to give you a compilation of the best moment in Misfits, but apparently YouTube is infested by "Best funny moments of Nathan" which is the rude Irish pretty boy from the first season. Instead I leave you with the trailer for the series:

Tuesday 10 December 2013

The Witcher - Nice game, but the story gets drown in silly sidequests

Geralt and his White Wolf medalion You have to appreciate The Witcher for at least two major reasons: one is that it is based on a series of books by a Polish author and second is that it is made almost exclusively by Polish programmers and software managers. It is basically Polish software, and for that the quality is really great. Not that I disconsider software coming from the country, but I imagine they have a lot less resources than major American game studios, for example.

The story is that of a witcher, a monster slayer. He has tremendous physical strength and can use magic thanks to magical and genetic changes that have transformed him into a sterile mutant. He is basically the Caucazian version of Blade, if you want. I have not read the stories, but from what I've heard they are rather morally ambiguous, featuring the witcher drinking and whoring like a madman in between monster slaying bouts. The game attempts to do the same thing, of course with the sex and foul language removed, as it would have been too gruesome among all the blood, gore and violence. (I was sarcastic there, in case you didn't see it, people in charge with the moral development of our society!)

In fact the concept of the game is marvelous: have a character that can make choices that affect the overall story in a fantasy game of feudal monsters and courtly intrigue. However, in order to do so, you must go on endless quests gathering this and that, running around like a marathoner on steroids (which I guess you are, with all the genetic alterations and potions). The poor guy runs so much that one gets tired just watching him move. That was the major issue I had with the game, over 70% of it is running around (and 10% animations).

The fighting style was intriguing, but ultimately annoying. You had to click on the monster you wanted to kill, then wait for a specific moment when the cursor changed in order to click again and perform a combo. Up to six clicks can build a combo, which gives the player a lot of opportunity to click on somebody else, click next to the monster or move the camera in a way in which it is temporarily impossible to fight. Also Gerald does not have automatic fighting, so unless you tell him to attack, he just sits there and takes it. The damn clicks make you feel you are doing something, though, which I guess is a plus.

You get to meet a lot of damsels in distress which you have the option of helping. Once you do that they are remarkably willing to discard their clothes for you. In that situation you get to see... a nicely drawn card of a partially naked woman representing the sexual act. Then you return to where you were... at the same hour... dressed... which makes one think of a problem with the witcher's endurance, so to speak.

The changes in storyline are interesting, and some of them don't seem to happen until they have had time to propagate. This means you cannot just save, make a choice, see what happens and load, as there is a long time between choice and effect. This also means you will have to play the game a lot just to see only one story line. You will probably have to Google for all the outcomes. I, as always, was a perfect gentleman. No matter how ugly that Adda chick was, I still slept next to her... twice... and of course we remained best friends. No, really, there is something seriously wrong with me.

Overall it is a pretty entertaining and captivating game. The end chapter (the fifth, if you are wondering) is fraught with animations and it seems you have nothing else to do but move a bit, see a movie, move a little bit, kill some guy, another movie and so on. The fight with Javed was the most difficult, I think, with the rest a complete breeze once I had upgraded the Igni spell to the maximum power.

I have, however, the certainty that with a simple hack to allow a person to click on the map and get there at warp speed (maybe stop if there is a monster on the road or something) this game would have been three times shorter and a lot more fun. I started with a lot of expectations about it, though, and maybe that is why I felt a little disappointed, especially with the "boss" fights which seemed to involve a lot of talking and hiding behind minions until I got to them and very easily kicked their ass.

Time to play The Witcher II, I guess! I leave you with a video review of the game.



Also, for more information about the Witcher, like the choices you can make and the consequences or the quests you never got around to finishing, go to the Witcher Wiki

Wednesday 4 December 2013

Launch code for US nukes was 00000000 for 20 years

This is how an US general looks like I tried to find a title with more impact than the title of the original article to express my combination of amusement and disdain; I couldn't. It says almost everything. Apparently, the reason why the nuke codes were reset was that US Strategic Command generals almost immediately had the PAL codes all reset to 00000000 to ensure that the missiles were ready for use regardless of whether the president was available to give authorization.. How insane is that? On the other hand, there is a positive side to all this: it goes to show that you are just as intelligent as an US general when you leave your phone PIN to the default 0000.

Monday 2 December 2013

Accelerando, by Charles Stross

Book cover Oh, what a wonderful book this was. A cross between a William Gibson and a Peter F. Hamilton book, Accelerando was like a cyberpunk's wet dream. Not only it describes the deep transformations of our culture caused by the increasing power and speed of computation, but it goes further, years, decades, centuries and millennia more. You know the feeling you get when you get close to the end of a book and you sigh "Oh, I wish it would continue to tell the story"? It happens at the end of every chapter. It's like Stross could have ended the book at any point, but he chose to continue the story until its satisfyingly circular end. What is it with writers and the return to origins, anyway? There is an explanation for the structure of the book, as the author originally published each chapter as a separate story.

What is even nicer is that the story doesn't skim the details, showing only superficial bits that further the story, but it goes into everything: cybernetics, economy, ethics, law, the nature of consciousness. It gets frightening at some points when you realize that in the situations depicted in the book reality would be even more carnivorous and that your own individuality (held coherent in the book for the benefit of the reader) is just an illusion we cling to, ready to dispel when we muster the courage (or the insanity) to let it go.

I also liked how, while it was human-centric, the book did not limit itself to one species, nor did it go the way of accelerating (pardon the pun) until the whole story becomes meaningless in some encounter with a God like alien or by complete transcendence. I have to say I appreciate Stross immensely for not doing so, which is the normal and easiest way for a geek to end such a story: by generalizing the hell out of the situation until no particulars make sense. In that, the writer showed real restraint and mature wisdom. It makes me want to read all of his books.

If you want to know what the plot is, you will have to read the book, as I can't really do it justice here. I can tell you that it made me believe in an explosive evolution of the human race in my lifetime more than any Kurzweil discourse and it did it easily, by simple measuring MIPS/gram on the scale of the entire Solar System. If we will run Moore's Law for a few more decades, it will make enormous sense that "dumb matter" is done for. It is a fantastic vision of computation as a devourer of mass, a frightening equation akin to Einstein's matter to energy conversion. Did I mention that it also - convincingly - explains Fermi's paradox, much more so than "we get to build androids for sex", which was the most believable for me so far?

Needless to say it, but I will anyway: go read it, read it now! It is an amazing book. It is a little too pretentious in some parts, when it bombards your brain with technobabble just so it gets you "future-shocked" enough to understand the characters, but what cyberpunk fan doesn't eat that up, anyway? Also the familial connections in the book are a bit too overdone, but then again, they provide the generational point of view necessary to describe centuries of human evolution. There is a page - surprisingly Web 0.9 for such a plot :) - for the book, with an extract from the first chapter, but I don't think it is representative for the entire work.

You can actually read the book online for free, from the author's site: Accelerando

Monday 25 November 2013

Joining the rows of a table to the best row of another table in T-SQL

This is something I have been hitting my head on from the beginning of my programming career: just find the best match in a table for each row in another table through a single query.

There are solutions, but they are all very inefficient. To demonstrate the issue I will start with a simple structure: tables A and B, having the same columns id, x and y. I want to get, for each point in table A defined by the (x,y) coordinates, the closest point in table B. I only need one and it doesn't need to be exclusive (other points in A might be closest to the same point). It doesn't even have to be one row in B for each row in A, in case there are two points at the exact same distance to a point in A. The creation of the structure is done here:
CREATE TABLE A(id INT PRIMARY KEY IDENTITY(1,1), x FLOAT, y FLOAT)
INSERT INTO A (x,y) VALUES(10,20),(20,30),(20,10),(30,20),(30,20),(10,30)

CREATE TABLE B(id INT PRIMARY KEY IDENTITY(1,1), x FLOAT, y FLOAT)
INSERT INTO B (x,y) VALUES(11,20),(20,31),(21,10),(31,21),(30,20),(11,30)

To find the distance from A to the closest point in B is trivial:
SELECT a.id, 
a.x,
a.y,
Min(( a.x - b.x ) * ( a.x - b.x ) + ( a.y - b.y ) * ( a.y - b.y )) AS
dist
FROM a
CROSS JOIN b
GROUP BY a.id,
a.x,
a.y
To get the id of the closest B point, not so easy.

The first naive solution would be to just find the row in B that corresponds to each row in A using nested selects, like this:
SELECT * 
FROM a
JOIN b
ON b.id = (SELECT TOP 1 b.id
FROM b
ORDER BY ( a.x - b.x ) * ( a.x - b.x ) + ( a.y - b.y ) * ( a.y - b.y ) ASC)

Looking at the execution plan we see what is going on: 86% of the query is spent on "Top N Sort".
Let's get some other solutions so we can compare them in the end in the same execution plan.

Another solution is to just use the result of the query that computes the distance and just join again on the distance. That means we would compare each row in A with each row in B twice, once for the computation of the MIN function and the other for the join:
SELECT j.*, 
b2.*
FROM (SELECT
a.id,
a.x,
a.y,
Min(( a.x - b.x ) * ( a.x - b.x ) + ( a.y - b.y ) * ( a.y - b.y )) AS m
FROM a
CROSS JOIN b
GROUP BY a.id,
a.x,
a.y) j
INNER JOIN b b2
ON j.m = ( j.x - b2.x ) * ( j.x - b2.x ) + ( j.y - b2.y ) * ( j.y - b2.y )

Something that does the same thing, essentially, but looks a little better is joining the table A with B and then again with B on having the point from B2 be closer to the one in B1, but then adding a condition that there is no B2 (in other words, B1 is closest):
SELECT a.*, 
b1.*
FROM a
CROSS JOIN b b1
LEFT JOIN b b2
ON ( a.x - b1.x ) * ( a.x - b1.x ) + ( a.y - b1.y ) *
( a.y - b1.y ) >
( a.x - b2.x ) * ( a.x - b2.x ) + ( a.y - b2.y ) *
( a.y - b2.y )
WHERE b2.id IS NULL

None of these solutions scan B only once for each row in A. Their relative complexity is this: 75%, 11% and 14%, respectively. In other words, finding the minimum distance and then joining with the B table again on the points that are in exactly that distance is the best solution. However, given some assumptions and a weird structure, we can get to something that runs in half that time:
SELECT id      AS Aid, 
x,
y,
m % 100 AS bId
FROM (SELECT a.id,
a.x,
a.y,
Min(Cast( ( ( a.x - b.x ) * ( a.x - b.x ) + ( a.y - b.y ) * ( a.y - b.y ) ) AS BIGINT) * 100 + b.id) AS m
FROM a
CROSS JOIN b
GROUP BY a.id,
a.x,
a.y) j

These are the assumptions that must be true in order for this to work:
  • The function value can be converted to a BIGINT without problems. (if the distance between points would have been subunitary, this would have lost precision)
  • The maximum ID in table B is under a certain value (in this case 100)
  • The converted function multiplied by this maximum number doesn't cause an overflow
Basically I am mathematically creating a container for the value of the function and the id of the point in B, computing the minimum, then extracting the id back from the value. Neat.

Another solution, one that makes most apparent sense, is using a feature that was introduced in SQL Server 2005: RANK. We rank the points in B to each point in A, based on our function, then we only get the first by selecting on the rank being 1. Unfortunately, this doesn't work as expected. First of all, you cannot use RANK in the WHERE clause, so you must select the rank first, then select from that selection to add the condition. This might mean horrid temporary data tables if tables A and B are huge. Also, after running the query, it appears it is slower than the one that joins on the minimum distance. Here it is:
SELECT aid, 
bid
FROM (SELECT a.id AS aId,
a.x,
a.y,
b.id AS bId,
Rank()
OVER(
partition BY a.id
ORDER BY (a.x-b.x)*(a.x-b.x)+(a.y-b.y)*(a.y-b.y) ASC) AS rnk
FROM a
CROSS JOIN b) x
WHERE rnk = 1

Comparing all the solutions so far, without the first naive one, with the nested selects, we get these values:
  1. Mathematical container of function value and id: 14%
  2. Selection of the minimum distance to each point and then joining with table B for the second time to look for the point that is at that distance: 21%
  3. Joining twice on the same table with the condition that one is better than the other and that the better one doesn't actually exist: 29%
  4. Using RANK: 36%, most surprisingly the worst solution

The final solution, adding some more computation in order to get rid of constants and some assumptions thus becomes:
DECLARE @MaxId BIGINT 

SELECT @MaxId = Isnull(Max(id) + 1, 1)
FROM B;

WITH q AS (SELECT A.id,
A.x,
A.y,
Min(Cast(Power(A.x-B.x, 2) + Power(A.y-B.y, 2) AS BIGINT) * @MaxId + B.id) AS m
FROM A
CROSS JOIN B
GROUP BY A.id,
A.x,
A.y)
SELECT id AS aId,
x,
y,
m % @MaxId AS bId
FROM q;


I am still looking and there is now a question on StackOverflow that attempts to get the answer from the community, so far with limited results.

Friday 22 November 2013

Prevent a Windows computer from going idle even when the Group Policy prevents you from installing any software

I work in this silly place where everything must be done according to some plan or procedure. They aren't even very good at it, but they are very proud of this bureaucracy. For example I don't have Outlook installed on my work machine, but on a virtual one which is in a different network and can be accessed only by remote desktop protocol. Some admin with a God complex thought it was a good idea to make the computer lock itself after a few minutes of idleness and even close the entire virtual machine when no one accesses it for a while. This might have some sick sense in the admin's head, but I need to know when an email arrives and so I would like to have this virtual machine open on the second monitor without having to enter the password every 5 minutes. To add hurt to offence, I cannot install any software on the virtual machine or using Powershell to prevent the computer going idle or anything useful like that. Good, a challenge! I need to find a way to keep the remote desktop session alive.

Enter Windows Script Hosting. I've created a small Javascript file that gets executed by the machine and occasionally moves the mouse and simulates pressing Shift. No more idleness and no need to access Group Policy or install anything. Just create a text file and paste the following code and then save it with a .js extension, then run it. It will keep the computer from going idle.
var WshShell = WScript.CreateObject("WScript.Shell");
for (var i=0; i<60; i++) // 60 minutes
{
WshShell.SendKeys('+');
WScript.Sleep (60000);
}

Step by step instructions for non technical people:
  1. Press the Windows key and E to start the Windows Explorer
  2. In the Explorer, navigate to Desktop
  3. Remove the setting for "Hide extensions for known file types" - this is done differently from Windows version to Windows version, so google it
  4. Create a new text file on the desktop by right clicking in it and choosing "New Text Document"
  5. Paste the code above in it
  6. Save the file (if you successfully removed the setting at point 3, you should not only see the name, but also the .txt extension for the file)
  7. Rename the file to busybee.js (or any name, as long as it ends with .js
  8. Double click it

The script will run 60 times at every minute (so for an hour) and keep the machine on which it runs on from going idle. Enjoy!

Displaying a message from a T-SQL query in real time

The preferred method to display anything in Transact-SQL is PRINT. You can print a string, a variable, an expression. However, as anyone soon finds out, the message get all cached in a buffer and displayed after the entire query ends. So if you have several long running queries in a single batch and you want to get real time messages from them, PRINT doesn't work. A quick search directs you to another MS SQL directive: RAISERROR (note the creative spelling that makes one think more of hearing Katy Perry RROR rather than a proper error raising directive). Also note that Microsoft recommends using a new construct called THROW, introduced in SQL2012. However, it only looks like a lamer version of RAISERROR. They both send a message to the client instantly, but the problem they have is that they do not, as PRINT does, accept an expression. So if you want to print something like 'The computed key from the query is: '+@Key you are out of luck as you need to declare a new nvarchar variable, fill it with the message value then use it in RAISERROR.

But there is a better solution. RAISERROR not only throws something at the client, it also flushes the message cache. So something like this works: PRINT 'The computed key from the query is: '+@Key; RAISERROR('',0,1) WITH NOWAIT;.

Of course, being the dev that I am, I wanted to encapsulate this into something that would be reusable and also get rid of the need do use plus signs and conversion to NVARCHAR, so I created this procedure that almost works like PRINT should have:
CREATE PROCEDURE Write (@P1  NVARCHAR(max)=NULL, 
@P2 NVARCHAR(max)=NULL,
@P3 NVARCHAR(max)=NULL,
@P4 NVARCHAR(max)=NULL,
@P5 NVARCHAR(max)=NULL,
@P6 NVARCHAR(max)=NULL,
@P7 NVARCHAR(max)=NULL,
@P8 NVARCHAR(max)=NULL,
@P9 NVARCHAR(max)=NULL,
@P10 NVARCHAR(max)=NULL)
AS
PRINT Isnull(@P1, '') + Isnull(@P2, '')
+ Isnull(@P3, '') + Isnull(@P4, '')
+ Isnull(@P5, '') + Isnull(@P6, '')
+ Isnull(@P7, '') + Isnull(@P8, '')
+ Isnull(@P9, '') + Isnull(@P10, '')

RAISERROR('',0,1)

And you use it as DECLARE @now DATETIME = GetDate()
Write 'The date today is ',@now
. Nice, huh? Of course what you would have liked to do is Write 'The date today is ',GetDate(), but apparently stored procedures do not accept functions as parameters, and functions do not accept PRINT inside them.

Sunday 17 November 2013

Are all love stories fantasies?

I remember when I was a kid in highschool and wanted so much to experience that amazing feeling books and movies described, the magical bond between man and woman, the thing that makes everything worthwhile and even death irrelevant. So I had the feeling. It was amazing, it was magical, it was everything I expected it to be and more. And yet it all turned out to be an illusion, a manufactured fantasy of my own making that just tumbled down like a castle made of cards. And I always thought that was sort of weird and uncommon, but now I am thinking that maybe all romance is like that.

Could it be that love is something we are programmed to need to feel and we are just doing all is necessary in order to have the feeling into a familiar and comforting setting? All other sensations and feelings and needs are accounted for: you are happy because something good happened, you are hungry because you haven't eaten, you are disgusted because someone did something, you want to belong because we are a social species, but love is something that doesn't make a lot of sense, it's just there. It also feels too personal to assign it under "a species thing", even if its ultimate purpose is just that. So, in order to justify it, you need an origin story, just like any good comic book hero.

Thus the myth is born, perpetuated, kept alive as long as you have the feeling. And when you break a relationship up, again there are these justifications, stuff that just appears out of thin air and one or both of the people involved are always shocked, because they weren't there five minutes ago, when people were in love. "You killed my parents!" "You threw me in acid!" and so on. I don't want to stretch the superhero thing too much, though. Alternatively, think back and try to imagine all of your relationships as movies in the Kick Ass franchise. :)

So this is how I am seeing this: you meet this other person, you develop feelings of affection (or maybe just carnal lust, I don't care) and your brain starts churning up this script in the background. Once the script is ready, the focus group enjoyed it, the producers secured the financing, you already have the cast and bang! you have a blockbuster movie you call love. Until then it was just... less, "simple" affection or enjoyment, but with the origin story it becomes the superfeeling, capable of incredible feats, worthy of songs and tales.

As an argument for this vision I bring the terrible reluctance of people to dispel the magical script of their love affair. When the mystery of their feelings is laid bare they feel they have lost something. When outsiders are trying to look at it logically or to deconstruct the story they meet with fanatical resistance. After all, if you are an artist, making your own movie and starring in it, you really hate critics.

And when you are in a long relationship, one that spans more than the fabled three years, you continue to have the feelings, now intertwined with practical issues like who gets the car, who walks the dog, but they don't look so great because the love movie, as cool as it was, is old. You remember those times, maybe share them with your friends, just like remembering a good movie (made even better in your head by the bad parts you chose to conveniently forget) and your friends' reactions are just like for a movie, either sharing their own or criticizing yours or agreeing it was cool, maybe even getting the old "they don't make them like they used to" speech.

Of course, in order to definitively prove such a thesis, one must delve into the idea of what is real and what is not. That's a can of worms I am not opening. And believing every love story is a fantasy that covers up more mundane things and in which everyone involved participates willingly doesn't diminish its power. You can enjoy a movie even when you know it's not real, especially because you know it's not real. I also have to say something about the nature of control in a love situation. It's not clear to me, yet, but the gist of it is that this is why people do enjoy playing romantic games of seduction and betrayal, but ultimately dismiss the practice as not serious. It's the same kind of behaviour one sees in parents that scoff at their children's games, but watch TV all day. The thing is in a story you don't have control. It becomes more and more powerful the more the characters can say it all happened to them, rather than being something they planned. Somehow having control in a romantic relationship is frowned upon and having a good story on why losing control was a good thing is a form of art.

I'll end with a fragment of a movie I really liked, one that you probably don't associate with romance: The Name of the Rose. There was this scene where a girl, dirty and inarticulate, scavenging in the castle's garbage, meets our young protagonist interpreted by Christian Slater and just humps him like there is no tomorrow, groaning and moaning like a wild animal for the whole five minutes; then she leaves. Later on, when people are trying to burn her as a witch, the boy saves her. At the time I found that very romantic, even if they didn't even share a word. Maybe it's the fact that he gets to save her in the end, but most likely it's Slater's performance of looking at the girl with a longing and very confused gaze, the "what the hell is happening to me?" look that betrays the background script writing in the back of his skull.

Thursday 7 November 2013

The magical world of advertising

There is nothing more fantastic than the advertising world. Whether it is a brilliant spot, winning prize after prize at ad competitions, or a formulaic and horribly boring video of a housewife getting aroused by the newest household products, marketing and advertising items have one thing in common: they can't possibly be true. Maniacal teens getting their faces stretched by hideously wide grins cannot possibly love a soft drink or a gum so much. The housewife I previously mentioned probably has a job and children to take care of on the side, chances are there is little that actually makes her happy and absolutely nothing that can make her ecstatic about cleaning products. The newest app is never as good as the banner annoyingly appearing over your web site indicates, nor is the latest enterprise software doing the job it's supposed to do beyond a superficial number of typical scenarios. No teen finds happiness by buying the latest smartphone or opting in the latest two year cell subscription.

The governments everywhere are not doing their job, not keeping the promises they made during electoral campaigns, companies try to hide their problems under the rug while bragging to anyone that listens of the quality of their staff and products, human resources hire people under false pretenses while employees advertise skills that they never had. Boys proffer their love to girls and they in turn promise to have actual sex after the relationship steadies. Security companies, national or commercial, are only interested in their own security rather than yours. People that sarcastically make fun of other people's commercials are never as cool as they attempt to look.

I don't know about you, but in the rare cases when software doesn't manage to remove ads completely from my browser or smart phone, the fact that the ads appear in front of me has no effect other than mild (or strong) annoyance. My mind filters out the flashing images, the over the top colors and embellished fonts, anyway. Even the user content and comments that have been touted as the soul of Web 2.0 (or is it 3.0 now?) which would allow me to feel connected and get the information I need, they either reek of aggressive stupidity or bring little useful information to the table. I don't care what the top N items are in any list, nor am I interested in what other people Liked or not on Facebook. I never give money to beggars and don't buy stuff from loud people.

As far I can see, there is nothing that can be called advertising that is actually true or at least beneficial. So I have to wonder, in view of this impression of mine, could it be possible that the promise of advertising as a whole is just a lie as well, that the advertising of the marketing science and art is just as much bullshit as anything else the comes out of their world? Could it be that advertising's greatest feat is to make us think it actually works?

Monday 4 November 2013

We, by Yevgeny Zamyatin (Eugene Zamiatin)

Book cover We is a classic book, written by Russian author Yevgeny Zamyatin, who envisioned how the future in the hands of the Bolsheviks will work against the individual. He imagined a world in which all people shave their head, wear the same uniform and have numbers instead of names and live in completely transparent buildings, with no privacy. As the preface of the book there are three commentaries on Zamiatin's genius, history as a writer in Russia, then his voluntary exile and the interdiction of his works in Russia.

So did I like the book? No. To say that is dated is an understatement, but it's not only that. The style is that of a diary, but one in which dialogues between people are described verbatim. The way they speak is emotional, hysterical, they interrupt each other and words are incoherent, so contradictory to the premise of the book. Even the inner thoughts of the main character are chaotic, childish, delirious, with wild mood swings. It was so horrible that, after deciding that 200 PDF pages were not going to be a lot of reading, I stopped at 50 and started skipping ahead. The entire book is like that.

Maybe Zamiatin was a great writer, but I don't see it. Just because he was Russian is no excuse for his incoherence. It felt like he wrote the book while intoxicated; with too much coffee. Even if he starts his book with Dear readers, you must think, at least a little. It helps, I believe that it was his duty as a writer to communicate his ideas in a digestible form. Maybe thinking is different from person to person, Zamiatin. If I think about it, the story within is similar to the movie Equilibrium, only that was more action based, as any American movie is prone to be, and watchable. The same champion of the system being seduced (through a woman, how else?) by the emotions that he was sworn to fight against.

About the subject of the book, I can say that stories about rebels fighting Utopian regimes are making me feel conflicted. I care about the personal desires of the individual, but I also care about the overall system. One must understand that a centralized and overall controlled system is as disturbing to a person that experienced individual freedom as is a decentralized system based on individuals is for someone that lived in a different environment. To assume that one or the other knows better is just arrogant and stupid. The worse thing that can happen, in my view, is to only have one acceptable system that, even if we could leave one country for another, would be the same all over the world.

On the other hand, once you, as an individual, decide that something is right and something is wrong, you have a responsibility to act. I just remember that quote Slaves dream not of freedom, but of becoming masters. It would be a lot easier for me to accept people rebelling against systems if they would stop attempting to change the world for everybody, not just themselves.

Well, I will leave you with a quote from the book, one that seemed eerily contemporary: If human liberty is equal to zero, man does not commit any crime.

Wednesday 30 October 2013

Rotating text with GDI Graphics within given bounds

An unlikely blog post from me, about graphics; and not any kind of graphics, but GDI graphics. It involves something that may seem simple at first: rotating a text in a rectangle container so that it is readable when you turn the page to the left. It is useful to write text in containers that have a height that is bigger than the width. This is not about writing vertically, that's another issue entirely.
So, the bird's eye view of the problem: I had to create a PDF that contains some generated images, a sort of chart with many colored rectangles that contain text. The issue is that some of them are a lot higher than they are wide, which means it is better to write text that is rotated, in this case to -90 degrees, or 270 degrees, if you like it more. To the left, as Beyoncé would put it.

I created the image, using the Bitmap class, then got a Graphics instance from it, then starting drawing things up. It's trivial to draw a line, fill a rectangle, or draw an arc. Just as easy it is to write some text, using the DrawString method of the Graphics object. I half expected there to be a parameter that would allow me to write rotated, but there wasn't. How hard could it be?

Let's start with the basics. You want to draw a colored rectangle and write some text into it. This is achieved by:
var rectangle=new Rectangle(x,y,width,height); // reuse the dimensions
g.FillRectangle(new SolidBrush(Color.Blue),rectangle); // fill the rectangle with the blue color
g.DrawRectangle(new Pen(Color.Black),rectangle); // draw a black border
g.DrawString("This is my text",new Font("Verdana",12,GraphicsUnit.Pixel),new SolidBrush(Color.Black),rectangle, new StringFormat {
LineAlignment=StringAlignment.Center,
Alignment=StringAlignment.Center,
Trimming = StringTrimming.None
}); // this draws a string in the middle of the rectangle, wrapping it up as needed

All very neat. However, you might already notice some problems. One of them is that there is no way to "overflow" the container. If you worked with HTML you know what I mean. If you use the method that uses a rectangle as a parameter, then the resulting text will NOT go over the edges of that rectangle. This is usually a good thing, but not all the time. Another issue that might have jumped in your eyes is that there is no way to control the way the text is wrapped. In fact, it will wrap the text in the middle of words or clip the text in order to keep the text inside the container. If you don't use the container function, there is no wrapping around. In other words, if you want custom wrapping you're going to have to go another way.
Enter TextRenderer, a class that is part of the Windows.Forms library. If you decide that linking to that library is acceptable, even if you are using this in a web or console application, you will see that the parameters given to the TextRenderer.DrawText method contain information about wrapping. I did that in my web application and indeed it worked. However, besides drawing the text really thick and ugly, you will see that it completely ignores text rotation, even if it has a specific option to not ignore translation tranforms (PreserveGraphicsTranslateTransform).

But let's not get into that at this moment. Let's assume we like the DrawString wrapping of text or we don't need it. What do we need to do in order to write at a 270 degrees angle? Basically you need to use two transformations, one translates and one rotates. I know it sounds like a bad cop joke, but it's not that complicated. The difficulty comes in understanding what to rotate and how.
Let's try the naive implementation, what everyone probably tried before going to almighty Google to find how it's really done:
// assume we already defined the rectangle and drew it
g.RotateTransform(-270);
g.DrawString("This is my text",new Font("Verdana",12,GraphicsUnit.Pixel),new SolidBrush(Color.Black),rectangle, new StringFormat {
LineAlignment=StringAlignment.Center,
Alignment=StringAlignment.Center,
Trimming = StringTrimming.None
}); // and cross fingers
g.ResetTranformation();
Of course it doesn't work. For once, the rotation transformation applies to the Graphics object and, in theory, the primitive drawing the text doesn't know what to rotate. Besides, how do you rotate it? On a corner, on the center, the center of the text or the container?
The trick with the rotation transformation is that it rotates on the origin, always. Therefore we need the translate transformation to help us out. Here is where it gets tricky.

g.TranslateTransform(rectangle.X+rectangle.Width/2,rectangle.Y+rectangle.Height/2); // we define the center of rotation in the center of the container rectangle
g.RotateTransform(-270); // this doesn't change
var newRectangle=new Rectangle(-rectangle.Height/2,-rectangle.Width/2,rectangle.Height,rectangle.Width); // notice that width is switched with height
g.DrawString("This is my text",new Font("Verdana",12,GraphicsUnit.Pixel),new SolidBrush(Color.Black),newRectangle, new StringFormat {
LineAlignment=StringAlignment.Center,
Alignment=StringAlignment.Center,
Trimming = StringTrimming.None
});
g.ResetTranformation();
So what's the deal? First of all we changed the origin of the entire graphics object and that means we have to draw anything relative to it. So if we would not have rotated the text, the new rectangle would have had the same width and height, but the origin in 0,0.
But we want to rotate it, and therefore we need to think of the original bounding rectangle relative to the new origin and rotated 270 degrees. That's what newRectangle is, a rotated original rectangle in which to limit the drawn string.

So this works, but how do you determine if the text needs to be rotated and its size?
Here we have to use MeasureString, but it's not easy. It basically does the same thing as DrawString, only it returns a size rather than drawing things. This means you cannot measure the actual text size, you will always get either the size of the text or the size of the container rectangle, if the text is bigger. I created a method that attempts to get the maximum font size for normal text and rotated text and then returns it. I do that by using a slightly larger bounding rectangle and then going a size down when I find the result. But it wasn't nice.

We have a real problem in the way Graphics wraps the text. A simple, but incomplete solution is to use TextRenderer to measure and Graphics.DrawString to draw. But it's not exactly what we need. The complete solution would determine its own wrapping, work with multiple strings and draw (and rotate) them individually. One interesting question is what happens if we try to draw a string containing new lines. And the answer is that it does render text line by line. We can use this to create our own wrapping and not work with individual strings.

So here is the final solution, a helper class that adds a new DrawString method to Graphics that takes the string, the font name, the text color and the bounding rectangle and writes the text as large as possible, with the orientation most befitting.

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace GraphicsTextRotation
{
public static class GraphicsExtensions
{
public static void DrawString(this Graphics g, string text, string fontName, Rectangle rect, Color textColor, int minTextSize=1)
{
var textInfo = getTextInfo(g, text, fontName, rect.Width, rect.Height); // get the largest possible font size and the necessary rotation and text wrapping
if (textInfo.Size < minTextSize) return;
g.TranslateTransform(rect.X + rect.Width / 2, rect.Y + rect.Height / 2); // translate for any rotation
Rectangle newRect;
if (textInfo.Rotation != 0) // a bit hackish, because we know the rotation is either 0 or -90
{
g.RotateTransform(textInfo.Rotation);
newRect = new Rectangle(-rect.Height / 2, -rect.Width / 2, rect.Height, rect.Width); //switch height with width
}
else
{
newRect = new Rectangle(-rect.Width / 2, -rect.Height / 2, rect.Width, rect.Height);
}
g.DrawString(textInfo.Text, new Font(fontName, textInfo.Size, GraphicsUnit.Pixel), new SolidBrush(textColor), newRect, new StringFormat
{
Alignment = StringAlignment.Center,
LineAlignment = StringAlignment.Center,
Trimming = StringTrimming.None
});
g.ResetTransform();
}

private static TextInfo getTextInfo(Graphics g, string text, string fontName, int width, int height)
{
var arr = getStringWraps(text); // get all the symmetrical ways to split this string
var result = new TextInfo();
foreach (string s in arr) //for each of them find the largest size that fits in the provided dimensions
{
var nsize = 0;
Font font;
SizeF size;
do
{
nsize++;
font = new Font(fontName, nsize, GraphicsUnit.Pixel);
size = g.MeasureString(s, font);
} while (size.Width <= width && size.Height <= height);
nsize--;
var rsize = 0;
do
{
rsize++;
font = new Font(fontName, rsize, GraphicsUnit.Pixel);
size = g.MeasureString(text, font);
} while (size.Width <= height && size.Height <= width);
rsize--;
if (nsize > result.Size)
{
result.Size = nsize;
result.Rotation = 0;
result.Text = s;
}
if (rsize > result.Size)
{
result.Size = rsize;
result.Rotation = -90;
result.Text = s;
}
}
return result;
}

private static List<string> getStringWraps(string text)
{
var result = new List<string>();
result.Add(text); // add the original text
var indexes = new List<int>();
var match = Regex.Match(text, @"\b"); // find all word breaks
while (match.Success)
{
indexes.Add(match.Index);
match = match.NextMatch();
}
for (var i = 1; i < indexes.Count; i++)
{
var pos = 0;
string segment;
var list = new List<string>();
for (var n = 1; n <= i; n++) // for all possible splits 1 to indexes.Count+1
{
var limit = text.Length / (i + 1) * n;
var index = closest(indexes, limit); // find the most symmetrical split
segment = index <= pos
? ""
: text.Substring(pos, index - pos);
if (!string.IsNullOrWhiteSpace(segment))
{
list.Add(segment);
}
pos = index;
}
segment = text.Substring(pos);
if (!string.IsNullOrWhiteSpace(segment))
{
list.Add(segment);
}
result.Add(string.Join("\r\n", list)); // add the split by new lines to the list of possibilities
}
return result;
}

private static int closest(List<int> indexes, int limit)
{
return indexes.OrderBy(i => Math.Abs(limit - i)).First();
}

private class TextInfo
{
public int Rotation { get; set; }
public float Size { get; set; }
public string Text { get; set; }
}
}
}

I hope you like it.

Friday 25 October 2013

Transposition of ranges in SQL and charts

I had this database table containing ranges (a start value and an end value). The challenge was creating a query that overlaps and transposes those ranges so I can say how many ranges are at any point in the total interval or values. As an example, "SELECT * FROM Ranges" would result in a table like:
StartEnd
1020
1030
2535
2040
and I am looking for something like this:
ValueCount
00
10
......
102
112
......
242
253
263

A naive implementation would get the minimum Start (or start with 0, as I did) and the maximum End, create an in memory or temporary table (Values) from min to max using an ugly WHILE block, then join it with the Ranges tables something like:
SELECT v.Val,Count(1) as Nr
FROM #Values v
INNER JOIN Ranges r
ON r.Start<=v AND r.[End]>=v

This kind of works, but for large ranges it becomes difficult. It takes a lot to create the Values table and the join and for extreme cases, like I had with values from 0 to 6 billion, it becomes impossible. The bottleneck here is this Values table, which is pretty much a horror to create and maintain. But what if you don't need all the values?

Before I tell you the solution I found, be warned that you have to properly define what a range is. Is a range 10-20 actually 10-19? In my case it was so, so that is why there are some subtractions with 1 or less than rather than less or equal conditions.

The solution is this:
SELECT DISTINCT Val
INTO #Values
FROM (
SELECT 0 as Val
UNION ALL
SELECT Start FROM Ranges
UNION ALL
SELECT [End]-1 FROM Ranges
) x
ORDER BY Val

The idea is that after you compute the ranges count per each of the start and end values you know that between one and the next the count of ranges will remain the same. The join is significantly faster, there is no ugly WHILE block and you don't need a 6 billion value table. It's easier to plot on a chart as well, with either of these variations:
See the variations:
SELECT v.Val,
Count(r.Start) as Nr
FROM #Values v
LEFT JOIN Ranges r
ON r.Start<=v.Val AND r.[End]>v.Val
GROUP BY v.Val

The result of the query above will be:
ValueNr
00
102
192
202
253
293
342
391
Hope this helps you

The Drunken Botanist - The Plants That Create the World's Great Drinks, by Amy Stewart

Book cover I will go forth immediately and say that this book is hard to read. It's not just that it uses English names of fruits and plants not often met in normal conversation or movies, it's not only that it uses the English measurements units that most Europeans have no understanding of and it's not even the author's apparent obsession with Angostura bitters :). It's because the book is filled with information and it is better suited to be used as a reference than a one-off read. It is obvious that in The Drunken Botanist the author, Amy Stewart, put a great deal of research; such an amount, in fact, that it would be impossible to be able to absorb all of it in one read. You realize that a book is great when you see affecting you in your every day life. No, I did not get drunk every day on strange cocktails, even if the temptation certainly existed, but I found myself looking at plants everywhere and wondering how they would taste in a drink. "Is that fruit edible? Probably not, it's red and the fruit of a decorative tree, so probably it is poisonous. But surely there is a way to destroy the poison and enjoy the taste in an alcoholic drink." That sort of thing.

And what I find even nicer is that the author is ready to continue the conversation started in the book on a special website called DrunkenBotanist.com

The book is split into very little chapters, each on a certain plant, a little bit of history - the degree of details varying wildly, but described with great humor and passion, the way they are used in drinks, cocktail recipes, some botanical or chemical info - although not too much. There are organized by themes, like leaves or fruits, grasses or trees, and so on, but essentially it's an enumeration of plants and their uses. This is something I didn't like that much. My issue is that I expected to see more botanical information, like how plants are related and how the drinks made from them are related. As such, you only get a few words at the beginning of a chapter about the plant's family, but no connection is made. Of course, no one stops you for researching and doing it yourself.
Another thing that bothered me a little was the images. I agree that full color and size images of the plants described would have printed as a huge book, but the e-book version I read had no such limitations. Instead of seeing a simplistic representation of some plants, I would have liked to see the plant itself. That would have helped me understand what each plant was in my language, as well.

I have to conclude that the book is a very interesting one, albeit difficult to finish reading. I understand the need to earn money and thus sell it as a book, but for me it seems that the book's format would have been a lot more appropriate for a web site and that some features should have been different in the electronic version than the printed one. Instead, the Drunken Botanist site is actually a blog, in a blog format, which is unusable as a reference, really. I recommend browsing the book and certainly have it available. Some information in it is great at parties and the botanical insight into the common drinks and their ingredients is fascinating. But think of it as something to have ready for when you need to know what you are mixing. I would say it's a mandatory read for any bartender, though.

Tuesday 15 October 2013

TV Series I've Been Watching - Part 16

It's the middle of autumn again and more and more series are making their entry into the list. But there are a lot of shows that I carry around from post to post without actually watching them. I've decided to remove them from the list until that time comes.



Let's start with the already described ones:

  • Doctor Who - Math Smith has left the show and the new doctor is an actor which I really like, Peter Capaldi, who you might remember from The Thick of It. Unfortunately, he will be very polite in Doctor Who; I would have loved to see him as irreverent, sweary and Scottish as in his other role.
  • Dexter - season 8 of the series has just ended (disastrously) and thus the show. It was a good run, I guess, even if the last seasons were quite ridiculous.
  • True Blood - I am becoming conflicted about this show. On one hand some of the storylines are very interesting, while on the other the multitude of stories, one more over the top than the other, shown in parallel in every episode, make it all feel like one supernatural soap opera. The ending of the season, with the invasion of the zombie vampires, also left me deflated.
  • The Good Wife - the fifth season just started, but I haven't got around to it. I think the show continues to be good, but they probably should wrap it up and do something else before it too dissolves into pointlessness.
  • Haven - the fourth season has just started. I will probably watch it with the wife, she seems to enjoy it.
  • Falling Skies - the third season was just weird. It's difficult enough to justify an almost medieval human resistance while being caught in an interstellar war even without having incompetent writers, which unfortunately the show has.
  • Southpark - Southpark is still running hot at season 17. The Butters episode in which he becomes a DMV witness was delicious.
  • Suits - Suits started fun, I really enjoyed it; for stupid reasons, but I did. The third season showed me that even those reasons are quickly fading. Everybody acts over the top and with almost no connection to real life. It's like a movie made by advertisers.
  • Breaking Bad - I started watching the first episode from season 5 only to realize I don't understand what the hell is going on. Did I even watch the fourth season? Unclear.
  • Homeland - season three starts with Carrie being thrown to the wolves as she goes more and more insane. Having a similar situation in the family doesn't make it easy to watch.
  • The Walking Dead - the fourth season just started, but I didn't get around to it, yet.
  • Game of Thrones - there was a lot of outrage on the Internet linked to the Red Wedding episode. It's the first true test of the show. If people will continue watching it even after blatantly killing some of their favourite characters, then the show will go on until Martin stops writing (or maybe not even then). I am afraid it might not happen that way, though.
  • Mad Men - does anyone remember this started as a movie about advertising people? Right now it seems to be more about the "mad" part of the title, as Don devolves more and more into a crazy person.
  • Continuum - Continuum continues, even if it got kind of weird in the last season. Everybody is related to everybody and makes deals with all. It's becoming an intertemporal high-school drama.
  • Copper - Copper started as in interesting show about Irish policeman in Five Points, but after the second season it got cancelled. The last season was pretty confusing and less and less having to do with the job and more with the personal. I did not like that.
  • Longmire - who killed the killer of madam Longmire? Was it sir Longmire? No. Was it Lou Diamond Philips? No again. Was it the killer hired by Lou Diamond? No, because he wasn't a killer. Does that mean people will stop worrying about it? Definitely not. I really want Longmire to retain that authentic countryside sheriff feel; the screenwriters seem to feel otherwise.
  • The Newsroom - drama, politics, revenge, more drama and finally, some drama. All over the top and on high speed. No wonder the city never sleeps if everybody is overactive.
  • Arrow - new Arrow season, with tales of bringing more superheroes into the story, like The Flash. Will this become the testbed for Playstation and XBox game scripts?
  • Elementary - the second season started with some interesting episodes. One even features Lestrade, as a celebrity addict. Funny, that.
  • House of Cards - this show is the first to be launched on Netflix (and produced by them as well). This means the entire first season was launched on the same day. No more waiting week after week to see a bit of the show, but how much time will it pass now until the next season? It's almost like releasing 10 hour movies.
  • Father Ted - I watched almost all episodes of this very old and obscure show, basically just because it was old and obscure. It's a laughing-track comedy about two idiotic priests on a small remote island in Ireland. Supposedly ireverent to religion, it's just a very silly show.

New shows:

  • The Tomorrow People (2013) - this remake of a really dumb old series just started. I haven't started watching it, but I've read a review that seemed to praise it. It stars beautiful young actors with superpowers, though.
  • The Legend of Korra - the second season has started with a transparent nefarious plot that only the dumb female avatar doesn't seem to get. Her character is so annoying!! Ugh!
  • Atlantis - I expected Atlantis to be a sort of Rome or Spartacus. The bronze-punk science fiction would have made a great show. Unfortunately it seems to be more a kind of a Hercules/Xena kind of show. There is this modern guy who gets into Atlantis through a portal, meets Pithagoras and Hercules, who are both different from the myths, and runs around with swords.
  • By Any Means - I could barely survive through the first episode. This is a British show about a group of people that are hired by the police to bring people to justice. Why doesn't the police do that directly? Because this group uses illegal and imoral acts to achieve their goals, then rationalize them by them being "the good guys" and acting all cool and funny. The show was basically offensive to me.
  • Hostages - a show about a doctor who must opperate on the United States president. Her family is taken hostage to force her to kill the president. Yeah, like that could happen. It has good actors and an intriguing plot, but it could go both ways. I've watched just the first episode.
  • Ironside - another show about a cop. This time the cop is black and in a wheelchair, even if he continues to strong arm suspects and his team with impunity. I found it pretentious and yet another show which is trying to force feed us vigilante justice from charismatic characters.
  • King and Maxwell - a show about a detective agency run by former Secret Service agents. At last a detective agency not run by complete amateurs that got bored. Rebecca Romjin is one of the pair, which makes the show nice to look at, at least. Now, the first episodes felt a bit silly, like a comedy, so I didn't continue watching it, but it could become better.
  • Low Winter Sun - TV series starring two less known, but nevertheless good actors: Lennie James and Mark Strong. It's very dark, with corrupt policemen and rampant gangs, but I can't say I enjoyed it much. Probably because it seems to miss a point.
  • Ray Donovan - something that stars Liev Schreiber and John Voight should be better than this, but the show is not bad. There is this guy that acts like a sort of damage control expert who has to contend with his father getting out of jail and being a dick. Control that damage, if you can!
  • Rewind - it is the season of shows about people playing God. This time they are not policemen, thank you, but an agency that uses technology to go back in time and "fix" things. All seemed straightforward until the end of the first episode, which promises a longer story arch and maybe even more interesting action.
  • Serangoon Road - another Australian show about private detectives, this time placed in Malaysia. Chinese, Australians, Javanese, British and Americans are all dancing around each other, trying to win the jackpot in this country torn by civil unrest and government dissolution. An interesting show, which I continue to watch.
  • Siberia - I almost want to ask "What is Siberia?" in a Matrixy sort of way. It is a series that starts as a reality show, only to have the contestants meet strange phenomena until the entire premise explodes and they find themselves stuck in Siberia, with no contact to the outside world and in mortal (supranatural/scifi) danger. The problem with the show is that it is pretty inconsistent and difficult to watch. It's like it doesn't know what it wants to be: a reality show, a Lost rip-off or something else.
  • Sleepy Hollow - Sleepy Hollow the movie was a masterpiece, combining a moody atmosphere that hinted of the supernatural with a main character that used logic and science to prove that it actually wasn't. The Sleepy Hollow series is the complete opposite, with a clear supernatural storyline involving good and bad witches, demons and Ichabod Crane, a civil war soldier who has found himself in the present and now helps (of course) the police deal with the rise of evil. It could have been good, but I really don't like any of the actors or characters, except maybe the sheriff, but he dies.
  • The Blacklist - I really wanted to like the series, because it stars James Spader (an old and bald one), an actor which I really liked in the past. But it's a confused mess. Spader plays the role of a rogue FBI agent that surrenders after 20 years only to help the FBI find catch the "truly bad" guys. He enlists the help of a girl FBI agent who probably is his daughter, even if he doesn't say it out loud. She is basically a clone of the FBI agent from Haven, while Spader's character is an arrogant prick. Impossible to empathise with anyone and even harder to see a point of this show.
  • The Bridge - an American remake of the Danish/Swedish series Bron, it involves a murderer that leaves a body on the exact border of Mexico and the US, forcing the creation of a joint taskforce. Beautiful Diane Kruger plays the functional autist American agent, while Demián Bichir plays the complex Mexican policeman. There are other characters, but some are even detrimental to the story, in my view. The show is solid and has some interesting and good acting. I hope it stays that way.
  • The Crazy Ones - just so you don't say I don't watch any comedy shows, this stars Robin Williams as a crazy and inspirational ad man, helped by two equally crazy guys and his daughter, played by Sarah Michelle Gellar. It has its moments, but it isn't really my style. Why can't they make comedy that also seems real, rather than completely over the top?
  • The Originals - oh, what a bunch of pretentious crap! a (too) late comer in the vampire TV show business, it even tries to reuse the vampire aristocrat variation. The only modern take of this soap opera like show is that it also adds witches to the mix. This is they year of the witches, after we got tired of vampires and werewolves and zombies didn't quite catch on.
  • The Psychopath Next Door - not an easy show to watch, it's full of psychological violence. It features a young very attractive female psychopath and how she deals with the world. Being a British show she really acts like a sociopath, shows no remorse and does the most atrocious acts just to get ridiculously unimportant things, rather than help the police catch bad guys or whatever :) A really good beginning and a fascinating glimpse in the mind of a true psycho.
  • Under The Dome - when in doubt, use a Stephen King story. Alas, I usually don't like King's writing it tends to drag on. Making a TV series out of one makes a rather boring watch. This is a sci-fi show about a transparent and impenetrable dome that completely isolates a small community from the outside world. The situation quickly devolves because of the people there, as primate tribes are wont to do when consequences disappear.
  • What Remains - British show, I just started to watch the first episode. It seems to revolve around human remains, discovered a long while after the crime (if it was one) and how people lives are affected by the investigation. Could be good.
  • Witches of East End - told you this is the season of the witch. This show is about a family of witches, incredibly good looking ones. The mother is Julia Ormond, her sister is Madchen Amick and one of the daughters is this hot and curvy brunette. Of course good looking guys and evil witches appear soon after. It seems a typical teen show, with drama coming exclusively from social situations, people who are romantic interests or/and are trying to kill you.
  • The Last Witch - The show just appeared and I haven't had the chance to watch it, but it's about witches, which validates my view that witches are the new vampires. At least this show is British.
  • Blackout - a faux documentary British series about a fictional British power blackout that lasted for more than a week. Kind of hard to watch because it's made like a montage of different camera shots taken by different people on handhelds and phones.

Well, it seems that removing the shows that I am not actively watching makes this list more manageable, but it also means you will have to look at more "TV Series I've been watching" blog posts to get an overview.