Can't update a CSV database or need to use workarounds?

[daluu]

I might have missed some page or example test, but a brief review of the wiki and some example tests, I don't seem to find much examples of inserts & updates of different database types. Seems most of the examples for various DBs are for selects where you can also dump the output of a select from one DB into another via "select into"

I was trying something like this basing code on the select and update examples:

var alasql = require('alasql'); alasql('UPDATE csv("/data/monitoring/logs/staging/statsdb.csv",{headers:true}) SET notes = "it works" WHERE device = "iPod2"');

but I get:

/Users/daluu/temp/node_modules/alasql/dist/alasql.js:1818 throw new Error(str); ^ Error: Parse error on line 1: UPDATE csv("/data/monitoring/l ----------^ Expecting 'SET', got 'LPAR' at Object.parseError (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:1818:15) at Object.parse (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:1883:22) at Function.alasql.parse (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:3982:16) at Function.alasql.dexec (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:4136:19) at Function.alasql.exec (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:4112:17) at alasql (/Users/daluu/temp/monitor/node_modules/alasql/dist/alasql.js:121:18) at Object. (/Users/daluu/temp/monitor/query.js:20:1) at Module._compile (module.js:460:26) at Object.Module._extensions..js (module.js:478:10) at Module.load (module.js:355:32)

Is updating a CSV DB directly not supported? Or did I just have the wrong syntax?

If not supported, what's best workaround? Use an in memory DB (or other) and then overwrite the CSV with in memory version to do the update via select into csv from in memory DB?

It would be nice to clarify on the wiki which DBs are and are not supported for the various SQL commands, because otherwise, one might assume it works against all.

[daluu]

Hmm... following examples on the site to create a DB, table (with fields/schema roughly matching CSV file), then select into newly created table from CSV seems to result in empty result "[]", or just returns the count of rows when I print "res" return value but not the actual data.

Querying directly with select * from csv works fine.

Would be nice if an example could be presented that shows you how to use this library to update a CSV database (then select back out the results to console log to verify the update).

[mathiasrw]

Thank you for a good input

It would be nice to clarify on the wiki which DBs are and are not supported for the various SQL commands, because otherwise, one might assume it works against all.

I really agree. Lets work on that.

As I understand the library: whats suported is SELECT INTO - so no update to files

I will have to dig into some examples and will get back to you. Probably tomorrow...

[agershun]

@mathiasrw Do we need to extend functionality with this statement?

UPDATE CSV(...) SET ... WHERE ...

[mathiasrw]

UPDATE CSV(...) SET ... WHERE ...

is a very good area to improve the library. I suggest we put it on the list of "really good things to implement after the critical ones".

If we implement this on CSV( we must offer the same functionality for the other export functions as forexample XLS(, JSON( and so on

[agershun]

Yes. The same for :

INSERT INTO CSV('a.csv') (a,b,c) VALUES (1,2,3);
DELETE FROM XLS('a.xls') WHERE a = 10;
UPDATE CSV('a.csv') SET b=20 WHERE a = 10;

We can have a problem with ```INSERT``` syntax, because parser can not
understand what is this:

INSERT INTO CSV('a.csv',{headers:true}) VALUES (1,2,3); INSERT INTO one(a,b,c) VALUES (1,2,3);

This functionality can be useful for command-line version of AlaSQL.

2016-01-11 16:11 GMT+03:00 Mathias Rangel Wulff <notifications@github.com>:

> UPDATE CSV(...) SET ... WHERE ...
>
> is a very good area to improve the library. I suggest we put it on the
> list of "really good things to implement after the critical ones".
>
> If we implement this on CSV( we must offer the same functionality for the
> other export functions as forexample XLS(, JSON( and so on
>
> —
> Reply to this email directly or view it on GitHub
> <https://github.com/agershun/alasql/issues/383#issuecomment-170544646>.
>

[mathiasrw]

We can have a problem with INSERT syntax, because parser can not understand what is this:

We have a lot of reserved words. Guess we have to add CSV, XLS ... and so on to the list of reserved words so that if one has a table named csv they have to

INSERT INTO `one` (a,b,c) VALUES (1,2,3);

Another thing:

INSERT INTO CSV('a.csv',{headers:true}) VALUES (1,2,3);

What will happen if the headers value is not set?

[mathiasrw]

Closed as its put on on hold on The Long List - only to be reopend when its time has come.