[sqlite] Issue with sqlite3 / import / strictly numerical table names

[Alan Kinder]

It would appear to me that sqlite and sqlite3 are out-of-sync in respect of
the handling of tables with strictly numerical table names - please see the
following sqlite3  log demonstrating the problem:

-- Loading resources from C:\Users\Kinder/.sqliterc
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .sys type C:\\Users\\Kinder\\.sqliterc
.mode columns
.headers on
sqlite> create table "_666" (c1 TEXT, c2 TEXT);
sqlite> create table "666" (c1 TEXT, c2 TEXT);
sqlite> .tables
666   _666
sqlite> .mode csv
sqlite> .sys type file.csv
"A","B"
sqlite> .import "file.csv" "_666"
sqlite> select * from "_666";
c1,c2
A,B
sqlite> .import "file.csv" "666"
Error: near "666": syntax error
sqlite>
----
Please feel free to contact me if you require additional information.
Regards,
Alan Kinder
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite importing csv

[Alan]

many thanks again for the replies and sorry I was amiss in not stating

that it was sqlite3 DXCC.db, I was using the .db suffix  but to no avail,

 however the problem has been that I was not getting it into the right 
folder


to enter the import  command.

Thanks Bill you hit it on the nose, when bringing up the cmd screen

it registered it was sitting at  Users\John

By entering cd C:\jt65Logger that put it into the correct folder

and I was able to successfully import the test csv file,  very simple

but sadly overlooked by my ignorance.


Alan

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

Re: [sqlite] sqlite importing csv

[Alan]

By the way I have a folder  C:\jt65Logger that contains all the 
databases etc to do with the project.



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

Re: [sqlite] sqlite importing csv

[Alan]

Simon,

 I entered what you asaked  and no, that file is in

the users\John directory

so I guess that the cmd  sqlite3 DXCC is not shifting the focus to the 
directory that the DXCC database is in


and i would need to be using some other set of commands to make that happen

Alan



On 25-Aug-16 16:45, Simon Slavin wrote:

On 25 Aug 2016, at 4:03am, Alan <zl...@clear.net.nz> wrote:


attempt to import a short test Excel table
of two columns with about 4 records in it, the main one I was trying to import 
had the same trouble
with, it had over 300 records in it with two columns.


Issue this command:

sqlite3 DXCC.sqlite

then inside the SQLite shell tool do the following:

CREATE TABLE testTable (testColumn TEXT);
.once testoutput.txt
.schema
.quit

Do you find a new file called 'testoutput.txt' in the same folder as your 
anr.csv file ?


Assuming that you /do/ get the file you expected ...

Open anr.csv using NOTEPAD.

Does the file look like comma-separated values to you ?

Is the top line a line of data (probably numbers) or does it have column names ?

You're importing the data in that file into a table.

Have you already defined the table in your SQLite database before you issue the 
.import command or are you expecting SQLite to create the table for you ?

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


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

Re: [sqlite] sqlite importing csv

[Alan]

after clicking on the command it is in

 C:\users\john

after typing sqlite3 DXCCit shows  sqlite>  if I then enter cd  it 
shows ...>





On 25-Aug-16 18:01, Simon Slavin wrote:

On 25 Aug 2016, at 6:59am, Alan <zl...@clear.net.nz> wrote:


I right Clicked on start and selected the windows  cmd, and as I understand it 
at the moment

by typing sqlite3 DXCC then it should be sitting in the directory or folder 
that the DXCC  database is in

I don't think this is correct.  Once you have started cmd instead of starting 
'sqlite3', use this command 'cd'.  It reports your current directory.

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


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

Re: [sqlite] sqlite importing csv

[Alan]

I have created a directory for the project I am working on, and the 
Database and the csv file both reside in that directory.


I right Clicked on start and selected the windows  cmd, and as I 
understand it at the moment


by typing sqlite3 DXCC then it should be sitting in the directory or 
folder that the DXCC  database is in


so should accept the sqlite command to import the csv file that is also 
sitting in that directory.


I also tried it with the Admin cmd.


On 25-Aug-16 16:38, Scott Robison wrote:

On Aug 24, 2016 10:25 PM, "Alan" <zl...@clear.net.nz> wrote:

forgot to reply to one of the questions.

I am using version 3.13.0.0 and it is 64 bit

I am running Windows 10 Pro

computer is intel I3  3.5Ghz with 8Gb RAM.

As my friend has no problem loading the same csv file with his Windows7

computer

I am guessing that it may be to do with running Windows 10.

What directory are you in? Are you using an elevated command prompt?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] sqlite importing csv

[Alan]

forgot to reply to one of the questions.

I am using version 3.13.0.0 and it is 64 bit

I am running Windows 10 Pro

computer is intel I3  3.5Ghz with 8Gb RAM.

As my friend has no problem loading the same csv file with his Windows7 
computer


I am guessing that it may be to do with running Windows 10.


Alan


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

Re: [sqlite] sqlite importing csv

[Alan]

HI Guys
really appreciate all your replies.What I am doing at present is to 
attempt to import a short test Excel table
of two columns with about 4 records in it, the main one I was trying to 
import had the same trouble

with, it had over 300 records in it with two columns.
the test one has headers on both columns plus 4 lots of data, and I have 
tried importing it into an existing(empty) table called DXE and then 
also into a table not existing to see if it would add it.

I have saved it under Excel csv comma delimited
and the suffix of the file definitely says .csv when looking at the 
folder with  windows file explorer.
It is all part of a learning curve for me as I am trying to learn how to 
write small programs
with windows forms and feeding data into about 22 different databases, 
this particular one
I am going to try and use as a look up of information to be used 
elsewhere in other databases,

(hope I am making sense) I am not a computer guru but trying to learn (LOL).

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

Re: [sqlite] importing csv

[Alan]

Hi Simon,

firstly both the database and the csv files are in ther same folder.

I have gone into Windows cmd

typed   sqlite3 DXCC(database)

.mode csv

.separator ,

.import anr.csv DXE(DXCC table)

I have also tried putting in the full path to the csv file and 
unfortunately


the same result.


Alan


On 25-Aug-16 10:12, Simon Slavin wrote:

On 24 Aug 2016, at 11:04pm, Alan <zl...@clear.net.nz> wrote:


Firstly I am very new to sqlite, but I have a problem when trying to

import a .csv file into a database, getting  "cannot open (file).csv"

I am using Windows10 and wondering if anyone else has had this problem.

The program is probably looking at the wrong folder/directory.  Make sure you 
understand where the program thinks the file is.

What program are you using to open the file ?

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


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

[sqlite] importing csv

[Alan]

Firstly I am very new to sqlite, but I have a problem when trying to

import a .csv file into a database, getting  "cannot open (file).csv"

I am using Windows10 and wondering if anyone else has had this problem.

A friend has been able to import exactly the same csv file  but he is 
using windows7.


I would appreciate any help on this.

Alan Hughes

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

[sqlite] Possible Bug

[Alan Bryan]

I am running into an issue with SQLite that I think might be a bug. When I
run the PRAGMA table_info

command
on a view that uses an aggregate function, the data type always comes up
empty.

To duplicate the issue, create a new SQLite 3 database and add a table. Add
at least one field in the table and make sure its a numeric field (such as
integer or float or something). Then create a new view with a SELECT statement
similar to the following:

SELECT *, Sum(MyField) AS MySumField FROM MyTable

Now run sqlite3  and type the following:

PRAGMA table_info(MyView);

You will notice there is no data type for some reason. Here is my output
when I issued the command:

0|Field1|INTEGER|0||01|Field2|INTEGER|0||02|Field3|FLOAT|0||03|SumField2||0||0

[sqlite] unixGetPageSize needed but undefined

[Alan Hourihane]

Hi all,

I logged this bug via Gentoo, but it should be done here...

https://bugs.gentoo.org/show_bug.cgi?id=517110

"
This function is wrapped up inside SQLITE_OMIT_WAL, whereas it's needed 
elsewhere. This function needs to be moved out of this ifdef.

/*
** Return the system page size.
**
** This function should not be called directly by other code in this file. 
** Instead, it should be called via macro osGetpagesize().
*/
static int unixGetpagesize(void){
#if defined(_BSD_SOURCE)
  return getpagesize();
#else
  return (int)sysconf(_SC_PAGESIZE);
#endif
}


libtool: link: m68k-atari-mint-gcc -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 
-DSQLITE_OMIT_LOAD_EXTENSION=1 -DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_RTREE -O2 
-pipe -o sqlite3 shell.o  ./.libs/libsqlite3.a -lreadline -lcurses -lpthread
./.libs/libsqlite3.a(sqlite3.o):sqlite3.o:(.data+0x578): undefined reference to 
`unixGetpagesize'
collect2: error: ld returned 1 exit status
Makefile:487: recipe for target 'sqlite3' failed
make: *** [sqlite3] Error 1
"

Thanks,

Alan.

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

[sqlite] index creation 30x slower on Mac than on Linux

[Alan Frankel]

We're using SQLite to manage a roughly 250Mb database on both Linux and Mac. In 
order to make our lookups fast, we're creating an index table in the database. 
On Linux, creating the table takes about 200 seconds. On the Mac the same 
operation takes 6,400 seconds. Here's the "CREATE INDEX" statement we're using:

CREATE INDEX Full_Closure_Index ON Full_Closure(Client,Dependency)

Why is the Mac 30 times slower than Linux? They're both on the same network, 
accessing the same network drive location, so neither has the advantage of 
local disk access.

Any suggestions on how to improve the performance here would be very welcome.

FYI, we're using different version of SQLite on each architecture: v 3.6.23.1 
on Linux and v 3.7.7 on the Mac.

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

Re: [sqlite] light weight write barriers

[Alan Cox]

> > Barriers are pretty much universal as you need them for power off !
> 
> I'm afraid, no storage (drives, if you like this term more) at the moment 
> supports 
> barriers and, as far as I know the storage history, has never supported.

The ATA cache flush is a write barrier, and given you have no NV cache
visible to the controller it's the same thing.

> Instead, what storage does support in this area are:

Yes - the devil is in the detail once you go beyond simple capabilities.

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

Re: [sqlite] light weight write barriers

[Alan Cox]

> Isn't any type of kernel-side ordering an exercise in futility, since
>a) the kernel has no knowledge of the disk's actual geometry
>b) most drives will internally re-order requests anyway

They will but only as permitted by the commands queued, so you have some
control depending upon the interface capabilities.

>c) cheap drives won't support barriers

Barriers are pretty much universal as you need them for power off !

> Even assuming the drives honored all your requests without lying, how would 
> you really want this behavior exposed? From the userland perspective, there 
> are very few apps that care. Probably only transactional databases, really.

And file systems internally sometimes. A file system is after all a
transactional database of sorts.

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

Re: [sqlite] light weight write barriers

[Alan Cox]

> How about that recently preliminary infrastructure to send ORDERED commands 
> instead of queue draining was deleted from the kernel, because "there's no 
> difference where to drain the queue, on the kernel or the storage side"?

Send patches.

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

Re: [sqlite] light weight write barriers

[Alan Cox]

> I don't want to flame on this topic, but you are not right here. As far as I 
> can 
> see, a big chunk of Linux storage and file system developers are/were 
> employed by 
> the "gold-plated storage" manufacturers, starting from FusionIO, SGI and 
> Oracle.
> 
> You know, RedHat from recent times also stepped to this market, at least I 
> saw 
> their advertisement on SDC 2012. So, you can add here all RedHat employees.

Booleans generally should be reserved for logic operators. Most of the
Linux companies work on both low and high end storage. The two are not
mutually exclusive nor do they divide neatly by market. Many big clouds
use cheap low end drives by the crate, some high end desktops are using
SAS although given you can get six 2.5" hotplug drives in a 5.25" bay I'm
not sure personally there is much point

(and I used to have fibrechannel on my Thinkpad 600 when docked 8))

> Our discussion started not from "value-for-money", but from a constant demand 
> to 
> perform ordered commands without full queue draining, which is ignored by the 
> Linux storage developers for YEARS as not useful, right?

Send patches with benchmarks demonstrating it is useful. It's really
quite simple. Code talks.

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

Re: [sqlite] light weight write barriers

[Alan Cox]

> > Hopefully, eventually the storage developers will realize the value
> > behind ordered commands and learn corresponding SCSI facilities to
> > deal with them.
> 
> Eventually, drive manufacturers will realize that trying to price
> guage people who want advanced features such as TCQ, DIF/DIX, is the
> best way to gaurantee that most people won't bother to purchase them,
> and hence the features will remain largely unused

I doubt they care. The profit on high end features from the people who
really need them I would bet far exceeds any other benefit of giving it to
others. Welcome to capitalism 8)

Plus - spinning rust for those end users is on the way out, SATA to flash
is a bit of hack and people are already putting a lot of focus onto
things like NVM Express.

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

Re: [sqlite] primary key with bulk insert (UNION SELECT)

[Alan Frankel]

I ended up modifying the statement to add NULL to the ROWID column for each 
row. SQLite silently replaces the NULL with an automatically generated row id. 
This works even in older versions, so this was the solution I ended up using.

Thanks,
Alan

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Davies
Sent: Friday, October 12, 2012 8:42 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] primary key with bulk insert (UNION SELECT)

On 11 October 2012 15:07, Alan Frankel <alan.fran...@mathworks.com> wrote:
> I have a table that uses an autogenerated id as primary key. I want to do 
> bulk inserts using UNION SELECT, but it doesn't seem to be happy unless I 
> specify an id for each row:
>
> sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name 
> sqlite> VARCHAR(25), distance REAL); insert into CelestialObject 
> sqlite> select 'Betelguese' as name, 200 as distance UNION SELECT 
> sqlite> 'Procyon', 500;
> Error: table CelestialObject has 3 columns but 2 values were supplied
>
> If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
> AUTOINCREMENT") when I create the table, the error is the same. Can anyone 
> tell me whether there's a way to use a bulk insert without specifying an id 
> for each row?

insert into CelestialObject( name, distance ) select 'Betelguese' as name, 200 
as distance UNION SELECT 'Procyon', 500;

>
> Thanks,
> Alan
>

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

[sqlite] primary key with bulk insert (UNION SELECT)

[Alan Frankel]

I have a table that uses an autogenerated id as primary key. I want to do bulk 
inserts using UNION SELECT, but it doesn't seem to be happy unless I specify an 
id for each row:

sqlite> create table CelestialObject (id INTEGER PRIMARY KEY, name VARCHAR(25), 
distance REAL);
sqlite> insert into CelestialObject select 'Betelguese' as name, 200 as 
distance UNION SELECT 'Procyon', 500;
Error: table CelestialObject has 3 columns but 2 values were supplied

If I specify AUTOINCREMENT for the id (i.e., "id INTEGER PRIMARY KEY 
AUTOINCREMENT") when I create the table, the error is the same. Can anyone tell 
me whether there's a way to use a bulk insert without specifying an id for each 
row?

Thanks,
Alan

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

[sqlite] Understanding Foreign Key Contraints

[Alan Chandler]

I have a fairly complex web based application which helps manage the 
process of running an American Football results picking competition 
through the season.  I am in the process of porting it to Sqlite ready 
for the start of next season.


I just discovered a bug in my handling of a constraint violation that 
has made me want to rethink my strategy  in this area.


My original strategy was to completely cascade deletes, but the bug I 
discovered showed me a place in the user interface where that approach 
could be too dangerous, as it wasn't obvious that there would be side 
effects at the user interface level.  So I want to replan my strategy.   
But given the complexity of the situation I want to make sure I don't 
make any mistakes.


There is one particular pattern that occurs in several places, where 
what might happen is ambiguous (at least to me), and I would like this 
mailing lists view of what will happen and what is the right thing to do 
to make it so. [Note the application is web based with Ajax calls.  
Every single page request or ajax call opens the database and does a 
"PRAGMA foreign_keys = ON" as its first function]


Let me list my key entities in this pattern

At the top level there are three

"Team" with primary key tid (which is a three character string - but 
that is probably irrelevant)

"Participant" with primary key uid
and
"Competition" with primary key cid.

There are then some secondary entities, for this example I need two

"Registration" (user registers for a competition) which has primary key 
(cid,uid).  Its foreign key constraints are defined as


cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE 
ON DELETE CASCADE, -- Competition ID
uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE 
ON DELETE CASCADE, --User ID


"Team_in_competition" with primary key (cid,tid). Its foreign key 
constraints are defined as
cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE 
ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, -- Competition ID
tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON 
DELETE CASCADE, --TeamID


And then a derived entity from the other two

"Playoff_pick" with primary key (cid,uid,tid).  Its the foreign key 
constrains on this one which is rather tricky


I want to arrange my constraints so that.

Deleting Competition or Participant Deletes everything below it

Deleting Team_in_competition fails with a constrain violation when there 
is a playoff_pick that refers to it


I am hoping that I can define the constraints so.

FOREIGN KEY (cid,uid) REFERENCES registration(cid,uid) ON UPDATE CASCADE 
ON DELETE CASCADE,

FOREIGN KEY (cid,tid) REFERENCES team_in_competition(cid,tid)


What I am hoping is that if I delete the "Competition" (or 
"Participant") , then it deletes the "Registration" which in turn 
deletes the "Playoff_pick" immediately, but that because the deleting of 
"Team_in_competition" is deferred until commit time, by that time the 
commit happens there is no "Playoff_pick" to prevent the 
"Team_in_competition" from being deleted.


Have I understood this right?












--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Strange issue with sqlite 3.7.9

[Alan Chandler]

On 05/07/12 01:05, Richard Hipp wrote:

On Wed, Jul 4, 2012 at 3:05 PM, Alan Chandler<a...@chandlerfamily.org.uk>wrote:


The commit referenced by that page:

http://www.sqlite.org/src/info/b23ae131874bc5c621f0

went into 3.7.9. So the problem was probably introduced in
3.7.9, not 3.7.10.



Indeed - I just tried the test case in that ticket and in fact
demonstrated that the bug is in 3.7.9


Does that mean that the problem is fixed by
http://www.sqlite.org/src/info/0dc4cb9355 and does not exist in recent
releases of SQLite?  Or are you saying that this is a new problem that
needs to be addressed.  If the latter, I'm going to need you to send me a
database again so that I can reproduce the problem, because I did keep the
one you sent last time.
No, its the same problem and fixed in later releases.  The only issue is 
that the main web site lists the bug as starting in 3.7.10, when in fact 
it starts in 3.7.9.  This is unfortunate because the latest Ubuntu LTS 
release (12.04) uses 3.7.9, and so my application broke again.  (I have 
reported this to Ubuntu and they have at least acknowledged the bug).


The test case in this ticket http://www.sqlite.org/src/info/b7c8682cc1 
demonstrates the problem in 3.7.9


--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Strange issue with sqlite 3.7.9

[Alan Chandler]

On 04/07/12 18:52, Dan Kennedy wrote:

On 07/04/2012 08:26 PM, Alan Chandler wrote:

Due to hardware problems with my Debian Stable server, I have just
upgraded to Ubuntu-Server 12.04.

I have installed sqlite3 and when I ask it the version (with .version)
it replies
SQLite 3.7.9 2011-11-01 00:52:41 
c7c6050ef060877ebe77b41d959e9df13f8c9b5e


Which exactly complies with the sqlite web site for this release.

Sometime ago I reported a problem with 3.7.10 referenced here
http://www.sqlite.org/src/info/b7c8682cc1


The commit referenced by that page:

   http://www.sqlite.org/src/info/b23ae131874bc5c621f0

went into 3.7.9. So the problem was probably introduced in
3.7.9, not 3.7.10. 


Indeed - I just tried the test case in that ticket and in fact 
demonstrated that the bug is in 3.7.9




--
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] Strange issue with sqlite 3.7.9

[Alan Chandler]

Due to hardware problems with my Debian Stable server, I have just 
upgraded to Ubuntu-Server 12.04.


I have installed sqlite3 and when I ask it the version (with .version) 
it replies

SQLite 3.7.9 2011-11-01 00:52:41 c7c6050ef060877ebe77b41d959e9df13f8c9b5e

Which exactly complies with the sqlite web site for this release.

Sometime ago I reported a problem with 3.7.10 referenced here 
http://www.sqlite.org/src/info/b7c8682cc1


Using the same database (which I would prefer not to publish because its 
full of personal financial information - I already gave Richard Hipp a 
randomised copy in respect of the last bug) I have experienced a problem 
shown below.  I am not sure this is the same problem as before (its very 
similar) but that was reported as being introduced with changes 
introduced in 3.7.10


select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM 
dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 
'Hartley' AND ((t.src = a.name and t.srccode = c.id) ) GROUP BY c.id;


Produces output, where as

select c.id,c.type,c.description,sum(t.dfamount) AS tamount FROM 
dfxaction as t, account as a,code AS c WHERE c.type = 'C' and a.domain = 
'Hartley' AND ((t.src = a.name and t.srccode = c.id) OR (t.dst = a.name 
and t.dstcode = c.id )) GROUP BY c.id;


does not even though ONLY added an OR clause within a bracketed AND clause

Just to confuse the issue dfxaction (but not the other tables) is a view 
- defined as below (and I think this is where the similarity to the 
other bug comes in).  If I replace that with xaction (the real table its 
based on) then the second select above does produce expected output.


CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,

CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN 
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)

END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;

ALSO just to confirm - I repeated the same experiment on sqlite3 version 
3.7.13 (Debian unstable version) and the second query performs perfectly


I am mentioning this here because the earlier bug was supposed to have 
been caused by a change made by 3.7.10, whereas this is 3.7.9 and given 
its the version of choice in ubuntu it might be better to clarify 
whether there is a problem there or not.


--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 09/03/12 00:29, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 6:47 PM, Alan Chandler<a...@chandlerfamily.org.uk>wrote:


The complete database schema is here

https://github.com/akc42/**AKCMoney/blob/master/app/inc/**database.sql<https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql>

My database is full of private financial data so I would rather not just
post it publically.  If you really need the data I could mail it to you
privately (its only 366kb big)


Can you scrub the data (replace numbers with values from random(), and all
strings with random text?)  If not, email the database directly to me.



I have done some randomisation and sent it to you privately

--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 23:32, Richard Hipp wrote:

On Thu, Mar 8, 2012 at 5:44 PM, Alan Chandler<a...@chandlerfamily.org.uk>wrote:


I have been running a financial management application application I wrote
for a number of years.  My "production" version runs on Debian stable
system as a result is running sqlite v3.7.3.  My personal development
machine is running Debian unstable and as a result has sqlite 3.7.10.

Earlier this week I discovered a bug in a rarely used part of the
application, so took a copy of the production database and ran it on my
development machine. I quickly found that and fixed it, but another major
element of the application appeared to give some strange results.

I have spend some down tracking down what caused the problem, and it seems
to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes the sql.
  It seems to me that the later release gets things wrong - but it might be
that the newer version has some sort of PRAGMA that I am not using right.
  So I would like to ask here where I am going wrong.

The basic issue is around a view on a table called "xaction" - the
transactions processed.  It has optional "source" and "destination"
accounts (must be at least one or the other but can also have both) and
optional "codes" that relate to classes of transaction as they appear in
the account.  I put a view on top of this which normalises the currency for
use in my accounts. The schema for the view is ...

CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst,
dstcode,t.description, rno, repeat,
CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS INTEGER)
END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;


The query that results in differences between the two versions of sqlite
(I have cut this down to the minimum I could find showed the difference).

SELECT
c.id AS id, c.type AS type, c.description AS description, t.*
FROM
dfxaction AS t, code AS c
WHERE
t.date BETWEEN 129384 AND 1325375999
AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
ORDER BY
c.description COLLATE NOCASE ASC;

The little part "t.src IS NOT NULL AND" seems to be the trigger for
cutting down the records to almost none from the full amount because when I
remove it I got more like the correct number of records.  Most of the
records excluded by putting the clause in DO NOT have t.src of NULL.

AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.

The other important aspect.  If I don't use a view, but instead replace
"FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10
delivers all the records I would expect.

So I am completely perplexed as to why there are changes between
behaviour.  Can anyone help me understand.


Maybe the change at http://www.sqlite.org/src/info/b23ae13187 broke
something.  Can you get us a complete schema with enough data to actually
run a test case that shows the problem?

The complete database schema is here

https://github.com/akc42/AKCMoney/blob/master/app/inc/database.sql

My database is full of private financial data so I would rather not just 
post it publically.  If you really need the data I could mail it to you 
privately (its only 366kb big)



--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

On 08/03/12 22:44, Alan Chandler wrote:
I have been running a financial management application application I 
wrote for a number of years.  My "production" version runs on Debian 
stable system as a result is running sqlite v3.7.3.  My personal 
development machine is running Debian unstable and as a result has 
sqlite 3.7.10.


Earlier this week I discovered a bug in a rarely used part of the 
application, so took a copy of the production database and ran it on 
my development machine. I quickly found that and fixed it, but another 
major element of the application appeared to give some strange results.


I have spend some down tracking down what caused the problem, and it 
seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 
processes the sql.  It seems to me that the later release gets things 
wrong - but it might be that the newer version has some sort of PRAGMA 
that I am not using right.  So I would like to ask here where I am 
going wrong.




Things have now got stranger.  I just saw the post on the e-mail list 
for sqlitestudio and thought that looks interesting, so I have now 
downloaded it.  It seems to be using sqlite 3.7.8


It works correctly, and the very same sql using sqlite manager in 
Mozilla goes wrong (this is linked to sqlite 3.7.10)


--
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Alan Chandler]

I have been running a financial management application application I 
wrote for a number of years.  My "production" version runs on Debian 
stable system as a result is running sqlite v3.7.3.  My personal 
development machine is running Debian unstable and as a result has 
sqlite 3.7.10.


Earlier this week I discovered a bug in a rarely used part of the 
application, so took a copy of the production database and ran it on my 
development machine. I quickly found that and fixed it, but another 
major element of the application appeared to give some strange results.


I have spend some down tracking down what caused the problem, and it 
seems to be a difference in how sqlite 3.7.3 and sqlite 3.7.10 processes 
the sql.  It seems to me that the later release gets things wrong - but 
it might be that the newer version has some sort of PRAGMA that I am not 
using right.  So I would like to ask here where I am going wrong.


The basic issue is around a view on a table called "xaction" - the 
transactions processed.  It has optional "source" and "destination" 
accounts (must be at least one or the other but can also have both) and 
optional "codes" that relate to classes of transaction as they appear in 
the account.  I put a view on top of this which normalises the currency 
for use in my accounts. The schema for the view is ...


CREATE VIEW dfxaction AS
SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,

CASE
WHEN t.currency = 'GBP' THEN t.amount
WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
WHEN t.dstamount IS NOT NULL AND da.currency = 'GBP' THEN 
t.dstamount
ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)

END AS dfamount
FROM
xaction AS t
LEFT JOIN account AS sa ON t.src = sa.name
LEFT JOIN account AS da ON t.dst = da.name
LEFT JOIN currency ON
t.currency != 'GBP' AND
(t.srcamount IS NULL OR sa.currency != 'GBP') AND
(t.dstamount IS NULL OR da.currency != 'GBP') AND
t.currency = currency.name;


The query that results in differences between the two versions of sqlite 
(I have cut this down to the minimum I could find showed the difference).


SELECT
c.id AS id, c.type AS type, c.description AS description, t.*
FROM
dfxaction AS t, code AS c
WHERE
t.date BETWEEN 129384 AND 1325375999
AND ((t.src IS NOT NULL AND t.srccode = c.id)  OR t.dstcode = c.id )
ORDER BY
c.description COLLATE NOCASE ASC;

The little part "t.src IS NOT NULL AND" seems to be the trigger for 
cutting down the records to almost none from the full amount because 
when I remove it I got more like the correct number of records.  Most of 
the records excluded by putting the clause in DO NOT have t.src of NULL.


AND OF COURSE ON sqlite 3.7.3 I get all the records I expect.

The other important aspect.  If I don't use a view, but instead replace 
"FROM dfxaction" with "FROM xaction" using the raw table then 3.7.10 
delivers all the records I would expect.


So I am completely perplexed as to why there are changes between 
behaviour.  Can anyone help me understand.


Thanks

--
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] How to Use an Apostrophe in a Text Field?

[Alan Holbrook]

Sorry if my reply didn't make it through the first time, but doubling up on
the apostrophes worked just fine.  My thanks to those of you who suggested
it.

On Thu, Apr 21, 2011 at 4:27 AM, venkat easwar <hareas...@yahoo.com> wrote:

>
>
>  The apostrophes are escaped by apostrophes. One more way you can do.
>
> insert into  () values ("*Goin' Down
> >> the Road Feelin' Bad*");
>
> It is double quotes before and after *. Similarly double quotes will be
> escaped
> by one more double quote
> VENKAT
>
>
>
>
> 
> From: Jim Morris <jmor...@bearriver.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Mon, April 18, 2011 8:25:54 PM
> Subject: Re: [sqlite] How to Use an Apostrophe in a Text Field?
>
> Did you try doubling the apostrophes?
>
> *Goin'' Down the Road Feelin'' Bad*
>
>
> On 4/17/2011 6:16 PM, Simon Slavin wrote:
> > On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
> >
> >> I'm using SQLite with VBE2008.  I've defined a table with a number of
> text
> >> fields in it.  If the information I want to write to the database
> contains
> >> an embedded apostrophe, the program throws an error.  That is, if I set
> >> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
> >> correctly and the program continues.  But if I set textfield1 to *Goin'
> Down
> >> the Road Feelin' Bad*, I get an error.
> >>
> >> Is there a way I can use an apostrophe in the data to be written?
> > Your library might do it for you.  If you're writing directly to the
> SQLite
> >library then I believe you can double the apostrophe:
> >
> > Goin'' Down the Road Feelin'' Bad
> >
> > so it might be worth trying that.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-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] How to Use an Apostrophe in a Text Field?

[Alan Holbrook]

Thanks to all for all the suggestions.  Doubling up the apostrophe worked.

On Mon, Apr 18, 2011 at 10:55 AM, Jim Morris <jmor...@bearriver.com> wrote:

> Did you try doubling the apostrophes?
>
> *Goin'' Down the Road Feelin'' Bad*
>
>
> On 4/17/2011 6:16 PM, Simon Slavin wrote:
> > On 17 Apr 2011, at 11:54pm, Alan Holbrook wrote:
> >
> >> I'm using SQLite with VBE2008.  I've defined a table with a number of
> text
> >> fields in it.  If the information I want to write to the database
> contains
> >> an embedded apostrophe, the program throws an error.  That is, if I set
> >> textfield1 to *Going Down the Road Feeling Bad*, the data gets written
> >> correctly and the program continues.  But if I set textfield1 to *Goin'
> Down
> >> the Road Feelin' Bad*, I get an error.
> >>
> >> Is there a way I can use an apostrophe in the data to be written?
> > Your library might do it for you.  If you're writing directly to the
> SQLite library then I believe you can double the apostrophe:
> >
> > Goin'' Down the Road Feelin'' Bad
> >
> > so it might be worth trying that.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to Use an Apostrophe in a Text Field?

[Alan Holbrook]

I'm using SQLite with VBE2008.  I've defined a table with a number of text
fields in it.  If the information I want to write to the database contains
an embedded apostrophe, the program throws an error.  That is, if I set
textfield1 to *Going Down the Road Feeling Bad*, the data gets written
correctly and the program continues.  But if I set textfield1 to *Goin' Down
the Road Feelin' Bad*, I get an error.

Is there a way I can use an apostrophe in the data to be written?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Detach says database is locked

[Alan Chandler]

On 21/10/10 00:35, Igor Tandetnik wrote:
> Alan Chandler<a...@chandlerfamily.org.uk>  wrote:
>> Further to my other post related to attaching to databases with PHP PDO,
>>   I have now managed to ATTACH OK
>>
>> However, when I come to DETACH, I am getting a Database is locked error
>> when I try and execute it.
>>
>> The only thing happening to that database in between ATTACH and DETACH
>> is a single row SELECT
>
> Make sure you reset or finalize the statement (not sure how it's done in PHP).
I believe closeCursor();  does the job.  If not, I unset the variable.

However, I have figured out the problem - which is really sad since I 
can't do what I hoped - which is loop round a set of rows from a higher 
level select statement ATTACHing and DETACHing to a database in turn . 
Because the top level select loop is its own transaction, you can't 
detach from the database which you attached in the inner part of the 
loop since at that moment you are in a transaction.

I think my way out of the problem is to pull out all the rows into a 
single array, then close the transaction and interate over the array 
members.  Fortunately in the case its just a menu - so there probably 
won't be too many items.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Detach says database is locked

[Alan Chandler]

Further to my other post related to attaching to databases with PHP PDO, 
  I have now managed to ATTACH OK

However, when I come to DETACH, I am getting a Database is locked error 
when I try and execute it.

The only thing happening to that database in between ATTACH and DETACH 
is a single row SELECT

I don't really understand why I can't DETACH.  Can anyone give me any 
ideas what this could be.
-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange position as a result of ATTACH

[Alan Chandler]

I am testing an application written in PHP, so all sqlite access is 
through PDO.

In a particular situation I am scanning a directory for filenames with a 
'.db' extension and attaching to each database in turn to do something 
with it. I am using a prepared statement ($astmt) to do the attach, 
binding it with each filename that matches the database type and 
executing the prepared statement.

Thus ...

$db=new PDO('sqlite:'.DATA_DIR.'football.ini');
...

$astmt = $db->prepare("ATTACH ? AS comp");
$fns = scandir(DATA_DIR);
foreach ($fns as $filename) {
if(filetype(DATA_DIR.$filename) == 'file') {
$split = splitFIlename($filename);
if($split[1] == 'db') {
// found a database file
$astmt->bindValue(1,DATA_DIR.$filename);
$astmt->execute(); //ATTACH
...
$astmt->closeCursor();
$db->exec("DETACH comp;");
}
}
}



The first time round the loop seems to work fine, but the second time 
round the loop, the $astmt->execute(); trying to attach to the file 
fails with SQLITE_SCHEMA

Reading the docs, it appears SQLITE_SCHEMA means I need to recompile the 
prepared statement each time round the loop.

WHY?

(I tried it and it works - but is inefficient).

PS = I suppose it may be a php bug that it is using the v1 version of 
prepare





-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] moving from mysql

[Alan Chandler]

On 16/10/10 21:07, Chris Percol wrote:
> Hi there,
>
> I am thinking of moving from mysql to sqlite3 and want to know if there are
> any gotchas?
>
> My current situation is a web based school booking system using a mysql
> database with four tables. The busiest of the tables may have 6000 rows
> added each year (I tend to empty this table at the end of the year). The
> other tables store a little information, maybe 100 rows between them.
>
> There is approx 100 users creating bookings throughout the week, not a lot
> of concurrents but I guess the possibility is there.
>
>  From what I read on the sqlite site sqlite could handle this like a breeze.
> I just wanted to ask for any thoughts regarding performance or limitations
> before I make the move.
>


I don't know what language your web application is in, but I have just 
completed a port from Postgres of a financial accounting and planning 
application I wrote several years ago for myself.  This was using PHP.

 From a performance perspective, it is really fast.  I open a 
transaction and the head of most web pages, and keep it open through out 
the page even if I am only using selects, and every page I have comes up 
instantenously.  Even one which calculates the complete profit and loss 
for my business, including calculating depreciation for capital 
purcheses made part way through a year finishes fast than you can notice.

Riding on the success of that I am part way through porting an American 
Football results picking competition

I think its important for a multiuser application to use WAL mode and I 
think this minimises the potential for locking conflicts from each of 
users.  The benefit of a web application is that each page request opens 
the database, does its stuff and closes it again.  There is therefore a 
reasonably large probability that all connections to the database are 
closed in a reasonably short space of time.  In WAL mode, the last 
database close encorporates the WAL back into the main database, so 
there is relatively little time when its not encorporated back in.

For me, the biggest problem is having sufficiently recent versions of 
all the code and libraries to support WAL mode.  At least one host 
(Bluehost) that I use can't handle that.


-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Nth row of on sqlite DB

[Alan Chandler]

On 14/10/10 17:26, Kavita Raghunathan wrote:
> I found an example:
> Select * from  order by ID where limit 1 offset n-1
>
> Will retreive the nth row.
> In this case order by ID is probably not necessary as its already ordered
> with a few missing rows.

It might happen to be ordered - but that is an implementation detail. 
Unless you use the ORDER BY clause the database may deliver the records 
in any order it wishes. It is not forced to use the ID order.


-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is the SYNTAX diagram wrong

[Alan Chandler]

I seem to be doing plenty of

SELECT * FROM a LEFT JOIN b WHERE ...;

but looking at the syntax diagrams at

http://www.sqlite.org/lang_select.html

it looks like I have to follow LEFT with OUTER.

Shouldn't the diagram allow OUTER to be bypassed?

-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Just want to double check on index need

[Alan Chandler]

On 14/10/10 17:28, Dan Kennedy wrote:
>
> On Oct 14, 2010, at 10:43 PM, Alan Chandler wrote:
>> CREATE TABLE div_winner_pick (
...
>>  PRIMARY KEY (cid,confid,divid,uid)
>> );

...
>>
>> CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid);


...

> It should be clearer. Basically the index would be redundant
> if it contains the same columns in the same order as the primary
> key. Or a prefix thereof. i.e. the following indexes would be
> all be redundant (pure overhead for no benefit):
>
>  CREATE INDEX x ON div_pick_winner(cid);
>  CREATE INDEX x ON div_pick_winner(cid,confid);
>  CREATE INDEX x ON div_pick_winner(cid,confid,divid);
>  CREATE INDEX x ON div_pick_winner(cid,confid,divid,uid);
>
> Your index is not redundant though.

This is interesting - what if I changed the primary key to be

PRIMARY KEY (uid,cid,confid,divid)

Is that an optimisation that is useful to make?


-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Just want to double check on index need

[Alan Chandler]

I am porting an application (American Football Results Picking 
Competition) over from a Postgres databaseo to SQLite which involves 
some fairly intense queries.  I am doing this partially to do some 
performance comparisons although I have some other reasons too.

I just want to make sure that I am setting up the indexes to some of the 
tables correctly.

Here is an example of a representative type of table

CREATE TABLE div_winner_pick (
 cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE 
ON DELETE CASCADE, -- Competition ID
 confid character(3) NOT NULL REFERENCES conference(confid) ON 
UPDATE CASCADE ON DELETE CASCADE, --Conference ID
 divid character(1) NOT NULL REFERENCES division(divid) ON UPDATE 
CASCADE ON DELETE CASCADE, --Division ID
 uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE 
ON DELETE CASCADE, --User ID
 tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON 
DELETE CASCADE, --Team who will win division
 submit_time bigint DEFAULT (strftime('%s','now')) NOT NULL, --Time 
of submission
 PRIMARY KEY (cid,confid,divid,uid)
);

where the Primary key references several columns

For this particular table - in my Postgres definition I created the 
following two indexes

CREATE INDEX div_win_pick_uid_idx ON div_winner_pick (uid);
CREATE INDEX div_win_pick_cid_idx ON div_winner_pick (cid);

i.e.  Two of the 4 fields that make up the primary key.

and I was anticipating doing the same - or something similar - I am not 
yet convinced I don't need to do

CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid);


However, I came across the following text on the SQLite Web Site as part 
of the explanation of the CREATE TABLE command



"INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY 
constraints are implemented by creating an index in the database (in the 
same way as a "CREATE UNIQUE INDEX" statement would). Such an index is 
used like any other index in the database to optimize queries. As a 
result, there often no advantage (but significant overhead) in creating 
an index on a set of columns that are already collectively subject to a 
UNIQUE or PRIMARY KEY constraint."


I just wanted to check that lack of advantage (and overhead) applies 
purely to an index across all columns of the primary key and that if I 
need the index across a lesser number of columns (because I am querying 
for all records that match where I can define the values "cid" and "uid" 
in the example above) it is still and advantage to create it separately.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Which data type is better for date?

[Alan Chandler]

On 04/09/10 12:31, Mike Zang wrote:
> I try to convert data to SQLite3 for iPad, please give me some detail
> suggestion.
>

I don't think this applies to you, but I had to build an application 
where time for the user has to be reasonably accurate (an American 
Football picking competition, where the deadline was 5 minutes before 
each match)  My users are worldwide.

I realised that on the server end, I could carry the date/time around as 
a UNIX timestamp (ie seconds from 1970 UTC) and then use javascript on 
the client end (in a browser) to locally display stuff as (after 
multiplying by 1000).

As a result, I almost always think about that approach as my first 
choice when writing a new app.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 20:29, Alan Chandler wrote:
> On 26/08/10 17:38, Simon Slavin wrote:
>
>> So someone can check it out.  Try it with a VIEW that definitely doesn't 
>> exist, or use
>>
>> CREATE VIEW IF NOT EXISTS ...
>>
>
> As far as I can work it out, the statement then prepares OK - but seems
> then to execute as a no op.  Since having completed that script and then
> checking the schema with the command line sqlite3 utility, the view no
> longer exists.
>
> This seems completely wrong.  Surely it should do this check at
> *execute* time not at *prepare* time.
>


Life is just too short.  The benefit of a prepared statement is lost 
when you can't have parameters and you are only using it once anyway, so 
whilst there is a slight lengthening of the time when the database is 
Locked, its just easier to move the whole thing into a PDO::exec 
function (which in SQLITE terms I think prepares and then executes 
immediately).

I did this, and my code now works fine.

There might be a discussion about the difference between semantic and 
syntactic validation of prepared statements, but I am not expert enough 
in SQL to know what the perceived wisdom in this area is.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 17:38, Simon Slavin wrote:

> So someone can check it out.  Try it with a VIEW that definitely doesn't 
> exist, or use
>
> CREATE VIEW IF NOT EXISTS ...
>

As far as I can work it out, the statement then prepares OK - but seems 
then to execute as a no op.  Since having completed that script and then 
checking the schema with the command line sqlite3 utility, the view no 
longer exists.

This seems completely wrong.  Surely it should do this check at 
*execute* time not at *prepare* time.

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 13:38, Simon Slavin wrote:
>
> On 26 Aug 2010, at 12:12pm, Alan Chandler wrote:
>
>> This time it reported that the view it would have created failed because
>> the table (view) already existed.
>
> I'm sorry to ask this, but can you check for us whether a VIEW by that name 
> really does exist ?  Don't forget, VIEWs get saved in the file, they're not 
> part of the attachment.

Yes it does - this VIEW outputs the transactions in the default currency 
(rather than the currency of the transaction) and the whole objective of 
my this particular php script is to update this view when the default 
currency changes.


a...@kanga:~/dev/money/db[master]$ sqlite3 money.db
SQLite version 3.7.0
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema dfxaction
CREATE VIEW dfxaction AS
 SELECT t.id,t.date,t.version, src, srccode, dst, 
dstcode,t.description, rno, repeat,
 CASE
 WHEN t.currency = 'GBP' THEN t.amount
 WHEN t.srcamount IS NOT NULL AND sa.currency = 'GBP' THEN 
t.srcamount
 WHEN t.dstamount IS NOT NULL AND da .currency = 'GBP' THEN 
t.dstamount
 ELSE CAST ((CAST (t.amount AS REAL) / currency.rate) AS 
INTEGER)
 END AS dfamount
 FROM
 xaction AS t
 LEFT JOIN account AS sa ON t.src = sa.name
 LEFT JOIN account AS da ON t.dst = da.name
 LEFT JOIN currency ON
 t.currency != 'GBP' AND
 (t.srcamount IS NULL OR sa.currency != 'GBP') AND
 (t.dstamount IS NULL OR da.currency != 'GBP') AND
     t.currency = currency.name;
sqlite>


-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Parameters in views preparation

[Alan Chandler]

On 26/08/10 12:20, Pavel Ivanov wrote:

> Yes, "validation" happens only at the time of execution. So you are
> apparently doing something wrong and you better show your code.
>

easiest is to provide links to a copy.  I've added a .txt extension to 
all the files to stop them being executed by the web server

The key php file I am talking about is this one

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/updatedefcur.php.txt

It is trying to update the view (dfxaction) which provides transaction 
amounts in the default currency because the default currency is changing..


You will see it requires db.inc which is here

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/db.inc.txt

This initializes the global variable $db by opening the sqlite database.

The whole database schema is loaded on initial initialization by reading 
this file

The dfxaction view is created almost at the end of this file.

http://www.chandlerfamily.org.uk/sites/default/files/files/snippets/database.sql.txt

(someone asked further down the thread whether the view really exists. 
Yes it does and its not temporary).



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Suggestion for project: Chat

[Alan Chandler]

On 24/08/10 17:21, Artur Reilin wrote:
> I thinking about coding a chat with sqlite. You know the most hosts not
> allow you to use chats or something that way, because they write and read
> to often data from mysql database.
>
> So my thought is to make a chat which use forums software mysql tables for
> user data, but writes and read chat text from sqlite database. As the chat
> content get pruned after some time, the database can't grow big. So the
> insert and selects should be fast.
>


I have already made a chat that uses SQLite.  Its available to clone 
from my git repository (licenced under the GPL).

See http://www.chandlerfamily.org.uk/content/software

To clone the repository you need

git clone git://www.chandlerfamily.org.uk/mbchat.git

This originally started life as an mysql database version, but using it 
on the web site I wrote it for (http://www.melindasbackups.com) it ran 
out of steam when 20 people were using it (as it had to poll the 
database every 2 seconds per user to get a sensible performance).

I went through some intermediate stage of using named pipes and sqlite 
before landing on the current version that forks a chat server for the 
time that anyone is in chat.  I have also added optional security 
controls so that its possible to encrypt chat messages.

It has multiple rooms which are added via adding them to the database, 
and various permissions and colours for users. (users see some rooms as 
public, others are moderated, and others they see only when they are 
members of a specific group - we used them as committee rooms).

There is a useable demo here

http://chat.hartley-consultants.com/

which is stand alone.  The code can also link to an SMF forum and derive 
username from the login identity of the user on the forum.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] SQlite 3.7.0 fix for sys/mman.h inclusion

[Alan Hourihane]

When building sqlite 3.7.0 on a system that doesn't have mmap, I use the
SQLITE_OMIT_WAL option, but this check didn't make it around the
 inclusion.

Patch attached to fix this problem.

Thanks,

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

Re: [sqlite] [PHP5-FPM] Sqlite3 or pdo_sqlite?

[Alan Chandler]

On 24/07/10 15:41, Alan Chandler wrote:
> On 24/07/10 14:18, J. King wrote:
>
>> PDO_sqlite3 also does have the advantage of being available by default
>> since PHP 5.0.0, whereas sqlite3 is only available by default since PHP
>> 5.3.0.  I'm aware of no other advantages to using PDO, and from what I've
>> read it's on the slow side.
>>
>
> Right now its the only php library that calls (or enables a call) to
> sqlite_busy_timeout.
>
> There is a patch which will make it into php 5.3.3 when it is released
> "very shortly" that does provide for that call.
>
> This makes the sqlite3 pretty bad for applications (such as the typical
> web site) where there might be some locking issues.
>
>
I meant that right now PDO:: is the only library that enables the call. 
  Sqlite3 will get it shortly

-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] [PHP5-FPM] Sqlite3 or pdo_sqlite?

[Alan Chandler]

On 24/07/10 14:18, J. King wrote:

> PDO_sqlite3 also does have the advantage of being available by default
> since PHP 5.0.0, whereas sqlite3 is only available by default since PHP
> 5.3.0.  I'm aware of no other advantages to using PDO, and from what I've
> read it's on the slow side.
>

Right now its the only php library that calls (or enables a call) to 
sqlite_busy_timeout.

There is a patch which will make it into php 5.3.3 when it is released 
"very shortly" that does provide for that call.

This makes the sqlite3 pretty bad for applications (such as the typical 
web site) where there might be some locking issues.


-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Help with complex UPDATE question

[Alan Chandler]

On 22/07/10 23:38, peterwinson1 wrote:
>
> Hello,
>
> I have a some what complex question about UPDATE.  I have the following
> table
>
> table1 (KEY, COL1)
>
> 0, 1
> 1, 2
> 2, 3
> 3, 4
>
> What I would like to do is to UPDATE COL1 by subtracting the COL1 value
> where KEY = 0 from the COL1 value of the current row so that the result
> would be.
>
> 0, 0
> 1, 1
> 2, 2
> 3, 3
>
> Can this be done in SQL?  It does not have to be one UPDATE/SELECT
> statement.
>
> Thank you
> pw
>
>

UPDATE table1 SET COL1 = (COL1 - (SELECT COL1 FROM table1 WHERE key = 0));




-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

Re: [sqlite] Couple of questions about WAL

[Alan Chandler]

On 22/07/10 17:14, Dan Kennedy wrote:
>

> When in WAL mode, clients use file-locks to implement a kind of
> robust (crash-proof) reference counting for each database file.
> When a client disconnects, if it is the only client connected to
> that database file, it automatically runs a checkpoint and
> then deletes the *-wal file.
>

Aah - this is an important point which I didn't get from the docs.  I 
thought the wal was only cleared after the 1000 page watermark had been 
reached.

>
> So one thing to bear in mind when using WAL mode is always to
> call sqlite3_close() on all database handles before process
> exit. Otherwise you may leave a big *-wal file in the file-system
> which will need to be traversed by the next client that connects
> to the database.

Thanks - that is an important point.  I don't do that at the moment.


I wrote
>> 2) If the WAL is not synced back to the database (because the 1000
>> pages
>> have yet to be created), is it still in non volatile storage, synced
>> at
>> the end of the last commit.  In other words, if the server happens to
>> get shut down and rebooted, will the WAL still be there and continue
>> to
>> function from the last committed transaction.  (And, given question 1
>> the WAL index will just be rebuilt).
>
> Yes and yes. Assuming you are using synchronous=FULL. If using
> synchronous=NORMAL then the contents of the WAL may or may not
> have made it to persistent media when the crash occured. In this
> case the checksums in the WAL are used to recover as many
> transactions as possible.

I am not so worries about a system crash killing a transaction in the 
middle as an otherwise quiescent system being shut down normally.

However, as you say, the last closed database connection clears the WAL 
anyway, so I don't think this matters


>> 4) Is the escalation of locks process process still in place.  In
>> particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN
>> EXCLUSIVE the same?  (My current approach is that for web access that
>> does only reads, I do a BEGIN, and then the SELECTS and then COMMIT,
>> for
>> one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an
>> application level check (via SELECT) on a version field on the
>> database
>> to ensure its the same as when I originally read it, ROLLBACK if it
>> isn't, but if it is proceed with the INSERTS and UPDATES and finally
>> COMMIT).
>
> In WAL mode, "BEGIN IMMEDIATE" and "BEGIN EXCLUSIVE" do the same
> thing - open a write transaction. In both cases readers are not
> affected (different from rollback mode - in rollback mode a
> "BEGIN EXCLUSIVE" would lock out all readers).

I have a worry about consistency of view rather than whether or not a 
reader is locked out.

If I do a BEGIN, SELECT1 and at that point a writer does BEGIN 
IMMEDIATE, SELECT3, UPDATE, COMMIT, and then I continue with SELECT2 
COMMIT, will SELECT1 and SELECT2 have a consistent view of the database 
unaffected by the UPDATE in the middle.  In other words, is the Readers 
view of how far up the WAL it is allowed to look get controlled by the 
BEGIN ... COMMIT bracket rather than just the individual SELECTS it is 
performing.



-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] Couple of questions about WAL

[Alan Chandler]

I have been reading about WAL, and there are a few questions I would 
like to ask.

1)  I am slightly confused about readers building the WAL index. It says 
way down the page

quote:

Using an ordinary disk file to provide shared memory has the 
disadvantage that it might actually do unnecessary disk I/O by writing 
the shared memory to disk. However, the developers do not think this is 
a major concern since the wal-index rarely exceeds 32 KiB in size and is 
never synced. Furthermore, the wal-index backing file is deleted when 
the last database connection disconnects, which often prevents any real 
disk I/O from ever happening.

/quote

In a situation where I have a web application (php based) in essence 
each request makes a database connection, does a couple of queries, and 
exits (thereby closing the connection).  Unless my site gets really 
loaded (which in the application I am thinking of porting over to SQLite 
doesn't happen) it is quite frequent that there are no open database 
connections.  Does this mean that every web access has to rebuild the 
index just to perform a query?  is this a long process?

2) If the WAL is not synced back to the database (because the 1000 pages 
have yet to be created), is it still in non volatile storage, synced at 
the end of the last commit.  In other words, if the server happens to 
get shut down and rebooted, will the WAL still be there and continue to 
function from the last committed transaction.  (And, given question 1 
the WAL index will just be rebuilt).

3) When the 1000 page (or perhaps a smaller number that might be more 
appropriate in my case) watermark is passed, does the writer (if not 
interrupted) completely clear the WAL.

4) Is the escalation of locks process process still in place.  In 
particular, are the semantics of BEGIN, BEGIN IMMEDIATE and BEGIN 
EXCLUSIVE the same?  (My current approach is that for web access that 
does only reads, I do a BEGIN, and then the SELECTS and then COMMIT, for 
one that has some INSERT and UPDATES, I do a BEGIN IMMEDIATE, do an 
application level check (via SELECT) on a version field on the database 
to ensure its the same as when I originally read it, ROLLBACK if it 
isn't, but if it is proceed with the INSERTS and UPDATES and finally 
COMMIT).
-- 
Alan Chandler
http://www.chandlerfamily.org.uk

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

[sqlite] Understanding how to use locking optimally

[Alan Chandler]

xample, sqlite will wait for up to this time value before 
giving up on obtaining an writable lock, but other drivers may interpret 
this as a connect or a read timeout interval. Requires int 
<http://uk.php.net/manual/en/language.types.integer.php> .

which sort of implies that it does).


I presume many people have experience of the sort of application pattern 
I am describing.  What approach to managing locking do you take?



-- 
Alan Chandler
http://www.chandlerfamily.org.uk


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

[sqlite] SQLite datatypes

[Alan Harris-Reid]

Hi there,

When creating a table in SQLite, I often get confused when confronted 
with all the possible datatypes which imply similar contents, so could 
anyone tell me the difference between the following data-types?

INT, INTEGER, SMALLINT, TINYINT
DEC, DECIMAL
LONGCHAR, LONGVARCHAR
DATETIME, SMALLDATETIME

Is there some documentation somewhere which lists the min./max. 
capacities of the various data-types?  For example, I guess smallint 
holds a larger maximum value than tinyint, but a smaller value than 
integer, but I have no idea of what these capacities are.

Any help would be appreciated.

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

Re: [sqlite] SQLite BUSY error - single-threaded app

[Alan Thomas]

The application runs as a singleton; there are no other applications
accessing the data base.

I do not run the command-line tool while testing.

I've cut down the example code:

  SqlStatus = sqlite3_open16 ( db_loc_string
 , 
 ) ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return DATASTORE_UNAVAILABLE ;
  }

  SqlStatus = sqlite3_prepare16_v2 ( SqlDB
   , L"BEGIN TRANSACTION"
   , 36
   , 
   , NULL
   ) ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_PREP_ERROR ;
  }

  SqlStatus = sqlite3_step ( SqlStmt ) ;
  if ( SQLITE_DONE != SqlStatus )
  {
. . .
return TXN_BEGIN_FAILED ;
  }

  SqlStatus = sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_FNLZ_ERROR ;
  }

  swprintf_s ( (PXID_CHAR)sql_statement
 , sizeof ( sql_statement ) / sizeof ( XID_CHAR )
 ,   L"DELETE"
  L" FROM RSPNS"
 L" WHERE . . .
 ) ;
  SqlStatus = sqlite3_prepare16_v2 ( SqlDB
   , sql_statement
   , ( lstrlen ( sql_statement ) + 1 ) *
sizeof ( XID_CHAR )
   , 
   , NULL
   ) ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_PREP_ERROR ;
  }

  SqlStatus = sqlite3_step ( SqlStmt ) ;
  if ( SQLITE_DONE != SqlStatus )
  {
. . .
return SQL_DLET_ERROR ;
  }

  SqlStatus = sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_FNLZ_ERROR ;
  }

  SqlStatus = sqlite3_prepare16_v2 ( SqlDB
   , L"COMMIT TRANSACTION"
   , 38
   , 
   , NULL
   ) ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_PREP_ERROR ;
  }

  SqlStatus = sqlite3_step ( SqlStmt ) ;
  if ( SQLITE_DONE != SqlStatus )
  {
. . .
return TXN_COMMIT_FAILED ;
  }

  SqlStatus = sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_FNLZ_ERROR ;
  }

  SqlStatus = sqlite3_prepare16_v2 ( SqlDB
   , L"BEGIN TRANSACTION"
   , 36
   , 
   , NULL
   ) ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_PREP_ERROR ;
  }

  /*-*/

  SqlStatus = sqlite3_step ( SqlStmt ) ;
  if ( SQLITE_DONE != SqlStatus )
  {
. . .
return TXN_BEGIN_FAILED ;
  }

  SqlStatus = sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;
  if ( SQLITE_OK != SqlStatus )
  {
. . .
return SQL_FNLZ_ERROR ;
  }

  . . .

  swprintf_s ( (PXID_CHAR)sql_statement
 , sizeof ( sql_statement ) / sizeof ( XID_CHAR )
 ,   L"INSERT"
  L" INTO RSPNS"
 L" ( SERVER_NAME"
 . . .
 L" , RESPONSE_TEXT"
  L" )"
   L" VALUES ( '%s'"
 . , ,
  L" , '%s'"
  L" )"
 , ServerName
 . . .
 , [m]
 ) ;

 SqlStatus = sqlite3_prepare16_v2 ( SqlDB
  , (const char *)sql_statement
  , ( lstrlen ( sql_statement ) + 1 ) *
sizeof ( XID_CHAR )
  , 
  , NULL
  ) ;
 if ( SQLITE_OK != SqlStatus )
 {
   SQLiteError ( sql_statement
   , L"sqlite3_prepare16_v2 ( )"
   ) ;
   sqlite3_close ( SqlDB ) ; SqlDB = NULL ;
   return SQL_PREP_ERROR ;
 }

 SqlStatus = sqlite3_step ( SqlStmt ) ;
 if ( SQLITE_DONE != SqlStatus )
 {
   . . . 
   return SQL_ISRT_ERROR ;
 }

 SqlStatus = sqlite3_finalize ( SqlStmt ) ;
 if ( SQLITE_OK != SqlStatus )
 {
   . . . 
   return XID_DBPA_SQL_FNLZ_ERROR ;
 }

  . . .

 SqlStatus = sqlite3_prepare16_v2 ( SqlDB
  , L"COMMIT TRANSACTION"
  , 38
  , 
  , NULL
  ) ;
 if ( SQLITE_OK != SqlStatus )
 {
   . . . 
   return XID_DBPA_SQL_PREP_ERROR ;
 }

 SqlStatus = sqlite3_step ( SqlStmt ) ;
 if ( SQLITE_DONE != SqlStatus )
 {
   . . . 
   return XID_DBPA_TXN_COMMIT_FAILED ;
 }

 SqlStatus = sqlite3_finalize ( SqlStmt 

[sqlite] SQLite BUSY error - single-threaded app

[Alan Thomas]

  {

   SQLiteError ( sql_statement

   , L"sqlite3_finalize ( )"

   ) ;

   sqlite3_close ( SqlDB ) ; SqlDB = NULL ;

   return XID_DBPA_SQL_FNLZ_ERROR ;

 }

 

  . . .

 

  SqlStatus = sqlite3_prepare16_v2 ( SqlDB

   , L"COMMIT TRANSACTION"

   , 38

   , 

   , NULL

   ) ;

  if ( SQLITE_OK != SqlStatus )

  {

SQLiteError ( L"XID_DBPA_TACL6530::SaveResponse ( )"

, L"COMMIT TRANSACTION 2"

, L"sqlite3_prepare16_V2 ( )"

) ;

sqlite3_close ( SqlDB ) ; SqlDB = NULL ;

return XID_DBPA_SQL_PREP_ERROR ;

  }

 

 

  SqlStatus = sqlite3_step ( SqlStmt ) ;

  if ( SQLITE_DONE != SqlStatus )

  {

SQLiteError ( L"XID_DBPA_TACL6530::SaveResponse ( )"

, L"COMMIT TRANSACTION 2"

, L"sqlite3_step ( )"

) ;

sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;

sqlite3_close( SqlDB   ) ; SqlDB   = NULL ;

return XID_DBPA_TXN_COMMIT_FAILED ;

  }

 

 

  SqlStatus = sqlite3_finalize ( SqlStmt ) ; SqlStmt = NULL ;

  if ( SQLITE_OK != SqlStatus )

  {

SQLiteError ( L"XID_DBPA_TACL6530::SaveResponse ( )"

, L"COMMIT TRANSACTION 2"

, L"sqlite3_finalize ( )"

) ;

sqlite3_close ( SqlDB ) ; SqlDB = NULL ;

return XID_DBPA_SQL_FNLZ_ERROR ;

  }

 

  sqlite3_close ( SqlDB ) ; SqlDB = NULL ;

The aforementioned message box and log tell me that the failure is detected
in the first COMMIT TRANSACTION on sqlite3_step ( ).

All of the archive content on SQLITE_BUSY appears to assume multi-threaded
database access; I have only a primary thread.

-   - Alan T.

 

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

[sqlite] Indexing question...

[Alan Harris-Reid]

I have a table with a primary key field called artist_id, which is also 
marked as autoincrement.  If I want an index on this field (eg. to 
improve performance when joining with other tables), do I have to index 
it explicitly, or does the fact that it is already an autoincrement pk 
field already take care of the index?

Any help would be appreciated.

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

Re: [sqlite] Ability to convert Access to SQLite

[Alan March]

I would recommend Talend (http://www.talend.com/). It allows for
export/import to/from almost any structured data source (including sqlite
and Access) and has very good scripting capabilities (a visual designer
which generates java or perl code). It doesn't have a scheduler but this may
be managed thru some other tool such as http://jobscheduler.sourceforge.net/
or, as you said, the windows task scheduler.




> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of scabral
> Sent: miércoles, 22 de julio de 2009 02:30 p.m.
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Ability to convert Access to SQLite
> 
> 
> The OS is Windows XP.  I know some VB script, some pearl and i'v used
> Visual
> VB and C# as well.
> 
> I was thinking that the script would called from a windows scheduled
> task
> and the script would then import the text file into the table in
> SQLite...Sounds easy, but i'm not sure what's available to create
> script.
> 
> thanks
> Scott
> 
> Simon Slavin-2 wrote:
> >
> >
> > On 22 Jul 2009, at 5:34pm, scabral wrote:
> >
> >> 1.  Need to automatically import text file into SQLite database on
> >> local
> >> machine (machine always on).  I was thinking of using scheduled task
> >> to run
> >> a script to do this, but not sure if that is possible.
> >
> > What programming languages or scripting languages do you know ?
> Which
> > operating system is that machine using ?
> >
> >> 2.  Neet to somehow create a front-end to query data from SQLite
> >> database
> >> (not sure if SQLite has front end application).
> >
> > It can talk to many many programming languages.  This will be
> > relatively easy, it's getting the data out of Access, and from the
> > text files that's hard.
> >
> > Do your users have access to web browsers ?  Can you write in PHP ?
> > Do you have a web server set up ?
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> 
> --
> View this message in context: http://www.nabble.com/Ability-to-convert-
> Access-to-SQLite-tp24609886p24610956.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.392 / Virus Database: 270.13.23/2254 - Release Date:
> 07/22/09 05:59:00

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

Re: [sqlite] Double entry bookkeeping

[Alan Cohen]

 able to give you some ideas that may (or not) help you.
The intent is not to frustrate you, but to give you information to tailor
your system to meet your needs.

BTW, my accounting systems experience has so far included McCormack & Dodge,
MSA, Software International (aka CA Universe), Lawson, SmartStream, Oracle,
PeopleSoft, Coda, Clarus, NetSuite, QuickBooks, Platinum, Solomon, GNUCash,
some homegrown systems, and a sampling of others on various OS's and
databases.  And, I have worked (at various times) with systems for the USA,
Canada, Mexico, UK, France, Netherlands, and some exposure to Australian
rules (which include recording depreciation to 5 decimal places).

Alan B. Cohen, CPA
AIM/YM/GMT/MSMess/Skype:   alanbcohen
Email:  alanbco...@gmail.com  ICQ:   49972123
Phone:+1 443/451-5369Efax: +1 413/653-6474

Hi all,

Below the basic SQL schema I have so far for double entry bookkeeping.

Does this look like a viable schema? I think I'm interpreting the
general structure of double entry bookkeeping and earlier discussion
here correctly. I welcome any comments specifically on the schema.

Basically each Transaction can have multiple Entries and each Entry
refers to one Account. An Entry may have an optional Narrative, stored
in the "Entries Narrative" table. Each Entry may have none, one or
more References, such as invoice numbers, order numbers, cheque
numbers, stored in the "Entries References" table.

create table Accounts
(
 ID integer
   primary key
   , Code text
   collate nocase
   unique
   not null
   references "Entries" ("Account Code")
   , Name text -- brief name
   collate nocase
)
;
create table "Transactions"
(
 ID integer
   primary key
   references "Entries" ("Transaction ID")
   , Date date -- julianday of the
date that the transaction occurred
)
;
create table "Entries"
(
 ID integer
   primary key
   references "Entries Narrative" (ID)
   , "Transaction ID" integer
   references Transactions (ID)
   on delete cascade
   on update cascade
   , "Account Code" text
   collate nocase
   references Accounts (Code)
   on delete restrict
   on update cascade
   , Amount integer-- amount in cents,
positive or negative
)
;
create table "Entries References"   -- Optional reference(s) for
each
Entry
(
 ID integer
   primary key
   , "Entry ID" integer
   references Entries (ID)
   on delete cascade
   on update cascade
   , "Reference" text  -- internal or
external reference such as
invoice or cheque number
   collate nocase
   not null
)
;
create table "Entries Narrative"-- Optional description for
each Entry
(
 ID integer
   primary key
   references "Entries" (ID)
   on delete cascade
   on update cascade
   , Narrative text
   not null
)
;

-- To prevent deletion of Transactions and Entries:

create trigger "Entries delete"
before delete
on "Entries"
begin
   select raise(rollback, 'You cannot delete Entries. You must instead
reverse out the Entries.');
end
;

-- And here is a view showing all the Entries grouped by Transaction:

create view "Entries Report"
as
select
 Entries.ID as ID
   , Transactions.ID as "Transaction ID"
   , date(Transactions.Date, '0.1 seconds', 'localtime') as Date
   , case when Amount < 0 then round(-Amount/100.0,2) end as Debit
   , case when Amount >= 0 then round(Amount/100.0,2) end as Credit
   , Accounts.Code
   , Accounts.Name
   , Narrative
   , group_concat("Reference", ', ') as "References"
from Entries
   left join "Entries Narrative" on Entries.ID = "Entries Narrative".ID
   left join "Entries References" on Entries.ID = "Entries
References"."Entry ID"
   left join Transactions on Entries."Transaction ID" = Transactions.ID
   left join Accounts on Entries."Account Code" = Accounts.Code
group by "Transaction ID", ID
;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best GUI?

[Alan Cohen]

This is a bit of a religious question, but which are the mest/most
popular *nix GUIs out there? I'm using a firefox plugin but not
entirely happy with it. What do you use and why?

A lot depends on what you are trying to accomplish.  For example, I move
between several machines, mixed between several Linux distros and two
Windows versions with an USB drive.  I have used sqliteadmin, sqlitespy, and
sqlite database browser (all Windows executables, all work under wine).  I
have also tried WinSQL (which is an ODBC-driven program I use when my
clients give me SQL access to the applications I'm installing for them.
None of these really provide the kind of data entry screens I'd like to
have; I have not had time to try OOo Base yet.

I'd be interested in other responses you get, as well.

Alan B. Cohen, CPA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] looking for data entry screen suggestions

[Alan Cohen]

I'm a relatively new SQLite user and I'm not much of a programmer, although
I have used several database GUI Tools over the years, including code
generators on PickOS and Windows.
I've been able to extract my data from Palm Desktop (twelve years of
accumulation), build tables and import my data into them in SQLite using the
SQLite Manager add-in from Firefox 3.05 (WinXP sp3).
What I'm looking to do is to build data lookup/entry screens to let me
access the local copy of the SQLite database on my computers (keeping the
database on an external USB drive).  I don't think I can rely on the SQLite
ODBC drivers as I move back and forth between several WinXP, Vista, and
Linux machines; some without admin rights.  It would be great if the
lookup/entry software was portable for the same reasons.  I have only been
able to use the SQLite Manager for list-based browsing; I'd prefer to be
able to define a single-record form for each table.

I admit to being very cost-sensitive at this time due to the precariousness
of my job situation and the general economy, so a no cost or FOSS solution
would be best.  I'd appreciate your suggestions.

Alan B. Cohen, CPA
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bindings problem

[Alan Hyde]

Thank you Roger - appreciated.


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

[sqlite] Bindings problem

[Alan Hyde]

Hi

I'm new to Sqlite and Python and have hit a snag.

I have a function that receives a single parameter "record" that  
contains the contents for the column "testdata"

The program fails with the message:

Incorrect number of bindings supplied. The current statement uses 0,  
and there are 10 supplied

There are 10 chars in the string variable that gets passed...it looks  
like the code sees each char in the string as a separate binding?

The source is as follows:

def updateDb(record):
c.execute('INSERT INTO ardrecords (testdata) VALUES ("?")', (record))



Thanks for the help.

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

Re: [sqlite] The IN keyword

[Alan Barkway]

On Wed, 28 Mar 2007 08:38:55 +0100, Jonas Sandman  
<[EMAIL PROTECTED]> wrote:


[...]


SELECT * FROM Files WHERE extension IN (:extension)


Just a guess here but might it be the case that you need to do this?

SELECT * FROM Files WHERE extension IN (:ext1, :ext2, :ext3)

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] SQLite and Linux problem

[Alan Mead]

Ashish Singh wrote:
> 1:After the installation how do I where can i go and acess SQLite 
> program on RedHat Linux

After installation (of the binary packages, at least), the command line
tools will be available.

You also (and most importantly) have the sqlite libraries for use in
your own code... including Perl which I've used very successfully... to
use Perl, I think you just become root and type 'cpan' then 'install
DBD::sqlite'  You can see a tiny bit of example Perl code after
installation of the sqlite DBD module by typing 'perldoc DBD::sqlite'
Also see the 'Performance' section of that page.

> Can anybody guide me how do I do all these things in sequence or  if 
> you can direct me towards
> some with where I can get appropriate help.

Are you asking for help to write a program that inserts several thousand
records and then performs some queries?

-- 
Alan D. Mead, Ph.D. : [EMAIL PROTECTED] : 815-588-3846

A statistician, who refused to fly after reading of the alarmingly high
probability that there will be a bomb on any given plane, realized that
the probability of there being two bombs on any given flight is very
low.  Now, whenever he flies, he carries a bomb with him.

RE: [sqlite] Pre-compiled SQL using the commandline interface

[Alan McGovern]

Hi,

Thanks for the response. I was pretty sure it wasn't possible to pregenerate 
when using the commandline interface, but i couldn't find anything to confirm 
either way.

I tried using two wrappers before (can't remember which ones now) but one of 
them only supported SQLite 2.x, and the second one refused to work for me for 
some reason. I'll give this one a shot and see how it goes. I assume its fully 
compatible with the latest SQLite even though it was last updated 6 months ago. 
If there is anything the wrapper doesn't handle correctly that i need to know 
about, let me know.

Thanks again,
Alan.

-Original Message-
From: Robert Simpson [mailto:[EMAIL PROTECTED]
Sent: 24 August 2005 06:29
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Pre-compiled SQL using the commandline interface


If you're going to all the trouble to write a program in C#, why not just
use the ADO.NET data provider for SQLite and insert into the databsae
yourself?

Seems rather silly to generate an insert statement and not actually execute
it in your code.  The command-line interface to sqlite isn't designed for
prepared statements and parameterized queries.

If you're using VS2003/.NET 1.1, use the Finisar ADO.NET library at
http://sourceforge.net/projects/adodotnetsqlite

If you're using VS2005/.NET 2.0, use my ADO.NET provider at
http://sourceforge.net/projects/sqlite-dotnet2

Robert


> -Original Message-
> From: Alan McGovern [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, August 23, 2005 5:51 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Pre-compiled SQL using the commandline interface
> 
> Hi,
> 
> I can't seem to find information about creating precompiled 
> statements when using the commandline program to access the 
> sqlite database. What i'm doing at the moment is using a C# 
> program to generate text files of insert statements up to 
> 100megs in size and then using .read to import them. Each 
> insert statement is identical except for the values that are 
> being imported, so in this scenario if i could use 
> precompiled SQL, it would result in quite a benefit, but i 
> can't seem to be able to do this.
> 
> Thanks,
> Alan.
> 



__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

[sqlite] Pre-compiled SQL using the commandline interface

[Alan McGovern]

Hi,

I can't seem to find information about creating precompiled statements when 
using the commandline program to access the sqlite database. What i'm doing at 
the moment is using a C# program to generate text files of insert statements up 
to 100megs in size and then using .read to import them. Each insert statement 
is identical except for the values that are being imported, so in this scenario 
if i could use precompiled SQL, it would result in quite a benefit, but i can't 
seem to be able to do this.

Thanks,
Alan.