Re: [sqlite] Remove me from this

[Kees Nuyt]

On Mon, 27 Oct 2014 07:20:04 +1100, Isaac Faulkner
<ifaulkne...@students.bdc.nsw.edu.au> 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

[Kees Nuyt]

On Thu, 16 Oct 2014 18:46:19 +0530, Prakash Premkumar
<prakash.p...@gmail.com> 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

[Kees Nuyt]

On Wed, 15 Oct 2014 01:26:10 +0200, Kees Nuyt <k.n...@zonnet.nl>
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

[Kees Nuyt]

On Tue, 14 Oct 2014 21:24:40 + (UTC), Pontus Bergsten
<pontus_bergs...@yahoo.se> 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

[Kees Nuyt]

On Wed, 08 Oct 2014 17:45:29 -0400, Mark Halegua
<phanto...@mindspring.com> 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

[Kees Nuyt]

On Sat, 04 Oct 2014 10:44:20 -0400, Mark Halegua
<phanto...@mindspring.com> 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

[Kees Nuyt]

On Mon, 6 Oct 2014 10:28:59 +0530, Prakash Premkumar
<prakash.p...@gmail.com> 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

[Kees Nuyt]

On Fri, 3 Oct 2014 18:39:29 +0530, Prakash Premkumar
<prakash.p...@gmail.com> 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

[Kees Nuyt]

On Mon, 22 Sep 2014 14:02:57 -0700, Jungle Boogie
<jungleboog...@gmail.com> wrote:

> Igor Tandetnik <i...@tandetnik.org>
> 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

[Kees Nuyt]

On Sun, 14 Sep 2014 00:18:34 -0400, Lea Verou <l...@verou.me>
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

[Kees Nuyt]

On Sat, 13 Sep 2014 00:55:23 +0200, Kees Nuyt <k.n...@zonnet.nl>
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

[Kees Nuyt]

On Fri, 12 Sep 2014 18:33:46 -0400, Mark Halegua
<phanto...@mindspring.com> wrote:

>On Friday, September 12, 2014 09:43:39 PM Simon Slavin wrote:
>> On 12 Sep 2014, at 9:15pm, Mark Halegua <phanto...@mindspring.com> wrote:
>> > On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
>> >> On 12 Sep 2014, at 5:28pm, Mark Halegua <phanto...@mindspring.com> 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

[Kees Nuyt]

On Fri, 12 Sep 2014 23:23:39 +0100, Simon Slavin
<slav...@bigfraud.org> wrote:

>
>On 12 Sep 2014, at 10:33pm, Kees Nuyt <k.n...@zonnet.nl> 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

[Kees Nuyt]

On Fri, 12 Sep 2014 17:48:27 -0400, Eric Rubin-Smith
<eas@gmail.com> 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

[Kees Nuyt]

On Fri, 12 Sep 2014 16:41:40 +0100, Simon Slavin
<slav...@bigfraud.org> wrote:

> On 12 Sep 2014, at 3:44pm, 
> Drago, William @ MWG - NARDAEAST <william.dr...@l-3com.com>
> 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

[Kees Nuyt]

On Thu, 11 Sep 2014 23:49:22 +0200, Kees Nuyt <k.n...@zonnet.nl>
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

[Kees Nuyt]

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

[Kees Nuyt]

On Fri, 8 Aug 2014 11:14:29 +0200, Giuseppe Costanzi
<giuseppecosta...@gmail.com> 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

[Kees Nuyt]

On Sun, 27 Jul 2014 23:16:05 -0700 (PDT), suparna
<suparna.dhin...@gmail.com> 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

[Kees Nuyt]

On Fri, 18 Jul 2014 21:56:31 +0100, Rui Fernandes <rui.kep...@gmail.com>
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

[Kees Nuyt]

On Thu, 17 Jul 2014 13:06:36 +0530, Sky Meena <sky.me...@gmail.com>
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 <rsm...@rsweb.co.za> 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

[Kees Nuyt]

On Thu, 17 Jul 2014 15:17:07 +0530, Sky Meena <sky.me...@gmail.com>
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 <slav...@bigfraud.org> wrote:
>
>>
>> On 9 May 2014, at 1:23pm, Sky Meena <sky.me...@gmail.com> 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

[Kees Nuyt]

On Mon, 14 Jul 2014 12:09:46 +0100, Simon Slavin <slav...@bigfraud.org>
wrote:

> On 14 Jul 2014, at 11:19am, Kees Nuyt <k.n...@zonnet.nl> wrote:
> 
> > On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin <slav...@bigfraud.org>
> > 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

[Kees Nuyt]

On Sun, 13 Jul 2014 22:05:29 +0100, Simon Slavin <slav...@bigfraud.org>
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

[Kees Nuyt]

On Sun, 13 Jul 2014 18:00:59 +0100, Simon Slavin <slav...@bigfraud.org>
wrote:

> On 12 Jul 2014, at 9:37am, Charles Parnot <charles.par...@gmail.com> 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!

[Kees Nuyt]

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!

[Kees Nuyt]

On Sun, 13 Jul 2014 07:23:08 +0800, "ddy is super man"
<boybin...@qq.com> 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

[Kees Nuyt]

On Thu, 03 Jul 2014 23:22:46 +0200, Martin Kleusberg
<mkleusb...@gmail.com> 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

[Kees Nuyt]

On Wed, 02 Jul 2014 17:03:43 +0100, Grzegorz Sikorski
<g.sikor...@kelvatek.com> 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

[Kees Nuyt]

On Wed, 2 Jul 2014 12:02:01 +0100, Simon Slavin <slav...@bigfraud.org>
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.

[Kees Nuyt]

On Sat, 24 May 2014 22:24:01 +0200, RSmith <rsm...@rsweb.co.za> 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.

[Kees Nuyt]

On Sat, 24 May 2014 03:08:32 -0700 (PDT), Humblebee
<fantasia.d...@gmail.com> 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

[Kees Nuyt]

On Sun, 18 May 2014 01:06:42 +0300, <to...@acm.org> 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

[Kees Nuyt]

On Thu, 8 May 2014 14:44:19 +0530, techi eth <techi...@gmail.com> 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

[Kees Nuyt]

On Fri, 25 Apr 2014 09:22:20 +0100 (BST), 
olivier Ménard <men37...@yahoo.fr> 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

[Kees Nuyt]

On Mon, 21 Apr 2014 13:30:15 +, "Drago, William @ MWG - NARDAEAST"
<william.dr...@l-3com.com> 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

[Kees Nuyt]

n Sat, 19 Apr 2014 10:38:01 +0100, c...@isbd.net wrote:

>Simon Slavin <slav...@bigfraud.org> 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

[Kees Nuyt]

On Tue, 15 Apr 2014 22:34:40 +0200, Marco Bambini <ma...@sqlabs.net>
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?

[Kees Nuyt]

On Sun, 6 Apr 2014 21:43:27 -0400, Richard Hipp <d...@sqlite.org> 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

[Kees Nuyt]

On Fri, 28 Mar 2014 12:56:59 +0100, "Kleiner Werner" <sqliteh...@web.de>
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:

[Kees Nuyt]

On Wed, 19 Mar 2014 14:39:39 -0700 (PDT), Stefano Ravagni
<stefanorava...@gmail.com> 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

[Kees Nuyt]

On Thu, 20 Mar 2014 18:05:48 +1300, Jono Poff
<jonathan.p...@taitradio.com> 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

[Kees Nuyt]

On Fri, 14 Mar 2014 07:56:57 -0700 (PDT), khaloud1987
<toumi.kha...@gmail.com> 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?

[Kees Nuyt]

On Wed, 12 Mar 2014 00:31:03 +0100, Gilles Ganault
<gilles.gana...@free.fr> 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

[Kees Nuyt]

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

[Kees Nuyt]

On Wed, 5 Mar 2014 22:41:47 -0800 (PST), SARC
<shreesha...@gmail.com> 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

[Kees Nuyt]

On Fri, 28 Feb 2014 18:54:34 -0600, Ashleigh
<ashleight...@yahoo.com> 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

[Kees Nuyt]

On Fri, 21 Feb 2014 14:15:10 -0700, Geo Wil <geowi...@gmail.com>
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,);
>
>if (sqlite3_prepare_v2(dBase, sqlStr2.c_str(), sqlStr2.size(),
>, 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,);
>}
>
>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

[Kees Nuyt]

On Sat, 15 Feb 2014 14:55:48 +, "Fabrice Triboix"
<ftrib...@falcon-one.com> 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

[Kees Nuyt]

On Wed, 12 Feb 2014 11:31:05 +0100, Pawe? Salawa
<pawelsal...@gmail.com> 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

[Kees Nuyt]

On Mon, 10 Feb 2014 14:18:18 -0500, C M <cmpyt...@gmail.com>
wrote:

>On Sat, Feb 8, 2014 at 4:28 AM, Kees Nuyt <k.n...@zonnet.nl> wrote:
>>
>> On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy
>> <danielk1...@gmail.com> 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

[Kees Nuyt]

On Sat, 08 Feb 2014 12:06:01 +0700, Dan Kennedy
<danielk1...@gmail.com> 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 <d...@sqlite.org> 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

[Kees Nuyt]

On Thu, 23 Jan 2014 16:30:57 +, "Joseph L. Casale"
<jcas...@activenetwerx.com> 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

[Kees Nuyt]

On Wed, 22 Jan 2014 12:42:27 -, "DJ Small Paul" <i...@djsmallpaul.co.uk> 
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

[Kees Nuyt]

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=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

[Kees Nuyt]

On Thu, 26 Dec 2013 11:59:10 +, dean gwilliam <mgbg25...@blueyonder.co.uk> 
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

[Kees Nuyt]

On Tue, 24 Dec 2013 22:55:32 -0500, dmitry babitsky <dbab...@gmail.com> 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

[Kees Nuyt]

On Sun, 15 Dec 2013 07:28:52 -0800 (PST), margave <margr...@mail.com> 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

[Kees Nuyt]

On Mon, 9 Dec 2013 17:48:34 +0530, Krishna Chaitanya Konduru 
<krish1...@gmail.com> 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

[Kees Nuyt]

On Mon, 2 Dec 2013 13:56:33 +0200 (EET), Nikola Boyadjiev <n.boyadj...@abv.bg> 
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

[Kees Nuyt]

On Sat, 30 Nov 2013 12:24:01 +0200, George <pinkisntw...@gmail.com>
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.

[Kees Nuyt]

On Sun, 24 Nov 2013 16:10:58 +, "Joseph L. Casale"
<jcas...@activenetwerx.com> 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

[Kees Nuyt]

On Tue, 19 Nov 2013 19:05:55 +0530, techi eth <techi...@gmail.com>
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

[Kees Nuyt]

On Mon, 18 Nov 2013 14:08:28 +0200, RSmith <rsm...@rsweb.co.za> 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 <rsm...@rsweb.co.za> 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

[Kees Nuyt]

On Mon, 18 Nov 2013 13:04:31 +0200, RSmith <rsm...@rsweb.co.za> 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

[Kees Nuyt]

On Sat, 16 Nov 2013 22:17:31 -0800 (PST), Joshua Grauman
<jnf...@grauman.com> 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 <jnf...@grauman.com> 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

[Kees Nuyt]

On Sat, 16 Nov 2013 23:59:35 +0100, Petite Abeille
<petite.abei...@gmail.com> wrote:

>
>On Nov 16, 2013, at 11:02 PM, Kees Nuyt <k.n...@zonnet.nl> 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

[Kees Nuyt]

On Sat, 16 Nov 2013 17:19:06 +0100, Petite Abeille
<petite.abei...@gmail.com> wrote:

>
>On Nov 16, 2013, at 4:11 PM, James K. Lowden <jklow...@schemamania.org> 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?

[Kees Nuyt]

On Tue, 12 Nov 2013 15:00:36 +1100, SongbookDB
<shaun_thom...@songbookdb.com> 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?

[Kees Nuyt]

On Tue, 12 Nov 2013 15:10:16 +1100, SongbookDB
<shaun_thom...@songbookdb.com> 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?

[Kees Nuyt]

On Thu, 24 Oct 2013 21:40:32 -0400, Igor Tandetnik <i...@tandetnik.org>
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

[Kees Nuyt]

On Wed, 25 Sep 2013 20:58:05 -0400, Aryc <alagnaryt...@gmail.com> 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

[Kees Nuyt]

On Sun, 22 Sep 2013 08:28:39 +0100 (BST), olivier Ménard
<men37...@yahoo.fr> 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 <slav...@bigfraud.org>
>À : olivier Ménard <men37...@yahoo.fr>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>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 <men37...@yahoo.fr> 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)

[Kees Nuyt]

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)

[Kees Nuyt]

On Wed, 18 Sep 2013 20:46:47 +, Reid Thompson
<reid.thomp...@ateb.com> 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)

[Kees Nuyt]

On Sat, 14 Sep 2013 21:56:23 +0400, Yuriy Kaminskiy <yum...@gmail.com>
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

[Kees Nuyt]

On Sun, 8 Sep 2013 22:56:20 +, "Joseph L. Casale"
<jcas...@activenetwerx.com> 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

[Kees Nuyt]

On Wed, 4 Sep 2013 10:57:44 +0530, Pratheek Prakash
<prathe...@tataelxsi.co.in> 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

[Kees Nuyt]

On Mon, 26 Aug 2013 18:33:02 +0530, Pratheek Prakash
<prathe...@tataelxsi.co.in> 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

[Kees Nuyt]

On Mon, 2 Sep 2013 17:32:31 +0100, Simon Slavin <slav...@bigfraud.org>
wrote:

>
>On 2 Sep 2013, at 3:58pm, Bert Huijben <rhuij...@apache.org> 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?

[Kees Nuyt]

tOn Sun, 1 Sep 2013 12:34:02 +0200, Paolo Bolzoni
<paolo.bolzoni.br...@gmail.com> 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

[Kees Nuyt]

On Mon, 26 Aug 2013 13:32:42 +0530, techi eth <techi...@gmail.com>
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

[Kees Nuyt]

On Wed, 14 Aug 2013 10:03:53 +0200, Thomas Krueger
<tom.krue...@gmail.com> 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

[Kees Nuyt]

On Mon, 12 Aug 2013 10:51 +0100 (BST), "Stephen Hughes"
<ri...@cix.co.uk> 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

[Kees Nuyt]

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

[Kees Nuyt]

On Sun, 21 Jul 2013 03:03:10 +0530, "V.Krishn" <vkris...@gmail.com>
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

[Kees Nuyt]

On Mon, 8 Jul 2013 06:43:33 +0100, Simon Slavin <slav...@bigfraud.org>
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

[Kees Nuyt]

On Thu, 4 Jul 2013 22:52:26 +0100, Simon Slavin <slav...@bigfraud.org>
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

[Kees Nuyt]

On Thu, 4 Jul 2013 15:15:14 -0400, "James K. Lowden"
<jklow...@schemamania.org> 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

[Kees Nuyt]

On Mon, 1 Jul 2013 07:23:54 +, Vijay Khurdiya
<vijay.khurd...@securetogether.com> 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?

[Kees Nuyt]

On Thu, 20 Jun 2013 06:37:22 +, YAN HONG YE <yanhong...@mpsa.com>
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

[Kees Nuyt]

On Sun, 9 Jun 2013 09:55:30 +0100, "Dave Wellman"
<dwell...@ward-analytics.com> 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

[Kees Nuyt]

On Sun, 21 Apr 2013 11:15:23 +0800 (CST), ?? <yunjie@163.com>
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

[Kees Nuyt]

On Wed, 10 Apr 2013 08:24:42 +, Bernardino Flores/Jeanologia
<bflo...@jeanologia-laser.com> 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

[Kees Nuyt]

On Mon, 08 Apr 2013 08:39:49 +0200, Alexandr N?mec <a.ne...@atlas.cz>
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

[Kees Nuyt]

On Wed, 27 Mar 2013 17:55:00 -0400, Jeff Archer
<jsarc...@nanotronicsimaging.com> wrote:

>On Wed, Mar 27, 2013 at 5:46 PM, David King <dk...@ketralnis.com> 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

[Kees Nuyt]

On Thu, 21 Feb 2013 18:00:58 +0100, Gert Van Assche
<ger...@datamundi.be> 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

[Kees Nuyt]

On Mon, 11 Feb 2013 05:42:17 -0800 (PST), Jeff Steffanina
<zc...@yahoo.com> 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

Re: [sqlite] Sqlite and AS400

[Kees Nuyt]

On Sun, 10 Feb 2013 18:42:08 + (GMT), Mauro Bertoli
<bertoli.ma...@yahoo.it> wrote:

> Hi all, I'm a new user in this list. Is possible to connect
> to a SQLITE database from AS400? Any help will be appreciated.
> Mauro

There appears to be a sqlite3 port to AS400 
embedded in "iSeries Python".

http://www.iseriespython.com/app/ispMain.py/Start?job=Posts==ViewSubject=LastPost=6=20=Y=598

If this is not what you are looking for, please specify 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] Fwd: How to prevent View sqlite database structure and contents from database browsers

[Kees Nuyt]

On Fri, 08 Feb 2013 19:46:49 +0100, Kees Nuyt <k.n...@zonnet.nl> wrote:

> ... to black access ...


... to block access ...

Note to self: proofread thoroughly.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users