Re: Want to use CSV formatted file as table
Hi -
I looked into the CSV storage engine. From what I could tell, it is very hard to get working on Windows. Seems like you have to use the configure tool in unix then move the source files to windows to compile or something. Too hard for me. :)
So anyway, I ended up testing it on linux instead. I was disappointed w/ the results. For instance, after inserting some rows and then deleting a row, I had some corruption in the file. See example...
mysql> create table csvtst ( c1 integer ) engine = CSV;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into csvtst values ( 1 );
Query OK, 1 row affected (0.02 sec)
mysql> insert into csvtst values ( 2 );
Query OK, 1 row affected (0.02 sec)
mysql> insert into csvtst values ( 3 );
Query OK, 1 row affected (0.01 sec)
mysql> select * from csvtst;
+------+
| c1 |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.01 sec)
mysql> delete from csvtst where c1 = 2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from csvtst;
+------+
| c1 |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)
mysql> insert into csvtst values ( 4 );
Query OK, 1 row affected (0.01 sec)
mysql> select * from csvtst;
+------+
| c1 |
+------+
| 1 |
| 3 |
| 0 |
+------+
3 rows in set (0.01 sec)
mysql> insert into csvtst values ( 5 );
Query OK, 1 row affected (0.00 sec)
mysql> select * from csvtst;
+------+
| c1 |
+------+
| 1 |
| 3 |
| 0 |
| 5 |
+------+
4 rows in set (0.01 sec)
By the way, the contents of the file look like this...
"1"
"3"
^@^@^@^@"4"
"5"
Also, it would be nice if you could swap in a CSV file and then be able to query it. But when I tried this, it failed. I actually copied the file from my first test, so the format of the file should be right, but still I got an error. Not sure what it means...
mysql> create table csvtst2 ( c1 integer ) engine = CSV;
Query OK, 0 rows affected (0.02 sec)
(Then I overwrote file csvtst2.CSV w/ csvtst.CSV from the previous test.)
mysql> select * from csvtst2;
ERROR 1030 (HY000): Got error 1 from storage engine
mysql>
Final comment...looking at the windows source file (ha_tina.cpp), it seems like this code is a work in progress. Unless I am looking in the wrong place or wrong version (I downloaded 5.0), I am surprised the online documentation would mention the CSV storage engine (without any caveats), when it really doesn't work that well. But I am new to open-source, so maybe I am naive about the process.
See these comments from the source file (ha_tina.cpp). Doesn't sound very encouraging....
/*
Make sure to look at ha_tina.h for more details.
First off, this is a play thing for me, there are a number of things wrong with it:
*) It was designed for csv and therefor its performance is highly questionable.
*) Indexes have not been implemented. This is because the files can be traded in
and out of the table directory without having to worry about rebuilding anything.
*) NULLs and "" are treated equally (like a spreadsheet).
*) There was in the beginning no point to anyone seeing this other then me, so there
is a good chance that I haven't quite documented it well.
*) Less design, more "make it work"
Now there are a few cool things with it:
*) Errors can result in corrupted data files.
*) Data files can be read by spreadsheets directly.
TODO:
*) Move to a block system for larger files
*) Error recovery, its all there, just need to finish it
*) Document how the chains work.
-Brian
*/
(No offense meant to Brian btw.)
I'm hoping someone will tell me I'm an idiot and I'm doing something wrong and that the CSV storage engine really works well. :)
Can anyone comment on their experience with it?
Thanks,
Dave