Taking a simple but complete schema, outlined below, and some sample data, also supplied, use the Mother Python ORM toolkit to perform operations on the data:

http://www.dbmother.org/

It is suggested, but not required, that sqlite be used as the database for the task: its driver is included with Python 2.5+ and all the ORMs support it out of the box.

Schema:

This suggested schema is the basis for a book review site. The focus of the site is the book, but within the book article you can link to other books by the same author, other books in the same series, or other books in the same genres. The schema is very slightly artificial as it tries to include 1:m, m:n, optional 1:1 and extended m:n relationships.

The key entity is the BOOK. It has a title which is unique and is limited to 100 characters and review notes which may be as long as you like. It must be written by an author who may have written other books. It may occupy a specific place in a series. It may belong to one or more genres, of which one is considered the principal genre. The book has a type -- Fiction or Nonfiction. If the book is Nonfiction, it is linked to a set of nonfiction data.

The AUTHOR has a unique name and, optionally, biographical notes. The author must have written at least one book in the database and may have written more.

The SERIES has a name and, optionally, overall review notes for the series. The series must contain at least one book in the database and may contain more.

The GENRE has a name and a brief description. A genre may contain no books, one or some.

The NONFICTION_DATA includes the publisher, publication date and number of pages of the book. Nonfiction data refers to only one book.

Task steps and completion criteria:

The first part of the task would be to use the ORM to create the schema according to its own techniques. Then the data should be loaded from the .csv files supplied.

The second part of the task would involve running a series of sample queries, exercising common data needs. A set of queries to use is listed below.

Completion criteria: upload the source code used for this ORM and the output from the query runs. Include a brief note indicating where the ORM appeared to facilitate a particular structure or query and where it seemed to hamper.

Queries:

Formulate and then print the output of these queries.

• List all books (title only) by the author John Buchan
• List all books for which Adventure is a genre
• List all authors with only one book in the database
• List the author and title of all books which don't form part of a series
• List the title, author, publisher and publication date for all books whose author's surname begins with 'B'. (For fiction books, the last two will be empty).
• List the top 5 most prolific authors
• List any genre which doesn't include any books
• Create a new genre "Python" and apply it to all books with the word "dark" in their title.
• Add a new non-fiction book "The Python Programming Language", published by "Python Press" in 2007.
• Update the notes on the book "Scorpia" to add the phrase: "I loved this book".

Data:

A data file has been made available in the ghop-python google group's files section at http://ghop-python.googlegroups.com/web/book-schema.zip. Although the data set comes from a real-world book review site (goodtoread.org) it has been artificially adjusted to suit the task. In particular, two non-fiction books were added with their additional data to be able to have a 1:1 optional relationship. And to extend the m:n between books and genres, the first listed of the genres should be considered the principal genre for a book.

I claim this task.

Good luck. Feel free to post if there's anything unclear.

This task is due January 23, 2008 17:20:00 UTC

I'm currently struggling with Mother's ability to pick up on many-to-many relationships. According to the manual1, if you create an intermediary table (like civilizations in the manual or, here in my SQL2, genres) Mother will automatically pick up on it once you run mothermapper. I've tried my best to follow the same SQL structure as the tutorial, Lifeforms and Planets through a Civilizations intermediate paralleling Genre and Book through a Genres intermediate.

As far as I can tell, though, it's not doing it since when I run initManyManager I get the error "mothers.py: The initManyManager() methods are not tested deeply: if You encounter bugs, don't panic: please, signal them." and then "speaker.py: SQL logic error or missing database."

Should I proceed without using initManyManager, manually adding the many-to-many relationships myself?

Here's the output from mothermapper if it's any help. I think relations should be 3 or 4, but 2 definitely seems low. Then again, the documentation never mentions how mothermapper calculates these numbers. The output file, mother.map, is in binary and doesn't seem to be much help.

http://pylonshq.com/pasties/674

I mistyped into the last pastie (#674): "-c dbmother.conf -" should read "-c dbmother.conf -s".

Strictly, that message isn't not a bug; isn't an info. Do you have any output which suggests that the code isn't actually working? You've pasted the commands and table defs but not the code at that point.

Apologies, that was an error on my part. I've switched to initChildManager per the manual's recommendation and it now works cheerfully.

I have an API question, though. According to the Mother Manual and the more thorough docstrings for mothers.py, there are only five methods that initChildManager adds to a class declaration: insert, get, deleteMultiple, updateMultiple, getMultiple. Per the manual, in order to add--say--a book with an author (where the book is the child and the author is parent) you would call (where = Book)

author.insertBook({'title': ..., ...}).

This would simultaneously insert the book and add the correct foreign key for the author.

The problem comes, however, when the book is a children of two parents: in this case, author and series. I can't call series.insertChild({...}) since there's already an existing book entry in the Book table. I also can't tell Mother to simply add the correct foreign key for series without insertion: none of the five given methods do that as far as I can tell. (I don't think any method for any init*Manager does that.) Right now, I'm just assigning the foreign key manually by querying the Series and Author table ahead of time.

Is this enough to satisfy the task?

Certainly if the ORM has this limitation then working round is perfectly acceptable for the task: part of the idea of these ORM tasks is to highlight differences (strengths, weaknesses etc.) of the different ORMs. If Mother doesn't allow for multiple foreign keys then that should be noted as part of the final write-up.

I'm unfamiliar with Mother myself so I can only echo your point of view based on the docs. If you haven't already, drop a line to the author of Mother per the website to see if he or she has any comments or suggestions. Or post to c.l.py (python-list@python.org) to see if anyone there has suggestions. This is perfectly within the bounds of the GHOP: open source works that way.

Thanks. I've decided to stick with manual foreign keys for the sake of time.

Here's my submission. Since Mother requires so many files, I've put them in a ZIP folder.

• book_review.sqlite is the final database that (hopefully) contains all the changes.
• clean_book_review.sqlite is the initial database constructed from mothermapper using the model.sql file. You can arrive at the final book_review.sqlite by overwriting the provided book_review.sqlite file with clean_book_review.sqlite and then executing import.py followed by output.py.
• import.py puts the CSV data into the SQLite database.
• output.py spits out the information needed for the task as well as the changes outlined in the task.
• output.txt is the output of output.py. It should produce the same output as check.py (the test file attached to the original ORM task) with different formatting and some additional information that check.py didn't produce.
• model.py is some repetitive code Mother needs to map tables to classes.
• mother.map was generated by mothermapper.
• dbmother.conf is a file used by Mother and mothermapper.
• data/ is the folder that contains the CSV.

Here's the blurb for the completion criteria:

Mother is an innovative ORM that works out relationships from SQL rather than a DSL or Python class attributes. However, only recognizing simple many-to-one relationships hampered the task here as the book entity was a child of author and series. In addition, the lack of a thorough querying API led me to resort to raw SQL at points when the MotherBox and MotherFusion classes weren't enough. I also thought the MotherSession API could've been better as currently it requires the user to pass a MotherSession instance to every MotherBox, DbMother, etc.

I'll get to this as soon as I can, hopefully this morning. On the surface it looks good, but I need to run the code... and at the moment I can't get hold of the apsw module!

OK, I'm happy with this one. Must admit, Mother is a bit surprising but I haven't really had a chance to get into it myself. Did you try emailing the author to see if he had any suggestions? I'd be interested to get his input. Anyhow you've done what the task wanted and I'm happy to close it.