All posts by dgaspar

The SET option in mysqlimport / LOAD DATA INFILE : transforming values before insert

 

Let’s say you want to load a text file into a mysql table, but some mandatory columns are missing in the file or you have to modify some data first.
Well, the LOAD DATA INFILE statement has the handful SET clause for transforming values before assigning them to columns.
Take this example:

LOAD DATA INFILE 'my_users.txt'
INTO TABLE MY_TABLE
(@id, @ignore_col_1, @ignore_col_2, user_name, user_group)
SET loaded_from = 'manual import', id = (@id + 1000)

which loads a file like

3,blabla,dummy,Daniel,admin
4,hahaha,hehehe,John,newbie

The 1st column of the file becomes the @id variable (later transformed by SET), the 2nd and 3rd columns are ignored (they become variables not used by SET), the “user_name” and “user_group” columns are loaded directly. An extra column “loaded_from” (that was not in the file) is also loaded by SET.

Here’s the result:

id user_name user_group loaded_from
1003 Daniel admin manual import
1004 John newbie manual import

 

So.. it would be nice to have this SET feature in mysqlimport too.
But it’s still not officially supported in the current versions (5.x)! (I’ve filled a BUG, let’s hope it gets validated).

When you call mysqlimport, it ends up creating a LOAD DATA INFILE statement, but there’s no trace of the SET clause. Here’s the src code:

#client/mysqlimport.c
...
end= add_load_option(end, escaped, " ESCAPED BY");
end= add_load_option(end, lines_terminated, " LINES TERMINATED BY");
if (opt_ignore_lines >= 0) end= strmov(longlong10_to_str(opt_ignore_lines,
strmov(end, " IGNORE "),10), " LINES");
// COLUMN definition is the last thing before executing the statement:
if (opt_columns) end= strmov(strmov(strmov(end, " ("), opt_columns),")");
*end= '\0';
if (mysql_query(mysql, sql_statement)){
...

 

Ugly solution: SQL-injecting the SET clause in the --columns option! LOL
You must leave unbalanced parenthesis because the program concats "(" + "--columns" + ")"

#!/bin/sh

filename=my_users.txt

cols="@id, @ignore_col_1, @ignore_col_2, user_name, user_group) \
SET loaded_from = 'manual import', id = (@id + 1000"
# ATTENTION, you must leave an unbalanced parenthesis!!!!

mysqlimport5 --columns="$cols" "$filename"

mysqlimport will then run:


LOAD DATA INFILE 'my_users.txt'
...
INTO TABLE MY_USERS
(
@id, @ignore_col_1, @ignore_col_2, user_name, user_group) SET loaded_from = 'manual import', id = (@id + 1000
) 

If you have found a better workaround, please share! 🙂

HOWTO Backup your iPhone Voicemail with sender number and timestamp (using linux/macosx/cygwin)

Here’s how to backup all your iPhone Voicemails, in case your voicemail inbox is full. (requires a JB iPhone)
Run the following script in a machine with sqlite installed:

#!/bin/bash

#your iPhone IP:
IP='192.168.0.12';

USR='root';
VOICEMAILPATH='/private/var/mobile/Library/Voicemail';
scp "$USR@$IP:$VOICEMAILPATH/{voicemail.db,*.amr}" .
sqlite3 -column voicemail.db "select ROWID||'.amr '||replace(replace(datetime(date,'unixepoch','localtime'),' ','-'),':','_')||'_'||sender||'_('||ROWID||').amr' from voicemail" | xargs -n 2 -t mv

All your voicemail files (.amr files) will be copied to your machine with a filename like %Y-%m-%d %H_%M_%S_sendernumber(id).amr, ex: “2010-04-28-22_50_56_+33630027552(19).amr”

Expected output:

~/Desktop/voicemail$ ./voicemail.sh 
voicemail.db         100%   28KB  28.0KB/s   00:01
19.amr                       100%   12KB  11.8KB/s   00:00
20.amr                       100%   21KB  20.9KB/s   00:00
mv 19.amr 2010-04-28-22-50-56_+336xxxxxxxx_(19).amr
mv 20.amr 2010-05-01-16-20-26_+336xxxxxxxx_(20).amr