[sqlite] (no subject)
please unregister me ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 13 Oct 2014, at 12:06pm, Rohit Kaushal rohitkausha...@gmail.com wrote: please unregister me Only you can stop forest fires. See the link at the bottom of every post to this list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://recycling-it.com/wp-content/themes/health.google.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
I have two tables: ARB KEY (PRIMARY KEY) ASSIGN (NOT NECESSARILY UNIQUE) DMC KEY (NOT UNIQUE) ASSIGN (NOT UNIQUE) VALUE I need to report all the records from ARB, and sum up the values if the keys match OR if the keys don't match, then sum up the values where the ASSIGN matches, but only if the ASSIGN is unique in ARB. SELECT ARB.KEY , ARB.ASSIGN , COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY) , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = ARB.ASSIGN AND ...), 0) FROM ARB; I can't think of what to put after that AND I don't think it matters, but I simplified things up there. DMC is actually a view of a table with KEY/ASSIGN and another with KEY/VALUE. Any help is appreciated. Thanks, David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 1/28/2014 2:26 PM, David Bicking wrote: I have two tables: ARB KEY (PRIMARY KEY) ASSIGN (NOT NECESSARILY UNIQUE) DMC KEY (NOT UNIQUE) ASSIGN (NOT UNIQUE) VALUE I need to report all the records from ARB, and sum up the values if the keys match OR if the keys don't match, then sum up the values where the ASSIGN matches, but only if the ASSIGN is unique in ARB. SELECT ARB.KEY , ARB.ASSIGN , COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY) , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = ARB.ASSIGN AND ...), 0) FROM ARB; I can't think of what to put after that AND Something like this perhaps: and 1 = (select count(*) from ARB t2 where t2.ASSIGN=ARB.ASSIGN) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Tue, 1/28/14, Igor Tandetnik i...@tandetnik.org wrote: Subject: Re: [sqlite] (no subject) To: sqlite-users@sqlite.org Date: Tuesday, January 28, 2014, 2:41 PM On 1/28/2014 2:26 PM, David Bicking wrote: I have two tables: ARB KEY (PRIMARY KEY) ASSIGN (NOT NECESSARILY UNIQUE) DMC KEY (NOT UNIQUE) ASSIGN (NOT UNIQUE) VALUE I need to report all the records from ARB, and sum up the values if the keys match OR if the keys don't match, then sum up the values where the ASSIGN matches, but only if the ASSIGN is unique in ARB. SELECT ARB.KEY , ARB.ASSIGN , COALESCE((SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.KEY = ARB.KEY) , (SELECT SUM(DMC.VALUE) FROM DMC WHERE DMC.ASSIGN = ARB.ASSIGN AND ...), 0) FROM ARB; I can't think of what to put after that AND Something like this perhaps: and 1 = (select count(*) from ARB t2 where t2.ASSIGN=ARB.ASSIGN) Igor Tandetnik Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
I have two tables of the form create table1 (id1 int, t1 text) create table2 (id2 int unique, t2 text) the data in table 1 is such that some values for t1 are NULL ID1 can contain duplicates ID2 is unique and for every instance of ID1 in table1 there will be a corresponding ID2 entry in table2 t2 in table 2 is always populated with a string For those entries in table1 where there is a null in t2 I want to copy the corresponding entry from table2 any ideas? thx. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 5/16/2013 9:29 AM, Paul Sanderson wrote: I have two tables of the form create table1 (id1 int, t1 text) create table2 (id2 int unique, t2 text) For those entries in table1 where there is a null in t2 I want to copy the corresponding entry from table2 update table1 set t1=(select t2 from table2 where id2=id1) where t1 is null; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
First idea: include a subject line. I'm not 100% clear on your message. You said: For those entries in table1 where there is a null in t2... I'm guessing you wanted to say: For those entries in table1 where there is a null in t1... Is that right? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Rob yes thats correct Igor - thanks I'll give that a go On 16 May 2013 14:51, Rob Richardson rdrichard...@rad-con.com wrote: First idea: include a subject line. I'm not 100% clear on your message. You said: For those entries in table1 where there is a null in t2... I'm guessing you wanted to say: For those entries in table1 where there is a null in t1... Is that right? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
That worked thanks Igor. On 16 May 2013 15:33, Paul Sanderson sandersonforens...@gmail.com wrote: Rob yes thats correct Igor - thanks I'll give that a go On 16 May 2013 14:51, Rob Richardson rdrichard...@rad-con.com wrote: First idea: include a subject line. I'm not 100% clear on your message. You said: For those entries in table1 where there is a null in t2... I'm guessing you wanted to say: For those entries in table1 where there is a null in t1... Is that right? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi all Im getting the following exception when I try to open a connection to a SQLite db: System.TypeLoadException: Requested Windows Runtime type 'Sqlite.Sqlite3' is not registered. --- System.Runtime.InteropServices.COMException: Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) Result StackTrace: at System.StubHelpers.StubHelpers.GetWinRTFactoryObject(IntPtr pCPCMD) at Sqlite.Sqlite3.sqlite3_open_v2(String filename, Database db, Int32 flags, String zVfs) at MSI.Framework.DataAccess.SQLite.SQLite3.Open(String filename, Database db, Int32 flags, IntPtr zVfs) at MSI.Framework.DataAccess.SQLite.SQLiteConnection..ctor(String databasePath, SQLiteOpenFlags openFlags, Boolean storeDateTimeAsTicks) at PhoneTests.UnitTest1.CreateATable() My situation is: im using sqlite-net-wp8 and sqllite as downloaded from nuget this produced a .net wrapper called SQLite.cs and SQLiteAsync.cs and im calling: using (var conn = new SQLiteConnection(@E:\TEMP\TEST.DB, SQLiteOpenFlags.ReadWrite)) Is this enough information or not? -- Kind Regards, Michael Grant Walmsley E: mgw...@gmail.com P: 0412886788 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 31 Jan 2013, at 7:33pm, Paul Sanderson sandersonforens...@gmail.com wrote: explain query plan and explain have been run on the table with the results below. Any ideas where and how I can improve performance? Can you show us the indexes you've created on rtable ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 1/31/2013 2:33 PM, Paul Sanderson wrote: My query is select fileref from rtable as r where vsc 0 and isgraphic = 1 and not exists (select md5 fr om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); explain query plan and explain have been run on the table with the results below. Any ideas where and how I can improve performance? An index on rtable(md5) should help. If you do have one, and it doesn't get picked, try changing isgraphic = 1 to +isgraphic = 1 in the inner select (the unary plus operator makes the index on isgraphic inapplicable, hopefully steering the query planner towards a more helpful index). Also, you can write the query a bit more compactly: select fileref from rtable where vsc 0 and isgraphic = 1 and md5 not in (select md5 from rtable where isgraphic = 1 and vsc = 0); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 31 Jan 2013, at 8:48pm, Paul Sanderson sandersonforens...@gmail.com wrote: Thanks all All columns in the query are indexed. That does not do you much good. Each SELECT can use only one index at a time. So if you have one index per column the query uses an index on, say, isgraphic, then it will have to search every value in vsc itself. The way to create indexes is to figure out one index that's useful for each SELECT. Here's the command you want to speed up: My query is select fileref from rtable as r where vsc 0 and isgraphic = 1 and not exists (select md5 fr om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); Depending on how chunky the values are in each column, a good index for this would be an index on (md5,isgraphic,vsc). Try creating this index and see if it helps. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 31 Jan 2013, at 8:58pm, Simon Slavin slav...@bigfraud.org wrote: Depending on how chunky the values are in each column, a good index for this would be an index on (md5,isgraphic,vsc). Sorry, that should be one index on (isgraphic,vsc), I think. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
This makes a huge difference in speed thanks Also, you can write the query a bit more compactly: select fileref from rtable where vsc 0 and isgraphic = 1 and md5 not in (select md5 from rtable where isgraphic = 1 and vsc = 0); On 31 January 2013 19:54, Igor Tandetnik i...@tandetnik.org wrote: On 1/31/2013 2:33 PM, Paul Sanderson wrote: My query is select fileref from rtable as r where vsc 0 and isgraphic = 1 and not exists (select md5 fr om rtable as r1 where r.md5 = r1.md5 and isgraphic = 1 and vsc = 0); explain query plan and explain have been run on the table with the results below. Any ideas where and how I can improve performance? An index on rtable(md5) should help. If you do have one, and it doesn't get picked, try changing isgraphic = 1 to +isgraphic = 1 in the inner select (the unary plus operator makes the index on isgraphic inapplicable, hopefully steering the query planner towards a more helpful index). Also, you can write the query a bit more compactly: select fileref from rtable where vsc 0 and isgraphic = 1 and md5 not in (select md5 from rtable where isgraphic = 1 and vsc = 0); -- Igor Tandetnik __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Does anyone know if there is a mailing list for the STL project? This e-mail is for the sole use of the intended recipient and may contain confidential or privileged information. Unauthorized use of its contents is prohibited. If you have received this e-mail in error, please notify sender immediately via return e-mail and then delete the original e-mail. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Arbol One arbol...@gmail.com wrote: Does anyone know if there is a mailing list for the STL project? Which of these do you mean by STL? http://en.wikipedia.org/wiki/Standard_Template_Library http://en.wikipedia.org/wiki/State_logic http://en.wikipedia.org/wiki/Subtitle_(captioning) http://en.wikipedia.org/wiki/STL_(file_format) http://en.wikipedia.org/wiki/Statement_List Also, how is your question related to SQLite? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 09/05/2012 06:36 PM, Arbol One wrote: That would be the C++ Standard Template Library http://en.wikipedia.org/wiki/Standard_Template_Library I am learning the library and as a student I always have questions. The STL isn't necessarily a project, as it is defined in the C++ standard and therefore is a part of the C++ programming language. If you are looking for public forums where the STL is discussed, you could give Usenet's comp.lang.c++ a try. If you don't have a usenet client around, here's a google news link: https://groups.google.com/forum/?fromgroups=#!forum/comp.lang.c++ Hope this helps, Rui Maciel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
For the removal of doubt: We have taken to requiring moderator approval before accepting new members onto this mailing list. And even so spam like that shown below sometime leaks through. There is an arms race on, with spammers getting ever more sophisticated in their attacks. Sorry for the noise. dragonslayer2k has now been banished from the mailing list, of course. Not that it matters, since dragonslayer2k was probably a throw-away account anyhow On Wed, Jul 18, 2012 at 3:40 PM, Danny dragonslaye...@yahoo.com wrote: http://www.searvhlands.com/blog/wp-content/plugins/zkuaufaainv/shop.php?cup209.pnghttp://www.sea**nds.com/blog/wp-content/plugins/zkuaufaainv/shop.php?cup209.png ___ 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] (no subject)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/07/12 13:00, Richard Hipp wrote: We have taken to requiring moderator approval before accepting new members onto this mailing list. A better approach as used elsewhere (eg Google Yahoo groups) is that the first ~10 messages from someone are moderated. (The moderator can put that person on the approved/banned list early.) Note that this applies even if someone joins, doesn't post for a year and then sends something. It could be legit or their account could have been hacked. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlAHJlMACgkQmOOfHg372QSQdACg21+RvVpBGFHMSTfQdMc6tOnu oMQAnjI1Ca/0RFjgMfIVqrtQTyRWp5h+ =d35G -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Wed, Jul 18, 2012 at 02:10:52PM -0700, Roger Binns scratched on the wall: On 18/07/12 13:00, Richard Hipp wrote: We have taken to requiring moderator approval before accepting new members onto this mailing list. A better approach as used elsewhere (eg Google Yahoo groups) is that the first ~10 messages from someone are moderated. (The moderator can put that person on the approved/banned list early.) Except that adds about 10x the workload for the moderators. I'd rather have the SQLite team working on code, even if it means suffering the occasional spam message. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 18/07/12 17:54, Jay A. Kreibich wrote: Except that adds about 10x the workload for the moderators. I'd rather have the SQLite team working on code, even if it means suffering the occasional spam message. Who says the SQLite team have to be the moderators? With Google Groups there is a web page with all pending messages shown and the actions available. Anyone with access to the page can moderate the messages. Heck I'd be happy to do some of the moderation. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlAHkaEACgkQmOOfHg372QSFXwCg1boCVH5leBJa1jMaxIFMeg/J ByAAoM+JPb2pyZwAsDvsmgS46BJ1Erfx =WFbu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://en.shijingsteel.com/pptlr.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Any one please tell me How to use triggers in sqlite using C... -- With Regards, *Bageesh.M.Bose* ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, I am new to SQLITE Would be using Microsoft VS 2008 2010 Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); ie. does sqlite implement an alternative to f_lock especially for :memory: databases. Many thanks Nick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 06/12/2012 05:41 PM, Nicholas Thompson wrote: Hi, I am new to SQLITE Would be using Microsoft VS 2008 2010 Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared,db); Should be safe, yes. Assuming you are using the library in either multi-thread or serialized (the default) mode. http://www.sqlite.org/threadsafe.html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Nicholas Thompson nick.john.thomp...@googlemail.com wrote: Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); Every time you connect to a :memory: database, a new in-memory database is created. There ain't no such thing as two connections to the same memory database. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Tue, Jun 12, 2012 at 8:32 AM, Igor Tandetnik itandet...@mvps.org wrote: Nicholas Thompson nick.john.thomp...@googlemail.com wrote: Is it possible to safely use multiple threads in a process with each thread making its own connection to the same database rc = sqlite3_open(file::memory:?cache=shared, db); Every time you connect to a :memory: database, a new in-memory database is created. There ain't no such thing as two connections to the same memory database. Igor, This comment will soon become out-of-date. See http://www.sqlite.org/draft/releaselog/3_7_13.html. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
At 07:08 06/06/2012, you wrote: Hi, I have few queries regarding the SQLite. I have listed them below please go through them and give your answers. Thanks Say if I am working on a DB with 3 tables with 100 records each and each records with some 5 fields of data.Each field is varchar(20) 1. What will be the Stack Size? Depends on what are you doing with the db, it vary with time. 2. What will be the Heap Size? Again, depends on what are you doing. 3. What is the max no. of file pointers that will be used at a point of time? If I Remember Correctly (IIRC) it's 2 per db file. But it was on 3.2.x times. Keep in mind that sqlite can access and manage up to 32 db files using ATTACH. 4. What is the max no. of Mutexes that will be used at a point of time? Don't know, why do you need it? 5. Is there a tool to calculate these? Compile with debug mode on and use gdb. Please bare my ignorance if any and answer these questions. Thanks Again, Raghava Reddy ___ 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] (no subject)
On Wed, Jun 6, 2012 at 1:08 AM, vallur raghava reddy vallur.raghavare...@gmail.com wrote: Hi, I have few queries regarding the SQLite. I have listed them below please go through them and give your answers. Thanks Say if I am working on a DB with 3 tables with 100 records each and each records with some 5 fields of data.Each field is varchar(20) 1. What will be the Stack Size? SQLite has been used in Windows device drivers, with a maximum stack frame size of 4K and a practical upper bound on the stack size of 2K. More space than this might be needed depending on what SQL you run. 2. What will be the Heap Size? Thre is a tradeoff between memory and speed. The more heap space you give the SQLite, the faster it will run. You can run it in less than 100K or 200K of heap in the default configuration. By tweaking the configuration, you can probably run it in 50K or less of heap. Most people are more interested in making it run faster than in minimizing the heap memory usage, and thus give it multiple megabytes to use. There are features in SQLite that let you control the amount of heap memory usage. In particular, you can configure SQLite in such as way that you hand it a single big chunk of heap memory at start time and it will never use any memory other than that one chunk you give it. 3. What is the max no. of file pointers that will be used at a point of time? Depends on what you are doing. There are at least 2 per database file. Note that all your TEMP tables are stored in a separate database file. Also, SQLite sometimes has to manifest a view and that manifestation is stored in a separate database file. 3 file descriptors per database file are required in WAL mode. 4. What is the max no. of Mutexes that will be used at a point of time? 5 + N + M where N is the number of open database connections and M is the number of open database files. 5. Is there a tool to calculate these? Please bare my ignorance if any and answer these questions. Thanks Again, Raghava Reddy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, I have few queries regarding the SQLite. I have listed them below please go through them and give your answers. Thanks Say if I am working on a DB with 3 tables with 100 records each and each records with some 5 fields of data.Each field is varchar(20) 1. What will be the Stack Size? 2. What will be the Heap Size? 3. What is the max no. of file pointers that will be used at a point of time? 4. What is the max no. of Mutexes that will be used at a point of time? 5. Is there a tool to calculate these? Please bare my ignorance if any and answer these questions. Thanks Again, Raghava Reddy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi! Thank You for replying Stefanos! Unfortunately, the errors were typos on my part. Sorry about that. As I wrote, everything works fine the first time around. What I am trying to accomplish is an update from the source data. If the entry is already there, just ignore it. Otherwise add the new data. I'm using version 3.7.12. Theories anyone? Regards, /Jörgen skrev 1970-01-01 01:00: pre wrap You are missing parentheses here: create table if not exists a (id integer primary key autoincrement, a text unique, b default current_timestamp); create table if not exists b (id integer primary key autoincrement, a integer references a(id) on update cascade on delete cascade, b text, c text, d text, e text, f real, g text, h text, unique (a, b) on conflict ignore); create table if not exists c (id integer primary key autoincrement, a integer, b integer references b(id) on update cascade on delete cascade, c integer, d text, e integer, f text, g blob, unique (a, b, c) on conflict ignore); Also here you miss 'a' insert into c (a, b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g); Also what sqlite version are you using? I have run this with the latest available version (3.7.12.1) under SQLite Manager (Firefox / Iceweasel extension). If you need any further help, please let me know. Cheers. Stefanos /preblockquote type=citepre wrap From: Jörgen Hägglund lt;jor...@sirlucifer.mine.nugt; To: sqlite-users@sqlite.org Sent: Sunday, June 3, 2012 11:10 PM Subject: [sqlite] Foreign key constraint failed Hi all! I have just recently discovered the strengths of foreign keys but now, I've been banging my head against the wall for a couple of days. I just can't figure out what is causing the error or how to get around it. Here's the specs (simplified) of my DB; create table if not exists a (id integer primary key autoincrement, a text unique, b default current_timestamp); create table if not exists b (id integer primary key autoincrement, a integer references a(id) on update cascade on delete cascade, b text, c text, d text, e text, f real, g text, h text, unique (a, b) on conflict ignore; create table if not exists c (id integer primary key autoincrement, a integer, b integer references b(id) on update cascade on delete cascade, c integer, d text, e integer, f text, g blob, unique (a, b, c) on conflict ignore; And the insert query; insert into c (b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g); The problem is that I get a Foreign key constraint failed error when I try to insert the same data twice. The first time it works as it should and the data is inserted correctly. But, the second time, I get the exception. Is there anyone out there who's willing (and able) to help me? And hopefully explain what I'm doing wrong. Regards, /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users /pre/body /html /html ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 4 Jun 2012, at 6:39pm, Jörgen Hägglund jorgenhaggl...@netscape.net wrote: What I am trying to accomplish is an update from the source data. If the entry is already there, just ignore it. Otherwise add the new data. If any unique key (including the primary key) of the existing row and the new entry match, then you can avoid getting an error by using INSERT OR IGNORE … This means that if the INSERT would normally result in an error message about duplicate key values, SQLite does not do the new INSERT, but returns an 'OK' result code, as if the INSERT had worked. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Fri, 25 May 2012 14:56:24 -0400, Frank Chang frank_chan...@hotmail.com wrote: http://swatim dot sw dot ohost dot de/gertycamp3.html Poisonous link, don't click. Frank, please perform a rigorous virus check on your machine(s), you may have been infected by a bot. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
How can I construct a update query to calculate and set a record field latency with the difference between timestamps by deviceid? Appears sqlite doesn't support lag and lead. ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the main ones of interest are 'timestamp', 'latency' and 'DeviceID'. 2012-05-01 13:12:11.103 Null 14356 2012-05-01 13:12:11.103 Null 14372 2012-05-01 13:12:11.103 Null 4356 2012-05-01 13:12:07.103 Null 14356 2012-05-01 13:12:11.221 Null14356 The data is collected inconsistently... many times out of sequence I want to update the latency field for each record with the latest previous timestamp for the same DeviceID. Run this update query every time new data is appended to the table. Periodically recalc the entire table if an out of sequence file is processed or at minimum recalc from the earliest out of sequence time to present. I can do this fairly easily in excel but am struggling in sql/sqlite. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 5/24/2012 11:46 AM, IQuant wrote: ie. I have a table with 1,000,000 + records collecting real time stats from many devices with many columns but the main ones of interest are 'timestamp', 'latency' and 'DeviceID'. 2012-05-01 13:12:11.103 Null 14356 2012-05-01 13:12:11.103 Null 14372 2012-05-01 13:12:11.103 Null 4356 2012-05-01 13:12:07.103 Null 14356 . 2012-05-01 13:12:11.221 Null14356 I want to update the latency field for each record with the latest previous timestamp for the same DeviceID. update MyTable set latency = ( select max(t2.timestamp) from MyTable t2 where t2.DeviceId = MyTable.DeviceId and t2.timestamp MyTable.timestamp); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php 5/7/2012 6:58:21 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
http://www.appsfordevelopment.elimak.com/wp-content/themes/twentyten/7zcub9rd.php 5/7/2012 6:58:21 AM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Here is the problem that I am having with SQLite with encryption extension enabled, and I need help. I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. First of all there some significant rows of a table are missing and another table whose structure is intact has all the rows empty. Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. A simple select on the partially filled table works, for example, but any select statement that involves aggregate functions (count, average, max min, distinct etc...) do not work. Third, I open the file with a text editor and I see that a significant chunk of the file towards the end is filled with the NUL character which is unlike all the other files that are not corrupted. This is the way my C# application works with the files: 1. Start transaction Write to table 1 Write to table 2 Write to table 3 Write around 80,000 rows in table 4 Write data to table 5 based on aggregate data from table 4 Commit transaction Table 4 has thousands of rows missing and table 5 is empty all together. Before someone states the obvious, I can see that there is no surprise that table 5 is empty since aggregate functions are not really working on table 4. But the question is what can lead to this kind of corruption where data write can stop after some thousands of rows and aggregate functions not working. Does anyone have any idea why this is happening? My problem is further complicated by the fact that I am not able to reproduce the problem for the life of me; it only happened to one of our clients, and they are not able to tell me anything that is useful to determine the sequence of events that lead to this file corruption. I do try to handle all exceptions in the application, but when this problem happens no exceptions are thrown by the application or the underlying SQLite library.Thanks for the help. Dawit ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 2 May 2012, at 8:55am, Octet Computech dawitteg...@hotmail.com wrote: I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. Are you calling the SQLite C library functions as described on this page: http://www.sqlite.org/c3ref/funclist.html or are you using some other library to execute SQLite commands ? If you're using another library, which library are you using ? Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. Please download the shell tool for your OS from http://www.sqlite.org/download.html and try accessing the database using that shell tool instead of whatever browser application you're using. Does the shell tool still suggest the same corruption as your browser app ? If there is no shell tool for your OS, you can transfer a copy of the database to a standard computer. The file format is identical on all platforms. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi Simon, thanks for the reply. I am using the C libraries as described in the link that you mentioned. I am not able to use a shell(or any other) [recompiled tool as the version of SQLite that I am using to create has the SEE (encryption) enabled. Whatever tool that I use I have to compile it with the licence and encryption key that is specific to our licence. At any rate, I have compiled a sql browser tool that uses the sql library with the encryption enabled, and I am having problem executing the SQL statemsnts (not all, but aggretate functions). Thanks for your help. From: slav...@bigfraud.org Date: Wed, 2 May 2012 11:57:40 +0100 To: sqlite-users@sqlite.org Subject: Re: [sqlite] (no subject) On 2 May 2012, at 8:55am, Octet Computech dawitteg...@hotmail.com wrote: I have an application that creates and saves some data in a SQLite database. I am using SQLite v 3.6.14, accessing it through the native dll form a C# WPF application. For some reason that I cant figure out am getting a corrupted file with some strange characteristics in some rare instances. Are you calling the SQLite C library functions as described on this page: http://www.sqlite.org/c3ref/funclist.html or are you using some other library to execute SQLite commands ? If you're using another library, which library are you using ? Second I am able to open the file with SQLite browser application (specially compiled for my license of SEE of course), but there are some Sql statements that do not work. Please download the shell tool for your OS from http://www.sqlite.org/download.html and try accessing the database using that shell tool instead of whatever browser application you're using. Does the shell tool still suggest the same corruption as your browser app ? If there is no shell tool for your OS, you can transfer a copy of the database to a standard computer. The file format is identical on all platforms. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
a href=http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html; http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html/a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
a href=http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html; http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html/a ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi All, I am running program in which I have created SQLite DB file using: sqlite3_open_v2() configured it: PRAGMA journal_mode = WAL PRAGMA synchronous=OFF Then created one table into SQLiteDB by using sqlite3_exec() in which I passed SQL command of creating table. After configuration, SQLite file size = 1024bytes Then I inserted 500 records into table using INSERT sql command in sqlite3_exec() SQLite file size = 69632 Then I did deletion of all 500 records from table using DELETE sql query in sqlite3_exec() SQLite file size = 113664 I don't know why file size is increased on deletion ?? Then I ran VACUUM command. SQLite file size remained 113664, it did not get reduced. But when I closed my application working on SQLite DB file, file size is reduced to 3072. I did not understand this behavior. I think as soon as I run VACUUM command, it should reduce file size at that moment. Please share correct behavior that should happen in this scenario shared above. Waiting for your response, I am stuck in my work. -- Thanks and Regards, - Tarun Thakur Module Lead NEC HCL System Technologies, Noida www.nechclst.in ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Wed, Jan 4, 2012 at 12:51 PM, Tarun er.tarun.9...@gmail.com wrote: Waiting for your response, I am stuck in my work. The physical size of the db file is an implementation detail, not a well-published behaviour with concrete reproducible results, and should in no way affect your use of sqlite3 (unless of course you are on a very space-constrained device). -- - stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Wed, 4 Jan 2012 17:21:18 +0530, Tarun er.tarun.9...@gmail.com wrote: Hi All, I am running program in which I have created SQLite DB file using: sqlite3_open_v2() configured it: PRAGMA journal_mode = WAL PRAGMA synchronous=OFF Then created one table into SQLiteDB by using sqlite3_exec() in which I passed SQL command of creating table. After configuration, SQLite file size = 1024bytes Then I inserted 500 records into table using INSERT sql command in sqlite3_exec() SQLite file size = 69632 Then I did deletion of all 500 records from table using DELETE sql query in sqlite3_exec() SQLite file size = 113664 I don't know why file size is increased on deletion ?? Then I ran VACUUM command. SQLite file size remained 113664, it did not get reduced. But when I closed my application working on SQLite DB file, file size is reduced to 3072. I did not understand this behavior. I think as soon as I run VACUUM command, it should reduce file size at that moment. Please share correct behavior that should happen in this scenario shared above. Waiting for your response, I am stuck in my work. Perhaps http://www.sqlite.org/pragma.html#pragma_incremental_vacuum is what you are looking for? -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Thanks, Pratibha Kulkarni ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 31 Aug 2011 at 23:24, Igor Tandetnik itandet...@mvps.org wrote: On 8/31/2011 5:56 PM, Tim Streater wrote: In the above, each database is newly created as shown. What I had forgotten to do was to create the test table in the second database before copying the data. What seems to happen is that, lacking a test table in the test2 database, SQLite appears to assume that I must mean the test table in the test1 database - it tries to copy data from the table into itself and so gets the error above. Yes. This is documented behavior - see http://sqlite.org/lang_attach.html. Thanks for that helpful link. I'm sure I've looked at it before, but didn't register all the details of how it works. Now I see there's a way of referring to the original database as main, I'll use fully qualified table names wherever I have attached databases. Is this reasonable behaviour? I might have expected to have a no such table error. Which part of the documentation might have led you to expect that? No part :-) Sorry for the lack of subject line originally BTW. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Today when trying to copy from one database to another, I had the following error (simplified example below): Second-Mini% sqlite3 test1 sqlite create table test (absid integer primary key, otherfield integer); sqlite insert into test (absid,otherfield) values (null, 10); sqlite insert into test (absid,otherfield) values (null, 20); sqlite select * from test; absid | otherfield --+--- 1 | 10 2 | 20 sqlite ^D Second-Mini% sqlite3 test2 sqlite attach database test1 as src; sqlite insert into test select * from src.test; Error: PRIMARY KEY must be unique sqlite In the above, each database is newly created as shown. What I had forgotten to do was to create the test table in the second database before copying the data. What seems to happen is that, lacking a test table in the test2 database, SQLite appears to assume that I must mean the test table in the test1 database - it tries to copy data from the table into itself and so gets the error above. Is this reasonable behaviour? I might have expected to have a no such table error. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Wed, Aug 31, 2011 at 10:56:00PM +0100, Tim Streater scratched on the wall: In the above, each database is newly created as shown. What I had forgotten to do was to create the test table in the second database before copying the data. What seems to happen is that, lacking a test table in the test2 database, SQLite appears to assume that I must mean the test table in the test1 database - it tries to copy data from the table into itself and so gets the error above. Is this reasonable behaviour? I might have expected to have a no such table error. Reasonable or not, it is the defined behavior: http://sqlite.org/lang_attach.html Tables in an attached database can be referred to using the syntax database-name.table-name. If the name of the table is unique across all attached databases and the main and temp databases, then the database-name prefix is not required. If two or more tables in different databases have the same name and the database-name prefix is not used on a table reference, then the table chosen is the one in the database that was least recently attached. In other words, SQLite will generally search the temp database, the main database, and then all attached databases in index order. This brings up some odd edge cases, as the temp database is searched before the main database, even though the main database has a lower index, but it generally works as expected. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 8/31/2011 5:56 PM, Tim Streater wrote: In the above, each database is newly created as shown. What I had forgotten to do was to create the test table in the second database before copying the data. What seems to happen is that, lacking a test table in the test2 database, SQLite appears to assume that I must mean the test table in the test1 database - it tries to copy data from the table into itself and so gets the error above. Yes. This is documented behavior - see http://sqlite.org/lang_attach.html. Is this reasonable behaviour? I might have expected to have a no such table error. Which part of the documentation might have led you to expect that? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
I am attempting to open an SQLite database on the Mac (OSX Snow Leopard) and am getting an error. This is the code I am using: char DBEnginePath[1000]; strcpy(DBEnginePath, Macintosh HD:Applications:Adobe InDesign CS5:Plug-Ins:WPC_ID:IndexData.db); fprintf(stderr, %s\n, DBEnginePath); // Sends correct path to stderr for verification. Result = sqlite3_open_v2(DBEnginePath, db_ptr, SQLITE_OPEN_READONLY, NULL); // Errors out here. const char *msg = sqlite3_errmsg(db_ptr); fprintf(stderr, Here's the SQLite error message: %s\n, msg); // Sent to stderr: Unable to open database file. I have verified that the file exists on the path described above. What am I doing wrong? TIA! R, John A.M. Darnell Senior Programmer Walsworth Publishing Company Brookfield, MO John may also be reached at johnamdarn...@gmail.commailto:johnamdarn...@gmail.com Trivia question Trivia question: In The Lord of the Rings,Leglolas was a prince among the Silvan Elves. What was the name of his father the King? For extra credit, what was his surname? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Ah, there is a way to write it so that you can have at most one of those constraints where only one makes sense, and not require a specific order of constraints, but it'd require listing all the possible orderings, which would be impractical. So if one wanted to enforce that there's at most one of such constraints then the best pace to do it in in sqlite3AddDefaultValue() and friends, rather than in the grammar. But really, is it worth it? No, certainly not. SQLite parser is fine like it is, no reason to bloat it with much fat just to flag ill constructs, particularily in this cas where there's no harm done. Thanks Nico for looking and for your detailed explanation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Anoher (silly) question about what SQLite considers valid input, again out of mere curiosity. A statement like: CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT 1 DEFAULT 2); doesn't cause any error: SQLite applies only the last constraint of each type, namely COLLATE BINARY and DEFAULT 2 in this case. Is there a rationale for allowing such statements or is that an effect of the 'Lite' nature? (Note: I'm not complaining, just asking.) Is it OK to ignore all but the last constraint of each type when parsing that statement, just like SQLite currently does? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Is there a rationale for allowing such statements or is that an effect of the 'Lite' nature? (Note: I'm not complaining, just asking.) I believe that's an effect of the typeless design. As SQLite doesn't have strict type names for columns it accepts pretty much anything for that. So in your case it thinks that type of first column is CHAR COLLATE NOCASE and for second INTEGER DEFAULT 1. What do those type names mean is up to you. :) Pavel On Thu, May 19, 2011 at 4:40 PM, Jean-Christophe Deschamps j...@antichoc.net wrote: Anoher (silly) question about what SQLite considers valid input, again out of mere curiosity. A statement like: CREATE TABLE a (a CHAR COLLATE NOCASE COLLATE BINARY, b INTEGER DEFAULT 1 DEFAULT 2); doesn't cause any error: SQLite applies only the last constraint of each type, namely COLLATE BINARY and DEFAULT 2 in this case. Is there a rationale for allowing such statements or is that an effect of the 'Lite' nature? (Note: I'm not complaining, just asking.) Is it OK to ignore all but the last constraint of each type when parsing that statement, just like SQLite currently does? ___ 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] (no subject)
On Thu, May 19, 2011 at 4:22 PM, Pavel Ivanov paiva...@gmail.com wrote:
Is there a rationale for allowing such statements or is that an effect
of the 'Lite' nature? (Note: I'm not complaining, just asking.)
I believe that's an effect of the typeless design. As SQLite doesn't
have strict type names for columns it accepts pretty much anything for
that. So in your case it thinks that type of first column is CHAR
COLLATE NOCASE and for second INTEGER DEFAULT 1. What do those type
names mean is up to you. :)
Not really, it seems to be an effect of the way the column constraint
grammar rules are written:
carg ::= CONSTRAINT nm ccons.
carg ::= ccons.
ccons ::= DEFAULT term(X).{sqlite3AddDefaultValue(pParse,X);}
ccons ::= DEFAULT LP expr(X) RP. {sqlite3AddDefaultValue(pParse,X);}
ccons ::= DEFAULT PLUS term(X). {sqlite3AddDefaultValue(pParse,X);}
ccons ::= DEFAULT MINUS(A) term(X). {
...
}
ccons ::= DEFAULT id(X). {
...
}
...
ccons ::= NULL onconf.
ccons ::= NOT NULL onconf(R).{sqlite3AddNotNull(pParse, R);}
ccons ::= PRIMARY KEY sortorder(Z) onconf(R) autoinc(I).
{sqlite3AddPrimaryKey(pParse,0,R,I,Z);}
ccons ::= UNIQUE onconf(R). {sqlite3CreateIndex(pParse,0,0,0,0,R,0,0,0,0);}
ccons ::= CHECK LP expr(X) RP. {sqlite3AddCheckConstraint(pParse,X.pExpr);}
ccons ::= REFERENCES nm(T) idxlist_opt(TA) refargs(R).
{sqlite3CreateForeignKey(pParse,0,T,TA,R);}
ccons ::= defer_subclause(D).{sqlite3DeferForeignKey(pParse,D);}
ccons ::= COLLATE ids(C).{sqlite3AddCollateType(pParse, C);}
However, I'm not sure how to write this such that there can be only
one of those constraints of which there should be just one but without
then imposing ordering on those constraints. IMO there's no need to
fix this.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Thu, May 19, 2011 at 4:28 PM, Nico Williams n...@cryptonector.com wrote: However, I'm not sure how to write this such that there can be only one of those constraints of which there should be just one but without then imposing ordering on those constraints. IMO there's no need to fix this. Ah, there is a way to write it so that you can have at most one of those constraints where only one makes sense, and not require a specific order of constraints, but it'd require listing all the possible orderings, which would be impractical. So if one wanted to enforce that there's at most one of such constraints then the best pace to do it in in sqlite3AddDefaultValue() and friends, rather than in the grammar. But really, is it worth it? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hello All, I have been using SQLite for a couple of years but have never posted to this list before. I am sure my problem is common and am looking for ideas to solve it. I have used SQLite extensively single-threaded with no problems (other than my own!). I am currently working on another project adding SQLite functionality to a multi-threaded environment. Here is my situation. Ubuntu 10.04 64-bit. Have used the SQLite3 library and compiled from source directly into the application. Same results. Multi-threaded compile flag configuration kept as the default and forced with sqlite3_config() to SQLITE_CONFIG_MULTITHREAD with no error reported. Open is via SQLITE_OPEN_READWRITE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE flags. I have tried this with and without shared cache. When I use the above configuration multi-threaded but using only one DB connection in which only one thread is accessing the DB at a time, it works fine for both read and write. I use OS read/write mutex in which all threads must obtain a write lock to get at the DB effectively forcing only one thread accessing the DB at one time. When I allow multiple readers with each thread using a different DB connection (open with the same flags) and each thread having exclusive use of its DB connection (no sharing of connections) and if more than one thread is reading the DB at the same time, the DB becomes locked for writing even when all the reads are finished. The DB is locked, not the OS mutex. There are no DB writes. How can the DB be locked for writes in this situation? I test this with the sqlite3 program and opening the database while the application is running and try to do an insert. I have been working for weeks on this and I feel there must be something simple I am overlooking. Thanks for any help. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
please remove my name jbh...@bluefrog.com from the mailing list. Thank you. 79 AE5IL John Houston www.rebuildinglostchurches.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 21 Oct 2010, at 2:42pm, jbh...@bluefrog.com jbh...@bluefrog.com wrote: please remove my name jbh...@bluefrog.com from the mailing list. Please click on the link included at the end of every message on this list. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
please unuscribed pcvetko...@yahoo.com, you insoult my mail, and i want to unuscribe for your contacts, thanx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Me too, all attempts to unsubscribe have failed. I have put a filter, but still my .pst is becoming big. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Pece Cvetkoski [pcvetko...@yahoo.com] Sent: Saturday, May 22, 2010 10:49 AM To: sqlite-users@sqlite.org Subject: [sqlite] (no subject) please unuscribed pcvetko...@yahoo.com, you insoult my mail, and i want to unuscribe for your contacts, thanx ___ 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] (no subject)
On Sat, 22 May 2010, Prakash Reddy Bande wrote: Me too, all attempts to unsubscribe have failed. On Sat, 22 May 2010, pcvetsko...@yahoo.com wrote: please unuscribed pcvetko...@yahoo.com Did you try this: http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Enter the email address you subscribed with, click 'Unsubscribe or edit options', follow the directions. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
(There are other fields, and the primary key is a combination of 5 columns)
For one code ('C0') I want to implement a rule that if I attempt to insert a
combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are
already in the table. (for non-'C0' codes, this rule doesn't apply.)
select * from assets;
Code Acct1 Acct2
'C0' 'name1' 'name2'
insert into assets values('C0', 'name1', 'name3'); -- ignore
insert into assets values('C0', 'name3', 'name4'); -- succeed
insert into assets values('C0', 'name1', 'name2'); -- ignore
insert into assets values('C1', 'name1', 'name2'); -- succeed
I tried:
insert into assets select 'C0', 'name1', 'name3' where not exists (select 1
from assets where Code='C0' and acct1='name1');
but it went ahead and inserted the row anyway. My hope was that since the where
clause was false, the select would return nothing and thus nothing would be
inserted.
Is there a way I can craft the insert to do this, or do I need to set up a
trigger to ignore the insert? If the latter, how should the trigger read?
Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Something screwed up in your application:
sqlite create table assets (Code Text, Acct1 Text, Acct2 Text);
sqlite insert into assets values ('C0', 'name1', 'name2');
sqlite select * from assets;
C0|name1|name2
sqlite insert into assets select 'C0', 'name1', 'name3' where not
exists (select 1 from assets where Code='C0' and acct1='name1');
sqlite select * from assets;
C0|name1|name2
sqlite
Pavel
On Tue, Oct 27, 2009 at 2:44 PM, David Bicking dbic...@yahoo.com wrote:
I have a table ASSETS with fields (Code Text, Acct1 Text, Acct2 Text).
(There are other fields, and the primary key is a combination of 5 columns)
For one code ('C0') I want to implement a rule that if I attempt to insert a
combination of 'C0'/Acct1/Acct2 it will be ignored if the first two keys are
already in the table. (for non-'C0' codes, this rule doesn't apply.)
select * from assets;
Code Acct1 Acct2
'C0' 'name1' 'name2'
insert into assets values('C0', 'name1', 'name3'); -- ignore
insert into assets values('C0', 'name3', 'name4'); -- succeed
insert into assets values('C0', 'name1', 'name2'); -- ignore
insert into assets values('C1', 'name1', 'name2'); -- succeed
I tried:
insert into assets select 'C0', 'name1', 'name3' where not exists (select 1
from assets where Code='C0' and acct1='name1');
but it went ahead and inserted the row anyway. My hope was that since the
where clause was false, the select would return nothing and thus nothing
would be inserted.
Is there a way I can craft the insert to do this, or do I need to set up a
trigger to ignore the insert? If the latter, how should the trigger read?
Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Indeed, stupid typo in my sql. Sorry for the noise and the lack of a subject
on the subject line in my first email.
David
--- On Tue, 10/27/09, Pavel Ivanov paiva...@gmail.com wrote:
From: Pavel Ivanov paiva...@gmail.com
Subject: Re: [sqlite] (no subject)
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Date: Tuesday, October 27, 2009, 3:26 PM
Something screwed up in your
application:
sqlite create table assets (Code Text, Acct1 Text,
Acct2 Text);
sqlite insert into assets values ('C0', 'name1',
'name2');
sqlite select * from assets;
C0|name1|name2
sqlite insert into assets select 'C0', 'name1', 'name3'
where not
exists (select 1 from assets where Code='C0' and
acct1='name1');
sqlite select * from assets;
C0|name1|name2
sqlite
Pavel
On Tue, Oct 27, 2009 at 2:44 PM, David Bicking dbic...@yahoo.com
wrote:
I have a table ASSETS with fields (Code Text, Acct1
Text, Acct2 Text).
(There are other fields, and the primary key is a
combination of 5 columns)
For one code ('C0') I want to implement a rule that if
I attempt to insert a combination of 'C0'/Acct1/Acct2 it
will be ignored if the first two keys are already in the
table. (for non-'C0' codes, this rule doesn't apply.)
select * from assets;
Code Acct1 Acct2
'C0' 'name1' 'name2'
insert into assets values('C0', 'name1', 'name3');
-- ignore
insert into assets values('C0', 'name3', 'name4');
-- succeed
insert into assets values('C0', 'name1', 'name2');
-- ignore
insert into assets values('C1', 'name1', 'name2');
-- succeed
I tried:
insert into assets select 'C0', 'name1', 'name3' where
not exists (select 1 from assets where Code='C0' and
acct1='name1');
but it went ahead and inserted the row anyway. My hope
was that since the where clause was false, the select would
return nothing and thus nothing would be inserted.
Is there a way I can craft the insert to do this, or
do I need to set up a trigger to ignore the insert? If the
latter, how should the trigger read?
Thanks,
David
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, (SQLite v3.6.17, on ARM with Linux 2.6.18) I'm currently investigating the applicability of SQLite in an embedded application. Part of this would be to store configuration data, for which SQLite seems like a natural fit. I also have a requirement for exchange of realtime data between processes. The data is only stored in RAM (probably on a tmpfs filesystem in Linux, so it can be shared) and, while atomic commits are valuable, it is not important to be robust in the face of reboots. Testing shows that I can commit writes at about 400 transactions per second and read tables at about 1400 queries per second. I have tried changing PRAGMA settings for journal_mode to OFF, synchronous OFF and temp_store to 2. These seem to have a small impact (to about 410 commits/second). PRAGMA journal_mode; gives no output though which makes me suspicious as to whether it is doing anything. The performance so far is perhaps adequate, but since it takes up almost 100% CPU to do it, it will of course tail off when I start to do anything with the data. Are there any other configuration options (per DB) that I should be considering, or other tips that might be relevant? Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi Erick -- I can only help a little with #3. How are your strings stored in your program? If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). That's what I do and all sorts of European and Asian customers don't have any issues with storing and retrieving local strings. If you don't use the wide-char (16) APIs, you would need to explicitly convert your strings to UTF-8 (which is not the same as ASCII) before handing to SQLite. Doug -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of e...@sitadella.com Sent: Thursday, August 20, 2009 4:21 PM To: sqlite-users@sqlite.org Subject: [sqlite] (no subject) Hi guys, This is my first post. I am creating a simple document archiving program for small businesses. I am creating it in a scripting language called www.autohotkey.com. I intend to place the SQLite database file on a network share and use sqlite.dll to access and manipulate it. In general, everything is on a relatively small scale: there will be less than 10 users who will occasionally interact with the database, there will be around 4 tables and based on experience with a similar system, I don't expect a total of more than 5 records after a few years of use. The client computers will be Windows XP or newer and the database file will be located on a network share on a Windows 2000 server or newer. 1. I have read that the file locking mechanisms on older windows networks are not very reliable and that it is not advisable to use SQLite on NFS or network shares. Given the robustness and efficiency of SQLite and the low frequency of use of my application, do I still need to worry about placing the database on a network share? 2. Should I modify any of the default settings to better suit this environment? 3. I am having problems reading and writing international characters to and from the database, specifically the norwegian characters æ, ø and å. If I use sqlite.exe to create a records containing æ, ø or å, I can read the record using sqlite.exe without any problems. Likewise, if I use SQLiteSpy to create a record containing ø, æ or å I can read the record using SQLiteSpy without any problems. But if I create a record in sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't work as expected and the special characters are converted to all sorts of oddball symbols like squares and question marks. I assume this is somehow due to different ASCII/UTF encodings, but how can these problems be avoided? 4. Select commands are case sensitive with æ, ø and å. Is there a simple workaround for this? Regards, Erik ___ 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] (no subject)
If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). Just don't forget that wchar_t on some platforms (reportedly on Linux for example) is 32-bit integer. So conversion between wchar_t and UCS-2 encoding is not always as easy as you can think. Pavel On Fri, Aug 21, 2009 at 9:44 AM, Dougpa...@poweradmin.com wrote: Hi Erick -- I can only help a little with #3. How are your strings stored in your program? If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). That's what I do and all sorts of European and Asian customers don't have any issues with storing and retrieving local strings. If you don't use the wide-char (16) APIs, you would need to explicitly convert your strings to UTF-8 (which is not the same as ASCII) before handing to SQLite. Doug -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of e...@sitadella.com Sent: Thursday, August 20, 2009 4:21 PM To: sqlite-users@sqlite.org Subject: [sqlite] (no subject) Hi guys, This is my first post. I am creating a simple document archiving program for small businesses. I am creating it in a scripting language called www.autohotkey.com. I intend to place the SQLite database file on a network share and use sqlite.dll to access and manipulate it. In general, everything is on a relatively small scale: there will be less than 10 users who will occasionally interact with the database, there will be around 4 tables and based on experience with a similar system, I don't expect a total of more than 5 records after a few years of use. The client computers will be Windows XP or newer and the database file will be located on a network share on a Windows 2000 server or newer. 1. I have read that the file locking mechanisms on older windows networks are not very reliable and that it is not advisable to use SQLite on NFS or network shares. Given the robustness and efficiency of SQLite and the low frequency of use of my application, do I still need to worry about placing the database on a network share? 2. Should I modify any of the default settings to better suit this environment? 3. I am having problems reading and writing international characters to and from the database, specifically the norwegian characters æ, ø and å. If I use sqlite.exe to create a records containing æ, ø or å, I can read the record using sqlite.exe without any problems. Likewise, if I use SQLiteSpy to create a record containing ø, æ or å I can read the record using SQLiteSpy without any problems. But if I create a record in sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't work as expected and the special characters are converted to all sorts of oddball symbols like squares and question marks. I assume this is somehow due to different ASCII/UTF encodings, but how can these problems be avoided? 4. Select commands are case sensitive with æ, ø and å. Is there a simple workaround for this? Regards, Erik ___ 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] (no subject)
Hi Pavel, ´¯¯¯ So conversion between wchar_t and UCS-2 encoding is not always as easy as you can think. `--- Is there really anyone using UCS-2 now or did you mean UTF-16? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Is there really anyone using UCS-2 now or did you mean UTF-16? No, I meant exactly UCS-2. Because UCS-2 guarantees that all symbols are represented by 2 bytes when UTF-16 does not. And I had an understanding that Doug said about this 16-bit guarantee. Also if we're talking about encoding where any character can be represented by a single variable of type wchar_t then we can talk only about UCS-2 or UCS-4, not about UTF-* variants. Though of course someone can talk about UTF-16 keeping in mind and relying on the fact that he will not ever deal with characters not fitting into 2 bytes in UTF-16 encoding and thus he effectively will work with UCS-2. Pavel On Fri, Aug 21, 2009 at 11:33 AM, Jean-Christophe Deschampsj...@q-e-d.org wrote: Hi Pavel, ´¯¯¯ So conversion between wchar_t and UCS-2 encoding is not always as easy as you can think. `--- Is there really anyone using UCS-2 now or did you mean UTF-16? ___ 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] (no subject)
Doug, 3. How are your strings stored in your program? If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). I have no idea how they are stored, but I would guess as standard ASCII. Other community members have provided the interface/wrappers between the scripting language I am using (AutoHotkey) and the SQLite.dll, but I don't think this provides access to the 16 apis you mention. you would need to explicitly convert your strings to UTF-8 (which is not the same as ASCII) before handing to SQLite. Explicitly converting back and forth between ASCII/UTF-8 seems to have solved this problem, thank you!! I hoped this would also solve the issues I am having with case sensitive international characters (Problem #4), but unfortunately not! (select * where LastName like '%ø%'; will not return names with capital Ø in them and vice versa). Pavel, Thank you for your reply too, I appreciate it. I think I will stick with the above mentioned solution. Regards, Erik P.S. Sorry for not having selected a subject/topic, I was too preoccupied with clarifying my post. If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). Just don't forget that wchar_t on some platforms (reportedly on Linux for example) is 32-bit integer. So conversion between wchar_t and UCS-2 encoding is not always as easy as you can think. Pavel On Fri, Aug 21, 2009 at 9:44 AM, Dougpa...@poweradmin.com wrote: Hi Erick -- I can only help a little with #3. How are your strings stored in your program? If they are stored with wchar_t, then using the '16' APIs is probably easiest to use (ie sqlite3_open16, sqlite3_prepare16_v2, etc). That's what I do and all sorts of European and Asian customers don't have any issues with storing and retrieving local strings. If you don't use the wide-char (16) APIs, you would need to explicitly convert your strings to UTF-8 (which is not the same as ASCII) before handing to SQLite. Doug -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of e...@sitadella.com Sent: Thursday, August 20, 2009 4:21 PM To: sqlite-users@sqlite.org Subject: [sqlite] (no subject) Hi guys, This is my first post. I am creating a simple document archiving program for small businesses. I am creating it in a scripting language called www.autohotkey.com. I intend to place the SQLite database file on a network share and use sqlite.dll to access and manipulate it. In general, everything is on a relatively small scale: there will be less than 10 users who will occasionally interact with the database, there will be around 4 tables and based on experience with a similar system, I don't expect a total of more than 5 records after a few years of use. The client computers will be Windows XP or newer and the database file will be located on a network share on a Windows 2000 server or newer. 1. I have read that the file locking mechanisms on older windows networks are not very reliable and that it is not advisable to use SQLite on NFS or network shares. Given the robustness and efficiency of SQLite and the low frequency of use of my application, do I still need to worry about placing the database on a network share? 2. Should I modify any of the default settings to better suit this environment? 3. I am having problems reading and writing international characters to and from the database, specifically the norwegian characters æ, ø and å. If I use sqlite.exe to create a records containing æ, ø or å, I can read the record using sqlite.exe without any problems. Likewise, if I use SQLiteSpy to create a record containing ø, æ or å I can read the record using SQLiteSpy without any problems. But if I create a record in sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't work as expected and the special characters are converted to all sorts of oddball symbols like squares and question marks. I assume this is somehow due to different ASCII/UTF encodings, but how can these problems be avoided? 4. Select commands are case sensitive with æ, ø and å. Is there a simple workaround for this? Regards, Erik ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite
Re: [sqlite] (no subject)
´¯¯¯ No, I meant exactly UCS-2. Because UCS-2 guarantees that all symbols are represented by 2 bytes when UTF-16 does not. And I had an understanding that Doug said about this 16-bit guarantee. Also if we're talking about encoding where any character can be represented by a single variable of type wchar_t then we can talk only about UCS-2 or UCS-4, not about UTF-* variants. Though of course someone can talk about UTF-16 keeping in mind and relying on the fact that he will not ever deal with characters not fitting into 2 bytes in UTF-16 encoding and thus he effectively will work with UCS-2. `--- I didn't see Doug mention anyhow a fixed-length guarantee. Granted, UCS-2 had the considerable advantage of a fixed-length codepoint representation. Nonetheless this doesn't imply that a single character is represented by one codepoint exactly, except if the application declares conformity to ISO 10646-1 (level 1) where only precomposed starter characters are allowed (for instance all combining codepoints are forbidden). It is most probably safe to pass such data to other Unicode application, but the reverse is not true: a perfectly valid Unicode sequence containing only low (plane 0) codes may be ill-formed wrt UCS. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi guys, This is my first post. I am creating a simple document archiving program for small businesses. I am creating it in a scripting language called www.autohotkey.com. I intend to place the SQLite database file on a network share and use sqlite.dll to access and manipulate it. In general, everything is on a relatively small scale: there will be less than 10 users who will occasionally interact with the database, there will be around 4 tables and based on experience with a similar system, I don't expect a total of more than 5 records after a few years of use. The client computers will be Windows XP or newer and the database file will be located on a network share on a Windows 2000 server or newer. 1. I have read that the file locking mechanisms on older windows networks are not very reliable and that it is not advisable to use SQLite on NFS or network shares. Given the robustness and efficiency of SQLite and the low frequency of use of my application, do I still need to worry about placing the database on a network share? 2. Should I modify any of the default settings to better suit this environment? 3. I am having problems reading and writing international characters to and from the database, specifically the norwegian characters æ, ø and å. If I use sqlite.exe to create a records containing æ, ø or å, I can read the record using sqlite.exe without any problems. Likewise, if I use SQLiteSpy to create a record containing ø, æ or å I can read the record using SQLiteSpy without any problems. But if I create a record in sqlite.exe and try to read it with SQLiteSpy or vice versa, it doesn't work as expected and the special characters are converted to all sorts of oddball symbols like squares and question marks. I assume this is somehow due to different ASCII/UTF encodings, but how can these problems be avoided? 4. Select commands are case sensitive with æ, ø and å. Is there a simple workaround for this? Regards, Erik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
-- This message has been scanned for viruses and dangerous content by Pinpoint, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
-- This message has been scanned for viruses and dangerous content by Pinpoint, and is believed to be clean. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
humm -- ArbolOne.org specializes in Custom Web Site Design, Web Site Re Design, Web Site Template Modifications, Web Site Maintenance, Integration of Payment Gateways (API's), Database Applications, Custom Applications and much more. 416.838.2057 arbol...@gmail.com O Allah, make my love for You the most beloved thing to me, and my fear for You the most fearful thing to me, and remove from me all worldly needs and wants by instilling a passion for meeting You, make the coolness of my eyes to worshipping You.” ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hello. I'm using SQLite with a VB wrapper (dhSQLite) for VB6. The following SQL string works fine for putting together a recordset where the DATE field contains only the date of the last day of each month. SQLString = SELECT date(Date,'start of month','+1 month','-1 day') as Date, _ First(Open,ID) as Open, Max(High) as High, _ Min(Low) as Low, Last(Close,ID) as Close FROM [ sTable ] GROUP By Year, Month Each record represents the Open, High, Low, Close price for a complete month, and is represented by the last day of the month. So we have... 2009-01-31 2009-02-28 2009-03-31 2009-04-30 ... It is currently in the -mm-dd format. The line date(Date,'start of month','+1 month','-1 day') as Date is what formats the month date to be the last day of the month. MY PROBLEM: I would like the date format to be either (mm/dd/) or (dd/mm/) depending on the user's system date format. In the US, we use mm/dd/. I'm completely lost on how to do this within the SELECT statement above. Can someone suggest? Thanks. Webbiz ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hello.
I'm using SQLite with a VB wrapper (dhSQLite) for VB6.
The following SQL string works fine for putting together a recordset where
the DATE field contains only the date of the last day of each month.
SQLString = SELECT date(Date,'start of month','+1 month','-1 day') as
Date, _
First(Open,ID) as Open, Max(High) as High, _
Min(Low) as Low, Last(Close,ID) as Close FROM [ sTable
] GROUP By Year, Month
Each record represents the Open, High, Low, Close price for a complete
month, and is represented by the last day of the month.
So we have...
2009-01-31
2009-02-28
2009-03-31
2009-04-30
...
It is currently in the -mm-dd format.
The line date(Date,'start of month','+1 month','-1 day') as Date is what
formats the month date to be the last day of the month.
MY PROBLEM:
I would like the date format to be either (mm/dd/) or (dd/mm/)
depending on the user's system date format.
In the US, we use mm/dd/.
I'm completely lost on how to do this within the SELECT statement above.
Can someone suggest?
Thanks.
Webbiz
select strftime ('%m/%d/%Y', Date,'start of month','+1 month','-1 day') from ...
--
Zaga
You have worked and not worked. Not working is the hardest work of all.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Date: Fri, 19 Jun 2009 14:53:05 -0700 From: Robert Lehr rl...@cadence.com Subject: Re: [sqlite] async io and locks To: sqlite-users@sqlite.org In-Reply-To: 43c62cbb-57db-4d1b-af36-2facf239c...@gmail.com Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes I have never been able to reply to messages w/in the mailing-list's thread. Outlook must be munging the headers to prevent proper threading. I'm trying to force the threading by manipulating the headers manually. I apologize if this reply winds up in the top-level as all of my others have. On Jun 20, 2009, at 12:30 AM, Dan wrote: On Jun 20, 2009, at 12:06 AM, Robert Lehr wrote: I finally got around to reviewing SQLite's asynchronous I/O functionality. http://sqlite.org/asyncvfs.html http://sqlite.org/asyncvfs.html We actually have an C++ wrapper to uses the same concept, a background thread for I/O. You mean you have implemented the callback methods in struct sqlite3_vfs to read and write asynchronously just as we have? Or some other technique? Another technique - the C++ __wrapper__, akin to the numerous other C++ wrappers that are being hacked together around the world. We required asynchrony. We therefore wrote our own wrapper that implements it. W/rt to the locking policy w/ multiple updates, are there design reasons for not releasing and re-acquiring a lock between transactions? That would facilitate higher concurrency albeit it a slightly higher cost than the current implementation. That cost should match the current cost of multiple transactions, though. It's to handle this: BEGIN; UPDATE t1 SET stuff WHERE condition; COMMIT; BEGIN UPDATE t2 SET morestuff WHERE anothercondition; COMMIT; If the SQLite user starts the second transaction before the asynchronous thread has had time to commit the first, it will read the database to figure out the set of rows to apply the morestuff modifications to. Once that has happened, the database file cannot be unlocked before the second transaction is committed. Otherwise, some other client might sneak in while the database was unlocked and modify table t2, changing the set of rows anothercondition selects. Of course, if the asynchronous thread manages to commit the first transaction to disk before the user has time to execute the second, the database file will be unlocked between transactions. Yes, except that I verified that this interpretation is incorrect according to the docs as I interpret them. The relevant part of the docs follows. If an application using asynchronous IO executes transactions in quick succession, other database users may be effectively locked out of the database. This is because when a BEGIN is executed, a database lock is established immediately. Particularly note that the following denotes that the lock on the database file persists beyond the duration of all transactions. But when the corresponding COMMIT or ROLLBACK occurs, the lock is not released until the relevant part of the write-queue has been flushed through. As a result, if a COMMIT is followed by a BEGIN before the write-queue is flushed through, the database is never unlocked,preventing other processes from accessing the database. Thus the async functionality does NOT simply wrap each update query (UPDATE, DELETE) in a transaction. If it did then I would be sending a completely different query, one pertaining to broken semantics (for our purposes) of automatically wrapping my queries in nested transactions which would, again, prevent me from adopting SQLite's baseline async functionality. BTW, it occurred to me as I wrote this reply that the fact the SQLite distinguishes FETCHES from CREATES, UPDATES and DELETES indicates that it is inspecting the queries themselves before deciding to append them to a write-queue or not. It therefore could detect beginnings and ends of transactions. Thus SQLite could enqueue transactions as it currently enqueues write queries. -robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, we are developing an application on android we are using SQLite Database and on phone we are getting SQLiteException:no such table. but, it is working fine on simulator. Can anyone provide any input on this? -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Mon, 1 Jun 2009 04:38:37 -0700 (PDT), Manasi Save manasi.s...@artificialmachines.com wrote: Hi, we are developing an application on android we are using SQLite Database and on phone we are getting SQLiteException:no such table. but, it is working fine on simulator. Can anyone provide any input on this? ASCII versus UTF-8 or UTF-16? -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi All, Can anyone help me out with the command to see the SQLite table defination on command-line SQLite application. -- Thanks and Regards, Manasi Save Artificial Machines Pvt Ltd. manasi.s...@artificialmachines.com Ph:- 9833537392 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi, select * from sqlite_master; Martin PS.: Please provide a subject which summarises your question. Manasi Save schrieb: Hi All, Can anyone help me out with the command to see the SQLite table defination on command-line SQLite application. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk engelsch...@codeswift.com wrote: select * from sqlite_master; Or: .dump tablename -- Samuel 'Shardz' Baldwin - staticfree.info/~samuel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On 26/05/2009 7:58 PM, Samuel Baldwin wrote: On Tue, May 26, 2009 at 4:45 PM, Martin.Engelschalk engelsch...@codeswift.com wrote: select * from sqlite_master; Or: .dump tablename Don't try that with your 100MB database without ensuring that your keyboard interrupt mechanism isn't seized up :-) Perhaps you meant .schema tablename Cheers, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
On Tue, May 26, 2009 at 6:34 PM, John Machin sjmac...@lexicon.net wrote: Don't try that with your 100MB database without ensuring that your keyboard interrupt mechanism isn't seized up :-) Perhaps you meant .schema tablename I did indeed. I even remember going, oh, yeah, don't want dump in this case, but yet my hands still wrote dump. I blame the commies and their fluoridation. -- Samuel 'Shardz' Baldwin - staticfree.info/~samuel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi All, I have the select statement as below sqlite select remoteId, hostName , remoteWXType from remoteWXTable order by hostName; and the output is below: 1|HostName1-T432|2 2|HostName2-T421|2 3|HostName3-XP|2 4|HostName3-XP|2 But I would like the sql statement to return as below: (HostName3 has two remoteId so I want to return as one row but two different remoteId as below) 1|HostName1-T432|2 2|HostName2-T421|2 3,4|HostName3-XP|2 Can you please help to change the sql statement to return the above result set. Thanks, JP ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Daniel,
Thanks for the pointers, but I do not believe that they apply. You are correct
in that the script is more complex and it is hard to post. I tried to post the
relevant portions with explanations.
In terms of connection (self.con) and cursor (self.cur) these are the only
existing attributes and no new connections or cursors are ever created.
These attributes are passed around and used as needed. Either pased as
arguments to external classes or used as self within methods of the clas that
created the connection.
Thanks anyway,
Boris
From: Daniel Watrous dwmaill...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Monday, February 2, 2009 11:55:52 AM
Subject: Re: [sqlite] (no subject)
Hey Boris,
It's a bit hard to follow what you've posted here, but I'm sure that's
because it came from a complicated script. Here are a few things to
keep in mind.
You've make the connection to be EXCLUSIVE, which means that once you
send the first SQL statement to the database the database is
effectively locked for any other connection regardless of what queries
they might send. You imply that when you call getDBConnection you
call sqlite.connect(). If self.con already has a connection this
would create a new connection and you would expect your database to be
locked at that point.
So, you could wrap the sqlite.connect() call in a conditional to see
if self.con is already a valid connection or you could call
self.con.commit() and self.con.close() before calling sqlite.connect
again.
Not sure if this helps. If you have a larger snippet of code you can
send it along.
Daniel
On Sat, Jan 31, 2009 at 11:51 PM, Boris Arloff boris.arl...@yahoo.com wrote:
Daniel,
Apologize for not posting a subject in the original request.
Thanks for offering to help; I do not think is a connection related
problem, but I could be wrong. Here are pertinent code segments:
# Method getDBConnection() is called, which performs the following:
# create a connection property
self.con = sqlite.connect( path + file,timeout=self.dbdefs.DB_TIMEOUT,
detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES,
isolation_level='EXCLUSIVE',check_same_thread=False)
# config some extensions: row_factory, text_factory
self..con.row_factory = sqlite.Row # access by index or col names
self.con.text_factory = sqlite.OptimizedUnicode # uncode/bytestr
#create a general cursor property
self.cur = self.con.cursor()
.
# Then the following method is called to create the tables if they do not
exist:
# create the database structure if does not exist
errBool = not self._DBHandler__createDBTables()
In this case it is a NOOP since the tables do exist; working with an existing
sqlite file.
# Then method deleteData(self, **kwargs) is called, which ends up executing
the deletes
# on each table:
for table in kwargs.keys():
...
cmd, errBool = self.__buildDeleteSQL(table, kwargs[table]) # build a
delete sql statement
...
errBool, err = self.__execSQLCmd(cmd, self.cur)
Method __execSQLCmd(self, cmd, cur), verifies for complete sql statement and:
cur.execute(cmd) # execute sql command
Every DELETE executes correctly.
# Then method insertData(self, **kwargs) is called:
for table in kwargs.keys():
...
for val in kwargs[table].keys():
...
row = kwargs[table][val]
cmd, errBool = self.__buildInsertSQL(table, row) # build insert sql
statements
...
errBool, err = self.__execSQLCmd(cmd, self.cur) # same method called
above to exec sql
# The cur.execute(cmd) succeeds the insert with the first two tables,
fails on the third table, only if
# the record of the first table is not the
last record.
# Delete and insert loops finish thru every db table; whenever errBool is
true, breaks out of the loop
# with an exception:
except err_handler.DBInsertFail:
errBool = True
errMsg += \nInsert statement structure:\n + str(kwargs)
except:
errBool = True #
something else wrong; check args
errMsg += \nGeneral exception at insertData; structure:\n + str(kwargs)
# If error is returned attempts a rollback; else attempts a commit:
if errBool:
try: self.cur.execute('ROLLBACK;') # rollback on error
except: pass
else:
try: self.cur.execute('COMMIT;')
except: pass
The same connection object is maintained throughout; it is never closed
until the program ends. Again the same code is used for successful and
failed results as outlined before.
Thanks,
Boris
From: Daniel Watrous dwmaill...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Saturday, January 31, 2009 5:42:40 PM
Subject: Re: [sqlite] (no subject)
why don't you send us some code
Re: [sqlite] (no subject)
Hey Boris,
It's a bit hard to follow what you've posted here, but I'm sure that's
because it came from a complicated script. Here are a few things to
keep in mind.
You've make the connection to be EXCLUSIVE, which means that once you
send the first SQL statement to the database the database is
effectively locked for any other connection regardless of what queries
they might send. You imply that when you call getDBConnection you
call sqlite.connect(). If self.con already has a connection this
would create a new connection and you would expect your database to be
locked at that point.
So, you could wrap the sqlite.connect() call in a conditional to see
if self.con is already a valid connection or you could call
self.con.commit() and self.con.close() before calling sqlite.connect
again.
Not sure if this helps. If you have a larger snippet of code you can
send it along.
Daniel
On Sat, Jan 31, 2009 at 11:51 PM, Boris Arloff boris.arl...@yahoo.com wrote:
Daniel,
Apologize for not posting a subject in the original request.
Thanks for offering to help; I do not think is a connection related
problem, but I could be wrong. Here are pertinent code segments:
# Method getDBConnection() is called, which performs the following:
# create a connection property
self.con = sqlite.connect( path + file,timeout=self.dbdefs.DB_TIMEOUT,
detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES,
isolation_level='EXCLUSIVE',check_same_thread=False)
# config some extensions: row_factory, text_factory
self.con.row_factory = sqlite.Row# access by index or col names
self.con.text_factory = sqlite.OptimizedUnicode# uncode/bytestr
#create a general cursor property
self.cur = self.con.cursor()
# Then the following method is called to create the tables if they do not
exist:
# create the database structure if does not exist
errBool = not self._DBHandler__createDBTables()
In this case it is a NOOP since the tables do exist; working with an existing
sqlite file.
# Then method deleteData(self, **kwargs) is called, which ends up executing
the deletes
# on each table:
for table in kwargs.keys():
...
cmd, errBool = self.__buildDeleteSQL(table, kwargs[table]) # build a
delete sql statement
...
errBool, err = self.__execSQLCmd(cmd, self.cur)
Method __execSQLCmd(self, cmd, cur), verifies for complete sql statement and:
cur.execute(cmd)# execute sql command
Every DELETE executes correctly.
# Then method insertData(self, **kwargs) is called:
for table in kwargs.keys():
...
for val in kwargs[table].keys():
...
row = kwargs[table][val]
cmd, errBool = self.__buildInsertSQL(table, row) # build insert sql
statements
...
errBool, err = self.__execSQLCmd(cmd, self.cur) # same method called
above to exec sql
# The cur.execute(cmd) succeeds the insert with the first two tables,
fails on the third table, only if
# the record of the first table is not the
last record.
# Delete and insert loops finish thru every db table; whenever errBool is
true, breaks out of the loop
# with an exception:
except err_handler.DBInsertFail:
errBool = True
errMsg += \nInsert statement structure:\n + str(kwargs)
except:
errBool = True#
something else wrong; check args
errMsg += \nGeneral exception at insertData; structure:\n + str(kwargs)
# If error is returned attempts a rollback; else attempts a commit:
if errBool:
try: self.cur.execute('ROLLBACK;')# rollback on error
except: pass
else:
try: self.cur.execute('COMMIT;')
except: pass
The same connection object is maintained throughout; it is never closed
until the program ends. Again the same code is used for successful and
failed results as outlined before.
Thanks,
Boris
From: Daniel Watrous dwmaill...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Saturday, January 31, 2009 5:42:40 PM
Subject: Re: [sqlite] (no subject)
why don't you send us some code. It sounds like you might have an
issue managing your connections.
On Sat, Jan 31, 2009 at 3:09 PM, Boris Arloff boris.arl...@yahoo.com wrote:
Hi,
I am having a small problem with an sqlite3 v3.5.6 database being accessed
from within python 2.5 (import sqlite3 as sqlite).
The database has been working correctly with the exception of the following
issue:
There are 17 tables with the first table being a dataset index table with
three columns (index, name, datetimestamp). All other records have various
columns one being a field that stores the index reference to this first
table. Therefore we can access different datasets.
If we create several datasets each with an incremental index (1,2,3, ...n)
in the first table, we can then delete the last dataset
[sqlite] (no subject)
Hi, I am having a small problem with an sqlite3 v3.5.6 database being accessed from within python 2.5 (import sqlite3 as sqlite). The database has been working correctly with the exception of the following issue: There are 17 tables with the first table being a dataset index table with three columns (index, name, datetimestamp). All other records have various columns one being a field that stores the index reference to this first table. Therefore we can access different datasets. If we create several datasets each with an incremental index (1,2,3, ...n) in the first table, we can then delete the last dataset n and recreate a new one with same name and index number; no problem. The problem results when we delete a dataset less than n (last created). When any dataset 1 thru n-1 is deleted and we attempt to reenter its information, the following happens: 1. Delete all records from all tables where the dataset id is lets say 3 (with n 3) 2. Then insert all data related to dataset id 3. 3.. The data set index table entry is successful with index=3, name set to the original name and new datetimestamp. The second table to be updated gets the its first record inserted correctly, corresponding to this dataset. The third and subsequent tables however fail to accept the insert and sqlite declares an sqlite3.OperationalError with database is locked. This operation using the last set of data entered does not report this same error and completes correctly. For example if we had only three datasets in the case above, then it would have completed successfully. As a special case if we only have one single dataset (i.e. n=1), then we can repeat the operation successfully until we drop dead. We can insert all the original data in every table, delete the data, then recreate the same data, and repeat. As soon as a second dataset is created we can repeat the process with records that pertain to dataset 2 only, any attempts to do the same to dataset 1 causes the database is locked error. Does anyone can help with this? It seems to me that sqlite corrupts the index when it is deleted, hence perhaps we cannot reuse the same index number. Thanks, Boris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
why don't you send us some code. It sounds like you might have an issue managing your connections. On Sat, Jan 31, 2009 at 3:09 PM, Boris Arloff boris.arl...@yahoo.com wrote: Hi, I am having a small problem with an sqlite3 v3.5.6 database being accessed from within python 2.5 (import sqlite3 as sqlite). The database has been working correctly with the exception of the following issue: There are 17 tables with the first table being a dataset index table with three columns (index, name, datetimestamp). All other records have various columns one being a field that stores the index reference to this first table. Therefore we can access different datasets. If we create several datasets each with an incremental index (1,2,3, ...n) in the first table, we can then delete the last dataset n and recreate a new one with same name and index number; no problem. The problem results when we delete a dataset less than n (last created). When any dataset 1 thru n-1 is deleted and we attempt to reenter its information, the following happens: 1. Delete all records from all tables where the dataset id is lets say 3 (with n 3) 2. Then insert all data related to dataset id 3. 3.. The data set index table entry is successful with index=3, name set to the original name and new datetimestamp. The second table to be updated gets the its first record inserted correctly, corresponding to this dataset. The third and subsequent tables however fail to accept the insert and sqlite declares an sqlite3.OperationalError with database is locked. This operation using the last set of data entered does not report this same error and completes correctly. For example if we had only three datasets in the case above, then it would have completed successfully. As a special case if we only have one single dataset (i.e. n=1), then we can repeat the operation successfully until we drop dead. We can insert all the original data in every table, delete the data, then recreate the same data, and repeat. As soon as a second dataset is created we can repeat the process with records that pertain to dataset 2 only, any attempts to do the same to dataset 1 causes the database is locked error. Does anyone can help with this? It seems to me that sqlite corrupts the index when it is deleted, hence perhaps we cannot reuse the same index number. Thanks, Boris ___ 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] (no subject)
Daniel,
Apologize for not posting a subject in the original request.
Thanks for offering to help; I do not think is a connection related
problem, but I could be wrong. Here are pertinent code segments:
# Method getDBConnection() is called, which performs the following:
# create a connection property
self.con = sqlite.connect( path + file,timeout=self.dbdefs.DB_TIMEOUT,
detect_types=sqlite.PARSE_DECLTYPES|sqlite.PARSE_COLNAMES,
isolation_level='EXCLUSIVE',check_same_thread=False)
# config some extensions: row_factory, text_factory
self.con.row_factory = sqlite.Row# access by index or col names
self.con.text_factory = sqlite.OptimizedUnicode# uncode/bytestr
#create a general cursor property
self.cur = self.con.cursor()
# Then the following method is called to create the tables if they do not exist:
# create the database structure if does not exist
errBool = not self._DBHandler__createDBTables()
In this case it is a NOOP since the tables do exist; working with an existing
sqlite file.
# Then method deleteData(self, **kwargs) is called, which ends up executing the
deletes
# on each table:
for table in kwargs.keys():
...
cmd, errBool = self.__buildDeleteSQL(table, kwargs[table]) # build a delete
sql statement
...
errBool, err = self.__execSQLCmd(cmd, self.cur)
Method __execSQLCmd(self, cmd, cur), verifies for complete sql statement and:
cur.execute(cmd)# execute sql command
Every DELETE executes correctly.
# Then method insertData(self, **kwargs) is called:
for table in kwargs.keys():
...
for val in kwargs[table].keys():
...
row = kwargs[table][val]
cmd, errBool = self.__buildInsertSQL(table, row) # build insert sql
statements
...
errBool, err = self.__execSQLCmd(cmd, self.cur) # same method called
above to exec sql
# The cur.execute(cmd) succeeds the insert with the first two tables,
fails on the third table, only if
# the record of the first table is not the
last record.
# Delete and insert loops finish thru every db table; whenever errBool is true,
breaks out of the loop
# with an exception:
except err_handler.DBInsertFail:
errBool = True
errMsg += \nInsert statement structure:\n + str(kwargs)
except:
errBool = True# something
else wrong; check args
errMsg += \nGeneral exception at insertData; structure:\n + str(kwargs)
# If error is returned attempts a rollback; else attempts a commit:
if errBool:
try: self.cur.execute('ROLLBACK;')# rollback on error
except: pass
else:
try: self.cur.execute('COMMIT;')
except: pass
The same connection object is maintained throughout; it is never closed
until the program ends. Again the same code is used for successful and
failed results as outlined before.
Thanks,
Boris
From: Daniel Watrous dwmaill...@gmail.com
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Saturday, January 31, 2009 5:42:40 PM
Subject: Re: [sqlite] (no subject)
why don't you send us some code. It sounds like you might have an
issue managing your connections.
On Sat, Jan 31, 2009 at 3:09 PM, Boris Arloff boris.arl...@yahoo.com wrote:
Hi,
I am having a small problem with an sqlite3 v3.5.6 database being accessed
from within python 2.5 (import sqlite3 as sqlite).
The database has been working correctly with the exception of the following
issue:
There are 17 tables with the first table being a dataset index table with
three columns (index, name, datetimestamp). All other records have various
columns one being a field that stores the index reference to this first
table. Therefore we can access different datasets.
If we create several datasets each with an incremental index (1,2,3, ...n) in
the first table, we can then delete the last dataset n and recreate a new one
with same name and index number; no problem.
The problem results when we delete a dataset less than n (last created).
When any dataset 1 thru n-1 is deleted and we attempt to reenter its
information, the following happens:
1. Delete all records from all tables where the dataset id is lets say 3
(with n 3)
2. Then insert all data related to dataset id 3.
3.. The data set index table entry is successful with index=3, name set to
the original name and new datetimestamp.
The second table to be updated gets the its first record inserted correctly,
corresponding to this dataset.
The third and subsequent tables however fail to accept the insert and sqlite
declares an sqlite3.OperationalError with database is locked.
This operation using the last set of data entered does not report this same
error and completes correctly. For example if we had only three datasets in
the case above, then it would have completed successfully. As a special case
if we only have one
[sqlite] (no subject)
I have problem with executing this query in sqlite. to reconstruct problem
please follow the following steps.
1. create table awal1, akhir1 and hasil1 first.
CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), Product
varchar(60), Location varchar(50), Begin datetime);
INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:20:34');
INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1',
'2007-05-06 10:48:57');
INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:25');
CREATE TABLE akhir1(Code char(5),Level varchar(8),Category varchar(50),Product
varchar(60), Location varchar(50),End datetime);
INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:31:57');
INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1',
'2007-05-06 11:42:46');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2',
'2007-05-06 11:19:21');
INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1',
'2007-05-06 11:19:25');
INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3',
'2007-05-07 17:28:27');
CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar (50),
Product varchar(60), Location varchar(50), Begin datetime, End datetime,
Difference integer, PRIMARY KEY
(Code,Level,Category,Product,Location,Begin,End));
2. then execute this query
insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category,
awal1.Product, awal1.Location, awal1.Begin,akhir1.End,
strftime(%s,akhir1.End)-strftime(%s,awal1.Begin) as Difference from
awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category =
akhir1.Category and awal1.Product = akhir1.Product and awal1.Location =
akhir1.Location and akhir1.End = awal1.Begin group by awal1.Begin,
awal1.Code, awal1.Category, awal1.Product, awal1.Location;
3. check the result
the result that i hope is like this
A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6
11:19:251828
A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6
11:19:211824
A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6
11:31:57683
A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6
11:31:57683
A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6
11:31:570
A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6
11:31:570
A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6
11:42:460
A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7
17:28:272
how to make result like i hope?
thanks for advanced
note : the result that i hope is mysql result's, so i confuse how to do this in
sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 rows
in mysql server, my computer starts not responding temporary, it tooks 100% cpu
usage but less PF or memory usage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Everything you want is on www.sqlite.org. If you cannot handle that you probably do not have a use for Sqlite. Satish wrote: Hi! Can I get some samples to work on sqlite that is how to create a database and inserting ... don' t think again I am joking plz help me.it was bit confusing when iam using sqlite command prompt Regards, Satish -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Friday, November 14, 2008 11:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] (no subject) On 11/13/08, Satish [EMAIL PROTECTED] wrote: Hi! Really I am Serious about this I am very new to database and I want to use sqllite.so I want to know.THE Reason why asked this question is I have seen a video of google tech bytes in that the presenter said that sqllite doesn't support foreign key and alter table.is this True.i want to know based on this I can proceed further. If you are serious, start with reading the documentation on sqlite.org. Check out the list of FAQs, the features, datatypes, syntax, etc. Do some background work more than just watching a video of google tech bytes whatever that is. Then post a well formed question on the list, with a clear subject line, and you will be promptly helped. Until then you will likely get a response equivalent to Wtf, is this a joke? :/ -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Cory Nelson Sent: Friday, November 14, 2008 11:22 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] (no subject) On Thu, Nov 13, 2008 at 9:31 PM, Satish [EMAIL PROTECTED] wrote: Hi! Will sqllite support Foreign Key and ALTER Table Comands .I want to know is there any documentation that helps me to know what are the classes that I can use and program.I use vc++ to connect to database.tell me if there any wrapper classes which I can use and also provide me documentation about those clases and functions present in it and how to use them. Wtf, is this a joke? :/ -- Cory Nelson ___ 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
