use leasedb (not crawler) to figure out how many shares you have and how many bytes

[zooko]

In current trunk, there is a "BucketCountingCrawler" whose job it is to count up how many shares are stored.

I propose that this be replaced by using the leasedb to count files (a simple SQL COUNT query!), and at the same time to extend the storage server's abilities by letting it be able to add up the aggregate sizes of things as well as their number.

This is part of an "overarching ticket" to eliminate most uses of crawler — ticket #1834.

[zooko]

The part about reporting total space usage would be very useful for customers of LeastAuthority.com (who pay per byte), among others.

[davidsarah]

+1.

[zooko]

Using leasedb this way would facilitate solving #671 — bring back sizelimit (i.e. max consumed, not min free).

[zooko]

Using leasedb this way would facilitate solving #940.

[davidsarah]

The most basic form of the 'total used space' query is

SELECT SUM(`used_space`) FROM `shares`

How much account-specific information should we add? At the moment, there are only two accounts -- anonymous and starter -- but that is already enough to introduce the complication that more than one account can hold a lease on the same share, so the query above is not equivalent to

SELECT SUM(`used_space`) FROM `shares` s JOIN `leases` l
       ON (s.`storage_index` = l.`storage_index` AND s.`shnum` = l.`shnum`)

since that can count space for a share more than once.

[davidsarah]

This query solves the above problem, giving the total number of leased shares and the total space used by leased shares:

SELECT COUNT(*), SUM(`used_space`)
  FROM (SELECT `used_space`
          FROM `shares` s JOIN `leases` l
          ON (s.`storage_index` = l.`storage_index` AND s.`shnum` = l.`shnum`)
          GROUP BY s.`storage_index`, s.`shnum`)

(Any WHERE clause can be added to the inner SELECT to pick leases that satisfy certain criteria.)

And this gives the number of shares and total used space leased by each account, sorted beginning with the one that is using most space:

SELECT `account_id`, COUNT(*), SUM(`used_space`)
  FROM `leases` l LEFT JOIN `shares` s
  ON (l.`storage_index` = s.`storage_index` AND l.`shnum` = s.`shnum`)
  GROUP BY `account_id` ORDER BY SUM(`used_space`) DESC

[zooko]

After talking with markberger today, I realized that #1818 is the ticket to merge leasedb into trunk, and #1819 is the superceding ticket to merge leasedb+cloud-backend into trunk.