[sqlite] Enable fts?
Hi all, How can I enable fts1 or fts2 modules? Do I need any changes in configure.ac or Makefile.in? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O and shared cache
Thanks, I will try to use the shared cache with Async I/O Each shared-cache has its own mutex... So, does it possible to have more than one shared cache within a single process ? One shared cache by db ? -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26405154.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
[sqlite] Problem passing SQLite field values to TCL function
I start off passing a central latitude ($lat_degrees) and longitude
($long_degrees) and a radius ($radius) to a TCL script. Using the
spherical cosine law to calculate distance, I want to select all sites
in a table within that given radius. Here are 2 code fragments from the
script...
===
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time. So the conversion is done internally in the distance() function.
proc sql_distance { lat1 long1 lat2 long2 } {
set radian [expr 180 / 3.1415926]
set lat1 [expr $lat1 / $radian ]
set long1 [expr $long1 / $radian * (-1) ]
set lat2 [expr $lat2 / $radian ]
set long2 [expr $long2 / $radian ]
return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 -
$long1)) * 6371}]}
db function distance sql_distance
...
db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation, stn_name,
distance( $lat_degrees, $long_degrees, deci_lat, deci_long) as dist
from cl.stations
where dist = $radius}
===
$lat_degrees and $long_degrees are defined
deci_lat and deci_long are valid field names (type real) in table
stations in the attached database (alias cl). I get an error message
which tells me that deci_lat and deci_long are not defined. I've run a
separate test to confirm that every row has non-null numbers in deci_lat
and deci_long. So that's not the problem. Here's the error message...
missing operand at _...@_
in expression _...@_/ 57.295780490442965
(parsing expression / 57.295780490442965)
invoked from within
expr $lat2 / $radian
(procedure sql_distance line 5)
invoked from within
sql_distance 49.25 123 {} {}
invoked from within
db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation,
stn_name,
distance( $lat_degrees, $long...
invoked from within
if { $argc 4 } {
puts Error: This query requires at least 4 parameters, namely
puts Central Latitude, Central Longitude, Radius, and at least...
(file ./tcltest.004 line 2
--
Walter Dnes waltd...@waltdnes.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] bad data in a database file or bug?
On Nov 17, 2009, at 9:49 PM, D. Richard Hipp wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com The database file is corrupt, but in a way that PRAGMA integrity_check does not detect. A single byte at an offset of 13568 into the file seems to have been changed from 0x0c into 0x00. How was this database created? Can you recreate this problem from scratch? D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O and shared cache
So, does it possible to have more than one shared cache within a single process ? Open the same database twice, using two different handles. At least I think it will work. Nope, it won't. That's the purpose of shared cache: if you open the same database several times with different connections they all will use the same cache instance (and in fact all use only one file handle for I/O operations). Although probably opening the same database file using different paths (like e.g. /some/path/to/db and /some/path/./to/db) will work and will force SQLite to use different instances of cache for such connections. Pavel On Wed, Nov 18, 2009 at 5:11 AM, Simon Slavin slav...@bigfraud.org wrote: On 18 Nov 2009, at 10:00am, presta wrote: So, does it possible to have more than one shared cache within a single process ? Open the same database twice, using two different handles. At least I think it will work. Simon. ___ 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] Asynchronous I/O and shared cache
To be more precise I would like to parallelize writes operations on different tables, so potentially in different db (files). It's why I think about using multi databases (1 by table), the shared cache system and the asynchronized I/O.. So if a shared cache is shared accross different databases, writes operation will be serialized, so according to all reply it seems that a shared cache is create for each different db instance ?? -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26407565.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] Asynchronous I/O and shared cache
Shared cache instance is created on file-by-file basis, i.e. if you open connections to file1.db and file2.db they will have different cache instances and any manipulations with these database files won't influence one another at all (any write operations can be executed in parallel). But if you open several connections to the same database file (from the same process of course) they all will use the same instance of shared cache (hence the word 'shared'). In this case all write operations will be serialized but only on table-by-table basis, i.e. if one connection is doing some updates in one table and another connection is doing updates in another table they will be able to be executed in parallel. But if different connections try to update the same table(s) they will be serialized. So for the purpose of parallelizing transactions working with different tables you don't have to split them into different databases - with shared cache they will be parallelized pretty effectively in the same file too Pavel. On Wed, Nov 18, 2009 at 8:19 AM, presta harc...@gmail.com wrote: To be more precise I would like to parallelize writes operations on different tables, so potentially in different db (files). It's why I think about using multi databases (1 by table), the shared cache system and the asynchronized I/O.. So if a shared cache is shared accross different databases, writes operation will be serialized, so according to all reply it seems that a shared cache is create for each different db instance ?? -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26407565.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O and shared cache
I'm confused according to Dan Kennedy : Each shared-cache has its own mutex. The mutex is held for the duration of each sqlite3_step() call. So the way you're defining it here, you can't have real concurrency when using shared-cache mode in any case. So, it's a little bit antagonist to say with shared cache they will be parallelized pretty effectively in the same file too -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26407922.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
[sqlite] another Feature Request: char from codepoint?
I wanted to get a clearer sense of how SQLite treats decomposed Unicode characters (http://unicode.org/faq/char_combmark.html#2) in its various string functions and with the concatenation operator yet there doesn't seem to be any way to get them into the database using the SQlite3.EXE command-line. I didn't see a function like SQLServer's NCHAR() that returns a character (~Unicode string) from a codepoint, which would enable something along these lines from the command-line: insert test (id, myTextColumn) values(1, unicharFromCodePoint(437) || unicharFromCodePoint(807) ) -- Latin capital letter Z with stroke (U+01B5) || a combining cedilla (U+0327) This would be a convenient function to have. Are there technical issues/obstacles? Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O and shared cache
I don't know what Dan meant by his words but AFAIK there's no mutex making exclusive grab of shared cache by sqlite3_step() call. There is only mutex making sqlite3_step() execution exclusive for connection object. Pavel On Wed, Nov 18, 2009 at 8:40 AM, presta harc...@gmail.com wrote: I'm confused according to Dan Kennedy : Each shared-cache has its own mutex. The mutex is held for the duration of each sqlite3_step() call. So the way you're defining it here, you can't have real concurrency when using shared-cache mode in any case. So, it's a little bit antagonist to say with shared cache they will be parallelized pretty effectively in the same file too -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26407922.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
Tim Romano tim.rom...@yahoo.com wrote: I wanted to get a clearer sense of how SQLite treats decomposed Unicode characters (http://unicode.org/faq/char_combmark.html#2) in its various string functions and with the concatenation operator yet there doesn't seem to be any way to get them into the database using the SQlite3.EXE command-line. select cast(x'41' as text); A The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I believe. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
Hi Igor, The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I believe. That means the user enters the hex UTF-8 (or 16 depending on base encoding) representation of the character. E.g.: select cast(x'c389' as text); É Something like: select chrw(x'c9'); or select chrw(201); would be much more convenient and not depend on base encoding IMHO. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I believe. I think it should be a database encoding which is either UTF-8 or UTF-16. So for Tim's case if his database encoding is UTF-16 insert statement can be like this: insert test (id, myTextColumn) values(1, x'01B50327') I'm not sure about byte order here but it can be easily determined with some tests. Pavel On Wed, Nov 18, 2009 at 11:13 AM, Igor Tandetnik itandet...@mvps.org wrote: Tim Romano tim.rom...@yahoo.com wrote: I wanted to get a clearer sense of how SQLite treats decomposed Unicode characters (http://unicode.org/faq/char_combmark.html#2) in its various string functions and with the concatenation operator yet there doesn't seem to be any way to get them into the database using the SQlite3.EXE command-line. select cast(x'41' as text); A The blob (x'41' is a blob literal) is expected to contain a UTF-8 sequence, I believe. Igor Tandetnik ___ 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] bad data in a database file or bug?
On Wed, Nov 18, 2009 at 4:06 AM, D. Richard Hipp d...@hwaci.com wrote: On Nov 17, 2009, at 9:49 PM, D. Richard Hipp wrote: On Nov 17, 2009, at 9:45 PM, Vasu Nori wrote: sqlite SELECT _id, typeof(_id) FROM feeds; 1|integer 2|integer 3|integer 4|integer 5|integer 6|integer 7|integer 8|integer 9|integer 10|integer 11|integer 0|integer 13|integer 14|integer Please email your database directly to me for analysis. d...@hwaci.com The database file is corrupt, but in a way that PRAGMA integrity_check does not detect. A single byte at an offset of 13568 into the file seems to have been changed from 0x0c into 0x00. How was this database created? Can you recreate this problem from scratch? database is created by an android application on a phone (running android). nothing extraordinary about this application's database creation. It is not reproducible that easily. any theories or hints on how one could debug this? D. Richard Hipp d...@hwaci.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] another Feature Request: char from codepoint?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Romano wrote: This would be a convenient function to have. Are there technical issues/obstacles? In general for all these feature requests for more functions there is no need for them to be added to the SQLite core. There is a very simple API and extension mechanism whereby you can add your own functions that do exactly what you want. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEN2kACgkQmOOfHg372QTmXgCglwgqyLB6ouPgOmj0M8s3wD3M cEYAoKD2bWVUnvR4e7DnaBFC6kVbOM+c =t3xA -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL error: disk I/O error and symlink
Hi. I have a strange problem. I have a database a.db.1 and symlink a.db which points to a.db.1 When I use command line sqlite3 command I get following. % echo select max(id) from t; | sqlite3 a.db.1 SQL error near line 1: disk I/O error but % echo select max(id) from t; | sqlite3 a.db 4461066 Could that be explained? I use sqlite3 version 3.6.7 -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: disk I/O error and symlink
On Nov 18, 2009, at 1:53 PM, priimak wrote: Hi. I have a strange problem. I have a database a.db.1 and symlink a.db which points to a.db.1 When I use command line sqlite3 command I get following. % echo select max(id) from t; | sqlite3 a.db.1 SQL error near line 1: disk I/O error but % echo select max(id) from t; | sqlite3 a.db 4461066 Could that be explained? I use sqlite3 version 3.6.7 Having multiple names for the same database file (either symbolic links or hard links) can lead to database corruption following an application crash if a different application reopens the same database via a different name. See paragraph 9.5 in http://www.sqlite.org/atomiccommit to understand why. Please do not create aliases of any kind for your database files. SQLite uses O_NOFOLLOW, not for this reason, but to prevent an attack using symbolic links that could allow an unprivileged user to trick SQLite into deleting a file that the unprivileged user does not have access rights for. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: disk I/O error and symlink
D. Richard Hipp wrote: On Nov 18, 2009, at 1:53 PM, priimak wrote: Hi. I have a strange problem. I have a database a.db.1 and symlink a.db which points to a.db.1 When I use command line sqlite3 command I get following. % echo select max(id) from t; | sqlite3 a.db.1 SQL error near line 1: disk I/O error but % echo select max(id) from t; | sqlite3 a.db 4461066 Could that be explained? I use sqlite3 version 3.6.7 Having multiple names for the same database file (either symbolic links or hard links) can lead to database corruption following an application crash if a different application reopens the same database via a different name. See paragraph 9.5 in http://www.sqlite.org/atomiccommit to understand why. Please do not create aliases of any kind for your database files. I see. However, the problem is related to journal been named after the database name. That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
On 18 Nov 2009, at 6:05pm, Roger Binns wrote: Tim Romano wrote: This would be a convenient function to have. Are there technical issues/obstacles? In general for all these feature requests for more functions there is no need for them to be added to the SQLite core. There is a very simple API and extension mechanism whereby you can add your own functions that do exactly what you want. I googled, and there doesn't seem to be a page which describes the abilities and limitations the extension system has. I'd like to check, then, with someone who understands how to write extensions. (A) Is it possible to define a new datatypen ? If the data itself was really stored in a BLOB, that would be okay, but I wouldn't want my users to know that, I'd want them to use this new datatype when they created columns. (B) If not, then if I needed to store some additional information for every TEXT field, could I do that ? If it was necessary to store it in a different table in the same database, e.g. to define a new table with tablename fieldname rowid columnname language that would be acceptable though very slow, but can an extension do that sort of thing ? And can a routine of an extension be automatically called whenever any row, column or table in the database is created/deleted ? (C) Is it possible to put a note inside a database file so that if a database was opened without a particular extension being loaded, an error would occur ? For example a SQLITE error code, with error text something like This database cannot be used without version 1.1 or higher of the extension 'complex numbers' being loaded. ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk activity on Linux
Thanks Walter and Paul I'll look into the things you pointed out and let you guys know how it turns out. Open Source approval at IBM is version specific and the approval process is quite a bit of work so I'd like to avoid it as long as possible. Bret Patterson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: disk I/O error and symlink
On 18 Nov 2009, at 7:15pm, priimak wrote: I see. However, the problem is related to journal been named after the database name. That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? If you use a symbolic link for a database file, there's some doubt about where the journal file belongs. The documentation says The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters -journal appended. The problem is that there's no way for sqlite to tell that your symbolic link is not the real database file. So it could create the journal file in the directory containing the link rather than the directory containing the actual database. And if another application opens the database directly rather than using the link, it will not find the journal file, so it will not deal with anything needing the journal file correctly. This is mentioned in section 6 of http://www.sqlite.org/lockingv3.html If that documentation is right, then for symbolic links, that might be considered a bug in sqlite. It should be possible to do a 'resolve links' on the file specification before deciding where to create the journal. On the other hand, for hard links this is not possible: the hard linking system does not have a 'real' location for the file and other pointers to it, instead each link to the file is equally valid. But either way it is documented that you shouldn't use links. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] listing free bytes in sqlite files
Hello, Given a sqlite database file I would like to create two lists: one of bytes which are in use and one of bytes which are free. What would be the most efficient way to do this? I have taken a look over the file format documentation but can't see a clear way to achieve this. Thanks, Matt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing free bytes in sqlite files
On 18 Nov 2009, at 8:31pm, Matt wrote: Given a sqlite database file I would like to create two lists: one of bytes which are in use and one of bytes which are free. What would be the most efficient way to do this? I have taken a look over the file format documentation but can't see a clear way to achieve this. You have to implement a routine that understands the file format and walks through the data structure. Or you could do it any number of silly ways, like change random bytes then see whether the file content has changed or the PRAGMA reports corruption. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: disk I/O error and symlink
That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? I believe there can be a problem with such scenario. I don't know exactly but I doubt that file system can operate with locks on file handle open via symlink and via actual name interchangeably. And if it doesn't your reader can read corrupted data if it reads in the middle of writer's transaction... Pavel On Wed, Nov 18, 2009 at 2:15 PM, priimak prii...@stanford.edu wrote: D. Richard Hipp wrote: On Nov 18, 2009, at 1:53 PM, priimak wrote: Hi. I have a strange problem. I have a database a.db.1 and symlink a.db which points to a.db.1 When I use command line sqlite3 command I get following. % echo select max(id) from t; | sqlite3 a.db.1 SQL error near line 1: disk I/O error but % echo select max(id) from t; | sqlite3 a.db 4461066 Could that be explained? I use sqlite3 version 3.6.7 Having multiple names for the same database file (either symbolic links or hard links) can lead to database corruption following an application crash if a different application reopens the same database via a different name. See paragraph 9.5 in http://www.sqlite.org/atomiccommit to understand why. Please do not create aliases of any kind for your database files. I see. However, the problem is related to journal been named after the database name. That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? -- Dmitri Priimak ___ 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] SQL error: disk I/O error and symlink
Simon Slavin wrote: On 18 Nov 2009, at 7:15pm, priimak wrote: I see. However, the problem is related to journal been named after the database name. That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? If you use a symbolic link for a database file, there's some doubt about where the journal file belongs. The documentation says The rollback journal is always located in the same directory as the database file and has the same name as the database file except with the 8 characters -journal appended. The problem is that there's no way for sqlite to tell that your symbolic link is not the real database file. So it could create the journal file in the directory containing the link rather than the directory containing the actual database. And if another application opens the database directly rather than using the link, it will not find the journal file, so it will not deal with anything needing the journal file correctly. This is mentioned in section 6 of http://www.sqlite.org/lockingv3.html If that documentation is right, then for symbolic links, that might be considered a bug in sqlite. It should be possible to do a 'resolve links' on the file specification before deciding where to create the journal. On the other hand, for hard links this is not possible: the hard linking system does not have a 'real' location for the file and other pointers to it, instead each link to the file is equally valid. But either way it is documented that you shouldn't use links. I understand that. However, that would not present a problem if I access database by its symlink for reading only and database directly if I am updating isn't? By the way the reason for that usage ( though there are work arounds ) is that I keep two instances of db file a.db.1 and a.db.2 with symlink a.db pointing to one or there db file. If a.db - a.db.1 I apply update to a.db.2, swap symlink and then update a.db.2. Symlink a.db used by webapp, which performs *only* select queries. And yes, I do have a.db.1-journal and a.db.2-journal files, but not a.db-journal That seems safe, isn't? -- Dmitri Priimak ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: disk I/O error and symlink
Pavel Ivanov wrote: That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? I believe there can be a problem with such scenario. I don't know exactly but I doubt that file system can operate with locks on file handle open via symlink and via actual name interchangeably. And if it doesn't your reader can read corrupted data if it reads in the middle of writer's transaction... I see and can certainly change that, but you can see that my problem is sort of opposite. I can also make any other symlink to a.db.1 then access db through it, but not directly. For example I can do % ln -s a.db.1 foobar.db and then access it through foobar.db, but not directly. What could cause such problem? -- Dmitri Priimak Pavel On Wed, Nov 18, 2009 at 2:15 PM, priimak prii...@stanford.edu wrote: D. Richard Hipp wrote: On Nov 18, 2009, at 1:53 PM, priimak wrote: Hi. I have a strange problem. I have a database a.db.1 and symlink a.db which points to a.db.1 When I use command line sqlite3 command I get following. % echo select max(id) from t; | sqlite3 a.db.1 SQL error near line 1: disk I/O error but % echo select max(id) from t; | sqlite3 a.db 4461066 Could that be explained? I use sqlite3 version 3.6.7 Having multiple names for the same database file (either symbolic links or hard links) can lead to database corruption following an application crash if a different application reopens the same database via a different name. See paragraph 9.5 in http://www.sqlite.org/atomiccommit to understand why. Please do not create aliases of any kind for your database files. I see. However, the problem is related to journal been named after the database name. That should not be a problem if I use symlink to access database for reads only and actual db file name for updating, isn't? -- Dmitri Priimak ___ 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] SQL error: disk I/O error and symlink
On 18 Nov 2009, at 8:38pm, priimak wrote: I understand that. However, that would not present a problem if I access database by its symlink for reading only and database directly if I am updating isn't? According to the documentation that could be a problem. The update application creates a journal file in the directory where the database file actually is. The application which reads the database file looks for a journal in the directory where the alias is, and doesn't find it. Please note that I haven't read the source code for sqlite3, and I don't know if that's really how it works. By the way the reason for that usage ( though there are work arounds ) is that I keep two instances of db file a.db.1 and a.db.2 with symlink a.db pointing to one or there db file. If a.db - a.db.1 I apply update to a.db.2, swap symlink and then update a.db.2. Symlink a.db used by webapp, which performs *only* select queries. Rather than use a symbolic link, store something in the database files themselves which says which one is being updated. Or use two different tables in the same database file. I understand your use of symbolic links and it makes sense with atomic files -- files where you open the file, read the whole thing, then close it. But it would seem to be a problem with SQLite files. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing free bytes in sqlite files
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matt wrote: Given a sqlite database file I would like to create two lists: one of bytes which are in use and one of bytes which are free. What would be the most efficient way to do this? What are you actually trying to achieve? If you are trying to work out how much slack space there is in the file then just turn on auto-vacuum which will remove slack space as appropriate. This inefficient method will give you an answer: - Make a backup using the backup API - Run vacuum on the backup - Compare that file size to current file size You should also note that SQLite works on pages not bytes. If you are trying to do steganography then you really do have to understand the file format. I have taken a look over the file format documentation but can't see a clear way to achieve this. The information is there. Look at the list of page types. Section 2.4 describes the free page list including a single field in the header saying how many free page list members there are. If you are also trying to find partially used pages then you'll have to iterate over each page of each type (section 2.2.2). Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEX30ACgkQmOOfHg372QQs/gCfWF7CG+UBXvxKagln+2xeB6d5 CbAAnjyIH1YROJNFqH2Qq7fGdOYv+rkE =25Ux -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] listing free bytes in sqlite files
On Wed, Nov 18, 2009 at 08:31:24PM -, Matt scratched on the wall: Hello, Given a sqlite database file I would like to create two lists: one of bytes which are in use and one of bytes which are free. What would be the most efficient way to do this? You have to define free. Blocks of bytes are managed in pages. There are free pages, and then there is free space within a page. Free pages can be re-used for nearly anything. Free space within a page is a lot harder to recover, and can only be used for specific things, depending on how the page is allocated. Free space within pages is just a fact of life, not unlike block allocations in a filesystem. PRAGMA page_count will return the total number of pages in a database file. PRAGMA freelist_count will return the number of free pages. PRAGMA page_size will return the number of bytes per page. You can expect a VACUUM to shrink a database file at least freelist_count * page_size bytes. Maybe a bit more if it can recover some of the intra-page free space. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Simon Slavin wrote: I googled, and there doesn't seem to be a page which describes the abilities and limitations the extension system has. A C function of your choosing can be called on each new connection being created: http://sqlite.org/c3ref/auto_extension.html You can create collations, functions, virtual tables and VFS: http://sqlite.org/c3ref/collation_needed.html http://sqlite.org/c3ref/create_collation.html http://sqlite.org/c3ref/create_function.html http://sqlite.org/c3ref/create_module_v2.html http://sqlite.org/c3ref/vfs_find.html [Details given to help future searches] (A) Is it possible to define a new datatypen ? At the SQLite level no. This is because a new datatype would require a change to the file format and would require adding APIs for that type (eg to bind values, receive values etc). If you wanted it visible in SQL then you'd also need new syntax which means updating the tokenizer and grammar. (Note: SQLite code is public domain so you are free to make an incompatible version with desired capabilities - it is just a lot of work.) If the data itself was really stored in a BLOB, that would be okay, but I wouldn't want my users to know that, I'd want them to use this new datatype when they created columns. You can do this by wrapping your access to the SQLite C API. For example some language bindings do it (but there are gotchas). I'll use an example of a Point object (with x and y members). You would need to provide an equivalent to the bind API: http://sqlite.org/c3ref/bind_blob.html For example you could make bind_point which then created the blob from the point data supplied and called sqlite3_bind_blob. You could even make this possible at the SQL level by a user defined function that returns a blob so this would work: INSERT INTO foo VALUES( Point(3,4) ); When values are returned you'd need some way of trying to extract a Point instead of a blob. In some cases you can use the declared type of a column: http://sqlite.org/c3ref/column_decltype.html However that only works when there are no calculations on a column. IIRC you also get NULL when going via a view. Note that existing SQLite functions are not going to know anything about your type. For example you'll be able to quite happily get the length of a Point and + or || operators certainly won't do what the user would expect. (B) If not, then if I needed to store some additional information for every TEXT field, could I do that ? If it was necessary to store it in a different table in the same database, That is trivial to do using virtual tables. but can an extension do that sort of thing Yes, extensions can implement virtual tables. And can a routine of an extension be automatically called whenever any row, column or table in the database is created/deleted ? Mostly, see the virtual table method list: http://sqlite.org/c3ref/module.html Note: you only get called with tables specifically created using your virtual table module. (C) Is it possible to put a note inside a database file so that if a database was opened without a particular extension being loaded, an error would occur ? No. That would require a file format change. However if the file has a virtual table and the module for that virtual table is not loaded then there will be an appropriate error message generated on first access to that virtual table. If you want to experiment with virtual tables and know Python then APSW is a good place to start (disclosure I am the author of APSW). http://apsw.googlecode.com/svn/publish/vtable.html http://apsw.googlecode.com/svn/publish/example.html#example-vtable Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEZsIACgkQmOOfHg372QSUoQCgyPr14la+YXkx67hxIBG9njhI otoAoIsR3ASbbuMoYxqbEwnaeBhOQr2O =N5UO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
I respectfully disagree, Roger, about the simplicity. Creating an external DLL would complicate my distribution scenario significantly because I'll have just as many users of my little database application working with Apple OSX as with Windows. I guarantee you, it would be easier for the very skilled programmers at SQLite to set these functions up to be included|excluded by throwing a few compiler switches (as they do for soundex() ) than for me to make my own external library for both OSX and Windows platforms. Basing that strictly on my own set of limited competencies. BTW, the Microsoft SQL Server 2000 reverse() function does it codepoint by codepoint, and their substring() function also splits combining characters from base characters; the len() function counts codepoints too, and does not merge combining forms and base forms into one unit. Regards Tim Romano Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Romano wrote: This would be a convenient function to have. Are there technical issues/obstacles? In general for all these feature requests for more functions there is no need for them to be added to the SQLite core. There is a very simple API and extension mechanism whereby you can add your own functions that do exactly what you want. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEN2kACgkQmOOfHg372QTmXgCglwgqyLB6ouPgOmj0M8s3wD3M cEYAoKD2bWVUnvR4e7DnaBFC6kVbOM+c =t3xA -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.425 / Virus Database: 270.14.72/2511 - Release Date: 11/18/09 07:50:00 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
Apologies. An accident -- I was trying to grab the To: address from a previous email and didn't realize there was a big thing attached. I don't see anything attached to this one. I hope there isn't. Tim Romano Jean-Christophe Deschamps wrote: Please don't set the ReplyTo field to the list when sending personal mail. There is no big secret here, but the list didn't appreciate this big thing and it desn't insert in a thread. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable fts?
Wednesday 18 of November 2009 09:39:27 Trung Nguyen Duc napisał(a): Hi all, How can I enable fts1 or fts2 modules? Do I need any changes in configure.ac or Makefile.in? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users It seems to be driven by SQLITE_ENABLE_FTS1 (...FTS2, ...FTS3) macro definitions. Try ./configure CFLAGS=-DSQLITE_ENABLE_FTS1 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Enable fts?
On Wed, Nov 18, 2009 at 4:28 PM, Grzegorz Wierzchowski gwierzchow...@wp.pl wrote: Wednesday 18 of November 2009 09:39:27 Trung Nguyen Duc napisał(a): Hi all, How can I enable fts1 or fts2 modules? Do I need any changes in configure.ac or Makefile.in? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users It seems to be driven by SQLITE_ENABLE_FTS1 (...FTS2, ...FTS3) macro definitions. Try ./configure CFLAGS=-DSQLITE_ENABLE_FTS1 I haven't checked, but I think the current source tree comes only with FTS3 which has superseded FTS1 and FTS2 for a while now. Any particular reason you want to use the older versions of FTS and not the latest? -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
Tim Romano tim.rom...@yahoo.com wrote: I respectfully disagree, Roger, about the simplicity. Creating an external DLL would complicate my distribution scenario significantly because I'll have just as many users of my little database application working with Apple OSX as with Windows. You don't need external DLL. You can implement your custom function directly in your application. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
On 18 Nov 2009, at 9:27pm, Roger Binns wrote: A C function of your choosing can be called on each new connection being created:[snip] Thanks for your detailed answer. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] another Feature Request: char from codepoint?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tim Romano wrote: I respectfully disagree, Roger, about the simplicity. Creating an external DLL would complicate my distribution scenario significantly because I'll have just as many users of my little database application working with Apple OSX as with Windows. I guarantee you, it would be easier for the very skilled programmers at SQLite to set these functions up to be included|excluded by throwing a few compiler switches (as they do for soundex() ) than for me to make my own external library for both OSX and Windows platforms. Basing that strictly on my own set of limited competencies. You don't have to get the code in the SQLite core. If you don't have the inhouse expertise then you can pay/convince anyone/someone else to do the work you want. The APIs are simple, the extension mechanism is simple and the documentation is excellent. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions (I just updated the build instructions on that page) Even if the functionality was added to the SQLite core, it would take a considerable amount of time until OSX included that updated code. You still have the issue that the code would likely be excluded by default just as soundex is, so it would still require re-compiling SQLite. It is easier to compile an extension module than to recompile SQLite. In addition the extension module would work with more versions of SQLite. BTW, the Microsoft SQL Server 2000 reverse() function does it codepoint by codepoint, And this exactly why it is better that you have control over the extension code. You can make it work exactly the way you want. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEgOAACgkQmOOfHg372QSgMgCfeSQM6vFDarq53dou4Bhb5yki PWIAn1vCLRzMBbjuaUmkv0KiWo+XHboa =7Z/3 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] behavior of collate on views changed from 3.6.2 to 3.6.16 and beyond - intentional?
I am noticing a behavior change in processing of collate from 3.6.2 to
versions 3.6.16 beyond.
it *could* make sense but I find some inconsistencies.. let me explain.
1. create a simple collation function which compares two input strings based
on just the second char (code is at the end of this email)
2. call this function twocase
3. create a table and populate with this sample data
sqlitecreate table foo(i text);
sqliteinsert into foo values(a2);
sqliteinsert into foo values(b1);
sqliteinsert into foo values(c5);
sqliteinsert into foo values(d4);
sqliteinsert into foo values(e3);
4. select from the table using orderby and collate
sqlite select * from foo order by i collate twocase;
b1
a2
e3
d4
c5
5. notice that the above are sorted on the 2nd char
6. create a view on the above table and select from the view with order by
and collate
sqlitecreate view foo_view as select i from foo;
sqliteselect * from foo_view;
sqliteselect * from foo_view order by i collate twocase;
a2
b1
c5
d4
e3
7. collate twocase func DOES NOT get called in the above and the result
ordering is not the same as the data in step#4
8. the above bug doesn't appear if I declare the the original table like
this
create table foo(i text collate twocase); -- column has collation
declaration
9. both the following sql statements return data collated by twocase func
select * from foo_view order by i collate twocase;
select * from foo_view order by i ;
this almost makes sense because collation is tied to the columns.
is this an intentional behavior change from 3.6.2 to 3.6.16?
*this is not backwards compatible change though. apps based on 3.6.2 and
earlier now break when they run into this.*
twocase collate func code is here
static int twocaseCollatingFunc(void *NotUsed, int n1, const void *v1, int
n2, const void *v2) {
if (n1 2 || n2 2) {
printf (lengths are smaller than 2: n1 = %d, n2 = %d\n, n1, n2);
return 0;
}
char c1 = ((char *)v1)[1];
char c2 = ((char *)v2)[1];
int rslt;
if (c1 c2)
rslt = -1;
else if (c1 c2)
rslt = 1;
else rslt = 0;
return rslt;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_value_type question
When the argument to sqlite3_value_type() is either a litteral or a result of some scalar function, can we rely on testing for the 5 datatypes reliably? I understand that when the argument comes from a column, then the type returned by sqlite3_value_type() is the column type. But litterals and return values from scalar functions have a well defined type. Is this defined type what sqlite3_value_type() returns in all such cases? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_value_type question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jean-Christophe Deschamps wrote: can we rely on testing for the 5 datatypes reliably? Yes, it will only be one of the 5 types. I understand that when the argument comes from a column, then the type returned by sqlite3_value_type() is the column type. The function does not return its value based on the column. It only returns it based on the sqlite3_value being interrogated. As an example it is perfectly possible for the first column in each row to have a different type in a table. If you called sqlite3_step that just selected each row then you would get a different return each time. Similarly if you are using this function for a user defined function callback then you would get exactly what the types of the parameters are. But litterals and return values from scalar functions have a well defined type. Is this defined type what sqlite3_value_type() returns in all such cases? You seem to be thinking of http://www.sqlite.org/c3ref/column_decltype.html which is far more woolly. sqlite3_value_type tells you exactly what the value being looked at type is. How that value arose is irrelevant. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksEjlYACgkQmOOfHg372QSFFwCaAgKR79nnmu5mERflUpFmI7+3 OtcAn13BDcDEfAQ0XeL3V0VCVm0NC8wx =P/XO -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Asynchronous I/O and shared cache
On Nov 18, 2009, at 10:03 PM, Pavel Ivanov wrote: I don't know what Dan meant by his words but AFAIK there's no mutex making exclusive grab of shared cache by sqlite3_step() call. There is only mutex making sqlite3_step() execution exclusive for connection object. I meant the mutex that is a member of the BtShared struct (BtShared.mutex). Grabbed by the call to sqlite3VdbeMutexEnterArray() at the top of sqlite3VdbeExec() and not released until that function returns. Pavel is right, technically it's not grabbed by sqlite3_step(). But 99% of the time spent in sqlite3_step() will be spent in a single call to sqlite3VdbeExec(), so the effect is similar. Dan. Pavel On Wed, Nov 18, 2009 at 8:40 AM, presta harc...@gmail.com wrote: I'm confused according to Dan Kennedy : Each shared-cache has its own mutex. The mutex is held for the duration of each sqlite3_step() call. So the way you're defining it here, you can't have real concurrency when using shared-cache mode in any case. So, it's a little bit antagonist to say with shared cache they will be parallelized pretty effectively in the same file too -- View this message in context: http://old.nabble.com/Asynchronous-I-O-and-shared-cache-tp26402983p26407922.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 ___ 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] Suggested user-defined-function example
Whilst trying to get a TCL script to create a function in SQLite I ran
into problems and did a lot of Googling. I got very tired of seeing the
same old same old...
proc sql_sqrt {x} {return [expr {sqrt($x)}]}
db function sqrt sql_sqrt
It didn't help me because it used only one parameter. It didn't say
anything about you
- *MUST NOT* have commas between parameters in the function definition
- *MUST* have commas between parameters when actually calling it
I spent several hours figuring this out. Here's a working example...
package require sqlite3
sqlite3 db :memory:
db eval {create table dual(x varchar(1))}
db eval {insert into dual values(' ')}
proc sql_addnum { a b } { return [expr { $a + $b }] }
db function addnum sql_addnum
db eval {select 'Hello world' as x from dual} {puts stdout $x}
db eval {select 999 as y from dual} {puts stdout $y}
db eval {select addnum(1, 2) as z from dual} {puts stdout $z}
db close
And the output is...
Hello world
999
3
Use this code as an example, and it may save someone else some time
down the road.
--
Walter Dnes waltd...@waltdnes.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] behavior of collate on views changed from 3.6.2 to 3.6.16 and beyond - intentional?
On Nov 19, 2009, at 6:40 AM, Vasu Nori wrote:
I am noticing a behavior change in processing of collate from
3.6.2 to
versions 3.6.16 beyond.
it *could* make sense but I find some inconsistencies.. let me
explain.
1. create a simple collation function which compares two input
strings based
on just the second char (code is at the end of this email)
2. call this function twocase
3. create a table and populate with this sample data
sqlitecreate table foo(i text);
sqliteinsert into foo values(a2);
sqliteinsert into foo values(b1);
sqliteinsert into foo values(c5);
sqliteinsert into foo values(d4);
sqliteinsert into foo values(e3);
4. select from the table using orderby and collate
sqlite select * from foo order by i collate twocase;
b1
a2
e3
d4
c5
5. notice that the above are sorted on the 2nd char
6. create a view on the above table and select from the view with
order by
and collate
sqlitecreate view foo_view as select i from foo;
sqliteselect * from foo_view;
sqliteselect * from foo_view order by i collate twocase;
a2
b1
c5
d4
e3
Could not reproduce this with 3.6.20 or 3.6.18 (see below). Do you
have a C program you can post that demonstrates the problem?
Dan.
~/sqlite/tipbld$ ./sqlite3
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE TABLE foo(i text);
sqlite INSERT INTO foo VALUES('a');
sqlite INSERT INTO foo VALUES('B');
sqlite INSERT INTO foo VALUES('c');
sqlite CREATE VIEW foo_view AS SELECT i FROM foo;
sqlite SELECT i FROM foo ORDER BY i COLLATE nocase;
a
B
c
sqlite SELECT i FROM foo_view ORDER BY i COLLATE nocase;
a
B
c
sqlite SELECT i FROM foo ORDER BY i;
B
a
c
sqlite SELECT i FROM foo_view ORDER BY i;
B
a
c
sqlite
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggested user-defined-function example
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Walter Dnes wrote: It didn't help me because it used only one parameter. It didn't say anything about you - *MUST NOT* have commas between parameters in the function definition That is standard TCL rules and has nothing to do with SQLite. - *MUST* have commas between parameters when actually calling it That is standard SQL rules which SQLite follows. Use this code as an example, and it may save someone else some time down the road. You (or anyone else) can edit the wiki: http://www.sqlite.org/cvstrac/wiki Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAksE4ZAACgkQmOOfHg372QTergCg4yFfzrsgjwFXWB4OhaABz/zG 7+cAn1PkDiWWYi5FKMl2sfA2KY8jB8wm =UPK+ -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] behavior of collate on views changed from 3.6.2 to 3.6.16 and beyond - intentional?
try using a user-defined collation function I included in the post. here it
is again.
static int twocaseCollatingFunc(void *NotUsed, int n1, const void *v1, int
n2, const void *v2) {
if (n1 2 || n2 2) {
printf (lengths are smaller than 2: n1 = %d, n2 = %d\n, n1, n2);
return 0;
}
char c1 = ((char *)v1)[1];
char c2 = ((char *)v2)[1];
int rslt;
if (c1 c2)
rslt = -1;
else if (c1 c2)
rslt = 1;
else rslt = 0;
return rslt;
}
add this code to src/main.c.
in the same file, don't forget to add createCollation() line for TWOCASE -
just like the line for NOCASE.
I tried it on 3.6.2 (works) and 3.6.16 (fails).
thanks for looking into this.
On Wed, Nov 18, 2009 at 9:31 PM, Dan Kennedy danielk1...@gmail.com wrote:
On Nov 19, 2009, at 6:40 AM, Vasu Nori wrote:
I am noticing a behavior change in processing of collate from
3.6.2 to
versions 3.6.16 beyond.
it *could* make sense but I find some inconsistencies.. let me
explain.
1. create a simple collation function which compares two input
strings based
on just the second char (code is at the end of this email)
2. call this function twocase
3. create a table and populate with this sample data
sqlitecreate table foo(i text);
sqliteinsert into foo values(a2);
sqliteinsert into foo values(b1);
sqliteinsert into foo values(c5);
sqliteinsert into foo values(d4);
sqliteinsert into foo values(e3);
4. select from the table using orderby and collate
sqlite select * from foo order by i collate twocase;
b1
a2
e3
d4
c5
5. notice that the above are sorted on the 2nd char
6. create a view on the above table and select from the view with
order by
and collate
sqlitecreate view foo_view as select i from foo;
sqliteselect * from foo_view;
sqliteselect * from foo_view order by i collate twocase;
a2
b1
c5
d4
e3
Could not reproduce this with 3.6.20 or 3.6.18 (see below). Do you
have a C program you can post that demonstrates the problem?
Dan.
~/sqlite/tipbld$ ./sqlite3
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE TABLE foo(i text);
sqlite INSERT INTO foo VALUES('a');
sqlite INSERT INTO foo VALUES('B');
sqlite INSERT INTO foo VALUES('c');
sqlite CREATE VIEW foo_view AS SELECT i FROM foo;
sqlite SELECT i FROM foo ORDER BY i COLLATE nocase;
a
B
c
sqlite SELECT i FROM foo_view ORDER BY i COLLATE nocase;
a
B
c
sqlite SELECT i FROM foo ORDER BY i;
B
a
c
sqlite SELECT i FROM foo_view ORDER BY i;
B
a
c
sqlite
___
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
