Re: [sqlite] A question about how to interpret pragma integrity_check

On Tue, Sep 15, 2009 at 8:11 PM, Tito Ciuro wrote: > On Sep 15, 2009, at 8:04 PM, Scott Hess wrote: >> Do you have any reason to believe that your database had exactly a >> single corruption? > > What do you mean by 'single corruption'? This particular database is > prone to index

Re: [sqlite] A question about how to interpret pragma integrity_check

On 16 Sep 2009, at 4:11am, Tito Ciuro wrote: > What do you mean by 'single corruption'? This particular database is > prone to index corruption. So use the sqlite3 command-line tool to dump it to a text file, then read it back in again. See if the new database is prone to corruption too.

Re: [sqlite] A question about how to interpret pragma integrity_check

On Tue, Sep 15, 2009 at 7:04 PM, Tito Ciuro wrote: > On Sep 15, 2009, at 6:59 PM, P Kishor wrote: > >> On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro wrote: >>> On Sep 15, 2009, at 6:42 PM, P Kishor wrote: >>> Could it be that those seemingly identical multiple

Re: [sqlite] A question about how to interpret pragma integrity_check

On Sep 15, 2009, at 6:59 PM, P Kishor wrote: > On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro wrote: >> On Sep 15, 2009, at 6:42 PM, P Kishor wrote: >> >>> Could it be that those seemingly identical multiple rows actually >>> have >>> trailing spaces or some other non-visible

Re: [sqlite] A question about how to interpret pragma integrity_check

On Tue, Sep 15, 2009 at 8:50 PM, Tito Ciuro wrote: > On Sep 15, 2009, at 6:42 PM, P Kishor wrote: > >> Could it be that those seemingly identical multiple rows actually have >> trailing spaces or some other non-visible character? Check for their >> length. > > Here we go: > >>

Re: [sqlite] A question about how to interpret pragma integrity_check

On Sep 15, 2009, at 6:42 PM, P Kishor wrote: > Could it be that those seemingly identical multiple rows actually have > trailing spaces or some other non-visible character? Check for their > length. Here we go: > sqlite> SELECT clientName, entityName, length(entityName) FROM > MyDBState ORDER

Re: [sqlite] A question about how to interpret pragma integrity_check

On Tue, Sep 15, 2009 at 8:34 PM, Tito Ciuro wrote: > Hello, > On Sep 15, 2009, at 6:15 PM, P Kishor wrote: > > well, your clientName, entityName combo is not unique in the list > above, and it should be given it is a PK. I have no idea how you > managed to insert these rows while

Re: [sqlite] A question about how to interpret pragma integrity_check

Hello, On Sep 15, 2009, at 6:15 PM, P Kishor wrote: > well, your clientName, entityName combo is not unique in the list > above, and it should be given it is a PK. I have no idea how you > managed to insert these rows while the PK constraint was active. Precisely. I cannot reproduce this

Re: [sqlite] How to speed up a query between two tables?

On Tue, 2009-09-15 at 22:31 +1000, John Machin wrote: > On 15/09/2009 7:25 PM, Kermit Mei wrote: > > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > >> On 15/09/2009 4:47 PM, Kermit Mei wrote: > >>> > >>> sqlite> SELECT HomeDev.text, ZPhDev.id > >>>...> FROM ZPhDev > >>>...> INNER

Re: [sqlite] A question about how to interpret pragma integrity_check

On 16 Sep 2009, at 2:15am, P Kishor wrote: > well, your clientName, entityName combo is not unique in the list > above, and it should be given it is a PK. I have no idea how you > managed to insert these rows while the PK constraint was active. What happens in SQLite if you load the data first,

Re: [sqlite] A question about how to interpret pragma integrity_check

On Tue, Sep 15, 2009 at 4:22 PM, Tito Ciuro wrote: > Hello, > > On Sep 15, 2009, at 12:55 PM, D. Richard Hipp wrote: > >> >> On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote: >> >>> Hello, >>> >>> Given the following pragma integrity_check output: >>> sqlite> pragma

Re: [sqlite] Practicality of using pragma integrity_check at runtime

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 rschnitzer wrote: > So my original question still stands about the side effects of > integrity_check and whether it would interfere with the continuous 24/7, > multiple-times-per-second access by our mission critical application. You would need to

Re: [sqlite] Practicality of using pragma integrity_check at runtime

hi Simon, Thank you for your response. On a couple of occasions, we have tracked down failures in our application to corruptions in the database. This was confirmed by viewing the database directly, as well as by integrity_check. As this has happened on at least 4 very different platforms,

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

Gert Cuykens wrote: > SELECT t.pid, > t.txt, > t.price, > t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' > FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, > SUM(o.qty) 'qty_sold' > FROM ORDERS o) qs ON qs.pid = t.pid >

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

009/9/15 Gert Cuykens : >   SELECT t.pid, >          t.txt, >          t.price, >          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' >     FROM PRODUCTS t > LEFT JOIN (SELECT o.pid, >                  SUM(o.qty) 'qty_sold' >             FROM ORDERS o) qs ON qs.pid = t.pid

[sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

SELECT t.pid, t.txt, t.price, t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty' FROM PRODUCTS t LEFT JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs ON qs.pid = t.pid WHERE t.pid = ? i have trouble running this

Re: [sqlite] Database error message

Thanks for your advice. I'm happily building my database through the terminal window as you suggested. The program tells me if I make a mistake and SELECT is working fine so far. So much for third party software. Jimmy Verner www.vernerlegal.com On Sep 15, 2009, at 12:06 PM, Simon

Re: [sqlite] Schema and database layout

Please see clarifications below: - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Schema and database layout

Re: [sqlite] Schema and database layout

On 15 Sep 2009, at 10:27pm, Kavita Raghunathan wrote: > 1) dont see a way to reuse schemas. In other words, I have say 10 > tables with the same schema. How would I prevent doing 10 CREATE > table commands ? Would there be a way I can pass the schema as well > as data into SQLite ? If you

Re: [sqlite] Practicality of using pragma integrity_check at runtime

On 15 Sep 2009, at 10:09pm, rschnitzer wrote: > Also, now the additional question I have: is integrity_check known or > believed to return false positives... so it's responses should not be > strictly relied upon? I raised that possibility not because I know that integrity_check returns false

Re: [sqlite] Schema and database layout

> 1) dont see a way to reuse schemas. In other words, I have say 10 tables with > the same schema. How would I prevent doing 10 CREATE table commands ? Create 1 table with 1 additional column "schema" which will contain numbers from 1 to 10. > Would there be a way I can pass the schema as well

[sqlite] Schema and database layout

All, I have a couple of questions: I've been studying the C/C++ interface, and 1) dont see a way to reuse schemas. In other words, I have say 10 tables with the same schema. How would I prevent doing 10 CREATE table commands ? Would there be a way I can pass the schema as well as data into

Re: [sqlite] A question about how to interpret pragma integrity_check

Hello, On Sep 15, 2009, at 12:55 PM, D. Richard Hipp wrote: > > On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote: > >> Hello, >> >> Given the following pragma integrity_check output: >> >>> sqlite> pragma integrity_check; >>> rowid 106931 missing from index sqlite_autoindex_MyDBState_1 >>> rowid

Re: [sqlite] Practicality of using pragma integrity_check at runtime

hi Simon, Thank you for your response. You are right that 5 minutes is probalby overkill, once per day is adequate for my purposes. Given that, can anyone give any advice on the performance impact whenever integrity_check does run? Does it lock out other read and writes, for how long, other

Re: [sqlite] A question about how to interpret pragma integrity_check

On Sep 15, 2009, at 3:33 PM, Tito Ciuro wrote: > Hello, > > Given the following pragma integrity_check output: > >> sqlite> pragma integrity_check; >> rowid 106931 missing from index sqlite_autoindex_MyDBState_1 >> rowid 106933 missing from index sqlite_autoindex_MyDBState_1 >> rowid 106935

Re: [sqlite] Temporal SQLite database

Alberto Simões wrote: > Dear SQLite users, > > I am preparing a temporal database. Basically, entries will have a compound > key composed by the real key and some kind of time stamp. This is not > complicated to do, but I would like to know if anybody did something similar > and if: > - used the

[sqlite] A question about how to interpret pragma integrity_check

Hello, Given the following pragma integrity_check output: > sqlite> pragma integrity_check; > rowid 106931 missing from index sqlite_autoindex_MyDBState_1 > rowid 106933 missing from index sqlite_autoindex_MyDBState_1 > rowid 106935 missing from index sqlite_autoindex_MyDBState_1 > wrong # of

Re: [sqlite] UPDATE with JOIN fails

Amazing!! That worked! Thanks Igor! -- View this message in context: http://www.nabble.com/UPDATE-with-JOIN-fails-tp25458099p25460200.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] [ANN] SQLJet 1.0.0 released

Hello All, We're glad to announce that SQLJet 1.0.0 has been released and available for download at http://sqljet.com/ web site. SQLJet is an independent pure Java open source implementation of the SQLite core functionality. SQLJet provides API to create, read and modify SQLite databases, but

Re: [sqlite] UPDATE with JOIN fails

Nightfox79 wrote: > I'm trying to update one table based on information in an other. I > have two tables, transactions (trans) and accounts (account). I want > to update the debit amount in the transaction table (trans) with vat > information from the accounts table

[sqlite] UPDATE with JOIN fails

I'm trying to update one table based on information in an other. I have two tables, transactions (trans) and accounts (account). I want to update the debit amount in the transaction table (trans) with vat information from the accounts table (account) for a particular account. Normally (ie. in

Re: [sqlite] Database error message

On 15 Sep 2009, at 3:41pm, Jimmy Verner wrote: > Here's what happens: So far as I can tell, my new databases are built > just like the old one, but when I try to import them with SQLite > Database Browser ( see sqlitebrowser.sourceforge.net ) I am told > "Error importing data at line 7345345."

Re: [sqlite] Practicality of using pragma integrity_check at runtime

On 15 Sep 2009, at 3:35pm, rschnitzer wrote: > I have an application that cannot afford to be locked out of the > database > for more than maybe a fraction of a second. However I'd like my > application > to be able to monitor the database for corruption. > > Given that, can anyone provide

[sqlite] Database error message

This is very much a newbie question, so pls. bear with me. I've built an sqlite database that works on an iPhone app. However, I find that to create additional databases, I have to cut and paste off the first one because I can't seem to create further error-free databases. This is driving

[sqlite] Practicality of using pragma integrity_check at runtime

hi, I have an application that cannot afford to be locked out of the database for more than maybe a fraction of a second. However I'd like my application to be able to monitor the database for corruption. Given that, can anyone provide any general comments on running "pragma integrity_check"

Re: [sqlite] "make test" was failed

> i can't see the attachment It's seems that an attachment arhive file was deleted by Mailman (or by an administrator) because file size was exceed 80KB. > why don't you use the sqlite-amalgamation*.zip ? that's very easy to > compile I want to port sqlite to an embedded target. OS of embedded

Re: [sqlite] How to speed up a query between two tables?

On 15/09/2009 7:25 PM, Kermit Mei wrote: > On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: >> On 15/09/2009 4:47 PM, Kermit Mei wrote: >>> >>> sqlite> SELECT HomeDev.text, ZPhDev.id >>>...> FROM ZPhDev >>>...> INNER JOIN HomeDev ON (HomeDev.id = ZPhDev.id) >> Are you sure that you

[sqlite] Pseudo-random access violation on Win32

I have a version of SQLite 3.6.18 that I compile myself for Win32 using Visual Studio .NET 2008. The preprocessor looks like this: _WINDOWS;_USRDLL;_WINDLL;SQLITE_ENABLE_COLUMN_METADATA;SQLITE_MEMDEBUG. Without the SQLITE_MEMDEBUG directive defined, I get somewhat random crashes (access

[sqlite] Temporal SQLite database

Dear SQLite users, I am preparing a temporal database. Basically, entries will have a compound key composed by the real key and some kind of time stamp. This is not complicated to do, but I would like to know if anybody did something similar and if: - used the number of non-leap seconds since

Re: [sqlite] How to speed up a query between two tables?

On Tue, 2009-09-15 at 17:37 +1000, John Machin wrote: > On 15/09/2009 4:47 PM, Kermit Mei wrote: > > Hello community! > > > > I have two tables: > > > > HomeDev(id, text, icon) > > > > and > > > > ZPhDev(id,HomeDevId) > > > > Now, I must usually access(read-only) the items: > > > > sqlite>