Database Fun

It may not sound like much of an accomplishment to those people out there with database experience, but I’m well on my way to hooking up the web frontend, Python heavy-lifting and application server (WebKit), and the sweet MySQL database backend. I’ve got all the pieces working and talking to each other, playing nicely, etc. Now the only things left to do are to create the production tables, populate them with current data, and re-write some of the Python data manipulation code that’s pulled from WebKit.

And this is just one of the many reasons that Python rules: query the LDAP server, get all contacts, write tab-delimited line for each in a huge text file, then call the “load data local infile” through the MySQLdb module… all in one handy-dandy little method. It’s fuckin’ brilliant.

On the down side, I couldn’t get Webware’s “MiddleKit” component to work properly. But, that’s probably for the best; I really should learn the ins and outs of SQL. Also, no mail client integration with the MySQL contacts database, which is blindingly fast compared to the shoddy OpenLDAP installation we’ve currently got running. That mail client thing is sure to be a sticking point… I may have to learn how to construct schema and install/upgrade the goddamned LDAP server after all.

People at work can be so whiny sometimes.


3 thoughts on “Database Fun

  1. You could do all that in one method in any language.

    Perl advocates will claim it can be done in one /line/, but don’t listen to them.

    Why bother with the tab file step? Why not just build up a data structure and blat it into the DB directly? In other words:

    schema_list = read_ldap(stuff_you_want)
    while(schema_list) {
    insert into my_db values (foo, bar, baz, quuz)

    Opening files via a long-lived Web server process should be a no-no.

  2. that schema_list should probably be an array of pointers to the info from each read (since there’s a lot of stuff in each read, right? ) And pardon that pun.

    Sorry for any confusion.

    [bitter mode=on]
    I thought the reason I didn’t interview there was there was someone who worked there who was supposed to deal with these kinds of issues.
    [bitter mode=off]

  3. The Python interface to the LDAP server returns a data structure (dictionary) based on the schema. Inserting a row into the table requires you to put the data in the proper order anyway (gotta call record[specific_key] to get it), so why bother with the “cursor.execute(”’insert into contacts values (tons of shit in order)”'” for 2000 records when you can iterate over every record, simply pick out the desired pieces, ‘\t’.join(pieces) + ‘\n’ and write them all to the text file? That takes about 2 seconds. Then it’s just the “load data local infile” at the mysql prompt. That takes about 0.02 seconds.

    Plus, it’s easier to verify the structure/order of the text file before slamming it into the table. Otherwise, you’re blindly doing inserts, and if something is ugly, it’ll only tell you that there were warnings after the fact without actually telling you what they were. Sure, it’s just testing/proof of concept right now, but why risk throwing in unusable crap, especially when the same script is going to populate the production table?

    All of it done on the filesystem, no long running web process involved today. Populate the database thusly, and the web process will open/close a database connection only when necessary (i.e., not cached), and not a file.

Comments are closed.