[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 i am seeing a duplicate rows inserted into the database. can you please explain us what could have been the reason for getting duplicate entries in the database.. The table is created with the following syntax CREATE TABLE objects (obj_id integer primary key, class_id integer, dn text, last_ccb integer); CREATE UNIQUE INDEX objects_idx on objects (dn); case 1: 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" VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); <=== INSERT INTO "objects" VALUES(1310,155,'entryId=63,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1311,155,'entryId=64,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1312,155,'entryId=65,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1313,155,'entryId=66,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1314,155,'entryId=67,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1315,155,'entryId=68,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); <== INSERT INTO "objects" VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0); INSERT INTO "objects" VALUES(1313,111,'fmAlarmModelId=CW,fmId=1',0); The following entries pointed by the arrow are duplicated. case 2: even though the specified dn is unique duplicate entries are being present in the database a) INSERT INTO "objects" VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0); INSERT INTO "objects" VALUES(1313,111,'fmAlarmModelId=CW,fmId=1',0); INSERT INTO "objects" VALUES(1314,111,'fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1315,110,'fmAlarmTypeId=ComSaAmfComponentCleanupFailed,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1316,110,'fmAlarmTypeId=ComSaAmfComponentInstantiationFailed,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1317,110,'fmAlarmTypeId=ComSaAmfSiUnassigned,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1318,110,'fmAlarmTypeId=ComSaCLMClusterNodeUnavailable,fmAlarmModelId=CW,fmId=1',0); INSERT INTO "objects" VALUES(1319,110,'fmAlarmTypeId=ComSaProxyStatusOfAComponentChangedToUnproxied,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1320,113,'fmId=1',18); INSERT INTO "objects" VALUES(1321,109,'folderQuotaInfoId=ACS_DATA,systemSupervisionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1322,109,'folderQuotaInfoId=ACS_LOGS,systemSupervisionMId=1,safApp=safImmService',0); b) INSERT INTO "objects" VALUES(1360,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1361,118,'externalMediaFunctionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1362,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1363,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0); INSERT INTO "objects" VALUES(1364,111,'fmAlarmModelId=CW,fmId=1',0); INSERT INTO "objects" VALUES(1365,111,'fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1366,110,'fmAlarmTypeId=ComSaAmfComponentCleanupFailed,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1367,110,'fmAlarmTypeId=ComSaAmfComponentInstantiationFailed,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1368,110,'fmAlarmTypeId=ComSaAmfSiUnassigned,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1369,110,'fmAlarmTypeId=ComSaCLMClusterNodeUnavailable,fmAlarmModelId=CW,fmId=1',0); INSERT INTO "objects" VALUES(1370,110,'fmAlarmTypeId=ComSaProxyStatusOfAComponentChangedToUnproxied,fmAlarmModelId=coreMw,fmId=1',0); INSERT INTO "objects" VALUES(1371,113,'fmId=1',0); INSERT INTO "objects" VALUES(1372,109,'folderQuotaInfoId=ACS_DATA,systemSupervisionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1373,109,'folderQuotaInfoId=ACS_LOGS,systemSupervisionMId=1,safApp=safImmService',0); Here the dn name is unique but still i am seeing duplicate dn names. -Neelakanta __
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" > VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); > <=== > INSERT INTO "objects" > VALUES(1310,155,'entryId=63,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1311,155,'entryId=64,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1312,155,'entryId=65,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1313,155,'entryId=66,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1314,155,'entryId=67,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1315,155,'entryId=68,tableId=CPGMembersTable,CSId=1',0); > INSERT INTO "objects" > VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); > <== > INSERT INTO "objects" > VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0); > INSERT INTO "objects" > > VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0); > INSERT INTO "objects" > VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0); > INSERT INTO "objects" VALUES(1313,111,'fmAlarmModelId=CW,fmId=1',0); > > > The following entries pointed by the arrow are duplicated. > Where are they duplicated? The output you provide does not show any duplicates (all have a unique 3rd column value). ... > Here the dn name is unique but still i am seeing duplicate dn names. > Please show us the duplicates. i don't see any dupes in your inserts and you didn't present any SELECT data which shows duplicates. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 server on the device. The cgi too needs to read and write to the DB. All applications and cgi are written in C -- so the question is: 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 perhaps even a library for the cgi and the application have the api as part of it? Any suggestions would be most welcome Thanks David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 INTO "objects" > > VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); > > <== > Where are they duplicated? The output you provide does not show any > duplicates (all have a unique 3rd column value). The PK (first column). The CREATE TABLE showed it as an INTEGER PRIMARY KEY, meaning that, if this happened as shown, the database would be corrupt. > i don't see any dupes in your inserts and you didn't present any > SELECT data which shows duplicates. Yes, please. Frequently when questions like this come up, the SELECTs don't actually show a problem. How are you inserting these? Your own code? Are you carefully checking all return codes? Do you actually know the dups are ending up in the database? -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 needed for building the amalgamation, without art/contrib/ext/test/doc/etc. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-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 Cygwin and passess without problems using
windows builds.
With normal build the script reports
unable to open database file
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)
This does not happen on Windows builds of the same sources.
I've attached the script and VDBE traces under cygwin and windows builds.
Cygwin version: 1.7.9(0.237/5/3)
Artyom Beilis
--
CppCMS - C++ Web Framework: http://cppcms.sf.net/
CppDB - C++ SQL Connectivity: http://cppcms.sf.net/sql/cppdb/VDBE Execution Trace:
0 Expire 000 00
1 Halt 000 00
VDBE Execution Trace:
0 Trace000 00
1 Noop 000 00
2 Goto 0 120 00
12 Transaction 000 00
13 TableLock010 sqlite_master 00
14 Goto 030 00
3 OpenRead 010 500 sqlite_master
4 Rewind 0 100 00
10 Close000 00
11 Halt 000 00
VDBE Execution Trace:
0 JournalMode 015 00
REG[1] = t3[wal](8)
1 ResultRow110 00
REG[1] = t3[wal](8)
wal
2 Halt 000 00
VDBE Execution Trace:
0 Trace000 00
1 Goto 0 480 00
48 Transaction 010 00
49 VerifyCookie 000 00
50 TableLock011 sqlite_master 00
51 Goto 020 00
2 ReadCookie 032 00
REG[3] = i:0
3 If 380 00
REG[3] = i:0
4 Integer 130 00
REG[3] = i:1
5 SetCookie023 00
REG[3] = i:1
6 Integer 130 00
REG[3] = i:1
7 SetCookie053 00
REG[3] = i:1
8 CreateTable 020 00
REG[2] = i:2
9 OpenWrite010 500
10 NewRowid 010 00
REG[1] = i:1
11 Null 030 00
REG[3] = NULL
12 Insert 031 08
REG[3] = NULL
REG[1] = i:1
13 Close000 00
14 CreateIndex 040 00
REG[4] = i:3
15 OpenWrite110 500 sqlite_master
16 NewRowid 150 00
REG[5] = i:2
17 String8 060 index 00
REG[6] = t5[index](8)
18 String8 070 sqlite_autoindex_sessions_1 00
REG[7] = t27[sqlite_autoinde](8)
19 String8 080 sessions 00
REG[8] = t8[sessions](8)
20 Copy 490 00
REG[4] = i:3
REG[9] = i:3
REG[9] = i:3
21 Null 0 100 00
REG[10] = NULL
22 MakeRecord 65 11 aaada 00
REG[11] = z47[0617431D0100696E64657873716C6974..C...indexsqlit](8)
23 Insert 1 115 18
REG[11] = z47[0617431D0100696E64657873716C6974..C...indexsqlit](8)
REG[5] = i:2
24 Close100 00
25 Close000 00
26 Null 0 120 00
REG[12] = NULL
27 OpenWrite210 500 sqlite_master
28 MustBeInt1 310 00
REG[1] = i:1
29 NotExists2 311 00 pk
REG[1] = i:1
30 Rowid2 120 00
REG[12] = i:1
31 NotNull 12 330 00
REG[12] = i:1
33 NotExists2 32 12 00
REG[12] = i:1
34 String8 0 130 table 00
REG[13] = t5[table](8)
35 String8 0 140 sessions 00
REG[14] = t8[sessions](8)
36 String8 0 150 sessions 00
REG[15] = t8[sessions](8)
37 Copy 2 160 00
REG[2] = i:2
REG[16] = i:2
REG[16] = i:2
38 String8 0 170 CREATE TABLE sessions (
sid varchar(32) primary key not null
) 00
REG[17] = t64[CREATE TABLE se](8)
39 NotExists2 40 12 00
REG[12] = i:1
40 MakeRecord 135 18 aaada 00
REG[18] = z93[07171D1D01810D7461626C6573657373...tablesess](8)
41 Insert 2 18 12 00
REG[18] = z93[07171D1D01810D7461626C6573657373...tablesess](8)
REG[12] = i:1
42 Goto 0 320 00
32 Goto 0 430 00
43 Close200 00
44 Integer 1 190 00
REG[19] = i:1
45 SetCookie01 19 00
REG[19] = i:1
46 ParseSchema 000 tbl_name='sessions' 00
VDBE Execution
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
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 sqlite-src-*.zip > file on the download page. > wget http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk > > p.s. Bonus points if I can get just the files needed for building the > amalgamation, without art/contrib/ext/test/doc/etc. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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()? > > This does not happen on Windows builds of the same sources. > > > I've attached the script and VDBE traces under cygwin and windows builds. > > Cygwin version: 1.7.9(0.237/5/3) > > > > Artyom Beilis > -- > CppCMS - C++ Web Framework: http://cppcms.sf.net/ > CppDB - C++ SQL Connectivity: http://cppcms.sf.net/sql/cppdb/ > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 (obj_id integer primary key, class_id integer, dn text, last_ccb integer); INSERT INTO "objects" VALUES(1,127,'APG43L_DDISKPATH=ACS_ACA_DATA,safApp=safImmService',0); INSERT INTO "objects" VALUES(2,127,'APG43L_DDISKPATH=ACS_ALEC_DATA,safApp=safImmService',0); INSERT INTO "objects" VALUES(3,127,'APG43L_DDISKPATH=ACS_CHB_DATA,safApp=safImmService',0); INSERT INTO "objects" VALUES(4,127,'APG43L_DDISKPATH=ACS_DATA,safApp=safImmService',0); INSERT INTO "objects" VALUES(5,127,'APG43L_DDISKPATH=ACS_DATA_BASE,safApp=safImmService',0); INSERT INTO "objects" VALUES(6,127,'APG43L_DDISKPATH=ACS_LOGS,safApp=safImmService',0); INSERT INTO "objects" VALUES(7,127,'APG43L_DDISKPATH=ACS_LOGS_BASE,safApp=safImmService',0); INSERT INTO "objects" VALUES(8,127,'APG43L_DDISKPATH=ACS_MTZ_DATA,safApp=safImmService',0); INSERT INTO "objects" VALUES(9,127,'APG43L_DDISKPATH=ACS_RTR_DATA,safApp=safImmService',0); | | | INSERT INTO "objects" VALUES(1304,155,'entryId=6,tableId=CPIdTable,CSId=1',0); INSERT INTO "objects" VALUES(1305,155,'entryId=6,tableId=HWCTable,CSId=1',0); INSERT INTO "objects" VALUES(1306,155,'entryId=6,tableId=NETable,CSId=1',0); 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" VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1310,155,'entryId=63,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1311,155,'entryId=64,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1312,155,'entryId=65,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1313,155,'entryId=66,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1314,155,'entryId=67,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1315,155,'entryId=68,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); INSERT INTO "objects" VALUES(1310,118,'externalMediaFunctionMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1311,115,'fileOutputId=1,statisticalCounterMId=1,safApp=safImmService',0); INSERT INTO "objects" VALUES(1312,114,'filterId=1,ldapId=1,ldapAuthenticationMethodId=1',0); 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" VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); can some one please tell me why the primary key is coming twice -Neel. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 [sqlite-users-boun...@sqlite.org] on
behalf of Artyom Beilis [artyom...@yahoo.com]
Sent: Wednesday, September 21, 2011 9:51 AM
To: sqlite-users@sqlite.org
Cc: artyom...@yahoo.com
Subject: EXT :[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 Cygwin and passess without problems using
windows builds.
With normal build the script reports
unable to open database file
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)
This does not happen on Windows builds of the same sources.
I've attached the script and VDBE traces under cygwin and windows builds.
Cygwin version: 1.7.9(0.237/5/3)
Artyom Beilis
--
CppCMS - C++ Web Framework: http://cppcms.sf.net/
CppDB - C++ SQL Connectivity: http://cppcms.sf.net/sql/cppdb/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 > > 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. > > > > > wget > http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk > > > > > > > p.s. Bonus points if I can get just the files needed for building the > > amalgamation, without art/contrib/ext/test/doc/etc. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] 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" > VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); > > can some one please tell me why the primary key is coming twice How do you know that the second INSERT statement is working ? Please show the result of SELECT * FROM objects WHERE obj_id = 1309 Also, I tested it myself: SQLite version 3.7.5 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE objects (obj_id integer primary key, class_id integer, dn text, ...> last_ccb integer); sqlite> INSERT INTO "objects" ...> VALUES(1309,155,'entryId=62,tableId=CPGMembersTable,CSId=1',0); sqlite> INSERT INTO "objects" ...> VALUES(1309,155,'entryId=99,tableId=CPGMembersTable,CSId=1',0); Error: PRIMARY KEY must be unique Cannot reproduce using version 3.7.5. Please try using just those three statements so we can tell what kind of bug you have. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 http://www.fossil-scm.org/fossil/doc/trunk/www/checkin_names.wiki for further information on supported TAG names. > 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 > > > 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. > > > > > > > > > wget > > http://www.sqlite.org/src/tarball/sqlite-latest-trunk.tar.gz?uuid=trunk > > > > > > > > > > > > p.s. Bonus points if I can get just the files needed for building the > > > amalgamation, without art/contrib/ext/test/doc/etc. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@sqlite.org > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > > > > > -- > > D. Richard Hipp > > d...@sqlite.org > > ___ > > 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 > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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-' (group does not get write permissions, even if it should get them). I've confirmed this both from the command line client (demonstration script below) and from RSQLite (the SQLite library for R, demonstration script is longer but could attach if anyone thinks it helps). I've checked with my admins, and they tell me that there was nothing unusual about how SQLite was installed. Any ideas for what could be the cause of this? It seems that to override the umask setting, SQLite must be doing something explicitly, right? ## Reproduction script below HOSTNAME [TestPermissions]$ umask 0007 HOSTNAME [TestPermissions]$ ll total 0 HOSTNAME [TestPermissions]$ touch TouchedFile.txt HOSTNAME [TestPermissions]$ sqlite3 DatabaseFile.sqlite SQLite version 3.3.6 Enter ".help" for instructions sqlite> .tables sqlite> .quit HOSTNAME [TestPermissions]$ ll total 0 -rw-r- 1 mtorfason faculty 0 Sep 22 10:07 DatabaseFile.sqlite -rw-rw 1 mtorfason faculty 0 Sep 22 10:07 TouchedFile.txt ## End reproduction script Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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, 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 perhaps even a > library for the cgi and the> application have the api as part of it? All this doesn't matter at all. The only restriction - both should use compatible versions of SQLite, i.e. if somewhere there's newer version of SQLite then you don't use its new features. And the easiest way would be to compile SQLite into both your cgi and application. Pavel On Thu, Sep 22, 2011 at 8:02 AM, david wrote: > 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 server on > the device. The cgi too needs to read and write to the DB. All applications > and cgi are written in C -- so the question is: 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 perhaps even a library for the cgi and the > application have the api as part of it? > > Any suggestions would be most welcome > > Thanks > > David > ___ > 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] 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 developing a CGI, i would recommend what Pavel did: compile the sqlite3 amalgamation into your project. The problem i've had with CGIs is that my primary hoster has an ancient version of sqlite3 installed, so i can't build against it. Having the amalgamation in my tree works around that. -- - stephan beal http://wanderinghorse.net/home/stephan/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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; 1086|155|entryId=0,tableId=CPIdTable,CSId=1|0 1087|155|entryId=1,tableId=CPGMembersTable,CSId=1|0 1088|155|entryId=1,tableId=CPGNamesTable,CSId=1|0 1089|155|entryId=1,tableId=CPIdTable,CSId=1|0 1090|155|entryId=1,tableId=HWCTable,CSId=1|0 1091|155|entryId=1,tableId=NETable,CSId=1|0 1092|155|entryId=10,tableId=CPGMembersTable,CSId=1|0 1093|155|entryId=10,tableId=CPGNamesTable,CSId=1|0 1094|155|entryId=10,tableId=CPIdTable,CSId=1|0 1095|155|entryId=10,tableId=HWCTable,CSId=1|0 1096|155|entryId=100,tableId=CPGMembersTable,CSId=1|0 1097|155|entryId=1001,tableId=CPIdTable,CSId=1|0 1098|155|entryId=101,tableId=CPGMembersTable,CSId=1|0 1099|155|entryId=102,tableId=CPGMembersTable,CSId=1|0 1100|155|entryId=103,tableId=CPGMembersTable,CSId=1|0 1101|155|entryId=104,tableId=CPGMembersTable,CSId=1|0 1102|155|entryId=105,tableId=CPGMembersTable,CSId=1|0 1103|155|entryId=106,tableId=CPGMembersTable,CSId=1|0 1104|155|entryId=107,tableId=CPGMembersTable,CSId=1|0 1105|155|entryId=108,tableId=CPGMembersTable,CSId=1|0 1106|155|entryId=109,tableId=CPGMembersTable,CSId=1|0 1107|155|entryId=11,tableId=CPGMembersTable,CSId=1|0 1108|155|entryId=11,tableId=CPGNamesTable,CSId=1|0 1109|155|entryId=11,tableId=CPIdTable,CSId=1|0 1110|155|entryId=11,tableId=HWCTable,CSId=1|0 |155|entryId=110,tableId=CPGMembersTable,CSId=1|0 1112|155|entryId=111,tableId=CPGMembersTable,CSId=1|0 1113|155|entryId=112,tableId=CPGMembersTable,CSId=1|0 1114|155|entryId=113,tableId=CPGMembersTable,CSId=1|0 1115|155|entryId=114,tableId=CPGMembersTable,CSId=1|0 1116|155|entryId=115,tableId=CPGMembersTable,CSId=1|0 1117|155|entryId=116,tableId=CPGMembersTable,CSId=1|0 1118|155|entryId=117,tableId=CPGMembersTable,CSId=1|0 1119|155|entryId=118,tableId=CPGMembersTable,CSId=1|0 1120|155|entryId=119,tableId=CPGMembersTable,CSId=1|0 1121|155|entryId=12,tableId=CPGMembersTable,CSId=1|0 1122|155|entryId=12,tableId=CPGNamesTable,CSId=1|0 1123|155|entryId=12,tableId=CPIdTable,CSId=1|0 1124|155|entryId=120,tableId=CPGMembersTable,CSId=1|0 1125|155|entryId=121,tableId=CPGMembersTable,CSId=1|0 1126|155|entryId=122,tableId=CPGMembersTable,CSId=1|0 1127|155|entryId=123,tableId=CPGMembersTable,CSId=1|0 1128|155|entryId=124,tableId=CPGMembersTable,CSId=1|0 1129|155|entryId=125,tableId=CPGMembersTable,CSId=1|0 1130|155|entryId=126,tableId=CPGMembersTable,CSId=1|0 1131|155|entryId=127,tableId=CPGMembersTable,CSId=1|0 1132|155|entryId=128,tableId=CPGMembersTable,CSId=1|0 1133|155|entryId=129,tableId=CPGMembersTable,CSId=1|0 1134|155|entryId=13,tableId=CPGMembersTable,CSId=1|0 1135|155|entryId=13,tableId=CPGNamesTable,CSId=1|0 1136|155|entryId=13,tableId=CPIdTable,CSId=1|0 1137|155|entryId=130,tableId=CPGMembersTable,CSId=1|0 1138|155|entryId=131,tableId=CPGMembersTable,CSId=1|0 1139|155|entryId=132,tableId=CPGMembersTable,CSId=1|0 1140|155|entryId=133,tableId=CPGMembersTable,CSId=1|0 1141|155|entryId=134,tableId=CPGMembersTable,CSId=1|0 1142|155|entryId=135,tableId=CPGMembersTable,CSId=1|0 1143|155|entryId=136,tableId=CPGMembersTable,CSId=1|0 1144|155|entryId=137,tableId=CPGMembersTable,CSId=1|0 1145|155|entryId=138,tableId=CPGMembersTable,CSId=1|0 1146|155|entryId=139,tableId=CPGMembersTable,CSId=1|0 1147|155|entryId=14,tableId=CPGMembersTable,CSId=1|0 1148|155|entryId=14,tableId=CPGNamesTable,CSId=1|0 1149|155|entryId=14,tableId=CPIdTable,CSId=1|0 1150|155|entryId=140,tableId=CPGMembersTable,CSId=1|0 1151|155|entryId=141,tableId=CPGMembersTable,CSId=1|0 1152|155|entryId=142,tableId=CPGMembersTable,CSId=1|0 1153|155|entryId=143,tableId=CPGMembersTable,CSId=1|0 1154|155|entryId=144,tableId=CPGMembersTable,CSId=1|0 1155|155|entryId=145,tableId=CPGMembersTable,CSId=1|0 1156|155|entryId=146,tableId=CPGMembersTable,CSId=1|0 1157|155|entryId=147,tableId=CPGMembersTable,CSId=1|0 1158|155|entryId=148,tableId=CPGMembersTable,CSId=1|0 1159|155|entryId=149,tableId=CPGMembersTable,CSId=1|0 1160|155|entryId=15,tableId=CPGMembersTable,CSId=1|0 1161|155|entryId=15,tableId=CPGNamesTable,CSId=1|0 1162|155|entryId=15,tableId=CPIdTable,CSId=1|0 1163|155|entryId=150,tableId=CPGMembersTable,CSId=1|0 1164|155|entryId=151,tableId=CPGMembersTable,CSId=1|0 1165|155|entryId=152,tableId=CPGMembersTable,CSId=1|0 1166|155|entryId=153,tableId=CPGMembersTable,CSId=1|0 1167|155|entryId=154,tableId=CPGMembersTable,CSId=1|0 1168|155|entryId=155,tableId=CPGMembersTable,CSId=1|0 1169|155|entryId=156,tableId=CPGMembersTable,CSId=1|0 1170|155|entryId=157,tableId=CPGMembersTable,CSId=1|0 1171|155|entryId=158,tableId=CPGMembersTable,CSId=1|0 1172|155|entryId=159,tableId=CPGMembersTable,CSId=1|0 1173|155|entryId=16,tableId=CPGMembersTable,CSId=1|0 1174|155|entryId=16,tableId=CPGNamesTable,CSId=1|0 1175|155|entryId=16,tableId=CPIdTable,CSId=1|0
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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 index manually or will the recreation of the index be handled automatically by sqlite? I couldn't find this information in the sqlite documentation. Regards JM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 (including removals and changes of > lines), will I have to remove and recreate the index manually or will > the recreation of the index be handled automatically by sqlite? Indexes are updated automatically. Note: this means indexes will speed up queries, but incur a performance penalty for insert/update/delete operations. Stats (see ANALYZE) are *not* updated automatically, so if you are using those and your table stats are somewhat dynamic, you may need to re-run ANALYZE. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 944 is never used Page 945 is never used Page 946 is never used Page 947 is never used Page 948 is never used Page 949 is never used Page 950 is never used Page 951 is never used Page 952 is never used Page 953 is never used Page 954 is never used Page 955 is never used Page 956 is never used Page 957 is never used Page 958 is never used Page 959 is never used Page 960 is never used Page 961 is never used Page 962 is never used Page 963 is never used Page 964 is never used Page 965 is never used Page 966 is never used Page 967 is never used Page 968 is never used Page 970 is never used Page 971 is never used Page 972 is never used Page 973 is never used Page 974 is never used Page 975 is never used Page 976 is never used Page 977 is never used Page 978 is never used Page 979 is never used Page 980 is never used Page 981 is never used Page 982 is never used Page 983 is never used Page 984 is never used Page 985 is never used rowid 1309 missing from index objects_idx rowid 1310 missing from index objects_idx rowid 1311 missing from index objects_idx rowid 1312 missing from index objects_idx rowid 1313 missing from index objects_idx rowid 1314 missing from index objects_idx rowid 1315 missing from index objects_idx rowid 1316 missing from index objects_idx rowid 1317 missing from index objects_idx rowid 1318 missing from index objects_idx rowid 1319 missing from index objects_idx rowid 1320 missing from index objects_idx rowid 1321 missing from index objects_idx rowid 1322 missing from index objects_idx wrong # of entries in index objects_idx Error: database disk image is malformed sqlite> Neelakanta ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 penalty for > insert/update/delete operations. ... but not much. I mean, don't expect a single INSERT to take a tenth of a second unless you have a hundred indexes or something. > Stats (see ANALYZE) are *not* updated automatically, so if you are > using those and your table stats are somewhat dynamic, you may need > to re-run ANALYZE. ... but again, not much. But your searches can be pretty fast even if you never use ANALYZE on the database. And the statistics ANALYZE produces are meant to be a pointer to the nature of your data so unless the type of data you're putting in the database has changed radically there's really not much point in running ANALYZE again. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 (min less than parent min > of 1315) > Page 943 is never used Are you using any PRAGMAs in apps which manipulate the database ? For instance 'PRAGMA synchronous = 0'. Are there any crashes or power-losses involved in getting the database to this stage ? Are you checking the result values returned by every SQLite operation, from the time you create the database to the point where 'PRAGMA integrity_check' shows something wrong ? If you do the same operations with the SQLite shell tool, do you get the same errors at the same point ? You can do this by simply putting all the commands in a text file and using '.read SQLCommands.txt' in the SQLite shell tool. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 recreation only once, wouldn't it? best regards JM Am Donnerstag, den 22.09.2011, 18:43 +0100 schrieb Simon Slavin: > 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 penalty for > > insert/update/delete operations. > > ... but not much. I mean, don't expect a single INSERT to take a tenth of a > second unless you have a hundred indexes or something. > > > Stats (see ANALYZE) are *not* updated automatically, so if you are > > using those and your table stats are somewhat dynamic, you may need > > to re-run ANALYZE. > > ... but again, not much. But your searches can be pretty fast even if you > never use ANALYZE on the database. And the statistics ANALYZE produces are > meant to be a pointer to the nature of your data so unless the type of data > you're putting in the database has changed radically there's really not much > point in running ANALYZE again. > > 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] 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 only once, wouldn't it? 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 the change. The amount of word done is a very little work because it was a very little change, not a lot of work because all the million entries have to be rewritten. As to when the changes to the index are actually done, from the point of view of the program making the changes, they are done after each operation. From the point of view of other programs with the same database open, they are done only after the transaction has been committed. (The above explanation is simplified for clarity.) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 that the "subject to umask" means that for each permission bit, the file will have the stricter permission of default and umask, so the only way to get looser permissions would be to compile a new version after changing the compilation option. No way to do that by setting an environmental variable or anything like that? Best, Magnus ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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) 0 1 1 SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~2 rows) 0 0 0 EXECUTE LIST SUBQUERY 1 1 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 0 0 EXECUTE LIST SUBQUERY 2 2 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 0 0 USE TEMP B-TREE FOR DISTINCT this takes about 3 seconds EXPLAIN QUERY PLAN SELECT DISTINCT ItemsME.Points FROM (ItemsME_Properties INNER JOIN ItemsME ON ItemsME_Properties.IDR = ItemsME.IDR) WHERE ItemsME.IDR IN (SELECT IDR FROM cProds WHERE Prod = 106) 0 0 1 SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~25 rows) 0 0 0 EXECUTE LIST SUBQUERY 1 1 0 0 SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows) 0 1 0 SEARCH TABLE ItemsME_Properties USING COVERING INDEX IDR (IDR=?) (~1 rows) 0 0 0 USE TEMP B-TREE FOR DISTINCT this runs in about 15 milisec => results are equal from both cases as ItemsME_Properties is temporary table (updated with triggers as reads are much more frequent) to workaround performance issues with search... (it should be 1:1 with ItemsME, just with less data, and some data reformatted) maybe I'm just stupid but I don't really see reason for scan in first case. am I really stupid ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 pointless. Just > use the inner join. Normally the left outer join would include all of > ItemsME_Properties, that probably explains the table scan. Yeah. the answer is I'm stupid. the left join was just for test sake (as was said tables are 1:1 and IDR is unique) as I have all kinds of performance problems with left outers. this one is on me not on SQLite lol - still would say it's strange quirk - but the query is also strange. Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 the > change. The amount of word done is a very little work because it was a very > little change, not a lot of work because all the million entries have to be > rewritten. > Ok. Good to know. Thanks! > As to when the changes to the index are actually done, from the point of view > of the program making the changes, they are done after each operation. From > the point of view of other programs with the same database open, they are > done only after the transaction has been committed. Got it. best regards ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 null on end of string) will not trim that string. found out the hard way. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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; CREATE INDEX bIndex ON b (id, partName); and the following query has a query plan that uses bIndex: EXPLAIN QUERY PLAN SELECT b.content FROM b WHERE b.id=? AND b.partName=? 0|0|0|SEARCH TABLE b USING INDEX bIndex (id=? AND partName=?) (~9 rows) However the subquery in the view yields a different query plan: EXPLAIN QUERY PLAN SELECT * FROM aView WHERE id=? AND partName=?; 0|0|0|SEARCH TABLE a USING INDEX sqlite_autoindex_a_1 (id=? AND partName=?) (~1 rows) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1 1|0|0|SCAN TABLE b (~1 rows) Is there a reason why the index is not used in the subquery? Best regards, Filip Navara ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[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. Testing various functions: hex(), relational operators, and group by get it right. max() and min() seem to get it right (but I may not have tested enough). length(), quote(), upper(), lower(), like(), and glob() get it wrong. group_concat() gets it wrong returning the result, but looks like it may have built the right thing internally. replace(), trim(), ltrim(), rtrim() get it right in the first parameter, but not in the second parameter. replace() gets it right in the third parameter. I'm not sure what else would be expected to allow NULs, so I didn't try numeric and date/time functions. Testing can be a bit of a pain. Things like: BEGIN TRANSACTION; CREATE TABLE t(a); insert into t values (cast(x'6F6E65202020' as text)); insert into t values (cast(x'6F6E6520202030' as text)); insert into t values (cast(x'6F6E6520202000' as text)); insert into t values (cast(x'6F6E6520202078' as text)); insert into t values (cast(x'6F6E650074776F20' as text)); insert into t values (cast(x'20006F6E6520' as text)); insert into t values (cast(x'00206F6E6520' as text)); COMMIT; .mode column .width 10 20 20 20 .header on select a, hex(a), hex(trim(a)), hex(trim(a,'one'||x'2000')) from t; Yields: a hex(a)hex(trim(a)) hex(trim(a,'one'||x' -- one 6F6E65202020 6F6E65 one 0 6F6E65202020306F6E652020203030 one 6F6E65202020006F6E652020200000 one x 6F6E65202020786F6E652020207878 one 6F6E650074776F20 6F6E650074776F007477 20006F6E6520 006F6E65 00 00206F6E6520 00206F6E6500 What I expect (including column screw-ups because NUL is zero width in xterm) is: a hex(a)hex(trim(a)) hex(trim(a,'one'||x' -- one 6F6E65202020 6F6E65 one 0 6F6E65202020306F6E652020203030 one6F6E65202020006F6E6520202000 one x 6F6E65202020786F6E652020207878 onetwo 6F6E650074776F20 6F6E650074776F7477 two 20006F6E6520 006F6E65 two 00206F6E6520 00206F6E65 Without the hex() calls, you can't even tell what worked and what didn't. --David Garfield Mira Suk writes: > 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 null on end of > string) > will not trim that string. > > found out the hard way. > > ___ > 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] 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 encoding cannot
be treated as text.)
You can also create UTF16 strings with an odd number of bytes.
sqlite> select cast(x'6a6b6c' as text);
jkl
sqlite> pragma encoding=utf16;
sqlite> select cast(x'6a6b6c' as text);
歪
sqlite> select length(cast(x'6a6b6c' as text));
1
sqlite> select hex(cast(x'6a6b6c' as text));
6A6B6C
sqlite> select hex('jkl');
6A006B006C00
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
iEYEARECAAYFAk570G4ACgkQmOOfHg372QSBnACfQ5zkX2sRU7L88LeuQ4R6dKyM
2D4An2zeXE6YYpBIceSnpB7Yr5HbSI92
=eQST
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 text column you stored with including null on end of > string) > will not trim that string. It does work - it removes all whitespace from the end of the string, up to but not including the first non-whitespace character. Which happens to be NUL. It follows the spec perfectly, it's your expectations that are wrong. Note that I didn't say it was wise to store NUL characters as part of the string - I only said that you could do it if you wanted to. sqlite3_bind_text takes the length parameter at face value, and stores exactly as many bytes as you tell it to store. It's up to you to ensure that the values actually make sense for your application. Garbage in/garbage out and all that. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. http://www.sqlite.org/compile.html#default_file_permissions Thanks for your help. But I take it that the "subject to umask" means that for each permission bit, the file will have the stricter permission of default and umask, so the only way to get looser permissions would be to compile a new version after changing the compilation option. No way to do that by setting an environmental variable or anything like that? That's correct. Really, you need to upgrade anyhow. 3.3.6 came out in 2006. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
