Grumble Grumble

I’ve started to run into serious performance issues with my SQL queries. I mentioned my concerns earlier, but now (still long before production time) I’m already experiencing clearly unacceptable performance.

I’ve added a couple of thousand translated strings to the database, and uploaded another PO file for the record. That would run the following query for each line in the PO file:

"SELECT Translation.TranslatedString FROM Translation,Language WHERE " .
"Translation.LanguageID = Language.LanguageID AND Language.LanguageCode = '%s' " .
"AND Translation.EnglishString = '%s'"

This I think is called a join, and my boring data structures and algorithms experience says it’s an n^2 algorithm. But I figured a couple of things:

1. This has got to be almost the most basic kind of SQL query you can write. Select from table1 where the foreign key in table1 is the same as the primary key in table2. Database 101? Why is MySQL not smart enough to cache the result of the subquery, and only repeat it if table2 is changed in the meantime? Was that really such a hard optimisation to make, or did they leave it slow on purpose to encourage better query design?
2. My training as a real software developer tells me to avoid race conditions, and despite the extremely low likelihood that the LanguageID for french is going to change during the runtime of this query, I should never assume, and rely on the DBMS for making that association.

I figured wrong. The query above, after I inserted enough records into Translation would take ages, my page would take 5 minutes to load during which time MySQL would use 97% of the CPU. Unacceptable.

I had to step over my reluctance of caching a key outside the DBMS and run a separate query to obtain Translation.LanguageID, and ended up with this query in my loop:

"SELECT Translation.TranslatedString FROM Translation,Language WHERE " .
"Translation.LanguageID = '%s' AND Translation.EnglishString = '%s'"

Works much faster. So much faster that I’ve almost stopped thinking about extra hardware for MySQL. Shame that I had to do this.

Here’s something not many people working with PO files have run into. What happens when your english/translated string contains a <b> tag and you try to display that string on a webpage? Luckily I have one of those (in Asunder, where it’s actually a GTK formatting tag, not an HTML tag), so I ran into this problem already.

Piece of cake to fix, PHP has a function called htmlspecialchars() which will escape the special-meaning characters such as “<” in strings. I can use that function before sending my strings over as HTML.

Now what happens if my PHP sends the browser not HTML but JSON, and I construct the page using the data in that JSON? Any guesses? Nothing happens. Because I create a DOM text node and put the string into there – it just shows up with the “<” and ‘”‘ and all the other special chars.

Cool.

Back in 2009 I wrote a post complaining about the needless complexities plural forms introduce to the i18n process. Now I ran into them again.

Working on the OSTD I have to make sure I work with all kinds of PO files, and that has to include PO files with plural forms. The format of PO files is not a standard, mostly beacuse there’s only one implementation for fully handling them (GNU gettext). Heck, it’s not even a spec. I was barely able to piece together an understanding of possible variations of the format using the gettext manual and looking at existing real PO files. That and the fact that the format is kind of loose and everything else I mentioned in the previous post made me hate plural forms even more than I did before.

My implementations of the PO file parser and writer do not handle plural forms. I am hoping that I’m ignoring them properly when reading, and it’s ok that they’re missing when writing. And I have no plans of supporting them in the future. I will use what little influence I have to convince app maintainers to not use them, or to get rid of them.

A long long time ago I decided to do an experiment. I ported my open source application ISO Master to Windows, and kept the port as proprietary software.

The experiment paid off, and I definitely made enough money to pay myself for the porting work, but that was then.

In december last year my webserver uploaded 12GB worth of the shareware windows version of ISO Master, typical month. The sales have been slumping off year over year, and last month they were under 20$.

Now that I have my new project (OSTD) which I’m excited about, and I’m going to need bandwidth for – I am looking for ways to save computer resources, and one of those precious resources is bandwidth.

Should I keep the demo version online? Money isn’t really an issue, these days I make peanuts from it. But I have an archivist mindset, I hate removing stuff from the internet, especially if that stuff works. So I am leery of just removing that file.

Also there are links to the Windows version on all sorts of websites, including for example this and this. Knowing that I get so much traffic via those websites helps me feel more proud of my accomplishments, and beeing a negative kind of guy that’s definitely important.

Perhaps when the OSTD is ready for release I will run a suite of performance tests and decide whether the bandwidth the windows users are sucking up is one of the problems I need to address.

There are a number of articles and blog posts out there trying to explain scope and closures, but overall I’d say that a majority of them aren’t crystal-clear.

No sh**. I’ve been trying to figure out how scope works in JavaScript on-and-off for the last several years. I was never a committed JS programmer though so it wasn’t important enough for me to make sure I learn it once and for all.

Why is it that something as simple as scope is so complicated? That was a rhetorical question, I don’t really want to hear excuses. Last week I was working on some moderately advanced JS code and sure enough I spent hours finding impossible bugs caused by assignments that overwrote values in the wrong variables.

I have no choice but to suck it up, but this blog is a partial registry of my complaints, and this one was definitely worth mentioning.

If you read the slightly older post and look at its screenshot and do some thinking – you might like me wonder this: given a bunch of JSON with multiple selections which can be modified in JavaScript using a form.. wait, modified using a form?

One of the nice things about json is you can just do myjson[x].whatever[whatever].12 = “ABC” and it works. But if all you have is a <select> element and an onClick handler – that’s not so straightforward.

You can store the string “[x].whatever[whatever].12″ in the value field of the <option> but sadly you cannot just do myjson”[x].whatever[whatever].12″ = “DEF”, that’s a syntax error.

I had to wonder and look for a while, I even found something called JsonPath, which I got really excited about until I realised it’s only for reading (what exactly is the point of it then?). Today I found the solution: eval!

So continuing the lame example above I would simply do this: eval(‘myjson['+x+'].’+whatever+’['+whatever'+'].’+12+’ = “‘+”DEF”+’”‘) Roughly speaking, I haven’t tested this line. But you get the point?

Now that works but luckily because I’m testing with a real pot file I found a bug – all the ‘\n’ literals in the strings are replaced with newlines by eval, which is not what I want. I found a solution that not only fixes that but I hope will prevent my evals from being hacked: I escape all the backslashes and the single quotes in the string before giving it to eval:

eval(jsPath + ” = ‘” + text.toString().replace(/\\/g,’\\\\’).replace(/’/g,”\\’”) + “‘;”);

Yey!

PHP has this really neat function, json_encode(). It can take an object of whatever type, including my own class with child arrays/classes, and make a valid JSON string out of it. I was going to write this function myself but I found PHP already has it.

There’s one concern I have about it – it takes the entire object tree and makes json out of it, and all my member variable names end up as keys in the JSON. So without much difficulty you can look at the JSON and see exactly how I structure the data on the server in the PHP.

I’m not sure I really care but this makes me uneasy. It just doesn’t feel right. Maybe it will be ok.

I got to implementing one of the primary use cases for OSTD – user uploads a template .pot file and gets a bunch of .po files with as many translated strings as possible.

From a design point of view this isn’t a big deal: parse the .pot into a data structure, make a query per string to the database, and save the results.

But wait, what if there is more than one translation (in the same language) per english string? I expect that to happen quite often, so I have to handle that case from the start.

What I decided to do was not create the po files but only create in-memory representations of them, send them over to the user as json, generate the entire body of the webpage from that json, allow the user to make his selections, and post the modified json back to the server.

Only then would I generate the actual po files for the user to download. This will save some disk space (not that I care much about that) but also it’s pretty interesting technically. Possibly this saved me quite a bit of work too – otherwise I’d have had to reparse a bunch of po files I myself have generated.

Here’s a snippet, the blue string has more than one option. All the tables with all their contents are JS-generated:

I think I mentioned earlier how I almost started writing a website in C and quickly realised that wasn’t the right tool for the job, and switched entirely to PHP.

For the work I did today I needed a good set of data structures:

• A set of files
  • Each with a set of english strings
    • Each with a set of 0 or more translations

Pice of cake, right? Right, it would have taken me a half an hour in C, took me over six hours in PHP.

One of the problems was a surprise in scoping. Turns out in PHP there is no such thing as block scoping, and they forgot to mention that in the manual (isn’t it obvious there’s only per function and per file scope?). This created some very weird bugs that took a lot of printing to figoure out.

Then there’s the arrays. How can you have a programming language without 0-indexed arrays? PHP forces you to manage the indicees yourself, since their ‘array’ is actually a hash table. No vector either, not list, basically only a hash table. Can get used to it I guess but was it really so hard to have something more structured?

Then there are the classes. I needed to use them because there is no concept of a struct and the arrays are so retarded. I won’t go too much into it, let me just say that I suspect classes in PHP resemble classes in Perl. Sure you can have them, but don’t expect them to be easy to use.

All this complaining, you say, but didn’t it take you years to learn how things work in C, what’s the problem with taking the time to learn how things work in PHP? Well – I would accept that if it weren’t for the prevailing opinion that PHP is easy to learn. It isn’t: you can get started using it very quickly but its similarities to C in syntax make it harder (not easier) to learn it well.

And yeah, the same applies to JavaScript, but whatever. That’s for when I’m ready to do something with this same data I mentioned above in the browser. Gonna be fun :)

In the last year I’ve had to use CPAN several times. Perl people are all fancy and insist on all the perl modules to have unit tests. Commendable, except when it doesn’t work.

At least half of the times I used CPAN the unit tests failed. CPAN smarty pants decides not to install modules that have unit tests that failed. That’s a good thing, right? Wrong.

The problem is that unit tests usually fail not because the software is broken but because the unit tests are broken. Writing unit tests is somewhat similar to writing design documents – generally speaking they are obsolete as soon as you’ve written them, they are never complete, and they don’t account for all the possible environments.

So when CPAN fails, what am I as a user supposed to do, go file a bug report and wait a few months for someone to possibly fix it? Be real. I just run cpan -f to force an install despite failed unit tests. And given that I’ve had to do that 50% of the time I wonder if the typical cpan user simply adds that flag in by default.