Re: [sqlite] vacuum and rowids

[Stephan Beal]

On Fri, Apr 29, 2011 at 3:37 AM, Simon Slavin  wrote:

> The thing is, there's not problem with referring to rowid, or ROWID or any
> of the other aliases when you do this.  The only problem is possible
> confusion for the programmer if you define a column with one of these names
> which /isn't/ an alias to SQLite's internal row numbers.  And it's just
> programmer confusion: if you know exactly how SQLite handles this then it's
> fine.
>

Another subtle problem/confusion point is that:

select * ...;

will not return 'rowid' unless it is explicitly declared or you do this:

select rowid, * ...

e.g.:
sqlite> select * from whiki_page_tag;
eek|tag1|
eek2|tag1|
eek|tag2|
sqlite> select rowid,* from whiki_page_tag;
1|eek|tag1|
2|eek2|tag1|
3|eek|tag2|


-- 
- 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] vacuum and rowids

[Nico Williams]

On Thu, Apr 28, 2011 at 5:20 PM, Dave Hayden  wrote:
> On Apr 28, 2011, at 12:41 PM, Pavel Ivanov wrote:
>>> After more poking, it appears that rowids might not be changed by a vacuum 
>>> if I have an index on the table. Is this true? If so, is it something I can 
>>> rely on going forward?
>>
>> No, it's not true. The only way to keep your rowids intact is to declare an 
>> INTEGER PRIMARY KEY alias for it. And you better never reference "rowid" 
>> name in your application or your database schema.
>
> Can you explain this in more detail? I've never seen any prohibition on using 
> "rowid" in the SQLite docs before. The page on autoincrement says
>
> "You can access the ROWID of an SQLite table using one the special column 
> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
> to use one of those special names, then the use of that name will refer to 
> the declared column not to the internal ROWID."
>
> which suggests that referring to rowids is fine. If I add a "rowid integer 
> primary key" column on my tables, it seems like everything would work the way 
> I want it to with minimal code changes. Any reason that won't work?

As long as your 'rowid' column is declared as an INTEGER PRIMARY KEY,
then yes, VACUUM will preserve rowids and you'll be able to use the
rowid column name without other issues.

Others have pointed out that using 'rowid' for any column other than
an INTEGER PRIMARY KEY column would be confusing, but that's not what
you're after.  But even so, using 'rowid' is probably not in your best
interest, not if you could use a more descriptive name, for example.

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

Re: [sqlite] vacuum and rowids

[Marco Bambini]

Dave please take a look at this blog post:
http://www.sqlabs.com/blog/?p=51
--
Marco Bambini
http://www.sqlabs.com






On Apr 28, 2011, at 9:36 PM, Dave Hayden wrote:

> When the VACUUM feature was added I took a look at using it to keep database 
> file sizes down, but discovered that it changed rowids and messed up my 
> references between tables (or what I gather the database people call "foreign 
> keys"). I'm playing around with this again and it looks like rowids aren't 
> affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild 
> the existing tables if I don't have to.
> 
> After more poking, it appears that rowids might not be changed by a vacuum if 
> I have an index on the table. Is this true? If so, is it something I can rely 
> on going forward?
> 
> Thanks!
> -Dave
> 
> ___
> 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] vacuum and rowids

[Pavel Ivanov]

>> which suggests that referring to rowids is fine.
>
> It does not suggest referring to ROWIDs is fine, it only says that it
> can be done.  I think Pavel's point is that referencing ROWIDs is bad
> practice, so that is why he says you shouldn't do it.

Yes, that's right. You can refer to rowid, but it's a bad practice.
Especially if you refer to it in foreign keys. As you have seen just
vacuum the database and suddenly you see some bugs, incorrect foreign
key references and you don't understand how they were able to make
their way into the database.

You are right that by just adding a column ROWID INTEGER PRIMARY KEY
you can fix things without changing much of the code but it would be a
big confusion for any developer who will look at your code either
after you or as an additional developer on the project. So in a short
term if you want a quick hack that would be fairly decent solution.
But in a long term I would suggest to add column ID INTEGER PRIMARY
KEY and change all references to rowid towards id.


Pavel


On Thu, Apr 28, 2011 at 9:31 PM, Rich Rattanni  wrote:
>> "You can access the ROWID of an SQLite table using one the special column 
>> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
>> to use one of those special names, then the use of that name will refer to 
>> the declared column not to the internal ROWID."
>>
>> which suggests that referring to rowids is fine.
>
> It does not suggest referring to ROWIDs is fine, it only says that it
> can be done.  I think Pavel's point is that referencing ROWIDs is bad
> practice, so that is why he says you shouldn't do it.
>
> --
> Rich
> ___
> 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] vacuum and rowids

[Simon Slavin]

On 29 Apr 2011, at 2:31am, Rich Rattanni wrote:

>> "You can access the ROWID of an SQLite table using one the special column 
>> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
>> to use one of those special names, then the use of that name will refer to 
>> the declared column not to the internal ROWID."
>> 
>> which suggests that referring to rowids is fine.
> 
> It does not suggest referring to ROWIDs is fine, it only says that it
> can be done.  I think Pavel's point is that referencing ROWIDs is bad
> practice, so that is why he says you shouldn't do it.

The thing is, there's not problem with referring to rowid, or ROWID or any of 
the other aliases when you do this.  The only problem is possible confusion for 
the programmer if you define a column with one of these names which /isn't/ an 
alias to SQLite's internal row numbers.  And it's just programmer confusion: if 
you know exactly how SQLite handles this then it's fine.

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

Re: [sqlite] vacuum and rowids

[Rich Rattanni]

> "You can access the ROWID of an SQLite table using one the special column 
> names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column 
> to use one of those special names, then the use of that name will refer to 
> the declared column not to the internal ROWID."
>
> which suggests that referring to rowids is fine.

It does not suggest referring to ROWIDs is fine, it only says that it
can be done.  I think Pavel's point is that referencing ROWIDs is bad
practice, so that is why he says you shouldn't do it.

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

Re: [sqlite] vacuum and rowids

[Dave Hayden]

On Apr 28, 2011, at 12:41 PM, Pavel Ivanov wrote:

>> After more poking, it appears that rowids might not be changed by a vacuum 
>> if I have an index on the table. Is this true? If so, is it something I can 
>> rely on going forward?
> 
> No, it's not true. The only way to keep your rowids intact is to declare an 
> INTEGER PRIMARY KEY alias for it. And you better never reference "rowid" name 
> in your application or your database schema.

Can you explain this in more detail? I've never seen any prohibition on using 
"rowid" in the SQLite docs before. The page on autoincrement says

"You can access the ROWID of an SQLite table using one the special column names 
ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use 
one of those special names, then the use of that name will refer to the 
declared column not to the internal ROWID."

which suggests that referring to rowids is fine. If I add a "rowid integer 
primary key" column on my tables, it seems like everything would work the way I 
want it to with minimal code changes. Any reason that won't work?

-D

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

Re: [sqlite] vacuum and rowids

[Pavel Ivanov]

> After more poking, it appears that rowids might not be changed by a vacuum if 
> I have an index on the table. Is this true? If so, is it something I can rely 
> on going forward?

No, it's not true. The only way to keep your rowids intact is to
declare an INTEGER PRIMARY KEY alias for it. And you better never
reference "rowid" name in your application or your database schema.


Pavel


On Thu, Apr 28, 2011 at 3:36 PM, Dave Hayden  wrote:
> When the VACUUM feature was added I took a look at using it to keep database 
> file sizes down, but discovered that it changed rowids and messed up my 
> references between tables (or what I gather the database people call "foreign 
> keys"). I'm playing around with this again and it looks like rowids aren't 
> affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild 
> the existing tables if I don't have to.
>
> After more poking, it appears that rowids might not be changed by a vacuum if 
> I have an index on the table. Is this true? If so, is it something I can rely 
> on going forward?
>
> Thanks!
> -Dave
>
> ___
> 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] vacuum and rowids

[Dave Hayden]

When the VACUUM feature was added I took a look at using it to keep database 
file sizes down, but discovered that it changed rowids and messed up my 
references between tables (or what I gather the database people call "foreign 
keys"). I'm playing around with this again and it looks like rowids aren't 
affected if I have an INTEGER PRIMARY KEY column, but I don't want to rebuild 
the existing tables if I don't have to.

After more poking, it appears that rowids might not be changed by a vacuum if I 
have an index on the table. Is this true? If so, is it something I can rely on 
going forward?

Thanks!
-Dave

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