Hoping for a solution to division problem?!

[Supremax67]

So here is the issue.

These works just fine...
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement*2;
-- or --
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/0.5;

But these don't work.
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement*0.5;
-- or --
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/2;

Oh and even this....
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement-1000;
... works. Although it breaks the game in a weird way. The game still accepts the settings

Anyone has any idea what is happening? I thought at first it was the game not accepting decimal values, but I tried to multiply with 1.01 and still no issues.

 

[LeeS]

Definition of the table from the base-game 01_GameplaySchema.sql file:

CREATE TABLE "GameSpeed_Turns" (
		"GameSpeedType" TEXT NOT NULL,
		"MonthIncrement" INTEGER NOT NULL,
		"TurnsPerIncrement" INTEGER NOT NULL,
		PRIMARY KEY(GameSpeedType, MonthIncrement, TurnsPerIncrement),
		FOREIGN KEY (GameSpeedType) REFERENCES GameSpeeds(GameSpeedType) ON DELETE CASCADE ON UPDATE CASCADE);

Both "MonthIncrement" and "TurnsPerIncrement" specify integer rather than real numbers.

The mathematics of dividing an integer by '.5' or multiplying the same original integer by a value of '2' results in the same integer result.

1 / .5 = 2
1 * 2 = 2

 

[Supremax67]

I am aware of the obvious here and only showed the division to show the system accepts divisions. Unfortunately your response doesn't explain why multiplying by 1.01 (as I stated in my original post) also works and so does subtractions.

The integer from the games value does not fall under fractions so a division by 2 should not break the variables as entering the same variables manually works just fine. But the mod I am designing is intended to work with other mods and adding the value manually would break the mod. It needs to be a division.

Here are the parameters the game won't accept in changing by division...
<Row>
<GameSpeedType>GAMESPEED_ONLINE</GameSpeedType>
<MonthIncrement>600</MonthIncrement>
<TurnsPerIncrement>30</TurnsPerIncrement>
</Row>
<Row>
<GameSpeedType>GAMESPEED_ONLINE</GameSpeedType>
<MonthIncrement>480</MonthIncrement>
<TurnsPerIncrement>20</TurnsPerIncrement>
</Row>
.....
<Row>
<GameSpeedType>GAMESPEED_ONLINE</GameSpeedType>
<MonthIncrement>24</MonthIncrement>
<TurnsPerIncrement>60</TurnsPerIncrement>
</Row>
<Row>
<GameSpeedType>GAMESPEED_ONLINE</GameSpeedType>
<MonthIncrement>12</MonthIncrement>
<TurnsPerIncrement>30</TurnsPerIncrement>


So a division by 2 wouldn't break the value as the numbers would become 300, 240 .... 12 and finally 6. All perfect integer and yes, this was tested using the requirement of only GAMESPEED_ONLINE, same problem. If i change those values manually, the game works just fine. But as I explained, changing the values manually would break the mod from working with other mods.

 

[isau]

Are you sure that is actually the problem you're having? I ran this test on the database using SQLite Studio and got these results (first column original value, second column val/2):

 

[Supremax67]

It is and pinpointed the source of the problem. I did a workaround which gave me the desired results.

The problem is the game doesn't update the table correctly if it's the same value as the division result of 960 and any values moving forward. So I was getting the problem with 480, 240, 120 etc when it was a division by 2. Similar logic was noticed when a division was done by 3.

However, I tricked it to give me 480 but not quite....
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/2-0.000000001

Now the value is 479.99999999 and the game keeps going on its merry way. This is a very interesting find as this equation allows a mod to adjust another mod MonthIncrement where some players couldn't easily do without modding themselves (entering the values normally overwrites the values, doesn't adjust them by a desired factor). I looked around, no modder is using this technique.

 

[Infixo]

But these don't work

Can you please describe what happens when you e.g. divide by 2? „Don’t work” doesn’t say anything about the problem.

 

[Beaust]

See @LeeS post of the schema, all 3 columns are PRIMARY KEY. This means each value must be unique does it not? So when Doing an update of just 2 rows as @Supermax has done in the original post it made them the same as other rows which can't happen. When doing the whole lot as @isau has done it works since all rows will be unique. That is also why MonthIncrement/2-0.0000000001 works because the result is not the same as the value in any other row.

Edit:- with the same reference to GameSpeedType.

 

[Infixo]

This means each value must be unique does it not?

No, it does not. It means that the combination of all 3 values must be unique within a table.

So when Doing an update of just 2 rows

He didn’t say anything about 2 rows in the first. He quoted a statement that works on the entire table and said „don’t work”. The same statement for @isau works. So, I am curious what doesn’t work.

 

[Supremax67]

When I stated it didn't work is because the game won't accept the settings and reverts to default settings. The only way I got it to work is with my trick mentioned above. However, I think I have a way of making my trick even better.
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/2-0.000000001
UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement+0.000000001

The problem only happens during the division process where any values that would end up a result of a division from 960 makes the game reject the changes (I tested my theory by ignoring changes to 480, 240, 120, etc and the game was accepting them, proving I was right). So I told myself what if I add a line that readjust for the subtraction? This should give me the exact desire results and since the numbers are moving upwards, nothing would break!

EDIT: Yep, the 2nd line works just fine. Now I get the value I was looking for with this workaround. It is not meant to be pretty, but it is works! AND, most importantly, can be used to stack on mods that is also changing the calendar.

 

[Infixo]

What you experience has nothing to do with division. It is just DB engine enforcing constraints, this whole situation is perfectly correct.
There is a row for GAMESPEED_ONLINE that has MonthIncrement=480 and TurnsPerIncrement=20. If you divide MI by 2, you will get 240. And so it happens that there is already a row that has MI=240 and TPI=20. Since they are part of the primary key, DB cannot allow for duplicates.

The safe way to perform any operations on primary keys is to store the table in a temporary one, delete all rows and recreate them from the temp table the way you like it. Then you can divide by 2 normally, without any tricks.

Also, just as a side note. When you divide by 2, you will end up with a row that has MonthIncrement=0. I am pretty sure that when the game will reach that point, it will either crash or the date will stop changing and the game will never end. Rare situation, only for Marathon after ca. 1350 turns.

 

[Supremax67]

There is a big difference between theories and actual game results...

The issue happens before trying to divide 480. The game bugs just trying to divide the 960 by 2, without trying any other changes! So your theory of the 480 being divided and seeing the same turn causing the game to glitch does not apply here as the other value of 480 does not have the same turnincrement.

Also, the game accepts multiple of the same value as I did monthincrement and turnincrement of 1 and 1 several in a row and the game didn't bug. What happened at the end of the test? The game kept going 1 turn at a time.

As for the issue you mentioned, I tested thoroughly and indeed the game doesn't accept below 1 so tested and solved with...
UPDATE GameSpeed_Turns SET MonthIncrement = 1 WHERE MonthIncrement < 1;

I was able to launch a Marathon game in the information ERA at x30, yeah that's right, the game at 45,000 turns and didn't crash. Nor was I able to get to the end as this required way too many turns just to find out, or for that matter any player would ever reach before civilization VII came out. So concern here is null.

 

[Infixo]

@Supremax67 SQL is a language that operates on sets. The order of the rows does not matter (to be precise: the order in which operations on specific rows in a database are performed is undetermined in SQL queries). Hence, you cannot say that some operation is performed before or after another.
Also, you cannot enter „multiple rows with 1 and 1”. After the first one, the next ones will throw an error. I suggest checking database.log more often, it will help.

 

[LeeS]

And open-ended updates that will affect multiple rows in a table are not implemented at all if any one effected row will create a unique constraint error as a result of the update. All the results of the open-ended update are first tested against the current values in the table for unique constraint violations before any rows are altered by the update command.

 

[Supremax67]

And open-ended updates that will affect multiple rows in a table are not implemented at all if any one effected row will create a unique constraint error as a result of the update. All the results of the open-ended update are first tested against the current values in the table for unique constraint violations before any rows are altered by the update command.

Open-ended? Keep in mind only started modding just last week and its not because I wanted too. The community was looking for an update for a popular mod that never got updated, authors running similar versions where no where to be found. So instead of being greedy and editing my own files, I figured why not.

So my level of knowledge here is lower than newbie and I have a terrible learning curve when it comes to reading material. Always been hands on. So the tables of SQL feels weird to me, how can I possibly understand them better? (On a side note, I got the desired results from my mod and currently adding more lines as I go along).

 

[LeeS]

By open-ended I mean an update structured like this

UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/2;

As opposed to one like this that has a restricting "WHERE" clause that would only allow it to be applied to one row within the table

UPDATE GameSpeed_Turns SET MonthIncrement = MonthIncrement/2 WHERE GameSpeedType='GAMESPEED_MARATHON' AND MonthIncrement=180 AND TurnsPerIncrement=100;

The first update is "open-ended" because it can affect multiple rows within the table (and in the specific example given will affect all rows in the table). The second example is "restricted" in comparison because the "WHERE" clause only allows the update to apply to the specific row within the table that matches for all three of the value-requirements given.

 

[isau]

If the error is a constraint fail or unique ID fail that should definitely be showing in Database.log. That is the first place you should look after making any update. You want a clean read out. If there are any errors whatsoever the mod is broken.

I can post an example of a clean read out when I get home.