Re: [sqlite] why a VIEW doesn't contain a ROWID
On Mon, 03 Sep 2007 18:05:05 -0300, you wrote:
>Hi Kees,
>
>He is telling about the Rowid the unique number that represents each row
>in the table, not about a table column named "ID" or anything else, or
>the primary key of the table.
>
>[]'s,
>
>Marco Antonio Abreu
>IT Quality Systems
>[EMAIL PROTECTED]
>http://www.itquality.com.br
You are right, but if a column is defined as INTEGER PRIMARY KEY
it acts as an alias for the (physical) ROWID, so it acually
describes the same case.
Definition:
http://www.sqlite.org/lang_createtable.html says:
Specifying a PRIMARY KEY normally just creates a UNIQUE index on
the corresponding columns. However, if primary key is on a
single column that has datatype INTEGER, then that column is
used internally as the actual key of the B-Tree for the table.
This means that the column may only hold unique integer values.
[...] If a table does not have an INTEGER PRIMARY KEY column,
then the B-Tree key will be a automatically generated integer.
The B-Tree key for a row can always be accessed using one of the
special names "ROWID", "OID", or "_ROWID_". This is true
regardless of whether or not there is an INTEGER PRIMARY KEY.
[...]
Proof:
CREATE TABLE testTbl(
t_name TEXT
);
INSERT INTO testTbl VALUES('d1');
INSERT INTO testTbl VALUES('d2');
INSERT INTO testTbl VALUES('d3');
INSERT INTO testTbl VALUES('d4');
SELECT ROWID,t_name FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_name='d2';
SELECT ROWID,t_name FROM testTbl;
1|d1
3|d3
4|d4
same picture..
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] why a VIEW doesn't contain a ROWID
Hi Kees,
He is telling about the Rowid the unique number that represents each row
in the table, not about a table column named "ID" or anything else, or
the primary key of the table.
[]'s,
Marco Antonio Abreu
IT Quality Systems
[EMAIL PROTECTED]
http://www.itquality.com.br
Kees Nuyt wrote:
> Hi Lokesh,
>
> On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:
>
>
>> This I know, but the thing is, I want the ROWID
>> in VIEW to be sequential even after a SELECT with
>> some condition has been executed, ie., from 1 to n.
>> Just like in normal table.
>> In your case it is not like that.
>>
>
> If you delete rows from a table the tables' rowid isn't
> consecutive anymore:
>
> CREATE TABLE testTbl(
> t_id INTEGER PRIMARY KEY,
> t_name TEXT
> );
> INSERT INTO testTbl VALUES( 1, 'd1' );
> INSERT INTO testTbl VALUES( 2, 'd2' );
> INSERT INTO testTbl VALUES( 3, 'd3' );
> INSERT INTO testTbl VALUES( 4, 'd4' );
> SELECT * FROM testTbl;
> 1|d1
> 2|d2
> 3|d3
> 4|d4
> DELETE FROM testTbl WHERE t_id=2;
> SELECT * FROM testTbl;
> 1|d1
> 3|d3
> 4|d4
>
>
>> By the way, what I mean to say is, why
>> don't we have default ROWID in >VIEW
>> like as in normal TABLE.
>>
>
> Because a view isn't a table.
>
> By the way, the concept of rowid is not in the SQL standard.
> It is a physical property (the B-Tree key) which rows happen to
> have when they are stored the SQLite way. It has no other
> meaning. A member of a set doesn't have an ordinal number in
> relational theory.
>
> Richard Hipp made rowid visible because some tight embedded
> applications can be speeded up nicely by using it.
> Any code which uses the rowid is not portable, though.
>
> The number of a row in a view is its order of its occurence.
> The first row has number 1
> The second row has number 2
> etc.
>
> It is easy to materialize that number in any language you will
> use around your SQL, even in a shell:
>
> sqlite3 your.db "select * from testTbl;" | \
> awk -v OFS='|' '{print NR,$0}'
>
> 1|1|d1
> 2|3|d3
> 3|4|d4
>
> note: \ is linewrap
>
> Regards,
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] why a VIEW doesn't contain a ROWID
Hi Lokesh,
On Mon, 3 Sep 2007 15:30:10 +0530, you wrote:
> This I know, but the thing is, I want the ROWID
> in VIEW to be sequential even after a SELECT with
> some condition has been executed, ie., from 1 to n.
> Just like in normal table.
> In your case it is not like that.
If you delete rows from a table the tables' rowid isn't
consecutive anymore:
CREATE TABLE testTbl(
t_id INTEGER PRIMARY KEY,
t_name TEXT
);
INSERT INTO testTbl VALUES( 1, 'd1' );
INSERT INTO testTbl VALUES( 2, 'd2' );
INSERT INTO testTbl VALUES( 3, 'd3' );
INSERT INTO testTbl VALUES( 4, 'd4' );
SELECT * FROM testTbl;
1|d1
2|d2
3|d3
4|d4
DELETE FROM testTbl WHERE t_id=2;
SELECT * FROM testTbl;
1|d1
3|d3
4|d4
> By the way, what I mean to say is, why
> don't we have default ROWID in >VIEW
> like as in normal TABLE.
Because a view isn't a table.
By the way, the concept of rowid is not in the SQL standard.
It is a physical property (the B-Tree key) which rows happen to
have when they are stored the SQLite way. It has no other
meaning. A member of a set doesn't have an ordinal number in
relational theory.
Richard Hipp made rowid visible because some tight embedded
applications can be speeded up nicely by using it.
Any code which uses the rowid is not portable, though.
The number of a row in a view is its order of its occurence.
The first row has number 1
The second row has number 2
etc.
It is easy to materialize that number in any language you will
use around your SQL, even in a shell:
sqlite3 your.db "select * from testTbl;" | \
awk -v OFS='|' '{print NR,$0}'
1|1|d1
2|3|d3
3|4|d4
note: \ is linewrap
Regards,
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] why a VIEW doesn't contain a ROWID
On 9/2/07, Babu, Lokesh <[EMAIL PROTECTED]> wrote: > Why a VIEW doesn't contain a ROWID field. Even though if it is > accessed, it contains (null) value. How to initialise this or how to > make it work as in TABLE. The rowid field exposes SQLite's internal storage mechanism for table rows. It has certain properties, such as being unique and relatively stable. Views are just stored queries which may reference many tables or calculate columns from other data, but do not store any data themselves. Since a view does not store a row, a rowid simply doesn't exist. It's not possible to create one. If you want table behavior, then use a table. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
This I know, but the thing is, I want the ROWID in VIEW to be sequential even after a SELECT with some condition has been executed, ie., from 1 to n. Just like in normal table. In your case it is not like that. Ah, OK, I asked a similar question a week or two ago, and had to come up with my own solution (two actually), in the absence of others. Look for the thread "Enumerating rows in a view" in this mail list (or archive). By the way, what I mean to say is, why don't we have default ROWID in VIEW like as in normal TABLE. In a few situations (such as my previous thread) I'd like to have an enumerated sequence in a view, but I would not want this to replace the rowid that we carry over from a table. We need the rowid from a table so we can match rows (eg when the user changes a value in view data which we need to redirect back to the corresponding table data). Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
This I know, but the thing is, I want the ROWID in VIEW to be sequential even after a SELECT with some condition has been executed, ie., from 1 to n. Just like in normal table. In your case it is not like that. By the way, what I mean to say is, why don't we have default ROWID in VIEW like as in normal TABLE. On 9/3/07, T&B <[EMAIL PROTECTED]> wrote: > > Why a VIEW doesn't contain a ROWID field. Even though if it is > > accessed, it contains (null) value. How to initialise this or how > > to make it work as in TABLE. > > To have your view include a rowid, you can include it in the select. > For instance, if you have a simple table: > > create table People > ( >"First Name" text, >"Last Name" text, >Email text, >Age integer > ) > > Then you can specifically include the rowid in a select statement or > view, such as this simple view based just on that table: > > create view Teenagers > as > select >rowid, >"First Name", >"Last Name" > from >People > where >Age >= 13 and Age <= 19 > ; > > Or to include all fields, you could: > > create view Everyone > as > select >rowid, >* > from >People > ; > > In those cases, the rowid in the view would be the same rowid as the > matching row in the table. > > Tom > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why a VIEW doesn't contain a ROWID
Why a VIEW doesn't contain a ROWID field. Even though if it is accessed, it contains (null) value. How to initialise this or how to make it work as in TABLE. To have your view include a rowid, you can include it in the select. For instance, if you have a simple table: create table People ( "First Name" text, "Last Name" text, Email text, Age integer ) Then you can specifically include the rowid in a select statement or view, such as this simple view based just on that table: create view Teenagers as select rowid, "First Name", "Last Name" from People where Age >= 13 and Age <= 19 ; Or to include all fields, you could: create view Everyone as select rowid, * from People ; In those cases, the rowid in the view would be the same rowid as the matching row in the table. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] why a VIEW doesn't contain a ROWID
Dear all, Why a VIEW doesn't contain a ROWID field. Even though if it is accessed, it contains (null) value. How to initialise this or how to make it work as in TABLE. - To unsubscribe, send email to [EMAIL PROTECTED] -
