Emacs SQL mode

Took me sometime to start working again with MySQL effectively. Again I had to find a decent clinet that allows me to edit a script and execute the commands from within the buffer for testing.

I looked into VIM plugin dbext, and it sucks!
I was not able to get it to easily get it to work, and after sometime finally it worked, but openning a file in VIM, and editing it (even non SQL), caused the screen to go dirty with garbage every where. C-L to redraw was not a help. I had to find something else !

So, emacs sql-mode comes with emacs 24.4.1 (on gentoo). So Let's open an sql script with emacs, then :

• M-x sql-mode
• M-x sql-mysql (fill in the values).
• M-x sql-set-product (type mysql or postgresql .... etc).
• M-x sql-set-sqli-buffer

Now, highlght the SQL statments, and C-c C-r or just C-c C-b to send the whole buffer. Enjoy !

StackOverflow: sql-set-sqli-buffer "there is no suitable sqli buffer"
Additional setup tips.

Mounting database as a file

Postgresql offers the ability to query external data. In some scenarios this can be really useful. This called SQL/MED ("SQL Management of External Data").

One of those cool scenarios is when you need to query a file inside code as if it was a database table. To do so you need to have at least postgresql-9.1.

We will use the simplest file format to demonstrate the idea.

Create a CVS file:

Year,Make,Model,Length
1997,Ford,E350,2.34
2000,Mercury,Cougar,2.38

Create a postgresql server:

CREATE SERVER file_server FOREIGN DATA WRAPPER "file_fdw" ;

Now it's the time to create the table:

CREATE FOREIGN TABLE test (
year int,
make text,
model text,
length float
) SERVER file_server
OPTIONS (format 'csv', filename '/tmp/test.txt', header 'true', delimiter ',', null '');
 

CREATE FOREIGN TABLE

Let's try a select:

testdb=# SELECT * from test ;                                                      
 year |  make   | model  | length 
------+---------+--------+--------
 1997 | Ford    | E350   |   2.34
 2000 | Mercury | Cougar |   2.38
(2 rows)

Change a value in the file, and re-run the select. You should see the changes reflected in query results.

Additional Forgein Data Wrapper exists and they are cool. Using them, you can query 'twitter', MySql tables,

LDAP servers.

Additional documentation:

Foreign data wrappers

For those who likes this idea but don't use postgresql, here's a perl utility that may help:

DBIx::FileSystem

Related documents:

http://archive09.linux.com/feature/127055 

https://github.com/bianster/mysqlfs

http://www.nongnu.org/libsqlfs/

http://www.perlmonks.org/?node_id=397814