Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 08/23/2011 05:52 AM, Richard Hipp wrote:
> (1) Compilers sometimes make mistakes.  So it is important that you test
> your object code - not just your source code.  That means running your test
> cases using exactly the same *.o files that you use for delivery.  "Fly what
> you test and test what you fly."

I would recommend also compiling and running the test suite using PCC
(it is becoming the standard compiler on BSDs), CLANG (increasingly
pervasive) and the Intel compiler (known for strong optimisations,
excellent VTune performance analysis tool).

  http://en.wikipedia.org/wiki/Portable_C_Compiler
  http://software.intel.com/en-us/articles/intel-parallel-studio-xe/

> (2) I need to come up with a second, independent method of verifying branch
> test coverage in SQLite.

It looks like you need both a compiler that generates the necessary
information in the debug tables and a profiling tool that knows how to
read that.  I can only find gcc+gcov doing that, although the Intel
tools may do so as well.

It seems like you should be able to get statement test coverage using
almost any compiler and gcov or callgrind, which is better than nothing
but not as good as branch coverage.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk5TMSwACgkQmOOfHg372QQkEgCaAkX4Nl8ezZwgP6eDinWh1NIz
ORQAoN3TN2+WMc3QumnXxDcsEp+eDOo7
=dEiZ
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Split Function for SQLite?

[Igor Tandetnik]

Gregory Moore  wrote:
> I need to split up a list of items in a single row so they each have
> their own row.
> 
> Basically I need to take this:
> 
> Key. Code
> --
> 1.  V1, v2, v3
> 
> And convert it to this:
> 
> Key. Code
> --
> 1.  V1
> 1.  V2
> 1.  V3

I don't think you can do this with SQL alone. You'll have to implement the 
logic in your favorite programming language.
-- 
Igor Tandetnik

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

Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain

[Richard Hipp]

On Mon, Aug 22, 2011 at 11:15 PM, Simon Slavin  wrote:

>
> On 23 Aug 2011, at 1:22am, Richard Hipp wrote:
>
> > It appears that GCC 4.1.0 is not generating any code for the second test
> in
> > the conditional.  In other words, GCC 4.1.0 is compiling that statement
> as
> > if it omitted the "&& p->a" term
>
> How interesting.  Can't solve your problem but pure curiosity on my part:
> is this a result of optimization ?  Would it be worth trying all the '-O'
> options, especially '-O0', in
>
> 
>
> Or would your observation be independent of any such settings ?
>

Notice that my assembly-language listings were generated without any options
other than -g (so that I would get line numbers) and -S (to stop before
running the assemblier).  No optimizations specified, which unless I'm badly
mistaken, means than no optimizations are run.


>
> Given that the later version of GCC fixes the bug, I guess someone
> somewhere noticed the problem.
>
> Simon.
> ___
> 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] EXT :Re: Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> How does sqlite insert a record ? More specifically how does sqlite update 
> the B-tree with the new record . Is there a linkage
> made between the newly inserted record and the previous one ? 

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

-- 
Igor Tandetnik

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

Re: [sqlite] Bug in GCC - suggestions sought for a backup tool chain

[Simon Slavin]

On 23 Aug 2011, at 1:22am, Richard Hipp wrote:

> It appears that GCC 4.1.0 is not generating any code for the second test in
> the conditional.  In other words, GCC 4.1.0 is compiling that statement as
> if it omitted the "&& p->a" term

How interesting.  Can't solve your problem but pure curiosity on my part: is 
this a result of optimization ?  Would it be worth trying all the '-O' options, 
especially '-O0', in



Or would your observation be independent of any such settings ?

Given that the later version of GCC fixes the bug, I guess someone somewhere 
noticed the problem.

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

[sqlite] Split Function for SQLite?

[Gregory Moore]

I need to split up a list of items in a single row so they each have
their own row.

Basically I need to take this:

Key. Code
--
1.  V1, v2, v3

And convert it to this:

Key. Code
--
1.  V1
1.  V2
1.  V3

After much googling I'm thinking I need a split function. I know
SQLite allows addition of functions. I did find a few different
implementations of split functions, but there were from full SQL and
SQL Server sites.  Do I need a split function designed specifically
for SQLite?  How would I go about adding the function so I can use it?
Thanks!

Greg Moore
thewatchful...@gmail.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Last record in db

[sreekumar . tp]

I understand now. 

How does sqlite insert a record ? More specifically how does sqlite update the 
B-tree with the new record . Is there a linkage made between the newly inserted 
record and the previous one ?
Sent from BlackBerry® on Airtel

-Original Message-
From: Simon Slavin 
Sender: sqlite-users-boun...@sqlite.org
Date: Mon, 22 Aug 2011 17:32:51 
To: General Discussion of SQLite Database
Reply-To: General Discussion of SQLite Database 
Subject: Re: [sqlite] EXT :Re:  Last record in db


On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote:

> On 8/22/2011 9:52 AM, Black, Michael (IS) wrote:
>> No...if you use autoincrement you can guarantee that "last" will be the last 
>> record inserted.
> 
> There's no contradiction. "Last" is still defined only for ordered sets 
> - you just chose a particular ordering, by rowid.

And even if you do that, it's easy to break the expected ordering:

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ; 
id  data  
--  --
1   one   
2   two   
3   tre 

> delete from t1 where data = 'two';

> insert into t1 (it, data) values (2, 'second');

Now the 'last' record is not the one with the highest value in the id column.

Igor is right.  The question from the original poster doesn't mean anything in 
SQL.  SQL has no concept of an order for rows, so it has no idea which row is 
'first' or 'last'.  If you, the programmer have your own idea what is first or 
last, write your own numbers into the database.

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

Re: [sqlite] How to reindex an FTS3 table after changing the tokenizer

[Scott Hess]

On Fri, Aug 12, 2011 at 11:27 AM, john Papier  wrote:
> I have a FTS3 table that was created with the simple tokenizer. I want to
> change the tokenizer and reindex the table.
>
> Is there a way to change the tokenizer in place and have it reindex with
> minimal code?
>
> Else the other option I was thinking about was dropping the table,
> re-creating it with the new tokenizer, and inserting back the data

That's the option!  Make it seamless something like:

BEGIN;
ALTER TABLE my_table RENAME TO my_table_tmp;
CREATE VIRTUAL TABLE my_table USING FTS3(blah blah blah);
INSERT INTO my_table SELECT x, y, z FROM my_table_tmp;
DROP TABLE my_table_tmp;
COMMIT;

There's really not anything more in-place, because the code would have
to be tricky, like remembering which rows were indexed with which
tokenizer, so that it can update the right parts of the index when the
row is updated or deleted.

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

[sqlite] Bug in GCC - suggestions sought for a backup tool chain

[Richard Hipp]

Consider this line of code in the "build.c" source file of SQLite:

  http://www.sqlite.org/src/artifact/77be7c217430?ln=3372

It appears that GCC 4.1.0 is not generating any code for the second test in
the conditional.  In other words, GCC 4.1.0 is compiling that statement as
if it omitted the "&& p->a" term and looked like this:

  if( p ){ 

You can see this for yourself by downloading the file above and then
running:

  gcc -g -S build.c

And then looking at the build.s output file.  With GCC 4.1.0, I get this:

.loc 1 3372 0
cmpl$0, 8(%ebp)
je.L920

Looks like only one test to me.  But with GCC 4.5.2 I get this:

.loc 1 3372 0
cmpq$0, -24(%rbp)
je.L611
.loc 1 3372 0 is_stmt 0 discriminator 1
movq-24(%rbp), %rax
addq$8, %rax
testq%rax, %rax
je.L611

Both tests appear to be coded this time.

As it happens, the GCC bug is harmless in this case.  SQLite never invokes
the sqlite3SrcListShiftJoinType() function with a non-NULL SrcList pointer
that has a NULL p->a value.  So the p->a!=NULL test really is always true.
(Note that the GCC optimizer has no way of knowing that because the function
has external linkage.)  And so it didn't matter that the test was omitted.
I didn't notice the problem until this morning, when I upgraded my desktop
to the latest Ubuntu containing GCC 4.5.2, and reran the full branch
coverage tests.  GCC 4.5.2 was showing that the p->a!=NULL branch was always
true.  Further investigation shows that it has always been always true but
that the GCC 4.1.0 bug simply masked the error up until now.

I see two take-aways from this episode:

(1) Compilers sometimes make mistakes.  So it is important that you test
your object code - not just your source code.  That means running your test
cases using exactly the same *.o files that you use for delivery.  "Fly what
you test and test what you fly."

(2) I need to come up with a second, independent method of verifying branch
test coverage in SQLite.  I have been using GCC+GCOV and it does a great job
and I fully intend to continue using it as the primary tool chain for
development and testing.  But in this case, because GCC was omitting a test,
it missed the fact that there was no test coverage for the omitted test.  So
it would be nice to have an independently developed tool chain that can be
used to confirm the results we get from GCOV.  Anybody have any suggestions?

-- 
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] Last record in db

[Black, Michael (IS)]

last_insert_row_id isn't guaranteed either...and here's the complete example of 
using autoincrement that is guaranteed to work and not be volatile.

D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1 (id integer primary key autoincrement,data text);
sqlite> insert into t1 values(null,'one');
sqlite> insert into t1 values(null,'two');
sqlite> insert into t1 values(null,'three');
sqlite> .quit
D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select last_insert_rowid() from t1 limit 1;
0

last_insert_rowid is volatile.

sqlite> select id,data from t1 order by id desc limit 1;
3,three
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select id,data from t1 order by id desc limit 1;
4|two
sqlite>.quit
D:\SQLite>sqlite3 t1.db
SQLite version 3.7.4
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> select id,data from t1 order by id desc limit 1;
4|two



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 3:01 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db


On 8/22/2011 3:43 PM, Black, Michael (IS) wrote:
> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant.  I assumed "last 
> inserted".

Defining the meaning of the word "last" in terms of an expression that
includes the word "last" is a bit circular. Let's say, "most recently
inserted".

> Sure you can construct an example that doesn't work.  But he didn't ask how 
> NOT to do it.

OK then, show how to do it. You haven't, yet.

> Don't you agree that using autoincrement properly guarantees
> retrieving the last inserted row? Or are you maintaining that is a false 
> statement?

I maintain that the request you have shown - select id, data from Auto
where id=(select max(id) from Auto); - doesn't always retrieve the most
recently inserted row, and thus doesn't in fact solve the problem you
claim it solves.

>  I'd like to see an example to disprove it if you maintain that its false.

I have shown one.
--
Igor Tandetnik

___
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] Strange foreign key constraint failed with DROP TABLE

[Richard Hipp]

On Mon, Aug 22, 2011 at 12:54 PM, Boris Kolpackov
wrote:

> Hi Dan,
>
> Dan Kennedy  writes:
>
> > It is. Now fixed in the trunk.
>
> Thanks for the fix. I patched 3.7.7.1 with it and indeed this now
> works:
>
> BEGIN TRANSACTION;
> DROP TABLE employer;
> DROP TABLE employee;
> COMMIT;
>
>
> However, this transaction:
>
> BEGIN TRANSACTION;
> DELETE FROM employer;
> DROP TABLE employer;
>
> DELETE FROM employee;
>

Dan has convinced me that the SQLite code is correct as documented.  The
documentation states:  "Foreign key DML errors are may be reported if: (1)
The parent table does not exist..."  And for the DELETE statement above, the
parent table does not exist.  So it is appropriate to rais an error.



>  DROP TABLE employee;
> COMMIT;
>
> Still issues "Error: no such table: main.employer" after the second
> DELETE. I don't think this should happen either. What do you think?
>
> Boris
> --
> Boris Kolpackov, Code Synthesis
> http://codesynthesis.com/~boris/blog
> Compiler-based ORM 
> system for C++
> http://codesynthesis.com/products/odb
> Open-source  XML data
> binding for C++   http://codesynthesis.com/products/xsd
> XML data binding for embedded systems
> http://codesynthesis.com/products/xsde
>
> ___
> 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] Last record in db

[Black, Michael (IS)]

According to the docs rowid is not guaranteed to be monotonic.

So this is not guaranteed to give the right answer.

SELECT ... ORDER BY rowid DESC LIMIT 1

However, define your own autoincrement (myid) and it is.

SELECT ... ORDER BY myid DESC LIMIT 1




Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, August 22, 2011 2:49 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db


On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote:

> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant. [snip]

which is, of course, the problem with that question.

> Don't you agree that using autoincrement properly guarantees retrieving the 
> last inserted row?

As long as you're not messing about with the way SQLite does things, using the

SELECT ... ORDER BY rowid DESC LIMIT 1

form is as good an answer as any.

By the way, I don't think anyone has mentioned either

SELECT last_insert_rowid() FROM myTable

or the

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*)

C function yet.

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

Re: [sqlite] Last record in db

[Simon Slavin]

On 22 Aug 2011, at 8:43pm, Black, Michael (IS) wrote:

> I thought we were answering the question "how can I retrive the last row" -- 
> though we never got a definition of what "last" meant. [snip]

which is, of course, the problem with that question.

> Don't you agree that using autoincrement properly guarantees retrieving the 
> last inserted row?

As long as you're not messing about with the way SQLite does things, using the

SELECT ... ORDER BY rowid DESC LIMIT 1

form is as good an answer as any.

By the way, I don't think anyone has mentioned either

SELECT last_insert_rowid() FROM myTable

or the

sqlite3_int64 sqlite3_last_insert_rowid(sqlite3*)

C function yet.

Simon.

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

Re: [sqlite] Last record in db

[Black, Michael (IS)]

I thought we were answering the question "how can I retrive the last row" -- 
though we never got a definition of what "last" meant.  I assumed "last 
inserted".



Sure you can construct an example that doesn't work.  But he didn't ask how NOT 
to do it.



Don't you agree that using autoincrement properly guarantees retrieving the 
last inserted row?  Or are you maintaining that is a false statement?  I'd like 
to see an example to disprove it if you maintain that its false.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 1:14 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:56 PM, Black, Michael (IS) wrote:
> Ahhh...you didn't let autoincrement do it's job...

Yes, quite intentionally, in order to emphasize the point that "record
with the largest rowid" and "record inserted most recently" are not
necessarily one and the same, whether or not AUTOINCREMENT was specified
when the table was created.

Of course it's possible to construct an example where the same record is
both most recently inserted and has the largest rowid. But it's also
possible to construct an example where these are two different records.
--
Igor Tandetnik

___
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] Simulating the BINARY data type

[Igor Tandetnik]

On 8/22/2011 2:34 PM, Pete wrote:
> How can I store and retrieve data in the equivalent of mySQL's BINARY
> datatype?  The collation sequence doesn't matter in this instance.  Is BLOB
> the appropriate sqlite datatype?

Yes, use BLOB.
-- 
Igor Tandetnik

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

[sqlite] Simulating the BINARY data type

[Pete]

How can I store and retrieve data in the equivalent of mySQL's BINARY
datatype?  The collation sequence doesn't matter in this instance.  Is BLOB
the appropriate sqlite datatype?
Pete
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:56 PM, Black, Michael (IS) wrote:
> Ahhh...you didn't let autoincrement do it's job...

Yes, quite intentionally, in order to emphasize the point that "record 
with the largest rowid" and "record inserted most recently" are not 
necessarily one and the same, whether or not AUTOINCREMENT was specified 
when the table was created.

Of course it's possible to construct an example where the same record is 
both most recently inserted and has the largest rowid. But it's also 
possible to construct an example where these are two different records.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Ahhh...you didn't let autoincrement do it's job...





sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(NULL, 'most recent');
sqlite> select id, data from Auto where id=(select max(id) from Auto);
4|most recent



I wouldn't trust rowid given the description that it can reuse numbers.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 12:44 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:33 PM, Black, Michael (IS) wrote:
> That's because my id is autoincrement and yours is not.

What do you mean, mine vs yours? I continue with your example, using the
same setup.

To avoid any confusion, here's a full session:

sqlite> create table NoAuto(id integer primary key, data text);
sqlite> insert into NoAuto values (NULL, 'one');
sqlite> insert into NoAuto values (NULL, 'two');
sqlite> insert into NoAuto values (NULL, 'three');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid)
from NoAuto);
3|three
sqlite> delete from NoAuto where data='two';
sqlite> insert into NoAuto values(2, 'most recent');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid)
from NoAuto);
3|three



sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from
Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(2, 'most recent');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from
Auto);
3|three



Note how the record with data=='most recent' was never selected, despite
being inserted by the most recent successful INSERT statement.
--
Igor Tandetnik

___
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] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:33 PM, Black, Michael (IS) wrote:
> That's because my id is autoincrement and yours is not.

What do you mean, mine vs yours? I continue with your example, using the 
same setup.

To avoid any confusion, here's a full session:

sqlite> create table NoAuto(id integer primary key, data text);
sqlite> insert into NoAuto values (NULL, 'one');
sqlite> insert into NoAuto values (NULL, 'two');
sqlite> insert into NoAuto values (NULL, 'three');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) 
from NoAuto);
3|three
sqlite> delete from NoAuto where data='two';
sqlite> insert into NoAuto values(2, 'most recent');
sqlite> select rowid, data from NoAuto where rowid=(select max(rowid) 
from NoAuto);
3|three



sqlite> create table Auto(id integer primary key autoincrement, data text);
sqlite> insert into Auto values (NULL, 'one');
sqlite> insert into Auto values (NULL, 'two');
sqlite> insert into Auto values (NULL, 'three');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from 
Auto);
3|three
sqlite> delete from Auto where data='two';
sqlite> insert into Auto values(2, 'most recent');
sqlite> select rowid, data from Auto where rowid=(select max(rowid) from 
Auto);
3|three



Note how the record with data=='most recent' was never selected, despite 
being inserted by the most recent successful INSERT statement.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Black, Michael (IS)]

That's because my id is autoincrement and yours is not.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 12:18 PM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 1:11 PM, Black, Michael (IS) wrote:
> Sure it does.
>
> sqlite>  select rowid,id,* from t1 where id=(select max(id) from t1);
> 4|4|4|two
>
>
>
> "two" was the last succesful insert in my example.

But 'another two' (id==2) was the last successful insert in my extension
of your example. Yet your statement still returns 4.


> I'm now noticing though that rowid is not working as documented.
>
> http://www.sqlite.org/autoinc.html
>
> Says "If no ROWID is specified on the insert, or if the specified
> ROWID has a value of NULL, then an appropriate ROWID is created
> automatically. The usual algorithm is to give the newly created row a
> ROWID that is one larger than the largest ROWID in the table prior to
> the insert."
>
> Butif I delete the max rowid I expect it to be re-used based on the 
> above

The quote you cite applies to the case where AUTOINCREMENT keyword is
not specified. But in your latest example, you do specify one.
--
Igor Tandetnik

___
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] Last record in db

[Igor Tandetnik]

On 8/22/2011 1:11 PM, Black, Michael (IS) wrote:
> Sure it does.
>
> sqlite>  select rowid,id,* from t1 where id=(select max(id) from t1);
> 4|4|4|two
>
>
>
> "two" was the last succesful insert in my example.

But 'another two' (id==2) was the last successful insert in my extension 
of your example. Yet your statement still returns 4.


> I'm now noticing though that rowid is not working as documented.
>
> http://www.sqlite.org/autoinc.html
>
> Says "If no ROWID is specified on the insert, or if the specified
> ROWID has a value of NULL, then an appropriate ROWID is created
> automatically. The usual algorithm is to give the newly created row a
> ROWID that is one larger than the largest ROWID in the table prior to
> the insert."
>
> Butif I delete the max rowid I expect it to be re-used based on the 
> above

The quote you cite applies to the case where AUTOINCREMENT keyword is 
not specified. But in your latest example, you do specify one.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Sure it does.

sqlite> select rowid,id,* from t1 where id=(select max(id) from t1);
4|4|4|two



"two" was the last succesful insert in my example.





I'm now noticing though that rowid is not working as documented.





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

Says "If no ROWID is specified on the insert, or if the specified ROWID has a 
value of NULL, then an appropriate ROWID is created automatically. The usual 
algorithm is to give the newly created row a ROWID that is one larger than the 
largest ROWID in the table prior to the insert."



Butif I delete the max rowid I expect it to be re-used based on the 
above



sqlite> create table t1(id integer primary key autoincrement,data text);
sqlite> insert into t1 values(null,'one');
sqlite> insert into t1 values(null,'two');
sqlite> insert into t1 values(null,'three');
sqlite> select rowid,* from t1;
1|1|one
2|2|two
3|3|three
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select rowid,* from t1;
1|1|one
3|3|three
4|4|two
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(null,'two');
sqlite> select rowid,* from t1;
1|1|one
3|3|three
5|5|two<< This should be 4,5 and not 5,5 according to the docs as "3" was 
the largest in the table prior to insert.











Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, August 22, 2011 11:49 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Last record in db

On 8/22/2011 12:42 PM, Black, Michael (IS) wrote:
> autoincrement does keep order regardless of deletes.  rowid won't guarantee 
> it.
>
>
>
> So you don't "have" to add your own unless you need more than autoincrement.
>
>
>
> sqlite>  create table t1(id integer primary key,data text);
> sqlite>  insert into t1 values(NULL,'one');
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  insert into t1 values(NULL,'three');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 3
> sqlite>  delete from t1 where data='two';
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 4
>
>
>
> You'll always get the "last" record that was successfully inserted.

sqlite> insert into t1 values(2, 'another two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4

It seems that the record your statement returns is not the record that
was successfully inserted most recently.
--
Igor Tandetnik

___
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] Strange foreign key constraint failed with DROP TABLE

[Boris Kolpackov]

Hi Dan,

Dan Kennedy  writes:

> It is. Now fixed in the trunk.

Thanks for the fix. I patched 3.7.7.1 with it and indeed this now
works:

BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;


However, this transaction:

BEGIN TRANSACTION;
DELETE FROM employer;
DROP TABLE employer;

DELETE FROM employee;
DROP TABLE employee;
COMMIT;

Still issues "Error: no such table: main.employer" after the second
DELETE. I don't think this should happen either. What do you think?

Boris
-- 
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++  http://codesynthesis.com/products/odb
Open-source XML data binding for C++   http://codesynthesis.com/products/xsd
XML data binding for embedded systems  http://codesynthesis.com/products/xsde

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

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 12:42 PM, Black, Michael (IS) wrote:
> autoincrement does keep order regardless of deletes.  rowid won't guarantee 
> it.
>
>
>
> So you don't "have" to add your own unless you need more than autoincrement.
>
>
>
> sqlite>  create table t1(id integer primary key,data text);
> sqlite>  insert into t1 values(NULL,'one');
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  insert into t1 values(NULL,'three');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 3
> sqlite>  delete from t1 where data='two';
> sqlite>  insert into t1 values(NULL,'two');
> sqlite>  select rowid from t1 where rowid=(select max(rowid) from t1);
> 4
>
>
>
> You'll always get the "last" record that was successfully inserted.

sqlite> insert into t1 values(2, 'another two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4

It seems that the record your statement returns is not the record that 
was successfully inserted most recently.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Stephan Beal]

On Mon, Aug 22, 2011 at 6:42 PM, Black, Michael (IS)  wrote:

> You'll always get the "last" record that was successfully inserted.
>

Just to play devil's advocate for a moment...

As i recall, someone posted a report on this list a few months ago to report
that the "last" insert ID (sqlite3_last_insert_rowid()) in his case was the
ID of an insert (in a different table) caused as a side-effect of an
on-insert trigger in the original target table.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

autoincrement does keep order regardless of deletes.  rowid won't guarantee it.



So you don't "have" to add your own unless you need more than autoincrement.



sqlite> create table t1(id integer primary key,data text);
sqlite> insert into t1 values(NULL,'one');
sqlite> insert into t1 values(NULL,'two');
sqlite> insert into t1 values(NULL,'three');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
3
sqlite> delete from t1 where data='two';
sqlite> insert into t1 values(NULL,'two');
sqlite> select rowid from t1 where rowid=(select max(rowid) from t1);
4



You'll always get the "last" record that was successfully inserted.





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Monday, August 22, 2011 11:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] EXT :Re: Last record in db


On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote:

> On 8/22/2011 9:52 AM, Black, Michael (IS) wrote:
>> No...if you use autoincrement you can guarantee that "last" will be the last 
>> record inserted.
>
> There's no contradiction. "Last" is still defined only for ordered sets
> - you just chose a particular ordering, by rowid.

And even if you do that, it's easy to break the expected ordering:

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ;
id  data
--  --
1   one
2   two
3   tre

> delete from t1 where data = 'two';

> insert into t1 (it, data) values (2, 'second');

Now the 'last' record is not the one with the highest value in the id column.

Igor is right.  The question from the original poster doesn't mean anything in 
SQL.  SQL has no concept of an order for rows, so it has no idea which row is 
'first' or 'last'.  If you, the programmer have your own idea what is first or 
last, write your own numbers into the database.

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

Re: [sqlite] EXT :Re: Last record in db

[Simon Slavin]

On 22 Aug 2011, at 5:13pm, Igor Tandetnik wrote:

> On 8/22/2011 9:52 AM, Black, Michael (IS) wrote:
>> No...if you use autoincrement you can guarantee that "last" will be the last 
>> record inserted.
> 
> There's no contradiction. "Last" is still defined only for ordered sets 
> - you just chose a particular ordering, by rowid.

And even if you do that, it's easy to break the expected ordering:

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ; 
id  data  
--  --
1   one   
2   two   
3   tre 

> delete from t1 where data = 'two';

> insert into t1 (it, data) values (2, 'second');

Now the 'last' record is not the one with the highest value in the id column.

Igor is right.  The question from the original poster doesn't mean anything in 
SQL.  SQL has no concept of an order for rows, so it has no idea which row is 
'first' or 'last'.  If you, the programmer have your own idea what is first or 
last, write your own numbers into the database.

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

Re: [sqlite] EXT :Re: Last record in db

[Igor Tandetnik]

On 8/22/2011 9:52 AM, Black, Michael (IS) wrote:
> No...if you use autoincrement you can guarantee that "last" will be the last 
> record inserted.

There's no contradiction. "Last" is still defined only for ordered sets 
- you just chose a particular ordering, by rowid.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Igor Tandetnik]

On 8/22/2011 10:04 AM, Brad Stiles wrote:
> What happens when you do:
>
> select * from t1 where rowid = (select max( rowid ) from t1);
>
> or
>
> select * from t1 where rowid in (select max( rowid ) from t1);

or

select * from t1 order by rowid desc limit 1;

Likely more efficient this way.
-- 
Igor Tandetnik

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

Re: [sqlite] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 11:25 AM,  wrote:

> schema 3
>
> PRAGMA integrity_check
> returns
>
> *** in database main ***
> rowid 0 missing from index JournalDateIndex
> rowid 0 missing from index sqlite_autoindex_Journal_1
> wrong # of entries in index JournalDateIndex
> wrong # of entries in index sqlite_autoindex_Journal_1
>

Try running "REINDEX" and see if that clears up the problem.


>
>
>
>
>
> From:
> Richard Hipp 
> To:
> General Discussion of SQLite Database 
> Date:
> 22/08/2011 17.21
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 11:17 AM,  wrote:
>
> >  SELECT rowid FROM (mytable) WHERE (mystuff)
> >
> > returns
> >
> > 37 identical rows(!)  where Rowid = 1
> >
>
> What is your schema?
>
> If you run "PRAGMA integrity_check"?
>
>
> >
> >
> >
> > Alessandro
> >
> >
> >
> >
> > From:
> > a.azzol...@custom.it
> > To:
> > 
> > Date:
> > 22/08/2011 16.26
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > sqlite> .dump sqlite_sequence
> > PRAGMA foreign_keys=OFF;
> > BEGIN TRANSACTION;
> > COMMIT;
> > sqlite>
> >
> >
> > Any idea?
> >
> > Thanks
> > Alessandro
> >
> >
> >
> > From:
> > Richard Hipp 
> > To:
> > General Discussion of SQLite Database 
> > Date:
> > 22/08/2011 16.11
> > Subject:
> > Re: [sqlite] Autoincrement failure
> >
> >
> >
> > On Mon, Aug 22, 2011 at 9:56 AM,  wrote:
> >
> > > Hallo,
> > >
> > > Have you ever seen a SQLite3 DB file with autoincrement algoritm
> broken?
> > > Every new record seems to be added with rowid=1 overwriting existing
> > > info...
> > >
> > > Any idea about the causes of this issue
> > > and about extracting lost data (if present)?
> > >
> >
> > Please run from the sqlite3.exe shell:
> >
> > .dump sqlite_sequence
> >
> > Tell us what you see.
> >
> >
> > >
> > > Many thanks
> > > Alessandro
> > > ___
> > > 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-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
> >
>
>
>
> --
> 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-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] Autoincrement failure

[A . Azzolini]

schema 3

PRAGMA integrity_check
returns

*** in database main ***
rowid 0 missing from index JournalDateIndex
rowid 0 missing from index sqlite_autoindex_Journal_1
wrong # of entries in index JournalDateIndex
wrong # of entries in index sqlite_autoindex_Journal_1





From:
Richard Hipp 
To:
General Discussion of SQLite Database 
Date:
22/08/2011 17.21
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 11:17 AM,  wrote:

>  SELECT rowid FROM (mytable) WHERE (mystuff)
>
> returns
>
> 37 identical rows(!)  where Rowid = 1
>

What is your schema?

If you run "PRAGMA integrity_check"?


>
>
>
> Alessandro
>
>
>
>
> From:
> a.azzol...@custom.it
> To:
> 
> Date:
> 22/08/2011 16.26
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> sqlite> .dump sqlite_sequence
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite>
>
>
> Any idea?
>
> Thanks
> Alessandro
>
>
>
> From:
> Richard Hipp 
> To:
> General Discussion of SQLite Database 
> Date:
> 22/08/2011 16.11
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 9:56 AM,  wrote:
>
> > Hallo,
> >
> > Have you ever seen a SQLite3 DB file with autoincrement algoritm 
broken?
> > Every new record seems to be added with rowid=1 overwriting existing
> > info...
> >
> > Any idea about the causes of this issue
> > and about extracting lost data (if present)?
> >
>
> Please run from the sqlite3.exe shell:
>
> .dump sqlite_sequence
>
> Tell us what you see.
>
>
> >
> > Many thanks
> > Alessandro
> > ___
> > 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-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
>



-- 
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 11:17 AM,  wrote:

>  SELECT rowid FROM (mytable) WHERE (mystuff)
>
> returns
>
> 37 identical rows(!)  where Rowid = 1
>

What is your schema?

If you run "PRAGMA integrity_check"?


>
>
>
> Alessandro
>
>
>
>
> From:
> a.azzol...@custom.it
> To:
> 
> Date:
> 22/08/2011 16.26
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> sqlite> .dump sqlite_sequence
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite>
>
>
> Any idea?
>
> Thanks
> Alessandro
>
>
>
> From:
> Richard Hipp 
> To:
> General Discussion of SQLite Database 
> Date:
> 22/08/2011 16.11
> Subject:
> Re: [sqlite] Autoincrement failure
>
>
>
> On Mon, Aug 22, 2011 at 9:56 AM,  wrote:
>
> > Hallo,
> >
> > Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> > Every new record seems to be added with rowid=1 overwriting existing
> > info...
> >
> > Any idea about the causes of this issue
> > and about extracting lost data (if present)?
> >
>
> Please run from the sqlite3.exe shell:
>
> .dump sqlite_sequence
>
> Tell us what you see.
>
>
> >
> > Many thanks
> > Alessandro
> > ___
> > 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-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
>



-- 
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] Autoincrement failure

[A . Azzolini]

 SELECT rowid FROM (mytable) WHERE (mystuff) 

returns

37 identical rows(!)  where Rowid = 1



Alessandro




From:
a.azzol...@custom.it
To:

Date:
22/08/2011 16.26
Subject:
Re: [sqlite] Autoincrement failure



sqlite> .dump sqlite_sequence 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> 


Any idea?

Thanks
Alessandro



From:
Richard Hipp 
To:
General Discussion of SQLite Database 
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM,  wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


>
> Many thanks
> Alessandro
> ___
> 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-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] Autoincrement failure

[A . Azzolini]

sqlite> .dump sqlite_sequence 
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;
sqlite> 


Any idea?

Thanks
Alessandro



From:
Richard Hipp 
To:
General Discussion of SQLite Database 
Date:
22/08/2011 16.11
Subject:
Re: [sqlite] Autoincrement failure



On Mon, Aug 22, 2011 at 9:56 AM,  wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


>
> Many thanks
> Alessandro
> ___
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT :Re: Last record in db

[Brad Stiles]

On Mon, Aug 22, 2011 at 10:08 AM, Black, Michael (IS)
 wrote:
> Brad got it:

>> select * from t1 where rowid = max( rowid ) ;
>> Error: misuse of aggregate function max()

> sqlite> select * from t1 where rowid = (select max(rowid) from t1);
> 3|three
>
> Why is max(rowid) a "misuse".  Seems perfectly logical to me.  Not for an 
> update but should work for select.

I'm guessing that "max(rowid)" all by itself is either evaluating
"rowid" as a variable or value independent of a table, or failing
entirely because there is no context for evaluating rowid in the
failing instance.

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

Re: [sqlite] Autoincrement failure

[Richard Hipp]

On Mon, Aug 22, 2011 at 9:56 AM,  wrote:

> Hallo,
>
> Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
> Every new record seems to be added with rowid=1 overwriting existing
> info...
>
> Any idea about the causes of this issue
> and about extracting lost data (if present)?
>

Please run from the sqlite3.exe shell:

 .dump sqlite_sequence

Tell us what you see.


>
> Many thanks
> Alessandro
> ___
> 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] Last record in db

[Black, Michael (IS)]

sqlite3 does NOT guarantee rowid always increments and never gives FULL return 
(at least according to the docs).



autoincrement does.

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



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Stephan Beal [sgb...@googlemail.com]
Sent: Monday, August 22, 2011 9:07 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db

On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley wrote:

> > select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>

That can be rewritten as:

> select * from t1 order by rowid desc limit 1;

sqlite3 guarantees that the rowid only increments, never decrements. If the
rowid limit is ever hit (very unlikely to happen!) you'll get a "db full"
error, in which case it's probably time to recreate the table to get the
rowid to start counting at 1 again.

--
- stephan beal
http://wanderinghorse.net/home/stephan/
___
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] EXT :Re: Last record in db

[Black, Michael (IS)]

Brad got it:



sqlite> select * from t1 where rowid = (select max(rowid) from t1);
3|three



Why is max(rowid) a "misuse".  Seems perfectly logical to me.  Not for an 
update but should work for select.



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Brad Stiles [bradley.sti...@gmail.com]
Sent: Monday, August 22, 2011 9:04 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db

What happens when you do:

select * from t1 where rowid = (select max( rowid ) from t1);

or

select * from t1 where rowid in (select max( rowid ) from t1);



On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley
 wrote:
>
> Black, Michael (IS) wrote:
>
>> select * from table where rowid=max(rowid);
>
> $ sqlite3 m2d1.sql3
> -- Loading resources from /home/sk/.sqliterc
> SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
>
>> .tables
> t1  t2  t3
>
>> .schema t1
> CREATE TABLE t1(id INT,data TEXT);
>
>> select * from t1 ;
> id  data
> --  --
> 1   one
> 2   two
> 3   tre
>
>> select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>
>> select max( rowid ) from t1 ;
> max( rowid )
> 
> 3
>
>
> --
> Stanley C. Kitching
> Human Being
> Phoenix, Arizona
>
>
> ___
> 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] Last record in db

[Stephan Beal]

On Mon, Aug 22, 2011 at 4:01 PM, Cousin Stanley wrote:

> > select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>

That can be rewritten as:

> select * from t1 order by rowid desc limit 1;

sqlite3 guarantees that the rowid only increments, never decrements. If the
rowid limit is ever hit (very unlikely to happen!) you'll get a "db full"
error, in which case it's probably time to recreate the table to get the
rowid to start counting at 1 again.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Brad Stiles]

What happens when you do:

select * from t1 where rowid = (select max( rowid ) from t1);

or

select * from t1 where rowid in (select max( rowid ) from t1);



On Mon, Aug 22, 2011 at 10:01 AM, Cousin Stanley
 wrote:
>
> Black, Michael (IS) wrote:
>
>> select * from table where rowid=max(rowid);
>
> $ sqlite3 m2d1.sql3
> -- Loading resources from /home/sk/.sqliterc
> SQLite version 3.7.3
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
>
>> .tables
> t1  t2  t3
>
>> .schema t1
> CREATE TABLE t1(id INT,data TEXT);
>
>> select * from t1 ;
> id          data
> --  --
> 1           one
> 2           two
> 3           tre
>
>> select * from t1 where rowid = max( rowid ) ;
> Error: misuse of aggregate function max()
>
>> select max( rowid ) from t1 ;
> max( rowid )
> 
> 3
>
>
> --
> Stanley C. Kitching
> Human Being
> Phoenix, Arizona
>
>
> ___
> 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] Last record in db

[Cousin Stanley]

Black, Michael (IS) wrote:

> select * from table where rowid=max(rowid);

$ sqlite3 m2d1.sql3
-- Loading resources from /home/sk/.sqliterc
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

> .tables
t1  t2  t3

> .schema t1
CREATE TABLE t1(id INT,data TEXT);

> select * from t1 ; 
id  data  
--  --
1   one   
2   two   
3   tre   

> select * from t1 where rowid = max( rowid ) ; 
Error: misuse of aggregate function max()

> select max( rowid ) from t1 ; 
max( rowid )

3 


-- 
Stanley C. Kitching
Human Being
Phoenix, Arizona


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

[sqlite] Autoincrement failure

[A . Azzolini]

Hallo,

Have you ever seen a SQLite3 DB file with autoincrement algoritm broken?
Every new record seems to be added with rowid=1 overwriting existing 
info...

Any idea about the causes of this issue
and about extracting lost data (if present)?

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

Re: [sqlite] EXT :Re: Last record in db

[Black, Michael (IS)]

No...if you use autoincrement you can guarantee that "last" will be the last 
record inserted.



So "select * from mytable where myid=max(myid)" will work where myid is 
autoincrement.



The normal rowid will work also as long as you don't delete the max(rowid) and 
you don't insert more than 9,223,372,036,854,775,807 rows.



select * from mytable where rowid=max(rowid)



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





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 8:28 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Last record in db


Is 'last' valid only for 'ordered' set of records?

--Original Message--
From: Igor Tandetnik
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: Re: [sqlite] Last record in db
Sent: Aug 22, 2011 18:51

sreekumar...@gmail.com wrote:
> Let's say there is a table in a db that holds a few thousands of records..
> Records are inserted and deleted from the table. At any given point I should 
> be able to retrieve the 'last' record..

Last by what ordering?

>  'Last' is probably the record which is stored at the node with max depth?

What's a "node" or a "depth" in this context?
--
Igor Tandetnik

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


Sent from BlackBerry® on Airtel
___
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] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> Is 'last' valid only for 'ordered' set of records?

It would be more precise to say that an ordering induces GetLastRecord 
function, and vice versa. If you have a total ordering, then the "last" record 
is the one that compares greater than all others in this ordering.

In the other direction, if you have GetLastRecord(set_of_records) function 
defined somehow, then you can pick the last record, remove it from the set, 
pick the last of the remaining, remove that one from the set, and so on. This 
process generates a total ordering of the set.
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[sreekumar . tp]

Is 'last' valid only for 'ordered' set of records?

--Original Message--
From: Igor Tandetnik
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: Re: [sqlite] Last record in db
Sent: Aug 22, 2011 18:51

sreekumar...@gmail.com wrote:
> Let's say there is a table in a db that holds a few thousands of records..
> Records are inserted and deleted from the table. At any given point I should 
> be able to retrieve the 'last' record..

Last by what ordering?

>  'Last' is probably the record which is stored at the node with max depth?

What's a "node" or a "depth" in this context?
-- 
Igor Tandetnik

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


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> Let's say there is a table in a db that holds a few thousands of records..
> Records are inserted and deleted from the table. At any given point I should 
> be able to retrieve the 'last' record..

Last by what ordering?

>  'Last' is probably the record which is stored at the node with max depth?

What's a "node" or a "depth" in this context?
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[sreekumar . tp]

Let's say there is a table in a db that holds a few thousands of records..
Records are inserted and deleted from the table. At any given point I should be 
able to retrieve the 'last' record..

 'Last' is probably the record which is stored at the node with max depth?


--Original Message--
From: Igor Tandetnik
Sender: sqlite-users-boun...@sqlite.org
To: sqlite-users@sqlite.org
ReplyTo: General Discussion of SQLite Database
Subject: Re: [sqlite] Last record in db
Sent: Aug 22, 2011 18:33

sreekumar...@gmail.com wrote:
> 1.What's the fastest way to retrieve the last record in the DB.

a) Define "last". b) From which table in the DB?

> 2. Sqlite3_step takes you to the next record. Is there an equivalent for 
> navigating backwards ?

Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor
-- 
Igor Tandetnik

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


Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Or if you are talking about some specific select statement.



select * from table order by mystuff desc;





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 7:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Last record in db

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
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] Last record in db

[Igor Tandetnik]

sreekumar...@gmail.com wrote:
> 1.What's the fastest way to retrieve the last record in the DB.

a) Define "last". b) From which table in the DB?

> 2. Sqlite3_step takes you to the next record. Is there an equivalent for 
> navigating backwards ?

Not really, but see http://www.sqlite.org/cvstrac/wiki/wiki?p=ScrollingCursor
-- 
Igor Tandetnik

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

Re: [sqlite] Last record in db

[Black, Michael (IS)]

Oh...can I guess?



select * from table where rowid=max(rowid);





Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of sreekumar...@gmail.com [sreekumar...@gmail.com]
Sent: Monday, August 22, 2011 7:51 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] Last record in db

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
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] Last record in db

[sreekumar . tp]

Hi,

1.What's the fastest way to retrieve the last record in the DB.
2. Sqlite3_step takes you to the next record. Is there an equivalent for 
navigating backwards ?
Sent from BlackBerry® on Airtel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to Add a Table in the existing Sqlite database at client machine

[Jack Hughes]

Using Fluent Migrator project you write the following class...

[Migration(201101011411)]
public class Version_002 : FluentMigrator.Migration
{
public override void Up()
{

Create.Column("ColumnName").OnTable("TableName").AsInt32().Nullable();
}

public override void Down()
{
Delete.Column("ColumnName").FromTable("TableName");
}
}

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Madhan Kumar
Sent: 22 August 2011 13:27
To: sqlite-users@sqlite.org
Subject: [sqlite] how to Add a Table in the existing Sqlite database at client 
machine

 I,
I am using C# windows application(.Net2010) with sqlite,

Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data(not disturbed), and only add a
new table/column.

Is there any way something like bat file - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

Waiting for your reply.
Thanks
___
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] update required

[Paul Linehan]

2011/8/21 Richard Hipp :

> But we've also gotten messages (including some irate late-night
> phone calls to my personal telephone) complaining of problems with
> English-language versions as well.


That is a disgrace, and I hope that I speak for everyone on this list in
condemning this sort of completely unacceptable behaviour.

I have known projects in the past which have floundered because
the lead programmer just said that they had had enough of
rude/obscene/offensive emails/communications and that they
were oprhaning the project.

Just let me say that I hope you regard people who engage in this
sort of idiocy as being unworthy of even your disdain and that you
don't stop your marvellous contributions to the software world.

I have Bugzilla working with SQLite - brillo! In the coming months I
hope to evaluate Fossil.

As the French would say (or rather write) Sincères salutations.


Paul...


> D. Richard Hipp

-- 


lineh...@tcd.ie

Mob: 00 353 86 864 5772
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to Add a Table in the existing Sqlite database at client machine

[Madhan Kumar]

 I,
I am using C# windows application(.Net2010) with sqlite,

Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data(not disturbed), and only add a
new table/column.

Is there any way something like bat file - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

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

Re: [sqlite] how to Add/modify a table in the existing Sqlite database at client side

[Sumit Gupta]

Hello,

IF you need to do that just to setup your database once, then you can use
any of free tools as suggested. Or you can simply run a SQLCommand through
your code to drop and recreate a table using Standard SQL Create Table
statement. Alter query will work to drop and add column as well.

Sumit

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Madhankumar Rajaram
Sent: 22 August 2011 16:45
To: sqlite-users@sqlite.org
Subject: [sqlite] how to Add/modify a table in the existing Sqlite database
at client side


Hi,
 I am using C# windows application(.Net2010) with sqlite,
and i
need
to change the database table.
   Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data, and only change the script
in order to add a new table/column.

Is there any way something like bat file  - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

Waiting for your reply.
Thanks

- Madhan




 
 
This email and any attached files ("Message") may contain confidential
and/or privileged information. It is intended solely for the addressee(s).
If you receive this Message in error, inform the sender by reply email,
delete the Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part
thereof is prohibited. Emails are susceptible to alteration. Neither Technip
nor any of its affiliates shall be liable for the Message if altered or
falsified nor shall they be liable for any damage caused by any virus that
might be transmitted with this Message.

___
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 Add/modify a table in the existing Sqlite database at client side

[Jack Hughes]

Take a look at Fluent Migrator project it supports modifying SQLite schema. 
http://lostechies.com/seanchambers/2011/04/02/fluentmigrator-getting-started/

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Madhankumar Rajaram
Sent: 22 August 2011 12:15
To: sqlite-users@sqlite.org
Subject: [sqlite] how to Add/modify a table in the existing Sqlite database at 
client side


Hi,
 I am using C# windows application(.Net2010) with sqlite, and i
need
to change the database table.
   Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data, and only change the script
in order to add a new table/column.

Is there any way something like bat file  - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

Waiting for your reply.
Thanks

- Madhan




 
 
This email and any attached files ("Message") may contain confidential and/or 
privileged information. It is intended solely for the addressee(s). If you 
receive this Message in error, inform the sender by reply email, delete the 
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part 
thereof is prohibited. Emails are susceptible to alteration. Neither Technip 
nor any of its affiliates shall be liable for the Message if altered or 
falsified nor shall they be liable for any damage caused by any virus that 
might be transmitted with this Message.

___
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] Add/modify a table in the existing Sqlite database in client side.

[Jean-Christophe Deschamps]

>I am using C# windows application(.Net2010) with sqlite, and i need
>to change the database table.
>Can you pls let me know, how to add or modify a new column/ 
> table in
>the existing Sqlite database that is in my client desktop machine.
>I want the existing database to have its data, and only change the script
>in order to add a new table/column.
>
>Is there any way something like bat file  - to execute and update the
>database file in client machine.
>or any other way to execute the scripts( like oracle updation of script)

Give SQLite Expert a try.  This 
third-party SQLite manager allows you to change your schema very easily 
as well as query/update your DB in all possible ways.

If you're going to use SQLite professionnally, don't hesitate to buy 
the Pro version which offers more features that the freeware 
one.  Whatever rate you're being paid, the tool will pay back in days, 
if not within hours.


--
j...@antichoc.net  

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

[sqlite] how to Add/modify a table in the existing Sqlite database at client side

[Madhankumar Rajaram]

Hi,
 I am using C# windows application(.Net2010) with sqlite, and i
need
to change the database table.
   Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data, and only change the script
in order to add a new table/column.

Is there any way something like bat file  - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

Waiting for your reply.
Thanks

- Madhan




 
 
This email and any attached files ("Message") may contain confidential and/or 
privileged information. It is intended solely for the addressee(s). If you 
receive this Message in error, inform the sender by reply email, delete the 
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part 
thereof is prohibited. Emails are susceptible to alteration. Neither Technip 
nor any of its affiliates shall be liable for the Message if altered or 
falsified nor shall they be liable for any damage caused by any virus that 
might be transmitted with this Message.

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

Re: [sqlite] Add/modify a table in the existing Sqlite database in client side.

[Simon Slavin]

On 22 Aug 2011, at 11:17am, Madhankumar Rajaram wrote:

>   I am using C# windows application(.Net2010) with sqlite, and i need
> to change the database table.
>   Can you pls let me know, how to add or modify a new column/ table in
> the existing Sqlite database that is in my client desktop machine.
> I want the existing database to have its data, and only change the script
> in order to add a new table/column.
> 
> Is there any way something like bat file  - to execute and update the
> database file in client machine.
> or any other way to execute the scripts( like oracle updation of script)

You can download the SQLite shell tool from



I'd guess that you would want

sqlite-shell-win32-x86-3070701.zip

You can use this to open your database file and execute any SQL commands you 
want against it, including 'CREATE TABLE ...' and 'ALTER TABLE ...'.  
Instructions and examples for using the shell tool can be found here:



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

[sqlite] Add/modify a table in the existing Sqlite database in client side.

[Madhankumar Rajaram]

Hi,
I am using C# windows application(.Net2010) with sqlite, and i need
to change the database table.
   Can you pls let me know, how to add or modify a new column/ table in
the existing Sqlite database that is in my client desktop machine.
I want the existing database to have its data, and only change the script
in order to add a new table/column.

Is there any way something like bat file  - to execute and update the
database file in client machine.
or any other way to execute the scripts( like oracle updation of script)

Waiting for your reply.
Thanks

- Madhan


 
 
This email and any attached files ("Message") may contain confidential and/or 
privileged information. It is intended solely for the addressee(s). If you 
receive this Message in error, inform the sender by reply email, delete the 
Message and destroy any printed copy.
Any unauthorized use, distribution, or copying of this Message or any part 
thereof is prohibited. Emails are susceptible to alteration. Neither Technip 
nor any of its affiliates shall be liable for the Message if altered or 
falsified nor shall they be liable for any damage caused by any virus that 
might be transmitted with this Message.

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

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

[Dan Kennedy]

On 08/20/2011 12:50 AM, Duquette, William H (318K) wrote:
>
> On 8/19/11 10:44 AM, "Boris Kolpackov"  wrote:
>
>> Hi William,
>>
>> "Duquette, William H (318K)"  writes:
>>
>>> On 8/19/11 10:18 AM, "Boris Kolpackov"  wrote:
>>>
>>> BEGIN TRANSACTION;
>>> DROP TABLE employer;
>>> DROP TABLE employee;
>>> COMMIT;
>>>
>>> According to the sqlite docs, dropping a table when FK constraints are
>>> enabled does an implicit "DELETE FROM" first.
>>> so how can there still be FK constraint violations?
>>> Am I missing something?
>> If so, then that would be the two of us. Though I think this is a bug
>> in SQLite.
>
> I think it might be.

It is. Now fixed in the trunk.

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

Dan.


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