Re: [sqlite] How to minimize or remove need for dirty bitmap memory size?
On Apr 14, 2009, at 4:00 PM, Bjorn Toft Madsen wrote: Hi, The docs for SQLite state: - Situations Where Another RDBMS May Work Better - Very large datasets - [...] [sqlite] has to allocate a bitmap of dirty pages in the disk file to help it manage its rollback journal. [...] Aside from a larger page size, what remedies are suggested for removing (or minimizing) this requirement? That statement is out of date. Since it was written the bitmap has been replaced with a better data structure that does not require the big per-transaction allocation. Dan. SQLite is more or less perfect for my needs, but I expect hundreds and hundreds of gigabytes of data so this high memory requirement means I have to possibly roll my own solution :( Thanks, Bjorn ___ 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] Sqlite3 is not compiling in Linux
Hi All, I tried to compile in Linux. But it is giving error at linking stage as 'unreferenced dlopne dlclose... Can anybody help me? With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to minimize or remove need for dirty bitmap memory size?
... and there was much rejoicing! :D Is it thus safe to say that sqlite can handle 100+ GB of data, or are other resources consumed exponentially as DB size goes up? (linear growth I can understand and handle, exponential growth is a problem for me) Thanks, Bjorn 2009/4/14 Dan danielk1...@gmail.com: On Apr 14, 2009, at 4:00 PM, Bjorn Toft Madsen wrote: Hi, The docs for SQLite state: - Situations Where Another RDBMS May Work Better - Very large datasets - [...] [sqlite] has to allocate a bitmap of dirty pages in the disk file to help it manage its rollback journal. [...] Aside from a larger page size, what remedies are suggested for removing (or minimizing) this requirement? That statement is out of date. Since it was written the bitmap has been replaced with a better data structure that does not require the big per-transaction allocation. Dan. SQLite is more or less perfect for my needs, but I expect hundreds and hundreds of gigabytes of data so this high memory requirement means I have to possibly roll my own solution :( Thanks, Bjorn ___ 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] Storage of blobs: to encode or not to encode?
On Mon, Apr 13, 2009 at 11:14:17AM -0700, Julian Bui wrote: Hi all, I have a question about encoding blob bytes before inserting a record into SQLite. CONTEXT: I have a base java (I'm using JDBC) class that has a many children. Each child may store a different data type (such as String, int, long, etc.) in its VALUE field. Since this value field could be many different data types, I will be storing the field's bytes into the database as a blob. QUESTIONS: -I have seen a couple of places on the internet saying that SQLite cannot inserting binary data unless it has been encoded. Can someone confirm this or preferrably point me to an OFFICIAL sqlite statement/page saying that this is true? As pointed out by others, SQLite is perfectly capable of storing unencoded BLOBS since SQLite 3.0 was released. -Will I need to encode my data? I do not think the String.getBytes() command returns bytes including a terminator. However, since the terminator is just 0, I assume that even storing the integer 0 as bytes in the blob would be a problem. Is it not? Given your Java context, the most portable way to encode Java objects is to make them implement the Serializable interface, and store the serialized text representation in the column. However, if all you're doing is storing simple types and not complex data structures, this is overkill and you're best off just exploiting SQLite's manifest type system as pointed out in other replies. Cheers, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Implicit indices on TEMP tables
Alex Ousherovitch alex.ousherovi...@opentv.com wrote in message news:5ee1928d06817b4788b64caf1a8517b002eae...@sfo-ex-01.ad.opentv.local Igor, yes, deletes do occur on table_name, but before every insert on table_name I delete all records from mods_log_table_name and nevertheless get duplicates. I would expect the implicit index on modrowid to be refreshed too, when mods_log_table_name gets emptied. It is. There is nothing wrong in the code you show. The problem must lie somewhere in the code you don't show. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3 is not compiling in Linux
2009/4/14 Pramoda M. A pramod...@kpitcummins.com Hi All, I tried to compile in Linux. But it is giving error at linking stage as 'unreferenced dlopne dlclose... Can anybody help me? did you add the -ldl linker flag? Vivien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to minimize or remove need for dirty bitmap memory size?
On Apr 14, 2009, at 6:33 AM, Dan wrote: On Apr 14, 2009, at 4:00 PM, Bjorn Toft Madsen wrote: Hi, The docs for SQLite state: - Situations Where Another RDBMS May Work Better - Very large datasets - [...] [sqlite] has to allocate a bitmap of dirty pages in the disk file to help it manage its rollback journal. [...] Aside from a larger page size, what remedies are suggested for removing (or minimizing) this requirement? That statement is out of date. Since it was written the bitmap has been replaced with a better data structure that does not require the big per-transaction allocation. Right. The change to get rid of that bitmap happened over a year ago. (That change also made transactions run a little faster, which was a surprising, though welcomed, side-effect.) Unfortunately, I failed to update the documentation referenced above. I have just now checked in a fix to the documentation. It now reads that one should consider a different database engine if you are contemplating databases larger than 2 tebibytes in size. SQLite should scale up to that size now, assuming you have a disk-drive and filesystem that can hold a file that big. 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] Sqlite3 is not compiling in Linux
2009/4/14 Pramoda M. A pramod...@kpitcummins.com What I have done is, Cc -lpthread sqlite3.o ourApplication.o -o main Then it is giving linker error. Is it right? try instead: cc -lpthread -ldl sqlite3.o ourApplication.o -o main Vivien ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to minimize or remove need for dirty bitmap memory size?
Hi, The docs for SQLite state: - Situations Where Another RDBMS May Work Better - Very large datasets - [...] [sqlite] has to allocate a bitmap of dirty pages in the disk file to help it manage its rollback journal. [...] Aside from a larger page size, what remedies are suggested for removing (or minimizing) this requirement? SQLite is more or less perfect for my needs, but I expect hundreds and hundreds of gigabytes of data so this high memory requirement means I have to possibly roll my own solution :( Thanks, Bjorn ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to calculate the size of my database for n records ?
Hello, I'm quite new at sqlite and I need to estimate the size the database I am creating could reach. Basically, estimating for any table how many bytes would be used for an average record. I mainly use INTEGER and REAL values, along with a few TEXT attributes but I couldn't find the number of bytes used to store those datatypes. Anyone could help me on this ? Is there any header or separator I need to take into account ? By the way, it seems INTEGER are stored on 2 to 8 bytes (from http://theopensourcery.com/sqlitedocs/sqdatatype3.html ). What about 16-byte integers ? Do I have to declare them as REAL to store them ? Many thanks in advance Best regards Nicolas ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3 is giving error while creating object file in Linux
Hi All, I tried to create object file of sqlite3.6.5 using Gcc -c sqlite3.c But it is giving following error. In pthreaMutexAlloc, PTHREAD_MUTEX_RECURSIVE IS UNDECLARED Please can anybody tell me what options has to be used? With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to calculate the size of my database for n records ?
Nicolas Gaiffe nicolas.gai...@gmail.com wrote in message news:a729b91e0904140537p7f97fae0q7f2afcb327e55...@mail.gmail.com By the way, it seems INTEGER are stored on 2 to 8 bytes (from http://theopensourcery.com/sqlitedocs/sqdatatype3.html ). What about 16-byte integers ? SQLite doesn't support those. Do I have to declare them as REAL to store them ? That's one option, but note that you'll lose precision. REAL is stored as 64-bit IETF floating point value, which has 52 mantissa bits (so not even all 64-bit integers can be exactly represented, let alone 128 bits). You could store such integers as BLOBs, but then you won't be able to do any math on them within SQL. Or, you could store them as two 64-bit integers (high and low halves in two separate columns), then you could do some math but it'll be rather awkward. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Appending Text to a Column
I am trying to append text to a field within my database - sothat if a user has stored 'search, tools' into the Tags column of tblEntry, they can add more tags later like: update tblEntry set Tags = Tags + ' new text' where RowID = 13 But whenever I try to do this I end up with 0 in the column. I've done searches and found discussions on concat(..) or append(..) but when I try to use these sqlite doesn't recognize them. How do I go about adding a value to the end of an existing value? Do I need to retrieve it and append it in my Java code as opposed to in the sql statement? Thanks! _ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Appending Text to a Column
Hi, use the || - Operator to concatenate text. + adds to numbers. update tblEntry set Tags = Tags || ' new text' where RowID = 13 martin centipede moto wrote: I am trying to append text to a field within my database - sothat if a user has stored 'search, tools' into the Tags column of tblEntry, they can add more tags later like: update tblEntry set Tags = Tags + ' new text' where RowID = 13 But whenever I try to do this I end up with 0 in the column. I've done searches and found discussions on concat(..) or append(..) but when I try to use these sqlite doesn't recognize them. How do I go about adding a value to the end of an existing value? Do I need to retrieve it and append it in my Java code as opposed to in the sql statement? Thanks! _ Windows Live™: Keep your life in sync. http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009 ___ 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] Aggregating BitFlags
Hi Guys, I have a scenario where I am representing a tree in a table, standard self relation ID/ParentId stuff. This tree contains bitflag data on each item, and I want to be able to propagate the bitflag up to the parent by repeatedly running a query that will bubble them up to the next Item. However, the bitwise OR operation I am performing only updates from a single bitflag, rather than all the child bitflags. I.e. I have: Items IDParentId NameFlags 1 - Root 0 2 1 AAA 1 3 1 BBB 2 4 3 CCC 4 5 4 DDD 8 And I want to be able to propagate the bitflags up the table so that any parent item will contain the child items bitmask: Items IDParentId NameFlags 1 - Root 15 2 1 AAA 1 3 1 BBB 14 4 3 CCC 12 5 4 DDD 8 What I get is: IDParentId NameFlags 1 - Root 1 2 1 AAA 1 3 1 BBB 14 4 3 CCC 12 5 4 DDD 8 I'm running this query: UPDATE Item SET Flags = Flags | (SELECT NestItem.Flags FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id) WHERE EXISTS (SELECT * FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id)) Ideally I'd like to aggregate the SELECT Bitflags into one Bitflag and OR it with the appropriate item. Any idea how to do this in SQL? The only way I can think of getting this to work properly is to pull the data out the DB and handle it in code. Regards Tristan Rhodes Cotoco Ltd Winners of e-Business Innovation Awards two years in a row. For more information go to http://www.cotoco.com http://www.cotoco.com/ Tel: +44 (0) 870 748 1400 Fax: +44 (0) 870 748 1411 ** cotoco ltd Registered in the UK, number 2986223 Registered Office: HTEC House, Southampton International Business Park, George Curl Way, Southampton, Hampshire, UK, SO18 2RX The contents of this email and any files transmitted with it may contain proprietary information which may be confidential and/or legally privileged. If you have received this email in error, please notify the sender by replying to this email and then delete it from your system. If you are not the intended recipient, you are not authorised to disclose, copy, distribute or retain this email or any part of it. The views expressed in this email are not necessarily the views of cotoco. Neither cotoco nor the sender accepts any liability or responsibility for viruses or other destructive elements and it is your responsibility to scan for any attachments. Thank you for your co-operation. ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Appending Text to a Column
Text concatenation is done with || Two vertical bars (virgules). update tblEntry set Tags = Tags || ' new text' where RowID = 13 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregating BitFlags
Tristan Rhodes tristan.rho...@cotoco.com wrote: I have a scenario where I am representing a tree in a table, standard self relation ID/ParentId stuff. This tree contains bitflag data on each item, and I want to be able to propagate the bitflag up to the parent by repeatedly running a query that will bubble them up to the next Item. However, the bitwise OR operation I am performing only updates from a single bitflag, rather than all the child bitflags. I'm running this query: UPDATE Item SET Flags = Flags | (SELECT NestItem.Flags FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id) WHERE EXISTS (SELECT * FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id)) What you need is something like this: UPDATE Item SET Flags = Flags | coalesce( (SELECT AggregateOR(NestItem.Flags) FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id), 0); The problem is, there is no built-in AggregateOR function you could use - I just made one up. If you only use a reasonably small number of bits, you could simulate it with this: Max(NestItem.Flags 1) | Max(NestItem.Flags 2) | Max(NestItem.Flags 4) | Max(NestItem.Flags 8) Otherwise, you would probably have to write a custom aggregate function. It should be fairly trivial. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregating BitFlags
Hi Igor, There are potentially a lot of bitflags, so I'm going to have to make a function, any good resources to get me started on this? Tristan Rhodes -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 14 April 2009 16:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Aggregating BitFlags Tristan Rhodes tristan.rho...@cotoco.com wrote: I have a scenario where I am representing a tree in a table, standard self relation ID/ParentId stuff. This tree contains bitflag data on each item, and I want to be able to propagate the bitflag up to the parent by repeatedly running a query that will bubble them up to the next Item. However, the bitwise OR operation I am performing only updates from a single bitflag, rather than all the child bitflags. I'm running this query: UPDATE Item SET Flags = Flags | (SELECT NestItem.Flags FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id) WHERE EXISTS (SELECT * FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id)) What you need is something like this: UPDATE Item SET Flags = Flags | coalesce( (SELECT AggregateOR(NestItem.Flags) FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id), 0); The problem is, there is no built-in AggregateOR function you could use - I just made one up. If you only use a reasonably small number of bits, you could simulate it with this: Max(NestItem.Flags 1) | Max(NestItem.Flags 2) | Max(NestItem.Flags 4) | Max(NestItem.Flags 8) Otherwise, you would probably have to write a custom aggregate function. It should be fairly trivial. 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] Aggregating BitFlags
In addition, I am trying to do this in .Net. I've had a look and it seems to only be available in PHP. Any way i can do this using the .Net framework? Regards Tristan Rhodes -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: 14 April 2009 16:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Aggregating BitFlags Tristan Rhodes tristan.rho...@cotoco.com wrote: I have a scenario where I am representing a tree in a table, standard self relation ID/ParentId stuff. This tree contains bitflag data on each item, and I want to be able to propagate the bitflag up to the parent by repeatedly running a query that will bubble them up to the next Item. However, the bitwise OR operation I am performing only updates from a single bitflag, rather than all the child bitflags. I'm running this query: UPDATE Item SET Flags = Flags | (SELECT NestItem.Flags FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id) WHERE EXISTS (SELECT * FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id)) What you need is something like this: UPDATE Item SET Flags = Flags | coalesce( (SELECT AggregateOR(NestItem.Flags) FROM Item AS NestItem WHERE NestItem.ParentId = Item.Id), 0); The problem is, there is no built-in AggregateOR function you could use - I just made one up. If you only use a reasonably small number of bits, you could simulate it with this: Max(NestItem.Flags 1) | Max(NestItem.Flags 2) | Max(NestItem.Flags 4) | Max(NestItem.Flags 8) Otherwise, you would probably have to write a custom aggregate function. It should be fairly trivial. 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
[sqlite] Newbie question about creating tables
My background is with SQL Server and Delphi, so the concepts in SQLite are new for me. I have a TDISQLite3Database component in my TDataModule with the DatabaseName set to Demo. Since I am familiar with the TDataset and other Delphi components, I would like to use those (at the expense of size and efficiency). What I need to do now is learn how to create Demo and add a table, Members. Ultimately, there will be tables in SQLite that will be synchronized with the tables on a server. So far I have not been able to find an example of creating the tables using DISQLite3 and the Delphi components. All suggestions are welcomed... Todd -- Ariste Software Petaluma, CA 94952 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite.exe for windows 98
Hello, The last version of Sqlite.exe is not working with Windows 98 Where can I find the last version of Sqlite.exe compatible for windows 98 ? Thank's MaxMax14 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_blob_read problem with buffer in Objective C
After successfully reading from a blob sequentially to a buffer in Objective C the buffer seems to be altered such that it can no longer be accessed from memory Is there some kind of special buffer type that I should be using? Here is sample of my code: int success; NSData *inBuffer; success = sqlite3_blob_read(blobData, inBuffer, intByteCount, intOffsetInBlob); After running this code, success == SQLITE_OK. Prior to reading the blob, inBuffer behaves normally. Afterward, it no longer behaves like a buffer, and I cannot get any valid data from it. I have set intByteCount to make sure it is smaller than the number of bytes in the Blob, and intOffsetInBlob = 0. Thanks for any help. Richard Dauben ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregating BitFlags
Tristan Rhodes tristan.rho...@cotoco.com wrote: There are potentially a lot of bitflags, so I'm going to have to make a function, any good resources to get me started on this? http://sqlite.org/c3ref/create_function.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Aggregating BitFlags
Tristan Rhodes tristan.rho...@cotoco.com wrote: In addition, I am trying to do this in .Net. I've had a look and it seems to only be available in PHP. It's definitely available in C. Any way i can do this using the .Net framework? No clue, sorry. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA read_uncommitted = 1
Joanne Pham joannekp...@yahoo.com wrote: I have read this document and it stated that if a database connectin in read-uncommitted mode does not attempt to obtain reaad-locks before reading from database tables. So in this case if the connection in read-uncommited modes then it allows the follwing : - concureently reads - also allows read if there is a write-lock right? Yes. But this all only applies to connections that share cache. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA read_uncommitted = 1
Hi Igor, I have another question : how to enabled shared-cache mode for the connection? Thanks, JP From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Sent: Tuesday, April 14, 2009 12:06:29 PM Subject: Re: [sqlite] PRAGMA read_uncommitted = 1 Joanne Pham joannekp...@yahoo.com wrote: I have read this document and it stated that if a database connectin in read-uncommitted mode does not attempt to obtain reaad-locks before reading from database tables. So in this case if the connection in read-uncommited modes then it allows the follwing : - concureently reads - also allows read if there is a write-lock right? Yes. But this all only applies to connections that share cache. 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] sqlite3_blob_read problem with buffer in Objective C
Richard Dauben rdau...@sbcglobal.net wrote: After successfully reading from a blob sequentially to a buffer in Objective C the buffer seems to be altered such that it can no longer be accessed from memory Is there some kind of special buffer type that I should be using? Here is sample of my code: int success; NSData *inBuffer; success = sqlite3_blob_read(blobData, inBuffer, intByteCount, intOffsetInBlob); This is wrong. First, it appears you need to use NSMutableData - NSData is intended to be read-only. Second, you don't read into NSMutableData pointer itself - you read into a buffer that NSMutableData wraps, accessible via its mutableBytes property. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_blob_read problem with buffer in Objective C
On Apr 14, 2009, at 12:48 PM, Richard Dauben wrote: After successfully reading from a blob sequentially to a buffer in Objective C the buffer seems to be altered such that it can no longer be accessed from memory Is there some kind of special buffer type that I should be using? Here is sample of my code: int success; NSData *inBuffer; success = sqlite3_blob_read(blobData, inBuffer, intByteCount, intOffsetInBlob); After running this code, success == SQLITE_OK. Prior to reading the blob, inBuffer behaves normally. Afterward, it no longer behaves like a buffer, and I cannot get any valid data from it. I have set intByteCount to make sure it is smaller than the number of bytes in the Blob, and intOffsetInBlob = 0 sqlite3_blob_read expects a valid pointer pointing to an allocated buffer, but you're passing it an uninitialized NSData pointer. Something like this ought to work: int success; NSMutableData *inBuffer = [NSMutableData dataWithLength:intByteCount]; if (inBuffer) success = sqlite3_blob_read(blobData, [inBuffer mutableBytes], intBytesCount, intOffsetInBlob); You'll probably also need to dispose of the NSMutableData object when you're through with it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA read_uncommitted = 1
Joanne Pham joannekp...@yahoo.com wrote: I have another question : how to enabled shared-cache mode for the connection? Thanks, http://sqlite.org/sharedcache.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA read_uncommitted = 1
Thanks Igor, I called this function rc = sqlite3_enable_shared_cache(1); in my main program to enable the share_cache so if this process is opened any connections then these connections will be using share-cache right? Sorry for too many questions. Thanks, JP From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Sent: Tuesday, April 14, 2009 1:29:08 PM Subject: Re: [sqlite] PRAGMA read_uncommitted = 1 Joanne Pham joannekp...@yahoo.com wrote: I have another question : how to enabled shared-cache mode for the connection? Thanks, http://sqlite.org/sharedcache.html 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] PRAGMA read_uncommitted = 1
Joanne Pham joannekp...@yahoo.com wrote: I called this function rc = sqlite3_enable_shared_cache(1); in my main program to enable the share_cache so if this process is opened any connections then these connections will be using share-cache right? Yes. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with ordering
Hi Lukáš, And I've been trying to run the following query: sqlite SELECT * FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock = 1000 AND parent_fk IS NOT NULL ORDER BY id DESC LIMIT 1; 835|29|0|309493|0|334|834|9|| CPU Time: user 0.027995 sys 0.00 As you can see, it takes ages and it shouldn't. When I don't use any ORDER BY clause, I get: sqlite SELECT * FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock = 1000 AND parent_fk IS NOT NULL LIMIT 1; 238||0|146026|0|95|236|9|| CPU Time: user 0.00 sys 0.00 Please note that this one is lightning fast, while the one above it is not. You don't mention how many rows in your table. Are you aware that the limit filter occurs after everything else, including the order by? So if your query returns a million rows, the million rows will be tabulated and sorted before your query then just picks one. That explains why it takes much longer when using order by even though you are only returning one row. A better/faster approach would be to filter the returned rows in the query, rather than using a limit. Something like: SELECT * FROM Event where id = ( SELECT max(id) FROM Event WHERE bringsSchedule = 0 AND sourceMachine_id = 9 AND virtualClock = 1000 AND parent_fk IS NOT NULL ) ; I think you'll need just the following index: CREATE INDEX EventIndex ON Event (bringsSchedule, sourceMachine_id, virtualClock, parent_fk); Hope this helps, Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Giving error While compiling sqlite in Linux
Hi All, I tried to compile sqlite3.6.5 in linux as gcc -c sqlite3.c It is giving error : in pthreadMutexAlloc PTHREAD_MUTEX_RECURSIVE is undeclared... Please help anybody... With Regards Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru | Board: +91 80 30783905 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
