Re: [sqlite] Why am I not getting anything?

[Igor Korot]

Thank you.

On Sun, Feb 10, 2019 at 1:35 AM Shawn Wagner  wrote:
>
> SELECT * FROM pragma_foreign_key_list('temp1');
>
> The table name needs to be a string for the pragma_foreign_key_list virtual
> table. (It's not for the corresponding pragma statement.)
>
> On Sat, Feb 9, 2019, 11:31 PM Igor Korot 
> > Hi, ALL,
> >
> > [code]
> > sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
> > type|name|tbl_name|rootpage|sql
> > table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name
> > VARCHAR(200))
> > table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id
> > INTEGER, my
> > name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES
> > temp(id))
> > table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value
> > NUMERIC(15,
> >  2))
> > table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data
> > TEXT)
> > table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id
> > INTEGER,
> > myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
> > sqlite>
> > sqlite>
> > sqlite>
> > sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
> > Error: no such column: temp1
> > sqlite>
> > [/code]
> >
> > I should get an information about the foreign key that exists for the
> > table temp1,
> > but I have an error.
> >
> > And when I execute the PRAGMA directly, I do get the results:
> >
> > [code]
> > sqlite> PRAGMA foreign_key_list(temp1);
> > id|seq|table|from|to|on_update|on_delete|match
> > 0|0|temp|id|id|NO ACTION|NO ACTION|NONE
> > [/code]
> >
> > [code]
> > sqlite> .version
> > SQLite 3.24.0 2018-06-04 19:24:41
> > c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
> > d5221ac8fb199a87ca
> > zlib version 1.2.11
> > gcc-5.2.0
> > sqlite>
> > [/code]
> >
> > This is on Windows 8.1
> >
> > Am I doing something wrong?
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why am I not getting anything?

[Shawn Wagner]

SELECT * FROM pragma_foreign_key_list('temp1');

The table name needs to be a string for the pragma_foreign_key_list virtual
table. (It's not for the corresponding pragma statement.)

On Sat, Feb 9, 2019, 11:31 PM Igor Korot  Hi, ALL,
>
> [code]
> sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
> type|name|tbl_name|rootpage|sql
> table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name
> VARCHAR(200))
> table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id
> INTEGER, my
> name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES
> temp(id))
> table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value
> NUMERIC(15,
>  2))
> table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data
> TEXT)
> table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id
> INTEGER,
> myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
> sqlite>
> sqlite>
> sqlite>
> sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
> Error: no such column: temp1
> sqlite>
> [/code]
>
> I should get an information about the foreign key that exists for the
> table temp1,
> but I have an error.
>
> And when I execute the PRAGMA directly, I do get the results:
>
> [code]
> sqlite> PRAGMA foreign_key_list(temp1);
> id|seq|table|from|to|on_update|on_delete|match
> 0|0|temp|id|id|NO ACTION|NO ACTION|NONE
> [/code]
>
> [code]
> sqlite> .version
> SQLite 3.24.0 2018-06-04 19:24:41
> c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
> d5221ac8fb199a87ca
> zlib version 1.2.11
> gcc-5.2.0
> sqlite>
> [/code]
>
> This is on Windows 8.1
>
> Am I doing something wrong?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why am I not getting anything?

[Igor Korot]

Hi, ALL,

[code]
sqlite> SELECT * FROM sqlite_master WHERE name LIKE '%temp%';
type|name|tbl_name|rootpage|sql
table|temp|temp|40|CREATE TABLE temp(id INTEGER PRIMARY KEY, name VARCHAR(200))
table|temp1|temp1|41|CREATE TABLE temp1(myid INTEGER PRIMARY KEY, id INTEGER, my
name VARCHAR(200), CONSTRAINT temp1tempid FOREIGN KEY(id) REFERENCES temp(id))
table|temp2|temp2|43|CREATE TABLE temp2(id INTEGER, name TEXT, value NUMERIC(15,
 2))
table|temp3|temp3|1268|CREATE TABLE temp3(id INTEGER PRIMARY KEY, data TEXT)
table|temp4|temp4|1269|CREATE TABLE temp4(myid INTEGER PRIMARY KEY, id INTEGER,
myname VARCHAR(200), FOREIGN KEY(id) REFERENCES temp(id))
sqlite>
sqlite>
sqlite>
sqlite> SELECT * FROM pragma_foreign_key_list(temp1);
Error: no such column: temp1
sqlite>
[/code]

I should get an information about the foreign key that exists for the
table temp1,
but I have an error.

And when I execute the PRAGMA directly, I do get the results:

[code]
sqlite> PRAGMA foreign_key_list(temp1);
id|seq|table|from|to|on_update|on_delete|match
0|0|temp|id|id|NO ACTION|NO ACTION|NONE
[/code]

[code]
sqlite> .version
SQLite 3.24.0 2018-06-04 19:24:41 c7ee0833225bfd8c5ec2f9bf62b97c4e04d03bd9566366
d5221ac8fb199a87ca
zlib version 1.2.11
gcc-5.2.0
sqlite>
[/code]

This is on Windows 8.1

Am I doing something wrong?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Was there an announcement of 3.27?

[J. King]

On February 8, 2019 3:31:51 PM EST, "Dominique Pellé" 
 wrote:
>David Raymond wrote:
>
>> SQLite version 3.27.1 is now available on the SQLite website:
>>
>>   https://sqlite.org/
>>   https://sqlite.org/download.html
>>   https://sqlite.org/releaselog/3_27_1.html
>
>Release notes https://sqlite.org/releaselog/3_27_1.html say:
>
>=== BEGIN QUOTE ===
>Added the remove_diacritics=2 option to FTS3 and FTS5.
>=== END QUOTE ===
>
>I wonder that this does.  FTS3 or FTS5 doc were not updated,
>since they only document remove_diacritics=0 and 1.
>
>I also use the opportunity to report a few typos in
>https://sqlite.org/fts5.html:
>
>- the second character replaced with an *asterix* (-> asterisk)
>- fts5 extension function made as part *of of* (repeated word "of")
>-  *an the* (-> the) auxiliary data is set to NULL
>
>Regards
>Dominique
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I'm sure someone savvier will correct me, but the source does indicate that 1 
is "simple" and 2 "complex". It's not entirely clear, but it seems like " 
complex" is able to handle multiple diacritics on one base character and 
"simple" cannot?
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Warren Young, on Saturday, February 9, 2019 06:15 PM, wrote...
>On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera  wrote:
>>
>> Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote...
>> >
>> >You may be interested in the sqldiff program that comes with SQLite:
>>
>> Yeah, I know about it, but I thought it was for two different DBs
>
>It is, but that doesn’t affect whether you will be *interested* in it, the 
>word I chose very carefully.
>
>Ponder this: How does sqldiff work internally?
>
>Interested now? :)

Ok, you got it. :-)

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

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

I’ll interpret the silence as a “no”?


> On Feb 7, 2019, at 11:53 AM, Joshua Thomas Wise  
> wrote:
> 
> Is the SQLite team aware of these issues?
> 
> 
>> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
>>  wrote:
>> 
>> Hello,
>> 
>> I found some behaviors that should probably be considered bugs (and should 
>> be fixed and/or documented).
>> 
>> Let’s start the explanation by observing some behavior that actually is 
>> correct and consistent. Below, we observe which type of action is reported 
>> by sqlite3_set_authorizer(), given some SQL input:
>> 
>> "CREATE TEMP TABLE foo(x)” -> SQLITE_CREATE_TEMP_TABLE
>> "CREATE TEMP VIEW foo AS SELECT 1” -> SQLITE_CREATE_TEMP_VIEW
>> "CREATE TEMP TRIGGER foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
>> SQLITE_CREATE_TEMP_TRIGGER
>> 
>> So far so good. But what happens when we use the “temp.foo” syntax instead 
>> of the TEMP keyword?
>> 
>> "CREATE TABLE temp.foo(x)" -> SQLITE_CREATE_TABLE
>> "CREATE INDEX temp.foo ON t(x)" -> SQLITE_CREATE_TEMP_INDEX
>> "CREATE VIEW temp.foo AS SELECT 1" -> SQLITE_CREATE_VIEW
>> "CREATE TRIGGER temp.foo BEFORE INSERT ON t BEGIN SELECT 1; END" -> 
>> SQLITE_CREATE_TEMP_TRIGGER
>> 
>> Uh-oh. We have some inconsistencies there. CREATE TABLE and CREATE VIEW 
>> report their regular CREATE_* variants, while CREATE INDEX and CREATE 
>> TRIGGER do report their TEMP_* variants.
>> 
>> I recommend that either all or none of those statements should report their 
>> TEMP_* variants to the sqlite3_set_authorizer() callback. If the decision is 
>> made to not use TEMP_* variants in these cases, the schema name (“temp”, in 
>> this case) should ideally be included as an argument to the 
>> sqlite3_set_authorizer() callback.
>> 
>> I also found strange inconsistencies regarding error messages. If we execute 
>> the 4 statements above, but this time with the SQLITE_OMIT_TEMPDB 
>> compile-time option present, we get the following error messages 
>> (respectively):
>> 
>> 'SQL logic error'
>> 'cannot create a TEMP index on non-TEMP table “t”'
>> 'SQL logic error’
>> 'SQL logic error’
>> 
>> However, if we replace “temp.foo” with “miss.foo” in each of those 
>> statements, we get the much better error message:
>> 
>> 'unknown database miss’
>> 
>> All of the observations described in this email were very surprising to me. 
>> Hopefully they can be fixed and/or documented.
>> 
>> Best regards,
>> 
>> Josh
> 

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

Re: [sqlite] Checking differences in tables

[Warren Young]

On Feb 9, 2019, at 12:20 PM, Jose Isaias Cabrera  wrote:
> 
> Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote...
> >
> >You may be interested in the sqldiff program that comes with SQLite:
> 
> Yeah, I know about it, but I thought it was for two different DBs

It is, but that doesn’t affect whether you will be *interested* in it, the word 
I chose very carefully.

Ponder this: How does sqldiff work internally?

Interested now? :)

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

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Warren Young, on Saturday, February 9, 2019 01:36 PM, wrote...
>On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera  wrote:
>>
>> Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do 
>> something like this,
>
>You may be interested in the sqldiff program that comes with SQLite:
>
>$ sqldiff --help
>Usage: sqldiff [options] DB1 DB2
>Output SQL text that would transform DB1 into DB2.
>Options:
>  --changeset FILE  Write a CHANGESET into FILE
>  -L|--lib LIBRARY  Load an SQLite extension library
>  --primarykey  Use schema-defined PRIMARY KEYs
>  --rbu Output SQL to create/populate RBU table(s)
>  --schema  Show only differences in the schema
>  --summary Show only a summary of the differences
>  --table TAB   Show only differences in table TAB
>  --transaction Show SQL output inside a transaction
>
>In this case, you want the --table option.

Yeah, I know about it, but I thought it was for two different DBs. Hmmm...  I 
will check it out.  Although, to check the difference on a table with a huge 
database with the DB1 being the same as DB2, I don't know how it will work,  
but, I will give it a try.  Thanks.

josé


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

Re: [sqlite] Checking differences in tables

[Warren Young]

On Feb 9, 2019, at 8:49 AM, Jose Isaias Cabrera  wrote:
> 
> Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do something 
> like this,

You may be interested in the sqldiff program that comes with SQLite:

$ sqldiff --help
Usage: sqldiff [options] DB1 DB2
Output SQL text that would transform DB1 into DB2.
Options:
  --changeset FILE  Write a CHANGESET into FILE
  -L|--lib LIBRARY  Load an SQLite extension library
  --primarykey  Use schema-defined PRIMARY KEYs
  --rbu Output SQL to create/populate RBU table(s)
  --schema  Show only differences in the schema
  --summary Show only a summary of the differences
  --table TAB   Show only differences in table TAB
  --transaction Show SQL output inside a transaction

In this case, you want the --table option.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

WOW
Clemens Ladisch on Saturday, February 9, 2019 11:44 AM wrote,
Jose Isaias Cabrera wrote:
>> t_20190208 (a PRIMARY KEY, b, c, d, e).
>>
>> I create a new table,
>>
>> t (a PRIMARY KEY, b, c, d, e)
>>
>> and insert a set of "new data", which contains changes that happened since 
>> yesterday
>> after the new set of data was created.  Right now, I bring the data out into 
>> two arrays
>> and check for the data outside SQLite by iterating through the fields and 
>> checking for
>> differences, one record at a time, but is there an easier or simpler way 
>> using SQLite
>> commands?
>
>This query returns all rows that are new or changed:
>
> SELECT * FROM t
>  EXCEPT
>  SELECT * FROM t_20190208;

wow, that's so simple.  Thanks.

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

Re: [sqlite] Checking differences in tables

[Clemens Ladisch]

Jose Isaias Cabrera wrote:
> t_20190208 (a PRIMARY KEY, b, c, d, e).
>
> I create a new table,
>
> t (a PRIMARY KEY, b, c, d, e)
>
> and insert a set of "new data", which contains changes that happened since 
> yesterday
> after the new set of data was created.  Right now, I bring the data out into 
> two arrays
> and check for the data outside SQLite by iterating through the fields and 
> checking for
> differences, one record at a time, but is there an easier or simpler way 
> using SQLite
> commands?

This query returns all rows that are new or changed:

  SELECT * FROM t
  EXCEPT
  SELECT * FROM t_20190208;


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

Re: [sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Simon Slavin on Saturday, February 9, 2019 11:02 AM wrote...
On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera  wrote:

> Imagine this scenario:I have this table,
>
> t (a PRIMARY KEY, b, c, d, e)
>
> that contains yesterday's data. Today, I rename that table to,
>
> t_20190208 (a PRIMARY KEY, b, c, d, e).

> That's not how you use tables.  Your software shouldn't be automatically 
> creating or dropping
> tables during the life of your program.

True, but there is a lot more details, but you're right in a very decent world. 
:-)

> Have one table for your data for all days.
> Add an extra column to the table for the datestamp.
> Then to select all data for a particular day you do
>
> SELECT * From t WHERE datestamp = "20190208"

We already have that.  The problem is that we need to check differences of data 
coming in for different days, weeks and months.

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

Re: [sqlite] Checking differences in tables

[Simon Slavin]

On 9 Feb 2019, at 3:49pm, Jose Isaias Cabrera  wrote:

> Imagine this scenario:I have this table,
> 
> t (a PRIMARY KEY, b, c, d, e)
> 
> that contains yesterday's data. Today, I rename that table to,
> 
> t_20190208 (a PRIMARY KEY, b, c, d, e).

That's not how you use tables.  Your software shouldn't be automatically 
creating or dropping tables during the life of your program.

Have one table for your data for all days.
Add an extra column to the table for the datestamp.
Then to select all data for a particular day you do

SELECT * From t WHERE datestamp = "20190208"

If you only ever need to keep two days worth of data, then instead of a 
datestamp column you can use one which contains the text 'yesterday' or 'today'.

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

[sqlite] Checking differences in tables

[Jose Isaias Cabrera]

Hi Gurus.

Imagine this scenario:I have this table,

t (a PRIMARY KEY, b, c, d, e)

that contains yesterday's data. Today, I rename that table to,

t_20190208 (a PRIMARY KEY, b, c, d, e).

I create a new table,

t (a PRIMARY KEY, b, c, d, e)

and insert a set of "new data", which contains changes that happened since 
yesterday after the new set of data was created.  Right now, I bring the data 
out into two arrays and check for the data outside SQLite by iterating through 
the fields and checking for differences, one record at a time, but is there an 
easier or simpler way using SQLite commands? The output could be something like,

field | t_20190208 | t
b | 2000   | 2100
e | week   | daily
etc.

Perhaps, Dr. Hipp and the team can write a quick PRAGMA that can do something 
like this,

PRAGMA cktablediff t_20190208 t table_name;

Then one can just call SELECT * FROM table_name;

Me and my dreams... He he he...

josé

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