-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 f
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
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
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
___
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 curiosi
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 funct
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:
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 wa
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
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 in
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;
>
>
> Howev
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
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 Inf
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 p
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
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
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
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 AUTOINCREM
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, da
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
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..
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 exampl
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 in
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;
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>
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_
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
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
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
___
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.
--
I
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
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:
Gener
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.
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;
sq
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:
On Mon, Aug 22, 2011 at 4:09 PM, Black, Michael (IS) wrote:
> 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
My apologies, i wasn't aware that autoincrement causes
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 perfe
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)?
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
_
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
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. I
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);
>
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
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
__
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,2
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 orderi
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...@gm
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 sto
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-
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...@s
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/
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...
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
htt
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();
}
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 co
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 th
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
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
Sen
>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, a
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
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 deskt
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,
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
63 matches
Mail list logo