Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

[Clemens Ladisch]

Yuri wrote:
> I noticed that my DB import process is much slower when run on the DB on 
> disk, vs. in memory.

It's possible that you forgot to wrap a single transaction around all changes.
Otherwise, you get an automatic transaction for every command, which requires
disk synchronization every time.


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

Re: [sqlite] 64-column covering index limit clarification

[Shawn Wagner]

Okay, next question: Does the query you're testing this with obey the
requirements needed to use the partial index (see
https://sqlite.org/partialindex.html#queries_using_partial_indexes)?

On Thu, Oct 18, 2018 at 9:43 PM Deon Brewis  wrote:

> Yes a non partial index beyond column 64 works as I would expect.
>
> - Deon
>
> > On Oct 18, 2018, at 12:34 PM, Shawn Wagner 
> wrote:
> >
> > Does a normal non-partial index make a difference in the query plan?
> >
> >> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:
> >>
> >> Hi,
> >>
> >> I seem to have run into a limit where SQLITE doesn't use an index
> >> correctly if an indexed column is over the 64th column in the table.
> It's a
> >> partial index like:
> >>
> >> CREATE INDEX idx ON
> >>  table(A, B DESC, C, D)
> >>  WHERE A > 0
> >>
> >> Where A and B are columns 70 and 72 on 'table'.
> >>
> >> I know about the 64-column limitation for covering indexes:
> >>
> >>
> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
> >>
> >> However, this isn't a covering index, it's a partial index. But it seems
> >> to run into the same limit. Even if I forced in the index into a query
> it
> >> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY
> A,
> >> B DESC" query. After I re-ordered the table, it magically started
> working.
> >>
> >> Is there any better documentation anywhere (other than the archive) of
> all
> >> of the cases to which the 64-column limit applies?
> >>
> >> - Deon
> >>
> >> ___
> >> 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] Why operations with in-memory DB are much faster than with on-disk DB?

[Simon Slavin]

On 19 Oct 2018, at 5:55am, Yuri  wrote:

> I noticed that my DB import process is much slower when run on the DB on 
> disk, vs. in memory. It reads files and runs a massive amount of 
> inserts/updates.

Memory access is much faster than disk access.  If you're using actual spinning 
hard disks, your computer has to wait for the disk to be spun to the right 
place before it can read or write data.  That could take 1/5000th of a second.  
By contrast, memory access is almost instant.

> Why is this? Is there any way to speed it with disk without using in-memory 
> DB?

If you are importing into an empty database and you don't care about corruption 
due to power-failure, it would be safe to do

PRAGMA synchronous = OFF;
PRAGMA journal_mode = OFF;

before the import.  If you do this, make sure you close the file after the 
import.  When it is reopened these settings will have been reset to the 
default.  For an explanation of what these do, see



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

[sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

[Yuri]

I noticed that my DB import process is much slower when run on the DB on 
disk, vs. in memory. It reads files and runs a massive amount of 
inserts/updates.



Why is this? Is there any way to speed it with disk without using 
in-memory DB?



Yuri


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

Re: [sqlite] 64-column covering index limit clarification

[Deon Brewis]

Yes a non partial index beyond column 64 works as I would expect.

- Deon

> On Oct 18, 2018, at 12:34 PM, Shawn Wagner  wrote:
> 
> Does a normal non-partial index make a difference in the query plan?
> 
>> On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:
>> 
>> Hi,
>> 
>> I seem to have run into a limit where SQLITE doesn't use an index
>> correctly if an indexed column is over the 64th column in the table. It's a
>> partial index like:
>> 
>> CREATE INDEX idx ON
>>  table(A, B DESC, C, D)
>>  WHERE A > 0
>> 
>> Where A and B are columns 70 and 72 on 'table'.
>> 
>> I know about the 64-column limitation for covering indexes:
>> 
>> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
>> 
>> However, this isn't a covering index, it's a partial index. But it seems
>> to run into the same limit. Even if I forced in the index into a query it
>> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A,
>> B DESC" query. After I re-ordered the table, it magically started working.
>> 
>> Is there any better documentation anywhere (other than the archive) of all
>> of the cases to which the 64-column limit applies?
>> 
>> - Deon
>> 
>> ___
>> 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] 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] Using SQLite in C# without System.Data.SQLite

[Simon Slavin]

On 19 Oct 2018, at 2:23am, Larry Brasfield  wrote:

> Jordy Deweer asks about alternatives to System.Data.SQLite because: “I run 
> into errors a lot, using the System.Data.SQLite libraries. It easily crashes, 
> trhows exceptions and similar issues...”.

System.Data.SQLite is generally considered to be stable and usable.  It's 
officially supported and its maintainer reads this list and responds to bug 
reports.

If you're getting buggy behaviour out of it, there's a big chance that 
something else in your project is the cause.  Please post details of your 
development environment and a bug that's simple to reproduce and we'll talk 
about it.

But first, please make sure you're detecting and handling errors however your 
language/environment expect you to, because a lot of errors result in using 
another SQLite call after one has reported an error.

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

[Larry Brasfield]

Jordy Deweer asks about alternatives to System.Data.SQLite because: “I run into 
errors a lot, using the System.Data.SQLite libraries. It easily crashes, trhows 
exceptions and similar issues...”.

I’ve used that module for several projects and found it to be generally robust. 
I have also seen that the developers working on it are responsive here (in this 
forum) and attend to bug reports promptly. It is a quality offering, IMO.

I venture that if you are seeing exceptions thrown from that module, they 
likely signify usage contrary to the intention underlying the API design.  
“Crashes”, (if the term has its usual meanings among SDEs), tend to indicate a 
bug somewhere. If they can be properly attributed to System.Data.SQLite, (with 
a simple C# test case), I am sure that the modules developers would jump on a 
bug report tendered here. I’m not sure what “similar issues” would be, but I 
imagine that if you brought the difficulties here, you would find willing help 
to resolve them, as I have seen often.



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

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Jens Alfke]

> On Oct 18, 2018, at 11:50 AM, Maziar Parsijani  
> wrote:
> 
> It will crash or exit the program.

You’re not handling errors correctly, then. I think you said you’re using 
Python? Then the query will probably throw a Python exception; you need to 
catch that and handle it appropriately.

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

Re: [sqlite] How to round to an Integer

[Keith Medcalf]

On Thursday, 18 October, 2018 14:13, Richard Hipp  wrote:

>On 10/18/18, John Harney  wrote:

>> Recently figured this out.  Seems to work fine

>> trim(trim(round(1.111,0),'0'),'.')   = 1

>CAST(1.111 AS integer)

That should be CAST(round(x,0) as integer) if you want the rounded result as an 
actual integer (the bare CAST truncates).  Note however that the default 
builtin round() function does "round half away from 0" and not "round half to 
even" rounding, so beware of using the result in further calculations ...

sqlite> select cast(1.111 as integer);
1
sqlite> select cast(1.5 as integer);
1
sqlite> select cast(1.6 as integer);
1
sqlite> select cast(round(1.6,0) as integer);
2
sqlite> select cast(round(1.5,0) as integer);
2
sqlite> select cast(round(2.5,0) as integer);
3
sqlite> select cast(roundhe(2.5,0) as integer);
2

---
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] Bug report: bug in datetime conversion function

[David Raymond]

You have to look at the original dates. The date you're giving it for the first 
one is Feb 29th on a leap year. So 31 years from Feb 29th goes to Feb 29th on a 
NON-leap year, and thus gets rolled over to March 1st. For the second one 
you're saying 31 years from March 1st, which also lands on March 1st.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fábio Pfeifer
Sent: Thursday, October 18, 2018 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Bug report: bug in datetime conversion function

Hello,

When working with Apple iOS databases, I found something strange when
dealing with dates. Apple iOS databases store dates as seconds from
2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29
there is a offset of one day if '31 years' modifier is used.
To reproduce, execute the following query:

select datetime(446860801, 'unixepoch', '31 years') as date1,
   datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2,
   datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3

date2 should be 1 day after date1, but shows as the same date!

Best regards,
Fábio Pfeifer
___
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] Hot-Journal with VFS

[Simon Slavin]

On 18 Oct 2018, at 3:27pm, Bob schwanzer  wrote:

> DB is
> opened by 10-20 processes each of which can have multiple threads.

What OS are you using ?
What programming language are you using ?
Are you calling the SQLite C library directly or using a shim ?

Does your program close each database as it is finished with it ?
Does the result code from _close() come back as SQLITE_OK ?

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

Re: [sqlite] Bug report: bug in datetime conversion function

[Simon Slavin]

On 18 Oct 2018, at 2:28pm, Fábio Pfeifer  wrote:

> When working with Apple iOS databases, I found something strange when dealing 
> with dates.

I suspect you are you are referring to a library routine which is used by lots 
of iOS software, but can you give us a specific App which exhibits this bug, 
and an easy way to find a database file from it ?

> Apple iOS databases store dates as seconds from
> 2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29 
> there is a offset of one day if '31 years' modifier is used.

I get the following:

SQLite version 3.24.0 2018-06-04 14:10:15
sqlite> select datetime(446860801, 'unixepoch', '31 years') as date1;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3;
2015-03-02 00:00:01
sqlite> 

Just for reference, 2015 was not a leap year, and 2016 was a leap year.

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

Re: [sqlite] How to round to an Integer

[Richard Hipp]

On 10/18/18, John Harney  wrote:
> Recently figured this out.  Seems to work fine
>
> trim(trim(round(1.111,0),'0'),'.')   = 1
>

CAST(1.111 AS integer)

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

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

[Rob Richardson]

What target platforms have you found on which it is unreliable?  I haven't
had problems on Win7, but I haven't used it all that much.

RobR

On Thu, Oct 18, 2018 at 2:57 PM Jordy Deweer  wrote:

> Larry Brasfield wrote: "Jordy Deweer asks: “Is there a way to use a
> SQLite database in a C# project, without
> > installing / configuring / depending on the System.Data.SQLite libraries?
> >
> > I really hope there is.”
> >
> > If you do a web search for the combination of terms “SQLite”, “C#” and
> “library”, you will find several alternative libraries.  Some of them are
> thinner wrappers (of SQLite) or provide less complete exposure of SQLite’s
> API.
> >
> > You have the option of fashioning your own wrapper using the C# P/Invoke
> mechanism, or you may find the ‘NativeMethods’ functionality of interest.
> However, doing this robustly requires enough expertise that you should be
> wary of tackling the project."
>
>
>
> Thank you so much for your reply. I sill surely look at the options you
> gave me.
>
>
> I ask because I run into errors a lot, using the System.Data.SQLite
> libraries. It easily crashes, trhows exceptions and similar issues... I
> found out that it matters which target platform is selected...
>
>
> So thanks once again.
>
>
> Regards, Jordy
>
> ___
> 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

[Rob Richardson]

Why don't you want to use System.Data.SQLite?

On Thu, Oct 18, 2018 at 11:04 AM Jordy Deweer  wrote:

> Dear all
>
> I am new to this group and this is my first question here.
>
> My question is as follows:
>
> Is there a way to use a SQLite database in a C# project, without
> installing / configuring / depending on the System.Data.SQLite libraries?
>
> I really hope there is.
>
> Thank you so much in advance for your help.
>
> Best wishes
>
> Jordy
>
> ___
> 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] How to round to an Integer

[John Harney]

Recently figured this out.  Seems to work fine

trim(trim(round(1.111,0),'0'),'.')   = 1






Aviso de Privacidad y Confidencialidad // Privacy and Confidentiality Notice // 
Avis de confidentialit?: https://privacy.grupobimbo.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hot-Journal with VFS

[Bob schwanzer]

Hi,
 I'm seeing hot journal frequently in our SQLite installation. We use VFS
which does some checksum and other operations. There are no abnormal
scenarios such as power off, process crash, abandoned transactions... DB is
opened by 10-20 processes each of which can have multiple threads.

Old version of SQLite: 3.7.13
Process A is in write mode. Process B which is reading a value. Process B
used to sees hot-journal.
Now, we upgraded to 3.9.2 
Process A is in write mode. Process B is trying to read a value. B  sees a
hot-journal and recovers it.
Process A will get error introduced in new new package: "The xDelete method
in the built-in VFSes now return SQLITE_IOERR_DELETE_NOENT if the file to be
deleted does not exist"

Few things stand out:
Hot journal was reported in 3.7.13. There are no logs to indicate it's
recovery.
Hot journal is seen in 3.9.2. There are logs to indicate it's rollback.
Committer Process(Process A) gets error.

I know some optimizations in HardDisk or OS is creating the problem. What
other layers could contribute to this anomaly? How could installation endup
with Hot journals without system experiencing crash or power loss.









--
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] Bug report: bug in datetime conversion function

[Fábio Pfeifer]

Hello,

When working with Apple iOS databases, I found something strange when
dealing with dates. Apple iOS databases store dates as seconds from
2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29
there is a offset of one day if '31 years' modifier is used.
To reproduce, execute the following query:

select datetime(446860801, 'unixepoch', '31 years') as date1,
   datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2,
   datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3

date2 should be 1 day after date1, but shows as the same date!

Best regards,
Fábio Pfeifer
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Peter da Silva]

Don't put raw user input where code is expected. Match strongs are code.
You need to encapsulate (eg escape) or filter (delete bad characters) match
strings outside sqlite.

On Thu., 18 Oct. 2018, 13:50 Maziar Parsijani, 
wrote:

> It will crash or exit the program.
>
> On Thu, Oct 18, 2018 at 2:27 PM Jens Alfke  wrote:
>
> >
> >
> > > On Oct 18, 2018, at 11:17 AM, Maziar Parsijani <
> > maziar.parsij...@gmail.com> wrote:
> > >
> > > I just search for words an alphabets in different languages with python
> > and
> > > my database is sqlite but I need to do something to not getting error
> > when
> > > user input a wrong character like the ones that I told before.I can ban
> > > user to not input these characters but I am curious to find a way on
> > sqlite.
> >
> > What’s wrong with getting an error? If the error code or message is
> > specific enough, you can detect it and tell the user they’ve entered
> > invalid characters.
> >
> > —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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64-column covering index limit clarification

[Shawn Wagner]

Does a normal non-partial index make a difference in the query plan?

On Thu, Oct 18, 2018, 12:30 PM Deon Brewis  wrote:

> Hi,
>
> I seem to have run into a limit where SQLITE doesn't use an index
> correctly if an indexed column is over the 64th column in the table. It's a
> partial index like:
>
> CREATE INDEX idx ON
>   table(A, B DESC, C, D)
>   WHERE A > 0
>
> Where A and B are columns 70 and 72 on 'table'.
>
> I know about the 64-column limitation for covering indexes:
>
> http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html
>
> However, this isn't a covering index, it's a partial index. But it seems
> to run into the same limit. Even if I forced in the index into a query it
> still does a "USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A,
> B DESC" query. After I re-ordered the table, it magically started working.
>
> Is there any better documentation anywhere (other than the archive) of all
> of the cases to which the 64-column limit applies?
>
> - Deon
>
> ___
> 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] 64-column covering index limit clarification

[Deon Brewis]

Hi,

I seem to have run into a limit where SQLITE doesn't use an index correctly if 
an indexed column is over the 64th column in the table. It's a partial index 
like:

CREATE INDEX idx ON
  table(A, B DESC, C, D)
  WHERE A > 0

Where A and B are columns 70 and 72 on 'table'.

I know about the 64-column limitation for covering indexes:
http://sqlite.1065341.n5.nabble.com/Max-of-63-columns-for-a-covering-index-to-work-td68945.html

However, this isn't a covering index, it's a partial index. But it seems to run 
into the same limit. Even if I forced in the index into a query it still does a 
"USE TEMP B-TREE" at the end to satisfy a simple "ORDER BY A, B DESC" query. 
After I re-ordered the table, it magically started working.

Is there any better documentation anywhere (other than the archive) of all of 
the cases to which the 64-column limit applies?

- Deon

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

Re: [sqlite] Strip leading "The" in alphabetical TOC?

[Simon Slavin]

On 18 Oct 2018, at 7:17pm, Charles Leifer  wrote:

> In the documentation alphabetical listing, it threw me off when I was lookup 
> up the JSON1 docs and didn't find them under "J", due to the title being "The 
> JSON1 Extension".

Some years ago I wrote a TITLE Collating Sequence for SQLite. I forget the 
finer details, but it did this:

1) Turn everything into upper case
2) If string ends with a digit, find the integer at the end of the string and 
add '0' at the beginning until it was ten characters long.
3) Some stuff about Roman numerals at the end
4) If string starts with "A ", remove that and put ", A" at the end.
5) If string starts with "THE ", remove that and put ", THE" at the end.
6) some other things I forget

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

[Jordy Deweer]

Larry Brasfield wrote: "Jordy Deweer asks: “Is there a way to use a 
SQLite database in a C# project, without

installing / configuring / depending on the System.Data.SQLite libraries?

I really hope there is.”

If you do a web search for the combination of terms “SQLite”, “C#” and 
“library”, you will find several alternative libraries.  Some of them are 
thinner wrappers (of SQLite) or provide less complete exposure of SQLite’s API.

You have the option of fashioning your own wrapper using the C# P/Invoke mechanism, 
or you may find the ‘NativeMethods’ functionality of interest.  However, doing this 
robustly requires enough expertise that you should be wary of tackling the 
project."




Thank you so much for your reply. I sill surely look at the options you 
gave me.



I ask because I run into errors a lot, using the System.Data.SQLite 
libraries. It easily crashes, trhows exceptions and similar issues... I 
found out that it matters which target platform is selected...



So thanks once again.


Regards, Jordy

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

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Maziar Parsijani]

It will crash or exit the program.

On Thu, Oct 18, 2018 at 2:27 PM Jens Alfke  wrote:

>
>
> > On Oct 18, 2018, at 11:17 AM, Maziar Parsijani <
> maziar.parsij...@gmail.com> wrote:
> >
> > I just search for words an alphabets in different languages with python
> and
> > my database is sqlite but I need to do something to not getting error
> when
> > user input a wrong character like the ones that I told before.I can ban
> > user to not input these characters but I am curious to find a way on
> sqlite.
>
> What’s wrong with getting an error? If the error code or message is
> specific enough, you can detect it and tell the user they’ve entered
> invalid characters.
>
> —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] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Jens Alfke]

> On Oct 18, 2018, at 11:17 AM, Maziar Parsijani  
> wrote:
> 
> I just search for words an alphabets in different languages with python and
> my database is sqlite but I need to do something to not getting error when
> user input a wrong character like the ones that I told before.I can ban
> user to not input these characters but I am curious to find a way on sqlite.

What’s wrong with getting an error? If the error code or message is specific 
enough, you can detect it and tell the user they’ve entered invalid characters.

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

[sqlite] Strip leading "The" in alphabetical TOC?

[Charles Leifer]

Hi,

In the documentation alphabetical listing, it threw me off when I was
lookup up the JSON1 docs and didn't find them under "J", due to the title
being "The JSON1 Extension".

Just a suggestion: you might strip leading "The " from the titles in the
documentation list?

https://sqlite.org/doclist.html

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

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Maziar Parsijani]

hi,
I just search for words an alphabets in different languages with python and
my database is sqlite but I need to do something to not getting error when
user input a wrong character like the ones that I told before.I can ban
user to not input these characters but I am curious to find a way on sqlite.

On Thu, Oct 18, 2018 at 8:25 AM Bob Gailer  wrote:

> On Oct 18, 2018 5:59 AM, "Maziar Parsijani" 
> wrote:
> >
> > Hi,
> > how could I ignore syntax errors like this?
> >
> > > *SELECT *,*
> > >
> > > * highlight(searchsimpleenhanced, 2, '', '') text*
> > >
> > > * FROM searchsimpleenhanced*
> > >
> > > * WHERE searchsimpleenhanced MATCH 'sth][';*
>
> As I understand the documentation match must be defined someplace what is
> the definition of match in your case? Or what are you trying to accomplish
> by using match?
> ___
> 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

[Larry Brasfield]

Jordy Deweer asks: “Is there a way to use a SQLite database in a C# project, 
without 
installing / configuring / depending on the System.Data.SQLite libraries?

I really hope there is.”

If you do a web search for the combination of terms “SQLite”, “C#” and 
“library”, you will find several alternative libraries.  Some of them are 
thinner wrappers (of SQLite) or provide less complete exposure of SQLite’s API.

You have the option of fashioning your own wrapper using the C# P/Invoke 
mechanism, or you may find the ‘NativeMethods’ functionality of interest.  
However, doing this robustly requires enough expertise that you should be wary 
of tackling the project.

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

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

[Jordy Deweer]

Dear all

I am new to this group and this is my first question here.

My question is as follows:

Is there a way to use a SQLite database in a C# project, without 
installing / configuring / depending on the System.Data.SQLite libraries?


I really hope there is.

Thank you so much in advance for your help.

Best wishes

Jordy

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

Re: [sqlite] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Bob Gailer]

On Oct 18, 2018 5:59 AM, "Maziar Parsijani" 
wrote:
>
> Hi,
> how could I ignore syntax errors like this?
>
> > *SELECT *,*
> >
> > * highlight(searchsimpleenhanced, 2, '', '') text*
> >
> > * FROM searchsimpleenhanced*
> >
> > * WHERE searchsimpleenhanced MATCH 'sth][';*

As I understand the documentation match must be defined someplace what is
the definition of match in your case? Or what are you trying to accomplish
by using match?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[邱朗]

Hi,

Thanks for look into it. I checked its readme 
https://www.sqlite.org/cgi/src/dir?ci=6cb537bdce85e088&name=ext/icu

It does not mention tokenizer at all so I guess you are right, it probably does 
not support that at all.
Qiulang


At 2018-10-18 17:58:38, "Keith Medcalf"  wrote:
>
>The ICU extension (as in icu.c) does not contain the tokenizer ... it only 
>contains the extension functions ... (upper / lower / etc)
>
>Looking in the amalgamation it appears that the icu tokenizer module is 
>defined in fts3_icu.c
>
>I have no idea how you load a tokenizer module as part of a loadable 
>extension, or even if that is supported at all (or specifically in the case of 
>icu).
>
>Generally speaking I think you just compile the amalgamation (or the full 
>source tree) with SQLITE_ENABLE_ICU defined to use icu.
>
>---
>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 ??
>>Sent: Thursday, 18 October, 2018 02:56
>>To: SQLite mailing list
>>Subject: Re: [sqlite] Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>Hi,
>>
>>Thanks for your reply. I change module name to libicu.so as you said
>>then got Error: unknown tokenizer: icu
>>
>>
>>sqlite> .load libicu.so
>>sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu
>>zh_CN);
>>Error: unknown tokenizer: icu
>>
>>
>>Why is that ? Is the whole point to build icu extension to get the
>>icu tokenizer ?
>>
>>
>>BTW I have build an icu sqlite version from amalgamation but this
>>time I need to icu extension only.
>>
>>
>>Thanks
>>
>>
>>Qiulang
>>
>>At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>>>
>>>The symbol name is sqlite3_icu_init.  When you load module lib.so
>>the symbol sqlite3__init is called.  You need to either (a) rename
>>the shared library to the correct name (libicu.so) or pass the name
>>of the init function (sqlite3_icu_init) to the loader when you load
>>the module, or (c) change the name of the sqlite3_icu_init function
>>in the icu.c source so that it matches the name that the module
>>loader is looking for ...
>>>
>>>(If you are building your own library from the amalgamation source
>>you can just compile the amalgamation with the SQLITE_ENABLE_ICU
>>symbol defined ...)
>>>
>>>---
>>>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 ??
Sent: Wednesday, 17 October, 2018 22:26
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
sqlite3_sqliteicu_init): symbol not found

I had thought I succeeded in building ICU extension because running
gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
>>succeeded
and libSqliteIcu.so was generated.
But when I load it I got error Error: dlsym(0x7fa073e02c60,
sqlite3_sqliteicu_init): symbol not found


How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.


Thanks


Qiulang
___
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] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[Keith Medcalf]

The ICU extension (as in icu.c) does not contain the tokenizer ... it only 
contains the extension functions ... (upper / lower / etc)

Looking in the amalgamation it appears that the icu tokenizer module is defined 
in fts3_icu.c

I have no idea how you load a tokenizer module as part of a loadable extension, 
or even if that is supported at all (or specifically in the case of icu).

Generally speaking I think you just compile the amalgamation (or the full 
source tree) with SQLITE_ENABLE_ICU defined to use icu.

---
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 ??
>Sent: Thursday, 18 October, 2018 02:56
>To: SQLite mailing list
>Subject: Re: [sqlite] Error: dlsym(0x7fa073e02c60,
>sqlite3_sqliteicu_init): symbol not found
>
>Hi,
>
>Thanks for your reply. I change module name to libicu.so as you said
>then got Error: unknown tokenizer: icu
>
>
>sqlite> .load libicu.so
>sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu
>zh_CN);
>Error: unknown tokenizer: icu
>
>
>Why is that ? Is the whole point to build icu extension to get the
>icu tokenizer ?
>
>
>BTW I have build an icu sqlite version from amalgamation but this
>time I need to icu extension only.
>
>
>Thanks
>
>
>Qiulang
>
>At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>>
>>The symbol name is sqlite3_icu_init.  When you load module lib.so
>the symbol sqlite3__init is called.  You need to either (a) rename
>the shared library to the correct name (libicu.so) or pass the name
>of the init function (sqlite3_icu_init) to the loader when you load
>the module, or (c) change the name of the sqlite3_icu_init function
>in the icu.c source so that it matches the name that the module
>loader is looking for ...
>>
>>(If you are building your own library from the amalgamation source
>you can just compile the amalgamation with the SQLITE_ENABLE_ICU
>symbol defined ...)
>>
>>---
>>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 ??
>>>Sent: Wednesday, 17 October, 2018 22:26
>>>To: sqlite-users@mailinglists.sqlite.org
>>>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>>>sqlite3_sqliteicu_init): symbol not found
>>>
>>>I had thought I succeeded in building ICU extension because running
>>>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so
>succeeded
>>>and libSqliteIcu.so was generated.
>>>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>>>sqlite3_sqliteicu_init): symbol not found
>>>
>>>
>>>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>>>
>>>
>>>Thanks
>>>
>>>
>>>Qiulang
>>>___
>>>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] [SQLITE]ignoring syntax errors during search for sth include "symbols"

[Maziar Parsijani]

Hi,
how could I ignore syntax errors like this?

> *SELECT *,*
>
> * highlight(searchsimpleenhanced, 2, '', '') text*
>
> * FROM searchsimpleenhanced*
>
> * WHERE searchsimpleenhanced MATCH 'sth][';*
>
there maybe nothing to match but I don't like to get syntax errors for a
symbol or character like([ ] () , . @ ...)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Error: dlsym(0x7fa073e02c60, sqlite3_sqliteicu_init): symbol not found

[邱朗]

Hi,

Thanks for your reply. I change module name to libicu.so as you said then got 
Error: unknown tokenizer: icu


sqlite> .load libicu.so
sqlite> CREATE VIRTUAL TABLE zh_text USING fts4(text, tokenize=icu zh_CN);
Error: unknown tokenizer: icu


Why is that ? Is the whole point to build icu extension to get the icu 
tokenizer ?  


BTW I have build an icu sqlite version from amalgamation but this time I need 
to icu extension only.


Thanks


Qiulang

At 2018-10-18 12:48:51, "Keith Medcalf"  wrote:
>
>The symbol name is sqlite3_icu_init.  When you load module lib.so the 
>symbol sqlite3__init is called.  You need to either (a) rename the shared 
>library to the correct name (libicu.so) or pass the name of the init function 
>(sqlite3_icu_init) to the loader when you load the module, or (c) change the 
>name of the sqlite3_icu_init function in the icu.c source so that it matches 
>the name that the module loader is looking for ...
>
>(If you are building your own library from the amalgamation source you can 
>just compile the amalgamation with the SQLITE_ENABLE_ICU symbol defined ...)
>
>---
>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 ??
>>Sent: Wednesday, 17 October, 2018 22:26
>>To: sqlite-users@mailinglists.sqlite.org
>>Subject: [sqlite] Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>I had thought I succeeded in building ICU extension because running
>>gcc -shared icu.c `icu-config --ldflags` -o libSqliteIcu.so succeeded
>>and libSqliteIcu.so was generated.
>>But when I load it I got error Error: dlsym(0x7fa073e02c60,
>>sqlite3_sqliteicu_init): symbol not found
>>
>>
>>How do I solve that ? I am using mac 10.13 & sqlite 3.24 btw.
>>
>>
>>Thanks
>>
>>
>>Qiulang
>>___
>>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