/misc/update.sql

#run clean_db.pl	



#upgrade moderator permissions to new codes

update USER_PERMISSIONS set PERMISSIONS=63 where PERMISSIONS=31;

update USER_PERMISSIONS set PERMISSIONS=31 where PERMISSIONS=15;

update USER_PERMISSIONS set PERMISSIONS=15 where PERMISSIONS=7;

#now run add_permissions.pl



CREATE TABLE sessions (

     id CHAR(32) NOT NULL PRIMARY KEY,

     a_session TEXT NOT NULL

  );

 

 #remove SESSION and SESSIONHISTORY tables

 drop table SESSION;

 drop table SESSIONHISTORY;

  

alter table USER add column  USER_ACTIVATED enum('True','False') not null default 'False';

update USER set USER_ACTIVATED='True';



alter table USER add column  USER_DONATED enum('True','False') not null default 'False';

update USER set USER_DONATED='False';



create table PAYMENT (

	FK_OBJECT_ID	 int unsigned not null unique,

	PAYMENT_TYPE	 enum('User','Advert'),

	PAYMENT_AMOUNT	 float,

	PAYMENT_USERNAME	text,

	PAYMENT_COMMENT 	text,

	primary key (FK_OBJECT_ID)

);



replace into CONTENT_TYPE (PK_CONTENTTYPE_ID,CONTENTTYPE_NAME,CONTENTTYPE_CLASS) values(52,'PAYMENT','WiganRL::DataObject::Payment');

replace into USER_PERMISSIONS (FK_USER_ID,FK_CONTENTTYPE_ID,PERMISSIONS) values(1,52,63);



#upload WiganRL_AdSense.pl and setup a daily cron job to run it



create table VIEWTOPIC (

	FK_OBJECT_ID	 int unsigned not null unique,

	FK_HEADPOST_ID	 int unsigned,

	FK_LASTPOST_ID	 int unsigned,

	FK_VIEWFORUM_ID	 int unsigned,

	VIEWTOPIC_TYPE	 enum('Normal','Sticky','Announcement') default 'Normal',

	VIEWTOPIC_LOCKED  enum('True','False') default 'False',

	VIEWTOPIC_NREPLIES	 int default 0,

	primary key (FK_OBJECT_ID),

	index FK_VIEWFORUM_ID (FK_VIEWFORUM_ID)

);



replace into CONTENT_TYPE (PK_CONTENTTYPE_ID,CONTENTTYPE_NAME,CONTENTTYPE_CLASS) values(53,'VIEWTOPIC','WiganRL::DataObject::Viewtopic');

insert into USER_PERMISSIONS (FK_USER_ID,FK_CONTENTTYPE_ID,PERMISSIONS) values(1,53,31);



alter table VIEW add column FK_VIEWTOPIC_ID int unsigned;

alter table VIEW add index FK_VIEWTOPIC_ID (FK_VIEWTOPIC_ID);



alter table VIEWFORUM add column FK_LASTPOST_ID int unsigned;

alter table VIEWFORUM add column VIEWFORUM_NPOSTS int unsigned default 0;

alter table VIEWFORUM add column VIEWFORUM_NTHREADS int unsigned default 0;



# now run update_viewtopic.pl and 

# drop table VIEW_THREAD

# if you are feeling brave



# not required

alter table OBJECT drop column OBJECT_DEPTH;



#marking read content

create table MARKED (

	FK_USER_ID	 int unsigned not null,

	FK_CONTENTTYPE_ID	 int unsigned not null,

	FK_OBJECT_ID	 int unsigned default null,

	MARK_TIME	 timestamp,

	index USER (FK_USER_ID,FK_CONTENTTYPE_ID,FK_OBJECT_ID),

	index USER1 (FK_USER_ID,FK_CONTENTTYPE_ID),

	index USER2 (FK_USER_ID)

);



# not needed

alter table VIEWFORUM drop column VIEWFORUM_NTHREADS;

alter table VIEW drop column VIEW_STICKY;



alter table USER change column USER_MSNM USER_MSN text;



create table PRIVATEMESSAGE (

    FK_OBJECT_ID    int unsigned not null unique,

    FK_TO_ID	 int unsigned not null,

    PRIVATEMESSAGE_TITLE   text,

    PRIVATEMESSAGE_BODY    text,

    PRIVATEMESSAGE_DELIVERED  enum('New','True','False') default 'New',

    primary key (FK_OBJECT_ID),

    index FK_TO_ID (FK_TO_ID),

    index PRIVATEMESSAGE_DELIVERED (PRIVATEMESSAGE_DELIVERED)

);



replace into CONTENT_TYPE (PK_CONTENTTYPE_ID,CONTENTTYPE_NAME,CONTENTTYPE_CLASS) values(54,'PRIVATEMESSAGE','WiganRL::DataObject::Privatemessage');

insert into USER_PERMISSIONS (FK_USER_ID,FK_CONTENTTYPE_ID,PERMISSIONS) values(1,54,31);





# now update all the users so they have permissions to send pms





alter table USER add column USER_ALLOWEMAIL enum('True','False') default 'True';

alter table USER add column USER_PMEMAILNOTIFY enum('True','False') default 'True';

alter table USER add column USER_PMPOPUPNOTIFY enum('True','False') default 'True';





#shiftone user contentrecordto another page

update USER_CONTENT set USERCONTENT_TYPE='Club.History' where USERCONTENT_TYPE='History';





alter table USER add column USER_WARN_NUMBER int unsigned default 0;

alter table USER add column USER_WARN_DATE datetime;

alter table USER add column USER_WARN_MODERATOR	int unsigned default null;



#get rid of view watches

delete from CONTENT_TYPE where CONTENTTYPE_NAME='Viewwatch';

drop table VIEWWATCH;



create table SUBSCRIPTION (

    PK_SUBSCRIPTION_ID  int unsigned not null auto_increment,

    FK_OBJECT_ID    int unsigned,

    FK_CONTENTTYPE_ID  int unsigned,

    FK_USER_ID int unsigned,

    SUBSCRIPTION_SENT enum('True','False') default 'False',

    primary key (PK_SUBSCRIPTION_ID),

    index FK_OBJECT_ID (FK_OBJECT_ID),

    index FK_CONTENTTYPE_ID (FK_CONTENTTYPE_ID),

    index FK_USER_ID (FK_USER_ID)

);



alter table MATCHSTATS add column MATCHSTATS_NPOSITIONS int unsigned default 0;



create table CONTENT_HISTORY (

  PK_CH_ID int unsigned not null auto_increment,

  FK_OBJECT_ID int unsigned not null,

  FK_USER_ID int unsigned not null,

  CONTENTHISTORY_DATE datetime,

  CONTENTHISTORY_RESTORE text,

  primary key (PK_CH_ID),

  index FK_OBJECT_ID (FK_OBJECT_ID)

  );



drop table CONTENTTYPE_LISTINFO;

create table CONTENTTYPE_LISTINFO (

  FK_CONTENTTYPE_ID int unsigned not null,

  CONTENTTYPE_PAGEIDS text,

  CONTENTTYPE_COUNT int unsigned,

  CONTENTTYPE_WHERE varchar(255),

  primary key (FK_CONTENTTYPE_ID,CONTENTTYPE_WHERE)

 );



create table CONTENTTYPE_LISTINFO (

  FK_CONTENTTYPE_ID int unsigned not null,

  CONTENTTYPE_PAGEIDS text,

  CONTENTTYPE_COUNT int unsigned,

  CONTENTTYPE_WHERE varchar(255),

  primary key (FK_CONTENTTYPE_ID)

 );





 #20080309



 alter table VIEW add column VIEW_SIGNATURE text;





 #20080906



 create table PAGECACHE (

	PK_PAGECACHE_ID		int unsigned auto_increment,

	PAGECACHE_STATUS	 varchar(255),

	PAGECACHE_UNIQUEFIELDS	 varchar(255),

	PAGECACHE_TEMPLATE	 varchar(255),

	PAGECACHE_TEMPLATEDIR	 varchar(255),

	PAGECACHE_CONTENT	 text,

	PAGECACHE_RMCONTENT	 text,

	PAGECACHE_TITLE		 text,

	PAGECACHE_RMTITLE	 text,

	PAGECACHE_DATESTAMP	 timestamp,

	primary key (PK_PAGECACHE_ID)

#	primary key (PAGECACHE_STATUS,PAGECACHE_UNIQUEFIELDS,PAGECACHE_TEMPLATEDIR)

) engine=MyISAM;	





#20081019



create table USER_BLOCKLIST (

	USERBLOCKLIST_EMAIL	varchar(255),

	primary key (USERBLOCKLIST_EMAIL)

);



alter table OBJECT add column OBJECT_PUBLISHED enum('True','False') not null default 'True';

update OBJECT set OBJECT_PUBLISHED='True';

alter table OBJECT add column OBJECT_CONTEXT text;



alter table PAGECACHE add column PAGECACHE_PAGETITLE varchar(255);

alter table PAGECACHE add column PAGECACHE_PAGEDESCRIPTION varchar(255);

alter table PAGECACHE add column PAGECACHE_PAGEKEYWORDS varchar(255);



#20081105



create table SEARCH_TERMLIST (

	PK_SEARCHTERM_ID 	int unsigned not null auto_increment,

	SEARCH_TERM	 varchar(255),

	SEARCH_TERMFREQUENCY 	int unsigned not null default 0,	

	primary key	 (PK_SEARCHTERM_ID)

);



alter table OBJECT add column OBJECT_INDEXED enum('True','False') not null default 'False';



create table SEARCH_CONTENTINDEX (

	FK_OBJECT_ID	 int unsigned not null,

	SEARCH_CONTENTINDEX 	text,

	primary key (FK_OBJECT_ID)

);



#20090209

 create table VARCACHE (

	VARCACHE_TYPE	 varchar(255),

	VARCACHE_NAME	 varchar(255),

	FK_OBJECT_ID	 int unsigned,

	VARCACHE_CONTENT	 text,

	VARCACHE_DATESTAMP	 timestamp,

	primary key (VARCACHE_TYPE,FK_OBJECT_ID,VARCACHE_NAME),

	index VARCACHE_TYPE (VARCACHE_TYPE,VARCACHE_NAME),

	index VARCACHE_DATESTAMP (VARCACHE_DATESTAMP)

);	





#20090215



drop table PAGECACHE;

 create table PAGECACHE (

	PK_PAGECACHE_ID		int unsigned auto_increment,

	PAGECACHE_STATUS varchar(255),

	PAGECACHE_UNIQUEFIELDS	 varchar(255),

	PAGECACHE_TEMPLATE	 varchar(255),

	PAGECACHE_TEMPLATEDIR	 varchar(255),

	FK_USER_ID	 int unsigned not null default 0,

	PAGECACHE_CONTENT	 longtext,

	PAGECACHE_RMCONTENT	 text,

	PAGECACHE_TITLE		 text,

	PAGECACHE_RMTITLE	 text,

	PAGECACHE_DATESTAMP	 timestamp,

	PAGECACHE_PAGETITLE text,

	PAGECACHE_PAGEDESCRIPTION text,

	PAGECACHE_PAGEKEYWORDS text,

	PAGECACHE_EXPIRY	 datetime,	

	primary key (PK_PAGECACHE_ID)

	#primary key (PAGECACHE_STATUS,PAGECACHE_UNIQUEFIELDS,PAGECACHE_TEMPLATEDIR,FK_USER_ID)

) engine=MyISAM;	





# page list caching



create table CONTENTTYPE_PAGEOBJS (

	FK_CONTENTTYPE_ID   int unsigned,

	CONTENTTYPE_WHERE   varchar(255),

	CONTENTTYPE_PAGEIND  int unsigned,

	CONTENTTYPE_PAGEOBJS text,

	primary key (FK_CONTENTTYPE_ID,CONTENTTYPE_WHERE)

);



#20100425

CREATE TABLE VIEW_Backup SELECT * FROM VIEW;



create table VIEW_OnDemand (

	FK_OBJECT_ID	int unsigned primary key,

    VIEW_BODY   text,

    VIEW_SIGNATURE text

) Engine=MyISAM;



insert into VIEW_OnDemand

	select FK_OBJECT_ID,VIEW_BODY,VIEW_SIGNATURE

	from VIEW;



alter table VIEW drop column VIEW_BODY;

alter table VIEW drop column VIEW_SIGNATURE;





#20120312



alter table PAGECACHE drop primary key;

alter table PAGECACHE add primary key (PAGECACHE_STATUS,PAGECACHE_UNIQUEFIELDS,PAGECACHE_TEMPLATEDIR);

alter table PAGECACHE add index EXPIRY (PAGECACHE_EXPIRY);