[Linuxvm-dev] Crude SQL Schema

[Kevin E. <ke...@en...>]

Ok, attached (and included in case u have problems with my attachment) is
a   very crude but basic sql schema for a voicemail system. I'm sure
there's a million little details we need to add....
  My ideas for the 'object model' are at this point pretty abstarct.
Basically the way I see it is you have a menu object which is intialized
with an instance of the ctserver. Each instance of the menu requires
some kind kind of user (the default user being "Outside Caller"), each
user has certain permission which we define. The user may navigate
around the menu and will be given options that coorespond to his
permissions. the tree structure of the menu would be best represented by
some kind XML document that can be customized via a web interface.
  If i had to make objects right now, i would make the following:

   Voicemail::Menu -- controls navigation thru voicemail menu system
   Voicemail::User -- represents a user, an instance of this will be used
in every instance of the Voicemail::Menu object.
   Voicemail::Permissions -- permission verifier
   Voicemail::DB -- db functions

what do u guys think? when will that box be outside the firewall so we can
start messing around with it....

k


voicemail.sql
---------------------
DROP TABLE IF EXISTS VM_Users  ;
-- the Vm_USERs table, holds basic info about a user
CREATE TABLE VM_Users (
  user_id integer auto_increment primary key,
  first_name varchar(100) not null,  -- basic name info
  last_name varchar(100) not null,
  mi varchar(1) null,
  active smallint not null default 0, -- active user in the system
  department_id  integer not null,
  permission_id  integer not null ,
  phone_keys  integer null -- phone keys for 'lookup' feature...
) auto_increment = 100;

DROP TABLE IF EXISTS VM_Greetings  ;
-- the VM_Greetings table, holds info about user's greetings....
CREATE TABLE VM_Greetings (
  greeting_id integer auto_increment primary key ,
  user_id integer not null,
  default_greeting smallint not null,
  greeting_wav_path varchar(200) not null
) auto_increment = 5001;


DROP TABLE IF EXISTS VM_Departments  ;
-- the VM_Greetings table, holds info about departments....
create table VM_Departments (
  department_id integer auto_increment primary key,
  department_name varchar(100) not null ,
  default_user_id integer  not null ,  -- who's mail box will it go to if
it's a general inquiry?
  department_desc_wav_path varchar(200)
) auto_increment  = 701;

DROP TABLE IF EXISTS VM_Messages  ;
-- the VM_Messages table, holds info about  all messages in the system
Create table VM_Messages (
  message_id integer auto_increment primary key,
  message_timestamp DATE not null,
  user_id_to  integer not null , -- who's the message for?
  user_id_from integer not null, -- who sent the message
  message_wav_path integer not null, --  where is the message on the server
  saved_message integer not null -- is this a saved message?
) auto_increment  = 10001 ;

DROP TABLE IF EXISTS VM_Permissions  ;
-- the VM_Permissions table, holds info about User permissions
Create table VM_Permissions (
  permission_id integer auto_increment primary key,
  permission_desc varchar(200) not null
) auto_increment  = 1 ;

-- sample data....
-- first, the departments...
INSERT INTO VM_Departments ( department_id, department_name,
default_user_id , department_desc_wav_path  )
values (0, 'Marketing', 0, '/var/data/makerting.wav');  -- makes
department id  701
INSERT INTO VM_Departments ( department_id, department_name,
default_user_id , department_desc_wav_path  )
values (0, 'Tech Support', 0, '/var/data/techsupport.wav'); -- makes
department id  702
INSERT INTO VM_Departments ( department_id, department_name,
default_user_id , department_desc_wav_path  )
values (0, 'Accounting', 0, '/var/data/accocunting.wav'); -- makes
department id 703
INSERT INTO VM_Departments ( department_id, department_name,
default_user_id , department_desc_wav_path  )
values (0, 'Information Technology', 0, '/var/data/it.wav'); -- makes
department id 703
-- let's see em
SELECT * FROM VM_Departments ;
-- now, how bout some permissions...
INSERT INTO VM_Permissions VALUES (0, 'Annonymous/Outside Caller
Permissions');
INSERT INTO VM_Permissions VALUES (0, 'Regular User Permissions'); --
permission level 2
INSERT INTO VM_Permissions VALUES (0, 'Super User Permissions'); --
permission level 3
-- let's see em
SELECT * FROM VM_Permissions ;


INSERT INTO VM_Users ( user_id , first_name, last_name , mi, active,
department_id,  permission_id,
  phone_keys ) VALUES (0,'Outside', 'Caller',null, 1,0,1,null);  -- this
is the outside caller, he'll be user_id 100
INSERT INTO VM_Users ( user_id , first_name, last_name , mi, active,
department_id,  permission_id,
  phone_keys ) VALUES (0,'Joe', 'Marketing','W', 1,701,2,627);  --
marketing guy
INSERT INTO VM_Users ( user_id , first_name, last_name , mi, active,
department_id,  permission_id,
  phone_keys ) VALUES (0,'Joe', 'Techsupport','W', 1,701,2,823);  -- tech
support genius
INSERT INTO VM_Users ( user_id , first_name, last_name , mi, active,
department_id,  permission_id,
  phone_keys ) VALUES (0,'Joe', 'Accounting','W', 1,701,2,222);  -- 
Number cruncher
INSERT INTO VM_Users ( user_id , first_name, last_name , mi, active,
department_id,  permission_id,
  phone_keys ) VALUES (0,'Joe', 'InfoTech','W', 1,701,3,222);  -- the IT
Guru, with higher permissions

SELECT * FROM VM_Users;