Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[Simon Slavin]

On 6 Apr 2012, at 1:50am, BareFeetWare  wrote:

> On 06/04/2012, at 12:12 AM, Richard Hipp wrote:
> 
>> The way SQLite keeps track of foreign key constraints is by use of a 
>> counter.  When foreign key constraints are violated, the counter increments 
>> and when the constraints are resolved, the counter decrements, and an error 
>> is issued at transaction commit if the counter is not zero.  But if the 
>> counter is not zero, we don't have any way of knowing which of the many 
>> constraints caused the problem.

Well, Richard has said that names of constraints which aren't to do with 
foreign keys will be returned in a future version which is already a great 
improvement.

> Please change this. Use a hash table or array or something instead of a 
> counter so SQLite knows what constraint failed.

Actually when I needed to do something similar I ended up building a delimited 
string rather than an array.  String ops seem to be faster than array ops, and 
you have the added advantage that if you do encounter failure and need to 
return which constraints remain, you already have a string suitable for putting 
in the return message.  But I have no idea how SQLite works internally and 
whether this would be appropriate.

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

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[Simon Slavin]

On 6 Apr 2012, at 3:27am, BareFeetWare  wrote:

> eliminating a lot of superfluous, redundant and inaccurate external checking

I see what you nearly did there.

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

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[BareFeetWare]

On 06/04/2012, at 11:02 AM, Richard Hipp wrote:

> On Thu, Apr 5, 2012 at 8:50 PM, BareFeetWare wrote:
> 
>> On 06/04/2012, at 12:12 AM, Richard Hipp wrote:
>> 
>>> The way SQLite keeps track of foreign key constraints is by use of a 
>>> counter...

>>> we don't have any way of knowing which of the many constraints caused the 
>>> problem.
>> 
>> Please change this. Use a hash table or array or something instead of a 
>> counter so SQLite knows what constraint failed.
> 
> There are tradeoffs here.  "Better constraint error tracking" is just another 
> way of say "runs slower and uses more memory".

Thanks for continuing the discussion. I think this is a very important issue, 
as I think do others who have asked over the years.

OK, so it's not an issue of programming difficulty - good to know - but is a 
performance issue. I can think of a few remedies for this:

1. Us the counter first. If there is a constraint violation, step through again 
with a hash table or array to track the specific violation. That way it will 
only be "slower" if there was an error, which will have stopped the operation 
anyway.

or:

2. Have a pragma that allows us to enable descriptive errors. We can choose to 
enable it when needed, or else we can do our own "if constraint error then 
enable pragma for descriptive errors and try again, show output".

I would also be interested in seeing what % speed difference it actually makes.

Benefits include:

1. SQLite's core logic becomes much more usable, eliminating a lot of 
superfluous, redundant and inaccurate external checking. I'm a big believer in 
the "keep the logic near the model" philosophy.

2. It will save hours of frustration trying to find the source of otherwise 
non-descriptive errors.

3. You will save yourself from another two years of people asking for this 
feature ;-)

Thanks for your consideration,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

[sqlite] sqlite3_transaction trans(db); can't work in c, I don't know why?

[YAN HONG YE]

sqlite_transaction trans(db); 
sqlite3_prepare(db,"insert into image values('.rar',?);",-1,&stat,0); 
sqlite3_bind_blob(stat,1,ffile,filesize,NULL); 
sqlite3_step(stat); 
free(ffile); 
sqlite3_finalize(stat); 
trans.commit();
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[Richard Hipp]

On Thu, Apr 5, 2012 at 8:50 PM, BareFeetWare wrote:

> On 06/04/2012, at 12:12 AM, Richard Hipp wrote:
>
> > The way SQLite keeps track of foreign key constraints is by use of a
> counter.  When foreign key constraints are violated, the counter increments
> and when the constraints are resolved, the counter decrements, and an error
> is issued at transaction commit if the counter is not zero.  But if the
> counter is not zero, we don't have any way of knowing which of the many
> constraints caused the problem.
>
> Please change this. Use a hash table or array or something instead of a
> counter so SQLite knows what constraint failed. I have no doubt that your
> programming skill far exceeds mine, but this is a common requirement in
> software design, even for a relative pleb like me.
>

There are tradeoffs here.  "Better constraint error tracking" is just
another way of say "runs slower and uses more memory".


>
> I have spent hours a day lately on a particular database design project
> tracking down why constraints and foreign keys failed during batch imports.
> More descriptive errors from SQLite would have saved me many of those hours.
>
> Thanks for your consideration,
> Tom
>
> Tom Brodhurst-Hill
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Twitter: http://twitter.com/barefeetware/
> Facebook: http://www.facebook.com/BareFeetWare
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[BareFeetWare]

On 06/04/2012, at 12:12 AM, Richard Hipp wrote:

> The way SQLite keeps track of foreign key constraints is by use of a counter. 
>  When foreign key constraints are violated, the counter increments and when 
> the constraints are resolved, the counter decrements, and an error is issued 
> at transaction commit if the counter is not zero.  But if the counter is not 
> zero, we don't have any way of knowing which of the many constraints caused 
> the problem.

Please change this. Use a hash table or array or something instead of a counter 
so SQLite knows what constraint failed. I have no doubt that your programming 
skill far exceeds mine, but this is a common requirement in software design, 
even for a relative pleb like me.

I have spent hours a day lately on a particular database design project 
tracking down why constraints and foreign keys failed during batch imports. 
More descriptive errors from SQLite would have saved me many of those hours.

Thanks for your consideration,
Tom

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Twitter: http://twitter.com/barefeetware/
Facebook: http://www.facebook.com/BareFeetWare

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

Re: [sqlite] SQlite3 Locking Question

[Jay A. Kreibich]

On Thu, Apr 05, 2012 at 07:29:48PM +0200, Andreas Wiencke scratched on the wall:
> >> In my code I want to read from one table and write the results to
> >> another table. But if I don't fetch all the results and close the
> >> reading connection, I cannot write to another table in the same
> >> database.
> >
> > ??Do it all from the same connection. ??The locks are "owned" by a
> > ??database connection, so opening a second connection won't work, even
> > ??if they're within the same process.
> >
> > ??You should, however, be able to run both the query and the inserts
> > ??from the same connection without issues, even if they're intermixed.
> 
> Thanks for all the answers! I know how to solve this problem now. But
> there is one thing that is still unclear to me:
> why did this work in SQlite2? Was Version 2 unsafe when executing such
> a pattern?

  SQLite2 was a long time ago-- longer than I've been involved with
  SQLite.

  That said, from what I remember hearing, SQLite2 only supported the
  "_exec()" style interface.  If I followed the thread correctly, at
  least part of the issue in this situation was that the first statement
  was not finalized correctly, releasing the locks.  That situation 
  couldn't have existed in SQLite2, as I understand it did not support
  the prepare/step/finalize style APIs.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO with SELECT

[Pete]

Hi SImon,
Sorry if my replies seem a little disjointed - I get a daily digest of the
list so I don't see any responses to my posts until the next day.

Anyway, turns out this solution doesnt work - the INSERT fails because
there are two columns in t3 but only 1 column named in the SELECT.  The
solution is per Michael's later reply which is :

INSERT into t3 (Col1) SELECT Col1 FROM t2;

Thanks to everyone for the solution.

Pete

On Thu, Apr 5, 2012 at 9:00 AM,  wrote:

> Message: 10
> Date: Thu, 5 Apr 2012 01:38:57 +0100
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] INSERT INTO with SELECT
> Message-ID: 
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Apr 2012, at 1:36am, Pete  wrote:
>
> > Here's my test:
> >
> > CREATE TABLE t2 (Col1 text,Col2 text);
> > insert into t2 (Col1) values('xxx');
> > select * from t2
> > xxx|
> >
> > CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> > insert into t3 SELECT * FROM t2;
> > select * from t3;
> > xxx|
> >
> > Why does t3.Col2 not have it's default value of 'abc'?
>
> Because you fed it a value for the second column: NULL.  If you want the
> second column to have a default value you might find that
>
> insert into t3 SELECT Col1 FROM t2;
>
> works.
>
> Simon.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite & JDBC & generated key

[gwenn]

Thanks for your replies.
I will add a tweak to ignore column access by name when running
"SELECT last_insert_rowid();"

On Thu, Apr 5, 2012 at 2:17 AM, Kees Nuyt  wrote:
> On Wed, 4 Apr 2012 21:08:24 +0200, gwenn  wrote:
>
>>  2) Do you know if there are other bindings that implement/support
>>     this kind of feature ?
>
> I almost forgot to mention:
>
>  SELECT last_insert_rowid();
>
> http://www.sqlite.org/lang_corefunc.html
>
> --
> Regards,
>
> Kees Nuyt
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compiling with VS 2010 (force foreign key constraints)

[Joe Mistachkin]

Matjaž Cof wrote:
> 
> I recompiled it, add reference to System.Data.SQlite and
> System.Data.SQLite.Linq to my project and run. I get: The type
> initializer for 'System.Data.SQLite.SQLiteFactory' threw an exception.
> I try to add SQLite.Interop.Static.2010  SQLITE_DEFAULT_FOREIGN_KEYS
> in preprocessor definitions, still same problem.
> 

Without knowing what the thrown exception actually was, it's a bit hard to
diagnose your issue.  However, to further the discussion, I'm going to just
assume it was either DllNotFoundException or BadImageFormatException.

When using the System.Data.SQLite assemblies, it is very important to
understand where the native SQLite code will be loaded from (i.e. normally
the "SQLite.Interop.dll" file).  Also, the processor architecture for the
native
SQLite code [assembly] must match that of the machine running the code
(unless the application executable has been flagged as "32-bit only", which
is quite rare).

The "SQLite.Interop.dll" file must be located in the application binary
directory
or along the PATH and the processor architecture must be appropriate for the
application process.

--
Joe Mistachkin

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

Re: [sqlite] Variation on INSERT with SELECT issue

[Pete]

Thank you Michael, that works.
Pete

On Thu, Apr 5, 2012 at 9:00 AM,  wrote:

> Message: 17
> Date: Thu, 5 Apr 2012 12:31:58 +
> From: "Black, Michael (IS)" 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] Variation on INSERT with SELECT issue
> Message-ID: <56DF5186-D679-4E16-9CF2-8CFAF0036CFD@mimectl>
> Content-Type: text/plain; charset="iso-8859-1"
>
> You need 2 inserts to do what you want.  Hopefully the order in the table
> doesn't matter to you.
>
>
>
> sqlite> CREATE TABLE t2 (Col1 text,Col2 text);
> sqlite> insert into t2 (Col1) values('xxx');
> sqlite> insert into t2 values('yyy','def');
> sqlite> select * from t2;
> xxx|
> yyy|def
> sqlite>
> sqlite>
> sqlite> CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> sqlite> insert into t3 SELECT * FROM t2 where Col2 is not null;
> sqlite> select * from t3;
> yyy|def
> sqlite> insert into t3 (Col1) SELECT Col1 FROM t2 where Col2 is null;
> sqlite> select * from t3;
> yyy|def
> xxx|abc
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Directorate
>
> Advanced GEOINT Solutions Operating Unit
>
> Northrop Grumman Information Systems
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite3 Locking Question

[Andreas Wiencke]

>> In my code I want to read from one table and write the results to
>> another table. But if I don't fetch all the results and close the
>> reading connection, I cannot write to another table in the same
>> database.
>
>  Do it all from the same connection.  The locks are "owned" by a
>  database connection, so opening a second connection won't work, even
>  if they're within the same process.
>
>  You should, however, be able to run both the query and the inserts
>  from the same connection without issues, even if they're intermixed.

Thanks for all the answers! I know how to solve this problem now. But
there is one thing that is still unclear to me:
why did this work in SQlite2? Was Version 2 unsafe when executing such
a pattern?

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

[sqlite] Compiling with VS 2010 (force foreign key constraints)

[Matjaž Cof]

I am trying something, and i am confused as far as i can be.
I only want that SQlite would enforce foreign key constraint by default. I
don't want to use PRAGMA foreign_keys every time i mada a connection. After
searching on net, i found that i can compile with
SQLITE_DEFAULT_FOREIGN_KEYS.
I downloaded full source from
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki.
I recompiled it, add reference to System.Data.SQlite and
System.Data.SQLite.Linq to my project and run. I get: The type initializer
for 'System.Data.SQLite.SQLiteFactory' threw an exception.
I try to add SQLite.Interop.Static.2010  SQLITE_DEFAULT_FOREIGN_KEYS in
preprocessor definitions, still same problem.

I can't beleive it that there is not precompiled library
with SQLITE_DEFAULT_FOREIGN_KEYS on the download page. I am sure that
everyone is using pragma for now.
Any help would be appreciated.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO with SELECT

[Igor Tandetnik]

On 4/5/2012 1:09 PM, Simon Slavin wrote:


On 5 Apr 2012, at 6:05pm, Simon Slavin  wrote:


I don't understand what you think is wrong with

insert into t3 SELECT Col1 FROM t2;


Oh, now I do.  Okay, SQLite insists on the number of columns matching when you 
use a sub-SELECT


So you could just do

insert into t3(Col1) SELECT Col1 FROM t2;
--
Igor Tandetnik

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

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 6:05pm, Simon Slavin  wrote:

> I don't understand what you think is wrong with
> 
> insert into t3 SELECT Col1 FROM t2;

Oh, now I do.  Okay, SQLite insists on the number of columns matching when you 
use a sub-SELECT even though it doesn't when you use the bracketed form where 
you define the columns.  Okay, ignore that and look at the rest of what I 
posted.

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

Re: [sqlite] INSERT INTO with SELECT

[Simon Slavin]

On 5 Apr 2012, at 5:33pm, Pete  wrote:

> Thank you Igor and Simon, I think I understand this now.  On reading the
> docs more closely, it looks like the scond test case (NOT NULL with a
> DEFAULT) could be solved by using INSERT OR REPLACE.  I'm wondering if
> there might be a way to solve the first test case by using a trigger?

I don't understand what you think is wrong with

insert into t3 SELECT Col1 FROM t2;

You obviously don't the second column from t2, so don't SELECT it.
If you have some kind of logic which states

NULL values are perfectly legal in t2.
But NULL values are not legal in t3, where they should always be replaced with 
'abc', but all non-NULL values must be preserved.

Then you could do all your INSERTs, then follow up with a

UPDATE t3 SET Col2='abc' WHERE Col2 IS NULL

(which will go faster if Col2 is indexed) or you can use SQLite's 
coalesce(X,Y,...) function as documented in



something like

insert into t3 SELECT Col1,coalesce(Col2,'abc') FROM t2;

Which you do depends on how many NULLs you have, how easy it is to make an 
appropraite index, and how many rows there are in the table overall.

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

Re: [sqlite] INSERT INTO with SELECT

[Igor Tandetnik]

On 4/5/2012 12:33 PM, Pete wrote:

Thank you Igor and Simon, I think I understand this now.  On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE.


I don't see how.


I'm wondering if
there might be a way to solve the first test case by using a trigger?


Possibly. I don't quite understand the nature of the problem, nor what 
would constitute a solution.


Would something like this help?

insert into t2 SELECT Col1, coalesce(Col2, 'abc') from t1;

--
Igor Tandetnik

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

Re: [sqlite] table names

[Igor Tandetnik]

On 4/5/2012 12:18 PM, inq1ltd wrote:

I have a program that is complete and in testing.

In testing we found that sqlite complains if a
table is named using a leading text numeral.

For example I can't name a table "12-0401".


Table name must be an identifier, or else surrounded by double quotes, as in

create table "12-0401" ("a column with spaces" text);
--
Igor Tandetnik

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

Re: [sqlite] table names

[Jay A. Kreibich]

On Thu, Apr 05, 2012 at 12:18:30PM -0400, inq1ltd scratched on the wall:
> 
> sqlite help,
> 
> I have a program that is complete and in testing.
> 
> In testing we found that sqlite complains if a 
> table is named using a leading text numeral.  
> 
> For example I can't name a table "12-0401".
> I haven't seen anything in the sqlite create table 
> info about not using a name such as this.
> 
> Is there any way around this? 

  Quote the table name in all SQL statements.  Table names are
  identifiers, so double-quotes are best:

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

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT INTO with SELECT

[Pete]

Thank you Igor and Simon, I think I understand this now.  On reading the
docs more closely, it looks like the scond test case (NOT NULL with a
DEFAULT) could be solved by using INSERT OR REPLACE.  I'm wondering if
there might be a way to solve the first test case by using a trigger?

Pete

On Thu, Apr 5, 2012 at 9:00 AM,  wrote:

> Message: 10
> Date: Thu, 5 Apr 2012 01:38:57 +0100
> From: Simon Slavin 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] INSERT INTO with SELECT
> Message-ID: 
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Apr 2012, at 1:36am, Pete  wrote:
>
> > Here's my test:
> >
> > CREATE TABLE t2 (Col1 text,Col2 text);
> > insert into t2 (Col1) values('xxx');
> > select * from t2
> > xxx|
> >
> > CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
> > insert into t3 SELECT * FROM t2;
> > select * from t3;
> > xxx|
> >
> > Why does t3.Col2 not have it's default value of 'abc'?
>
> Because you fed it a value for the second column: NULL.  If you want the
> second column to have a default value you might find that
>
> insert into t3 SELECT Col1 FROM t2;
>
> works.
>
> Simon.
>
> --
>
> Message: 11
> Date: Wed, 4 Apr 2012 17:48:05 -0700
> From: Pete 
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Variation on INSERT with SELECT issue
> Message-ID:
> >
> Content-Type: text/plain; charset=ISO-8859-1
>
> The test this time was:
>
> sqlite> create table t1 (Col1,Col2);
> sqlite> insert into t1 (Col1) values ('xxx');
> sqlite> select * from t1;
> xxx|
>
> sqlite> create table t2 (Col1, col2 not null default 'abc');
> sqlite> insert into t2 SELECT * from t1;
> SQL error: t2.col2 may not be NULL
>
> --
> Pete
>
>
> --
>
> Message: 12
> Date: Wed, 04 Apr 2012 20:52:58 -0400
> From: Igor Tandetnik 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Variation on INSERT with SELECT issue
> Message-ID: 
> Content-Type: text/plain; charset=UTF-8; format=flowed
>
> On 4/4/2012 8:48 PM, Pete wrote:
> > The test this time was:
> >
> > sqlite>  create table t1 (Col1,Col2);
> > sqlite>  insert into t1 (Col1) values ('xxx');
> > sqlite>  select * from t1;
> > xxx|
> >
> > sqlite>  create table t2 (Col1, col2 not null default 'abc');
> > sqlite>  insert into t2 SELECT * from t1;
> > SQL error: t2.col2 may not be NULL
>
> Default clause applies when you omit a column from the list in INSERT
> (like you did when inserting into t1). It doesn't apply when you attempt
> to insert NULL explicitly.
> --
> Igor Tandetnik
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] table names

[Rob Richardson]

In SQLite Expert, I created table "12-345" with no problem, but I noted that 
when the new table was displayed, its name was wrapped in square brackets: 
"[12-345]".  Perhaps you could wrap numeric names in brackets similarly.

Good luck!

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

[sqlite] table names

[inq1ltd]

sqlite help,

I have a program that is complete and in testing.

In testing we found that sqlite complains if a 
table is named using a leading text numeral.  

For example I can't name a table "12-0401".
I haven't seen anything in the sqlite create table 
info about not using a name such as this.

Is there any way around this? 

Had we known this a while ago it could have been 
managed but now it is a major problem.

Any suggestions that allows the use of a text numeral 
as the first character in a table name would be helpful.

jd


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

Re: [sqlite] SQlite3 Locking Question

[Jay A. Kreibich]

On Thu, Apr 05, 2012 at 03:15:37PM +0100, Simon Slavin scratched on the wall:
> On 5 Apr 2012, at 3:04pm, Andreas Wiencke  wrote:

> > In my code I want to read from one table and write the results to
> > another table. But if I don't fetch all the results and close the
> > reading connection, I cannot write to another table in the same database.
> 
> As a rule, even though SQLite allows you to do a SELECT in many steps
> bit by bit, you shouldn't mess with the contents of the database in
> the middle of a SELECT.  I'm not saying that it will (always,
> sometimes) fail, merely that a SELECT should be considered atomic.

  I would disagree.  It is a pretty common practice to step through a
  query and update other elements in the database.  I'm not just
  talking about SQLite either, this is a fairly common pattern in all
  database development.

  In the case of SQLite, it is all perfectly safe.  Updating the tables
  involved in the original query may result in some odd results, but 
  pretty much everything else is fair game.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite3 Locking Question

[Simon Slavin]

On 5 Apr 2012, at 3:15pm, Richard Hipp  wrote:

> Probably the $r query is not being finalized, and is thus holding the read
> lock, preventing the subsequent write from happening.  You shouldn't have
> to close the connection in order to finalize the query - but I don't know
> the PHP syntax for finalizing the query without closing the connection.

The appropriate finalize method exists.  The correct way to use the SQLite3 
routines for a SELECT is (quickly made up from the top of my head, untested) ...

try {
$result = db->query($theCommand);
} catch (Exception $e) {
die('Error while executing ' .$theCommand. ': '. $e->getMessage());
}

while ($thisRow = $result->fetchArray(SQLITE3_ASSOC)) {
... do something with $thisRow ...
}
$result->finalize();

And now you point it out, I see that the OP has indeed forgotten to finalize 
his result set in his PHP code, so the SELECT never finishes.

The PHP SQLite3 API is a very thin wrapper around the C++ API.  I found that 
knowing one of them well, I could easily find an exact equivalent function in 
the other.  This is not the same as the other two ways of accessing SQLite 
databases available to PHP users, which try to standardise across many database 
engines so they are futher away from what someone familiar with SQLite would 
expect to see.

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

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[Jay A. Kreibich]

On Thu, Apr 05, 2012 at 04:08:29PM +0200, Gert Corthout scratched on the wall:
> 
> hi all,
> I have the database set to fail in case of constraint violation.
> And it does return SQLITE_CONSTRAINT from sqlite3_step when I try
> to insert/update an invalid foreign key. But is there any way to
> find out exactly which constraint failed as I might have many
> foreign key fields in the record.

  No.

  Search the mailing list for multiple, extended discussions about why.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite3 Locking Question

[Jay A. Kreibich]

On Thu, Apr 05, 2012 at 04:04:23PM +0200, Andreas Wiencke scratched on the wall:
> 
> Hello,
> 
> I have a question regarding the locking in Sqlite3: Does Sqlite3
> lock the entire database when there is a lock on only one table?

  The whole database.  http://sqlite.org/lockingv3.html

> In my code I want to read from one table and write the results to
> another table. But if I don't fetch all the results and close the
> reading connection, I cannot write to another table in the same
> database.

  Do it all from the same connection.  The locks are "owned" by a
  database connection, so opening a second connection won't work, even
  if they're within the same process.

  You should, however, be able to run both the query and the inserts
  from the same connection without issues, even if they're intermixed.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQlite3 Locking Question

[Simon Slavin]

On 5 Apr 2012, at 3:04pm, Andreas Wiencke  wrote:

> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the 
> entire database when there is a lock on only one table?

Yes.  In fact it locks the entire database when there's only reason to lock one 
row.  Either nothing or everything is locked.

> In my code I want to read from one table and write the results to another 
> table. But if I don't fetch all the results and close the reading
> connection, I cannot write to another table in the same database.

As a rule, even though SQLite allows you to do a SELECT in many steps bit by 
bit, you shouldn't mess with the contents of the database in the middle of a 
SELECT.  I'm not saying that it will (always, sometimes) fail, merely that a 
SELECT should be considered atomic.

> Is this the intended behavior? And how could I work around that?

Various ways.  You can buffer your writes until you've finished your reads, 
either by accumulating the values in an array or by making a really long string 
of INSERT commands.  If you have an extremely long database you're searching 
then you can use a cursor technique to iterate through the database bit by bit, 
with each SELECT fetching just the next record until there are no more records. 
 Much slower but it lets you get through multi-gigabyte databases without using 
lots of memory.

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

Re: [sqlite] SQlite3 Locking Question

[Richard Hipp]

On Thu, Apr 5, 2012 at 10:04 AM, Andreas Wiencke wrote:

>
> Hello,
>
> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the
> entire database when there is a lock on only one table?
> In my code I want to read from one table and write the results to another
> table. But if I don't fetch all the results and close the reading
> connection, I cannot write to another table in the same database. Is this
> the intended behavior? And how could I work around that?
> I believe that this worked in In SQlite2. Here is a reduced example in PHP:
>
> $db="/tmp/testdb";
>//unlink($db); // always use a fresh database
>
>// open database s1
>$s1 = new SQLite3($db);
>
>// create a table
>$s1->query("CREATE TABLE t1(one varchar(10), two smallint);");
>
>// write one entry
>$s1->query("INSERT INTO t1 VALUES('dog',1);");
>$s1->query("INSERT INTO t1 VALUES('cat',2);");
>
>// read the only entry and output it
>$r = $s1->query("SELECT * FROM t1");
>$a = $r->fetchArray(SQLITE3_ASSOC);
>var_dump($a);
>
>// close connection to s1, makes the code work
>//$s1->close();
>

Probably the $r query is not being finalized, and is thus holding the read
lock, preventing the subsequent write from happening.  You shouldn't have
to close the connection in order to finalize the query - but I don't know
the PHP syntax for finalizing the query without closing the connection.

A work-around is to use write-ahead log
(WAL)mode, where it is possible to
read on one connection while simultaneously
writing on a different connection.  You'll still want to figure out why
your query is not finalizing, though, since while the query will not longer
block writes, it will block checkpoints from running to completion, which
can result in excessively large WAL files.



>
>// write to table s2 -> does not work because database is locked
>$s2 = new SQLite3($db);
>$s2->query("CREATE TABLE t2(one varchar(10), two smallint);");
>$s2->query("INSERT INTO t2 VALUES('fish',3);");
> ?>
>
> This code produces a warning "database is locked" and does not insert the
> value "fish".
>
> Any help is appreciated.
>
> Thank you,
>
> Andreas
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQLITE_CONSTRAINT, which one failed?

[Richard Hipp]

On Thu, Apr 5, 2012 at 10:08 AM, Gert Corthout wrote:

>
> hi all,
> I have the database set to fail in case of constraint violation. And it
> does return SQLITE_CONSTRAINT from sqlite3_step when I try to insert/update
> an invalid foreign key. But is there any way to find out exactly which
> constraint failed as I might have many foreign key fields in the record.
>

The latest code on trunk will tell you which CHECK constraint failed.  But
SQLite doesn't know which foreign key constraint failed.  The way SQLite
keeps track of foreign key constraints is by use of a counter.  When
foreign key constraints are violated, the counter increments and when the
constraints are resolved, the counter decrements, and an error is issued at
transaction commit if the counter is not zero.  But if the counter is not
zero, we don't have any way of knowing which of the many constraints caused
the problem.




> thanks in advance,Gert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] SQlite3 Locking Question

[Pavel Ivanov]

> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the 
> entire database when there is a lock on only one table?

Yes.

> But if I don't fetch all the results and close the reading
> connection, I cannot write to another table in the same database. Is this the 
> intended behavior?

Yes.

> And how could I work around that?

Try to use PRAGMA journal_mode = WAL (if you use SQLite 3.7.0 or above).


Pavel


On Thu, Apr 5, 2012 at 10:04 AM, Andreas Wiencke  wrote:
>
> Hello,
>
> I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the 
> entire database when there is a lock on only one table?
> In my code I want to read from one table and write the results to another 
> table. But if I don't fetch all the results and close the reading
> connection, I cannot write to another table in the same database. Is this the 
> intended behavior? And how could I work around that?
> I believe that this worked in In SQlite2. Here is a reduced example in PHP:
>
>         $db="/tmp/testdb";
>        //unlink($db); // always use a fresh database
>
>        // open database s1
>        $s1 = new SQLite3($db);
>
>        // create a table
>        $s1->query("CREATE TABLE t1(one varchar(10), two smallint);");
>
>        // write one entry
>        $s1->query("INSERT INTO t1 VALUES('dog',1);");
>        $s1->query("INSERT INTO t1 VALUES('cat',2);");
>
>        // read the only entry and output it
>        $r = $s1->query("SELECT * FROM t1");
>        $a = $r->fetchArray(SQLITE3_ASSOC);
>        var_dump($a);
>
>        // close connection to s1, makes the code work
>        //$s1->close();
>
>        // write to table s2 -> does not work because database is locked
>        $s2 = new SQLite3($db);
>        $s2->query("CREATE TABLE t2(one varchar(10), two smallint);");
>        $s2->query("INSERT INTO t2 VALUES('fish',3);");
> ?>
>
> This code produces a warning "database is locked" and does not insert the 
> value "fish".
>
> Any help is appreciated.
>
> Thank you,
>
> Andreas
>
>
> ___
> 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] SQLITE_CONSTRAINT, which one failed?

[Gert Corthout]

hi all,
I have the database set to fail in case of constraint violation. And it does 
return SQLITE_CONSTRAINT from sqlite3_step when I try to insert/update an 
invalid foreign key. But is there any way to find out exactly which constraint 
failed as I might have many foreign key fields in the record.
thanks in advance,Gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQlite3 Locking Question

[Andreas Wiencke]

Hello,

I have a question regarding the locking in Sqlite3: Does Sqlite3 lock the 
entire database when there is a lock on only one table?
In my code I want to read from one table and write the results to another 
table. But if I don't fetch all the results and close the reading
connection, I cannot write to another table in the same database. Is this the 
intended behavior? And how could I work around that?
I believe that this worked in In SQlite2. Here is a reduced example in PHP:

query("CREATE TABLE t1(one varchar(10), two smallint);");

// write one entry
$s1->query("INSERT INTO t1 VALUES('dog',1);");
$s1->query("INSERT INTO t1 VALUES('cat',2);");

// read the only entry and output it
$r = $s1->query("SELECT * FROM t1");
$a = $r->fetchArray(SQLITE3_ASSOC);
var_dump($a);

// close connection to s1, makes the code work
//$s1->close();

// write to table s2 -> does not work because database is locked
$s2 = new SQLite3($db);
$s2->query("CREATE TABLE t2(one varchar(10), two smallint);");
$s2->query("INSERT INTO t2 VALUES('fish',3);");
?>

This code produces a warning "database is locked" and does not insert the value 
"fish".

Any help is appreciated.

Thank you,

Andreas
  

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

Re: [sqlite] SQLite db file viewer

[Igor Tandetnik]

Meng Ng  wrote:
> I have a SQLite db file. Can someone tell which software is needed to view
> the file? Thanks.

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

-- 
Igor Tandetnik

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

Re: [sqlite] SQLite db file viewer

[Simon Slavin]

On 5 Apr 2012, at 2:44pm, Meng Ng  wrote:

> I have a SQLite db file. Can someone tell which software is needed to view
> the file? Thanks.

The SQLite team provides a command line tool which you can use to view the file 
in various formats.  Documentation:



Download:



There are many other programs which can view the contents of a SQLite database 
available from other places, and you can write your own.

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

[sqlite] SQLite db file viewer

[Meng Ng]

Hi,

I have a SQLite db file. Can someone tell which software is needed to view
the file? Thanks.

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

[sqlite] Unicode problem when setting PRAGMA journal_mode

[Nick Shaw]

Hi all,

Our windows application uses sqlite, and we've had no problems with it in our 
existing builds, which use the multibyte character set.  We are now converting 
our codebase to Unicode.  SqLite is quite happily running with our Unicode 
builds, creating SqLite databases in UTF-16LE encoding.

We have come across one very odd thing, though.  If I run the query "PRAGMA 
journal_mode = NORMAL" (actually, setting it to ANY of the value values, 
including "OFF" does the same thing), everything runs fine until the code 
closes the database, at which point the database returns SQLITE_BUSY, with the 
error "unable to close due to unfinalised statements".  This happens even if 
the only query I run on the database is that pragma command.  I can run other 
pragmas fine (e.g. "PRAGMA synchronous = NORMAL") and all SQL queries are fine, 
and the database will close happily; it's just this journal_mode pragma that's 
causing this odd behaviour, and ONLY in the Unicode build of our code - the 
multibyte build doesn't show this problem.  This happens whether the database 
exists already [in UTF-16LE mode], or whether it creates it on first opening.

Any ideas what could be wrong?  We're using SqLite build v3.7.11.

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

Re: [sqlite] Variation on INSERT with SELECT issue

[Black, Michael (IS)]

You need 2 inserts to do what you want.  Hopefully the order in the table 
doesn't matter to you.



sqlite> CREATE TABLE t2 (Col1 text,Col2 text);
sqlite> insert into t2 (Col1) values('xxx');
sqlite> insert into t2 values('yyy','def');
sqlite> select * from t2;
xxx|
yyy|def
sqlite>
sqlite>
sqlite> CREATE TABLE t3 (Col1 text,Col2 text default 'abc');
sqlite> insert into t3 SELECT * FROM t2 where Col2 is not null;
sqlite> select * from t3;
yyy|def
sqlite> insert into t3 (Col1) SELECT Col1 FROM t2 where Col2 is null;
sqlite> select * from t3;
yyy|def
xxx|abc



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Pete [p...@mollysrevenge.com]
Sent: Wednesday, April 04, 2012 7:48 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Variation on INSERT with SELECT issue

The test this time was:

sqlite> create table t1 (Col1,Col2);
sqlite> insert into t1 (Col1) values ('xxx');
sqlite> select * from t1;
xxx|

sqlite> create table t2 (Col1, col2 not null default 'abc');
sqlite> insert into t2 SELECT * from t1;
SQL error: t2.col2 may not be NULL

--
Pete
___
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] about The open-source Fossil version control system

[Niall O'Reilly]

On 5 Apr 2012, at 04:42, YAN HONG YE wrote:

> When I open the source on the page, but I don't found 
>  #include "config.h"
>  #include "main.h"
> file, where it is?

You need to do your homework.

Persistently looking to people on the list to do it 
for you is not just inappropriate, but simply rude.  

I'ld prefer not to have to be so brutaly candid; 
however, I've noticed that other list members have 
tried to express this more politely, but you seem 
not to be minded to heed their message. 


Best regards,
Niall O'Reilly

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

Re: [sqlite] sqlite3_transaction can't work

[Simon Davies]

On 5 April 2012 09:44, YAN HONG YE  wrote:
> sqlite3_transaction trans(db); can't work in c, I don't know why?

Give us a clue: what are you expecting, and what actually happens...

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

[sqlite] sqlite3_transaction can't work

[YAN HONG YE]

sqlite3_transaction trans(db); can't work in c, I don't know why?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users