# DataPointAggregateScopeQueries

Query: Objects
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join Games on Games.GameId = GameObjects.GameId


Query: ObjectsByType
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join Games on Games.GameId = GameObjects.GameId where GameObjects.Type = @ExtraData


Query: LocalPlayerObjects
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join GamePlayers on GameObjects.PlayerObjectId = GamePlayers.PlayerObjectId inner join Games on Games.GameId = GameObjects.GameId where GamePlayers.IsLocal = 1


Query: LocalPlayerObjectsByType
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join GamePlayers on GameObjects.PlayerObjectId = GamePlayers.PlayerObjectId inner join Games on Games.GameId = GameObjects.GameId where GamePlayers.IsLocal = 1 and GameObjects.Type = @ExtraData


Query: MajorPlayerObjects
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join GamePlayers on GameObjects.PlayerObjectId = GamePlayers.PlayerObjectId inner join Games on Games.GameId = GameObjects.GameId where GamePlayers.IsMajor = 1


Query: MajorPlayerObjectsByType
SQL:
select Ruleset, GameObjects.GameId, ObjectId from GameObjects inner join GamePlayers on GameObjects.PlayerObjectId = GamePlayers.PlayerObjectId inner join Games on Games.GameId = GameObjects.GameId where GamePlayers.IsMajor = 1 and GameObjects.Type = @ExtraData


Query: Players
SQL:
select Ruleset, GameObjects.GameId, GamePlayers.PlayerObjectId as ObjectId from GamePlayers inner join GameObjects on GamePlayers.PlayerObjectId = GameObjects.ObjectId inner join Games on GameObjects.GameId = Games.GameId


Query: MajorPlayers
SQL:
select Ruleset, GameObjects.GameId, GamePlayers.PlayerObjectId as ObjectId from GamePlayers inner join GameObjects on GamePlayers.PlayerObjectId = GameObjects.ObjectId inner join Games on GameObjects.GameId = Games.GameId where GamePlayers.IsMajor = 1


Query: LocalPlayers
SQL:
select Ruleset, GameObjects.GameId, GamePlayers.PlayerObjectId as ObjectId from GamePlayers inner join GameObjects on GamePlayers.PlayerObjectId = GameObjects.ObjectId inner join Games on GameObjects.GameId = Games.GameId where GamePlayers.IsLocal = 1


# DataPointAggregateQueries

Query: ObjectDataSet_Values
SQL:
WITH RECURSIVE range(X,MaxX) AS (SELECT MIN(X),MAX(X) FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint and ObjectId = @ObjectId) UNION ALL SELECT X+1,MaxX FROM range WHERE X < MaxX) SELECT (SELECT Y FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint and ObjectId = @ObjectId) AND X <= range.X ORDER BY X DESC LIMIT 1) AS ValueNumeric FROM range WHERE ValueNumeric IS NOT NULL


Query: ObjectDataSet_LastValue
SQL:
select Y as ValueNumeric from DataSetValues where DataSetId = (select DataSetId from DataSets where DataSet = @DataPoint and ObjectId = @ObjectId) AND Y IS NOT NULL order by X DESC LIMIT 1


Query: PlayerDataSet_Values
SQL:
WITH RECURSIVE range(X,MaxX) AS (SELECT MIN(X),MAX(X) FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets INNER JOIN GameObjects ON GameObjects.ObjectId = DataSets.ObjectId WHERE DataSet = @DataPoint and (GameObjects.ObjectId = @ObjectId or PlayerObjectId = @ObjectId)) UNION ALL SELECT X+1,MaxX FROM range WHERE X < MaxX) SELECT (SELECT Y FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets INNER JOIN GameObjects ON GameObjects.ObjectId = DataSets.ObjectId WHERE DataSet = @DataPoint and (GameObjects.ObjectId = @ObjectId or PlayerObjectId = @ObjectId)) AND X <= range.X ORDER BY X DESC LIMIT 1) AS ValueNumeric FROM range WHERE ValueNumeric IS NOT NULL


Query: PlayerDataSet_LastValue
SQL:
select Y as ValueNumeric from DataSetValues where DataSetId = (select DataSetId from DataSets inner join GameObjects on GameObjects.ObjectId = DataSets.ObjectId where DataSet = @DataPoint and (GameObjects.ObjectId = @ObjectId or PlayerObjectId = @ObjectId)) AND Y IS NOT NULL order by X DESC LIMIT 1


Query: GameDataSet_Values
SQL:
WITH RECURSIVE range(X,MaxX) AS (SELECT MIN(X),MAX(X) FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint AND GameId = @GameId) UNION ALL SELECT X+1,MaxX FROM range WHERE X < MaxX) SELECT (SELECT Y FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint AND GameId = @GameId) AND X <= range.X ORDER BY X DESC LIMIT 1) AS ValueNumeric FROM range WHERE ValueNumeric IS NOT NULL


Query: GameDataSet_LastValue
SQL:
select Y as ValueNumeric from DataSetValues where DataSetId = (select DataSetId from DataSets where DataSet = @DataPoint and GameId = @GameId) AND Y IS NOT NULL order by X DESC LIMIT 1


Query: RulesetDataSet_Values
SQL:
WITH RECURSIVE range(X,MaxX) AS (SELECT MIN(X),MAX(X) FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint AND Ruleset = @Ruleset) UNION ALL SELECT X+1,MaxX FROM range WHERE X < MaxX) SELECT (SELECT Y FROM DataSetValues WHERE DataSetId = (SELECT DataSetId FROM DataSets WHERE DataSet = @DataPoint AND Ruleset = @Ruleset) AND X <= range.X ORDER BY X DESC LIMIT 1) AS ValueNumeric FROM range WHERE ValueNumeric IS NOT NULL


Query: RulesetDataSet_LastValue
SQL:
select Y as ValueNumeric from DataSetValues where DataSetId = (select DataSetId from DataSets where DataSet = @DataPoint and Ruleset = @Ruleset) AND Y IS NOT NULL order by X DESC LIMIT 1


Query: ObjectDataPoints
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset)


Query: ObjectDataPoints_ByType
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = @ExtraData


Query: ObjectDataPoints_Players
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = "Player"


Query: ObjectDataPoints_Players_NoCoalesce
SQL:
select ValueType, ValueNumeric, ValueString, ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = "Player"


Query: ObjectDataPoints_LocalPlayers
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) inner join GamePlayers on gobj.ObjectId = GamePlayers.PlayerObjectId where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = "Player" and GamePlayers.IsLocal = 1


Query: ObjectDataPoints_LocalPlayers_NoCoalesce
SQL:
select ValueType, ValueNumeric, ValueString, ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) inner join GamePlayers on gobj.ObjectId = GamePlayers.PlayerObjectId where DataPoint = @DataPoint and (@ObjectId is null or ObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = "Player" and GamePlayers.IsLocal = 1


Query: ObjectDataPoints_PlayerObjects_ByType
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) where DataPoint = @DataPoint and PlayerObjectId is not null and (@ObjectId is null or PlayerObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = @ExtraData


Query: ObjectDataPoints_LocalPlayerObjects_ByType
SQL:
select ValueType, ValueNumeric, ValueString, coalesce(ValueObjectId, odpv.ObjectId) as ValueObjectId from ObjectDataPointValues odpv inner join GameObjects gobj using (ObjectId) inner join Games using (GameId) inner join GamePlayers using (PlayerObjectId) where DataPoint = @DataPoint and PlayerObjectId is not null and (@ObjectId is null or PlayerObjectId = @ObjectId) and (@GameId is null or GameId = @GameId) and (@Ruleset is null or Ruleset = @Ruleset) and gobj.Type = @ExtraData and GamePlayers.IsLocal = 1


# GraphQueries

Query: GameDataSets
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.GameId=@GameId and DataSet=@DataSet


Query: GameDataSetsByType
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.Type=@ExtraData and GameObjects.GameId=@GameId and DataSet=@DataSet


Query: GameDeltaDataSetsByType
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId, 1 as IsDelta from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.Type=@ExtraData and GameObjects.GameId=@GameId and DataSet=@DataSet


Query: PlayerDataSets
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.PlayerObjectId=@ObjectId and DataSet=@DataSet


Query: PlayerDataSetsByType
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.Type=@ExtraData and GameObjects.PlayerObjectId=@ObjectId and DataSet=@DataSet


Query: PlayerDeltaDataSetsByType
SQL:
select DataSets.ObjectId, Datasets.Type, DataSetId, 1 as IsDelta from GameObjects inner join DataSets on GameObjects.ObjectId = DataSets.ObjectId where GameObjects.Type=@ExtraData and GameObjects.PlayerObjectId=@ObjectId and DataSet=@DataSet


# ReportQueries

Query: PlayerTypesByDataPoint
SQL:
select distinct ValueType as Type,ObjectId from ObjectDataPointValues where DataPoint=@ExtraData and ObjectId=@ObjectId


# Queries

QueryId: Defeats
SQL:
SELECT DefeatType AS ParameterId, D.Name AS Name, D.Description AS Description, Visible, ReadOnly, 1 AS SupportsSinglePlayer, 1 AS SupportsLANMultiplayer, 1 AS SupportsInternetMultiplayer, 1 AS SupportsHotSeat, 1 AS SupportsPlayByCloud, 0 AS ChangeableAfterGameStart, 'bool' AS Domain, 0 AS Hash, 1 AS DefaultValue, 'Game' AS ConfigurationGroup, DefeatType AS ConfigurationId, 'Victories' AS GroupId, 10 AS SortIndex FROM Defeats D, Rulesets WHERE RulesetType = ?1 and D.Domain = Rulesets.DefeatDomain


QueryId: Victories
SQL:
SELECT VictoryType AS ParameterId, V.Name AS Name, V.Description AS Description, Visible, ReadOnly, 1 AS SupportsSinglePlayer, 1 AS SupportsLANMultiplayer, 1 AS SupportsInternetMultiplayer, 1 AS SupportsHotSeat, 1 AS SupportsPlayByCloud, 0 AS ChangeableAfterGameStart, 'bool' AS Domain, 0 AS Hash, 1 AS DefaultValue, 'Game' AS ConfigurationGroup, VictoryType AS ConfigurationId, 'Victories' AS GroupId, 20 AS SortIndex FROM Victories V, Rulesets WHERE RulesetType =?1 and V.Domain = Rulesets.VictoryDomain


QueryId: RulesetFixedMaxTurnsCriteria
SQL:
SELECT 'MaxTurns' AS ParameterId, 'Game' AS ConfigurationGroup, 'RULESET' AS ConfigurationId, 'NotEquals' AS Operator, RulesetType AS ConfigurationValue FROM Rulesets WHERE FixedMaxTurns = 1


QueryId: RulesetNoTeamCriteria
SQL:
SELECT 'NoTeams' AS ParameterId, 'Game' AS ConfigurationGroup, 'RULESET' AS ConfigurationId, 'NotEquals' AS Operator, RulesetType AS ConfigurationValue FROM Rulesets WHERE RequiresNoTeams = 1


QueryId: RulesetUniqueLeaderCriteria
SQL:
SELECT 'NoDupeLeaders' AS ParameterId, 'Game' AS ConfigurationGroup, 'RULESET' AS ConfigurationId, 'NotEquals' AS Operator, RulesetType AS ConfigurationValue FROM Rulesets WHERE RequiresUniqueLeaders = 1


QueryId: RulesetUniqueCivilizationsCriteria
SQL:
SELECT 'NoDupeCivilizations' AS ParameterId, 'Game' AS ConfigurationGroup, 'RULESET' AS ConfigurationId, 'NotEquals' AS Operator, RulesetType AS ConfigurationValue FROM Rulesets WHERE RequiresUniqueCivilizations = 1


QueryId: MapUniqueLeaderCriteria
SQL:
SELECT 'NoDupeLeaders' AS ParameterId, 'Map' AS ConfigurationGroup, 'MAP_SCRIPT' AS ConfigurationId, 'NotEquals' AS Operator, File AS ConfigurationValue FROM Maps WHERE RequiresUniqueLeaders = 1


QueryId: MapWorldWrapDependencies
SQL:
SELECT 'WorldWrap' AS ParameterId, 'Map' AS ConfigurationGroup, 'MAP_SCRIPT' AS ConfigurationId, 'NotEquals' AS Operator, File AS ConfigurationValue FROM Maps WHERE DisableWorldWrap = 1 OR StaticMap = 1


QueryId: MapResourcesDependencies
SQL:
SELECT 'Resources' AS ParameterId, 'Map' AS ConfigurationGroup, 'MAP_SCRIPT' AS ConfigurationId, 'NotEquals' AS Operator, File AS ConfigurationValue FROM Maps WHERE StaticMap = 1


QueryId: MapNaturalWondersDependencies
SQL:
SELECT 'NaturalWonders' AS ParameterId, 'Map' AS ConfigurationGroup, 'MAP_SCRIPT' AS ConfigurationId, 'NotEquals' AS Operator, File AS ConfigurationValue FROM Maps WHERE StaticMap = 1


QueryId: Rulesets
SQL:
SELECT 'Rulesets' AS Domain, Name, Description, RulesetType AS Value, SortIndex FROM Rulesets WHERE ((?1 = -379035929 and SupportsSinglePlayer = 1) or ((?1 = 1161765858 or ?1 = 20463770 or ?1 = -413661464) and SupportsMultiplayer = 1) or (?1 = 208129763 and SupportsHotSeat = 1) or (?1 = 285185446 and SupportsPlayByCloud = 1))


QueryId: Maps
SQL:
SELECT Domain, Name, Description, File AS Value, SortIndex FROM Maps WHERE (not WorldBuilderOnly or ?)


QueryId: Players
SQL:
SELECT Domain, LeaderName AS Name, LeaderAbilityDescription AS Description, LeaderType AS Value, coalesce(SortIndex,100) AS SortIndex FROM Players


QueryId: MapLeaders
SQL:
SELECT 'Players:*' AS Domain, LeaderType as Value, 'intersect' as Filter FROM MapLeaders WHERE Map = ?1


QueryId: MapDomainOverrides
SQL:
SELECT ParameterId, Domain FROM MapDomainOverrides WHERE Map = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: MapSupportedValues
SQL:
SELECT Domain, Value, 'intersect' AS Filter FROM MapSupportedValues WHERE Map = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: MapUnSupportedValues
SQL:
SELECT Domain, Value, 'difference' AS Filter FROM MapUnSupportedValues WHERE Map = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: RulesetDomainOverrides
SQL:
SELECT ParameterId, Domain FROM RulesetDomainOverrides WHERE Ruleset = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: RulesetSupportedValues
SQL:
SELECT Domain, Value, 'intersect' AS Filter FROM RulesetSupportedValues WHERE Ruleset = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: RulesetUnSupportedValues
SQL:
SELECT Domain, Value, 'difference' AS Filter FROM RulesetUnSupportedValues WHERE Ruleset = ?1 and (PlayerId = ?2 or PlayerId is null)


QueryId: RulesetFixedMaxTurns
SQL:
SELECT 'StandardTurnLimits' as Domain, 'TURNLIMIT_CUSTOM' as Value, 'intersect' AS Filter FROM Rulesets WHERE RuleSetType = ?1 AND FixedMaxTurns = 1


QueryId: RulesetTurnLimitNoneUpdates
SQL:
SELECT 'Game' AS SourceGroup, 'RULESET' AS SourceId, RuleSetType AS SourceValue, 'Game' AS TargetGroup, 'GAME_TURN_LIMIT' AS TargetId, 'TURNLIMIT_NONE' AS TargetValue, 1 AS 'Hash' FROM Rulesets WHERE MaxTurns = 0


QueryId: RulesetTurnLimitCustomUpdates
SQL:
SELECT 'Game' AS SourceGroup, 'RULESET' AS SourceId, RuleSetType AS SourceValue, 'Game' AS TargetGroup, 'GAME_TURN_LIMIT' AS TargetId, 'TURNLIMIT_CUSTOM' AS TargetValue, 1 AS 'Hash' FROM Rulesets WHERE MaxTurns > 0


QueryId: RulesetMaxTurnUpdates
SQL:
SELECT 'Game' AS SourceGroup, 'RULESET' AS SourceId, RulesetType AS SourceValue, 'Game' AS TargetGroup, 'GAME_MAX_TURNS' AS TargetId, MaxTurns AS TargetValue FROM Rulesets WHERE MaxTurns IS NOT NULL


QueryId: RulesetNoTeamsUpdates
SQL:
SELECT 'Game' AS SourceGroup, 'RULESET' AS SourceId, RulesetType AS SourceValue, 'Game' AS TargetGroup, 'NO_TEAMS' AS TargetId, 1 AS TargetValue FROM Rulesets WHERE RequiresNoTeams = 1


QueryId: RulesetUniqueLeaderUpdates
SQL:
SELECT 'Game' AS SourceGroup, 'RULESET' AS SourceId, RulesetType AS SourceValue, 'Game' AS TargetGroup, 'NO_DUPLICATE_LEADERS' AS TargetId, 1 AS TargetValue FROM Rulesets WHERE RequiresUniqueLeaders = 1


QueryId: MapUniqueLeaderUpdates
SQL:
SELECT 'Map' AS SourceGroup, 'MAP_SCRIPT' AS SourceId, File AS SourceValue, 'Game' AS TargetGroup, 'NO_DUPLICATE_LEADERS' AS TargetId, 1 AS TargetValue FROM Maps WHERE RequiresUniqueLeaders = 1


QueryId: VictoryUpdates
SQL:
SELECT 'Game' As SourceGroup, 'RULESET' AS SourceId, RulesetType AS SourceValue, 'Game' AS TargetGroup, VictoryType AS TargetId, 1 AS TargetValue FROM Victories V, Rulesets WHERE V.EnabledByDefault = 1 AND V.Domain = Rulesets.VictoryDomain


