Index: openacs-4/packages/bug-tracker/sql/postgresql/bug-tracker-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/bug-tracker/sql/postgresql/bug-tracker-create.sql,v
diff -u -r1.6 -r1.7
--- openacs-4/packages/bug-tracker/sql/postgresql/bug-tracker-create.sql	9 Dec 2002 21:24:01 -0000	1.6
+++ openacs-4/packages/bug-tracker/sql/postgresql/bug-tracker-create.sql	5 Mar 2003 17:40:28 -0000	1.7
@@ -14,73 +14,174 @@
   email_subject_name            text,
   maintainer                    integer 
                                 constraint bt_projects_maintainer_fk
-                                references users(user_id)
+                                references users(user_id),
+  folder_id                     integer
+                                constraint bt_projects_folder_fk
+                                references cr_folders(folder_id),
+  root_keyword_id               integer
+                                constraint bt_projects_keyword_fk
+                                references cr_keywords(keyword_id)
 );
 
-create function bt_project__new(
+create or replace function bt_project__new(
     integer                      -- package_id
 ) returns integer 
 as '
 declare
-    new__package_id              alias for $1;
-    rec                          record;
-    v_count                      integer;
+    p_package_id                alias for $1;
+    v_count                     integer;
+    v_instance_name             varchar;
+    v_creation_user             integer;
+    v_creation_ip               varchar;
+    v_folder_id                 integer;
+    v_keyword_id                integer;
 begin
     select count(*)
     into   v_count
     from   bt_projects
-    where  project_id = new__package_id;
+    where  project_id = p_package_id;
 
     if v_count > 0 then
         return 0;
     end if;
 
-    -- insert the row into bt_projects
-    insert into bt_projects (project_id) values (new__package_id);
+    -- get instance name for the content folder
+    select p.instance_name, o.creation_user, o.creation_ip
+    into   v_instance_name, v_creation_user, v_creation_ip
+    from   apm_packages p join acs_objects o on (p.package_id = o.object_id)
+    where  p.package_id = p_package_id;
 
-    -- copy over the default severity/priority codes
-    for rec in select * from bt_severity_codes where project_id is null loop
-        insert into bt_severity_codes 
-               (severity_id, project_id, severity_name, sort_order, default_p) 
-        select acs_object_id_seq.nextval, new__package_id, rec.severity_name, rec.sort_order, rec.default_p;
-    end loop;
+    -- create a root CR folder
+    v_folder_id := content_folder__new(
+        ''bug_tracker_''||p_package_id,        -- name
+        v_instance_name,                       -- label
+        null,                                  -- description
+        content_item_globals.c_root_folder_id  -- parent_bi
+    );
 
-    for rec in select * from bt_priority_codes where project_id is null loop
-        insert into bt_priority_codes 
-               (priority_id, project_id, priority_name, sort_order, default_p) 
-        select acs_object_id_seq.nextval, new__package_id, rec.priority_name, rec.sort_order, rec.default_p;
-    end loop;
+    -- register our content type
+    PERFORM content_folder__register_content_type (
+        v_folder_id,          -- folder_id
+        ''bt_bug_revision'',  -- content_type
+        ''t''                 -- include_subtypes
+    );
 
+    -- create the instance root keyword
+    v_keyword_id := content_keyword__new(
+        v_instance_name,                -- heading
+        null,                           -- description
+        null,                           -- parent_id
+        null,                           -- keyword_id
+        current_timestamp,              -- creation_date
+        v_creation_user,                -- creation_user
+        v_creation_ip,                  -- creation_ip
+        ''content_keyword''             -- object_type
+    );
+
+    -- insert the row into bt_projects
+    insert into bt_projects 
+        (project_id, folder_id, root_keyword_id) 
+    values 
+        (p_package_id, v_folder_id, v_keyword_id);
+
     -- Create a General component to start with
     insert into bt_components (component_id, project_id, component_name)
-    select acs_object_id_seq.nextval, new__package_id, ''General'';
+    select acs_object_id_seq.nextval, p_package_id, ''General'';
 
     return 0;
 end;
 ' language 'plpgsql';
 
-create function bt_project__delete(
-    integer                      -- package_id
-) returns integer 
+
+create or replace function bt_project__delete(
+    integer                 -- project_id
+) returns integer
 as '
 declare
-    delete__package_id           alias for $1;
+    p_project_id          alias for $1;
+    v_folder_id           integer;
+    v_root_keyword_id     integer;
+    rec                   record;
 begin
-    -- delete severity/priority codes
-    delete from bt_severity_codes where project_id = delete__package_id;
-    delete from bt_priority_codes where project_id = delete__package_id;
+    -- get the content folder for this instance
+    select folder_id, root_keyword_id
+    into   v_folder_id, v_root_keyword_id
+    from   bt_projects
+    where  project_id = p_project_id;
 
-    -- delete the  row from bt_projects
-    delete from bt_projects where project_id = delete__package_id;
+    -- This gets done in tcl before we are called ... for now
+    --  Delete the bugs
+    -- for rec in select item_id from cr_items where parent_id = v_folder_id
+    -- loop
+    --     perform bt_bug__delete(rec.item_id);
+    -- end loop;
 
+    -- Delete the patches
+    for rec in select patch_id from bt_patches where project_id = p_project_id
+    loop
+         perform bt_patch__delete(rec.patch_id);
+    end loop;
+
+    -- delete the content folder
+    raise notice ''about to delete content_folder.'';
+    perform content_folder__delete(v_folder_id);
+
+    -- delete the projects keywords
+    perform bt_projects__keywords_delete(p_project_id, ''t'');
+
+    -- These tables should really be set up to cascade
+    delete from bt_versions where project_id = p_project_id;
+    delete from bt_components where project_id = p_project_id;
+    delete from bt_user_prefs where project_id = p_project_id;      
+
+    delete from bt_projects where project_id = p_project_id;   
+
     return 0;
 end;
 ' language 'plpgsql';
 
-       
+create or replace function bt_project__keywords_delete(
+    integer,                 -- project_id
+    bool                     -- delete_root_p
+) returns integer
+as '
+declare
+    p_project_id          alias for $1;
+    p_delete_root_p       alias for $1;
+    v_root_keyword_id     integer;
+    rec                   record;
+begin
+    -- get the content folder for this instance
+    select root_keyword_id
+    into   v_root_keyword_id
+    from   bt_projects
+    where  project_id = p_project_id;
 
-    
+    -- if we are deleting the root, remove it from the project as well
+    if p_delete_root_p = 1 then
+        update bt_projects 
+        set    root_keyword_id = null 
+        where  project_id = p_project_id;
+    end if;
 
+    -- delete the projects keywords
+    for rec in 
+        select k2.keyword_id
+        from   cr_keywords k1, cr_keywords k2
+        where  k1.keyword_id = v_root_keyword_id
+        and    k2.tree_sortkey between k1.tree_sortkey and tree_right(k1.tree_sortkey)
+        order  by length(k2.tree_sortkey) desc
+    loop
+        if (p_delete_root_p = 1) or (rec.keyword_id != v_root_keyword_id) then
+            perform content_keyword__delete(rec.keyword_id);
+        end if;
+    end loop;
+
+    return 0;
+end;
+' language 'plpgsql';
+
+
 create table bt_versions (
   version_id                    integer not null
                                 constraint bt_versions_pk
@@ -114,7 +215,7 @@
 
 -- but we just make a stored function that alters the active version
 
-create function bt_version__set_active (
+create or replace function bt_version__set_active (
    integer                       -- active_version_id
 ) returns integer 
 as '
@@ -152,385 +253,349 @@
                                 references users(user_id)
 );
 
+-- default keywords per keyword parent
+-- e.g. default priority, default severity, etc.
 
-create function bt_component__default_assignee(
-   integer                      -- component_id
-) returns integer
-as '
-declare
-    p_component_id              alias for $1;
-    v_assignee                  integer;
-begin
-    select maintainer
-    into   v_assignee
-    from   bt_components
-    where  component_id = p_component_id;
+create table bt_default_keywords (
+  project_id                    integer not null
+                                constraint bt_default_keywords_project_fk
+                                references bt_projects(project_id)
+                                on delete cascade,
+  parent_id                     integer not null
+                                constraint bt_default_keyw_parent_keyw_fk
+                                references cr_keywords(keyword_id)
+                                on delete cascade,
+  keyword_id                    integer not null
+                                constraint bt_default_keyw_keyword_fk
+                                references cr_keywords(keyword_id)
+                                on delete cascade,
+  constraint bt_default_keywords_prj_par_un
+  unique (project_id, parent_id)
+);
 
-    if v_assignee is null then
-        select p.maintainer
-        into   v_assignee
-        from   bt_projects p, bt_components c
-        where  p.project_id = c.project_id
-        and    c.component_id = p_component_id;
-    end if;
+create index bt_default_keyw_parent_id_idx on bt_default_keywords(parent_id);
+create index bt_default_keyw_keyword_id_idx on bt_default_keywords(keyword_id);
 
-    return v_assignee;
-end;
-' language 'plpgsql';
-
-
-create table bt_severity_codes (
-  severity_id                   integer not null
-                                constraint bt_severity_codes_pk
-                                primary key,
-  project_id                    integer 
-                                constraint bt_severity_codes_projects_fk
-                                references bt_projects(project_id),
-  severity_name                 varchar(500) not null,
-  sort_order                    integer not null,
-  default_p                     char(1) not null
-                                constraint bt_severity_codes_default_p_ck
-                                check (default_p in ('t','f'))
-                                default 'f',
-  constraint bt_severity_codes_name_un
-  unique(project_id, severity_name),
-  constraint bt_severity_codes_sort_order_un
-  unique(project_id, sort_order)
+-- content_item subtype
+create table bt_bugs(
+  bug_id                        integer
+                                constraint bt_bug_pk
+                                primary key
+                                constraint bt_bug_bt_bug_fk
+                                references cr_items(item_id)
+                                on delete cascade,
+  -- this is the only column we really add here
+  bug_number                    integer,
+  -- the comment from the initial action
+  -- denormalized from a far-fetched workflow join
+  comment_content               text,
+  comment_format                varchar(200),
+  -- denormalized from cr_items
+  parent_id                     integer,
+  live_revision_id              integer,
+  -- denormalized from cr_revisions.title
+  summary                       varchar(1000),
+  -- denormalized from bt_projects
+  project_id                    integer,
+  -- denormalized from bt_bug_revisions
+  component_id                  integer,
+  resolution                    varchar(50),
+  user_agent                    varchar(500),
+  found_in_version              integer,
+  fix_for_version               integer,
+  fixed_in_version              integer,
+  -- denormalized from acs_objects
+  creation_date                 timestamp,
+  creation_user                 integer,
+  -- constraint
+  constraint bt_bug_parent_id_bug_number_un
+  unique (parent_id, bug_number)
 );
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p)
-select acs_object_id_seq.nextval, null, 'Critical', 1, 'f';
+-- LARS:
+-- we need to figure out which ones of these will be used by the query optimizer
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Major', 2, 'f';
+create index bt_bugs_proj_id_bug_number_idx on bt_bugs(project_id, bug_number);
+create index bt_bugs_bug_number_idx on bt_bugs(bug_number);
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Normal', 3, 't';
+create index bt_bugs_proj_id_fix_for_idx on bt_bugs(project_id, fix_for_version);
+create index bt_bugs_fix_for_version_idx on bt_bugs(fix_for_version);
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Minor', 4, 'f';
+create index bt_bugs_proj_id_crea_date_idx on bt_bugs(project_id, creation_date);
+create index bt_bugs_creation_date_idx on bt_bugs(creation_date);
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Trivial', 5, 'f';
+-- Create the bug content item object type
 
-insert into bt_severity_codes (severity_id, project_id, severity_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Enhancement', 6, 'f';
-
-
-create table bt_priority_codes (
-  priority_id                   integer not null
-                                constraint bt_priority_codes_pk
-                                primary key,
-  project_id                    integer 
-                                constraint bt_priority_codes_projects_fk
-                                references bt_projects(project_id),
-  priority_name                 varchar(500) not null,
-  sort_order                    integer not null,
-  default_p                     char(1) not null
-                                constraint bt_priority_codes_default_p_ck
-                                check (default_p in ('t','f'))
-                                default 'f',
-  constraint bt_priority_codes_name_un
-  unique(project_id, priority_name),
-  constraint bt_priority_codes_sort_order_un
-  unique(project_id, sort_order)
+select acs_object_type__create_type (
+    'bt_bug',
+    'Bug',
+    'Bugs',
+    'acs_object',
+    'bt_bugs',
+    'bug_id',
+    null,
+    'f',
+    null,
+    null
 );
 
-insert into bt_priority_codes (priority_id, project_id, priority_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'High', 1, 'f';
 
-insert into bt_priority_codes (priority_id, project_id, priority_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Normal', 2, 't';
-
-insert into bt_priority_codes (priority_id, project_id, priority_name, sort_order, default_p) 
-select acs_object_id_seq.nextval, null, 'Low', 3, 'f';
-
-
-
--- We maintain a public bug number, different from the 
--- bug_id, because bug_id is drawn on the acs_objects sequence
--- which is used for tons of other things. This gives us cleaner
--- bug numbers.
-
-create sequence t_bt_bug_number_seq;
-create view bt_bug_number_seq as
-select nextval('t_bt_bug_number_seq') as nextval;
-
-create table bt_bugs (
-  bug_id                        integer 
-                                constraint bt_bugs_pk
+-- content_revision specialization
+create table bt_bug_revisions (
+  bug_revision_id               integer 
+                                constraint bt_bug_rev_pk
                                 primary key
-                                constraint bt_bugs_bug_id_fk
-                                references acs_objects(object_id),
-  project_id                    integer 
-                                constraint bt_bugs_projects_fk
-                                references bt_projects(project_id),
-
+                                constraint bt_bug_rev_bug_id_fk
+                                references cr_revisions(revision_id)
+                                on delete cascade,
   component_id                  integer 
-                                constraint bt_bugs_components_fk
+                                constraint bt_bug_rev_components_fk
                                 references bt_components(component_id),
-  bug_number                    integer not null,
-  status                        varchar(50) not null
-                                constraint bt_bugs_status_ck
-                                check (status in ('open', 'resolved', 'closed'))
-                                default 'open',
   resolution                    varchar(50)
-                                constraint bt_bugs_resolution_ck
+                                constraint bt_bug_rev_resolution_ck
                                 check (resolution is null or 
                                        resolution in ('fixed','bydesign','wontfix','postponed','duplicate','norepro','needinfo')),
-  bug_type                      varchar(50) not null
-                                constraint bt_bugs_bug_type_ck
-                                check (bug_type in ('bug', 'suggestion','todo')),
-  severity                      integer not null
-                                constraint bt_bugs_severity_fk
-                                references bt_severity_codes(severity_id),
-  priority                      integer not null
-                                constraint bt_bugs_priority_fk
-                                references bt_priority_codes(priority_id),
   user_agent                    varchar(500),
-  original_estimate_minutes     integer,
-  latest_estimate_minutes       integer,
-  elapsed_time_minutes          integer,
   found_in_version              integer
-                                constraint bt_bugs_found_in_version_fk   
+                                constraint bt_bug_rev_found_in_version_fk   
                                 references bt_versions(version_id), 
   fix_for_version               integer
-                                constraint bt_bugs_fix_for_version_fk   
+                                constraint bt_bug_rev_fix_for_version_fk   
                                 references bt_versions(version_id), 
   fixed_in_version              integer
-                                constraint bt_bugs_fixed_in_version_fk   
-                                references bt_versions(version_id), 
-  summary                       varchar(500) not null,                                
-  assignee                      integer
-                                constraint bt_bug_assignee_fk
-                                references users(user_id),
-  constraint bt_bugs_bug_number_un
-  unique (project_id, bug_number)
+                                constraint bt_bug_rev_fixed_in_version_fk   
+                                references bt_versions(version_id)
 );
 
-create table bt_bug_actions (
-  action_id                     integer not null
-                                constraint bt_bug_actions_pk
-                                primary key,
-  bug_id                        integer not null
-                                constraint bt_bug_actions_bug_fk
-                                references bt_bugs(bug_id)
-                                on delete cascade,
-  action                        varchar(50)
-                                constraint bt_bug_actions_action_ck
-                                check (action in ('open','edit','comment','reassign','resolve','reopen','close')),
-  resolution                    varchar(50)
-                                constraint bt_bugs_actions_resolution_ck
-                                check (resolution is null or 
-                                       resolution in ('fixed','bydesign','wontfix','postponed','duplicate','norepro','needinfo')),
-  actor                         integer not null
-                                constraint bt_bug_actions_actor_fk
-                                references users(user_id),
-  action_date                   timestamp not null
-                                default now(),
-  comment                       text,
-  comment_format                varchar(30) default 'plain' not null
-                                constraint  bt_bug_actions_comment_format_ck
-                                check (comment_format in ('html', 'plain', 'pre'))
+-- Create the bug revision content type
+
+select content_type__create_type (
+    'bt_bug_revision',
+    'content_revision',
+    'Bug Revision',
+    'Bug Revisions',
+    'btbug_revisions',
+    'bug_revision_id',
+    'content_revision.revision_name'
 );
 
--- Create the bt_bug object type
+select define_function_args ('bt_bug__new','bug_id,bug_number,package_id,component_id,found_in_version,summary,user_agent,comment_content,comment_formt,creation_date,creation_user,creation_ip,item_subtype;bt_bug,content_type;bt_bug_revision');
 
-create function inline_0 ()
-returns integer as '
-begin
-    PERFORM acs_object_type__create_type (
-	''bt_bug'',
-	''Bug'',
-	''Bugs'',
-	''acs_object'',
-	''bt_bugs'',
-	''bug_id'',
-	null,
-	''f'',
-	null,
-	''bt_bug__name''
-	);
-
-    return 0;
-end;' language 'plpgsql';
-
-select inline_0 ();
-
-drop function inline_0 ();
-
-
-create function bt_bug__new(
+create or replace function bt_bug__new(
     integer,     -- bug_id
-    integer,     -- project_id
+    integer,     -- bug_number
+    integer,     -- package_id
     integer,     -- component_id
-    varchar,     -- bug_type 
-    integer,     -- severity
-    integer,     -- priority
     integer,     -- found_in_version
     varchar,     -- summary
-    text,        -- description
-    varchar,     -- desc_format
     varchar,     -- user_agent
+    text,        -- comment_content
+    varchar,     -- comment_format
+    timestamp,   -- creation_date
     integer,     -- creation_user
-    varchar      -- creation_ip
+    varchar,     -- creation_ip
+    varchar,     -- item_subtype
+    varchar      -- content_type
 ) returns int
 as '
 declare
     p_bug_id                    alias for $1;
-    p_project_id                alias for $2;
-    p_component_id              alias for $3;
-    p_bug_type                  alias for $4;
-    p_severity                  alias for $5;
-    p_priority                  alias for $6;
-    p_found_in_version          alias for $7;
-    p_summary                   alias for $8;
-    p_description               alias for $9;
-    p_desc_format               alias for $10;
-    p_user_agent                alias for $11;
-    p_creation_user             alias for $12;
-    p_creation_ip               alias for $13;
+    p_bug_number                alias for $2;
+    p_package_id                alias for $3;
+    p_component_id              alias for $4;
+    p_found_in_version          alias for $5;
+    p_summary                   alias for $6;
+    p_user_agent                alias for $7;
+    p_comment_content           alias for $8;
+    p_comment_format            alias for $9;
+    p_creation_date             alias for $10;
+    p_creation_user             alias for $11;
+    p_creation_ip               alias for $12;
+    p_item_subtype              alias for $13;
+    p_content_type              alias for $14;
+
     v_bug_id                    integer;
+    v_revision_id               integer;
     v_bug_number                integer;
-    v_assignee                  integer;
-    v_action_id                 integer;
+    v_folder_id                 integer;
 begin
-    v_assignee := bt_component__default_assignee(p_component_id);
+    -- get the content folder for this instance
+    select folder_id
+    into   v_folder_id
+    from   bt_projects
+    where  project_id = p_package_id;
 
-    v_bug_id := acs_object__new(
-        p_bug_id,               -- object_id
-        ''bt_bug'',             -- object_type
-        now(),                  -- creation_date
-        p_creation_user,        -- creation_user
-        p_creation_ip,          -- creation_ip
-        p_project_id,           -- context_id
-        ''t''                   -- security_inherit_p
+    -- get bug_number
+    if p_bug_number is null then
+      select coalesce(max(bug_number),0) + 1
+      into   v_bug_number
+      from   bt_bugs
+      where  parent_id = v_folder_id;
+    else
+      v_bug_number := p_bug_number;
+    end if;
+
+    -- create the content item
+    v_bug_id := content_item__new(
+        v_bug_number,              -- name
+        v_folder_id,               -- parent_id
+        p_bug_id,                  -- item_id
+        null,                      -- locale        
+        p_creation_date,           -- creation_date
+        p_creation_user,           -- creation_user
+        v_folder_id,               -- context_id
+        p_creation_ip,             -- creation_ip
+        p_item_subtype,            -- item_subtype
+        p_content_type,            -- content_type
+        null,                      -- title
+        null,                      -- description
+        null,                      -- mime_type
+        null,                      -- nls_language
+        null                       -- data
     );
 
-    select coalesce(max(bug_number),0) + 1
-    into   v_bug_number
-    from   bt_bugs
-    where  project_id = p_project_id;
-
+    -- create the item type row
     insert into bt_bugs
-        (bug_id, project_id, component_id,  bug_number, bug_type, severity, assignee,
-         priority, found_in_version, summary, user_agent)
+        (bug_id, bug_number, comment_content, comment_format, parent_id, project_id, creation_date, creation_user)
     values
-        (v_bug_id, p_project_id, p_component_id, v_bug_number, p_bug_type, p_severity, v_assignee,
-        p_priority, p_found_in_version, p_summary, p_user_agent);
+        (v_bug_id, v_bug_number, p_comment_content, p_comment_format, v_folder_id, p_package_id, p_creation_date, p_creation_user);
 
-    select nextval(''t_acs_object_id_seq'') 
-    into   v_action_id;
+    -- create the initial revision
+    v_revision_id := bt_bug_revision__new(
+        null,                      -- bug_revision_id
+        v_bug_id,                  -- bug_id
+        p_component_id,            -- component_id
+        p_found_in_version,        -- found_in_version
+        null,                      -- fix_for_version
+        null,                      -- fixed_in_version
+        null,                      -- resolution
+        p_user_agent,              -- user_agent
+        p_summary,                 -- summary
+        p_creation_date,           -- creation_date
+        p_creation_user,           -- creation_user
+        p_creation_ip              -- creation_ip
+    );
 
-    insert into bt_bug_actions
-        (action_id, bug_id, action, actor, comment, comment_format)
-    values
-        (v_action_id, v_bug_id, ''open'', p_creation_user, p_description, p_desc_format);
-        
-    return 0;
+    return v_bug_id;
 end;
 ' language 'plpgsql';
 
 
-create function bt_bug__name(
+create or replace function bt_bug__delete(
    integer                      -- bug_id
-) returns varchar
+) returns integer
 as '
 declare
-   name__bug_id                 alias for $1;
-   v_name                       varchar;
+    p_bug_id                    alias for $1;
+    v_case_id                   integer;
+    rec                         record;
 begin
-   select summary
-   into   v_name
-   from   bt_bugs
-   where  bug_id = name__bug_id;
+    -- Every bug is associated with a workflow case
+    select case_id 
+    into   v_case_id 
+    from   workflow_cases 
+    where  object_id = p_bug_id;
 
-   return v_name;
-end;
-' language 'plpgsql';
+    perform workflow_case__delete(v_case_id);
 
+    -- Every bug may have notifications attached to it
+    -- and there is one column in the notificaitons datamodel that doesn''t
+    -- cascade
+    for rec in select notification_id from notifications 
+               where response_id = p_bug_id loop
 
-create function bt_bug__delete(
-   integer                      -- bug_id
-) returns integer
-as '
-declare
-    delete__bug_id              alias for $1;
-begin
-    perform acs_object__delete(delete__bug_id);
+        perform notification__delete (rec.notification_id);
+    end loop;
 
+    -- unset live & latest revision
+--    update cr_items
+--    set    live_revision = null,
+--           latest_revision = null
+--    where  item_id = p_bug_id;
+
+    perform content_item__delete(p_bug_id);
+
     return 0;
 end;
 ' language 'plpgsql';
 
 
-create function bt_bug__status_sort_order(
-    varchar                     -- status
-) returns integer
-as '
-declare
-    p_status                    alias for $1;
-    v_sort_order                integer;
-begin
-    v_sort_order := case p_status
-        when ''open''     then 1
-        when ''resolved'' then 2
-        when ''closed''   then 3
-                          else 4
-    end;
-    
-    return v_sort_order;
-end;
-' language 'plpgsql';
 
 
-create function bt_bug__bug_type_sort_order(
-    varchar                     -- bug_type
-) returns integer
+create or replace function bt_bug_revision__new(
+    integer,        -- bug_revision_id
+    integer,        -- bug_id
+    integer,        -- component_id
+    integer,        -- found_in_version
+    integer,        -- fix_for_version
+    integer,        -- fixed_in_version
+    varchar,        -- resolution
+    varchar,        -- user_agent
+    varchar,        -- summary
+    timestamp,      -- creation_date
+    integer,        -- creation_user
+    varchar         -- creation_ip
+) returns int
 as '
 declare
-    p_bug_type                  alias for $1;
-    v_sort_order                integer;
+    p_bug_revision_id       alias for $1;
+    p_bug_id                alias for $2;
+    p_component_id          alias for $3;
+    p_found_in_version      alias for $4;
+    p_fix_for_version       alias for $5;
+    p_fixed_in_version      alias for $6;
+    p_resolution            alias for $7;
+    p_user_agent            alias for $8;
+    p_summary               alias for $9;
+    p_creation_date         alias for $10;
+    p_creation_user         alias for $11;
+    p_creation_ip           alias for $12;
+
+    v_revision_id               integer;
 begin
-    v_sort_order := case p_bug_type
-        when ''bug''        then 1
-        when ''suggestion'' then 2
-        when ''todo''       then 3
-                            else 4
-    end;
+    -- create the initial revision
+    v_revision_id := content_revision__new(
+        p_summary,              -- title
+        null,                   -- description
+        now(),                  -- publish_date
+        null,                   -- mime_type
+        null,                   -- nls_language        
+        null,                   -- new_data
+        p_bug_id,               -- item_id
+        p_bug_revision_id,      -- revision_id
+        p_creation_date,        -- creation_date
+        p_creation_user,        -- creation_user
+        p_creation_ip           -- creation_ip
+    );
 
-    return v_sort_order;
-end;
-' language 'plpgsql';
+    -- insert into the bug-specific revision table
+    insert into bt_bug_revisions 
+        (bug_revision_id, component_id, resolution, user_agent, found_in_version, fix_for_version, fixed_in_version)
+    values
+        (v_revision_id, p_component_id, p_resolution, p_user_agent, p_found_in_version, p_fix_for_version, p_fixed_in_version);
 
+    -- make this revision live
+    PERFORM content_item__set_live_revision(v_revision_id);
 
--- In SDM: sdm_patches
--- In SDM, this is a relationship between a general comment and a ticket
---         supposedly the patch itself is stored as a special comment
--- In BT: Probably something similar, not sure.
+    -- update the cache
+    update bt_bugs
+    set    live_revision_id = v_revision_id,
+           summary = p_summary,
+           component_id = p_component_id,
+           resolution = p_resolution,
+           user_agent = p_user_agent,
+           found_in_version = p_found_in_version,
+           fix_for_version = p_fix_for_version,
+           fixed_in_version = p_fixed_in_version
+    where  bug_id = p_bug_id;
 
--- In SDM: sdm_ticket_ratings
--- In BT: We'll leave that out for now, but supposedly we could use a modified 
---        version of my ratings package from pinds.com
+    return v_revision_id;
+end;
+' language 'plpgsql';
 
--- All of the following should be doable with acs_rels of some sort
--- (I'm not too strong in that data model, but I suppose I'll learn over the next few days)
 
--- sdm_ticket_assignments
--- sdm_bug_release_maps
--- sdm_user_ticket_interest_map
--- sdm_user_module_interest_map
--- sdm_user_package_interest_map
--- sdm_related_tickets_map
 
 
--- In SDM: sdm_notifications
---         This seems to be a table to hold notifications until they're actually sent out in batch
---         depending on the user's preferences
--- In BT: We'd probably do something similar.
-
--- In SDM: sdm_notification_prefs
--- In BT: 
 create table bt_user_prefs (
   user_id                       integer not null
                                 constraint bt_user_prefs_user_id_fk
@@ -545,93 +610,79 @@
   primary key (user_id, project_id)
 );
 
--- For stability, URLs contain patch numbers rather than ACS Object ids.
--- This avoids dependence on the ACS kernel and makes upgrades easier.
-create sequence t_bt_patch_number_seq;
-create view bt_patch_number_seq as
-select nextval('t_bt_patch_number_seq') as nextval;
 
 create table bt_patches (
-       patch_id                 integer 
-                                constraint bt_patches_pk
-                                primary key
-                                constraint bt_patches_pid_fk
-                                references acs_objects(object_id),
-       patch_number             integer not null,
-       project_id               integer
-                                constraint bt_patches_projects_fk
-                                references bt_projects(project_id),
-       component_id             integer
-                                constraint bt_patches_components_fk
-                                references bt_components(component_id),
-       summary                  text,
-       content                  text,
-       generated_from_version   integer
-                                constraint bt_patches_vid_fk
-                                references bt_versions(version_id),
-       apply_to_version         integer
-                                constraint bt_patchs_apply_to_version_fk   
-                                references bt_versions(version_id), 
-       applied_to_version       integer
-                                constraint bt_patchs_applied_to_version_fk   
-                                references bt_versions(version_id), 
-       status                   varchar(50) not null
-                                constraint bt_patchs_status_ck
-                                check (status in ('open', 'accepted', 'refused', 'deleted'))
-                                default 'open',
-       constraint bt_patches_un
-       unique(patch_number, project_id)
+  patch_id                 integer 
+                           constraint bt_patches_pk
+                           primary key
+                           constraint bt_patches_pid_fk
+                           references acs_objects(object_id),
+  patch_number             integer not null,
+  project_id               integer
+                           constraint bt_patches_projects_fk
+                           references bt_projects(project_id),
+  component_id             integer
+                           constraint bt_patches_components_fk
+                           references bt_components(component_id),
+  summary                  text,
+  content                  text,
+  generated_from_version   integer
+                           constraint bt_patches_vid_fk
+                           references bt_versions(version_id),
+  apply_to_version         integer
+                           constraint bt_patchs_apply_to_version_fk   
+                           references bt_versions(version_id), 
+  applied_to_version       integer
+                           constraint bt_patchs_applied_to_version_fk   
+                           references bt_versions(version_id), 
+  status                   varchar(50) not null
+                           constraint bt_patchs_status_ck
+                           check (status in ('open', 'accepted', 'refused', 'deleted'))
+                           default 'open',
+  constraint bt_patches_un
+  unique(patch_number, project_id)
 );
 
 create table bt_patch_actions (
-       action_id                integer not null
-                                constraint bt_patch_actions_pk
-                                primary key,
-       patch_id                 integer not null
-                                constraint bt_patch_actions_patch_fk
-                                references bt_patches(patch_id)
-                                on delete cascade,
-       action                   varchar(50)
-                                constraint bt_patch_actions_action_ck
-                                check (action in ('open', 'edit', 'comment', 'accept', 
-                                                  'reopen', 'refuse', 'delete')) 
-                                default 'open',
-       actor                    integer not null
-                                constraint bt_patch_actions_actor_fk
-                                references users(user_id),
-       action_date              timestamp not null
-                                default now(),
-       comment                  text,
-       comment_format           varchar(30) default 'plain' not null
-                                constraint  bt_patch_actions_comment_format_ck
-                                check (comment_format in ('html', 'plain', 'pre'))
+  action_id                integer not null
+                           constraint bt_patch_actions_pk
+                           primary key,
+  patch_id                 integer not null
+                           constraint bt_patch_actions_patch_fk
+                           references bt_patches(patch_id)
+                           on delete cascade,
+  action                   varchar(50)
+                           constraint bt_patch_actions_action_ck
+                           check (action in ('open', 'edit', 'comment', 'accept', 
+                                             'reopen', 'refuse', 'delete')) 
+                           default 'open',
+  actor                    integer not null
+                           constraint bt_patch_actions_actor_fk
+                           references users(user_id),
+  action_date              timestamp not null
+                           default now(),
+  comment_text             text,
+  comment_format           varchar(30) default 'plain' not null
+                           constraint  bt_patch_actions_comment_format_ck
+                           check (comment_format in ('html', 'plain', 'pre'))
 );
 
 -- Create the bt_patch object type
-create function inline_0 ()
-returns integer as '
-begin
-    PERFORM acs_object_type__create_type (
-	''bt_patch'',
-	''Patch'',
-	''Patches'',
-	''acs_object'',
-	''bt_patches'',
-	''patch_id'',
-	null,
-	''f'',
-	null,
-	''bt_patch__name''
-	);
+select acs_object_type__create_type (
+    'bt_patch',
+    'Patch',
+    'Patches',
+    'acs_object',
+    'bt_patches',
+    'patch_id',
+    null,
+    'f',
+    null,
+    'bt_patch__name'
+);
 
-    return 0;
-end;' language 'plpgsql';
 
-select inline_0 ();
-
-drop function inline_0 ();
-
-create function bt_patch__new(
+create or replace function bt_patch__new(
     integer,     -- patch_id
     integer,     -- project_id
     integer,     -- component_id
@@ -662,8 +713,8 @@
 begin
 
     v_patch_id := acs_object__new(
-        p_patch_id,               -- object_id
-        ''bt_patch'',             -- object_type
+        p_patch_id,             -- object_id
+        ''bt_patch'',           -- object_type
         now(),                  -- creation_date
         p_creation_user,        -- creation_user
         p_creation_ip,          -- creation_ip
@@ -697,15 +748,15 @@
     into   v_action_id;
 
     insert into bt_patch_actions
-        (action_id, patch_id, action, actor, comment, comment_format)
+        (action_id, patch_id, action, actor, comment_text, comment_format)
      values
         (v_action_id, v_patch_id, ''open'', p_creation_user, p_description, p_description_format);
 
-    return 0;
+    return v_patch_id;
 end;
 ' language 'plpgsql';
 
-create function bt_patch__name(
+create or replace function bt_patch__name(
    integer                      -- patch_id
 ) returns varchar
 as '
@@ -722,7 +773,7 @@
 end;
 ' language 'plpgsql';
 
-create function bt_patch__delete(
+create or replace function bt_patch__delete(
    integer                      -- patch_id
 ) returns integer
 as '
@@ -737,16 +788,17 @@
 
 -- There is a many to many relationship between patches and bugs
 create table bt_patch_bug_map (
-       patch_id            integer not null
-                           constraint bt_patch_bug_map_pid_fk
-                           references bt_patches(patch_id)
-                           on delete cascade,
-       bug_id              integer not null
-                           constraint bt_patch_bug_map_bid_fk
-                           references bt_bugs(bug_id)
-                           on delete cascade,
-       constraint bt_patch_bug_map_un
-       unique (patch_id, bug_id)
+  patch_id            integer not null
+                      constraint bt_patch_bug_map_pid_fk
+                      references bt_patches(patch_id)
+                      on delete cascade,
+  bug_id              integer not null
+                      constraint bt_patch_bug_map_bid_fk
+                      references cr_items(item_id)
+                      on delete cascade,
+  constraint bt_patch_bug_map_un
+  unique (patch_id, bug_id)
 );
 
-\i bug-tracker-notifications-init.sql
+create index bt_patch_bug_map_patch_id_idx on bt_patch_bug_map(patch_id);
+create index bt_patch_bug_map_bug_id_idx on bt_patch_bug_map(bug_id);
