X-Git-Url: https://defaria.com/gitweb/?a=blobdiff_plain;f=maps%2Fbin%2FMAPSDB.sql;h=211334b27c0d225a3fd93ea8951bf8f9e1f3fa05;hb=7ddf095f187ca60d9a70fb83b2bc3c2b6d91f088;hp=54a49174637893285c9f3805a0e9cc00f24c82ed;hpb=aed243fe03fc8f9b40f753ea1ef85ce59cce9011;p=clearscm.git

diff --git a/maps/bin/MAPSDB.sql b/maps/bin/MAPSDB.sql
index 54a4917..211334b 100644
--- a/maps/bin/MAPSDB.sql
+++ b/maps/bin/MAPSDB.sql
@@ -1,12 +1,12 @@
 -------------------------------------------------------------------------------
 --
--- File:	$RCSFile$
--- Revision:	$Revision: 1.1 $
--- Description:	This file creates the MAPS database.
--- Author:	Andrew@DeFaria.com
--- Created:	Tue May 13 13:28:18 PDT 2003
--- Modified:	$Date: 2013/06/12 14:05:47 $
--- Language:	SQL
+-- File:        $RCSFile$
+-- Revision:    $Revision: 1.1 $
+-- Description: This file creates the MAPS database.
+-- Author:      Andrew@DeFaria.com
+-- Created:     Tue May 13 13:28:18 PDT 2003
+-- Modified:    $Date: 2013/06/12 14:05:47 $
+-- Language:    SQL
 --
 -- Copyright (c) 2000-2006, Andrew@DeFaria.com, all rights reserved
 --
@@ -22,56 +22,68 @@ use MAPS;
 
 -- user: Valid users and their passwords are contained here
 create table user (
-  userid			varchar (128)	not null,
-  name				tinytext	not null,
-  email				varchar (128)	not null,
-  password			tinytext	not null,
+  userid      varchar (128) not null,
+  name        tinytext      not null,
+  email       varchar (128) not null,
+  password    tinytext      not null,
   primary key (userid)
 ); -- user
 
 -- useropts: User's options are stored here
 create table useropts (
-  userid			varchar (128)	not null,
-  name				tinytext,
-  value				varchar (128),
+  userid         varchar (128) not null,
+  name           tinytext,
+  value          varchar (128),
   key user_index (userid),
   foreign key (userid) references user (userid) on delete cascade
 ); -- useropts
 
 -- email: Table that holds the email
 create table email (
-  userid			varchar (128)	not null,
-  sender			varchar (128)	not null,
-  subject			varchar (255),
-  timestamp			datetime,
-  data				longblob,
-  key user_index (userid),
-  foreign key (userid) references user (userid) on delete cascade,
+  userid           varchar (128) not null,
+  sender           varchar (128) not null,
+  subject          varchar (255),
+  timestamp        datetime,
+  data             longblob,
+  key user_index   (userid),
+  foreign key      (userid) references user (userid) on delete cascade,
   key sender_index (sender)
 ); -- email
 
--- whitelist: Table holds the users' whitelists
+-- list: Table holds the users' various lists
 create table list (
-  userid			varchar (128)	not null,
-  type				enum ("white", "black", "null") not null,
-  pattern			varchar (128),
-  domain			varchar (128),
-  comment			varchar (128),
-  sequence			smallint,
-  hit_count			integer,
-  last_hit			datetime,
-  key user_index (userid),
+  userid            varchar (128)                   not null,
+  type              enum ("white", "black", "null") not null,
+  pattern           varchar (128),
+  domain            varchar (128),
+  comment           varchar (128),
+  sequence          smallint,
+  hit_count         integer,
+  last_hit          datetime,
+
+-- Retention: This field indicates how much time must pass before an inactive
+--            list entry should be scrubbed. Null indicates retain forever.
+--            other values include "x day(s)", "x month(s)", "x year(s)". So,
+--            for example, a user on the white list may have its retention set
+--            to say 1 year and when mapsscrub runs, if last_hit is older than
+--            a year the whitelist entry would be removed. If, however, 
+--            retention is null then the record is kept forever. This is useful
+--            for the null and black lists where one might want to insure that
+--            a particular domain (e.g. @talentburst.com) will never come off
+--            of the nulllist.
+  retention         varchar (40),
+  key user_index    (userid),
   key user_listtype (userid, type),
-  unique (userid, type, sequence),
-  foreign key (userid) references user (userid) on delete cascade
+  unique            (userid, type, sequence),
+  foreign key       (userid) references user (userid) on delete cascade
 ); -- list
 
 -- log: Table to hold log information
 create table log (
-  userid			varchar (128)	not null,
-  timestamp			datetime,
-  sender			varchar (128),
-  type				enum (
+  userid    varchar (128) not null,
+  timestamp datetime,
+  sender    varchar (128),
+  type      enum (
     "blacklist",
     "debug",
     "error",
@@ -82,15 +94,12 @@ create table log (
     "returned",
     "whitelist"
   ) not null,
-  message			varchar (255)	not null,
+  message        varchar (255) not null,
   key user_index (userid),
-  foreign key (userid) references user (userid) on delete cascade
+  foreign key     (userid) references user (userid) on delete cascade
 ); -- log
 
 -- Create users
---grant all privileges 
---  on MAPS.* to mapsadmin@"%"  identified by "mapsadmin";
---grant select
---  on MAPS.* to mapsreader@"%" identified by "reader";
---grant insert, select, update, delete
---  on MAPS.* to mapswriter@"%" identified by "writer";
+-- New 8.0 syntax...
+--create user 'maps'@'localhost' identified by 'spam';
+grant all privileges on MAPS.* to 'maps'@'localhost';