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

-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

Re: [sqlite] Split Function for SQLite?

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

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

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

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

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

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

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

[sqlite] Split Function for SQLite?

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

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

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

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

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

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

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

Re: [sqlite] Last record in db

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

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

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

Re: [sqlite] Last record in db

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

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Simulating the BINARY data type

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

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

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

Re: [sqlite] Last record in db

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,

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,

Re: [sqlite] Last record in db

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

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

Re: [sqlite] Last record in db

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

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

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

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>

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

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

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

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Autoincrement failure

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

Re: [sqlite] Autoincrement failure

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

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

Re: [sqlite] Autoincrement failure

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;

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

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

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)

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

Re: [sqlite] Last record in db

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

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

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

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

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

Re: [sqlite] Last record in db

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

[sqlite] Autoincrement failure

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

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

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

Re: [sqlite] Last record in db

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

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

Re: [sqlite] Last record in db

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

[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

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

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(); }

Re: [sqlite] update required

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

[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

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

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.

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

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

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

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

[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

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

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

[sqlite] Add/modify a table in the existing Sqlite database in 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

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

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"