DB.Query (Civ5 API)

From Civilization Modding Wiki
Revision as of 18:33, 19 September 2012 by DonQuich (talk | contribs) (Bot update)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigationJump to 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.

Retrieved from "https://modiki.civfanatics.com/index.php?title=DB.Query_(Civ5_API)&oldid=10853"