8 Posts Tagged 'SQL' RSS

Goodbye Tokyo Cabinet, hello PostgreSQL?

The first version of this blog used MySQL; then I switched to Tokyo Cabinet. But now I've switched back to PostgreSQL. Here's why.

June 29, 2010 @ 4:32 AM PDT
Cateogory: Programming

Adminer, where have you been all my life?

How do you view and edit data in a mysql DB? Lots of ways.

There's always commandline mysql. This is how I do it around 50% of the time. But it could be better. ASCII-art table dumps are not the easiest things to read, and readline-based history and editing only gets you so far.

phpMyAdmin is what I grew up with. It's pretty good but it's way too heavyweight. It also has all kinds of funky Javascript, to the point where I actually use Greasemonkey to remove some of it. Auto-selecting query text when you try to edit it in particular drives me crazy. Auto-selecting ANYTHING on Linux, where selecting text usually equals clobbering the X clipboard, is a really really bad idea. Last I tried it's a one line of Javascript in Greasemonkey to fix this by the way:

document.getElementById('sqlquery').removeAttribute('onfocus');

But phpMyAdmin may have changed since last I used it, it's been a while.

There's the native mysql GUI, called mysql-gui-tools in Gentoo. It's a standalone app that's pretty good, but the Linux version is gimped up compared to the Windows version for some strange reason. In any case it seems to be discontinued or something. There's some new MySQL Workbench thing coming, which I'll probably try once it hits Gentoo, but it looks like overkill for my simple needs.

SQuirreL SQL is another cross-platform GUI app. It connects to lots of different kinds of DBs (pretty much any DB that Java can talk to), so at work where I have get my mysql server to talk to someone else's MS SQLServer (ugh) I use this. But it's very heavyweight and not the most enjoyable interface.

Last week I chanced upon something pretty good. Adminer is a single PHP file you throw on a server and there you go. It gives you something vaguely similar to phpMyAdmin but far more lightweight. There's very little Javascript messing with my query-writing and the styling is minimal and easy to read. I don't know how secure it is, so I don't plan to put it on any public servers, but on my test server doing web development it's good. I love bouncing back and forth between a the database in one browser tab and my website in another tab. This is what I use now.

November 28, 2009 @ 9:47 AM PST
Cateogory: Programming

Wordpress DB migration - encoding fun

Note to self. Using mysqldump to dump a table with latin1 encoding and then editing it and importing it into a second DB with utf8 encoding isn't a lot of fun.

I found some instructions for properly opening mysqldump files in Vim with the proper encoding. I'm still somewhat confused what the difference between enc and fenc is in Vim but this worked. Eventually.

Also during the hour of banging my head on my keyboard, somewhere I read that using shell redirects to dump the mysqldump output to a file is sometimes a bad idea if there are multibyte characters in it, and instead you should use the -r flag to specify an output file. Could be true, I don't remember.

March 07, 2009 @ 6:28 AM PST
Cateogory: Programming
Tags: SQL, Vim

Ruby on Rails migrations

I started a new RoR project recently, and for the first time tried using migrations. They're mildly acceptable yet I still find myself not caring much for them.

The benefits of migrations as I understand it (and why those benefits don't matter to me) are:

  • You get to "version control" your database schema.

But if I want version control, I'll use a real version control system. A bunch of files in a directory sorted by a numeric prefix is rudimentary compared with Bazaar or SVN or anything else.

Migrations also require you to write all the code to "rollback" your changes yourself by hand, which is tedious. Every time I write an add_column I have to write a corresponding remove_column too. Many times I've gotten the add part right, and typoed the remove part, so the migration was broken, and I didn't realize until much later. Many times I wrote a remove part that I never ever had a need to use.

Because migrations are a single big long linear path, rolling way back to some point in history will obliterate a lot of changes you may want to keep that were made after that point; the older a migration is, the less likely you'll ever run it again.

Maybe I should be unit testing my migrations, you may say. I say what's the point? I'm adding a column to a DB. Do I really need to test that? Unit testing is fine, but there's a point beyond which it becomes silly. Especially for a web app, especially for something so trivial. * You can write DB stuff in Ruby rather than writing SQL manually.

This saves you a little bit of effort. But if you know SQL, it's not THAT hard to write an ALTER TABLE query. In fact given the whole script/generate migration SomeLongName step and then opening and editing and saving and testing and uploading and running the migration file, it's actually far faster for me to drop into a mysql prompt and do it that way.

There are also some things you can't do with Rails migration methods, and you need raw SQL. In which case you end up writing SQL anyways. Depending what you're doing, this may never happen or it may happen frequently enough to be annoying. * You can deploy your DB changes by uploading the migrations and running rake.

This is a good thing, but you can also save some SQL into a text file and run it by piping it into mysql from the command line, or importing it via phpmyadmin, or various other methods. * Migrations are database-agnostic, whereas SQL is usually tied to a single database.

My first reaction is, so what? What are the chances that I'm going to change my DB engine? Not very likely in the kinds of things I work on. My second reaction is that the kind of queries you write to create and edit tables are so generic that they're likely to work in most or all DB engines that Ruby supports anyways, or will work after minor edits.

  • Migrations do some magic things like automatically setting up an id field for your models etc.

This is the only real benefit to migrations that I can see: a small amount of time savings and convenience and integration with the rest of the tangled mess of RoR.

What I'm trying right now is writing all my table creation code in SQL, putting it all into .sql files, and putting those files under version control in Bazaar. This is better version control than migrations offer. And my table definitions are completely intact in a single file each, which is easily browsable and editable. (I think Rails lets you export schemas too, but it's not as nice as having them in files to look at and edit directly in my opinion.) If I want to see differences in my schema over time, I can bzr diff the files that contain them. If I want to take a single table and reproduce it in some DB somewhere, I can run that single .sql file. If I want to deploy, I rsync the files to my server and pipe them into a mysql prompt.

The good thing about RoR is that I can completely ignore migrations if I don't like them. And for my large legacy RoR app, I am ignoring them. However I plan to stick with them on this new app as far as I can; maybe more benefits will reveal themselves.

March 12, 2008 @ 4:15 PM PDT
Cateogory: Programming
Tags: Ruby, SQL, Rails

Vim brute force

In the end all code ends up as machine language. Generally the way to write effective code is to use a high-level language and let a smart compiler expand a tiny bit of high-level code it into a great deal of low-level machine language.

But another way I seem to end up doing this is using vim. I will generate mounds and mounds of temporary, throw-away, ugly code, but I will generate it extremely fast using vim. It's almost like vim is acting like an extremely high-level compiler; I take a very-high-level idea and using a little bit of vim trickery, I let vim expand it out into a large amount of high-level code.

Say I have a data file with two huge tab-separated columns; I need to insert the value from the second column into a table anywhere I find the value in the first column. There's possibly some way of doing this with one or two SQL queries to slurp the data file directly, but I don't know how to do it in MS-SQL server at work (ugh) and the brute-force vim/Perl way of doing it comes immediately to mind:

:perldo s{^([^\t]+)\t(.*)$}{UPDATE table SET field_x = '$2' WHERE field_y = '$1'}

It could also be done with a native vim regex obviously. Either way I end up with pages upon pages of SQL commands each of which does a single update. This is sloppy and slow, but it scales better than you'd think. I can do many thousands of inserts in this way in a few seconds. If I was doing millions rather than thousands I'd probably take the time to do this the right way, but I rarely if ever find myself needing to do millions of updates.

Is sloppy, ugly high-level code still bad if you didn't actually WRITE it, but had a program quickly write it for you? Machine language is sloppy-looking too, but it's sloppy for a reason (i.e. that's what computers understand). My sloppiness is for another reason: I'm lazy and this saves me time. But I feel like I may be developing bad habits. Then again there are times when I only need a hammer for a few seconds to pound in a nail or two, and I'm not trying to build a whole house. In time like those any hammer will probably do.

February 19, 2007 @ 2:19 PM PST
Cateogory: Programming
Tags: Perl, SQL, Vim

Re-categorizing Lots of Wordpress Entries

I decided to add a "Gentoo" tag to each post in my WordPress blog that has a "Linux" tag on it now. Reason being that Gentoo is the only Linux flavor I use, and Gentoo deserves some lovin'.

However I didn't want to just change the name of the "Linux" category to "Gentoo". I was afraid it would mess up my permalinks. WordPress doesn't really have "tags", they're really categories. So I figured I'll just keep both; both tags are applicable anyways. But re-tagging 50+ entries manually doesn't seem like a lot of fun, does it?

So I made a new category called Gentoo. Then I ran a query similar to

INSERT INTO wp_post2cat (post_id, category_id) 
SELECT post_id as post_id, 33 as category_id 
FROM wp_post2cat 
WHERE category_id = 5

where 33 is the ID of the Gentoo category, and 5 is the ID of the Linux category. Now, each post tagged "Linux" is also tagged "Gentoo". The other thing to do is edit the category_count field value in wp_categories, for the Gentoo category. It was 0, so I set it to the number of posts it should have.

Et voil?. (And now, I get to add a SQL tag! New tags are always exciting.)

September 14, 2006 @ 4:08 AM PDT
Cateogory: Linux

Vim: fun with filters

Vim lets you pipe text through an external filter. There are some obviously nice ways to use this in Linux, like

:!sort | uniq

which will sort all your lines, and then get rid of duplicate lines. But you can do things that are much more sophisticated if you write your own scripts which read from STDIN and output something back to STDOUT.

For example I wrote this Ruby script.

#!/usr/bin/ruby

del = %r{#{Regexp.escape ARGV[0]}} if ARGV[0]
del ||= %r{\s+}
STDIN.each do |line|
    puts '(' + line.strip.gsub(/'/,"''").split(del,-1).collect{|x| "'#{x}'"}.join(',') + '),'
end

This will take a line full of delimited fields, escape all the single-quotes, split into fields on the delimiter, wrap each field in single-quotes, put commas between the fields, wrap each line in (), and put a comma at the end of the line. You can either specify a delimiter, or don't specify one and it'll default to splitting on whitespace. I use this to turn a delimited ASCII file of data into a form suitable for an INSERT command in SQL. So if I start with this:

bob|2|3|Some description
chester|1|4|Another description
sarah|99|0|Let's try an apostrophe

and run this in Vim:

:%!sql_wrap.rb '|'

I get this:

('bob','2','3','Some description'),
('chester','1','4','Another description'),
('sarah','99','0','Let''s try an apostrophe'),

Or consider another simple example. This script will HTML-escape text:

#!/usr/bin/ruby

require 'cgi'

STDIN.each do |line|
    puts CGI::escapeHTML(line)
end</pre>

So it'll turn this:

Is 5 > 3?  Yes, & isn't that neat?

into this:

Is 5 &gt; 3?  Yes, &amp; isn't that neat?
August 22, 2006 @ 1:06 PM PDT
Cateogory: Programming
Tags: HTML, Ruby, SQL, Vim

Data

This will be one of my few "happy" rants.

I've known I wanted to be a code monkey since I was in high school. But if you'd asked me what I wanted to do back then, I'd have likely said "program games!" or "something flashy having to do with graphics!".

When I got to college and studied computer science, I was at first horrified to learn that most of computer science was pushing data around. A whole class on how to store data in hashes, stacks, and trees. Another whole class on how to sort data once it was in those structures. It was terribly boring to me at the time.

How interesting that pushing data around turned out to be so darned fascinating. I think perhaps it was boring because I was no good at it before. I just spent a couple days turning a flat HTML table into a normalized SQL table. A couple years ago I'd have sat there copying and pasting from one file into phpmyadmin. Now, I bang out some Perl, parse up the HTML, turn it into a SQL query, and run it. I'm probably at least an order of magnitude more productive now at nearly EVERYTHING than I was even a few years ago.

No matter what you want to do with computers, whether it be designing games or GUI design or web design or general software engineering or whatever, a huge part of it is going to end up being "playing with data". Getting data from here to there, and translating it along the way. Storing and organizing the data so that you can do something with it. Your source code itself is just another form of data, on one very important level. The fact that I find all this so much fun is possibly one of the reasons I so love Perl.

June 19, 2006 @ 2:52 AM PDT
Cateogory: Rants