Counting bookmarks in folders in Fx3

[David McRitchie]

For Fx3 which uses sqlite database, does anyone have a quick method of
counting bookmarks and folders in a folder, and recursively counting all
items in subfolders as well. Actually would also want to know how many of
them had keyword shortcuts.

--
David McRitchie,

[captjlddavis]

David,

Sqlite Manager gives a lot of db information.
Select Browse and Search - Look at the bottom of the right panel.

Appears to give total count of "records".

Select "Keywords" in left panel - right panel bottom shows number
(count) of records.

Does this help any ?

regards:captjlddavis

[David McRitchie]

"captjlddavis" ...

> On 8/18/2008 09:33, David McRitchie wrote:
>> For Fx3 which uses sqlite database, does anyone have a quick method of
>> counting bookmarks and folders in a folder, and recursively counting all
>> items in subfolders as well. Actually would also want to know how many of
>> them had keyword shortcuts.
>

> Sqlite Manager gives a lot of db information.
> Select Browse and Search - Look at the bottom of the right panel.
>
> Appears to give total count of "records".
>
> Select "Keywords" in left panel - right panel bottom shows number
> (count) of records.
>
> Does this help any ?

Hi captjlddavis,
Yep tells me I have 2741 bookmarks and I have 501 bookmarks.
How would I tell how many bookmarks in a folder (and subfolders)
have keywords -- if you know. Is there a cookbook approach using
this extension I never learned SQL queries.

[captjlddavis]

David,
Sorry that I can't be onyhelp - I also no nothing about sqlite db.
All I can suggest is play with the thing and see if you come up with
anything.

regards:captjlddavis

[Roland de Ruiter]

For an introduction to SQL, read
<http://www.w3schools.com/sql/default.asp>, in particular the SELECT
statement and WHERE clause (forget about INSERT, UPDATE and DELETE for
now).

By "keyword" do you mean (1) a keyword defined to do a smart search
(<http://support.mozilla.com/en-US/kb/Smart+keywords>) or (2) a tag (for
labeling bookmarks)?

The keywords for smart searches seem (*) to be stored in the table
"moz_keyword". To show which smart searches you have defined you can run
the following query (paste text into the "Execute SQL" tab of the SQLite
Manager and hit the "Run SQL" button)

SELECT kw.keyword, plc.url, bm.*
FROM moz_bookmarks AS bm, moz_keywords AS kw, moz_places AS plc
WHERE bm.keyword_id = kw.id
AND bm.fk = plc.id

Tags on the other hand, are stored in the "moz_bookmarks" table, along
with bookmarks, bookmark folders and other stuff. It's quite a complex
table; I haven't discovered yet how data in this table relate to each
other and to other tables.

However, to find the tags you have defined, execute the following query

SELECT tg.id, tg.title
FROM moz_bookmarks AS tg, moz_bookmarks_roots AS rt
WHERE tg.parent = rt.folder_id
AND rt.root_name = 'tags'

The actual URL's of a bookmark aren't stored in the "moz_bookmarks"
table, but in the "moz_places" table. The "fk" column of a row in the
"moz_bookmarks" table relates to the "id" column of the "moz_places"
table. To view the urls associated with the entries (rows) in the
"moz_bookmarks" table, execute this:

SELECT plc.url, bm.*
FROM moz_bookmarks AS bm, moz_places AS plc
WHERE bm.fk = plc.id
ORDER BY plc.url

You'll probably see that the same URL appears more than once. This is
probably because one row describes the actual bookmark (as defined by
you) while another row defines a tag for such the same URL.

To relate tags to actual URL's, use this:

SELECT tg.title AS 'tag', plc.url, bm.*
FROM moz_bookmarks AS tg, moz_bookmarks_roots AS rt, moz_bookmarks AS
bm, moz_places AS plc
WHERE tg.parent = rt.folder_id
AND rt.root_name = 'tags'
AND tg.id = bm.parent
AND bm.fk = plc.id
ORDER BY tg.title, plc.url

HTH ...

(*) Until now I haven't found an "official" document describing the
tables of the "places.sqlite" database. So what you read above is some
"intelligent" guessing on my account.
--
Regards,

Roland

[David McRitchie]

"Roland de Ruiter" ...

> On 18-8-2008 20:48, David McRitchie wrote:
>> Is there a cookbook approach using
>> this extension I never learned SQL queries.
>>
> For an introduction to SQL, read
> <http://www.w3schools.com/sql/default.asp>, in particular the SELECT
> statement and WHERE clause (forget about INSERT, UPDATE and DELETE for now).

> The keywords for smart searches seem (*) to be stored in the table
> "moz_keyword".

Ho Roland,
Yes those are the one the ones (I don't use Tags), the references and examples
should certainly get me started.