Last Modified 2008-04-03 by Chuck Hagenbuch

About Groo

Groo was written in PHP 5.1 with Horde 3.1.

The archived CVS repository can be downloaded from ftp://ftp.horde.org/pub/archive/.

High-level use cases

At a minimum, Groo should allow a user to:

  • catalog her books, by typing ISBNs or scanning barcodes
    • invite friends who will have access to her books
    • ask to borrow a book
    • approve lending of a book

After the basics are done, we also want our user to be able to:

  • search, tag, comment, recommend and rate books in her collective catalog
    • get e-mail remembering to return a book, or to ask for a book which is overdue
    • arrange the exchange of books, either setting up meetings or scheduling drops

Basic entities

The main entities in the Groo system are Users, Books and Items. Users are basically managed and stored by the Horde infrastructure.

Books vs. Items

What we call a Book in Groo is not a physical object, but rather a certain edition of a work, such as "PHP in a Nutshell, ISBN: 0596100671". An Item is a particular physical instance or exemplar of a Book, such as my own copy of "PHP in a Nutshell". Eventually Items will also refer to DVDs, LPs, CDs.

So Groo has a public database of Books, and private collections of Items, which are the user's book collections. The collections are implemented as Horde shares.

Application components


Each record represents a book in print, usually identified by an ISBN. Since older books don't have an ISBN, the primary key is and internal sequence. When the ISBN is NULL the title must not be NULL (but this is not enforced at this time).

Books have a one-to-many relationship with Items (see groo_items groo_items).

Books have a many-to-many relationship to Creators (see groo_creators and groo_l_book_creator).

The title field is indexed for full text search.

CREATE TABLE groo_books (


    isbn13 CHAR(14),        -- ISBN13 + 1 DIGIT = GTIN

    title VARCHAR(255),     -- Either isbn13 or title must not be null

    title_sort VARCHAR(255),-- Title for sorting purposes (article at end)

    edition VARCHAR(63),    -- Ex: 1st; extended ultimate director's cut edition

    publisher VARCHAR(63),

    issued CHAR(10),        -- publication date in partial ISO-8601 format Ex: 1999-12

    metadata_status VARCHAR(32),           -- status of the data in this record

    metadata_source VARCHAR(32) NOT NULL,  -- source of the data in this record

    metadata_modified_dt TIMESTAMP,

    created_dt DATETIME NOT NULL,


    PRIMARY KEY (book_id),

    UNIQUE (isbn13),

    FULLTEXT (title)



The name field is indexed for full text search.

CREATE TABLE groo_creators (

    creator_id INT UNSIGNED NOT NULL,

    name VARCHAR(255) NOT NULL,

    metadata_source VARCHAR(32) NOT NULL,

    metadata_modified_dt TIMESTAMP,

    created_dt DATETIME NOT NULL,


    PRIMARY KEY (creator_id),

    FULLTEXT (name)



This table establishes the many-to-many relationship between Books and Creators

CREATE TABLE groo_l_book_creator (


    creator_id INT UNSIGNED NOT NULL,

    role CHAR(16),                    -- author, translator, editor, illustrator etc.

    citation_order SMALLINT UNSIGNED, -- the order in which the creator name appears


    PRIMARY KEY (book_id, creator_id)



This table structured was borrowed from Mnemo (the mnemo_memos table). The only change, besides the fieldnames, was the addition of the item_book_id field, which is a foreign key linking to the groo_books table.

Both item_id and item_uid are randomly generated unique strings. A recent discussion on the dev list concluded with Chuck saying that id fields (such as item_id) in Horde apps should be converted back to integers. Item_uid will continue as is, because it is used for syncing to external devices. I don't know wether Groo items could be usefully synced to a cell phone or PDA, but for the time being the field stays.

CREATE TABLE groo_items (

    item_owner      VARCHAR(255) NOT NULL, -- id of the Horde share which owns this item

    item_id         VARCHAR(32) NOT NULL,  -- id of the item (a random string)

    item_uid        VARCHAR(255) NOT NULL, -- uid of the item, for syncing to external devices

    item_desc       VARCHAR(255) NOT NULL, -- short description of the item

    item_body       TEXT,                  -- user comments on the item

    item_book_id    INT UNSIGNED NOT NULL, -- the book of which this item is a copy

    item_category   VARCHAR(80),           -- one of the user defined categories

    item_private    SMALLINT NOT NULL DEFAULT 0, -- boolean?

    item_modified   TIMESTAMP,             -- used to sort items by date of change


    PRIMARY KEY (item_owner, item_id)



This table is used by the Fetch.php module to record transactions with the remote metadata sources.

Calling ./Fetch.php --fetch-untitled via the command line adds a record in this table for each Book without a title added since the last fetch operation.

Fetch.php then passes the list of queries to the fetch drivers (currently only Amazon.php is implemented). The fetch drivers should be prioritized, that is, the first one invoked should be the one most likely to have the metadata. For US books, Amazon.com is a good first choice because it has book covers (the Library of Congress catalog is more complete but does not have the convers).

CREATE TABLE groo_remote_queries (


    metadata_source VARCHAR(32) NOT NULL,   -- eg: amazon.com, bn.br etc.

    param_name VARCHAR(32) NOT NULL,        -- eg: asin, isbn etc.

    param_value VARCHAR(255) NOT NULL,      -- eg: 0486273474

    last_attempt_num INT UNSIGNED DEFAULT 0,-- fetch attempts counter

    last_attempt_dt DATETIME,

    query_status CHAR(32),                  -- interna status string, eg: SUCCESS, NOT_FOUND

    message TEXT,                           -- the actual message returned by the remote system

    record_modified_dt TIMESTAMP,

    created_dt DATETIME NOT NULL,


    PRIMARY KEY (query_id),

    UNIQUE (metadata_source, param_name, param_value)



This is intended to control the lease of items between users. No code dealing with this has been implemented yet. The plan is to try to leverage the Nag (tasks) functionality do implement this, so some of the fields below may turn out to be redundant.

CREATE TABLE groo_item_leases (


    item_id INT UNSIGNED NOT NULL,  -- leased item id

    requester_uid VARCHAR(255),     -- who asked to borrow

    lease_status CHAR(32),          -- internal status string, eg: PENDING, OVERDUE

    return_condition TEXT,          -- optional owner comment on the state of the returned item

    start_dt DATETIME,              -- start of the lease period

    end_dt DATETIME,                -- end of the lease period

    returned_dt DATETIME,           -- date when the item was returned to the owner

    record_modified_dt TIMESTAMP,

    created_dt DATETIME NOT NULL,


    PRIMARY KEY (lease_id)


The Book Commons

Is the collection of all books which freely circulate among people. Most of them are unaccounted for.

I may borrow a book from someone, forget to return it, and then pass it on to somebody else.

Liberated books

Groo users will be able to "liberate" some of their books, which then become available to all users as part of the book commons. A liberated book can be held by anyone as long as no one else wants to borrow it. If you are holding a liberated book and another person asks for it, you are required to pass it on within a certain period.