Re: [sqlite] Is there a way to select using cid?

[Simon Slavin]

On 22 Mar 2019, at 9:34pm, Peng Yu  wrote:

> There are cid's for each table. Is there a way to use "select" with
> cid's instead of their names?

In recent versions of SQLite you can use the result from 
pragma_table_info('test') as if it is a table.  So you can do

SELECT select * from pragma_table_info('test') WHERE cid=1;

Simon.

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

[sqlite] Is there a way to select using cid?

[Peng Yu]

There are cid's for each table. Is there a way to use "select" with
cid's instead of their names? Thanks.

$ sqlite3 dbfile 

Re: [sqlite] Row locking sqlite3

[Jens Alfke]

> On Mar 22, 2019, at 1:38 PM, Barry Smith  wrote:
> 
> You might be interested in the BEGIN CONCURRENT branch. It does page level 
> locking (not quite as granular as row level).
> 
> https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md 
> 

Oh, this is interesting. Is this a feature in development that will be released 
soon?

[And to be pedantic: according to those docs, this feature does not do page 
level locking; it’s optimistic not pessimistic concurrency.]

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

Re: [sqlite] Row locking sqlite3

[Thomas Kurz]

Ah, great, thank you very much for this information.

- Original Message - 
From: Barry Smith 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 21:38:10
Subject: [sqlite] Row locking sqlite3

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:

> This sounds interesting. I have some questions about:

>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.

> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?



> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3

> Hi,

> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.

> https://github.com/sqlumdash/sqlumdash/

> Are there other packages similar to sqlite3 but support row locking? Thanks.

> -- 
> Regards,
> Peng
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row locking sqlite3

[Barry Smith]

You might be interested in the BEGIN CONCURRENT branch. It does page level 
locking (not quite as granular as row level).

https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begin_concurrent.md

> On 22 Mar 2019, at 11:48 am, Thomas Kurz  wrote:
> 
> This sounds interesting. I have some questions about:
> 
>> Row lock information is shared with processes. If a process finished 
>> unexpectedly, unnecessary lock information might be stayed. In order to 
>> unlock them, please use sqlumdash_cleaner.exe which clears all record 
>> information. If there is a process which is in a transaction, 
>> sqlumdash_cleaner.exe should be called after end the transaction.
> 
> - Where is row lock information stored? In database file, in journal file, in 
> WAL file, or in memory?
> - Why should the cleaner be called after the end of a transaction?
> - I don't like the idea of calling an external exe in case of problems (and 
> more than that, after every transaction??). Couldn't you introduce a PRAGMA 
> for unlocking rows?
> 
> 
> 
> - Original Message - 
> From: Peng Yu 
> To: SQLite mailing list 
> Sent: Friday, March 22, 2019, 15:25:24
> Subject: [sqlite] Row locking sqlite3
> 
> Hi,
> 
> I see that sqlite3 still does not support row locking. This package
> tries to resolve this problem. But it does not have a standard build
> process for Linux.
> 
> https://github.com/sqlumdash/sqlumdash/
> 
> Are there other packages similar to sqlite3 but support row locking? Thanks.
> 
> -- 
> Regards,
> Peng
> ___
> 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] Row locking sqlite3

[Thomas Kurz]

This sounds interesting. I have some questions about:

> Row lock information is shared with processes. If a process finished 
> unexpectedly, unnecessary lock information might be stayed. In order to 
> unlock them, please use sqlumdash_cleaner.exe which clears all record 
> information. If there is a process which is in a transaction, 
> sqlumdash_cleaner.exe should be called after end the transaction.

- Where is row lock information stored? In database file, in journal file, in 
WAL file, or in memory?
- Why should the cleaner be called after the end of a transaction?
- I don't like the idea of calling an external exe in case of problems (and 
more than that, after every transaction??). Couldn't you introduce a PRAGMA for 
unlocking rows?



- Original Message - 
From: Peng Yu 
To: SQLite mailing list 
Sent: Friday, March 22, 2019, 15:25:24
Subject: [sqlite] Row locking sqlite3

Hi,

I see that sqlite3 still does not support row locking. This package
tries to resolve this problem. But it does not have a standard build
process for Linux.

https://github.com/sqlumdash/sqlumdash/

Are there other packages similar to sqlite3 but support row locking? Thanks.

-- 
Regards,
Peng
___
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] sqlite3_set_auxdata has no effect during an insert

[Jens Alfke]

I have C functions that are invoked during queries. Some of these functions 
take parameter strings that need parsing, sort of like JSONPaths. I use 
sqlite3_set_auxdata to memoize the parsed versions of these.

I just noticed that, when these functions are used in indexes — e.g. "CREATE 
INDEX foo ON table (foo(col, 'x.y.z'))" — then when the function 'foo' is 
invoked during a row insertion, the sqlite3_set_auxdata has no effect. Thus the 
function ends up parsing the 'x.y.z' string over and over, once per inserted 
row. This accounts for about 10% of the runtime of the function (it would be 
more if the path were more complex).

I'm aware that sqlite3_set_auxdata isn't guaranteed to cache the value, in fact 
the docs only go so far as to say it "may be preserved". However, it would be 
good for performance if it could be cached in this situation.

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

Re: [sqlite] shell session feature, #define awkwardness

[Robert M. Münch]

The docs at https://www.sqlite.org/compile.html state: „SQLITE_ENABLE_SESSION 
This option enables the session extension.“ which is not enough. You need to 
define SQLITE_ENABLE_PREUPDATE_HOOK too.

Robert M. Münch


On 8 Aug 2018, at 17:55, Larry Brasfield wrote:

> In shell.c, #defining the variable SQLITE_ENABLE_SESSION, (regardless of
> its value), enables the session feature.
>
> In sqlite3.c, #defining the variables SQLITE_ENABLE_SESSION=1 and
> SQLITE_ENABLE_PREUPDATE_HOOK=1 is necessary for the shell link to succeed.
>
> I suggest this could be simplified, or at least made more coherent.  If the
> update-related session behavior is optional in sqlite3, it probably should
> be in the shell also.
>
> --
> Larry Brasfield
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Backing up a SQLite database without the CLI

[Scott Perry]

`ATTACH` and iterating over all tables with `INSERT INTO SELECT` is how `VACUUM 
INTO` is implemented (see src/vacuum.c).

A less complicated way to back up the database might be to run `BEGIN` followed 
by `PRAGMA user_version` to acquire a read lock, after which you can safely 
copy the database (and wal, if appropriate) files directly.

Scott

On Mar 18, 2019, at 08:21, Jonathan Moules  wrote:
> 
> Hi Simon,
> 
> Thanks for your thoughts. Sorry, I should have been clearer: I have no way of 
> knowing if there are other open connections to the file - there may be as 
> it's a web-application. So I'll assume there are connections.
> 
> At this point I'm starting to think that the best option is to create a new 
> database with the requisite structure and copy the data across via an ATTACH 
> (there are only two tables and one will almost always be empty at this point).
> 
> Any other thoughts welcome though!
> Cheers,
> Jonathan
> 
> On 2019-03-18 13:37, Simon Slavin wrote:
>> On 18 Mar 2019, at 1:10pm, Jonathan Moules  
>> wrote:
>> 
>>> I was wondering if there was a good way of backing up an SQLite database if 
>>> you do *not* have access to the SQLite command line tool (which I know has 
>>> .backup - https://stackoverflow.com/a/25684912). [snip]
>>> I've considered simply running "PRAGMA wal_checkpointer;" and then copying 
>>> the file immediately after that, but that still seems prone to error.
>> Ideally, rather than force a WAL checkpoint, close the file, make the copy, 
>> then open it again.  This does not take significantly more time, and it 
>> ensures that you will copy the right thing no matter what caching and 
>> optimization your tools are trying to do.
>> 
>> In more general terms ...
>> 
>> Are you trying to backup while the database is being modified using SQLite 
>> function calls ?
>> 
>> If not, then the data is just a single file.  Assuming all programs using 
>> SQLite calls closed their connections properly, just copy the file using any 
>> file copy commands, or file copy primatives in your favourite programming 
>> language.  In PHP I'd use the built-in copy command:
>> 
>> 
>> 
>> There may be a journal file there and you can copy that too, but just the 
>> database file is enough for a backup for emergency purposes.
>> 
>> If you're trying to copy a file while connections still have it open then 
>> you should use SQLite API calls to do it.  The obvious ones are in the 
>> SQLite Online Backup API, which is the set of calls underlying the '.backup' 
>> command you mentioned.  You can find documentation for this here:
>> 
>> 
>> 
>> Unfortunately I don't think the PHP sqlite3 tools give access to this API.
>> 
>> Hope that helps.  Don't hesitate to get back to us if we can help.
>> ___
>> 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] library interfering with input function when running in python console

[Jean-Luc Hainaut]

On 20/03/2019 22:48, Anthony-William Thibault wrote:

Hello there!

Consider the following program

Import sqlite3
x = Input("Enter your name”)
print(“Hello, ” + x)

When you run the code directly with python (double click the .py file or choose 
open with python) it won’t work


Not surprising, this is not a Python program, for several reasons. Try 
this one instead:


import sqlite3
x = raw_input("Enter your name")
print("Hello, " + x)

J-L Hainaut

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

Re: [sqlite] cmd line question on import

[Jose Isaias Cabrera]

Windows 7, DOS command line.  It works, thanks.


From: sqlite-users on behalf of Simon Slavin
Sent: Friday, March 22, 2019 11:15 AM
To: SQLite mailing list
Subject: Re: [sqlite] cmd line question on import

On 22 Mar 2019, at 3:06pm, Jose Isaias Cabrera  wrote:

> I have a db in c:\temp\sqliteDB.sqlite3 which has the Project_List table. I 
> can use,
>
> sqlite3 "c:\temp\sqliteDB.sqlite3"
>
> and copy and paste the content of the file, and it works fine. But I am 
> trying to do this from the command line.  Something like,
>
> sqlite3 "c:\temp\sqliteDB.sqlite3" -i c:\temp\import.sql

The answer depends on the shell you're using.  You do not tell us what 
operating system or which shell you're using.  However, the answer is likely to 
be something like

sqlite3 "c:\temp\sqliteDB.sqlite3" < c:\temp\import.sql
___
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] BUG - LEFT JOIN + IFNULL

[Richard Hipp]

On 3/22/19, Marek Šrom  wrote:
>
>  I found following bug, using SQLite version 3.27.2 on windows...
>

The following test script seems to work fine for me.

create table test (a text);
insert into test values(null);
insert into test values('test');
create table test2 (b text);
.mode quote
.echo on
select a, test2.b, ifnull(test2.b,test.a) from test left join test2 on
(1=0) order by a DESC;
select a, test2.b, ifnull(test2.b,test.a) from test left join test2 on
(1=0) order by a;

Are you seeing the problem on the native SQLite command-line shell, or
in some third-party GUI tool.

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

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Richard Hipp]

On 3/22/19, R Smith  wrote:
> On 2019/03/22 5:30 PM, Richard Hipp wrote:
>>
>> More recent versions of SQLite do issue a warning on the sqlite3_log
>> interface if you use a double-quoted string literal.  But not many
>> people look at warnings, it turns out.
>
>
> I don't see these in my logs from the standard sqlite3_log interface
> using standard downloadable DLL from the sqlite.org site of version
> 3.25.1 - is this addition even more recent or do I need to compile
> something in or such?
>
> Or perhaps I'm not understanding what specific use case would trigger
> the log?

Looks like this was added to 3.27.0.

The following script demonstrates:


.log stdout
SELECT "This is a test";



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

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[R Smith]

On 2019/03/22 5:30 PM, Richard Hipp wrote:


More recent versions of SQLite do issue a warning on the sqlite3_log
interface if you use a double-quoted string literal.  But not many
people look at warnings, it turns out.



I don't see these in my logs from the standard sqlite3_log interface 
using standard downloadable DLL from the sqlite.org site of version 
3.25.1 - is this addition even more recent or do I need to compile 
something in or such?


Or perhaps I'm not understanding what specific use case would trigger 
the log?



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

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[R Smith]

On 2019/03/21 2:31 AM, Steve Horvath wrote:

I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.

Of the four queries below, the second query should have returned the same
result set as the first query. As a side note, I also tried creating the
tables with no primary keys and got the same results. I also tried using
the "ON" syntax and got the same results.



"WHITE" refers to the column named "WHITE" while 'WHITE' would refer to 
the string of data 'White'. SQLite is very forgiving (for historical 
reasons) of mistakes like that so that if you pose the object id "WHTE" 
to the query planner and it doesn't find a column named "WHTE" it 
assumes you probably meant a string like 'whte', which is why your 
second example works.


We've been lobbying for an SQLite "Strict" mode which would never allow 
such shenanigans, but it's probably a rather huge undertaking.




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

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Richard Hipp]

On 3/20/19, Steve Horvath  wrote:
>
> I hope you have enough details to troubleshoot!
>

String literals in SQL should be enclosed in single-quotes, not
double-quotes.  'WHITE', not "WHITE".  Double-quotes are used to
escape identifier names.  This is standard SQL.

When SQLite was first designed, year and years ago, MySQL 3.x was the
most widely used database engine, and so SQLite accepted double-quoted
string literals for compatibility, if it could not match the name
against a known identifier.  This was a design mistake on my part.  I
should have never tried to be MySQL 3.x compatible.  But I cannot go
back and change it now, as that would break SQLite compatibility!
Probably you would not have made this mistake if SQLite had issued
syntax errors for double-quoted string literals.

More recent versions of SQLite do issue a warning on the sqlite3_log
interface if you use a double-quoted string literal.  But not many
people look at warnings, it turns out.


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

Re: [sqlite] library interfering with input function when running in python console

[Mike King]

I think this is a problem with the Python SQLite wrapper you are using not
SQLite itself. Maybe a better place to ask would be on their mailing list.
Also, consider giving a bit more detail as to why it fails as this may help
them diagnose the issue.

Cheers

Mike

On Fri, 22 Mar 2019 at 15:10, Anthony-William Thibault <
awthiba...@hotmail.com> wrote:

> Hello there!
>
> Consider the following program
>
> Import sqlite3
> x = Input("Enter your name”)
> print(“Hello, ” + x)
>
> When you run the code directly with python (double click the .py file or
> choose open with python) it won’t work
>
> However, the following code will work
>
> x = Input("Enter your name”)
> print(“Hello, ” + x)
> Import sqlite3
>
> I tried running it using Anaconda and it worked, so it’s only when running
> it with the default Python compiler that I encountered this issue.
>
> I tried on 2 different computers and the problem was still occurring
>
> I’m using Python 3.7.2 and Windows 10
>
>
> ___
> 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] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Simon Slavin]

To quote a string in SQLite use apostrophes.  This refers to a five character 
string:

'black'

If you surround something with double quotes SQLite understands it as an entity 
name.  Entities are things like tables and columns.  So this is understood to 
be a column name:

"black"

.  Because SQLite was originally written to handle such strings it manages to 
interpret

insert into competitors values (1109,"SERHAN", "James");

correctly instead of giving an error message.  However this construction

WHERE competitors.last="WHITE"

probably doesn't mean what you think it means.

In future it might be a good idea to avoid column names which you might use a 
values.  That way you're less likely to encounter the problem.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Shawn Wagner]

Use single quotes, not double quotes, for strings. Double quotes are for
identifiers. And you have a column named white...

On Fri, Mar 22, 2019, 8:11 AM Steve Horvath 
wrote:

> Hi,
>
> I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.
>
> Of the four queries below, the second query should have returned the same
> result set as the first query. As a side note, I also tried creating the
> tables with no primary keys and got the same results. I also tried using
> the "ON" syntax and got the same results.
>
> sqlite3.exe bugtest
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> create table competitors (compid INTEGER PRIMARY KEY, last TEXT,
> first TEXT);
> sqlite> create table matches (category INTEGER, number INTEGER, blue
> INTEGER, white INTEGER, PRIMARY KEY(category,number));
> sqlite> insert into matches values(10032,2,1109,1197);
> sqlite> insert into competitors values (1109,"SERHAN", "James");
> sqlite> insert into competitors values (1197,"WHITE","Justin");
> sqlite> .headers on
> sqlite> select * from matches;
> category|number|blue|white
> 10032|2|1109|1197
> sqlite> select * from competitors;
> compid|last|first
> 1109|SERHAN|James
> 1197|WHITE|Justin
>
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="Justin";
> compid|last|first
> 1197|WHITE|Justin
>
> sqlite> select competitors.* from matches INNER JOIN competitors
> WHERE matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="WHITE";
>
> sqlite> select competitors.* from matches INNER JOIN competitors
> WHERE matches.blue = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="James";
> compid|last|first
> 1109|SERHAN|James
>
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.blue = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="SERHAN";
> compid|last|first
> 1109|SERHAN|James
>
> sqlite>.quit
>
> Out of curiosity, I changed the last name of "WHITE" to "WHTE" and queries
> now work!?!?
>
> sqlite3.exe bugtest
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> update competitors set last="WHTE" where compid=1197;
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.last="WHTE";
> 1197|WHTE|Justin
> sqlite> select competitors.* from matches INNER JOIN competitors WHERE
> matches.white = competitors.compid and matches.category=10032 and
> matches.number=2 and competitors.first="Justin";
> 1197|WHTE|Justin
> sqlite> select competitors.* from matches INNER JOIN competitors ON
> matches.blue = competitors.compid where matches.category=10032 and
> matches.number=2 and competitors.first="James";
> 1109|SERHAN|James
> sqlite> select competitors.* from matches INNER JOIN competitors ON
> matches.blue = competitors.compid where matches.category=10032 and
> matches.number=2 and competitors.last="SERHAN";
> 1109|SERHAN|James
> sqlite> .quit
>
> I hope you have enough details to troubleshoot!
>
> Thanks in advance,
> STeve Horvath
> ___
> 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] cmd line question on import

[Simon Slavin]

On 22 Mar 2019, at 3:06pm, Jose Isaias Cabrera  wrote:

> I have a db in c:\temp\sqliteDB.sqlite3 which has the Project_List table. I 
> can use,
> 
> sqlite3 "c:\temp\sqliteDB.sqlite3"
> 
> and copy and paste the content of the file, and it works fine. But I am 
> trying to do this from the command line.  Something like,
> 
> sqlite3 "c:\temp\sqliteDB.sqlite3" -i c:\temp\import.sql

The answer depends on the shell you're using.  You do not tell us what 
operating system or which shell you're using.  However, the answer is likely to 
be something like

sqlite3 "c:\temp\sqliteDB.sqlite3" < c:\temp\import.sql
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG - LEFT JOIN + IFNULL

[Marek Šrom]

 
 
 
 Hello,
 
 I found following bug, using SQLite version 3.27.2 on windows...
 
 
 
 
 Using field from left joined table in ifnull when first row contains null 
 value causes in following rows value to be empty string instead of correct 
 value...
 
 
 
 
 
 
 
 To reproduce:
 
 create table test (a text);
 
 insert into test values(null);
 
 insert into test values('test');
 
 create table test2 (b text);
 
 
 
 
 select a, test2.b, ifnull(test2.b,test.a) from test left join test2 on (1=
 0) order by a desc;
 
 
 
 
 returns:
 
 "test",null,"test"
 
 null,null,null
 
 
 
 
 It is OK...
 
 
 
 
 
 select a, test2.b, ifnull(test2.b,test.a) from test left join test2 on (1=
 0) order by a;
 
 
 
 
 returns: 
 
 null,null,null
 
 "test",null,""
 
 
 
 
 It is wrong, should return:
 
 null,null,null
 
 "test",null,"test"
 
 
 
 
 
 
 
 It also happens when ifnull(test2.b,test.a) is replaced by
 
 COALESCE(test2.b,test.a)
 
 or
 
 CASE WHEN test2.b IS NULL THEN test.a ELSE test2.b END
 
 
 
 
 Thank you
 
 
 
 
 Marek Srom
 
 
 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

[Steve Horvath]

Hi,

I found an issue with SQLite 3.27.2 and is also existent in 3.20.1.

Of the four queries below, the second query should have returned the same
result set as the first query. As a side note, I also tried creating the
tables with no primary keys and got the same results. I also tried using
the "ON" syntax and got the same results.

sqlite3.exe bugtest
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> create table competitors (compid INTEGER PRIMARY KEY, last TEXT,
first TEXT);
sqlite> create table matches (category INTEGER, number INTEGER, blue
INTEGER, white INTEGER, PRIMARY KEY(category,number));
sqlite> insert into matches values(10032,2,1109,1197);
sqlite> insert into competitors values (1109,"SERHAN", "James");
sqlite> insert into competitors values (1197,"WHITE","Justin");
sqlite> .headers on
sqlite> select * from matches;
category|number|blue|white
10032|2|1109|1197
sqlite> select * from competitors;
compid|last|first
1109|SERHAN|James
1197|WHITE|Justin

sqlite> select competitors.* from matches INNER JOIN competitors WHERE
matches.white = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.first="Justin";
compid|last|first
1197|WHITE|Justin

sqlite> select competitors.* from matches INNER JOIN competitors
WHERE matches.white = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.last="WHITE";

sqlite> select competitors.* from matches INNER JOIN competitors
WHERE matches.blue = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.first="James";
compid|last|first
1109|SERHAN|James

sqlite> select competitors.* from matches INNER JOIN competitors WHERE
matches.blue = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.last="SERHAN";
compid|last|first
1109|SERHAN|James

sqlite>.quit

Out of curiosity, I changed the last name of "WHITE" to "WHTE" and queries
now work!?!?

sqlite3.exe bugtest
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> update competitors set last="WHTE" where compid=1197;
sqlite> select competitors.* from matches INNER JOIN competitors WHERE
matches.white = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.last="WHTE";
1197|WHTE|Justin
sqlite> select competitors.* from matches INNER JOIN competitors WHERE
matches.white = competitors.compid and matches.category=10032 and
matches.number=2 and competitors.first="Justin";
1197|WHTE|Justin
sqlite> select competitors.* from matches INNER JOIN competitors ON
matches.blue = competitors.compid where matches.category=10032 and
matches.number=2 and competitors.first="James";
1109|SERHAN|James
sqlite> select competitors.* from matches INNER JOIN competitors ON
matches.blue = competitors.compid where matches.category=10032 and
matches.number=2 and competitors.last="SERHAN";
1109|SERHAN|James
sqlite> .quit

I hope you have enough details to troubleshoot!

Thanks in advance,
STeve Horvath
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] library interfering with input function when running in python console

[Anthony-William Thibault]

Hello there!

Consider the following program

Import sqlite3
x = Input("Enter your name”)
print(“Hello, ” + x)

When you run the code directly with python (double click the .py file or choose 
open with python) it won’t work

However, the following code will work

x = Input("Enter your name”)
print(“Hello, ” + x)
Import sqlite3

I tried running it using Anaconda and it worked, so it’s only when running it 
with the default Python compiler that I encountered this issue.

I tried on 2 different computers and the problem was still occurring

I’m using Python 3.7.2 and Windows 10


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

[sqlite] cmd line question on import

[Jose Isaias Cabrera]

Greetings!

I have this file, import.sql, which contains a structure like this,
BEGIN;
INSERT OR REPLACE INTO Project_List values (
...
);
...
...
INSERT OR REPLACE INTO Project_List values (
...
);
END;

There are 253 INSERT OR REPLACE statements.  What I am trying to do is to run 
it from the sqlite3 command line.  I have a db in c:\temp\sqliteDB.sqlite3 
which has the Project_List table.  I can use,

sqlite3 "c:\temp\sqliteDB.sqlite3"

and copy and paste the content of the file, and it works fine.  But I am trying 
to do this from the command line.  Something like,

sqlite3 "c:\temp\sqliteDB.sqlite3" -i c:\temp\import.sql

But I don't know what to use from these help lines:
11:03:23.28>sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -A ARGS...   run ".archive ARGS" and exit
   -append  append the database to the end of the file
   -ascii   set output mode to 'ascii'
   -bailstop after hitting an error
   -batch   force batch I/O
   -column  set output mode to 'column'
   -cmd COMMAND run "COMMAND" before reading stdin
   -csv set output mode to 'csv'
   -echoprint commands before execution
   -init FILENAME   read/process named file
   -[no]header  turn headers on or off
   -helpshow this message
   -htmlset output mode to HTML
   -interactive force interactive I/O
   -lineset output mode to 'line'
   -listset output mode to 'list'
   -lookaside SIZE Nuse N entries of SZ bytes for lookaside memory
   -memtracetrace all memory allocations and deallocations
   -mmap N  default mmap size set to N
   -newline SEP set output row separator. Default: '\n'
   -nullvalue TEXT  set text string for NULL values. Default ''
   -pagecache SIZE Nuse N slots of SZ bytes each for page cache memory
   -quote   set output mode to 'quote'
   -readonlyopen the database read-only
   -separator SEP   set output column separator. Default: '|'
   -stats   print memory stats before each finalize
   -version show SQLite version
   -vfs NAMEuse NAME as the default VFS
   -zip open the file as a ZIP Archive

Thanks for the help.

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

[sqlite] Row locking sqlite3

[Peng Yu]

Hi,

I see that sqlite3 still does not support row locking. This package
tries to resolve this problem. But it does not have a standard build
process for Linux.

https://github.com/sqlumdash/sqlumdash/

Are there other packages similar to sqlite3 but support row locking? Thanks.

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

Re: [sqlite] [EXTERNAL] Re: filling a key/value table

[Hick Gunter]

The declared type BLOB has the advantage of not messing around with the type of 
whatever is stored. Specifically, I have strings that look like 16 digit 
numbers, some with a leading zero, that would get clobbered by NUMERIC 
affinity; likewise, TEXT affinity would convert integers to text, wasting 
effort and space. I don't intend to actually store BLOBs in this field.

In my use case, the storage class of the actual values is constant for a given 
keyid. I could add an index on (value,keyid,location) and SQLite could quickly 
exclude any TEXT values from a key lookup given an INTEGER value just by 
looking at the manifest. INTEGER or REAL sort before TEXT.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Simon Slavin
Gesendet: Donnerstag, 21. März 2019 17:38
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] filling a key/value table

On 21 Mar 2019, at 4:04pm, Hick Gunter  wrote:

> CREATE TABLE logidx( keyid INTEGER, value BLOB, timestamp INTEGER, primary 
> key (keyid, value, location) ) WITHOUT ROWID;

Can't answer the question you posed and I'm happy to see Igor could.  But I 
wanted to speak against including a BLOB field in a compound PRIMARY KEY.

The PRIMARY KEY gets used a lot when SQLite does anything with a row, and 
having to match a whole BLOB and then compound it is going to take some time.  
I think it would be faster to let SQLite do its normal rowid thing and define 
the above PRIMARY KEY as a UNIQUE INDEX instead.

Of course, it may be faster or more convenient to you in some other way in 
which case I stand corrected.

Simon.
___
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