History of SchemaChangelog

! bitweaver ReleaseTwo Schema changes

^This is an ongoing list of __any__ changes to __any__ DataDict array in a schema_inc in __any__ package (except: ((BitcommercePackage)) ).^

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)). If you are not sure what this is all about or how to use the information on this page, read the ((How To Use SchemaChangelog)).

Due to the length of this page, we have split it in two, please consult ((ReleaseTwoSchemaChangeLog1)) for older changes.

||~Date|Table|Reason|Who
14-August-2008 | gmap | update icon table to have a theme_id association | [/wjames5]
SQL92|{code source=SQL title="longer maptype descriptions."}
ALTER TABLE gmaps_maptypes ALTER COLUMN description TYPE TEXT;
--mysql use:
ALTER TABLE gmaps_maptypes CHANGE description description TEXT;
{/code}
14-August-2008 | gmap | update icon table to have a theme_id association | [/wjames5]
SQL92|{code source=SQL title="Add theme_id"}
ALTER TABLE gmaps_icon_styles ADD COLUMN theme_id INT4 NOT NULL;

-- NOTE: You must run the installer step 4 "Resolve Conflicts" to also add a new gmaps_icon_themes table before trying to add this constraint.
ALTER TABLE gmaps_icon_styles ADD CONSTRAINT gmaps_icon_theme_ref FOREIGN KEY (theme_id) REFERENCES gmaps_icon_themes(theme_id);
{/code}
23-MAY-2008 | liberty | replace old unused meta tables with new ones | [/xing]
SQL92|{code source=SQL title="Remove old unused tables and replace them with new ones"}
-- remove unused meta tables
DROP TABLE liberty_meta_content_map;
DROP TABLE liberty_meta_data;
DROP TABLE liberty_meta_types;

-- add new meta tables
-- Expand TEXT as appropriate ( FB -> BLOB SUB_TYPE TEXT )
CREATE TABLE liberty_attachment_meta_data (
attachment_id INT NOT NULL,
meta_type_id INT NOT NULL,
meta_title_id INT NOT NULL,
meta_value TEXT
);
CREATE TABLE liberty_meta_types (
meta_type_id INT PRIMARY KEY,
meta_type VARCHAR(250) NOT NULL
);
CREATE TABLE liberty_meta_titles (
meta_title_id INT PRIMARY KEY,
meta_title VARCHAR(250) NOT NULL
);
ALTER TABLE liberty_attachment_meta_data ADD CONSTRAINT lib_attachment_meta_id_ref FOREIGN KEY (attachment_id) REFERENCES liberty_attachments(attachment_id);
ALTER TABLE liberty_attachment_meta_data ADD CONSTRAINT lib_attachment_meta_type_ref FOREIGN KEY (meta_type_id) REFERENCES liberty_meta_types(meta_type_id);
ALTER TABLE liberty_attachment_meta_data ADD CONSTRAINT lib_attachment_meta_title_ref FOREIGN KEY (meta_title_id) REFERENCES liberty_meta_titles(meta_title_id);
CREATE UNIQUE INDEX lib_attachment_meta_idx ON liberty_attachment_meta_data(attachment_id,meta_type_id,meta_title_id);
{/code}
10-MAY-2008 | liberty | add a liberty_attachment_prefs table | [/xing]
SQL92|{code source=SQL}
CREATE TABLE liberty_attachment_prefs (
attachment_id INT,
pref_name VARCHAR(40),
pref_value VARCHAR(250)
);
CREATE UNIQUE INDEX liberty_att_prefs_att_name_idx ON liberty_attachment_prefs(attachment_id,pref_name);
CREATE INDEX liberty_att_prefs_att_idx ON liberty_attachment_prefs(attachment_id);
ALTER TABLE liberty_attachment_prefs ADD CONSTRAINT lib_att_prefs_content_ref FOREIGN KEY (attachment_id) REFERENCES liberty_attachments(attachment_id);
{/code}
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'].' --&amp;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}
13-OCT-2006 | liberty | Aliases for liberty content modified to allow composite primary key | [/lsces]
SQL92 | {code source=SQL}
CREATE TABLE liberty_aliases( content_id INT NOT NULL, alias_title VARCHAR(190), CONSTRAINT liberty_aliases_content_fkey FOREIGN KEY (content_id) REFERENCES liberty_content(content_id) );
{/code}
11-OCT-2006 | nexus_plugins | NexusPackage cleanup | [/xing]
SQL92 | {code source=SQL}
DROP TABLE nexus_plugins;
{/code}
11-OCT-2006 | liberty_meta_data | LibertyPackage meta data tables - can be used for Fisheye EXIF, etc. | [/spider]
SQL92 | {code source=SQL}
CREATE TABLE liberty_meta_types( meta_type_guid VARCHAR(16) PRIMARY KEY, meta_type_title VARCHAR(250) );
CREATE TABLE liberty_meta_data ( content_id INT NOT NULL, meta_type_guid VARCHAR(16) NOT NULL, meta_key VARCHAR(250) NOT NULL, meta_title VARCHAR(250) NOT NULL, meta_value_short VARCHAR(250), meta_value_long TEXT, CONSTRAINT liberty_meta_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id), CONSTRAINT liberty_meta_guid_ref FOREIGN KEY (meta_type_guid) REFERENCES liberty_meta_types(meta_type_guid) );
{/code}
11-OCT-2006 | blogs | Convert Blogs to Content | [/spider]
SQL92 | {code source=SQL}
DELETE FROM liberty_content_types WHERE content_type_guid = 'bitblog';
INSERT INTO liberty_content_types (content_type_guid, content_description, maintainer_url, handler_class, handler_package, handler_file) VALUES ('bitblog', 'Blog', 'http://www.bitweaver.org', 'BitBlog', 'blogs', 'BitBlog.php');
ALTER TABLE blogs ADD content_id INT;
UPDATE blogs SET content_id = nextval('liberty_content_id_seq');
INSERT INTO liberty_content (content_id, title, data, created, last_modified, user_id, modifier_user_id, format_guid, content_type_guid, content_status_id) (SELECT content_id, title, description, created, last_modified, user_id, user_id, 'tikiwiki', 'bitblog', 50 FROM blogs);
INSERT INTO liberty_content_hits (content_id, hits) (SELECT content_id,hits FROM blogs);
ALTER TABLE blogs RENAME public_blog TO is_public;
ALTER TABLE blogs DROP title;
ALTER TABLE blogs DROP description;
ALTER TABLE blogs DROP created;
ALTER TABLE blogs DROP last_modified;
ALTER TABLE blogs DROP user_id;
ALTER TABLE blogs DROP hits;
ALTER TABLE blogs ADD CONSTRAINT blogs_content_id_ref FOREIGN KEY (content_id) REFERENCES liberty_content(content_id);
ALTER TABLE blogs ALTER content_id SET NOT NULL;
{/code}
06-OCT-2006 | liberty | Aliases for liberty content | [/spider]
SQL92 | {code source=SQL}
CREATE TABLE liberty_aliases( alias_name VARCHAR(250), content_id INT NOT NULL, CONSTRAINT liberty_aliases_content_fkey FOREIGN KEY (content_id) REFERENCES liberty_content(content_id) );
{/code}
20-SEP-2006 | ((Liberty Content Status)) | [/spider]
SQL92 | {code source=SQL}
CREATE TABLE liberty_content_status ( content_status_id INT4 NOT NULL PRIMARY KEY, content_status_name VARCHAR(128) NOT NULL );
ALTER TABLE liberty_content ADD COLUMN content_status_id INT4;
ALTER TABLE liberty_content ADD CONSTRAINT libert_content_status_ref FOREIGN KEY (content_status_id) REFERENCES liberty_content_status(content_status_id);
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-999, 'Deleted');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-998, 'Spam');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-201, 'Suspended');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-100, 'Denied');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-40, 'Private');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-30, 'Password Protected');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-20, 'Group Protected');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (-10, 'Hidden');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (10, 'Draft');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (50, 'Available');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (101, 'Pending Approval');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (102, 'Commercial');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (200, 'Recommended');
INSERT INTO liberty_content_status (content_status_id,content_status_name) VALUES (999, 'Copy Protected');
UPDATE liberty_content SET content_status_id=50;
ALTER TABLE liberty_content ALTER content_status_id SET NOT NULL;
{/code}
15-SEP-2006| LibertyPackage | clean up liberty_action_log table and make use of it in liberty | [/xing]
SQL92| {code source=SQL}
ALTER TABLE liberty_action_log RENAME COLUMN log_action TO log_message;
ALTER TABLE liberty_action_log RENAME COLUMN action_comment TO error_message; -- change column action_error to have a char length limit of 250 chars
ALTER TABLE liberty_action_log ALTER COLUMN error_message TYPE VARCHAR(250);
ALTER TABLE liberty_action_log ALTER content_id DROP NOT NULL;
{/code}
13-SEP-2006| StarsPackage| reverted to original stars version and forked version rating stars to superstars | [/xing]
Please revert all stars changes in here if you want to use bitweaver version of stars or download and use _bit_superstars
09-SEP-2006| StarsPackage| remove stars table, add update_count colums to stars_version and stars_history, add indexs to enforce unique constraints | [/jht001]
SQL 92 |{code source="sql"}
drop table stars;
ALTER TABLE stars_version ADD COLUMN update_count INT4 default 0;
ALTER TABLE stars_history ADD COLUMN update_count INT4 default 0;
ALTER TABLE stars_version ADD PRIMARY KEY(content_id,version);
ALTER TABLE stars_history ADD PRIMARY KEY(content_id,version,user_id);
{/code}
06-SEP-2006| FisheyePackage |add image position fractional ordering to image galleries | [/spider]
SQL 92 |{code source="sql"}
ALTER TABLE fisheye_gallery_image_map RENAME item_position TO old_position;
ALTER TABLE fisheye_gallery_image_map ADD COLUMN item_position FLOAT;
UPDATE fisheye_gallery_image_map SET item_position = old_position;
ALTER TABLE fisheye_gallery_image_map DROP COLUMN old_position;
{/code}
21-AUG-2006| UsersPackage | add password expiration | [/jht001]
SQL 92 |{code source="sql"}
ALTER TABLE users_users ADD COLUMN provpass_expires INT8;
{/code}
21-AUG-2006| StarsPackage | add perversion ratings | [/hash9]
SQL 92 |{code source="sql"}
CREATE TABLE stars_version ( content_id INTEGER NOT NULL, version INTEGER NOT NULL, rating_count INTEGER, rating INTEGER, CONSTRAINT stars_version_ref FOREIGN KEY (content_id) REFERENCES liberty_content( content_id ));
ALTER TABLE stars_history ADD COLUMN version INTEGER NOT NULL;
{/code}
18-AUG-2006| LibertyPackage | move highly volatile hits columns out of liberty_content and into a separate table | [/jht001] and [/spiderr]
SQL 92 |{code source="sql"}
CREATE TABLE liberty_content_hits ( content_id INTEGER PRIMARY KEY, hits INTEGER NOT NULL DEFAULT 1, last_hit BIGINT, CONSTRAINT liberty_content_hits_ref FOREIGN KEY (content_id) REFERENCES liberty_content( content_id ));
INSERT INTO liberty_content_hits ( content_id, hits, last_hit ) (SELECT content_id, hits, last_hit FROM liberty_content);
ALTER TABLE liberty_content DROP hits;
ALTER TABLE liberty_content DROP last_hit;
{/code}
26-JUL-2006| LibertyPackage | Add anonymous name to comments | [/Hash9]
SQL 92 |{code source="sql"}
ALTER TABLE liberty_comments ADD anon_name VARCHAR(40);
{/code}
17-JUL-2006| UsersPackage | Improve users_cnxn to record user assumption ID | [/spider]
SQL 92 |{code source="sql"}
ALTER TABLE users_cnxn ADD assume_from_user_id INT;
{/code}
10-JUL-2006| multisites | added per site content restricitions | [/nickpalmer]
CREATE| {code source="sql"}
CREATE TABLE multisite_content ( multisite_id INTEGER NOT NULL, content_id INTEGER NOT NULL , CONSTRAINT multisite_content_ref FOREIGN KEY (content_id) REFERENCES liberty_content( content_id ) , CONSTRAINT multisite_multisite_ref FOREIGN KEY (multisite_id) REFERENCES multisites( multisite_id ), PRIMARY KEY (multisite_id, content_id) );
INSERT INTO `kernel_config`(`package`,`config_name`,`config_value`) VALUES ('multisites', 'multisites_per_site_content','y');
INSERT INTO `kernel_config`(`package`,`config_name`,`config_value`) VALUES ('multisites', 'multisites_use_jstab','y');
INSERT INTO `kernel_config`(`package`,`config_name`,`config_value`) VALUES ('multistes', 'multisites_limit_member_number','10');
INSERT INTO `users_permissions` (`perm_name`, `perm_desc`, `perm_level`, `package`) VALUES ('p_multisites_restrict_content', 'Can restrict content to certain sites', 'editor', 'multisites');
INSERT INTO `users_permissions` (`perm_name`, `perm_desc`, `perm_level`, `package`) VALUES ('p_multisites_view_restricted', 'Can view all site restricted content', 'admin', 'multisites');
{/code}
12-JUN-2006| fisheye | image comment - no more useful after spider changes| [/sylvie]
SQL 92 |{code source="sql"}
OBSOLETE: ALTER TABLE fisheye_gallery ADD COLUMN image_comment VARCHAR(1);
{/code}
12-MAY-2006| shoutbox | record IP | [/spider]
SQL 92 |{code source="sql"}
ALTER TABLE shoutbox ADD COLUMN shout_ip VARCHAR(16);
{/code}
12-MAY-2006|users_permissions, users_group_permissions|p_wiki_view_history|[/sylvie]
SQL 92 |{code source="sql"}
INSERT INTO `users_permissions` (`perm_name`,`perm_desc`, `perm_level`, `package`) VALUES ('p_wiki_view_history', 'Can view page history', 'basic', 'wiki');
INSERT INTO `users_group_permissions` (`group_id`, `perm_name`) VALUES (-1,'p_wiki_view_history');
{/code}
8-MAY-2006|users_groups|to have a after registration page|[/sylvie]
SQL 92 |{code source="sql"}
ALTER TABLE users_groups ADD COLUMN after_registration_page varchar(255);
{/code}
5-MAY-2006|liberty_plugins|add plugin_path column that plugins can be kept in any directory without having to scan for them when parsing|[/xing]
SQL 92 | {code source="sql"}
ALTER TABLE liberty_plugins ADD COLUMN plugin_path varchar(250);
{/code}
5-MAY-2006|liberty_content_prefs|rename some liberty_content_prefs column for consistency|[/xing]
SQL 92 | {code source="sql"}
ALTER TABLE liberty_content_prefs RENAME COLUMN name TO pref_name;
{/code}
||
Page History
Date/CommentUserIPVersion
21 Apr 2010 (15:51 UTC)
spiderr24.171.168.221329
Current • Source
spiderr24.171.168.223328
View • Compare • Difference • Source
spiderr69.134.250.112327
View • Compare • Difference • Source
spiderr69.134.250.112326
View • Compare • Difference • Source
spiderr69.134.250.112325
View • Compare • Difference • Source
Tyler Bello69.134.250.112324
View • Compare • Difference • Source
Tyler Bello69.134.250.112323
View • Compare • Difference • Source
Tyler Bello69.134.250.112322
View • Compare • Difference • Source
Daniel Sutcliffe71.161.102.6321
View • Compare • Difference • Source
Lester Caine81.138.11.136320
View • Compare • Difference • Source
spiderr71.77.29.231319
View • Compare • Difference • Source
spiderr71.77.29.231317
View • Compare • Difference • Source
spiderr71.77.29.231316
View • Compare • Difference • Source
xing62.47.247.26315
View • Compare • Difference • Source
xing62.47.247.26314
View • Compare • Difference • Source
xing62.47.247.26313
View • Compare • Difference • Source
SpOOnman83.143.43.103312
View • Compare • Difference • Source
xing62.47.234.56309
View • Compare • Difference • Source
xing62.47.234.56308
View • Compare • Difference • Source
Will69.203.72.161307
View • Compare • Difference • Source
Will69.203.72.161306
View • Compare • Difference • Source
Will69.203.72.161305
View • Compare • Difference • Source
Will69.203.72.161304
View • Compare • Difference • Source
Lester Caine81.138.11.136303
View • Compare • Difference • Source
xing194.152.164.45302
View • Compare • Difference • Source
xing194.152.164.45301
View • Compare • Difference • Source