Re: [sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

OK i must have must the posts from the 12th of March till the end of 
that week , being busy with other things.


On 24-3-2020 09:19, Hick Gunter wrote:

See announcement on the mailing list dated march 12th

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Donnerstag, 12. März 2020 21:18
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] New SQLite Forum established - this mailing list 
is deprecated

I have set up an on-line forum as a replacement for this mailing list:

 https://sqlite.org/forum
 https://www.sqlite.org/forum/forumpost/a6a27d79ac

Please consider subscribing to the new Forum.  The intent is that the forum 
will eventually replace this mailing list.

The Forum is powered by Fossil.  It has been in active use in the Fossil 
community for a couple of years, and has worked well.  See the second link 
above for more information.

--
D. Richard Hipp
d...@sqlite.org
___


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Luuk
Gesendet: Dienstag, 24. März 2020 09:13
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] The mailing list is deprecated.. [was: Re: [EXTERNAL] 
No such column error]


On 24-3-2020 09:04, Luuk wrote:

"The mailing list is deprecated. You need to go to
https://sqlite.org/forum/ for the sqlite forum." 

Can anyone give the source of this?

No, i do NOT mean the source of the forum, but the source for "The list is 
deprecated"


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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

On 24-3-2020 09:04, Luuk wrote:
"The mailing list is deprecated. You need to go to 
https://sqlite.org/forum/ for the sqlite forum." 


Can anyone give the source of this?


No, i do NOT mean the source of the forum, but the source for "The list 
is deprecated"



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

[sqlite] The mailing list is deprecated...... [was: Re: [EXTERNAL] No such column error]

[Luuk]

"The mailing list is deprecated. You need to go to 
https://sqlite.org/forum/ for the sqlite forum." 


Can anyone give the source of this?

On 23-3-2020 14:35, Hick Gunter wrote:

The mailing list is deprecated. You need to go tohttps://sqlite.org/forum/  for 
the sqlite forum.

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

Re: [sqlite] After deleting data from a FTS table and doing VACUUM, unwanted data remains

[Luuk]

script:

#!/bin/bash

if [ ! -f test.db ] ; then sqlite3 test.db "CREATE VIRTUAL TABLE tab 
USING fts5(x)"; fi

sqlite3 test.db ".import wikipedia tab"
a=$(sqlite3 test.db "SELECT count(*) FROM tab_data")
echo "# records after import: $a"
sqlite3 test.db "DELETE FROM tab"
a=$(sqlite3 test.db "SELECT count(*) FROM tab_data")
echo "# records after DELETE: $a"
sqlite3 test.db "VACUUM"
a=$(sqlite3 test.db "SELECT count(*) FROM tab_data")
echo "# records after vacuum: $a"

output:

$> ./test.sh
# records after import: 15
# records after DELETE: 27
# records after vacuum: 27
$> ./test.sh
# records after import: 40
# records after DELETE: 52
# records after vacuum: 52
$> ./test.sh
# records after import: 65
# records after DELETE: 2
# records after vacuum: 2
$> ./test.sh
# records after import: 15
# records after DELETE: 27
# records after vacuum: 27
$> ./test.sh
# records after import: 40
# records after DELETE: 52
# records after vacuum: 52
$> vi testsh
$> vi test.sh
$>

So, the index does not grow indefinitely

On 25-2-2020 14:00, Graham Holden wrote:

It is an interesting problem. And the above is just guesswork... It would
be good to verify experimentally that the index really does grow
indefinitely

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

Re: [sqlite] Performance Issue on Large Table

[Luuk]

On 21-2-2020 02:24, Chip Beaulieu wrote:

I have a table with 4.5 million records with full text indexing. Reads are very 
fast, but deleting / inserting  / updating takes on average about 50 seconds 
per record. I often do batches of 30,000 deletes / inserts at a time. The last 
batch took 10 hours to complete.

Here are the details:

Table Schema:

CREATE TABLE `Verses` (
`ID`integer PRIMARY KEY AUTOINCREMENT,
`BibleID`   integer NOT NULL,
`Book`  integer NOT NULL,
`Chapter`   integer NOT NULL,
`VerseNumber`   integer NOT NULL,
`Scripture` text,
`Comments`  text,


I would recommend to not store `Comments` in the same table as 
`Scripture`, or are you trying to re-write the Bible? 


This will avoid the deletion of unchanged data, which is inserted later 
on (only because `Comments` did change?)



--
Luuk

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

Re: [sqlite] NOTNULL

[Luuk]

On 8-1-2020 12:15, R Smith wrote:

I find the keyword NOTNULL listed among known SQLite keywords -
no. 88 on this page: https://sqlite.org/lang_keywords.html

But cannot find a single mention of it or place to use it in SQLite, 
nor get any hit on the sqlite.org search except in reference to the 
above list.


1. What is it for?

2. Does it have/need any documentation?


(from: https://sqlite.org/changes.html  )


 2001-10-02 (2.0.1)

1. 
2. ...
3. Extend the expression syntax to support "expr NOT NULL" (with a
   space between the "NOT" and the "NULL") in addition to "expr
   NOTNULL" (with no space).

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

Re: [sqlite] AVG Function HowTo

[Luuk]

On 28-12-2019 13:07, Csanyi Pal wrote:

Hi,

I have attached here the exported sql file of my little sqlite database.

I am a newbe in the sqlite language so I ask here only an advice in 
which direction to go, eg. which functions of sqlite language to use 
to achieve my goal?




SQList is able to calulate averages. lets give simple example

select min(a),avg(a),max(a)
from (select 1 as a union all select 2 union all select 3 union all 
select 4);


It will output:

1|2.5|4

because 1 is the minimum values of the selected values 1,2,3,4

2.5 is the average of the selected values 1,2,3,4

and 4 is the max values of those.


For calculating with date (and/or time) function you should read this page:
https://www.sqlite.org/lang_datefunc.html

If you created an example that 'does not work' (i do mean 'that does not 
do what you expect it to do' ), come back here with that example, and 
i'm sure someone will help you


post the database structure, the query and some example data, and if 
possible the expected output...




Do I think well?

Any advices will be appreciated!


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

Re: [sqlite] OT!!!!! Understanding the WITH clause

[Luuk]

On 20-6-2019 08:11, Michael Falconer wrote:

SELECT peace FROM disaster WHERE disaster.cause = 'Windows';

Returns: *nix



The query should be

SELECT peace
FROM disaster
WHERE (disaster.cause LIKE '%Windows%'
   OR disaster.cause LIKE '%nix%'
   OR disaster.cause LIKE '%incompetence%'
   );


Also then return value might be different, depending on the time of day 
and possible other, at present not known, parameters.




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

Re: [sqlite] Understanding the WITH clause

[Luuk]

On 16-6-2019 19:32, E.Pasma wrote:

Message to Luuk: thanks for explaining Pax vobiscum.
E. Pasma


Thanks for the recursive CTE.

Luuk

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

Re: [sqlite] Understanding the WITH clause

[Luuk]

On 15-6-2019 18:24, Sam Carleton wrote:

Pax vobiscum,
Sam Carleton


I had to pull up a dictionary to know what Pax (=Peace) you are talking 
about..


(https://en.wikipedia.org/wiki/Pax_(liturgy))

"In Christian liturgy 
, "the /*Pax*/" is an 
abbreviation of the Latin 
salutations "/pax 
vobis/" ("peace to you") or "/pax vobiscum/" ("peace with you"), which 
are used in the Catholic 
Mass 
and Lutheran 
Divine Service 
."


I do think (without any expressed opinion here) that there's is olny a 
small percentage of people who did know withoutout searching the 
internet (of the dictionary at home).


Greetings from Holland,
"*Holland*is a region and former province on the western coast of the 
Netherlands . The name 
/Holland/is also frequently used informally to refer to the whole of the 
country of the Netherlands. This usage is commonly accepted in other 
countries,^[2]  
and sometimes employed by the Dutch themselves.^[2] 
 However, some 
in the Netherlands, particularly those from regions outside Holland, may 
find it undesirable^[2] 
 or 
misrepresentative to use the term for the whole country." 
(https://en.wikipedia.org/wiki/Holland)


Yes, i'm one of the people who do find Holland misrepresentative for 
'the Netherlands' (https://en.wikipedia.org/wiki/Netherlands)


But, since drifting away from topic,

Houdoe (https://en.wikipedia.org/wiki/Houdoe)





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

Re: [sqlite] How to insert the BLOB in database?

[Luuk]

On 10-6-2019 05:08, Mark Halegua wrote:

On Monday, June 10, 2019 03:46:02 AM Simon Slavin wrote:

On 10 Jun 2019, at 3:44am, Mark Halegua  wrote:

I probably should figure this out, but in a GUI, how do I recover a
graphic from the database?

Programming.  SQLite can't do it since it doesn't even understand that that
sequence of octets is a graphics.

How you do it in programming depends on your development environment and
libraries. ___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I'm using Python.  What would the programming sequence be to display the 
graphic in
that language?

Mark

mARK


google for:  python show picture from database sqlite

https://stackoverflow.com/questions/30818728/retrieve-image-from-sqlite3-database-and-directly-display-on-kivy-window




___
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] Failing SQLite .import returns exit status 0

[Luuk]

On 8-6-2019 15:26, Simon Slavin wrote:

Use

 .bail ON

If the shell bails out because of an error, it should be setting the exit code 
to indicate an error.


unfortunatly still 0 is returned  (sqlite3 version 3.28.0)

~/temp> cat empty.sh
#!/bin/bash

rm -f empty.db empty.csv
echo "" > empty.csv
echo "a, b" >> empty.csv
sqlite3 empty.db \
'.bail on' \
'DROP TABLE IF EXISTS empty;' \
'.mode csv' \
'CREATE TABLE IF NOT EXISTS empty(A, B NOT NULL);' \
'.import empty.csv empty' \
'.exit'
echo "error code is" $?

~/temp> ./empty.sh
empty.csv:1: expected 2 columns but found 1 - filling the rest with NULL
empty.csv:1: INSERT failed: NOT NULL constraint failed: empty.B
error code is 0
~/temp>


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

Re: [sqlite] How to set access permissions to protect a database file?

[Luuk]

On 26-5-2019 13:52, Adrian Ho wrote:

On 26/5/19 7:49 AM, Markos wrote:

I made a program (reading_room.tcl), with Sqlite running on Debian 9,
to control the books of a reading room.

I implemented an authentication system for common users and
administrator users in the reading_room.tcl program.

Now I want that any user logged in the Linux be able to run the
program reading_room.tcl, which will access the database (books.db)

But I want to protect the file books.db so that only the the program
reading_room.tcl can access the books.db file (to read or write). But
that no user could delete or write to the file books.db (only the
program reading_room.tcl)

The standard Unix permissions/ACLs architecture doesn't support this use
case directly.


Can you give some more information on this, because it seems to work as 
i excpect it to:


Database is 'owned' by user 'luuk', trying to access via 'luuk2', both 
users are in the group 'users':


luuk2@opensuse1:/home/luuk/temp> whoami
luuk2
luuk2@opensuse1:/home/luuk/temp> ls -l test.sqlite
-r--r--r-- 1 luuk users 8192 May 26 18:34 test.sqlite
luuk2@opensuse1:/home/luuk/temp> sqlite3 test.sqlite
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> select * from test;
1
2
sqlite> insert into test values(3);
Error: attempt to write a readonly database
sqlite> .q
luuk2@opensuse1:/home/luuk/temp>




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

Re: [sqlite] How to set access permissions to protect a database file?

[Luuk]

On 26-5-2019 01:49, Markos wrote:

Hi,

I made a program (reading_room.tcl), with Sqlite running on Debian 9, 
to control the books of a reading room.


I implemented an authentication system for common users and 
administrator users in the reading_room.tcl program.


Now I want that any user logged in the Linux be able to run the 
program reading_room.tcl, which will access the database (books.db)


But I want to protect the file books.db so that only the the program 
reading_room.tcl can access the books.db file (to read or write). But 
that no user could delete or write to the file books.db (only the 
program reading_room.tcl)


Please, how can I configure the system to do that?

How to define the permissions?

Thanks,

Markos

If you have read, and used, this: 
https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt than you 
should know the answer ;)


Otherwise set access permissions on the database  (use: 'man chmod' 
and/or 'man chown', to find out how to do that under Debian 9)


If a user has no right to modify the db, and tries to do an update, an 
error is returned from sqlite:


sqlite> insert into User values(42);
Error: attempt to write a readonly database




___
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] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

[Luuk]

On 13-5-2019 20:06, David Raymond wrote:

What version are you using? Because it seems to be working fine for me on 
3.28.0 Windows CLI.


D:\TEMP>sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.


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

Re: [sqlite] REINDEX causes rows not to be fetched in a WITHOUT ROWIDs table and PRIMARY KEY DESC

[Luuk]

On 13-5-2019 19:36, Manuel Rigger wrote:

Hi everyone,

Consider the following test case:

CREATE TABLE t0 (c0 PRIMARY KEY DESC, c1 UNIQUE DEFAULT NULL) WITHOUT ROWID;
INSERT INTO t0(c0) VALUES (1), (2), (3), (4), (5);
REINDEX;
SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 ISNULL; --
returns 5|

I would expect that the SELECT statement fetches the five rows, however,
only one is fetched. Only when omitting the REINDEX are all five rows
fetched.

Best,
Manuel
___


sqlite> SELECT * FROM t0 WHERE t0.c0 IN (SELECT c0 FROM t0) AND t0.c1 
ISNULL;

c0|c1
5|
sqlite> SELECT * FROM t0 WHERE (1 or t0.c0 IN (SELECT c0 FROM t0)) AND 
(1 or t0.c1 ISNULL);

c0|c1
5|
4|
3|
2|
1|
sqlite> SELECT * FROM t0 WHERE (0 or t0.c0 IN (SELECT c0 FROM t0)) AND 
(0 or t0.c1 ISNULL);

c0|c1
5|
4|
3|
2|
1|
sqlite>


The first attempt (adding '1 or') seems to be tooo obvious, but the 
second ... ;-)



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

Re: [sqlite] unsubscribe

[Luuk]

should that no be below? 

On 13-5-2019 11:21, Tim Streater wrote:

On 13 May 2019, at 09:19, Christof Arnosti  
wrote:


http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Do that yourself at the link above.




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

Re: [sqlite] feature request -- enhance strftime() implementing %V, %g and %G for week of year according to ISO 8601

[Luuk]

On 9-5-2019 18:20, Nißl Reinhard wrote:

Hi,

it would be nice, if sqlite3's strftime() would support the following 
formatting codes:
%g  The last 2 digits of the ISO 8601 week-based year as a decimal number 
(00 - 99)
%G  The ISO 8601 week-based year as a decimal number
%V  ISO 8601 week number as a decimal number (00 - 53)

minimum value for %V is 01.


The ISO 8601 week and week-based year produced by %V, %g, and %G, uses a week 
that begins on Monday, where week 1 is the week that contains January 4th, 
which is the first week that includes at least four days of the year. If the 
first Monday of the year is the 2nd, 3rd, or 4th, the preceding days are part 
of the last week of the preceding year. For those days, %V is replaced by the 
last week of the preceding year, and both %g and %G are replaced by the digits 
of the preceding year.

The above text has originally been taken from this documentation and corrected regarding 
"%V is replaced by 53":
https://docs.microsoft.com/de-de/cpp/c-runtime-library/reference/strftime-wcsftime-strftime-l-wcsftime-l?view=vs-2019


better docs:

http://man7.org/linux/man-pages/man3/strftime.3.html

;-)



Thanks.

Bye.
--
Reinhard Nißl
reinhard.ni...@fee.de

___
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] Getting the week of the month from strftime or date functions

[Luuk]

On 4-5-2019 15:21, Petite Abeille wrote:



On May 4, 2019, at 12:47, Luuk  wrote:

As others have noted, it's a question of definition, and which definition do 
you follow?

What about just sticking with the ISO week definition?

https://en.wikipedia.org/wiki/ISO_week_date


This is the 'standard' used here where i live, so i can accept that ;)


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

Re: [sqlite] Getting the week of the month from strftime or date functions

[Luuk]

On 2-5-2019 22:17, Jose Isaias Cabrera wrote:

I found this very interesting,

15:52:46.71>sqlite3
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT strftime('%W','2019-01-01');
00
sqlite> SELECT strftime('%W','2019-01-02');
00
sqlite> SELECT strftime('%W','2019-01-03');
00
sqlite> SELECT strftime('%W','2019-01-04');
00
sqlite> SELECT strftime('%W','2019-01-05');
00
sqlite> SELECT strftime('%W','2019-01-06');
00
sqlite> SELECT strftime('%W','2019-01-07');
01

I expected 2019-01-01 to be part of week 1, since it was Tuesday.  So, back to 
the drawing board. ;-)  Thanks.


It's more complex than that

sqlite> select strftime('%W','2018-12-29');
52
sqlite> select strftime('%W','2018-12-30');
52
sqlite> select strftime('%W','2018-12-31');
53
sqlite> select strftime('%W','2019-01-01');
00
sqlite>


As others have noted, it's a question of definition, and which 
definition do you follow?


- Does a week start on Sunday, or on Monday?
- Is week #1 the week in which the month starts, the first complete week 
within the month, or the first week with at least 4 days?


Even EXCEL (Microsoft) has problems with this, that's why they 
implemented server WEEKNUM functions


=WEEKNUM(;2) for the 31th december returns 53 (the second 
parameter is used to specify when a week starts)


=ISO.WEEKNUM() for the same date returns 1


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

Re: [sqlite] Row values with IN

[Luuk]

On 23-4-2019 18:08, Simon Slavin wrote:

On 23 Apr 2019, at 4:14pm, Charles Leifer  wrote:


SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1));

Perhaps the row-value doc could clarify the behavior of IN with row values?

This


((2019, 1, 1), (2019, 2, 1))


This works:

SELECT * FROM info WHERE (year,month,day) = (2019, 1, 1) OR (year,month,day)
= (2019, 2, 1);

but it's not using IN, and more characters to type


is not a list of scalers, it's a list of lists of scalers.  So the 
documentation does explain it.  However, I think that the documentation doesn't 
sufficiently explain the problem.  Or maybe the use of IN or lists deserves its 
own page rather than being buried in the extremely long page on expressions.

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

Re: [sqlite] SQLite error while fetching the data from a table

[Luuk]

On 22-4-2019 14:03, Ananta Jena wrote:

Hi All,

Need quick help to resolve one issue i am getting now.
I am a new user of SQLite.

my code:
connection =
DriverManager.getConnection("jdbc:sqlite:C:\\sqllite\\sqlite-tools-win32-x86-328\\Stories.db");
  Statement st = connection.createStatement();
  ResultSet b = st.executeQuery("select count(*) from stories;");

Note : Connection is established successfully and also table STORIES has 1
record as well.

while executing this ,i am geting below error:
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no
such table: stories)



The error says: "SQL error or missing database"

on the next line is: "no such table: stories"


Conclusion: there must be something wrong in the code you did not post.

(but i'm not a java programmer)

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

Re: [sqlite] Table names starting with sqlite (not sqlite_)

[Luuk]

On 19-4-2019 20:53, Semih Hazar wrote:


The bug was introduced in version 3.25.0.  It was due to a LIKE
pattern attempting to match against "sqlite_%%" except "_" means
"match any single character to to LIKE operator, so we had to go back
in and escape that character.  The problem is fixed on trunk.

You can find the relevant changes and a reference to the ticket on the
timeline: https://www.sqlite.org/src/timeline?ymd=20190419



Thank you for the update and the fast response.

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

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

Re: [sqlite] Table names starting with sqlite (not sqlite_)

[Luuk]

On 19-4-2019 16:19, Semih Hazar wrote:

Hi,

With 3.28.0 I'm getting an inconsistent behavior if I try to rename a table
if its name starts with "sqlite". It's not "sqlite3_" by the way, which
just gives an error since it's a reserved keyword.

After renaming, .table command shows the new name, but a select from the
table errors out. If I try to rename it again, things seems to be OK.
Please take a look at the output below which works fine with 3.24.0

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> CREATE TABLE "sqlite1234" ("id" integer);
sqlite> ALTER TABLE "sqlite1234" RENAME TO "User";
sqlite> SELECT * FROM "User";
Error: no such table: User
sqlite> .table
User
sqlite> SELECT * FROM sqlite_master;
table|User|User|2|CREATE TABLE "sqlite1234" ("id" integer)
sqlite> ALTER TABLE "sqlite1234" RENAME TO "User";
sqlite> SELECT * FROM "User";
sqlite>

Best Regards,
Semih Hazar


This is the same behavior as in 3.27.2:

D:\TEMP>sqlite3 temp.sqlite
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> CREATE TABLE "sqlite1234" ("id" integer);
sqlite> ALTER TABLE "sqlite1234" RENAME TO "User";
sqlite>  SELECT * FROM "User";
Error: no such table: User
sqlite> .table
User
sqlite> SELECT * FROM sqlite_master;
table|User|User|2|CREATE TABLE "sqlite1234" ("id" integer)
sqlite> ALTER TABLE "sqlite1234" RENAME TO "User";
sqlite> SELECT * FROM "User";
sqlite>
sqlite>
sqlite> .table
User
sqlite> .schema
CREATE TABLE IF NOT EXISTS "User" ("id" integer);
sqlite>

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

Re: [sqlite] Inserting the same column multiple times

[Luuk]

On 14-4-2019 09:36, Shawn Wagner wrote:

Discovered this tonight answering a question on stack overflow:

sqlite> create table foo(a, b);
sqlite> insert into foo(a,b,a,b) values(1,2,3,4);
sqlite> select * from foo;
a   b
--  --
1   2

Inserting a column multiple times only uses the first corresponding value.
I don't see this documented anywhere.

By contract, a single UPDATE of the same column multiple times uses the
last one and ignores the rest:

sqlite> update foo set a=3, a=4;
sqlite> select * from foo;
a   b
--  --
4   2

And that is documented.

The inconsistency is annoying, but changing how either one works will
doubtless break somebody's code. Maybe clarify INSERT's behavior in its
documentation? Logging a warning in the case of a column being used
multiple times might be nice too.


An error like this one should do:

sqlite> insert into foo values(1,2,3,4);
Error: table foo has 2 columns but 4 values were supplied

Because, i do think, that it would never be possible to specify more 
than the number of columns in an insert statement?



MS-SQL has this:

create table foo(a int, b int);

insert into foo(a,b,a,b) values(1,2,3,4);

Msg 264, Level 16, State 1, Line 3
The column name 'a' is specified more than once in the SET clause or 
column list of an INSERT. A column cannot be assigned more than one 
value in the same clause. Modify the clause to make sure that a column 
is updated only once. If this statement updates or inserts columns into 
a view, column aliasing can conceal the duplication in your code.

Msg 264, Level 16, State 1, Line 3
The column name 'b' is specified more than once in the SET clause or 
column list of an INSERT. A column cannot be assigned more than one 
value in the same clause. Modify the clause to make sure that a column 
is updated only once. If this statement updates or inserts columns into 
a view, column aliasing can conceal the duplication in your code.



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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Luuk]

On 10-4-2019 10:28, Kees Nuyt wrote:

On Wed, 10 Apr 2019 13:17:23 +1000, John wrote:


I have not used extensions before. I understand that some are included
in the amalgamation source file and that some of these are enabled by
default. So, which ones are built-in and which of those are enabled in
the standard downloadable Win32 SQLite CLI?

sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;


D:\>sqlite3
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode column
sqlite> .header on
sqlite> .width 28 8
sqlite> select * from pragma_function_list;
Error: no such table: pragma_function_list
sqlite>

This is "the standard downloadable Win32 SQLite CLI?"


If an extension is built-in and enabled, what do I need to do to use it.
The instructions seem to be for the case where an extension is built as
an external library (.dll) to be loaded by eg. .load ./csv where csv
would be csv.dll in the current directory. If the csv extension was
built-in, would I still need to load it to activate it?

I don't think so.


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

Re: [sqlite] Built in and enabled Virtual Table extensions in SQLite CLI?

[Luuk]

On 10-4-2019 05:17, John McMahon wrote:
which ones are built-in and which of those are enabled in the standard 
downloadable Win32 SQLite CLI


Some possibility tho show this like (i.e.) '.extensions' would be very 
nice!?





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

Re: [sqlite] Help with INDEXing a query

[Luuk]

On 3-4-2019 19:34, Jose Isaias Cabrera wrote:

Never mind, guys.  I was missing the INDEX for the table for the first left 
join:

CREATE INDEX PLE_ProjID ON Project_List_Extra (ProjID);

Everything is nice, now.  Thanks.


From: Jose Isaias Cabrera
Sent: Wednesday, April 3, 2019 01:02 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Help with INDEXing a query


Greetings!

I am trying to speed up this query,


.

sqlite> .expert
sqlite> select * from testing where a=42;
CREATE INDEX testing_idx_0061 ON testing(a);

SEARCH TABLE testing USING INDEX testing_idx_0061 (a=?)

sqlite>

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

Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)

[Luuk]

On 30-3-2019 12:28, softw...@quantentunnel.de wrote:

Dear colleagues

I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as it 
seems not documented.

When using an init file (even if an empty file), sqlite3 outputs an extra empty 
line to stdout. This messes up parsing of the sqlite3 output, as this line is 
not present in the absence of an init file:

buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);"
buero:~$ touch /tmp/test.init
buero:~$ ls -l /tmp/test.*
-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null

1
buero:~$


My configuration
buero:~$ sqlite3 --version
3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
buero:~$ uname -a
Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 
2019 x86_64 x86_64 x86_64 GNU/Linux
___


upgrade ?

~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);"

~$
~$ touch /tmp/test.init
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
~$
~$
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null

~$ ls -l /tmp
total 16
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db
-rw-rw-rw- 1 luuk luuk    0 Mar 30 17:18 test.init
~$ sqlite3 -version
3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

~$

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

Re: [sqlite] Clear sqlite3 terminal enhancement

[Luuk]

On 28-3-2019 14:42, Dominique Devienne wrote:

On Thu, Mar 28, 2019 at 1:16 PM Clemens Ladisch  wrote:


Jeffrey Walton wrote:

When working in the Linux terminal we can clear the scrollback with
the 'clear' command; and we can delete all history and scrollback with
the 'reset' command. I am not able to do the same within the sqlite3
terminal.

Those are programs run from the shell.  So you can use ".shell clear" or
".shell reset".


Nice trick, thanks for sharing. `.shell cls` on Windows worked for me. --DD



The 'reset' option is even shorter on Windows.

Try ALT+F7

;)


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

[sqlite] where did my data go ? Re: Import data into a temporary table

[Luuk]

On 7-3-2019 22:45, Eric Tsau wrote:

Hi,
Is it possible to add the option of importing data into a temporary table?
Currently you have to create a temporary table first before importing to
it, or having to drop the table afterwards.
.import dump.csv temp.table
or
.import dump.csv attach.table

Regards
Eric
C:\TEMP>del test.sqlite



C:\TEMP>type abc.csv
a,b,c
1,2,3
4,5,6
7,8,9

C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
sqlite> .import abc.csv test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
  "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>
sqlite> .import abc.csv temp.test
Error: no such table: temp.test
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
  "a,b,c" TEXT
);
COMMIT;
sqlite> .quit


Where did my data go (see above)?

Luckily it's there when i restart sqlite3.exe:



C:\TEMP>sqlite3 test.sqlite
SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
sqlite> select * from test;
1,2,3
4,5,6
7,8,9
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(
  "a,b,c" TEXT
);
INSERT INTO test VALUES('1,2,3');
INSERT INTO test VALUES('4,5,6');
INSERT INTO test VALUES('7,8,9');
COMMIT;
sqlite>


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

Re: [sqlite] Handling ROLLBACK

[Luuk]

On 3-3-2019 16:27, Jesse Rittner wrote:

It didn't work correctly because the sequence of commands you sent doesn't
make sense.

BEGIN;
...
RELEASE point1;
...
ROLLBACK;
...
END;

First you began an explicit transaction. Then you tried to release a
savepoint that you never created. (Hence the "no such savepoint" error
message.) Then you rolled back the explicit transaction. Then you tried to
end (i.e., commit) the explicit transaction, but you already rolled back, so
this is invalid. (Hence the "no transaction is active" error message.)

I'm not quite sure what you were trying to do. It appears you want a nested
transaction, which can only be accomplished with savepoints.



i was just 'testing' a bit with transactions (and savepoints),

in order to try to understand the question of Simon.

But it is still no very clear to /me what the meaning of his question 
is/was.



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

Re: [sqlite] Handling ROLLBACK

[Luuk]

On 3-3-2019 15:33, Simon Slavin wrote:

On 3 Mar 2019, at 2:29pm, Luuk  wrote:


Conclusion: RESTORE does not end TRANSACTION ?

Your statement is correct.  However, RESTORE is a partner of SAVEPOINT.  My 
question does not consider SAVEPOINTs.

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



Something like this (SAVEPOINT/RELEASE):

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>

Something like this (NO SAVEPOINT/RELEASE):

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite>
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
Error: no such savepoint: point1
sqlite> INSERT INTO test VALUES (11);
sqlite> ROLLBACK;
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
Error: cannot commit - no transaction is active
sqlite> SELECT * FROM test;
1
2
3
4
10
12
sqlite>

Record '11' is missing, seems OK because off ROLLBACK

But i'am a bit confused about the error "Error: cannot commit - no 
transaction is active"


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

Re: [sqlite] Handling ROLLBACK

[Luuk]

On 3-3-2019 15:01, Richard Damon wrote:

On Mar 3, 2019, at 8:32 AM, Simon Slavin  wrote:

To summarize, the list feels that this is an incorrect model

BEGIN;
... first set of commands
ROLLBACK;
... second set of commands
END;

whereas this is how things are meant to work:

BEGIN;
... first set of commands
ROLLBACK;
BEGIN;
... second set of commands
END;

and that since ROLLBACK ends a transaction, it releases locks.  The above is 
correct for all journalling models except for OFF, where the effect of ROLLBACK 
is undefined.  (I'm ignoring SAVEPOINTs for now.)

Simon.


And the way to get the first pattern is to set a SAVEPOINT right after the 
BEGIN and restore back to it instead of using ROLLBACK.
___


Conclusion: RESTORE does not end TRANSACTION ?

or am i missing something important in this discussion ;)

sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> ROLLBACK;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
Error: cannot commit - no transaction is active
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>
sqlite>
sqlite>
sqlite>
sqlite> DELETE FROM test WHERE i>=11;
sqlite> .dump test
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(i int primary key);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(2);
INSERT INTO test VALUES(3);
INSERT INTO test VALUES(4);
INSERT INTO test VALUES(10);
COMMIT;
sqlite>
sqlite> BEGIN;
sqlite> SAVEPOINT point1;
sqlite> INSERT INTO test VALUES (10);
Error: UNIQUE constraint failed: test.i
sqlite> RELEASE point1;
sqlite> INSERT INTO test VALUES (11);
sqlite> INSERT INTO test VALUES (12);
sqlite> END;
sqlite> SELECT * FROM test;
1
2
3
4
10
11
12
sqlite>

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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Luuk]

On 18-2-2019 14:51, Simon Slavin wrote:

On 18 Feb 2019, at 1:33pm, Rocky Ji  wrote:


Here's the new thing: https://pastebin.com/raw/pSqjvJdZ

Again, can we get rid of them sub-query?

The "sum()" with the "group by" in the subquery make it difficult.

There's nothing wrong with that query just as it is.  In that form SQLite will 
figure out a very efficient query plan for what you're trying to do.  Don't 
worry about there being a subquery in there.

Simon.


Indeed, there's nothing wrong with the sub-query, but it 'can' be done 
without it. This 'example' has way too few records to test what 
performance will do when using the 'without sub-query', compared to the 
simple variant.



sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where 
t2.tekst=t1.tekst) as totals from test t1;

QUERY PLAN
|--SCAN TABLE test AS t1
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN TABLE test AS t2
i tekst  tota
  -  
1 a  5
2 b  2
3 c  3
4 a  5
sqlite> select t1.i, t1.tekst, sum(t2.i) from test t1,test t2 where 
t2.tekst=t1.tekst group by t1.i,t1.tekst;

QUERY PLAN
|--SCAN TABLE test AS t1
|--SEARCH TABLE test AS t2 USING AUTOMATIC COVERING INDEX (tekst=?)
`--USE TEMP B-TREE FOR GROUP BY
i tekst  sum(
  -  
1 a  5
2 b  2
3 c  3
4 a  5
sqlite>

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

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

[Luuk]

On 17-2-2019 17:46, Rocky Ji wrote:

Hello everyone,

How can I prevent group by clause from reducing the number of rows without
affecting accuracy of what aggregate functions provide?

Scenario:
My club has-many coaches.
Each coach trains a team of players.
Of course, a player has-many matches and a match has-many players.
Given the schema: https://pastebin.com/raw/C77mXsHJ
and sample data: https://pastebin.com/raw/GhsYktRS

I want a result like: https://pastebin.com/raw/stikDvYS

NOTE: for a match X, profit/match is `sum(salary of all players playing in
X) - X.bets`

To get the result, here's what I came up with: https://pastebin.com/ckgicBWS

If I un-comment those lines, I get the profit column but rows are reduced,
how can I prevent that?

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



C:\TEMP>\util\sqlite3
SQLite version 3.27.1 2019-02-08 13:17:39
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(i int, tekst varchar(20));
sqlite> insert into test values (1,'a');
sqlite> insert into test values (2,'b');
sqlite> insert into test values (3,'c');
sqlite> insert into test values (4,'a');
sqlite> select t1.i, t1.tekst, (select sum(t2.i) from test t2 where 
t2.tekst=t1.tekst) as totals from test t1;

1|a|5
2|b|2
3|c|3
4|a|5
sqlite>

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

Re: [sqlite] Min/Max and skip-scan optimizations

[Luuk]

On 4-2-2019 14:55, Gerlando Falauto wrote:

Thank you Luuk, I understand your point.
However, the query plan already takes advantage of the index and should be
retrieving data in that order.
Reading the docs
https://www.sqlite.org/optoverview.html#order_by_optimizations my
understanding was that
SQLite would be taking advantage of that.
So perhaps my use case it's too complicated (many columns -- some filtered,
some not -- skip/scan, all together) to make it obvious to the query
planner that data *is* already sorted.
Or maybe it never occurred to anyone that someone might be trying to do
something like that.
Or (most likely) my understanding of how data is retrieved is plain wrong...

Thank you!
Gerlando



It says:

SQLite *attempts* to use an index to satisfy the ORDER BY clause of a 
query when possible



To be (abolutely!) SURE results are in the correct order, you need an 
ORDER BY.


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

Re: [sqlite] Min/Max and skip-scan optimizations

[Luuk]

On 3-2-2019 23:29, Gerlando Falauto wrote:

IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should
ideally yield the exact same query plan.
In the end adding an ORDER BY clause on the exact same columns of the index
used to traverse the table, should be easily recognizable.
Knowing absolutely nothing about the internals though, I have no idea
whether this particular use case has been overlooked, or it would just be
unfeasible to handle it.



In SQL, when doing a SELECT, the order of the results is undetermined 
(by definition).


If you want/need to results to be ORDERed, you need to add 'ORDER BY'. 
This will always show as an extra step in your QUERY PLAN.


One can never know (for sure) if the output of this is in the correct order:

CREATE TABLE test(i primary key);
INSERT INTO test values(4);
INSERT INTO test values(2);
INSERT INTO test values(3);
INSERT INTO test values(1);
SELECT i FROM test;

4
2
3
1

To 'know' it is in the correct order one has to define an ORDER BY to 
specify in which order the data should be returned


SELECT i FROM test order by 2*i+i%2;

1
2
3
4



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

Re: [sqlite] Variable Declaration

[Luuk]

On 19-1-2019 14:49, Luuk wrote:



This question is not about: 'parameter binding'!

It's about 'variable decalaration'...

oops 'variable declaration'

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

Re: [sqlite] Variable Declaration

[Luuk]

On 19-1-2019 14:23, Jesse Rittner wrote:

What language do you want to use? Parameter binding is a feature of the
SQLite C API. So pretty much any language with a C FFI will suffice. There's
a list on Wikipedia, not sure how authoritative it is.
https://en.wikipedia.org/wiki/SQLite#Programming_language_support (Note:
When it says they "provide bindings for SQLite", it's referring to the FFI,
not parameter binding.)



This question is not about: 'parameter binding'!

It's about 'variable decalaration'...


In MS-SQL you can do things like:

C:\temp>PSQL.EXE -E -S SQL2017DEV

1> declare @count int = 10;
2> select * from test where i<=@count
3> go
 i
 ---
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10

(10 rows affected)
1>



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

Re: [sqlite] Session extension, "INSERT OR REPLACE" and "WITHOUT ROWID"

[Luuk]

On 6-1-2019 14:11, Daniel Kraft wrote:

Since it seems that the mailing list swallowed my attached example code,
I've put it on Github as well:

https://gist.github.com/domob1812/7842edade949b5169edaf9de79f1b6d1



The example code (from github.com), so no-one has to go-to github:

#include 

#include 

#include 
#include 

int
printResults (void*, int numColumns, char** values, char** columns)
{
  for (int i = 0; i < numColumns; ++i)
std::cout << "  " << columns[i] << " = " << values[i];
  std::cout << std::endl;

  return 0;
}

int
abortOnConflict (void* ctx, int conflict, sqlite3_changeset_iter* it)
{
  LOG (ERROR) << "Changeset application has a conflict of type " << conflict;
  return SQLITE_CHANGESET_ABORT;
}

int
main (int argc, char** argv)
{
  LOG (INFO)
  << "Using SQLite version " << SQLITE_VERSION
  << " (library version: " << sqlite3_libversion () << ")";
  CHECK_EQ (SQLITE_VERSION_NUMBER, sqlite3_libversion_number ())
  << "Mismatch between header and library SQLite versions";

  sqlite3* db;
  CHECK_EQ (sqlite3_open (":memory:", ), SQLITE_OK);

  /* Create schema (table WITHOUT ROWID) and insert some initial data.  */
  CHECK_EQ (sqlite3_exec (db, R"(
CREATE TABLE `test` (
  `id` INTEGER PRIMARY KEY,
  `value` INTEGER
)
WITHOUT ROWID
;
INSERT INTO `test` (`id`, `value`) VALUES (100, 1);
  )", nullptr, nullptr, nullptr), SQLITE_OK);

  /* Make a modification and record a changeset for it.  */
  sqlite3_session* session;
  CHECK_EQ (sqlite3session_create (db, "main", ), SQLITE_OK);
  CHECK_EQ (sqlite3session_attach (session, nullptr), SQLITE_OK);
  CHECK_EQ (sqlite3_exec (db, R"(
-- UPDATE `test` SET `value` = 2 WHERE `id` = 100;
-- INSERT INTO `test` (`id`, `value`) VALUES (200, 3);
INSERT OR REPLACE INTO `test`
  (`id`, `value`) VALUES (100, 2), (200, 3)
  )", nullptr, nullptr, nullptr), SQLITE_OK);
  int changeSize;
  void* changeBytes;
  CHECK_EQ (sqlite3session_changeset (session, , ),
SQLITE_OK);
  sqlite3session_delete (session);

  /* Apply the inverted changeset to revert the change.  */
  int invertedSize;
  void* invertedBytes;
  CHECK_EQ (sqlite3changeset_invert (changeSize, changeBytes,
 , ),
SQLITE_OK);
  CHECK_EQ (sqlite3changeset_apply (db, invertedSize, invertedBytes, nullptr,
, nullptr),
SQLITE_OK);
  sqlite3_free (changeBytes);
  sqlite3_free (invertedBytes);

  /* Print contents of table for debugging.  */
  std::cout << "Final values of table:" << std::endl;
  CHECK_EQ (sqlite3_exec (db, R"(
SELECT * FROM `test`
  )", , nullptr, nullptr), SQLITE_OK);

  sqlite3_close (db);
  return EXIT_SUCCESS;
}

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

Re: [sqlite] i Know i should use 'AS', but ....

[Luuk]

On 26-12-2018 08:20, Clemens Ladisch wrote:

Luuk wrote:

sqlite> .mode column
sqlite> .headers on
sqlite> select 1 as X,date() as d union all select 2,date() union all
select 3,datetime();
X   d
--  --
1   2018-12-25
2   2018-12-25
3   2018-12-25

The value is longer than the column with (which is probably based on the
values in the first line).

Try ".width 10 20".


A, now i know why i do not use 'mode column'

It's too much typing work to find out how wide the columns are.

'.mode tabs' seems to fit my need more.

Thanks!


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

Re: [sqlite] [OT] Re: i Know i should use 'AS', but ....

[Luuk]

On 24-12-2018 21:25, Shawn Wagner wrote:

Using '.mode column" in conjunction with ".headers on" you're already using
makes it a lot more obvious.



like this:


SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 1 as X,date() as d union all select 2,date() union all 
select 3,datetime();

1|2018-12-25
2|2018-12-25
3|2018-12-25 18:56:41
sqlite> .mode column
sqlite> .headers on
sqlite> select 1 as X,date() as d union all select 2,date() union all 
select 3,datetime();

X   d
--  --
1   2018-12-25
2   2018-12-25
3   2018-12-25
sqlite> select 1 as X,datetime() as d union all select 2,date() union 
all select 3,datetime();

X   d
--  ---
1   2018-12-25 18:57:11
2   2018-12-25
3   2018-12-25 18:57:11
sqlite>



the first result seems correct (datetime after the 3)

in the secondresult the third line is casted to date? (or misses time?)

the above seems to be not correct when looking at the last query.

Same result in version 'SQLite version 3.26.0'




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

[sqlite] [OT] Re: i Know i should use 'AS', but ....

[Luuk]

On 24-12-2018 19:21, Peter Johnson wrote:

The headers are present in all three queries you pasted.

The first result shows two rows, the top row is the header.

The other two results show 4 rows each, the top row of each is the header
row.

-P

On Tue, 25 Dec 2018, 3:42 AM Luuk 
sqlite> .version
SQLite 3.26.0

sqlite> .headers on

sqlite> select 1 as X,date();
X|date()
1|2018-12-24
sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x
union all select 2 union all select 3);
x|row_number() over (order by 1 desc)
3|1
2|2
1|3

Why are the headers missing in above query?




Why was i overlooking this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] i Know i should use 'AS', but ....

[Luuk]

sqlite> .version
SQLite 3.26.0

sqlite> .headers on

sqlite> select 1 as X,date();
X|date()
1|2018-12-24
sqlite> select x,row_number() over (order by 1 desc) from (select 1 as x 
union all select 2 union all select 3);

x|row_number() over (order by 1 desc)
3|1
2|2
1|3

Why are the headers missing in above query?



sqlite> select x,row_number() over (order by 1 desc) as Y from (select 1 
as x union all select 2 union all select 3);

x|Y
3|1
2|2
1|3
sqlite>

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

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

[Luuk]

On 14-12-2018 16:54, Keith Medcalf wrote:

You get the message about missing collating sequences because the database is 
using collating sequences that you don't have (ie, are missing) in your version 
of SQLite3.




Extensioon in SQLite, thats somewhere on my TODO list ;)

Is there any documentation on where to find these extensions for Windows?

Trying to compile something meself (on windows) is a too deep hole right 
now ;)





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

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

[Luuk]

On 14-12-2018 11:22, Simon Slavin wrote:

On 14 Dec 2018, at 10:15am, Luuk  wrote:


why do i get 'no collation sequence' with this statement:

sqlite> select Folder_Path from Folder;
Error: no such collation sequence: NoCaseUnicode

There's a UNIQUE index for Folder_Path COLLATE NoCaseUnicode.  Iterating 
through that index would involve processing less data than reading the entire 
table.  So SQLite decides that using that index to complete your SELECT command 
is a good strategy.  But that collation is not included in the default 
compilation of SQLite so SQLite realises it can't use that index.

Simon.


OK, but a slightly different statement, which needs to scan the complete 
table, also returns the error:


sqlite> explain query plan select Folder_Path from Folder order by 
upper(Folder_Path);

Error: no such collation sequence: NoCaseUnicode
sqlite> explain query plan select upper(Folder_Path) from Folder order 
by upper(Folder_Path);

Error: no such collation sequence: NoCaseUnicode

My conclusion would be that this is a bug (in the definition of the 
table, no in SQLite).


Or, but i double it will make sence,

When SQLite realises it can't use that index, should it revert to a full 
table scan?


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

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

[Luuk]

On 14-12-2018 11:15, Luuk wrote:


On 13-12-2018 18:26, Simon Slavin wrote:
On 13 Dec 2018, at 2:57pm, Carlo capaldo  
wrote:



UPDATE folder

    SET Folder_Path = 'E:\Photos'

would change the directory references in all existing 16 rows 
currently containing Folder_Path references to the wrong locations 
to the correct location ‘E:\Photos’ however this also generates a 
“UNIQUE constraint failed” error.
You are correct about what the command does.  However, the schema of 
the database (its structure, including how all its tables, indexes 
and views are defined) includes the following:


CREATE UNIQUE INDEX Folder_Path ON Folder(Folder_Path);

This means that no two rows in the Folder TABLE may have the same 
values for Folder_Path .  I'm sure that makes sense to the person who 
defined the schema, so let's look at the current values:


sqlite> .mode column
sqlite> .width 60 20
sqlite> SELECT Folder_Path,Folder_DisplayName FROM Folder;



Just because i was interested in the structure of tis SQLite db, i was 
looking at the version on my computer.


sqlite> select Folder_Path, Folder_DisplayName from Folder;
Folder_Path  Folder_DisplayName
---  --
C:\Users\Luuk2\Pictures  Pictures
C:\Users\Luuk2\Pictures  Camera Roll
C:\Users\Luuk2\OneDrive  Afbeeldingen
C:\Users\Luuk2\Pictures  Saved Pictures
C:\Users\Luuk2\OneDrive  Schermopnamen
C:\Users\Luuk2\Pictures  Screenshots

OK so far, no problems, but why do i get 'no collation sequence' with 
this statement:


sqlite> select Folder_Path from Folder;
Error: no such collation sequence: NoCaseUnicode
sqlite>



For completenes the CREATE TABLE for this table (without indexes an 
triggers):


CREATE TABLE Folder(
    Folder_Id INTEGER PRIMARY KEY,
    Folder_ParentFolderId INTEGER REFERENCES Folder ON DELETE CASCADE,
    Folder_LibraryRelationship INTEGER,
    Folder_Source INTEGER,
    Folder_SourceId INTEGER REFERENCES Source ON DELETE CASCADE,
    Folder_Path TEXT COLLATE NoCaseUnicode,
    Folder_DisplayName TEXT COLLATE NoCaseLinguistic,
    Folder_DateCreated INTEGER,
    Folder_DateModified INTEGER,
    Folder_KnownFolderType INTEGER,
    Folder_SyncWith INTEGER,
    Folder_StorageProviderFileId TEXT,
    Folder_InOneDrivePicturesScope INTEGER,
    Folder_ItemCount INTEGER);




sqlite> .version
SQLite 3.21.0 2017-10-24 18:55:49 
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de48827

sqlite>




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

Re: [sqlite] Updating Folder_Path in Windows 10 Photos App

[Luuk]

On 13-12-2018 18:26, Simon Slavin wrote:

On 13 Dec 2018, at 2:57pm, Carlo capaldo  wrote:


UPDATE folder

SET Folder_Path = 'E:\Photos'

would change the directory references in all existing 16 rows currently 
containing Folder_Path references to the wrong locations to the correct 
location ‘E:\Photos’ however this also generates a “UNIQUE constraint failed” 
error.

You are correct about what the command does.  However, the schema of the 
database (its structure, including how all its tables, indexes and views are 
defined) includes the following:

CREATE UNIQUE INDEX Folder_Path ON Folder(Folder_Path);

This means that no two rows in the Folder TABLE may have the same values for 
Folder_Path .  I'm sure that makes sense to the person who defined the schema, 
so let's look at the current values:

sqlite> .mode column
sqlite> .width 60 20
sqlite> SELECT Folder_Path,Folder_DisplayName FROM Folder;



Just because i was interested in the structure of tis SQLite db, i was 
looking at the version on my computer.


sqlite> select Folder_Path, Folder_DisplayName from Folder;
Folder_Path  Folder_DisplayName
---  --
C:\Users\Luuk2\Pictures  Pictures
C:\Users\Luuk2\Pictures  Camera Roll
C:\Users\Luuk2\OneDrive  Afbeeldingen
C:\Users\Luuk2\Pictures  Saved Pictures
C:\Users\Luuk2\OneDrive  Schermopnamen
C:\Users\Luuk2\Pictures  Screenshots

OK so far, no problems, but why do i get 'no collation sequence' with 
this statement:


sqlite> select Folder_Path from Folder;
Error: no such collation sequence: NoCaseUnicode
sqlite>



For completenes the CREATE TABLE for this table (without indexes an 
triggers):


CREATE TABLE Folder(
    Folder_Id INTEGER PRIMARY KEY,
    Folder_ParentFolderId INTEGER REFERENCES Folder ON DELETE CASCADE,
    Folder_LibraryRelationship INTEGER,
    Folder_Source INTEGER,
    Folder_SourceId INTEGER REFERENCES Source ON DELETE CASCADE,
    Folder_Path TEXT COLLATE NoCaseUnicode,
    Folder_DisplayName TEXT COLLATE NoCaseLinguistic,
    Folder_DateCreated INTEGER,
    Folder_DateModified INTEGER,
    Folder_KnownFolderType INTEGER,
    Folder_SyncWith INTEGER,
    Folder_StorageProviderFileId TEXT,
    Folder_InOneDrivePicturesScope INTEGER,
    Folder_ItemCount INTEGER);





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

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Luuk]

On 11-12-2018 10:09, Wout Mertens wrote:

Hi Luuk,

Not sure if you realize this, but your email comes over as very aggressive,
and if there's one person on this mailing list that doesn't deserve that,
it's dr Hipp.

In particular, the quotes around forgot seem to imply that it was forgotten
on purpose.

Personally, I would have worded it as "I looked at the test and I wonder if
this test case is addressed". Email communication is easy to misconstrue…

Cheers,

Wout.



Sorry, again sorry,

it must have been an interlingual misphrased wording of /me  ;)

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

Re: [sqlite] SQLITE gives incorrect results for 'NOT IN' query if partial index exists

[Luuk]

On 8-12-2018 23:47, Richard Hipp wrote:

On 12/8/18, Deon Brewis  wrote:

I'm curious how that test that you added works?  i.e. What causes the test
to fail if the results are wrong?

The particular test case you are referring to is written in in the TCL
language.  The TCL tests are the oldest set of tests for SQLite since
SQLite is really a TCL-extension that escaped into the wild.

Everything in TCL is a command followed by zero or more arguments.  In
this sense, TCL is very much like Bourne shell.  COMMAND ARG1 ARG2
ARG3   Where TCL excels is in how it quotes the arguments.  Curly
braces {...} are quoting characters that nest.   Take, for example,
the "if" command in TCL:

 if {$a<0} {
   set a [expr {-$a}]
 } else {
   set a [expr {$a+10}]
 }

In this case, the "if" command has four arguments

  if EXPR SCRIPT else SCRIPT

When the "if" command runs, it evaluates its first argument EXPR.  If
EXPR is true, then the if command runs the SCRIPT given in the second
argument.  Otherwise it runs the SCRIPT in the fourth argument.  The
magic, you see, is in the use of nested curly braces for quoting.

The test command you refer to is this:

do_execsql_test index6-12.1 {
   DROP TABLE IF EXISTS t1;
   DROP TABLE IF EXISTS t2;
   CREATE TABLE t1(a,b);
   INSERT INTO t1 VALUES(1,1);
   INSERT INTO t1 VALUES(2,2);
   CREATE TABLE t2(x);
   INSERT INTO t2 VALUES(1);
   INSERT INTO t2 VALUES(2);
   SELECT 'one', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
   CREATE INDEX t1a ON t1(a) WHERE b=1;
   SELECT 'two', * FROM t2 WHERE x NOT IN (SELECT a FROM t1);
} {}

The name of the command is "do_execsql_test".  That command takes
three arguments:

 do_execsql_test   TESTNAME   SQL-SCRIPT   EXPECTED-RESULT

This command simply runs the SQL found in its second argument and
accumulates the results.  The accumulated result should exactly match
the third argument.  If it does not match, then it prints an error
message and increments the error counter.

If you start with the canonical SQL source code, you can generate the
appropriate TCL interpreter by typing

 ./configure; make testfixture

Or on windows:

  nmake /f Makefile.msc testfixture.exe

Then you say "./testfixture test/index6.test" to run that particular test file.



You 'forgot' to add the example that Olivier Mascia gave?:

select * from bar WHERE x IN (SELECT y from foo); -- this will wrongly return 1.





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

Re: [sqlite] Fill empty space with random

[Luuk]

On 14-10-2018 17:07, J Decker wrote:
> (sorry for the math err s/16/32/g and s/512/256/   - I double and halfed
> the wrong directions.)
>
> On Sun, Oct 14, 2018 at 7:57 AM J Decker  wrote:
>
>>
>> On Sun, Oct 14, 2018 at 7:24 AM Luuk  wrote:
>>
>>> On 14-10-2018 16:17, Simon Slavin wrote:
>>>> On 14 Oct 2018, at 12:56pm, J Decker  wrote:
>>>>
>>>>> Is there maybe a compile option for sqlite to fill empty space in a db
>>> with random data rather than 0 ?
>>>> There is not.  But
>>>>
>>>> (A) It may be an easy change to the source code
>>>> (B) Your operating system may have a setting to do this automatically
>>> to freed blocks on a storage device.
>>>> (C) Your device driver may have a setting to do this automatically to
>>> freed blocks on the device.
>>>> That type of security is normally done at OS or device level, not by
>>> each individual app.
>>>> Simon.
>>>>
>>> Can you give any hints on why it would be a security issue to fill
>>> 'empty space' with 0, and why 'random data' should be used?
>>>
>>> ?
>>>
>> I hesitate to describe the real scenario; and want to instead manufacture
>> one; but in either case I feel there will be more comments about the
>> underlaying system than on Sqlite itself.
>>
>> In the simple case, the VFS that the sqlite Db is mounted in is encrypted
>> with a long key.  The key has cycles at 4096(A) and 16(B1-Bn) bytes
>> (4096/16 = 256 cycles of Bn); such that each sector is masked with
>> A^B1(256x), A^B2(256x), ... all together there is no repetition because the
>> change from Bn to B(n+1) at the 4096 boundary makes the stream overall
>> appear continuously random.
>> Only data that is written is actually masked...
>>
>> Sqlite likes to write 0's in large splotches (in my usage); which leaks
>> key information; (only slightly more than the data stored in tables
>> typically, which is a lot of the same bytes (0, 1 for instance and A-Z, a-z
>> less-so; but all of that has upper bit(s) that are 0... )
>>
>> And even is a specific sector (or several) is 'cracked' it doesn't do any
>> good for any other page... but if LOTS of pages are found, it becomes
>> easier to find what the overall A key is, which makes finding sector keys
>> that you only need a few 32-64 bytes of 0's to reveal the sector specific
>> key (for later use?)
>>
>> The keys are a procedurally generated with a PRNG sha2 bit streams based;
>> so 512 bits (16 bytes) at a time; and sha algorithms generates VERY good PR
>> numbers. which can be consumed as end-to-end bit streams.
>>
>> I might look into it; there are certainly a great test suite available to
>> reveal issues; but I expect Sqlite 'expects' memory to be 0 initialized
>> (even when filled from disk) and that it will be a HUGE can of worms.
>>
>>
>>

Thanks for the explanation ...

I never would have guessed that you "I double and halfedthe wrong
directions. " 



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

Re: [sqlite] Fill empty space with random

[Luuk]

On 14-10-2018 16:17, Simon Slavin wrote:
> On 14 Oct 2018, at 12:56pm, J Decker  wrote:
>
>> Is there maybe a compile option for sqlite to fill empty space in a db with 
>> random data rather than 0 ?
> There is not.  But
>
> (A) It may be an easy change to the source code
> (B) Your operating system may have a setting to do this automatically to 
> freed blocks on a storage device.
> (C) Your device driver may have a setting to do this automatically to freed 
> blocks on the device.
>
> That type of security is normally done at OS or device level, not by each 
> individual app.
>
> Simon.
>
Can you give any hints on why it would be a security issue to fill
'empty space' with 0, and why 'random data' should be used?

?


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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Luuk]

On 7-10-2018 01:18, Warren Young wrote:
> On Oct 6, 2018, at 2:21 PM, Simon Slavin  wrote:
>>
>> Excel ate the financial business world because companies use Excel to solve 
>> a simple problem, then add a feature, then add another feature, and keep 
>> going until they have some crawling creeping horror that needs to return to 
>> R'lyeh.  There is never any point in this process when a manager looks at 
>> what's being done with Excel and says "Okay we need to hire a programmer to 
>> turn this into a proper App.”.
> Up to a certain point, there’s nothing wrong with that process.

Yes, there is!
>
> One of the tasks smart management should be doing is keeping an eye on these 
> guerrilla software development projects and step in when it becomes clear 
> they’ve got a valuable business tool that needs to be rewritten on a stronger 
> foundation to allow its continued growth and increasing value to the business.
The 'software development department' should forbid 'software
developmentprojects' which iare not done by them.
>
> If your business is big enough to have an IT staff with at least one 
> professional programmer, turning Excel prototypes into professional business 
> tools is mainly a matter of scheduling.  (Again, a function of management.)
"turning Excel prototypes into..." is a NO-GO.  It simply means that IT
staff did not do its own work 'the correct way'!.
>
> The main need for a personal database is in organizations too small to have 
> full-time programming staff.  (That includes most home use as well.)
>
> Maybe that’s the biggest reason this sort of software is rare and expensive: 
> big business doesn’t need it, so it only gets funded by small businesses and 
> home users, who are notoriously tight when it comes to software licensing.
>
> Contrast the elements of a traditional office software suite, which are 
> needed clear through the Fortune uint8_t.
> ___
>

At the office where i work we have an Excel sheet which takes >10
minutes to load, and i do not beleive its because of the absolute size
of the excel sheet
This Excel sheet contains (or tries to contain) )functionality which
should have been implemented a long time ago in our ERP-application.




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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Luuk]

On 6-10-2018 18:23, Warren Young wrote:
> On Oct 6, 2018, at 9:46 AM, Roger Schlueter  wrote:
>> In addition to the freebies mentioned by Luuk, WordPerfect Office X9 is a 
>> commercial product that includes all of Excel's features including 
>> import/export of Excel data.
> I guess neither of you read the article, because the point of the original 
> question isn’t to get recommendations for yet another spreadsheet program, 
> it’s to get a program that lets us get away from spreadsheets-as-databases, 
> because when such creations get complicated, they become very difficult to 
> debug.  
>
> (The article is about a guy who hates the fact that he allowed it to become 
> known that he can debug these monstrosities, with the result that people keep 
> bringing them to him.)
>
> What we want is a SQLite-based program along the lines of Access or 
> FileMaker, preferably with some kind of cloud capability.

i never did anything with FileMaker, but if Excel is a no-way-to-go,
than Access is most certainly too !

and, yes, i did not read the article... (sorry!)


Even a simple program (or (gui?)-interface on top of sqlite will not
stop those Excel-whizz-kids from crafting things in Excel.


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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Luuk]

On 6-10-2018 13:40, Winfried wrote:
> Hello,
>
> After reading this article…
>
> "In the workplace, spreadsheet experts face a constant barrage of help
> requests"
> https://www.wsj.com/articles/the-first-rule-of-microsoft-exceldont-tell-anyone-youre-good-at-it-1538754380
>
> … I'd like to check if there is a Windows GUI available as a valid
> alternative to Excel, either free or commercial, that would let people
> import XLS data and provide most of the Excel features.
>
> Thank you.
>
>
>

LibreOffice (http://libreoffice.org/)
or
OpenOffice (http://www.openoffice.org/)


LibreOffice provides all the functions of Excel, but the macro's is a
nightmare ;)
(or, in other words, writing a macro in Excel is easier than in LibreOffice)

i dont use OpenOffice, so i cannot give any info on it.

Finally, this question is prettu OFF-TOPIC for this list ?
maybe more suitable, pick a list from
https://www.libreoffice.org/get-help/mailing-lists/


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

Re: [sqlite] Full text serch - Matching all except chosen

[Luuk]

On 31-7-2018 10:52, Luuk wrote:
> From: http://www.sqlitetutorial.net/sqlite-full-text-search/
> For example, to get the documents that match the |learn| phrase but
> doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows:
>
> LECT *
+SE
> FROM posts
> WHERE posts MATCH 'learn NOT text';
>
>

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

Re: [sqlite] Full text serch - Matching all except chosen

[Luuk]

On 31-7-2018 10:52, Luuk wrote:
> On 31-7-2018 07:25, paul tracy wrote:
>> Forgive me if this is the wrong way to do this but I'm a newbie.
>> I am using version 3.24.0 with FTS5
>> Is there a way to perform a full text search that returns every row except 
>> records matching a specified query string?
>> The following does not work because of a syntax error as the syntax requires 
>> a query string before the NOT operator: … MATCH 'NOT blah';The following 
>> also does not work because the * operand cannot be used by itself: … MATCH 
>> '* NOT blah';In desperation I tried the following which returned data but a 
>> seemingly random set of data: MATCH NOT 'blah';
>> I wound up using something like this …
>> SELECT * FROM mytable WHERE id NOT IN (SELECT id from FullTextIndex WHERE 
>> FullTextIndex MATCH 'blah');
>> I think this is much slower on large databases than a full FTS-based query 
>> but maybe I'm wrong and this is as fast as it gets.
>> Any insight would be greatly appreciated.
> From: http://www.sqlitetutorial.net/sqlite-full-text-search/
>
> For example, to get the documents that match the |learn| phrase but
> doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows:
>
> LECT *
> FROM posts
> WHERE posts MATCH 'learn NOT text';
>
>
> But this should work too (untested):
> SELECT *
> FROM posts
> WHERE NOT posts MATCH 'text' AND posts MATCH 'learn';
>
According to this docs it should, (so no need to test :-):-))
https://www.sqlite.org/lang_expr.html
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full text serch - Matching all except chosen

[Luuk]

On 31-7-2018 07:25, paul tracy wrote:
> Forgive me if this is the wrong way to do this but I'm a newbie.
> I am using version 3.24.0 with FTS5
> Is there a way to perform a full text search that returns every row except 
> records matching a specified query string?
> The following does not work because of a syntax error as the syntax requires 
> a query string before the NOT operator: … MATCH 'NOT blah';The following also 
> does not work because the * operand cannot be used by itself: … MATCH '* NOT 
> blah';In desperation I tried the following which returned data but a 
> seemingly random set of data: MATCH NOT 'blah';
> I wound up using something like this …
> SELECT * FROM mytable WHERE id NOT IN (SELECT id from FullTextIndex WHERE 
> FullTextIndex MATCH 'blah');
> I think this is much slower on large databases than a full FTS-based query 
> but maybe I'm wrong and this is as fast as it gets.
> Any insight would be greatly appreciated.
From: http://www.sqlitetutorial.net/sqlite-full-text-search/

For example, to get the documents that match the |learn| phrase but
doesn’t match the |FTS5| phrase, you use the |NOT| operator as follows:

LECT *
FROM posts
WHERE posts MATCH 'learn NOT text';


But this should work too (untested):
SELECT *
FROM posts
WHERE NOT posts MATCH 'text' AND posts MATCH 'learn';

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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 15:45, Luuk wrote:
>
> In SQLite3 you are allowed to do this:
> SELECT a,b,c
> FROM t1
> GROUP BY a
>
> The values of 'b' and 'c' will be taken from a 'random' row...
>
> But if we rewrite this in SQL, i am getting something like this:
> SELECT
>a,
>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
> FROM t1 t
> GROUP BY a
>
> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
> i mean how can one be use that both values are from the same row?
> This is not a problem to SQLite, because in SQLite the values of b and c
> seems to be originating from the same row, but what about *SQL* (if that
> exists...?)
>

I think i'll do this:

select x.a, t1.b, t1.c
from (select t1.a, min(t1.rowid)
    from t1
    group by t1.a) x
inner join t1 on x.rowid=t1.rowid;

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

Re: [sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 15:39, Abroży Nieprzełoży wrote:
>> SELECT
>>a,
>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>> FROM t1 t
>> GROUP BY a
> Why not
> select a, min(b) as b, min(c) as c from t1 group by a;
> ?

It still does not quarantee that the valuse show for b and c are comming
from the same row...


>
> 2018-06-30 15:12 GMT+02:00, Luuk :
>> On 30-6-2018 14:55, Keith Medcalf wrote:
>>> Note that this is SQLite3 specific (and specific to Sybase of the era
>>> where Microsoft SQL Server was actually just a rebranded Sybase, and
>>> Microsoft re-writes of SQL Server up to about 2000).  Technically you
>>> cannot do a query of the form:
>>>
>>> SELECT c1, c2
>>>   FROM t1
>>> GROUP BY c2;
>>>
>>> because each column in the select list must be either an aggregate or
>>> listed in the GROUP BY clause.  SQLite3 allows c1 to be a bare column
>>> however and the value returned is taken from "some random row" of the
>>> group.  If there are multiple such columns, they all come from the same
>>> row in the group.  Although documented as a "random" row of the group, it
>>> is the first (or last) row visited in the group while solving the query
>>> (and this is of course subject to change but within the same version of
>>> SQLite3 will deterministically be the row either first or last in the
>>> visitation order -- the actual row may of course change depending on use
>>> of indexes, etc).  You can re-write this part so it will work in other SQL
>>> dialects that strictly enforce the requirement for c1 to be either an
>>> aggregate or listed in the group by clause.
>>>
>>> ---
>>> The fact that there's a Highway to Hell but only a Stairway to Heaven says
>>> a lot about anticipated traffic volume.
>>>
>> Ok ,my highway to hell start here (regargind the use of SQL)
>>
>> In SQLite3 you are allowed to do this:
>> SELECT a,b,c
>> FROM t1
>> GROUP BY a
>>
>> The values of 'b' and 'c' will be taken from a 'random' row...
>>
>> But if we rewrite this in SQL, i am getting something like this:
>> SELECT
>>a,
>>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>> FROM t1 t
>> GROUP BY a
>>
>> QUESTION: How does one get the proper relationship between 'B' and 'C'?,
>> i mean how can one be use that both values are from the same row?
>> This is not a problem to SQLite, because in SQLite the values of b and c
>> seems to be originating from the same row, but what about *SQL* (if that
>> exists...?)
>>
>> --
>> some test results:
>> sqlite> insert into t1 values (1,1,2);
>> sqlite> insert into t1 values (1,2,1);
>> sqlite> insert into t1 values (2,2,1);
>> sqlite> insert into t1 values (2,1,2);
>> sqlite> select a,b,c from t1 group by a;
>> 1|2|1
>> 2|1|2
>> sqlite> SELECT
>>...>a,
>>...>(SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
>>...>(SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
>>...> FROM t1 t
>>...> GROUP BY a;
>> 1|1|1
>> 2|1|1
>> sqlite>
>>
>> ___
>> 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

[sqlite] GROUP BY [Re: unique values from a subset of data based on two fields]

[Luuk]

On 30-6-2018 14:55, Keith Medcalf wrote:
> Note that this is SQLite3 specific (and specific to Sybase of the era where 
> Microsoft SQL Server was actually just a rebranded Sybase, and Microsoft 
> re-writes of SQL Server up to about 2000).  Technically you cannot do a query 
> of the form:
>
> SELECT c1, c2
>   FROM t1
> GROUP BY c2;
>
> because each column in the select list must be either an aggregate or listed 
> in the GROUP BY clause.  SQLite3 allows c1 to be a bare column however and 
> the value returned is taken from "some random row" of the group.  If there 
> are multiple such columns, they all come from the same row in the group.  
> Although documented as a "random" row of the group, it is the first (or last) 
> row visited in the group while solving the query (and this is of course 
> subject to change but within the same version of SQLite3 will 
> deterministically be the row either first or last in the visitation order -- 
> the actual row may of course change depending on use of indexes, etc).  You 
> can re-write this part so it will work in other SQL dialects that strictly 
> enforce the requirement for c1 to be either an aggregate or listed in the 
> group by clause.
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
> lot about anticipated traffic volume.
>
Ok ,my highway to hell start here (regargind the use of SQL)

In SQLite3 you are allowed to do this:
SELECT a,b,c
FROM t1
GROUP BY a

The values of 'b' and 'c' will be taken from a 'random' row...

But if we rewrite this in SQL, i am getting something like this:
SELECT
   a,
   (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
   (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
FROM t1 t
GROUP BY a

QUESTION: How does one get the proper relationship between 'B' and 'C'?,
i mean how can one be use that both values are from the same row?
This is not a problem to SQLite, because in SQLite the values of b and c
seems to be originating from the same row, but what about *SQL* (if that
exists...?)

-- 
some test results:
sqlite> insert into t1 values (1,1,2);
sqlite> insert into t1 values (1,2,1);
sqlite> insert into t1 values (2,2,1);
sqlite> insert into t1 values (2,1,2);
sqlite> select a,b,c from t1 group by a;
1|2|1
2|1|2
sqlite> SELECT
   ...>    a,
   ...>    (SELECT MIN(b) FROM T1 WHERE a=t.a) AS B,
   ...>    (SELECT MIN(c) FROM T1 WHERE a=t.a) AS C
   ...> FROM t1 t
   ...> GROUP BY a;
1|1|1
2|1|1
sqlite>

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

Re: [sqlite] Determine collation associated with sort

[Luuk]

On 30-6-2018 14:05, x wrote:
> Suppose I have a select such as
>
> ‘select * from TblsAndJoins where Condns order by OrdCol1,OrdCol2,...,OrdColn’
>
> I want to know the collation associated with the sort. I know a COLLATE 
> condition could be attached to the order by BUT suppose there’s none. How 
> would I determine the collation associated with the sort?
>
> I’m familiar with https://sqlite.org/c3ref/table_column_metadata.html
>
> int sqlite3_table_column_metadata(
>   sqlite3 *db,/* Connection handle */
>   const char *zDbName,/* Database name or NULLtion sequence */
>   const char *zTableName, /* Table name */
>   const char *zColumnName,/* Column name */
>   char const **pzDataType,/* OUTPUT: Declared data type */
>  char const **pzCollSeq, /* OUTPUT: Collation sequence name */
>   int *pNotNull,  /* OUTPUT: True if NOT NULL constraint exists */
>   int *pPrimaryKey,   /* OUTPUT: True if column part of PK */
>   int *pAutoinc   /* OUTPUT: True if column is auto-increment */
> );
>
> which would tell me if a particular column of the sort has a collation 
> sequence but what if it’s the index itself that has a collation attached? How 
> would I cover all possible angles?
>
>

I think it's explaned in the docs here:
https://sqlite.org/datatype3.html#collation


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

[sqlite] ROWID....

[Luuk]

In the docs (https://www.sqlite.org/autoinc.html) it says:
In SQLite, table rows normally have a 64-bit signed integer ROWID
 

Question:
Why it this a signed integer, and not an unsigned integer?

Simply by choice? of is there something more to say about this?


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

Re: [sqlite] Configuring csv extension to use '; ' as column delimiter instead of ',' ?

[Luuk]

On 1-5-2018 16:20, Simon Slavin wrote:
> On 1 May 2018, at 3:01pm, Olivier Mascia  wrote:
>
>> My question was more generic, even though it didn't look that way: the 
>> well-known and (maybe too) much-used software tool named Excel tend to 
>> encourage people to export "CSV" files which are actually "SCSV" files 
>> (semi-colon separated values). So the need to script some data happens 
>> regularly.
> What kind of insanity would lead someone to invent semi-colon delimited 
> format when CSV already existed ?  I bet it was a badly-thought-out solution 
> for European numeric formats which use a comma as a decimal point.  Tell 
> whoever uses that format to stop.
>
> Seems like the best way to solve this would be to write a converter for 
> Windows which converts SCSV to CSV.  Then it could be used by all Excel users 
> instead of just SQLite users.  Take a look at
>
> 
>
> 
>
> and hack up a solution.  I'd do it myself but I don't use Windows.
>
> Simon.
>

You 'forgot' this link:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_windows8-mso_2010/sep-support-for-older-excel/293076a3-6593-4ceb-8167-d29aa3418773

;)


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

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

[Luuk]

On 28-4-2018 12:05, J Decker wrote:
> https://productforums.google.com/forum/?utm_medium=email_source=footer#!msg/gmail/vaG8BpMPov0/JqRR4wk2CQAJ
>
> On Sat, Apr 28, 2018 at 3:05 AM, J Decker  wrote:
>
>> Yes, but then that spams the whole thread, and counts against sqlite, not
>> the individual message.
>>
>>

Ok, but the whole thread of the OP (who started this thread) is SPAM
anywayz (in relation to SQLite)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

[Luuk]

On 28-4-2018 11:26, J Decker wrote:
> On Sat, Apr 28, 2018 at 2:08 AM, Luuk <luu...@gmail.com> wrote:
>
>> On 28-4-2018 07:36, Luuk wrote:
>>> On 27-4-2018 20:57, Denis Burke wrote:
>>>> I know steps were taken to reduce it, but just confirming it is still
>> going
>>>> on today.
>>>> I do not see spam in this list.
>>>>
>>>> I think you are infected with a SPAM-generator ;(
>>>>
>>>> now i'm receiving SPAM too, but this has noting to do with this mailling
>>>> list,, an example below (note the' Form:'!):
>>>>
> Right.  (I want some too)
>
>

The easiest way to (remporarily?) solve this is to hit the 'Report spam'
button in de web-interface of gmail
(only works if you are reading via @gmail.com)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

[Luuk]

On 28-4-2018 07:36, Luuk wrote:
> On 27-4-2018 20:57, Denis Burke wrote:
>> I know steps were taken to reduce it, but just confirming it is still going
>> on today.
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> I do not see spam in this list.
>
> The main reason is, i only read messages that contain the text
> '[sqlite]' in the subject.
> It's one of the features of gmail to filter on such a thing.
>
I think you are infected with a SPAM-generator ;(

now i'm receiving SPAM too, but this has noting to do with this mailling
list,, an example below (note the' Form:'!):

-- 
 Forwarded Message 
Subject:Re: [sqlite] Yes - Getting SPAM from Using Mailing List
Date:   Sat, 28 Apr 2018 02:40:08 -0600
From:   Natosha Jacobs <awareessi...@safenmeet.xyz>
Reply-To:   awareessi...@safenmeet.xyz
To: luu...@gmail.com



Cool  Luuk i'm 100 sure you and me we can have a good time together.
Just trust me okey.. Am not an escort ok so plz dont think me like that.

I just need you to prove you are not minor and safe.

My friends also here with me while am writing you. If you can come now
you can ride us both. Just give me a call. Here’s Verify to See my
profile <http://trk.aqfunnel.com/5ae1e83db6920d3980756a7f>

Again I did attach pic of mine also. How is that?  Come and treat us in
real plz. To prove to you that im real, your Name is Luuk Caver it  ,i
hope now you believe that I’m real. 5+7 =12 am i right ??
 


On Fri, 27 Apr 2018 at 11:36 PM, Luuk <luu...@gmail.com
<mailto:luu...@gmail.com>> wrote:
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Yes - Getting SPAM from Using Mailing List

[Luuk]

On 27-4-2018 20:57, Denis Burke wrote:
> I know steps were taken to reduce it, but just confirming it is still going
> on today.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
I do not see spam in this list.

The main reason is, i only read messages that contain the text
'[sqlite]' in the subject.
It's one of the features of gmail to filter on such a thing.

-- 
Luuk

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

Re: [sqlite] How many AUTOINCREMENT tables are in your schema?

[Luuk]

0


C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default>sqlite3
global-messages-db.sqlite ".schema --indent" | findstr /i "autoincrement"

C:\Users\Luuk\AppData\Roaming\Thunderbird\Profiles\.default>


On 16-3-2018 16:37, Richard Hipp wrote:
> '.schema --indent'

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

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Luuk]

On 21-01-18 17:15, Brian Curley wrote:
> Well, I did oversimplify to just say 'pipe it through', but it's really
> more like a sed usage.
>
> You wouldn't see much difference if you'd pipe your delimited output
> through sed or awk either, unless you threw in some directives, or a
> script. It would require some planning on the part of the user, but there's
> a cookbook on the jq site that covers this.
>
>
> https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json
>
>
> There's other takes on this same recipe out there, on StackExchange, etc.
>
> As with any such localized solution, once you get it working, you can use
> it seamlessly as a function or an aliased call.
>
> Regards.
>
> Brian P Curley
>
>
>
>
> On Jan 21, 2018 10:15 AM, "Luuk" <luu...@gmail.com> wrote:
>
> On 21-01-18 16:05, Brian Curley wrote:
>> Is there even a need to embed it into sqlite itself? Since you're on the
>> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
>> through jq instead.
>>
>> Beautiful creature that jq...
>>
>> Regards.
>>
>> Brian P Curley
>>
>>
> luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
> 1
> 2
> 3
> luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
> 1
> 2
> 3
>
> Can you give an example please?
> ___
>

Thanks, will look at it, when i'm doing someting with JSON, and CSV
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite command line tool fails to dump data

[Luuk]

On 21-01-18 16:16, J Decker wrote:
> create table test (a,b)
> insert into test (a,b) values ( ?,? )
>   bind 'hello\0world.' 'te\0st'

luuk@opensuse:~/tmp> sqlite3
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table test(a,b);
sqlite> insert into test values ('hello\0world','te\0st');
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE test(a,b);
INSERT INTO "test" VALUES('hello\0world','te\0st');
COMMIT;
sqlite>


who told you that 'bind' works on the shell??
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Luuk]

On 21-01-18 16:05, Brian Curley wrote:
> Is there even a need to embed it into sqlite itself? Since you're on the
> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
> through jq instead.
>
> Beautiful creature that jq...
>
> Regards.
>
> Brian P Curley
>
>
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
1
2
3
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
1
2
3

Can you give an example please?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Crash when querying a unique index containing 12 columns using sub-selects

[Luuk]

On 11-01-18 18:01, Matthew Towler wrote:
> Hi
>
> I believe I have found a bug in sqlite, which my tests show was introduced 
> between versions 3.7.17 and 3.8.0 and remains present in all versions up to 
> and including 3.21.0 (I have also tested 3.8.11, 3.9.3, 3.12.2 all of which 
> hang.  I believe it closely related to, and probably just a bigger version 
> of, bug https://www.sqlite.org/src/tktview/9f2eb3abac9b83222f8a (problems 
> with indices with more than four columns) which was introduced in 3.8.0, and 
> fixed in a later version.  I am struggling slightly to produce a small 
> example for reproduction as to get it to fail requires both a complex 
> arrangement of tables and a significant volume of data (it all works 
> flawlessly when there are only a few rows) and the data I have is 
> proprietary.  I thought in the first instance I might be able to pass on what 
> I can easily and as it is so very similar to the previous issue someone 
> familiar with the code might understand the issue immediately - whilst I work 
> on getting more data I can pass on.  I hope this is acceptable, apologies for 
> the long message.
>
> I have an ABUNDANCE table with 12 rows, with a unique index across all the 
> rows.
>
> CREATE TABLE ABUNDANCE(AbundanceId INTEGER PRIMARY KEY NOT NULL,AbundanceId0 
> INTEGER,AbundanceId1 INTEGER,AbundanceId2 INTEGER,AbundanceId3 
> INTEGER,AbundanceId4 INTEGER,AbundanceId5 INTEGER,AbundanceId6 
> INTEGER,AbundanceId7 INTEGER,AbundanceId8 INTEGER,SetAbundanceId0 
> INTEGER,SetAbundanceId1 INTEGER,SetAbundanceId2 INTEGER);

a more readable version:

CREATE TABLE ABUNDANCE(
  AbundanceId INTEGER PRIMARY KEY NOT NULL,
  AbundanceId0 INTEGER,
  AbundanceId1 INTEGER,
  AbundanceId2 INTEGER,
  AbundanceId3 INTEGER,
  AbundanceId4 INTEGER,
  AbundanceId5 INTEGER,
  AbundanceId6 INTEGER,
  AbundanceId7 INTEGER,
  AbundanceId8 INTEGER,
  SetAbundanceId0 INTEGER,
  SetAbundanceId1 INTEGER,
  SetAbundanceId2 INTEGER);


> CREATE UNIQUE INDEX ABUNDANCE_UNIQUE_DATA_INDEX ON 
> ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8,SetAbundanceId0,SetAbundanceId1,SetAbundanceId2);
> CREATE INDEX ABUNDANCE ON 
> ABUNDANCE(AbundanceId0,AbundanceId1,AbundanceId2,AbundanceId3,AbundanceId4,AbundanceId5,AbundanceId6,AbundanceId7,AbundanceId8);
Error: there is already a table named ABUNDANCE


When trying (good try !) to report an error, make sure you copy/paste
this kind of stuff, of tripple check!

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 07-01-18 19:09, x wrote:
>> Because reading the whole record (all 3 fields) is more expensive than
>> just reading the index which has all the info you need to give a correct
>> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'
> Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field 
> (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was 
> included in the query?
>
>
sorry, i do not know
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 07-01-18 18:49, x wrote:
> Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it 
> clear that Tbl had numerous secondary indexes attached to it. The table has 
> 2.4 million records and 13 columns. There is a non-unique index on one of the 
> other columns (x integer) which is the one appearing in my explain query 
> plan. The following illustrates the problem. I’ve no idea why adding the 3rd 
> column is necessary to replicate it.
>

Because reading the whole record (all 3 fields) is more expensive than
just reading the index which has all the info you need to give a correct
answer on 'SELECT ID FROM TBL WHERE ID IS NULL;'

>
> sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER;
>
> sqlite> CREATE INDEX XXX ON TBL(X);
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL
>
> sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER;
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID;
>
> 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX
>
> sqlite>
>
>

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 06-01-18 19:00, x wrote:
>>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
>>> selectid|order|from|detail
>>> 0|0|0|SCAN TABLE Tbl
>> I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
>> returned in case of not ``NOT NULL'' field.
>
>
> Yeah, I would get that result as well if I had no secondary indexes on Tbl. 
> If either you or Luuk add a secondary index XXX to your versions of Tbl 
> you’ll get the same result I’m getting and maybe then we’ll be on the same 
> page regarding ordering.
>
>
>
I still do not knwo what you are doing...
multiple indexes define, and i stillsee 'SCAN TABLE ...'
can you post the output of your table definition?

sqlite> .schema tbl
CREATE TABLE tbl (id integer primary key not null);
CREATE INDEX idx2 on tbl(id);
CREATE INDEX idx3 on tbl(id DESC);
sqlite> explain query plan select id from tbl where id is null;
0|0|0|SCAN TABLE tbl
sqlite>

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

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

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 06-01-18 10:44, x wrote:
> Thanks for the replies.
>
> While I’m now on board as to what a NULL is I’m still a bit puzzled by the 
> query planner.
>
> Explain query plan select ID from Tbl where ID is null order by ID;
>
> returns
>
> SCAN TABLE Tbl USING COVERING INDEX ...
>
>
I do see different things:
sqlite> .version
SQLite 3.21.0 2017-10-24 18:55:49
1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de4alt2
sqlite> .schema tbl
CREATE TABLE tbl (id integer primary key not null);
sqlite> select count(*) from tbl;
count(*)
8388608
sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl
sqlite>

> It doesn’t even use the ID pk despite the fact it’s the requested order by??
That is explained by Cezary, if the explanation was unclear answer to
his post...
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'

[Luuk]

On 06-01-18 00:49, Simon Slavin wrote:
>
> To expand on this, in SQL NULL has a special meaning.  

This should read:
NULL has a special meaning.
and not:
in SQL NULL has a special meaning.

Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not
more special than in any other environment.

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

Re: [sqlite] pragma table_info return column delimiters?

[Luuk]

On 03-01-18 11:15, Bart Smissaert wrote:
> Is there a way with pragma table_info or otherwise (other than parsing the
> table create statement from SQLite_master) to get the column names
> including the column delimiters, eg double quotes or square brackets? So I
> would get eg: [column1] [column2] etc. if indeed the column names were
> delimited like that.
>
> RBS
>
What are column delimiters?

|sqlite> select * from test;
a, b, c
1, 2, drie
4, 5, zes
sqlite> select a,b,c from test;
a, b, c
1, 2, drie
4, 5, zes
sqlite> select [a],[b],[c] from test;
a, b, c
1, 2, drie
4, 5, zes
sqlite> select "a","b","c" from test;
a, b, c
1, 2, drie
4, 5, zes

Of course, you shoul not use single quotes ;)
sqlite> select 'a','b','c' from test;
'a', 'b', 'c'
a, b, c
a, b, c
sqlite>|
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] scanstats

[Luuk]

The info on .help is not complete

i.e. '.version' is missing

also '.scanstats' give info that one should user 'on', of 'off'. When
one of these options is used a warning is show that this option is not
available

suggestion: remove the '.scanstats' from the list or, give the warning
when doing '.scanstats' about it not being available.


luuk@opensuse:~/tmp> sqlite-autoconf-321/sqlite3
SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .scanstats
Usage: .scanstats on|off
sqlite> .scanstats on
Warning: .scanstats not available in this build.
sqlite>


BTW, it's hard to find out how to compile sQlite with this
*SQLITE_ENABLE_STMT_SCANSTATUS*

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

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[Luuk]

On 01-01-18 16:52, E.Pasma wrote:
> Clemens Ladisch wrote:
>
>> Luuk wrote:
>>> On 01-01-18 03:14, Shane Dev wrote:
>>>> select * from nodes where not exists (select * from edges where
>>>> child=nodes.id);
>>>
>>> Changing this to:
>>>
>>> select * from nodes where not exists (select 1 from edges where
>>> child=nodes.id);
>>>
>>> saved in my test about 10% of time
>>
>> Then I have to doubt your test; the generated code (see the EXPLAIN
>> output) is exactly the same.
>
>

the 3rd step of EXPLAIN changed from:

1|0|0|SCAN TABLE edges
to:
1|0|0|SCAN TABLE edges USING COVERING INDEX iedges


luuk@opensuse:~/tmp> sqlite3 nodes.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .timer on
sqlite> select * from nodes where not exists (select * from edges where
child=nodes.id) and id<10;
Run Time: real 254.400 user 254.007998 sys 0.119976
sqlite> select * from nodes where not exists (select * from edges where
child=nodes.id) and id<10;
Run Time: real 234.342 user 233.348752 sys 0.491637
sqlite> select * from nodes where not exists (select 1 from edges where
child=nodes.id) and id<10;
Run Time: real 219.904 user 218.968920 sys 0.651569
sqlite> select * from nodes where not exists (select 1 from edges where
child=nodes.id) and id<10;
Run Time: real 219.929 user 219.562780 sys 0.127948
sqlite> select * from nodes where not exists (select * from edges where
child=nodes.id) and id<10;
Run Time: real 236.423 user 234.648774 sys 1.622957




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

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[Luuk]

On 01-01-18 12:18, Luuk wrote:
> On 01-01-18 03:14, Shane Dev wrote:
>> Hello,
>>
>> I have a directed acyclic graph defined as follows -
>>
>> sqlite> .sch
>> CREATE TABLE nodes(id integer primary key, description text);
>> CREATE TABLE edges(parent not null references nodes, child not null
>> references nodes, primary key(parent, child));
>>
>> Now I want to find the "roots" of the graph - i.e nodes which are not
>> children of other nodes -
>>
>> select * from nodes where not exists (select * from edges where child=
>> nodes.id);
>>
>> This works but is very slow when there are a million nodes and edges.
> Changing this to:
>
> select * from nodes where not exists (select 1 from edges where child=
> nodes.id);
>
> saved in my test about 10% of time
>
>> Is there any way to speed it up?

Above is the same as what Clemens already mentioned ...

sqlite> explain query plan select * from nodes where not exists (select
* from edges where child=nodes.id);
0|0|0|SCAN TABLE nodes
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE edges
Run Time: real 0.009 user 0.00 sys 0.00
sqlite>
sqlite> explain query plan select * from nodes where not exists (select
1 from edges where child=nodes.id);
0|0|0|SCAN TABLE nodes
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE edges USING COVERING INDEX iedges
Run Time: real 0.003 user 0.00 sys 0.00
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

[Luuk]

On 01-01-18 03:14, Shane Dev wrote:
> Hello,
>
> I have a directed acyclic graph defined as follows -
>
> sqlite> .sch
> CREATE TABLE nodes(id integer primary key, description text);
> CREATE TABLE edges(parent not null references nodes, child not null
> references nodes, primary key(parent, child));
>
> Now I want to find the "roots" of the graph - i.e nodes which are not
> children of other nodes -
>
> select * from nodes where not exists (select * from edges where child=
> nodes.id);
>
> This works but is very slow when there are a million nodes and edges.

Changing this to:

select * from nodes where not exists (select 1 from edges where child=
nodes.id);

saved in my test about 10% of time

> Is there any way to speed it up?

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

Re: [sqlite] Importing Text to Create a Table

[Luuk]

Can you give a (short) example of some lines,
and how you import them?

Creating a table with 1 field text 255 seems not the right way to do
this if you are only interested in emailaddresses.



On 22-12-17 01:22, Lawrence Murphy wrote:
> I am supporting a website which aims to protect a forest from development.
> The website produces an email for supporters to mail out. A copy of the
> email is sent to our Gmail address and we wish to capture the supporters
> return email address. Google provides an archive of our Gmail account which
> is 458Mbs in size and contains a lot of superfluous data.
>
> I have tried making a table with one column, text 255c in size and doing
> the import but it takes more than overnight and is still running. Is there
> a quicker way to import the data?
>
> Warm Regards,
> Lawrence
>
>

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

Re: [sqlite] random value get re-generated too often in SQLite

[Luuk]

On 08-12-17 23:34, Scott Doctor wrote:
>
> Is it possible that the first call to random is cached and the cached
> value is being returned in subsequent calls?
>
> -
> Scott Doctor
> sc...@scottdoctor.com
> -
>

The easiest way to get this behaviour is to store the random value in a
temp table, than you can use it as long as you like.

sqlite> create temp table rnd (i integer);
sqlite> select * from rnd;
sqlite> insert into rnd values (random());
sqlite> select * from rnd;
7337803792641969525
sqlite>

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

Re: [sqlite] How to dump all the tables into separate files?

[Luuk]

You are correct, but i'm not using sqlite enough to know (or remember) 
it also has a sqlite_master :-):-)


On 08-12-17 16:48, Donald Griggs wrote:

For windows, I think the following would work:

sqlite3 %DB% -batch "SELECT TBL_NAME FROM sqlite_master WHERE type=='table'
order by tbl_name;"   >%temp%\dump.tmp


The order by is not needed here (who cares about the order of exporting 
tables??? :-P:-D



for /f "usebackq" %%i in (`type %temp%\dump.tmp`) do sqlite3 %DB% -batch
".mode tabs" -batch "SELECT * FROM %%i " >%%i.tsv

delete %temp%\dump.tmp >nul

On Fri, Dec 8, 2017 at 10:19 AM, Luuk <luu...@gmail.com> wrote:


On 08-12-17 14:52, Luuk wrote:


On 08-12-17 14:13, Simon Slavin wrote:


On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote:

I'd like to dump all the tables to separate files, one table one file.

Each file should be in TSV format.

Is there a convenient way to do so in sqlite3?


which you might want to use with

.mode tabs

Simon.


On Windows you can use this batchfile:
@echo off
set DB=somedirectory\yourDBfile.sqlite
sqlite3 %DB% -batch ".tables" >c:\temp\dump.tmp
for /f "usebackq" %%i in (`type c:\temp\dump.tmp`) do sqlite3 %DB% -batch
".mode tabs" -batch "SELECT * FROM %%i" >%%i.tsv

Let /me know if you do not use Windows, this can be rewritten to work on
another OS too ;)




There's a bug in my script (Windows verion only, its solved in the
linux-version :-):-)!)

when doing this:
sqlite3 db.sqlite -batch ".tables"

My script 'assumes' the output gives 1 table per line, and now only the
first table on a line is exported to TSV

in Linux writing scripts is more easy, so this problem can be dealt with:

#!/bin/bash

db=somedirectory/yourDBfile.sqlite
sqlite3 $db -batch ".tables" | sed -e 's/  */\n/g' | grep -v '^$' | while
read line
do
 echo $line
 sqlite3 $db -batch ".mode tabs" -batch "SELECT * FROM $line"

$line.tsv

done



For MacOS you have to replace the sed expression:
's/  */\n/g'(BTW there are 2 spaces before the '*'!!
with:
's/  */\$'\n/g' (still 2 spaces before the '*' :-)



___
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] How to dump all the tables into separate files?

[Luuk]

On 08-12-17 14:52, Luuk wrote:

On 08-12-17 14:13, Simon Slavin wrote:


On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote:


I'd like to dump all the tables to separate files, one table one file.
Each file should be in TSV format.

Is there a convenient way to do so in sqlite3?


which you might want to use with

.mode tabs

Simon.

On Windows you can use this batchfile:
@echo off
set DB=somedirectory\yourDBfile.sqlite
sqlite3 %DB% -batch ".tables" >c:\temp\dump.tmp
for /f "usebackq" %%i in (`type c:\temp\dump.tmp`) do sqlite3 %DB% 
-batch ".mode tabs" -batch "SELECT * FROM %%i" >%%i.tsv


Let /me know if you do not use Windows, this can be rewritten to work 
on another OS too ;)





There's a bug in my script (Windows verion only, its solved in the 
linux-version :-):-)!)


when doing this:
sqlite3 db.sqlite -batch ".tables"

My script 'assumes' the output gives 1 table per line, and now only the 
first table on a line is exported to TSV


in Linux writing scripts is more easy, so this problem can be dealt with:

#!/bin/bash

db=somedirectory/yourDBfile.sqlite
sqlite3 $db -batch ".tables" | sed -e 's/  */\n/g' | grep -v '^$' | 
while read line

do
    echo $line
    sqlite3 $db -batch ".mode tabs" -batch "SELECT * FROM $line" 
>$line.tsv

done



For MacOS you have to replace the sed expression:
's/  */\n/g'    (BTW there are 2 spaces before the '*'!!
with:
's/  */\$'\n/g' (still 2 spaces before the '*' :-)


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

Re: [sqlite] How to dump all the tables into separate files?

[Luuk]

On 08-12-17 14:13, Simon Slavin wrote:


On 8 Dec 2017, at 7:02am, Peng Yu  wrote:


I'd like to dump all the tables to separate files, one table one file.
Each file should be in TSV format.

Is there a convenient way to do so in sqlite3?

There’s no direct output from the SQLite library to produce TSV format.

You could write one in your own programming language.

Alternatively, you could script the SQLite Command-line tool to produce your 
.tsv files for you.  Take a look at section 15 of



which you might want to use with

.mode tabs

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


On Windows you can use this batchfile:
@echo off
set DB=somedirectory\yourDBfile.sqlite
sqlite3 %DB% -batch ".tables" >c:\temp\dump.tmp
for /f "usebackq" %%i in (`type c:\temp\dump.tmp`) do sqlite3 %DB% 
-batch ".mode tabs" -batch "SELECT * FROM %%i" >%%i.tsv


Let /me know if you do not use Windows, this can be rewritten to work on 
another OS too ;)



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

Re: [sqlite] Out of date online documents

[Luuk]

On 31-12-16 20:52, Richard Hipp wrote:

On 12/31/16, Paul Lambert  wrote:

While exploring the sqlite.org website for specific sqlite details I found
the document at the URL below which is labelled as "draft" and appears to
be from version 3.6.11.  The latest release is 3.15 and I currently have
version 3.13 installed.


https://www.sqlite.org/sessions/sqlite.html

That is indeed wildly obsolete documentation.  But there are no
hyperlinks to it from the home page.  How did you find it?



http://lmgtfy.com/?q=sqlite.org+command+line+shell
second link

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

Re: [sqlite] Why takes the second SELECT three times as much time?

[Luuk]

On 16-10-16 12:00, Cecil Westerhof wrote:

I have defined the following table:
 CREATE TABLE messages (
 dateTEXT NOT NULL DEFAULT CURRENT_DATE,
 timeTEXT NOT NULL DEFAULT CURRENT_TIME,
 typeTEXT NOT NULL,
 messageTEXT NOT NULL,

 PRIMARY KEY (date, time, type)
 );
 CREATE INDEX messages_date ON messages(date);
 CREATE INDEX messages_time ON messages(time);
 CREATE INDEX messages_type ON messages(type);

And the following view:
 CREATE VIEW temperatureStatistics AS
 SELECT   date AS Date
 ,MIN(message) AS Minimum
 ,MAX(message) AS Maximum
 ,AVG(message) AS Average
 ,COUNT(*) AS Count
 FROM messages
 WHEREtype = 'cpu-temp'
 GROUP BY date

I also created the folowing two queries:
 SELECT   date AS Date
 ,MIN(message) AS Minimum
 ,MAX(message) AS Maximum
 ,AVG(message) AS Average
 ,COUNT(*) AS Count
 FROM messages
 WHEREtype = 'cpu-temp'
  AND date BETWEEN (SELECT date('now', '-7 day'))
   AND (SELECT date('now', '-1 day'))
 GROUP BY date
 ORDER BY date DESC

and:
 SELECT   *
 FROM temperatureStatistics
 WHEREdate BETWEEN (SELECT date('now', '-7 day'))
   AND (SELECT date('now', '-1 day'))
 ORDER BY date DESC

But the first one is about three times as fast as the second one. What
am I doing wrong here?



Because your second query has to build the complete view before it can 
decide if a result is between the selected dates?

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

Re: [sqlite] Complicated join

[Luuk]

On 19-09-16 21:28, David Bicking wrote:

This is what I want:

SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr;
A  1  1
A  2  1
A  3  1
A  4  1
A  5  5  -- matches the (A,5) record in the M table.
A  6  1
A  7  1
A  8  1
A  9  1
B  1  NULL  -- no match found for CombinedKeyfield in M

Did this part of my post not make it to your reader?
Your output is almost what I want, except that the A.5 line is matching 1 and 5 
in the M table, and I only want it to match the 5.

Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
Thanks,David


sqlite> select E.CombinedKeyField, E.EvtNbr, max(M.EvtNbr)
   ...> from E left join M
   ...> on E.CombinedKeyField = M.CombinedKeyField
   ...> and (E.EvtNbr = M.EvtNbr
   ...> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   ...>WHERE M1.CombinedKeyField = 
E.CombinedKeyField

   ...>)
   ...> ) GROUP BY E.CombinedKeyField, E.EvtNbr ;
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite>


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

Re: [sqlite] Complicated join

[Luuk]

On 19-09-16 19:33, David Bicking wrote:

select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
from E left join M
on E.CombinedKeyField = M.CombinedKeyField
and (E.EvtNbr = M.EvtNbr
or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
WHERE M1.CombinedKeyField = E.CombinedKeyField
)
)


expected output is missing

now we need to guess at what you want the output to look like


But it doubles up on A,5, matching both on A,1 and A,5 in M

which line is correct? (or are they both correct?)

And it doesn't return B,1 with no match even though it is a left join.

In my output i do see 'B|1|' ..


sqlite> select E.CombinedKeyField, E.EvtNbr, M.EvtNbr
   ...> from E left join M
   ...> on E.CombinedKeyField = M.CombinedKeyField
   ...> and (E.EvtNbr = M.EvtNbr
   ...> or  M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
   ...>WHERE M1.CombinedKeyField = 
E.CombinedKeyField

   ...>)
   ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



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

Re: [sqlite] Complicated join

[Luuk]

On 19-09-16 19:33, David Bicking wrote:

INSERT INTO M (CombinedKeyField, EvtNbr)

VALUES ('A', 1),
('A', 5);

INSERT INTO E (CombineKeyField, EvtNbr)
VALUES ('A', 1)
, ('A', 2)
, ('A', 3)
, ('A', 4)
, ('A', 5)
, ('A', 6)
, ('A', 7)
, ('A', 8)
, ('A', 9)
, ('B', 1);




What is the name of this field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

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

Re: [sqlite] where is the old version of sqlite3，anyone could tell me？

[Luuk]

On 03-09-16 04:59, 陈伟 wrote:


发自我的 iPhone
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



define 'old' 

see: http://www.sqlite.org/src/timeline?b=2008-08-06+10:50:00=50=release
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] You may add sha256 checksum for files in the download page?

[Luuk]

On 02-04-16 19:13, Simon Slavin wrote:
> On 2 Apr 2016, at 11:35am, Pavel Volkov  wrote:
>
>> You may add sha256 checksum for files in the download page?
> SHA1 is so easy to crack now it might make sense to replace the SHA1 
> checksums with SHA256.  Does anyone have a good reason why SHA1 is still 
> needed ?
>
>

I do not think the SHA1 codes are there to 'crack' something...

[sqlite] Efficient relational SELECT

[Luuk]

On 04-02-16 19:32, Simon Slavin wrote:
> Simplified explanation.  Here's the setup:
>
> Two tables:
>
> rooms: each room has an id (think the standard SQLite rowid/id) and some 
> other columns
> bookings: includes room id, date, time, and some other stuff
>
> Date/time is encoded as a long COLLATEable string.  In other words sorting a 
> column by my date/time format will sort correctly into date order.
>
> Bookings may be made in reverse order.  In other words, someone may book a 
> room for next month, and after that someone may book the same room for 
> tomorrow.
>
> System contains about 50 rooms and 5000 bookings.  I can create whatever 
> indexes you want.
>
> Requirement:
>
> I want to show an HTML table which lists some rooms (using WHERE and ORDER 
> BY) and the latest time each of those rooms is booked for.  At the moment my 
> code uses one SELECT to get room details and one SELECT for each room to find 
> the most recent booking.
>
> The obvious thing is to combine the two using JOIN.  But the best JOIN I can 
> think of has to find the most recent booking using a clunky test for the 
> biggest date.  And I can't figure out a good phrasing for a sub-select.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Will this simple solution be too slow?:

SELECT id, room, date, time
FROM rooms
LEFT JOIN bookngs ON rooms.id=bookings.room
GROUP BY rooms.id, bookings.date
HAVING bookings.date=MAX(bookings.date) OR bookings.date IS NULL


  This email has been sent from a
virus-free computer not protected by Avast.
www.avast.com  

[sqlite] Can SQLite know from the statement string if it is row producing or not?

[Luuk]

On 08-01-16 21:27, Bart Smissaert wrote:
> I am interested to know from the statement string if the statement is
> invalid, row producing (could produce rows) or non row producing. I know
> sqlite3_prepare16_v2 can see if the statement is valid or not but how about
> the other 2?
> I can do this in code no problem, but it may not always be 100% reliable
> for example there
> could be a new pragma or maybe some new SQL keyword.
>
>


What is you exact definition of 'row_producing' and 'non_row_producing'?

Will this statement be 'row_producing' or 'no_row_producing' or ...?:
INSERT INTO table1 VALUES(1);
if table1 is defined as 'CREATE TABLE table1(i INTEGER PRIMARY KEY);'

how does this change if the table was defined as:
CREATE TABLE table1(i);

[sqlite] Apparent sqlite bug

[Luuk]

On 03-01-16 00:11, richard parkins wrote:
> An INSERT statement which fails with no explicit conflict clause appears to 
> throw away a pending SAVEPOINT.
> The following sequence demonstrates this behaviour
> SAVEPOINT demonstration;
> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
> "last name" "TEXT",
> "address",
> PRIMARY KEY ( "first name", "last name" )
> ) WITHOUT ROWID;
> INSERT INTO "PK" default values;
> ROLLBACK TO demonstration;
>
> As expected, the insert fails with Error: NOT NULL constraint, but the 
> ROLLBACK statement then also fails with Error: no such savepoint.
> If INSERT is replaced by INSERT OR ABORT (which is supposed to be the 
> default), the ROLLBACK statement does not fail.
> Environment:- sqlite 3.9.2 built from the amalgamation, running on UBUNTU 
> Linux 14.04.3 LTS. SQL statements run with sqliteman built from 
> https://github.com/rparkins999/sqliteman.git.
>
> I originally saw this problem running the INSERT inside sqliiteman. If you 
> run sqliteman, create the PK table as shown but omitting the WITHOUT ROWID 
> clause, insert a row of all nulls, and then try using sqliteman's Alter Table 
> function (accessible by right click on the table name) and try to change it 
> to a WITHOUT ROWID table, you'll see no such savepoint: ALTER_TABLE Unable to 
> fetch row. This is caused by sqliteman's internal cleanup algorithm trying to 
> roll back after failing to insert the data from the old table into its newly 
> created WITHOUT ROWID table, and not being able to do so because the 
> savepoint has vanished. Changing INSERT in my code to INSERT OR ABORT makes 
> the rollback work properly.
> Richard Parkins
>


a ROLLBACK was done because of the Error...

compare your code with this example:
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE IF NOT EXISTS "PK" ( "first name" "TEXT",
...> "last name" "TEXT",
...> "address",
...> PRIMARY KEY ( "first name", "last name" )
...> ) WITHOUT ROWID;
sqlite> SAVEPOINT demonstration;
sqlite> INSERT INTO "PK" values ("1","1","1");
sqlite> SELECT * FROM "PK";
1|1|1
sqlite> INSERT INTO "PK" default values;
Error: NOT NULL constraint failed: PK.first name
sqlite> SELECT * FROM "PK";
sqlite>
sqlite>