Inventory tracking database

Introduction

In light of my failure with FGDB and the recent decision to pare down the number of different interpreters running on the freegeek.org server, I'm trying to work out how we may our existing infrastructure to construct an inventory system.

What we have already:

1. Publicly-accessible server
2. MySQL database
3. httpd with mod_php
4. Web framework (MediaWiki)

Overview

I intend to implement the new system as a MySQL database on dynamo. We can create arbitrary clients for this database with ODBC. Additionally I hope to make inventory reports accessible on this wiki via Special: pages. http://www.mediawiki.org/wiki/Writing_a_new_special_page

Special pages are technically not editable, as they are dynamically constructed from a database call. Unlike with normal wiki pages, Special pages can be read-restricted as well, in case we feel the need to keep our inventory secret for some reason.

I propose to build an inventory database and use it to track our stuff. I further propose to use Wikimedia's user system to track our volunteers. This will mean, among other things, that every volunteer must have a wiki account- which in turn will require a full-time-ish wiki admin. Also I hope to implement a Kerberos or similar SSO system so that FGSEA volunteers' logins work on all FGSEA areas (save dynamo).

Entries to the inventory database should be fairly automated, as follows: we'll build a custom bootable volume (cd, harddisk, usb stick, whatever) using a recent Ubuntu kernel and a minimal userland which, among other things will run lshw at startup. This script will then, if the machine is connected to a network, upload the resulting XML file, either to freegeek.org or to a local server, via curl. Then the XML file can be parsed into a data table by MySQL: http://dev.mysql.com/doc/refman/5.5/en/load-xml.html but this is a separate step that can be performed in a batch, possibly a cron job if we can come up with some method of validating the XML. Does that last matter? I'm not sure.

Tools and components:

client end:

1. debootstrap - for building boot images
2. lshw
3. curl, wget, nc or similar

server end: This bit is already set up :)

Project parts and necessary infrastructure

Chunks of the project

This project can be conceptually broken up into a number of semi-independent pieces. That is, the person working on one piece shouldn't have to know much of anything specific about the other pieces. I will list them out here and if I can get people to adopt chunks, then I will put their names on each section so folks know who to contact for help.

Project Management

I'm afraid this will probably have to be me, User:koanhead, unless someone else wants to do it. It shouldn't be to difficult to find someone more experienced at management than I am.

See the FGSEA github here: https://github.com/organizations/freegeek-seattle

Rolling up a custom boot volume

This is the part I'm currently working on. See Custom boot volume for more details.

This boot volume will need a kernel that has modules enabled and that probes hardware on startup. In fact a vanilla Ubuntu distro would probably work, with a few simple additions:

1. It needs lshw and curl installed
2. It needs a startup script in rc.S or simlar that runs lshw -xml, captures the output, and then sends that data to the database.

.

Setting up XML -> MySQL parser

This will involve figuring out how to make lshw's tree-structured XML output fit into MySQL, and designing the tables. The actual parsing can be done by a MySQL function, the prep work is pretty much all of it.

This prep work involves translating the XML Schema file in lshw source (doc/lshw.xsd) into a usable, normalized MySQL database. If we can find an automated tool to do this, great. It might need to be done by hand.

This role could also make the "People" tables that relate information on volunteers, vendors, etc to inventory and Wikimedia user data.

Building the Wikimedia extension to display these database reports as Special: pages

Again, most of the work here is done for you, especially if you already are a Wikimedia hacker. If you are not, then there are a few special objects and data structures to know about: http://www.mediawiki.org/wiki/Manual:Database_access http://www.mediawiki.org/wiki/Writing_a_new_special_page This will require basic object-oriented PHP knowledge, and access to whichever group owns the Wikimedia code on the server (test server, that is).

There might be more stuff that I've forgotten about. Hopefully someone will come along and add / change things.

Concerns:

1. Doing automatic uploads with hard-coded passwords to our live webserver is obviously a Bad Idea. It's probably advisable to set up a secondary, local server to receive the files and then upload them in a batch. The local server can even do the XML->MySQL conversion, I suppose.
2. Converting XML to MySQL tables is not a trivial task. lshw's XML output may not be suited to it. Not knowing how the import proceeds yet I'm not sure how will lshw's varying output will work with SQL tables' fixed number of fields. Here is an idea of what the XML output looks like. http://ezix.org/project/wiki/HardwareLiSter#Attributes has more information on how lshw works. The problem seems generalizable to: how to collapse a tree into one or more RDB tables?
3. This is a HUGE amount of work for one guy to do. I estimate this is a 100+ man-hour job for someone with my level of expertise. I don't have 100 hours to devote to this. A little help here?
4. Those working on this project, or at least whomever plugs in the code to make the Special: pages, will need Developer status on the wiki. So far only Mark and Will have the power to grant this. The need is not immediate- a lot of groundwork needs to be laid first.