Dne 14. 12. 19 v 9:36 Darren Duncan napsal(a):
> With respect to SQLite, every column is the union of: Null, every Integer,
> every Float, every Text, every Blob.
OK, we can close this thread with that it is currently impossible to track the
declared column types through relational operations.
Just to explain what my generic program does: it is typically used as a filter
and works on the fly. The user feeds a stream of relations (tables) into it,
the program does some transformations (execute SELECTs in this case) and sends
a stream of another relations on the standard output.
The input relations have declared attribute types (currently boolean, integer
and string) and it does not allow mixing various types in the same column.
For example the user can do this:
relpipe-in-fstab | relpipe-tr-sql --relation "x" "SELECT * FROM fstab WHERE
type = ?" --parameter "ext4" | relpipe-out-tabular
x:
╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬───────────────────────────────────────┬────────────────┬────────────────╮
│ scheme (string) │ device (string) │ mount_point
(string) │ type (string) │ options (string) │ dump
(integer) │ pass (integer) │
├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼───────────────────────────────────────┼────────────────┼────────────────┤
│ UUID │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
1 │
│ │ /dev/sde │ /mnt/data
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
2 │
╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴───────────────────────────────────────┴────────────────┴────────────────╯
Record count: 2
If you look at the header, the integer types of the "dump" and "pass" columns
are preserved (I get the type from the sqlite3_column_decltype() function).
But if the user just slightly modifies the query:
relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT mount_point, dump+100,
pass+1000 FROM fstab WHERE type = ?" --parameter "ext4" | relpipe-out-tabular
x:
╭──────────────────────┬───────────────────┬────────────────────╮
│ mount_point (string) │ dump+100 (string) │ pass+1000 (string) │
├──────────────────────┼───────────────────┼────────────────────┤
│ / │ 100 │ 1001 │
│ /mnt/data │ 100 │ 1002 │
╰──────────────────────┴───────────────────┴────────────────────╯
Record count: 2
the type information disappears and former integers becomes mere strings (a
fallback generic type here). From the point of view of a developer who knows
SQLite internals, this is obvious. But from the user's point of view, this is
quite weird and unexpected behavior. Users expect that a mathematical operation
will return the original (integer) type or at least some other numeric type –
but not a text string. I would have to add something like --type-cast "dump"
"integer" option to explicitly specify the types and convert the strings back
to the integers. But I was wondering whether there is a more user-friendly
automatic way.
The source codes and more information are available here:
<https://relational-pipes.globalcode.info/>.
>
> With respect to a generic application programming language, every column is
> of the most generic type, such as "Object" or "Any" for examples.
>
> Now, if you want to be more precise, you can say that the type of every
> column is the union of all values currently in it. This means that the type
> of an empty column is the empty type consisting of zero values, which is a
> subset of all other types just as the universal type is the superset of all
> other types.
>
> Generally speaking, you want to support union types.
In my software, the most generic type is the text string. e.g. 123 will become
"123", true will become "true" or byte array (when implemented) will become a
hexadecimal text string. And it is currently also used as a fallback – if I can
not get a better type from SQLite, the attribute is returned as a string. So
the text string serves as that union type for me.
Franta
relpipe-in-fstab | relpipe-tr-sql --relation x "SELECT * FROM fstab WHERE type
= ?" --parameter "ext4" | relpipe-out-tabular
x:
╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬───────────────────────────────────────┬────────────────┬────────────────╮
│ scheme (string) │ device (string) │ mount_point
(string) │ type (string) │ options (string) │ dump
(integer) │ pass (integer) │
├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼───────────────────────────────────────┼────────────────┼────────────────┤
│ UUID │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
1 │
│ │ /dev/sde │ /mnt/data
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
2 │
╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴───────────────────────────────────────┴────────────────┴────────────────╯
Record count: 2
$ cat relpipe-out-fstab.txt | relpipe-in-fstab | relpipe-tr-sql --relation x
"SELECT mount_point, dump+100, pass+1000 FROM fstab WHERE type = ?" --parameter
"ext4" | relpipe-out-tabular
x:
╭──────────────────────┬───────────────────┬────────────────────╮
│ mount_point (string) │ dump+100 (string) │ pass+1000 (string) │
├──────────────────────┼───────────────────┼────────────────────┤
│ / │ 100 │ 1001 │
│ /mnt/data │ 100 │ 1002 │
╰──────────────────────┴───────────────────┴────────────────────╯
Record count: 2
relpipe-in-fstab | relpipe-tr-awk --relation "fstab" --define 't' string "ext4"
--where '(type == t)' | relpipe-out-tabular
fstab:
╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬───────────────────────────────────────┬────────────────┬────────────────╮
│ scheme (string) │ device (string) │ mount_point
(string) │ type (string) │ options (string) │ dump
(integer) │ pass (integer) │
├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼───────────────────────────────────────┼────────────────┼────────────────┤
│ UUID │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
1 │
│ │ /dev/sde │ /mnt/data
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 0 │
2 │
╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴───────────────────────────────────────┴────────────────┴────────────────╯
Record count: 2
relpipe-in-fstab | relpipe-tr-awk --relation "fstab" --define 't' string "ext4"
--for-each '(type == t) { dump += 100; pass += 1000; record(); }' |
relpipe-out-tabular
fstab:
╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬───────────────────────────────────────┬────────────────┬────────────────╮
│ scheme (string) │ device (string) │ mount_point
(string) │ type (string) │ options (string) │ dump
(integer) │ pass (integer) │
├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼───────────────────────────────────────┼────────────────┼────────────────┤
│ UUID │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 100 │
1001 │
│ │ /dev/sde │ /mnt/data
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 100 │
1002 │
╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴───────────────────────────────────────┴────────────────┴────────────────╯
Record count: 2
relpipe-in-fstab | relpipe-tr-guile --relation "fstab" --define 't' string
"ext4" --where '(string= $type t)' --for-each '(set! $dump (+ $dump 100)) (set!
$pass (+ $pass 1000))' | relpipe-out-tabular
fstab:
╭─────────────────┬──────────────────────────────────────┬──────────────────────┬───────────────┬───────────────────────────────────────┬────────────────┬────────────────╮
│ scheme (string) │ device (string) │ mount_point
(string) │ type (string) │ options (string) │ dump
(integer) │ pass (integer) │
├─────────────────┼──────────────────────────────────────┼──────────────────────┼───────────────┼───────────────────────────────────────┼────────────────┼────────────────┤
│ UUID │ 29758270-fd25-4a6c-a7bb-9a18302816af │ /
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 100 │
1001 │
│ │ /dev/sde │ /mnt/data
│ ext4 │ relatime,user_xattr,errors=remount-ro │ 100 │
1002 │
╰─────────────────┴──────────────────────────────────────┴──────────────────────┴───────────────┴───────────────────────────────────────┴────────────────┴────────────────╯
Record count: 2
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
