Re: [sqlite] New SQLite Forum established - this mailing list is deprecated

[Scott Robison]

They can subscribe to the forum too. :)

On Thu, Mar 12, 2020, 2:40 PM Simon Slavin  wrote:

> Well, that'll annoy the nabble people.  And I can live with that.
> ___
> 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] Can I search all tables and columns of SQLite database for a specific text string?

[Scott]

Tom and Slavin:
The dump of information sounds like a good idea. To Slavin's question, the user 
need to be able to repeated search, but as a developer, I would want and idea I 
can eventually implement repeatedly. I've done this successfully in the past, 
but it required 4-5 methods handling a combination of SQL statements and 
looping arrays.
Thanks for your help guys!
Scott ValleryEcclesiastes 4:9-10 

On Thursday, February 13, 2020, 09:35:54 AM EST, Simon Slavin 
 wrote:  
 
 On 13 Feb 2020, at 2:01pm, Scott  wrote:

> Can I search all tables and columns of SQLite database for a specific text 
> string?

No.  There's no way to do this, and there's no way to say "all tables" in SQL.  
In other words it's not easy to write such a thing.

I like Thomas Kurz's solution, to dump the database as SQL commands.  
Alternatively you could write a text exporter for all the columns you think 
might hold the string, then search that text.

If you want better solutions, it might help to tell us whether this is a 
one-time problem, something you (the developer) might want to do repeatedly, or 
something you want the user to be able to do repeatedly.
___
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] Can I search all tables and columns of SQLite database for a specific text string?

[Scott]

Can I search all tables and columns of SQLite database for a specific text 
string? I'm sure this question has been asked many times, but I'm having 
trouble finding a solid answer.
My problem: My clients SQLite database has 11 tables and multiple columns (some 
designated and others not) and they want to be able to search the entire 
database for a specific text or phrase.
What I have done: I've been searching a couple days and found the Full Text 
search on SQLite home based upon using a virtual table, but I don't think that 
will work. It appears that I may be able to search the sqlite_master but it 
seems it may only contain table and column information only minus the data.
What I'm working in: This is an Android app written in Java using the SQLite
What I hope to do: Find a simple query statement or combination of statements 
that may help to efficiently query for the string across tables and columns 
before I resort to multiple queries and methods for all 11 tables.
I'm looking for any experienced thoughts or suggestions anyone may have 
encountered resolving this kind of issue if available. I'm not expecting anyone 
to solve it for me -- just some guidance would be helpful.
Thanks,
Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Robison]

On Tue, Feb 4, 2020, 5:23 PM J. King  wrote

> Not everyone has access to carrays and intarrays, either, such as PHP
> users like myself.
>

But everyone has access to temp tables, and I think the idea of creating a
temp table, inserting 1000 items in a loop, and using that temp table in
the query, is a lot more clear than formatting a query as a string with
?1000 or more bound parameters. It is more idiomatic SQL, I would think.


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

Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

[Scott Perry]

On Feb 4, 2020, at 12:26 PM, Simon Slavin  wrote:
> 
> On 4 Feb 2020, at 7:13pm, Deon Brewis  wrote:
> 
>> WHERE x IN (?1,?2,?3,?4...,?1000 )
> 
> People really do this ?  Sheesh.

It's a pretty common pattern. Sure, queries that are likely to use very large 
IN groups ought to be using carray or intarray but not everyone knows they 
exist :)

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

Re: [sqlite] importing data to a table that has generated-columns

[Scott Robison]

On Sun, Jan 26, 2020 at 11:01 AM chiahui chen 
wrote:

> Hi,
>
> After creating a table (total 8 columns including 1 generated column) , I
> tried to import data from a csv file (each record has values for 7 columns
> that match the non-generated column names and data types, no headers ).
>
> The system issued " error:  table has 7 columns but 8 values were supplied.
> ' I wonder why.
>
> After experimenting different ways to import data to a table that has a
> generated column, so far I only found that  .read command with a .sql file
> that was output  as a result of  'mode insert'  is successful. Is there any
> other ways to import data to an existing generated-column table?
>

I would be inclined to import the csv as a temp table, then write an INSERT
INTO ... SELECT ... query to move the appropriate columns from the temp
table into the new table.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Coding standard

[Scott Robison]

On Thu, Dec 12, 2019, 11:04 PM Valentin Davydov 
wrote:

> On Thu, Dec 12, 2019 at 11:19:44AM -0500, Richard Hipp wrote:
> >
> > #define sqlite3Strlen30NN(C) (strlen(C)&0x3fff)
> >
> > The tool does not provide any details beyond "Use of strlen".
>
> So why not just #define sqlite3Strlen30NN(C) (strnlen(C,0x3fff))
>

strnlen is not in the standard library. It is available on many platforms
but is not standard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:31 PM Bart Smissaert 
wrote:

> I know I can do something like this:
>
> select replace(postcode, rtrim(postcode, replace(postcode, ' ', '')), '')
> from addresses
>
> which will get the part of the postcode starting with the space.
> Problem however is how to deal with the forward slash.
>

Do you have to do this in SQLite itself? Can you load an extension that
provides regex? How did you go from comment strings to postcodes?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] last occurrence of /*

[Scott Robison]

On Fri, Dec 6, 2019, 4:00 PM Bart Smissaert 
wrote:

> Have table with SQL statements and these statements may have comments,
> starting with /*
> How do I select the part of this statement starting with the last /*  ?
> So if the statement is:
> select field1 /*comment 1 */ from table1 /*comment 2*/
> I would like to get:
> /*comment 2*/


Is there more format to the comment than just plain text?

My senior project was a SQLite helper that would read a bunch of statements
from a sql script and create classes to automate the boilerplate code and
ensure they were prepareable. To accomplish this I needed some new syntax
that wouldn't interfere with SQLite, so I embedded it in comments, but they
had to conform to a simple structure so that I could find them via regex.

Perhaps, if it is only the last comment you care about, you could use a
regex. I'm on a phone right now and don't want to try to type in valid
regex strings, but that would be useful I could help with it when I'm near
a real keyboard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What is the C language standard to which sqlite conforms ?

[Scott Robison]

On Mon, Nov 18, 2019 at 3:44 PM Dennis Clarke  wrote:

>
> Same question as a few days ago.
>
> This may have been asked many times before but always seems to be a
> valid question.  On some machines with different compilers I get good
> results using C99 strict compliance. On other machines, such as those
> running Red Hat Enterprise Linux, I get terrible results.
>

Per https://www.sqlite.org/howtocompile.html it is "ANSI-C". C89 is the
ANSI-C standard, C90 is the first ISO-C standard. They are practically
identical.

Note that it is not strict ANSI-C, since ANSI-C doesn't provide for 64 bit
integers, and it does not provide for platform specific APIs or functions.
But as much as is possible, it is written to work with standard C as it has
existed for about 30 years now.

Different compilers have various degrees of compliance with their C89 / C90
/ C99 implementations. C99 is more strict about some things that C89 did
not care about, and the developers have made concessions on occasion to
conform to C99 when it did not compromise C89 compatibility. But as much as
possible it is desired to maintain C89 compatibility because there are
platforms that still are stuck with older compiler standards.

Note: I am not a member of the dev team, just recounting my understanding
of how things are.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem building DLL on Windows - no exported symbols

[Reynolds, Scott]

I'm trying to build SQLite (sqlite-autoconf-329.tar.gz) for Windows x64 
using Visual Studio 2017.  All items are building, but my test application is 
unable to resolve any SQLite symbols.  This would seem to be the result of 
sqlite3.def being essentially empty (a single EXPORTS line).  Here's the 
process I'm following:


  1.  Log into my Windows 10 machine
  2.  Start the "x64 Native Tools Command Prompt for VS 2017"
  3.  Change directory to ...\sqlite-autoconf-329 which contains the SQLite 
source code
  4.  Execute "nmake /f Makefile.msc dll PLATFORM=x64

Any ideas?

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

Re: [sqlite] DEF CON (wasL A license plate of NULL)

[Scott Perry]

On Aug 12, 2019, at 1:14 PM, Keith Medcalf  wrote:
> The only interesting thing is CVE-2015-7036, but I don't know if that was so 
> much an SQLite3 issue, as it was an issue in the use of the tokenizer by 
> Apple.  In either case, Apple fixed their bugs and SQLite3 was hardened 
> against some inappropriate (unintended by the application developer) uses of 
> the fts3_tokenizer() function.

Generally speaking, the issue was the availability of a built-in function 
accessible from the query language that took a function pointer as a parameter. 

Specifically, the vector was that an attacker with the ability to execute 
arbitrary SQL could cause execution to jump to the address of their choosing 
via the second parameter to fts3_tokenizer. Using established ROP techniques 
this could be used to gain control of the process.

The attack is even more interesting when combined with Check Point Research's 
recent publication (search for "SELECT code_execution FROM * USING SQLite;"), 
which explains how to gain control of a process from a database file by 
replacing all of its tables with views containing malicious queries.

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

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

[Scott Robison]

On Thu, Jun 13, 2019, 8:51 AM R Smith  wrote:

> On 2019/06/13 4:44 PM, Doug Currie wrote:
> >>
> >> Except by the rules of IEEE (as I understand them)
> >>
> >> -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero"
> >>
> > Except that 0.0 is also an approximation to zero, not "true zero."
> >
> > Consider that 1/-0.0 is -inf whereas 1/0.0 is +int
>
>
> I do not know if this is the result case in any of the programming
> languages, but in Mathematical terms that is just not true.
>
> 1/0.0 --> Undefined, doesn't exist, cannot be computed, Should error
> out. Anything returning +Inf or -Inf is plain wrong.
> I posit the same holds true for 1/-0.0
>

One of the features of the standard is "gradual underflow". I think of it
as a limit like concept. Hence why the storage of negative zero has a
place, why the computation of x/+-0.0 yields +-inf. It is more than just an
artifact of the standard intended to solve certain computational problems.

All of that is irrelevant, though. The standard works the way the standard
works. SQLite has the option of either rendering -0.0 with or without a
sign. Should the system be changed?

I think that the possibility of breaking existing SQLite usage by changing
the long established text output makes it ill advised to make such a
change. Combined with the idea that sometimes SQLite will convert between
fp & integer zero on storage, thus losing the sign, illustrates that there
is too much history of how SQLite handles numbers already.

Such a change would not harm me in any way I can perceive. I have not
written any code that depends on -0.0, nor have I written code that depends
on the textual conversion thereof. I just think that SQLite's history of
maintaining backward compatibility by not gratuitously changing established
functionality that some people might depend on, even when it seems like an
improvement, should result in no change in the cast of -0.0 to text.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

[Scott Robison]

On Wed, Jun 12, 2019, 10:02 AM James K. Lowden 
wrote:

> On Wed, 12 Jun 2019 09:35:13 -0400
> Richard Hipp  wrote:
>
> > Question:  Should SQLite be enhanced to show -0.0 as "-0.0"?
>
> No.
>
> 1.  Prior art.  I can't think of a single programming language that
> displays -0.0 without jumping through hoops.
>
> 2.  Math.  Negative zero is not a mathematical concept.
>
> 3.  Utility.  There is none.  The user is only inconvenienced.
>
> Negative zero is an artifact of the IEEE 754, not a feature.  By
> contrast, IEEE adopted 2s-complement signed integers specificially to
> avoid negative zero.  That's evidently harder to do with floating
> point. ;-)
>
> I see no advantage to exposing a technical mechanical artifact to the
> user.
>

Further, how much code might exist that depends on the current
functionality? Code that converts a real to a string then compares the
string value against 0.0.

If the decision is made to differentiate 0.0 with a prefixed sign, will all
positive values be likewise prefixed with + explicitly when converted to
string?

I think leaving it with the status quo is the best course of action.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE encryption password

[Scott Doctor]

Here is a suggestion. You can select between decimal and 
hexadecimal output.



https://nousrandom.net/randominteger/index.html


-
Scott Doctor
sc...@scottdoctor.com
-

On 5/6/2019 13:20, Jens Alfke wrote:



On May 5, 2019, at 11:04 PM, Birajendu Sahu  wrote:

I would like to generate a device specific key upon installation of the
application. Same time I don't want to save the key in device memory too.

For AES256, just generate 32 bytes (256 bits) of securely random data and use 
that as the key. (“Securely random” means do not use `rand` or `random`, rather 
a random number generator provided by the OS or a crypto library. This varies 
by platform. On macOS see SecRandom.h.)

(Don’t try to do anything fancy to generate the key. There is nothing you can 
do that will give you a more-random, less-guessable key than your OS’s secure 
RNG.)

The key should be stored in OS-provided secure storage. On macOS that’s the 
Keychain. I’m sure Windows and Android have some equivalent of that.

—Jens
___
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] Help with sqlite3_value_text

[Scott Robison]

On Fri, Apr 12, 2019, 1:06 PM Keith Medcalf  wrote:

>
> Actually you would have to convert the strings to UCS-4.  UTF-16 is a
> variable-length encoding.  An actual "unicode character" is (at this
> present moment in time, though perhaps not tomorrow) 4 bytes (64-bits).
>

That is some impressive compression! :)

Regardless, even if you use UCS-4, you still have the issue of combining
characters. Unicode is complex as had been observed.
___
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:
>> 
>> <https://secure.php.net/manual/en/function.copy.php>
>> 
>> 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:
>> 
>> <https://www.sqlite.org/backup.html>
>> 
>> 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] Corruption of database when renaming a table

[Scott Perry]

The behaviour of ALTER TABLE changed in 3.25.0 and breaks compatibility with 
many existing applications. Your statements (corrected for use of single 
quotes, as Ryan mentioned) work for me when `PRAGMA legacy_alter_table=1;`.

More info on SQLite's website: https://www.sqlite.org/lang_altertable.html

Scott

> On Mar 15, 2019, at 06:29, tjerzyko  wrote:
> 
> I'm having corruption problem with a certain database file. You can download 
> it here:
> https://drive.google.com/file/d/1RCPoPgoLdc2VgF2uX2zPFrkheFi9z3b_/view?usp=sharing
> It was created with SQLite 3.8.7 or older version (I cannot say for sure now)
> I open it with sqlite3.exe and run the following script:
> 
> begin;
> alter table fs_params rename to fs_params_20291_27910;
> CREATE TABLE FS_PARAMS(FS_NO INTEGER,FS_OWNER_ID TEXT,FS_OWNER_NAME TEXT);
> drop table 'fs_params_20291_27910';
> alter table file_locks rename to file_locks_27446_24559;
> CREATE TABLE FILE_LOCKS(FILE_ID INTEGER, PLAYER_ID TEXT UNIQUE, USER_NAME 
> TEXT, FOREIGN KEY(FILE_ID) REFERENCES FILE(FILE_ID));
> drop table 'file_locks_27446_24559';
> CREATE INDEX L_FILE_ID ON FILE_LOCKS(FILE_ID);
> alter table cam_in_file rename to cam_in_file_22705_10035;
> end;
> 
> It throws the following error:
> Error: near line 9: error in trigger T_FILE_BDELETE: no such table: 
> main.file_locks_27446_24559
> 
> Probably my script is incorrect, but another thing worries me more. I open 
> the database again and:
> 
> e:\recorder\DB\LOOP-003>sqlite3 loop-003.dat
> SQLite version 3.27.2 2019-02-25 16:06:06
> Enter ".help" for usage hints.
> sqlite> .tables
> AUDIO_IN_FILE  FS_ATTRIBUTES  REC_RECORDING_MODE
> AUDIO_IN_FRAGMENT  FS_PARAMS  REC_WORKING_MODE
> CAM_IN_FILEINTEGRITY_TEST STORAGE_BLOCK
> CAM_IN_FRAGMENTLOOP_FRAGMENT  T_SCHEDULE
> FILE   REC_LATEST_CONNECTION
> FILE_LOCKS REC_LIST_TIMESTAMP
> sqlite> select * from CAM_IN_FILE;
> Error: no such table: CAM_IN_FILE
> sqlite>
> 
> The table exists, but not quite. The database seems to be corrupted.
> 
> When I tried the same procedure on database created with newer SQLite 
> version, there were no such problems.
> 
> Tomasz Jerzykowski
> 
> 
> ___
> 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] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Scott]

Andy, David, Tim!!!
Thanks for the help and advice... I think I will name mine "theKraken"... ;) 
LOL!
Scott ValleryEcclesiastes 4:9-10 

On Wednesday, February 13, 2019, 10:34:51 AM EST, Tim Streater 
 wrote:  
 
 On 13 Feb 2019, at 15:23, David Raymond  wrote:

> On a humor tangent, am I the only person who has ever named a savepoint
> "theHounds" for the sole purpose of being able to run the statement:
>
> release theHounds;

welease Bwian;



-- 
Cheers  --  Tim
___
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] Am I understanding how to use ROLLBACK - SAVEPOINT correctly?

[Scott]

Hi All!
 I've been reading about SAVEPOINT and ROLLBACK in the SQLite documentation. My 
project has an embedded SQLite DB with multiple tables (relational) that may or 
may not be read or updated because of choices the user makes.  Am I 
understanding correctly that I can use these commands to rollback to the 
previous savepoint if there is an error, or the user changes their mind? This 
is all through Java code, so I wasn't sure if the PreparedStatement "Execute()" 
or "ExecuteQuery" would still allow a rollback if I'm understanding correctly. 
For example, would I do this:
Connect to the DB
[ Pass the command to save: ]SAVEPOINT 'current'
[ User choices are processed: SELECT and UPDATE statements ]
[ if error or user changes their mind ]    ROLLBACK TRANSACTION TO SAVEPOINT 
'current'

[ otherwise... upon success ]    RELEASE SAVEPOINT 'current'

Close DB connection
Thanks for your time!
Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

Last reply... I figured out the cause. I had a Regex to validate but there was 
a second validation I forgot about with a DateTime object check causing the 
issue - so blank was defaulting. I removed it and no issue.
Thanks!
Scott ValleryEcclesiastes 4:9-10 

On Tuesday, February 5, 2019, 12:05:43 PM EST, Simon Slavin 
 wrote:  
 
 On 5 Feb 2019, at 4:58pm, Shawn Wagner  wrote:

> Reread the rules at the start of 3.1. TIME doesn't have any of the
> substrings used to indicate a particular other affinity, so it's treated as 
> NUMERIC.

You're right.  I somehow remembered the default as TEXT, not NUMERIC.  I was 
wrong.  Thanks for the correction.

Simon.
___
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] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

Hi Simon...
Thanks, I do have my  moments! LOL! I have a Regex on the GUI limiting the user 
to hh:mm:ss format or simply blank. Somehow that must be getting translated 
into a full date and time as default when blank.
Scott ValleryEcclesiastes 4:9-10 

On Tuesday, February 5, 2019, 11:55:47 AM EST, Simon Slavin 
 wrote:  
 
 On 5 Feb 2019, at 4:45pm, Scott  wrote:

> I think I understand now and I'm using SQLite Studio.  It appears SQLite 
> Studio is acting as a enhanced wrapper around SQLite3.

That makes a lot more sense.  Well done for figuring it out.

You have lots of options on how to store your dates.  You can store them as 
text, or as a number of days.  Which one you do depends on whether you want to 
make searching easier, or counting days easier.

Good luck with your task.  And don't hesitate to come back if you think we can 
help with something.

Simon.
___
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] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

Hi David!
I think I know what direction I need to go and this helps and makes sense. I 
may simply need to figure out what is sending the "Thu Jan 01 00:00:00 EST 
1970" and handle it from there.
Thanks!
Scott ValleryEcclesiastes 4:9-10 

On Tuesday, February 5, 2019, 11:11:56 AM EST, David Raymond 
 wrote:  
 
 https://www.sqlite.org/datatype3.html#determination_of_column_affinity

"Time(8)" ends up as  numeric affinity for that field. When it's given the 
string "Thu Jan 01 00:00:00 EST 1970" to store in the numeric field, that 
string can't be losslessly converted to a number, and thus it leaves it alone 
and stores it as-given as text.



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, February 05, 2019 10:44 AM
To: SQLite mailing list
Subject: Re: [sqlite] My TimeStamp field keeps populating though I don't write 
anything to it

On 5 Feb 2019, at 3:29pm, Scott  wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is 
> the generated description of the structure defined by SQLite3 based upon Data 
> Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done 
so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of 
section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in 
section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  
SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the 
paragraph just above that table.

Hope this helps.
___
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] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

I think I understand now and I'm using SQLite Studio.  It appears SQLite Studio 
is acting as a enhanced wrapper around SQLite3.  I did some reading and there 
is a SQLite Studio manual with with a list of 16 data types and it looks as if 
you can configure editors around those data types. I probably need to be apart 
of that forum for this question.
Thanks for the help! I appreciate the patience until my thick head could be 
penetrated :)
16 (17) Data 
Types:https://github.com/pawelsalawa/sqlitestudio/wiki/User_Manual#value-editor-dialog


Scott ValleryEcclesiastes 4:9-10 

On Tuesday, February 5, 2019, 10:44:40 AM EST, Simon Slavin 
 wrote:  
 
 On 5 Feb 2019, at 3:29pm, Scott  wrote:

> Thanks for the timely response. I provided below the details. "Time(8)" is 
> the generated description of the structure defined by SQLite3 based upon Data 
> Type options. So I apologize if I don't understand you comment.

SQLite didn't generate "Time(3)".  Some other piece of software may have done 
so, and told SQLite to use it as a datatype.

SQLite doesn't have a Time datatype.  See the list of datatypes at the top of 
section 2 of

<https://www.sqlite.org/datatype3.html>

SQLite is taking "Time(8)" and understanding it as "TEXT".  See the table in 
section 3.1.1 of the above web page.

SQLite doesn't recognise limits to the number of characters in text fields.  
SQLite is ignoring the "(8)" giving it a limit of 8 characters.  See the 
paragraph just above that table.

Hope this helps.
___
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] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

Hi Tim!
"Time(8) has no meaning to SQLite"

Thanks for the timely response. I provided below the details. "Time(8)" is the 
generated description of the structure defined by SQLite3 based upon Data Type 
options. So I apologize if I don't understand you comment.
Thanks!
Column Data Type SizeCommentID INTEGER 
PKSummary VARCHAR 120Comment BLOBPage VARCHAR 10TimeStamp TIME 8Hyperlink BLOB
Scott Vallery
Ecclesiastes 4:9-10 

On Tuesday, February 5, 2019, 8:23:53 AM EST, Tim Streater 
 wrote:  
 
 On 05 Feb 2019, at 13:08, Scott  wrote:

> I have a Comment table with the fields: CommentID, Summary, Comment, Pages,
> TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8
> characters only. Not every comment in this table requires a timestamp, so it
> should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST
> 1970" and it will not let me clear it. This is being populated with the
> following Java code, in the past, it would just remain null. I included a
> Regular Expression to check the GUI field data when it is used. But, I can't
> even manually clear it now and I haven't changed the setup of the database,
> the table or TimeStamp field.
> Any thoughts or suggestions?

1) Please show your whole table definition for the comment table.

2) Time(8) has no meaning to SQLite, your timestamp field will probably default 
to TEXT.
  See: https://www.sqlite.org/datatype3.html



-- 
Cheers  --  Tim
___
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] My TimeStamp field keeps populating though I don't write anything to it

[Scott]

I have a Comment table with the fields: CommentID, Summary, Comment, Pages, 
TimeStamp, Hyperlink. The TimeStamp field is setup as a Time(8), time with 8 
characters only. Not every comment in this table requires a timestamp, so it 
should remain null. However, it has started adding "Thu Jan 01 00:00:00 EST 
1970" and it will not let me clear it. This is being populated with the 
following Java code, in the past, it would just remain null. I included a 
Regular Expression to check the GUI field data when it is used. But, I can't 
even manually clear it now and I haven't changed the setup of the database, the 
table or TimeStamp field.
Any thoughts or suggestions?
String insertComment = "INSERT INTO Comment(Summary, Comment, Page, TimeStamp, 
Hyperlink) VALUES(?, ?, ?, ?, ?)";stmt = 
conn.prepareStatement(insertComment);stmt.setString(1,this.tbxSummary.getText());stmt.setString(2,this.tbxComment.getText());stmt.setString(3,this.tbxPages.getText());stmt.setString(4,this.tbxTimeStamp.getText());stmt.setString(5,
 this.tbxHyperlink.getText());stmt.execute()

Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to do I get an 'AND' condition to work in an SQLite query?

[Scott]

Figured it out! I had set the column Deleted to "CHAR" but all the fields 
without 'X' were null. If I replaced null with a valid character it worked.
Thanks for your time.
Scott ValleryEcclesiastes 4:9-10 

On Thursday, January 31, 2019, 12:46:34 PM EST, Scott 
 wrote:  
 
 I can return results successfully from the t.Topic and n.Deleted columns 
separately, but when I try to use AND I receive no results. I'm not sure what I 
may be doing wrong. This is my first exhaustive work with a database project, 
so I've had to learn some syntax along the way, but has me stumped.
SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c
LEFT JOIN Notes as n ON n.CommentID = c.CommentID

LEFT JOIN Source as s ON n.SourceID = s.SourceID

LEFT JOIN Topic as t ON n.TopicID = t.TopicID

WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X')



I've tried and even with INNER JOIN:
(WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 
'Manuscript Copies') AND (n.Deleted <> 'X')
WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X'
Thanks,
Scott ValleryEcclesiastes 4:9-10  
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to do I get an 'AND' condition to work in an SQLite query?

[Scott]

I can return results successfully from the t.Topic and n.Deleted columns 
separately, but when I try to use AND I receive no results. I'm not sure what I 
may be doing wrong. This is my first exhaustive work with a database project, 
so I've had to learn some syntax along the way, but has me stumped.
SELECT n.NoteID, s.SourceType, s.Title, c.Summary FROM Comment as c
LEFT JOIN Notes as n ON n.CommentID = c.CommentID

LEFT JOIN Source as s ON n.SourceID = s.SourceID

LEFT JOIN Topic as t ON n.TopicID = t.TopicID

WHERE (t.Topic = 'Manuscript Copies') AND (n.Deleted <> 'X')



I've tried and even with INNER JOIN:
(WHERE t.Topic = 'Manuscript Copies') AND n.Deleted <> 'X')WHERE (t.Topic = 
'Manuscript Copies') AND (n.Deleted <> 'X')
WHERE t.Topic = 'Manuscript Copies' AND n.Deleted <> 'X'
Thanks,
Scott ValleryEcclesiastes 4:9-10
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SEE Temp Files

[Scott Perry]

> On Jan 29, 2019, at 10:12, Jens Alfke  wrote:
> 
>> On Jan 28, 2019, at 3:35 PM, Richard Hipp  wrote:
>> 
>> On the other
>> hand, if you set SQLITE_TEMP_STORE=2 so that intermediate results are
>> held in memory, then intermediate results may be written to swap space
>> when the device gets under memory pressure. 
> 
> Mobile OSs don’t swap. (iOS for certain; I don’t have confirmation of this 
> for Android, but our local Android dev believes it’s so.)

While iOS 12 does not swap memory in the traditional sense, there are 
conditions under which it may write an idle application's memory to disk.

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

Re: [sqlite] -wal and -shm files left behind by libsqlite3.dylib

[Scott Perry]

This is expected behaviour. As Richard guessed, the system libsqlite3.dylib is 
built with SQLITE_ENABLE_PERSIST_WAL. For Darwin systems, the compromise of 
keeping the extra 4MiB (max) file around is generally worth the reduced I/O 
overhead that results from creating, resizing, and unlinking files.

If you need, you can still override this behaviour using the 
SQLITE_FCNTL_PERSIST_WAL opcode to the sqlite3_file_control() interface.

Scott


On Jan 28, 2019, at 10:32, Carsten Müncheberg  
wrote:
> 
> When loading and using /usr/lib/libsqlite3.dylib (3.19.3) which is shipped
> with MacOS 10.13.6 I noticed that the -wal and -shm files are not deleted
> when closing the last connection to a database. I tested this with the
> sqlite3 command line tool. It does not happen when I compile and link
> SQLite myself.
> 
> Is this expected behavior or some kind of bug?
> 
> Carsten

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

Re: [sqlite] CoreData - when database gets closed

[Scott Perry]

On Jan 22, 2019, at 9:02 AM, Simon Slavin  wrote:
> 
> On 22 Jan 2019, at 3:45pm, Maldonado-Salazar, Carlos 
>  wrote:
> 
>> Is there a way to know when CoreData closes sqlite files?. I’m using 
>> CoreData in an iOS app and I set file attributes for sqlite file to be 
>> NSFileProtectionCompleteUnlessOpen which throws away the key to encrypt the 
>> file when it’s closed, denying access to it.
> 
> I don't think I've seen one.  Technically, CoreData might hold its file open 
> after your application has quit, for example if it's doing lazy writing.  So 
> there'd be nothing to notify

A persistent store coordinator will close its handle when the store is removed. 
This happens naturally when the coordinator is deallocated, or you can do it 
manually using -[NSPersistentStoreCoordinator removePersistentStore:error:].

One common problem people run into is unexpected object lifetime extension when 
the coordinator gets added to a autorelease pool higher up in the call stack. 
In those conditions you can make the lifetime semantics of the coordinator more 
precise through explicit use of @autoreleasepool blocks.

> Using NSFileProtectionCompleteUnlessOpen or NSFileProtectionComplete should 
> do a good job of keeping your data secure.

I'd recommend using NSFileProtectionComplete over 
NSFileProtectionCompleteUnlessOpen. The former is simpler and self-securing 
(the filesystem will return an error if Core Data attempts to read or write 
data when the device is locked).

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

Re: [sqlite] Variable Declaration

[Scott Robison]

On Sat, Jan 19, 2019, 6:53 AM Simon Slavin 
> On 19 Jan 2019, at 4:49am, Stephen Chrzanowski 
wrote:
>
> > I know about the bindings.  I don't know about all languages supporting
it.
>
> Bindings are part of the SQLite API.  Any language which can make SQLite
calls should be supporting binding.
>
> Using binding means you can have the variables you want -- as many as you
want -- in whatever programming language you're using.  SQLite doesn't need
variables before your programming language has variables.  This is why
SQLite doesn't need variables.


Unless you want to write a script to run from the sqlite3 shell. Then you
don't have access to "variables" in the way the OP suggested.

Of course, you can use temp tables as though they are variables, though the
syntax isn't as elegant as the provided example.

1> declare @count int = 10;
2> select * from test where i<=@count

can be reformulated as

1> create temp table vars(name, value);
2> insert into vars values('count', 10);
3> select * from test where i <= (select value from vars where
name='count');

That's just one example, of course. Multiple variations on the theme are
possible if one needs variables that live exclusively in SQL without using
a host language and that also survive for more than a single statement.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locking problems

[Scott]

I'm a newbie on the block and this is my first  post, but have you considered 
'threading'? This sounds like something that you need to create a thread to do. 
Creating a thread should then allow you to set parameters to wait until one 
process completes and switch back and forth. I've done this in VB and C# but I 
think you stated below you using Linux and C, which I have no clue... but I 
found a video here:  https://www.youtube.com/watch?v=rvbGbmXJ0f0
Scott ValleryEcclesiastes 4:9-10 

On Saturday, January 19, 2019, 7:11:57 AM EST, andrew.g...@l3t.com 
 wrote:  
 
 I am having significant difficulty getting two process to not slam into each 
other when accessing the same database.  The behavior I want is for one process 
to wait while the database is busy being written to by the other, but instead I 
immediately get SQLITE_BUSY.  This is happening to me when using a local 
filesystem, so NFS is not the issue.  (Though I expect NFS to bite me soon 
enough.)

My trouble began on a Red Hat Enterprise Linux 6.4 system, though said computer 
exhibited other bizarre behaviors in the past (randomly unmounting filesystems 
and changing existing processes' current working directories to empty string), 
so I moved to Slackware64-current to make sure I could reproduce the problem in 
a modern environment.  Sure enough, I can.

sqlite3_busy_timeout(db, 5000) doesn't seem to help, not even when I add 
-DSQLITE_ENABLE_SETLK_TIMEOUT to my SQLite build command line.  sqlite3_step() 
doing an INSERT randomly fails to acquire the RESERVED lock.  I even tried 
writing my own retry-on-busy code (made several attempts), with no success.

I'm using SQLite 3.26.0 [bf8c1b2b7a] with GCC 8.2.0 on Linux 4.19.6, though I 
also had the same problems with GCC 4.4.7 on Linux 2.6.32-431.3.1.el6.x86_64.

Tracing through the SQLite amalgamation code with gdb, one thing I find odd is 
line 66334 doesn't seem to actually ever try calling 
btreeInvokeBusyHandler(pBt) despite rc being SQLITE_BUSY, since 
pbt->inTransaction isn't TRANS_NONE but rather TRANS_WRITE (I think).  This 
prevents it from waiting and/or retrying if busy, so the loop immediately 
bails, then SQLITE_BUSY rapidly percolates up to my application.

However, there's a chance my analysis may be invalid because I made my 
application pause with SIGSTOP on SQLITE_BUSY, at which point I attached gdb 
and forced it to call sqlite3_step() again so I could watch the execution path. 
 So maybe pbt->inTransaction was set sometime after unixFileLock() decided 
things went wrong.  But I highly doubt it.

Maybe a better debug strategy would have been to set a breakpoint in one 
process right when it acquires RESERVED, then start a second process in gdb and 
see what happens when it can't immediately get its own RESERVED lock.  (Or 
can't get SHARED because the first process had PENDING, etc.)  But it's 
midnight on Friday and I need to go home.

By the way, the SQLITE_LOCK_TRACE code seems to have a little bitrot.  I had to 
move the #define sqlite3DebugPrintf section earlier in the amalgamation, plus 
change the first %d on line 33121 to %ld because my pthread_t is a 64-bit value.

I created a test program that reproduces the issue, pasted at the bottom of 
this email.  (Are attachments allowed on this mailing list?  I sure hope my 
mailer doesn't eat my newlines.)  Sometimes it fails every time I try, 
sometimes I have to try it a dozen times.  To make it fail more frequently, 
uncomment more fork() calls.  (My original application on the Red Hat system 
failed every single time I ran two copies at once.)

No clue if this is of any value, but here's part of the output from 
SQLITE_LOCK_TRACE:

OPEN 11480672 test.db
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 4222915 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 4222915 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 4222915 0
FETCH 11480672 page 1 hash()
fcntl 140340145452864 3 SETLK UNLCK 0 0 4221434 0
fcntl 140340145452864 3 SETLK RDLCK 1073741824 1 0 0
fcntl 140340145452864 3 SETLK RDLCK 1073741826 510 0 0
fcntl 140340145452864 3 SETLK UNLCK 1073741824 1 0 0
FETCH 11480672 page 2 hash()
fcntl 140340145452864 3 SETLK WRLCK 1073741825 1 4273584 0
TRANSACTION 11480672
JOURNAL 11480672 page 2 needSync=1 hash()
fcntl 140340145452864 3 SETLK WRLCK 1073741824 1 2 0
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
fcntl 140340145452864 3 SETLK WRLCK 1073741826 510 2 -1
fcntl-failure-reason: RDLCK 1073741826 510 16020
(last two lines repeat)

Here's the test SQL being executed:

CREATE TABLE t(a, b, c);              -- Once only
BEGIN;
SELECT count(*) FROM t;              -- Used as :rows below
INSERT INTO t VALUES(:pid, :rows, 0); -- :pid is getpid()
INSERT INTO t VALUES(:pid, :rows, 1);
INSERT INTO t VALUES(:pid, :rows, 2);
COMMIT;

Here's test.c:

#include 
#include 
#include 
#include 
#include 
#include 
#include "sqlite3

[sqlite] Ideas or Guide on SQLite Search Engine for a relational database?

[Scott]

I apologize, I sent this from a different email than I registered accidentally.
Hi Everyone!
The Ask and what I’ve done:
I'm building a research database with an embedded SQLite DB using Intellij for 
development. I've created the data entry point for the users; however, I'm 
looking for anything anyone might want to share or suggest as a code, link, or 
design, whatever it may be, on developing a search engine for a relational 
database. I’ve been reading through tutorials on SQLiteTutorials.net to learn a 
little more about indexing and searching. I’ve come across the FTS5 (“Full Text 
Search”) tutorial, but I’m having difficulty finding anything that may assist 
with a multi-table search. I guess it would nice to have an example to follow. 
I wouldn’t think this would be the first-time for this, but I’m a little naïve 
too. 
 
I'm not asking for someone to solve the problem but just provide a direction 
that may save a lot of research time. I don’t know if my images will post or be 
available through this email, but I've provided some images below of the GUI 
and the SQLite DB which has 8 tables all linking back to the primary table 
called "Source". This is a new task for me and my development skills in Java.

The Goal:
This Research DB users want to be able to search the database either by Source, 
Topic, Question, Comments, Quotes, Authors, which most are a one to many with 
one a many to many relationship. A single source, for example a book entered in 
the database, could be linked to multiple topics, questions, Comments, Quotes, 
etc. So the users may want to search each of these separately on a particular 
subject, or possibly all the tables for a particular subject. Topic will help 
the mostly, but a topic may be addressed in a comment or quote, etc, that may 
not be associated directly with a particular topic. So there is a need to 
search a number of fields over 8 tables. My thoughts are that the search 
criteria should probably return into a table list, then when the user selects a 
particular row, this data would populate into corresponding fields for 
readability similar to the "Entry" tab (see below).

The only thing I suspect may make this easier than expected is that no matter 
whether the user searches by Topic, or Question, or Comment, etc, the search 
will always join all the tables and return all the fields for anything linked 
back to a source. However, the source will be associated to many of each of 
those.
 
Thanks,
 
Scott
 
  
 
   
   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Scott Doctor]

Try creating a fresh project, call it sqliteshell.exe

The amalgamation zip has a file called shell.c

Add the files shell.c, sqlite3.c, and sqlite3.h to the new project

compile.

See if you still get any errors. The shell.c program is the 
command line utility. See if you still get any errors.



-
Scott Doctor
sc...@scottdoctor.com
-

On 12/21/2018 13:02, Larry Brasfield wrote:

Zydeholic wrote:
➢ I compile and get one error: Severity    Code    Description    Project    
File    Line    Suppression State Error    LNK2001    unresolved external 
symbol _sqlite3_version    sqlite_try_3    
C:\Users\DSNoS\source\repos\sqlite_try_3\sqlite_try_3\sqlite3.obj    1

That symbol is declared, and a definition for the object so named is coded, in 
the sqlite3.c amalgamation without the possibility of omission by the 
preprocessor.  So I find it exceedingly strange that your link operation is 
complaining of an unresolvable reference to that symbol in sqlite3.obj.  For a 
C compilation, which you certainly should be using for that C source, the name 
should be undecorated, except for the leading underscore, just as it appears in 
the above-quoted error message.  This leads me to believe you are doing 
something too strange for anybody to guess with the information provided so far.

You may notice that this thread is misnamed for this latest difficulty, since 
the code does actually compile.  If I had to name it accurately, it would be 
called: [off topic] Need build instructions for my project which uses SQLite in 
a development environment differing from the one actually supported by the 
SQLite team.

Some questions to ask yourself as you attempt to sort this out:
1. Am I compiling the .c sources as C language?
2. Have I modified the sources everybody assumes are as released by the SQLite 
team?
3. What does insight does dumpbin.exe, (the VC command line tool for showing 
compiled image content), provide into my link errors?
4. How does my sqlite3.obj differ from the one I get following step 19 at 
https://www.sqlite.org/cli.html , and why?
___
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] Need setup code for VC++ 2017 that will ACTUALLY COMPILE

[Scott Doctor]

Click the link.

https://sqlite.org/download.html

Download the amalgamation zip file.

Unzip to your files directory. Should be two files. sqlite.c and 
sqlite.h


Add #include "sqlite.h" to your file.

Compile.


-
Scott Doctor
sc...@scottdoctor.com
-

On 12/20/2018 15:34, Igor Korot wrote:

Hi,
Unfortunately you didn't tell what did you try to compile, where did
you get the files you tried to compile from and
you didn't even supply the error message you received from the compilation.

Please follow-up and provide this and hopefully someone here will be
able to help.

Thank you.

On Thu, Dec 20, 2018 at 5:17 PM Donald Shepherd
 wrote:

You'll need to provide more information, speaking as someone who compiles
the amalgamation off the SQLite website with VC++ 2017 on Windows 10 with
no issues.  SQLite is C code, not C++ code, but VC++ detects that based off
the file extension and compiles it as such.

Trying to use whatever "CPPSqlite3.cpp" is and adding C++ headers is not
likely to get you anywhere unless you follow up with whoever created those
files for assistance.

Regards,
Donald Shepherd.

On Fri, 21 Dec 2018 at 09:41, zydeholic  wrote:


Hello folks,
I looked through the last few months of posts in the archive, and no
subject lines seemed to cover this.
I've tried a couple of CPPSqlite3.cpp and .h from github.com.I've tried
code from a couple of websites.
NOTHING seems to compile all the way through.

I'm using Visual Studio 2017 C++.  I'm on a WIndows 10 machine.  64bit,
but compiling 32 bit.
I tried Code::Blocks and got different, but equally incomplete compiles.

I've included  in the includes.  I've downloaded sqlite3.c and .h.
Nothing seems to work.  Is there ANYWHERE that offers a step-by-step
solution to this that WILL COMPILE all the way through.
I'm sure this has been covered before, but I did not see a way to search
the entire archives.  Any help appreciated.
Thanks.
___
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] Question about floating point

[Scott Robison]

On Mon, Dec 17, 2018 at 2:50 PM Thomas Kurz  wrote:

> Ok, as there seem to be some experts about floating-point numbers here,
> there is one aspect that I never understood:
>
> floats are stored as a fractional part, which is binary encoded, and an
> integer-type exponent. The first leads to the famous rounding errors as
> there is no exact representation of most fractions.
>
> Can someone explain to me why it has been defined this way? Having 1 bit
> sign, 11 bit exponent, and 52 bit mantissa, I would have stored the (in the
> meantime well known) number 211496.26 as 21149626E-2, i.e. I would have
> stored a 52 bit integer number and appropriate exponent. This way there
> should be no rounding errors and one would always have a guaranteed
> precision of ~15 significant digits.
>

To get the maximum precision possible from a binary floating point number,
the designers of the format took advantage of the fact that all numbers
other than zero would have a 1 bit set somewhere in their representation.
To that end, "normal" floating point numbers actually have a 53 bit
mantissa. "But that equals 65 bits! You can't cram 65 bits into a 64 bit
word." But you can if the most significant set bit of the mantissa is
implied just to the left of the explicitly given 52 bits of the mantissa.
The most significant digit of a decimal number can be any value from 1
through 9, so you can't use this same trick to extend the precision of a
decimal floating point number.

In addition to normal numbers, there are subnormal numbers, where the left
most digit is implicitly a 0 bit. The value zero happens to be a subnormal
number with all bits set to zero.

Even without the implicit bit, many / most schemes for encoding decimal
digits in binary lose some portion of the range that is possible with
binary representations, and the IEEE designers wanted the best of both
worlds, range and precision, so they gave up exact decimal representation
in favor of binary.

Your approach of coding is what the decimal type does in the .net platform,
among other examples, but the available range is smaller than IEEE binary
floating point numbers of the same size.

As far as it goes, you can still have rounding errors that propagate with a
decimal scheme such as you suggest. Simply add 1/3 + 1/3 + 1/3 in a decimal
representation.

333E-15 + 333E-15 + 333E-15 =
999E-15. But it should be 1000E-15 (or 1E0). It
doesn't matter how many bits of precision you add, you can never do this
type of math exactly with decimal floating point numbers. Any time the
decimal expansion extends beyond the bit length of the available precision,
rounding choices are going to have to be made at some point, and some
calculation will be inexact.

Note: I am spouting from memory, so my apologies if I've gotten any
terminology wrong (such as subnormal vs denormal, so similar other ideas).

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

Re: [sqlite] Mac: Users receive "database disk image is malformed" errors after restoring database from Time Machine backup

[Scott Perry]

On Dec 11, 2018, at 04:01, Daniel Alm  wrote:
> 
> Hi,
> 
> For the past half year we’ve been receiving reports from users who had 
> restored their SQLite-based databases from a Time Machine backup. Afterwards, 
> they would receive "database disk image is malformed” errors. The app also 
> backs up the user’s data “manually” to a ZIP file every week; those backups 
> seem to be working fine. We also haven’t received reports from other backup 
> tools causing issues. I have also suspected a bug in Time Machine, but it is 
> striking that the issues did seem to start occurring after an update to the 
> app (luckily, in fact, with the same update that also introduced the “manual” 
> backups).

Time Machine achieves eventual consistency by restarting when it detects that a 
file has changed since the backup was started. It does not have special 
provisions for SQLite database files.

Even if the scheduled backup never runs, the conditions under which a database 
would be captured in an utterly inconsistent state should be vanishingly rare. 
It would be most useful if you could share a representative database with 
Richard for analysis.

> Changes that we made to our setup in the update that coincided with the 
> errors occurring:
> - Upgraded SQLite from 3.21 to 3.24 (we have since reverted to 3.23.1 in 
> another update; no improvement).
> - Used memory mapping for read accesses via “PRAGMA mmap_size = 1073741824;” 
> (we have since reverted to “PRAGMA mmap_size = 0;” after reading 
> http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html
>  
> <http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html>;
>  no improvement).
> - Using a secondary database via [ATTACH 
> DATABASE](https://www.sqlite.org/lang_attach.html 
> <https://www.sqlite.org/lang_attach.html>) (although this also seems to occur 
> for users without such a database).
> 
> At this point, I am at a loss, especially given that SQLite should be fairly 
> robust against database corruption. While our app is running in the 
> background all the time, it is not very write-heavy (~ one transaction per 
> minute taking just a few milliseconds). Also, the app had been running fine 
> before the update for a long time without any reports of this issue. I might 
> be doing something wrong or have changed anything else, but I don’t know 
> what; if you have any ideas, let me know.
> 
> Any suggestions on what could be the culprit or what else I could try besides 
> downgrading all the way to SQLite 3.21 would be appreciated.

Out of curiosity, why aren't you using the SQLite that comes with the OS?

Scott

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

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Scott Perry]

On Nov 26, 2018, at 14:16, Simon Slavin  wrote:
> 
> On 26 Nov 2018, at 9:09pm, Scott Perry  wrote:
> 
>> For Bill's purposes—investigating a copied, non-corrupt database—it would 
>> probably be easiest to just convert from the Cocoa epoch to the Unix epoch 
>> by updating all the columns that currently store Cocoa timestamps. Something 
>> like:
>> 
>>   UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;
> 
> If you want to access your date in that form but leave the database usable by 
> Apple's libraries you can create a view which has a new column which modifies 
> the date in the above way.  As long as your VIEW's name does not clash with 
> one Apple wants to use, Apple's utilities should not stop working just 
> because you created a new view in a Core Data database.  I have previously 
> done this without problems but I may have just been lucky.
> 
> Come to think of that, Scott, you're in a better position to confirm that 
> than I am.


Local experimentation is one of the greatest learning tools, but I don't 
recommend making any customizations to Core Data stores that will ever be used 
on someone else's device as the framework is not especially sympathetic to 
meddling.

With that disclaimer out of the way, as a general rule Core Data namespaces all 
of its resources by prefixing them with Z. Migrations (which occur when 
updating the store to a new model version, or on first use after an operating 
system update) may destroy schema customizations.

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

Re: [sqlite] SQLite iOS timestamp type mapping settings must be set to float to get correct data

[Scott Perry]

On Nov 20, 2018, at 12:41 PM, Jens Alfke  wrote:

> On Nov 20, 2018, at 11:44 AM, Bill Hashman  
> wrote:
> 
>> The timestamp from iOS systems is not compliant with ISO 8601/Unix or other 
>> common timestamps.  It appears apple has their start date offset 31 years.
> 
> Yes, the ‘epoch’ in Apple’s own APIs (CoreFoundation, Foundation) is 
> 1/1/2001, expressed as a double. But of course the POSIX APIs on Apple 
> platforms use the regular Unix epoch of 1/1/1970 as integer.

That database looks like it was produced by Core Data, which does not use 
SQLite's timestamp-specific features since NSDate and friends are backed by 
doubles.

For Bill's purposes—investigating a copied, non-corrupt database—it would 
probably be easiest to just convert from the Cocoa epoch to the Unix epoch by 
updating all the columns that currently store Cocoa timestamps. Something like:

UPDATE ZTIMEENTRY SET ZDATE = ZDATE + 978307200;

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

Re: [sqlite] Regarding CoC

[Scott Perry]

I have to admit I was a lot more excited about the concept of SQLite having a 
Code of Conduct until I actually read it. Regardless of the fact that I seem to 
fail a great many of its provisions, it seems fairly deaf—if not 
antagonistic—to the issues of our times that have created demand for such 
documents.

Scott

On Oct 19, 2018, at 7:11 AM, Richard Hipp  wrote:
> 
> On 10/19/18, Mantas Gridinas  wrote:
>> 
>> I found code of conduct in documentation and I was wondering if it were
>> true. Checking the version history it appears to have been added on
>> 2018-02-22.
>> 
> 
> Yes.  Clients were encouraging me to have a code of conduct.  (Having
> a CoC seems to be a trendy thing nowadays.)  So I looked around and
> came up with what you found, submitted the idea to the whole staff,
> and everybody approved.
> 
> -- 
> 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

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

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Scott Doctor]

Why not just add the amalgmation to your source then do C 
function calss. I do not get why you would use a dll when you 
can just link in the amalgamtion into your program and have full 
access to the latest version.



-
Scott Doctor
sc...@scottdoctor.com
-

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

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

[Scott Robison]

On Tue, Oct 9, 2018, 6:34 AM Will Parsons  wrote:

> On Sunday,  7 Oct 2018  5:25 PM -0400, Keith Medcalf wrote:
> >
> > Many people do not "do" web forums.  I am one of them.  If there is not
> a mailing list then it does not exist.
>
> I completely agree.  I read and post to the SQLite mailing via Gmane,
> and I used to do the same for Fossil.  Now that the Fossil mailing
> list has gone away, I can't use Gmane any more, so I no longer follow
> Fossil.  Please, let's *not* have the same thing happen with SQLite3!


Isn't Gmane a web forum style interface to email lists? That doesn't seem
much different than what fossil is now doing, though maybe my lack of
experience using Gmane is preventing me from seeing a difference.

As described by at least one other, I use email to track the fossil forum
just as I always have, and can log in there to post when I want. I can
appreciate why people who refuse to use a web interface might object to it,
but not Gmane.

There are potential cons to the forum, but there are pros as well.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Fri, Sep 21, 2018 at 12:39 AM 邱朗  wrote:
>
> >I think it could be made to work, or at least, I have experience
> >making it work with CJK based on functionality exposed via ICU. I
> >don't know if the unicode tokenizer uses ICU or if the functionality
> >in ICU that I used is available in the unicode tables. Not
> >understanding any of the languages represented by CJK, I can't say
> >with any confidence how good my solution was, but it seemed to be good
> >enough for the use case of my management and customers in the impacted
> >regions.
>
> I am Chinese and I know a little bit of Korean, I can help to test your 
> product :D  All Jokes aside I also tried to build an ICU SQlite macOS version 
> but I failed. All the document I googled seem outdated. e.g. I used this (and 
> other solutions) but I just can not build a macOS version. Do you have any 
> experience for that ?
>
>
> ./configure CFLAGS="-I/usr/local/opt/icu4c/include -DSQLITE_ENABLE_ICU 
> `icu-config --cppflags`" LDFLAGS="-L/usr/local/opt/icu4c/lib `icu-config 
> --ldflags`"

I do not have macOS experience, and I don't have a lot of free time
either. Mainly I was trying to explain what I had done in the past and
what I used. Hopefully someone else will chime in with more details
about the Unicode61 tokenizer and whether it is standalone or depends
on ICU.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Fri, Sep 21, 2018 at 12:02 AM 邱朗  wrote:
>
> https://www.sqlite.org/fts5.html said " The unicode tokenizer classifies all 
> unicode characters as either "separator" or "token" characters. By default 
> all space and punctuation characters, as defined by Unicode 6.1, are 
> considered separators, and all other characters as token characters... "  I 
> really doubt unicode tokenizer requires white space, that is ascii tokenizer.

Forgive my imprecise use of language. I should have said separators
instead of whitespace. Regardless, CJK uses implicit separation
between words, and that description seems to indicate that the unicode
tokenizer expects explicit separators (be they whitespace or
punctuation or something else) between tokens.

> That was why I thought it might work for CJK.

I think it could be made to work, or at least, I have experience
making it work with CJK based on functionality exposed via ICU. I
don't know if the unicode tokenizer uses ICU or if the functionality
in ICU that I used is available in the unicode tables. Not
understanding any of the languages represented by CJK, I can't say
with any confidence how good my solution was, but it seemed to be good
enough for the use case of my management and customers in the impacted
regions.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite fts5 Unicode61 Tokenizer does not support CJK(Chinese Japanese Krean)?

[Scott Robison]

On Thu, Sep 20, 2018, 8:21 PM 邱朗  wrote:

> Hi,
> I had thought Unicode61 Tokenizer can support CJK -- Chinese Japanese
> Korean I verify my sqlite supports fts5
>
> {snipped}
>
> But to my surprise it can't find any CJK word at all. Why is that ?


Based on my experience with such things, I suspect that the tokenizer
requires whitespace between adjacent words, which is not the case with CJK.
Word breaks are implicit, not explicit.

Is the Unicode61 tokenizer based on ICU? I had to implement an algorithm
for software at work that used functionality from ICU to find CJK word
boundaries, so I believe it is possible, just not as straightforward as
whitespace delimited words.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

[Scott Robison]

On Thu, Sep 13, 2018 at 2:05 AM Urs Wagner  wrote:
>
> SQLite 3.24.0
> Entity Framework 6.2.0
>
> The result cannot be coerced into a string (compiler error).

Maybe I'm misreading this, but the subject line shows "foreign_keys =
[zero][en]" ... it appears the word ON ([oh][en]) is being written as
0N ([zero][en]) instead.

Maybe that's just an email typo, but thought I'd point it out.

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

Re: [sqlite] Lemon Parser vs bubble-generator.tcl

[Scott Robison]

On Fri, Aug 31, 2018 at 2:59 PM Warren Young  wrote:
>
> On Aug 31, 2018, at 1:55 PM, Scott Robison  wrote:
> >
> > Is one generated from the other, or are they maintained separately?
>
> They’re separate.  Here’s the Tcl source for the bubble diagrams:

As I suspected having looked at them in the source tree. Thanks for
the confirmation.

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

[sqlite] Lemon Parser vs bubble-generator.tcl

[Scott Robison]

SQLite language as implemented uses a Lemon based parser. The syntax
diagrams are created from the bubble-generator.tcl script. Is one
generated from the other, or are they maintained separately? Is one
(or another file that I don't know exists yet) the canonical
description of the "complete" SQLite syntax?

I ask because I'm working on a project idea for a senior project class
I'll be taking this fall and want to do something related to
identifying SQL syntax at C++ compile time. In doing a little research
/ planning, I started by creating my own syntax description from some
of the diagrams, then realized "I should just use portions of the
syntax diagram script directly", then started wondering about the
parser vs the diagram script.

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

Re: [sqlite] Best practices for forward conversion of database formats?

[Scott Robison]

On Thu, Aug 30, 2018 at 11:10 AM Roger Binns  wrote:
>
> On 30/08/18 09:51, Randall Smith wrote:
> > is how to convert existing data from one DB format to another, given some 
> > arbitrary set of changes in the database schema in the interim.
>
> I use SQLite's user pragma.  It starts at zero.
>
> https://sqlite.org/pragma.html#pragma_user_version
>
> My code ends up looking like this:
>
> if user_version==0:
> CREATE TABLE IF NOT EXISTS events(key, time, message);
> PRAGMA user_version=1;
>
> if user_version==1:
> CREATE INDEX IF NOT EXISTS [events:message] ON events(message);
> PRAGMA user_version=2;
>
> if user_version==2:
> ALTER TABLE events ADD COLUMN severity;
> PRAGMA user_version=3;
>
> This ensures that the currently running code will upgrade the schema as
> needed.  Ensure the commands are wrapped in a transaction so they either
> completely happen or not.
>
> I am helped by having low complexity schemas.  If yours are large you
> could probably generate something like the above.  Some ORM style
> engines also have schema and data upgrade functionality.

One place I worked for had a proprietary ORM style interface that was
intended to bridge the gap between an older legacy database and
SQLite. It's upgrade process involved an in memory data structure that
listed each upgrade step (add a column, move data, drop a column,
create a table, etc) then for each step, read each row of data, write
a custom insert statement, prepare, execute, finalize, discard, lather
rinse repeat. It was very slow. Very very slow. I don't think it is
possible to qualify it with sufficient verys to convey just how slow
it was.

I replaced it with a straight forward implementation that did a direct
SQL statement migration from the old schema to the new schema. Begin a
transaction, one statement per table, commit the transaction. We wound
up going from as much as 48 hours to migrate a 10 GB or so DB to about
5 to 15 minutes (it's been a while, I don't remember exactly).

If you have a simple schema and/or a small data set, the ORM migration
approach might work well for you. If you have a complex schema and
especially if you have a large data set, I'd encourage you to look
into alternatives to an ORM approach. I don't think all ORMs would
necessarily be as slow as the one I worked with was, but it was just
the wrong tool for the job in that particular case.

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

[sqlite] Shouldn't have to specify primary key explicitly

[Scott Robertson]

SQLite is supposed to autoincrement by default when a column is defined
as "INTEGER PRIMARY KEY" according to everything I've read. But I've
only gotten this to work if I let SQLite create its own PK column. If I
have an explicit PK column, I am expected to specify an ID myself. What
am I missing? I don't know why I'm getting this error. Thanks.


CREATE TABLE test1 (name TEXT, date DATE);

INSERT INTO test1 VALUES ('Barney', 1999

);


SELECT * FROM test1;

name date

-- --

Barney 1999


SELECT *, rowid FROM test1;

name date rowid

-- -- --

Barney 1999 1


CREATE TABLE test2 (

id INTEGER PRIMARY KEY,

book text,

page INTEGER

);


INSERT INTO test2 VALUES (

'Fletch',

245

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

1,

'Dragnet',

17

);


SELECT *, rowid FROM test2;

id book page id

-- -- -- --

1 Dragnet 17 1


INSERT INTO test2 VALUES (

'Lord of the Rings',

327

);

Error: table test2 has 3 columns but 2 values were supplied


INSERT INTO test2 VALUES (

9,

'Lord of the Rings',

327

);


SELECT *, rowid FROM test2;

id book page id

-- -- -- --

1 Dragnet 17 1

9 Lord of th 327 9


-- 

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

Re: [sqlite] Strange Corruption Issue

[Scott Robison]

On Mon, Jun 18, 2018 at 9:15 PM, Patrick Herbst  wrote:
> I'm using sqlite in an embedded application, running on SSD.
>
> journal_mode=persist
> so that it is more resilient to loss of power.
>
> I'm seeing corruption.  I'm using sqlite to log events on the system,
> and the corruption is well in the middle of a power session; not at
> the tail end of log when a power loss might occur.
>
> What i'm seeing is just a few pages corrupted with random bits being
> flipped.  looking in a hex editor I can see the corrupted data, and
> where I can tell what values it SHOULD be, I see that they're wrong,
> but only by a single bit flip in random bytes here and there.  for
> example a "A" is "a", or a "E" is "A".  These are all changes of a
> single bit.  there are far more examples... but in pretty much every
> case (even when RowID's are wrong) its just off by a bit.
>
> I'm using sqlite 3.7 (i know, old, but this this system is old).  Has
> anyone else seen random bit flips?  Any idea what could be causing it?

My first guess would be failing RAM chips.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange Corruption Issue

[Scott Doctor]

SSD's have a limited number of write cycles. You may have a 
failing SSD. Those are still, IMO, another 5-10 years before 
they solve the write lifetime reliabilty issue.


-
Scott Doctor
sc...@scottdoctor.com
-

On 6/18/2018 20:15, Patrick Herbst wrote:

I'm using sqlite in an embedded application, running on SSD.

journal_mode=persist
so that it is more resilient to loss of power.

I'm seeing corruption.  I'm using sqlite to log events on the system,
and the corruption is well in the middle of a power session; not at
the tail end of log when a power loss might occur.

What i'm seeing is just a few pages corrupted with random bits being
flipped.  looking in a hex editor I can see the corrupted data, and
where I can tell what values it SHOULD be, I see that they're wrong,
but only by a single bit flip in random bytes here and there.  for
example a "A" is "a", or a "E" is "A".  These are all changes of a
single bit.  there are far more examples... but in pretty much every
case (even when RowID's are wrong) its just off by a bit.

I'm using sqlite 3.7 (i know, old, but this this system is old).  Has
anyone else seen random bit flips?  Any idea what could be causing it?
___
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] Idea: defining table-valued functions directly in SQL

[Scott Robison]

On Sat, Jun 9, 2018 at 7:00 PM, Andy Goth  wrote:
> On 06/09/18 18:04, Simon Slavin wrote:
>>
>> CREATE TABLE tempLog (
>> datestamp TEXT COLLATE NOCASE PRIMARY KEY,
>> centTemp REAL,
>> fahrTemp AS (centTemp*9/5 + 32) )
>>
>> I'm happy with another syntax as long as it does the same thing.
>
>
> CREATE TABLE tempLog (
>datestamp TEXT COLLATE NOCASE PRIMARY KEY
>  , centTemp  REAL);
> CREATE VIEW tempLogView AS
>  SELECT *
>   , centTemp * 9 / 5 + 32 AS fahrTemp
>FROM tempLog;
>
>> Niggle 1: Can a computed column refer to a column defined after it ?
>
>
> With the view syntax I showed above, "computed" columns can only refer
> to columns that exist in the underlying tables.  I wish SELECT statement
> expressions could refer not only to input columns but also output
> columns that have been named using AS, but we don't have this feature.

SELECT *, (computation on Y) AS X FROM (
   SELECT *, (some computation) AS Y FROM sometable)

It is a little annoying having to nest them, but it works.

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

Re: [sqlite] sqlite.org website is now HTTPS-only

[Scott Robison]

On Fri, Jun 8, 2018, 12:19 AM Ron Yorston  wrote:

> Dennis Clarke wrote:
> >On 6/7/18 9:59 PM, Richard Hipp wrote:
> >> On 6/7/18, Scott Doctor  wrote:
> >>> Just out of curiosity, is the sqlite website using nginx or
> >>> apache as the server?
> >>
> >> None of the above.
> >>
> >> The web server is one that I wrote myself
> >
> >You're level of cool just jumped to UNIX silverback level :-)
>
> Meh.  *All* programmers of a certain age wrote their own web server.
>

But did they write it with their own text editor? :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Feature suggestion / requesst

[Scott Robison]

On Fri, Jun 8, 2018, 12:11 AM Hick Gunter  wrote:

> >
> >
> >I've encountered a feature that I think would be awesome:
> >https://www.postgresql.org/docs/9.3/static/dml-returning.html
> >
> >Example: INSERT INTO blah (this, that, another) VALUES (x, y, z)
> RETURNING id;
> >
>
> What does this do if the INSERT creates multiple rows? What about inserts
> generated from trigger programs?


Excellent questions that I don't know the answers to, but this does not
stop me from having an opinion.

I think if multiple rows are inserted, this should return multiple rows. It
is effectively a select of the inserted data.

Triggers are external to the insert, so I would expect them to not
contribute to the returning syntax result set.

Ultimately I expect, if accepted as an enhancement to SQLite, the team
would ask WWPGD, and model the implementation on that. I am not an expert
at PG so my ideas above could be way off base. I would think DML statements
using returning could be used anywhere a select could be used making for
more expressive SQL without necessarily needing to drop to the host
language.

Just a thought that I found interesting.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature suggestion / requesst

[Scott Robison]

On Thu, Jun 7, 2018, 9:25 PM Rowan Worth  wrote:

> On 3 June 2018 at 07:28, Scott Robison  wrote:
>
> > I've encountered a feature that I think would be awesome:
> > https://www.postgresql.org/docs/9.3/static/dml-returning.html
> >
> > Example: INSERT INTO blah (this, that, another) VALUES (x, y, z)
> RETURNING
> > id;
> >
>
>
> > my thoughts are just that this could greatly simplify a lot of sql
> > code that currently has to prepare and execute at least two statements
> > to accomplish what is conceptually an atomic task.
> >
>
> For most use cases you only need a single query:
>
> if (sqlite3_exec(db, "INSERT INTO blah (this, that, another) VALUES (x,
> y, z)") == SQLITE_OK) {
> long id = sqlite3_last_insert_rowid(db);
> ...
> }


Fair enough. My statement was intended as a SQL only solution that is
independent of the language bindings in use.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite.org website is now HTTPS-only

[Scott Doctor]

Just out of curiosity, is the sqlite website using nginx or 
apache as the server?



-
Scott Doctor
sc...@scottdoctor.com
-


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

Re: [sqlite] Filename encoding on Unix platforms

[Scott Robison]

On Mon, Jun 4, 2018 at 10:56 PM, Christopher Head  wrote:
> Hello,
> I have a question regarding text encoding of filenames on Unix
> platforms. I’ve read the two related mailing list threads I could find
> in the archive,
> 
> and
> .
> Both of those explain that, on Unix platforms, the filename string is
> passed unmodified by SQLite directly to the open() syscall.

{snipped stuff}

While I cannot comment on which of 1 through 3 is true, given that the
VFS layer is adaptable, it would be "simple" (if so desired) to adapt
SQLite to do whatever filename transformation was required for a
particular system. You likely have thought of this (and hopefully I
didn't miss it in your post), but there is no reason why SQLite
couldn't support both Glib and Qt (and even other) file open semantics
even if the core team opted not to support them within the Unix VFS
code itself.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Feature suggestion / requesst

[Scott Robison]

Background: I never finished a degree back in the dark ages, but
recently was provided an opportunity to earn a degree to go along with
my experience at a really affordable price. As a result, I'm taking
various classes to demonstrate my worthiness. :)

Last semester I had a class that used Oracle. I still have nightmares.

This semester I am taking a class that has exposed me to postgresql
for the first time. I can appreciate why the SQLite teams question is
WWPGD when considering new features.

I've encountered a feature that I think would be awesome:
https://www.postgresql.org/docs/9.3/static/dml-returning.html

Example: INSERT INTO blah (this, that, another) VALUES (x, y, z) RETURNING id;

It seems a very handy "single step" way (from the perspective of the
SQL programmer) to "select" some data from insert / update / delete
statements.

I concede to anyone who thinks this isn't very light their argument,
and agree it would add some amount of heft to SQLite. I suspect not
much, but I also know how easy it is for people who have no idea to
say that to me about my own software. I don't know. I concede to
anyone else who thinks we already have ways to do this their argument,
my thoughts are just that this could greatly simplify a lot of sql
code that currently has to prepare and execute at least two statements
to accomplish what is conceptually an atomic task.

Thank you for your time.

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

Re: [sqlite] This is driving me nuts

[Scott Robison]

Sqlite will use different strategies for ASC and desc ordering and result
set sizes. Perhaps one is creating a temp btree to order the results. I
think explain query plan might help show exactly what sqlite is
contributing to the memory consumption without the need for as much
speculation. Not intended as a critical comment, just a thought in passing.

On Thu, May 31, 2018, 9:22 AM x  wrote:

> Yes, I think you’re right. I suppose maybe it recognises the desc sequence
> from page access.
>
>
>
> 
> From: sqlite-users  on
> behalf of Andy Ling 
> Sent: Thursday, May 31, 2018 3:57:27 PM
> To: 'SQLite mailing list'
> Subject: Re: [sqlite] This is driving me nuts
>
> > While that makes sense Clemens it again fails to explain why it does no
> > caching when desc is stepped through first. I am at least satisfied that
> it’s not
> > something I should dwell on or, more the point, it isn’t down to a bug
> in my
> > code which is why I got embroiled in it in the first place.
> >
>
> My understanding from what Clemens said, which might clarify.
>
> When ascending pages are read sequentially, so sequential mode is used and
> the pages get saved in the cache, but when descending, pages are read in
> reverse order which makes the cache think it's random so it doesn't keep
> them.
>
> So if you do ascending first the cache gets filled and eats up memory.
> Then when you do descending it gradually deletes all the pages from the
> cache
> and frees up the memory. If you do it the other way round, descending
> uses one page worth of memory then ascending eats up enough for all the
> pages.
>
> Andy Ling
>
> ---
> This email has been scanned for email related threats and delivered safely
> by Mimecast.
> For more information please visit http://www.mimecast.com
>
> ---
>
> ___
> 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] SQLite is a LoC Preferred Format for datasets

[Scott Robison]

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

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

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

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

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

Re: [sqlite] Always call a value-quoting routine

[Scott Robison]

On Sun, May 6, 2018 at 11:34 PM, Rowan Worth  wrote:
> Amusing -- but without the leading single-quote it would take intentional
> effort for a programmer to detonate this payload.
>
> Its omission is interesting though. Does it indicate an incompetent
> attacker, or is companieshouse.gov.uk using some bespoke approach like
> "delete all single quotes" instead of actually quoting strings?

It could just indicate someone with a sense of humor who crafted a
name that looks like an injection attack for their company.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Always call a value-quoting routine

[Scott Robison]

Thanks for sharing that. It will undoubtedly be useful to me in a computer
security class I'm taking this semester.

On Sat, May 5, 2018, 4:57 PM Simon Slavin  wrote:

> This is a genuine company registered under the UK Companies Act:
>
> 
>
> The name of company is
>
> ; DROP TABLE "COMPANIES";-- LTD
>
> (Note: For legal reasons a UK company name must end in 'LTD' or 'plc',
> depending on the type of company it is.)
>
> Simon.
> ___
> 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] Fossil Feature Request

[Scott Robison]

On Fri, Mar 30, 2018 at 1:32 PM, J Decker  wrote:
>  Sqlite's Fossile browser can't link line numbers...
>
>
> Add ability to link to lines of source...
>
>
> was trying to share this as another reference for getting UTF8 characters
> from strings
>
> #define READ_UTF8(zIn, zTerm, c)
> https://www.sqlite.org/src/artifact/810fbfebe12359f1
>
> which is like line 155 and kinda hard to find... when sharing refernces
> from github I can select a line to share directly.

Try something like this:

http://www.fossil-scm.org/fossil/artifact/6eb26bb7a6?ln=1755-1759
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018, 8:18 AM Richard Hipp <d...@sqlite.org> wrote:

> On 3/29/18, Scott Robison <sc...@casaderobison.com> wrote:
> >  It seems a
> > reasonable to suggestion to add it.
>
> Version 3.23.0 is in bug-fix-only mode.  It'll have to wait.
>

Of course. I wasn't suggesting that it should be done with all haste, just
agreeing with the original suggestion.

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

Re: [sqlite] .dump command and user_version

[Scott Robison]

On Thu, Mar 29, 2018 at 6:56 AM, Simon Slavin  wrote:
>
>
> On 29 Mar 2018, at 1:47pm, Wout Mertens  wrote:
>
>> I noticed that `.dump` does not output the user_version pragma. It seems to
>> me that that is part of the database data?
>>
>> I don't actually use it, but it might be interesting to add this for
>> completeness?
>
> .dump is for things which affect data and data integrity only.  "PRAGMA 
> foreign_keys" is one of the few PRAGMAs which affects data integrity: if a 
> database is operated with that PRAGMA not set, the data could be corrupted.
>
> Numerous PRAGMAs have no effect on integrity.  For example the journal mode 
> and page size.  They are not included in the output of .dump.

Yet as he said, user_version is data. It can be queried and can be
checked to make decisions about what to do with the data. It seems a
reasonable to suggestion to add it.
___
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?

[Scott Robison]

0

On Mar 16, 2018 9:37 AM, "Richard Hipp"  wrote:

> This is a survey, the results of which will help us to make SQLite faster.
>
> How many tables in your schema(s) use AUTOINCREMENT?
>
> I just need a single integer, the count of uses of the AUTOINCREMENT
> in your overall schema.  You might compute this using:
>
>sqlite3 yourfile.db '.schema --indent' | grep -i autoincrement | wc -l
>
> Private email to me is fine.  Thanks for participating in this survey!
> --
> 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
>
___
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'

[Scott Robison]

Integer primary key is by definition not null, so looking for a null value
on an index can't work. I guess there exists an optimization opportunity to
just return an emotional set, though it seems easier to not specify an
impossible condition.

As to why it does a table scan, the primary key isn't a separate index. The
rowid primary key is part of the table itself.

On Jan 7, 2018 11:22 AM, "Luuk"  wrote:

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

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 5:46 PM, petern <peter.nichvolo...@gmail.com> wrote:
> Hi Scott.
>
>>Are there other aggregate functions that take multiple arguments?
>
> Absolutely.  I've got a few in my code which deserialize table rows into
> runtime objects.  Fortunately, the DISTINCT filter makes no sense in that
> use case, so I didn't bump into this issue myself.

Thanks for the info. In doing some quick searches, I found multiple
descriptions of "generic SQL" aggregates that gave a syntax of
"aggregate([DISTINCT|ALL] expression)", which led me to assume that
maybe the standard only allows that syntax with a single expression,
not an expression list. I say maybe because the examples I found were
clearly not the standard.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 4:15 PM, petern  wrote:
> Hi Tony.  Good. Yes, simpler test case is always better when posting
> possible bugs.
>
> Unfortunately, as Cezary points out, this error is by design (from
> select.c):
>
>if( pFunc->iDistinct>=0 ){
>   Expr *pE = pFunc->pExpr;
>   assert( !ExprHasProperty(pE, EP_xIsSelect) );
>   if( pE->x.pList==0 || pE->x.pList->nExpr!=1 ){
> sqlite3ErrorMsg(pParse, "DISTINCT aggregates must have exactly one "
>"argument");
> pFunc->iDistinct = -1;
>   }else{
>
> It would be interesting to understand the harm avoided by disallowing
> DISTINCT scope of all the aggregate parameters.   Probably slower, but what
> else?  Usually, there is a comment in the source but not for this one.

I'm not the guy who wrote it or decided how it should work, but it
seems to me that "group_concat(distinct x,y)" would not work
intuitively if y is a variable, though (somewhat surprisingly to me)
it works:

sqlite> create table t(x,y);
sqlite> insert into t values (1,','),(1,'+'),(2,'.'),(2,'-');
sqlite> select group_concat(x,y) from t;
1+1.2-2
sqlite> select group_concat(distinct x,y) from t;
Error: DISTINCT aggregates must have exactly one argument
sqlite> select group_concat(x,y) from (select distinct x, y from t);
1+1.2-2

Are there other aggregate functions that take multiple arguments? I
can't find any examples online of aggregates that take more than one,
which seems like the normal way something like this would be done.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GROUP_CONCAT with DISTINCT bug

[Scott Robison]

On Tue, Jan 2, 2018 at 1:36 PM, Tony Papadimitriou  wrote:
> create table t(s);
> insert into t values ('A'),('A'),('B');
>
> select group_concat(s,', ') from t group by null;   -- OK
> select group_concat(distinct s) from t group by null;   -- OK
> select group_concat(distinct s,', ') from t group by null;  -- ERROR
>
> -- The moment the optional delimiter is given along with DISTINCT you get 
> this error:
> -- Error: near line 6: DISTINCT aggregates must have exactly one argument

A limitation of the SQL syntax. I suspect this might work for your
example use case: select group_concat(s, ',') from (select distinct s
as s from t) group by null;

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

Re: [sqlite] Move to Github!!?

[Scott Doctor]

What fossil needs is for the UI to perform ALL normal common 
functions (new, commit, clone,...) WITHOUT having to open a 
command line window. That is imo the main limitation.



-
Scott Doctor
sc...@scottdoctor.com
-


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

Re: [sqlite] INSERT OR IGNORE ignores constraints. Bug ?

[Scott Robison]

On Dec 21, 2017 10:50 AM, "Simon Slavin"  wrote:



On 21 Dec 2017, at 3:46pm, David Raymond  wrote:

> The only potential problem with "insert or ignore into" is that it will
ignore any constraint violation for that record insert

Wait.  What ?

SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE MyTable (a INTEGER, CONSTRAINT noless CHECK (a > 10));
sqlite> INSERT INTO MyTable VALUES (15);
sqlite> INSERT INTO MyTable VALUES (5);
Error: CHECK constraint failed: noless
sqlite> INSERT OR IGNORE INTO MyTable VALUES (6);
sqlite> SELECT * FROM MyTable;
15
sqlite>

What the hell ?  Why does that work ?  Isn’t it a huge bug ?  How did you
discover it ?

My understanding is that using INSERT OR IGNORE meant that bad inserts
would fail, but they would do so silently, without triggering an error
result.


Insert 15 succeeded, 5 failed with error, 6 failed without error. Seems
exactly as advertised. What am I missing?

Note that or ignore is statement specific, not transaction specific, if I
understand correctly.
___
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

[Scott Doctor]

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
-

On 12/8/2017 12:09, John McKown wrote:

On Fri, Dec 8, 2017 at 12:54 PM, John Mount <jmo...@win-vector.com> wrote:


I am seeing an issue where a random value in a sub-query gets re-generated
each time the value is used in later queries.  Below is an example query:

SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT * from (
VALUES(1),(2) ) ) a ) b

One would expect r1 == r2.

---
John Mount


​Interesting. I duplicated your example using PostgreSQL and it does what
you said you expected: R1==R2. In SQLite3, the .eqp full shows:

sqlite> .eqp full
sqlite> SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT *
from ( VALUES(1),(2) ) ) a ) b;
--EQP-- 0,0,0,SCAN SUBQUERY 1
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 0 000  Start at 0
1 InitCoroutine  1 7 200
sqlite_sq_5619D81F9BF8
2 Integer1 2 000  r[2]=1
3 Yield  1 0 000
4 Integer2 2 000  r[2]=2
5 Yield  1 0 000
6 EndCoroutine   1 0 000
7 InitCoroutine  1 0 200
8   Yield  1 13000  next row of
"sqlite_sq_5619D81F9BF8"
9   Function0  0 0 4 random(0)  00  r[4]=func(r[0])
10  Function0  0 0 5 random(0)  00  r[5]=func(r[0])
11  ResultRow  4 2 000  output=r[4..5]
12Goto   0 8 000
13Halt   0 0 000
4548137244590923354  8821858240296964415
761559492082550893  2723588653195689097
​
I think this is being done due to the "subquery flattening" as described on
http://sqlite.org/optoverview.html, which says:

To overcome this problem, SQLite attempts to flatten subqueries in the FROM
clause of a SELECT. This involves inserting the FROM clause of the subquery
into the FROM clause of the outer query and rewriting expressions in the
outer query that refer to the result set of the subquery. For example:

So your "SELECT r AS r1, r AS r2 FROM ( SELECT random() AS r FROM ( SELECT
* from ( VALUES(1),(2) ) ) a ) b;" ​be​comes

"SELECT random() AS r1, random() AS r2 FROM ( SELECT * from (values(1),(2))
a) b;"

I think the above is born out if you put the word DISTINCT in front of the
random() in the original example. This forces SQLite to _not_ use subquery
flattening. Example:

sqlite> SELECT  r AS r1, r AS r2 FROM ( SELECT distinct random() AS r FROM
( SELECT * from ( VALUES(1),(2) ) c ) a ) b;
--EQP-- 1,0,0,SCAN SUBQUERY 2 AS c
--EQP-- 1,0,0,USE TEMP B-TREE FOR DISTINCT
--EQP-- 0,0,0,SCAN SUBQUERY 1 AS b
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 0 000  Start at 0
1 InitCoroutine  1 18200
sqlite_sq_55E270A58EA8
2 InitCoroutine  2 8 300
sqlite_sq_55E270A58AA8
3 Integer1 3 000  r[3]=1
4 Yield  2 0 000
5 Integer2 3 000  r[3]=2
6 Yield  2 0 000
7 EndCoroutine   2 0 000
8 OpenEphemeral  3 0 0 k(1,B) 08  nColumn=0
9 InitCoroutine  2 0 300
10  Yield  2 17000  next row of
"sqlite_sq_55E270A58AA8"
11  Function0  0 0 5 random(0)  00  r[5]=func(r[0])
12  Found  3 165 1  00  key=r[5]
13  MakeRecord 5 1 600  r[6]=mkrec(r[5])
14  IdxInsert  3 6 000  key=r[6]
15  Yield  1 0 000
16Goto   0 10000
17EndCoroutine   1 0 000
18InitCoroutine  1 0 200
19  Yield  1 24000  next row of
"sqlite_sq_55E270A58EA8"
20  Copy   5 7 000  r[7]=r[5];
sqlite_sq_55E270A58EA8.r
21  Copy   7 8 000  r[8]=r[7]
22  ResultRow  7 2 000  output=r[7..8]
23Goto   0 190

Re: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB

[Scott Robison]

They'll be able to renew the certificate after some payments are made after
the free 6 month trial had lapsed. :)

On Dec 5, 2017 5:15 PM, "Keith Medcalf"  wrote:

>
> Uses an expired SSL certificate ...
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of sub sk79
> >Sent: Tuesday, 5 December, 2017 14:48
> >To: SQLite mailing list
> >Subject: [sqlite] StepSqlite: SuperPowers for SQLite and BerkeleyDB
> >
> >Hi All:
> >
> >The next version of StepSqlite - enhanced-PL/SQL compiler for SQLite
> >and Oracle-TM
> >BerkeleyDB
> >(https://www.metatranz.com/stepsqlite)
> >is due soon and is packed with Super Powers which will blow your
> >mind!
> >
> >For a start, how about powerful Analytic Window Functions,
> >Collections,
> >Bulk-Ops?
> >Now, if you think that is huge, wait, there is a lot more!!!
> >Look for a detailed announcement in first week of New Year 2018.
> >
> >Currently aiming for a January end beta-release after the ongoing
> >rigorous
> >testing and bug-fix cycle.
> >
> >Meanwhile you can sign-up for beta. Everyone signing up for beta -
> >till Jan
> >31, 2018, gets a professional version free for 6 months (July 31,
> >2018).
> >Those already on the list need not re-register - you are already
> >included! There
> >will always be a free developer version (size-limited ).
> >
> >Be the first to experience the power - get on the waiting list:
> >http://www.metatranz.com/stepsqlite/ShowSignUp?guestlogin=ON
> >
> >Happy New Year!!
> >
> >-SKashikar
> >(Founder)
> >__
> >**StepSqlite** enhanced-PL/SQL on Sqlite
> >and BerkeleyDB: *Be Done at the Speed of Lite!*
> >
> >Twitter: https://twitter.com/metatranz
> >Facebook: https://www.facebook.com/Metatranz/
> >___
> >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] Sqlite and docker performance question

[Scott Robison]

Perhaps the file sync performed by SQLite is more expensive in the docker
environment than in the host. That would make sense to me.

On Nov 30, 2017 7:07 AM, "Sebastien HEITZMANN" <2...@2le.net> wrote:

> In my last mail i have multiple table creation and index. It seam that the
> overtime is for all the create statement.
>
> It really looks like a disk pb. But can't identify so much difference in my
> direct disk access ( with DD )
>
> I will try it on an other host.
>
> 2017-11-30 14:59 GMT+01:00 Simon Slavin :
>
> > OP wrote:
> >
> > > CREATE TABLE f (fid VARCHAR, path VARCHAR, meta VARCHAR, mtime INTEGER,
> > > virtual INTEGER, pfid VARCHAR, type VARCHAR, ts INTEGER);
> >
> > Is this the first content of a new file ?  If so, SQLite has to create
> the
> > file and write some structure information as well as writing the table.
> I
> > suspect that the time taken for the overhead is far more than the time
> > taken for the CREATE command.
> >
> > Could you try changing f.sql to create ten tables ?  For example create
> > the table "f1 as above then create tables "f2" to "f1" with the same
> > columns ?  It would be interesting to see what this does to both timings.
> >
> > Simon.
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> *Sébastien HEITZMANN*
> Gérant & Directeur technique
> +33 (0)3 89 333 889
> Plus d'info sur : www.2le.net
>
>
>  lentreprise/194148499368?ref=ts>
>   
> 
> ___
> 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] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

[Scott Robison]

On Fri, Sep 29, 2017 at 1:20 PM, Bob Friesenhahn
<bfrie...@simple.dallas.tx.us> wrote:
> On Fri, 29 Sep 2017, Scott Robison wrote:
>>
>>
>> The problem is that there is no one best practice for resolving all
>> such warnings in a way that makes all compilers happy. It is possible
>> to fix all the warnings for one platform, then move on to the next
>> platform and fix all its warnings, and return to the original platform
>> and discover that new warnings have been introduced.
>
>
> My own experience has been that it is possible to write valid C code which
> does not produce warnings at high warning levels on just about any standard
> C compiler.  It is not necessarily a case of "whack a mole". The most
> annoying exception is the Microsoft Visual C Compiler, which produces
> deprecation warnings for standard functions.
>
> One does need to be very careful when fixing compiler warnings so as to not
> introduce new bugs.  The most dangerous warnings to work on are those
> involving signed vs unsigned types.

Except for the fact that the OP called for maximum pedantic warnings.
In that case, you can't reliably fix all the warnings, because
different compilers have different ideas of what maximum means.

In this very thread there is a warning from GCC about

#if SQLITE_4_BYTE_ALIGNED_MALLOC

not being defined. But the standard requires that undefined symbols
being replaced with 0 during preprocessing. How is that warning
useful? It is by definition standard compliant and well defined.

The problem is not just with MSVC.

It is not that warning free code is impossible to create, it just
depends on the details, which sometimes make it very difficult.

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

Re: [sqlite] Please remove multiple warnings from compiler about optimisation, variable conversion, signed overflow and many more potential errors.

[Scott Robison]

On Fri, Sep 29, 2017 at 11:14 AM, Denis V. Razumovsky  wrote:
> I would like to draw attention to the document: "The Power of 10: Rules
> for Developing Safety-Critical Code" from  NASA/JPL Laboratory.
> https://en.wikipedia.org/wiki/The_Power_of_10:_Rules_for_Developing_Safety-Critical_Code

The problem is that there is no one best practice for resolving all
such warnings in a way that makes all compilers happy. It is possible
to fix all the warnings for one platform, then move on to the next
platform and fix all its warnings, and return to the original platform
and discover that new warnings have been introduced.

Additionally, SQLite has never been responsible for the destruction of
a space craft, so maybe SQLite has something right that NASA has
wrong. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug: failure to write journal reported as "disk I/O error"

[Scott Robison]

There are physical errors and there are logical errors. If an error is
generated from write, it's not unreasonable to classify it as an
"output error". From read as an "input error".

There is a lot of sqlite source code that already exists and has been
written to work with the current interface. That's probably one of the
reasons why extended errors were created, to provide finer
granularity. Regardless of whether it is ideal or not, changing sqlite
in a way that would break existing code is unlikely to happen.

Ultimately it doesn't matter when error codes were added to a given
operating system or which predates what. A decision was made in the
past. The options are to live with decisions that were made in the
past (one I've seen espoused multiple times in this mailing list),
come up with an approach that allows old code to work but exposes new
information (probably the genesis of extended error codes), or break
older code (which I've not seen done deliberately).

I'm not trying to tell you that your point is invalid. It makes sense
in many ways. Short of a time machine I doubt anything will change
(though those decisions are above my pay grade).

That being said, I don't know any non-technical users who are going to
panic that IOERR means their hard drive is dying specifically because
of that text being displayed. Panic perhaps, but not that a hard drive
is about to die. Most people I know don't have that level of
understanding to correlate IO / ERR / hard drive failure rates. They
just think the stupid program is broken and not letting them get their
work done. As for the experienced technical people I know (or at least
me), their first thought would be to investigate the problem, not to
assume their hard drive is failing.


On Tue, Sep 26, 2017 at 2:17 PM, Guy Harris <g...@alum.mit.edu> wrote:
> On Sep 26, 2017, at 1:05 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 26 Sep 2017, at 8:47pm, Guy Harris <g...@alum.mit.edu> wrote:
>>
>>> On Sep 26, 2017, at 8:22 AM, Jens Alfke <j...@mooseyard.com> wrote:
>>>
>>>> The basic error code is SQLITE_IOERR, which just means "Some kind of disk 
>>>> I/O error occurred” according to the comment. Which is true in this case; 
>>>> an I/O operation returned an error.
>>>
>>> But the *disk* didn't - the *operating system* did, so if SQLITE_IOERR 
>>> really means "Some kind of disk I/O error occurred", it's *not* the right 
>>> error to return for a *permission* error.
>>
>> Those error codes were devised in a day when OS error codes were more simple.
>
> EDQUOT was introduced in 1982, with 4.2BSD; when was SQLITE_IOERR devised?
>
>> Also please note that those error codes are addressed to programmers.  Your 
>> users should never see the text explanation of the number.  Because your 
>> users wouldn’t know what to do about them.
>
> A user wouldn't know what to do with "you've exceeded your stored data 
> quota"?  If so, your site has failed to explain to the users that they've 
> been given a quota, limiting the amount of space on the server that they can 
> use, and that if they exceed their quota, they either need to delete stuff 
> they no longer need, move stuff they might *someday* need but don't need 
> *now* to some archival medium, or ask their system administrator to increase 
> their quota?
>
>> At most the user can be shown the number returned to they can quote it in a 
>> support call.
>
> The *number* might annoy the support staff; right off the top of your head, 
> what's the error number for "file system quota exceeded" or "I/O error"?  (No 
> cheating by looking it up in a man page or include file!)
>
> And, yes, there needs to be *some* way to get the underlying problem reported 
> to somebody in a position to do something about it - where "the underlying 
> problem" includes "what did the OS say?" as much as it includes "what SQLite 
> operation got the error?".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] Support for named foreign keys?

[Scott Robison]

Ah, I misread. Sorry for the noise.

On Sep 24, 2017 4:59 PM, "Keith Medcalf" <kmedc...@dessus.com> wrote:

>
> I don't think so.  The question is about interpreting the schema, not
> about executing anything at all.
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Scott Robison
> >Sent: Sunday, 24 September, 2017 16:47
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Support for named foreign keys?
> >
> >I think he's asking for FK constraint names to be reported in
> >conflict
> >messages which has been requested in the past, but not included up
> >until
> >now because of the approach taken.
> >
> >On Sep 24, 2017 4:16 PM, "Keith Medcalf" <kmedc...@dessus.com> wrote:
> >
> >>
> >> Why do you think this?
> >>
> >> The syntax diagrams are quite clear that FOREIGN KEY constraints
> >can be
> >> named.
> >>
> >> sqlite> create table parent
> >>...> (id integer primary key);
> >> sqlite> create table child
> >>...> (fk integer null,
> >>...> constraint dippydoo foreign key (fk) references
> >parent(id));
> >> sqlite>
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-Original Message-
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >> >Sent: Sunday, 24 September, 2017 12:06
> >> >To: SQlite User
> >> >Subject: [sqlite] Support for named foreign keys?
> >> >
> >> >Hi,
> >> >
> >> >when will SQLite support the named foreign keys?
> >> >
> >> >I am using SchemaCrawler to visualize my SQLite databases and
> >because
> >> >SQLite does not support it yet, foreign keys get automatically
> >> >generated foreign key names in the diagram.
> >> >
> >> >So it would be nice if SQLite would support it one day. Is'n it?
> >> >
> >> >--
> >> >Best, Pali
> >> >___
> >> >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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Support for named foreign keys?

[Scott Robison]

I think he's asking for FK constraint names to be reported in conflict
messages which has been requested in the past, but not included up until
now because of the approach taken.

On Sep 24, 2017 4:16 PM, "Keith Medcalf"  wrote:

>
> Why do you think this?
>
> The syntax diagrams are quite clear that FOREIGN KEY constraints can be
> named.
>
> sqlite> create table parent
>...> (id integer primary key);
> sqlite> create table child
>...> (fk integer null,
>...> constraint dippydoo foreign key (fk) references parent(id));
> sqlite>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-Original Message-
> >From: sqlite-users [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Csányi Pál
> >Sent: Sunday, 24 September, 2017 12:06
> >To: SQlite User
> >Subject: [sqlite] Support for named foreign keys?
> >
> >Hi,
> >
> >when will SQLite support the named foreign keys?
> >
> >I am using SchemaCrawler to visualize my SQLite databases and because
> >SQLite does not support it yet, foreign keys get automatically
> >generated foreign key names in the diagram.
> >
> >So it would be nice if SQLite would support it one day. Is'n it?
> >
> >--
> >Best, Pali
> >___
> >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] Problem with mailing list

[Scott Doctor]

Check your spam folder. Some messages get trapped from the list in there 
occassionally

On September 6, 2017 1:28:21 AM PDT, Bart Smissaert <bart.smissa...@gmail.com> 
wrote:
>For some reason it seems postings I send sometimes don't get through or
>maybe they do get through but I can't see them.
>I posted something yesterday at 12:55 pm (Problem on Windows 10
>machines) and I still can't see that posting on the list.
>I mail from my normal GMail account.
>Is there anything that I might be doing wrong?
>
>RBS
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

- - - -
Scott Doctor
sc...@scottdoctor.com

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

Re: [sqlite] What's the level of B+-Tree ?

[Scott Robison]

My understanding is that SQLite doesn't use the traditional definition of
b-tree because it doesn't use fixed size records/keys. It will cram as few
or as many as possible.

I'm not in a position to confirm that, but it was something I read a few
years ago I think.

On Aug 11, 2017 9:16 AM, "james ni"  wrote:

> Yes, yes, that's what I'm seeking
>
> 
> From: sqlite-users  on
> behalf of R Smith 
> Sent: Friday, August 11, 2017 18:25
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] What's the level of B+-Tree ?
>
>
> On 2017/08/11 11:08 AM, Clemens Ladisch wrote:
> > james ni wrote:
> >> As in the example that I provided, there are 4 cells in a single btree
> >> page. So there must be some mechanism to determine hoe many keys that
> >> one cell can own.
> > I want to know exactly the very value and just how to change the value
> > to a larger one, for example, 256, 512, or even larger.
> > Keys (and values) can have arbitrary size, so to change how many can fit
> > into a page, make them smaller.  :)
>
> I think perhaps there is a communication failure here. I think the OP is
> looking for a way to change the target key count or maximum allowed keys
> or whatever other value controls how wide a B-Tree page/leaf becomes
> before SQLite decides to de-congest it into two/more new apartments
> (whatever they may be). I /think/ that's what is being asked.
>
> If that is the question - I'm not an expert on it, but I don't think it
> works like that. I think DB Page-sizes dictate those decisions, but I
> might well be wrong.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> sqlite-users Info Page listinfo/sqlite-users>
> mailinglists.sqlite.org
> To see the collection of prior postings to the list, visit the
> sqlite-users Archives. (The current archive is only available to the list
> ...
>
>
> ___
> 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] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

[Scott Robison]

On Fri, Jul 14, 2017 at 11:55 AM, Howard Kapustein
<howard.kapust...@microsoft.com> wrote:
> sqlite> create table blah(id integer primary key not null, x text, y integer 
> not null);
> sqlite> create index blahindex on blah (y);
> sqlite> explain query plan select * from blah where y & ? != 0;
> 0|0|0|SCAN TABLE blah
>
> But other numeric expressions do e.g.
>
> sqlite> explain query plan select * from blah where y = ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?)
> sqlite> explain query plan select * from blah where y > ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?)
>
> Is this a known issue?

The indexed value of y in blah is a sorted list of all values from
lowest to highest. It is good for asking questions like "is there a
row where y = something" or "y relates to something". The bitwise and
operator is asking the question "where at least one bit from a set is
not zero".

Let's say you have 100 rows with different values of y. If you ask
for rows "where y & 2 != 0", you should get all the even numbers. The
only way to get that information from an index that includes all
values of y (even and odd) is to scan.

No issue here.

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

Re: [sqlite] VALUES clause quirk or bug?

[Scott Robison]

On Sat, Jul 8, 2017 at 8:50 PM, petern <peter.nichvolo...@gmail.com> wrote:
> The bug here is how the VALUES logic can't have it both ways.  If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored.  They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause.   That is of
> course unless there is a software easter egg here that, depending on as yet
> undisclosed well crafted literal values, allows the column names to be
> elegantly specified in addition to the row values.
>
> Also sent through sqlite3_exec():
>
> SELECT * FROM (VALUES ("1",2),(3,4));
> 1,
> 1,2
> 3,4

The ultimate problem here is that long ago a decision was made to be
liberal with interpretation of SQL text so that people had a better
chance of seeing what they expected. Single quoted text is a string,
double quoted text is an identifier. If a string is given where an
identifier is expected, it'll be treated as an identifier in an
attempt to be helpful. If an identifier is given where a string is
expected, and no such identifier exists, it'll be interpreted as a
string instead.

I've read commentary from DRH suggesting that if he had it to do over
again, some of these "helpful" things might not have been done.
Unfortunately, backward compatibility demands that this not be changed
at this point in time.

Further (to me anyway), the keyword "VALUES" seems to explicitly
exclude the option of naming columns. "VALUES" it says, and that's all
you can get from it. I can certainly see the utility of inline
anonymous tables with named columns, but that particular syntax goes
out of its way to omit the potential of naming the columns.

So, should SQLite be pickier in the syntax it supports? Probably. Can
it be changed retroactively and break a bunch of existing code?
Probably not (though it's not my position to say one way or the
other). Are there other syntactic constructs that give you the ability
to have named columns in an otherwise anonymous query? Yes.

Good luck!

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

Re: [sqlite] INSERT ... VALUES / want to "skip" default values

[Scott Robison]

On Jun 28, 2017 6:51 AM, "Simon Slavin"  wrote:



On 28 Jun 2017, at 9:45am, Clemens Ladisch  wrote:

> An explicit NULL works only for the autoincrement column, but not for
default values.

Really ?  In that case I withdraw my previous answer.  I thought that NULLs
were converted to the default value for a column (which is usually NULL but
can be overridden with a DEFAULT clause).  Thanks for the correction.


Depending on needs, one can make a column not null with on conflict
replace, at which point trying to insert or update with null will use the
default, but that is only available if your column can never be null.


Simon.
___
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] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Tue, Jun 27, 2017 at 4:18 AM, Richard Hipp <d...@sqlite.org> wrote:
> The CSV import feature of the SQLite command-line shell expects to
> find UTF-8.  It does not understand other encodings, and I have no
> plans to add converters for alternative encodings any time soon.
>
> The latest version of trunk skips over a UTF-8 BOM at the beginning of
> the input file.

A little late, but it occurred to me how to make this "work" with
older versions of sqlite3 that support readfile / writefile. Say I
have a UTF8 BOM encoded file. I can trim it from SQLite then import
the trimmed version:

sqlite> select writefile('temp.csv', substr(readfile('utf8.csv'), 4));

sqlite> .import temp.csv temp
sqlite> .import utf8.csv utf8
sqlite> .schema
CREATE TABLE temp(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);
CREATE TABLE utf8(
  "?a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);

Alternatively, without readfile / writefile support:

sqlite> pragma writable_schema = 1;
sqlite> update sqlite_master set sql = replace(sql, char(0xFEFF), '')
where name = 'utf8';
sqlite> pragma writable_schema = 0;
sqlite> vacuum;
sqlite> .schema
CREATE TABLE temp(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);
CREATE TABLE utf8(
  "a" TEXT,
  "b" TEXT,
  "c" TEXT,
  "d" TEXT
);

Still, not nearly as friendly as sqlite shell doing it for you.

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

Re: [sqlite] [OT] UTF8-BOM and text encoding detection (was: UTF8-BOM not disregarded in CSV import)

[Scott Robison]

On Jun 27, 2017 12:13 AM, "Rowan Worth"  wrote:

I'm sure I've simplified things with this description - have I missed
something crucial? Is the BOM argument about future proofing? Are we
worried about EBCDIC? Is my perspective too anglo-centric?


The original issue was two of the largest companies in the world output the
Byte Encoding Mark(TM)(Patent Pending) (or BOM) at the beginning of UTF-8
encoded text streams, and it would be friendly for the SQLite3 shell to
skip it or use it for encoding identification in at least some cases.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 9:02 AM, "Simon Slavin"  wrote:

There is no convention for "This software understands both UTF-16BE and
UTF-16LE but nothing else.".  If it handles any BOMs, it should handle all
five.  However, it can handle them by identifying, for example, UTF-32BE
and returning an error indicating that it can’t handle any encodings which
aren’t 16 bit.

Try to be consistent across all fields in your protocol/API.

References:




+1

FAQ quote:

Q: When a BOM is used, is it only in 16-bit Unicode text?

A: No, a BOM can be used as a signature no matter how the Unicode text is
transformed: UTF-16, UTF-8, or UTF-32.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 4:05 AM, "Rowan Worth" <row...@dug.com> wrote:

On 26 June 2017 at 16:55, Scott Robison <sc...@casaderobison.com> wrote:

> Byte Order Mark isn't perfectly descriptive when used with UTF-8. Neither
> is dialing a cell phone. Language evolves.
>

It's not descriptive in the slightest because UTF-8's byte order is
*specified by the encoding*.


I fear you may not have read my entire email or at least have missed my
point.

 I'm not advocating one way or
> another, but if a system strips U+FEFF from a text stream after using it
to
> determine the encoding, surely it is reasonable to expect that for all
> supported encodings.
>

?? Are you going to strip 0xFE 0xFF from the front of my iso8859-1 encoded
stream and drop my beautiful smiley? þÿ
Different encodings demand different treatment. BOM is an artifact of
16/32-bit unicode encodings and can kindly keep its nose out of [the
relatively elegant] UTF-8.


One, I'm not going to do anything. Two, clearly I'm taking about the three
byte UTF-8 sequence that decodes to U+FEFF. Three, you are correct about
different encodings. I was trying to move the discussion past the idea of
byte order when what we're really talking about is encoding detection.
ZWNBSP was used for encoding detection because it had a convenient property
that allowed differentiation between multiple encodings and could be safely
ignored. The fact that the Unicode folks renamed it BOM instead of TEI or
BEM or whatever doesn't mean it can't be used with other unicode
transformations. It is neither required, recommended, nor forbidden with
UTF-8, it's up to systems exchanging data to decide how to deal with it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 25, 2017 1:16 PM, "Cezary H. Noweta"  wrote:


Certainly, there are no objections to extend an import's functionality
in such a way that it ignores the initial 0xFEFF. However, an import
should allow ZWNBSP as the first character, in its basic form, to be
conforming to the standard.


If we're going to conform to the standard, U+FEFF has been deprecated as
ZWNBSP since Unicode 3.2 in 2002. U+2060 is the Word Joiner now. U+FEFF is
now "reserved" for differentiation of encodings at the beginning of a
stream of text. It may not be required or recommended, but it's not
forbidden either.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UTF8-BOM not disregarded in CSV import

[Scott Robison]

On Jun 26, 2017 1:47 AM, "Rowan Worth"  wrote:

On 26 June 2017 at 15:09, Eric Grange  wrote:

> Alas, there is no end in sight to the pain for the Unicode decision to not
> make the BOM compulsory for UTF-8.
>

UTF-8 is byte oriented. The very concept of byte order is nonsense in this
context as there is no multi-byte storage primitives to worry about.

Making it optional or non-necessary basically made every single text file
> ambiguous
>

Easily solved by never including a superflous BOM in UTF-8 text.


Some people talk about dialing a phone or referring to a remote control as
a clicker, even though most of us don't use pulse based dialing or remote
controls that actually click.

The reality is that interchange of text requires some means to communicate
the encoding, in band or out of band. ZWNBSP (now BOM) was selected as a
handy in band way to distinguish LE from BE fixed size multi-byte text. One
could just as easily call that stupid and demand everyone use network byte
order.

Byte Order Mark isn't perfectly descriptive when used with UTF-8. Neither
is dialing a cell phone. Language evolves.

Maybe people would prefer calling it TEI (Text Encoding Identifier). Then
we could get back to discussion of whether or not stripping U+FEFF from the
beginning of text streams is a good idea. I'm not advocating one way or
another, but if a system strips U+FEFF from a text stream after using it to
determine the encoding, surely it is reasonable to expect that for all
supported encodings. If it doesn't do that for one, it shouldn't do it for
any.

Does SQLite3 support UTF-16 CSV files with BOM/TEI? If not, then UTF-8 need
not. If so, perhaps it should.

As for using a signature at the beginning of UTF-8 text, it certainly can
be useful to distinguish Unicode from code pages & other incompatible
encodings.

That being said, it's not difficult to strip TEI from a file before passing
it to SQLite3 (or any other tool for that matter).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to search for fields with accents in UTF-8 data?

[Scott Robison]

On Tue, Jun 20, 2017 at 8:17 AM, Olivier Mascia  wrote:
>> Le 20 juin 2017 à 15:24, R Smith  a écrit :
>>
>> As an aside - I never understood the reasons for that. I get that Windows 
>> has a less "techy" clientèle than Linux for instance, and that the backwards 
>> compatibility is paramount, and that no console command ever need fall 
>> outside the 7-bit ANSI range of characters... but geez, how much effort can 
>> it be to make it Unicode-friendly? It's not like the Windows API lacks any 
>> Unicode functionality - even Notepad can handle it masterfully.
>
> I wouldn't like looking like I'm trolling this subject, but this is only a 
> matter of I/O functions used by programs built to interact with the display 
> and keyboard when run in a console. Windows needs those programs to use 
> ReadConsoleW/WriteConsoleW to do the proper thing.  Those programs using C 
> library to read or output byte streams can't do anything equivalent no matter 
> what 'codepage' is set to be used or to/from what DBCS the program attempts 
> conversion to/from.
>
> I learned this postulat here last year and have had excellent success with 
> console I/O ever since in my programmings.

About a year ago I had to write an emergency fixup tool for my
employer because of a backward breaking change at Microsoft that was
almost certainly due to a breakdown in revision control. The tool
needed to be localized, but it was sufficiently simple that a console
mode executable was sufficient. I had to jump through hoops to make it
work, but (by way of confirmation), the problems were in the CRT, not
the Win32 API. It was able to write and read Unicode ...

> To be complete, regarding proper display of the output, there is a secondary 
> consideration. The fonts available in Windows are far from covering a large 
> subset of the glyphs.  For eastern languages on a western Windows edition, 
> you generally need to change your console settings to make it use another 
> font than the default one, just so that it can draw the needed glyphs.  But 
> the basic thing to do is get the program running in the console (here we are 
> talking shell.c - sqlite3.exe) to output Windows wide-chars using the 
> function WriteConsoleW(). And use ReadConsoleW() to read wide-chars chunks 
> from the console input, before converting internally to UTF-8 or whatever 
> wanted.

... assuming of course that the locale was using a font that supported
the character set for that area. This was true for our purposes by
default, as we weren't expecting English speaking customers to need to
see Asian languages.

> Sqlite3 shell.c when patched that way is as pleasant to use on Windows 
> console as it can be on a modern Linux or macOS.
>
> Input files feeded to sqlite3.exe need to be in UTF-8, as well as output sent 
> by sqlite3.exe will be: that part is perfectly OK today in sqlite3.exe. Only 
> the keyboard reading and console output writing lacks a little.

Agreed.

>> but geez, how much effort can it be to make it Unicode-friendly?
>
> To further comment on a more general plane than the sqlite3.exe, the issue is 
> deeper in Windows than in its console. Once upon a time (!), they made the 
> choice of 16 bits per characters encoding as the *right* way (their right 
> way!) to do Unicode. It took time for this to evolve, recognizing the need 
> for multi-16 bits words encoding (UTF-16), so they could have chosen UTF-8 
> from day one, but that was not what history recorded. Later UTF-8 got *some* 
> support in the OS (through conversion functions). But never UTF-8 was raised 
> to full citizenship.  There is even a CHCP 65001 to set the 'codepage' to 
> UTF-8. It works partly in some circumstances, but is far from being 'right'. 
> No matter what you would do, there is no way for any file I/O primitive of 
> the OS to take an UTF-8 string as a filename. And this extend to the 
> C-library on Windows platform. The only unicode support is to pass a UTF-16 
> filename through functions ending with a W in the name. Those 'ansi' 
> functions, ending with an A in the name, are merely wrappers converting to 
> the wide chars versions.  There have been numerous requests to Microsoft to 
> let people and developers set the ANSI codepage to UTF-8 so that the file I/O 
> functions taking a narrow char filename string can interpret it as UTF-8. 
> Some are still waiting for that day to come, others use the W-side of things, 
> complicating portability of their codebase. :)

Windows NT was released in 1993. It had been in development for years.
It decided Unicode for I18N/L10N/W6R (WhateveR) purposes was better
than a ton of different code pages. At the point Microsoft committed
to Unicode, it was a two byte / sixteen bit encoding. There was no
UTF-8. There was no UTF-16. Other than endian issues, there was
nothing to worry about. Win32 was an "all new" API.

POSIX people didn't want to re-write the entire API to support 16 bit
characters, 

Re: [sqlite] unusual but trivially reproducible bug

[Scott Robison]

Not a bug. Instead of a keyword, you've defined an alias for the table
named "limit1".

On Jun 19, 2017 4:00 AM, "Robert Cousins"  wrote:

> Summary:
>   Leaving out the space after the word 'limit' causes the limit
> clause to be ignored.
> I've reproduced it on version 3.19.2 2017-05-25 16:50:27
> edb4e819b0c058c7d74d27ebd14cc5ceb2bad6a6144a486a970182b7afe3f8b9
> A sample output is below.
> On one hand, this is a failure to catch a syntax error. On the other
> hand, the fact that it isn't caught is perhaps problematic and indicates
> an underlying parser issue.
> Thanx
>
> sqlite> create table foo(id Integer not null primary key, bar int);
> sqlite> insert into foo values (NULL,1),(NULL,2),(NULL,3);
> sqlite> select * from foo limit1;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit2;
> id  bar
> --  --
> 1   1
> 2   2
> 3   3
> sqlite> select * from foo limit 1;
> id  bar
> --  --
> 1   1
> sqlite> select * from foo limit 2;
> id  bar
> --  --
> 1   1
> 2   2
> 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

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 8:26 PM, "Keith Medcalf"  wrote:


Additionally, declaring NOT NULL or NULL is ignored.  CHECK constraints are
honoured.  DEFAULT values are ignored.

so CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000) DEFAULT (-1));
&  CREATE TABLE x(id INTEGER NULL PRIMARY KEY CHECK (id>1000));
is CREATE TABLE x(id INTEGER PRIMARY KEY CHECK(id>1000));

This is because an "INTEGER PRIMARY KEY" has a computed default that you
cannot override, and the rowid must always be always NOT NULL ...


Thanks for the info. I'm assuming that the order of constraints is
unimportant so check could come before PK.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Jun 12, 2017 5:43 PM, "Richard Hipp" <d...@sqlite.org> wrote:

On 6/13/17, Scott Robison <sc...@casaderobison.com> wrote:
>
> Is it fair to say that the rowid aliasing behavior does not require
> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
> that order as the "type") as long as the type is INTEGER and the
> constraint PRIMARY KEY appears somewhere in the column's constraint
> list?

See https://www.sqlite.org/lang_createtable.html#rowid

   CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Columns x *not* a ROWID.  Life is complicated.  Sorry for the mess.
But we have to preserve backwards compatibility - even with bugs like
this.


One,  no problem.

Two, is "INTEGER PRIMARY KEY ASC" the same? I will try it tomorrow.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INTEGER PRIMARY KEY

[Scott Robison]

On Mon, Jun 12, 2017 at 4:20 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>
> On 12 Jun 2017, at 11:01pm, Scott Robison <sc...@casaderobison.com> wrote:
>
>> Is it fair to say that the rowid aliasing behavior does not require
>> (by design) the incantation "INTEGER PRIMARY KEY" (all three words in
>> that order as the "type") as long as the type is INTEGER and the
>> constraint PRIMARY KEY appears somewhere in the column's constraint
>> list?
>
> FAQ #1 is specifically worded …
>
> <https://www.sqlite.org/faq.html#q1>
>
> But you’re being very specific.  I think you’re right but someone who has 
> read the source code might know otherwise.

The question was raised at work so I got curious about it. It makes
sense to me that the rowid aliasing behavior would only be determined
after lexing and parsing the SQL statement, but the documentation
statements of "INTEGER PRIMARY KEY" being necessary led some people
(understandably) down a path thinking you needed those three words in
that order (perhaps).

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

[sqlite] INTEGER PRIMARY KEY

[Scott Robison]

This is as much out of curiosity as anything. I know that to get the
rowid aliasing behavior for a table one must define the column type as
INTEGER and using the constraint PRIMARY KEY. Something like:

CREATE TABLE A(B INTEGER PRIMARY KEY);

In testing this afternoon I was curious if I could give the constraint a name:

CREATE TABLE A(B INTEGER CONSTRAINT B_PK PRIMARY KEY);

I can, and it is still an alias of the rowid. If I change the type to
INT then it is not an alias. This all makes sense.

If I insert NOT NULL between the type and PK constraint, it still is
an alias (as long as the type is INTEGER).

Is it fair to say that the rowid aliasing behavior does not require
(by design) the incantation "INTEGER PRIMARY KEY" (all three words in
that order as the "type") as long as the type is INTEGER and the
constraint PRIMARY KEY appears somewhere in the column's constraint
list?
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: SELECT Max(IndexedField) doesn't use partial index

[Scott Robison]

On May 9, 2017 9:07 PM, "jose isaias cabrera" <jic...@barrioinvi.net> wrote:


Scott Robison wrote...

On Mon, May 8, 2017 at 11:40 AM, Paul van Helden <p...@planetgis.co.za>
wrote:

> Hi,
>>
>> I use a lot of indexes on fields that typically contain lots of NULLs, so
>> the WHERE NOT NULL partial indexing seems very useful.
>>
>> However when I compare the "EXPLAIN QUERY PLAN" with a partial index vs. a
>> normal index, SQLite won't use the index to find Max(IndexedField) if it
>> is
>> a partial index.
>>
>> Is this an optimization opportunity? I understand that other kinds of
>> partial indexes might exclude possible Min or Max values, but a NOT NULL
>> index would be fine for mins, maxes and most other things?
>>
>
This may be an optimization opportunity, but you can easily force the
> use of that index by stating WHERE NOT NULL in the select query
> itself. For example:
>
> CREATE TABLE a(b);
> CREATE INDEX ab on a(b) where b is not null;
>
> sqlite> explain query plan select max(b) from a;
> 0|0|0|SEARCH TABLE a
> sqlite> explain query plan select max(b) from a where b is not null;
> 0|0|0|SEARCH TABLE a USING COVERING INDEX ab
>

So, a newbie question here...  What happens if you have,

INDEX ab on a(b);

and no "where b is not null".  Doesn't the INDEX takes care of all those
nulls and the same outcome would result?

thanks.


Yes, it might just need to walk through a larger data set. If nulls are
rare, there probably wouldn't be much of a difference (not in front of a
computer to check). If the column was mostly nulls, and a lot of rows, it
might be a lot faster.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users