ReleaseOneSchemaChangelog

Database changes in ReleaseOne

Created by: spiderr, Last modification: 31 Jan 2006 (20:14 UTC)

bitweaver ReleaseOne Schema changes


This is an ongoing list of all changes to all DataDict arrays in a schema_inc in all packages that occured during ReleaseOne.


Date
Table
Reason
Who
02-NOV-2005 users_permissions Add a permission for uploading non-image files in fisheye spiderr
Sql92 UPDATE:

INSERT INTO users_permissions VALUES( 'bit_p_fisheye_upload_nonimages', 'Can upload non-image files', 'editors', 'fisheye' );
27-OCT-2005 users_permissions Add a permission for creating languages xing
Sql92 UPDATE:

INSERT INTO users_permissions VALUES( 'bit_p_create_languages', 'Can create new languages', 'admin', 'languages' );
30-SEP-2005 Users Migrate tri-state preference to permissions - added to R1, old feature_custom_theme also kept for now spiderr
Sql92 UPDATE:

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' );
22-JUN-2005 ReleaseOne final ships! cvs branch R1 created
21-JUN-2005 bitweaver / TikiPro name change
postgres UPDATE:

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;
Sql92 UPDATE:

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';
postgres UPDATE:

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
);
10-JUN-2005 categories migrate all columns in object_type to CONTENT_TYPE_GUID's spider
Sql92 UPDATE:

UPDATE tiki_categorized_objects SET object_type='tikiblogpost' WHERE object_type='blogpost';
20-MAY-2005 fisheye Add possiblity to hide tabs and tool buttons from users xing
Sql92 UPDATE:

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');
19-MAY-2005 fisheye Add granular user administration perms spiderr
Sql92 UPDATE:

INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_edit_users', 'Can edit the information for other users', 'admin', 'users');
13-MAY-2005 ReleaseOne Beta 2 Released
12-MAY-2005 languages gut unused translation tables from Beta 1 for increased scale spiderr
Sql92 UPDATE

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) );
09-MAY-2005 fisheye Restrict changing fisheye gallery thumbnail size spiderr
Sql92 UPDATE:

INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_change_thumbnail_size', 'Can change gallery thumbnail image sizes.', 'editors', 'fisheye');
07-APR-2005 liberty set default value of hits to NOTNULL that increasing hits works xing
Sql92 UPDATE:

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;
09-MAR-2005 liberty Permission added for printing
Sql92 UPDATE:

INSERT INTO users_permissions (perm_name, perm_desc, level, package) VALUES('tiki_p_print', 'Can print content', 'basic', 'liberty');
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:

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');
23-FEB-2005 pdf new permission for PDF
Sql92 UPDATE:

INSERT INTO users_permissions (perm_name,perm_desc,level,package) VALUES ('tiki_p_pdf_generation', 'Can create PDF files', 'registered', 'pdf');
15-FEB-2005 fisheye Fisheye has not been released, however hear are a few changes for anyone running out of CVS
Sql92 UPDATE:

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;
28-JAN-2005 new perms Added some new permissions, schema was *not* changed
Sql92 UPDATE:

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' );
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:

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;
bitweaver ReleaseOne Alpha3 Shipped
2-DEC-2004 tiki_categorized_objects standardized naming and type to match user_objectpermissions spiderr
Sql92 UPDATE:

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;
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.

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;
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