Learn to Hack Games - Civilization VI - SQL - Part 2

Description

This is part 2 of my series on game modding. In this video I create a randomized tech tree for Civilization 6. Topics covered: SQL, XML, LUA, Debugging, Modding, Hacking, Reverse Engineering

Transcript

I received a request to hack the civilization 6 game from leafcutter Overlord he said can you do a randomized tech tree well let's see what I can do okay I'm starting a new game I'm gonna click on my Tech Tree wait a second those don't belong here at the front I guess I did it let's take a look at Civics yep if you're interested in learning how to code this stick around my name's Devin and this channel is for coders interested in gaming hacking and AI if you're not familiar with civilization let me show you how it normally goes starting a new game again without the hack putting Tech Tree this is normal Pottery husbandry mining so let's see how I would code this the easiest way to search inside of code I think is to look at strings because the code itself is going to get turned into zeros and ones drinks which is text like Pottery here those characters are going to stay intact in the game so if I search for those that'll give me a clue let me search for the word Pottery inside the code I'm going to open up my task manager and I'm going to look for the civilized station executable I'm going to right click and say open file location that'll tell me where the code is in the game an executable is all the code turned into zeros and ones and I can run that through a disassembler which will turn it back into Assembly Language which is a human-like version of the machine code it's still really hard to read but it's possible however first I'm going to start just searching all of these files because sometimes there's some configuration files that are helpful where I won't have to actually read the code itself I'm going to open up visual studio code you don't have to use this tool but it's one that I like it's for coding but I'm just going to use it for searching I'm going to tell it I want to search in this folder and from the game I'm going to search for pottery well I have some XML files here and XML files are typically configuration files that are somewhat human readable but also computer readable this one says buildings probably not useful resources probably not either technologies that makes sense this might be it because it's the tech tree that I'm looking at okay this is really interesting so Pottery right here it has a type and a name and this is not really what we see on the screen because this has a weird prefix so those are more like variable names that must be translated somehow okay but here the cost is interesting if I go back into the game the Potter has a cost of 16. I'm not sure why the cost doesn't match so I'll have to investigate that error type makes sense so if I open up here if I let me hide this so on the far left side it says ancient era and if I scroll it says classical era so Pottery is inside the ancient era so that makes sense and then UI tree row UI probably stands for user interface and we're looking at a tech tree so row is probably like where it's displayed computers usually count by zero so sailing's probably row zero astrology Row one out of curiosity let's just see if I modify this XML file let's see what happens so with it being cost 25 in the XML and it being 16 in the UI let's just double it and see what happens so let me save and then I wouldn't expect the game to just automatically update I'm probably gonna have to quit out and it didn't because it still said showed 16. reopen the game start a new game open up the tech tree okay that's a little weird Pottery shifted to the right but it did change to 33. so I think it was 16 before and I doubled it from 25 to 50. let me open up a calculator I guess it is basically double and I think the reason it moved to the right is it must automatically sort based on the cost so that the cheapest things are on the left and the most expensive things are on the right it is a little weird this dashed line got drawn through it but I'm given the game data it's not expecting so it makes sense that it's gonna get confused okay so I was able to modify this file so in theory I could write a program Ram that opens up this XML file changes all the data around saves it and then I just do that every time before I open the game but the downside to that is if I permanently modify this XML file it would be hard for someone to get it back to the original state so it's probably better if I randomize it on the Fly while the game's running so let's see what else I would have to change in here to get a truly randomized Tech Tree I think I would want to pick two random Technologies so let's say pottery and currency in order to swap them I could keep the names the same but I'd want to swap their costs because that's going to make them shift left or right I would also want to change the era to make sure it moved to the correct section and if this UI Turi row is vertically where it shows I probably want to swap that too let's just see if we can swap pottery and currency so I'm going to undo what I did before and I'm going to take this 120 put it in Pottery it's 25 in currency they both have the same UI row so I'll just leave that alone but currency was under classical and pottery was under ancient let's scroll through the rest of the file to see what else might need to change okay so I have prerequisites here so if Pottery or currency have any prerequisites I should probably swap those around and let's see what else quotes I don't think I need to change that because we still want the description and text to be the same boosts are how you earn the technology quicker if you accomplish some goal in the game so I think we want to leave the Boost the same so you earned them the same way modifiers it looks like this is the actual bonus you get when you earn the technology so I probably don't want to modify those either because we want the Technologies themselves to stay the same we just want them to be earned in a different order anywhere a prerequisite is called Pottery I'm going to change it to currency I guess I'll blink out all the ones that say currency so I have kind of a placeholder because those are the ones I'll need to change to pottery and then the pottery ones I will change the currency so then I need the blank ones and change them to Pottery here's a blank let me save that let me quit out the game and reload and something I forgot to mention is before you start hacking a game you want to open it up and make sure that it does not have any valve anti-cheat so if you get caught cheating they'll ban you so you can't play online anymore so I don't see anything that says anti-cheat here but still just to be safe I'm actually hacking the demo and as an example let me look at the game I know does have anti-cheat so if I scroll down here on Counter Strike it says valve anti-cheat enabled so we didn't see that on civilization so we should be safe anyways let's start up a game in Civilization and to open up the tech tree so we tried to change pottery with currency and it looks like it works currency is now on the left it shows 16 costs which is what Padre had the nice thing is the arrows are drawn correctly now so maybe the reason that the like dashed line was covering up Pottery before was because we didn't set up the prerequisites correctly and then let me scroll and see Pottery okay pottery's over here it has 80. and you can see all the descriptions and icons are still the same and also the eras so Pottery got put into the classical era so it looks perfect so we know we can modify it that way let's see how we can modify it in code so that I don't have to modify these XML files every time so I'm going to hold Ctrl Z just to undo everything I changed I guess let's search for another word in the code um what's probably happening is this XML file has a list of data points and something in the code has to be reading each one of these lines one at a time to figure out the data inside of them and it's not very likely that it'll be in one of these files because XML is a configuration but the thing that's going to read the XML is probably going to be actual code so I'm probably going to have to open this up in a disassembler but just to be safe I always search these files first because if I can find them in a regular text file instead of in the actual code it's going to be much easier for me we've got an SQL file which is for databases create table okay that sounds really promising because if there's a database that stores all this data we might be able to modify the database and the nice thing is database code SQL is like programming code so we could possibly modify it to randomize because the problem with the XML is you can't put actual code in there so I'd need a separate program to randomize this file and then I also have another configuration file XML and then two Lua files so Lua files are also promising because they are a scripting language that a lot of games use for like controlling the AI so let me just open these little files up uh okay so this Lua file probably won't help me because it says civilopedia and I know that is the UI for the encyclopedia so this is just going to be rendering something on the screen that explains what each technology means so if I modify this code it might change how the encyclopedia looks it might randomize it in the way it's rendered but that's not necessarily going to change the game rules and logic so that I can actually earn the Technologies out of order so I don't think that's going to help me let's try the next Lua okay so this is related to the tech tree so this could be promising tool tip helper pick is that I mean picture art size is so this actually may not be helpful either because I'm wondering if this is about rendering a Tech Tree on the screen so just like the previous one was probably rendering the encyclopedia this all looks graphical to me padding and art maybe this is just rendering the texture on the screen and just like I said before if I modify it just when it's rendered it may not actually change the logic behind the scene so it might be changing it too late in the game so let's start with the database and see where we go from there I guess I should explain a little bit about how SQL Works SQL is for relational databases which means you have a whole bunch of tables and the tables have relationships between them so for example we were looking at Technologies before and also technology prerequisites so those would be probably two separate tables and they would point to each other okay so this probably matches the XML really closely so the prerequisites we're looking at we have a row with an attribute called technology and an attribute called prereq Tech and it has a string value inside so if we go back inside this table there are two columns with those names each of them can store text so other types of data would be things like integers which are numbers Boolean which is true or false not null so null is basically a missing value so it's not exactly like zero it's more like the absence of any value at all so by saying not null that's a constraint that tells this column to not allow the code to insert a missing value primary key so that's something that uniquely identifies a specific row so that would prevent the code from accidentally duplicating the technology so the same technology can't have the same prerequisite multiple times a lot of times you only have one primary key but this is called a composite primary key so there's two different columns combined that create the uniqueness and it's beneficial to have a unique value in each row because that's going to be helpful for doing faster lookups in a table you could also create an index to make lookups faster as well and then foreign Keys is how you create relationships so saying this technology column right here it references a separate table called Technologies oh that's good I'm going to need that table possibly and then because that's how it was in the XML as well there was this technology section that had a bunch of rows inside of it so there must be a table called that and then the technology column here matches the technology type column in the Technologies table so for example where we've got Tech underscore pottery that same value would be inside this technology type Tech underscore Pottery so it would connect those two together if I'm wanting to search for which prerequisites a technology has that's what the foreign key or the relationship does and here this delete Cascade update Cascade because technology is kind of the main table so if I were to delete Pottery out of the main technology table it would automatically delete it out of the prerequisites table as well so I know I've got two tables technology and prereqs uh I wonder if somewhere in here it's inserting the data into those tables from the XML let me just scroll through real quick okay so I had a bunch of the create tables all ended and now I'm going to inserts navigation properties okay so I'm not sure this navigation property table is but this is basically saying insert some data into this table and it's listing which columns to insert into and then which values to put into those columns so each insert is going to create one row but that's the end of my file so unfortunately I don't exactly know how this XML data is put into the database and I actually don't even know if this database is used by the game or not but let's see what we can figure out so first of all at the very top I noticed this little command pragma schema version I'm kind of curious which type of database this is using let's just copy this and Google it and see what comes up okay so the first thing that came up is SQL Lite so I'm going to assume that's probably the database they're using the reason I wanted to know that is there are lots of different versions of SQL they're all extremely similar but they have slight differences so knowing which database engine I'm using is going to help me and sqlite makes sense because that's a small database that's usually embedded inside of a program whereas other types of databases like SQL Server are generally run on a server somewhere and not necessarily inside of an app so I want to see if I modify this SQL file what might happen and maybe the first thing I can do is databases have a concept called a trigger and basically they are like an event that occurs anytime a specific action happens so since I know that there's a table called Technologies then I'm gonna create a trigger based on that so I'll say after insert on Technologies and then I need a semicolon whenever I end a command so inside of the trigger I don't need this tab but it will just help make it easier to read so what this is saying is anytime the technology table gets a record inserted then I'm going to execute this trigger and this is just a made-up name it needs to be unique but it's I can use any name that I want so usually you want to make it something descriptive that kind of matches what the trigger's doing so I think I want to just run an update statement on the Technologies table and let me see what columns are in the Technologies table okay so here's the table with its list of columns I basically want to test if I change something in this SQL file if it'll actually make an effect on the game the same way that by modifying the XML I made an effect on the game so ideally I want to change something that's visible on the screen so that I know if it actually had an effect so this description here that might be useful I could put some actual text okay so I'm going to say update technology set description equals and I'll just say the word test I think where um what's the name of the columns again technology type or technology type equals Tech underscore Pottery semicolon normally for databases it's not case sensitive I'm not positive if sqlite is so I'll just to be safe make it all uppercase because that's how it was in the XML languages like JavaScript or C plus are case sensitive when you compare strings so just be safe and also this was a little odd in in SQL a single equal sign is a comparison in other languages like C plus plus it's a double equal sign as a comparison so I'm saying take the Technologies table search for anywhere that this technology type column is pottery and update it and modify the description column of that row to be test and because I know technology type is the primary unique key then this should only update the one matching row but in theory if I did not have a where clause in here like if I just said update set description it would actually update the entire table and modify every row to have that value so you do want to be careful with your where Clause to make sure it's filtering for the correct rows I just remembered some databases prefer single quotes instead of double quotes I'm not super familiar with sqlite I usually use SQL Server and the reason I use double quotes is because they're being used here but those are for column names which is different than strings so I wonder if I should have used single quotes here I forgot to use the word begin right here so when you at the end of the create trigger statement you need to wrap your code and it begin and okay I started the new game okay perfect that is really exciting so the word test showed up when I Mouse over Potter so that means if I modify something inside of the SQL it affects the game so let's think about the end goal here when I modified the XML I had to change the prerequisites and the Technologies and I can do that in a trigger but there's a few caveats I don't want to randomize the data until it's completely inserted and with sqlite you can do a bulk insert into a table and in this case like with navigation properties they're doing one row at a time I don't know if the real code for Technologies is inserting one row at a time but for sqlite with a trigger it only will trigger on one row at a time so if they insert 100 rows in bulk I'll get a hundred different trigger executions so the issue with that if I randomize during the first execution of the trigger I'll only have one technology available so that's not really going to randomize anything I need to wait until all I don't know how many Technologies there are but all 100 are inserted and then randomized afterwards and also because I have to randomize the prerequisites and the Technologies I really need to wait until they're both sets are inserted and I don't know do they does the code insert all the Technologies one at a time and then afterwards insert all the prerequisites one at a time or does it do like one technology and then one prerequisite I'm going to assume it probably does all the Technologies first and then all the prerequisite second because since there's relationships between um like these Keys have to be looked up in the Technologies table as that's a relationship there would be an exception in the database if the key didn't if the the relationship couldn't be navigated because the key didn't exist in the primary table so I'm going to guess they probably insert all the Technologies first and then afterwards of prerequisites but I should verify that and the second thing I should verify is this trigger is for an insert there's other things you can trigger on I probably should verify whether or not there's any rows that are updated or deleted it probably doesn't matter but just to be safe I'll check for it because if that is happening if for example if I randomize things and then afterwards the values get modified then it could unrandomize my data and I wonder if I want to wait till after prerequisites and Technologies are done I wonder what table comes after like quotes so I should look at the if there's a table for this as well and then check the order that the rows are inserted so let's see search for Tech quotes okay there is a table so I know all those three rows exist so let's do something here to just kind of figure out what order things are happening so I'm going to do a trigger on technology prereqs okay so that's the name of this table so I want to check for inserts on that and I'll just put the table name into the description since that's going to show up in the UI on the game then I can give myself a note basically telling me if the code was executed or not so I'll also do it on quotes and these names need to be unique they don't have to match the actual table name so I'll just give them a random value just so it doesn't crash so I'm always updating the technology table based on these different triggers and then I also want to look for updates as well so I'll copy that whole section and paste it change changes to update and then put an update in the text here uh since I don't have a where clause on here they're going to overwrite each other based on which one happens like first or last uh so for example if this one changes everything to Technologies and then immediately afterwards everything gets changed to prerequisites then I won't actually know if this first trigger was happening or not so I'm going to put a where clause on here and only do it for specific uh technology names so this one I'll do if it's pottery and pottery animal husbandry mining sailing astrology and irrigation so I'm going to restart the game the idea here is I want each trigger to be completely isolated to a specific technology so I know for each individual trigger whether or not it's actually executing because I need to know how the code inserts the records into the data database so that I can be smart about how I randomize and modify the data start a new game hope tree okay so Pottery says Technologies husbandry says technology prereqs mining says technology quotes these descriptions look like the default these don't have any weird words that I added let me double check so that would be so these first three triggers I'll executed these last three of the updates none of them did so sailing Estrada astrology irrigation these are all so that's good news so the data is never modified so I don't even need to worry about that and all three of these triggers are happening so what I need to know is which order these are happening in whether it's in bulk all Technologies and then afterwards all prereqs and after resolve quotes or whether or not they're all mixed up and happening in random orders so I think a way I could do that is I could add on to my where Clause so I'm going to assume that technology quotes happens at the very end so if I say only modify the pottery if there exists at least one row inside of quotes then that would tell me whether or not every single one of the Technologies is inserted before quotes start getting inserted or whether or not they're happening out of order and actually I'm going to take off pottery because I just want to know if any row any technology row is inserted after quotes start getting inserted so I think I actually just don't want this trigger to run at all and I can actually run both of these so I want to see if anything ever says Technologies or technology prereqs but the idea would be everything just says its default description start a new game look at the tech tree okay all the descriptions look normal so I'm I'm pretty confident it's possible I made a mistake in my logic but none of this code actually matters this was just testing to make sure I understand how the data is getting inserted so my theory that I'm 90 confident in is that all Technologies are inserted and then all the prerequisites are inserted afterwards and then afterwards all the quotes because that would make sense if I were to code it that's how I would code it and I haven't seen any evidence otherwise so what that means is I probably want my code inside of the technology quote trigger because if I put my code inside of either one of these triggers it would be hard for me to know if it's all the way completed inserting every single technology and every single prerequisite and I don't want to randomize before all the data is there but if I do it on the technology quote then I know all the data's there so I don't think I need these triggers at all I do want this trigger so let me give it a better name and instead of after insert I think I actually want B4 insert so the very first time this gets executed this table should be completely empty whereas if I did after insert there would be one row the first time I get executed and then I just want to randomize the data one time and then leave it alone so I'm only going to execute the code if the quote table is still empty because that means it's the first time the trigger got executed another way I could do it is at the end of the trigger I could have the trigger disable itself either way it would work but also do it this way so I'll say where not exists select star from quotes so it'll only update the description to this value it's the first time this trigger is executing and while I'm at it I think I want some extra debug data in here that might be useful for later I want to find out what version of sqlite is being used because that will affect what type of commands I can execute because if they have a really old version there might be some newer commands that I'm not allowed to use and the randomized code is going to be complicated so I need to know what features are available to me okay when I Mouse over it says 3.13.0 so let me download sqlite 3.13.0 I couldn't find the exact version of sqlite Studio that I needed but this is the closest I could find so the studio version is 306 but that's just the user interface version the sqlite is the actual engine database engine that runs behind the scenes and it is version 3 8 10 which is pretty close because I need three thirteen zeros so it's about five versions off from 8 to 13. there might be some new features that were added but it's unlikely they removed any features so if I code something uh some test code that works to randomize the data in here it should also work in Civilization so I'll use this for testing just because the random code I'm going to write is going to be complicated and it's a pain to open and close the game every time I make a minor change so I want to code it in here first and be confident that it's correct before I paste it into the game so let's copy just the data I need so I know I need to be able to work with the technology table so I'm going to copy this across to sqlite and then I can execute it now and then refresh my tables on the left so I shouldn't need to keep this around I'll just comment it out because unless I drop the table it should stay that stay there I may want to be able to truncate truncate deletes all the data out of a table so I'm just commenting this out but in the future I'm guessing every time I do a test I'm going to want to clear out the data for my next test so I'm also going to need the prerequisite is right here and I'll I think if I highlight and execute it should run just that what I have highlighted yep truncie table technology pricks I need semicolons on those and then I don't think I actually need the quote table because even though I'm executing this code inside of a trigger I'm only doing that because that's a way to inject it into the game when the event happens in the game that the data is ready but in sqlite studio I can execute the code whenever I want I don't need to wait for the game to cause it to happen because I'm just doing a test here so I'll just insert directly into these with some fake data for testing and because I'm just testing I also don't need all of these columns problem so okay I'm going to change era because I did that in my testing with XML before I wasn't changing these before the advisor I think I will change UI tree row in critical I don't know if I need those but I don't think I do because all of this data I want it to stay the same I do want to keep costs so I'm basically just switching the cost and the era so that it it's location on the tech tree changes but the actual name stays the same all both those columns because I'll be modifying them both I don't think I care about the relationships okay and then I want to insert some fake data here testing list off my column names we can pasting but I guess be just as easy to retype it okay and I think a good test would maybe be three random Technologies so let's do free animal husbandry and Mining it might be better if they're from different eras our values okay so if these three can randomize correctly what's at my code written then I'll assume everything else can rattle it'll cost on these Pottery I think was 25 yep uh Celestial navigation is 120 military tactics is 300 and then the era official navigation it's classical military tactics is some medieval and then the tree row so this one's a negative two it just for fun I'm gonna pretend that Celestial navigation is one and then I already remember that Pottery was Zero yeah because I don't want to have two of them that are negative two because then I won't know if it actually swapped correctly because if it randomly swaps Celestial and Military it's going to be swapping negative two with negative two so I won't actually know if it's swapped okay so I'm inserting three records into the Technologies table these are the list of columns these are the list of values and the prerequisites want to do basically the same way didn't create the writing Row in the technology well that's fine it won't hurt anything because I deleted the foreign keys so it's not going to throw an exception that because writing doesn't exist the product probably has multiple relationships but I'm just gonna assume if I get it working with one it probably will work with more and if I find a bug after I get it running in the game then I'll research it more but just for ease of testing if I only have a couple of values it'll be easier to visualize if I've coded correctly Celestial navigation has sailing I guess there is something I need to think about which is the technology could be on either side of the prerequisite it could be the primary or it could be the dependent I probably want a an example of both Pottery is only on the right side Celestial is only on the left side so I guess that's fine that gives me an example of each military maybe you don't even need I already have an example of each but it's in case there's military tactics okay so I have my example data so let me run this just to make sure inserts correctly so now I gotta think about this is just my setup code for how I'm going to test but now I got to think about how I'm actually going to code this so something I think could come in handy every table has a column called row ID which uh basically is just an incrementing number for which order the rows come in so what I could do I think is take the technology table sort it in a random order and insert it into a second table because if it's in random order then I could match up like the number one which is Pottery in the original table if it's in random order in the second table then number one might be military tactics so if I match up the number ones to each other it would show me that pottery and military tactics are connected and the idea is I wanted to swap two technologies with each other so that will create a randomized tech tree because if every single tech technology gets swapped with one other random technology that should create basically a completely randomized Tech Tree so let's try that I'm going to create a table that will be almost identical to the original Technologies but I'll call it randomized Technologies and I don't think I need the actual values here like cost I just need the name of Technology type and then I'll insert to randomized column name I'm going to insert into is technology type before when I inserted it I use this values command that will only insert one row instead I want to insert entire data set like a bulk insert and I'm going to get that data set by selecting which is like being or reading all the data out of a different table so I'm going to select from original Technologies table and I have to list what columns I want so if I did an asterisk here it would mean read every single column but I'm only only care about the technology name because I'm not going to randomize the cost I'm just going to swap the cost with whichever matching technology it's whatever technology gets matched up with so if I just run this it will make keep all the Technologies in the same order but I want them in random order I don't need to specify this row ID column that will be automatic sqlite always creates a row ID column even if you don't tell it to and that's always the order that the records were created in when they were first inserted into the database so there's a function called random if I order by that work so I'll create the table the records then I'll test a querying the data okay so before it was Pottery first and Celestial navigation then military tactics and now that's basically the exact opposite order but it definitely randomized it so next I want to match up the I guess I'm going to swap out so I'm going to call this table swapped oh geez and I'm gonna need the technology name twice okay so I've got my name ice and I'll call one original swapped and put a primary key on here so that if I accidentally put it so that the same combination is used twice it gives me an error I think I'll also make each one unique so that iterate so Padre can't be original twice and it can't be swapped twice okay so let me create that table and then let me figure out how I'm going to insert into that table up to Technologies you don't have to have quotes around a table name or a column name in some cases I've done it and sometimes I haven't it works either way okay so I'm going to need to select from both the original technology and the randomized technology I think select from ologies join randomized technology so after you say it a table name you can give it an alias so you could say the word as and then a lot of times you just use the first letter of the table name so Technologies has t but you don't have to have the word as so a lot of times I'll just do space t there it's just nice to have a shortened Alias so that when you it's it's like a variable name and every time I reference this table it's nice to just use one letter instead of retyping the full name I use a join I need to say which columns between these two tables I'm going to use in this relationship to match up the records let's do it on the row ID like I mentioned earlier actually if I were to run the demise I've got these rows one two three if I do the same thing on Technologies I've got rows one two three but for Technologies it's Pottery is number one and for randomized military tactics is number one so I'm saying match up where these two tables have the same value so match up the two number ones that match up the two number twos and match up the two number threes oh that's going to do is create I'll just for now do a select star when I join these so you can see the results it's going to create a nation so all of the rows from the technology table are on the left they're all the columns from the technology table on it are on the left and then from the randomized technology it's on the right so you can see Celestial navigation is matched up with pottery and it doesn't show the row ID column because I didn't tell it to include that but it's fine so when I insert into the swapped technology so T dot technology type from this first table the technology type is going to get put into original comma r dot g type so from the randomized the technology type is going to go into swapped and here it says technology type colon one that's just saying that this technology type column name is duplicated because both tables have that column name but with this the colon one is just saying it's a different one but it has the same name so it's just keeping them separated so that's why I need to put this alias in front r dot and T dot because if I just said technology type wouldn't know which of the two tables to grab that value from okay so let me run this and let me explain the join actually so this is a shortcut for the word inner join and what that means is both tables have to have the value a matching value for the row to to for to keep the row on the final result uh and if I take off the word inner it's still it still means inner join the other options are left outer or right outer I can also take out the word outer if I just say right join it means the same thing as right outer and if I say left join it means the same thing as left outer and then I can also do a full join oh or or a full outer but the outer doesn't isn't required so let me explain what those means well let me start with the left join if the left table has the number one and the right table does not have the number one then with the left outer join it would still keep a row but on the right side it would just have the null value meaning a missing value and but if it was the opposite if it was a left join and the rights table had a number one and the left table did not have a number one uh it would just exclude the row altogether and a right join would basically just be the opposite of that and then full join be a combination so if left side had a number one and the right side didn't it would keep it and if the right side had a number two on the left side didn't it would also keep that so it's a combination of both the left and the right outer joints but by doing an inner join or just a join I'm saying I need value to exist on both sides of the table in order to keep it in the final result so run this insert and let me collect swapped Technologies table see Celestial navigation got matched up with pottery shop with military tactics military tactics got matched up with Celestial navigation let me just add I'm not going to execute this yet but just in case I need for it for testing later I still have to drop the table and recreate it a code is all test code but this code at the bottom is stuff I'm going to keep in my final and my final code so I want to keep the two sections separated so nap the swapped Technologies I need update the values in the technology table the same way before where I keep the name Pottery the same but any of the values from Celestial navigation so it needs to grab the 120 classical one and put them in here and then the opposite it needs to take from Pottery the 25 ancient zero and put them in military tactics one thing that's a little tricky about this is

let's say I have a variable called pottery and it equals 25. I have a variable celestial navigation which equals 120. want to copy the value from celestial three afterwards I want to copy Pottery into military tactics work because so the value 25 is put into Pottery right and the value 120 is put into celestial is getting copied into Pottery so Pottery is now 120. but the original value of 25 has been lost so when I try to copy Pottery into tactics instead of getting the original 25 copied into tactics it's going to get the number 120. so both pottery and tactics will be 120. the way you typically do something like this is you first put Pottery since it's the first thing that you're overriding you put its original value into like a swap variable um so swap equals pottery and then when you copy any tactics you could use swap instead of Potter so that way tactics gets the original 25. so I guess another way to do that would be to call it a backup Pottery we're going to back up Pottery value so that even though it's been modified by Celestial we can still put it into tactics so in a database it's going to be the same thing when I run an update they're going to be overriding each other and so I kind of need to make a backup first so in order to make a backup first want to create a backup table which will basically be identical to this technology stable I remember I deleted a bunch of columns here that I didn't need for testing I also don't need to back up the entire table I just need to back up the columns that I I'm going to override so backup Technologies there's a shortcut so I I could create the table like this and then afterwards say insert into backup Technologies select star from Technologies would work but I actually don't even need to do the crate at all instead I can say create table pick up Technologies as select start from technology and it will essentially automatically figure out what columns are in this Technologies table and put them into this table so and but if I'm going to do that in this test code I only have a few columns but in the real code I have a bunch of columns and I don't really want to back up everything so it probably wouldn't hurt but I'll just back up the values I care about so I want the technology type cost error type UI tree row should work and then I'll add a drop up here just for testing in case I need to I have a backup now so the columns I'm modifying are cost era type and UI tree row so I'm going to write what's called a sub query so in parentheses I'm going to write a completely separate query that finds what value to put into the cost so the sub query is like this original table Technologies it's going to look it up in the swapped Technologies and look it up in the backup Technologies and Buffs will be to find which technology is being swapped with and the backup will be used to find what cost value B dot cost because I'll use BS backup my Alias and then I'm going to join this backup Technologies to swapped Technologies of s this is going to be a little bit tricky so the the update command is going to if I don't have a where clause in here update every single Row in the entire table and so think of it like a big loop going one row at a time and each row when it decides what cost value to do it's going to run this query to find out the value so I can use this technology name as filter inside of this query I know which cost to look up so on the swapped Technologies I'm going to say the original is from LG's dot technology type so here I have the data for my original and swapped so Technologies it's going to Loop over the whole table and if we remember the first value that I inserted originally was Pottery so that's the first value it's going to try to update is this row so it's going to look for anything in the swapped table that says Pottery as original I have the swap table shown here and the original Pottery is the third row it's going to look up this Pottery value and filter to this row and then the cost that I want to return comes from backup so far when I've joined I have not said what to filter for on backup Technologies so right now it's returning three different costs one for each value of backup so I wanted to filter for the military tactics value for the backup so that the updates pottery I wanted to put in the cost for military tactics so I need to say and S not swapped and B dot technology type so I'm saying from swapped Technologies filter for pottery because that's the first row in Technologies then because that row has military tactics and then take backup technology and look for military tactics so there'll only be one row for backup Technologies one row for swap Technologies and because we joined them it'll be one long record that has all the the values from the one row of backup Technologies next to all the columns for the one row swap Technologies and I'm just returning the cost from the backup which is the military tactics cost modifying the Technologies table and we discuss how it's a loop and the first value that's being modified was Pottery because it's the first row in the table so what we did all our filters for then it's going to modify the pottery row and give it the cost of the military tactics row I'm going to copy this exact same for error type the only thing I'm going to change is just which column is being read out of the backup to assigned same thing for UI tree row that should be everything I need to swap the technology table and I'm going to next want to do this a similar thing for the prerequisites but for now let's just execute it and see what happens so first let's check what's supposed to be swapped let's just screenshot this so I'm going to hit print screen on my keyboard and paste it into a paint program so I can remember what these were for later we're on this update so I should see that all the data is swapped around my screenshot Pottery was supposed to get swapped with military tactics tactics has 300 medieval negative two so that swapped exactly how it was supposed to next randomize the prerequisites because if you remember when I was looking at the XML file and I was changing the prerequisites and I was swapping pottery with currency I think so I deleted all the I think I deleted all the currencies and I just remembered that an empty string was a placeholder for pottery and then afterwards I went to all the potteries and I did currency that's necessary for the exact same reason that that backup variable is necessary because if I don't delete the currencies first but instead changing all the potteries to currency and when I get down to this currency here I'm not going to know was this a pottery that got changed to currency or was this an original currency that should have been left alone because potteries are changing the currencies but the currencies are changing the horseback riding and so I need to know was this an original currency or was it a pottery that got already got changed to currency so I'm going to need backup type mechanism the way I did it before was I just cleared out the currencies and left it an empty string the problem with that is when you do an update statement uh like I did here I don't have any where Clause I'm not filtering for a specific type of data or like a specific row so I'm updating the entire table all at once in bulk so what would happen is if I followed that mechanism where I just cleared out you know all the currencies well the same thing would happen with the ship building and navigation all and astrology all at the same time so everything we get emptied out and so I would have the same issue I wouldn't know what value is this supposed to be so instead I think I'll just do something like add a space to it so maybe I take all the currencies and add a space and then when I change all the potteries to currency I won't add the space and that way I'll know the currencies that have a space were original and the currencies that don't have a space are the new ones okay let me think of how I want to code this prerequisite randomization or swapping so first I want to update the prerequisite table so update that table with a set I'm not gonna have a where Clause because just like before I'm going to Loop over every single row and update all of them

but I want to change both of the columns just like before and I'll have a sub query that decides what value it's going to put in and I'm not going to worry about the second column yet because once I have the technology one written I'll just copy and paste and modify it so I'm modifying the technology so I just want to use the swapped technology so s dot technology from swapped Technologies so I'm going to Alias it with s so that it's easier I don't have to retype the whole table name and I don't need to do a join because this it's already looping over every row technology prereqs so I'll just say where s dot swapped equals technology prereqs dot technology so basically the technology column and the prerequisites I'm searching for that technology value in the swapped table and I'm figuring out which original value oh this should be original not technology I'm finding which original value it was swapped from so I'm going to look in prerequisites for anything or the technology is pottery and then I will find the swap technology and get the original value which is Celestial and I'll return that so I'm going to change any prerequisite that says pottery will instead get changed to be Celestial navigation hopefully that makes sense um and then I'm going to do the exact same thing here with the prerequisite text Tech so instead of looking for a technology so in the main table of technology prereqs I I guess I should query this table real quick so you can see what it looks like so if Pottery is the value in the prerequisite tag I'm going so as it's looping through if it's on the row for for that has Pottery it's going to look up in the swapped Technologies what it was swapped with so then if I look at Swap Technologies pottery with swatched with swapped with Celestial navigation so then it's going to grab that as the original and put it into the prerequisite Tech that seems like it should work but then I mentioned the issue where I need to put a space in there so if I put a space most SQL languages uh if you want to take two text values and combine them together so here I have a text value of a space and here I have the text value that came from this column in most SQL languages you use a plus sign to and it's called concatenation when you add two words together in the case of sqlite for some reason they use this double pipe symbol and that means the word or

um in most languages but for some reason in sqlite this is concatenate I feel like it's a little bit awkward but I think this should be correct let me run it and see what happens okay so I got an error um about the not null constraint so right here where was it technology prereqs I said that technology cannot have the value of null so for example it's going through every row of prerequisite and it's going to modify this technology with its swapped value so it's going to be looking for what writing got swapped with so it's going to be looping through every every Row in prerequisites modifying the per exit column and it's going to run the sub query and it's going to say what is a swapped value of writing well I don't have a swapped value for writing because writing was not in my original list of Technologies so I don't think that that null error will happen in the real game but just in case I I need to fix it for my debugging code anyways and it shouldn't hurt anything in the real game but what I can do is just say wrap this with an if null I'll do it on both of these so that function it will run the first section of code and then you put a comma in a parenthesis and if the value that came out of that first section was null you can swap it with a different value so I'll just tell it to keep the original value that it had I have my parentheses wrong because I should have put the if null outside of this let me move this

okay hopefully that makes sense so the the query is returning a single value and if it's null then it'll get swapped out with this alternate value okay I'm gonna run this update statement and test to see if it works but I don't need to make a backup of the original values um because I can see right here what the original value should have been and I still have the backup of what things were swapped in my paint so I'll do the update

oh and then I put spaces on here after I'm done I want to remove the spaces so I'll just

so the trim command takes away spaces on the on the start or ending of a string this will fix the extra space that I added and that was necessary because it kind of depends on the specific database engine but sqlite since it kind of updates one row at a time then that's the issue where if I didn't add the space

um the next row when it tries to execute it could get confused on if it was one a value that was already modified or not okay so I'm going to remove the spaces and then let me just look at the new technology prereqs and see if they were swapped how we expect so originally writing was matched up with pottery and we wouldn't expect writing to get modified because we don't have that in our swap table at all our swap table has Pottery navigation and tactics so writing stayed in the same spot but Pottery got swapped with navigation so if we look in how it should have got swapped it says navigation and pottery are matched up so that looks good and then the next one shows military tactics and sailing so navigation was matched up with sailing so let's see what navigation was swapped with so navigation and military tactics so here we see military tactics and sailing and then the last one we had military tactics and Mathematics mathematics so both in the case of sailing mathematics and writing they all stayed the same because they were values that are not in our swap table because they were not in our original so that's just kind of bad data for testing in the real game that shouldn't be an issue but in this case the military tactics which was the one on the left um it got swapped with Pottery so let's just make sure

um yep so pottery and math and military tactics are right there so it looks like it's working and um I don't visually see a space on the front so that should be fine I feel like that is all the code I need to run this so let's copy into the actual game and see what happens so I don't need the test data at the top so where I created the fake tables and the inserts the game's already doing that for me but all my other code I need so I'm going to copy it Ctrl C I'm gonna come into my SQL and paste it now I need to think about how I want to run this because when we when we were testing in sqlite studio we could just execute any query when we wanted but in this case we have to use a trigger to activate our code so first of all the tables that I'm creating can be above the trigger so that they get created like before any records get inserted I want those tables to get created so because I'll get an exception if I try to use these tables and they don't exist yet and then we already decided that this technology quote trigger would be a good place to write all of our code and we had this where clause and this before insert to say basically only run this update statement one time uh because the first time the trigger executes the since it's a before insert there will be no rows and it's checking to see if there's an existing Row in this table and so because it'll only execute if there are no rows yet so it's only on the first time the trigger gets executed so I want to keep the same where clause for all of my code I wrote below so that all of the randomized code only happens one time but this update statement this was just for testing I was trying to figure out the sqlite version so I don't need this update I just need the wear but this word only append it to the right queries

so let's copy all of this code inside my trigger and I want to tab it in so that it looks like it's visually inside the trigger and I am going to paste this where clause on the bottom of all the queries so they each only execute one time

they'll only execute the very first time that the trigger executes but this create table is actually a little tricky I don't know what will happen if I put this where Clause here I think the first time the trigger executes it will try to create the table uh with all the data that comes from the Technologies table the second time because of the where Clause when it runs the select the where Clause will filter it to have zero results but I think it will still try to create the table even though it will have zero results in it but the problem is the table will have already existed so uh it will crash the second time the trigger executes because they'll try to create a table that already exists I think I'm not positive but just to be safe I'm going to move this create table out here if it runs before the trigger executes then the technology table is going to be empty so it's going to create this backup table but it's going to create it with no values so the first time the trigger runs I'm just going to insert the values that I need can copy the column names okay so it's basically the same as before it's just a two-step process now instead of one so when I added this where not exists um I pasted it to the wrong spot so an order Buy has to come at the very end of the query after the where and I accidentally put it before start the game open the tech tree okay this looks amazing so instantly we can tell it's all randomized because uh Pottery is not on the front anymore and just and the arrows all look good we don't have that bug that we had the first time we modified Pottery where the line got drawn over Pottery because it had its prerequisite set up wrong so it's looking good uh let's try to actually play the game for just a second and make sure it doesn't crash that I can actually research these things so well if I research satellites it should allow me to get tanks so right now I don't even have a city let me make a city and then if I choose which unit I want to build I can't build a tank and normally it's far into the game that you can get a tank because you've got to start from the ancient era first you're not going to jump all the way to the Future era but I'll try satellites so it has this number one and that tells it kind of which order it's gonna research Technologies in so if I scroll to the right and I pick something random you can see that the prerequisites are working correctly because I I picked construction and it shows as a three and the arrows come backwards and it knows that it needs to research these other things first so the prerequisites are working correctly but anyways let me just start with just satellites and then I'll just start skipping my turn it's going to force me to build something and force me to move my characters around skip the turn skip the turn I don't know how many it looks like it'd take about four turns before satellites will be done so I'll just keep moving my units around until four turns are up

okay so it says satellites is done perfect then I want to go to my town and I want to well first of all it lets me choose a new research so let's open up the tech tree satellites is obviously completed you can tell and so now I could start working on engineering or guidance systems so the texture seems to be working great but more importantly let me uh pick my city you can see right here the tank is available I'll switch to tank and you can see the tank shot up here so texture is working great even though it's randomized so I think it the code's working the only thing I did not randomize was the Civics tree so let's see if we can't do this just really quickly using the existing code we wrote so I'll quit out of the game and this is going to be cool and easy I'm just going to select all the code I wrote I'm going to open up Notepad paste it all in and I'm just going to look for the word Technologies and change it with civics

and then the word technology type change it with Civic type let me double check the column names and the table so create table civics okay yes it is Civic type specific type replace all let me make sure the table name Civic quote yep Civic quotes

let me make sure the term technology is not in here anywhere okay technology prereqs so that's probably going to be Civic prereq yep Civic prereq and search for the word technology anywhere else so this is either Civic or Civic type let's see just okay Civic and prereq Civic oh so instead of prereq Tech should be prereq Civic and then the last one technology

and that was going to be Civic type I believe I believe that's everything let me just search for the word Tech okay I didn't miss any so now that I've done that big fine replays so I'll just paste it all here at the end the Civic prereqs table I think I did a final replace for the wrong column name on accident so inside of Civic prereqs there are two columns prereq Civic and Civic so in my code let me look for anywhere that table is used so I said Civic type it should just be Civic and same thing here same thing here the prereq Civic looks correct I think it was just the Civic type and then same thing on here so we type specific type okay save that and restart the game started open the tech tree okay this looks perfect so it's completely randomized as before so the first thing that should show up is code of laws and yeah these are completely random order so working great that's it thanks for watching drop a like if you enjoyed And subscribe if you want to see other content like this and most importantly I need ideas for other games to hack so please in the comments below tell me what game you've been playing and what hack you think would be interesting for that game