Re: [sqlite] Permissions issue with SQLite

On 09/23/2011 03:09 AM, Magnus Thor Torfason wrote: On 9/22/2011 10:25, Dan Kennedy wrote: For new versions, new db files are created with the permissions specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS. Subject to umask of course.

Re: [sqlite] c-api document suggestion

Mira Suk wrote: > On 9/21/2011 21:22 Igor Tandetnik wrote: > >> You can include the NUL terminator, if you want it to actually be stored >> in the database. > > Actually you can't - if you do all SQL string functions will not work. > to be clear - > SELECT TRIM(what ever

Re: [sqlite] NUL handling bugs (was Re: c-api document suggestion)

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/22/2011 04:53 PM, David Garfield wrote: > As far as I am concerned, this is a SERIOUS bug in sqlite. It gets worse. Many of those string functions will also happily operate on blobs where it makes no sense. (A bunch of bytes without an

[sqlite] NUL handling bugs (was Re: c-api document suggestion)

As far as I am concerned, this is a SERIOUS bug in sqlite. When you have counted strings, which is all sqlite has (except for some API functions), NULs have no special meaning. This is fairly easily seen in the command line interface, where the code ignores the length and uses strlen forms.

[sqlite] Index not used in subquery

Hi, I have the following schema: CREATE TABLE a (id INT, partName TEXT, content BLOB, PRIMARY KEY(id, partName)); CREATE TABLE b (id, partName, content); CREATE VIEW aView AS SELECT a.id, a.partName, COALESCE(a.content, (SELECT b.content FROM b WHERE b.id=a.id AND b.partName=a.partName)) FROM a;

Re: [sqlite] c-api document suggestion

On 9/21/2011 21:22 Igor Tandetnik wrote: You can include the NUL terminator, if you want it to actually be stored in the database. Igor Tandetnik Actually you can't - if you do all SQL string functions will not work. to be clear - SELECT TRIM(what ever text column you stored with including

Re: [sqlite] Indices recreation

Am Donnerstag, den 22.09.2011, 20:28 +0100 schrieb Simon Slavin: > Indexes are not recreated from scratch unless you explicitly ask for them to > be (rare). If you open a database with a million rows in and add or change a > row, the indexes for that table are each modified slightly to reflect

Re: [sqlite] LEFT JOIN optimization

_ > Od: "Jim Morris" > Komu: > Datum: 22.09.2011 23:06 > Předmět: Re: [sqlite] LEFT JOIN optimization > > Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is > an associated ItemsME record so the left outer join is

Re: [sqlite] LEFT JOIN optimization

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is an associated ItemsME record so the left outer join is pointless. Just use the inner join. Normally the left outer join would include all of ItemsME_Properties, that probably explains the table scan.

[sqlite] LEFT JOIN optimization

EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106)   selectid    order    from    detail 0    0    0    SCAN TABLE ItemsME_Properties (~100 rows)

Re: [sqlite] Permissions issue with SQLite

On 9/22/2011 10:25, Dan Kennedy wrote: For new versions, new db files are created with the permissions specified by compilation option SQLITE_DEFAULT_FILE_PERMISSIONS. Subject to umask of course. http://www.sqlite.org/compile.html#default_file_permissions Thanks for your help. But I take it

Re: [sqlite] Indices recreation

On 22 Sep 2011, at 8:11pm, JM wrote: > is the index updated after each insert/update/delete operation or is > it updated after the transaction containing (many of) these operations > has been committed? That way an insertion of many lines into the > database would take time for index recreation

Re: [sqlite] Indices recreation

Thanks for your replies! They were very helpful! But is the index updated after each insert/update/delete operation or is it updated after the transaction containing (many of) these operations has been committed? That way an insertion of many lines into the database would take time for index

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On 22 Sep 2011, at 6:28pm, neelakanta reddy wrote: > The following is the result od pragma integrity check: > > sqlite> PRAGMA integrity_check; > *** in database main *** > Main freelist: 38 of 38 pages missing from overflow list starting at 0 > On tree page 608 cell 13: Rowid 1309 out of order

Re: [sqlite] Indices recreation

On 22 Sep 2011, at 5:30pm, Jay A. Kreibich wrote: > Indexes are updated automatically. As they are with all implementations of SQL. That's why you didn't see it documented: nobody thought there was any question. > Note: this means indexes will > speed up queries, but incur a performance

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

The following is the result od pragma integrity check: sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 38 of 38 pages missing from overflow list starting at 0 On tree page 608 cell 13: Rowid 1309 out of order (min less than parent min of 1315) Page 943 is never used Page

Re: [sqlite] Indices recreation

On Thu, Sep 22, 2011 at 06:17:06PM +0200, JM scratched on the wall: > Hello All > > I'm relatively new to sqlite and database stuff and I have a question > concerning indices in sqlite: > > If I create an index for a table to speed up searches, etc. and then do > writing operation on that table

[sqlite] Indices recreation

Hello All I'm relatively new to sqlite and database stuff and I have a question concerning indices in sqlite: If I create an index for a table to speed up searches, etc. and then do writing operation on that table (including removals and changes of lines), will I have to remove and recreate the

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On Thu, Sep 22, 2011 at 11:55 AM, neelakanta reddy < reddy.neelaka...@gmail.com> wrote: > The following is observed > What does this show: PRAGMA integrity_check; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

The following is observed sqlite> SELECT * FROM objects WHERE obj_id = 1309; 1309|155|entryId=62,tableId=CPGMembersTable,CSId=1|0 sqlite> If i try to select it from different column here i can see two rows having same obj_ids sqlite> SELECT * FROM objects WHERE class_id=155;

Re: [sqlite] Newbie starting off question

On Thu, Sep 22, 2011 at 4:39 PM, Pavel Ivanov wrote: > ...> might be a stupid question -- I am not sure ) Or perhaps one library > for CGI > > and one for the application? Or perhaps even a library for the cgi and > the > > application have the api as part of it? > If you're

Re: [sqlite] Newbie starting off question

> What would be the best way> to communicate with the DB? Would it make sense > just to have one linux> shared library containing the DB API ?  (I am a Linux > newbie too -- so this> might be a stupid question -- I am not sure ) Or > perhaps one library for CGI> and one for the application? Or

Re: [sqlite] Permissions issue with SQLite

On 09/22/2011 09:20 PM, Stephan Beal wrote: On Thu, Sep 22, 2011 at 4:16 PM, Magnus Thor Torfason< zulutime@gmail.com> wrote: SQLite version 3.3.6 Just to preempt the inevitable request to try it on a current version: this is reproducible on 3.7.2 (Ubuntu 10.10). For new versions,

Re: [sqlite] Permissions issue with SQLite

On Thu, Sep 22, 2011 at 4:16 PM, Magnus Thor Torfason < zulutime@gmail.com> wrote: > SQLite version 3.3.6 > Just to preempt the inevitable request to try it on a current version: this is reproducible on 3.7.2 (Ubuntu 10.10). -- - stephan beal http://wanderinghorse.net/home/stephan/

[sqlite] Permissions issue with SQLite

Hi, I'm having a permission issue with SQLite on my institution's computing grid/cluster. When I create a file using touch (or any other program for that matter, it correctly receives '-rw-rw' as the permission. However, when I create it from SQLite, it gets created with '-rw-r-'

Re: [sqlite] SQlite code checkout

On Thu, Sep 22, 2011 at 8:57 AM, Baruch Burstein wrote: > Thank you. > And what would the URL be for a specific tag? > http://www.sqlite.org/src/tarball/NAME-OF-FILE-AFTER-DOWNLOAD?uuid=TAG TAG can be any valid check-in or version or branch name or timestamp. See

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On 22 Sep 2011, at 1:26pm, neelakanta reddy wrote: > Here the obj_id which is primary is having duplicate primary value entries > > INSERT INTO "objects" > VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" >

Re: [sqlite] SQlite code checkout

Thank you. And what would the URL be for a specific tag? On Thu, Sep 22, 2011 at 3:17 PM, Richard Hipp wrote: > On Thu, Sep 22, 2011 at 8:05 AM, Baruch Burstein >wrote: > > > How can I get a clean copy of the latest SQLite trunk in a script? Fossil > >

Re: [sqlite] EXT : Assertion in Sqlite 3.7.x on Cygwin using WAL

Me thinkst the database can't open the WAL file. I don't see your script in the attachments but where is your database being opened? Michael D. Black Senior Scientist NG Information Systems Advanced Analytics Directorate From: sqlite-users-boun...@sqlite.org

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

here the Duplication means the object table obj_id which is a primary key i duplicated twice Here my question is for primary key (obj_id) , we are getting two entries for the same primary id which must be unique since the database is large i am pasting the db bump output CREATE TABLE objects

Re: [sqlite] Assertion in Sqlite 3.7.x on Cygwin using WAL

2011/9/21 Artyom Beilis > > While building with -DSQLITE_DEBUG > it reports an assertion: > > assertion "isExclusive==0 || isCreate" failed: file "sqlite3.c", line > 34058, function: winOpen > (Version 3.7.8) > Can you send us a stack trace at the point of the assert()?

Re: [sqlite] SQlite code checkout

On Thu, Sep 22, 2011 at 8:05 AM, Baruch Burstein wrote: > How can I get a clean copy of the latest SQLite trunk in a script? Fossil > seems to only have an option for a "checkout", with all the additional > files > involved. I want just a clean copy of the code, like the

Re: [sqlite] sqlite3_analyzer

Thank you, that worked. Thanks for sqlite and fossil, two amazing pieces of software! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Assertion in Sqlite 3.7.x on Cygwin using WAL

Hello, This simple script: pragma journal_mode=wal; CREATE TABLE IF NOT EXISTS sessions (   sid varchar(32) primary key not null ); begin; INSERT OR REPLACE INTO sessions values('0123456789abcdef0123456789abcde1'); -- There it fails after insert or replace before commit commit; Fails on

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On Thu, Sep 22, 2011 at 2:04 PM, Jay A. Kreibich wrote: > The PK (first column). The CREATE TABLE showed it as an INTEGER > PRIMARY KEY Ah, indeed. i skipped that and looked at the unique index :/. -- - stephan beal http://wanderinghorse.net/home/stephan/

[sqlite] SQlite code checkout

How can I get a clean copy of the latest SQLite trunk in a script? Fossil seems to only have an option for a "checkout", with all the additional files involved. I want just a clean copy of the code, like the sqlite-src-*.zip file on the download page. p.s. Bonus points if I can get just the files

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On Thu, Sep 22, 2011 at 01:46:25PM +0200, Stephan Beal scratched on the wall: > On Thu, Sep 22, 2011 at 1:37 PM, neelakanta reddy < > reddy.neelaka...@gmail.com> wrote: > > > INSERT INTO "objects" > > VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); > > <=== > > INSERT

[sqlite] Newbie starting off question

Hi All I am developing an embedded system based around Linux. It consists of an application collecting small amounts of data from the USB port. I wish to write this to SQLite. This application also needs to read small amounts of data from the DB. The client interface is via html/cgi on a web

Re: [sqlite] duplicate ( Corruption) in sqlite3 database

On Thu, Sep 22, 2011 at 1:37 PM, neelakanta reddy < reddy.neelaka...@gmail.com> wrote: > INSERT INTO "objects" > VALUES(1307,155,'entryId=60,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1308,155,'entryId=61,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" >

[sqlite] duplicate ( Corruption) in sqlite3 database

I am using sqlite3 along with drbd. I found the primary key for the table is inserted twice and the UNIQUE attribute of the table is also not unique. The application is inserting the objects into the table, and the machine got rebooted. After coming up from reboot, when i looked into the database