Re: [sqlite] Success stories
В сообщении от Monday 21 July 2008 04:59:10 Kang Kai написал(а):
> Thanks for sharing your experience, would you please introduce how you deal
> with the concurrency?
I'm using AOL web server + tclsqlite. Small and fast read/write transactions +
in-memory && file-based database replicas + "db timeout" function + split
database by region or month as example to group of databases is enough for
most causes.
Selects is extremely fast by default. Usually not optimized select on one
table or a lot of tables in SQLite is more fast than very optimized query in
PostgreSQL. It's fantastic!
I'm using "PRAGMA page_size=4096" on ext3 filesystem with 4k block size.
For example:
sqlite3 db :memory:
db timeout [ns_config "ns/server/dataset" timeout]
db eval {PRAGMA page_size=4096}
db eval {PRAGMA default_cache_size=1}
set dbmainfile [dbmainfile]
db eval{ATTACH DATABASE $dbmainfile as merch}
set dbuserfile [dbuserfile]
db eval{ATTACH DATABASE $dbuserfile as user}
There are main in-memory database "main" and application database "merch" and
persistent user storage database "user". One-time replicas are created
in-memory and long-time report tables, etc. are created in user database. In
PostgreSQL corresponding way is to create user schemas and tables/views in
these schemas (PostgreSQL creating different files for each table). Now I'm
creating dataset and generate web page by them:
===
# generate filters
...
# create replica by filters
db eval {create table data_replica as select ... from data where ...}
# html drop-down lists
db eval {select distinct a from data_replica} {...}
db eval {select distinct b from data_replica} {...}
...
# html table
db eval {select a,b,c from data_replica} {...}
===
So read lock on application database is short.
Besides read locks not blocked database for long time than database is
accessible for write always. One insert is very fast by default and big
dataset insert/update is better prepare in database replica and sync by one
query with application database.
# prepare dataset
db eval {create table replica as ...}
db eval {insert into replica values ()}
db eval {update replica ...}
# sync with application database (insert/update)
db eval {insert into merch.data select ... from replica}
Users session storage is in-memory with sync dump/restore procedures for
stop/start server.
compress/uncompress functions is very useful for fast disk read/write. See
http://www.mail-archive.com/sqlite-users%40sqlite.org/msg17018.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] busy_timeout and shared_cache
On Jul 21, 2008, at 5:46 AM, Daniel Önnerby wrote: > Hi all! > > I usually set the sqlite3_busy_timeout to 10 seconds or something like > that to make sure that my db isn't locked by any other connection > at the > same time. This way I usually do not need to check for SQLITE_BUSY. > Now I just tried out the sqlite3_enable_shared_cache and has enabled > shared cache on 3 different threads connected to the same db. > The funny thing is that now the bust_timeout seems to fail. Instead > sqlite3_step will now return SQLITE_LOCKED every now and then (and > I can > assure you that the timeout has not been reached). > > Is this a bug, or is this an undocumented expected behavior? The busy-handler is never called when a shared-cache client cannot procede because of a transaction, table or schema lock held by another client of the same shared cache. See section 2 of this: http://www.sqlite.org/sharedcache.html for details on those three types of locks. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
I just tried the same steps on a memorydb using the NOCASE collation
sequence, and it worked fine ...
C:\Src>sqlite3 :memory:
SQLite version 3.6.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .headers on
sqlite> create table foo(myvalue text collate nocase);
sqlite> create unique index fooidx on foo(myvalue);
sqlite>
sqlite> insert into foo(myvalue) values('abc');
sqlite> insert into foo(myvalue) values('abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('Abc');
SQL error: column myvalue is not unique
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique
sqlite> drop index fooidx;
sqlite> select * from foo;
myvalue
abc
sqlite> create unique index fooidx on foo(myvalue);
sqlite> insert into foo(myvalue) values('ABC');
SQL error: column myvalue is not unique
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:52 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined collation UNIQUE INDEX
No, assigning collation during index creation makes no difference. I also
tried
REINDEX with no luck.
I am using verison 3.5.9 on a winxp box (forgot to mention that).
csmith
Robert Simpson wrote:
> Does this work?
>
> CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
>
> Robert
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
> Sent: Sunday, July 20, 2008 9:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] User-defined collation UNIQUE INDEX
>
> I am trying to add a very simple collation via load_extension. The
> collation is
> a case-insensitive wchar compare for windows (using _wcsicmp). It is
> designed
> for windows pathnames being stored in utf16. All works fine until I try
to
> add
> the final touch, a UNIQUE INDEX.
>
> Question: How do I get a UNIQUE INDEX to follow the collation assigned to
a
> column?
>
> NOTE: in the below examples, I also tried assigning the collation during
> index
> creation rather than table creation ... didn't help any.
>
> -- collation named PATH was loaded via load_extension
> sqlite> create table test (str text collate path);
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> abc
> sqlite> select * from test where str = 'abC';
> abc
> sqlite> select * from test where str = 'aBC';
> abc
>
> The above looks great. Now add the unique index...
>
> sqlite> create unique index myidx on test (str);
> sqlite> insert into test values ('abc');
>
> The above is the first problem. The unique index should of detected that
> the
> value 'abc' already existed in a 'str' column.
>
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> The next problem is that the above select does not find any matching
> records, as
> it did prior to the unique index being added. I thought it could be
because
> the
> table is messed up due to duplicate column values? So I did the below:
>
> sqlite> delete from test;
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> Still no result. I then removed the unique index:
>
> sqlite> drop index myidx;
> sqlite> select * from test where str = 'ABC';
> abc
> sqlite>
>
> Thanks,
> csmith
>
>
___
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] Amount of memory for caching one page with x byte page size?
On Jul 21, 2008, at 2:10 AM, [EMAIL PROTECTED] wrote: > > Hello, > > to answer the following question: > > Amount of memory for caching one page with x byte page size? > > I found in the Draft 3.6.0 Doc the following information: > > > PRAGMA page_size = bytes; > > Query or set the page size of the database. > The page size may only be set if the database has not yet been > created. > The page size must be a power of two greater than or equal to 512 and > less than or equal to SQLITE_MAX_PAGE_SIZE. > The maximum value for SQLITE_MAX_PAGE_SIZE is 32768. > > > > PRAGMA default_cache_size = Number-of-pages; > > Query or change the maximum number of database disk pages that SQLite > will hold in memory at once. > Each page uses 1K on disk and about 1.5K in memory. ... > > > Obviously you have an overhand per page-size to calculate the memory > requirement for caching on page. > > Do I understand this right? Right. When a page is loaded into the cache, SQLite allocates memory for the page data (1024 bytes, or whatever size you are using) and also memory for a structure full of other variables required by each cached page (page number, dirty flag, is-journalled flag, various pointers for linked lists, lots of stuff). The overhead probably isn't quite 512 bytes, but as a rule of thumb this formula works Ok. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
No, assigning collation during index creation makes no difference. I also
tried
REINDEX with no luck.
I am using verison 3.5.9 on a winxp box (forgot to mention that).
csmith
Robert Simpson wrote:
> Does this work?
>
> CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
>
> Robert
>
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
> Sent: Sunday, July 20, 2008 9:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] User-defined collation UNIQUE INDEX
>
> I am trying to add a very simple collation via load_extension. The
> collation is
> a case-insensitive wchar compare for windows (using _wcsicmp). It is
> designed
> for windows pathnames being stored in utf16. All works fine until I try to
> add
> the final touch, a UNIQUE INDEX.
>
> Question: How do I get a UNIQUE INDEX to follow the collation assigned to a
> column?
>
> NOTE: in the below examples, I also tried assigning the collation during
> index
> creation rather than table creation ... didn't help any.
>
> -- collation named PATH was loaded via load_extension
> sqlite> create table test (str text collate path);
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> abc
> sqlite> select * from test where str = 'abC';
> abc
> sqlite> select * from test where str = 'aBC';
> abc
>
> The above looks great. Now add the unique index...
>
> sqlite> create unique index myidx on test (str);
> sqlite> insert into test values ('abc');
>
> The above is the first problem. The unique index should of detected that
> the
> value 'abc' already existed in a 'str' column.
>
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> The next problem is that the above select does not find any matching
> records, as
> it did prior to the unique index being added. I thought it could be because
> the
> table is messed up due to duplicate column values? So I did the below:
>
> sqlite> delete from test;
> sqlite> insert into test values ('abc');
> sqlite> select * from test where str = 'abc';
> sqlite>
>
> Still no result. I then removed the unique index:
>
> sqlite> drop index myidx;
> sqlite> select * from test where str = 'ABC';
> abc
> sqlite>
>
> Thanks,
> csmith
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] User-defined collation UNIQUE INDEX
Does this work?
CREATE UNIQUE INDEX myidx ON test(str COLLATE path);
Robert
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of C. Smith
Sent: Sunday, July 20, 2008 9:05 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] User-defined collation UNIQUE INDEX
I am trying to add a very simple collation via load_extension. The
collation is
a case-insensitive wchar compare for windows (using _wcsicmp). It is
designed
for windows pathnames being stored in utf16. All works fine until I try to
add
the final touch, a UNIQUE INDEX.
Question: How do I get a UNIQUE INDEX to follow the collation assigned to a
column?
NOTE: in the below examples, I also tried assigning the collation during
index
creation rather than table creation ... didn't help any.
-- collation named PATH was loaded via load_extension
sqlite> create table test (str text collate path);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
abc
sqlite> select * from test where str = 'abC';
abc
sqlite> select * from test where str = 'aBC';
abc
The above looks great. Now add the unique index...
sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');
The above is the first problem. The unique index should of detected that
the
value 'abc' already existed in a 'str' column.
sqlite> select * from test where str = 'abc';
sqlite>
The next problem is that the above select does not find any matching
records, as
it did prior to the unique index being added. I thought it could be because
the
table is messed up due to duplicate column values? So I did the below:
sqlite> delete from test;
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
sqlite>
Still no result. I then removed the unique index:
sqlite> drop index myidx;
sqlite> select * from test where str = 'ABC';
abc
sqlite>
Thanks,
csmith
___
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] User-defined collation UNIQUE INDEX
I am trying to add a very simple collation via load_extension. The collation
is
a case-insensitive wchar compare for windows (using _wcsicmp). It is designed
for windows pathnames being stored in utf16. All works fine until I try to add
the final touch, a UNIQUE INDEX.
Question: How do I get a UNIQUE INDEX to follow the collation assigned to a
column?
NOTE: in the below examples, I also tried assigning the collation during index
creation rather than table creation ... didn't help any.
-- collation named PATH was loaded via load_extension
sqlite> create table test (str text collate path);
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
abc
sqlite> select * from test where str = 'abC';
abc
sqlite> select * from test where str = 'aBC';
abc
The above looks great. Now add the unique index...
sqlite> create unique index myidx on test (str);
sqlite> insert into test values ('abc');
The above is the first problem. The unique index should of detected that the
value 'abc' already existed in a 'str' column.
sqlite> select * from test where str = 'abc';
sqlite>
The next problem is that the above select does not find any matching records,
as
it did prior to the unique index being added. I thought it could be because
the
table is messed up due to duplicate column values? So I did the below:
sqlite> delete from test;
sqlite> insert into test values ('abc');
sqlite> select * from test where str = 'abc';
sqlite>
Still no result. I then removed the unique index:
sqlite> drop index myidx;
sqlite> select * from test where str = 'ABC';
abc
sqlite>
Thanks,
csmith
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare_v2 and error reporting
The list seems to have mysteriously eaten my attachment (perhaps a bad
configuration setting):
#include
#include
#include
int main()
{
{
sqlite3 *pDatabase;
int iRet = sqlite3_open("foo", );
assert(iRet == SQLITE_OK);
sqlite3_stmt *m_pStatement;
iRet = sqlite3_prepare( pDatabase, "ROLLBACK;", 9,
_pStatement, NULL);
assert(iRet == SQLITE_OK);
iRet = sqlite3_step( m_pStatement );
assert(iRet == SQLITE_ERROR);
iRet = sqlite3_reset(m_pStatement);
printf("%s\n", sqlite3_errmsg(pDatabase));
}
{
sqlite3 *pDatabase;
int iRet = sqlite3_open("foo", );
assert(iRet == SQLITE_OK);
sqlite3_stmt *m_pStatement;
iRet = sqlite3_prepare_v2( pDatabase, "ROLLBACK;", 9,
_pStatement, NULL);
assert(iRet == SQLITE_OK);
iRet = sqlite3_step( m_pStatement );
assert(iRet == SQLITE_ERROR);
// iRet = sqlite3_reset(m_pStatement);
printf("%s\n", sqlite3_errmsg(pDatabase));
}
}
--
Glenn Maynard
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_prepare_v2 and error reporting
Why does the attached test case show the expected "cannot rollback - no transaction is active" error with the v1 interface, but the generic "SQL logic error or missing database" error when using the v2 interface? If I add a sqlite3_reset to the v2 interface case (treating it like v1), I get the expected error message. (tested on 3.6.0) -- Glenn Maynard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrent access by multiple processes
"Sherief N. Farouk" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Does SQLite safely handle concurrent access by multiple processes > reading/writing from the same db? Yes. It implements a database-wide multiple-readers-single-writer lock. > Are there any platform exceptions > to that? http://sqlite.org/lockingv3.html , in particular section 6. http://sqlite.org/atomiccommit.html , in particular section 9 Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Success stories
Thanks for sharing your experience, would you please introduce how you deal with the concurrency? Regards, Kevin. > Hello! > > I did migrate two projects from PostgreSQL (one is ~22 Gb database with very > complex reports) and now migrate oracle project (>100 Gb billing database > with distributed data collectors) for best performance on multi-core servers > and SATA disks. SQLite database may be replicated or copied very simple and > it's very useful property for distributed projects. I did use SQLite some > years on linux servers && windows desktops && linux desktops && winmobile > PDA/Smartphones and now I'm know that SQLite is sure remedy and I can > eliminate PostgreSQL/Oracle from my projects completely. > > My results is up to 60x better performance and more quality C && tcl code and > more fast development process. > > There are some patterns for hight load SQLIte projects development in SQLite > Wiki. But successful examples of big projects is more comprehensive knowledge > for developers. > > Thanks for excellent database engine! > > Best regards, Alexey. > ___ > 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] Database locking and blocking
When a process gets SQLITE_BUSY, it needs to wait for the blocking operation to finish before it can successfully retry (whether it's just retrying a COMMIT or redoing a whole transaction). How do people typically handle this? If a different process is the one holding the lock, then you need to sleep for a while, or sleep(0) to yield the scheduler. (This seems to be the case sqlite3_busy_* has in mind.) However, in a state-engine application where multiple independent tasks in one thread might have interleaved, "simultaneous" access to the database, then you need to stash away what you're doing and let the other tasks run, so the one blocking you can finish. If you're in a state engine (or Lua coroutines, etc.), it's hard to tell which of these cases you're in. If you guess incorrectly, you'll either chew CPU by not yielding the scheduler, or you'll yield the scheduler when the blocking task is in the same process. Both of these can be pretty bad failure modes. I may end up just never leaving a transaction unfinished between state engine updates, so there are never half-finished transactions sitting around holding locks (guaranteeing that the correct thing to do is sleep). That's not good for large, expensive transactions, though, where other tasks should be given a chance to run. -- Glenn Maynard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
"Alexey Pechnikov" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > ? ? ?? Sunday 20 July 2008 21:20:19 Jay A. Kreibich > ???(?): >> The good news is that you can re-implement the LIKE function fairly >> easily. There have been a number of posts in the past dealing with >> using external Unicode/I18N libraries to implement a more complete >> 'LIKE' function. > > There is unicode extension in /ext/icu of SQLite source code. With ICU extension, LIKE operator may consider Ö and ö to be equal, but is still unlikely to make ö and o equal. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] busy_timeout and shared_cache
Hi all! I usually set the sqlite3_busy_timeout to 10 seconds or something like that to make sure that my db isn't locked by any other connection at the same time. This way I usually do not need to check for SQLITE_BUSY. Now I just tried out the sqlite3_enable_shared_cache and has enabled shared cache on 3 different threads connected to the same db. The funny thing is that now the bust_timeout seems to fail. Instead sqlite3_step will now return SQLITE_LOCKED every now and then (and I can assure you that the timeout has not been reached). Is this a bug, or is this an undocumented expected behavior? BTW: I'm using SQLite 3.6.0 compiled from amalgamation with VS2005. Best regards Daniel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
В сообщении от Sunday 20 July 2008 23:39:34 Alexey Pechnikov написал(а): > > The good news is that you can re-implement the LIKE function fairly > > easily. There have been a number of posts in the past dealing with > > using external Unicode/I18N libraries to implement a more complete > > 'LIKE' function. > > There is unicode extension in /ext/icu of SQLite source code. I can't > compile this extension but source code is simple and I think compilation is > not very hard. icu extension from SQLite 3.5.9 on my linux debian etch box compiling and working fine now. On debian lenny there are some dependence problems but lenny is unstable distribution. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
В сообщении от Sunday 20 July 2008 21:20:19 Jay A. Kreibich написал(а): > The good news is that you can re-implement the LIKE function fairly > easily. There have been a number of posts in the past dealing with > using external Unicode/I18N libraries to implement a more complete > 'LIKE' function. There is unicode extension in /ext/icu of SQLite source code. I can't compile this extension but source code is simple and I think compilation is not very hard. From readme file: === This directory contains source code for the SQLite "ICU" extension, an integration of the "International Components for Unicode" library with SQLite. Documentation follows. 1. Features 1.1 SQL Scalars upper() and lower() 1.2 Unicode Aware LIKE Operator 1.3 ICU Collation Sequences 1.4 SQL REGEXP Operator === ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Amount of memory for caching one page with x byte page size?
Hello, to answer the following question: Amount of memory for caching one page with x byte page size? I found in the Draft 3.6.0 Doc the following information: PRAGMA page_size = bytes; Query or set the page size of the database. The page size may only be set if the database has not yet been created. The page size must be a power of two greater than or equal to 512 and less than or equal to SQLITE_MAX_PAGE_SIZE. The maximum value for SQLITE_MAX_PAGE_SIZE is 32768. PRAGMA default_cache_size = Number-of-pages; Query or change the maximum number of database disk pages that SQLite will hold in memory at once. Each page uses 1K on disk and about 1.5K in memory. ... Obviously you have an overhand per page-size to calculate the memory requirement for caching on page. Do I understand this right? kind regards Rainer ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Diacritics (umlaut) select in SQLite
On Sun, Jul 20, 2008 at 07:09:54PM +0200, william sqllite scratched on the wall: > Hi all, > > We're trying to get a big dictionary website running on SQLite (was MySQL), > but we ran into some trouble that we can't really seem to fix. > > While we're using UTF-8 coding, a query like > > SELECT * FROM language WHERE word like '%o%' > > doesn't find words with Ö or ö, while it did in MySQL. Also the case with > for example a for àáâãä and å. > > Is it just a simple configuration thing maybe? Or is sqlite just not ready > for these kind of searches? Known issue. From http://www.sqlite.org/lang_expr.html on the 'LIKE' operator: SQLite only understands upper/lower case for 7-bit Latin characters. Hence the LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' LIKE 'Æ' is FALSE.) This isn't the exact problem you're having, but the point is that SQLite is not very Unicode aware, beyond correctly supporting encoding and decoding. And that's to be expected, given the huge complexities of dealing with different languages. The good news is that you can re-implement the LIKE function fairly easily. There have been a number of posts in the past dealing with using external Unicode/I18N libraries to implement a more complete 'LIKE' function. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "'People who live in bamboo houses should not throw pandas.' Jesus said that." - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Diacritics (umlaut) select in SQLite
Hi all, We're trying to get a big dictionary website running on SQLite (was MySQL), but we ran into some trouble that we can't really seem to fix. While we're using UTF-8 coding, a query like SELECT * FROM language WHERE word like '%o%' doesn't find words with Ö or ö, while it did in MySQL. Also the case with for example a for àáâãä and å. Is it just a simple configuration thing maybe? Or is sqlite just not ready for these kind of searches? -William ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG in RTree ?
On Sun, 20 Jul 2008 09:03:44 +0200, you wrote: >Hello to all, > >I think I have found a bug in the RTree extension (I'm using version 3.6.0) >If I run this script : It works perfectly for me. Here is my version of the script: sqlite_version():3.6.0 DROP TABLE IF EXISTS GEO_TEST; DROP TABLE IF EXISTS SI_GEO_TEST; CREATE TABLE GEO_TEST (FID INTEGER PRIMARY KEY NOT NULL, NAME CHAR NOT NULL, E_UTMX REAL, E_UTMY REAL); CREATE VIRTUAL TABLE SI_GEO_TEST USING rtree(si_pkid_si, si_xmin_si, si_xmax_si, si_ymin_si, si_ymax_si); CREATE TRIGGER TSII_GEO_TEST AFTER INSERT ON GEO_TEST FOR EACH ROW BEGIN INSERT INTO SI_GEO_TEST (si_pkid_si, si_xmin_si, si_xmax_si, si_ymin_si, si_ymax_si) VALUES (NEW.ROWID, NEW.E_UTMX, NEW.E_UTMX, NEW.E_UTMY, NEW.E_UTMY); END; CREATE TRIGGER TSIU_GEO_TEST AFTER UPDATE ON GEO_TEST FOR EACH ROW BEGIN UPDATE SI_GEO_TEST SET si_xmin_si = NEW.E_UTMX, si_xmax_si = NEW.E_UTMX, si_ymin_si = NEW.E_UTMY, si_ymax_si = NEW.E_UTMY WHERE si_pkid_si = NEW.ROWID; END; CREATE TRIGGER TSID_GEO_TEST AFTER DELETE ON GEO_TEST FOR EACH ROW BEGIN DELETE FROM SI_GEO_TEST WHERE si_pkid_si = OLD.ROWID; END; BEGIN; INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1, 'A', 10.0, 10.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2, 'B', 20.0, 20.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (3, 'C', 30.0, 30.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (4, 'D', 40.0, 40.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (5, 'E', 50.0, 50.0); END; SELECT * FROM GEO_TEST; 1|A|10.0|10.0 2|B|20.0|20.0 3|C|30.0|30.0 4|D|40.0|40.0 5|E|50.0|50.0 SELECT * FROM SI_GEO_TEST; 1|10.0|10.0|10.0|10.0 2|20.0|20.0|20.0|20.0 3|30.0|30.0|30.0|30.0 4|40.0|40.0|40.0|40.0 5|50.0|50.0|50.0|50.0 > >The first row is not inserted in the GEO_TEST table. > >If I do the inserts this way (with the first insert out of the transaction): > >INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1, 'A', >10.0, 10.0); >BEGIN; >INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2, 'B', >20.0, 20.0); [snip] >then every thing goes right. > >Am I doing something wrong ? I don't see any errors, I copied your code verbatim, only changed some indentation. >Thanks in advance. > >Xevi -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] BUG in RTree ?
Hello to all, I think I have found a bug in the RTree extension (I'm using version 3.6.0) If I run this script : DROP TABLE IF EXISTS GEO_TEST; DROP TABLE IF EXISTS SI_GEO_TEST; CREATE TABLE GEO_TEST (FID INTEGER PRIMARY KEY NOT NULL, NAME CHAR NOT NULL, E_UTMX REAL, E_UTMY REAL); CREATE VIRTUAL TABLE SI_GEO_TEST USING rtree(si_pkid_si, si_xmin_si, si_xmax_si, si_ymin_si, si_ymax_si); CREATE TRIGGER TSII_GEO_TEST AFTER INSERT ON GEO_TEST FOR EACH ROW BEGIN INSERT INTO SI_GEO_TEST (si_pkid_si, si_xmin_si, si_xmax_si, si_ymin_si, si_ymax_si) VALUES (NEW.ROWID, NEW.E_UTMX, NEW.E_UTMX, NEW.E_UTMY, NEW.E_UTMY); END; CREATE TRIGGER TSIU_GEO_TEST AFTER UPDATE ON GEO_TEST FOR EACH ROW BEGIN UPDATE SI_GEO_TEST SET si_xmin_si = NEW.E_UTMX, si_xmax_si = NEW.E_UTMX, si_ymin_si = NEW.E_UTMY, si_ymax_si = NEW.E_UTMY WHERE si_pkid_si = NEW.ROWID; END; CREATE TRIGGER TSID_GEO_TEST AFTER DELETE ON GEO_TEST FOR EACH ROW BEGIN DELETE FROM SI_GEO_TEST WHERE si_pkid_si = OLD.ROWID; END; BEGIN; INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1, 'A', 10.0, 10.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2, 'B', 20.0, 20.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (3, 'C', 30.0, 30.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (4, 'D', 40.0, 40.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (5, 'E', 50.0, 50.0); END; The first row is not inserted in the GEO_TEST table. If I do the inserts this way (with the first insert out of the transaction): INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (1, 'A', 10.0, 10.0); BEGIN; INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (2, 'B', 20.0, 20.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (3, 'C', 30.0, 30.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (4, 'D', 40.0, 40.0); INSERT INTO GEO_TEST(FID, NAME, E_UTMX, E_UTMY) VALUES (5, 'E', 50.0, 50.0); END; then every thing goes right. Am I doing something wrong ? Thanks in advance. Xevi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
