Re: [sqlite] SQLite with Entity Framework problem

[Joe Mistachkin]

Christiano Borchardt wrote:
> 
> My SQL server has a table with a primary key of type smallint which is an
> identity column. This is the AUTOINCREMENT equivalent on SQLite. However
> AUTOINCREMENT in SQlite only allows the Integer type.
> When the entity framework loads the entity for this table it crashes
> because the type returned by SQLite data provider is an Int64 while the
> expected by EF is Int16.
> 

I'm not sure why Entity Framework would use an Int16 for that.  What is the
schema of the database table in question?  Also, could you include the code
for the associated entity class?

> 
> The version of SQLite I'm using is 1.0.92.0 and the entity framework is
> 6.0.0.0.
> 

Is the version of Entity Framework exactly 6.0.0.0 or is it something like
6.0.0.2?

--
Joe Mistachkin

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

Re: [sqlite] transactions do not respect delete

[Jim Carroll]

> Date: Tue, 6 May 2014 14:57:30 +0200
> From: Mark Lawrence 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] transactions do not respect delete
> Message-ID: <20140506125730.ga23...@rekudos.net>
> Content-Type: text/plain; charset=us-ascii
>
> > It would appear the DELETE was successful, and the first INSERT was
> > successful. But when the second INSERT failed (as it was intended
to)..it
> > did not ROLLBACK the database.
>
> Even though the second INSERT fails, your script still calls COMMIT
> on an open transaction in which the DELETE and first INSERT have
> succeeded.

You are exactly correct (sound of palm slapping my own face).

I was working on a proof of concept using the sqlite command-line shell, and
completely forget the fact that in code, we'd get an exception when we
attempted the second INSERT, at which point it would be up to us to either
invoke COMMIT or ROLLBACK.

Sorry for the foolishness

Jim




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

[sqlite] SQLite with Entity Framework problem

[Christiano Borchardt]

Hi there,

We have a system which uses SQL server as a primary database. We are
thinking about replacing the entity framework provider so we can use SQLite
in-memory for integration tests.

I have done few tests and generally System.Data.SQlite works fine. The only
exception I have so far is:

My SQL server has a table with a primary key of type smallint which is an
identity column. This is the AUTOINCREMENT equivalent on SQLite. However
AUTOINCREMENT in SQlite only allows the Integer type.
When the entity framework loads the entity for this table it crashes
because the type returned by SQLite data provider is an Int64 while the
expected by EF is Int16.

Is there a way to tweak or by pass this behavior?

The version of SQLite I'm using is 1.0.92.0 and the entity framework is
6.0.0.0.

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

Re: [sqlite] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 5:26 PM, Gene Connor  wrote:

> Subject: Re: [sqlite] Bug in division?
> From: j...@kreibi.ch
> Date: Tue, 6 May 2014 17:02:02 -0500
> CC: neothreeei...@hotmail.com
> To: sqlite-users@sqlite.org
> 
> > The system does not return 0 any time you divide two integers, but it does 
> > return zero for 2 / 4.  After all, how many *whole* times does four go into 
> > two?
> 
>  
> 
> Right.  I discovered this 'problem' when I divided a real big integer by an 
> even bigger integer.  The actual result was 0.004572 or something like that.
> 
> MS Access did it right, but SQLite returned 0.  That prompted my bug? report
> 
> I also found out SQLite might change the column affinity to INTEGER if your 
> data can be converted "losslessly”.


You’re mixing terms.  “Affinity” is the “default type” of the column.  It is 
defined by the CREATE TABLE statement and is part of the table definition, 
meaning it cannot change on a row-by-row basis.  In SQLite, a column “affinity" 
is different from the “type" of a value in a given column, which (in SQLite, 
anyways) can change from row to row.  In fact, SQLite “types” and “affinities” 
do not even share the same set of classifications… Value types are limited to 
{NULL, INTEGER, REAL, TEXT, BLOB}, while affinities are {TEXT, NUMERIC, 
INTEGER, REAL, NONE}.

In your example below, you define the columns at “NUMERIC," which results in a 
NUMERIC affinity for the two columns (although not because the names match, see 
section 2.1 of http://www.sqlite.org/datatype3.html#affinity)

NUMERIC affinities have a preference to store things as an integer, followed by 
a floating point, followed by a string or other native type.  As you’ve shown, 
if SQLite can “up convert” a value, it will.  It isn’t just floating point 
numbers that get converted.  "INSERT INTO tbl1 VALUES ( ‘2’, ‘4’ )" will also 
result in two integers, even though you’ve passed in two strings.

For what it’s worth, all this is pretty clearly documented: 
http://www.sqlite.org/datatype3.html#affinity

It is a bit weird, but it comes from the fact that SQLite allows different 
types on a row by row basis.


> sqlite> CREATE TABLE TBL1 (N1 NUMERIC, N2 NUMERIC);
> 
> Same thing happens with type DECIMAL.
> 
> This means your best bet is to store /all/ numbers in FLOAT or DOUBLE 
> columns.   Or use CAST  (ugh).

Well, not really.  Large integer values cannot be accurately stored in 
floating-point values.  If you really want floating-point values, and you’re 
using a number domain that is acceptable to use floating-point values, then 
sure.  But if you want integers, they store larger precise numbers, and are 
usually much smaller on disk.

In short, the same as any other engineering decision: use the right tool for 
the job.

  -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] Bug in division?

[Jay Kreibich]

On May 6, 2014, at 4:29 PM, John Drescher  wrote:

>> Interesting.  It makes NO sense to return 0 when dividing two integers.
>> 
> 
> Never took a C/C++ class?



The system does not return 0 any time you divide two integers, but it does 
return zero for 2 / 4.  After all, how many *whole* times does four go into two?

 -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] Is there a single file version of System.Data.SQLite?

[Joe Mistachkin]

Drago, William @ MWG - NARDAEAST wrote:
> 
> Is there a way to use the "System.Data.SQLite.dll" mixed-mode assembly
outside
> of the GAC? Or is there a 32 bit only single file version of SQLite?
>

Sure, you should be able to load the mixed-mode assembly from an arbitrary
location
using the LoadFrom() method, as seen here:

http://msdn.microsoft.com/en-us/library/1009fa28%28v=vs.110%29.aspx

Alternatively, if the mixed-mode assembly is located in the same directory
as the
application binary itself, it may be possible to use the Load() method, as
seen here:

http://msdn.microsoft.com/en-us/library/ky3942xh%28v=vs.110%29.aspx

--
Joe Mistachkin

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

[sqlite] LSM Leaks memory

[sqlite]

I have this trivial program:

int main(int argc, char **argv)
{
lsm_db* db;
lsm_new(lsm_default_env(), );
lsm_open(db, "lsm");

lsm_cursor *csr;
lsm_csr_open(db, );
lsm_csr_seek(csr, "a", 1, LSM_SEEK_GE);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_seek(csr, "a", 1, LSM_SEEK_EQ);
lsm_csr_close(csr);
lsm_begin(db, 1);
lsm_insert(
db, "abc", 3,
"def", 3
);
lsm_commit(db, 0);
lsm_close(db);
}

And I run it under valgrind with --leak-check=full it reports this:

==1741== 1,008 (112 direct, 896 indirect) bytes in 1 blocks are definitely lost 
in loss record 4 of 4
==1741==at 0x4C28BED: malloc (vg_replace_malloc.c:263)
==1741==by 0x413D4B: lsmPosixOsMalloc (lsm_unix.c:472)
==1741==by 0x4046C5: lsmMallocZero (lsm_mem.c:50)
==1741==by 0x404730: lsmMallocZeroRc (lsm_mem.c:69)

In a more complex program, lsm seems to leak memory to no bounds, causing my 
application.

Are bug reports against LSM even helpful?

Charles

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

Re: [sqlite] FW: Bug in division?

[John Drescher]

> Interesting.  It makes NO sense to return 0 when dividing two integers.
>

Never took a C/C++ class?

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

Re: [sqlite] Foreign Key errors

[Stephan Beal]

On Tue, May 6, 2014 at 11:24 PM, Petite Abeille wrote:

> On May 6, 2014, at 11:17 PM, Richard Hipp  wrote:
>
> > It is theoretically possible to keep track of which constraints are
> failing
> > so that the particular constraint can be identified in the error message.
> > But that woudl require more memory and CPU cycles.
>
> That would be resources well spent.
>

And might even be justifiable given other recent speed improvements which
offset them ;).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key errors

[Petite Abeille]

On May 6, 2014, at 11:17 PM, Richard Hipp  wrote:

> It is theoretically possible to keep track of which constraints are failing
> so that the particular constraint can be identified in the error message.
> But that woudl require more memory and CPU cycles.

That would be resources well spent. 

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

Re: [sqlite] Foreign Key errors

[Richard Hipp]

On Tue, May 6, 2014 at 5:12 PM, Peter Haworth  wrote:

> It seems that foreign key errors on columns where the foreign key
> definition has a constraint name don't include the constraint name in the
> error message.  This is using sqlite version 3.8.3.1.
>
> Is this under the control of a compile switch or PRAGMA or am I stuck with
> the way it is?
>

SQLite does not keep track of which foreign key constraints fail.  It
simply keeps a counter which is incremented whenever a foreign key
constraint fails and decremented whenever the foreign key constraint is
resolved.  If that counter is positive at the conclusion of a statement,
that means one or more foreign key constraints failed, but it provides no
information about which constraint.

(Actually, there are two counters, one for immediate constraints and
another for deferred constraints.)

It is theoretically possible to keep track of which constraints are failing
so that the particular constraint can be identified in the error message.
But that woudl require more memory and CPU cycles.




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



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

[sqlite] Is there a single file version of System.Data.SQLite?

[Drago, William @ MWG - NARDAEAST]

All,

Is there a way to use the "System.Data.SQLite.dll" mixed-mode assembly outside 
of the GAC? Or is there a 32 bit only single file version of SQLite?

I'm running a 32 bit, non-managed code interpreted language that can't find  
the SQLite.Interop.dll files if I start my program from a network drive (it 
works fine locally).

I've never needed the 64 bit interop .dll, so I'm wondering if there's a 
version of SQLite that is a single file .dll.

Thanks,
--
Bill Drago
Senior Engineer

L3 Communications / Narda Microwave East
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l-3com.com

CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key errors

[Peter Haworth]

It seems that foreign key errors on columns where the foreign key
definition has a constraint name don't include the constraint name in the
error message.  This is using sqlite version 3.8.3.1.

Is this under the control of a compile switch or PRAGMA or am I stuck with
the way it is?

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

[sqlite] group_concat(distinct) with empty strings

[Hinrichsen, John]

Are the results below expected?

$ sqlite3
SQLite version 3.8.4.3 2014-04-03 16:53:12
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE z AS SELECT NULL AS a;
sqlite> SELECT (SELECT DISTINCT COALESCE(a,'') FROM z) IS NULL;
0
sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,'')) FROM z) IS
NULL;
1
sqlite> SELECT (SELECT GROUP_CONCAT(DISTINCT COALESCE(a,' ')) FROM z) IS
NULL;
0
sqlite>

This problem looks similar to:
http://sqlite.1065341.n5.nabble.com/group-concat-and-empty-strings-td62226.html

-- 

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee, you should not 
disseminate, distribute, alter or copy this e-mail. Please notify the 
sender immediately by e-mail if you have received this e-mail by mistake 
and delete this e-mail from your system. E-mail transmissions cannot be 
guaranteed to be secure or without error as information could be 
intercepted, corrupted, lost, destroyed, or arrive late or incomplete. The 
sender, therefore, does not accept liability for any errors or omissions in 
the contents of this message which arise during or as a result of e-mail 
transmission. If verification is required, please request a hard-copy 
version. This message is provided for information purposes and should not 
be construed as a solicitation or offer to buy or sell any securities or 
related financial instruments in any jurisdiction.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] NOP INSERT still writes to the DB/journal

[Christian Smith]

On Mon, May 05, 2014 at 05:00:08PM -0400, Richard Hipp wrote:
> On Mon, May 5, 2014 at 4:53 PM, Patrick Donnelly wrote:
> 
> > Hi,
> >
> > I have an INSERT that looks like
> >
> > INSERT INTO T
> > SELECT ...
> >
> > which I'm running numerous times a second that generally does nothing
> > because the SELECT returns no rows. Unfortunately, I've found that
> > SQLite still does numerous disk writes anyway in this situation.
> >
> 
> I'm unable to reproduce this behavior.  Here is my test script:
> 
> 
> No writes.
> 

I suspect the culprit is file access time updates. Using something
like relatime mount option under Linux would eliminate atime updates
for files that have not been modified.

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

Re: [sqlite] Very slow when query max(col) on empty set?

[Igor Tandetnik]

On 5/6/2014 10:19 AM, Woody Wu wrote:

I observed a strange behavior.  I was operating on a big table, there are 
200,000 records in it. The table has a primary key or unique index, (time, id1, 
id2), all of these indexed columns are integers.

The following query statement executed very slow, it took 15 secs on my ARM 
device,

1. select max(time) from mytable where time <  and id1 = k1 and id2 = n.

where  is a value that large enough that no a record has its time field 
equals to it. k1 is a not existed value that cannot be matched by any id1 
column in the table, while n is a normal value that can be matched in the table 
by a subset of records.

However, if I replace k1with another value that can be found in the table and 
keep everything unchanged, like below,

2. select max(time) from mytable where where time < 9 and id1 = k2 and 
id2 = n.

This query run very well, it only took less than 1 second on the same system.


In this query, only the "time" part of the index helps, but not the 
"id1, id2" part. SQLite scans the index backwards, from highest time 
down, and stops as soon as it finds a record that satisfies the two 
equality checks. In the first case, no such record exists, so every 
single record ends up being looked at. In the second case, apparently a 
matching record is found early enough.

--
Igor Tandetnik

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

Re: [sqlite] Very slow when query max(col) on empty set?

[Clemens Ladisch]

Woody Wu wrote:
> The following query statement executed very slow, it took 15 secs on my ARM 
> device,
>
> 1. select max(time) from mytable where time <  and id1 = k1 and id2 = 
> n.
>
> However, if I replace k1with another value that can be found in the table and 
> keep everything unchanged, like below,
>
> 2. select max(time) from mytable where where time < 9 and id1 = k2 
> and id2 = n.
>
> This query run very well, it only took less than 1 second on the same system.
>
> Could anyone explain this to me?

The database can help with the explanation.
What is the output of EXPLAIN QUERY PLAN for both queries?


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

[sqlite] Graphic User Interface to browse Sqlite, written in pure Python

[big stone]

Hello,

I failed to find a basic but correct Graphic User Interface program for
Sqlite in pure Python 3, with a liberal licence.
==> Did anyone know of something  I may have missed ?

As I don't have great goals, I started to build a small one.

I'm posting it there in hope :
- it can be of interest for some Python and Sqlite users,
- some of you can help to improve it more quickly :
. remaining tcl/tk glitches solving,
. better looking icons,
. missing basic functions.



Teaser :
https://github.com/stonebig/baresql/blob/master/examples/sqlite_py_manager.GIF?raw=true

Source : (not very clean source, )
https://github.com/stonebig/baresql/blob/master/examples/sqlite_py_manager.py


Status :
- works on any python 3.3 PC  : ONE .py file, only default modules.
- allows to create embedded functions in ... Python,
- GUI to import  CSV tables,
- GUI to attach other Sqlite databases,
- multi-Tab, multiple SQL requests and results per 'Tab'
- New query 'Tab' opening via :
   . double-clicking on a table '(Definition') field,
   . or double-clicking on a table normal field,

To Do :
- cleaning-up the code,
- CSV export,
- CSV import character-set handling (auto-detection of utf-8 utf-16),
- information bubble on the icon bar,
- (what basic function do you think is missing  ?)

Headaches  :
- the not perfect layout adjustements of 'Frames' inside a 'Tab' Window.
- the refresh of the left-database is closing the tree.



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

[sqlite] Very slow when query max(col) on empty set?

[Woody Wu]

I observed a strange behavior.  I was operating on a big table, there are 
200,000 records in it. The table has a primary key or unique index, (time, id1, 
id2), all of these indexed columns are integers.

The following query statement executed very slow, it took 15 secs on my ARM 
device,

1. select max(time) from mytable where time <  and id1 = k1 and id2 = n.

where  is a value that large enough that no a record has its time field 
equals to it. k1 is a not existed value that cannot be matched by any id1 
column in the table, while n is a normal value that can be matched in the table 
by a subset of records.

However, if I replace k1with another value that can be found in the table and 
keep everything unchanged, like below,

2. select max(time) from mytable where where time < 9 and id1 = k2 and 
id2 = n.

This query run very well, it only took less than 1 second on the same system.

Also, I tried to replace the very large time value to a small enough one,

3. select max(time) from mytable where time < 0 and id1=k1 and id2=n

This query also run very fast.

Could anyone explain this to me? Thanks in advance.

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

Re: [sqlite] Bug in division?

[Simon Slavin]

On 6 May 2014, at 1:52pm, RSmith  wrote:

> I think the OP might be seeing the list via one of those connected sites and 
> not getting the feedback. Maybe send a direct mail to him.

I'll send a personal email to him.

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

Re: [sqlite] transactions do not respect delete

[Dominique Devienne]

On Tue, May 6, 2014 at 2:59 PM, Clemens Ladisch  wrote:
> With the COMMIT, you told the database that the transaction succeeded
> (which means that the effects of all successful statements are saved
> permanently.)  If you want the transaction to fail, execute ROLLBACK
> instead.

But that's only possible for interactive SQL sessions. You cannot
"branch" in SQL "scripts" (successions of SQL commands) one executes
via sqlite3[.exe] at the command line. Using the ROLLBACK conflict
resolution on the insert is the only way to achieve what he wants as
described, short of switching to a "programmatic" environment to run
his "scripts" for full branching, etc... --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] transactions do not respect delete

[Clemens Ladisch]

Jim Carroll wrote:
> BEGIN;
>  DELETE FROM A;
>  INSERT INTO A VALUES(1, "goodbye");
>  INSERT INTO A VALUES(1, "world");-- fails
> COMMIT;
>
> It would appear the DELETE was successful, and the first INSERT was
> successful. But when the second INSERT failed (as it was intended to)..it
> did not ROLLBACK the database.

Why should it?  The statement failed, but this does not imply that the
transaction failed.  (Depending on the application, it might be possible
to recover by updating that record, or by ignoring the error.)

With the COMMIT, you told the database that the transaction succeeded
(which means that the effects of all successful statements are saved
permanently.)  If you want the transaction to fail, execute ROLLBACK
instead.


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

Re: [sqlite] transactions do not respect delete

[Mark Lawrence]

> It would appear the DELETE was successful, and the first INSERT was
> successful. But when the second INSERT failed (as it was intended to)..it
> did not ROLLBACK the database.

Even though the second INSERT fails, your script still calls COMMIT
on an open transaction in which the DELETE and first INSERT have
succeeded.

Typically an application would explicitly call ROLLBACK after a
statement failure if it didn't want the transaction to commit. The
following for example works the way you probably want it to.

CREATE TABLE A(id INT PRIMARY KEY, val TEXT);

INSERT INTO A VALUES(1, "hello");

BEGIN;
 DELETE FROM A;
 INSERT INTO A VALUES(1, "goodbye");  
 INSERT INTO A VALUES(1, "world");  
ROLLBACK;

SELECT * FROM A;

It appears your expectation is that if a statement fails then the
transaction is invalid (thereby ignoring the COMMIT). SQLite treats
that situation differently.

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

Re: [sqlite] transactions do not respect delete

[Dominique Devienne]

On Tue, May 6, 2014 at 2:31 PM, Jim Carroll  wrote:
>
> CREATE TABLE A(id INT PRIMARY KEY, val TEXT);
> INSERT INTO A VALUES(1, "hello");
> BEGIN;
>  DELETE FROM A;
>  INSERT INTO A VALUES(1, "goodbye");
>  INSERT INTO A VALUES(1, "world");
> COMMIT;

Sounds like you want ON CONFLICT ROLLBACK. --DD

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

Re: [sqlite] FW: Bug in division?

[RSmith]

I think the OP might be seeing the list via one of those connected sites and 
not getting the feedback. Maybe send a direct mail to him.

On 2014/05/06 14:48, John Drescher wrote:

Any reason I haven't heard back about this bug?

You did not get the rest of the discussion on your post? It is not a
bug but an implementation allowed behavior and has to do with integer
division.

John
___
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] FW: Bug in division?

[John Drescher]

> Any reason I haven't heard back about this bug?

You did not get the rest of the discussion on your post? It is not a
bug but an implementation allowed behavior and has to do with integer
division.

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

Re: [sqlite] Bug in division?

[Simon Slavin]

On 6 May 2014, at 2:06am, Gene Connor  wrote:

> SELECT DISTINCT 2/4 AS RESULT FROM TABLE;
> returns 0

Not a bug.  By providing two integer operands you have asked for integer 
arithmetic, and will get an integer answer.

It's something that happens in several different languages, even C !  Worse 
still, in some languages you get an integer answer if /either/ of the operands 
are integers.

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

[sqlite] FW: Bug in division?

[Gene Connor]

Any reason I haven't heard back about this bug?
 
Thanks

  _  

From: Gene Connor [mailto:neothreeei...@hotmail.com] 
Sent: Wednesday, April 30, 2014 12:21 AM
To: sqlite-users@sqlite.org
Subject: Bug in division?



SELECT DISTINCT 2/4 AS RESULT FROM TABLE;
returns 0

SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;
returns 0.5 (correct)

SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;
returns 0.5 (correct)

SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;
returns 0.5 (correct)

SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;
returns 0.49875 (correct)

As long as one or both numbers has at least one decimal place, it calcs
correctly.


Verified that it also happens in queries using tables and real data.


Windows SQLite version 3.8.4.3 2014-04-03 16:53:12


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

[sqlite] transactions do not respect delete

[Jim Carroll]

I need to modify all the content in a table. So I wrap the modifications
inside a transaction to ensure either all the operations succeed, or none
do.  I start the modifications with a DELETE statement, followed by INSERTs.
What I've discovered is even if an INSERT fails, the DELETE has still takes
place, and the database is not rolled back to the pre-transaction state.

 

I've created an example to demonstrate this issue. Put the following
commands into a script called EXAMPLE.SQL

 

CREATE TABLE A(id INT PRIMARY KEY, val TEXT);

 

INSERT INTO A VALUES(1, "hello");

 

BEGIN;

 DELETE FROM A;

 INSERT INTO A VALUES(1, "goodbye");  

 INSERT INTO A VALUES(1, "world");

COMMIT;

 

 SELECT * FROM A;

 

If you run the script: "sqlite3 a.db < EXAMPLE.SQL", you will see:

 

SQL error near line 10: column id is not unique

1|goodbye

 

What's surprising is that the SELECT statement results did not show
'1|hello'. 

 

It would appear the DELETE was successful, and the first INSERT was
successful. But when the second INSERT failed (as it was intended to)..it
did not ROLLBACK the database.

 

Is this a sqlite error? Or an error in my understanding of what is supposed
to happen?

 

Thanks

Jim

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