Re: [sqlite] disable file locking mechanism over the network

[Roman Fleysher]

I like the idea of FLoM-based VFS !!

I am not sure I am up to actually doing it, but this should be as good a 
locking mechanism as any other.

Thank you, Keith.

Roman


From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Keith Medcalf [kmedc...@dessus.com]
Sent: Saturday, September 28, 2019 12:48 AM
To: SQLite mailing list
Subject: Re: [sqlite] disable file locking mechanism over the network

On Friday, 27 September, 2019 22:11, Roman Fleysher 
 wrote:

>Based on the link you provide, it looks like I need unix-none VFS and
>specify it as:

>ATTACH 'file:demo2.db?vfs=unix-none' AS demo2;

Yup.  I think you can also do that when opening the file on the sqlite3_open_v2 
call by using the same URI format.  I think using nolock=1 without specifying a 
VFS does the same thing ... ie, using a filename of 'file:demo2.db?nolock=1'

>FLoM will be ensuring sequential access. The journaling, as I understand,
>is independent of locking and will still work. That is robustness to
>crashes will remain.  unix-none just disables file lock requests, which
>are the cause of the overhead. Is this correct?

I haven't looked at the actual code for the VFS but that is my impression.  The 
description for using the nolock=1 URI parameter says that you must have the 
application "serialize writes" in order to maintain consistency and if you are 
using FLoM to synchronize application access to the sqlite3 api, I would think 
that would cover it.  The nolock=1 specifically says that it disables "file 
locking operations" only but maintains the change detection logic which should 
presumably maintain cache consistency (I think the purpose of the nolock=1 is 
simply to allow "generic" vfs selection, by appending -none to the default vfs 
name -- ie, so that if you are running on Windows it uses win32-none instead of 
win32, and on unix uses unix-none instead of unix).

As an aside, it might be interesting to derive another VFS from the standard 
"unix" VFS which just replaces the locking operations with synchronization 
using FLoM, so that nothing special at all is required at the application level 
other than selecting a different VFS layer.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-usersdata=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C3e48038bb3254b2b636608d743cf2ce2%7C04c70eb48f2648079934e02e89266ad0%7C1%7C1%7C637052429406868157sdata=iJc7whQWoukCo1QUL8yAL73j5wcG5e%2Bw8TFU2RkHY1U%3Dreserved=0
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Import XLS file?

[sky5walk]

If you are uncomfortable with exporting to csv and importing csv from the
command line,
you could install DB Browser for SQLite,
https://github.com/sqlitebrowser/sqlitebrowser.
Then manually build your SQLite tables and cut and paste into them from the
spreadsheet.

On Tue, Oct 1, 2019 at 8:58 PM Jake Thaw  wrote:

> You might like to consider the xlsx virtual table I wrote last year:
>
> https://github.com/jakethaw/xlsx_vtab
>
> Note that this only works for xlsx files, and may have unexpected
> results in some circumstances (e.g. merged cells).
>
> On Wed, Oct 2, 2019 at 4:39 AM Winfried  wrote:
> >
> > Hello,
> >
> > I need to import an XLS file into SQLite.
> >
> > The  web site    doesn't seem to show
> how, and
> > searching the archives  here 
>  didn't
> > help.
> >
> > FWIW, the data are in the file's first sheet, while other sheets only
> > contain text infos about the data.
> >
> > 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
> ___
> 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] [EXTERNAL] Re: Newbie Issues with COLLATE

[Hick Gunter]

I'm guessing that LOCALE_NOCASE will probably be causing things that collate 
distinct in NOCASE to collate equal, so the risk of breaking UNIQUE constraints 
seems rather small

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Richard Hipp
Gesendet: Dienstag, 01. Oktober 2019 19:41
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Newbie Issues with COLLATE

On 10/1/19, Jim and Carol Ingram  wrote:
> QUESTION
> ONE:  Are these "LOCALE_..." collations custom collations developed by
> the software programmers,

Yes.  The application is using the sqlite3_create_collation() API
(https://www.sqlite.org/c3ref/create_collation.html) to create a new collating 
sequence that they are calling "LOCALE_NOCASE".  We can guess at what that 
collating sequence does from its name, but without seeing the code, we don't 
really know.

>
> I have noted by trial and error that modifying the original query's
> last line to include COLLATE NOCASE ("group by k.Keyword COLLATE
> NOCASE;") makes the query work against the original Keywords table
> without error.  I suspect this is the correct way to address the error
> message rather than my admittedly messy and time-consuming workaround.
> QUESTION TWO:  Can anyone verify that this is indeed the acceptable
> way to address the error message, or provide a more acceptable way to do it?

That seems like a reasonable approach to me!

Another thing to consider, depending on how much data there is, is to run the 
".dump" command to convert the whole database into a big pile of SQL.  Then 
edit the SQL to change LOCALE_NOCASE into just NOCASE, and reimport it into a 
new SQLite database.  Then all of your queries will work correctly.

Or, you could set "PRAGMA writable_schema=ON" and then do an UPDATE statement 
on the sqlite_master table to actually change the text of the CREATE TABLE 
statement:

PRAGMA writable_schema=on;
UPDATE sqlite_master SET sql=replace(sql,'LOCALE_NOCASE','NOCASE')
  WHERE name LIKE 'keywords';

Then exit the command-line tool and reopen and type "REINDEX".  Then you should 
be good to go.  Warning:  Make a backup copy first, as things might to wrong.  
In particular, the change from LOCALE_NOCASE to just NOCASE might possibly 
cause the UNIQUE constraint to start failing in one or more cases.  (Unlikely, 
but possible.)  So be prepared to work around such difficulties.

> And finally, QUESTION THREE: Can anyone point me to a thorough and
> detailed tutorial of the whole SQLite COLLATE subject, including
> creation of custom collations and loading them into the SQLite
> command-line executable if possible?  The documentation seems really thin in 
> this area!
>

The https://www.sqlite.org/c3ref/create_collation.html document is about all we 
have.  There might be more information in some of the books about SQLite.

--
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


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

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users