Re: [sqlite] Things you shouldn't assume when you store names

[Richard Damon]

On 11/12/19 2:42 PM, Michael Tiernan wrote:
> On 11/10/19 1:21 AM, Gary R. Schmidt wrote:
>> So what happens when someone from a family who only uses first- and
>> last-names moves to Kansas?
>>
>> Do they have to make up a middle-name so that he idiots can fill out
>> the forms? 
>
> I am most definitely not going to take one side or the other. My only
> suggestion is for anyone to see the depth and complexity of the
> problem, get involved in genealogy. You'll want to scream very
> quickly. :) 

Yep, I AM involved in genealogy, and there you not only want to just
record the person's name, but you have a real reason to want to group
people by 'last name' as that is one hint that they might be related,
but there are all sorts of rules in different cultures about family
names (can't really call them 'last names' as they aren't always last,
and 'Surnames' aren't accurate either)

-- 
Richard Damon

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

Re: [sqlite] Things you shouldn't assume when you store names

[Michael Tiernan]

On 11/10/19 1:21 AM, Gary R. Schmidt wrote:
So what happens when someone from a family who only uses first- and 
last-names moves to Kansas?


Do they have to make up a middle-name so that he idiots can fill out 
the forms? 


I am most definitely not going to take one side or the other. My only 
suggestion is for anyone to see the depth and complexity of the problem, 
get involved in genealogy. You'll want to scream very quickly. :)


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

Re: [sqlite] JSON_EXTRACT does not work with non-BMP characters

[Serhiy Storchaka]

10.11.19 19:54, Richard Hipp пише:

On 11/8/19, Serhiy Storchaka  wrote:

JSON_EXTRACT [1] produces gibberish for encoded non-BMP characters.

Thanks for the bug report and test case.

I checked in candidate fix here:
https://www.sqlite.org/src/timeline?c=51027f08c0478f1b

I need to do additional testing, and due to conflicting obligations,
I'm not sure when I will have a chance to do that.  So the issue
should remain open for now.



Thank you for your quick response and fix. I checked the code and it 
looks completely correct to me.



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

[sqlite] Checkin df51ae19c1 pager.c line 4866 nUri undefined

[Keith Medcalf]

nUri is only defined if SQLITE_DEBUG is defined.  Should this line be #ifndef 
SQLITE_DEBUG?


-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Why does Python turn UTF8 data in SQLite into Latin1?

[Winfried]

Sorry. Windows 7 32 bits US + with French locale + Python 3.7.0.

Turns out it had nothing to do with SQLite, and everything to do with
Python's write(), which uses the default locale, so the following is
required to output data as UTF-8 instead of cp1252 used on this computer:


testoutput = open("check.from.Python.txt", "w",,encoding='UTF-8')


Thank you.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Custom window functions vs builtin

[Merijn Verstraaten]

> On 12 Nov 2019, at 14:58, Dan Kennedy  wrote:
> 
> No, they don't need to handle that. The rows will always be added/removed in 
> ORDER BY order for all window functions (and will be removed in the same 
> order in which they were added if the ORDER BY order is ambiguous).

Ah, excellent! This dramatically simplifies my life :)

Could I suggest adding a note to this extent to 
https://www.sqlite.org/windowfunctions.html#udfwinfunc for the next paranoid 
person?

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

Re: [sqlite] WAL2 mode

[Dan Kennedy]

On 12/11/62 19:00, Simon Slavin wrote:

On 12 Nov 2019, at 10:06am, Dan Kennedy  wrote:


This branch might interest you:

   https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2

" In wal2 mode, the system uses two wal files instead of one. The files are named "-wal" 
and "-wal2""

Could this be changed to -wal1 and -wal2 ?  Or any other suffixes that aren't 
used by a different mode ?

This is to make crash/corruption diagnostics simpler.  At the moment, if the 
database file is so corrupt it can't be opened by SQLite, just by looking at 
the files in the directory I can tell a lot about what journal mode the 
database was in and what was being done, and what the user did to try to 
restore a backup.

But users do all sorts of weird things to try to recover from crashes, 
including restoring a database, sometimes in a different journal mode, but 
leaving journal files in place.  Seeing whether there's a -wal file and/or a 
-wal1 file, and comparing the changedates on the files, will give me better 
clues about what was done.  It means I can get further in figuring out what was 
going on before hexdumping the files concerned.


Fair point.

I think it reuse *-wal in order to avoid an extra call to access() when 
opening a read-transaction in rollback mode. There might be other 
reasons too. It's only a branch for now - this is something to consider 
if it ever gets rolled into the main version though.


Dan.



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

Re: [sqlite] Custom window functions vs builtin

[Dan Kennedy]

On 12/11/62 18:50, Merijn Verstraaten wrote:

I already asked this question without an answer, but as it hidden somewhere 
nested deeply in another thread I think it may have simply gone under the 
radar. Apologies for the duplication if you already saw it!

How is the behaviour of (custom) window functions defined? Specifically, in the 
presence of an ORDER BY on the window.

The functionality of row_number/rank/dense_rank seems to require that xStep and 
xInverse are called on rows in the order specified by ORDER BY. And, indeed, 
the implementation of row_number() in the sqlite source seems to rely on being 
called in the same order as ORDER BY, but at the same time the documentation 
states:

"the built-in window functions, however, require special-case handling in the query 
planner and hence new window functions that exhibit the exceptional properties found in 
the built-in window functions cannot be added by the application."

So does this indeed mean that these builtin ones are handled specially and 
other windows functions have to accept/deal with having their window arguments 
added/removed in an arbitrary order?


No, they don't need to handle that. The rows will always be 
added/removed in ORDER BY order for all window functions (and will be 
removed in the same order in which they were added if the ORDER BY order 
is ambiguous).


There is some special handling for the various built-in window functions 
though. For example, most of them ignore the window type. You can't 
implement percent_rank(), cume_dist() or ntile() without knowing the 
number of rows in the partition before returning any values, so that 
requires special handling as well. There are probably other things too...


Dan.


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

[sqlite] Wrong initialization for pPager->zJournal in sqlite3PagerOpen

[Mariano Quesada Morales]

The zJournal path must have at least 2 null characters at the end of the string 
or an "Acess violation reading" exception can be thrown. With the current code 
the second null characters is overridden by these instructions:
...
pPager->zWal = >zJournal[nPathname+8+1];
memcpy(pPager->zWal, zPathname, nPathname);
...

I think pPager->zWal should start one position further: pPager->zWal = 
>zJournal[nPathname+8+2];


Regards

Mariano Quesada

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

Re: [sqlite] database disk image is malformed

[Andreas Kretzer]

Just to make sure, you didn't oversee that (like I was ...):
Daemonizing a process is also a fork() - and this invalidates
your connection!

I used the daemon() function in my program (together with a
few other processes) that used a DB connection. I opened
this connection _before_ I called daemon() and everything seemed
right. But as soon, as the other processes terminated, the lock
and WAL files went away, and everything I did with the DB didn't
reach the actual files. After that, the DB image was damaged.

Andreas

Am 11.11.2019 um 18:37 schrieb Simon Slavin:

On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:


The main process first opens the databases and checks that their
version matches that of the software and if not, the databases are
closed and initialized by running a script.

After closing the databases, main process forks the children and
all processes (including main process) open the databases and use
their own connections.

What I was trying to ask was this:  If any of the children dies
(a bug in the code), main process will restart the child.  At
this point, the main process has the databases open, so the new
child receives the connections as well.  What should I do now?

Okay, that gives us enough information to work with.

The conservative way to do it is to have the main process close the connection 
before forking and open it again.  Then, of course, the child processes make 
their own connections.

But I don't think that's necessary.  A child process can have access to the 
main process' database connection but ignore it.  So I think the main process 
can fork without closing its connection.  Then each child can never use that 
one but instead make its own.

Of course, every one of these connections needs to set a timeout.  And every 
call to the SQLite3 library needs to check its result code and make sure it is 
getting SQLITE_OK (or, for queries, SQLITE_DONE etc.).


Should the child close the databases before opening them again?
Will this close the databases for the main process as well?

As you suspected, closing the connection releases both memory structures and 
file handles.  Anything that tries to use that connection will then fail 
because it has no idea what it's talking to.

What puzzles me is this: you're getting "database malformed" and nothing you've 
described justifies this.  Assuming that this isn't just one old database which is 
genuinely corrupt, but that you are using a fresh uncorrupt database each time, you seem 
to have a genuine bug in your code.

This happens mostly because something is stomping on the memory assigned to a 
connection.  In your case, this probably means something is stomping on the 
memory assigned to one of the child processes.

So, first write yourself a quick script to use the shell tool to check the 
database for corruption.  Then run that, even while your program is running, 
and see if you can figure out whether your database really is corrupt or 
whether your program is getting spurious error messages.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--

Mit freundlichen Grüßen
Andreas Kretzer

ETB Electronic Team
Beratungs- und Vertriebs GmbH

Berliner Straße 8a
15537 Erkner

FON   +49 3362 889349-12
FAX   +49 3362 889349-23

email: a.kret...@etb-electronic.de

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee
Geschäftsführer: Wolfgang A. Runge, Marco Runge, Jürgen Gentzsch


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

Re: [sqlite] database disk image is malformed

[Jukka Marin]

On Mon, Nov 11, 2019 at 05:37:37PM +, Simon Slavin wrote:
> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
> 
> > The main process first opens the databases and checks that their
> > version matches that of the software and if not, the databases are
> > closed and initialized by running a script.
> > 
> > After closing the databases, main process forks the children and
> > all processes (including main process) open the databases and use
> > their own connections.
> > 
> > What I was trying to ask was this:  If any of the children dies
> > (a bug in the code), main process will restart the child.  At
> > this point, the main process has the databases open, so the new
> > child receives the connections as well.  What should I do now?
> 
> Okay, that gives us enough information to work with.
> 
> The conservative way to do it is to have the main process close the 
> connection before forking and open it again.  Then, of course, the child 
> processes make their own connections.
> 
> But I don't think that's necessary.  A child process can have access to the 
> main process' database connection but ignore it.  So I think the main process 
> can fork without closing its connection.  Then each child can never use that 
> one but instead make its own.

Okay, that's what I was hoping for.

> Of course, every one of these connections needs to set a timeout.  And every 
> call to the SQLite3 library needs to check its result code and make sure it 
> is getting SQLITE_OK (or, for queries, SQLITE_DONE etc.).

Yes, I'm doing all this,

> > Should the child close the databases before opening them again?
> > Will this close the databases for the main process as well?
> 
> As you suspected, closing the connection releases both memory structures and 
> file handles.  Anything that tries to use that connection will then fail 
> because it has no idea what it's talking to.

Ok.

> What puzzles me is this: you're getting "database malformed" and nothing 
> you've described justifies this.  Assuming that this isn't just one old 
> database which is genuinely corrupt, but that you are using a fresh uncorrupt 
> database each time, you seem to have a genuine bug in your code.

That was happening before I changed my code to open the databases in
the children.  Originally, only main process opened the databases and
the children "inherited" the connections.  I was wondering if this was
okay, but I didn't see a warning in the SQLite docs, so.. I never found
the "how to corrupt your database" manual on my own ;-)

I came back to the mailing list when I noticed the above "problem" of
a child dying and needing to respawn it while the main process already
has the databases open.

So I guess it's safe now that all the children open the databases by
themselves.

Thanks to all who responded!

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

Re: [sqlite] Things you shouldn't assume when you store names

[Amit Chaudhuri]

Ha ha ha - Oh Simon.what *have* you done? [Apologies for the noise
- could not resist this one.]

On Sat, 9 Nov 2019 at 19:26, Simon Slavin  wrote:
>
> Since I don't see many posts yet this weekend, please excuse one of mine 
> which isn't exactly on charter.  Feel free to argue me out of posting in 
> personal (offlist) email.
>
> In a previous job I got to see databases made up by all sorts of other people 
> and organisations.  Every time I saw a field called 'firstname' or 'second 
> name' or 'surname' or 'familyname' I groaned.  So I was nodding along as I 
> read this:
>
> 
>
> I think this one is unusually well-written.
>
> In case you want to know how best to handle personal names, the current 
> consensus seems to be to use a single field containing the whole name, which 
> can be searched by substring.  Computer systems for places with non-Roman 
> character sets sometimes use two fields: name in local characters (Chinese, 
> Devanagari, etc.) and name in Roman characters.
>
> Also note that current privacy legislation in the US and EU means you are not 
> allowed to ask for anything like 'full legal name' unless you cannot run your 
> business without it.  Ask them for their name, and store what they tell you, 
> with the words in the order they gave them.  If you need to sort people in 
> name order (think very hard about why, first), create a field called 'sort 
> order' and populate it yourself.  Sorting is your problem, not that of the 
> people you're sorting.
>
> Part of a continuing series including falsehoods about dates, times, places, 
> street addresses, gender, relations, phone numbers, taxes, and amounts of 
> money.
>
> Good luck, and watch your back.
> ___
> 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] WAL2 mode

[Simon Slavin]

On 12 Nov 2019, at 10:06am, Dan Kennedy  wrote:

> This branch might interest you:
> 
>   https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2

" In wal2 mode, the system uses two wal files instead of one. The files are 
named "-wal" and "-wal2" "

Could this be changed to -wal1 and -wal2 ?  Or any other suffixes that aren't 
used by a different mode ?

This is to make crash/corruption diagnostics simpler.  At the moment, if the 
database file is so corrupt it can't be opened by SQLite, just by looking at 
the files in the directory I can tell a lot about what journal mode the 
database was in and what was being done, and what the user did to try to 
restore a backup.

But users do all sorts of weird things to try to recover from crashes, 
including restoring a database, sometimes in a different journal mode, but 
leaving journal files in place.  Seeing whether there's a -wal file and/or a 
-wal1 file, and comparing the changedates on the files, will give me better 
clues about what was done.  It means I can get further in figuring out what was 
going on before hexdumping the files concerned.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Custom window functions vs builtin

[Merijn Verstraaten]

I already asked this question without an answer, but as it hidden somewhere 
nested deeply in another thread I think it may have simply gone under the 
radar. Apologies for the duplication if you already saw it!

How is the behaviour of (custom) window functions defined? Specifically, in the 
presence of an ORDER BY on the window.

The functionality of row_number/rank/dense_rank seems to require that xStep and 
xInverse are called on rows in the order specified by ORDER BY. And, indeed, 
the implementation of row_number() in the sqlite source seems to rely on being 
called in the same order as ORDER BY, but at the same time the documentation 
states:

"the built-in window functions, however, require special-case handling in the 
query planner and hence new window functions that exhibit the exceptional 
properties found in the built-in window functions cannot be added by the 
application."

So does this indeed mean that these builtin ones are handled specially and 
other windows functions have to accept/deal with having their window arguments 
added/removed in an arbitrary order?

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

Re: [sqlite] proposal for write-lock on "commit" rather than "begin transaction"

[Dan Kennedy]

On 25/10/62 23:07, Brannon King wrote:

This is a request for a small change to the handling of multiple
connections. I think it would significantly enhance the usefulness there
via allowing multiple "views" of the data.

Consider that I have two simultaneous connections to one file, named Con1
and Con2. They could be in one process or one thread -- that's irrelevant.
Either one may write to the DB; we don't know yet. For starters, assume
that their journal mode is MEMORY.

Both connections begin with "begin transaction". Already I'm dead in the
water; one of those will fail presently with "database is locked". But it
doesn't need to be that way! Each connection can have its own journal file,
especially if it's in memory. Once one connection commits, the other
connection will no longer be allowed to commit. It will be forced to
rollback (or perhaps rebase if there are no conflicts).

Multiple WAL files could be supported in a similar fashion; they just need
some kind of unique naming scheme. For recovery, the user would be prompted
to select one or none. It doesn't seem that far from Sqlite's current
behavior. Thoughts?


This branch might interest you:

  https://www.sqlite.org/src/timeline?r=begin-concurrent-pnu-wal2

The "BEGIN CONCURRENT" idea is that two connections may concurrently 
have independent write transactions based on optimistic read/write page 
locking.


Dan.





~Brannon
___
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