Re: [sqlite] Sqlite reading all column data on selects.

Hi Richard, assume I have a table mytable (id, blob1,blob2,blob3,blob4) where each blob extends over several pages. Then I do the following SQL command: select blob4 from mytable where id = 4711 Do I understand you correctly that in the case autuvacuum =true, the pages covered by blob1 to

[sqlite] querying r-trees

Dear all, I would like to discuss a new feature in the SQLite R-tree which is not very difficult to implement but would improve query performance a lot for use cases where the MBR (minimum bounding rectangle) of the query object leads to a too large candidate set. First of all the data

[sqlite] FTS3 *_content tables (do I really need them?)

Dear all,   a few weeks ago, I asked the question below but did not get any response on it. A few minutes ago,  I found a remark (cf.  http://osdir.com/ml/freedesktop.tracker/2008-07/msg00085.html)  that it might be possible to avoid the redundant storage of document information in the fts

Re: [sqlite] Near misses

Hi, I guess the speed could significantly be improved, if you leave out _car and _ar. The inverted index which is basically (term, blob_containing_all_document_ids_of_this_term), cannot skip any of the alphabetically ordered terms if the first character is variable. At least that's my

[sqlite] FTS

Dear all,   we plan to use FTS in embedded devices for address search. One requirement is to save storage.   Assume I create a table FTS_addresses (Field1,Field,2,..Fieldn), where Field1 is an identifier for my addresses. If now field1 would be used as document id, and if every fts query returns

[sqlite] FTS3

bigger than 1MByte. Can I somehow avoid this? Best Martin   Von: Martin Pfeifle martinpfei...@yahoo.de An: General Discussion of SQLite Database sqlite-users@sqlite.org Gesendet: Freitag, den 29. Mai 2009, 08:59:45 Uhr Betreff: Re: [sqlite] FTS3 One further

Re: [sqlite] FTS3

Von: D. Richard Hipp d...@hwaci.com An: General Discussion of SQLite Database sqlite-users@sqlite.org Gesendet: Dienstag, den 26. Mai 2009, 12:27:59 Uhr Betreff: Re: [sqlite] FTS3 On May 26, 2009, at 5:03 AM, Martin Pfeifle wrote: Dear all, we need full and fuzzy text search

[sqlite] FTS3

Dear all, we need full and fuzzy text search for addresses. Currently we are looking into Lucene and SQLite's FTS extension. For us it is crucial to understand the file structures and the concepts behind the libraries. Is there a self-contained, comprehensive document for FTS3 (besides the

Re: [sqlite] FTS3

26, 2009, at 5:03 AM, Martin Pfeifle wrote: Dear all, we need full and fuzzy text search for addresses. Currently we are looking into Lucene and SQLite's FTS extension. For us it is crucial to understand the file structures and the  concepts behind the libraries. Is there a self-contained

[sqlite] Accessing tables containing multiple Blobs

Assume you have a table mytable(id, blob1, blob2,blob3). You have a page_size of 1k and each blob is in average 20KBytes, i.e. a row is of size 60KByte. My question now is where does the projection of an sql-statement like select blob2 from mytable where id=777 take place. Are all 60KBytes read

[sqlite] retrieval of blobs stored in sqlite

Hi, I have a question regarding the retrieval of BLOBs. Assume you have a table mytable(id, blob) and the page size is 1k. If we now carry out an sql-query like select blob from mytable where id=4711 and the blob size is 100k. Am I then correct that the pager asks 100 times for a page of size 1k

[sqlite] fragmentation, overflow pages

Hi, we store proprietary organized data in blobs within an sqlite database. Assume you have a table mydata(id, attr1, attr2, blob). The page size of the database is 1k. If you now store blobs in your table which are larger than 1k, sqlite uses overflow pages and the content of the blobs is

[sqlite] Binary Format

Hi, we do use SQLite in a standardisation initiative and have to state which binary file-format of sqlite is used. Up to now, I was of the opinion that all sqlite versions 3.x use the same binary sqlite file format but only differ in the library functionality. Can somebody confirm that the

Re: [sqlite] Binary Format

thank you. Best Martin Von: Jay A. Kreibich j...@kreibi.ch An: General Discussion of SQLite Database sqlite-users@sqlite.org Gesendet: Mittwoch, den 1. April 2009, 15:52:08 Uhr Betreff: Re: [sqlite] Binary Format On Wed, Apr 01, 2009 at 08:24:29AM +, Martin

Re: [sqlite] Binary Format

, at 4:24 AM, Martin Pfeifle wrote: Hi, we do use SQLite in a standardisation initiative and have to state which binary file-format of sqlite is used. Up to now, I was of the opinion that all sqlite versions 3.x use the  same binary sqlite file format but only differ in the library

[sqlite] virtual file system

Dear all, I have a question regarding virtual file systems. I assume I can load my own virtual file system by calling the  c-function sqlite3_vfs_register(...). Am I right that I cannot load a virtual file system by a pragma command or a core function similar to load_extension? I would

Re: [sqlite] virtual file system

Martin Pfeifle wrote: I have a question regarding virtual file systems. I assume I can load my own virtual file system by calling the  c-function sqlite3_vfs_register(...). That function passes the vfs name and a series of callbacks that implement the vfs to SQLite.  To actually use a vfs, you need

Re: [sqlite] virtual file system

itandet...@mvps.org An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 26. März 2009, 20:30:33 Uhr Betreff: Re: [sqlite] virtual file system Martin Pfeifle wrote: Thanks. I appreciate very much, the way I can add my own loadable extension myExtension.dll by simply calling on SQL-level select

Re: [sqlite] virtual file system

thank you. This helped me a lot. Best Martin Von: Igor Tandetnik itandet...@mvps.org An: sqlite-users@sqlite.org Gesendet: Donnerstag, den 26. März 2009, 20:55:18 Uhr Betreff: Re: [sqlite] virtual file system Martin Pfeifle martinpfei...@yahoo.de wrote

Re: [sqlite] Fuzzy Matching

Hi, I think there is nothing available except FTS. Doing a full table scan and computing for each string the (Levenstein) distance to the query object is too time consuming. So what I would like to see is the implementation of a generic metric index which needs as one parameter a metric distance

[sqlite] How can I change big-endian to little-endian in the database file

Dear all, in an upcoming project, it is required to store all integer values as little endian instead of big endian (don't ask why). Nevertheless, I would like to use SQLite in that project. What do we have to change in the sqlite library, if we store the integers as little endian. I came across

AW: [sqlite] Soft search in database

Unfortunately, the fts module of sqlite does not support fuzzy text search = google search. What you first need is a similarity measure between strings, e.g. the Edit-distance. Based on such a similarity measure, you could build up an appropriate index structure, e.g. a Relational M-tree (cf.

AW: [sqlite] PK and rowid

uniqId is the same as rowid, so you will get the same execution plans for ...where rowid=x and ... where uniqId=x. - Ursprüngliche Mail Von: chetana bhargav [EMAIL PROTECTED] An: sqlite-users@sqlite.org Gesendet: Mittwoch, den 11. Oktober 2006, 09:19:28 Uhr Betreff: [sqlite] PK and

[sqlite] UTF-question

Hi, is it possible to use UTF-16 access functionsa, e.g. sqlite3_bind_text16 or sqlite3_column_text16, together with a UTF-8 database? Do I get a byte identical UTF-8 database file if I write data to the database using sqlite3_bind_text16 and if I use sqlite3_bind_text? Best Martin

AW: AW: [sqlite] Performance question

Hi Michael, could you please (re)post the exact create inex statements +primary key you used. For speeding up your query, you need an index on x only but not on id,x. Best Martin - Ursprüngliche Mail Von: Michael Wohlwend [EMAIL PROTECTED] An: sqlite-users@sqlite.org

AW: [sqlite] journal-off assert

hi, i got the same error when I ported sqlite to an operating system using a proprietary file system. The reason was that our file system did not support sparse files. i.e. the fstat-command returned the wrong file-size. Maybe you should independently of SQLite try to * create a file, * write

AW: AW: [sqlite] New JDBC driver for SQLite

Hi David, could you please shortly outline the differences between your jdbc driver and the one developed by Christian Werner? Best Martin - Ursprüngliche Mail Von: David Crawshaw [EMAIL PROTECTED] An: sqlite-users@sqlite.org; Martin Pfeifle [EMAIL PROTECTED] Gesendet: Sonntag, den 30

AW: [sqlite] New JDBC driver for SQLite

Does the jdbc driver also provide the direct reading and writing of BLOBs? Best Matin - Ursprüngliche Mail Von: David Crawshaw [EMAIL PROTECTED] An: sqlite-users@sqlite.org Gesendet: Sonntag, den 30. Juli 2006, 14:56:18 Uhr Betreff: [sqlite] New JDBC driver for SQLite Hello all, I

[sqlite] accessing sqlite files directly via http

Hello, We would like to access (only reading access) a remote sqlite database via http. On a remote computer, an http server is located and an sqlite database file (we are not allowed to install any software on that http-server). On a client computer, an application program wants to access the

[sqlite] full text search

Dear all, I have just seen that there are some thoughts going on to incorporate full text search into SQLite. http://www.sqlite.org/cvstrac/wiki/wiki?p=FullTextIndex What is the current status on that project?

[sqlite] ACID for attached databases

Dear all, We plan to update several sqlite database files within one transaction. I was just reading the following on the SQLite homepage: Transactions involving multiple attached databases are atomic, assuming that the main database is not :memory:. We do not have memory databases. But I do

AW: [sqlite] Re: ACID for attached databases

thank you, that is exactly what I searched for. Best Martin - Ursprüngliche Mail Von: Igor Tandetnik [EMAIL PROTECTED] An: SQLite sqlite-users@sqlite.org Gesendet: Montag, den 22. Mai 2006, 15:03:03 Uhr Betreff: [sqlite] Re: ACID for attached databases Martin Pfeifle [EMAIL PROTECTED

AW: [sqlite] Re: spatial sqlite anyone ?

Hi, I think the simplest solution would be to put a spatial index on top of the B-tree, that's what e.g. Oracle does in their Spatial Cartridge. Basically you store the index data in relations and index these relations by B-trees. In this case, you do not have to change the core code of SQLite.

AW: AW: [sqlite] Re: spatial sqlite anyone ?

: Re: AW: [sqlite] Re: spatial sqlite anyone ? Martin Pfeifle wrote: Hi, I think the simplest solution would be to put a spatial index on top of the B-tree, that's what e.g. Oracle does in their Spatial Cartridge. Basically you store the index data in relations and index these relations by B-trees

AW: [sqlite] spatial sqlite anyone ?

I am very interested. We are working on spatial sqlite for almost one year. We plan to include sqlite into an embedded spatial application. You mention that there are open-source code for library 3 and 4. Can you give me a hint where to find it? I will contact you at the end of the week providing

AW: [sqlite] primary key and physical data organization

That's great thank you very much. - Ursprüngliche Mail Von: [EMAIL PROTECTED] An: sqlite-users@sqlite.org; Martin Pfeifle [EMAIL PROTECTED] Gesendet: Mittwoch, den 5. April 2006, 23:09:25 Uhr Betreff: Re: [sqlite] primary key and physical data organization Martin Pfeifle [EMAIL

AW: [sqlite] primary key and physical data organization

] primary key and physical data organization Martin Pfeifle wrote: Hi, Assume I have a table containing an integer primary key. As far as I know, this value is identical to the ROWID, right? How does SQLite organize the data within the file? Does it try to organize the data on the pages

[sqlite] extensible indexing

Dear all, is it somehow possible to add an own index-structure, e.g. M-tree, R-tree, Quadtree...to SQLite? Has anyone ever done that? Best Martin ___ Telefonate ohne weitere Kosten vom PC zum PC:

[sqlite] In memory table - BLOB

Dear all, How can I create an in-memory table? Is it possible to store an in-memory table in a BLOB and then store it permanently in an SQLite database? Obviously, later on, I would like to use the content of the BLOB again as in-memory table. Is this possible or not? Best Martin

[sqlite] vxworks

Hi, I am in deep trouble. I would like to use sqlite on vxworks. There are no fysnc, fcntl calls available which are used in os_unix.c. Can anybody help me? PLEASE! ___ Gesendet von Yahoo! Mail - Jetzt

[sqlite] User-defined Collating Sequences

Hi, does anybody know whether a code example for User-defined Collating Sequences in C exists, and where I can find such an example. Or even better has somebody already implemented such a User-defined Collating Sequence taking German Umlaute into account. Best Martin

[sqlite] compression

Hello, does anybody know whether it is possible to compress sqlite data on the page level. If I compress the sqlite database file with zlib I get very high compression rates due to the character of the stored data. I think this problem is related to the problem of using encrypted databases.

[sqlite] index organized table +compression

Hello, I just started to consider the use of SQLite for a rather big project. For this project it would be beneficial if the database supports index organized tables. Furthermore, it would be helpful, if we could compress the data by integrating compression techniques into the files os.c. Does