Re: [sqlite] SQLite server
Wednesday, December 22, 2010, 1:19:25 AM, you wrote: SS> On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote: >> Implementing an SQLite-based server does not obviously enable this in and of >> itself. If you could open a database on a remote machine using its filename >> as the OP was trying to do it would enable this, but we got into this >> discussion when it was discovered that doing so was a bad idea. SS> This was my first thought when I considered implementing an SS> SQLite-over-IP protocol: that there was no user model and SS> therefore no need for passwords. Mounting a database on the SS> server would mean that anyone who knew your IP address, port SS> number and database name could access the data. SS> So my conclusion was that you would need to implement, in the SS> first case, a simple user privilege model, nominating which users SS> (or which IP addresses) could access which databases. SS> Just to throw into the pot: SS> It's not that hard to do an extremely simple version of this SS> using HTTP and either XML or JSON. Set up a web server with some SS> PHP or Python pages which take the following parameters: SS> databasename SS> command SS> You call up the right web page with the right values for the SS> parameters, either as a GET or a PUT. One web page just executes SS> the command and returns a result code, result message, etc., in SS> either XML or JASON, depending on what the programmer likes best. SS> A different web page is for executing SELECTs (or some PRAGMAs) SS> and returns the above plus a table of results. If you are considering heading down this track I'd suggest seriously considering using a REST API. I've been doing an evaluation of various NoSQL Databases recently, mainly with CouchDB and it uses REST quite effectively. And stick with JSON. Emulating the WebSQL API may be another option. This is used by WebKit/Chrome etc. to access SQLite. And another option could be to use the MySQL tcp/ip interface which I assume is well documented. I mention this because I am doing some work with Node.js which has a MySQL interface and am using XMLHTTPRequest (Ajax) in Javascript in the Browser to access the MySQL DB on the Web Server via. Node.js. --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bundling sqlite database together with exe file.
I'd also suggest option 3. And make sure you extract the file to a folder that the user has permission to access. Monday, October 25, 2010, 1:17:13 AM, you wrote: KN> On Sun, 24 Oct 2010 20:57:15 +0800, Mohd Radzi Ibrahim KN> <mra...@pc.jaring.my> wrote: >> Hi, >> I am planning to deploy my sqlite database together >> with the exe file. Is there a way to open the sqlite >> database with a file handle and starting offset of >> the file, as read-only? >> >> Thank you for any suggestion. KN> Not out of the box. KN> I can think of three solutions: KN> 1- Difficult: change the sqlite3 library in such a way so it KN> uses the whole executable as the "first page" of the KN> database KN> 2- Easier: Store the output of the .dump command of the KN> sqlite3 shell in a 'resource' in the executable and on KN> execution of your program read the resource and load it in KN> an in-memory database (filename ":memory:" ) by calling KN> sqlite3_exec() on each of its statements. KN> 3- Easier: Store the database file as-is in a resource in KN> the executable and on execution of your program write it to KN> disk and open it in the normal way. KN> HTH -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "Using SQLite" - O'Reilly Deal of the Day
Wednesday, August 25, 2010, 9:37:17 AM, you wrote: GS> On 8/24/2010 8:09 AM, Jay A. Kreibich wrote: >> >>"Using SQLite" has gone to press! To celebrate, "Using SQLite" is >>is today's O'Reilly "Ebook Deal of the Day." GS> And quite a deal it is! Thank you for posting the announcement, Jay. GS> I had just been wondering what book to start reading on my BlackBerry, GS> and now I know. GS> Thanks again, GS> Gerry GS> PS Quick review: The index looks pretty decent. :) I somehow missed Jay's post, but just bought my copy - thanks. Index :) --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Berkeley DB adds SQL using SQLite API !!
Thursday, April 1, 2010, 12:16:13 PM, you wrote: JJD> On Wed, Mar 31, 2010 at 8:50 AM, Wiktor Adamski JJD> <bardzotajneko...@interia.pl> wrote: >>> There were many problems with >>> that approach: >> ... >>> (3) Each table and index is in a >>> separate file so your "database" was a directory full of files instead >>> of a single file >> >> This one is not a problem. Actually I don't see how 1 file is better >> than 1 directory. For example mac application is a directory not a >> file and no one complains. And with several files database would be >> faster (for example dropping a table is instant or fragmentation is >> handled by OS without need for vacuuming whole database). Also with >> current SQLite implementation only tables would be locked by a >> transation not a whole database (a few years ago there were even >> document on SQLite website listing splittnig database to several >> files as one way to implement table level locks in SQLite). >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> JJD> Two reasons I prefer the single file approach: JJD> 1. Simpler copy, tables and indexes don't get lost or mismatched. JJD> 2. fewer handles to open a database. Lower overhead. JJD> This still is a small footprint, high-performance, low overhead SQL JJD> implementation. It does what it needs to do and no more. Also from the "end user" perspective it is so much easier for them to backup or copy a single file. --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Password protection?
Saturday, March 6, 2010, 2:57:59 PM, you wrote: J> Hello! J> Does SQLite allow for password protecting a database? How about J> tables, can they be individually password protected? No, you would need to do this in your application. A password alone would be very weak as anyone can look at an SQLIte database with a host of browser apps or the SQLite command line tool. There is an extension you can purchase that does database encryption if you want real protection. --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite compile error
Hi Gary, It sounds like there is no application code containing either main() or standard Windows app startup code. SQLite is just a library and needs to be linked with your application or test code. You also need to provide some specific information on the compiler you are using. Saturday, March 6, 2010, 6:18:18 AM, you wrote: GZ> Good Afternoon, GZ> Today I downloaded sqlite-amalgamation-3_6_22.zip from the GZ> sqlite.org website because I was looking for a SQL database to GZ> replace MS Access on my machine. I compiled sqlite3.c using a c GZ> compiler and came up with this error: GZ> [Linker error] undefined reference to `winm...@16' GZ> Can you help me? GZ> Thanks, >> Gary Zigmann, MBA >> Clinical Data Analyst >> Gifford Medical Center >> gzigm...@giffordmed.org --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Windows] Good GUI alternative to sqlite.exe?
Gilles, The best I have found is SQLite Expert http://www.sqliteexpert.com/ The Personal version is free. And I have checked out many. Friday, October 23, 2009, 8:45:18 PM, you wrote: GG> Hello GG> I'm looking for a Windows alternative to the CLI sqlite.exe to manage GG> SQLite databases. GG> SQLiteSpy (www.yunqa.de) is OK, but unless I missed the option, it GG> won't let me copy the output of a SELECT into the clipboard so I can GG> paste it elsewhere. GG> Are there better alternatives? GG> Thank you. --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ANN: SQLite 3.6.16.C#
Hi Noah, A name suggestion: "SharpLightSQL" - SLSQL or just SLS. Sunday, August 2, 2009, 10:57:56 PM, you wrote: NH> Richard sent me a gentle reminder that read in part: NH> NH> Please also note that the SQLite source code is in the public domain, but NH> the "SQLite" name is not. SQLite is a registered trade mark. If I don't NH> defend the trademark, then I could lose it. So, I really do need to insist NH> that you not use the name "SQLite" for your product. NH> NH> This is an excellent reminder, and until this is done, I've removed access NH> to the source code and will terminate this google code project. I'll post NH> an announcement in the future when the new project is ready. NH> Also, if anyone has an ideal about what to call it ... NH> Regards, --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The SQL Guide to SQLite
Rick, Changing the conversation a little I would be interested to know your opinion of this book, as I'm sure would others. Many were disappointed with "The Definitive Guide to SQLite". Sunday, July 19, 2009, 11:56:05 AM, you wrote: RR> Okay. We're talking two different things here. RR> One states "academic papers" and you state "technical documents". RR> This is a "book", not an "academic paper or technical document". RR> I'm all for Names and Dates. I'm quite familiar with (Williams and Jones RR> 1981) and other such references. They appear in most of the books I possess. RR> However, bracketed references such as [SMI01] do not. First time in my 50 RR> years I've come across this. RR> Are we assuming that everyone who buys this book attended University? RR> Another thing I'm familiar with are TAGS in documents. These looked like RR> TAGS to me. I immediately assumed the TAGS weren't replaced with the actual RR> material. RR> Anyway, I think enough has been said on this. One should never ASSUME that a RR> convention is understood by ALL readers. Apparently, it is not. RR> Best regards, RR> Rich RR> #>>-Original Message- #>>From: sqlite-users-boun...@sqlite.org #>>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard #>>Sent: Saturday, July 18, 2009 8:00 PM #>>To: General Discussion of SQLite Database #>>Subject: Re: [sqlite] The SQL Guide to SQLite #>> #>>On Sat, 18 Jul 2009, Rick Ratchford wrote: #>> #>>> All I know is that this is a book. I have a vast library of #>>technical #>>> books and this is the ONLY one that uses this convention. #>>Even my copy #>>> of "A New Kind of Science" by Wolfram doesn't use this #>>convention. :-b #>> #>> There are many conventions for citations in books, #>>reports, articles, and other documents that cite original #>>sources. When I was in academia, the ecological literature #>>(books, papers, etc.) used a (name date) format; e.g., (Smith #>>1962), or (Williams and Jones 1981), or (Foobar et al. 1954). #>>The bibliography or reference section (and there is a #>>difference between those #>>two) was arranged in alphabetic order. Many other technical #>>books (including #>>mine) use a numeric citation, e.g., [20], and the #>>bibliography is numeric rather than alphabetic. Still other #>>technical documents use the author abreviation plus two-digit #>>year system which is what you apparently encountered; e.g., #>>[ORA92] or [SMI01]. They are all common. #>> #>> Personally, I like the author/year system because it's #>>explicit and easy to comprehend without requiring looking at #>>the references section. #>>Regardless, it's up to the publisher, country, or the #>>practice of a particular discipline which one is used. #>> #>> It's unfortunate that you had such difficulty figuring out #>>the citation system. #>> #>>Rich #>> #>>-- #>>Richard B. Shepard, Ph.D. | Integrity #>> Credibility #>>Applied Ecosystem Services, Inc.|Innovation #>><http://www.appl-ecosys.com> Voice: 503-667-4517 #>>Fax: 503-667-8863 #>>___ #>>sqlite-users mailing list #>>sqlite-users@sqlite.org #>>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users #>> #>> RR> ___ RR> sqlite-users mailing list RR> sqlite-users@sqlite.org RR> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Announce of the new "Versioning" extension
Hi Alexey,
Thank you for the license change and readme.
Wednesday, July 15, 2009, 7:16:44 PM, you wrote:
AP> Hello!
AP> On Wednesday 15 July 2009 09:56:28 Neville Franks wrote:
>> Hi Alexey,
>> Thank you for this extension which could be quite interesting to many
>> SQLite users. Is there any documentation on this available, possibly
>> in your new book? I couldn't find any with the source at
>> http://mobigroup.ru/files/sqlite-ext/
AP> I did add README file.
AP>
>> Also you have used the GNU License which means we cannot use this in
>> any commercial applications. It also goes against the Public Domain
>> license used by SQLite itself - see
>> http://www.sqlite.org/copyright.html It would be great if this could
>> be changed.
AP> I did change license to Public Domain same as SQLite core and my other
extensions.
AP> P.S. Added indexes in new "Versioning" version.
AP> README =
AP> "Versioning" SQLite extension
AP> Copyright 2009 Alexey Pechnikov <pechni...@mobigroup.ru>
AP> The code is public domain.
AP> The extension may be used for table versioning and replication.
AP> Functions:
AP> versioning_table(SOURCE) - add versioning support for SOURCE table by
AP> 1. drop if exists previously created _versioning_SOURCE table
AP> 2. add _versioning_SOURCE table to store versions
AP> 4. copy current state of SOURCE table
AP> 3. create triggers on SOURCE table
AP> unversioning_table(SOURCE) - remove versioning support for
AP> SOURCE table. Doesn't drop _versioning_SOURCE table but only remove SOURCE
triggers!
AP> The _versioning_SOURCE table consists all fields of SOURCE table
AP> without any checks or constraints and some additional fields
AP> _date REAL, _action TEXT, _rowid INTEGER
AP> to store date of perform action on SOURCE row, action name ('I' -
AP> insert, 'U' - update, 'D' - delete) and original record rowid.
AP>
AP> Add versioning example:
AP> CREATE TABLE key (name text not null);
AP> select versioning_table('key');
AP> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_key(name text, _date REAL, _action TEXT, _rowid
INTEGER);
AP> CREATE TABLE key (name text not null);
AP> CREATE INDEX versioning_key_date_idx on _versioning_key(_date);
AP> CREATE INDEX versioning_key_rowid_date_idx on _versioning_key(_rowid,_date);
AP> CREATE TRIGGER _versioning_key_d AFTER DELETE ON key BEGIN INSERT
AP> INTO _versioning_key (_date, _action, _rowid) values
AP> (julianday('now'), 'D', old.rowid);END;
AP> CREATE TRIGGER _versioning_key_i AFTER INSERT ON key BEGIN INSERT
AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'I' as
AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END;
AP> CREATE TRIGGER _versioning_key_u AFTER UPDATE ON key BEGIN INSERT
AP> INTO _versioning_key SELECT *, julianday('now') as _date, 'U' as
AP> _action, new.rowid as _rowid FROM key WHERE rowid=new.rowid;END;
AP>
AP> Versioning example:
AP> insert into key (name) values ('test key 1');
AP> insert into key (name) values ('test key 1');
AP> delete from key;
AP> .header on
AP> select * from _versioning_key;
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> test key 1|2455027.87582772|I|2
AP> |2455027.87709961|D|1
AP> |2455027.87709961|D|2
AP>
AP> Now you can select versions of SOURCE row by rowid:
AP> .header on
AP> select * from _versioning_key where _rowid=1;
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> |2455027.87709961|D|1
AP>
AP> For replication can be selected versions of all records by
AP> current time which were changed after previous syncronization (1 hour ago,
as example):
AP> .header on
AP> select * from _versioning_key where _date>julianday('now','-1 hour');
AP> name|_date|_action|_rowid
AP> test key 1|2455027.87582762|I|1
AP> test key 1|2455027.87582772|I|2
AP> |2455027.87709961|D|1
AP> |2455027.87709961|D|2
AP> These records may be synced by sql dump or by other ways.
AP> Best regards, Alexey Pechnikov.
AP> http://pechnikov.tel/
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Announce of the new "Versioning" extension
Hi Alexey,
Thank you for this extension which could be quite interesting to many
SQLite users. Is there any documentation on this available, possibly
in your new book? I couldn't find any with the source at
http://mobigroup.ru/files/sqlite-ext/
Also you have used the GNU License which means we cannot use this in
any commercial applications. It also goes against the Public Domain
license used by SQLite itself - see
http://www.sqlite.org/copyright.html It would be great if this could
be changed.
Tuesday, July 14, 2009, 10:17:18 PM, you wrote:
AP> Hello!
AP> This may be used for table versioning and replication.
AP> Source code is available here
AP> http://mobigroup.ru/files/sqlite-ext/
AP> You can get from the debian repository the SQLite build with some extra
extensions:
AP> deb http://mobigroup.ru/debian/ lenny main contrib non-free
AP> deb-src http://mobigroup.ru/debian/ lenny main contrib non-free
AP> =Test script==
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
AP> insert into sessions (key,value) values ('test key 0','test value 0');
AP> select versioning_table('sessions');
AP> .schema
AP> select * from _versioning_sessions;
AP> insert into sessions (key,value) values ('test key 1','test value 1');
AP> insert into sessions (key,value) values ('test key 2','test value 2');
AP> select * from _versioning_sessions;
AP> delete from sessions;
AP> select * from _versioning_sessions;
AP> select unversioning_table('sessions');
AP> .schema
AP> ==Test script results=
sqlite>> CREATE TABLE sessions (
AP>...> key text not null,
AP>...> value text not null
AP>...> );
sqlite>> insert into sessions (key,value) values ('test key 0','test value 0');
sqlite>>
sqlite>> select versioning_table('sessions');
sqlite>> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_sessions(key text,value text, _date
AP> REAL, _action TEXT, _rowid INTEGER);
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
AP> CREATE TRIGGER _versioning_sessions_d AFTER DELETE ON sessions
AP> BEGIN INSERT INTO _versioning_sessions (_date, _action, _rowid)
AP> values (julianday('now'), 'D', old.rowid);END;
AP> CREATE TRIGGER _versioning_sessions_i AFTER INSERT ON sessions
AP> BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now')
AP> as _date, 'I' as _action, new.rowid as _rowid FROM sessions WHERE
rowid=new.rowid;END;
AP> CREATE TRIGGER _versioning_sessions_u AFTER UPDATE ON sessions
AP> BEGIN INSERT INTO _versioning_sessions SELECT *, julianday('now')
AP> as _date, 'U' as _action, new.rowid as _rowid FROM sessions WHERE
rowid=new.rowid;END;
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
sqlite>>
sqlite>> insert into sessions (key,value) values ('test key 1','test value 1');
sqlite>> insert into sessions (key,value) values ('test key 2','test value 2');
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
sqlite>>
sqlite>> delete from sessions;
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
AP> ||2455027.00753382|D|1
AP> ||2455027.00753382|D|2
AP> ||2455027.00753382|D|3
sqlite>>
sqlite>> select unversioning_table('sessions');
sqlite>> .schema
AP> CREATE TABLE _undo(sql TEXT, status TEXT);
AP> CREATE TABLE _versioning_sessions(key text,value text, _date
AP> REAL, _action TEXT, _rowid INTEGER);
AP> CREATE TABLE sessions (
AP> key text not null,
AP> value text not null
AP> );
sqlite>> select * from _versioning_sessions;
AP> test key 0|test value 0|2455027.00753221|I|1
AP> test key 1|test value 1|2455027.00753347|I|2
AP> test key 2|test value 2|2455027.00753368|I|3
AP> ||2455027.00753382|D|1
AP> ||2455027.00753382|D|2
AP> ||2455027.00753382|D|3
AP> ==
AP> Best regards, Alexey Pechnikov.
AP> http://pechnikov.tel/
AP> ___
AP> sqlite-users mailing list
AP> sqlite-users@sqlite.org
AP> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Repost: Accessing a DB while copying it causes Windows to eat virtual memory
A proper backup program that opened the file for r/o, non-exclusive use may be able to copy the file. I thought SQLite has a live backup capability now. Surely that is the best way to handle this. Wednesday, July 8, 2009, 6:16:41 PM, you wrote: JS> It looks like you are trying to copy from a process other than the one JS> which holds the lock. JS> Think abnout it - the lock gives exlusive access to the file to the JS> process which sets it and blocks all other processes. JS> Stan Bielski wrote: >> Just to make sure I understood you correctly, is this what you are >> suggesting? >> >> * Open file with sqlite app (in my case sqlite3) >> * execute BEGIN EXCLUSIVE TRANSACTION; >> * initiate the file copy >> * COMMIT; after the copy is finished. >> >> I tried doing this, but very early into the copy Windows issues the >> following error: >> >> Errror 0x80070021: The process cannot access the file because another >> process has locked a portion of the file. >> >> >> On Tue, Jul 7, 2009 at 1:03 PM, John Stanton<jo...@viacognis.com> wrote: >> >>> You should synchronize your backup (copy). Try surrounding it with an >>> exclusive transaction. >>> >>> Stan Bielski wrote: >>> >>>> Sorry for the repost, but the original thread was hijacked by another >>>> list user. This is a serious problem IMHO; it looks like the DB can't >>>> be backed-up without rendering the machine unusable if a query hits it >>>> while a copy is in progress. >>>> >>>> Hello, >>>> >>>> In the course of copying a largish (20 GB) database file while >>>> accessing it via sqlite3, the machine became very unresponsive. I >>>> opened task manager and found that the system was using a huge amount >>>> of virtual memory, causing it to thrash. Per-process memory usage >>>> looked normal and did not add up to anywhere near system-wide VM >>>> usage. >>>> >>>> I ran into this issue at a customer site and was able to reproduce it >>>> using a local Windows 2008 installation. I have not installed any >>>> backup software or a virus scanner. Storage is local disk, SQLite >>>> version is 3.3.17. >>>> >>>> At first I thought that this was a general Windows problem involving a >>>> process accessing a file that is being copied, but other binaries I >>>> tested do not cause the same behavior that sqlite3 does. I performed >>>> the following experiments to try to diagnose the issue. >>>> >>>> Case 1: >>>> >>>> * I copy a 20 GB sqlite DB using Windows' own copy utility (e.g. via >>>> explorer). >>>> * At any point during the copy, I open the file being copied in sqlite3 >>>> * I exit sqlite3. >>>> * During the rest of the copy the OS will consume virtual memory >>>> linear (seemingly identical) to the amount of data copied since the >>>> process opened the file. >>>> >>>> I repeated this experiment using a similarly-sized file created from >>>> /dev/zero (i.e. an invalid DB) and the results were the same. >>>> >>>> Case 2: >>>> >>>> * I copy the sqlite DB using Windows' own copy utility (e.g. via explorer). >>>> * At any point during the copy, I run 'strings' with the file as an >>>> argument. >>>> * I exit strings. >>>> * The copy does not result in the OS consuming additional virtual memory. >>>> >>>> Case 3: >>>> >>>> * I open the DB in sqlite3 >>>> * I let sqlite3 idle and do not input any commands. >>>> * I copy a sqlite DB using Windows' own copy utility (e.g. via explorer). >>>> * I continue to let sqlite3 idle and do not input any commands. >>>> * During the rest of the copy the OS will consume virtual memory >>>> linear (seemingly identical) to the amount of data copied since the >>>> process opened the file. >>>> >>>> Is there a workaround for this issue? Any assistance or info is >>>> appreciated. >>>> >>>> Thanks, >>>> -Stan -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing sqlite using javascript
Monday, May 18, 2009, 3:16:45 PM, you wrote: SP> I had asked the same question a few days back,but need a some more help. SP> I am having my whole SQLite database on client's machine.I want a SP> way to access that db using javscript.I heard about gears,but the SP> problem is my target PC has firefox 1.08 and gears is for 1.5+.So SP> is there any other way around?...Also is there any tutorial or SP> guide which i can have online for referring when i am writing the code?. SP> Thank you. There are several ways to do this. From Firefox see: http://codesnippets.joyent.com/posts/show/1030 The ExtJS Library also provides access to SQLite, but I have not used that part of ExtJS yet. Finally Google: "sqlite from javascript". --- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you guys use sqlite from C++?
I use a modified version of the C++ wrapper http://www.codeproject.com/KB/database/CppSQLite.aspx Monday, April 27, 2009, 8:35:43 PM, you wrote: V> I've made my own wrapper class around sqlite for executing V> database commands. Its completely generic and supports the use of V> binds and parameter substitution through the use of variable V> arguments () as well as a printf-style format string that V> clues the routine into the types of the arguments. For example: Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert speeds slowing down as database size increases (newb)
The most common reason which comes up here time and again is that the inserts are wrapped in a transaction. See BEGIN, END statements in the Docs. You haven't mentioned whether you are using a transaction, so I may be misguided in my reply. But the sample code doesn't! Wednesday, October 29, 2008, 7:59:54 PM, you wrote: JB> Hi everyone, JB> First off, I'm a database and sqlite newbie. I'm inserting many many JB> records and indexing over one of the double attributes. I am seeing JB> my insert times slowly degrade as the database grows in size until JB> it's unacceptable - less than 1 write per millisecond (other databases JB> have scaled well). I'm using a intel core 2 duo with 2 GB of ram and JB> an ordinary HDD. JB> ... -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Update while Stepping through Select results
Saturday, September 6, 2008, 10:03:00 AM, you wrote: IT> "Neville Franks" <[EMAIL PROTECTED]> wrote IT> in message news:[EMAIL PROTECTED] >> At present the clm data is a list of one or more numeric id's >> separated by either a space or comma. I need to update this so the >> id's are always comma separated. IT> update tableName set field=replace(field, ' ', ','); Thanks Igor. Unfortunately I oversimplified my explanation, and the processing that is required is somewhat more complex. However this opened my eyes to the possibility of writing a user defined function to use with update. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Update while Stepping through Select results
Hi Igor, Saturday, September 6, 2008, 8:27:01 AM, you wrote: IT> Neville Franks <[EMAIL PROTECTED]> wrote: >> I need to update a column in a set of rows in a table. For each row I >> need to extract the columns value, change it and update the row. >> >> My question is, is it valid to perform an SQL UPDATE inside a >> sqlite3_step() loop. IT> Yes. Thanks. >> Finally is there a better way to accomplish. IT> What is the nature of the change you need to perform? At present the clm data is a list of one or more numeric id's separated by either a space or comma. I need to update this so the id's are always comma separated. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Update while Stepping through Select results
I need to update a column in a set of rows in a table. For each row I need to extract the columns value, change it and update the row. My question is, is it valid to perform an SQL UPDATE inside a sqlite3_step() loop. Or put another way will subsequent calls to sqlite3_step() behave correctly following an UPDATE. I assume so as this is a fairly basic operation. I have done a quick documentation search to no avail. Finally is there a better way to accomplish. Maybe an UPDATE with a TRIGGER using a user defined function. Thanks. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Hi John, Re. Javascript being slow you may be interested in EJScript which is an Embedded Javascript implementation with a Native Code Compiler. See: http://www.ejscript.org/products/ejs/doc/guide/ejs/language/overview.html and http://www.ejscript.org I have not (yet) used it so can't comment further. I also use JSON with SQLite and have C/C++ code to parse and build a tree (DOM) from JSON. Along with some simple JSON Path style lookup. And re. Sockets and doubles. I've written a layer that basically sends/receives variants using sockets. If you are familiar with Boost::Any it is like that, however I use different implementation. Wednesday, June 11, 2008, 10:48:31 AM, you wrote: JS> We use an application server I wrote which handles HTTP, serves file and JS> has embedded Sqlite for the RPCs. The RPC can deliver its result either JS> in XML for widely distributed applications or as JSON if it is JS> responding to a WWW browser in AJAX mode. JS> We keep a local library of SQL RPCs so that SQl never appears on the JS> network and we have immunity from injection attacks. It also means that JS> we can cache compiled SQL, a useful performance win. JS> We use the Expat parser in remote programs using the XML format. A JS> wrapper makes it a verifying parser to ensure well formed XML. JS> The server is multi threaded and maintains a pool of live threads so it JS> can respond quickly and assign multiple threads to one browser JS> connection. Shared cache in Sqlite and some extra caching to maintain JS> multiple open databases and results makes Sqlite behave like a simple to JS> use enterprise DB server, but without the overhead of extra processes. JS> We use mutexes for synchronization, set up as read and write locks and JS> avoid the POSIX file locks. JS> We installed Javascript as a procedural language to be used by Sqlite JS> instead of PL/SQL but that is not a great success (v. slow) and we are JS> going to experiment with using Python. JS> Based on our experience you should be very happy with your Sqlite based JS> RPC capability. JS> Alex Katebi wrote: >> John & John, >> >>Actually my API used to be XML using SCEW a DOM like XML parser that uses >> Expat. >> >>For my particular application RPC made more sense to me. What could be >> easier than a function call? Another advantage was that I did not have to >> create any functions. I am just using SQLite's C API. Now the users of my >> application can query any table on the server side using select. Since my >> application is a network server, and network debugging capability is >> crucial. >> The only ugliness is that select locks the tables. I wish D. Hipp would give >> us an option for pStmt to create a temporary table of the select result set >> and delete that temp table after finalize automatically. This way a client >> can sit on a prepare/step for a long time. >> >>I solved the endian issue pretty easy by sending the type code. >> >> Thanks, >> -Alex >> >> >> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> >> wrote: >> >>> Alex Katebi wrote: >>>> Yes I need to do it as 8 byte buffer. Convert the endianess to the >>> network >>>> then back to host for 8 byte integer. >>>> I think XML is great for command validation and CLI auto typing, help >>> etc. >>>> Besides parsing issue, XML can not handle binary data directly. >>>> >>> As John pointed out, XML is not intended to handle binary data >>> directly. We use XML as a transfer medium for binary data and simply >>> base64 encode it before encapsulation. >>> >>> >>> John Elrick >>> Fenestra Technologies >>> ___ >>> 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 JS> ___ JS> sqlite-users mailing list JS> sqlite-users@sqlite.org JS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite overall or not?
Wednesday, June 11, 2008, 12:12:33 AM, you wrote:
p> Hello. I really need help with this, because Im almost out of
p> ideas here. Help please!
p> sqldata is a vector< vector < char* > >
p> row is a vector < char* >
p> sqldata sql3wrapper::pobierzPodmiot(){
p> const char* tail;
p> sqlite3_stmt* statement;
p> std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
p>
p> sqlite3_prepare_v2(interface,query.c_str(),-1,,);
p> sqldata data;
p> for(int a=0;sqlite3_step(statement)!=101;a++){
p> row set;
p> data.push_back(set);
p> for(int az=1; az<sqlite3_column_count(statement); az++){
p>
p> data[a].push_back((char*)sqlite3_column_text(statement,az));
p> }
p> std::cout<<(data[a])[2]<<std::endl;
p> }
p> return data;
p> sqlite3_finalize(statement);
p> }
p> this does cout properly. But when I'm using it in a different place:
Try moving:
sqlite3_finalize(statement);
return data;
I would also populate set and then push that to data. And I would use
one of the C++ wrappers seeing that you are using C++.
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparison of SQLite applications for Mac
Hi Hartwig, The last release for this was Apr 2005 so it looks like it has died. I'm also working on a mini-review of SQLite GUI DB Managers for Windows. I'll post to the list when it is ready. Thursday, May 8, 2008, 4:45:09 PM, you wrote: HW> Hi Tom, HW> SQLite Database Browser (sqlitebrowser.sourceforge.net) seems to be HW> missing. HW> Hartwig HW> Am 07.05.2008 um 06:20 schrieb BareFeet: >> Dennis Cote wrote: >> >>>> 2. Know of another application that should be included. >>>> >> >>> You may want to include the free SQLite Manager add on for Firefox. >>> See >>> https://addons.mozilla.org/en-US/firefox/addon/5817 for additional >>> information. >>> >>> It provides a general database browser and editor that works on Mac >>> OS as well. >> >> Thanks for the pointer, Dennis. I've added the SQLite Manager for >> Firefox to my review matrix of SQLite GUI software at: >> http://www.tandb.com.au/sqlite/compare/?mlp >> >> If anyone else knows of another program worth adding to the mix, >> please let me know. >> >> Please let me know of any corrections to what's there or any stand out >> features in your favorite program that you think are worth comparing. >> >> Thanks, >> Tom >> BareFeet >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> HW> ___ HW> sqlite-users mailing list HW> sqlite-users@sqlite.org HW> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Populating and scrolling the Listbox using query
Tuesday, April 22, 2008, 2:52:37 PM, you wrote: Why not just keep the rowid's in an array and query the actual row contents as needed. This is typical with virtual list or tree controls. That is you keep a reference to the data, not the data itself. F> Thanks for the quick reply Epankoke. F> We tried as you mentioned. But we are restricted with the memory size and F> the storage of the needed data occupies some MB's of space in the memory so F> we cannot go for it. Is there any other work around to perform the scrolling F> condition. F> Kindly help in this regard. F> Regards, F> Farzana. F> epankoke wrote: >> >> Is it possible to store all of the needed data in memory? If so, why not >> read the required information into an array and just update an index >> variable to keep track of where you are at in the array when the user >> clicks the up and down buttons? That should be quite fast. >> >> -- >> Eric Pankoke >> Founder / Lead Developer >> Point Of Light Software >> http://www.polsoftware.com/ >> >> -- Original message -- >> From: Farzana <[EMAIL PROTECTED]> >>> >>> Thanks for your reply Igor. >>> >>> We tried populating the listbox as mentioned in the URL.We are successful >>> in >>> populating the listbox but when we go for scrolling the data, it takes >>> more >>> time to move forward and backward since it has to execute the query >>> everytime. We are using a PocketPc so it is much slower. Is there any >>> other >>> way to do this or can some one provide us a sample code for the same. >>> We are using a Table say Employees where we have to dsiplay their Job >>> Description in ascending order in a user defined listbox with scroll up >>> and >>> scroll down buttons. Can anyone provide us a suggestion. >>> Thanks in Advance. >>> >>> Regards, >>> Farzana >>> >>> >>> >>> Igor Tandetnik wrote: >>> > >>> > "Farzana" <[EMAIL PROTECTED]> >>> > wrote in message news:[EMAIL PROTECTED] >>> >> We are working in eVC++ environment with SQLite database.We need to >>> >> populate the listbox with the values obtained by executing the query. >>> >> We were able to get the values of the query by using the API's >>> >> sqlite3_prepare and sqlite3_step. >>> >> But we were able to populate and move the listbox in the downward >>> >> direction only >>> > >>> > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor >>> > >>> > Igor Tandetnik >>> > >>> > >>> > >>> > ___ >>> > sqlite-users mailing list >>> > sqlite-users@sqlite.org >>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> > >>> > >>> >>> -- >>> View this message in context: >>> http://www.nabble.com/Populating-and-scrolling-the-Listbox-using-query-tp1667617 >>> 8p16806114.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> ___ >>> 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 >> >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete inside Select+Step
Monday, April 21, 2008, 10:44:15 AM, you wrote: >> I actually need to call a function for each iteration of >> sqlite3_step() and depending on what it returns, delete the row or >> not. B> Perhaps you could include your function in an example. Well it is a function that calls various other functions which call various other functions, none of which know anything about, nor do anything with the SQLite DB. So I don't see it is relevant. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Delete inside Select+Step
Use the latest SQLite Version can you do:
select clma from tablea;
while( sqlite3_step() )
{
if ( clma == somevalue )
delete from tablea clma=somevalue;
else
process row;
}
ie. Delete a row while stepping through the results of a select and
know the remaining sqlite3_step()'s will work correctly?
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Quick Review/Reference
The book is sitting on a server in China and I would have to assume it is an illegal copy. Not something folks here should be using. Tuesday, April 15, 2008, 7:58:03 PM, you wrote: m> is this what everybody is looking for?? m> http://booksforpeople.blogspot.com/2008/03/definitive-guide-to-sqlite-free-book.html m> there are other sources for free ebooks m> Laurie m> Mike Owens wrote: >> On Mon, Apr 14, 2008 at 8:45 AM, Martin Jenkins <[EMAIL PROTECTED]> wrote: >> >>> Mike Owens wrote: >>> > I've been lobbying Apress to release the book in electronic form for >>> > free. It's currently under consideration, but I've not heard anything >>> > back yet. >>> > >>> That seems a bit extreme - how about a user generated/funded index on >>> the web somewhere? >>> Download and print a PDF, stick it in the back of the book and... >>> >> >> I wasn't referring to releasing the book as an exclusive solution to >> the index problem, but rather for the community and SQLite in general. >> Fixing the index issue would just be a nice dividend. I am indebted to >> Apress for picking up the book and putting substantial resources into >> making it a good book (they truly made it a much better book than it >> ever would have been otherwise), and I would like to see them recoup >> their costs and benefit in any way they so desire. But I couldn't care >> less for any gain on my part. I wrote the book for SQLite. Don't get >> me wrong, I jumped at the chance to write a book when offered to me, >> but my main concern is that the book helps people and furthers the >> project. As long as I can avoid getting vilified on Amazon, I'm happy. >> Ultimately, I would love to see this book turn out like the "Dive Into >> Python" book, which is available online, or the >> Subversion/Samba/Asterisk books. I think it is in keeping with the >> open source philosophy. But the decision in this case is not mine >> alone to make. Regardless of their decision, I applaud Apress for >> their continued efforts in supporting books on open source software, >> and the people who reward their efforts by purchasing them. >> >> -- Mike >> >> On Mon, Apr 14, 2008 at 8:45 AM, Martin Jenkins <[EMAIL PROTECTED]> wrote: >> >>> Mike Owens wrote: >>> > I've been lobbying Apress to release the book in electronic form for >>> > free. It's currently under consideration, but I've not heard anything >>> > back yet. >>> > >>> That seems a bit extreme - how about a user generated/funded index on >>> the web somewhere? >>> Download and print a PDF, stick it in the back of the book and... >>> >>> Martin >>> >>> >>> ___ >>> 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 >> >> m> ___ m> sqlite-users mailing list m> sqlite-users@sqlite.org m> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Quick Review/Reference
For SQLite in particular "The Definitive Guide to SQLite" by Micahel Owens is reasonably good. Unfortunately it has the worst index of any book I can recall seeing, other than some Cook Books. This makes it very difficult and frustrating to use as a Reference Book. I also purchased "Beginning Database Design - from Novice to Professional" by Clare Churcher, but found it to much of a beginners book for my needs. Saturday, April 12, 2008, 3:23:10 PM, you wrote: AU> Hey everyone, AU> Just got back into the world of SQL after being away for 5 AU> years. This time I decided to jump in with SQLite. It really has AU> me excited. AU> Anyways, I have done a lot database work but I seem to have AU> forgotten a lot of the subtleties of database designs/schema. AU> I am wondering if any of you have been in a similar AU> situation? Is there a set of documents/notes that you refer to? AU> Something that covers SQL and database design in general? AU> For example, for Python I refer to "A Byte of Python" and for AU> LaTex there is "The Not So Short Introduction to LaTeX." Something AU> similar for SQL is what I am looking for. AU> I've googled around but I thought why not ask the SQL veterans here? AU> Thanks, AU> Amit AU> ___ AU> sqlite-users mailing list AU> sqlite-users@sqlite.org AU> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of constraints
I have a table where two columns must be unique so I've specified UNIQUE( tagid, recid ) in the Create Table. It is perfectly valid, but infrequent, for an insert to occur where the constraint will fail. In this scenario I'm wondering whether it is best practice to always do a select first and not proceed with the insert as required, or just handle the conflict as being acceptable vs. an exception? Seeing this is in infrequent, my feeling is that always doing a select first is a waist of time & resources. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with query
Thanks Igor and Puneet, These are very different solutions, or so it appears to me. Any idea whether the join or the sub-select would be faster? In my example there is an index on tagid. Wednesday, April 9, 2008, 8:12:53 AM, you wrote: IT> Neville Franks <[EMAIL PROTECTED]> wrote: >> I have a table that holds 1 to many items. To keep it simple say it >> has 2 columns: tagid and noteid. A given tagid can have many noteid's. >> ex. >> tagid noteid >> -- -- >> a 1 >> a 4 >> a 7 >> b 7 >> b 3 >> c 1 >> >> I want to perform a query: give me all noteid's that have tagid a and >> tagid b. IT> select t1.noteid IT> from tableName t1 join tableName t2 on (t1.noteid = t2.noteid) IT> where t1.tagid='a' and t2.tagid='b'; IT> Igor Tandetnik IT> ___ IT> sqlite-users mailing list IT> sqlite-users@sqlite.org IT> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help with query
I have a table that holds 1 to many items. To keep it simple say it has 2 columns: tagid and noteid. A given tagid can have many noteid's. ex. tagid noteid -- -- a 1 a 4 a 7 b 7 b 3 c 1 I want to perform a query: give me all noteid's that have tagid a and tagid b. The result for the example above would be: noteid -- 7 Can someone point me in the right direction. I am also interested in optimal performance here. I'm new to SQL and so far I am just doing simple select's. Thanks. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of two columns for a key and query on first clm.
Let me rephrase the question slightly. If I do select * from table where clm1='def'; then step through the results will I see all rows that include 'def'. The answer must be yes. The only issue is what order they will be in. If I want them ordered by clm2 then yes I'd probably need use ORDER BY. However in this specific example I would have thought the index order would be used, which is clm1+clm2 which is the same as using ORDER BY clm2. But I appreciate this isn't guaranteed. Friday, March 21, 2008, 11:41:52 AM, you wrote: JS> No, use ORDER BY JS> Neville Franks wrote: >> If I use two columns for a key (primary or separate index) and query >> just on the first column component will I always get back the first >> match in a set. For example. >> >> - >> create table mytable ( clm1 text collate nocase, clm2 text >> collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); >> >> insert following: >> Clm1 Clm2 >> abc 123 >> abc 456 >> abc 789 >> def 123 >> def 456 >> def 789 >> >> select * from table where clm1='def'; >> - >> >> Will the returned row always be def - 123. ie. the first row for def? >> >> I've looked at the query plan for this select and it does use the >> index if clm1 alone is in the query and it appears to match on the >> first row. >> >> Also my tests indicate I do get back the first matching row. But I'd >> like confirmation if possible. >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Use of two columns for a key and query on first clm.
If I use two columns for a key (primary or separate index) and query just on the first column component will I always get back the first match in a set. For example. - create table mytable ( clm1 text collate nocase, clm2 text collate nocase, constraint mycs1 primary key( clm1, clm2 ) ); insert following: Clm1 Clm2 abc 123 abc 456 abc 789 def 123 def 456 def 789 select * from table where clm1='def'; - Will the returned row always be def - 123. ie. the first row for def? I've looked at the query plan for this select and it does use the index if clm1 alone is in the query and it appears to match on the first row. Also my tests indicate I do get back the first matching row. But I'd like confirmation if possible. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update while stepping through a prepared query
Monday, March 17, 2008, 12:03:26 PM, you wrote: IT> "Neville Franks" <[EMAIL PROTECTED]> wrote IT> in message news:[EMAIL PROTECTED] >> Can I do an Update while stepping through a prepared query? IT> Yes, in recent enough versions of SQLite, and assuming both the SELECT IT> and UPDATE run on the same database connection. Thanks Igor, that is good to hear. I'm using 3.5.6 and the same database connection. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Update while stepping through a prepared query
Can I do an Update while stepping through a prepared query?
example in psuedo code:
---
compile( "select rowid, tag, expanded from tags order by tag;"
sqlite3_step();
do
{
if ( some_clm == some_value )
sqlite3_exec( "update tags set expanded=%d where rowid=%Q;",
.. );
// will the subsequent sqlite3_step() calls perform as
expected?
}
while( sqlite3_step() == SQLITE_ROW );
-
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does an sqlite3_get_table() results set get updated.
Tuesday, March 11, 2008, 12:34:02 PM, you wrote: JS> Neville Franks wrote: >> Hi John, >> >> Tuesday, March 11, 2008, 11:51:57 AM, you wrote: >> >> JS> Neville Franks wrote: >> >>>>Hi John, >>>> >>>>Tuesday, March 11, 2008, 10:18:30 AM, you wrote: >>>> >>>>JS> Neville Franks wrote: >>>> >>>> >>>>>>Tuesday, March 11, 2008, 8:48:05 AM, you wrote: >>>>>> >>>>>>JAK> On Tue, Mar 11, 2008 at 08:37:27AM +1100, Neville >>>>>>Franks scratched on the wall: >>>>>> >>>>>> >>>>>> >>>>>>>>If I use sqlite3_get_table() and update/insert/delete one of the tables >>>>>>>>in the original query, does the results set get updated. ie. If I >>>>>>>>iterate through the original results from sqlite3_get_table() will I >>>>>>>>see the changes, or do I need to run the sqlite3_get_table() query >>>>>>>>anew? >>>>>>>> >>>>>>>>If I do see the changes does this also apply to an sqlite3_get_table() >>>>>>>>query that was on a VIEW? >>>>>> >>>>>> >>>>>>JAK> From <http://www.sqlite.org/c3ref/free_table.html>: >>>>>> >>>>>>JAK> The sqlite3_get_table() interface is implemented as a wrapper >>>>>>around >>>>>>JAK> sqlite3_exec(). The sqlite3_get_table() routine does not have >>>>>>access >>>>>>JAK> to any internal data structures of SQLite. It uses only the >>>>>>public >>>>>>JAK> interface defined here. As a consequence, errors that occur in >>>>>>the >>>>>>JAK> wrapper layer outside of the internal sqlite3_exec() call are not >>>>>>JAK> reflected in subsequent calls to sqlite3_errcode() or >>>>>>JAK> sqlite3_errmsg(). >>>>>> >>>>>>JAK> In other words, "No." You need to run it again. >>>>>> >>>>>>JAK>-j >>>>>> >>>>>>Jay, >>>>>>Thanks, I assumed that would be the case. Dynamically updating GUI's >>>>>>on SQL DB updates appears to be challenging. >>>>>> >>>> >>>>JS> Are you using Windows and the WIN API? If so you can just set up >>>>JS> callbacks to do it. >>>> >>>>I am using C++ and Windows. Do you mean use "triggers"? >>>> >> >> JS> No. If you have the data in some form of windows control like a >> JS> listview when you get a notify messge telling you it is changed you can >> JS> fire a callback to perform an Sqlite update. It is a bit tedious to >> JS> program. It is quite a while since I wrote such a program so I can only >> JS> give you a big picture. >> >> JS> By making the edit phase a transaction you can give the user the option >> JS> of commiting changes or rolling back. >> >> >> Thanks, but that isn't what I'm trying to do. >> >> I'm displaying tree's which are built from SQL queries and I ideally >> want the tree control to reflect updates to the underlying tables >> without have to do queries all over again and rebuild the trees from >> scratch. >> JS> In that case a trigger activating a user function which updates the tree JS> would be a way of doing that. Depends on number of user, processes etc. JS> We use an XML output from the DB to do that is a very ditributed way. I'm creating XML as well. I've written my own tree control that interfaces to a data source, without knowing nor caring what the data source is. It doesn't store any data itself. I was hoping I could use SQLite as a data source, but that won't work. I'll start by recreating the XML whenever the underlying SQL changes and if that proves too slow, I'll look at in situ updates to the XML DOM. There can be 10's of thousands of nodes in the trees. I'll have to run some tests with large trees to determine how long it takes to create same. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Basic system setup for tracing?
Tuesday, March 11, 2008, 11:10:03 AM, you wrote: ss>> I am trying to setup a very basic system for just tracing the sqlite ss> code, ss>> but I didn't have any luck. ss>> Code::Blocks compiles everything, but when it comes to tracing, it ss> traces ss>> even through comments. ss>> Eclipse CDT includes the sqlite3.h, and accepts the *sqlite3 pointer, ss> but ss>> doesn't accept the function db_open. Actually i don't understand how ss> this is ss>> possible. ss>> DevC++ doesn't compile sqlite3 and signals lots of errors in sqlite3.c. ss>> The environments are setup for c++ compiling via mingw and run the ss>> traditional hello world, and the sqlite used is the amalgamation. ss>> Is there a straightforward way to trace sqlite, without messing around?? ss>> Thanks, ss>> Saverio >>What do you mean by tracing. Do you mean code profiling? Or easy >>navigation though the code? Or ...? ss> Just step-in; I'm studying the sqlite code and it would be easier if I could ss> see the internal in action. I can't comment on the compilers you mention, however Microsoft VC6 has no problem tracing through the SQLite code in the debugger. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Does an sqlite3_get_table() results set get updated.
Hi John, Tuesday, March 11, 2008, 10:18:30 AM, you wrote: JS> Neville Franks wrote: >> Tuesday, March 11, 2008, 8:48:05 AM, you wrote: >> >> JAK> On Tue, Mar 11, 2008 at 08:37:27AM +1100, Neville Franks scratched on >> the wall: >> >>>>If I use sqlite3_get_table() and update/insert/delete one of the tables >>>>in the original query, does the results set get updated. ie. If I >>>>iterate through the original results from sqlite3_get_table() will I >>>>see the changes, or do I need to run the sqlite3_get_table() query >>>>anew? >>>> >>>>If I do see the changes does this also apply to an sqlite3_get_table() >>>>query that was on a VIEW? >> >> >> JAK> From <http://www.sqlite.org/c3ref/free_table.html>: >> >> JAK> The sqlite3_get_table() interface is implemented as a wrapper around >> JAK> sqlite3_exec(). The sqlite3_get_table() routine does not have access >> JAK> to any internal data structures of SQLite. It uses only the public >> JAK> interface defined here. As a consequence, errors that occur in the >> JAK> wrapper layer outside of the internal sqlite3_exec() call are not >> JAK> reflected in subsequent calls to sqlite3_errcode() or >> JAK> sqlite3_errmsg(). >> >> JAK> In other words, "No." You need to run it again. >> >> JAK>-j >> >> Jay, >> Thanks, I assumed that would be the case. Dynamically updating GUI's >> on SQL DB updates appears to be challenging. >> JS> Are you using Windows and the WIN API? If so you can just set up JS> callbacks to do it. I am using C++ and Windows. Do you mean use "triggers"? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Basic system setup for tracing?
Tuesday, March 11, 2008, 9:42:37 AM, you wrote: ss> I am trying to setup a very basic system for just tracing the sqlite code, ss> but I didn't have any luck. ss> Code::Blocks compiles everything, but when it comes to tracing, it traces ss> even through comments. ss> Eclipse CDT includes the sqlite3.h, and accepts the *sqlite3 pointer, but ss> doesn't accept the function db_open. Actually i don't understand how this is ss> possible. ss> DevC++ doesn't compile sqlite3 and signals lots of errors in sqlite3.c. ss> The environments are setup for c++ compiling via mingw and run the ss> traditional hello world, and the sqlite used is the amalgamation. ss> Is there a straightforward way to trace sqlite, without messing around?? ss> Thanks, ss> Saverio What do you mean by tracing. Do you mean code profiling? Or easy navigation though the code? Or ...? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Does an sqlite3_get_table() results set get updated.
If I use sqlite3_get_table() and update/insert/delete one of the tables in the original query, does the results set get updated. ie. If I iterate through the original results from sqlite3_get_table() will I see the changes, or do I need to run the sqlite3_get_table() query anew? If I do see the changes does this also apply to an sqlite3_get_table() query that was on a VIEW? I realize I could write a sample app to try this, but would rather try and save the time. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3: handling of indices
I think you are misinterpreting this. It says: "Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout." This does not say it recreates the index, just the internal representation. Indices are stored in the database file. I don't know what leads you to think otherwise. The file size may not increase if you just add a few records and the if the clm which is indexed is narrow (short words). Basically you concerns are unfounded. Monday, March 3, 2008, 7:58:50 AM, you wrote: ML> Hello sqlite-users, ML> is it true, that in sqlite3 indices are not stored in the database-file? ML> When reading the section ML> http://sqlite.org/lang_createindex.html it ML> seems that CREATE INDEX statements are only stored in the sqlite_master ML> table and the index will be generated every time the database is opened. ML> Can sqlite3 be forced to store the index itself in the database-file? ML> (In SQLite2 indices were stored directly in the file, wheren't they?) ML> When creating an index on a database the filesize does not increase so I ML> suppose the index is not stored in the file. ML> Background: ML> 1. The database will be used "read-only", a regeneration of the index is ML> not necessary. ML> 2. The database will be accessed via jdbc, a regeneration of indices ML> with every opening of the file causes to many "regeneration". ML> TIA, ML> Michael ML> ___ ML> sqlite-users mailing list ML> sqlite-users@sqlite.org ML> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Update fail without ERRORS
Well I'm very new to SQLite but shouldn't:
UPDATE table SET Value=12.3 WHERE Address=7 and Port=1
be:
UPDATE table SET Value='12.3' WHERE Address='7' and Port='1';
sqlite3_vmprintf() is the recommended method to build SQL with
parameters.
Saturday, March 1, 2008, 6:31:50 PM, you wrote:
tti> The code is very long, I'll try to put here the core of my application.
tti> I'm using a C++ Class where one function is "sqlraw" that I use to
tti> execute a SQL statement:
tti> CLASS DEFINITION
tti> sqlite3 *db;
tti> int expander::
tti> open_db(char * pDbName)
tti> {
tti>int rc;
tti>rc = sqlite3_open(pDbName, );
tti> if( rc )
tti>{
tti> fprintf(stderr, "Can't open database: %s\n",
tti> sqlite3_errmsg(db));
tti> sqlite3_close(db);
tti> exit(1);
tti>}
tti>return(0);
tti> }
tti> int expander::sqlraw(char *pSql)
tti> {
tti>int rc;
tti>char *zErrMsg = 0;
tti> printf("SQLRAW: SQL=%s\n",pSql);
tti>printf("Database %d\n",db);
tti>rc =
tti> sqlite3_exec(db,pSql, NULL, NULL, );
tti>printf("SQLRAW: Stato=%d
tti> - OK=%d\n",rc, SQLITE_OK);
tti>if( rc!=SQLITE_OK )
tti>{
tti>fprintf(stderr,
tti> "SQL error: %s\n", zErrMsg);
tti>sqlite3_free(zErrMsg);
tti>return(-1);
tti>}
tti> return(0);
tti> }
tti> int main(int argc ,char *argv[])
tti> {
tti>expander expio;
tti> char sPre[2048[;
tti>expio.open("test.db");
tti>strcpy(sPre,"UPDATE
tti> table SET Value=12.3 WHERE Address=7 and Port=1");
tti>if (expio.sqlraw
tti> (sPre) == 0)
tti>{
tti>/ / Action for no error
tti>}
tti>else
tti>{
tti> // Manage error conditions
tti>}
tti> When I execute the code, sqlraw
tti> function print the pSql string, and this is the same I pass.
tti> The
tti> Database descriptor is the same returned from open function, and status
tti> code is OK!!!
tti> But table value isn't updated.
tti> I don't understand
tti> what's matter, and because i haven't any error message I can't debug
tti> it.
tti> Any suggestion is VERY VERY appreciate
tti> Pierluigi Bucolo
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieve Rownumber in sqlite
Hi Kalyani, There is a column named ROWID which gives you the internal row number. Saturday, March 1, 2008, 9:32:44 AM, you wrote: KP> In SQL Server2005, Row_number() function is used to retrieve the KP> sequential number of a row within a partition of a result set, starting KP> at 1 for the first row in each partition. Which is very useful when KP> implementing paging through a large number records in Table. Is there KP> any function available in SQLite similar to this. KP> Thanks KP> -Kalyani -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DATETIME data type
Friday, February 29, 2008, 8:29:16 AM, you wrote:
YZ> It seems that sqlite3 does not support DATETIME data type.
YZ> If I have the following data in table t1, how do I select people who is
YZ> older than certain date?
YZ> create table t1(dob text, name text);
YZ> insert into t1('11/12/1930', 'Larry');
YZ> insert into t1('2/23/2003', 'Mary');
YZ> select * from t1 where dob < '3/24/1950';
Well I'm very new to SQLite but I think you need to use the formats
specified on "Date And Time Functions" Wiki page:
http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions
Trying to compare dates in the format you have used would require
conversion to something sensible like -MM-DD
I am personally a big fan of the ISO-8601 format and use them
everywhere.
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question on Blobs
Hi Mike,
The data in this example happens to come from file, but that isn't
relevant. The line:
rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
is binding a chunk of data on the heap to the blob column and
inserting that into the database. Where this chunk of data comes from
isn't relevant. HTH.
Thursday, February 28, 2008, 2:35:27 PM, you wrote:
MM> Wow, Peter, didn't expect that anyone would go to the trouble of writing a
MM> program on the spot
MM> Just curious, but from those few things that I have seen, it appears that
MM> you can only put a Blob into the DB if it is already on disc, right? All
MM> three examples I have seen passed the filename to the database, and one of
MM> them was working within a server context, so I wasn't sure how the local
MM> filename would be of any use to a machine that is in another part of the
MM> room (or anywhere else...).
MM> Just so you understand what it is I am trying to do, I am working in a
MM> Multimedia programming environment (Pure Data), and I would like to be able
MM> to read and write some chunks of audio or video as needed. While Pure Data
MM> is a realtime environment, I am not expecting this to be responsive to work
MM> in realtime.
MM> Thanks again, I will study this to see if it tells me anything more...
MM> Mike
MM> On Wed, Feb 27, 2008 at 8:02 PM, Peter A. Friend <[EMAIL PROTECTED]>
MM> wrote:
>>
>> On Feb 27, 2008, at 4:48 PM, Mike McGonagle wrote:
>>
>> > Hello all,
>> > I was hoping that someone might share some tips on working with
>> > Blobs? I
>> > would like to be able to store some images and sound files in a
>> > database,
>> > but never having dealt with them, I am kind of at a loss for some
>> > examples.
>> > I have looked on the web, and there are few examples that were of use.
>>
>> Well, I wrote a quick and dirty program for stuffing image files into
>> a database. You just provide a directory and it stats() each file,
>> allocates enough space for the image data, then loads it from disk.
>> Sql statement is something like:
>>
>> char* sql = "insert into i (name, data) values (?, ?);";
>>
>> Of course if your images are huge this method coud be problematic. I
>> believe SQLite supports an incremental way to do this but I haven't
>> looked at those calls yet.
>>
>>while ( (dentry = readdir(dir)) != '\0') {
>> if (dentry->d_name[0] == '.')
>> continue;
>>
>> if (fd != -1) {
>> close(fd);
>> fd = -1;
>> }
>>
>> if (data != '\0') {
>> free(data);
>> data = '\0';
>> }
>>
>> snprintf(fname, sizeof(fname), "%s/%s", newdir, dentry->d_name);
>> stat(fname, );
>>
>> if ( (data = malloc(sb.st_size)) == '\0') {
>> fprintf(stderr, "malloc() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if ( (fd = open(fname, O_RDONLY, )) == -1) {
>> fprintf(stderr, "open() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if ( (retval = read(fd, data, sb.st_size)) == -1) {
>> fprintf(stderr, "read() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> if (retval != sb.st_size) {
>> fprintf(stderr, "read() failed\n");
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_bind_text(stmt, 1, dentry->d_name, dentry->d_namlen,
>> SQLITE_STATIC);
>>
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "sqlite3_bind_text() %s\n", sqlite3_errmsg
>> (db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_bind_blob(stmt, 2, data, sb.st_size, SQLITE_STATIC);
>>
>> if (rc != SQLITE_OK) {
>> fprintf(stderr, "sqlite3_bind_blob() %s\n", sqlite3_errmsg
>> (db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> rc = sqlite3_step(stmt);
>>
>> if (rc != SQLITE_DONE) {
>> fprintf(stderr, "sqlite3_step() %s\n", sqlite3_errmsg(db));
>> sqlite3_finalize(stmt);
>> sqlite3_close(db);
>> exit(1);
>> }
>>
>> sqlite3_reset(stmt);
>>}
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Keeping ROWID for INSERT OR REPLACE
I want to insert a row if its key clm doesn't exist otherwise update it. I can search for the row and then do either an insert or update accordingly. However I was wondering whether the SQLite Conflict Resolution: INSERT OR REPLACE would be more efficient (faster). The problem is the REPLACE deletes the existing row and INSERT adds a new one, loosing the ROWID value of the original row, which I need to keep. So my question is should I just forget this and do it the: select -> if not found insert otherwise update way or is there a way to maintain the original rowid using INSERT OR REPLACE? If not what is the fastest way to check if a row exists, assuming the search is on a single clm which is indexed. ex. select myclm from mytable where myclm='abc'; select count(*) from mytable where myclm='abc'; add limit 1 to either of the above etc. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] compiling C program to use the shared library
Monday, February 25, 2008, 2:13:13 PM, you wrote: NL> On Mon, Feb 25, 2008 at 2:37 AM, Sam Carleton NL> <[EMAIL PROTECTED]> wrote: >> Thank you, I know all about LoadLibrary. I also saw the header file >> that contains a structure with function pointers to all the exported >> methods. But are you telling me that no one has published the code to >> load up that structure? In the mean time I used the info from the >> other post to simply compile the one big .c file as a DLL that >> generated the stub lib I needed to link my code against and it seems >> to run fine with the officially compiled DLL. NL> The reason an import library isn't included is because you need a NL> different one for each compiler you use to link. NL> With Microsoft compilers you can use LIB.EXE to generate an import NL> library for you given a DLL and a .DEF file. You can also google for NL> IMPLIB. It does the same job for you, but without needing a .DEF file. NL> Other compilers have similar systems. NL> I don't recall the exact parameters, so just look at the command help NL> (or google is your friend). NL> Regards, NL> ~Nuno Lucas NL> ___ NL> sqlite-users mailing list NL> sqlite-users@sqlite.org NL> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users For MSVC use: c:\>lib /def:sqlite.def to create the lib file to link against. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index for Primary Key column missing?
Jens, Thanks for that. I incorrectly assumed because the index was named xxx_tags it wasn't for the 'tag' column, but related to the table, which is named 'tags'. I had read the content you referred to. Thursday, February 21, 2008, 11:11:19 PM, you wrote: JM> Am 21.02.2008 um 10:30 schrieb Neville Franks: >> I have created a table with a column: tag text primary key >> >> When I do: >> sqlite> .indices tags >> sqlite_autoindex_tags_1 >> >> I only see the one index which I assume is for the ROWID clm. JM> No, this is the index for your 'tag' column as can be seen by sqlite>> EXPLAIN QUERY PLAN SELECT * FROM tags WHERE tag='foo'; JM> 0|0|TABLE tags WITH INDEX sqlite_autoindex_tags_1 JM> (see also <http://sqlite.org/lang_createtable.html>, especially the JM> section "Specifying a PRIMARY KEY..." for more details) >> I have >> inserted a row into the table, in case that is relevant. >> >> My understanding is that a primary key column will have an index. So >> my question is where is the index for the 'tag' column? >> >> This is the complete CREATE TABLE SQL. >> >> CREATE TABLE tags >> ( >>tag text primary key, >>description text, >>date_created text DEFAULT CURRENT_TIMESTAMP, >>style text >> ) -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The best way to handle dynamic table creation
Simon, Thanks for that. Just shows how new I am at SQL. Thursday, February 21, 2008, 9:01:22 PM, you wrote: SD> See "IF NOT EXISTS" in http://www.sqlite.org/lang_createtable.html SD> Rgds, SD> Simon SD> On 21/02/2008, Neville Franks <[EMAIL PROTECTED]> wrote: >> I need to create tables on the fly which will persist across sessions. >> These tables may or may not already exist. >> >> Calling sqlite3_exec( "create table ..." ) for a table which already >> exists returns SQLITE_ERROR, which I assume can be returned for a range >> of different errors. You can check the szError string to determine the >> precise error, but this will fall down if the error text is ever >> changed, so I'm reluctant to do this. Further it doesn't seem right to >> try and create a table that already exists. >> >> So my solution is do do a query: >> select name, from sqlite_master where type='table' and name=table_name; >> and check the result before attempting to create the table. >> >> My question is what do other folks do here? Is my solution a good one? >> >> -- >> Best regards, >> Neville Franks, http://www.surfulater.com http://blog.surfulater.com >> >> >> _______ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] The best way to handle dynamic table creation
I need to create tables on the fly which will persist across sessions. These tables may or may not already exist. Calling sqlite3_exec( "create table ..." ) for a table which already exists returns SQLITE_ERROR, which I assume can be returned for a range of different errors. You can check the szError string to determine the precise error, but this will fall down if the error text is ever changed, so I'm reluctant to do this. Further it doesn't seem right to try and create a table that already exists. So my solution is do do a query: select name, from sqlite_master where type='table' and name=table_name; and check the result before attempting to create the table. My question is what do other folks do here? Is my solution a good one? -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Index for Primary Key column missing?
I have created a table with a column: tag text primary key When I do: sqlite> .indices tags sqlite_autoindex_tags_1 I only see the one index which I assume is for the ROWID clm. I have inserted a row into the table, in case that is relevant. My understanding is that a primary key column will have an index. So my question is where is the index for the 'tag' column? This is the complete CREATE TABLE SQL. CREATE TABLE tags ( tag text primary key, description text, date_created text DEFAULT CURRENT_TIMESTAMP, style text ) Thanks. -- Best regards, Neville Franks, http://www.surfulater.com http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there always an index on ROWID?
Hi Jeff, Thanks, I thought that would be the case, but didn't want to start creating tables etc. and find I was wrong. Sorry about the spam-challenge you received. I didn't have it setup right for this list. Thursday, February 21, 2008, 11:30:51 AM, you wrote: JH> The table data is stored in a b-tree keyed off of the rowid, so JH> lookups based on rowid should always be fast. JH> -Jeff JH> On Wed, Feb 20, 2008 at 6:27 PM, Neville Franks <[EMAIL PROTECTED]> wrote: >> Hi, >> If I create a table with a Primary key on a TEXT clm, will there still >> be an Index on the in-built ROWID clm. The reason for asking this is >> that I need fast (indexed) lookup to rows by both ROWID and my TEXT >> clm. >> >> I am just starting out with SQLite and have almost finished Michael >> Owens book, but have been unable to find an answer to this. >> >> Thanks. >> >> -- >> Best regards, >> Neville Franks, Author of Surfulater - Your off-line Digital Reference >> Library >> Soft As It Gets Pty Ltd, http://www.surfulater.com - Download your copy >> now. >> Victoria, Australia Blog: http://blog.surfulater.com >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> -- Best regards, Neville Franks, Author of Surfulater - Your off-line Digital Reference Library Soft As It Gets Pty Ltd, http://www.surfulater.com - Download your copy now. Victoria, Australia Blog: http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is there always an index on ROWID?
Hi, If I create a table with a Primary key on a TEXT clm, will there still be an Index on the in-built ROWID clm. The reason for asking this is that I need fast (indexed) lookup to rows by both ROWID and my TEXT clm. I am just starting out with SQLite and have almost finished Michael Owens book, but have been unable to find an answer to this. Thanks. -- Best regards, Neville Franks, Author of Surfulater - Your off-line Digital Reference Library Soft As It Gets Pty Ltd, http://www.surfulater.com - Download your copy now. Victoria, Australia Blog: http://blog.surfulater.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
