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 the BitcommercePackage ).^

These are ReleaseTwo database changes. __DO NOT USE__ the above changes if you are on ReleaseOne. If you need to know the changes for ReleaseOne, see the ReleaseOneSchemaChangelog.


||__::Date::__|__::Table::__|__::Reason::__|__::Who::__
1-FEB-2006 | liberty tables | unify table names | [/xing]
SQL 92 | {code source="sql"}
ALTER TABLE tiki_content RENAME TO liberty_content;
ALTER TABLE tiki_attachments RENAME TO liberty_attachments;
ALTER TABLE tiki_files RENAME TO liberty_files;
ALTER TABLE tiki_structures RENAME TO liberty_structures;
ALTER TABLE tiki_comments RENAME TO liberty_comments;
ALTER TABLE tiki_plugins RENAME TO liberty_plugins;
ALTER TABLE tiki_content_types RENAME TO liberty_content_types;
ALTER TABLE tiki_link_cache RENAME TO liberty_link_cache;
{/code}
1-FEB-2006 | tiki_sessions | unify table names | [/xing]
SQL 92 | {code source="sql"}
ALTER TABLE tiki_sessions RENAME TO users_sessions;
{/code}
31-JAN-2006 | everything | unify table names | [/spider]
SQL 92 | {code source="sql"}
-- de-tikify articles
ALTER TABLE tiki_articles RENAME TO articles;
ALTER TABLE tiki_article_status RENAME TO article_status;
ALTER TABLE tiki_article_topics RENAME TO article_topics;
ALTER TABLE tiki_article_types RENAME TO article_types;

-- de-tikify blogs
ALTER TABLE tiki_blogs RENAME TO blogs;
ALTER TABLE tiki_blog_posts RENAME TO blog_posts;
ALTER TABLE tiki_blog_activity RENAME TO blog_activity;
ALTER TABLE tiki_blog_posts_images RENAME TO blog_posts_images;

-- de-tikify categories
ALTER TABLE tiki_categories RENAME TO categories;
ALTER TABLE tiki_categorized_objects RENAME TO categories_objects;
ALTER TABLE tiki_category_objects RENAME TO categories_objects_map;
ALTER TABLE tiki_category_sites RENAME TO categories_sites;

-- de-tikify chatterbox
ALTER TABLE bit_chatterbox RENAME TO chatterbox;

-- de-tikify events
ALTER TABLE bit_events RENAME TO events;

-- de-tikify fisheye
ALTER TABLE tiki_fisheye_gallery RENAME TO fisheye_gallery;
ALTER TABLE tiki_fisheye_image RENAME TO fisheye_image;
ALTER TABLE tiki_fisheye_gallery_image_map RENAME TO fisheye_gallery_image_map;
ALTER TABLE tiki_thumbnail_queue RENAME TO liberty_thumbnail_queue;

-- galaxia does not need de-tikifying

-- de-tikify gatekeeper
ALTER TABLE tiki_security RENAME TO gatekeeper_security;
ALTER TABLE tiki_content_security_map RENAME TO gatekeeper_security_map;

-- de-tikify hotwords
ALTER TABLE tiki_hotwords RENAME TO hotwords;

-- de-tikify languages
ALTER TABLE tiki_i18n_languages RENAME TO i18n_languages;
ALTER TABLE tiki_i18n_masters RENAME TO i18n_masters;
ALTER TABLE tiki_i18n_strings RENAME TO i18n_strings;
ALTER TABLE tiki_i18n_version_map RENAME TO i18n_version_map;

-- de-tikify messages
ALTER TABLE messu_messages RENAME TO messages;
ALTER TABLE messu_system_message_map RENAME TO messages_system_map;

-- de-tikify multisites
ALTER TABLE tiki_multisites RENAME TO multisites;
ALTER TABLE tiki_multisite_preferences RENAME TO multisite_preferences;

-- de-tikify newsletters
ALTER TABLE tiki_newsletters RENAME TO newsletters;
ALTER TABLE tiki_newsletters_editions RENAME TO newsletters_editions;
ALTER TABLE tiki_mail_subscriptions RENAME TO mail_subscriptions;
ALTER TABLE tiki_mail_errors RENAME TO mail_errors;
ALTER TABLE tiki_mail_mailings RENAME TO mail_mailings;
ALTER TABLE tiki_mail_queue RENAME TO mail_queue;

-- de-tikify nexus
ALTER TABLE tiki_nexus_plugins RENAME TO nexus_plugins;
ALTER TABLE tiki_nexus_menus RENAME TO nexus_menus;
ALTER TABLE tiki_nexus_menu_items RENAME TO nexus_menu_items;

-- de-tikify pigeonholes
ALTER TABLE bit_pigeonholes RENAME TO pigeonholes;
ALTER TABLE bit_pigeonhole_members RENAME TO pigeonhole_members;
ALTER TABLE bit_pigeonhole_settings RENAME TO pigeonhole_settings;

-- de-tikify quicktags
ALTER TABLE tiki_quicktags RENAME TO quicktags;

-- de-tikify quota
ALTER TABLE tiki_quotas RENAME TO quotas;

-- de-tikify rss
ALTER TABLE tiki_rss_feeds RENAME TO rss_feeds;
ALTER TABLE tiki_rss_modules RENAME TO rss_modules;

-- de-tikify shoutbox
ALTER TABLE tiki_shoutbox RENAME TO shoutbox;

-- de-tikify stats
ALTER TABLE tiki_referer_stats RENAME TO stats_referers;

-- de-tikify stickies
ALTER TABLE tiki_stickies RENAME TO stickies;

-- de-tikify themes
ALTER TABLE tiki_theme_control_categs RENAME TO theme_control_categs;
ALTER TABLE tiki_theme_control_sections RENAME TO theme_control_sections;
ALTER TABLE tiki_theme_control_objects RENAME TO theme_control_objects;

-- de-tikify users
ALTER TABLE users_objectpermissions RENAME TO users_object_permissions;
ALTER TABLE users_grouppermissions RENAME TO users_group_permissions;
ALTER TABLE tiki_semaphores RENAME TO users_semaphores;
ALTER TABLE tiki_user_watches RENAME TO users_watches;

-- de-tikify wiki
ALTER TABLE tiki_pages RENAME TO wiki_pages;
ALTER TABLE tiki_extwiki RENAME TO wiki_ext;
ALTER TABLE tiki_received_pages RENAME TO wiki_received_pages;
ALTER TABLE tiki_tags RENAME TO wiki_tags;
ALTER TABLE tiki_copyrights RENAME TO liberty_copyrights;
ALTER TABLE tiki_actionlog RENAME TO wiki_action_log;
ALTER TABLE tiki_page_footnotes RENAME TO wiki_footnotes;
ALTER TABLE tiki_history RENAME TO liberty_content_history;
ALTER TABLE tiki_links RENAME TO liberty_content_links;
{/code}
31-JAN-2006 | user tables | unify table names | [/spider]
SQL 92 | {code source="sql"}
ALTER TABLE users_objectpermissions RENAME TO users_object_permissions;
ALTER TABLE users_grouppermissions RENAME TO users_group_permissions;
ALTER TABLE tiki_semaphores RENAME TO users_semaphores;
ALTER TABLE tiki_user_watches RENAME TO users_watches;
{/code}
29-JAN-2006 | users / liberty | generalize tiki_user_preferences to liberty_content_prefs | [/spiderr]
SQL 92 | {code source="sql"}
ALTER TABLE tiki_user_preferences RENAME TO liberty_content_prefs;
ALTER TABLE liberty_content_prefs RENAME pref_name TO name;
ALTER TABLE liberty_content_prefs ADD content_id INT;
UPDATE liberty_content_prefs SET content_id = (SELECT tc.content_id FROM tiki_content tc INNER JOIN users_users uu ON(tc.content_id=uu.content_id) WHERE uu.user_id=liberty_content_prefs.user_id );
ALTER TABLE liberty_content_prefs DROP user_id;
{/code}
25-JAN-2006 | articles | shorten names of sequence generators | lsces
PHP UPDATE: |{code source="PHP"}
global $gBitSystem;
$current = $gBitSystem->mDb->GenID( "tiki_article_topics_topic_id_seq" );
$gBitSystem->mDb->DropSequence( "tiki_article_topics_topic_id_seq");
$gBitSystem->mDb->CreateSequence( "tiki_article_topics_t_id_seq", $current );
$current = $gBitSystem->mDb->GenID( "tiki_article_types_article_type_id_seq" );
$gBitSystem->mDb->DropSequence( "tiki_article_types_article_type_id_seq");
$gBitSystem->mDb->CreateSequence( "tiki_article_types_a_t_id_seq", $current );
{/code}
23-JAN-2006 | tiki_content | add timestamps for hits and events | lsces
Sql92 UPDATE: |{code source="sql"}
ALTER TABLE tiki_content ADD last_hit INT8;
ALTER TABLE tiki_content ADD event_time INT8;
UPDATE tiki_content SET last_hit = last_modified;
{/code}
23-JAN-2006 | bit_pigeonholes_members | drop custom positioning of items | xing
Sql92 UPDATE: |{code source="sql"}
ALTER TABLE bit_pigeonhole_members DROP COLUMN pos;
{/code}
23-JAN-2006 | users_favorites_map | New favorites map to flag favorite content | spiderr
Sql92 UPDATE: |{code source="sql"}
CREATE TABLE users_favorites_map ( favorite_content_id INT4 NOT NULL, user_id INT4 NOT NULL, PRIMARY KEY (favorite_content_id, user_id) ) ;
CREATE INDEX users_fav_con_idx ON users_favorites_map (favorite_content_id) ;
CREATE INDEX users_fav_user_idx ON users_favorites_map (user_id)
{/code}
23-JAN-2006 | messu_system_message_map | MessuPackage updates for system messages and group_id sending to enable mass scaling | spiderr
Sql92 UPDATE: |{code source="sql"}
CREATE TABLE messu_system_message_map ( msg_id INT NOT NULL, to_user_id INT NOT NULL, is_read VARCHAR(1), is_flagged VARCHAR(1), is_replied VARCHAR(1), priority INT, is_hidden VARCHAR(1), CONSTRAINT tiki_messu_sys_message_ref FOREIGN KEY (msg_id) REFERENCES messu_messages (msg_id), CONSTRAINT tiki_messu_sys_to_user_ref FOREIGN KEY (to_user_id) REFERENCES users_users (user_id) );
ALTER TABLE messu_messages ADD COLUMN group_id INT;
{/code}
23-JAN-2006 |
----------------- END ReleaseOne ------------------
02-NOV-2005 | users_permissions | Add a permission for uploading non-image files in fisheye | spiderr
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions VALUES( 'bit_p_fisheye_upload_nonimages', 'Can upload non-image files', 'editors', 'fisheye' );
{/code}
27-OCT-2005 | users_permissions | Add a permission for creating languages | xing
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions VALUES( 'bit_p_create_languages', 'Can create new languages', 'admin', 'languages' );
{/code}
30-SEP-2005 | Users | Migrate tri-state preference to permissions - added to R1, old feature_custom_theme also kept for now | spiderr
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions VALUES( 'bit_p_custom_home_theme', 'Can modify user homepage theme', 'editors', 'users' );
INSERT INTO users_permissions VALUES( 'bit_p_custom_home_layout', 'Can modify user homepage layout', 'editors', 'users' );
INSERT INTO users_permissions VALUES( 'bit_p_create_css', 'Can create custom style sheets', 'editors', 'users' );
{/code}
22-JUN-2005| ReleaseOne final ships! cvs branch R1 created
21-JUN-2005 | bitweaver / TikiPro name change
postgres UPDATE: |{code source="sql"}
ALTER TABLE users_grouppermissions DROP CONSTRAINT users_groupperm_perm_ref;
ALTER TABLE tiki_content DROP CONSTRAINT tiki_content_type_ref;
ALTER TABLE tiki_attachments DROP CONSTRAINT tiki_attachment_type_ref;
{/code}
Sql92 UPDATE: |{code source="sql"}
UPDATE tiki_module_map SET module_rsrc=replace(module_rsrc, 'tikipackage', 'bitpackage');
UPDATE tiki_content_types SET content_type_guid=replace(content_type_guid,'tiki','bit');
UPDATE tiki_content SET content_type_guid=replace(content_type_guid,'tiki','bit');
UPDATE users_grouppermissions SET perm_name=replace(perm_name,'tiki_','bit_');
UPDATE users_permissions SET perm_name=replace(perm_name,'tiki_','bit_');
UPDATE users_objectpermissions SET perm_name=replace(perm_name,'tiki_','bit_');
UPDATE tiki_plugins SET plugin_guid='bitfile' WHERE plugin_guid='tiki_files';
UPDATE tiki_attachments SET attachment_plugin_guid='bitfile' WHERE attachment_plugin_guid='tiki_files';
{/code}
postgres UPDATE: |{code source="sql"}
ALTER TABLE tiki_content ADD CONSTRAINT "tiki_content_type_ref" FOREIGN KEY (content_type_guid) REFERENCES tiki_content_types(content_type_guid
);
ALTER TABLE users_grouppermissions ADD CONSTRAINT "users_groupperm_perm_ref" FOREIGN KEY (perm_name) REFERENCES users_permissions(perm_name);
ALTER TABLE tiki_attachments ADD CONSTRAINT "tiki_attachment_type_ref" FOREIGN KEY (attachment_plugin_guid) REFERENCES tiki_plugins(plugin_guid
);
{/code}
10-JUN-2005 | categories | migrate all columns in object_type to CONTENT_TYPE_GUID's | spider
Sql92 UPDATE: |{code source="sql"}
UPDATE tiki_categorized_objects SET object_type='tikiblogpost' WHERE object_type='blogpost';
{/code}
20-MAY-2005 | fisheye | Add possiblity to hide tabs and tool buttons from users | xing
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_view_tabs_and_tools', 'Can view tab and tool links', 'registered', 'users');
{/code}
19-MAY-2005 | fisheye | Add granular user administration perms | spiderr
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_edit_users', 'Can edit the information for other users', 'admin', 'users');
{/code}
13-MAY-2005| ReleaseOne Beta 2 Released
12-MAY-2005 | languages | gut unused translation tables from Beta 1 for increased scale | spiderr
Sql92 UPDATE | {code source="sql"}
CREATE TABLE tiki_i18n_languages ( lang_code VARCHAR(32) NOT NULL, native_name VARCHAR(255), english_name VARCHAR(255), is_disabled VARCHAR(1), PRIMARY KEY (lang_code) );
CREATE TABLE tiki_i18n_masters ( source_hash VARCHAR(32) NOT NULL, package VARCHAR(100), created INT8 NOT NULL, source TEXT, PRIMARY KEY (source_hash) );
CREATE TABLE tiki_i18n_strings ( source_hash VARCHAR(32) NOT NULL, lang_code VARCHAR(32) NOT NULL, last_modified INT8 NOT NULL, tran TEXT, PRIMARY KEY (source_hash, lang_code) );
CREATE TABLE tiki_i18n_version_map ( source_hash VARCHAR(32) NOT NULL, version VARCHAR(32), PRIMARY KEY (source_hash) );
{/code}
09-MAY-2005 | fisheye | Restrict changing fisheye gallery thumbnail size | spiderr
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_change_thumbnail_size', 'Can change gallery thumbnail image sizes.', 'editors', 'fisheye');
{/code}
07-APR-2005 | liberty | set default value of hits to NOTNULL that increasing hits works | xing
Sql92 UPDATE: |{code source="sql"}
UPDATE tiki_content SET hits=0 WHERE hits is NULL;
ALTER TABLE tiki_content ALTER hits SET DEFAULT 0;
ALTER TABLE tiki_content ALTER hits SET NOT NULL;
{/code}
09-MAR-2005| liberty | Permission added for printing
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_print', 'Can print content', 'basic', 'liberty');
{/code}
01-MAR-2005| ReleaseOne Beta 1 Released
26-FEB-2005 | liberty & pdf | new permission to control attachment, and centralize pdf generation to a permission, not a feature
Sql92 UPDATE: |{code source="sql"}
INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_content_attachments', 'Can create content attachments', 'registered', 'liberty');
insert into users_permissions (perm_name,perm_desc,level,package) VALUES ('tiki_p_pdf_generation', 'Can create PDF\'s from ', 'registered', 'pdf');
{/code}
23-FEB-2005 | pdf | new permission for PDF
Sql92 UPDATE: |{code source=sql)}
INSERT INTO users_permissions (perm_name,perm_desc,level,package) VALUES ('tiki_p_pdf_generation', 'Can create PDF files', 'registered', 'pdf');
{CODE}
15-FEB-2005 | fisheye | Fisheye has not been released, however hear are a few changes for anyone running out of CVS
Sql92 UPDATE: | {code source=sql)}
ALTER TABLE tiki_fisheye_thumbnail_queue RENAME TO tiki_thumbnail_queue;
ALTER TABLE tiki_thumbnail_queue RENAME image_id TO content_id;
ALTER TABLE tiki_thumbnail_queue RENAME creation_date TO queue_date;
ALTER TABLE tiki_fisheye_image ADD photo_date int8;
{CODE}
28-JAN-2005 | new perms | Added some new permissions, schema was *not* changed
Sql92 UPDATE: | {code source=sql)}
INSERT INTO users_permissions VALUES ( 'tiki_p_user_group_perms', 'Can assign permissions to personal groups', 'editors', 'users' );
INSERT INTO users_permissions VALUES ('tiki_p_user_group_members', 'Can assign users to personal groups', 'registered', 'users' );
INSERT INTO users_permissions VALUES ('tiki_p_user_group_subgroups', 'Can include other groups in groups', 'editors', 'users' );
{/CODE}
bitweaver -CLYDE- Alpha4 Shipped
21-DEC-2004 | group changes | converted all group_name foreign keys to group_id to meet CLYDE requirements of immutable foreign keys
Sql92 UPDATE: | {code source=sql)}
ALTER TABLE users_groups ADD COLUMN user_id INT;
UPDATE users_groups SET user_id=2;
ALTER TABLE users_groups ALTER user_id SET NOT NULL;

ALTER TABLE users_groups ADD COLUMN group_id INT;
CREATE SEQUENCE users_groups_id_seq;
UPDATE users_groups SET group_id=-1 WHERE group_name='Anonymous';
UPDATE users_groups SET group_id=nextval('users_groups_id_seq') WHERE group_id IS NULL;
ALTER TABLE users_groups ALTER group_id SET NOT NULL;

ALTER TABLE users_grouppermissions ADD COLUMN group_id INT;
UPDATE users_grouppermissions SET group_id=(SELECT group_id FROM users_groups WHERE group_name=users_grouppermissions.group_name);
ALTER TABLE users_grouppermissions ALTER group_id SET NOT NULL;
ALTER TABLE users_grouppermissions DROP COLUMN group_name;

ALTER TABLE users_objectpermissions ADD COLUMN group_id INT;
UPDATE users_objectpermissions SET group_id=(SELECT group_id FROM users_groups WHERE group_name=users_objectpermissions.group_name);
ALTER TABLE users_objectpermissions ALTER group_id SET NOT NULL;
ALTER TABLE users_objectpermissions DROP COLUMN group_name;

ALTER TABLE users_usergroups RENAME TO users_groups_map;
-- Alternatively: CREATE TABLE users_groups_map ( user_id INTEGER NOT NULL, group_id INTEGER NOT NULL, PRIMARY KEY (user_id, group_id) );
ALTER TABLE users_groups_map ADD COLUMN group_id INT;
UPDATE users_groups_map SET group_id=(SELECT group_id FROM users_groups WHERE group_name=users_groups_map.group_name);
ALTER TABLE users_groups_map ALTER group_id SET NOT NULL;
ALTER TABLE users_groups_map DROP COLUMN group_name;

ALTER TABLE tiki_group_inclusion RENAME TO users_groups_inclusion;
ALTER TABLE users_groups_inclusion ADD COLUMN group_id INT;
UPDATE users_groups_inclusion SET group_id=(SELECT group_id FROM users_groups WHERE group_name=users_groups_inclusion.group_name);
ALTER TABLE users_groups_inclusion ALTER group_id SET NOT NULL;
ALTER TABLE users_groups_inclusion DROP COLUMN group_name;
ALTER TABLE users_groups_inclusion ADD COLUMN include_group_id INT;
UPDATE users_groups_inclusion SET include_group_id=(SELECT group_id FROM users_groups WHERE group_name=users_groups_inclusion.include_group);
ALTER TABLE users_groups_inclusion ALTER include_group_id SET NOT NULL;
ALTER TABLE users_groups_inclusion DROP COLUMN include_group;

ALTER TABLE users_users ADD COLUMN default_group_id INT;
UPDATE users_users SET default_group_id=(SELECT group_id FROM users_groups WHERE group_name=users_users.default_group);
ALTER TABLE users_users DROP COLUMN default_group;
{/CODE}
bitweaver ReleaseOne Alpha3 Shipped
2-DEC-2004 | tiki_categorized_objects | standardized naming and type to match user_objectpermissions | spiderr
Sql92 UPDATE: | {CODE source=sql}
ALTER TABLE tiki_categorized_objects RENAME type TO object_type;
ALTER TABLE tiki_categorized_objects ADD COLUMN object_id INTEGER;
UPDATE tiki_categorized_objects SET object_id=(SELECT content_id FROM tiki_blog_posts WHERE post_id=tiki_categorized_objects.obj_id);
UPDATE tiki_categorized_objects SET object_type='tikiblogpost' WHERE object_type='blogpost';
UPDATE tiki_categorized_objects SET object_id=(SELECT content_id FROM tiki_content WHERE title=tiki_categorized_objects.obj_id AND content_type_guid='tikipage');
UPDATE tiki_categorized_objects SET object_type='tikipage' WHERE object_type='wiki page';
ALTER TABLE tiki_categorized_objects ALTER object_id SET NOT NULL;
ALTER TABLE tiki_categorized_objects DROP obj_id;
{/CODE}
MySQL UPDATE: | Note: Due to mysql < 4.1 *NOT* supported subselects, you will loose any categorized objects. If you don't want to do this, you will need to get upgraded to MySQL 4.1 and run the updates from the above SQL92 code block. {code source=sql)}
DELETE FROM tiki_categorized_objects;
ALTER TABLE tiki_categorized_objects CHANGE `type` TO `object_type` VARCHAR(20);
ALTER TABLE tiki_categorized_objects ADD COLUMN object_id INTEGER;
ALTER TABLE tiki_categorized_objects CHANGE object_id object_id INTEGER NOT NULL;
ALTER TABLE tiki_categorized_objects DROP obj_id;
{/CODE}
1-DEC-2004 | users_objectpermissions | Changed object_id to be of type I4. Added object_type to be part of the primary key | drewslater

29-NOV-2004 | tiki_history | added format_guid so that format can change between versions | spiderr
29-NOV-2004 | tiki_blobs | added user_id column after much pondering with drewslater | spiderr
29-NOV-2004 | tiki_files | added user_id column after much pondering with drewslater | spiderr
|tiki_semaphores | was accidentally removed. added back, and some columns renamed for minimal name collision | spiderr
18-NOV-2004 | tiki_copyrights | overlooked user_name converted to user_id | spiderr
|tiki_semaphores | was accidentally removed. added back, and some columns renamed for minimal name collision | spiderr
|tiki_tags | data column changed from BLOB to standard TEXT | spiderr
| tiki_content_types | now has complete columns to reverse engineer an content_id | spiderr
| tiki_plugins | has maintainer_url added | spider
|tiki_structures | was moved to Liberty from wiki and generalized with content_id's | spider ||