You think we just work at a comic book store for our folks, huh?

I'm only a minimal MySQL user, but I've got a problem with a large table full of data and I'm hoping for tips on how to improve it.

Right now I have a table which looks like this:

CREATE TABLE `books` (
  `id` int(11) NOT NULL auto_increment,
  `owner` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  ....
  PRIMARY KEY  (`id`),
  KEY( `owner`)
)  ;

This allows me to lookup all the BOOKS a USER has - because the user table has an ID and the books table has an owner attribute.

However I've got hundreds of users, and thousands of books. So I'm thinking I want to be able to find the list of books a user has.

Initially I thought I could use a view:

CREATE VIEW view_steve  AS select * FROM books WHERE owner=73

But that suffers from a problem - the table has discountinuous IDs coming from the books table, and I'd love to be able to work with them in steps of 1. (Also having to create a view for each user is an overhead I could live without. Perhaps some stored procedure magic is what I need?)

Is there a simple way that I can create a view/subtable which would allow me to return something like:

|id|book_id|owner | title      |....|
|0 | 17    | Steve| Pies       | ..|
|1 | 32    | Steve| Fly Fishing| ..|
|2 | 21    | Steve| Smiles     | ..|
|3 | 24    | Steve| Debian     | ..|

Where the "id" is a consecutive, incrementing number, such that "paging" becomes trivial?

ObQuote: The Lost Boys

Syndicated 2008-07-19 14:16:07 from Steve Kemp's Blog

I don't know about you, but I am planning to scream and run

Over the past few nights I've managed to successfully migrate the Debian Administration website to the jQuery javascript library

This means that my own javascript library code has been removed, replaced, and improved!

The site itself doesn't use very much javascript - there are a couple of places where focus is set to a couple of elements, but other than that we're only talking about:

• Adding tags to articles, blog entrys, & polls
• Looking at the tag cloud
• Using the messaging system

Still there are a couple of enhancements that I've got planned which will make the site neater and more featureful for those users who've chosen to enable javascript in their browsers.

Here's my list of previous javascript usage - out of date now that I've basically chosen to use jQuery for everything.

ObQuote: Short Circuit.

Syndicated 2008-07-18 16:13:05 from Steve Kemp's Blog

It's no use pretending it hasn't happened cause it has

Yesterday I was forced to test my backup system in anger, on a large scale, for the first time in months.

A broken package upgrade meant that my anti-spam system lost the contents of all its MySQL databases.

That was a little traumatic, to say the least. But happily I have a good scheme of backups in place, and only a single MX machine was affected.

So, whilst there was approximately an hour of downtime on the primary MX the service as a whole continued to run, and the secondary (+ trial tertiary) MX machines managed to handle the load between them.

I'm almost pleased I had to suffer this downtime, because it did convince me that my split-architecture is stable - and that the loss of the primary MX machine isn't a catastrophic failure.

The main reason for panicing was that I was late for a night in the pub. Thankfully the people I were due to meet believe in flexible approaches to start times - something I personally don't really believe in.

Anyway the mail service is running well, and I've setup "instant activation now", combined with a full month of free service which is helping attract more users.

Apart from that I've continued my plan of migrating away from Xen, and toward KVM. That is going well.

I've got a few guests up and running, and I'm impressed at how stable, fast, and simple the whole process is. :)

ObQuote: Brief Encounter

(That is a great film; and a true classic. Recommended.)

Syndicated 2008-07-14 18:36:35 from Steve Kemp's Blog

He'll save every one of us!

So recently there have been a few posts by people discussing the idea of distributed bug reporting systems. This is topical, becuse I've been working on something vaguely related - a support system.

Why is a support system, or ticketing system, related to a bug tracker? To answer that you must first clarify what you mean by a support system (or a bug tracker for that matter). There are two distinct types of support systems:

"Full"

This is a system which does "everything". Each submitted support, or bug, request has an associated status, severity, priority and work log. It might be assigned to multiple people through its lifetime, and it might be moved around various internal queues.

Example: Request Tracker.

"Minimal"

A reported ticket is an email submission.

A "ticket" from start to finish is nothing more than a collection of mails upon a particular topic - there is no assigned "owner", no "priority", no object-specific attributes at all.

I've neither the time, patience, or desire to create any system like the first one. But I've become increasingly dissatisfied with my current support system, roundup, for various reasons. So I need something new.

Previously I ruled out several support systems and most of my objections still stand, although I admit I've not really looked again. I will certainly do so shortly.

So what have I done? Well I figure I care very little about queues, priorities, reports, and all that stuff. (On the basis that priority is mostly in the ticket itself, and that I'm the sole recipient of the submissions. Previously I had a partner to share them, but these days just me.)

I want something that is basically e-mail centric. That reduces the functionality of my support system to two distinct parts:

Ticket Submission

An email to sumbit@example.com should result in three actions:

• A message being sent back to the submitter. "Hey got your ticket. We'll fix it. Love you long time.". (Set the reply-to address appropriately!)
• A message to the site admin(s) saying "Hey, you've got a new ticket submission. Deal."
• A new ticket being created/stored somewhere.

(There are additional nice things to have - such as SMS alerts outside 10:00-17:00, etc, but those are trivial bolt-ons.)

Ticket Updates

When a mail comes in to "ticket-XXXX@example.org" - which was setup in the submission process, or when a mail arrives with subject "[issueXXXX]" we need to append the correspondance to the existing ticket.

If that ticket doesn't exist we report an error. If the ticket is closed we re-open it.

So, what do we have? A script that is conceptually simple, and can be invoked via a pipe transport of Exim4. A script that just reads the submitted email from STDIN and only has to decide a couple of things based pretty much on the contents of the "To:" and "Subject:" headers.

Simple, no?

OK it was actually amazingly simple once broken down. The amazing part is that it all works. I figured that I'd manage new tickets by writing them to mbox files - simple to do. Simple to understand.

So the process goes:

• New email arrives to "submit@example.org"
• /home/steve/tickets/1 is created with the received message written to it.
• I get a mail to steve@steve.example.org.uk saying "new ticket!!!".
• I reply to the ticket by opening /home/steve/tickets/1 in mutt - where my muttrc ensures that when I reply the from address is the ticket address, and there is an automatic CC.
• The cycle turns, and more discussion happens until we're all happy the ticket is "done"..
• A mail to ticket-xxxx-done@example.org" closes the ticket by moving it from ~/tickets/ to ~/tickets/archived/.

There are a couple of helper scripts:

• ticket-close - close a ticket manually.
• ticket-list - show all open tickets
• ticket-view - open a ticket in mutt

But basically one script and mutt does it all.

So far it's been tested and it is rockin'! (Look at me, hangin' with the cool kids!)

Obviously it is pretty Steve-specific, and there is only a toy CGI process for viewing history online, but I'm actually feeling pretty good about it.

Once I've closed all my existing tickets I will probably migrate over to it. At least this handles (read: ignores) MIME - which makes it a clear winner of roundup, which just eats, bounces, or corrupts submitted multipart messages.

ObQuote: Flash Gordon

Syndicated 2008-07-10 21:35:23 from Steve Kemp's Blog

When I was your age, television was called books

There are few programs I use with so much combined love & loathing than GNU screen.

Yesterday I spent a while adding another feature I've been wanting for so long, the unbindall primitive.

In many cases I find myself using screen as a wrapper around other things. But usually I end up having to disable dangerous keybindings, to gain security or to protect users from themselves.

Typically this leads to a screenrc file looking like this:

#
#  Disable these bindings.
#
bind :
bind s
bind S
bind Z
bind ^\
bind c
bind ^c
bind z
bind Z
bind B
...

Instead it would be better if I could just say:

#
#  Unbind *all* keystrokes
#
unbindall

#
#  Restore actions we need/want/love.
#
bind x quit
bind d detach
bind c screen
..

Anyway, thanks to a small patch I can now.

ObQuote: The Princess Bride</>

Syndicated 2008-07-08 16:04:22 from Steve Kemp's Blog