Re: [sqlite] LIKE operator and collations

[Hick Gunter]

Actually SQLite does support X'...' literals for creating blobs.

-Ursprüngliche Nachricht-
Von: Dominique Devienne [mailto:ddevie...@gmail.com]
Gesendet: Dienstag, 13. Mai 2014 18:19
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] LIKE operator and collations

On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka  wrote:
> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

make that

BETWEEN('xxx', 'xxx' + char(1114109))

I don't think SQlite supports \u literals, nor does it support hex 
literals, so must use the decimal equivalent to U+10FFFD.

 C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '\uDBFF\uDFFD';
\uDBFF\uDFFD
sqlite> select char(1114109);
􏿽
sqlite> select char(0x10FFFD);
Error: unrecognized token: "0x10FFFD"
sqlite> select typeof(char(1114109));
text
sqlite> select length(char(1114109));
1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


---
Gunter Hick
Software Engineer

Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna,
Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then
delete this message from your system. Please do not copy it or use it for any 
purposes, or disclose its contents to any person as to do so could be a breach 
of confidence. Thank you for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

[Mattias Kindborg]

These are the steps I had to perform:

Install Visual Studio 2005. I was unable to successfully create a new 
solution platform based on another existing without VS2005 installed.
In VS2008 create a new solution platform based on existing platform 
'Pocket PC 2003 (ARMV4)'. The new solution platform should the the one you 
wish to target. We have from our CPU manufacturers received a Windows CE 
x86 .NET3.5 SDK that we use.
Make sure that all project properties of SQLite.Interop.CE.2008 were 
copied to the new solution platform.
On project properties of SQLite.Interop.CE.2008 change machine from 'ARM' 
to 'X86' in 'Configuration Properties' -> 'Linker' -> 'Command Line'.
In sqlite3.c do the following changes, marked with [NEW]:

[NEW] #define SQLITE_OS_WINCE 1
#define SQLITE_CORE 1
#define SQLITE_AMALGAMATION 1
#ifndef SQLITE_PRIVATE

...

[NEW] #define HAVE_LOCALTIME_S 0 

/*
** On recent Windows platforms, the localtime_s() function is available
** as part of the "Secure CRT". It is essentially equivalent to 
** localtime_r() available under most POSIX platforms, except that the 
** order of the parameters is reversed.
**

...

[NEW] struct tm *__cdecl localtime(const time_t *t); 

/*
** The following routine implements the rough equivalent of localtime_r()
** using whatever operating-system specific localtime facility that
** is available.  This routine returns 0 on success and
** non-zero on any kind of error.
**

After these changes I was able to compile the project.

/Mattias



From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-13 18:19
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



I just realized that I'm missing a prerequisite for developing for the 
devices I'm dealing with. They're running a stripped WinCE load that my 
company didn't create, so I don't have the SDK that I need to compile for 
it. I'll have to contact the manufacturer in hopes of getting my hands on 
it.

That does look hopeful, though. Did you just have to force those 
precompiler defines for it to compile properly?

Thanks,
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [
mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mattias Kindborg
Sent: 13 May, 2014 05:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

I finally managed to build SQLite using help from the following thread: 
http://sqlite.1065341.n5.nabble.com/Building-SQLite-on-Windows-Embedded-Compact-7-WEC7-td64722.html


/Mattias


From:   Mattias Kindborg 
To: General Discussion of SQLite Database 
Date:   2014-05-12 10:58
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



I have found versions of the download page where ARM wasn't mentioned, 
e.g. 
http://system.data.sqlite.org/index.html/artifact/99229e8a9f2b261771735c32de22ae025292c01a


. I thought the lack of mentioning ARM meant that x86 was supported.

What do you think?

/Mattias



From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 17:45
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



It must have been, considering other software running on these WinCE 6 
devices I have are using an older version of SQLite. They're not using 
.NET CF, though.

I can't find an ftp archive (or any archive) of older versions of the 
System.Data.SQLite libraries. Have you found one, by any chance?
--
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [
mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mattias Kindborg
Sent: 9 May, 2014 06:03
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

Hi Eric.

Then I guess it was a conscious decision to drop support for x86 with .NET 


CF 3.5, becuse I think it has been supported in olver versions of SQLite?

Best regards,
Mattias


From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 14:43
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



Among the binaries all I see is for .NET Compact Framework 3.9 (the latest 


from Microsoft, but not supported prior to their collection of operating 
systems that were released last year, so it doesn't work with WinCE 6).

I have compiled for ARM with .NET CF 3.5 in VS2008, but not x86.
--
Eric

> On May 9, 2014, at 1:02 AM, "Mattias Kindborg" 
 wrote:
> 
> Doesn't SQLite have support for Windows CE x86 with .NET3.5? On the 
> download site for System.Data.SQLite (
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
it 
> seems that only ARM is supported.
> 
> Best regards,
> Mattias Kindborg
> __

Re: [sqlite] Retrieve a int or a sqlite_int64

[gwenn]

Ok,
Thanks.

On Tue, May 13, 2014 at 11:59 PM, Teg  wrote:
> Hello Charles,
>
> Tuesday, May 13, 2014, 3:12:09 PM, you wrote:
>
> CS> Load it with sqlite3_value_int64 every time. If the number fits in a 32 
> bit
> CS> integer, then you can store it in one.
>
> This is what I do. Everything is 64 bits to be future proof.
>
>
> CS> Charles
> CS> ___
> CS> sqlite-users mailing list
> CS> sqlite-users@sqlite.org
> CS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> --
> Best regards,
>  Tegmailto:t...@djii.com
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite's VFS in C++

[J Decker]

the name that gets passed is the one you pass to sqlite_open... so just use
that as an indicator of which object to use and in the open callback,
result with the appropriate object... or don't use the name and ignore
it?  snprintf( somebuf, sizeof( somebuf ) / sizeof( somebuf[0] ), "%p",
your_file_descriptor ); sqlite3_open( somebuf ) ?


On Tue, May 13, 2014 at 4:43 AM, Ralf wrote:

> Hello,
>
> I would like to use SQLite in my C++(11) project.
>
> Assume that there is a class File which is able to store arbitrary data.
> It provides basic IO functionality read() write() truncate() and sync().
>
> Now I would like to use my File to store SQLite databases (persistently).
>
> To understand my motivation, let me explain in short words what exactly
> File does:
> A "File" splits its content into several fixed-sized encrypted blocks
> and stores those blocks on some kind of storage.
> At any point in time, it is guaranteed, that no other process will
> interfere and use those blocks, so we don't need any locking mechanisms.
>
> The easiest way to implement this would be to store the whole database
> in a local temporary folder using unix vfs, close it, read all its
> content and call my file->write() routine.
> This strategy contains some ugliness as I would like to avoid using
> temporary files.
>
> So I had a deeper look into SQLite's VFS[1] interface and I think that
> it could possibly offer a better solution.
>
> Now my problem:
>
> There may be several File objects, each representing a SQLite database.
> Those objects exist before SQLite accesses them - so there is no need,
> that SQLite generates those objects.
>
> How can I use VFS to work with those objects?
>
> Generally sqlite3_open*() is used to generate a struct sqlite3*
> regarding a certain kind of VFS type.
> But I can not use sqlite3_open*() in order to "open" a File as
> sqlite3_open*() makes use of a const char* filename that obviously
> indicates the filename :-)
> In my case, there is nothing like a filename but there is already an
> object on which SQLite shall work with.
>
> So I would need sth. like:
>
> File *f = ...;
> sqlite3_open(f, myOwnVFSDescriptor);
>
> Are there any possibilities to solve this problem?
> Or would it be better to create a temporary SQLite database file and
> copy its content afterwards?
>
> One last question:
> I also read about in memory and temporary databases [2].
>
> Do in memory databases have the same structure as database files?
> If so, is it possible to dump and load in memory databases? (this could
> also offer a nice solution)
>
> [1] http://sqlite.org/vfs.html
> [2] http://sqlite.org/inmemorydb.html
>
> Regards and thanks!
>   Ralf
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using SQLite's VFS in C++

[James K. Lowden]

On Tue, 13 May 2014 13:43:09 +0200
Ralf  wrote:

> A "File" splits its content into several fixed-sized encrypted blocks
> and stores those blocks on some kind of storage.
> At any point in time, it is guaranteed, that no other process will
> interfere and use those blocks, so we don't need any locking
> mechanisms.

Why involve SQLite, exactly?  You already have an object that can write
to a file, no need for transactions, and no desire to decompose that
object into columns and rows to share with other processes/users.  You
seem to think each such object is not just a row in a table, but an
entire database.  

> I would like to avoid using temporary files.

doesn't leap out as a case for SQLite.  Whatever complexity you avoid
with temporary files will surely be met tenfold for VFS support.  

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

Re: [sqlite] Porting SQLite to plain C RTOS

[mm.w]

http://pubs.opengroup.org/onlinepubs/7908799/xsh/systime.h.html

http://pubs.opengroup.org/onlinepubs/7908799/xsh/flockfile.html

http://pubs.opengroup.org/onlinepubs/009695399/functions/fcntl.html

// IEEE Std 1003.1, 2004 Edition @!!= linux



On Tue, May 13, 2014 at 7:55 AM, Artur Deterer wrote:

> Hello SQLite Developers,
>
>
>
> my company would like to use sqlite in our embedded systems for which we
> use
> a plain C proprietary operating system.
>
> Memory allocation and deallocation within this system is realized through
> standard malloc/free functions. At this point of
>
> system development we are trying to adapt SQLite to our platform and  face
> some difficulties:
>
>
>
> Before I tell you which errors occur I explain to you the work steps I
> walked through.
>
> 1)As far as I know I have to tell sqlite that it should not
> work
> on the implemented operating systems but on a
>
> custom platform and this is to be done setting the macro
> SQL_OTHER_OS to 1. (in my code, line 30: SQL_OTHER_OS=1;)
>
> I did this within the first lines of sqlite3.c file. and at
> line 9870 I changed the value of SQL_OTHER_OS to 1 too.
>
> 2)With our single-threaded task scheduler we also have to tell
> sqlite to not allow multithreading operations
>
> as I did changing the value of SQL_THREADSAFE to 0 in line
> 7081.
>
> 3)On your web pages you wrote that a user has to implement the
> functions sqlite3_os_init() and sqlite3_os_end()
>
> in case that he wants sqlite to run on a custom system but
> I
> do not know which implementation is needed or whether
>
> an own implementation is needed because our os memory
> operations are based on malloc and free.
>
> 4)As I read your manual to port SQLite to a new operating
> system, I realized that an own implementation of
>
> sqlite3_vfs is necessary. I took
>  test_demovfs.c as
> skeleton for a ownOSVFS implementation. Within our os
>
> we have an own implementation of FAT32 file system
> functions
> which I used for substituting the file operation
>
> functions offered in test_demovfs.
>
> 5)I set the function sqlite3_initialize() within sqlite3.c in
> comments to implement this function within uTaskerVFS.c.
>
> This file also includes the sqlite3 header file and is
> appended to this email.
>
>
>
> As I walked through these steps I am not confident whether my
> implementation
> is correct or misses any point (e.g. sqlite3_os_init()).
>
> The main reason I write to you is that our cross-compiler (ARM GCC) for an
> ARM Cortex M4 system throws compiler errors
>
> according to sqlite. They tell me that references to several c structures
> (e.g. timeval, flock) are missing. These are used within a
>
> code part that asks whether the current platform is Linux (if macro: line
> 23528 ) what obviously is not correct.
>
> I think that if the macro SQL_OTHER_OS set to 1 the code should not enable
> the code part  designed for linux os.
>
>
>
> Have you an idea of solving the porting difficulties I described in this
> email ?
>
> It would be great ,too, if you could tell me whether I am wrong with my
> implementation.
>
> Can you please give me a more detailed instruction or maybe an example
>
> integration of sqlite into a custom os?
>
>
>
> Kind regards
>
> Deterer
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite-users list failed to block large attachment

[Darren Duncan]

Is something wrong with the configuration of this sqlite-users list?

A message of subject "Porting SQLite to plain C RTOS" was allowed and 
distributed through it this morning with attachments.


Not only attachments, but about 5MB of attachments.

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

[sqlite] Using SQLite's VFS in C++

[Ralf]

Hello,

I would like to use SQLite in my C++(11) project.

Assume that there is a class File which is able to store arbitrary data.
It provides basic IO functionality read() write() truncate() and sync().

Now I would like to use my File to store SQLite databases (persistently).

To understand my motivation, let me explain in short words what exactly
File does:
A "File" splits its content into several fixed-sized encrypted blocks
and stores those blocks on some kind of storage.
At any point in time, it is guaranteed, that no other process will
interfere and use those blocks, so we don't need any locking mechanisms.

The easiest way to implement this would be to store the whole database
in a local temporary folder using unix vfs, close it, read all its
content and call my file->write() routine.
This strategy contains some ugliness as I would like to avoid using
temporary files.

So I had a deeper look into SQLite's VFS[1] interface and I think that
it could possibly offer a better solution.

Now my problem:

There may be several File objects, each representing a SQLite database.
Those objects exist before SQLite accesses them - so there is no need,
that SQLite generates those objects.

How can I use VFS to work with those objects?

Generally sqlite3_open*() is used to generate a struct sqlite3*
regarding a certain kind of VFS type.
But I can not use sqlite3_open*() in order to "open" a File as
sqlite3_open*() makes use of a const char* filename that obviously
indicates the filename :-)
In my case, there is nothing like a filename but there is already an
object on which SQLite shall work with.

So I would need sth. like:

File *f = ...;
sqlite3_open(f, myOwnVFSDescriptor);

Are there any possibilities to solve this problem?
Or would it be better to create a temporary SQLite database file and
copy its content afterwards?

One last question:
I also read about in memory and temporary databases [2].

Do in memory databases have the same structure as database files?
If so, is it possible to dump and load in memory databases? (this could
also offer a nice solution)

[1] http://sqlite.org/vfs.html
[2] http://sqlite.org/inmemorydb.html

Regards and thanks!
  Ralf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Retrieve a int or a sqlite_int64

[Teg]

Hello Charles,

Tuesday, May 13, 2014, 3:12:09 PM, you wrote:

CS> Load it with sqlite3_value_int64 every time. If the number fits in a 32 bit
CS> integer, then you can store it in one.

This is what I do. Everything is 64 bits to be future proof. 


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



-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] Retrieve a int or a sqlite_int64

[Charles Samuels]

On Tuesday, May 13, 2014 12:02:27 PM gwenn wrote:
> Is there any way to differentiate one value persisted with
> sqlite3_bind_int from another persisted with sqlite3_bind_int64 ?

No, there's no difference

> How to know which method between sqlite3_value_int and
> sqlite3_value_int64 should be used to retrieve the value back ?

Load it with sqlite3_value_int64 every time. If the number fits in a 32 bit 
integer, then you can store it in one.

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

[sqlite] Retrieve a int or a sqlite_int64

[gwenn]

Hello,
Is there any way to differentiate one value persisted with
sqlite3_bind_int from another persisted with sqlite3_bind_int64 ?
How to know which method between sqlite3_value_int and
sqlite3_value_int64 should be used to retrieve the value back ?
Regards.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Simon Slavin]

On 13 May 2014, at 5:21pm, Constantine Yannakopoulos wrote:

> ​This is very interesting Jan. The only way this could fail is if the
> collation implementation does something funny if it encounters this
> character​, e.g. choose to ignore it when comparing.

That cuts out a very large number of collations.  The solution works fine for 
any collation which orders strings according to Unicode order.  But the point 
of creating a correlation is that you don't want that order.  For instance, I 
have a correlation which is used for IP addresses and expects its input to look 
like

d.d.d.d

where each 'd' is one or more digits.  If you hand it a string which doesn't 
conform (octet missing, or octet > 255), that string evaluates the same as 
'0.0.0.0'.  If I was to use

... WHERE source LIKE '10.%'

and the char(1114109) solution was used I'd get incorrect results whereas

... WHERE source BETWEEN '10.0.0.0' AND '10.255.255.255'

works fine.

There isn't a solution to the problem unless SQLite has further information 
from whoever devised the collation.

Simon.

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

Re: [sqlite] Best compression for sqlite3 ?

[Alain Meunier]

Ok thanks a lot !

> Date: Tue, 13 May 2014 14:53:03 +0800
> From: m...@onghu.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Best compression for sqlite3 ?
> 
> On 13/5/2014 2:45 PM, Simon Slavin wrote:
> >
> > There are two versions of your question: one for compression of a database 
> > which is only going to be read from now on and another for compression of a 
> > database which has to support writing.
> >
> > hwaci, Richard Hipp's own company, support both ZIPVFS and CEROD:
> >
> > http://www.hwaci.com/sw/sqlite/zipvfs.html
> > http://www.hwaci.com/sw/sqlite/cerod.html
> >
> > which do both things.  Since the guy who runs that company is also the main 
> > man behind SQLite itself, there's a good chance that support will continue.
> >
> > When comparing database sizes and compression factors you should bear in 
> > mind that different DBMSs access data at different speeds and require more 
> > or fewer indexes to do it at the same speeds.  In other words, publishing a 
> > simpler '40% of file size' doesn't tell the whole story.
> 
> While Simon is absolutely correct, I can confirm that in production, our 
> databases using CEROD (read only) are typically around 40% ~ 45% of the 
> size of the original database.  However, we have also seen cases, where 
> we reduce the size of the original database by 200MB and the resulting 
> compressed database size reduces only by 20MB since the original data 
> had a lot of data that was very easy to compress.
> 
> As always, your mileage may vary - but this is what we have observed 
> with CEROD.
> 
> Best Regards,
> Mohit.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction in one thread and other thread also trying to write data

[J Decker]

It works best to have a connection per thread.


On Tue, May 13, 2014 at 6:12 AM, d b  wrote:

> Hi all,
>
>
>My application is multithreaded. It maintains only connection per
> application. Database accessed by single process only. ThreadA will do
> database write operations(bulk) in a transaction. ThreadB will do single
> write operation without transaction but same connection pointer.
>
>Here, application needs to synchronize the calls among threads by using
> synchronization technique(critical_section/mutex)? (OR) begin transaction
> and commit will synchronize the calls between threads?
>
>Please suggest.
>
> Thanks,
> av.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Dominique Devienne]

On Tue, May 13, 2014 at 5:50 PM, Jan Slodicka  wrote:
> So one could replace "LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

make that

BETWEEN('xxx', 'xxx' + char(1114109))

I don't think SQlite supports \u literals, nor does it support hex
literals, so must use the decimal equivalent to U+10FFFD.

 C:\Users\DDevienne>sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '\uDBFF\uDFFD';
\uDBFF\uDFFD
sqlite> select char(1114109);
􏿽
sqlite> select char(0x10FFFD);
Error: unrecognized token: "0x10FFFD"
sqlite> select typeof(char(1114109));
text
sqlite> select length(char(1114109));
1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LIKE operator and collations

[Constantine Yannakopoulos]

On Tue, May 13, 2014 at 6:50 PM, Jan Slodicka  wrote:

> Any comments are welcome.


​This is very interesting Jan. The only way this could fail is if the
collation implementation does something funny if it encounters this
character​, e.g. choose to ignore it when comparing. Since most collations
end up calling the OS unicode API, and this handles U+10FFFD correctly,
this should be a very rare case.

This may be a reason for Dr Hipp to reject adding this to the LIKE
optimization, but anyone could choose to make the optimization manually
using BETWEEN instead of LIKE if they are sure that any custom collations
they have coded and are using handle U+10FFFD correctly. This is obviously
true in your case.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

[Eric Johnston]

I just realized that I'm missing a prerequisite for developing for the devices 
I'm dealing with. They're running a stripped WinCE load that my company didn't 
create, so I don't have the SDK that I need to compile for it. I'll have to 
contact the manufacturer in hopes of getting my hands on it.

That does look hopeful, though. Did you just have to force those precompiler 
defines for it to compile properly?

Thanks,
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Mattias Kindborg
Sent: 13 May, 2014 05:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

I finally managed to build SQLite using help from the following thread: 
http://sqlite.1065341.n5.nabble.com/Building-SQLite-on-Windows-Embedded-Compact-7-WEC7-td64722.html

/Mattias


From:   Mattias Kindborg 
To: General Discussion of SQLite Database 
Date:   2014-05-12 10:58
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



I have found versions of the download page where ARM wasn't mentioned, e.g. 
http://system.data.sqlite.org/index.html/artifact/99229e8a9f2b261771735c32de22ae025292c01a

. I thought the lack of mentioning ARM meant that x86 was supported.

What do you think?

/Mattias



From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 17:45
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



It must have been, considering other software running on these WinCE 6 devices 
I have are using an older version of SQLite. They're not using .NET CF, though.

I can't find an ftp archive (or any archive) of older versions of the 
System.Data.SQLite libraries. Have you found one, by any chance?
--
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [
mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mattias Kindborg
Sent: 9 May, 2014 06:03
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

Hi Eric.

Then I guess it was a conscious decision to drop support for x86 with .NET 

CF 3.5, becuse I think it has been supported in olver versions of SQLite?

Best regards,
Mattias


From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 14:43
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



Among the binaries all I see is for .NET Compact Framework 3.9 (the latest 

from Microsoft, but not supported prior to their collection of operating 
systems that were released last year, so it doesn't work with WinCE 6).

I have compiled for ARM with .NET CF 3.5 in VS2008, but not x86.
--
Eric

> On May 9, 2014, at 1:02 AM, "Mattias Kindborg" 
 wrote:
> 
> Doesn't SQLite have support for Windows CE x86 with .NET3.5? On the 
> download site for System.Data.SQLite (
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
it 
> seems that only ARM is supported.
> 
> Best regards,
> Mattias Kindborg
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

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

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

Constantine Yannakopoulos wrote
> On Mon, May 12, 2014 at 4:46 PM, Jan Slodicka <

> jano@

> > wrote:
> I understand that it is difficult to find the least greater character of a
> given character if you are unaware of the inner workings of a collation,
> but maybe finding a consistent upper limit for all characters in all
> possible collations is not impossible?
> 
> -- Constantine
> ___
> sqlite-users mailing list

> sqlite-users@

> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

I could be wrong when I refused this idea. U+10FFFD (represented as
surrogate pair "\uDBFF\uDFFD") may be that character. So one could replace
"LIKE 'xxx%'" by "BETWEEN('xxx', 'xxx' + '\uDBFF\uDFFD').

U+10FFFD is the highest codepoint from the Unicode Private Use Area (PUA).
Unicode standards do not specify anything for this area. So vendors and/or
apps (MS Word for example) can define here their own characters incl. their
properties (font, sorting etc.). I saw somewhere that somebody placed here
Japanese Kanji characters, for example.

msdn statement for CompareString: "All characters in the PUA are sorted
after all other Unicode characters. Within the area, characters are sorted
in numerical order." Hence Windows is OK.

I could not find any such notion for UCI library, but when I tested their
interactive demo, character U+10FFFD was really sorted at the end. (Even
with Japanese, Hindi etc.) That might mean that at least the standard setup
of the UCI library sorts U+10FFFD in the expected way. Of course, this is no
proof - UCI library can be customized.

After all, it seems that U+10FFFD is a good choice, at least for a vast
majority of cases.

BTW, my tests showed that the speed improvement of the LIKE optimization is
in the range 10x-100x.

Any comments are welcome.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75643.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transaction in one thread and other thread also trying to write data

[Igor Tandetnik]

On 5/13/2014 9:12 AM, d b wrote:

My application is multithreaded. It maintains only connection per
application. Database accessed by single process only. ThreadA will do
database write operations(bulk) in a transaction. ThreadB will do single
write operation without transaction but same connection pointer.


Transaction is a property of a connection, not a thread. If ThreadA 
started an explicit transaction on a connection, then writes by ThreadB 
on the same connection will also become part of that transaction.



Here, application needs to synchronize the calls among threads by using
synchronization technique(critical_section/mutex)? (OR) begin transaction
and commit will synchronize the calls between threads?


Individual sqlite3_* API calls on a given connection are serialized by 
SQLite: each call locks a mutex associated with the connection on entry, 
and unlocks it right before returning. If you need more synchronization 
than that, you would have to provide it yourself.


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

Re: [sqlite] transaction in one thread and other thread also trying to write data

[Constantine Yannakopoulos]

On Tue, May 13, 2014 at 4:12 PM, d b  wrote:

>My application is multithreaded. It maintains only connection per
> application. Database accessed by single process only. ThreadA will do
> database write operations(bulk) in a transaction. ThreadB will do single
> write operation without transaction but same connection pointer.
>
>Here, application needs to synchronize the calls among threads by using
> synchronization technique(critical_section/mutex)? (OR) begin transaction
> and commit will synchronize the calls between threads?
>
>Please suggest.
>

Please read this: http://www.sqlite.org/threadsafe.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] transaction in one thread and other thread also trying to write data

[d b]

Hi all,


   My application is multithreaded. It maintains only connection per
application. Database accessed by single process only. ThreadA will do
database write operations(bulk) in a transaction. ThreadB will do single
write operation without transaction but same connection pointer.

   Here, application needs to synchronize the calls among threads by using
synchronization technique(critical_section/mutex)? (OR) begin transaction
and commit will synchronize the calls between threads?

   Please suggest.

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

Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

[Mattias Kindborg]

I finally managed to build SQLite using help from the following thread: 
http://sqlite.1065341.n5.nabble.com/Building-SQLite-on-Windows-Embedded-Compact-7-WEC7-td64722.html

/Mattias


From:   Mattias Kindborg 
To: General Discussion of SQLite Database 
Date:   2014-05-12 10:58
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



I have found versions of the download page where ARM wasn't mentioned, 
e.g. 
http://system.data.sqlite.org/index.html/artifact/99229e8a9f2b261771735c32de22ae025292c01a

. I thought the lack of mentioning ARM meant that x86 was supported.

What do you think?

/Mattias



From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 17:45
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



It must have been, considering other software running on these WinCE 6 
devices I have are using an older version of SQLite. They're not using 
.NET CF, though.

I can't find an ftp archive (or any archive) of older versions of the 
System.Data.SQLite libraries. Have you found one, by any chance?
--
Eric

-Original Message-
From: sqlite-users-boun...@sqlite.org [
mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Mattias Kindborg
Sent: 9 May, 2014 06:03
To: General Discussion of SQLite Database
Subject: Re: [sqlite] System.Data.SQLite on Windows CE x86 with .NET3.5

Hi Eric.

Then I guess it was a conscious decision to drop support for x86 with .NET 

CF 3.5, becuse I think it has been supported in olver versions of SQLite?

Best regards,
Mattias


From:   Eric Johnston 
To: General Discussion of SQLite Database 
Date:   2014-05-09 14:43
Subject:Re: [sqlite] System.Data.SQLite on Windows CE x86 with 
.NET3.5
Sent by:sqlite-users-boun...@sqlite.org



Among the binaries all I see is for .NET Compact Framework 3.9 (the latest 

from Microsoft, but not supported prior to their collection of operating 
systems that were released last year, so it doesn't work with WinCE 6).

I have compiled for ARM with .NET CF 3.5 in VS2008, but not x86.
--
Eric

> On May 9, 2014, at 1:02 AM, "Mattias Kindborg" 
 wrote:
> 
> Doesn't SQLite have support for Windows CE x86 with .NET3.5? On the 
> download site for System.Data.SQLite (
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki)
it 
> seems that only ARM is supported.
> 
> Best regards,
> Mattias Kindborg
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

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

Re: [sqlite] LIKE operator and collations

[Jan Slodicka]

> ​It can be implemented if the definition of a collation is extended to be 
able to provide this information as Simon suggested.

Sure, this could be done. But I am not sure whether it would be that
usefull. For example I would not use it for my current application (C#).



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/LIKE-operator-and-collations-tp73789p75638.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FTS3/4 merge function behaviour when deleting rows

[andrewmo]

On 11 May 2014 00:01, Scott Robison-2 [via SQLite] <
ml-node+s1065341n75608...@n5.nabble.com> wrote:

> I suspect the primary use case it was designed and tested for (and in fact
> the way we use it at my place of employment) was more for "only growing
> datasets" and less for an environment where stuff is continually being
> added and deleted. We use SQLite as a "caching data structure" for
> information read from a proprietary less functional third party database
> product so as to speed up queries for a session, but the session is
> discarded when the program is closed. (Trust me, it makes sense for us.)
>

Understood. I can see that there are lots of cases where FTS3, or sqlite in
general could be used without needing to regularly delete content, or where
there is the opportunity to rebuild indexes. I was a bit remiss in making
use of FTS3 in our system without really investigating this behaviour.
Having successfully made use of sqlite in our software (that runs on large
servers and embedded devices) to store the application configuration and
automatically generated data at runtime for many years, I assumed FTS would
allow the same pattern when it came to storing searchable text.


>
> That does not mean that zero consideration was given to your use case, but
> I suspect (though I have no evidence to back me up) that the test cases
> for
> deletion were focused on correct functionality in the face of relatively
> random insertions and deletions, not for "record leaks" due to future
> merge
> failures.
>
> In any case, I was not aware of this problem, and can see where (if
> validated, and it seems reproducible per your directions) it is a problem
> for some use cases and some cleanup would be useful for those use cases.
> Regardless, it does seem to "work correctly" though in a suboptimal way
> per
> resource usage.
>

How could I go about getting this issue 'validated'. The company I work for
has considerable interest in seeing this resolved, and as myself and my
team have other no experience of modifying sqlite (and we have several
other projects needing progressed), we would like to pay someone to take
this further. If anyone is interested in taking this on please get in touch!


> Have you tried creating a new database at some point that consists of only
> the "live active" records in a "leaky" database so as to compare what the
> size could/should be if the "current data set" had been created with zero
> deletions of data along the way?
>

If I understand your question correctly, then the python script (linked in
original mail) provides this. It first adds 2000 documents, then goes into
a cycle of deleting and adding 100. When 2000 have been added, the db size
is 108Kb. 70 pages of which is the content, and 57 being made up by the
metadata in the rest of the FTS tables. This would be better if the
'optimize' command was used, but that is a separate issue. For my purposes
I don't really mind if the index size is the same size as the data, what
matters is;
 1) The index does not grow indefinitely. It can be predictably kept within
some bounds.
 2) Merge operations can still be undertaken incrementally. Turning 'merge'
into 'optimize' isn't a solution as it takes too long to run on a large
dataset.


> --
> Scott Robison
> ___
> sqlite-users mailing list
> [hidden email] 
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  If you reply to this email, your message will be added to the discussion
> below:
>
> http://sqlite.1065341.n5.nabble.com/FTS3-4-merge-function-behaviour-when-deleting-rows-tp75370p75608.html
>  To unsubscribe from FTS3/4 merge function behaviour when deleting rows, click
> here
> .
> NAML
>




--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/FTS3-4-merge-function-behaviour-when-deleting-rows-tp75370p75637.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users