Database for claiming chapters

jonvw · Post by **jonvw** » November 3rd, 2005, 2:49 pm

Hi all,

I thought I would suggest that the current method of managing books-to-be-recorded in the forums will probably not scale very well. I wondered if there might be any interest in a database-driven tool that would make it easy to view chapters-to-be-recorded and claim them.

I could put together something in ruby or python and mysql if there's a need.

Jonathan

tis · Post by **tis** » November 3rd, 2005, 3:35 pm

jonvw

Thanks for the offer! I'm certain that there would be interest.... I've been thinking the same thing myself. The forum signup works pretty well now, but if we even double the number of books being recorded at a time I think it'll get complicated.

I know Hugh is busy right now, but I'm sure he'll want to weigh in here (Kristen too, I expect). So let's use this thread to work out we would like a sign-up system to look like. I'd be thinking we need to aim to cover the following areas:

- a way for a team leader to add a new book to the system (*)
- a unique URL for each book which the team leader can post in the forum (we'll still want to use the forum to discuss the book!)
- a way to see what chapters are available
- a way to sign up to do a chapter
- a way for the team leader to 'orphan' a chapter (remove a volunteer who can't follow through) (*)
- a way to mark a chapter as completed and give a URL EITHER
- to the archive.org files (in an ideal world this would be linked into the automatic uploader we hope to produce)
- OR to a file that the team leader can pick up in order to upload to archive.org
- a way to autogenerate the catalog page(s) from the database
- later, some sort of search facility

Obviously we wouldn't need all of this in the first cut

(*) probably restricted access; we can handle this with .htaccess / .htpasswd

We have python (2.3.4), mysql (12.22) and ruby (1.8.2) on the server (thanks, nyip.net!), and a 'spare' database instance that we asked for in order to do exactly this sort of thing

.

Chris

Kristen · Post by **Kristen** » November 3rd, 2005, 6:11 pm

It would be wonderful to have a system that revolves around books from selection to cataloging. tis, your features list looks complete to me!

In my mind, the cataloging is a bigger problem than reader selection. At the moment the forums work OK for claiming chapters, and though I imagine we're going to grow like crazy, book leaders can manage to drum up readers in their own ways for a little longer.

But listeners...they need a catalogue!

So I'd propose a starting with the catalog and working back through the process to the readers selection.

I'd be happy to work with you on the UI. I'm a crap programmer, but I know how to organise information on a page!

jonvw · Post by **jonvw** » November 3rd, 2005, 7:16 pm

It seems to me that these two functions could be combined into one application (or two applications using the same database) fairly easily. Based on your feature list, tis, I see only three types of data that would require their own tables:

books - a table with bibliographic data about the book itself, as well as a URL (or URLs) to the finished project and a link to who the team leader is/was.

chapters - data pertaining to each individual chapter (who reads, how long it is, whether it's complete, when it was completed, where it can be found, etc.)

users - we'd probably want to store information about readers/team leaders here so that they can be attached quickly to a project (chapter or book).

There might be a couple of link tables in here, too, but I forsee a fairly simple schema for the database.

Did I leave anything out? What kinds of bibliographic data will be cataloged for each book?

Jonathan

Kristen · Post by **Kristen** » November 3rd, 2005, 7:50 pm

Here's what I imagine we need (based on previous discussions of and ideas bandied in the forums)

Book data:

Status (i.e. readers wanted, in progress, completed - I guess this would be a seperate table)
Link to e-text
Title
Author
Year published?
Language?
Link to Librivox forum thread

Chapter data:

Chapter #
Chapter Title (if applicible)
Anticipated Due Date (selectable by reader when chapter is claimed)
Reader
Date Read (optional)
Location of Reader (optional)
Link to chapter (on archive.org, presumably)
Run Time
File Format
File Size

tis · Post by **tis** » November 3rd, 2005, 7:52 pm

Kristen - yes, the catalog is probably the higher priority. But I like jonvw's idea of using a single database to drive the whole thing.

In fact, if we design it right, I guess the page for a book will naturally turn from (a) 'request for readers page' to (b) 'work in progress page' to (c) 'completed work' page.

(a) and (b) are very similar, especially as some chapters will be read before all have been signed up for, and also as a book could move back from (b) to (a) (if a reader drops out).

(c) should look a bit different (no reading notes, ID tags etc); and books in state (c) should also be included in the master catalog page.

As for the data required, for books: we'd want things like the URL of the gutenberg text, the description of the ID3 tags to be used, and perhaps a freeform "additional notes for readers". See the pages where people request readers for the sort of thing that would be needed. Might also be nice to include a 'has been podcasted' field (maybe on 'chapter' rather than 'book').

Maybe if Kristen made some html mockups of what it could look like that would help us to identify the database requirements?

I'm also more than happy to help; my UI skills are crap, but I can do SQL/PHP/Python/Ruby. Or I can just stand on the sidelines and throw additional feature requirements into the mix (that's actually my day job

).

Other features that occur to me:
- email team leader when someone signs up
- email forum moderator when all chapters are signed up for (so they can move the topic into 'readers found')
- likewise when the last chapter has been read (so they can move it to 'completed')
- automatically post a message to the blog when a book is uploaded to archive and generally available (this might be best done by the uploader script - see another thread)

Kristen · Post by **Kristen** » November 3rd, 2005, 8:08 pm

tis wrote: stand on the sidelines and throw additional feature requirements into the mix (that's actually my day job )

And you're good at it. Look at you go!!

Post by **hugh** » November 3rd, 2005, 9:14 pm

wow. cool. that all looks amazing.

one thing to note on the chapter sign-up etc, I think librivox is working so well right now because of the interaction on the forum. we need to be careful not to put too many tech blocks between interaction of volunteers -- even if that means the process is less efficient. meaning, I wonder if the best thing is to have the catalog (as kristen says, more important now) working smoothly - listing all the important info, status of books, meta data, tagging? ranking? rss-feeds for "hugh's librivox favourites?" ... etc... but to leave chap sign-up & volunteer managemtn in the hands of team leaders in the forum. that way the volunteers are committing to a person, rather than an interface & initial contact and follow-up will be more personal.

just some thoughts, to be considered.

jonvw · Post by **jonvw** » November 3rd, 2005, 11:19 pm

hugh wrote: I wonder if the best thing is to have the catalog (as kristen says, more important now) working smoothly - listing all the important info, status of books, meta data, tagging? ranking? rss-feeds for "hugh's librivox favourites?" ... etc... but to leave chap sign-up & volunteer managemtn in the hands of team leaders in the forum. that way the volunteers are committing to a person, rather than an interface & initial contact and follow-up will be more personal.

What if the database/catalogue could be used for chapter/book management (i.e. the list of completed, taken, and available chapters would display in the catalogue, and this list could be edited by team leaders), but volunteers would still need to post a message in the forum to sign up? This would give people a handy place to look to see where there are available chapters, but it would keep the social/interactive element in place. We could even build a query into the catalogue to list all chapters needing readers, regardless of the book.

tis · Post by **tis** » November 4th, 2005, 1:59 am

Perhaps a good solution would be to only allow the book leader to actually use the database-sign-up page. That way, when someone volunteers, the book leader would enter their details into the sign up form.

Then we can have a 'what chapters are looking for readers?' web page, as jonvw suggests, which would also provide what jeanosullivan was looking for (see the 'Orphaned Chapters' thread). The list of chapters looking for readers could link back into the relevant forum thread.

So here's the use case:

Volunteer looks at 'chapters needing readers' page
Volunteer selects a chapter (or book) that they would like to read
The link takes them to the forum
Volunteer signs up in the forum
Team leader sees that they have volunteered and enters the details into the database via a 'book leader' page

Post by **hugh** » November 4th, 2005, 6:46 am

sounding gooder & gooder.

jonvw · Post by **jonvw** » November 4th, 2005, 8:19 am

tis,

The use case you describe was basically what I had in mind, too. It would make it easy to see at a glance what's been done and what needs to be done, but it still encourages community participation and makes sure that all changes are authorized by a trusted individual.

I'd be happy to do the actual coding. I couldn't sleep last night, so I cobbled together a prototype in Ruby on Rails based on some of the initial feature requests. I've actually got something like a very rudimentary catalog running on my laptop right now.

If it's feasible, I'd vote to do this on Ruby on Rails for two reasons: (a) I like ruby and (b) I have already written a library catalog for a customer of mine using Ruby on Rails, and I think a lot of the code from that project could be duplicated here.

Jonathan

tis · Post by **tis** » November 4th, 2005, 5:31 pm

jonvw,

RoR is fine; I'd quite like to learn.... it's really cool at the moment

Aside from ruby (which we have), do we need to install anything else on the server (it's a pretty standard apache setup)?

If you have a tarball you can email me I can set it up in a secure area of librivox.org and see if it works in our environment.

jonvw · Post by **jonvw** » November 4th, 2005, 6:38 pm

tis,

I can send you a tarball of what I have, but I'll I'd like a couple of days to get it ready. What I have right now is running in PostgreSQL, so I'll need to make a few changes to get it working on mySQL, as well as making a few tweaks to the models.

If you don't have rails installed, that can be installed with rubygems. Instructions can be found at http://download.rubyonrails.com/ . This will come with a pure ruby database driver for mySQL, which should be adequate for testing purposes.

The only other thing I can think of that will probably be needed is mod_fastcgi for Apache. This is not strictly necessary -- mod_cgi will work, but it doesn't really scale well. I've experimented with using Apache and mod_fastcgi, but I've had some trouble getting it to work correctly, and I haven't used it in a production environment. This part is probably the trickiest bit, but I'm pretty confident that we can get it working correctly.

I'll work on the prototype this weekend and send you a tarball once I am reasonably sure that it will run in your environment.

BTW -- if you need to reach me directly, it's jonvw AT jonvw DOT com or AIM jonvw

Jonathan

kayray · Post by **kayray** » November 6th, 2005, 3:48 pm

hey all,

hugh likes the look of this page:

http://librivox.org/call-of-the-wild-by-jack-london/

for the eventual standard look-and-feel of the catalog pages. i'll be fixing up the current catalog to match that page, and thought i should mention it here too, so you guys know what to shoot for.

kara