60 lines
1.2 KiB
SQL
60 lines
1.2 KiB
SQL
CREATE TABLE `VolunteerPositions` (
|
|
`VolunteerPositionId` INT NOT NULL AUTO_INCREMENT,
|
|
`VolunteerId` INT NOT NULL,
|
|
`PositionId` INT NOT NULL,
|
|
`GeographyId` INT NOT NULL,
|
|
`Open` TINYINT NOT NULL DEFAULT '1',
|
|
`Assigned` TINYINT NOT NULL DEFAULT '0',
|
|
PRIMARY KEY (`VolunteerPositionId`),
|
|
INDEX `Index_Position` (`PositionId` ASC),
|
|
INDEX `Index_Geography` (`GeographyId` ASC),
|
|
INDEX `Index_Open` (`Open` ASC),
|
|
INDEX `Index_Volunteer` (`VolunteerId` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `CreateVolunteerPosition`
|
|
|
|
|
|
#
|
|
|
|
CREATE PROCEDURE `CreateVolunteerPosition`(
|
|
IN volunteerId INT,
|
|
IN positionId INT,
|
|
IN geographyId INT
|
|
)
|
|
BEGIN
|
|
|
|
INSERT INTO VolunteerPositions (VolunteerId,PositionId,GeographyId,Open,Assigned)
|
|
VALUES (volunteerId,positionId,geographyId,1,0);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `CloseVolunteerPosition`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `CloseVolunteerPosition`(
|
|
volunteerPositionId INTEGER,
|
|
assigned TINYINT
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE VolunteerPositions
|
|
SET VolunteerPositions.Open=0,VolunteerRoles.Assigned=assigned
|
|
WHERE VolunteerPositions.VolunteerPositionId=volunteerPositionId AND VolunteerPositions.Open=1;
|
|
|
|
SELECT ROW_COUNT() AS RowsUpdated;
|
|
|
|
|
|
END |