[sqlite] import txt file to sqlite
I wanna import a text file to sqlite db, this follow code is right? char *zErrMsg = 0; sqlite3 *db; rc=sqlite3_open(foods.db,db); sqlite3_exec(db, CREATE TABLE contact (fliename varchar(128) UNIQUE, fzip blob, ntest int, ntest1 int);, 0, 0, zErrMsg); sqlite3_exec(db, .separator ',';, 0, 0, zErrMsg); sqlite3_exec(db, .import e:/contact.txt contact, 0, 0, zErrMsg); thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import txt file to sqlite
On 12 March 2012 08:35, YAN HONG YE yanhong...@mpsa.com wrote: I wanna import a text file to sqlite db, this follow code is right? char *zErrMsg = 0; sqlite3 *db; rc=sqlite3_open(foods.db,db); sqlite3_exec(db, CREATE TABLE contact (fliename varchar(128) UNIQUE, fzip blob, ntest int, ntest1 int);, 0, 0, zErrMsg); sqlite3_exec(db, .separator ',';, 0, 0, zErrMsg); sqlite3_exec(db, .import e:/contact.txt contact, 0, 0, zErrMsg); No. Sqlite3 dot commands are not executed by the sqlite3 library, but by shell.c http://www.sqlite.org/sqlite.html thanks! Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Bug? Subtract Two Sum-Values
Hi again and sorry when this Posting notreally required. Few days ago i posted following problem, a testcase was required and I attached a testcase in my answer. Now i'm not sure if the testcase reciving the MaillingList, so i send the testcase again. Should be the testcase already arrived the MaillingList, a Admin delete this message please. Markus = markusge Thu, 08 Mar 2012 05:39:31 -0800 Hi, I have a problem with a Select which subtract two Sum-Values. Normally the correct value of my Select should be 0 but when ich run my Select Sqlite bring back as Result 9.09494 This is my Select: select kto,sum(neg-pos) from ( select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen where kto1 =3020 group by kto1 union select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen where kto2 =3020 group by kto2 ) group by kto; If I Only Run the inner Select select kto1 kto,sum(betrag) pos,sum(0) neg from buchungen where kto1 =3020 group by kto1 union select kto2 kto,sum(0) pos, sum(betrag) neg from buchungen where kto2 =3020 group by kto2 Sqlite give me the correct values 3020| 0 |7154.79 3020| 7154.79 |0 ___ Ihr WEB.DE Postfach immer dabei: die kostenlose WEB.DE Mail App für iPhone und Android. https://produkte.web.de/freemail_mobile_startseite/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Risk of setting SQLITE_DISABLE_DIRSYNC ?
Hi there, We are integrating SQLite into our software and I would like to understand what the impact of enabling SQLITE_DISABLE_DIRSYNC is, in particular when running on ext3 and ext4 filesystems (with journal=ordered mode). I searched the mail archives and could not find a response, the doc is also a bit short. For info, the Fedora RPM for sqlite has this enabled. Any explanation appreciated. Thanks, -- Diego Santa Cruz, PhD Technology Architect _ SpinetiX S.A. Rue des Terreaux 17 1003, Lausanne, Switzerland T +41 21 341 15 50 F +41 21 311 19 56 diego.santac...@spinetix.com http://www.spinetix.com http://www.youtube.com/SpinetiXTeam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Am 10.03.2012 09:06, schrieb Kit: 2012/3/9 Christoph P.U. Kukuliesk...@kukulies.org: CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); What makes the contents of two files equal (so that their contents can be represented by the same resource) ? md5sum = md5sum My problem: what do I have to change in TABLE instance so that I can use it to determine whether the key is already in the resource TABLE? - Make new md5sum from new data INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12, Christoph P.U. Kukulies k...@kukulies.org: INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fw: Installing SQLite into SD Card of Android Mobile
Hi Following is the Email Query I have made i am on urgent need basis for getting a solution for the same. Original Query : zOn Fri, Mar 9, 2012 at 2:30 AM, Deepak Pradhan deep...@datacompwebtech.com wrote: Hi I am Deepak Pardhan from India I am Android Developer working for DATACOMP Web Technologies (I) Pvt. Ltd. I am developing application which sync data from computer to android mobile devices. Since the data size is enormous it will not support the size of Sqlite database of internal memory. I was wondering if we can install SQLITE INTO SD CARD or EXTERNAL MEMORY where the memory size is much higher. I would like to inform as uploading the data on internet server is not an option that we could take. We need to Sync data from computer to android mobile devices. We would appreciate an urgent assistance for the same. We will be happy to assist you on the public SQLite mailing list: sqlite-users@sqlite.org SQLite itself will work find on an SD Card - though there are issues with many SD card controllers lying about fsync() which can result in database corruption when removing the card. But this is a hardware issue that cannot be fixed with software. Regards Deepak Pradhan Then Second Query for which i need solution : On Fri, Mar 9, 2012 at 8:35 AM, Deepak Pradhan deep...@datacompwebtech.com wrote: Hi I highly appreciate for quick reply of the below mentioned query. I wanted to know how we can install SQLite on SDCard where it creates SQL Database engine. That sounds more like an Android question than an SQLite question. Why don't you ask on sqlite-users@sqlite.org - I'm guessing somebody there will be able to help you. And the same would help my Android Applicaion to work smoothly for database in SDCard. Regards Deepak Pradhan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Possible typo when acquiring a pending lock.
On Mon, Mar 12, 2012 at 1:23 AM, James Russell Moore
j.russell.mo...@gmail.com wrote:
Hello, I just saw what could be a possible typo when acquiring a pending
lock.
In the current version of the amalgamation (3.7.10) line 23200 there is the
following code:
The code in question is part of the OS/2 driver, which is contributed
code. Nobody on the core SQLite team has the capability of compiling or
testing SQLite on OS/2.
If any OS/2 users see this, can you please look into the matter for us?
/* Acquire a PENDING lock
*/
if( locktype==EXCLUSIVE_LOCK res == NO_ERROR ){
newLocktype = PENDING_LOCK;
gotPendingLock = 0;
OSTRACE(( LOCK %d acquire pending lock. pending lock boolean unset.\n,
pFile-h ));
}
In the surroundings of that snippet there are checks to acquire the
different kinds of locks but in all of them the lock type is tested against
what's is being acquired. Furthermore, the next test following this one to
acquire an exclusive lock has the same condition, which wouldn't
be necessary if the current code for the pending lock is correct:
/* Acquire an EXCLUSIVE lock
*/
if( locktype==EXCLUSIVE_LOCK res == NO_ERROR ){
assert( pFile-locktype=SHARED_LOCK );
res = unlockReadLock(pFile);
...
In line 33996 the same situation happens:
/* Acquire a PENDING lock
*/
if( locktype==EXCLUSIVE_LOCK res ){
newLocktype = PENDING_LOCK;
gotPendingLock = 0;
}
It could be nothing, since I don't have a deep knowledge on how SQLite is
implemented, but in the documentation in
SQLite.orghttp://sqlite.org/lockingv3.htmlthe following text
appears:
A PENDING lock means that the process holding the lock wants to write to
the database as soon as possible and is just waiting on all current SHARED
locks to clear so that it can get an EXCLUSIVE lock.
Given the name of the variables involved in those pieces of code going from
an EXCLUSIVE lock to a PENDING lock doesn't seem logical. I've attached a
patch on how I think is correct in case it is preferable (available
herehttp://pastebin.com/phEUEAiY
too).
Sorry for the trouble caused if the current state is correct.
Kind regards,
James Russell.
___
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] Possible typo when acquiring a pending lock.
On 12 Mar 2012, at 12:30pm, Richard Hipp d...@sqlite.org wrote: The code in question is part of the OS/2 driver, which is contributed code. Nobody on the core SQLite team has the capability of compiling or testing SQLite on OS/2. If any OS/2 users see this, can you please look into the matter for us? grin If any OS/2 users see this, can you post ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A possible bug probably partially fixed before
On Fri, Mar 9, 2012 at 12:04 PM, Max Vlasov max.vla...@gmail.com wrote: So looks on the way from 3.6.10 to 3.7.10 something was really fixed related to this issue, but seems like not everything. Don't know whether this is serious or a problem at all, but I spent some time with such queries and narrowed the random part to (the table still the same, auto-incremented id from 1 to 1000) Select id, (abs(random() % 1000)) as rndid from TestTable where id = rndid order by id desc limit 5 which in 3.7.10 returns results like id / rndid 44284 441134 440135 439805 438971 Looks like rndid is evaluated only once for comparison, but produces different random-originated results for output.I remember there was a discussion about it and even different versions behave differently. I think that either rndid here should contain the value evaluated for where or comparison should be performed for every row separately What also puzzles me is that another variation of the original query Select id, (abs(random() % (ToValue-FromValue + 1))) as actualrndid from TestTable left join (Select 1 as FromValue, 1000 as ToValue) StatTable where id=actualrndid order by id desc limit 10 ...always returns for left (id) column random values from 800 to 900 (no other ranges), but if I change ToValue-FromValue to 999 (no other modifications to the query), id column starts returning values from different part of 1..1000 range (the version is still the same, 3.7.10) Max ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR REPLACE
On Fri, Mar 09, 2012 at 09:49:22AM +1100, BareFeetWare wrote:
I suggest only using insert or replace if you genuinely want to delete and
replace with a new row. This is generally not hat you want if there are any
foreign keys.
Only use insert or ignore if you are inserting exactly the same row as what
might already be there.
Otherwise use a combination of insert and update.
So, for example, if you are wanting to add a person that may not already be
in your table:
insert or ignore into Person (First Name, Last Name, Company, Email)
select 'Tom', 'Brodhurst-Hill', 'BareFeetWare', 'develo...@barefeetware.com')
;
update Person
set Email = 'develo...@barefeetware.com'
where First Name = 'Tom' and Last Name = 'Brodhurst-Hill'
;
I've had a similar problem in the past, and solved it by using a pre-insert
trigger to do the desired update.
Thus, the insert/update from above becomes just an insert (or ignore),
with an implied update in the trigger, which appears to be sematically
closer to what people want in the above case (though not in the original
subject matter.)
In my case, it was inserting event data if not already existing, else
updating an existing event record from the new event data (such as incrementing
an event count and updating timestamps):
create table events (
identifier text primary key,
count integer default 1,
firstoccurrence date not null,
lastoccurrence date not null,
other fields
);
create trigger event_dedup
before insert on events
for each row
begin
update events set count=count+1,
lastoccurrence=NEW.lastoccurrence where identifier=NEW.identifier;
end;
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
2',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
pause
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
2',datetime('now'),datetime('now'));
insert or ignore into events
(identifier,firstoccurrence,lastoccurrence) values ('Some event
1',datetime('now'),datetime('now'));
select * from events;
Some event 1|4|2012-03-12 13:34:43|2012-03-12 13:34:48|...
Some event 2|2|2012-03-12 13:34:43|2012-03-12 13:34:48|...
It must be noted as well that the above select or ignore data can be
generated from a select, so in the original question, the update could
be written as:
insert or ignore into t1 ( tid, a, b ) select t1.tid, t1.a+t2.a,
t1.b+t2.b from t1 join t2 on (t1.tid=t2.tid);
So long as the update in the pre-insert trigger updates all the required
fields. The select with join is only evaluated once, so will be more
efficient if the join was the dominant performance bottleneck, and emulates
the update from select noted in oracle elsewhere in the thread.
Christian
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unique qualifier
I cannot seem to get the UNIQUE constraint to work in sqlite. The following is the trace from my command line. Note that if I remove the UNIQUE constraint, it works fine. C:\sqlite myDB.db SQLite version 3.6.17 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); SQL error: SQL logic error or missing database sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT); sqlite The examples I have seen are using this syntax, what am I doing wrong? Bryce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
Here's again the schema: CREATE TABLE instance ( path TEXT, basename TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum) ); CREATE TABLE resource ( md5sum TEXT, data BLOB, primary key(md5sum) ); INSERT OR IGNORE INTO resource ... INSERT OR REPLACE INTO instance ... Thanks a lot. What is the idea behind the INSERT OR REPLACE in your solution? Christoph If you edit a test for some version of your software, md5sum is changed too. You must replace row in `instance`. It just happened that I ran over the directory tree a second and a third time. Wouldn't the second and the third run result in being the records just replaced (since they have the same data in all columns and I don't have any uniqueness defined). But instead I have three identical entries from each run. -- Christoph ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique qualifier
On 12 March 2012 15:49, Bryce Lembke bry...@vpieng.com wrote: I cannot seem to get the UNIQUE constraint to work in sqlite. The following is the trace from my command line. Note that if I remove the UNIQUE constraint, it works fine. C:\sqlite myDB.db SQLite version 3.6.17 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); SQL error: SQL logic error or missing database sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT); sqlite The examples I have seen are using this syntax, what am I doing wrong? Bryce I have just tried your table create statement in versions 3.3.14 and 3.7.10 on Win7; in both cases no error SQLite version 3.3.14 Enter .help for instructions sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); sqlite Where does your shell come from? What environment? Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TEXT PRIMARY KEY
2012/3/12 Christoph P.U. Kukulies k...@kukulies.org: Wouldn't the second and the third run result in being the records just replaced (since they have the same data in all columns and I don't have any uniqueness defined). But instead I have three identical entries from each run. Christoph I recommend to add an attribute `version`. Version of main application, not test. Add primary key or unique. CREATE TABLE instance ( path TEXT, basename TEXT, version TEXT, size INT, md5sum TEXT, creation_date TEXT, last_write_time TEXT, FOREIGN KEY (md5sum) REFERENCES resource (md5sum), PRIMARY KEY (path,basename,version) ); You may try s/PRIMARY KEY/UNIQUE/ -- Kit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA user_version of attached database
When multiple databases are attached to the main, is there a way to get the user_version of any of the attached DBs? Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA user_version of attached database
A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be main or temp for the main and the TEMP databases. If the optional database name is omitted, main is assumed. In some pragmas, the database name is meaningless and is simply ignored. Thus, PRAGMA database.user_version; Peter From: Marc L. Allen mlal...@outsitenetworks.com To: sqlite-users@sqlite.org sqlite-users@sqlite.org Sent: Mon, March 12, 2012 10:27:54 AM Subject: [sqlite] PRAGMA user_version of attached database When multiple databases are attached to the main, is there a way to get the user_version of any of the attached DBs? Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you * * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ 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 user_version of attached database
Thanks. Sorry I missed that. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Peter Aronson Sent: Monday, March 12, 2012 1:43 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PRAGMA user_version of attached database A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be main or temp for the main and the TEMP databases. If the optional database name is omitted, main is assumed. In some pragmas, the database name is meaningless and is simply ignored. Thus, PRAGMA database.user_version; Peter From: Marc L. Allen mlal...@outsitenetworks.com To: sqlite-users@sqlite.org sqlite-users@sqlite.org Sent: Mon, March 12, 2012 10:27:54 AM Subject: [sqlite] PRAGMA user_version of attached database When multiple databases are attached to the main, is there a way to get the user_version of any of the attached DBs? Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you * * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ 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] PRAGMA user_version of attached database
Thanks. Sorry I missed that. And, yeah.. it's right there at the top. With a pretty diagram, too. I know. For some reason, I thought it would be specified with ATTACH itself. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR REPLACE
On Mon, Mar 12, 2012 at 9:02 AM, Christian Smith csm...@thewrongchristian.org.uk wrote: On Fri, Mar 09, 2012 at 09:49:22AM +1100, BareFeetWare wrote: I suggest only using insert or replace if you genuinely want to delete and replace with a new row. This is generally not hat you want if there are any foreign keys. Only use insert or ignore if you are inserting exactly the same row as what might already be there. Otherwise use a combination of insert and update. So, for example, if you are wanting to add a person that may not already be in your table: insert or ignore into Person (First Name, Last Name, Company, Email) select 'Tom', 'Brodhurst-Hill', 'BareFeetWare', 'develo...@barefeetware.com') ; update Person set Email = 'develo...@barefeetware.com' where First Name = 'Tom' and Last Name = 'Brodhurst-Hill' ; I've had a similar problem in the past, and solved it by using a pre-insert trigger to do the desired update. Ah, yes, thanks! Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique qualifier
Yes. It is odd. I am using the cmd prompt using WindowsXP. I have also tried it on a separate Windows7 machine with the same results. My shell is just doing Start-run then I type cmd. I am going to try with a different version of sqlite. Bryce Re: [sqlite] Unique qualifier Simon Davies Mon, 12 Mar 2012 09:18:58 -0700 On 12 March 2012 15:49, Bryce Lembke bry...@vpieng.com wrote: I cannot seem to get the UNIQUE constraint to work in sqlite. The following is the trace from my command line. Note that if I remove the UNIQUE constraint, it works fine. C:\sqlite myDB.db SQLite version 3.6.17 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); SQL error: SQL logic error or missing database sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT); sqlite The examples I have seen are using this syntax, what am I doing wrong? Bryce I have just tried your table create statement in versions 3.3.14 and 3.7.10 on Win7; in both cases no error SQLite version 3.3.14 Enter .help for instructions sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); sqlite Where does your shell come from? What environment? Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique qualifier
OK. I moved to 3.7.10 and it works fine now. I was using the 3.6.17 that was included with EJSCRIPT. I am wondering if there is an issue with that one, but I am good for now. Thanks. Bryce From: Bryce Lembke Sent: Monday, March 12, 2012 1:38 PM To: 'sqlite-users@sqlite.org' Subject: Re: Unique qualifier Yes. It is odd. I am using the cmd prompt using WindowsXP. I have also tried it on a separate Windows7 machine with the same results. My shell is just doing Start-run then I type cmd. I am going to try with a different version of sqlite. Bryce Re: [sqlite] Unique qualifier Simon Davies Mon, 12 Mar 2012 09:18:58 -0700 On 12 March 2012 15:49, Bryce Lembke bry...@vpieng.com wrote: I cannot seem to get the UNIQUE constraint to work in sqlite. The following is the trace from my command line. Note that if I remove the UNIQUE constraint, it works fine. C:\sqlite myDB.db SQLite version 3.6.17 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); SQL error: SQL logic error or missing database sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT); sqlite The examples I have seen are using this syntax, what am I doing wrong? Bryce I have just tried your table create statement in versions 3.3.14 and 3.7.10 on Win7; in both cases no error SQLite version 3.3.14 Enter .help for instructions sqlite CREATE TABLE employees (id INTEGER PRIMARY KEY,name TEXT UNIQUE); sqlite Where does your shell come from? What environment? Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sqlite FTS retrieve inverted index
Hello, unfortunately I have already posted this question on stackoverflowhttp://stackoverflow.com/questions/9657016/get-inverted-index-from-sqlite-fts-table, hope that this mailing list is right address. After I have implemented a full text search function in my application using Sqlite and FTS tables I would be interested in a (performant) way of retrieving the FULL inverted index (or large part) out of my FTS (sub-)table. In effect - I would need a result table including the terms, docid's and number of occurences. I am actually searching for some basic code/examples to read the segdir / segments table (where the actual index is stored ) and construct my desired result table (in effect - the inverted index). But any solution which could retrieve the full (or large part of) my inverted index using queries including MATCH, MATCHINFO , etc. (sorry, I'm no Sqlite export) would be highly appreciated! Best, mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite FTS retrieve inverted index
See http://www.sqlite.org/draft/fts3.html#fts4aux 2012/3/13 Mario Annau mario.an...@gmail.com: Hello, unfortunately I have already posted this question on stackoverflowhttp://stackoverflow.com/questions/9657016/get-inverted-index-from-sqlite-fts-table, hope that this mailing list is right address. After I have implemented a full text search function in my application using Sqlite and FTS tables I would be interested in a (performant) way of retrieving the FULL inverted index (or large part) out of my FTS (sub-)table. In effect - I would need a result table including the terms, docid's and number of occurences. I am actually searching for some basic code/examples to read the segdir / segments table (where the actual index is stored ) and construct my desired result table (in effect - the inverted index). But any solution which could retrieve the full (or large part of) my inverted index using queries including MATCH, MATCHINFO , etc. (sorry, I'm no Sqlite export) would be highly appreciated! Best, mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite FTS retrieve inverted index
Hi Alexey, tha Am 12. März 2012 22:14 schrieb Alexey Pechnikov pechni...@mobigroup.ru: See http://www.sqlite.org/draft/fts3.html#fts4aux have already tried the fts4aux table. however, I would also need the number of occurrences of each term in each document. Therefore, like in the docs, not only *-- The following query returns this data:**--**-- apple | * | 1 | 1**-- apple | 0 | 1 | 1**-- banana | * | 2 | 2**-- banana | 0 | 2 | 2**-- cherry | * | 3 | 3**-- cherry | 0 | 1 | 1**-- cherry | 1 | 2 | 2**-- date| * | 1 | 2**-- date| 0 | 1 | 2**-- elderberry | * | 1 | 2**-- elderberry | 1 | 1 | 1**-- elderberry | 1 | 1 | 1* but a result table like this: Term|col |docid| occurences -- -- apple | 0 | 1 | 1 -- banana | 0 | 2 | 1 -- cherry | 0 | 3 | 1 -- cherry | 1 | 1 | 1 -- cherry | 1 | 2 | 1 -- date| 0 | 2 | 2 -- elderberry | 0 | 3 | 1 -- elderberry | 1 | 3 | 1 Best, mario 2012/3/13 Mario Annau mario.an...@gmail.com: Hello, unfortunately I have already posted this question on stackoverflow http://stackoverflow.com/questions/9657016/get-inverted-index-from-sqlite-fts-table , hope that this mailing list is right address. After I have implemented a full text search function in my application using Sqlite and FTS tables I would be interested in a (performant) way of retrieving the FULL inverted index (or large part) out of my FTS (sub-)table. In effect - I would need a result table including the terms, docid's and number of occurences. I am actually searching for some basic code/examples to read the segdir / segments table (where the actual index is stored ) and construct my desired result table (in effect - the inverted index). But any solution which could retrieve the full (or large part of) my inverted index using queries including MATCH, MATCHINFO , etc. (sorry, I'm no Sqlite export) would be highly appreciated! Best, mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ 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] SELECT that returns the name of the fields
Hi all, I am a novice with SQLite and I wonder if there is a select that returns the names of the fields in the table? Thanks and sorry for my bad English -- Alessio Forconi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SELECT that returns the name of the fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/03/12 14:42, Alessio Forconi wrote: I am a novice with SQLite and I wonder if there is a select that returns the names of the fields in the table? You most likely want pragma table_info: http://www.sqlite.org/pragma.html#pragma_table_info If you have a SELECT then you can see what the associated names for each column of the result are by using sqlite3_column_name: http://www.sqlite.org/c3ref/column_name.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk9ebw4ACgkQmOOfHg372QT26QCg1xAarj3w+VlOC6mVJECB22tP WugAoJvHsA12DNPEtEw5I713efjRpxoB =Y8Gn -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Unique indexes apparently not working
Hello, I have a problem with a customer database that is very strange. This is part of the DB's schema: - BEGIN - CREATE TABLE IF NOT EXISTS user_identity ( id INTEGER PRIMARY KEY, shortName TEXT, domainName TEXT, lastUpdated INTEGER, ucgID INTEGER ); CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID' ON 'user_identity' (id ASC); CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON 'user_identity' (shortName ASC, ucgID ASC); -- END -- The problem is that a customer has a database where (shortName, ucgID) have duplicates. - When I try to insert manually a new record, the index is enforced; - When I try to update a column, the index is enforced; - If I try to reindex the table, it fails; - If I drop the index and try to add it again, it fails. At this point I really have no idea of what could have caused that. If anyone has seen something similar before, please share. :) Cheers, -- -alex http://www.artisancoder.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote: id INTEGER PRIMARY KEY, CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID' ON 'user_identity' (id ASC); Not directly related to your problem, but… these two clauses are redundant… a primary key is unique by definition… no point adding another unique index on top of it... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
Hallo, On Mon, Mar 12, 2012 at 11:16 PM, Petite Abeille petite.abei...@gmail.com wrote: On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote: id INTEGER PRIMARY KEY, CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByID' ON 'user_identity' (id ASC); Not directly related to your problem, but… these two clauses are redundant… a primary key is unique by definition… no point adding another unique index on top of it... You are right and I know it, this code was there before I started here. :) -- -alex http://www.artisancoder.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
On Mar 12, 2012, at 11:11 PM, Alex Queiroz wrote: The problem is that a customer has a database where (shortName, ucgID) have duplicates. Hmmm… really? That would be most peculiar... In any case, what does the following statement returns? select shortName, ucgID, count( * ) from user_identity group by shortName, ucgID having count( * ) 1 If it returns anything… are you sure you have a unique index in the first place? PRAGMA index_info( userIdentityByUcgID ) If both answers are yes, well, then, congratulation… you managed to badly confuse SQLite :D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON 'user_identity' (shortName ASC, ucgID ASC); As a general rule you should reserve single quotes to string litterals. Either leave schema names alone (no whitespace, not keyword) or use [my pretty table], my favorite table, `my non-standard unique index on (int) table, that I myself made for me only`. The problem is that a customer has a database where (shortName, ucgID) have duplicates. - When I try to insert manually a new record, the index is enforced; - When I try to update a column, the index is enforced; - If I try to reindex the table, it fails; - If I drop the index and try to add it again, it fails. What does an integrity check say? At this point I really have no idea of what could have caused that. An hex editor? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
Hello, On Mon, Mar 12, 2012 at 11:34 PM, Petite Abeille petite.abei...@gmail.com wrote: Hmmm… really? That would be most peculiar... Indeed. I have now run this: sqlite PRAGMA integrity_check; rowid 192697 missing from index userIdentityByUcgID rowid 192701 missing from index userIdentityByUcgID rowid 192705 missing from index userIdentityByUcgID rowid 192710 missing from index userIdentityByUcgID rowid 192711 missing from index userIdentityByUcgID rowid 192712 missing from index userIdentityByUcgID rowid 192716 missing from index userIdentityByUcgID ... ... ... -- -alex http://www.artisancoder.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
On 12 Mar 2012, at 10:11pm, Alex Queiroz asand...@gmail.com wrote: - If I try to reindex the table, it fails; - If I drop the index and try to add it again, it fails. For both the above ... What command are you issuing, and what result are you getting from SQLite when it fails ? (i.e. what is it doing instead of working ?) Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unique indexes apparently not working
Hello, On Mon, Mar 12, 2012 at 11:52 PM, Simon Slavin slav...@bigfraud.org wrote: On 12 Mar 2012, at 10:11pm, Alex Queiroz asand...@gmail.com wrote: - If I try to reindex the table, it fails; - If I drop the index and try to add it again, it fails. For both the above ... What command are you issuing, and what result are you getting from SQLite when it fails ? (i.e. what is it doing instead of working ?) sqlite reindex 'user_identity'; Error: indexed columns are not unique sqlite drop index userIdentityByUcgID; sqlite CREATE UNIQUE INDEX IF NOT EXISTS 'userIdentityByUcgID'ON 'user_identity' (shortName ASC, ucgID ASC); Error: indexed columns are not unique Cheers, -- -alex http://www.artisancoder.com/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] import a text to sqlite
here my code,where is wrong? char mma[250]; strcpy(mma,sqlite3 foods.db \ .separator ',' \ \.import dzhhkmysql.txt dzh\); system(mma); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import a text to sqlite
On 13 Mar 2012, at 2:28am, YAN HONG YE yanhong...@mpsa.com wrote: here my code,where is wrong? char mma[250]; strcpy(mma,sqlite3 foods.db \ .separator ',' \ \.import dzhhkmysql.txt dzh\); system(mma); ___ Instead of using system(), write that text to a file. Then see what the file looks like. Then type the file into the computer yourself and watch what happens. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] where wrong
C:\sqlite\libsqlite3 foods.db .separator ',' .import dzhhkmysql.txt dzh sqlite3: Error: too many options: .import dzhhkmysql.txt dzh Use -help for a list of options. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] where wrong
On 13 Mar 2012, at 2:42am, YAN HONG YE yanhong...@mpsa.com wrote: C:\sqlite\libsqlite3 foods.db .separator ',' .import dzhhkmysql.txt dzh sqlite3: Error: too many options: .import dzhhkmysql.txt dzh Use -help for a list of options. You cannot just type lots of commands on the line you use to start the sqlite3 program. And I think you are using the wrong quotes. Please take a look at the end of this page for some good examples: http://www.sqlite.org/sqlite.html If you need this just to run once, just type the commands yourself. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR REPLACE
On 12.03.2012 16:02, Christian Smith wrote: I've had a similar problem in the past, and solved it by using a pre-insert trigger to do the desired update. Thus, the insert/update from above becomes just an insert (or ignore), with an implied update in the trigger, which appears to be sematically closer to what people want in the above case (though not in the original subject matter.) Thank you Christian! Your insightful advice led me to the following (slightly more natural) variation (with the OP's sample): create view t1_inc as select t1.rowid, t1.a, t2.b, t1.a + t2.b a_next, t1.b + t2.b b_next from t2 inner join t1 on t1.id = t2.id ; create trigger t1_inc_apply instead of update on t1_inc begin update t1 set a = NEW.a_next, b = NEW.b_next where rowid = NEW.rowid ; end ; update t1_inc set a = a_next, b = b_next ; The only visible downside is, that in both variants (your original/the above), generated VDBE code contains OpenEphemeral and (AFAICT) temporary record for every row in the join, but maybe someone knows variation of the trigger based approach which avoids this ... ? Kind regards, Alek ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
