Changeset: 5c26916f2a22 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/5c26916f2a22
Modified Files:
sql/backends/monet5/vaults/csv/csv.c
sql/test/file_loader/Tests/file_loader_field_separator.test.in
Branch: default
Log Message:
improved csv loader
added tsv/psv extentions
fixed isses with detection of last column seperator
added (initial) null detection (sofar only empty value)
diffs (154 lines):
diff --git a/sql/backends/monet5/vaults/csv/csv.c
b/sql/backends/monet5/vaults/csv/csv.c
--- a/sql/backends/monet5/vaults/csv/csv.c
+++ b/sql/backends/monet5/vaults/csv/csv.c
@@ -94,7 +94,7 @@ detect_delimiter(const char *buf, char q
break;
int nr = 1;
while( (s = next_delim(s, e, d, q)) != NULL && s<e ) {
- if (s+1 < e)
+ if (s+1 <= e)
nr++;
s++;
}
@@ -120,7 +120,8 @@ detect_delimiter(const char *buf, char q
}
typedef enum csv {
- CSV_BOOLEAN = 0,
+ CSV_NULL = 0,
+ CSV_BOOLEAN,
CSV_BIGINT,
CSV_DECIMAL,
CSV_DOUBLE,
@@ -140,6 +141,8 @@ static char*
csv_type_map(csv_type ct)
{
switch(ct.type) {
+ case CSV_NULL:
+ return "null";
case CSV_BOOLEAN:
return "boolean";
case CSV_BIGINT:
@@ -161,6 +164,15 @@ csv_type_map(csv_type ct)
}
static bool
+detect_null(const char *s, const char *e)
+{
+ if (e == s)
+ return true;
+ /* TODO parse NULL value(s) */
+ return false;
+}
+
+static bool
detect_bool(const char *s, const char *e)
{
if ((e - s) == 1 && (*s == 'T' || *s == 't' || *s == 'F' || *s == 'f'))
@@ -254,12 +266,14 @@ detect_types_row(const char *s, const ch
if (!types)
return NULL;
for(int i = 0; i< nr_fields; i++) {
- const char *n = next_delim(s, e, delim, quote);
+ const char *n = (i<nr_fields-1)?next_delim(s, e, delim,
quote):e;
int scale = 0;
types[i].type = CSV_STRING;
if (n) {
- if (detect_bool(s,n))
+ if (detect_null(s,n))
+ types[i].type = CSV_NULL;
+ else if (detect_bool(s,n))
types[i].type = CSV_BOOLEAN;
else if (detect_bigint(s, n))
types[i].type = CSV_BIGINT;
@@ -499,6 +513,8 @@ CSVprelude(Client cntxt, MalBlkPtr mb, M
(void)cntxt; (void)mb; (void)stk; (void)pci;
fl_register("csv", &csv_relation, &csv_load);
+ fl_register("tsv", &csv_relation, &csv_load);
+ fl_register("psv", &csv_relation, &csv_load);
return MAL_SUCCEED;
}
@@ -506,6 +522,8 @@ static str
CSVepilogue(void *ret)
{
fl_unregister("csv");
+ fl_unregister("tsv");
+ fl_unregister("psv");
(void)ret;
return MAL_SUCCEED;
}
diff --git a/sql/test/file_loader/Tests/file_loader_field_separator.test.in
b/sql/test/file_loader/Tests/file_loader_field_separator.test.in
--- a/sql/test/file_loader/Tests/file_loader_field_separator.test.in
+++ b/sql/test/file_loader/Tests/file_loader_field_separator.test.in
@@ -1,6 +1,5 @@
# tests to load cars (4 columns, 4 rows) data files with different field
separators: comma , (.csv), pipe | (.psv), tab (.tsv), semicolon ; (.csv)
-## next query incorrectly returns error: received 1 columns, expected 4 columns
query ITTI
select * from '$QTSTSRCDIR/cars.csv'
----
@@ -21,17 +20,10 @@ Lightyear
0
2022
-## next query incorrectly returns all columns as 1 column data. Should error:
received 4 columns, expected 1 columns
-query T
-select * from '$QTSTSRCDIR/cars.csv'
+statement error 42000!SELECT: The number of aliases don't match the number of
columns (1 != 4)
+select * from '$QTSTSRCDIR/cars.csv' cars(c)
----
-2000,"Ford","Focus",1994
-2001,"Honda",,
-2004,"Tesla","S3XY",2019
-2014,"Lightyear","0",2022
-
-## next query incorrectly returns error: received 1 columns, expected 4 columns
query ITTI
select * from file_loader(r'$QTSTSRCDIR/cars.csv')
----
@@ -56,7 +48,6 @@ statement error 42000!syntax error, unex
select * from file_loader(R'$QTSTSRCDIR/cars.csv') file(id, brand, model, year)
----
-## next query incorrectly returns error: 42000!SELECT: The number of aliases
don't match the number of columns (4 != 1)
query ITTI
select * from file_loader(r'$QTSTSRCDIR/cars.csv') as cars(id, brand, model,
"year")
----
@@ -77,16 +68,10 @@ Lightyear
0
2022
-## next query incorrectly returns all columns as 1 column data. Should error:
received 4 columns, expected 1 columns
-query T
+statement error 42000!SELECT: The number of aliases don't match the number of
columns (1 != 4)
select * from file_loader('$QTSTSRCDIR/cars.csv') cars(cols)
----
-2000,"Ford","Focus",1994
-2001,"Honda",,
-2004,"Tesla","S3XY",2019
-2014,"Lightyear","0",2022
-## next query incorrectly returns error: 42000!SELECT: file_loader function
failed 'Filename extension 'psv' missing'
query ITTI
select * from file_loader(r'$QTSTSRCDIR/cars.psv') as cars(id, brand, model,
"year")
----
@@ -107,7 +92,6 @@ Lightyear
0
2022
-## next query incorrectly returns error: 42000!SELECT: file_loader function
failed 'Filename extension 'tsv' missing'
query ITTI
select * from file_loader(r'$QTSTSRCDIR/cars.tsv') as cars(id, brand, model,
"year")
----
@@ -128,7 +112,6 @@ Lightyear
0
2022
-## next query incorrectly returns error: 42000!SELECT: The number of aliases
don't match the number of columns (4 != 1)
query ITTI
select * from file_loader(r'$QTSTSRCDIR/cars_semicolon.csv') as cars(id,
brand, model, "year")
----
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
