Difference between revisions of "Setting up Database"

From Nexus Mods Wiki
Jump to: navigation, search
(Created page with "<div class="OutlineElement Ltr BCX2 SCXW122478776" style="direction: ltr"><div class="OutlineElement Ltr BCX2 SCXW209497143" style="direction: ltr"> <span class="TextRun SCX...")
 
 
(20 intermediate revisions by 8 users not shown)
Line 1: Line 1:
<div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr"><div class="OutlineElement Ltr  BCX2 SCXW209497143" style="direction: ltr">
+
 
<span class="TextRun SCXW209497143 BCX2" data-contrast="none" style="color: rgb(47, 84, 150);  font-size: 13pt;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif;  line-height: 22.6625px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">Database</span></span><span class="EOP SCXW209497143 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 13pt;  line-height: 22.6625px;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif">&nbsp;</span>
+
= Database =
</div> <div class="OutlineElement Ltr  BCX2 SCXW209497143" style="direction: ltr">
+
 
<span class="TextRun SCXW209497143 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">Game database contains all the game data, such as items, dialogues, perks etc... The </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">modding</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">pack contains a dump of the game database in SQL format. You are going to need to run a </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">postgreSQL</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">server on your computer, which the </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">modding</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">tools will connect to.</span></span><span class="EOP SCXW209497143 BCX2" data-ccp-props="{" 201341983":0,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
The Game database contains all the game data, such as items, dialogues, perks, etc. The modding pack contains a dump of the game database in SQL format. In this tutorial we are going to use PostreSQL hosted locally on your computer, which the modding tools will connect to.&nbsp;
</div> <div class="OutlineElement Ltr  BCX2 SCXW209497143" style="direction: ltr">
+
 
<span class="TextRun SCXW209497143 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">First download and install </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">postgresql</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">from their website</span></span><span class="TextRun SCXW209497143 BCX2" data-contrast="none" style="color: rgb(255, 0, 0);  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">(https://www.postgresql.org/)</span></span><span class="TextRun SCXW209497143 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">, then install it. DB server should automatically start. Then you need to connect to the server, create a database called </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">ConfigDB</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">(the name is </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">unforunately</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">hardcoded into some of the tools), and add extension “</span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">uuid-ossp</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">”.</span></span><span class="EOP SCXW209497143 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt; line-height: 19.425px; font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
First download and install PostreSQL from [https://www.postgresql.org/ their website]. (Do not install version 12, as this might cause a missing column "adsrc" error when you later run the sequences.sql script) You will also want to install the 64-bit PosgreSQL ODBC driver, which you can get from [https://odbc.postgresql.org/ https://odbc.postgresql.org/]. After install the database server should be up and running. Then we open pgAdmin (comes with the PostgreSQL package)&nbsp;tool and connect to the server. After connecting to the server we have to create a new database called&nbsp;<tt><q>ConfigDB</q></tt>&nbsp;(the name is unfortunatley hardcoded into some of the tools). When created add new <tt><q>uuid-ossp</q></tt> extention to this database.
</div> <div class="OutlineElement Ltr  BCX2 SCXW209497143" style="direction: ltr">
+
 
<span class="TextRun SCXW209497143 BCX2" data-contrast="auto" style="font-size: 11pt; font-family: Calibri, Calibri_MSFontService, sans-serif; line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">In </span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">pgAdmin</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">(a tool that comes with </span><span class="ContextualSpellingAndGrammarError SCXW209497143 BCX2" style="background-color: inherit">basic</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit"></span><span class="SpellingError SCXW209497143 BCX2" style="background-color: inherit">postgresql</span><span class="NormalTextRun SCXW209497143 BCX2" style="background-color: inherit">package):</span></span><span class="EOP SCXW209497143 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt; line-height: 19.425px; font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
https://wiki.nexusmods.com/images/0/0c/Setting_Up_Database_image1.jpg
 +
 
 +
If you do not have acces to the pgAdmin you can run this via command line using&nbsp;''psql.exe''&nbsp;(comes with PostgreSQL package) with the following commands.&nbsp;
 +
<div style="background:#000; border:1px solid #cccccc; padding:5px 10px"><tt>CREATE DATABASE ConfigDB;<br/> CREATE EXTENSION "uuid-ossp";</tt></div>  
 +
Note that creating your database through the psql tool will result in stripping out the case sensitivity (ConfigDB becomes configdb). The mod tools do not appear to require precise case for the database name,&nbsp; but other SQL tools (such as running psql from the command line in the example below) might.
 +
 
 +
After&nbsp;completing first step we use&nbsp;''psql.exe or cmd'' to restore the database from provided dumps.
 +
<div style="background:#000; border:1px solid #cccccc; padding:5px 10px">
 +
<tt><PostgreSQL Installation Path>/bin/psql.exe -f <KCD Installation Path>/Data_reference/modding.sql -U&nbsp;postgres –d&nbsp;ConfigDB -q&nbsp;</tt>
 +
 
 +
<tt><PostgreSQL Installation Path>/bin/psql.exe -f&nbsp;<KCD Installation Path>/Data_reference/public.sql -U&nbsp;postgres –d&nbsp;ConfigDB -q&nbsp;</tt>
 
</div>  
 
</div>  
[[File:Setting Up Database image1.jpg]]<br/> <br/> <span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-US" lang="EN-US"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Or run psql.exe (also comes with</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">postgresql</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">) and input following commands:</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
You will be asked for your database password again. Ideally this command should run with no output of any kind (the -q switch hides all non-errors, and there should be no errors). In your database, there should be now a schema called <q><tt>modding</tt></q>&nbsp;with 468 tables.&nbsp;&nbsp;
</div> <div class="OutlineElement Ltr BCX2 SCXW122478776" style="direction: ltr">
+
 
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Consolas, Consolas_MSFontService, monospace;  line-height: 19.425px" xml:lang="EN-US" lang="EN-US"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">CREATE DATABASE </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">ConfigDB</span></span><span class="LineBreakBlob BlobObject DragDrop SCXW122478776 BCX2" style="font-size: 11pt; line-height: 19.425px;  font-family: Consolas, Consolas_MSFontService, monospace"><span class="SCXW122478776 BCX2">&nbsp;</span></span><br/> <span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt; font-family: Consolas, Consolas_MSFontService, monospace; line-height: 19.425px" xml:lang="EN-US" lang="EN-US"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">CREATE EXTENSION “</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">uuid-ossp</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit"></span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Consolas, Consolas_MSFontService, monospace">&nbsp;</span>
+
&nbsp;
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
 
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun CommentStart SCXW122478776 BCX2" style="background-color: inherit">Then run psql.exe to restore the database from provided dumps:</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
&nbsp;
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
 
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt; font-family: Consolas, Consolas_MSFontService, monospace; line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">psql.exe -f </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">Data_reference</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">/</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">modding.sql</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">-U </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">postgres</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">–d </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">ConfigDB</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">-q</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Consolas, Consolas_MSFontService, monospace">&nbsp;</span>
+
&nbsp;
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
 
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Consolas, Consolas_MSFontService, monospace;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">psql.exe -f </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">Data_reference</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">/</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">public.sql</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">-U </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">postgres</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">–d </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">ConfigDB</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">-q</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Consolas, Consolas_MSFontService, monospace">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
== Autoincrement ==
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Ideally this command should run with no output of any kind (the -q switch hides all non-errors, and there should be no errors). In your database, there should be now a schema called “</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">modding</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">” with 468 tables.</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
<span style="background:#ff9900">WARNING: This process was changed completely in version 2 of the modding tools. Make sure you have the new sequences.sql file</span>
<span class="TextRun SCXW122478776 BCX2" data-contrast="none" style="color: rgb(31, 55, 99);  font-size: 12pt;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif;  line-height: 20.5042px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Autoincrement</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 12pt;  line-height: 20.5042px;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
Some tables use autoincrement to generate IDs for new entries. If you are going to create new dialogues, you are going to need to adjust the initial value for this autoincrement to a value unique for your mod. If you don’t, your mod will clash with any other mods that also failed to set a unique value. To do this, pick an offset ([//forums.nexusmods.com/index.php?/topic/8510843-register-unique-modding-ids-20/ ''unofficial registry of IDs for modders'']), then run following command (replace X with your selected offset):&nbsp;
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Some tables use autoincrement to generate IDs for new entries. If you are going to create new dialogues, you are going to need to adjust the initial value for this autoincrement to a value unique for your mod. If you don’t, your mod will clash with any other mods that also failed to set a unique value. Do this by running following SQL query:</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif"></span>
+
<div style="background:#000;  border:1px solid #cccccc; padding:5px 10px"><tt><PostgreSQL Installation Path>/bin/psql.exe -f <KCD Installation Path>/Data_reference/sequences.sql -U postgres –d ConfigDB -v sequenceoffset=X –q</tt></div>
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
When using PostgreSQL 12 or newer, you will get an error message about "adsrc" column. To fix this error, open sequences.sql in text editor and replace all "adsrc" with "pg_get_expr(ad.adbin, ad.adrelid)", then save file and re-execute command.
<span class="TextRun SCXW122478776 BCX2" data-contrast="none" style="color: rgb(47, 84, 150);  font-size: 13pt;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif;  line-height: 22.6625px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Setting up tools</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":0,"335559740":259}"="" style="font-size: 13pt;  line-height: 22.6625px;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
== (Optional) Inspecting&nbsp;the Database ==
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">There are several tools that work with the SQL database. Most of them require you to set up correct registry keys which they read connection information from. To do that, modify file</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-weight: bold;  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Tools</span><span class="ContextualSpellingAndGrammarError SCXW122478776 BCX2" style="background-color: inherit">/</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-weight: bold;  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="ContextualSpellingAndGrammarError SCXW122478776 BCX2" style="background-color: inherit">!</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-weight: bold;  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="ContextualSpellingAndGrammarError SCXW122478776 BCX2" style="background-color: inherit">registry/db.reg</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">&nbsp; (you only need to modify the password, “</span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">postgres</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">” is the default user) and then run it. </span><span class="AdvancedProofingIssue SCXW122478776 BCX2" style="background-color: inherit">All of</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">the tools, except the Sandbox editor, use these registry keys. To get the Sandbox to connect, fill in correct connection information in</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-weight: bold;  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">user.cfg</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">.</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Sandbox editor also requires</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">an</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">ODBC driver to use. Install one for </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">postgreSQL</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">from</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="none" style="color: rgb(255, 0, 0);  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">their </span><span class="ContextualSpellingAndGrammarError SCXW122478776 BCX2" style="background-color: inherit">site(</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit"></span></span>[https://odbc.postgresql.org/ <span class="TextRun Underlined SCXW122478776 BCX2" data-contrast="none" style="color: rgb(255, 0, 0);  text-decoration: underline;  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">https://odbc.postgresql.org/</span></span>]<span class="TextRun SCXW122478776 BCX2" data-contrast="none" style="color: rgb(255, 0, 0);  font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">)</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">(use 64-bit version).</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
You can check yourself whether the database has been set up properly by looking at the&nbsp;<tt><q>START</q></tt>&nbsp;values of the sequences in the&nbsp;<tt><q>ConfigDB</q></tt>&nbsp;database. The values in the&nbsp;<tt><q>modding</q></tt>&nbsp;schema should either be equal to values in&nbsp;the cmd output of the first&nbsp;''psql.exe&nbsp;''command above or a sum of one of these&nbsp;cmd output values and your offset. Similarly, the value in the&nbsp;<tt><q>public</q></tt>&nbsp;schema should be equal to the&nbsp;value&nbsp;in&nbsp;the cmd output of the second&nbsp;''psql.exe&nbsp;''command above.
<span class="TextRun SCXW122478776 BCX2" data-contrast="none" style="color: rgb(31, 55, 99);  font-size: 12pt;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif;  line-height: 20.5042px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Working with </span><span class="ContextualSpellingAndGrammarError SCXW122478776 BCX2" style="background-color: inherit">database</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559738":40,"335559739":160,"335559740":259}"="" style="font-size: 12pt;  line-height: 20.5042px;  font-family: Calibri Light, Calibri Light_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
https://staticdelivery.nexusmods.com/images/2298/47833563-1584964381.png
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">The database can be edited with any SQL client you prefer, or you can use our custom tool.&nbsp; It is a plugin for MS Excel, which simplifies editing tables by unpacking foreign keys. To use it, you need to install in from Tools/ExcelDbAddin2, which installs it directly into MS Excel (it can be removed at any time in “Add or Remove programs”). It will appear as a new menu item called “WHS DB”.</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> <div class="OutlineElement Ltr  BCX2 SCXW122478776" style="direction: ltr">
+
<tt><q>START</q></tt>&nbsp;value = cmd output (20) + offset (69000)
<span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Any changes made to the database will be visible in the Sandbox editor (</span><span class="AdvancedProofingIssue SCXW122478776 BCX2" style="background-color: inherit">with the exception of</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">Localization). The game, however, reads the database from XML files inside </span><span class="SpellingError SCXW122478776 BCX2" style="background-color: inherit">Tables.pak</span><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">or mod PAK files. To create such PAKs, use the editor’s [[Modding_Tool|modding ]][[Modding_Tool|window]]&nbsp;</span></span><span class="TextRun SCXW122478776 BCX2" data-contrast="auto" style="font-size: 11pt;  font-family: Calibri, Calibri_MSFontService, sans-serif;  line-height: 19.425px" xml:lang="EN-GB" lang="EN-GB"><span class="NormalTextRun SCXW122478776 BCX2" style="background-color: inherit">.</span></span><span class="EOP SCXW122478776 BCX2" data-ccp-props="{" 201341983":0,"335559739":160,"335559740":259}"="" style="font-size: 11pt;  line-height: 19.425px;  font-family: Calibri, Calibri_MSFontService, sans-serif">&nbsp;</span>
+
 
</div> 
+
== Setting up tools&nbsp; ==
 +
 
 +
There are several tools that work with the SQL database. Most of them require you to set up correct registry keys which they read connection information from. To do that, modify file <q><tt>Tools/!registry/db.reg</tt></q>&nbsp; (you only need to modify the password, <tt><q>postgres</q></tt>&nbsp;is the default user) and then run it.&nbsp;All of the tools, except the Sandbox editor, use these registry keys. To get the Sandbox to connect, fill in correct connection information in <tt><q>user.cfg</q></tt>. Sandbox editor also requires an ''ODBC Driver'' to use. Install one for&nbsp;postgreSQL from [https://odbc.postgresql.org/ their&nbsp;site] (use 64-bit version).&nbsp;
 +
 
 +
&nbsp;
 +
 
 +
== Working with Database ==
 +
 
 +
The database can be edited with any SQL client you prefer, or you can use our custom tool.&nbsp; It is a plugin for MS Excel, which simplifies editing tables by unpacking foreign keys. To use it, you need to install in from <tt><q>Tools/ExcelDbAddin2</q></tt>, which installs it directly into MS Excel (it can be removed at any time in “Add or Remove programs”). It will appear as a new menu item called <tt><q>WHS DB</q></tt>.&nbsp;
 +
 
 +
Any changes made to the database will be visible in the Sandbox editor (with the exception of Localization). The game, however, reads the database from XML files inside&nbsp;''Tables.pak'' or mod PAK files. To create such PAKs, use the editor’s&nbsp;[https://wiki.nexusmods.com/index.php/Modding_Tool modding window]&nbsp;.
 +
 
 +
&nbsp;
 +
 
 +
== (Tip) Closing&nbsp;the Database ==
 +
 
 +
Don't fear the browser warning below, your setup is automatically saved and the browser can safely be closed without having to redo the database setup. You have to remember your password though.
 +
 
 +
https://staticdelivery.nexusmods.com/images/2298/47833563-1584964929.png
 +
 
 +
&nbsp;
 +
 
 +
== TL;DR ==
 +
 
 +
#Install PostgreSQL server.
 +
#Install ODBC driver (64bit) for PostgreSQL.  
 +
#Open ''pgAdmin'', it's installed with PostgreSQL package.
 +
#Create&nbsp;''ConfigDB''&nbsp;database.
 +
#Add&nbsp;''uuid-ossp&nbsp;''extension to that database.  
 +
#Edit ''Data_reference/sequences.sql&nbsp;''number 10578&nbsp;to&nbsp;[//forums.nexusmods.com/index.php?/topic/8510843-register-unique-modding-ids-20/ the ID you claimed].
 +
#Migrate files&nbsp;<br/> <br/> <tt>psql.exe -f&nbsp;Data_reference/modding.sql -U&nbsp;postgres –d&nbsp;ConfigDB -q</tt><br/> <tt>psql.exe -f&nbsp;Data_reference/public.sql -U&nbsp;postgres –d&nbsp;ConfigDB -q</tt><br/> <tt>psql.exe -f Data_reference/sequences.sql -U postgres –d ConfigDB –q&nbsp;</tt><br/> &nbsp;
 +
#Configure your&nbsp;''user.cfg''&nbsp;in root directory<br/> <br/> wh_sys_SQLServer = "127.0.0.1"<br/> wh_sys_SQLDatabase = "ConfigDB"<br/> wh_sys_SQLUser = "postgres"<br/> wh_sys_SQLPassword = "'''YOUR DATABASE PASSWORD'''"<br/> wh_sys_SQLBranch = "modding"<br/> wh_sys_PreferredDB = SQL, TBL, XML<br/> wh_sys_SQLWritable = 1
 +
#&nbsp;  
 +
#Done, start editor.<br/> &nbsp;  
 +
 
 
[[Category:Kingdom Come Deliverance]] [[Category:Getting Started]]
 
[[Category:Kingdom Come Deliverance]] [[Category:Getting Started]]

Latest revision as of 15:37, 19 February 2021

Database

The Game database contains all the game data, such as items, dialogues, perks, etc. The modding pack contains a dump of the game database in SQL format. In this tutorial we are going to use PostreSQL hosted locally on your computer, which the modding tools will connect to. 

First download and install PostreSQL from their website. (Do not install version 12, as this might cause a missing column "adsrc" error when you later run the sequences.sql script) You will also want to install the 64-bit PosgreSQL ODBC driver, which you can get from https://odbc.postgresql.org/. After install the database server should be up and running. Then we open pgAdmin (comes with the PostgreSQL package) tool and connect to the server. After connecting to the server we have to create a new database called ConfigDB (the name is unfortunatley hardcoded into some of the tools). When created add new uuid-ossp extention to this database.

Setting_Up_Database_image1.jpg

If you do not have acces to the pgAdmin you can run this via command line using psql.exe (comes with PostgreSQL package) with the following commands. 

CREATE DATABASE ConfigDB;
CREATE EXTENSION "uuid-ossp";

Note that creating your database through the psql tool will result in stripping out the case sensitivity (ConfigDB becomes configdb). The mod tools do not appear to require precise case for the database name,  but other SQL tools (such as running psql from the command line in the example below) might.

After completing first step we use psql.exe or cmd to restore the database from provided dumps.

<PostgreSQL Installation Path>/bin/psql.exe -f <KCD Installation Path>/Data_reference/modding.sql -U postgres –d ConfigDB -q 

<PostgreSQL Installation Path>/bin/psql.exe -f <KCD Installation Path>/Data_reference/public.sql -U postgres –d ConfigDB -q 

You will be asked for your database password again. Ideally this command should run with no output of any kind (the -q switch hides all non-errors, and there should be no errors). In your database, there should be now a schema called modding with 468 tables.  

 

 

 


Autoincrement

WARNING: This process was changed completely in version 2 of the modding tools. Make sure you have the new sequences.sql file

Some tables use autoincrement to generate IDs for new entries. If you are going to create new dialogues, you are going to need to adjust the initial value for this autoincrement to a value unique for your mod. If you don’t, your mod will clash with any other mods that also failed to set a unique value. To do this, pick an offset (unofficial registry of IDs for modders), then run following command (replace X with your selected offset): 

<PostgreSQL Installation Path>/bin/psql.exe -f <KCD Installation Path>/Data_reference/sequences.sql -U postgres –d ConfigDB -v sequenceoffset=X –q

When using PostgreSQL 12 or newer, you will get an error message about "adsrc" column. To fix this error, open sequences.sql in text editor and replace all "adsrc" with "pg_get_expr(ad.adbin, ad.adrelid)", then save file and re-execute command.

(Optional) Inspecting the Database

You can check yourself whether the database has been set up properly by looking at the START values of the sequences in the ConfigDB database. The values in the modding schema should either be equal to values in the cmd output of the first psql.exe command above or a sum of one of these cmd output values and your offset. Similarly, the value in the public schema should be equal to the value in the cmd output of the second psql.exe command above.

47833563-1584964381.png

START value = cmd output (20) + offset (69000)

Setting up tools 

There are several tools that work with the SQL database. Most of them require you to set up correct registry keys which they read connection information from. To do that, modify file Tools/!registry/db.reg  (you only need to modify the password, postgres is the default user) and then run it. All of the tools, except the Sandbox editor, use these registry keys. To get the Sandbox to connect, fill in correct connection information in user.cfg. Sandbox editor also requires an ODBC Driver to use. Install one for postgreSQL from their site (use 64-bit version). 

 

Working with Database

The database can be edited with any SQL client you prefer, or you can use our custom tool.  It is a plugin for MS Excel, which simplifies editing tables by unpacking foreign keys. To use it, you need to install in from Tools/ExcelDbAddin2, which installs it directly into MS Excel (it can be removed at any time in “Add or Remove programs”). It will appear as a new menu item called WHS DB

Any changes made to the database will be visible in the Sandbox editor (with the exception of Localization). The game, however, reads the database from XML files inside Tables.pak or mod PAK files. To create such PAKs, use the editor’s modding window .

 

(Tip) Closing the Database

Don't fear the browser warning below, your setup is automatically saved and the browser can safely be closed without having to redo the database setup. You have to remember your password though.

47833563-1584964929.png

 

TL;DR

  1. Install PostgreSQL server.
  2. Install ODBC driver (64bit) for PostgreSQL.
  3. Open pgAdmin, it's installed with PostgreSQL package.
  4. Create ConfigDB database.
  5. Add uuid-ossp extension to that database.
  6. Edit Data_reference/sequences.sql number 10578 to the ID you claimed.
  7. Migrate files 

    psql.exe -f Data_reference/modding.sql -U postgres –d ConfigDB -q
    psql.exe -f Data_reference/public.sql -U postgres –d ConfigDB -q
    psql.exe -f Data_reference/sequences.sql -U postgres –d ConfigDB –q 
     
  8. Configure your user.cfg in root directory

    wh_sys_SQLServer = "127.0.0.1"
    wh_sys_SQLDatabase = "ConfigDB"
    wh_sys_SQLUser = "postgres"
    wh_sys_SQLPassword = "YOUR DATABASE PASSWORD"
    wh_sys_SQLBranch = "modding"
    wh_sys_PreferredDB = SQL, TBL, XML
    wh_sys_SQLWritable = 1
  9.  
  10. Done, start editor.