History of ReleaseTwo Schema Changelog 2007
! Bitweaver ReleaseTwo Schema changes 2007
This is the first part of the ongoing Schema changes in ReleaseTwo. For more recent changes, please consult SchemaChangeLog
These are ReleaseTwo database changes. __DO NOT USE__ these changes if you are on ReleaseOne. If you need to know the changes for ReleaseOne, see the ReleaseOneSchemaChangelog.
See also:
* ((SchemaChangeLog))
* ((ReleaseTwo Schema Changelog 2007))
* ((ReleaseTwo Schema Changelog 2006))
||~Date|Table|Reason|Who
25-SEP-2007 | liberty_content_data, bit_pages | table to hold normalized content like descriptions, and maybe lc.data one day (index updated on 17-OCT) | [/spider], [/xing]
SQL92|{code source=SQL}
CREATE TABLE liberty_content_data( content_id INT PRIMARY KEY, data_type VARCHAR(32) NOT NULL, data TEXT );
CREATE UNIQUE INDEX liberty_content_data_idx ON liberty_content_data( content_id, data_type);
-- If you previously installed this table from changelog, you need to drop existing constraint
-- ALTER TABLE liberty_content_data DROP CONSTRAINT liberty_content_data_pkey;
ALTER TABLE liberty_content_data ADD CONSTRAINT liberty_content_data_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
ALTER TABLE liberty_content_history ADD summary TEXT;
UPDATE liberty_content_history SET summary=description;
ALTER TABLE liberty_content_history DROP COLUMN description;
INSERT INTO liberty_content_data (content_id,data,data_type) (SELECT content_id, description, 'summary' FROM wiki_pages WHERE description IS NOT NULL);
ALTER TABLE wiki_pages DROP COLUMN description;
{/code}
19-SEP-2007 | articles | remove obsolete image_attachment_id column | [/xing]
SQL92|{code source=SQL}
-- make sure the content is attached to this article
UPDATE liberty_attachments SET content_id = articles.content_id WHERE articles.image_attachment_id = liberty_attachments.attachment_id;
-- copy information from image_attachment_id to liberty_attachments
UPDATE liberty_attachments SET is_primary = 'y' WHERE articles.image_attachment_id = liberty_attachments.attachment_id;
-- ok to drop the column now
ALTER TABLE articles DROP COLUMN image_attachment_id;
{/code}
16-SEP-2007 | liberty_attachments | refactor liberty_attachments by removing the attachment map, and only allow attachments to be connected to one file. This is a detailed update, so be sure to __read the sql comments__ in the update ( lines that being with -- ). | [/spider], [/xing], [/nickpalmer]
SQL92 and PHP|{code source=SQL title="Initial SQL Setup"}
-- Start transactions for real databases
BEGIN;
-- Set up data base with new columns
ALTER TABLE liberty_attachments ADD COLUMN content_id integer;
ALTER TABLE liberty_attachments ADD COLUMN is_primary char(1);
--Finish Transaction
COMMIT;
-- Now, check to see if you have any attachments linked to multiple content. This is not a common thing, so it is normal to have none.
SELECT attachment_id, count(*) FROM liberty_attachments_map GROUP BY attachment_id HAVING COUNT(*) > 1;
-- If this query returns 0 rows, you do not have to run the PHP code. Skip ahead to the next SQL section further down.
{/code}{code source="PHP" title="PHP script for multiple attachments"}
<?php
// You can copy the contents of this script to a file in your bitweaver
// directory and point your browser to it.
// e.g.: copy the contents to /path/to/bitweaver/upgrade.php
// Then point your browser to: http://my.domain.com/upgrade.php
// Only run this script ONCE!
require_once( 'bit_setup_inc.php' );
$gBitSystem->verifyPermission( 'p_admin' );
// get duplicates
$query = "SELECT `attachment_id`, COUNT(*) FROM `".BIT_DB_PREFIX."liberty_attachments_map` GROUP BY `attachment_id` HAVING COUNT(*) > ?";
if( $duplicates = $gBitSystem->mDb->getAll( $query, array( 1 ))) {
// SQL to get attachment information
$get_sql = "
SELECT la.*, lf.*, lam.`content_id`
FROM `".BIT_DB_PREFIX."liberty_attachments` la
INNER JOIN `".BIT_DB_PREFIX."liberty_attachments_map` lam ON( la.`attachment_id` = lam.`attachment_id` )
INNER JOIN `".BIT_DB_PREFIX."liberty_files` lf ON( lf.`file_id` = la.`foreign_id` )
WHERE la.`attachment_id` = ?";
// SQL for liberty_files
$file_sql = "INSERT INTO `".BIT_DB_PREFIX."liberty_files` ( `storage_path`, `file_id`, `mime_type`, `file_size`, `user_id` ) VALUES ( ?, ?, ?, ?, ? )";
// SQL for liberty_attachments
$attachment_sql = "INSERT INTO `".BIT_DB_PREFIX."liberty_attachments` ( `content_id`, `attachment_id`, `attachment_plugin_guid`, `foreign_id`, `user_id` ) VALUES ( ?, ?, ?, ?, ? )";
// SQL for map
$map_sql = "DELETE FROM `".BIT_DB_PREFIX."liberty_attachments_map` WHERE `attachment_id` = ? AND `content_id` = ?";
foreach( $duplicates as $duplicate ) {
$attachment = $gBitSystem->mDb->getRow( $get_sql, array( $duplicate['attachment_id'] ));
// get new values
$attachment["new_attachment_id"] = $gBitSystem->mDb->GenID( "liberty_attachments_id_seq" );
$attachment["new_foreign_id"] = $gBitSystem->mDb->GenID( "liberty_files_id_seq" );
$attachment["new_storage_path"] = LibertyAttachable::getStorageBranch( $attachment["new_attachment_id"], $attachment["user_id"], LibertyAttachable::getStorageSubDirName() ).basename( $attachment['storage_path'] );
// add new entry in liberty_files
echo "Copy attachment: ".$attachment['storage_path'].' --&gt; '.$attachment['new_storage_path'].'
';
if( is_file( BIT_ROOT_PATH.$attachment['storage_path'] )) {
copy_r( dirname( $attachment['storage_path'] ), dirname( $attachment['new_storage_path'] ));
// liberty_attachments
$gBitSystem->mDb->query( $attachment_sql, array( $attachment["content_id"], $attachment["new_attachment_id"], $attachment["attachment_plugin_guid"], $attachment["new_foreign_id"], $attachment["user_id"] ));
// liberty_files
$gBitSystem->mDb->query( $file_sql, array( $attachment["new_storage_path"], $attachment["new_foreign_id"], $attachment["mime_type"], $attachment["file_size"], $attachment["user_id"] ));
}
// remove entry in map
$gBitSystem->mDb->query( $map_sql, array( $attachment["attachment_id"], $attachment["content_id"] ));
}
}
function copy_r( $pSource, $pTarget ) {
if( is_dir( $pSource )) {
@mkdir_p( $pTarget );
$d = dir( $pSource );
while( FALSE !== ( $entry = $d->read() )) {
if( $entry == '.' || $entry == '..' ) {
continue;
}
$Entry = $pSource.'/'.$entry;
if( is_dir( $Entry )) {
copy_r( $Entry, $pTarget.'/'.$entry );
continue;
}
copy( $Entry, $pTarget.'/'.$entry );
}
$d->close();
} else {
copy( $pSource, $pTarget );
}
}
?>
{/code}{code source="SQL" title="Final SQL part"}
-- If you had to run the above PHP code, you can make sure it worked by running this again:
SELECT attachment_id, count(*) FROM liberty_attachments_map GROUP BY attachment_id HAVING COUNT(*) > 1;
-- If you still have duplicates, you can try running the PHP script again. you might have an attachment attached more than twice
BEGIN;
-- Copy the content_id from the attachments map
UPDATE liberty_attachments SET content_id=(SELECT content_id FROM liberty_attachments_map lam WHERE lam.attachment_id=liberty_attachments.attachment_id);
-- if the primary_attachment_id is set then update the is_primary flag
UPDATE liberty_attachments SET is_primary = 'y'
WHERE (attachment_id, content_id) IN
( SELECT lc.primary_attachment_id, lc.content_id
FROM liberty_content lc
WHERE lc.primary_attachment_id IS NOT NULL
);
-- Okay to drop this now.
DROP TABLE liberty_attachments_map;
-- Okay to drop the column now.
ALTER TABLE liberty_content DROP COLUMN primary_attachment_id;
--Finish Transaction
COMMIT;
{/code}
06-JUL-2007 | liberty_content_permissions | is_exlcuded column to handle eventual exlcusion of a permission | [/spider], [/xing]
SQL92|{code source=SQL}
ALTER TABLE liberty_content_permissions ADD is_revoked VARCHAR(1);
-- This was briefly named is_excluded. If you had made that update, us these statements to simply change the column name:
-- SQL92: ALTER TABLE liberty_content_permissions RENAME COLUMN is_excluded TO is_revoked;
-- MYSQL: ALTER TABLE liberty_content_permissions CHANGE is_excluded is_revoked VARCHAR(1);
{/code}
30-JUN-2007 | liberty_content | update primary_attachment_id that treasury will still work | [/xing]
SQL92|{code source=SQL}
UPDATE liberty_content SET primary_attachment_id=( SELECT attachment_id FROM liberty_attachments_map lam INNER JOIN liberty_content lc ON ( lc.content_id=lam.content_id ) WHERE lc.content_id=liberty_content.content_id AND lc.content_type_guid='treasuryitem' );
UPDATE liberty_attachments SET attachment_plugin_guid = 'bitfile' WHERE attachment_plugin_guid = 'treasury';
{/code}
22-JUN-2007 | events | rename count column as it is a reserved word | [/lsces]
SQL92|{code source=SQL}
ALTER TABLE events RENAME COLUMN count TO r_count;
-- MYSQL use this instead:
ALTER TABLE events CHANGE count r_count int;
{/code}
21-JUN-2007 | kernel | add session table for ADOdb session managment | [/lsces]
SQL92|{code source=SQL}
CREATE TABLE sessions (
sesskey varchar(32) NOT NULL,
expiry integer NOT NULL,
expireref varchar(32),
session_data text,
primary key (sesskey)
);
{/code}
19-JUN-2007 | LibertyPackage | add a content connection map to liberty | [/xing]
SQL92|{code source=SQL}
-- please note that the item_postion column was changed from int to float on the 20-JUN-2007
CREATE TABLE liberty_content_connection_map (
from_content_id integer NOT NULL,
to_content_id integer NOT NULL,
item_position float
);
-- now we can add some constraints
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_content_connection_map_pkey PRIMARY KEY (from_content_id, to_content_id);
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_from_content_id_ref FOREIGN KEY (from_content_id) REFERENCES liberty_content(content_id);
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_to_content_id_ref FOREIGN KEY (to_content_id) REFERENCES liberty_content(content_id);
{/code}
14-JUN-2007 | LibertyPackage | move fisheye_exif_data to liberty_meta_data | [/xing]
SQL92|{code source=SQL}
-- you can alter the existing table if you have it
ALTER TABLE fisheye_exif_data RENAME TO liberty_meta_data;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_name TO meta_name;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_value_short TO meta_value_short;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_value_long TO meta_value_long;
-- MYSQL users use these three to rename the columns
ALTER TABLE liberty_meta_data CHANGE exif_name meta_name VARCHAR(250) NOT NULL;
ALTER TABLE liberty_meta_data CHANGE exif_value_short meta_value_short VARCHAR(250);
ALTER TABLE liberty_meta_data CHANGE exif_value_long meta_value_long TEXT;
-- If you don't have the liberty_meta_data table to work with, this is how you add it
CREATE TABLE liberty_meta_data (
meta_type_guid character varying(16) NOT NULL,
meta_key character varying(250) NOT NULL,
meta_title character varying(250) NOT NULL,
meta_value_short character varying(250),
meta_value_long text
);
CREATE TABLE liberty_meta_content_map (
content_id integer NOT NULL,
meta_key character varying(250) NOT NULL
);
CREATE TABLE liberty_meta_types (
meta_type_guid character varying(16) NOT NULL,
meta_type_title character varying(250)
);
-- and the constraint
ALTER TABLE liberty_meta_content_map ADD CONSTRAINT liberty_meta_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
{/code}
14-JUN-2007 | EventsPackage | Add events_invites table for managing invites and add a few indexes for speed. | [/nickpalmer]
SQL92|{code source=SQL}
CREATE TABLE events_invites ( invites_id INT4 NOT NULL, content_id INT4 NOT NULL, event_content_id INT4 NOT NULL, user_id INT4 NOT NULL, interest INT4 NOT NULL, guests INT4 , CONSTRAINT "events_users_content_ref" FOREIGN KEY ("content_id") REFERENCES "liberty_content"( "content_id") , CONSTRAINT "events_users_event_content_ref" FOREIGN KEY ("event_content_id") REFERENCES "liberty_content"( "content_id") , PRIMARY KEY (invites_id) );
CREATE INDEX events_events_location_idx ON events (location_id);
CREATE INDEX events_invites_event_idx ON events_invites (event_content_id);
CREATE INDEX events_invites_user_idx ON events_invites (user_id);
CREATE SEQUENCE events_invites_id_seq START 1;
{/code}
14-JUN-2007 | LibertyPackage | Initilalise 'common_storage_plugin' | [/lsces]
SQL92|{code source=SQL}INSERT INTO kernel_config VALUES ( 'common_storage_plugin', 'liberty', 'bitfile' ){/code}
14-JUN-2007 | EventsPackage | interval is reserved on MySQL so needs changing | [/lsces]
SQL92|{code source=SQL}
ALTER TABLE events RENAME COLUMN interval TO event_interval;
{/code}
13-JUN-2007 | LibertyPackage | add order column to libert_attachment_map | [/lsces]
ADODB |Later a unique index would be useful on attachement_id, item_position but is probably going to be database specific.{code source=SQL}
ALTER TABLE liberty_attachments_map ADD item_position INT;
{/code}
12-JUN-2007 | LibertyPackage | add primary_attachment_id to directly associate an attachment with content | [/xing], [/nickpalmer]
SQL92 | {code source=SQL}
ALTER TABLE liberty_content ADD COLUMN primary_attachment_id int;
ALTER TABLE liberty_content ADD CONSTRAINT liberty_content_attachment_ref FOREIGN KEY (primary_attachment_id) REFERENCES liberty_attachments (attachment_id);
{/code}
10-JUN-2007 | EventsPackage | massive update to prepare for new functionality | [/nickpalmer]
SQL92 | {code source=SQL}
CREATE TABLE events_types (
type_id int,
name varchar(30) NOT NULL,
description varchar(160)
);
CREATE SEQUENCE events_types_id_seq;
CREATE UNIQUE INDEX events_type_id_idx ON events_types(type_id);
ALTER TABLE events ADD COLUMN cost varchar(160);
ALTER TABLE events ADD COLUMN type_id int;
ALTER TABLE events ADD COLUMN location_id int;
ALTER TABLE events ADD COLUMN frequency int;
ALTER TABLE events ADD COLUMN interval int;
ALTER TABLE events ADD COLUMN count int;
ALTER TABLE events ADD COLUMN end_date int;
ALTER TABLE events ADD COLUMN bylists text;
ALTER TABLE events ADD CONSTRAINT events_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
ALTER TABLE events ADD CONSTRAINT events_type_ref FOREIGN KEY (type_id) REFERENCES events_types (type_id);
CREATE TABLE events_on (
content_id int,
event_on int
);
ALTER TABLE events_on ADD CONSTRAINT events_on_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
CREATE UNIQUE INDEX events_on_id_idx ON events_on(content_id);
CREATE INDEX event_on_on_idx ON events_on(event_on);
{/code}
01-JUN-2007 | WikiPackage | remove unused columns | [/xing]
SQL92 | {code source=SQL}
ALTER TABLE wiki_pages DROP COLUMN page_cache;
ALTER TABLE wiki_pages DROP COLUMN wiki_cache;
ALTER TABLE wiki_pages DROP COLUMN cache_timestamp;
ALTER TABLE wiki_pages DROP COLUMN points;
ALTER TABLE wiki_pages DROP COLUMN votes;
ALTER TABLE wiki_pages DROP COLUMN page_rank;
{/code}
04-MAY-2007 | NewslettersPackage | add mail_clickthrough and other track | [/spider]
SQL92 | {code source=SQL}
CREATE TABLE mail_clickthrough ( user_id INT NOT NULL, content_id INT NOT NULL, clicked_url VARCHAR(250) NOT NULL, clicks INT, CONSTRAINT mail_clickthrough_user_ref FOREIGN KEY (user_id) REFERENCES users_users (user_id), CONSTRAINT mail_clickthrough_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id) );
ALTER TABLE mail_queue ADD COLUMN last_read_ip VARCHAR(40);
{/code}
13-APR-2007 (A Friday!)| liberty | split liberty_attachments into two tables | [/nickpalmer]
SQL92 | {code source=SQL}
-- Create The Table
CREATE TABLE liberty_attachments_map (
attachment_id INT NOT NULL,
content_id INT NOT NULL
);
-- MYSQL users comment out the next three lines. (And get a REAL database. ;)
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_unique UNIQUE (attachment_id, content_id);
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_attachment_ref FOREIGN KEY (attachment_id) REFERENCES liberty_attachments (attachment_id);
-- Copy the data over
INSERT INTO liberty_attachments_map (attachment_id, content_id) (SELECT attachment_id, content_id FROM liberty_attachments);
-- Drop the old column
ALTER TABLE liberty_attachments DROP COLUMN content_id;
{/code}
5-APR-2007 | events | add new cols for event end time. multi-day events not yet supported | [/nickpalmer]
SQL92 | {code source=SQL}
ALTER TABLE events ADD end_time INT;
{/code}
02-APR-2007 | themes tables | simplify themes schema | [/xing]
SQL92 | {code source=SQL}
-- see comment at bottom of page for MySQL
-- rename old themes_layouts table to a temporary table name
ALTER TABLE themes_layouts RENAME to themes_layouts_original;
-- create new themes_layouts table
-- module_id is PRIMARY - constraint added below
CREATE TABLE themes_layouts (
module_id integer NOT NULL,
title VARCHAR(255),
layout VARCHAR(160) DEFAULT 'kernel' NOT NULL,
layout_area character varying(1) NOT NULL,
module_rows integer,
module_rsrc character varying(250) NOT NULL,
params character varying(255),
cache_time bigint,
groups character varying(255),
pos integer DEFAULT 1 NOT NULL
);
-- The following only works on real databases. MySQL does count for a plethora of reasons. If someone comes up with the mysql hackish way to do these things, please add it.
-- create PRIMARY constraint
ALTER TABLE themes_layouts ADD CONSTRAINT themes_layouts_pkey PRIMARY KEY (module_id);
-- merge data from old layout tables into new table
CREATE SEQUENCE themes_layouts_module_id_seq;
INSERT INTO themes_layouts ( module_id, title, layout, layout_area, module_rows, module_rsrc, params, cache_time, groups, pos )
SELECT NEXTVAL('themes_layouts_module_id_seq'), tlm.title, tl.layout, tl.layout_position, tlm.module_rows, tmm.module_rsrc, tlm.params, tlm.cache_time, tlm.groups, tl.ord
FROM themes_layouts_modules tlm, themes_layouts_original tl, themes_module_map tmm
WHERE tlm.module_id=tl.module_id AND tmm.module_id=tlm.module_id AND user_id=1;
-- It should be ok to drop these tables now
-- DROP TABLE themes_layouts_original; DROPing this table will destroy and user created layouts. Stay tuned for an upcoming UserhomePackage
-- DROP TABLE themes_layouts_modules;
-- DROP TABLE themes_module_map;
{/code}
26-MAR-2007 | blogs_posts_map | add field for crossposting note | [/Will]
SQL92 | {code source=SQL}
ALTER TABLE blogs_posts_map ADD crosspost_note TEXT;
{/code}
19-MAR-2007 | liberty_content_permissions, users_object_permissions | move users_object_permissions to liberty_content_permissions | [/spiderr]
SQL92 | {code source=SQL}
ALTER TABLE users_object_permissions RENAME to liberty_content_permissions;
ALTER TABLE liberty_content_permissions RENAME object_id TO content_id;
-- MySQL users replace above query with: ALTER TABLE liberty_content_permissions CHANGE object_id content_id INT;
ALTER TABLE liberty_content_permissions ADD CONSTRAINT liberty_content_perm_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
-- If you are running an old/tikiwiki upgraded bw, youmight need to clean up the schema as follows:
ALTER TABLE liberty_content_permissions DROP objectId;
ALTER TABLE liberty_content_permissions DROP object_type;
ALTER TABLE liberty_content_permissions ADD PRIMARY KEY pri_key (perm_name,group_id,content_id);
ALTER TABLE liberty_content_permissions DROP PRIMARY KEY;
ALTER TABLE liberty_content_permissions ADD PRIMARY KEY pri_key (perm_name,group_id,content_id);
{/code}
9-MAR-2007 | blog_posts, blogs_posts_map | add blogs posts normalization | [/spiderr], [/Will]
SQL92 | {code source=SQL}
CREATE TABLE blogs_posts_map ( post_content_id INT NOT NULL, blog_content_id INT NOT NULL, date_added INT );
-- Might be necessary for postgres, etc. so CONSTRAINTS work.
CREATE UNIQUE INDEX blog_posts_content_idx ON blog_posts(content_id);
CREATE UNIQUE INDEX blogs_content_idx ON blogs(content_id);
ALTER TABLE blogs_posts_map ADD CONSTRAINT blogs_posts_map_post_ref FOREIGN KEY (post_content_id) REFERENCES blog_posts (content_id);
ALTER TABLE blogs_posts_map ADD CONSTRAINT blogs_posts_map_blog_ref FOREIGN KEY (blog_content_id) REFERENCES blogs (content_id);
INSERT INTO blogs_posts_map (post_content_id,blog_content_id,date_added) (SELECT bp.content_id,b.content_id,lc.created FROM blog_posts bp INNER JOIN blogs b ON(bp.blog_id=b.blog_id) INNER JOIN liberty_content lc ON(bp.content_id=lc.content_id) );
ALTER TABLE blog_posts DROP COLUMN blog_id;
{/code}
9-MAR-2007 | boards | add migrate_board_id to allow phpbb redirects | [/spiderr]
SQL92 | {code source=SQL}
ALTER TABLE boards ADD COLUMN migrate_board_id INT;
{/code}
9-MAR-2007 | liberty | change values for some content_status_id | [/nickpalmer]
SQL92 | {code source=SQL}
INSERT INTO liberty_content_status (content_status_id, content_status_name) VALUES (-5, 'New Draft');
UPDATE liberty_content SET content_status_id = -5 WHERE content_status_id = 10;
DELETE FROM liberty_content_status where content_status_name = 'Draft';
UPDATE liberty_content_status set content_status_name = 'Draft' WHERE content_status_name = 'New Draft';
INSERT INTO liberty_content_status (content_status_id, content_status_name) VALUES (-1, 'New Pending Approval');
UPDATE liberty_content SET content_status_id = -1 WHERE content_status_id = 101;
DELETE FROM liberty_content_status where content_status_name = 'Pending Approval';
UPDATE liberty_content_status set content_status_name = 'Pending Approval' WHERE content_status_name = 'New Pending Approval';
{/code}
3-MAR-2007 | blog_posts | add permissions for viewing future dated and expired posts | [/wjames5]
SQL92 | {code source=SQL}
INSERT INTO users_permissions( perm_name,perm_desc, perm_level, package ) VALUES( 'p_blogs_posts_read_future', 'Can view future dated posts', 'editors', 'blogs' );
INSERT INTO users_permissions( perm_name,perm_desc, perm_level, package ) VALUES( 'p_blogs_posts_read_expired', 'Can view expired posts', 'editors', 'blogs' );
{/code}
2-MAR-2007 | blog_posts | add new cols for future dating posts and setting expiration date | [/wjames5]
SQL92 | {code source=SQL}
ALTER TABLE blog_posts ADD publish_date INT;
ALTER TABLE blog_posts ADD expire_date INT;
{/code}
1-MAR-2007 | liberty_process_queue | add new table for background processing queues | [/xing]
SQL92 | {code source=SQL}
CREATE TABLE liberty_process_queue (
process_id serial NOT NULL,
content_id integer NOT NULL,
queue_date int8 NOT NULL,
begin_date int8,
end_date int8,
process_status character varying(64),
log_message text,
processor character varying(250),
processor_parameters text
);
-- MYSQL Version: CREATE TABLE liberty_process_queue ( process_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, content_id integer NOT NULL, queue_date int8 NOT NULL, begin_date INT8, end_date INT8, process_status character varying(64), log_message text, processor character varying(250), processor_parameters text );
{/code}
26-FEB-2007 | liberty_action_log | reset liberty_action_logs column properties | [/xing]
SQL92 | {code source=SQL}
ALTER TABLE liberty_action_log RENAME log_message TO temp_message;
ALTER TABLE liberty_action_log ADD COLUMN log_message VARCHAR(250) NOT NULL DEFAULT '';
UPDATE liberty_action_log set log_message = temp_message;
ALTER TABLE liberty_action_log DROP COLUMN temp_message;
ALTER TABLE liberty_action_log RENAME error_message TO temp_error;
ALTER TABLE liberty_action_log ADD COLUMN error_message VARCHAR(250) NOT NULL DEFAULT '';
UPDATE liberty_action_log set temp_error = '' WHERE temp_error IS NULL;
UPDATE liberty_action_log set error_message = temp_error;
ALTER TABLE liberty_action_log DROP COLUMN temp_error;
{/code}
16-FEB-2007 | liberty_thumbnail_queue | move liberty_thumbnail_queue from fisheye schema_inc.php to liberty, and add processor and processor_parameters columns to liberty_thumbnail_queue for future flexibility | [/spider]
SQL92 | {code source=SQL}
ALTER TABLE liberty_thumbnail_queue ADD processor VARCHAR(250);
ALTER TABLE liberty_thumbnail_queue ADD processor_parameters VARCHAR(250);
{/code}
16-JAN-2007 | kernel_config | update config names to tie in with the rest of bitweaver | [/xing]
SQL92 | {code source=SQL}
UPDATE kernel_config SET config_name='i18n_browser_languages' WHERE config_name='browser_languages';
UPDATE kernel_config SET config_name='i18n_interactive_translation' WHERE config_name='interactive_translation';
UPDATE kernel_config SET config_name='i18n_interactive_bittranslation' WHERE config_name='interactive_bittranslation';
UPDATE kernel_config SET config_name='i18n_record_untranslated' WHERE config_name='languages_record_untranslated';
UPDATE kernel_config SET config_name='i18n_track_translation_usage' WHERE config_name='track_translation_usage';
{/code}
||
This is the first part of the ongoing Schema changes in ReleaseTwo. For more recent changes, please consult SchemaChangeLog
These are ReleaseTwo database changes. __DO NOT USE__ these changes if you are on ReleaseOne. If you need to know the changes for ReleaseOne, see the ReleaseOneSchemaChangelog.
See also:
* ((SchemaChangeLog))
* ((ReleaseTwo Schema Changelog 2007))
* ((ReleaseTwo Schema Changelog 2006))
||~Date|Table|Reason|Who
25-SEP-2007 | liberty_content_data, bit_pages | table to hold normalized content like descriptions, and maybe lc.data one day (index updated on 17-OCT) | [/spider], [/xing]
SQL92|{code source=SQL}
CREATE TABLE liberty_content_data( content_id INT PRIMARY KEY, data_type VARCHAR(32) NOT NULL, data TEXT );
CREATE UNIQUE INDEX liberty_content_data_idx ON liberty_content_data( content_id, data_type);
-- If you previously installed this table from changelog, you need to drop existing constraint
-- ALTER TABLE liberty_content_data DROP CONSTRAINT liberty_content_data_pkey;
ALTER TABLE liberty_content_data ADD CONSTRAINT liberty_content_data_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
ALTER TABLE liberty_content_history ADD summary TEXT;
UPDATE liberty_content_history SET summary=description;
ALTER TABLE liberty_content_history DROP COLUMN description;
INSERT INTO liberty_content_data (content_id,data,data_type) (SELECT content_id, description, 'summary' FROM wiki_pages WHERE description IS NOT NULL);
ALTER TABLE wiki_pages DROP COLUMN description;
{/code}
19-SEP-2007 | articles | remove obsolete image_attachment_id column | [/xing]
SQL92|{code source=SQL}
-- make sure the content is attached to this article
UPDATE liberty_attachments SET content_id = articles.content_id WHERE articles.image_attachment_id = liberty_attachments.attachment_id;
-- copy information from image_attachment_id to liberty_attachments
UPDATE liberty_attachments SET is_primary = 'y' WHERE articles.image_attachment_id = liberty_attachments.attachment_id;
-- ok to drop the column now
ALTER TABLE articles DROP COLUMN image_attachment_id;
{/code}
16-SEP-2007 | liberty_attachments | refactor liberty_attachments by removing the attachment map, and only allow attachments to be connected to one file. This is a detailed update, so be sure to __read the sql comments__ in the update ( lines that being with -- ). | [/spider], [/xing], [/nickpalmer]
SQL92 and PHP|{code source=SQL title="Initial SQL Setup"}
-- Start transactions for real databases
BEGIN;
-- Set up data base with new columns
ALTER TABLE liberty_attachments ADD COLUMN content_id integer;
ALTER TABLE liberty_attachments ADD COLUMN is_primary char(1);
--Finish Transaction
COMMIT;
-- Now, check to see if you have any attachments linked to multiple content. This is not a common thing, so it is normal to have none.
SELECT attachment_id, count(*) FROM liberty_attachments_map GROUP BY attachment_id HAVING COUNT(*) > 1;
-- If this query returns 0 rows, you do not have to run the PHP code. Skip ahead to the next SQL section further down.
{/code}{code source="PHP" title="PHP script for multiple attachments"}
<?php
// You can copy the contents of this script to a file in your bitweaver
// directory and point your browser to it.
// e.g.: copy the contents to /path/to/bitweaver/upgrade.php
// Then point your browser to: http://my.domain.com/upgrade.php
// Only run this script ONCE!
require_once( 'bit_setup_inc.php' );
$gBitSystem->verifyPermission( 'p_admin' );
// get duplicates
$query = "SELECT `attachment_id`, COUNT(*) FROM `".BIT_DB_PREFIX."liberty_attachments_map` GROUP BY `attachment_id` HAVING COUNT(*) > ?";
if( $duplicates = $gBitSystem->mDb->getAll( $query, array( 1 ))) {
// SQL to get attachment information
$get_sql = "
SELECT la.*, lf.*, lam.`content_id`
FROM `".BIT_DB_PREFIX."liberty_attachments` la
INNER JOIN `".BIT_DB_PREFIX."liberty_attachments_map` lam ON( la.`attachment_id` = lam.`attachment_id` )
INNER JOIN `".BIT_DB_PREFIX."liberty_files` lf ON( lf.`file_id` = la.`foreign_id` )
WHERE la.`attachment_id` = ?";
// SQL for liberty_files
$file_sql = "INSERT INTO `".BIT_DB_PREFIX."liberty_files` ( `storage_path`, `file_id`, `mime_type`, `file_size`, `user_id` ) VALUES ( ?, ?, ?, ?, ? )";
// SQL for liberty_attachments
$attachment_sql = "INSERT INTO `".BIT_DB_PREFIX."liberty_attachments` ( `content_id`, `attachment_id`, `attachment_plugin_guid`, `foreign_id`, `user_id` ) VALUES ( ?, ?, ?, ?, ? )";
// SQL for map
$map_sql = "DELETE FROM `".BIT_DB_PREFIX."liberty_attachments_map` WHERE `attachment_id` = ? AND `content_id` = ?";
foreach( $duplicates as $duplicate ) {
$attachment = $gBitSystem->mDb->getRow( $get_sql, array( $duplicate['attachment_id'] ));
// get new values
$attachment["new_attachment_id"] = $gBitSystem->mDb->GenID( "liberty_attachments_id_seq" );
$attachment["new_foreign_id"] = $gBitSystem->mDb->GenID( "liberty_files_id_seq" );
$attachment["new_storage_path"] = LibertyAttachable::getStorageBranch( $attachment["new_attachment_id"], $attachment["user_id"], LibertyAttachable::getStorageSubDirName() ).basename( $attachment['storage_path'] );
// add new entry in liberty_files
echo "Copy attachment: ".$attachment['storage_path'].' --&gt; '.$attachment['new_storage_path'].'
';
if( is_file( BIT_ROOT_PATH.$attachment['storage_path'] )) {
copy_r( dirname( $attachment['storage_path'] ), dirname( $attachment['new_storage_path'] ));
// liberty_attachments
$gBitSystem->mDb->query( $attachment_sql, array( $attachment["content_id"], $attachment["new_attachment_id"], $attachment["attachment_plugin_guid"], $attachment["new_foreign_id"], $attachment["user_id"] ));
// liberty_files
$gBitSystem->mDb->query( $file_sql, array( $attachment["new_storage_path"], $attachment["new_foreign_id"], $attachment["mime_type"], $attachment["file_size"], $attachment["user_id"] ));
}
// remove entry in map
$gBitSystem->mDb->query( $map_sql, array( $attachment["attachment_id"], $attachment["content_id"] ));
}
}
function copy_r( $pSource, $pTarget ) {
if( is_dir( $pSource )) {
@mkdir_p( $pTarget );
$d = dir( $pSource );
while( FALSE !== ( $entry = $d->read() )) {
if( $entry == '.' || $entry == '..' ) {
continue;
}
$Entry = $pSource.'/'.$entry;
if( is_dir( $Entry )) {
copy_r( $Entry, $pTarget.'/'.$entry );
continue;
}
copy( $Entry, $pTarget.'/'.$entry );
}
$d->close();
} else {
copy( $pSource, $pTarget );
}
}
?>
{/code}{code source="SQL" title="Final SQL part"}
-- If you had to run the above PHP code, you can make sure it worked by running this again:
SELECT attachment_id, count(*) FROM liberty_attachments_map GROUP BY attachment_id HAVING COUNT(*) > 1;
-- If you still have duplicates, you can try running the PHP script again. you might have an attachment attached more than twice
BEGIN;
-- Copy the content_id from the attachments map
UPDATE liberty_attachments SET content_id=(SELECT content_id FROM liberty_attachments_map lam WHERE lam.attachment_id=liberty_attachments.attachment_id);
-- if the primary_attachment_id is set then update the is_primary flag
UPDATE liberty_attachments SET is_primary = 'y'
WHERE (attachment_id, content_id) IN
( SELECT lc.primary_attachment_id, lc.content_id
FROM liberty_content lc
WHERE lc.primary_attachment_id IS NOT NULL
);
-- Okay to drop this now.
DROP TABLE liberty_attachments_map;
-- Okay to drop the column now.
ALTER TABLE liberty_content DROP COLUMN primary_attachment_id;
--Finish Transaction
COMMIT;
{/code}
06-JUL-2007 | liberty_content_permissions | is_exlcuded column to handle eventual exlcusion of a permission | [/spider], [/xing]
SQL92|{code source=SQL}
ALTER TABLE liberty_content_permissions ADD is_revoked VARCHAR(1);
-- This was briefly named is_excluded. If you had made that update, us these statements to simply change the column name:
-- SQL92: ALTER TABLE liberty_content_permissions RENAME COLUMN is_excluded TO is_revoked;
-- MYSQL: ALTER TABLE liberty_content_permissions CHANGE is_excluded is_revoked VARCHAR(1);
{/code}
30-JUN-2007 | liberty_content | update primary_attachment_id that treasury will still work | [/xing]
SQL92|{code source=SQL}
UPDATE liberty_content SET primary_attachment_id=( SELECT attachment_id FROM liberty_attachments_map lam INNER JOIN liberty_content lc ON ( lc.content_id=lam.content_id ) WHERE lc.content_id=liberty_content.content_id AND lc.content_type_guid='treasuryitem' );
UPDATE liberty_attachments SET attachment_plugin_guid = 'bitfile' WHERE attachment_plugin_guid = 'treasury';
{/code}
22-JUN-2007 | events | rename count column as it is a reserved word | [/lsces]
SQL92|{code source=SQL}
ALTER TABLE events RENAME COLUMN count TO r_count;
-- MYSQL use this instead:
ALTER TABLE events CHANGE count r_count int;
{/code}
21-JUN-2007 | kernel | add session table for ADOdb session managment | [/lsces]
SQL92|{code source=SQL}
CREATE TABLE sessions (
sesskey varchar(32) NOT NULL,
expiry integer NOT NULL,
expireref varchar(32),
session_data text,
primary key (sesskey)
);
{/code}
19-JUN-2007 | LibertyPackage | add a content connection map to liberty | [/xing]
SQL92|{code source=SQL}
-- please note that the item_postion column was changed from int to float on the 20-JUN-2007
CREATE TABLE liberty_content_connection_map (
from_content_id integer NOT NULL,
to_content_id integer NOT NULL,
item_position float
);
-- now we can add some constraints
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_content_connection_map_pkey PRIMARY KEY (from_content_id, to_content_id);
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_from_content_id_ref FOREIGN KEY (from_content_id) REFERENCES liberty_content(content_id);
ALTER TABLE liberty_content_connection_map ADD CONSTRAINT liberty_to_content_id_ref FOREIGN KEY (to_content_id) REFERENCES liberty_content(content_id);
{/code}
14-JUN-2007 | LibertyPackage | move fisheye_exif_data to liberty_meta_data | [/xing]
SQL92|{code source=SQL}
-- you can alter the existing table if you have it
ALTER TABLE fisheye_exif_data RENAME TO liberty_meta_data;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_name TO meta_name;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_value_short TO meta_value_short;
ALTER TABLE liberty_meta_data RENAME COLUMN exif_value_long TO meta_value_long;
-- MYSQL users use these three to rename the columns
ALTER TABLE liberty_meta_data CHANGE exif_name meta_name VARCHAR(250) NOT NULL;
ALTER TABLE liberty_meta_data CHANGE exif_value_short meta_value_short VARCHAR(250);
ALTER TABLE liberty_meta_data CHANGE exif_value_long meta_value_long TEXT;
-- If you don't have the liberty_meta_data table to work with, this is how you add it
CREATE TABLE liberty_meta_data (
meta_type_guid character varying(16) NOT NULL,
meta_key character varying(250) NOT NULL,
meta_title character varying(250) NOT NULL,
meta_value_short character varying(250),
meta_value_long text
);
CREATE TABLE liberty_meta_content_map (
content_id integer NOT NULL,
meta_key character varying(250) NOT NULL
);
CREATE TABLE liberty_meta_types (
meta_type_guid character varying(16) NOT NULL,
meta_type_title character varying(250)
);
-- and the constraint
ALTER TABLE liberty_meta_content_map ADD CONSTRAINT liberty_meta_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
{/code}
14-JUN-2007 | EventsPackage | Add events_invites table for managing invites and add a few indexes for speed. | [/nickpalmer]
SQL92|{code source=SQL}
CREATE TABLE events_invites ( invites_id INT4 NOT NULL, content_id INT4 NOT NULL, event_content_id INT4 NOT NULL, user_id INT4 NOT NULL, interest INT4 NOT NULL, guests INT4 , CONSTRAINT "events_users_content_ref" FOREIGN KEY ("content_id") REFERENCES "liberty_content"( "content_id") , CONSTRAINT "events_users_event_content_ref" FOREIGN KEY ("event_content_id") REFERENCES "liberty_content"( "content_id") , PRIMARY KEY (invites_id) );
CREATE INDEX events_events_location_idx ON events (location_id);
CREATE INDEX events_invites_event_idx ON events_invites (event_content_id);
CREATE INDEX events_invites_user_idx ON events_invites (user_id);
CREATE SEQUENCE events_invites_id_seq START 1;
{/code}
14-JUN-2007 | LibertyPackage | Initilalise 'common_storage_plugin' | [/lsces]
SQL92|{code source=SQL}INSERT INTO kernel_config VALUES ( 'common_storage_plugin', 'liberty', 'bitfile' ){/code}
14-JUN-2007 | EventsPackage | interval is reserved on MySQL so needs changing | [/lsces]
SQL92|{code source=SQL}
ALTER TABLE events RENAME COLUMN interval TO event_interval;
{/code}
13-JUN-2007 | LibertyPackage | add order column to libert_attachment_map | [/lsces]
ADODB |Later a unique index would be useful on attachement_id, item_position but is probably going to be database specific.{code source=SQL}
ALTER TABLE liberty_attachments_map ADD item_position INT;
{/code}
12-JUN-2007 | LibertyPackage | add primary_attachment_id to directly associate an attachment with content | [/xing], [/nickpalmer]
SQL92 | {code source=SQL}
ALTER TABLE liberty_content ADD COLUMN primary_attachment_id int;
ALTER TABLE liberty_content ADD CONSTRAINT liberty_content_attachment_ref FOREIGN KEY (primary_attachment_id) REFERENCES liberty_attachments (attachment_id);
{/code}
10-JUN-2007 | EventsPackage | massive update to prepare for new functionality | [/nickpalmer]
SQL92 | {code source=SQL}
CREATE TABLE events_types (
type_id int,
name varchar(30) NOT NULL,
description varchar(160)
);
CREATE SEQUENCE events_types_id_seq;
CREATE UNIQUE INDEX events_type_id_idx ON events_types(type_id);
ALTER TABLE events ADD COLUMN cost varchar(160);
ALTER TABLE events ADD COLUMN type_id int;
ALTER TABLE events ADD COLUMN location_id int;
ALTER TABLE events ADD COLUMN frequency int;
ALTER TABLE events ADD COLUMN interval int;
ALTER TABLE events ADD COLUMN count int;
ALTER TABLE events ADD COLUMN end_date int;
ALTER TABLE events ADD COLUMN bylists text;
ALTER TABLE events ADD CONSTRAINT events_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
ALTER TABLE events ADD CONSTRAINT events_type_ref FOREIGN KEY (type_id) REFERENCES events_types (type_id);
CREATE TABLE events_on (
content_id int,
event_on int
);
ALTER TABLE events_on ADD CONSTRAINT events_on_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
CREATE UNIQUE INDEX events_on_id_idx ON events_on(content_id);
CREATE INDEX event_on_on_idx ON events_on(event_on);
{/code}
01-JUN-2007 | WikiPackage | remove unused columns | [/xing]
SQL92 | {code source=SQL}
ALTER TABLE wiki_pages DROP COLUMN page_cache;
ALTER TABLE wiki_pages DROP COLUMN wiki_cache;
ALTER TABLE wiki_pages DROP COLUMN cache_timestamp;
ALTER TABLE wiki_pages DROP COLUMN points;
ALTER TABLE wiki_pages DROP COLUMN votes;
ALTER TABLE wiki_pages DROP COLUMN page_rank;
{/code}
04-MAY-2007 | NewslettersPackage | add mail_clickthrough and other track | [/spider]
SQL92 | {code source=SQL}
CREATE TABLE mail_clickthrough ( user_id INT NOT NULL, content_id INT NOT NULL, clicked_url VARCHAR(250) NOT NULL, clicks INT, CONSTRAINT mail_clickthrough_user_ref FOREIGN KEY (user_id) REFERENCES users_users (user_id), CONSTRAINT mail_clickthrough_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id) );
ALTER TABLE mail_queue ADD COLUMN last_read_ip VARCHAR(40);
{/code}
13-APR-2007 (A Friday!)| liberty | split liberty_attachments into two tables | [/nickpalmer]
SQL92 | {code source=SQL}
-- Create The Table
CREATE TABLE liberty_attachments_map (
attachment_id INT NOT NULL,
content_id INT NOT NULL
);
-- MYSQL users comment out the next three lines. (And get a REAL database. ;)
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_unique UNIQUE (attachment_id, content_id);
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content (content_id);
ALTER TABLE liberty_attachments_map ADD CONSTRAINT liberty_attachments_map_attachment_ref FOREIGN KEY (attachment_id) REFERENCES liberty_attachments (attachment_id);
-- Copy the data over
INSERT INTO liberty_attachments_map (attachment_id, content_id) (SELECT attachment_id, content_id FROM liberty_attachments);
-- Drop the old column
ALTER TABLE liberty_attachments DROP COLUMN content_id;
{/code}
5-APR-2007 | events | add new cols for event end time. multi-day events not yet supported | [/nickpalmer]
SQL92 | {code source=SQL}
ALTER TABLE events ADD end_time INT;
{/code}
02-APR-2007 | themes tables | simplify themes schema | [/xing]
SQL92 | {code source=SQL}
-- see comment at bottom of page for MySQL
-- rename old themes_layouts table to a temporary table name
ALTER TABLE themes_layouts RENAME to themes_layouts_original;
-- create new themes_layouts table
-- module_id is PRIMARY - constraint added below
CREATE TABLE themes_layouts (
module_id integer NOT NULL,
title VARCHAR(255),
layout VARCHAR(160) DEFAULT 'kernel' NOT NULL,
layout_area character varying(1) NOT NULL,
module_rows integer,
module_rsrc character varying(250) NOT NULL,
params character varying(255),
cache_time bigint,
groups character varying(255),
pos integer DEFAULT 1 NOT NULL
);
-- The following only works on real databases. MySQL does count for a plethora of reasons. If someone comes up with the mysql hackish way to do these things, please add it.
-- create PRIMARY constraint
ALTER TABLE themes_layouts ADD CONSTRAINT themes_layouts_pkey PRIMARY KEY (module_id);
-- merge data from old layout tables into new table
CREATE SEQUENCE themes_layouts_module_id_seq;
INSERT INTO themes_layouts ( module_id, title, layout, layout_area, module_rows, module_rsrc, params, cache_time, groups, pos )
SELECT NEXTVAL('themes_layouts_module_id_seq'), tlm.title, tl.layout, tl.layout_position, tlm.module_rows, tmm.module_rsrc, tlm.params, tlm.cache_time, tlm.groups, tl.ord
FROM themes_layouts_modules tlm, themes_layouts_original tl, themes_module_map tmm
WHERE tlm.module_id=tl.module_id AND tmm.module_id=tlm.module_id AND user_id=1;
-- It should be ok to drop these tables now
-- DROP TABLE themes_layouts_original; DROPing this table will destroy and user created layouts. Stay tuned for an upcoming UserhomePackage
-- DROP TABLE themes_layouts_modules;
-- DROP TABLE themes_module_map;
{/code}
26-MAR-2007 | blogs_posts_map | add field for crossposting note | [/Will]
SQL92 | {code source=SQL}
ALTER TABLE blogs_posts_map ADD crosspost_note TEXT;
{/code}
19-MAR-2007 | liberty_content_permissions, users_object_permissions | move users_object_permissions to liberty_content_permissions | [/spiderr]
SQL92 | {code source=SQL}
ALTER TABLE users_object_permissions RENAME to liberty_content_permissions;
ALTER TABLE liberty_content_permissions RENAME object_id TO content_id;
-- MySQL users replace above query with: ALTER TABLE liberty_content_permissions CHANGE object_id content_id INT;
ALTER TABLE liberty_content_permissions ADD CONSTRAINT liberty_content_perm_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
-- If you are running an old/tikiwiki upgraded bw, youmight need to clean up the schema as follows:
ALTER TABLE liberty_content_permissions DROP objectId;
ALTER TABLE liberty_content_permissions DROP object_type;
ALTER TABLE liberty_content_permissions ADD PRIMARY KEY pri_key (perm_name,group_id,content_id);
ALTER TABLE liberty_content_permissions DROP PRIMARY KEY;
ALTER TABLE liberty_content_permissions ADD PRIMARY KEY pri_key (perm_name,group_id,content_id);
{/code}
9-MAR-2007 | blog_posts, blogs_posts_map | add blogs posts normalization | [/spiderr], [/Will]
SQL92 | {code source=SQL}
CREATE TABLE blogs_posts_map ( post_content_id INT NOT NULL, blog_content_id INT NOT NULL, date_added INT );
-- Might be necessary for postgres, etc. so CONSTRAINTS work.
CREATE UNIQUE INDEX blog_posts_content_idx ON blog_posts(content_id);
CREATE UNIQUE INDEX blogs_content_idx ON blogs(content_id);
ALTER TABLE blogs_posts_map ADD CONSTRAINT blogs_posts_map_post_ref FOREIGN KEY (post_content_id) REFERENCES blog_posts (content_id);
ALTER TABLE blogs_posts_map ADD CONSTRAINT blogs_posts_map_blog_ref FOREIGN KEY (blog_content_id) REFERENCES blogs (content_id);
INSERT INTO blogs_posts_map (post_content_id,blog_content_id,date_added) (SELECT bp.content_id,b.content_id,lc.created FROM blog_posts bp INNER JOIN blogs b ON(bp.blog_id=b.blog_id) INNER JOIN liberty_content lc ON(bp.content_id=lc.content_id) );
ALTER TABLE blog_posts DROP COLUMN blog_id;
{/code}
9-MAR-2007 | boards | add migrate_board_id to allow phpbb redirects | [/spiderr]
SQL92 | {code source=SQL}
ALTER TABLE boards ADD COLUMN migrate_board_id INT;
{/code}
9-MAR-2007 | liberty | change values for some content_status_id | [/nickpalmer]
SQL92 | {code source=SQL}
INSERT INTO liberty_content_status (content_status_id, content_status_name) VALUES (-5, 'New Draft');
UPDATE liberty_content SET content_status_id = -5 WHERE content_status_id = 10;
DELETE FROM liberty_content_status where content_status_name = 'Draft';
UPDATE liberty_content_status set content_status_name = 'Draft' WHERE content_status_name = 'New Draft';
INSERT INTO liberty_content_status (content_status_id, content_status_name) VALUES (-1, 'New Pending Approval');
UPDATE liberty_content SET content_status_id = -1 WHERE content_status_id = 101;
DELETE FROM liberty_content_status where content_status_name = 'Pending Approval';
UPDATE liberty_content_status set content_status_name = 'Pending Approval' WHERE content_status_name = 'New Pending Approval';
{/code}
3-MAR-2007 | blog_posts | add permissions for viewing future dated and expired posts | [/wjames5]
SQL92 | {code source=SQL}
INSERT INTO users_permissions( perm_name,perm_desc, perm_level, package ) VALUES( 'p_blogs_posts_read_future', 'Can view future dated posts', 'editors', 'blogs' );
INSERT INTO users_permissions( perm_name,perm_desc, perm_level, package ) VALUES( 'p_blogs_posts_read_expired', 'Can view expired posts', 'editors', 'blogs' );
{/code}
2-MAR-2007 | blog_posts | add new cols for future dating posts and setting expiration date | [/wjames5]
SQL92 | {code source=SQL}
ALTER TABLE blog_posts ADD publish_date INT;
ALTER TABLE blog_posts ADD expire_date INT;
{/code}
1-MAR-2007 | liberty_process_queue | add new table for background processing queues | [/xing]
SQL92 | {code source=SQL}
CREATE TABLE liberty_process_queue (
process_id serial NOT NULL,
content_id integer NOT NULL,
queue_date int8 NOT NULL,
begin_date int8,
end_date int8,
process_status character varying(64),
log_message text,
processor character varying(250),
processor_parameters text
);
-- MYSQL Version: CREATE TABLE liberty_process_queue ( process_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, content_id integer NOT NULL, queue_date int8 NOT NULL, begin_date INT8, end_date INT8, process_status character varying(64), log_message text, processor character varying(250), processor_parameters text );
{/code}
26-FEB-2007 | liberty_action_log | reset liberty_action_logs column properties | [/xing]
SQL92 | {code source=SQL}
ALTER TABLE liberty_action_log RENAME log_message TO temp_message;
ALTER TABLE liberty_action_log ADD COLUMN log_message VARCHAR(250) NOT NULL DEFAULT '';
UPDATE liberty_action_log set log_message = temp_message;
ALTER TABLE liberty_action_log DROP COLUMN temp_message;
ALTER TABLE liberty_action_log RENAME error_message TO temp_error;
ALTER TABLE liberty_action_log ADD COLUMN error_message VARCHAR(250) NOT NULL DEFAULT '';
UPDATE liberty_action_log set temp_error = '' WHERE temp_error IS NULL;
UPDATE liberty_action_log set error_message = temp_error;
ALTER TABLE liberty_action_log DROP COLUMN temp_error;
{/code}
16-FEB-2007 | liberty_thumbnail_queue | move liberty_thumbnail_queue from fisheye schema_inc.php to liberty, and add processor and processor_parameters columns to liberty_thumbnail_queue for future flexibility | [/spider]
SQL92 | {code source=SQL}
ALTER TABLE liberty_thumbnail_queue ADD processor VARCHAR(250);
ALTER TABLE liberty_thumbnail_queue ADD processor_parameters VARCHAR(250);
{/code}
16-JAN-2007 | kernel_config | update config names to tie in with the rest of bitweaver | [/xing]
SQL92 | {code source=SQL}
UPDATE kernel_config SET config_name='i18n_browser_languages' WHERE config_name='browser_languages';
UPDATE kernel_config SET config_name='i18n_interactive_translation' WHERE config_name='interactive_translation';
UPDATE kernel_config SET config_name='i18n_interactive_bittranslation' WHERE config_name='interactive_bittranslation';
UPDATE kernel_config SET config_name='i18n_record_untranslated' WHERE config_name='languages_record_untranslated';
UPDATE kernel_config SET config_name='i18n_track_translation_usage' WHERE config_name='track_translation_usage';
{/code}
||