424 lines
22 KiB
PHP
424 lines
22 KiB
PHP
<?php
|
|
require_once dirname(__FILE__).'/accesscheck.php';
|
|
|
|
define("STRUCTUREVERSION","2.10.12");
|
|
|
|
$DBstruct = array( # order of tables is essential for smooth upgrade
|
|
"attribute" => array ( # attributes of a user or a message
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"name" => array("varchar(255) not null","Name"),
|
|
"type" => array("varchar(30)","Type of attribute"),
|
|
"listorder" => array("integer","order of listing"),
|
|
"default_value" => array("varchar(255)","Default value"),
|
|
"required" => array("tinyint","Required for user to fill out"),
|
|
"tablename" => array("varchar(255)","Name of table with values"),
|
|
"index_1" => array("nameindex (name)",""),
|
|
"index_2" => array("idnameindex (id,name)","")
|
|
),
|
|
"user_attribute" => array(
|
|
"attributeid" => array("integer not null","attribute"),
|
|
"userid" => array("integer not null","user"),
|
|
"value" => array("varchar(255)","Value of this attribute for this user"),
|
|
"primary key" => array("(attributeid,userid)","PKey"),
|
|
"index_1" => array("userindex (userid)",""),
|
|
"index_2" => array("attindex (attributeid)",""),
|
|
"index_3" => array("userattid (attributeid,userid)",""),
|
|
"index_4" => array("attuserid (userid,attributeid)",""),
|
|
),
|
|
"user" => array ( # a user in the system
|
|
"id" => array("integer not null primary key auto_increment","sysexp:ID"),
|
|
"email" => array("varchar(255) not null","Email"),
|
|
"confirmed" => array("tinyint default 0","sys:Is this user confirmed"),
|
|
"blacklisted" => array("tinyint default 0","sys:Is this user blacklisted"),
|
|
"bouncecount" => array("integer default 0","sys:Number of bounces on this user"),
|
|
"entered" => array("datetime", "sysexp:Entered"),
|
|
"modified" => array("timestamp", "sysexp:Last Modified"),
|
|
"uniqid" => array("varchar(255)","sys:Unique ID for User"),
|
|
"unique_1" => array("(email)","sys:unique"),
|
|
"htmlemail" => array("tinyint default 0","Send this user HTML emails"),
|
|
"subscribepage" => array("integer","sysexp:Which page was used to subscribe"),
|
|
"rssfrequency" => array("varchar(100)","RSS Frequency"),
|
|
"password" => array("varchar(255)","Password"),
|
|
"passwordchanged" => array("date","sys:Last time password was changed"),
|
|
"disabled" => array("tinyint default 0","Is this account disabled?"),
|
|
"extradata" => array("text","Additional data"),
|
|
"foreignkey" => array("varchar(100)","Foreign Key"),
|
|
"index_1" => array("(foreignkey)","sys:Foreign Key"),
|
|
"index_2" => array("idx_phplist_user_user_uniqid (uniqid)","sys:index"),
|
|
"index_3" => array("emailidx (email)","sys:index"),
|
|
"index_4" => array("enteredindex (entered)","sys:index"),
|
|
),
|
|
"user_history" => array(
|
|
"id" => array("integer not null primary key auto_increment","sys:ID"),
|
|
"userid" => array("integer not null",""),
|
|
"ip" => array("varchar(255)",""),
|
|
"date" => array("datetime",""),
|
|
"summary" => array("varchar(255)","History Summary"),
|
|
"detail" => array("text","History Detail"),
|
|
"systeminfo" => array("text","System Information"),
|
|
"index_1" => array("userididx (userid)","sys:index"),
|
|
"index_2" => array("dateidx (date)","sys:index"),
|
|
),
|
|
"user_blacklist" => array(
|
|
"email" => array("varchar(255) not null unique","Email"),
|
|
"added" => array("datetime","When added to blacklist"),
|
|
"index_1" => array("emailidx (email)",""),
|
|
),
|
|
"user_blacklist_data" => array(
|
|
"email" => array("varchar(255) not null unique","Email"),
|
|
"name" => array("varchar(100) not null","Name of Dataitem"),
|
|
"data" => array("text",""),
|
|
"index_1" => array("emailidx (email)",""),
|
|
"index_2" => array("emailnameidx (email,name)",""),
|
|
),
|
|
"list" => array ( # a list in the system
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"name" => array("varchar(255) not null","Name"),
|
|
"description" => array("Text","Description"),
|
|
"entered" => array("datetime","Entered"),
|
|
"listorder" => array("integer","Order of listing"),
|
|
"prefix" => array("varchar(10)","Subject prefix"),
|
|
"rssfeed" => array("varchar(255)","Rss Feed"),
|
|
"modified" => array("timestamp", "Modified"),
|
|
"active" => array("tinyint","Active"),
|
|
"owner" => array("integer","Admin who is owner of this list"),
|
|
"index_1" => array("nameidx (name)",""),
|
|
"index_2" => array("listorderidx (listorder)",""),
|
|
),
|
|
"listrss" => array( # rss details for a RSS source of a list
|
|
"listid" => array("integer not null","List ID"),
|
|
"type" => array("varchar(255) not null","Type of this entry"),
|
|
"entered" => array("datetime not null",""),
|
|
"info" => array("text",""),
|
|
"index_1" => array("listididx (listid)",""),
|
|
"index_2" => array("enteredidx (entered)",""),
|
|
),
|
|
"listuser" => array ( # user subscription to a list
|
|
"userid" => array("integer not null","User ID"),
|
|
"listid" => array("integer not null","List ID"),
|
|
"entered" => array("datetime", "Entered"),
|
|
"modified" => array("timestamp", "Modified"),
|
|
"primary key" => array("(userid,listid)","Primary Key"),
|
|
"index_1" => array("userenteredidx (userid,entered)",""),
|
|
"index_2" => array("userlistenteredidx (userid,listid,entered)",""),
|
|
),
|
|
"message" => array ( # a message
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"subject" => array("varchar(255) not null default '(no subject)'","subject"),
|
|
"fromfield" => array("varchar(255) not null default ''","from"),
|
|
"tofield" => array("varchar(255) not null default ''","tofield"),
|
|
"replyto" => array("varchar(255) not null default ''","reply-to"),
|
|
"message" => array("mediumtext","Message"),
|
|
"textmessage" => array("mediumtext","Text version of Message"),
|
|
"footer" => array("text","Footer for a message"),
|
|
"entered" => array("datetime","Entered"),
|
|
"modified" => array("timestamp", "Modified"),
|
|
"embargo" => array("datetime","Time to send message"),
|
|
"repeatinterval" => array("integer default 0","Number of seconds to repeat the message"),
|
|
"repeatuntil" => array("datetime","Final time to stop repetition"),
|
|
# "status" => array("enum('submitted','inprocess','sent','cancelled','prepared','draft')","Status"),
|
|
"status" => array("varchar(255)","Status"),
|
|
"userselection" => array("text","query to select the users for this message"),
|
|
"sent" => array("datetime", "sent"),
|
|
"htmlformatted" => array("tinyint default 0","Is this message HTML formatted"),
|
|
"sendformat" => array("varchar(20)","Format to send this message in"),
|
|
"template" => array("integer","Template to use"),
|
|
"processed" => array("mediumint unsigned default 0", "Number Processed"),
|
|
"astext" => array("integer default 0","Sent as text"),
|
|
"ashtml" => array("integer default 0","Sent as HTML"),
|
|
"astextandhtml" => array("integer default 0","Sent as Text and HTML"), // obsolete
|
|
"aspdf" => array("integer default 0","Sent as PDF"),
|
|
"astextandpdf" => array("integer default 0","Sent as Text and PDF"),
|
|
"viewed" => array("integer default 0","Was the message viewed"),
|
|
"bouncecount" => array("integer default 0","How many bounces on this message"),
|
|
"sendstart" => array("datetime","When did sending of this message start"),
|
|
"rsstemplate" => array("varchar(100)","if used as a RSS template, what frequency"),
|
|
"owner" => array("integer","Admin who is owner")
|
|
),
|
|
"messagedata" => array(
|
|
"name" => array("varchar(100) not null","Name of field"),
|
|
"id" => array("integer not null", "Message ID"),
|
|
"data" => array("text","Data"),
|
|
"primary key" => array("(name,id)",""),
|
|
),
|
|
"listmessage" => array ( # linking messages to a list
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"messageid" => array("integer not null","Message ID"),
|
|
"listid" => array("integer not null","List ID"),
|
|
"entered" => array("datetime", "Entered"),
|
|
"modified" => array("timestamp","Modified"),
|
|
"unique_1" => array("(messageid,listid)",""),
|
|
"index_1" => array("listmessageidx (listid,messageid)",""),
|
|
),
|
|
"rssitem" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"title" => array("varchar(100) not null","Title"),
|
|
"link" => array("varchar(100) not null","Link"),
|
|
"source" => array("varchar(255)",""),
|
|
"list" => array("integer not null",""),
|
|
"index_1" => array("titlelinkidx (title,link)",""),
|
|
"index_2" => array("titleidx (title)",""),
|
|
"index_3" => array("listidx (list)",""),
|
|
"added" => array("datetime",""),
|
|
"processed" => array("mediumint unsigned default 0", "Number Processed"),
|
|
"astext" => array("integer default 0","Sent as text"),
|
|
"ashtml" => array("integer default 0","Sent as HTML"),
|
|
),
|
|
"rssitem_data" => array(
|
|
"itemid" => array("integer not null","rss item id"),
|
|
"tag" => array("varchar(100) not null",""),
|
|
"primary key" => array("(itemid,tag)",""),
|
|
"data" => array("text","")
|
|
),
|
|
"rssitem_user" => array(
|
|
"itemid" => array("integer not null","rss item id"),
|
|
"userid" => array("integer not null","user id"),
|
|
"entered" => array("timestamp", "Entered"),
|
|
"primary key" => array("(itemid,userid)","")
|
|
),
|
|
"user_rss" => array(
|
|
"userid" => array("integer not null primary key","user id"),
|
|
"last" => array("datetime", "Last time this user was sent something")
|
|
),
|
|
"message_attachment" => array( # attachments for a message
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"messageid" => array("integer not null","Message ID"),
|
|
"attachmentid" => array("integer not null","Attachment ID"),
|
|
"index_1" => array("messageidx (messageid)",""),
|
|
"index_2" => array("messageattidx (messageid,attachmentid)",""),
|
|
),
|
|
"attachment" => array (
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"filename" => array("varchar(255)","file"),
|
|
"remotefile" => array("varchar(255)","The original location on the uploader machine"),
|
|
"mimetype" => array("varchar(255)","The type of attachment"),
|
|
"description" => array("text","Description"),
|
|
"size" => array("integer","Size of the file")
|
|
),
|
|
"usermessage" => array ( # linking messages to a user
|
|
#"id" => array("integer not null primary key auto_increment","ID"),
|
|
"messageid" => array("integer not null","Message ID"),
|
|
"userid" => array("integer not null","User ID"),
|
|
"entered" => array("datetime not null", "Entered"),
|
|
"viewed" => array("datetime","When viewed"),
|
|
"status" => array("varchar(255)","Status of message"),
|
|
"primary key" => array("(userid,messageid)","Primary key"),
|
|
"index_1" => array("messageidindex (messageid)",""),
|
|
"index_2" => array("useridindex (userid)",""),
|
|
"index_3" => array("enteredindex (entered)",""),
|
|
),
|
|
"sendprocess" => array( # keep track of running send processes to avoid to many running concurrently
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"started" => array("datetime", "Start Time"),
|
|
"modified" => array("timestamp","Modified"),
|
|
"alive" => array("integer default 1","Is this process still alive?"),
|
|
"ipaddress" => array("varchar(50)","IP Address of who started it"),
|
|
"page" => array("varchar(100)","The page that this process runs in")
|
|
),
|
|
"template" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"title" => array("varchar(255) not null","Title"),
|
|
"template" => array("longblob","The template"),
|
|
"listorder" => array("integer",""),
|
|
"unique_1" => array("(title)","")
|
|
),
|
|
"templateimage" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"template" => array("integer not null default 0","id of template"),
|
|
"mimetype" => array("varchar(100)","Mime Type"),
|
|
"filename" => array("varchar(100)","Filename"),
|
|
"data" => array("longblob","The image"),
|
|
"width" => array("integer",""),
|
|
"height" => array("integer",""),
|
|
"index_1" => array("templateidx (template)",""),
|
|
),
|
|
"bounce" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"date" => array("datetime","Date received"),
|
|
"header" => array("text","Header of bounce"),
|
|
"data" => array("blob","The bounce"),
|
|
"status" => array("varchar(255)","Status of this bounce"),
|
|
"comment" => array("text","System Comment"),
|
|
"index_1" => array("dateindex (date)",""),
|
|
"index_2" => array("statusindex (status(10))",""),
|
|
),
|
|
"user_message_bounce" => array( # bounce. We can have one usermessage bounce multiple times
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"user" => array("integer not null","User ID"),
|
|
"message" => array("integer not null","Message ID"),
|
|
"bounce" => array("integer not null","Bounce ID"),
|
|
"time" => array("timestamp","When did it bounce"),
|
|
"index_1" => array("umbindex (user,message,bounce)","index"),
|
|
"index_2" => array("useridx (user)","index"),
|
|
"index_3" => array("msgidx (message)","index"),
|
|
"index_4" => array("bounceidx (bounce)","index"),
|
|
),
|
|
"user_message_forward" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"user" => array("integer not null","User ID"),
|
|
"message" => array("integer not null","Message ID"),
|
|
"forward" => array("varchar(255)","Forward email"),
|
|
"status" => array("varchar(255)","Status of forward"),
|
|
"time" => array("timestamp","When was it forwarded"),
|
|
"index_1" => array("usermessageidx (user,message)","index"),
|
|
"index_2" => array("useridx (user)","index"),
|
|
"index_3" => array("messageidx (message)","index"),
|
|
),
|
|
"config" => array(
|
|
"item" => array("varchar(35) not null primary key","ID"),
|
|
"value" => array("longtext","Value"),
|
|
"editable" => array("tinyint default 1","Editable?"),
|
|
"type" => array("varchar(25)","Type of data"),
|
|
),
|
|
"admin" => array (
|
|
"id" => array("integer not null primary key auto_increment","sys:ID"),
|
|
"loginname" => array("varchar(25) not null","Login Name (max 25 chars)"),
|
|
"namelc" => array("varchar(255)","sys:Normalised loginname"),
|
|
"email" => array("varchar(255) not null","Email"),
|
|
"created" => array("datetime","sys:Time Created"),
|
|
"modified" => array("timestamp","sys:Time modified"),
|
|
"modifiedby" => array("varchar(25)","sys:Modified by"),
|
|
"password" => array("varchar(255)","Password"),
|
|
"passwordchanged" => array("date","sys:Last time password was changed"),
|
|
"superuser" => array("tinyint default 0","Is this user Super Admin?"),
|
|
"disabled" => array("tinyint default 0","Is this account disabled?"),
|
|
"unique_1" => array("(loginname)",""),
|
|
"index_1" => array("loginnameidx (loginname)",""),
|
|
),
|
|
"adminattribute" => array ( # attributes for an admin
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"name" => array("varchar(255) not null","Name"),
|
|
"type" => array("varchar(30)","Type of attribute"),
|
|
"listorder" => array("integer","order of listing"),
|
|
"default_value" => array("varchar(255)","Default value"),
|
|
"required" => array("tinyint","Required for user to fill out"),
|
|
"tablename" => array("varchar(255)","Name of table with values")
|
|
),
|
|
"admin_attribute" => array( # attributes of an admin
|
|
"adminattributeid" => array("integer not null","attribute number"),
|
|
"adminid" => array("integer not null","id of admin"),
|
|
"value" => array("varchar(255)","Value of this attribute for this admin"),
|
|
"primary key" => array("(adminattributeid,adminid)","PKey")
|
|
),
|
|
"task" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"page" => array("varchar(25) not null","Page, page in system"),
|
|
"type" => array("varchar(25) not null","Type: system, list, user, message, admin"),
|
|
"unique_1" => array("(page)",""),
|
|
"index_1" => array("pageidx (page)","index"),
|
|
"index_2" => array("pagetypeidx (page,type)","index"),
|
|
),
|
|
"admin_task" => array(
|
|
"adminid" => array("integer not null","id of admin"),
|
|
"taskid" => array("integer not null","id of task"),
|
|
"level" => array("integer","Level: all,none,view,add,edit,delete,self"),
|
|
"primary key" => array("(adminid,taskid)","PKey")
|
|
),
|
|
"subscribepage" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"title" => array("varchar(255) not null","Title"),
|
|
"active" => array("tinyint default 0",""),
|
|
"owner" => array("integer","Admin who is owner of this page")
|
|
),
|
|
"subscribepage_data" => array(
|
|
"id" => array("integer not null","ID"),
|
|
"name" => array("varchar(100) not null","Name of field"),
|
|
"data" => array("text","data"),
|
|
"primary key" => array("(id,name)","")
|
|
),
|
|
"eventlog" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"entered" => array("datetime",""),
|
|
"page" => array("varchar(100)","page this log was for"),
|
|
"entry" => array("text",""),
|
|
"index_1" => array("enteredidx (entered)","index"),
|
|
"index_2" => array("pageidx (page)","index"),
|
|
),
|
|
"urlcache" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"url" => array("varchar(255) not null",""),
|
|
"lastmodified" => array("integer",""),
|
|
"added" => array("datetime",""),
|
|
"content" => array("mediumtext",""),
|
|
"index_1" => array("urlindex (url)",""),
|
|
),
|
|
"linktrack" => array (
|
|
"linkid" => array("integer not null primary key auto_increment", "Link ID"),
|
|
"messageid" => array("integer not null","Message ID"),
|
|
"userid" => array("integer not null","User ID"),
|
|
"url" => array("varchar(255)", "URL to log"),
|
|
"forward" => array("text","URL to forward to"),
|
|
"firstclick" => array("datetime","When first clicked"),
|
|
"latestclick" => array("timestamp", "When last clicked"),
|
|
"clicked" => array("integer default 0", "Number of clicks"),
|
|
"index_1" => array("midindex (messageid)",""),
|
|
"index_2" => array("uidindex (userid)",""),
|
|
"index_3" => array("urlindex (url)",""),
|
|
"index_4" => array("miduidindex (messageid,userid)",""),
|
|
"index_5" => array("miduidurlindex (messageid,userid,url)",""),
|
|
"unique_1" => array("(messageid,userid,url)","")
|
|
),
|
|
"linktrack_userclick" => array (
|
|
"linkid" => array("integer not null",""),
|
|
"userid" => array("integer not null",""),
|
|
"messageid" => array("integer not null",""),
|
|
"name" => array("varchar(255)","Name of data"),
|
|
"data" => array("text",""),
|
|
"date" => array("datetime",""),
|
|
"index_1" => array("linkindex (linkid)",""),
|
|
"index_2" => array("uidindex (userid)",""),
|
|
"index_3" => array("midindex (messageid)",""),
|
|
"index_4" => array("linkuserindex (linkid,userid)",""),
|
|
"index_5" => array("linkusermessageindex (linkid,userid,messageid)",""),
|
|
),
|
|
"userstats" => array(
|
|
"id" => array("integer not null primary key auto_increment",""),
|
|
"unixdate" => array("integer","date in unix format"),
|
|
"item" => array("varchar(255)",""),
|
|
"listid" => array("integer default 0",""),
|
|
"value" => array("integer default 0",""),
|
|
"index_1" => array("dateindex (unixdate)",""),
|
|
"index_2" => array("itemindex (item)",""),
|
|
"index_3" => array("listindex (listid)",""),
|
|
"index_4" => array("listdateindex (listid,unixdate)",""),
|
|
"unique_1" => array("entry (unixdate,item,listid)",""),
|
|
),
|
|
# session table structure, table is created as the name identified in the config file
|
|
# "sessiontable" => array(
|
|
# "sessionid" => array("CHAR(32) NOT NULL PRIMARY KEY",""),
|
|
# "lastactive" => array("INTEGER NOT NULL",""),
|
|
# "data" => array("LONGTEXT",""),
|
|
# ),
|
|
"bounceregex" => array(
|
|
"id" => array("integer not null primary key auto_increment","ID"),
|
|
"regex" => array("varchar(255)","Regex"),
|
|
"action" => array("varchar(255)","Action on rule"),
|
|
"listorder" => array("integer default 0",""),
|
|
"admin" => array("integer",""),
|
|
"comment" => array("text",""),
|
|
"status" => array("varchar(255)",""),
|
|
"count" => array("integer default 0","Count of matching bounces on this rule"),
|
|
"unique_1" => array("regex (regex)",""),
|
|
),
|
|
"bounceregex_bounce" => array(
|
|
"regex" => array("integer not null","Related regex"),
|
|
"bounce" => array("integer not null","Related bounce"),
|
|
"primary key" => array("(regex,bounce)",""),
|
|
),
|
|
/* "translation" => array(
|
|
"id" => array("integer not null primary key auto_increment",""),
|
|
"translator" => array("varchar(255)","Name of translator"),
|
|
"email" => array("varchar(255)","email of translator"),
|
|
"pass" => array("varchar(255)","encrypted password for translation"),
|
|
"ident" => array("varchar(255)","Translation identifier")
|
|
),
|
|
"translation_data" => array(
|
|
"id" => array("integer not null","Translation ID"),
|
|
"item" => array("varchar(100)","Item to translate"),
|
|
"primary key" => array("(id,item)","")
|
|
)
|
|
*/
|
|
);
|
|
|
|
?>
|