Will rootpage number ever change?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Will rootpage number ever change?

Jose Isaias Cabrera-4

Greetings.

When I run this command,

sqlite> SELECT * FROM sqlite_master WHERE type='table' AND name='PMOTitles';
type|name|tbl_name|rootpage|sql
table|PMOTitles|PMOTitles|560|CREATE TABLE PMOTitles
    (
      TitleKey PRIMARY KEY,
      Titles
    )
sqlite>

I see that there is a rootpage assignment for each table.  And on the help, [1], sixth paragraph, it says, "The sqlite_master.rootpage column stores the page number of the root b-tree page for tables and indexes." I have no idea what that means, but the question I have is, will that number ever change?  Thanks.

josé

[1] https://sqlite.org/fileformat.html
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Will rootpage number ever change?

Richard Hipp-3
On 8/15/19, Jose Isaias Cabrera <[hidden email]> wrote:
> I have no idea
> what that means, but the question I have is, will that number ever change?
> Thanks.

Yes.  Root page numbers will change, for example when you run VACUUM.

An SQLite database consists of a "forest" of b-trees. Each table and
each index is a separate b-tree. Each b-tree has a root page.  All
other pages of the b-tree are descended from the root page.  The
sqlite_master table identifies the page number of the root of each
b-tree.  The sqlite_master table itself is also a b-tree which is
always rooted on page 1.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Will rootpage number ever change?

Jose Isaias Cabrera-4
Richard Hipp, on Thursday, August 15, 2019 09:45 AM, wrote...
>
> On 8/15/19, Jose Isaias Cabrera, on
> > I have no idea
> > what that means, but the question I have is, will that number ever change?
> > Thanks.
>
> Yes.  Root page numbers will change, for example when you run VACUUM.

Yep, you're right.  I just ran vacuum, and it jumped from 560 to 11.  Interesting.

> An SQLite database consists of a "forest" of b-trees. Each table and
> each index is a separate b-tree. Each b-tree has a root page.  All
> other pages of the b-tree are descended from the root page.  The
> sqlite_master table identifies the page number of the root of each
> b-tree.  The sqlite_master table itself is also a b-tree which is
> always rooted on page 1.

Thanks for this.  What I am looking for is an unique ID that I can use to call that table.  Does that exists in the internal of SQLite? I know I can create a table myself keep track of them myself, but I am trying to see if it exists. ;-)

One last question... Take a look at this command...

sqlite> SELECT * FROM sqlite_master WHERE type='table' AND name='PMOTitles';
type|name|tbl_name|rootpage|sql
table|PMOTitles|PMOTitles|11|CREATE TABLE PMOTitles
    (
      TitleKey PRIMARY KEY,
      Titles
    )

What is the difference in the name and tbl_name headers, above?  Can I rename any of those two without damaging the DB? Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Will rootpage number ever change?

Simon Slavin-3
On 15 Aug 2019, at 3:01pm, Jose Isaias Cabrera <[hidden email]> wrote:

> sqlite> SELECT * FROM sqlite_master WHERE type='table' AND name='PMOTitles';
> type|name|tbl_name|rootpage|sql
> table|PMOTitles|PMOTitles|11|CREATE TABLE PMOTitles
>    (
>      TitleKey PRIMARY KEY,
>      Titles
>    )
>
> What is the difference in the name and tbl_name headers, above?

The table called "sqlite_master" has lots of things in it, not only tables but also, for example, indexes.  Your SELECT picks out only tables, so all the rows returned by that SELECT will have the same thing in "name" and "tbl_name".  But for rows which are indexes, "name" and "tbl_name" are different.

> Can I rename any of those two without damaging the DB? Thanks.

I'm not sure I understand that question.  If you're asking whether you can rename a column in sqlite_master, then no.  Leave it alone.  Having sqlite_master working correctly is extremely important !

If you're asking whether you can make changes to the data in sqlite_master, then the answer is "Not unless you know what you're doing.".  A VIEW, or an INDEX, can refer to another TABLE by name.  If you rename a table, things can stop working.

If you're careful to restore integrity and get everything right, you can make changes to "sqlite_master", and then tell SQLite to rescan it so that it sees your changes.  To do this, use

<https://sqlite.org/pragma.html#pragma_writable_schema>

I seem to remember that after you've made changes to "sqlite_master" there's a command you are meant to use to make SQLite reread it, but I don't remember what command that is right now, so perhaps you'd better just close and reopen the connection.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Will rootpage number ever change?

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

On Thursday, 15 August, 2019 08:02, Jose Isaias Cabrera <[hidden email]> wrote:

>Thanks for this.  What I am looking for is an unique ID that I can
>use to call that table.  Does that exists in the internal of SQLite?
>I know I can create a table myself keep track of them myself, but I
>am trying to see if it exists. ;-)

sqlite_master records the database schema (that is, the format of persistent objects) by storing the SQL used to create them.

There are a number of constraints enforced internally for the uniqueness of the object name, however, the combination of (type, name) will always be unique even if the same name cannot be used for multiple types (for example, you cannot have the same name for both a view and a table in the same database, but you can have a before or after trigger with the same name as a table, but you cannot have an instead of trigger with the same name as a table).

There is no pseudokey (surrogate key) such as an explicit rowid, and there are no indexes on sqlite_master (nor can you create one).

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users