-- data model for the ArsDigita MTA monitoring system
-- by Branimir Dolicki <bdolicki@arsdigita.com>
-- copyright 1999 ArsDigita LLC 
-- 
-- the idea by Philip Greenspun <philg@mit.edu>:
-- http://photo.net/wtr/arsdigita-server-architecture.html

create sequence mmon_server_id_sequence start with 5000;
create sequence mmon_run_count start with 1;
create sequence mmon_event_count start with 1;

create table mmon_servers (
   server_id                       integer not null
                                   constraint primkey_mmon_servers primary key,
   ip_or_hostname                  varchar(60) not null,
   -- It's probably better to put the IP here to avoid
   -- problems with with hostname resolution.  On the
   -- other hand someone who often changes IP adresses
   -- might find it more convenient to use the hostname.
   smtp_port                       integer default 25 not null,
   -- It may come handy not to use the standard port 25, for example
   -- while testing or with port redirection with the Secure Shell.
   bouncer_email                   varchar(100) default 'mmon_bouncer' not null,
   -- This is the special email address which should exist
   -- on the monitored server.  Anything sent to that address
   -- should return to sender.  It can be an address
   -- without the '@hostname' part if the monitored server
   -- will recognize it.
   first_monitored                 date not null,
   smtp_ok_p                       char(1) default 't'
                                   check(smtp_ok_p in ('t','f')),
   last_unbounced_emailet_id       varchar(70),
   run_period                      integer default 3 not null,
   run_group                       integer default 0 not null
);

create table mmon_log (
   event_id                        integer not null 
                                   constraint primkey_mmon_log primary key,
   server_id                       not null references mmon_servers,
   event_time                      date,
   event_description               varchar(40),
   error_message                   varchar(200),
   emailet_id                      varchar(70),
   event_reported_p                char(1) default 'f' check(event_reported_p in ('t','f'))
);

create table mmon_global_state (
   last_notification_time          date   
);

insert into mmon_global_state values (NULL);

create index mmon_log_idx on mmon_log (server_id);








