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

[sqlite] Set minimum word/character length in FTS4?

[skibulk]

Hi, I know that you can set the minimum word size in MySQL. I.E. if you
querry "dog" and the Minimum character length is set to 4 you won't get any
results. Is this same customization option avaliable in SQLite's Full Text
Search engine?

Thanks!
-- 
View this message in context: 
http://old.nabble.com/Set-minimum-word-character-length-in-FTS4--tp31499462p31499462.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] sqlite 3.7.6.2: sqlite3VdeExec says we have a corrupted database

[Simon Slavin]

On 28 Apr 2011, at 7:28pm, Frank Chang wrote:

>  Good afternoon, We are using the latest version of sqlite, 3.7.6.2 in 
> Windows XP 32bit ( and Centos Linux 5.5). sqlite3_step, sqlite3_reset and 
> sqlite3VdeExec says we have a corrupted database. We are not using the WAL 
> mode. 
>When I using sqlite3.exe to query the database, the queries return 
> correct values. So I am wondering if our database is really corrupted.



Use sqlite3.exe, or write your own code, to issue the following command:

PRAGMA integrity_check;

(from )

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

[sqlite] sqlite 3.7.6.2: sqlite3VdeExec says we have a corrupted database

[Frank Chang]

  Good afternoon, We are using the latest version of sqlite, 3.7.6.2 in Windows 
XP 32bit ( and Centos Linux 5.5). sqlite3_step, sqlite3_reset and 
sqlite3VdeExec says we have a corrupted database. We are not using the WAL 
mode. 
When I using sqlite3.exe to query the database, the queries return 
correct values. So I am wondering if our database is really corrupted.
Using the Windows C++ 2008 Debugger, we can isolate the detection of 
the corrupted sqlite database to the following lines:
lines 64503 - 64513 of sqlite.c(sqlite3VdbeExec) shown below. 
We were wondering how to fix this problem. Is our sqlite database 
really corrupted or are we doing something wrong in our application. Below 
lines  64504 - 64513 we show an excerpt of our application code.   
 
/* If we have read more header data than was contained in the header,
** or if the end of the last field appears to be past the end of the
** record, or if the end of the last field appears to be before the end
** of the record (when all fields present), then we must be dealing
** with a corrupt database.
*/
if( (u.am.zIdx > u.am.zEndHdr) || (u.am.offset > u.am.payloadSize)
|| (u.am.zIdx==u.am.zEndHdr && u.am.offset!=u.am.payloadSize) ){
rc = SQLITE_CORRUPT_BKPT;
goto op_column_out;
}
}
 
 
--APPLICATION CODE---
 strcpy(SelectStatement, "select [Key] from KeyFile order by rowid");
 ReturnValue=sqlite3_prepare(Database,SelectStatement,-1,,0);
 int mm(0);
 while (true){
 
  status =  sqlite3_step(Statement); 
  if (status == SQLITE_ROW) {
   bytes = sqlite3_column_bytes(Statement,0);
   Key = new char[bytes+1];
   memset(Key,0,54);
   memcpy(Key, sqlite3_column_text(Statement, 0),bytes);
  char *filestart = KeyArray->operator [](Offset); //Memory Mapped File
   strcpy([Offset],Key); //Memory Mapped File
   
   Offset += Matchcode->KeySize();
   delete [] Key;
   mm += 1;
  }
  else{
 resetstatus = sqlite3_reset(Statement);
 KeyArray->AddFinalRange(); // Memory Mapped File
break;
  }
 }
 status = sqlite3_finalize(Statement);
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users