----------------------------------------- | | | LANdb - The Network Management Database | | | ----------------------------------------- This file describes a bit of LANdb's background, and how it's constructed. The content in this file may be of interest to developers who wish to extend LANdb, or to anyone interested in learning some of the intricacies of the application. The file is constantly being updated Table of Contents I. Introduction II. Structure III. The LANdb API IV. The Future I. Introduction LANdb started as a simple, reliable method of collecting and maintaining network information. It's grown into quite a bit more. Network administrators can now catalog every node on their network (hey, let me know if there's one that you can't get into the DB), quickly, easily, and permanently. The project's inspiration was the current state of management at his workplace. Spreadsheets, Excel, Comma Separated values, and a few perl scripts to search through data were the only things available to us. Keeping the data up to date was the responsibility of one person; getting the data to that person was the technician's responsibility. The "secretary" updates her spreadsheet, copies it to a floppy, her hard drive, and a network drive for backups, then FTP's the file to a webserver, where the perl scripts grep through everything during a search request. Not only is this inefficient, it's dangerous. I can't count the number of times something went wrong, resulting in a complete closet/switch re-audit. During the summers (this is a University setting), the technicians would spend days re-auditing every closet on campus, just to catch the jack activations and changes that weren't recorded in the spreadsheets. Needless to say, something had to be done. I'm a big Linux user/administrator, and I needed an excuse to learn about databases. Enter LANdb. II. Structure The application, obviously, is a web-based interface, written in Perl. MySQL is the backend of choice, chosen for its incredible speed and reliability. The price tag helps too though-- you can use MySQL free of charge, as long as you're not paid to install it (? Read their license for yourself). MySQL isn't technically a Relational Database (RDBMS), but you can make it do pretty much everything you'd do with Oracle et all, and MySQL has some handy extentions to the SQL standard. Unfortunately, use of MySQL causes some difficulties porting LANdb to other RDBMS's, but none of them are impossible to fix. Need it ported? Just ask... To keep the database fast and light, data is broken into several small tables, rather than storing everything in one or two huge tables. This keeps LANdb operating at amazing speeds, even on lower-end machines. In fact, the original target machine was a P75 with 32 megs RAM, which already ran several services. The actual test machine (the author's) is a P233 MMX with 48 megs, but the installation of v0.75.1 on that P75 operates at quite reasonable speed. The idea behind keeping everything small and tight is this: Some of your buildings will have hundreds of jacks (connections, drops, etc.), whereas others will have fewer than one hundred. Why drag down functions that are only operating on that small building with the burden of the larger building's data? Also, since you're only working on data for one building at a time, there's no need at all to be pulling data from another building into memory. Finally, several small tables provides for better data integrity-- this way, one corrupt table doesn't trash your entire database. Enough about the "why," let's get into the "how." As mentioned above, each building has it's own table. General information on all buildings is stored in the 'building' table. Each individual building table named as 'building_N' where N is an auto-incremented value stored in the 'building' table. MySQL's 'auto_increment' function is used to keep this value. The figure below illustrates this relationship. Database | ------Building------ | | | building_00000001 building_00000002 building_00000003 By performing a simple lookup in the 'building' table, you can get to your target 'building_N' table, which contains the pertinent data. The very same schema is used to store Switch and VLAN information, which keeps the application very consistent throughout. Other information is stored separately from the often-used tables, to keep things that you may not need constantly from bogging down that which you do. Building Attributes, Media Types, Closet information, VLAN information, and the LANdb version are kept separate from the tables that actually store information about what jack is on what port. Duplication of data is an important consideration. Wherever possible, it's generally a good idea in a database to avoid duplicating data. However, when performance and correctness become an issue, a larger database is acceptable. An example of this problem follows. Jack number, Port, Slot, Cable number, Closet, and Switch identifier are all stored in the 'building_N' tables. However, in the 'switch_N' tables, the Jack, Port, and Slot appear again. This is contrary to common database logic-- no two tables should store the same piece of information. But consider the consequences of not breaking up that data: Someone enters an inactive jack into the database. Someone else enters an active jack. Suppose we want to query what slot/port and switch this jack is on. Without the port/slot identifier, we have to have something that says 'active/inactive,' and then perform a lookup on the switch table if it's marked 'active,' searching through possibly several hundred rows for our jack number. By storing the slot/port number, we save ourselves a bit of work. On the other hand, we want to avoid unnecessary data duplication. Enter the idea of keeping a general 'switches' table, which stores general info about each 'switch_N.' Things like closet name, switch name, switch IP, building name, etc., can be stored here, so as to avoid having unnecessary columns in constantly-accessed, data-intensive tables. Like data, duplication of code is taboo. Thus, nearly every function of the application is stored in it's own script on the server. When a user needs to lookup a connection by port number, he simply requests the script 'lo-by-port1.cgi' which passes him to the same script numbered 2, and so on, untill the data is accessed. There's no need to pull globs of perl code into memory (interpreted languages are slow, remember) if we can leave it on the disk. The most important part of this code-reduction process was the advent of the LANdb API, first suggested and implemented by Frank Sweetser, one of the first LANdb users. The API makes common functions available to all scripts, allowing better performance, and making it easier for others to add extensions to the application. The API is covered more in-depth in the LANdb API section. As of v0.80 (not yet released at the writing of this document), LANdb's user management is crude. There are basically two users-- those who have, and those who have not. The 'admin' user has full rights to every funtion, and any other user specified by the DB administrator has only read-only access. Until better user management is implemented, it's left to the developer to decide who gets access to which functions, which can obviously cause problems. This is obviously a poor user management policy, and it will be changed in the .9x development series to be released in 1.0, where a user manager module, as well as a system of function ID's will be implemented. III. The LANdb API In the next episode... IV. The Future Relax, I haven't even gotten the present finished yet... -John Madden weez@avenir.dhs.org