Code: Select all
BEGIN TRANSACTION;
CREATE TABLE EventResults (
eid INTEGER PRIMARY KEY,
gid INTEGER,
Event INTEGER,
DateTime DATE,
ComputerName VARCHAR(15),
Subject INTEGER,
SubjectName VARCHAR(32),
Context VARCHAR(24),
ObjectRef VARCHAR(4000)
);
CREATE TABLE GameResults (
gid INTEGER PRIMARY KEY,
szGameID VARCHAR(18),
szName VARCHAR(65),
szWinningTeam VARCHAR(21),
szCore VARCHAR(21),
nWinningTeamID INTEGER,
bIsGoalConquest BOOLEAN,
bIsGoalCountdown BOOLEAN,
bIsGoalTeamKills BOOLEAN,
bIsGoalProsperity BOOLEAN,
bIsGoalArtifacts BOOLEAN,
bIsGoalFlags BOOLEAN,
nGoalConquest INTEGER,
nGoalCountdown INTEGER,
nGoalTeamKills INTEGER,
fGoalProsperity FLOAT,
nGoalArtifacts INTEGER,
nGoalFlags INTEGER,
nDuration INTEGER,
timeEnter DATE
);
CREATE TABLE PlayerResults (
pid INTEGER PRIMARY KEY,
gid INTEGER,
szGameID VARCHAR(18),
nTeamID INTEGER,
szName VARCHAR(32),
cPlayerKills INTEGER,
cBuilderKills INTEGER,
cLayerKills INTEGER,
cMinerKills INTEGER,
cBaseKills INTEGER,
cBaseCaptures INTEGER,
cPilotBaseKills INTEGER,
cPilotBaseCaptures INTEGER,
cDeaths INTEGER,
cEjections INTEGER,
cRescues INTEGER,
cFlags INTEGER,
cArtifacts INTEGER,
cTechsRecovered INTEGER,
cAlephsSpotted INTEGER,
cAsteroidsSpotted INTEGER,
fCombatRating FLOAT,
fScore FLOAT,
nTimePlayed INTEGER,
nTimeCmd INTEGER,
bWin BOOLEAN,
bLose BOOLEAN,
bWinCmd BOOLEAN,
bLoseCmd BOOLEAN,
CharacterID INTEGER
);
CREATE TABLE TeamResults (
tid INTEGER PRIMARY KEY,
gid INTEGER,
szGameID VARCHAR(18),
nTeamID INTEGER,
szName VARCHAR(25),
szTechs VARCHAR(101),
nCivID INTEGER,
cPlayerKills INTEGER,
cBaseKills INTEGER,
cBaseCaptures INTEGER,
cDeaths INTEGER,
cEjections INTEGER,
cFlags INTEGER,
cArtifacts INTEGER,
nConquestPercent INTEGER,
nProsperityPercentBought INTEGER,
nProsperityPercentComplete INTEGER,
nTimeEndured INTEGER
);
CREATE TRIGGER insert_GameResults_timeEnter AFTER INSERT ON GameResults BEGIN UPDATE GameResults SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid; END;
COMMIT;Code: Select all
sub CreateBalanceTables() {
$dbh->do(qq{
CREATE TABLE balance (
bid INTEGER PRIMARY KEY,
name VARCHAR(12),
mu NUMERIC(2,2),
sigma NUMERIC(2,2),
rank INTEGER,
timeEnter DATE
);});
$dbh->do(qq{
CREATE TABLE balancelog (
blid INTEGER PRIMARY KEY,
bid INTEGER,
bidOLD INTEGER,
nameNEW VARCHAR(12),
muNEW NUMERIC(2,2),
sigmaNEW NUMERIC(2,2),
rankNEW INTEGER,
nameOLD VARCHAR(12),
muOLD NUMERIC(2,2),
sigmaOLD NUMERIC(2,2),
rankOLD INTEGER ,
sqlAction VARCHAR(15),
timeEnter DATE,
timeUpdate DATE,
timestamp DATE
);});
$dbh->do(qq{CREATE TRIGGER update_balancelog AFTER UPDATE ON balance BEGIN INSERT INTO balancelog (bid,bidOLD,nameOLD,nameNEW,muOLD,muNEW,sigmaOLD,sigmaNEW,rankOLD,rankNEW,sql
Action,timeEnter,timeUpdate,timestamp) values (new.bid,old.bid,old.name,new.name,old.mu,new.mu,old.sigma, new.sigma,old.rank,new.rank, 'UPDATE',old.timeEnter,DATETIME('NOW'),DATETIME('NOW') ); END;});
$dbh->do(qq{CREATE TRIGGER insert_balancelog AFTER INSERT ON balance BEGIN INSERT INTO balancelog (bid,nameNEW,muNEW,sigmaNEW,rankNEW, sqlAction,timeEnter,timestamp) values (new.bid,new.name,new.mu,new.sigma,new.rank,'INSERT',new.timeEnter,DATETIME('NOW') ); END;});
$dbh->do(qq{CREATE TRIGGER delete_balancelog DELETE ON balance BEGIN INSERT INTO balancelog (bid,nameOLD,muNEW,sigmaOLD,rankOLD,sqlAction,timeEnter) values (old.bid,old.name,old.mu,old.sigma,old.rank, 'DELETE',DATETIME('NOW') ); END;});
$dbh->do(qq{CREATE TRIGGER insert_balnce_timeEnter AFTER INSERT ON balance BEGIN UPDATE balance SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid; END;});
return;
}
