Parsing Decimal Separators

[Steve Walmsley (OP)]

I've run into an issue in my decimal separator quest.

I have a function that detects whether the decimal separator is a comma or period in the number I am checking. It can tell the difference between 0.1 and 0,1 and parse them correctly. It can also handle 1,000.01 and 1.000,01 correctly.

Unfortunately, when there is no decimal specified, it struggles, 86,000 is parsed as 86, because the function can't decide if that is 86,000 with a period separator or 86 with a comma separator.

I could check the decimal separator of the PC on which the code is running, but if the player uses the opposite the problem still remains. Besides, the database uses periods so I don't want to have to change input and output of every field based on local culture as I will probably be adding more bugs that I am fixing. The 'detect on input' option would have been a lot easier if I could get it to work.

So at this point, I am going to have to stick with period separators only and players will have to continue changing it when running Aurora.

[Froggiest1982]

I've run into an issue in my decimal separator quest.

I have a function that detects whether the decimal separator is a comma or period in the number I am checking. It can tell the difference between 0.1 and 0,1 and parse them correctly. It can also handle 1,000.01 and 1.000,01 correctly.

Unfortunately, when there is no decimal specified, it struggles, 86,000 is parsed as 86, because the function can't decide if that is 86,000 with a period separator or 86 with a comma separator.

I could check the decimal separator of the PC on which the code is running, but if the player uses the opposite the problem still remains. Besides, the database uses periods so I don't want to have to change input and output of every field based on local culture as I will probably be adding more bugs that I am fixing. The 'detect on input' option would have been a lot easier if I could get it to work.

So at this point, I am going to have to stick with period separators only and players will have to continue changing it when running Aurora.

Hi Steve, your effort is admirable however I still don't understand: beside Aurora, what is the problem in having a comma or dot as decimal separator for the average user? I mean when I was in Italy I did switch it for VB6 noticing absolutely nothing different in my day to day operations. Now I am NZ so it is pretty much not an issue.

Same goes with the date format change: again I am not noticing anything different.

I am asking because frankly I don't understand the problem some people have in changing these 2 things.

I would like to understand better as I am sure there is something I am missing and this will help me in comprehending the issue better.

[Garfunkel]

It's a thing most people never have to fiddle with in their computer so it's a common issue for new players.

Then some few people might be doing statistics or accounting or math or engineering on their PC in which case using the wrong symbol might have serious consequences.

[Nori]

I think the biggest issue is no matter how much you tell people, some will still run into the issue because they don't read.

Steve, could you simply detect if a person is using the comma and warn them to change on startup?

[Steve Walmsley (OP)]

I think the biggest issue is no matter how much you tell people, some will still run into the issue because they don't read.

Steve, could you simply detect if a person is using the comma and warn them to change on startup?

Yes, that is a very good idea.

[Demonius]

Tbh i have no identifiable Problems with it on my win10 System. I wonder if most of those Problem reports come form the old obsolete Win7 users you keep seeing here.

[Droll]

I think the biggest issue is no matter how much you tell people, some will still run into the issue because they don't read.

Steve, could you simply detect if a person is using the comma and warn them to change on startup?

This is a good idea definitely but for the future never assume that the average player is going to be reading the forums with any regularity for them to be aware of certain issues that you or I may know.

[amram]

I've run into an issue in my decimal separator quest.

I have a function that detects whether the decimal separator is a comma or period in the number I am checking. It can tell the difference between 0.1 and 0,1 and parse them correctly. It can also handle 1,000.01 and 1.000,01 correctly.

Unfortunately, when there is no decimal specified, it struggles, 86,000 is parsed as 86, because the function can't decide if that is 86,000 with a period separator or 86 with a comma separator.

I could check the decimal separator of the PC on which the code is running, but if the player uses the opposite the problem still remains. Besides, the database uses periods so I don't want to have to change input and output of every field based on local culture as I will probably be adding more bugs that I am fixing. The 'detect on input' option would have been a lot easier if I could get it to work.

So at this point, I am going to have to stick with period separators only and players will have to continue changing it when running Aurora.

If you can uniquely identify a machine, you can know if the current database has been loaded previously on this machine, so you can know when its first run and just ask the user which way they want it.

perhaps: https://www.nextofwindows.com/the-best-way-to-uniquely-identify-a-windows-machine

Maybe one tiny little table in the database, machine_id, group_separator, decimal_separator

So when you post an update, the game can check that machine ID, recognise that it is being opened for the first time on a new machine and pops a window, user sets it.

That would avoid issues with asking the OS for localisation and the user not matching OS expectation or moving a database to another machine where the localisation is the other way around and not changing how they enter data.

It also obviates the issue of not being able to tell 86,000 from 86.000 without first knowing if its a decimal or group separator, since the user tells you which it is now.

[Froggiest1982]

It's a thing most people never have to fiddle with in their computer so it's a common issue for new players.

Then some few people might be doing statistics or accounting or math or engineering on their PC in which case using the wrong symbol might have serious consequences.

I do that but mostly and my programs runs their own decimal factor and it's not influenced by windows. Same goes for excel even if I use libre office, but again not an issue.

I do agree it's some extra step you dont need to take with other games/program so could make installation user friendly.

I believe the solution of the popup being a great idea. Simple yet effective!

[SpikeTheHobbitMage]

I've run into an issue in my decimal separator quest.

I have a function that detects whether the decimal separator is a comma or period in the number I am checking. It can tell the difference between 0.1 and 0,1 and parse them correctly. It can also handle 1,000.01 and 1.000,01 correctly.

Unfortunately, when there is no decimal specified, it struggles, 86,000 is parsed as 86, because the function can't decide if that is 86,000 with a period separator or 86 with a comma separator.

I could check the decimal separator of the PC on which the code is running, but if the player uses the opposite the problem still remains. Besides, the database uses periods so I don't want to have to change input and output of every field based on local culture as I will probably be adding more bugs that I am fixing. The 'detect on input' option would have been a lot easier if I could get it to work.

So at this point, I am going to have to stick with period separators only and players will have to continue changing it when running Aurora.

For the UI just use C#'s built in conversion functions instead of rolling your own.

Code: [Select]

float.Parse("1.200,00", System.Globalization.CultureInfo.CurrentCulture.NumberFormat);

For the database, use prepared queries and sqlite will handle the *format conversions itself.  This has the added benefit of being much faster.

Code: [Select]

using (SQLiteTransaction trans = connection.BeginTransaction()) {
  using (SQLiteCommand insertTable1 = new SQLiteCommand("INSERT INTO table (foo, bar, bin, baz) VALUES (?, ?, ?, ?);", connection) {
    SQliteParameter foo = new SQLiteParameter();
    SQliteParameter bar = new SQLiteParameter();
    SQliteParameter bin = new SQLiteParameter();
    SQliteParameter baz = new SQLiteParameter();

    insertTable1.Parameters.add(foo);
    insertTable1.Parameters.add(bar);
    insertTable1.Parameters.add(bin);
    insertTable1.Parameters.add(baz);

    for (n=0; n < limit; n++) {
      foo = data[n].foo;   // 500
      bar = data[n].bar;   // 3.2
      bin = data[n].bin;   // "SQLite will escape this string itself"
      baz = data[n].baz;   // null
      insertTable1.ExecuteNonQuery();
    }
  }

  using (SQLiteCommand insertTable2 = new SQLiteCommand("INSERT INTO table2 (foo, bar, bin, baz) VALUES (?, ?, ?, ?);", connection) {
  }

  trans.Commit();
}
For best performance wrap the entire save operation in a single transaction instead of a transaction for each table, and then wrap the transaction in a try block.  This should also help with the missing troops and missing fleets bugs.

*Edit for clarity: I mean conversion between C# data types and sqlite's on-disk formats, not between UI text and disk.
Edit again to show multiple queries in the same commit.

[db48x]

Besides, the database uses periods so I don't want to have to change input and output of every field based on local culture as I will probably be adding more bugs that I am fixing.

You know best, of course, but is this really true? I've looked through the database and not seen any fields where a string is used to hold a number. Nor have I noticed any place where you've inserted strings into fields that are declared as numeric (sqlite doesn't raise an error when that happens, it just stores the string instead of a number). This means that the data in the database is the same regardless of the user's chosen representation, which is a good thing. As long as you format that number into a string using the user's culture before you put it in the textbox, and parse it according to the user's culture when you take it back out, everything should work. There's even a type of text box that you can specify as only accepting input that matches a format string, which will reject everything else for you. This alone would eliminate a lot of potential errors.

[Caplin]

Is there a way we could confirm whether this is true or not? I'm not all that up on SQL, but presumably the schema would provide info on how the data is stored. (If this is what you've looked at already, please feel free to ignore.)

[Doren]

I think right now Aurora is already doing fine with the decimal separator. I think only place I noticed that there were issues was mineral search window since it has pre-inserted input.

Best practice would be to use numbers in DB as mentioned previously as these are culture agnostic and DB wouldn't even care what separator user uses.
So when ever you are getting a input just assume a float or double and allow dotNet to cast the string normally into a number. This way all the conversions happens with users settings and with numbers there's no decimal separators and you do not have to deal with them.

Same with putting data to display: use dotNet to string methods and if you want to cut amount of numbers after decimal point provide a formatting definition. In formatting definition a decimal symbol is "." and it will print the string with the actual user culture separator.

Here's some snippets from a project of mine:

Code: [Select]

private string formatString = "+0.00#;-0.00#;0";
private string formatShortString = "+0.###;-0.###;0";
private string formatShortStringNoPlus = "0.###;-0.###;0";

row.Cells[ColumnMaxHitpoints].Value = apparel.MaxHitpoints.ToString(formatShortStringNoPlus);
row.Cells[ColumnInsulationHeatMultiplier].Value = (apparel.InsulationHeatMultiplier * 100).ToString(formatShortStringNoPlus) + "%";
row.Cells[ColumnInsulationHeatdStatic].Value = apparel.InsulationHeat.ToString(formatShortString) + "C";

If you do not want this level of manipulation there's also some pre-defined formats like "Currency" and "Number" but there you are at quite a lot of mercy of OS so I'd suggest staying away of those as only places where these normally are used are text and excel type software and they allow user to modify the display format.

All in all I'd suggest that deal with numbers in code and DB and format them as strings to display then you do not have to deal with decimal separators nor thousand separators the OS is taking care of that

[AlitarSemiramis]

I'm a little lost on the exact problem here. Ideally:

- Whenever Aurora is reading a string from the UI to transform it into a number in memory, it should use the culture of the computer where the software is running.
- Whenever Aurora is printing a number in memory to the UI, it prints it using the culture of the computer where it is running.

If the DB is storing some numbers as strings with a particular separator, then writing or reading those strings from the DB to a number in memory should be done with whatever culture the DB is expecting. This means that Aurora may need two different code paths to handle transforming between strings and numbers if the DB culture and the user culture are different.

The only problem would be if the DB has a number stored as a string, and then that goes directly to the UI without being transformed to a number in memory, or viceversa. Those cases would need to be changed so there's a middle transformation to a number so the correct culture is applied.

[SpikeTheHobbitMage]

The database decimal point problem is entirely due to using string substitution instead of prepared statements.  This is also why we can't use single quotes in names.  VB Aurora did the same thing, though in fairness I don't recall if Access even supported prepared statements or not. 

It is a pity that C# doesn't seem to support Python prepared statement semantics.  For comparison this is the Python equivalent of my previous example:

Code: [Select]

with apsw.Connection('database.sqlite') as db:
    db.cursor().executemany('INSERT INTO table1 (foo, bar, bin, baz) VALUES (?, ?, ?, ?);',
        ((row.foo, row.bar, row.bin, row.baz) for row in data1))
    db.cursor().executemany('INSERT INTO table2 (foo, bar, bin, baz) VALUES (?, ?, ?, ?);',
        ((row.foo, row.bar, row.bin, row.baz) for row in data2))