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'].' --&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}
|
|| |