68 lines
1.6 KiB
SQL
68 lines
1.6 KiB
SQL
ALTER TABLE `Memberships`
|
|
ADD COLUMN `MembershipNumberLocal` INT(11) NOT NULL DEFAULT 0 AFTER `MemberSince`,
|
|
ADD COLUMN `MembershipNumberFamily` INT(11) NOT NULL DEFAULT 0 AFTER `MembershipNumberLocal`,
|
|
ADD INDEX `Index_NumLocal` (`MembershipNumberLocal` ASC),
|
|
ADD INDEX `Index_NumFamily` (`MembershipNumberFamily` ASC)
|
|
|
|
#
|
|
|
|
ALTER TABLE `Organizations`
|
|
ADD COLUMN `GrandestparentOrganizationId` INT(11) NOT NULL DEFAULT 0 AFTER `ParentOrganizationId`,
|
|
ADD INDEX `Index_Parent` (`ParentOrganizationId` ASC),
|
|
ADD INDEX `Index_Family` (`GrandestparentOrganizationId` ASC)
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `CurrencyExchangeRateSnapshots` (
|
|
`CurrencyExchangeRateSnapshotId` INT NOT NULL AUTO_INCREMENT,
|
|
`DateTime` DATETIME NOT NULL,
|
|
PRIMARY KEY (`CurrencyExchangeRateSnapshotId`),
|
|
INDEX `Ix_Date` (`DateTime` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `CurrencyExchangeRateSnapshotData` (
|
|
`CurrencyExchangeRateSnapshotId` INT NOT NULL AUTO_INCREMENT,
|
|
`CurrencyAId` INT(11) NOT NULL,
|
|
`CurrencyBId` INT(11) NOT NULL,
|
|
`APerB` DOUBLE NOT NULL,
|
|
PRIMARY KEY (`CurrencyExchangeRateSnapshotId`));
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `CreateExchangeRateSnapshot` (
|
|
IN dateTimeUtc DATETIME
|
|
)
|
|
BEGIN
|
|
|
|
INSERT INTO CurrencyExchangeRateSnapshots (DateTime)
|
|
VALUES (dateTimeUtc);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `InsertExchangeRateSnapshotData` (
|
|
IN currencyExchangeRateSnapshotId INTEGER,
|
|
IN currencyAId INTEGER,
|
|
IN currencyBId INTEGER,
|
|
IN aPerB DOUBLE
|
|
)
|
|
BEGIN
|
|
|
|
INSERT INTO CurrencyExchangeRateSnapshotData (CurrencyExchangeRateSnapshotId,CurrencyAId,CurrencyBId,APerB)
|
|
VALUES (currencyExchangeRateSnapshotId,currencyAId,currencyBId,aPerB);
|
|
|
|
END
|
|
|