Why does eval.c lack a row separator parameter?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Why does eval.c lack a row separator parameter?

petern
Is there a practical reason why eval.c was designed with only two
parameters?

https://www.sqlite.org/src/artifact/f971962e92ebb8b0

Why eval(X,Y) instead of eval(X,Y,Z)?  The second form with both an
optional column separator Y and an optional row separator Z is far more
useful.

I develop an interesting test case below.  Using the proposed extension
function eval3(X,Y,Z) in native SQLite, I compute a persistent db meta-data
table 'columns' by aggregating the output of PRAGMA table_info() over the
system table.

Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> --table meta data experiments
sqlite> .load sqlite-ext/eval3
sqlite> .load sqlite-ext/csv
sqlite>
sqlite> DROP VIEW IF EXISTS columns_refresh;
sqlite> CREATE VIEW IF NOT EXISTS columns_refresh AS SELECT (null)refresh;
sqlite> DROP TRIGGER IF EXISTS columns_refresh;
sqlite> CREATE TRIGGER IF NOT EXISTS columns_refresh INSTEAD OF INSERT ON
columns_refresh BEGIN
   ...> SELECT eval3('DROP TABLE IF EXISTS columns');
   ...> WITH columns_csv_str AS
   ...> (SELECT group_concat(eval3(printf('pragma
table_info(''%s'')',name),',',','||name||char(10)),'')str FROM
sqlite_master WHERE type='table')
   ...> SELECT eval3(printf(
   ...> 'CREATE VIRTUAL TABLE columns USING csv(data=''%s'',schema=''CREATE
TABLE x(cid,name,type,not_null,dflt_value,pk,tableName)'')'
   ...> ,str)) FROM columns_csv_str;
   ...> END;

sqlite> .header on
sqlite> SELECT * FROM columns;
Error: no such table: columns

sqlite> CREATE TABLE artist(
   ...>   artistid    INTEGER PRIMARY KEY,
   ...>   artistname  TEXT
   ...> );

sqlite> INSERT INTO columns_refresh VALUES(1);

sqlite> SELECT * FROM columns;
cid|name|type|not_null|dflt_value|pk|tableName
0|artistid|INTEGER|0||1|artist
1|artistname|TEXT|0||0|artist

sqlite> CREATE TABLE track(
   ...>   trackid     INTEGER,
   ...>   trackname   TEXT,
   ...>   trackartist INTEGER,
   ...>   FOREIGN KEY(trackartist) REFERENCES artist(artistid)
   ...> );

sqlite> INSERT INTO columns_refresh VALUES(1);

sqlite> SELECT * FROM columns;
cid|name|type|not_null|dflt_value|pk|tableName
0|artistid|INTEGER|0||1|artist
1|artistname|TEXT|0||0|artist
0|trackid|INTEGER|0||0|track
1|trackname|TEXT|0||0|track
2|trackartist|INTEGER|0||0|track

sqlite> SELECT (tableName||'.'||name)int_cols FROM columns WHERE type LIKE
'INT%';
int_cols
artist.artistid
track.trackid
track.trackartist
sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users