Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Scott Robinson wrote:

> What version of SQLite are you using for this? I just did the
> following and do not see the string quoted values you are describing:

I'm sorry, the example given was just the current one I was
using to test the fix. In general I found the application was
quoting all Numbers and I was WRONG about the meta data stored
in the db file. It seems in almost all cases Sqlite is doing
a very good job of storing meta data I think.

Too clarify my application is coded in Java and is using a JDBC.

The example belows indicates that even when a mistake is made for
instance in declaring a type of TINYIN, the SQL type stored or
derived from the JDBC for the meta data is, 4, INTEGER. Good
guess!

CREATE TABLE exnumeric(
weight TINYNT NOT NULL,
speed DOUBLE);

NameJava Class   SQLTypeSQL Type Name
weight  java.lang.Object4   TINYNT
speed   java.lang.Object8   DOUBLE

In general if a table type is declared outside the defined SQL
types it still, for example (weight, kilograms), is defined as
TEXT it seems.

The issue I was having had to do with keying off only the Java
Class which in all cases is java.lang.Object. Most other databases
return for example the speed column, as java.lang.Double
from a Java ResultSetMetaData.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[Scott Robison]

On Wed, May 30, 2018 at 12:15 PM, dmp  wrote:
> DROP TABLE IF EXISTS mySinkDBTable;
> CREATE TABLE mySinkDBTable (
> key_id1 INTEGER UNSIGNED NOT NULL,
> key_id2 INTEGER UNSIGNED NOT NULL,
> text VARCHAR
> );
>
> --
> -- Dumping data for table mySinkDBTable
> --
>
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
> Corrected:
> INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');
>
> Since the user is allowed to store the metadata for the table
> types, example above, it is difficult for tools too determine
> the proper processing for the data. I understand the flexibility,
> and perhaps typeof() would solve most of my issues, but it would
> be nice to have metadata field type stored as INTEGER, REAL,
> NONE, TEXT, or BLOB.

What version of SQLite are you using for this? I just did the
following and do not see the string quoted values you are describing:

sqlite> CREATE TABLE mySinkDBTable (
   ...>   key_id1 INTEGER UNSIGNED NOT NULL,
   ...>   key_id2 INTEGER UNSIGNED NOT NULL,
   ...>   text VARCHAR
   ...> );
sqlite> insert into mySinkDBTable (key_id1, key_id2, text) VALUES('1','8','51');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE mySinkDBTable (
  key_id1 INTEGER UNSIGNED NOT NULL,
  key_id2 INTEGER UNSIGNED NOT NULL,
  text VARCHAR
);
INSERT INTO mySinkDBTable VALUES(1,8,'51');
COMMIT;

Given the comment in your data dump, I'm thinking your example came
from MySQL, not SQLite. Even if you try to insert quoted strings into
SQLite with the given column definitions, SQLite converts them to the
given type affinity before storing them, and uses that type affinity
when dumping the database.

-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[dmp]

Suppose outside the subject of this thread, but in the document.


Sustainability factors

Self-documentation:
"The database format incorporates technical and structural metadata
 needed to interpret and manipulate the data itself. For example,
 a database file will include the CREATE TABLE declarations that
 define tables and columns. To the extent that meaningful names are
 used for tables and columns, the nature and context of the data
 may be recorded. However, there is no explicit structure within
 the file for storing fuller descriptive and contextual metadata.
 Nor is there a capability to embed in the file a metadata object
 conforming to a schema outside the SQLite specification."


This constantly bites me. This morning I had to generate a fix
to correct context of exported SQL statements for a dump of data.

The numeric values were being quoted as strings so therefore when
imported back in, they would have been treated as strings instead of
numbers.

DROP TABLE IF EXISTS mySinkDBTable;
CREATE TABLE mySinkDBTable (
key_id1 INTEGER UNSIGNED NOT NULL,
key_id2 INTEGER UNSIGNED NOT NULL,
text VARCHAR
);

--
-- Dumping data for table mySinkDBTable
--

INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES('1', '8', '51');
Corrected:
INSERT INTO mySinkDBTable (key_id1, key_id2, text) VALUES(1, 8, '51');

Since the user is allowed to store the metadata for the table
types, example above, it is difficult for tools too determine
the proper processing for the data. I understand the flexibility,
and perhaps typeof() would solve most of my issues, but it would
be nice to have metadata field type stored as INTEGER, REAL,
NONE, TEXT, or BLOB.

danap.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite is a LoC Preferred Format for datasets

[Simon Slavin]

On 29 May 2018, at 7:42pm, Richard Hipp  wrote:

> Thanks to Simon Willison for pointing out that SQLite is a Preferred
> Format for datasets according to the US Library of Congress.
> https://simonwillison.net/2018/May/28/library-of-congress/

Obviously a good choice, which in no way makes it inevitable.  Well done Dev 
Team.

The description of SQLite on



Is very detailed and apparently written by someone who has used SQLite in 
anger, or at least understands the documentation.  I found only one (trivial) 
error.  It's interesting to see what the LOC wants described about one of its 
formats, and what it doesn't care about.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite is a LoC Preferred Format for datasets

[Richard Hipp]

Thanks to Simon Willison for pointing out that SQLite is a Preferred
Format for datasets according to the US Library of Congress.
https://simonwillison.net/2018/May/28/library-of-congress/

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users