Keith's Blog

TxtSushi 0.5.1

2010-03-07T13:56:00.000-08:00

I've just released TxtSushi 0.5.1 on hackage. The only reason for this release was to update the license from GPL to BSD (there are no new features or fixes).

I initially released under GPL because I wasn't really sure where I wanted to go with TxtSushi and I knew it was easier to go from GPL to a more liberal license than to move in the other direction. A recent thread on licensing on haskell cafe convinced me that BSD is the way to go.

TxtSushi 0.5.0

2009-12-30T17:09:00.000-08:00

Hiya! I've uploaded TxtSushi 0.5.0 to hackage and there are quite a few changes. I still need to update the documentation on http://keithsheppard.name/txt-sushi which probably won't happen for a week or so (Edit: I have made the updates) but I'll summarize the changes here:

generalized the JOIN syntax to allow joins on arbitrary expressions (you used to only be able to join on column IDs). This test script demonstrates a join on a simple expression
added initial support for the "for x in [col1 .. col4] yield expr" syntax. See this test script for an example of how it works.
parser now works with UNIX, DOS and Mac newlines no matter which OS you're working on
added integrated help for all SQL functions and operators. Use "tssql -help" to get a list of all functions and operators. Use "tssql -help sum" to get help on the SUM function
added many new function definitions
Grouped data syntax generalization: you can now group tables multiple times (via nested selects) and join those grouped tables to other tables (grouped or not). You can even mix grouped and non-grouped columns in the same expression.
Many bug fixes and code simplifications (still learning how to write good Haskell). Most notably it is now very easy to extend tssql with new SQL functions and operations. Here's an example of what it takes to add a new function.
added support for bool constants in SQL expressions
improved error messages
Declared the "classic join" performance bug a feature :-)
and most importantly: changed the synopsis to "The SQL link in your *NIX chain"

Have fun and please send me your bug reports!

TxtSushi 0.4.0

2009-10-04T14:39:00.000-07:00

I have just uploaded version 0.4.0 of TxtSushi with the following changes:

General code improvements: fixed any -Wall warnings, improved help messages and SQL parser error messages
External sort: added an -external-sort option to tssql which tells tssql to sort on disk for JOIN and ORDER BY. This was tested on a 10G CSV file and it worked but it took 10 hours on my macbook! I think that this can be improved.
Nested select: TxtSushi now allows nested select statements
Classic joins: added support for older style join syntax. These joins are still unoptimized though so their time complexity is row_count^2 rather than the row_count(log row_count) complexity when using the INNER JOIN syntax
Transpose utilities: added transposecsv and transposetab utilities which will print a transposed version of the given table

Fellow Haskell Hackers Wanted:

I think TxtSushi fills a unique niche in the *NIX toolchain for tabular data. On one side you have python, perl, sed, awk which are all great for general purpose text processing but it takes a lot of hand rolled code to do table filtering/joining/transformation and it gets even worse if you want to be able to deal correctly with quoted fields. On the other side you have real databases which allow you to do all of these things but which tend to be heavyweight solutions that require data import/export and don't want anything to do with your other *NIX commands. TxtSushi is different because it takes your text tables (or event STDIN) as they are and doesn't want to be anything more than an SQL link in your *NIX tool chain.

The two long term goals that I have for TxtSushi are to improve it to the point where it is generally available on *NIX distro package managers and that it is "owned" and developed by a group instead of just me. These two goals really go together since I may not be able to do all of the work required to mature TxtSushi by myself. I thought about keeping these goals to myself until I could get to the point where the core architecture are solidified and the remaining work would amount to developing and integrating modules into the existing architecture, but I think things are just moving too slowly with me hacking alone. So, if you are interested in hacking TxtSushi with me let me know!

Off the top of my head here are some relatively self-contained contributions that can be made:

Extensible SQL Functions: This one should be fun. The way I implemented SQL functions isn't very pretty. The function parsers are in SQLParser.hs toward the bottom and the SQL Function execution code is in a big function guard toward the bottom of SQLExecution. I think we should have an SQLFunction type which would bundle up both the parsing and execution for a single SQL function/operator. The real payoff is that we could then have an XMonad-like configuration file that allows users to define their own SQL functions (Eg: if a user wants to define a STD_DEV aggregate function, now they can as long as they know haskell).
Improve table parsing: the table parsing code was the first thing I wrote and it shows. It should be replaced by something more like what you find in Real World Haskell: Using Parsec which is much more concise and tolerant of any end-of-line encoding
External sort: The external sort algorithm works but could probably use a more expert Haskell hacker's eye to become efficient (there were a couple of reasons I couldn't just use the external-sort cabal library as-is).
Efficient classic joins: The classic style WHERE joins should be just as efficient as the INNER JOIN joins. If you're interested in this I have an idea for what I think may be the easiest way to do this. See issue 7
Support for SQL Case Statement: See issue 8
Implement window functions like RANK() and ROW_NUMBER(): See issue 11
Test cases? Bug reports? Some other idea you have? ...

If you're curious, the simplest way to browse the source is here http://patch-tag.com/r/keithshep/txt-sushi.

MySQL responds to the threat ...

2009-06-19T18:24:00.000-07:00

... posed by TxtSushi's foray into the database market. http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html In all seriousness, this looks like a really nice solution.

HaExcel

2009-06-18T19:13:00.000-07:00

I stumbled upon HaExcel today and thought "Wow! This sounds just like TxtSushi." After looking more closely it has important differences. It handles import/export between spreadsheet and database rather than directly performing queries on a set of spreadsheets. If you're looking to bridge spreadsheets and databases you should give it a look.

TxtSushi 0.3.0

2009-05-31T19:17:00.001-07:00

Just released TxtSushi 0.3.0 which adds:

Improved SQL parsing and error reporting
GROUP BY ... HAVING support with several aggregate functions (see home page)
A little utility "namecolumns" which will add sequential column names to a table which is useful if your flat file doesn't have a header with column names.

TxtSushi 0.2

2009-05-21T20:20:00.000-07:00

I just released TxtSushi 0.2 with the following updates:

Improved type coercion. Some of the rules I was using before did not make sense. At some point I will write down what the rules are.
Added some extra functions/operators including a regex matcher. Here is the full list: SUBSTRING, UPPER, LOWER, TRIM, *, /, +, - (binary and unary), =, <> (not equal test), <, <=, >, >=, AND, OR, || (string concatination), =~ (regex matching)

Yay! TxtSushi 0.1

2009-05-16T19:08:00.000-07:00

I just released my first haskell application called TxtSushi. It's basically useful for processing comma-delimited tables with SQL select statements, plus some other small conversion and formatting utilities. Here's an example that I just tried out with real data and ... it works!

wget -q -O - ftp://ftp.informatics.jax.org/pub/reports/MRK_List2.rpt | tabtocsv - \
| tssql -table mgi - \
'select `MGI Accession ID`, Symbol, Chr, trim(`cM Position`)
from mgi where (Chr = 1 or Chr = 8 or Chr = 19) and trim(`cM Position`) = "N/A"
order by Chr+0, Symbol' \
| csvtopretty -

Which gives you:

MGI Accession ID|Symbol                     |Chr|TRIM(cM Position)
MGI:3829209     |100039643                  |1  |N/A
MGI:3823247     |100042382                  |1  |N/A
MGI:3826364     |665246                     |1  |N/A
MGI:3828086     |667118                     |1  |N/A
MGI:3829949     |Bmd5a                      |1  |N/A
MGI:3829954     |Bmd5b                      |1  |N/A
MGI:3829959     |Bmd5c                      |1  |N/A
MGI:3762525     |Drinkcacl24                |1  |N/A
MGI:3762526     |Drinkmgcl21                |1  |N/A
MGI:3762535     |Drinkmgcl24                |1  |N/A
MGI:3762388     |Drinksac5                  |1  |N/A
MGI:3836959     |Mir1927                    |1  |N/A
MGI:3836960     |Mir1928                    |1  |N/A
MGI:3837225     |Mir1981                    |1  |N/A
MGI:98018       |OTTMUSG00000002279         |1  |N/A
MGI:3840135     |OTTMUSG00000020948         |1  |N/A
MGI:3834078     |OTTMUSG00000026591         |1  |N/A
MGI:3826770     |Qrr1                       |1  |N/A
MGI:3826773     |Qrr1d                      |1  |N/A
MGI:3826772     |Qrr1p                      |1  |N/A
MGI:3844119     |Sfp1                       |1  |N/A
MGI:3832320     |T(1E2.1;8B1.2)2Lub         |1  |N/A
MGI:3843694     |Tg(tetO-Chrnb2*V287L)H3Gica|1  |N/A
MGI:3720916     |Tgq9                       |1  |N/A
MGI:3640786     |lrm1                       |1  |N/A
MGI:3822907     |384645                     |8  |N/A
MGI:1924337     |Ankrd11                    |8  |N/A
MGI:3844136     |Arrh1                      |8  |N/A
MGI:3705791     |Defa-ps3                   |8  |N/A
MGI:3837211     |Mir1966                    |8  |N/A
MGI:3837213     |Mir1967                    |8  |N/A
MGI:3837215     |Mir1968                    |8  |N/A
MGI:3837216     |Mir1969                    |8  |N/A
MGI:3833469     |OTTMUSG00000016477         |8  |N/A
MGI:3833836     |OTTMUSG00000031120         |8  |N/A
MGI:3844123     |Sfp3                       |8  |N/A
MGI:3628904     |T(Tp(1E2.1);8B1.2)2Lub     |8  |N/A
MGI:3720925     |Tgq18                      |8  |N/A
MGI:3640782     |gpg6                       |8  |N/A
MGI:88396       |Chrm1                      |19 |N/A
MGI:3762554     |Drinkqhcl2                 |19 |N/A
MGI:3762516     |Drinksac2                  |19 |N/A
MGI:3720096     |Hdlq59                     |19 |N/A
MGI:3837023     |Mir1950                    |19 |N/A
MGI:1914960     |Polr2g                     |19 |N/A
MGI:3843453     |Prdt5                      |19 |N/A
MGI:3828068     |Tgq29                      |19 |N/A

I created this because it is something that will be useful to my work (flat files are just about everywhere you turn in bioinformatics), but I'm really hoping that this will be something that is generally useful to other people.