ユーザーが定義可能なデータベースアプリケーションの構築（パート2）

create table chair (
    chair_id bigint not null identity (1,1),
    created_on smalldatetime,
    created_by bigint,
    modified_on smalldatetime,
    modified_by bigint)

alter table chair add 
    constraint pk_chair primary key  clustered 
    (
        chair_id
    ) 

create table chair_attribute (
    chair_attribute_id bigint not null identity (1,1),
    chair_id bigint not null,
    system_attribute_id bigint not null,
    chair_attribute_value,
    created_on smalldatetime,
    created_by bigint,
    modified_on smalldatetime,
    modified_by bigint)

alter table chair_attribute add
    constraint pk_chair_attribute primary key clustered
    (
        chair_attribute_id
    )

create procedure chair_get 
    @chair_id bigint
as
    select * from chair where chair_id = @chair_id

create procedure chair_insert
    @created_by bigint, 
    @chair_id bigint output
as
    insert into chair (created_by, created_on) 
        values (@created_by, getdate())

    if @@ERROR <> 0
        begin
            -- not good, we need to reset the
            -- chair id and return the error
            @chair_id = 0
            return @@ERROR
        end
    else
        begin
            @chair_id = @@IDENTITY
            return 0
        end

create procedure chair_update
    @chair_id bigint,
    @modified_by bigint
as
    -- update the chair table, we only need
    -- to touch the modified columns since
    -- that’s the only real data here
    update chair 
        set 
            modified_by = @modified_by, 
            modified_on = getdate()
            where chair_id = @chair_id
        if @@ERROR = 0
            return 0
        else
            return @@ERROR

create procedure chair_delete
    @chair_id bigint
as
    -- delete the attributes before we move ahead
    delete chair_attribute where chair_id = @chair_id

    if @@ERROR <> 0
        return @@ERROR

    -- OK, we can proceed
    delete chair where chair_id = @chair_id

    return @@ERROR

create procedure chair_attribute_get
    @chair_id bigint
    -- said is short for system_attribute_id
    @chair_said bigint = 0
as
    -- get all the attributes for this chair
    if @chair_said = 0
        select 
            chair_id,
            chair.modified_on chair_modified_on,
            chair.modified_by chair_modified_by,
            chair.created_on chair_created_on,
            chair.created_by chair_created_by,
            chair_attribute_value,
            system_attribute_name,
            system_attribute_description,
            system_attribute_display_text,
            system_attribute_display_order,
            ca.modified_on chair_attribute_modified_on,
            ca.modified_by chair_attribute_modified_by,
            ca.created_on chair_attribute_created_on, 
            ca.created_by chair_attribute_created_by,
            ca.chair_attribute_id
        from chair
            left join chair_attribute ca on
                ca.chair_id = chair.chair_id
            left join system_attribute sa on
                sa.system_attribute_id =
                    ca.system_attribute_id
        where chair.chair_id = @chair_id
    else -- we want a particular said only
        select 
            chair_id,
            chair.modified_on chair_modified_on,
            chair.modified_by chair_modified_by,
            chair.created_on chair_created_on,
            chair.created_by chair_created_by,
            chair_attribute_value,
            system_attribute_name,
            system_attribute_description,
            system_attribute_display_text,
            system_attribute_display_order,
            ca.modified_on chair_attribute_modified_on,
            ca.modified_by chair_attribute_modified_by,
            ca.created_on chair_attribute_created_on, 
            ca.created_by chair_attribute_created_by,
            ca.chair_attribute_id
        from chair
            left join chair_attribute ca on
                ca.chair_id = chair.chair_id
            left join system_attribute sa on
                sa.system_attribute_id =
                    ca.system_attribute_id
        where
            chair.chair_id = @chair_id and
            ca.system_option_id = @chair_said

create procedure chair_attribute_insert
    @chair_id bigint,
    @said bigint,
    @chair_attribute_value varchar(8000),
    @created_by bigint,
    @chair_attribute_id bigint output,
    @error_text varchar(500) output
as

/* first we need to test that the 
passed value is legal  */

-- is there a verify for this said?
declare @sp_name varchar(255), 
    @sp_text varchar(500),
    @verify_status

select @sp_name = system_attribute_verify_sql
    from system_attribute
    where system_attribute_id = @said

/* 
if sp_name is filled, then we have something to test
all of the verify stored procedures take one value
and return a 0 or a 1.  0 means the value is OK,
1 means the value failed.
The procedures take 2 parameters, the value in and
an output parameter with the error text.
*/
if @sp_name <> ’’  begin
    exec @verify_status = @sp_name
      @chair_attribute_value, @sp_text output
    if @verify_status = 1  begin
        -- we have a failure in the verify process
        set @error_text = @sp_text
        return 1
end

-- we were either successful in the verify
-- or didn’t need to
insert into chair_attribute
 (chair_id, chair_attribute_value, system_attribute_id,
  created_by, created_on)
 values
 (@chair_id, @chair_attribute_value, @said,
  @created_by, getdate())
    if @@ERROR = 0
        set @chair_attribute_id = @@IDENTITY
        -- mark the chair itself as modified
        exec chair_update @chair_id, @created_by
end -- end verify_status = 0

create procedure chair_attribute_update
    @chair_attribute_id bigint,
    @chair_id bigint,
    @said bigint,
    @modified_by bigint,
    @error_text
as
    /* We need to verify the value first */
    declare @sp_name varchar(255), 
        @sp_text varchar(500),
        @verify_status

select @sp_name = system_attribute_verify_sql
    from system_attribute
    where system_attribute_id = @said

/* 
  if sp_name is filled, then we have something to
  test all of the verify stored procedures take
  one value and return a 0 or a 1.  0 means the
  value is OK, 1 means the value failed.
  The procedures take 2 parameters, the value in
   and an output parameter with the error text.
*/
if @sp_name <> ’’  begin
    exec @verify_status = @sp_name
      @chair_attribute_value, @sp_text output
    if @verify_status = 1  begin
        -- we have a failure in the verify process
        set @error_text = @sp_text
        return 1
end

    /* if we made it this far, we’re OK */
    update chair_attribute
    set
        chair_attribute_value = @chair_attribute_value,
        chair_id = @chair_id,
        system_attribute_id = @said,
        modified_by = @modified_by,
        modified_on = @modified_on
    where
        chair_attribute_id = @chair_attribute_id

    /* we need to touch the parent */
    exec chair_update @chair_id, @modified_by

    -- we can simply return the @@ERROR here
    return @@ERROR

create procedure chair_attribute_delete
    @chair_attribute_id bigint,
    @modified_by bigint
as

    /* we need to get the parent item before we continue */
    declare @chair_id bigint
    select @chair_id = chair_id from chair_attribute
      where chair_attribute_id = @chair_attribute_id

    delete chair_attribute
      where chair_attribute_id = @chair_attribute_id
    if @@ERROR = 0
        exec chair_update @chair_id, @modified_by 
    return @@ERROR