Re: [sqlite] querying with BLOB in WHERE clause, possible?
On Fri, 14 Nov 2014 04:59:58 -0700 (MST), bjdodo wrote: >Hi > >Sorry for resurrecting an old thread. I got the where clause working for >queries with byte array arguments based on this discussion. I cannot find >the way to use byte arrays in where clauses for update and delete >statements. I know it is terrible to use byte arrays as query arguments, I >need this because of some 3rd party library problems. I also know that I can >query for the row and do the update using some other column in the where >clause I just find that "hacky". So if there is a way to do this trick for >deletes and updates please let me know. $ sqlite3 test.sqlite SQLite version 3.8.8 2014-11-13 14:30:56 Enter ".help" for usage hints. sqlite> CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL, bl BLOB); sqlite> INSERT INTO T VALUES (1,x'313131'); sqlite> INSERT INTO T VALUES (2,x'323232'); sqlite> UPDATE T SET bl=x'33' WHERE bl=x'323232'; sqlite> SELECT * FROM t; 1|111 2|333 sqlite> DELETE FROM t WHERE bl=x'313131'; sqlite> SELECT * FROM t; 2|333 sqlite> >Thank you, >Jozsef Hope this helps. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Access Temp Tables/Views in background thread
On Mon, 27 Oct 2014 21:53:59 -0700, Balance On I wrote: > Hi All, > > I have an application where I create a temp table/view in one thread. I > then try to perform some operations in background and I don’t have access > to those temp objects. I thought that those were process scoped. Is it > possible to access temp tables/views from across threads? Temp objects are connection scoped. > I am currently doing this in Objective-C and am using blocks to open a > connection, perform operations, then close the connection in the background > on a concurrent dispatch queue. Are you sure the temp objects are accessed via the same connection (db object) as where they were created? > Is there a better multi-threaded approach? I'll leave that to someone more experienced in threading. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Remove me from this
On Mon, 27 Oct 2014 07:20:04 +1100, Isaac Faulkner wrote: > > >I did not sign up for this someone hacked my email stop spamming me please Visit the link below, make it send your password, then login and unsubscribe. Regards, Kees Nuyt >___ >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] Granularity of Locks in sqlite
On Thu, 16 Oct 2014 18:46:19 +0530, Prakash Premkumar wrote: >Hi, > > From what I understand from reading the followig doc: > http://www.sqlite.org/lockingv3.html > sqlite supports only file level locking. Correct. > Is there any attempts to improve > the granularity of locking to table level or row level ? Considering the introduction of http://www.sqlite.org/whentouse.html , SQLite does not target use cases where a finer granularity would be useful, so I wouldn't expect any attempts to change that behaviour. Typically, embedded database libraries don't (have to) care about concurrency at all, so the concurrency that SQLite supports is a lot already. There are enough other products available that target that market. > Thanks a lot. You're welcome. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance issue when copying data from one sqlite database to another
On Wed, 15 Oct 2014 01:26:10 +0200, Kees Nuyt wrote: > http://knuyt.demon.nl/sqlite.org/faq.html#q19 Oops, make that http://www.sqlite.org/faq.html#q19 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance issue when copying data from one sqlite database to another
On Tue, 14 Oct 2014 21:24:40 + (UTC), Pontus Bergsten wrote: >Hi, >I have the following problem setup: [...] > INSERT INTO Dest.TheTable (field1, ..., fieldN) FROM Main.TheTable WHERE time > BETWEEN t1 AND t2 > > Is there any technique that can be used for tuning the performance of sqlite3 > in this scenario? I suspect this is a case of http://knuyt.demon.nl/sqlite.org/faq.html#q19 -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] In python, determine database status
On Wed, 08 Oct 2014 17:45:29 -0400, Mark Halegua wrote: > I have an application I'm using sqlite3 as the database for. The program is > designed to > view and add/edit information. The viewing part is set up and working nicely > using pysqlite > and wxpython. > > What the problem is is from viewing trying to add data. I get a database is > locked error. > > I think my problem is I've opened the database in different modules for > different views of the > data (there are six tables, one of which relates to two/three others, another > which relates to > one other) and therefore the database is locked for anything like adding data. What isolation_level do you use for the connections? https://pysqlite.readthedocs.org/en/latest/sqlite3.html#sqlite3.Connection.isolation_level https://pysqlite.readthedocs.org/en/latest/sqlite3.html#sqlite3-controlling-transactions What journalling mode do you use? Mode WAL might be useful here. http://www.sqlite.org/pragma.html#pragma_journal_mode > I've looked for ways to mitigate this behavior, globalizing the database > access perhaps, or > simply making the add functions a totally separate program (which would be, I > think, a poor > way to do this). It might help if there were wsome way to determine the > database status in > some way which could tell me what state it's in (open for viewing, locked, > unlocked, etc). > > Can anyone help with some clues for me? I hope the hints above help. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] passing error messages to pysqlite
On Sat, 04 Oct 2014 10:44:20 -0400, Mark Halegua wrote: > self.cdata.execute('insert into publishers(publisher_name, >remarks) values("test", "remarks");') Are you sure this is correct? In SQL, string literals are delimited by single quotes. Double quotes can be used around identifiers, like column names. Double quotes are necessary when an identifier happens to be a keyword or contains uncommon characters (e.g. spaces). I know next to nothing about python, but in the pysqlite docs, I find examples like: # Insert a row of data c.execute("""insert into stocks values ('2006-01-05','BUY','RHAT',100,35.14)""") https://pysqlite.readthedocs.org/en/latest/sqlite3.html No idea whether that solves your problem. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Filling struct Select in sqlite
On Mon, 6 Oct 2014 10:28:59 +0530, Prakash Premkumar wrote: >Instead of generating an sql query for a select statement, I would like to >bypass the parser and fill in struct Select by myself based on the data I >have and Pass it to sqlite3Select() function. Can you please give me some >pointers in this direction ? > >I think eliminating parsing would be an optimization. Hi Prakash, Are you aware you can re-use a compiled statement? If you use placeholders instead of literals where values are expected in SQL statements, it suffices to sqlite3_bind() new values, sqlite3_step(), and finally sqlite3_reset() to use the same compiled statement again and again. That way, there is no need to _finalize() and _prepare() every time, and you may not need that optimization at all. -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Vdbe Program Generation
On Fri, 3 Oct 2014 18:39:29 +0530, Prakash Premkumar wrote: > Can you please tell me which function is > sqlite actually generates the Vdbe > program for a give sql string ? http://www.sqlite.org/c3ref/prepare.html -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite max arguments assistance
On Mon, 22 Sep 2014 14:02:57 -0700, Jungle Boogie wrote: > Igor Tandetnik > wrote Mon, 22 Sep 2014 16:34:18 -0400 >> >> Just as I thought. You are storing your values as text - not as numbers - and >> comparing them accordingly, in alphabetical order. > > Sorry, I'm not certain I know the answer to this as I don't generate the data. > Opening the csv file in Excel, I did have to change the transaction_amount > column from "general" to "number". I think your assumptions are correct, > though. I'll see if I can look at the values in the other database. > > I know the data is generated from a different database and a file is created, > portalusemonthly.csv that's sent to a location where I can get it? portalusemonthly.csv probably lists the amounts as .,"999.63", .,"16695.36", and/or the receiving table doesn't define column transaction_amount as a numeric type (REAL, NUMBER, INTEGER and the like). By the way, lacking a currency or decimal type, the best way to represent money amounts is INTEGER, expressed as cents. > Is there anything I can do post export from the other database to change the > values correctly? Have a look at http://sqlite.org/datatype3.html "2.3 Column Affinity Behavior Example" -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: Aggregate functions in subqueries
On Sun, 14 Sep 2014 00:18:34 -0400, Lea Verou wrote: > Per the 3.7.11 changelog [1], queries of the form > SELECT max(x), y FROM table return the value of y > from the same row that contains the maximum x value. > However, this: > select y from (SELECT max(x), y FROM table); > would not return the same y rows. This would work as expected: > select m, y from (SELECT max(x) as m, y FROM table); > > [1]: http://www.sqlite.org/changes.html#version_3_7_11 Confirmed. However, SELECT max(x), y FROM t1 is not proper SQL, as a non-aggregate expression (in this case column y) is used that doesn't appear in a GROUP BY clause. SQLite allows it, and in the simplest of statements it returns a value of y from one of the rows that matches x=max(x), as promised. Apparently, in SELECT y FROM (SELECT max(x), y FROM t1); it is optimized out somehow. Workaround: SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1); which may return multiple rows, so you'd have to use LIMIT 1 or max(y) or min(y), whichever is most appropriate for your use case. I agree that the result is quite unexpected. Test script and results on 3.8.7 2014-09-06 17:06:13 ad7063aa1a0db32cdbe71815545b2edca57d3bcc and 3.8.7 2014-09-12 20:30:59 b332a84d5154f70f3197537df4af243eaebbb011: CREATE TABLE t1 (x INTEGER, y INTEGER PRIMARY KEY); INSERT INTO t1 (x,y) VALUES (1,1); INSERT INTO t1 (x,y) VALUES (2,2); INSERT INTO t1 (x,y) VALUES (3,3); INSERT INTO t1 (x,y) VALUES (3,4); INSERT INTO t1 (x,y) VALUES (2,5); SELECT * FROM t1; 1 1 2 2 3 3 3 4 2 5 SELECT max(x), y FROM t1; 3 3 SELECT y FROM (SELECT max(x), y FROM t1); 5 SELECT m, y FROM (SELECT max(x) as m, y FROM t1); 3 3 SELECT y FROM t1 WHERE x=(SELECT max(x) FROM t1); 3 4 -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite db is locked on network drive
On Sat, 13 Sep 2014 00:55:23 +0200, Kees Nuyt wrote: > It works better on your local filesystem. Perhaps you can configure (a partition on) the Seagate GoFlex as an iSCSI target and configure an iSCSI initiator on the PC ? A quick websearch on "seagate goflex iscsi" suggests that's possible. If so, locking should work. (warning: untested) -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite db is locked on network drive
On Fri, 12 Sep 2014 18:33:46 -0400, Mark Halegua wrote: >On Friday, September 12, 2014 09:43:39 PM Simon Slavin wrote: >> On 12 Sep 2014, at 9:15pm, Mark Halegua wrote: >> > On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote: >> >> On 12 Sep 2014, at 5:28pm, Mark Halegua wrote: >> >>> The db file is stored on a seagate goflex device as my kinda file >> >>> server. >> >>> on that device I can open the db to read but not to write to. >> >> >> >> Do you get an error message ? Or do your changes just disappear ? >> >> >> >> If it's an error message, what step generates it and what does it say ? >> > >> > when I try an insert I get this: >> > >> > Error: database is locked >> >> Put a text file on you goflex device. >> >> Then access your text file with a text editor and try to make a change in >> it. Will it let you save the change you make ? >> >> Simon. > >Yes. The only time I have a problem with files is the sqlite db > >Mark The Seagate goflex appears to be a NAS (Network Attached Storage, you probably access it using CIFS/SMB/SAMBA (the protocol microsoft uses for file and printer sharing). If the NAS doesn't implement the protocol 100% perfect, locking issues will occur, which can lead to corruption. See also: http://sqlite.org/lockingv3.html#how_to_corrupt "... On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem. " It works better on your local filesystem. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reinstalling My Build Environment
On Fri, 12 Sep 2014 23:23:39 +0100, Simon Slavin wrote: > >On 12 Sep 2014, at 10:33pm, Kees Nuyt wrote: > >> Sure, it's all available at >> https://system.data.sqlite.org/ >> >> I think the material there covers most of the questions. > >So the appropriate thing to do is post a pointer to > ><https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki> > >? That answers the questions about what to install, where to find it and how >to install it ? Yes, and also https://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki Actually, a pointer to https://system.data.sqlite.org/ will usually suffice, they will find their way in the menu. People asking about developing with, and deploying System.Data.SQLite should be familiar with the wording, which is quite specific for MS Windows .NET development and the Visual Studio IDE. Problem is, they sometimes aren't (and neither am I, so those questions usually don't trigger me). -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On Fri, 12 Sep 2014 17:48:27 -0400, Eric Rubin-Smith wrote: >Looking at the sqlite web site and mailing lists shows that the SQLite team >has taken a stab at answering the question, "is it faster to read a blob >out of sqlite or out of a file?". See the links below. > >Does the team have analogous guidance regarding write speeds? Good question, but I don't have an immediate answer. Perhaps you can set up a quick benchmark with sqlar: http://www.sqlite.org/sqlar Use the -n option to disable compression. > I'm also interested in the delete path. It seems like SQLite doesn't have > much hope of competing with a native filesystem unlink(2) call to delete a > file that is many gigabytes long, for example. Is that right? sqlar yields a sqlite3 database, so you can benchmark DELETE performance using SQL. Optimization opportunity: printf "PRAGMA page_size=bytes; VACUUM;\n" \ | sqlite3 benchmark.sqlar >Eric > >References: > >http://sqlite.1065341.n5.nabble.com/Internal-v-External-BLOBs-td15515.html >http://www.sqlite.org/intern-v-extern-blob.html -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reinstalling My Build Environment
On Fri, 12 Sep 2014 16:41:40 +0100, Simon Slavin wrote: > On 12 Sep 2014, at 3:44pm, > Drago, William @ MWG - NARDAEAST > wrote: > > It seems like a lot of people are unaware of > > System.Data.Sqlite. This is understandable since most people on > > this list are C programmers not .NET/C#/VB developers, but this > > is the support list for System.Data.Sqlite as well as SQLite, > > isn't it? > All true. > > Shouldn't we all be aware that anyone asking for help with > > Visual Studio and/or EF6 and/or referencing file names like > > sqlite-netFx451-setup-bundle-x86-2013-1.0.92.0.exe, is asking > > about System.Data.Sqlite? > How would I know that ? I've never programmed for Visual Studio > or for .NET. I don't really know what they are (and don't need > to). I don't know what names like 'System.Data.Something' and > 'netFx451' indicate. For all I know they're something about > Android. Well, if it doesn't ring the bell on your front door, why answer? Currently SQLite has created a very diverse landscape of solutions, nobody expects we can cover them all by ourselves. > There seems to be a confusing multiplicity of possible setups > including things like Visual Studio and Entity Framework. And > they don't all end up needing the same SQLite > package/library/executable/bundle from the same download site. > While numerous people on this list can advise on PRAGMAs or a > good way to phrase something in SQL, to answer questions like > that you really need someone who actually uses that platform, > and sometimes even someone who uses that version of that > platform. > > So people post questions like this, but their questions don't > get answered as quickly as other questions posted on the same > day. And, reasonably, they think they're being ignored or their > question didn't get posted properly or something. They'll get used to the turn-around time of mailing lists soon enough, if they are bothered to invest some time themselves. If they really need immediate answers, they can buy a support contract. Eventually, when nobody else in the mailing list provides a valid answer, Joe Mistachkin (member of the SQLite development team) usually picks it up. http://sqlite.org/crew.html > Would it be possible to write a document for people looking for > the right thing to download and install for their version of > .NET or the Visual IDE or whatever it is ? Perhaps with a text- > based decision table to let readers figure out which one they > want ? And an explanation like the one I keep seeing about > whether you just need runtime support or whether you also want > setup/viewing integrated into the IDE. Then if a question > includes the words 'Visual Studio' or .NET, anyone can respond > with a pointer to that document as a first approximation to an > answer, whether they understand the question or not. > > If such document already exists, could someone post a pointer ? Sure, it's all available at https://system.data.sqlite.org/ I think the material there covers most of the questions. -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] presentation about ordering and atomicity of filesystems
On Thu, 11 Sep 2014 23:49:22 +0200, Kees Nuyt wrote: > Today I bumped into a presentation about ordering and atomicity > of filesystems that might interest you. > > https://www.youtube.com/watch?v=YvchhB1-Aws Compliments for sqlite at 43:20 .. 43:59 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] presentation about ordering and atomicity of filesystems
Hi all, Today I bumped into a presentation about ordering and atomicity of filesystems that might interest you. https://www.youtube.com/watch?v=YvchhB1-Aws The Application/Storage Interface: After All These Years, We're Still Doing It Wrong Remzi Arpaci-Dusseau, University of Wisconsin—Madison Talk at usenix 2014 Published on Sep 4, 2014 by USENIX Association Videos Somewhat related to the article drh recently wrote about using sqlite as an application data store. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] After ANALYZE all has become slow
On Fri, 8 Aug 2014 11:14:29 +0200, Giuseppe Costanzi wrote: >>>> sqlite3.sqlite_version >'3.5.9' Version 3.5.9 is more than 6 years old. A lot of optimizations were introduced since that version. Please upgrade and try again. -- Groet, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3 has stopped working
On Sun, 27 Jul 2014 23:16:05 -0700 (PDT), suparna wrote: >I'm doing my project by using sqlite with code block.. >and it create connection properly with sqlite... >but while runing,I got the following message and do not know what I am to do >: >"sqltext.exe(my database file name) has stopped working >A problem caused the progrm to stop working correctly. >Windows will close the progrm and notify you if a solution is available..." >:( :( Without seeing your code, I can only suggest that your program is dereferencing an invalid pointer, possibly by a programming error or some form of memory (heap) corruption. Try to run the program with a debugger to pinpoint the error. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Creating fiels
On Fri, 18 Jul 2014 21:56:31 +0100, Rui Fernandes wrote: > I already know how to import a csv file, and save it in SQLite format. > But how can I define the time of variable in the fields since it assumes > all of them are TEXT? > > Where can I find this information? And do I create this before the input - > how? To do that, you have to create the table beforehand, with the correct column definitions. After that, .import the file, without the header line in this case. http://www.sqlite.org/mark/cli.html?For+the+second+case*table.#csv >Kind regards, > >Miguel Fernandes -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Setting boundaries in a search
On Thu, 17 Jul 2014 13:06:36 +0530, Sky Meena wrote: > how to set a password to open a sqlite db in sqlite browser Answered in a different thread 2 minutes ago. -- Regards, Kees Nuyt >On Thu, Jul 17, 2014 at 7:18 AM, RSmith wrote: > >> >> On 2014/07/16 14:55, Rob Willett wrote: >> >> I’ll second what Simon says, I use the very same technique for a table >>> with 4M+ records in and its so fast I thought I had an error and looked for >>> bugs in my code. I >assumed (incorrectly) that it would be very slow, It >>> isn’t. >>> >> >> In a similar fashion I had made this system for basically loading CSV >> files into an SQLite DB, then running all kinds of rules on it (which I >> could make with a designer) such as search-replace, substitute column >> values, do checks, delete rows with empty values in a certain column, etc. >> etc. and then finally export it to a CSV again, all basically streamlining >> a datafeed alteration process into a one-click thing. The viewer I used was >> based on a method discussed in another thread where the virtual view would >> get actual data only for items in the visible field by primary key. I >> tested it with some CSV tables over 250MB big resulting in 10mil+ rows, and >> here was my surprise, whether I looked at the top of the list, or the >> bottom, or anywhere else, the data retrieval was instantaneous - retrieving >> a page worth of records at whatever speed I can scroll the vertical >> scrollbar - not a single slow-down as I got further down or indeed any >> other slowness. I have come to expect great performance from SQLite as a >> standard, but I am still often surprised at just how quick it can be. >> >> (btw: This app is freely shared if anyone needs something of the kind or >> fancy testing the above, just mail me) >> >> ...// I have come to realise that the people who answer here are real >>> experts, I will not embarrass them by naming names//... >>> >> >> Oh don't worry, we know exactly who you mean... ;) >> >> It's Igor right? >> >> We all want to be like Igor when we grow up... *sighs dreamily* >> >> >> >> >> >> ___ >> 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] sqlite db transfer
On Thu, 17 Jul 2014 15:17:07 +0530, Sky Meena wrote: > i sent a sqlite db from server to client using c program .. in this how i > set a password for the db to open in sqlite browser... In short: you can't. SQLite does not implement SQL access control (GRANT/REVOKE). The only protection you can rely on are the access control features of the filesystem the database resides in. You could buy a licence for the SQLite encryption extension (named SEE), <http://www.hwaci.com/sw/sqlite/see.html> , which enable you to encrypt the database, but I doubt sqlite browser would be able to open an encrypted SQLite database. Hope this helps -- Regards, Kees Nuyt >On Fri, May 9, 2014 at 6:05 PM, Simon Slavin wrote: > >> >> On 9 May 2014, at 1:23pm, Sky Meena wrote: >> >> > i working in server client... c program. i need to transfer db from >> server >> > to client. in udp socket .. i to send a db. >> >> SQLite does not involve a server or a client. All processing and access >> of the database is done inside your application. It does not communicate >> over IP, or use a socket, or anything like that. >> >> You can write your own server if you want, and many people have. Or if >> all you want is to send an entire database, you can use any method which >> would send a text file from one computer to another. For SQLite, if no >> program is accessing a database then the database is just one file. >> >> Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] capturing and testing a hot journal
On Mon, 14 Jul 2014 12:09:46 +0100, Simon Slavin wrote: > On 14 Jul 2014, at 11:19am, Kees Nuyt wrote: > > > On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin > > wrote: > > > >> I had to explain to some users that a database > >> change is not 'safe' until the database is closed. > > > > As far as I know, a database change is safe after a successfull COMMIT. > > Commit also releases locks. > > That's what the documentation says, and it's a safe way to > operate if all your access to the file is via one API. > Unfortunately, the drivers for many storage media lie to the > operating system and do not flush changes to disk when told to. > On a test system running Windows 98, using a C program writing a > text file, I was able to prove that doing all the locking and > flushing the documentation required still did not properly > update the file on disk. However, the file was always updated > by a few seconds after the file was closed so I have used that > as a yardstick ever since. Aha, I see. Yes, ill-behaving filesystems can do that. The question is whether experiences on Windows 98 are still relevant for rules of thumb in 2014. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Brief intro to SQLite in Python
On Sun, 13 Jul 2014 22:05:29 +0100, Simon Slavin wrote: > A Python programmer was doing a lot of data processing in Python > and wondered whether SQLite could speed it up: > > <http://sebastianraschka.com/Articles/sqlite3_database.html> > > The article and the database needs of the programmer are rather > simple, and the results are unremarkable (fast DBMS is fast). > But the examples of SQLite-via-Python code are clear and well > written and may be useful for Python users who want to learn > SQLite. Especially for the triple-quoting needed for > .execute(). His follow-up article is nice too: <http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html> -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] capturing and testing a hot journal
On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin wrote: > On 12 Jul 2014, at 9:37am, Charles Parnot wrote: > > - the journal file is actually not “hot” and I misunderstood > the conditions that make it hot > > That one. The files on disk aren't 'hot' (as I think you mean > it) while you're in a transaction. > > Your file system is not pushing journal changes at the file > level. It doesn't need to do that while a transaction is open. > since while the transaction is open, the database is locked so > nothing else can use it anyway, and if your app crashes the > whole transaction will be ignored. > > SQLite could be written to push transactions to the journal file > on each change, but that would involve lots of writing to disk, > so it would make SQLite slower, and for no gain. > > > [snip] The test case I am generating is just for a simple edge > case of our Dropbox-based syncing > > > Yes, DropBox can be a problem for open SQLite databases. As a > file level duplication system which does not understand locks, > there's no good way to make DropBox work with open SQLite > databases, or as a mediator for concurrent multi-user changes to > a database. > I had to explain to some users that a database > change is not 'safe' until the database is closed. As far as I know, a database change is safe after a successfull COMMIT. Commit also releases locks. > One thing that's worth testing is to make sure that recovery > after crashes always yields a database with either pre- or post- > transaction data rather than something corrupt which can't be > opened. I don't know much about how DropBox works. Could it > perhaps end up with a database file from one computer but > journal file from another ? Indeed, or a journal file and a database file of different points in time. One could expect dropbox to respect locks, but it doesn't seem to do that. It also soesn't seem to synchronize a directory in an atomic fashion, which would be necessary to maintain consistency for sqlite or any other software that works on time-coordinated sets of files. In my opinion dropbox should not be used on directories with SQLite databases at all. It would be better to only allow dropbox access to directories with backups, and an application level synchronisation/recovery mechanism to reconstruct the main database from the backup when needed. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hello, I would like to know the difference between sqlite2 and sqlite3!
Oops, I should have linked to the official site, not my local copy. Corrected below. === > Hello, I would like to know the difference > between sqlite2 and sqlite3!? sqlite2 is deprecated since 2004, not maintained since 2005 and should only be used to convert legacy sqlite2 databases to sqlite3. sqlite3 is current and actively maintained / optimized. Differences (as perceived almost 10 years ago): http://www.sqlite.org/version3.html Release history: http://www.sqlite.org/changes.html Hope this helps. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hello, I would like to know the difference between sqlite2 and sqlite3!
On Sun, 13 Jul 2014 07:23:08 +0800, "ddy is super man" wrote: > Hello, I would like to know the difference > between sqlite2 and sqlite3!? sqlite2 is deprecated since 2004, not maintained since 2005 and should only be used to convert legacy sqlite2 databases to sqlite3. sqlite3 is current and actively maintained / optimized. Differences (as perceived almost 10 years ago): http://knuyt.demon.nl/sqlite.org/version3.html Release history: http://knuyt.demon.nl/sqlite.org/changes.html Hope this helps. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? sqlite3_column_name returns name of PK for rowid
On Thu, 03 Jul 2014 23:22:46 +0200, Martin Kleusberg wrote: > I've encountered some odd behaviour when using the sqlite3_column_name > function. Here's my attempt to build a minimal example. > > Part 1: The database. Note that the table is not a 'without rowid' table and > has a primary key: > > $ sqlite3 test.db > create table test(id integer primary key, bla integer); > insert into test(bla) values(5); > .quit > > Part 2: A C program using the sqlite3_column_name function > to determine the column names of the result set of a statement: [snip] > "SELECT rowid,* FROM test", [snip] > The output of this program is: > column #1: id > column #2: id > column #3: bla > > However, I'd have expected the following: > column #1: rowid > column #2: id > column #3: bla > > I've tested this using the latest version of SQLite, i.e. 3.8.5, and did a > (admittedly very quick) search but couldn't find anything. > > If there's any required information I didn't provide or any > sane explanation please let me know :) My explanation: By stating id INTEGER PRIMARY KEY, you aliased ROWID to id. In this case there is no separate ROWID column, id takes its place. Whenever you use ROWID for this table, you will get the value of id, in the internal schema stucture, only one name can be stored, and a separate (implicit, not aliased) ROWID column will be hidden, yet accessible. Without INTEGER PRIMARY KEY, the ROWID is still there, but it is hidden. Check it with PRAGMA table_info(test); You will see the id column, never a ROWID column. I think this behaviour is consistent with http://sqlite.org/lang_createtable.html#rowid , and the naming you observe reminds you of the role of id in the table. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with many connections
On Wed, 02 Jul 2014 17:03:43 +0100, Grzegorz Sikorski wrote: > Hi Hick, > > You were right I was not finalizing statements and this prevented close > to complete. When I modified my code to finalize all statements before > close it works fine. > > However I was always checking return status from sqlite_close_v2 call > and it was always 0. This is fine according to the documentation: > > "If sqlite3_close_v2() is called on a database connection > <http://www.sqlite.org/c3ref/sqlite3.html> that still has outstanding > prepared statements <http://www.sqlite.org/c3ref/stmt.html>, BLOB > handles <http://www.sqlite.org/c3ref/blob.html>, and/or sqlite3_backup > <http://www.sqlite.org/c3ref/backup.html> objects then it returns > SQLITE_OK but the deallocation of resources is deferred until all > prepared statements <http://www.sqlite.org/c3ref/stmt.html>, BLOB > handles <http://www.sqlite.org/c3ref/blob.html>, and sqlite3_backup > <http://www.sqlite.org/c3ref/backup.html> objects are also destroyed." > > I admit I had missed this sentence, but to be honest, it seems to be > very confusing behaviour. Perhaps http://www.sqlite.org/c3ref/next_stmt.html is useful here. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Porting SQLite to Nurit OS ver
On Wed, 2 Jul 2014 12:02:01 +0100, Simon Slavin wrote: > > Thank you for your precise and useful description of what is happening. > > Unfortunately this list does not allow attachments, but can you paste > a copy of the final error, the one about the register map, > to one of your posts ? Make sure we can see both the full > error text and which line of code it's complaining about. The error report was included, you just have to scroll down a bit more. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On Sat, 24 May 2014 22:24:01 +0200, RSmith wrote: > INSERT OR UPDATE TeamPersonTable (tpId,teamId,personId,orderId) > VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); That will raise a syntax error. Make it: INSERT OR REPLACE INTO TeamPersonTable (tpId,teamId,personId,orderId) VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); or even: REPLACE INTO TeamPersonTable (tpId,teamId,personId,orderId) VALUES (1,3,4,0),(2,3,5,1),(3,7,4,1),(4,7,5,0); http://www.sqlite.org/lang_insert.html -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple Select from IN - from a newbie.
On Sat, 24 May 2014 03:08:32 -0700 (PDT), Humblebee wrote: >I'm making good headway with the conversion from the use of strings >into a normalized database. With the kind help of everyone on the >list, I can retrieve the data from the new structure. > >I am now at the stage of saving the data to the new structure. Each >time I do an update, I need to update multiple rows in the >TeamPersonTable with a new orderId and TeamId. I looked up the SQLite >documentation and found that Insert works on multiple rows. Can I >update multiple rows with one statement ? Not in the same way as : INSERT (columnlist) VALUES (valuelist1) , (valuelist2) ... , (valuelistN); You can UPDATE multiple rows, but all of them will get the same column values as specified in SET col1=val1,col2=val2,...,colN=valN . The WHERE cluase determines which rows are updated. >Cheers. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggestion for shell .IMPORT improvement
On Sun, 18 May 2014 01:06:42 +0300, wrote: > PS. By the way, any progress on the shell enhancement to load/save blobs? > This will make the shell capable of handling any database without the need > for external programs. I think SQLite File Archiver comes very close: http://www.sqlite.org/sar/doc/trunk/README.md -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] duplicate row in sqlite3 database
On Thu, 8 May 2014 14:44:19 +0530, techi eth wrote: >Hi, > >SQlite3 have any method where it can avoid adding duplicate row or throwing >error status code on duplication., > >Techi- What Simon said; and you can also define the behaviour in the table definition in an ON CONFLICT clause: http://sqlite.org/lang_conflict.html -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] foreign keys
On Fri, 25 Apr 2014 09:22:20 +0100 (BST), olivier Ménard wrote: > Hello > > I tried, (with sqlite3 command line) : > > create table T(a primary key not null); > create table U(a references T not null); Shouldn't that be: create table U(a not null references T(a)); ? > insert into U values(4) > > and it works but i don't want to, because the value 4 is not in T. > I've tried PRAGMA foreign_keys first, but it's the same. Old version perhaps? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BLOBs and NULLs
On Mon, 21 Apr 2014 13:30:15 +, "Drago, William @ MWG - NARDAEAST" wrote: > Should I split this table up into smaller tables to > eliminate the NULLs (e.g. use one table each for IL, > Phase, RL, Isolation)? Adding to what Richard said: (3) NULLs are not a problem by themselves, they take hardly any storage at all, ust the type indicator that every every row has for every column. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] printf function is shown in docs but not found when I try it
n Sat, 19 Apr 2014 10:38:01 +0100, c...@isbd.net wrote: >Simon Slavin wrote: >> >> On 18 Apr 2014, at 2:49pm, c...@isbd.net wrote: >> >> > Xubuntu 13.10 (which I'm currently using) has sqlite 3.7.17. >> > >> > Xubuntu 14.04 has sqlite 3.8.2, I'm not sure if they're likely to move >> > to anything newer during its lifetime. >> >> If you're writing C or C++ code then you don't need to use something >> supplied >> with your OS. Just download current amalgamation version files and include >> the .h and .c files in your application source code. >> >No, I'm not running from within C/C++, I'm using sqlite3 databases >from my wiki (DokuWiki) so it's being accessed using PHP's PDO >interface. Can I update *that* to get printf() available within SQL >queries? PDO used to have a variant pdo_sqlite3_external (or similar name), which linked dynamically to a standard sqlite3 library, but that wasn't supported on all platforms (don't remember which though). It was a great solution, because you could upgrade sqlite3 independent from the PHP release cycle. I'm afraid it is totally extinct by now, but you might be lucky. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_analyzer source code
On Tue, 15 Apr 2014 22:34:40 +0200, Marco Bambini wrote: > If I remember correctly, sqlite3_analyzer is a python script... > I am not able to find its source code since in the download > page there is a binary app only. > > Any help? The sources are available in the online fossil repository, http://www.sqlite.org/cgi/src/doc/trunk/README.md The Makefile will tell you what sqlite3_analyzer is made of. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is there a way to load a blob from the shell?
On Sun, 6 Apr 2014 21:43:27 -0400, Richard Hipp wrote: > At http://www.sqlite.org/sar there is a utility program that generates an > "SQLite Archive", similar to a ZIP archive but using SQLite as the file > format instead of the ZIP format. Wonderful, thanks! [Open]Solaris users may want to add: CC += -D_XOPEN_SOURCE=500 -O2 to the Makefile. For completeness, I also added target: clean: -rm sar sqlite3.o -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Use of AUTOINCREMENT
On Fri, 28 Mar 2014 12:56:59 +0100, "Kleiner Werner" wrote: > > >Hello >I saw some discussions here about using the word "AUTOINCREMENT" for a primary >key or not. > >Sometimes the meaning is "INTEGER PRIMARY KEY" is enough, because this will >also make an auto increment id. > >Can someone give me a clarification if "AUTOINCREMENT" is needed or not? >In the FAQ I can read this; >" The new key will be unique over all keys currently in the table, > but it might overlap with keys that have been previously deleted > from the table." > > Does this mean, if there are 10 rows with id 1 -10 > and I delete row with id 5, > then the next insert will be ID = 5? > Or is next ID = 11? The next ID will usually be 11, but it is not guaranteed. One day, it could suddenly be 5. > I understand that if I need absolutely unique IDs over > the lifetime I need to use AUTOINCREMENT. That's correct? That's correct. > And with AUTOINCREMENT the last IDs will be stored in a > table "sqlite_sequence"? Yes > Are there sow disatvantages of using AUTOINCREMENT , > like SQLite is slower or something else? It has to do a bit more, so it will be slightly slower, but not much. The sequence table is small and will be in the cache most of the time. If the sequence is updated, it will have to be flushed to disk at COMMIT TRANSACTION. I wouldn't worry about it unless you are in a very fast real-time environment. Premature optimization is the root of all evil. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date in gridview visualized in YYYY-MM-DD format:
On Wed, 19 Mar 2014 14:39:39 -0700 (PDT), Stefano Ravagni wrote: >Hello, i'm new in SQLite develop... > >i'n a gridview (.NET) i visualize the data type in -MM-DD format > >Using others database i ever see data in format which follow culture >setting (italian in this case)... but not in SQLite.. > >How could i solve ? SQLite has no type "date". If you see "-MM-DD", it is stored as type TEXT, in exactly that format (and that is always correct everywhere, because ISO8601 is the international standard). You can use one of the date formatting functions in your select statement to change the presentation. http://sqlite.org/lang_datefunc.html -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] very slow fdsync() calls
On Thu, 20 Mar 2014 18:05:48 +1300, Jono Poff wrote: > and I can't easily > change the page size of the db either. You may have other reasons why you can't do it easily, but PRAGMA page_size=newpagesize; VACUUM; will convert to newpagesize. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite3_exec
On Fri, 14 Mar 2014 07:56:57 -0700 (PDT), khaloud1987 wrote: > the problem arises when I am trying to erase lines and I have a power failure > so that it deletes rows but sometimes I have a line that is deleted from the > first table and not from the second. > (yes i have a table with this name table2 and i can't read the return value > when it fails caused by the power failure) So, after a power failure, you want both deletes to have succeeded, or none at all. That behaviour is called atomicity (the A in ACID), and the way to obtain that is to wrap the statements in a transaction. BEGIN; DELETE FROM table1 WHERE ...; DELETE FROM table2 WHERE ...; COMMIT; -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Windows user app to display input form + reporting?
On Wed, 12 Mar 2014 00:31:03 +0100, Gilles Ganault wrote: >Hello > > A friend needs to move from Excel to a database. The school won't > pay for the full version of MS Office that includes Access, so > recommended that she use LibreOffice Base instead. > > I just checked it out, and it seems to only be a front-end to the > HSQLDB database which is written in Java. > > Before I check it out, I'd like to make sure there's no SQLite-based > lighter solution where... > 1. I'll run the commands originally to build the DB + tables > 2. The Windows application will either display forms for her to enter > data, or provide an easy way to import data from Excel > 3. It will then display data through some reporting tool. > > Is there a good SQLite-based alternative to LibreOffice Base? > > I found this: > http://sqlitestudio.pl > http://www.valentina-db.com > http://www.navicat.com/products/navicat-for-sqlite Not what you asked for, but LibreOffice Base can use SQLite via ODBC: http://ch-werner.de/sqliteodbc/ I have no experience with that solution, but at least the package is maintained and reasonably up to date. HTH -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL quine using with
Someone called zzo38 posted a quine (self-replicating program) on Internet Relay Chat in network: Freenode, channel: #sqlite [2014-03-08 11:01:59] < zzo38> I made a quine program in SQL. [2014-03-08 11:02:10] < zzo38> with q(q) as (select 'with q(q) as (select ''#'') select replace(q,x''23'',replace('''''''','''''''''''')) from q;') select replace(q,x'23',replace(q,'''','''''')) from q; [2014-03-08 11:02:52] < zzo38> Do you like quine program in SQL? [2014-03-08 11:03:06] < zzo38> Maybe do you have a better (shorter) one? Note: SQL preferably written as a oneliner References: http://en.wikipedia.org/wiki/Quine_(computing) http://sqlite.org/lang_with.html Enjoy! -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqLlite default value setting
On Wed, 5 Mar 2014 22:41:47 -0800 (PST), SARC wrote: > Hi, > I am using sqlLite administrator interface(0.8.3.2 public beta) in my > windows XP PC. I am setting default value for some of columns(text,integer) > in my table(like 0,'none' etc). But it seems some quotation marks will be > added around the default values after i do some editing to the table each > time(like 'none' will become '''none''' if i edit first time, then > '''''none''''',it will add more and more quotes each time i do some > modifications in table). Why is this happening? what to do to stop this? > please help > Thank you I would suspect "sqlLite administrator interface(0.8.3.2 public beta)". The SQLite core library will not do that by itself. Try to report a bug to the developer of "sqlLite administrator", apparently this public beta is not fit for general availability. HTH -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] New
On Fri, 28 Feb 2014 18:54:34 -0600, Ashleigh wrote: > I'm trying to view files from my iphone backup I'm not > sure which program it is it says sqlite it is a black box > like the windows command That would be the sqlite command line tool, sqlite3.exe . If you start a MS Windows command window (CMD.EXE), then type sqlite3 , sqlite will open that file (if it really is a sqlite database). Then type .h for help. If you prefer a graphical user interface, I can recommend the sqlite manager plugin in the Firefox web browser. > If any one knows a better way to read and understand the files I would > greatly appreciate it >I think the file ext. is a plist. >Live, love & laugh. > -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Delete From Not Working Properly
On Fri, 21 Feb 2014 14:15:10 -0700, Geo Wil wrote: >Hello, > >Yesterday, while working on my game, I noticed that my game data was not >updating in my save SQLite3 database. So I started tracking down what was >going on and eventually my search lead me to my dData function. > >How this function works is that you pass a table name and a bool, the bool >is just there in case errors happen to notify the player. I did exhaustive >tests yesterday on this block of code and found that it was not functioning >properly, it was not deleting the data from my tables. > >I checked to see if sqlite3_step was producing an error but it was sending >back a value of 101 or SQLITE_DONE but the table data remained unchanged. >I also commented out the code I use to replace the deleted data just to >make sure the data retrieval code was not at fault. With just the dData >call and nothing else the data still would not delete from the table. > >Here is the code I am using for my dData function: > >void Database::dData(string table, bool* bErrors) >{ >sqlStr2 = "Delete From " + table; > >sqlite3_exec(dBase,"BEGIN TRANSACTION",NULL,NULL,&error); > >if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(), >&statement2, 0) == SQLITE_OK) >{ >sqlite3_step(statement2); >*bErrors = false; > >finalize(statement2, bErrors); >} > >else >{ >*bErrors = true; >createBInfo(); >d.createBReport("SQL Code 3",sqlite3_errmsg(dBase),bLocale + >to_string(__LINE__),bTDate,"./SC_Log.txt"); >} > >sqlite3_exec(dBase,"END TRANSACTION",NULL,NULL,&error); >} > >I am also going to link to my save database in case it itself is to blame >although I tested this on several different files with the same results. > >http://sc.lmpgames.com/scSave.sqlite > >I already have posted about this on StackOverflow and so far everyone that >has attempted to figure this out has been stumped. I am using Microsoft >Visual Studio 2012 and C++. Which version of MS Windows? What is the path to the database file? Is the database perhaps stored in a "protected" directory? Any errors on execution of the BEGIN and COMMIT statements? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about how sqlite recovers after a power loss
On Sat, 15 Feb 2014 14:55:48 +, "Fabrice Triboix" wrote: > Hi Richard, > > All right, many thanks for that. So if I do, say, > a SELECT just after opening the database, that's > when the recovery will actually take place and > the journal file will be replayed and deleted. > > Is my understanding correct? Yes. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in SQLite 3.8.2
On Wed, 12 Feb 2014 11:31:05 +0100, Pawe? Salawa wrote: > - open database A and attach database B: > ATTACH 'database_b.db' AS 'attached'; This is not the main cause, but that should be: ATTACH 'database_b.db' AS attached; (attached should not be a literal but an identifier, just like table names and column names.) -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
On Mon, 10 Feb 2014 14:18:18 -0500, C M wrote: >On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt wrote: >> >> On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy >> wrote: > > > >> >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR >> >> >> >> SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and >Settings\user\My >> >> Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) >> >> SQLITE_IOERR >> >> >> >> SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM >Durations >> >> WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O >> >> error (3338) SQLITE_IOERR > > >> >Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED >> >exception. Maybe a virus scanner or some other background process had >> >temporarily locked the database file. >> > >> >Dan. >> >> I agree, and I think Dropbox is the culprit here. > >May I ask either Dan or Kees, or anyone here, how to go from the error >codes to that diagnosis? > >Kees, why do you think Dropbox is the culprit? Because the log indicates that the database and its journal are located in a dropbox directory: "SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and Settings\user\My Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) SQLITE_IOERR" Given that name, I just assumed you are using dropbox. Dropbox will try to synchronise files in that directory with its copy in the cloud whenever its contents have changed. In an effort to make a consistent copy, dropbox will probably lock the file during the copy operation. Both the database and its journal will change frequently when you are using it, and dropbox will try yo keep up. You may want to temporarily disable dropbox to avoid this unwanted cpncurrent access, or perhaps restrict dropbox to time slots when you don't use the database. >I may want to deploy this app to users who would also backup their database >by having it in the Dropbox folder. What would people suggest I do about >this? Perhaps you could put the database somewhere else and periodically use the sqlite3_backup() interface to save a copy to the dropbox folder. >Thanks again to all. (btw, I now view my original subject line as a bit >much; I was just frustrated by it happening so randomly and without the >ability to fix it) -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] help needed for major SQLite problem
On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy wrote: >On 02/08/2014 03:00 AM, C M wrote: >> This is a follow-up to a question I asked on this list on Sep 1st, 2013, >> about an error that I was randomly getting with disk-based SQLite database >> in a Python desktop application. I now have more info to provide about the >> error...such as what was asked for at that time: >> >> On Sun, Sep 1, 2013 at 6:12 PM, Richard Hipp wrote: >> >>> Does Python have an interface to the error and warning log mechanism of >>> SQLite? (http://www.sqlite.org/errlog.html) Can you turn that on? It >>> will probably give more details about what it happening. >>> >> I wasn't able to do this at first, but thanks to switching from the >> standard sqlite3 module in Python (otherwise known as pysqslite) to Roger >> Binns's APSW module, and then also using an experimental module, >> apswdbapi2, from Edzard Pasma (thank you both), I was able to set things up >> to return the warning log...I think. Today, after not seeing the error in >> a very long time, I hit the error, and this was printed to sys.stdout: >> >> SQLITE_LOG: delayed 1375ms for lock/sharing conflict (10) SQLITE_IOERR >> >> SQLITE_LOG: os_win.c:35129: (5) winAccess(C:\Documents and Settings\user\My >> Documents\My Dropbox\myapp\gorp.db-journal) - Access is denied. (3338) >> SQLITE_IOERR >> >> SQLITE_LOG: statement aborts at 16: [SELECT resumes, start FROM Durations >> WHERE start='2014-02-07 14:24:14.064000' AND value='activity'] disk I/O >> error (3338) SQLITE_IOERR >> >> Does that give anyone a better idea of what could be happening and how I >> can fix this problem? > >Looks like GetFileAttributesEx() might be throwing an ERROR_ACCESS_DENIED >exception. Maybe a virus scanner or some other background process had >temporarily locked the database file. > >Dan. I agree, and I think Dropbox is the culprit here. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transaction behaviour
On Thu, 23 Jan 2014 16:30:57 +, "Joseph L. Casale" wrote: >I have a scenario where I am writing a series of entries across several tables >with relationships using Python and context managers. The sql is abstracted >away from the user by a class providing all the needed methods. These each >open implicit transactions, now its been asked that during the bulk loading >process, we wrap it all up in a transaction so nothing will be committed in >the event of some problem during the load. > >This presents a problem as far as I know, aside from extending the schema >with a table to indicate state that is updated upon completion, is there >anything >about transactions I am not seeing where I can accomplish leaving the bulk >load uncommitted in the event of an issue in my case? Start the bulk load with "BEGIN IMMEDIATE;" or "BEGIN EXCLUSIVE;" and count errors. If there were no errors at the end of the bulk load, issue "COMMIT;", else issue "ROLLBACK". If the program crashes, sqlite will take care of the rollback automatically the next time any process opens the database. http://sqlite.org/c3ref/get_autocommit.html can help you decide what mode you are in. By the way, autocommit is not the best thing to do if you have related insert/update/delete statements in an application transaction. Related updates should be in the same, atomic, transaction. >Thanks, >jlc -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple data conversion in SQLite - please help
On Wed, 22 Jan 2014 12:42:27 -, "DJ Small Paul" wrote: >Hi everyone, > > > >I hope this is an easy one for you! > > > >I've got an SQLite 3 database from an iphone app. I've pulled a table out >and the "date" column is in double binary - How do I see it as the actual >"date"?? It is probably in Julianday format: http://sqlite.org/lang_datefunc.html perhaps with an Apple specific offset. Aplle sometimes uses its own perception of day zero, however, I forgot what date that was. Some simple experiments will tell you. I hope this helps. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to build sqlite3 on [Open]Solaris
For your information, this is how I currently build sqlite3 from trunk on Solaris "SXCE" (OpenSolaris, yeah, it's old). I'm probably kicking in open doors for some people, but it might be interesting for some others. Thanks to whoever did the autoconf/makefile effort for sqlite3 and fossil! Paths: ~/src/sqlite3 : fossil checkout ~/bld/sqlite3 : builddir ~/usr/bin : installdir $ cat /etc/release Solaris Express Community Edition snv_130 X86 Copyright 2009 Sun Microsystems, Inc. All Rights Reserved. Use is subject to license terms. Assembled 14 December 2009 $ uname -a SunOS ozon 5.11 snv_130 i86pc i386 i86pc # function for sqlite3 in my generic build script: mksqlite3 () { INSTALLDIR="${HOME}/usr/bin" test ! -z "${PULL}" \ && cd ${HOME}/src/sqlite3 \ && fossil pull \ && fossil update trunk \ && cd cd ${HOME}/bld/sqlite3 \ && rm -rf ${HOME}/bld/sqlite3/* \ && ../../src/sqlite3/configure \ --prefix=${HOME}/usr \ --enable-amalgamation \ --disable-shared \ --enable-static \ --enable-load-extension \ --enable-threadsafe \ --enable-readline \ --disable-tcl \ --with-readline-lib="-L/usr/lib -R/usr/lib -lreadline -ltermcap" \ --with-readline-inc=-I/usr/include/readline \ && make sqlite3.c \ && make \ && make install \ && make clean } # size $ ls -nl ~/usr/bin/sqlite3 -rwxr-xr-x+ 1 6553610 1121820 Jan 16 23:46 /extra/home/knu/usr/bin/sqlite3 # Obviously, I could add this to the function: $ strip /extra/home/knu/usr/bin/sqlite3 # stripped sized $ ls -nl ~/usr/bin/sqlite3 -rwxr-xr-x+ 1 6553610528628 Jan 17 00:07 /extra/home/knu/usr/bin/sqlite3 # dependencies $ ldd /extra/home/knu/usr/bin/sqlite3 libreadline.so.5 => /usr/lib/libreadline.so.5 libcurses.so.1 =>/usr/lib/libcurses.so.1 libc.so.1 => /usr/lib/libc.so.1 libm.so.2 => /lib/libm.so.2 $ sqlite3 SQLite version 3.8.3 2014-01-16 15:31:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .q BTW, the fossil build is even more simple: mkfossil () { set -xv test ! -z "${PULL}" && cd ${HOME}/src/fossil && fossil pull && fossil update trunk && cd cd ${HOME}/src/fossil \ && ./configure \ && make \ && mv -f fossil ${HOME}/usr/bin/fossil \ && make clean \ && cd set +xv } On Solaris, I complie trunk every now and then, on MS Windows, I run the released executables. I'll try the same on XStreamOS shortly. http://www.sonicle.com/index.jsp?pagename=xstreamos-desktop&parent=products XStreamOS is an OpenSolaris fork. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select only records with fields that BEGIN with a certain sub-string
On Thu, 26 Dec 2013 11:59:10 +, dean gwilliam wrote: >Thank you all for your helpful advice. An alternative is PRAGMA table_info(yourtable); http://sqlite.org/pragma.html#pragma_table_info It resturns a result set, one row per column in yourtable. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug: PRAGMA busy_timeout outputs the value, screws up html
On Tue, 24 Dec 2013 22:55:32 -0500, dmitry babitsky wrote: >*bug description:* >Using PRAGMA busy_timeout outputs the value which in case of html output >screws up the display. >To be more precise, it creates an extra row/recordset with the timeout >value, which is what then shows on the html page. > >There should be a way to say "set the pragma, but don't output it". >It is especially important for -html mode which is designed for end-user >consumption. >What's more, it's very difficult to grep away as you can see. > >*To replicate:* > >echo "create table foo as select 1 as c;select * from foo;" |sqlite -html >-header -cmd "PRAGMA busy_timeout =1000;" :memory: >*timeout* >** >*1000* >** >c > >1 > Although it would be nice to have that PRAGMA output suppressed, there is an easy workaround (oneliner wrapped by mail) : printf ".output stderr\nPRAGMA busy_timeout =1000;\n.output stdout\ncreate table foo as select 1 as c;select * from foo;" |sqlite3 2>/dev/null -html -header :memory: yields: c 1 -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] An "unable to open database file" error that has nothing to do with opening database file
On Sun, 15 Dec 2013 07:28:52 -0800 (PST), margave wrote: >Addendum: I ran "file" on sqlite3.exe ... > >On computer B (where sqlite3 works): PE32 executable (console) Intel 80386, >for MS Windows >On computer A (where it does not): PE32+ executable (console) x86-64, for >MS Windows Well, something is not completely the same. I'd start with examining : * Exact windows version (start/computer/system properties) * Exact cygwin version * Package choices in cygwin * sqlite3 --version * Security properties of the database file (roperties / security / advanced / etc.) Remark: Do you know that concurrent access to sqlite files on network shared filesystems is not safe? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite download for 64 bit
On Mon, 9 Dec 2013 17:48:34 +0530, Krishna Chaitanya Konduru wrote: >hi >at the sqlite download page there is download for win 32 x86 what abut >64bit os.. would the same appllication runon both 32 and 64 bit os?? I am a >total newbie SQLite compiled for 32 bit works fine on 32-bit and 64-bit Windows OS. Only for very large databases you may need SQLite compiled for 64 bit. Typically, that is not something a newbie would do. >Regards >Krishna -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite ver: 1.0.89 issue
On Mon, 2 Dec 2013 13:56:33 +0200 (EET), Nikola Boyadjiev wrote: > > Hello, > I'm very sorry, the files did not attach to the previous e-mail i sent, I > will attach them to this one. > Hope this doesn't cause any issues.. > Thanks, > Nikola The list doesn't forward attachments. Often, it is sufficient to paste the text of the following items into the body of your mail : - the schema (use sqlite3 command line tool, .schema command - the select statement you want to ask about, exactly as used in your application And you will get better/quicker/more answers if you also post: - a minimal set of sample data that can demonstrate the problem - expected result - obtained result -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inefficient query plan in queries with multiple joins
On Sat, 30 Nov 2013 12:24:01 +0200, George wrote: >I have a query that is used to populate a table in my website. The query >joins 4 different tables and returns around 10 columns, and I want to order >on 4 of them, each of those 4 being on a different table. I also use a >LIMIT clause for pagination. > >I have noticed that when I order using just one column then the query is >very fast, because no TEMP B-TREE is used. When I add the other columns >then TEMP B-TREE is used and the query is very slow. > >Is there some way to avoid this TEMP B-TREE? Since I am using LIMIT 25, the >database really only needs to order those 25 (or slightly more) rows. No, to be able to order those, it has to order the whole result set, and offer you 25 rows of the ordered result. You could try to use more restrictive ON and/or WHERE clauses to reduce the size of the intermediate result set. For faster pagination, read : http://sqlite.org/cvstrac/wiki?p=ScrollingCursor -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert with multiple nested selects.
On Sun, 24 Nov 2013 16:10:58 +, "Joseph L. Casale" wrote: >Hey guys, >Trying to create a statement for use with parameters in a Python execute method >when performing inserts with multiple nested selects. I can adjust it for use >with Python, but I am having issues when there is more than one nested select. > >Something such as: > >INSERT OR IGNORE INTO table_a >( >col_a, >col_b, >col_c, >col_d >) >SELECT col_a FROM (SELECT id FROM table_b WHERE name=?) >,? >,? >,SELECT col_d FROM (SELECT id FROM table_c WHERE name=?); > >Anyone have a hint on how to perform such as query? Perhaps: INSERT OR IGNORE INTO table_a ( col_a, col_b, col_c, col_d ) VALUES ( (SELECT id FROM table_b WHERE name=?) ,? ,? ,(SELECT id FROM table_c WHERE name=?) ); >Thanks, >jlc HTH -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQlite3 Query Format for Export
On Tue, 19 Nov 2013 19:05:55 +0530, techi eth wrote: >I need this often. > >If possible provide some hint of plugin in Linux platform. > >Thanks Have a look at the sqlite3 command line tool: sqlite3 -help and at the .help command in that same command line tool. Of interest are e.g. : .separator .mode With .mode list , combined with SELECT and || concatenation, almost anything can be done. SELECT '{"' || firstname || '"' , '"' || lastname || '"' , empnumber , , '"' || country || '"}' FROM . I use .mode line sometimes, which is easy to postprocess, or .mode list with .separator '\001', if the data is suitable. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
On Mon, 18 Nov 2013 14:08:28 +0200, RSmith wrote: >Well this is the reason for my initial misunderstanding - which I then thought >I had wrong, but either you have it wrong too... or I >had it right in the first place. Ok, less cryptically now: > >It all depends on whether he has a Column called "name" that might be Null, or >whether he has a parameter which checks column "name" >and which might be null... in one case (your's) we check for null values in >the column and in another case (my later case) we check >if the parameter is null, not the column, and then from that decide whether to >use it as a check or not - not sure which but between >your and my solutions both are covered though, so I hope the OP gets sorted >out - if not, let us know... Yeah, I didn't go back far enough in the discussion, so I missed part of the spec. I'm sure the OP will sort it out after so many hints :) >On 2013/11/18 13:55, Kees Nuyt wrote: >> On Mon, 18 Nov 2013 13:04:31 +0200, RSmith wrote: >> >>> Oops, misprint... >>> >>> name won't be null of course, the parameter needs to be null, kindly >>> replace the queries offered like this: >>> >>> delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 >>> )); >>> >>> or in the second form: >>> >>> delete from emp where ( key = ?1 ) AND (( ?2 = '' ) OR ( name = ?2 )); >>> >>> I think this is closer to the intended - thanks, >>> Ryan >> Uhm, I think you mean: >> >> delete from emp >> where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 )); >> >> delete from emp >> where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 )); >> >> To cover both NULL and empty: >> >> delete from emp >> where key = ?1 AND (name IS NULL OR name = '' OR name = ?2); >> >> >>> On 2013/11/18 12:56, RSmith wrote: >>>> I might be missing something extraordinarily obvious... but I cannot >>>> understand the use case for this logic you have. >>>> >>>> My first response was to just use "delete from emp where key=123" and be >>>> done with it, who cares what the name is, right? >>>> >>>> But then it dawned on me that you may for some reason have that key NOT as >>>> a unique key, which means you can have many keys that >>>> are 123 in which case delete where key = 123 will remove all of them, but >>>> adding a name as an optional second parameter/check now >>>> makes sense. >>>> >>>> Some old-school boolean logic to the rescue then: >>>> If this last case is true: >>>> >>>> delete from emp where (key = '123') AND ((name IS NULL) OR (name = >>>> 'abc')); >>>> >>>> >>>> will simply delete all keys with 123 values, but only if the name is >>>> either not specified, or the name is both specified and >>>> specific. >>>> >>>> >>>> >>>> Be careful that you might not be binding null values, but maybe empty >>>> strings in stead of values, so another solution might be: >>>> >>>> delete from emp where (key = '123') AND ((name = '') OR (name = 'abc')); >>>> >>>> >>>> You get the idea. >>>> >>>> Cheers, >>>> Ryan >>>> >>>> >>>> On 2013/11/18 09:45, d b wrote: >>>>> Hi, >>>>> >>>>> >>>>> I am trying to make single query instead of below two queries. Can >>>>> somebody help? >>>>> >>>>>1. delete from emp where key = '123'; >>>>>2. delete from emp where key = '123' and name = 'abc'; >>>>> >>>>> if Key available, execute 1st query. if key and name available, execute >>>>> 2nd >>>>> query. >>>>> >>>>>Is it possible to write in single query? >>>>> -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query optimization
On Mon, 18 Nov 2013 13:04:31 +0200, RSmith wrote: >Oops, misprint... > >name won't be null of course, the parameter needs to be null, kindly replace >the queries offered like this: > > delete from emp where ( key = ?1 ) AND (( ?2 IS NULL ) OR ( name = ?2 )); > >or in the second form: > > delete from emp where ( key = ?1 ) AND (( ?2 = '' ) OR ( name = ?2 )); > >I think this is closer to the intended - thanks, >Ryan Uhm, I think you mean: delete from emp where ( key = ?1 ) AND (( name IS NULL ) OR ( name = ?2 )); delete from emp where ( key = ?1 ) AND (( name = '' ) OR ( name = ?2 )); To cover both NULL and empty: delete from emp where key = ?1 AND (name IS NULL OR name = '' OR name = ?2); >On 2013/11/18 12:56, RSmith wrote: >> I might be missing something extraordinarily obvious... but I cannot >> understand the use case for this logic you have. >> >> My first response was to just use "delete from emp where key=123" and be >> done with it, who cares what the name is, right? >> >> But then it dawned on me that you may for some reason have that key NOT as a >> unique key, which means you can have many keys that >> are 123 in which case delete where key = 123 will remove all of them, but >> adding a name as an optional second parameter/check now >> makes sense. >> >> Some old-school boolean logic to the rescue then: >> If this last case is true: >> >> delete from emp where (key = '123') AND ((name IS NULL) OR (name = 'abc')); >> >> >> will simply delete all keys with 123 values, but only if the name is either >> not specified, or the name is both specified and >> specific. >> >> >> >> Be careful that you might not be binding null values, but maybe empty >> strings in stead of values, so another solution might be: >> >> delete from emp where (key = '123') AND ((name = '') OR (name = 'abc')); >> >> >> You get the idea. >> >> Cheers, >> Ryan >> >> >> On 2013/11/18 09:45, d b wrote: >>> Hi, >>> >>> >>>I am trying to make single query instead of below two queries. Can >>> somebody help? >>> >>> 1. delete from emp where key = '123'; >>> 2. delete from emp where key = '123' and name = 'abc'; >>> >>> if Key available, execute 1st query. if key and name available, execute 2nd >>> query. >>> >>> Is it possible to write in single query? >>> >>> Regards, >>> va >>> ___ >>> 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 -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite server/file-locking scenario
On Sat, 16 Nov 2013 22:17:31 -0800 (PST), Joshua Grauman wrote: >Thanks so much for the reply. Sorry for the ignorance, but wouldn't only >the sectors (page cache) that are being written need to be cached? Database pages are updated in sqlites page cache, then, being 'dirty', flushed to the filesystem, which may try to postpone writing to disk and keep it in the filesystem cache for a while, or write it immediately if it likes to. Sqlite tries to instruct the filesystem to flush the filesystem cache to disk at certain moments. The filesystem image of the database is consistent when there are no open transactions (everything committed). During transactions, you have to assume the filesystem cache is not up to date, may be partially updated, and is not guaranteed to be consistent. Only in combination with a journal a consistent version can be reconstructed. >And I >was trying to read up on how sqlite does atomic writes, but doesn't the >way sqlite handles atomic writes guarentee that the file is *always* in a >valid state (even from the perspective of other programs that try to read >it while being written)? Not with "PRAGMA synchronous=off;" http://sqlite.org/pragma.html#pragma_synchronous To have a consistent image of the database in the filesystem when you start the copy, you have to make sure the database image in filesystem is consistent with PRAGMA synchronous=normal; and, like Simon says, lock the database file with "BEGIN IMMEDIATE" or "BEGIN EXLCUSIVE" to prevent partioal updates appearing in the image the filesystem has. >Josh > >> >> On 16 Nov 2013, at 11:37pm, Joshua Grauman wrote: >> >>> Or conversely, that if sqlite has the file open to write, my program >>> will read a cached version (if reading and writing happen at the same >>> time, I'm fine with the reader getting a slightly stale version). But >>> I'm not completely clear on how Linux file locking works... So do I >>> need to add a mutex to my program to make sure that a reader doesn't >>> get a corrupt database file? >> >> Good questions, checking a bad assumption. There is no such thing as a >> 'cached version' of a database file. Unix doesn't do things like that. >> Imagine you had a database file that was 20GB long. How long do you >> think it would take to make a cached version, and where do you think it >> would put it ?. >> >> So if you're reading a database file without using locking then you're >> running the risk of reading some of it before a change and some of it after >> the change. So yes, you need some form of mutex. Or to use the SQLite >> backup API to read the file. Or to use the normal SQLite API to open the >> file read/only and read all the data. >> >> Simon. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL 2003 in sqlite
On Sat, 16 Nov 2013 23:59:35 +0100, Petite Abeille wrote: > >On Nov 16, 2013, at 11:02 PM, Kees Nuyt wrote: > >> For the application, the merge would look like a single >> INSERT INTO merge_t statement. > >H…. clever lateral thinking, but I doubt this will fly in practice :) > >Two main issues: > >(1) ‘or ignore’ is most likely inappropriate as unrelated >constraint violations will trigger it (e.g. null constraint). >Resulting in a no-op altogether as neither insert nor update >will do anything in practice. Leading to loss of data. I would mitigate that by only offering key columns (primary and unique, probably also foreign keys) to the insert, and rely on default constraints for the value of all other columns. Default values should not violate any check constraints. The update would take care of the other columns. The INSERT INTO merge_t would use OR ABORT or OR ROLLBACK to handle the case that the triggered update fails due to check constraints. >(2) Both insert & update statement will execute irrespectively >of the state of the data, doubling the workload. After the insert attempt, all related pages will be in the cache, so it would double the computation workload at most, not the I/O workload. > And, really, at this point (additional views, instead of triggers, > highjacking of DML semantics, silent loss of data), the cure might > seem worst than the disease. > > As far as I can tell, there is no way to reasonably emulate MERGE > in SQLite, short of resorting to some external programming logic. I agree my solution would only be a workaround, and the required DML is not really "elegant", but from the applications perspective it's not too bad. Perhaps better than having to maintain external programming logic. I'll implement this workaround some day, when I have a use case, and be punished by reality :) -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL 2003 in sqlite
On Sat, 16 Nov 2013 17:19:06 +0100, Petite Abeille wrote: > >On Nov 16, 2013, at 4:11 PM, James K. Lowden wrote: > >> The logical equivalent of MERGE is accomplished by one INSERT and one >> UPDATE inside a user-defined transaction. Given SQLite's locking >> semantics, it's atomic. Nothing procedural about it. > >Well, one would still need to wrap these transaction and branching in some >kind of procedural code. So procedural it is. A possible solution might be: Create a VIEW "merge_t" on the table "t" you want to merge into. Create an INSTEAD OF TRIGGER that handles the gory details of INSERT OR IGNORE INTO t ; UPDATE t set For the application, the merge would look like a single INSERT INTO merge_t statement. (untested) Remark: The view and the trigger are pretty straightforward, and the SQL could probably be generated by a smart m4 macro. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
On Tue, 12 Nov 2013 15:00:36 +1100, SongbookDB wrote: >Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. > >I can load and search the db now. > >So to drop the index 'index1' for the table 'table1', do I just type DROP >INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the >database in SQLite Administrator. What operating system are we talking about? What is the exact full path to the database file? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Ghost index?
On Tue, 12 Nov 2013 15:10:16 +1100, SongbookDB wrote: >I've notices if I make a mistake, the prompt turns to ...>, and then >legitimate commands no longer work. Is there a way to get back to the >normal prompt? The .h command tells you what you can do there. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this a proper syntax?
On Thu, 24 Oct 2013 21:40:32 -0400, Igor Tandetnik wrote: >On 10/24/2013 9:17 PM, David Bicking wrote: >> But I think sqlite would notice that the subquery was the same in the >> UPDATE statement that Igor T gave and not run it twice > >I don't believe SQLite is quite that smart. It wasn't last time I >checked. I think it will, too, run the subquery twice, but since it uses >an index, that should be reasonably fast. If the subquery runs twice, the relevant pages will almost certainly be in the page cache, which makes it almost as fast as when the second run was optimized out. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite database created in PHP not readable in python becomes how do i download the correct version of the python sqlite API
On Wed, 25 Sep 2013 20:58:05 -0400, Aryc wrote: >first - thanks for all the insights. >the versions are different (Ugh) but that raises a new question "how do i >correct that?" >these versions were defined by the download of PHP and Python (IE i didn't get > the choice) >below is the PHP code and Python code i used, followed by a hex dump of the >beginning of the database file > >php code= >$yourfile = 'DTrial.sqlite'; >$database = new SQLiteDatabase($yourfile, 0666, $error); >if (!$database) { >$error = (file_exists($yourfile)) ? "Impossible to open, check > permissions" : "Impossible to create, check permissions"; >die($error); >} [code snipped] PHP has both sqlite2 (named sqlite) and sqlite3. http://www.php.net/manual/en/refs.database.vendors.php The PHP sqlite3 API has changed over the years, you may have to rewrite your code. If you have valuable data in the database, you can convert it by one of several methods: * use the PHP sqlite2 API to read the v2 database contents and the PHP sqlite3 API to write it into a new v3 db * writing the contents to a text file with v2 code and importing that with v3 code * or (easiest) by using the sqlite command line tools: sqlite old-sqlite2db .dump | sqlite3 new-sqlite3db The latter method will not result in an optimal schema, you'd better refactor that. The sqlite v2 command line tool may be buried somewhere deep in your PHP installation. If you can't find it anywhere, contact me off list so I can send you a download link (.zip file with MS Windows .exe and .dll, v2.8.17). It used to be still available from sqlite.org, but I don't know the hidden URL. You can build it yourself by checking out the correct version of the fossil repositories (links at the bottom of the http://sqlite.org/download.html page). Hope this helps. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] multiple connection to the same DB
On Sun, 22 Sep 2013 08:28:39 +0100 (BST), olivier Ménard wrote: >Hi > >I'll try to give you more details >We were all on Ubuntu, each colleague on a different computer > >with the sqlite command line tool i've created a db with > >$ cd ... sothat i'was located on a server directory shared with my >colleagues with the same permissions >$ sqlite3 birth.sqlite >>>> create table people(n,y); >>>> insert into people('olivier', 1969); > >then from other computers >$ cd .. to the same directory >$ sqlite3 birth.sqlite >>>> insert into people('simon', 1960); > >finally from my computer : >>>> select * from people; > >n y >-- -- >'simon' 1960 > >and olivier isn't in the DB. Many network file systems do not behave well with SQLite: http://sqlite.org/lockingv3.html#how_to_corrupt That is especially true when the network file server and/or network file client are heavily optimized for speed. You can try to recompile SQLite to use dot-file locking: http://www.sqlite.org/compile.html#enable_locking_style If you need a database server, SQLite is not the perfect choice. In those use cases, a database server like PostgresQL or MySQL or MariaDB and its respective client program is more appropriate: http://sqlite.org/whentouse.html More info: http://www.sqlite.org/search?q=locking >Thank's for your help. >Olivier. > > > > > De : Simon Slavin >À : olivier Ménard ; General Discussion of SQLite Database > >Envoyé le : Mardi 10 septembre 2013 21h43 >Objet : Re: [sqlite] multiple connection to the same DB > > > >On 10 Sep 2013, at 4:37pm, olivier Ménard wrote: > >> I've tried with my colleagues to write data to the same SQLite DB-file from >> differents accounts. >> When someone added a new line in the DB, sometimes older existing data were >> lost as if they had never existed and sometimes not. >> >> Why ? >> >> Multiple access are maybe allowed only for reading ? > >What operating system are you using ? > >What software or library calls are you using ? The SQLite shell tool ? The C >API ? > >How are the computers you are using accessing the same file ? Are you using >access across a network ? If so, what network protocol are you using ? > >Simon. >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)
On Wed, 18 Sep 2013 20:53:31 -0700 (PDT), niubao wrote: >Thank you very much Simon, for your detailed and very clear explanation on >this. I wonder if there is some materials, a tutorial or something, that >are dedicated to SQLite schema change for beginners? > >There seems to be so many things to consider. In the "Structure" tab of the Firefox "SQLite Manager" add-on, you can export the schema for a table to a text file [1], then edit the text file and feed it back into the database with the menu item Database / Import, or with the sqlite command line tool [2]. [1] can be compared with .dump in the sqlite command line tool, as in echo .dump | sqlite3 yourdbfile >schemafile.sql edit schemafile.sql to reflect your modifications [2] sqlite3 yourdbile http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)
On Wed, 18 Sep 2013 20:46:47 +, Reid Thompson wrote: > >On Wed, 2013-09-18 at 11:02 -0700, Bao Niu wrote: > >> > > Is there a tool that allows you to graphically change sqlite schema as >> > simple >> > > as editing a spreadsheet? > >https://code.google.com/p/sqlite-manager/ should meet your needs +1 -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] racing with date('now') (was: Select with dates)
On Sat, 14 Sep 2013 21:56:23 +0400, Yuriy Kaminskiy wrote: > >PS from postgresql documentation: >=== cut >http://www.postgresql.org/docs/9.3/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT >=== >Since these functions return the start time of the current transaction, their >values do not change during the transaction. This is considered a feature: the >intent is to allow a single transaction to have a consistent notion of the >"current" time, so that multiple modifications within the same transaction bear >the same time stamp. >=== cut === >*That's* way to go. I agree. See also: http://troels.arvin.dk/db/rdbms/#functions-LOCALTIMESTAMP and http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt page 139, 6.8, General Rules : 3) If an SQL-statement generally contains more than one reference to one or more s, then all such ref- erences are effectively evaluated simultaneously. Other sources (Interbase) add even more rules: Any statements triggered by a SQL statement (eg: Triggers or Stored Procedures) will evaluate CURRENT_TIME to be the same value. This value persists until the end of the SQL statement. Now, the discussion can be, did the standard really mean a constant timestamp for a statement or for a transaction? I would vote for the latter, but then another value should be available to get the systemtime, for ewxample to be able to register the start- and end time of a transaction. Perhaps that should be the difference between CURRENT_TIME and datetime('now')? just my two cents... -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement
On Sun, 8 Sep 2013 22:56:20 +, "Joseph L. Casale" wrote: >Hi, >What is the most efficient way to insert several records into a table which >has a fk ref to the auto incrementing pk of another insert I need to do in the >same statement. What is efficient? Apparently you are not looking for performance, but for short SQL code. In that case I think a combination of updateble view (instead of triggers are very powerful) and emulated variables might be what your are looking for. Have a look at this example: http://ideone.com/C36YV Rewrite for your use case... I agree with Keith Medcalf that the "val" in your case would have to be declared unique, and inserts into a should be insert or ignore. This is partially demonzstrated in http://ideone.com/bTOre . In the latter, the "a" tables are presumed preloaded in a separate pass, you could try to combine the triggers in both examples to take care of that. >I am migrating some code away from using the SQLAlchemy orm to using the >Core. The way the data is returned to me is a string (requiring an insert into >table A) >accompanied by several more strings (requiring inserts into table B with a ref >to a pk >in table A's row). > >So instead of doing this the typical way, if I can prepare all the sql as one >large >statement for several sets of related inserts (The initial insert into table A >with all >the related inserts into table B) I will get the performance I am after. > >Does this seem reasonable? Sqlite doesn't support variable declaration but I am >sure there is a more efficient means to this using something along the lines of >INSERT INTO SELECT, just not sure how to craft this with "n" inserts based on >one >select from the PK generating initial insert. > >Thanks, >jlc HTH -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite port to RTOS
On Wed, 4 Sep 2013 10:57:44 +0530, Pratheek Prakash wrote: > Hi Kees Nuyt, > > That was really helpful. Also I have another doubt. > Eventually I will be running sqlite integrated with other > modules in a board. That is what SQLite is made for. > As far as I have read I suppose that sqlite treats > a database as a file. Yes, a SQLite database is a file. SQLite will also creaste journal files (in the same directory as the database) and possibly temporary files (elsewhere in the filesystem). > Adding data to the database and retrieving data from the > database is equivalent to write() and read() file > operations. But in board where can I create that database > file like creating one in computer? Is it possible? Creating a database is as simple as opening it, and creating one or more tables using SQL statements. In principle you only have to implement a VFS to port SQLite to a new platform, the VFS is so to speak the operating system abstraction layer (See my previous message). The VFSses for Windows and POSIX (Unix and the like) are included in the source tree, some other people may have implemented VFSses for other operating systems. As far as SQLite is concerned, a C program using SQLite will be the same on any operating system, and the database file itself is portable between all platforms. > Also for communicating with the sqlite library do I need > to use command line interface always? No, the command line interface is a reference implementations and development tool. It can also be used productively from shell scripts. > Because in board its > not possible. Can I call those library functions directly > from the application? SQLite is an embedded SQL database library. Typically it is used via the C API, or by using a wrapper for other languages. Canonical program structure: Program init sqlite3_open_v2() sqlite3_prepare_v2() Statement execution loop sqlite3_bind_*() sqlite3_step() sqlite3_column_*() end loop sqlite3_reset() Program exit sqlite3_finalize() sqlite3_close() Please note that some of these entry points have a _v2() version, which is the preferred version. Please read the docs of each of those API entry points carefully, and don't forget to check the status after each and every call. > It will be really helpful if you can provide me with some > inputs on these You'll have to read more of the documentation, and experiment. Here are a few more pointers: http://sqlite.org/docs.html http://sqlite.org/c3ref/intro.html http://sqlite.org/cintro.html http://sqlite.org/arch.html Coding examples: http://icculus.org/~chunky/stuff/sqlite3_example/ Last but not least: The hint of Donald Griggs of about a day ago is very valuable! -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite port to RTOS
On Mon, 26 Aug 2013 18:33:02 +0530, Pratheek Prakash wrote: >Hi all, > I had downloaded the SQLite ver 3.8 code base from the >SQLite website. In the documentation they say that it supports Unix >(Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT). I >would like to port SQLite to uITRON RTOS. Is it possible to port the >code to an RTOS? If possible what could be the main challenges that I >may face? > Waiting for your valuable inputs. The main task you will face is writing a sqlite VFS module for your operating system. The Unix VFS may serve as an example. A good starting point in the documentation is: http://sqlite.org/custombuild.html >Regards >Pratheek -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0
On Mon, 2 Sep 2013 17:32:31 +0100, Simon Slavin wrote: > >On 2 Sep 2013, at 3:58pm, Bert Huijben wrote: > >> We anticipate that the wc_id column will be used more in future versions and >> I had hoped that the assumption that a better index match (matching more >> usable columns) would always be preferable over one that uses less columns. > >If you want to test for good indexes and don't understand how indexing will >work on your data ... > >1) Put in a convincing set of data >2) Run ANALYZE >3) Make up a ton of indexes, indexing lots of tables in many different orders. >4) Find the SELECT and UPDATEs you run most often and use EXPLAIN QUERY PLAN >on them. >5) Keep the indexes the query plans mention, and drop the ones they don't >mention. I would reverse 2) and 3), otherwise ANALYZE has nothing to analyze. The query plans of 4) are only valid with results of ANALYZE. Without sqlite_stat1 , the SQLite optimizer may choose different indexes at runtime. So, this strategy only works if Bert decides to include a populated sqlite_stat1 table in the SVN init code. >This stuff can't be done by theory: there are too many >possibilities and it requires too good an understanding >of how the query planner works for non-experts. >There's no substitute for actual testing. > >Simon. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is SQLite a DBMS?
tOn Sun, 1 Sep 2013 12:34:02 +0200, Paolo Bolzoni wrote: > > Wait a second, this is a mailing list where you need > to register to write. Isn't it? Yes, but he appears to have posted from nabble. I guess nabble has a subscription. > It means the OP actually registered Probably not > but he did not > try to seek for wikipedia sqlite in google? Yes, that probably means he is young and has not learned how to do research yet. > It is just me or it is quite weird? Weirdness is common on the internet. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking Error
On Mon, 26 Aug 2013 13:32:42 +0530, techi eth wrote: >PRAGMA jouranl_mode = WAL That's misspelled, both in your text and in your code. Try: PRAGMA journal_mode=WAL; You only have to do this once, e.g. at database creation. Journal mode WAL is a persistent property of the database file. Every connection will respect it. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Difference in pragma table_info between tables and views
On Wed, 14 Aug 2013 10:03:53 +0200, Thomas Krueger wrote: >Hi All, > >I noticed a difference in the output that pragma table_info gives for >tables and views. It seems, that not null conditions aren't properly >returned for views: > >create table atab ( id int not null primary key, withnulls text, >withoutnulls text NOT NULL ); >create view aview as select * from atab; >pragma table_info(atab); >pragma table_info(aview); > >Please notice that table_info(aview) returns always 0 in the not_null >column, whereas the same column is properly reported for pragma >table_info(atab). > >Is this known and intentional? Is there plans to correct that behavior? I >have to deal with any database schema, wanted to use table_info as a means >to extract the column definitions of views. NOT NULL is a check constraint, which is only effective for INSERT or UPDATE operations. A view is a SELECT query and it is not inserted into or updated ever, except when an INSTEAD OF trigger is created, in which case any modification triggers the check constraints of the underlying table[s]. So, to me, not returning values for the notnull properties appears to be proper behaviour (but I understand your wish). For a generic tool, it probably is not overly difficult to parse the column list and source tables of a simple view, and refer back to the appropriate table_info() results to derive the NOT NULL constraint. >I understand that reporting the pk-ness of a column is likely to be >intentionally not correct for views. Indeed. >Thomas -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite Input with validation and lookup
On Mon, 12 Aug 2013 10:51 +0100 (BST), "Stephen Hughes" wrote: >SQLite Input with validation and lookup >This may be a FAQ, if so please point me in the right direction, but if >not what I am looking for is a BROWSE style INPUT which will:- > - allow me to force UPPER case. > - set MAXIMUM/MINIMUM values. > - LOOKUP and retrieve data from a masterfile. All those are features of a user interface framework, which SQLite is not. SQLite is "only" a database engine, used to store and retrieve data (and very good at that). It will happily serve the storage you need behind such a framework, on top of the actual application data. >For example:- > - enter a Quantity and Product Code >which will automatically:- > - retrieve and display the unit cost. > - retrieve and display the Product description. > - calculate and display the total cost. > > I have done the above using Visual FoxPro but I cannot find > anything with similar functionality for SQLite. You could construct such a user interface framework, and be supported by sqlite features: - column constraints (value range) - table constraints (value range) - reference constraints (foreign keys) - meta data (PRAGMA table_info(), PRAGMA foreign_key_list() - creative use of type names e.g. encode handlers between the parens behind a type name. http://sqlite.org/pragma.html#pragma_table_info http://sqlite.org/pragma.html#pragma_foreign_key_list For requirements you cannot implement using the above, you may need to create additional meta data tables. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select prepared statement always returning SQLITE_DONE
On Thu, 1 Aug 2013 21:22:23 -0700 (PDT), ngsbioinformat...@gmail.com wrote: >Hi all - I've got sqlite embedded in an iOS app. I have a database with 1 >table, and am creating a prepared select statement with a where clause on 1 >field. In my loop, my order of operations is: sqlite3_bind_text, >sqlite3_step, sqlite3_reset. > >One the first iteration of the loop, sqlite3_step returns SQLITE_ROW and >all the correct values. On the second and every subsequent iteration, >sqlite3_step returns SQLITE_DONE. Why is this? I thought I could call >sqlite3_reset then rebind a variable and called step again. All the >documentation points to this being correct. Am I missing something? > >Ryan You appear to do _prepare() loop _bind() _step() [ if not _DONE use results ] _reset() end loop _reset() resets the statement, so the cursor is invalidated and the statement is re-initialized. This way you will always only retrieve * either zero rows (the WHERE clause doesn't match anything) .. in this case _step() returns no data and status SQLITE_DONE * or one row (the WHERE clause matches one or more rows) Perhaps this is not what you meant to do? Typically, re-using a prepared statement has this structure: _prepare() loop 1 until bind list exhausted _bind() loop 2 until SQLITE_DONE _step() [ if not _DONE use results ] end loop 2 _reset() end loop 1 _finalize() But perhaps I don't understand your problem? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table name in attach'ed databases
On Sun, 21 Jul 2013 03:03:10 +0530, "V.Krishn" wrote: >following works: >attach database 'file:test.db' as 'test'; #OK >select * from test.employees limit 1; #OK > >but, >attach database 'file:test.db' as '123test'; #OK >select * from 123test.employees limit 1; #Gives error > >I hope this is not a bug ? >(assuming here that table names starting with numeric is not allowed). The filename is in a literal, the identifier is not. Indeed identifiers should not start with a digit. So, drop the quoting in the identifier. attach database 'file:test.db' as test;-- OK attach database 'file:test.db' as 123test; -- not OK attach database 'file:test.db' as test123; -- OK Note: these are database names, not table names. A double quoted database identifier as in "123test" might work (or not, you can test that easily), but why bother? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite3 extended API usage
On Mon, 8 Jul 2013 06:43:33 +0100, Simon Slavin wrote: > There is no call 'sqlite_get_table()' in SQLite version 3. > Nor is there anything like it. > The closest thing is 'sqlite3_exec()'. Not completely true. The C API documentation mentions a legacy interface: sqlite3_get_table(). http://www.sqlite.org/c3ref/free_table.html -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table format for most efficient query
On Thu, 4 Jul 2013 22:52:26 +0100, Simon Slavin wrote: > > I assume you missed a comma: > > create table lookup (index int, start int, end int) indeed > But actually it’s a bad idea to use the words > 'index' and 'end’ for columns because they're > used as reserved words in SQL. I agree. > So try something like > create table lookup (rowindex int, rangestart int, rangeend int) Even better: create table lookup ( rowindex INTEGER PRIMARY KEY , rangestart int , rangeend int ); This way, rowindex aliases the internal ROWID column, saving an integer column. Also, JOIN performance on rowindex will be better as it removes one level of indirection. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Another 2 questions about SQLite
On Thu, 4 Jul 2013 15:15:14 -0400, "James K. Lowden" wrote: > This weird case is one of (I would say) misusing the connection. IMO > SQLite should return an error if prepare is issued on a connection for > which a previous prepare was not finalized or reset. That would > forestall discussions like, this and prevent confusion and error. Not the _prepare() is critical, but the first call of_step() after _prepare() or _reset(). In fact it is a nice feature to prepare (a whole bunch of) statements in advance (which runs the optimizer and generates the code for the virtual machine) and reuse them (with different bindings). Every use (AKA statement execution) is: loop _bind() _step() ... other stuff endloop _reset() At program init: _prepare() At program exit: _finalize() Re-prepare() is only necessary when the schema changes, and can be automatic in some versions of _prepare(). In most applications the schema is quite static. my EUR 0.02 -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Getting Constraints Details
On Mon, 1 Jul 2013 07:23:54 +, Vijay Khurdiya wrote: >How to get details of constraints associated with Data in SQLite3. > >Ex : I want to find out DEFAULT constraints value associated with data. Some of that info is available in the results of PRAGMA table_info(yourtablename); -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] anyone know how to use ta-lib api to the sqlite database?
On Thu, 20 Jun 2013 06:37:22 +, YAN HONG YE wrote: > the ta-lib.org, the ta-lib library could use in EXCEL, > I wanna use it in the sqlite database, but I don't know > how to write the c or c++ code to load the ta-lib api in > sqlite database, anyone know it? You cannot load a library into a database. You can link a library with your application, together with the sqlite3 library. Then use the sqlite extension API, as documented in http://sqlite.org/c3ref/create_function.html to add self-written functions to the SQL syntax. Those functions can then call the ta-lib functions where needed. YOu might not need ta-lib at all. There are several open source extension libraries available which may or may not do what you need. Search the web and the archives of this mailing list. One example is http://www.schemamania.org/sql/sqlite/udf/ Note: The whole SQLite API is C, not C++, but it can be called from C++. Warning: I've never done any of this myself, and I don't know what kind of API ta-lib offers. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Updating a table from itself
On Sun, 9 Jun 2013 09:55:30 +0100, "Dave Wellman" wrote: >Hi Igor, > >Many thanks for that. > >For this example I'm updating a single column (c2). If I needed to update >multiple columns in the table would I need to use the SELECT construct for >each column? Yes. >Cheers, >Dave > >-Original Message- >From: sqlite-users-boun...@sqlite.org >[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik >Sent: 08 June 2013 20:01 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Updating a table from itself > >On 6/8/2013 2:51 PM, Dave Wellman wrote: >> update t1 from (select c1,c2 from t1) as dt1 set c2 = dt1.c2 where >> t1.c1 = dt1.c2 - 1; > >update t1 set c2 = coalesce((select c2 from t1 dt1 where t1.c1 = dt1.c2 >- 1), c2); -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite abnormal IO writing
On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?? wrote: > > Ok,I do not make my means clearly. I mean 60 seconds after my > program started,not token 60 seconds to load database file. > > Now, I got the reason of sqlite abnormal IO writing,it about > batch insert. Here is my usecase: One table about 4 column and > 500 row,the content of every row no exceed 100 byte, every time > I update the whole table using batch query. > > It should take about one second and 100k IO writing on > estimate,BUT it sustained about 20 second and wrote about 2.5M > actually. > > Now,I modify the implement of batch query, it take about one > second and 70k IO writing.So there are abnormal something in > batch query indeed,Sqlite or Qt SQL module. Is this still about bulk INSERT or about a SEELCT query? In which way did you modify it? Perhaps http://sqlite.org/faq.html#q19 helps? -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A sqlite database changing the contents when I change the database name or user rights
On Wed, 10 Apr 2013 08:24:42 +, Bernardino Flores/Jeanologia wrote: > Any idea about what is happening? Three causes come to mind: 1) Is your database in a system-protected directory, like "C:\Program Files" or "C:\Program Files(x86)"? If so, Windows virtualizes the file and works on a system-provided copy somewhere in C:\Users\%username%\Appdata . Non-static data files do not belong in a program directory. 2) If you only copy the database file, but an "hot" journal is also present-but-not-copied, you corrupt your database. http://sqlite.org/lockingv3.html#how_to_corrupt 3) On some versions of MS Windows, .db is a special extension, which gets a special treatment and should be avoided. Some people reported bad performance if that extension is used, which improves when replaced by something like .sqlite or .etilqs . -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 64bit compatibility warnings
On Mon, 08 Apr 2013 08:39:49 +0200, Alexandr N?mec wrote: > Hi all, > > thanks for your replies, but unfortunately they did not answer > my original question whether these warnings are harmless and > can be ignored or not. These warnings reported by the VS C++ > compiler are about "possible loss of data", so it is a > situation when a "int64" expression result is assigned to an > "int" variable for example. In such cases these warnings are > very legitimate. If such an assignment is the real intention > of the programmer, an explicit (int) typecast should be added, > because it will > >- tell to the rest of the world, that the programmer knows > what he is doing, ie. he really wants to "truncate" the result, The programmers know what they are doing. As <http://www.sqlite.org/faq.html#q17> tells, they are harmless if all tests scripts succeed. The test scripts are run before every SQLite release. SQLite is not released if a test fails. So, the warnings can be ignored. >- eliminate compiler warnings of this type. > > There are only 5 warning of this type in the entire code base, > so that should be an easy fix. Warnings are fixed eventually ( e.g. <http://www.sqlite.org/cgi/src/info/274d2a2266> ), but with a lower priority than making sure all tests succeed. >Alex -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance with journal_mode = off
On Wed, 27 Mar 2013 17:55:00 -0400, Jeff Archer wrote: >On Wed, Mar 27, 2013 at 5:46 PM, David King wrote: >> >> > I am populating a database with 5764 records using the exact same data set >> > each time into a newly created file. >> > When I use no explicit transactions (default atomic commit) it takes 17.7 >> > seconds. >> > When I set journal_mode = off, same operation takes 5.5 seconds. >> > If I do all 5764 inserts within a single transaction only 2.5 seconds. >> >> >> That sounds about right, yeah. With journalling, >> most disk writes have to be done twice (once to the >> journal and once to the data file). > > Which is why I expected journal_mode = off to make it faster. But it > is 3 seconds faster when I leave journaling enabled and do all writes > within a single transaction. I miss one test case: both journal_mode = off, and all 5764 inserts within a single transaction. Give that one a thought (and a try) and you'll understand the difference. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import TXT file
On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche wrote: >If I remove the double quotes, the problem is solved indeed. >I'm working on windows. Do you know if there is an alternative to the SED >command or tool? For just removing double quotes, I would recommend the tr (translate) program. Something like: echo "CREATE TABLE Source (Segments TEXT);" | sqlite3 dbfile tr -d '"' import.txt sqlite3 dbfile .import import.txt Source The sed (stream editor) is fine here too, as demonstrated before. For a general stream editor I would recommend gawk, which is much easier and more powerful than tr or sed when the transformations get more complicated. With gawk it is easy to create INSERT statements (from the top of my head, untested). gawk -f xform.awk test.txt | sqlite3 dbfile xform.awk contains: ### BEGIN{ FS = "" insfmt = "INSERT INTO Source (Segments) VALUES ('%s');\n" print "CREATE TABLE Source (Segments TEXT);" print "BEGIN;" } # the following action is executed for every input line { gsub(/"/,"")# remove double quotes gsub(/'/,"''") # escape every single quote with another single quote printf insfmt,$0 } END{ print "COMMIT;" } EOF tr, sed and gawk have implementations on Windows, for example in http://unxutils.sourceforge.net/ Note: you need both UnxUtils.zip and UnxUpdates.zip >Or is there a way I can force sqlite3 to ignore these double quotes? > >thanks > >gert -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set Mode to HTML via VBScript
On Mon, 11 Feb 2013 05:42:17 -0800 (PST), Jeff Steffanina wrote: > >Hello All, >During a sqlite3 session, I need to APPEN output to a FILE in the HTML mode. > >Here is my SELECT: Select * from Booking; > >What I really want to say is Set the mode, Execute select and append to >MySummary: > >mode html Select * from Booking >> MySummary.html Two oneliners (possibly wrapped in transmission) 1) printf ".mode html\nSELECT * FROM Booking;\n"|sqlite3 Mydb >>My.html 2) sqlite3 -html Mydb "SELECT * FROM Booking;" >>My.html Try: sqlite3 -help for more options. -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users