headers
Tito Ciuro | 1 May 2004 10:39
Picon
Gravatar

[sqlite] Explanation about ROWID requested

Hello,

A few days ago I posted a question and I haven't seen any comments so 
far. I'm really curious about ROWID's volatility. How can I make sure 
that ROWIDs do not get re-initialized? I'm posting the message once 
again hoping that someone will explain how I should properly use 
ROWIDs.

Thanks again. Best regards,

-- Tito

**************************************************************

Would this explanation about ROWID make sense?:

> Referencing ROWID: If you make references to ROWID but then export 
> your database (using, for example, the ".dump" command of the sqlite 
> shell) and reimport it, all of your ROWIDs will change and your 
> references won't
> be right any more.  If you use an INTEGER PRIMARY KEY, the ".dump" 
> command will preserve the values and your references will not be 
> broken by the export.
>
> *** IMPORTANT ***: Please note that ROWID columns are always created 
> by SQLite, even if you don't specify it when creating the table via 
> CREATE TABLE. If this is the case and you do something like this:
>
> SELECT * FROM <some table>
>
(Continue reading)

Permalink | Reply | 21 comments |
headers
eno | 1 May 2004 12:58
Gravatar

Re: [sqlite] Explanation about ROWID requested

Tito Ciuro wrote:

> Hello,
> 
> A few days ago I posted a question and I haven't seen any comments so 
> far. I'm really curious about ROWID's volatility. How can I make sure 
> that ROWIDs do not get re-initialized? I'm posting the message once 
> again hoping that someone will explain how I should properly use ROWIDs.
> 
> Thanks again. Best regards,
> 
> -- Tito

Hi Tito,

AFAIK the ROWID is used internally as a means to organize the individual 
rows of a table in storage. Basically it is an integer, which is 
different between different rows.

However, if a table features an INTEGER PRIMARY KEY column (which 
obviously is different between different rows too), the database engine 
might use that value instead if an internally only handled value, 
thereby saving one stage of indirection.

Of course, when writing such a table into an external format and 
rereading it, these values shall not change - as it is with all values 
saved via ".dump" and reread (if they would, nobody would ".dump" call a 
backup utility, but "obfuscator" instead :)

I even think referencing the ROWID, even when possible, is IMHO a sign
(Continue reading)

Permalink | Reply | 0 comments |
headers
Derrell.Lipman | 1 May 2004 14:21

Re: [sqlite] Explanation about ROWID requested

Tito Ciuro <tciuro@...> writes:

> Hello,
>
> A few days ago I posted a question and I haven't seen any comments so
> far. I'm really curious about ROWID's volatility. How can I make sure that
> ROWIDs do not get re-initialized? I'm posting the message once again hoping
> that someone will explain how I should properly use ROWIDs.

If you declare an INTEGER PRIMARY KEY then it will be used as the ROWID:

    sqlite> CREATE TABLE test
       ...> (
       ...>   id INTEGER PRIMARY KEY,
       ...>   t  TEXT
       ...> );
    sqlite> INSERT INTO test VALUES (23, 'hello');
    sqlite> INSERT INTO test VALUES (42, 'world');
    sqlite> SELECT id, t FROM test;
       id = 23
        t = hello

       id = 42
        t = world
    sqlite> SELECT ROWID, t FROM test;
    ROWID = 23
        t = hello

    ROWID = 42
        t = world
(Continue reading)

Permalink | Reply | 0 comments |
headers
D. Richard Hipp | 1 May 2004 14:38

Re: [sqlite] Explanation about ROWID requested

Every row of every table has a ROWID.  The ROWID can be
called "ROWID", "_ROWID_", and/or "OID".  All three names
refer to the same value and can be used interchangably.
But if you declare a column with any of those names, the
name refers to your declared column, not the actual
ROWID.  This is similar to how an local automatic variable
will hide a global variable by the same name in C/C++.

If you declare a column to be an INTEGER PRIMARY KEY, then
that column becomes another alias for the ROWID.

Examples:

Given a table of the following form:

    CREATE TABLE t1(x INTEGER PRIMARY KEY, y);

All of the following statements are equivalent:

    SELECT rowid, y FROM t1;
    SELECT _rowid_, y FROM t1;
    SELECT oid, y FROM t1;
    SELECT x, y FROM t1;

As far as SQLite is concerned, "rowid", "_rowid_", "oid",
and "x" are just different names for the same value.

These statements are also all equivalent:

    INSERT INTO t1(rowid,y) VALUES(1,2);
(Continue reading)

Permalink | Reply | 18 comments |
headers
Tito Ciuro | 1 May 2004 15:44
Picon
Gravatar

Re: [sqlite] Explanation about ROWID requested

Hello Dr. Hipp,

On 1 may 2004, at 8:38, D. Richard Hipp wrote:

> When you do a "SELECT *", the results contain only columns
> that are explicitly declared in the CREATE TABLE statement.
> If you have declared an INTEGER PRIMARY KEY column, then
> the rowid will appear under that column name.  If there is
> no INTEGER PRIMARY KEY, then the rowid will not be a part
> of the result.  The ".dump" command works by doing a
> "SELECT *".  So if you do not have an INTEGER PRIMARY KEY
> in your table declaration, the rowid will not be part of the
> saved data and will be lost when the table is reconstructed.

This is the explanation I was hoping for. Excellent!

BTW... I think that this information should be mentioned in the 
documentation, FAQ, or Wiki (why not all 3! ;-) If nobody is against 
it, I can take care of the Wiki page. Is that OK with you?

I would like to thank everyone else that answered my post. I truly 
appreciate it.

Best regards,

-- Tito

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
(Continue reading)

Permalink | Reply | 0 comments |
headers
Nemanja Corlija | 2 May 2004 12:25

[sqlite] problems with building tclsqlite on win32

Hi,

I have a problem with compiling static version
of tclsqlite on win32 with MinGW/MSYS?

I've downloaded tcltk-8.4.1-1.exe from MinGW site
and installed it in MinGW dirs.

I've compiled both, static and shared, versions of
SQLite from 2.8.13 sources and then I tried to build
libtclsqlite.la target and here is what I get:

<shell>
$ mingw32-make.exe libtclsqlite.la
./libtool --mode=compile gcc -g -O2 -DOS_UNIX=0 -DOS_WIN=1 -I. -I./src  -c ./src/tclsqlite.c
 gcc -g -O2 -DOS_UNIX=0 -DOS_WIN=1 -I. -I./src -c ./src/tclsqlite.c  -DDLL_EXPORT -DPIC -o .libs/tclsqlite.o
 gcc -g -O2 -DOS_UNIX=0 -DOS_WIN=1 -I. -I./src -c ./src/tclsqlite.c -o tclsqlite.o >/dev/null 2>&1
./libtool --mode=link gcc -g -O2 -DOS_UNIX=0 -DOS_WIN=1 -I. -I./src -o libtclsqlite.la tclsqlite.lo \
libsqlite.la -ltcl84   -rpath /usr/local/lib/sqlite \
-version-info "8:6:8"

*** Warning: This system can not link to static lib archive libsqlite.la.
*** I have the capability to make that library automatically link in when
*** you link to this library.  But I can only do this if you have a
*** shared version of the library, which you do not appear to have.
libtool: link: warning: undefined symbols not allowed in i686-pc-mingw32 shared libraries
ar cru .libs/libtclsqlite.a  tclsqlite.o
ranlib .libs/libtclsqlite.a
creating libtclsqlite.la
(cd .libs && rm -f libtclsqlite.la && ln -s ../libtclsqlite.la libtclsqlite.la)
(Continue reading)

Permalink | Reply | 0 comments |
headers
Puneet Kishor | 2 May 2004 20:09
Favicon

[sqlite] correct syntax for CASE...

I am getting seriously hassled trying to do the following simple 
thing...

SELECT
         (CASE
           WHEN
             c.firstname ISNULL AND c.lastname ISNULL
           THEN
             'unnamed'
           ELSE
             c.firstname & ' ' & c.lastname
         END) AS fullname,
FROM contacts c

any advice on where I am being dense reading the docs?

ps. Would be nice to have examples of sytax, esp. expr syntax, in the 
docs.

;-)

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 16 comments |
headers
D. Richard Hipp | 2 May 2004 20:28

Re: [sqlite] correct syntax for CASE...

Puneet Kishor wrote:
> I am getting seriously hassled trying to do the following simple thing...
> 
> SELECT
>         (CASE
>           WHEN
>             c.firstname ISNULL AND c.lastname ISNULL
>           THEN
>             'unnamed'
>           ELSE
>             c.firstname & ' ' & c.lastname
                           ^     ^

Probably you mean the concatenate operator, which in
SQL is ||, not &.  & is not an SQL operator as far as
I am aware, though SQLite uses it to mean bit-wise AND,
just like C/C++.

>         END) AS fullname,
> FROM contacts c
> 

--

-- 
D. Richard Hipp -- drh@... -- 704.948.4565

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
(Continue reading)

Permalink | Reply | 15 comments |
headers
Puneet Kishor | 2 May 2004 20:59
Favicon

[sqlite] CONCAT in SQLite (was Re: [sqlite] correct syntax for CASE...)


On May 2, 2004, at 1:28 PM, D. Richard Hipp wrote:

> Puneet Kishor wrote:
>> I am getting seriously hassled trying to do the following simple 
>> thing...
>> SELECT
>>         (CASE
>>           WHEN
>>             c.firstname ISNULL AND c.lastname ISNULL
>>           THEN
>>             'unnamed'
>>           ELSE
>>             c.firstname & ' ' & c.lastname
>                           ^     ^
>
> Probably you mean the concatenate operator, which in
> SQL is ||, not &.  & is not an SQL operator as far as
> I am aware, though SQLite uses it to mean bit-wise AND,
> just like C/C++.
>
>>         END) AS fullname,
>> FROM contacts c
>
>

drats... concat is '+' in Javascript, '.' in Perl, '&' in Access and 
SQL Server, '||' in SQlite, and, well, 'CONCAT' in Oracle. Why can't 
the entire world just speak Hindi ;-).
(Continue reading)

Permalink | Reply | 14 comments |
headers
Puneet Kishor | 2 May 2004 21:01
Favicon

[sqlite] command history

Is there a way to enable some kind of command history in the SQLite 
shell? You know, press the up arrow to get the previous command... I 
think it is called READLINE support, no?

Its a pain in the derierre hitting the up arrow and getting all the 
^]]A kind of junk on the screen, but bad habits die hard...

Many thanks,

---------------------------------------------------------------------
To unsubscribe, e-mail: sqlite-users-unsubscribe@...
For additional commands, e-mail: sqlite-users-help@...
Permalink | Reply | 7 comments |

Gmane