CREATE TABLE purchaseorder (
   purchaseorder_id VARCHAR(12) NOT NULL UNIQUE PRIMARY KEY,
   supplier         VARCHAR(50),
   order_date       DATE,
   vat              REAL,
   last_loaded      INT
);

CREATE TYPE item_category_enum AS ENUM (
                'av',
                'cables',
                'cases',
                'components',
                'consumables',
                'desktop',
                'maintenance',
                'mechanical',
                'monitor',
                'networking',
                'networkswitch',
                'office',
                'peripherals',
                'photographic',
                'portable',
                'portable als',
                'power',
                'printer',
                'robotics',
                'server',
                'services',
                'software',
                'storage',
                'storagearray',
                'tablet',
                'vision',
                'wap',
                'UNKNOWN'
);

/* This table is a representation of the orders files. There is a row for each individual item in the order.
   If an order file is edited, the associated rows in this table are deleted and reloaded - so no state
   can be recorded in this table. */
CREATE TABLE item (
   item_id       SERIAL NOT NULL UNIQUE PRIMARY KEY,
   category      item_category_enum,
   description   VARCHAR(120),
   purchaseorder VARCHAR(12) REFERENCES purchaseorder(purchaseorder_id),
   system        INT REFERENCES system(system_id),
   orderline     SMALLINT,      /* item row in orders - used to group items together */
   acq_date      DATE,          /* Date delivered */
   serial        VARCHAR(70),
   warranty      VARCHAR(80),
   cost          DECIMAL(10,2), 
   budget        VARCHAR(20),
   requestor     VARCHAR(32),
);

CREATE TYPE system_allocationtype_enum AS ENUM (
   'person',
   'server',
   'podium',
   'lab',
   'unallocated',
   'junk',
   'other'
);

CREATE TYPE system_managedtype_enum AS ENUM (
   'unknown',
   'lcfg',  /* or should it be DICE? */
   'selfdynamic',
   'selfstatic',
   'disposed', /* Not sure will be used */
   'other'
);

/* Systems (desktops, laptops, storage arrays, network switches) which we track (might be better if table
  were called 'tracked' ?). A system can be built from many "items". */

CREATE TABLE system (
   system_id          SERIAL NOT NULL UNIQUE PRIMARY KEY,
   make               VARCHAR(20),   
   model              VARCHAR(40),
   allocated          VARCHAR(20),      /* Note that we'll want to link to a person table */
   allocated_type     system_allocationtype_enum,
   barcode            VARCHAR(20),     
   hostname           VARCHAR(60),
   os                 VARCHAR(60),      /* Perhaps left in report table?, but no safe index*/
   kernel             VARCHAR(40),      /* perhaps left in report table?, but no safe index */
   type               system_managedtype_enum,
   lastconfirmed_date DATE,             /* for portable equipment, to record when ownership last checked */
   owner              VARCHAR(50),      
   space              INT REFERENCES space(space_id),
   manual_location    BOOLEAN,          /* set if the location has been set manually */
   last_seen          DATE,             /* Currently used for 'macaddr last seen' */
   role               VARCHAR(50),      /* What is this system/device used for (or purpose table?) */
   modified           BOOLEAN,          /* Set when the row has been modified */
   orphaned           BOOLEAN,          /* True if a serial-no that was part of an order is no longer listed on that order */
);

/* There can be multiple macaddr rows for each system */
CREATE TABLE macaddr (
   macaddr_id    SERIAL NOT NULL UNIQUE PRIMARY KEY, 
   system        INT references system(system_id),
   macaddr       VARCHAR(20),
   generate_dhcp BOOLEAN        /* When set, this address will be used for generating DHCP records */
);

CREATE TABLE site (
   site_id      SERIAL NOT NULL UNIQUE PRIMARY KEY,
   organisation VARCHAR(40),
   fullname     VARCHAR(80),
   abbrev       VARCHAR(8),
   postcode     VARCHAR(12),
   address      VARCHAR(100)
   /* GPS coords?*/
);

CREATE TYPE space_type_enum AS ENUM (
            'Building',
            'Corridor',
            'Cupboard',
            'Floor',
            'Open Plan',
            'Room',
            'Space',
            'Unknown'
);

CREATE TABLE space (
   space_id SERIAL NOT NULL UNIQUE PRIMARY KEY,
   site     INT REFERENCES site(system_id), 
   name     VARCHAR (12),
   type     space_type_enum,
   floor    VARCHAR (1)
);

CREATE TYPE history_changetype_enum AS ENUM (
   'creation',
   'location',
   'allocated',
   'type',
   'hostname',
   'comment',
   'disposed'     /* not sure will be used ? */
);

/* Table used to record changes in certain values of a system */
CREATE TABLE history (
   history_id    SERIAL NOT NULL UNIQUE PRIMARY KEY,
   date_recorded DATE,
   system        INT REFERENCES system(system_id),
   changetype    history_changetype_enum,
   newvalue      VARCHAR(256)
);

/* Loaded from combined SelectPC reports. Reloaded on a periodic basis */
CREATE TABLE supplierreport (
   sr_id   SERIAL NOT NULL UNIQUE PRIMARY KEY,
   macaddr VARCHAR(20),
   serial  VARCHAR(70),
   model   VARCHAR(60),
   po      VARCHAR(12)
);


CREATE TABLE report (
   /* These are the reports produced by the clientreport script on DICE machines. The DICE machines
     write into this table directly, so we don't want to link to the system table hence a separate table
     that is periodically sync'd into the main system rows. However, this does have the disadvantage 
     that we miss out on a lot of the information in this table.
     table. 

     Alternative is that we use something like remctl from each client to the to accept the data and 
     update the system and report row.
   */

   hostname      VARCHAR(60),
   make          VARCHAR(40),  
   model         VARCHAR(60),
   serial        VARCHAR(70),
   memory        INT, 
   kernel        VARCHAR(40),
   rebootpending VARCHAR(20),
   date          DATE,
   monitor       VARCHAR(40),
   monitorsno    VARCHAR(40),
   monitoryear   INT,
   mac           VARCHAR(20),
   os            VARCHAR(60), 
   mousepresent  boolean,
   kbdpresent    boolean
);

-- AlastairScobie - 06 Aug 2013

Topic revision: r1 - 06 Aug 2013 - 08:52:56 - AlastairScobie
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback
This Wiki uses Cookies