Allright, so if you're reading this, you're probably also looking at the
proposed new schema for LANdb.  Odds are, you looked at the schema, and went
"Huh?"  If so, congratulations, you're sane - it's taken me almost as long to
write it as it has to understand it!  (If you *did* look at the schema and
find it obvious, may I reccomend drugs, kernel hacking, or both?)  Mostly this
will give you a blow-by-blow of the individual tables, with a few extra
paragraphs to explain some of the more convoluted concepts.  Overall, there
are five groups of tightly coupled tables within the database, so I'll go
through each group together.

First, the simplest one - the global_attributes table.  This is a simple
key/value pair table.  The only one guaranteed to be in there at the moment is
the version tuple.  By sticking the version in here, it makes it rather
difficult to accidentally mismatch the database schema and the code attempting
to bang on it.  For those familiar with the old schema, yes, the version table
is gone.  So we can tell easily enough if the version table or
global_attributes is present whether we should even both checking the version,
now can't we?

Second is the user management tables.  The primary table here is the user
table.  It stores a listing of all users, and their encrypted passwords.  The
hash is used for the session key sent back and forth to the browser, and the
time is the last time that the user performed an action, used for determining
if the session has timed out yet.  The syslog table, which contains one tuple
for each event that is logged, includes when, from where, and uses the user
table to key the username.

The functions table indexes all of the various functions - searching,
updating, adding, etc.  The allowed_functions table is a glue table that maps
out which users are allowed to perform which functions.

Allright - now for the third one.  This would be the huge clump of tables that
vaguely resembles a flowchart made from a nice Italian meal, complete with
meatballs, a few too many glasses of wine, and two helpings of dessert the
waiter dropped and smeared into the middle of the mess.  We'll try to make
this easier by starting at the "top" and working out way in.

A good starting point here is the building table.  It stores an indexed list
of buildings, along with the location and a comment.  (What?  you want to
store the color and number of windows of each building?  Don't worry, we'll
get to that later, along with closets, switches, boards, and jacks.  And yes,
individual ports, too, though you may regret it!)  Next down is the closet
table, which stores per closet the name, building, location, and again a
comment.  Two other tables key directly into the closet table - the chassis
table, and jack table.  

Next up is the chassis table - one row per switch/hub/router/washing
machine/whatever you plug your jacks into on the closet side.  Each tuple
contains the name, IP address, description, read/write and read only SNMP
community strings, which closet it's in, and a foreign key for which model it
is.  The model is contained in the chassis_model table, which contains the
name, and the ifindex_formula - a perl fragment which takes the slot and port
number, and returns an ifindex suitable for identifying that particular port
in the SNMP jungle for that particular switch.  If this is null, the ifindex
field for any ports generated off of this switch will be left null.

The jack table.  This is a list of all jacks - the things the cables dangling
off of the computers plug into.  Attributes include name (whatever is
scribbled on the front of it), description (second one from the left with a
big crack from the movers), and cable, if you have some kind of unique
identifier for each cable in the closet (here, we use it to keep track of the
patch panel location.  Very handy when you have 1000 drops in a single closet
that are *mostly* ordered!).  Very closely related is the interbuilding_jack
table.  Unless you've got one building, or at least one bit 'o network
hardware serving your entire campus, you've probably got some kind of
interbuilding links, probably fiber optics.  This little table allows you to
associate a given jack in one building/closet with another jack in a different
building/closet.  From here, you can figure out a good deal of the physical
network topology.

The media_id attribute keys into the media table, which is simply a list of
possible media types you can put in.  A quick note - yes, the port table does
indeed also key into the media table, no, no consistency checking is done
between the two.  You can tell LANdb you're plugging a SM LC fiber jack into a
10b2 port, and it will cheerfully nod an say "okay".  This is because there
are sufficient ways around just about any media problem ( ie, catN <-> catM,
various fiber connections, etc) that it's simply not worth it to try and teach
LANdb all that logic IMNSHO.

The closet_id attribute keys into (c'mon, guess...) the closet table!  and
from there you can continue on to which building you're in (the drop, that is
- I certainly hope you know what building *you're* in right now!).  And the
port_id attribute likewise keys into the port table.

So now we're on the port table.  One tuple per port.  Ifindex is, not
surprisingly, the ifindex this switch has decided to assign to it, if it has
one at all, so don't depend on this being non-null.   The port_model_id field
keys into the port_model table, which... well, let's just get to that later
(yes, I *am* putting that whole section off for now.  Skip ahead, or deal.).
The port field indicates the port number this port is on it's particular
board.  Follow the board_id field back to the board table, which knows which
slot it's on, then the chassis_id back to the chassis table, so we know what
J. Random Network Hardware we're attached to.

Note the uplinks table here.  This serves a very similar purpose relative to
ports and chassis as does the interbuilding_jacks table relative to jacks.  If
you've got multiple chassis plugged directly into each other with no
intervening jacks (ie, they're seperated only by two feet of air space and
four feet of patch cord) you can represent it here.  Note that this doesn't
try and stop you from making any kind of loops - 1) loops can be "safely"
created if you've got spanning tree enabled, the moon is in phase, and you've
sacrificed 3.14159 virgin goats, and 2) there are some chassis with a
sufficiently advanced case of multiple personalities you do indeed have to run
patch cords from board a to board b just to get the everybody plugged into the
chassis talking to each other.

The snmp_agent table, at first, seems to be eroneously keyed into by the port
table, especially when it also keys into the chassis table itself.  This makes
sense for two reasons - 1) it is possible to have multiple agents in a single
chassis, each one controlling only a subset of all ports, and 2) if the agent
is embedded in the chassis itself, it may be possible to remove all boards
(and hence all ports) from that chassis without affecting the agent itself at
all.  If the other attributes in this table aren't quite obvious, you
shouldn't be doing anything with SNMP before reading up - you might hurt
yourself by doing so.

Okay, next forkfull of this platefull of al dente foreign key relations - the
various model tables.  There are three main tables (chassis_model,
board_model, and port_model), and two glue tables (chassis_boards_model and
board_ports_model).  The chassis_model, as was mentioned previously if you
remember (I know I didn't 'till I went and looked) simply contains the name of
the model of the chassis (ie, d5000, Centillion 100, etc) and the ifindex
forumla.  Similarly, the board_model contains the name of the board model
(8132TX, etc) and the port_model table contains the name of each and every
last type of port (8132TX RJ45 port, 400-1LX Gig MDA, etc).  Note that the
port_model table contains an additional field - the lan_type_id, which keys
into the lan_type table.  This table is just a simple list of the various LAN
flavors you happen to have installed (ethernet, token ring, ATM, etc).  This
is associated with the port rather than the chassis because many of the more
intelligent (or at least more expensive) devices at least claim to be able to
deal with multiple types, depending on which boards are purchased.

The two glue tables chassis_boards_model and board_ports_model serve to
indicate which boards can fit into which slots in which chassis, and which
ports can be found on which model of which board.  The mandatory field
indicates whether this particular bit of hardware is always found with it's
relevant parent, usually indicating that it's built in, rather than a
removable module.

Okay - so in the schema's current incarnation, this covers everything except
the various configuration tables.  There are essentially two main sections
holding configuration data - the port configuration tables, and everything
else. We'll start with the easier of the two - everything else.

The everything else configuration section includes the other two
not-directly-connected table chunks.  One of the chunks handles arbitrary
values (strings and numbers) while the other handles enumerated values (red
blue or green, 10 100 100, etc).

Start with the arbitrary settings tables, specifically the misc_string_setting
table.  The name field is the label of this particular setting (color, speed,
hemisphere, etc).  The table field indicates which of the various other
entities this applies to (chassis, boards, jacks, etc (I'll release a more
authorotative list of which values are respected once the schema is finalized
(hah!) but not ports).  The len field gives the maximum length for this
setting.  The model_id field, if applicable for this particular entity, tells
which models this setting can apply to (doesn't make much sense to store
whether this switch runs a LES/BUS on a token ring switch, now does it?).  The
misc_string_value table stores the mapping between each instance of the
appropriate foreign entity, along with the actual value.

The enumerated setting side works similarly.  The misc_enumerated setting
stores again the name of the setting, which foreign entity it applies to, and
which model of those foreign entites to which applies it applies to (fun with
obfuscated english!).  The misc_enumerated_value table in this case holds one
tuple for each possible value of each tuple of misc_enumerated_setting, which
also holds a reference to the default value in the default_enumerated_value_id
field.  The misc_enumerated_glue table is used to map which instances in which
foreign table have which value assigned.

So based on this so far, the whole port configuration portion of the schema
should be pretty obvious, right?  You want me to explain it anyways?  Damn.
Oh well, I'll give it a shot.

Eventually.  If you can figure it out and write a good description, please let
me know - I have enough trouble explaining it in person, so doing so in
writing should be a neat trick =)