DB.Query (Civ5 API)

From Civilization Modding Wiki
Jump to: navigation, search

This page is a part of the Lua and UI Reference (Civ5).


Function.png This function is a member of DB.

This is a static method, invoke it with a dot.


Executes an arbitrary SQL statement on the gameplay database and returns recordset. In situations where the quick and easy enumeration that GameInfo provides is not enough and either updates to the database or a complex query such as a join is required, DB.Query can be used. This method executes any arbitrary SQL command on the gameplay database and returns a recordset. Variable arguments can be used in the SQL command. For information about how to use literal arguments, see here. The SQL command does not actually get executed until the query is iterated. Even if your statement is not intended to return a record set, you must still iterate it to cause the "Step" to occur.


Usage

string DB.Query(string SqlStatement, ... statement_args = nil)


Returned Value

A valid database query on success, nil on error.

Parameters

SqlStatement: A SQL string.
statement_args: One or many arguments that get injected into the SQL statement.

Can be of type nil,number, or string.


Examples

-- Obtain all custom map options which are dropdowns for the current map script and their possible values.
-- NOTE: Does not include checkbox-based options.
local sqlGetCustomMapOptions =
{{Type5|select * from MapScriptOptions
where
exists
(select 1 from MapScriptOptionPossibleValues
where
FileName = MapScriptOptions.FileName and
OptionID = MapScriptOptions.OptionID) and
Hidden = 0 and
FileName = ?}};
local currentMapScript = PreGame.GetMapScript();
for option in DB.Query(sqlGetCustomMapOptions, currentMapScript) do
options[option.OptionID] = {
ID = option.OptionID,
Name = Locale.ConvertTextKey(option.Name),
ToolTip = (option.Description) and Locale.ConvertTextKey(option.Description) or nil,
Disabled = (option.ReadOnly == 1) and true or false,
DefaultValue = option.DefaultValue,
SortPriority = option.SortPriority,
Values = {},
};
end


Source code samples

Redundant occurences have been removed.

AdvancedSetup.lua

UI/FrontEnd/GameSetup/AdvancedSetup.lua
0049
for option in DB.Query("select * from MapScriptOptions where exists (select 1 from MapScriptOptionPossibleValues where FileName = MapScriptOptions.FileName and OptionID = MapScriptOptions.OptionID) and Hidden = 0 and FileName = ?", currentMapScript) do
0061
for possibleValue in DB.Query("select * from MapScriptOptionPossibleValues where FileName = ? order by SortIndex ASC", currentMapScript) do
0184
for option in DB.Query("select * from MapScriptOptions where not exists (select 1 from MapScriptOptionPossibleValues where FileName = MapScriptOptions.FileName and OptionID = MapScriptOptions.OptionID) and Hidden = 0 and FileName = ?", PreGame.GetMapScript()) do
0401
for row in DB.Query(sql) do


CivilopediaScreen.lua

UI/Civilopedia/CivilopediaScreen.lua
1370
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Technologies ORDER By Random() LIMIT 1") do
1403
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Units ORDER By Random() LIMIT 1") do
1435
for row in DB.Query("SELECT PortraitIndex, IconAtlas from UnitPromotions ORDER By Random() LIMIT 1") do
1467
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Buildings where WonderSplashImage IS NULL ORDER By Random() LIMIT 1") do
1499
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Buildings Where WonderSplashImage IS NOT NULL ORDER By Random() LIMIT 1") do
1531
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Policies Where IconAtlas IS NOT NULL ORDER By Random() LIMIT 1") do
1587
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Leaders where Type <> \"LEADER_BARBARIAN\" ORDER By Random() LIMIT 1") do
1645
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Terrains ORDER By Random() LIMIT 1") do
1683
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Resources ORDER By Random() LIMIT 1") do
1716
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Improvements ORDER By Random() LIMIT 1") do


CivilopediaScreen.lua (G&K)

DLC/Expansion/UI/Civilopedia/CivilopediaScreen.lua
0460
for unit in DB.Query("SELECT Units.ID, Units.Description, Units.PortraitIndex, Units.IconAtlas From Units where FaithCost > 0 and not RequiresFaithPurchaseEnabled and ShowInPedia == 1") do
0489
for unit in DB.Query("SELECT Units.ID, Units.Description, Units.PortraitIndex, Units.IconAtlas From Units where PreReqTech is NULL and Special is NULL and (Units.FaithCost = 0 or RequiresFaithPurchaseEnabled) and Units.ShowInPedia = 1") do
0714
for building in DB.Query("SELECT Buildings.ID, Buildings.Description, Buildings.PortraitIndex, Buildings.IconAtlas from Buildings inner join  BuildingClasses on Buildings.BuildingClass = BuildingClasses.Type where FaithCost > 0 and BuildingClasses.MaxGlobalInstances < 0 and BuildingClasses.MaxPlayerInstances <> 1 and BuildingClasses.MaxTeamInstances < 0;") do
0752
for building in DB.Query(sql) do


CivilopediaScreen.lua (G&K)

DLC/Expansion/Scenarios/SteampunkScenario/CivilopediaScreen.lua
1588
for row in DB.Query("SELECT PortraitIndex, IconAtlas from Policies ORDER By Random() LIMIT 1") do


GameCalendarUtilities.lua

Gameplay/Lua/GameCalendarUtilities.lua
0041
for row in DB.Query([[SELECT MonthIncrement, TurnsPerIncrement FROM GameSpeed_Turns WHERE GameSpeedType = ? ORDER BY rowid ASC]], gameSpeedType) do


MapmakerUtilities.lua

Gameplay/Lua/MapmakerUtilities.lua
0466
for row in DB.Query("select count(*) as count from Civilization_Start_Region_Priority where CivilizationType = ?", civType) do
0474
for row in DB.Query("select count(*) as count from Civilization_Start_Region_Avoid where CivilizationType = ?", civType) do


StagingRoom.lua

UI/FrontEnd/Multiplayer/StagingRoom.lua
1019
for row in DB.Query([[SELECT
1020
   Civilizations.ID as CivID,
1021
   Civilizations.Description as CivDescription,
1022
   Civilizations.ShortDescription as CivShortDescription,
1023
   Leaders.Description as LeaderDescription
1024
   FROM Civilizations, Leaders, Civilization_Leaders
1025
   WHERE
1026
   Civilizations.Playable = 1 AND
1027
   Civilizations.Type = Civilization_Leaders.CivilizationType AND
1028
   Leaders.Type = Civilization_Leaders.LeaderheadType
1029
   ]]) do


UniqueBonuses.lua

UI/FrontEnd/GameSetup/UniqueBonuses.lua
0112
for row in DB.Query([[SELECT ID, Description, PortraitIndex, IconAtlas from Units INNER JOIN
0113
   Civilization_UnitClassOverrides ON Units.Type = Civilization_UnitClassOverrides.UnitType
0114
   WHERE Civilization_UnitClassOverrides.CivilizationType = ? AND
0115
   Civilization_UnitClassOverrides.UnitType IS NOT NULL]], civ.Type) do
0120
for row in DB.Query([[SELECT ID, Description, PortraitIndex, IconAtlas from Buildings INNER JOIN
0121
   Civilization_BuildingClassOverrides ON Buildings.Type = Civilization_BuildingClassOverrides.BuildingType
0122
   WHERE Civilization_BuildingClassOverrides.CivilizationType = ? AND
0123
   Civilization_BuildingClassOverrides.BuildingType IS NOT NULL]], civ.Type) do
0128
for row in DB.Query([[SELECT ID, Description, PortraitIndex, IconAtlas from Improvements where CivilizationType = ?]], civ.Type) do



The initial version of this page was created by the Civ5 API Bot, see the Civ5 API Reference FAQ. Some of the texts come from the 2kgames' wiki and most of code samples are copyrighted to Firaxis.
Functions' signatures were either copied from the 2kgames' wiki, or infered from the Lua source files and the binaries. Errors are possible.
Contributors may find help in the Contributors guide to the Civ5 API.