[tahoe-lafs-trac-stream] [tahoe-lafs] #1836: use leasedb (not crawler) to figure out how many shares you have and how many bytes

[tahoe-lafs]

#1836: use leasedb (not crawler) to figure out how many shares you have and how
many bytes
-------------------------+-------------------------------------------------
     Reporter:  zooko    |      Owner:  davidsarah
         Type:  defect   |     Status:  assigned
     Priority:  normal   |  Milestone:  undecided
    Component:  code-    |    Version:  1.9.2
  storage                |   Keywords:  leases garbage-collection
   Resolution:           |  accounting
Launchpad Bug:           |
-------------------------+-------------------------------------------------

Comment (by 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
 }}}

-- 
Ticket URL: <https://tahoe-lafs.org/trac/tahoe-lafs/ticket/1836#comment:9>
tahoe-lafs <https://tahoe-lafs.org>
secure decentralized storage