These are ReleaseTwo database changes.
these changes if you are on ReleaseOne. If you need to know the changes for ReleaseOne, see the
. If you are not sure what this is all about or how to use the information on this page, read the
.
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 | longer maptype descriptions.
ALTER TABLE gmaps_maptypes ALTER COLUMN description TYPE TEXT; --mysql use: ALTER TABLE gmaps_maptypes CHANGE description description TEXT;
|
14-August-2008 | gmap | update icon table to have a theme_id association | /wjames5 |
SQL92 | 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);
|
23-MAY-2008 | liberty | replace old unused meta tables with new ones | /xing |
SQL92 | 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);
|
10-MAY-2008 | liberty | add a liberty_attachment_prefs table | /xing |
SQL92 |
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);
|
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 |
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;
|
19-SEP-2007 | articles | remove obsolete image_attachment_id column | /xing |
SQL92 |
-- 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;
|
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 | 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.
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'].' --> '.$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 ); } } ?>
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;
|
06-JUL-2007 | liberty_content_permissions | is_exlcuded column to handle eventual exlcusion of a permission | /spider, /xing |
SQL92 |
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);
|
30-JUN-2007 | liberty_content | update primary_attachment_id that treasury will still work | /xing |
SQL92 |
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';
|
22-JUN-2007 | events | rename count column as it is a reserved word | /lsces |
SQL92 |
ALTER TABLE events RENAME COLUMN count TO r_count; -- MYSQL use this instead: ALTER TABLE events CHANGE count r_count int;
|
21-JUN-2007 | kernel | add session table for ADOdb session managment | /lsces |
SQL92 |
CREATE TABLE sessions ( sesskey varchar(32) NOT NULL, expiry integer NOT NULL, expireref varchar(32), session_data text, primary key (sesskey) );
|
19-JUN-2007 | LibertyPackage | add a content connection map to liberty | /xing |
SQL92 |
-- 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);
|
14-JUN-2007 | LibertyPackage | move fisheye_exif_data to liberty_meta_data | /xing |
SQL92 |
-- 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);
|
14-JUN-2007 | EventsPackage | Add events_invites table for managing invites and add a few indexes for speed. | /nickpalmer |
SQL92 |
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;
|
14-JUN-2007 | LibertyPackage | Initilalise 'common_storage_plugin' | /lsces |
SQL92 |
INSERT INTO kernel_config VALUES ( 'common_storage_plugin', 'liberty', 'bitfile' )
|
14-JUN-2007 | EventsPackage | interval is reserved on MySQL so needs changing | /lsces |
SQL92 |
ALTER TABLE events RENAME COLUMN interval TO event_interval;
|
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.
ALTER TABLE liberty_attachments_map ADD item_position INT;
|
12-JUN-2007 | LibertyPackage | add primary_attachment_id to directly associate an attachment with content | /xing, /nickpalmer |
SQL92 |
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);
|
10-JUN-2007 | EventsPackage | massive update to prepare for new functionality | /nickpalmer |
SQL92 |
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);
|
01-JUN-2007 | WikiPackage | remove unused columns | /xing |
SQL92 |
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;
|
04-MAY-2007 | NewslettersPackage | add mail_clickthrough and other track | /spider |
SQL92 |
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);
|
13-APR-2007 (A Friday!) | liberty | split liberty_attachments into two tables | /nickpalmer |
SQL92 |
-- 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;
|
5-APR-2007 | events | add new cols for event end time. multi-day events not yet supported | /nickpalmer |
SQL92 |
ALTER TABLE events ADD end_time INT;
|
02-APR-2007 | themes tables | simplify themes schema | /xing |
SQL92 |
-- 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;
|
26-MAR-2007 | blogs_posts_map | add field for crossposting note | /Will |
SQL92 |
ALTER TABLE blogs_posts_map ADD crosspost_note TEXT;
|
19-MAR-2007 | liberty_content_permissions, users_object_permissions | move users_object_permissions to liberty_content_permissions | /spiderr |
SQL92 |
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);
|
9-MAR-2007 | blog_posts, blogs_posts_map | add blogs posts normalization | /spiderr, /Will |
SQL92 |
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;
|
9-MAR-2007 | boards | add migrate_board_id to allow phpbb redirects | /spiderr |
SQL92 |
ALTER TABLE boards ADD COLUMN migrate_board_id INT;
|
9-MAR-2007 | liberty | change values for some content_status_id | /nickpalmer |
SQL92 |
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';
|
3-MAR-2007 | blog_posts | add permissions for viewing future dated and expired posts | /wjames5 |
SQL92 |
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' );
|
2-MAR-2007 | blog_posts | add new cols for future dating posts and setting expiration date | /wjames5 |
SQL92 |
ALTER TABLE blog_posts ADD publish_date INT; ALTER TABLE blog_posts ADD expire_date INT;
|
1-MAR-2007 | liberty_process_queue | add new table for background processing queues | /xing |
SQL92 |
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 );
|
26-FEB-2007 | liberty_action_log | reset liberty_action_logs column properties | /xing |
SQL92 |
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;
|
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 |
ALTER TABLE liberty_thumbnail_queue ADD processor VARCHAR(250); ALTER TABLE liberty_thumbnail_queue ADD processor_parameters VARCHAR(250);
|
16-JAN-2007 | kernel_config | update config names to tie in with the rest of bitweaver | /xing |
SQL92 |
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';
|
13-OCT-2006 | liberty | Aliases for liberty content modified to allow composite primary key | /lsces |
SQL92 |
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) );
|
11-OCT-2006 | nexus_plugins | NexusPackage cleanup | /xing |
SQL92 |
DROP TABLE nexus_plugins;
|
11-OCT-2006 | liberty_meta_data | LibertyPackage meta data tables - can be used for Fisheye EXIF, etc. | /spider |
SQL92 |
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) );
|
11-OCT-2006 | blogs | Convert Blogs to Content | /spider |
SQL92 |
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;
|
06-OCT-2006 | liberty | Aliases for liberty content | /spider |
SQL92 |
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) );
|
20-SEP-2006 | Liberty Content Status | /spider |
SQL92 |
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;
|
15-SEP-2006 | LibertyPackage | clean up liberty_action_log table and make use of it in liberty | /xing |
SQL92 |
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;
|
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 |
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);
|
06-SEP-2006 | FisheyePackage | add image position fractional ordering to image galleries | /spider |
SQL 92 |
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;
|
21-AUG-2006 | UsersPackage | add password expiration | /jht001 |
SQL 92 |
ALTER TABLE users_users ADD COLUMN provpass_expires INT8;
|
21-AUG-2006 | StarsPackage | add perversion ratings | /hash9 |
SQL 92 |
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;
|
18-AUG-2006 | LibertyPackage | move highly volatile hits columns out of liberty_content and into a separate table | /jht001 and /spiderr |
SQL 92 |
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;
|
26-JUL-2006 | LibertyPackage | Add anonymous name to comments | /Hash9 |
SQL 92 |
ALTER TABLE liberty_comments ADD anon_name VARCHAR(40);
|
17-JUL-2006 | UsersPackage | Improve users_cnxn to record user assumption ID | /spider |
SQL 92 |
ALTER TABLE users_cnxn ADD assume_from_user_id INT;
|
10-JUL-2006 | multisites | added per site content restricitions | /nickpalmer |
CREATE |
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');
|
12-JUN-2006 | fisheye | image comment - no more useful after spider changes | /sylvie |
SQL 92 |
OBSOLETE: ALTER TABLE fisheye_gallery ADD COLUMN image_comment VARCHAR(1);
|
12-MAY-2006 | shoutbox | record IP | /spider |
SQL 92 |
ALTER TABLE shoutbox ADD COLUMN shout_ip VARCHAR(16);
|
12-MAY-2006 | users_permissions, users_group_permissions | p_wiki_view_history | /sylvie |
SQL 92 |
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');
|
8-MAY-2006 | users_groups | to have a after registration page | /sylvie |
SQL 92 |
ALTER TABLE users_groups ADD COLUMN after_registration_page varchar(255);
|
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 |
ALTER TABLE liberty_plugins ADD COLUMN plugin_path varchar(250);
|
5-MAY-2006 | liberty_content_prefs | rename some liberty_content_prefs column for consistency | /xing |
SQL 92 |
ALTER TABLE liberty_content_prefs RENAME COLUMN name TO pref_name;
|