zondag 27 april 2014

Stupid quotes

SELECT 'foo' IN ('foo'
(1 row)

So what went wrong here?  Two things. The linebreak in the query is just presentation and when it's left out you get:

SELECT 'foo' IN ('foo''bar');
(1 row)

Which makes sense because the two singlequots form an escaped singlequote. The comparison is actually against "foo'bar", and of course that's not equal to "foo".

About using a phrase as a password...

Today I read an article about "wasting user's time".

In this article, the author claims: "A long password phrase is as secure as a short password with numbers and symbols yet easier to remember", showing a screenshot from this website

This is a very dangrous thing to say.

Using a phrase means you are actually reducing the number of  possible combinations, because a phrase will use only known words and follow a known grammer. They are predicatble.
The sentence "i love pizza" is said to take 546 years to hack, but I'd argue it's more like two minutes. Most people will feel the need to start their personal passphrase with "I", they just do. Then they will describe something about themselves, and it will be generic and simple. "I like", "I love", "I think", follow that by any subject you can love, like, hate, enjoy, and that takes care of most of your "secure phrases".
You still have to incorporate capital letters etc to make the words unpredictable and then you're back where you started: "I L0ve P1zzA!?"

donderdag 17 april 2014

Reblog: NoSQL brings down three bitcoin exchanges

A story of fail, and why you should use a RDBMS when you are working with money.

Read the full article at: http://hackingdistributed.com/2014/04/06/another-one-bites-the-dust-flexcoin/?utm_content=buffer8316b&utm_medium=social&utm_source=twitter.com&utm_campaign=buffer

On creating a multilingual website.

How to create a multilingual website?

Easy: don't do it.

I mean: don't try to create a website that can handle all content in many languages.
No matter how hard you try, you will never actually have all content in all languages at all times. That's just how it is. What's more, you will have to make exceptions for pages that simply will never exist in a particular language. Don't think you won't, everybody does and so will you.

Also, words have different lengths in most languages, you cannot simply translate "go home" to the Dutch "ga terug naar huis" and expect that to fit in the same space. You would have to create some ├╝berflexible HTML that can make the site look good no matter how long or short the translations get.

What you will end up with is a complex system consisting mostly of exceptions, which is hard to maintain and ultimately not very pretty to look at.

Solution: create a separate copy of the website for each language. That way you don't have to make any exceptions, every site can be perfectly tailored to it's language and it will never link to pages that don't yet exist in that language.

"But then updating the site's html requires separate actions for each site!"

Yes, you will have to do the same thing several times, but not having any exceptions in the html/CSS/backend means you can pretty much copy/paste the changes without breaking anything.

woensdag 16 april 2014

Why I like PostgreSQL more than MySQL: Maria_DB's new dynamic columns vs the age-old HStore.

MariaDB has introduced dynamic columns, and MySQL users are amazed. They can now store key/value pairs in a column and fetch them back out by key.

Sound familiar? It should, because PostgreSQL has supported HStore for years, alongside XML, and recent versions have enabled JSON for the same purpose.

And of course PostgreSQL can use functional and partial, and GIN indexes to index the content of HStore, JSON and XML columns, making them fast to query against.

dinsdag 15 april 2014

Why I like PostgreSQL more than MySQL: fulltext search.

Yes, I  know MySQL has fulltext search too, but it's functionality is limited to doing a boolean search.

In PostgreSQL you get access to a much more usefull set of features, such as dictionaries that allow stemming (also finding 'swim' when you search for 'swimming') filtering stopwords, mathing on word proximity, etc.

For a practical implementation of a so-called "good enough" search engine in pure PostgrSQL, see this blogpost: http://blog.lostpropertyhq.com/postgres-full-text-search-is-good-enough/?utm_source=dbweekly&utm_medium=email

maandag 14 april 2014

Why I like PostgreSQL more than MySQL: ENUM

Opinions vary when it comes to enum, but one thing everybody agrees on is that they should work.

Everyone except MySQL, see: http://blog.endpoint.com/2014/04/sanity-thy-name-is-not-mysql.html

Would any MySQL user's care to try justifying that behaviour? :-)

zondag 13 april 2014

About using non-standard SQL features.

A common argument I hear when I mention the many features of PostgresSQL is "but those are not SQL standard, if we use them we can't easily migrate to a different database." That sounds like a good argument at first, the less work you have to do, the better, right?

Well... no.

Custom features are made custom because either they solve a common problem that's tricky to do in standard SQL, or because it is much, much faster than standard SQL. Most applications never migrate, and those that do, do it exactly once. Forcing an application to be inefficient throughout it's lifetime just so you can shave a few hours off a migration which you probably won't do anyway, is just stupid.

Perhaps more importantly, even the simplest pieces of SQL can and will contain database specific differences. For example; MySQL is not case-sensitive in string comparisons. In short: you will have to check *EVERY* query anyway. Writing complex code to replace a custom SQL feature just makes things harder to migrate.

So what of the handfull of cases that do migrate? Well, they don't migrate, they gain support for another database. Supporting a new database takes time and while you are writing the new code you will have to keep the production side running on the old database. You cannot remove any of the old code because you'd risk not being able to fix urgent issues in production.

What you shold do is split the database-related code into a separate section of the source. When you neeed to start supporting a new database you copy that piece of the code, modify it to suit the new database and instruct your application to use the new codecode.

zaterdag 12 april 2014

The power of communities, and why that's not always a good thing.

Communities are great. They provide support for many products and enable you to share the knowledge of others. Wonderfull!

The only drawback is that the communities of popular tools like PHP and MySQL consist mosty of enthausiasts and beginners. They mean well and they spend hours answering questions to the best of their ability, but they simply do not have enough experience to give the "correct" answer.

What's worse, as I mentioned in my other article "don't contradict the locals", forums are ruled by a few regular users who are considered to be the experts by the rest. They usually don't care about having the right answer, they care about being right and they will come up with the most idiotic reasonings why what their answer is right.

Consequently the bad knowledge is promoted and developers keep messing about with bad solutions.
PHP programers love the intval() function, and MySQL user's are quite happy to add a "GROUP BY" to every query, just in case...

vrijdag 11 april 2014

Active intelligence...

Sometimes I find myself wondering what the world is coming to, and just when I think it can't be that bad I see something that makes me think it's actually much worse...

Today I got a message from meetup.com, informing me about a new group that I might be interested in, called "Active intelligence". Sounds cool, no? So let's look at their intro:

"Active Intelligence Academy provides full day, intensive workshops to get you up to speed on technology topics that you're most interested in. Like us on Facebook!" 


donderdag 10 april 2014

GROUP BY, use it or lose it.

Dear MySQL users,

Please read the flipping manual about what GROUP BY is, and stop sticking it in every single query you write.

Thank you.

dinsdag 8 april 2014

Why I like PostgreSQL more than MySQL: regular expressions in a string-replace.

MySQL's regexp functions are limited to matching patterns, PostgreSQL can also use them to fetch substrings, and to replace substrings.

For example; to remove any spaces from a dutch zipcode:

SELECT regexp_replace('   134 5 AA   ', E'\\s', '', 'g')

That is: replace any whitespace character with an empty string, globally.

This way you can write a CHECK constraint, trigger or rule that accepts a zipcode in the raw format, and validate and clean it before storing it, making life a little easier on the application.

dinsdag 1 april 2014

To the management: read this.

Stefan Koopmanschap wrote a nice article that will seem every familiar to most developers, and that most managers really ought to read.

It explains, among other things, how management often manages to actually prevent the people they manage from doing the tasks they're assigned to do.