OL: some useful queries

Open Library uses Postgres as database.
All the OL’s entities are stored as things in the thing table.
Every raw contains:

 id | key | type | latest_revision | created | last_modified 
----+-----+------+-----------------+---------+---------------

Some useful types are: /type/author /type/work /type/edition /type/user

openlibrary=# SELECT * FROM thing WHERE key='/type/author' OR key='/type/edition' OR key='/type/work' OR key='/type/user';
 
    id    |      key      | type | latest_revision |          created           |       last_modified        
----------+---------------+------+-----------------+----------------------------+----------------------------
 17872418 | /type/work    |    1 |              14 | 2008-08-18 22:51:38.685066 | 2010-08-09 23:37:25.678493
       22 | /type/user    |    1 |               5 | 2008-03-19 16:44:20.354477 | 2009-03-16 06:21:53.030443
       52 | /type/edition |    1 |              33 | 2008-03-19 16:44:24.216334 | 2009-09-22 10:44:06.178888
       58 | /type/author  |    1 |              11 | 2008-03-19 16:44:24.216334 | 2009-06-29 12:35:31.346997
  • Count the authors:
    openlibrary=# SELECT count(*) as count FROM thing WHERE type='58';
  • Count the works:
    openlibrary=# SELECT count(*) as count FROM thing WHERE type='17872418';
  • Count the editions:
    openlibrary=# SELECT count(*) as count FROM thing WHERE type='52';
  • Count the users:
    openlibrary=# SELECT count(*) as count FROM thing WHERE type='22';