[sqlite] storing data across multiple tables

[Keith Medcalf]

On Thursday, 16 July, 2015, 21:50, Hayden Livingston  said:

> Thanks, Keith for correcting my usage.

> I'm referring to tables when I mean database schema, so pardon my
> technical terminology.

> I have multiple tables of data already, say TableA, TableB. These
> tables have different representations, one may contain a TEXT column,
> the other may contain and INTEGER column, but all of them contain an
> INTEGER field called time, which is unique.

Ok, I understand your usage now.  Question:  Do you have multiple data items 
(ie, for the different tables data column) that have the same primary key (ie, 
the TIME)?  Do you need to be able to query against the "data" value(s)?  Do 
you need to "match" the results with the data column name?

> I also want to be able to query the union of results TableA and
> TableB. This is more of an architecture question to get the best
> performance. Given the requirement to also show the data in a format
> that is less schematic and more free-form how do people achieve that.

> I thought of the case that I could double write my data into TableA
> and TableB, and then also "CommonTable", which is the union of all
> entries in TableA, and TableB. Of course since there column number and
> types may not match, I create a "representation" -- basically take all
> the columns of that entry and append them into a single string.

> so my CommonTable looks like:

> CREATE TABLE CommonTable (INTEGER TimeEntry, TEXT COMMONDATA)

> This seems wasteful in terms of space -- I was wondering if others had
> thoughts.

One possible thought is that you can indeed put multiple data items in the same 
table, and you can store any "type" of data in any location.  If all you need 
is say four values, and a time you could use something like:

create table DataTable(Time Integer Primary Key, Value1 numeric default null, 
value2 numeric default null, value3 numeric default null);

Then you store the data in whatever format it is in the table, setting the 
columns that do not apply to null.

You can then issue your query to output your commondata string:

select time, coalesce(Value1, '') || ', ' || coalesce(Value2, '') || ', ' || 
'coalesce(Value3, '')
  from DataTable
 where Time between 159 and 2000
   and 25 in (Value1, Value2, Value3)
   and 'Test Data' in (Value1, Value2, Value3)

for example.  Efficiency will be based on knowing the right column to look in 
for constraints other than the Time (which will determine the number of rows to 
scan).


> On Thu, Jul 16, 2015 at 7:55 PM, Keith Medcalf 
> wrote:
> >> I have different schema data that I'd like to store across tables, and
> >> it is sorted in chronological order.
> >
> > Please explain what you mean by "schema", and especially "schema data".
> In a relational database there is only one schema per database.  This
> single schema describes the tables and indices, views, and triggers stored
> in that particular database (and, for other database engines, there may be
> other types of objects described in the schema).
> >
> > Some database engines provide "views" of the single schema and pretend
> that these views comprise "multiple schemata", however, they are not.
> They are simply a filtered view of the single schema for the database.
> With these systems (such as Oracle, SQL Server, etc etc etc) the "schema"
> you choose simply imposes a "filtered view" and "defaults" on certain
> attributes of the real single schema.  For example, there is an attribute
> (column) in the schema called "schema name".
> >
> > When you choose a schema then all you get to see are the things where
> the column [schema name] is equal to what you set, plus the dbo.  When you
> create a new object, the [schema name] is automagically set to be the name
> of the schema you are choosing to use.  The magic of the schema table
> queries make it "appear" as if the items in your chosen [schema name]
> obscure the remainder of the schema, but this is only an illusion created
> by the WHERE clause.
> >
> >> But then I also want to be able to make queries across tables when I
> >> do a query that says after time X, I want it to show me all rows --
> >> obviously different schema doesn't make this possible.
> >>
> >> So, In my application code I'm going to through loop through all the
> >> tables. The table count is usually high hundreds (~600-800)
> >>
> >> I'm wondering if other people do this kind of stuff. Or another option
> >> is to duplicate the data in some non schematized format (like a
> >> summary table of sorts that essentially duplicates every row in every
> >> table)
> >
> > I really don't understand what you are trying to accomplish.  Are you
> truing (for some unexplained reason) to "partition" data that otherwise
> would belong in one table into a large number of tables for some reason --
> for example, instead of having a column [Colour] which describes the
> colour of the thing the tuple is describing, instead have a bunch of
> tables called Red, 

[sqlite] storing data across multiple tables

[Keith Medcalf]

> I have different schema data that I'd like to store across tables, and
> it is sorted in chronological order.

Please explain what you mean by "schema", and especially "schema data".  In a 
relational database there is only one schema per database.  This single schema 
describes the tables and indices, views, and triggers stored in that particular 
database (and, for other database engines, there may be other types of objects 
described in the schema).

Some database engines provide "views" of the single schema and pretend that 
these views comprise "multiple schemata", however, they are not.  They are 
simply a filtered view of the single schema for the database.  With these 
systems (such as Oracle, SQL Server, etc etc etc) the "schema" you choose 
simply imposes a "filtered view" and "defaults" on certain attributes of the 
real single schema.  For example, there is an attribute (column) in the schema 
called "schema name".  

When you choose a schema then all you get to see are the things where the 
column [schema name] is equal to what you set, plus the dbo.  When you create a 
new object, the [schema name] is automagically set to be the name of the schema 
you are choosing to use.  The magic of the schema table queries make it 
"appear" as if the items in your chosen [schema name] obscure the remainder of 
the schema, but this is only an illusion created by the WHERE clause.

> But then I also want to be able to make queries across tables when I
> do a query that says after time X, I want it to show me all rows --
> obviously different schema doesn't make this possible.
> 
> So, In my application code I'm going to through loop through all the
> tables. The table count is usually high hundreds (~600-800)
> 
> I'm wondering if other people do this kind of stuff. Or another option
> is to duplicate the data in some non schematized format (like a
> summary table of sorts that essentially duplicates every row in every
> table)

I really don't understand what you are trying to accomplish.  Are you truing 
(for some unexplained reason) to "partition" data that otherwise would belong 
in one table into a large number of tables for some reason -- for example, 
instead of having a column [Colour] which describes the colour of the thing the 
tuple is describing, instead have a bunch of tables called Red, Yellow, Green, 
Blue (etc), so that you have to search all of them instead of just one?

[sqlite] storing data across multiple tables

[Hayden Livingston]

Thanks, Keith for correcting my usage.

I'm referring to tables when I mean database schema, so pardon my
technical terminology.

I have multiple tables of data already, say TableA, TableB. These
tables have different representations, one may contain a TEXT column,
the other may contain and INTEGER column, but all of them contain an
INTEGER field called time, which is unique.

I also want to be able to query the union of results TableA and
TableB. This is more of an architecture question to get the best
performance. Given the requirement to also show the data in a format
that is less schematic and more free-form how do people achieve that.

I thought of the case that I could double write my data into TableA
and TableB, and then also "CommonTable", which is the union of all
entries in TableA, and TableB. Of course since there column number and
types may not match, I create a "representation" -- basically take all
the columns of that entry and append them into a single string.

so my CommonTable looks like:

CREATE TABLE CommonTable (INTEGER TimeEntry, TEXT COMMONDATA)

This seems wasteful in terms of space -- I was wondering if others had thoughts.

On Thu, Jul 16, 2015 at 7:55 PM, Keith Medcalf  wrote:
>> I have different schema data that I'd like to store across tables, and
>> it is sorted in chronological order.
>
> Please explain what you mean by "schema", and especially "schema data".  In a 
> relational database there is only one schema per database.  This single 
> schema describes the tables and indices, views, and triggers stored in that 
> particular database (and, for other database engines, there may be other 
> types of objects described in the schema).
>
> Some database engines provide "views" of the single schema and pretend that 
> these views comprise "multiple schemata", however, they are not.  They are 
> simply a filtered view of the single schema for the database.  With these 
> systems (such as Oracle, SQL Server, etc etc etc) the "schema" you choose 
> simply imposes a "filtered view" and "defaults" on certain attributes of the 
> real single schema.  For example, there is an attribute (column) in the 
> schema called "schema name".
>
> When you choose a schema then all you get to see are the things where the 
> column [schema name] is equal to what you set, plus the dbo.  When you create 
> a new object, the [schema name] is automagically set to be the name of the 
> schema you are choosing to use.  The magic of the schema table queries make 
> it "appear" as if the items in your chosen [schema name] obscure the 
> remainder of the schema, but this is only an illusion created by the WHERE 
> clause.
>
>> But then I also want to be able to make queries across tables when I
>> do a query that says after time X, I want it to show me all rows --
>> obviously different schema doesn't make this possible.
>>
>> So, In my application code I'm going to through loop through all the
>> tables. The table count is usually high hundreds (~600-800)
>>
>> I'm wondering if other people do this kind of stuff. Or another option
>> is to duplicate the data in some non schematized format (like a
>> summary table of sorts that essentially duplicates every row in every
>> table)
>
> I really don't understand what you are trying to accomplish.  Are you truing 
> (for some unexplained reason) to "partition" data that otherwise would belong 
> in one table into a large number of tables for some reason -- for example, 
> instead of having a column [Colour] which describes the colour of the thing 
> the tuple is describing, instead have a bunch of tables called Red, Yellow, 
> Green, Blue (etc), so that you have to search all of them instead of just one?
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] storing data across multiple tables

[Hayden Livingston]

I have different schema data that I'd like to store across tables, and
it is sorted in chronological order.

But then I also want to be able to make queries across tables when I
do a query that says after time X, I want it to show me all rows --
obviously different schema doesn't make this possible.

So, In my application code I'm going to through loop through all the
tables. The table count is usually high hundreds (~600-800)

I'm wondering if other people do this kind of stuff. Or another option
is to duplicate the data in some non schematized format (like a
summary table of sorts that essentially duplicates every row in every
table)

Ideas? Improvements?

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Jim Callahan]

"Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses dynamic typing . The
declared type of a column is used to determine the affinity
 of the column only."
https://www.sqlite.org/lang_createtable.html

Each column in an SQLite 3 database is assigned one of the following type
affinities:

   - TEXT
   - NUMERIC
   - INTEGER
   - REAL
   - NONE

...The affinity of a column is determined by the declared type of the
column, according to the following rules in the order shown:

   1.

   If the declared type contains the string "INT" then it is assigned
   INTEGER affinity.
   2.

   If the declared type of the column contains any of the strings "CHAR",
   "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type
   VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity.
   3.

   If the declared type for a column contains the string "BLOB" or if no
   type is specified then the column has affinity NONE.
   4.

   If the declared type for a column contains any of the strings "REAL",
   "FLOA", or "DOUB" then the column has REAL affinity.
   5.

   Otherwise, the affinity is NUMERIC.

Note that the order of the rules for determining column affinity is
important. A column whose declared type is "CHARINT" will match both rules
1 and 2 but the first rule takes precedence and so the column affinity will
be INTEGER.
https://www.sqlite.org/datatype3.html#affinity

Thus, in the affinity rules, "Notice that the type VARCHAR contains the
string "CHAR" and is thus assigned TEXT affinity."

These rules allow  complex SQL CREATE TABLE statements to be run (for
compatibility) and mapped to the five storage affinities (which keep SQLite
small and fast) used by SQLite.

Jim Callahan
Orlando, FL

On Thu, Jul 16, 2015 at 6:09 PM, Simon Slavin  wrote:

>
> On 16 Jul 2015, at 10:46pm, Hayden Livingston 
> wrote:
>
> > It seems that
> >
> > CREATE TABLE A( something varchar(255) )
> >
> > it just
> >
> > CREATE TABLE A( something text )
> >
> > Why have it at all?
>
> Lots of people take SQL code originally intended for other SQL engines,
> start up SQLite and want it to work first time.  So it does.  It won't
> perfectly copy the behaviour of engines which respect the '255' but it's
> good enough to assure the programmer that SQLite is worth using.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] how to use fts5 ?

[Dan Kennedy]

On 07/16/2015 02:32 PM, Laurent Dami wrote:
> Hi there,
>
> I'm interested in testing the fts5 extension, already documented 
> (https://www.sqlite.org/fts5.html) , but I didn't find any 
> instructions on how to compile it. I found the sources in ext/fts5, 
> but apparently there is a piece of yacc grammar that doesn't compile 
> under yacc/bison.
>
> Did I miss some documentation ? Or is it not ready yet for use ?
>
> Thanks in advance,

Hi,

Slightly more up to date docs, including how to compile, here:

   http://sqlite.org/draft/fts5.html#section_2

Just noticed there is an HTML bug in the code for that section. Where it 
says:

   $ ls fts5.& 91;ch]

it should of course be:

   $ ls fts5.[ch]

FTS5 status: I think FTS5 is more than ready to test now. The current 
focus here is on running lots of performance tests to see if there are 
any tweaks we can make to the way data is stored in the database (i.e. 
the schema of the SQL tables used to store the underlying data) that 
will speed things up. So it's possible the file-format might change a 
little bit before it's actually released.

We're very interested in bugs, performance regressions (relative to 
FTS4) and other performance problems right now.

Thanks,
Dan.







>
> Laurent Dami
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to perform regex on string

[Jean Chevalier]

Have you looked at https://www.sqlite.org/src/artifact/af92cdaa5058fcec ?


Simon wrote:
|
| Do you have to use regexp ?  The following works:
|
| sqlite> SELECT replace('The time is %s.', '%s', strftime('%s','now'));
| The time is 1437054006.
|
|
| On 15 Jul 2015, Rick asked:
| >
| > Is there a simple way to replace portions of a string using the regex
| > function of sqlite without actually accessing any specific DB entres.
| >
| > Similar to
| >
| > SELECT strftime('%s','now') as unixtimestamp
| >
|

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Hayden Livingston]

Thanks!

On Thu, Jul 16, 2015 at 2:50 PM, Roger Binns  wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 07/16/2015 02:46 PM, Hayden Livingston wrote:
>> It seems that
>>
>> CREATE TABLE A( something varchar(255) )
>>
>> it just
>>
>> CREATE TABLE A( something text )
>>
>> Why have it at all?
>
> Because other code and other humans look at the database too.
> Consider it documentation of intent.  If it has no value to you, then
> leave out the type.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1
>
> iEYEARECAAYFAlWoJyIACgkQmOOfHg372QTu+wCghdSY6eDO5zXezGV+/xRXHHDr
> rWUAn1XuuG65S/48qjyfx9TP/lzXYS0g
> =Fzkn
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 07/16/2015 02:46 PM, Hayden Livingston wrote:
> It seems that
> 
> CREATE TABLE A( something varchar(255) )
> 
> it just
> 
> CREATE TABLE A( something text )
> 
> Why have it at all?

Because other code and other humans look at the database too.
Consider it documentation of intent.  If it has no value to you, then
leave out the type.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlWoJyIACgkQmOOfHg372QTu+wCghdSY6eDO5zXezGV+/xRXHHDr
rWUAn1XuuG65S/48qjyfx9TP/lzXYS0g
=Fzkn
-END PGP SIGNATURE-

[sqlite] why does SQLite support VARCHAR(X) when the doc says it ignores it?

[Hayden Livingston]

It seems that

CREATE TABLE A( something varchar(255) )

it just

CREATE TABLE A( something text )

Why have it at all?

[sqlite] How to perform regex on string

[Simon Slavin]

On 15 Jul 2015, at 1:53pm, Rick  wrote:

> Is there a simple way to replace portions of a string using the regex
> function of sqlite without actually accessing any specific DB entres.
> 
> Similar to 
> 
> SELECT strftime('%s','now') as unixtimestamp

Do you have to use regexp ?  The following works:

sqlite> SELECT replace('The time is %s.', '%s', strftime('%s','now'));
The time is 1437054006.

Simon.

[sqlite] Error compiling pre-release snapshot

[Miroslav Rajcic]

Hello,

I've just tried to compile the pre-release snapshot: 
sqlite-amalgamation-201507141718.zip

I got following error:
error C2129: static function 'void sqlite3SelectWrongNumTermsError(Parse 
*,Select *)' declared but not defined
sqlite3.c(13553) : see declaration of 'sqlite3SelectWrongNumTermsError'

It appears that the function in question has code protected by:
#ifndef SQLITE_OMIT_COMPOUND_SELECT
but its declaration and usage in the code are not protected by the same 
define.

That causes the error in question when program compiles the library with 
SQLITE_OMIT_COMPOUND_SELECT defined.

Regards,
   Miroslav

[sqlite] Schema-less JSON SQLite DB?

[Eduardo Morras]

On Wed, 15 Jul 2015 17:22:36 +0200
Sergej Jure?ko  wrote:

> An idea I?ve had a while ago was to implement functions for json
> documents (using sqlite3_create_function_v2)
> 
> Json would be stored in a text column. You would need to define 2
> functions:
> - docget(document,key1,subval1,subval2,?)
> - docset(document,key1,subval1,subval2,..,val)
> 
> Last parameter of docset is value you wish to set.
> 
> So for instance one would write
> INSERT INTO mytable VALUES (1,?{key : 10, subdoc : {subkey : ?a"}}?);
> SELECT id,doc FROM mytable WHERE docget(doc,?key") > 10;
> SELECT id,doc FROM mytable WHERE docget(doc,?key?,?subdoc?,?subkey?)
> = ?a?; UPDATE doc FROM mytable WHERE id=1 SET docset(doc,?key?,12);
> 
> One could even implement indexes on keys within documents using
> additional tables.
> 
> What do you guys think? Is it stupid, could it be improved?

Perhaps Dr. Hipps, R. unql implementation should be resurrected or at least, 
used as good/bad example. It has an extension to sql to query unstructured data 
and a layer on top sqlite to store json data. It's abandonware now afaik.

http://unql.sqlite.org/index.html/wiki?name=UnQL
http://www.dataversity.net/unql-a-standardized-query-language-for-nosql-databases/
http://www.couchbase.com/press-releases/unql-query-language


> 
> Sergej


---   ---
Eduardo Morras 

[sqlite] how to use fts5 ?

[Laurent Dami]

Hi there,

I'm interested in testing the fts5 extension, already documented 
(https://www.sqlite.org/fts5.html) , but I didn't find any instructions 
on how to compile it. I found the sources in ext/fts5, but apparently 
there is a piece of yacc grammar that doesn't compile under yacc/bison.

Did I miss some documentation ? Or is it not ready yet for use ?

Thanks in advance,

Laurent Dami

[sqlite] Schema-less JSON SQLite DB?

[Sergej Jurečko]

I will probably get around to doing it in the next few months. Hopefully I
have the time, right now I'm very busy. I will post to this message board
when I'm done.


Sergej

[sqlite] mmap on FreeBSD/DragonFly

[Baptiste Daroussin]

Hi,

I have been testing sqlite mmap on FreeBSD/DragonFly and it works
perfectly well. (the package manager uses sqlite this is where I have
been testing it)

would it be possible to activate it?
https://people.freebsd.org/~bapt/freebsd-dragonfly-mmap.diff

Best regards,
Bapt