C# Aurora Database

[littleWolf]

Maybe  use polar coordinates for all bodies?

Database contain 3 CONSTANT for every body (or 5, if need store starting angle Ao and time to):
ParentId - id for parent body (planet, star and etc)
R - radius of orbit (distance from parent body)
Va - angle speed (around parent body)

Calculate relative body position (from parent body) on any time t  with easy formula:

X=R*sin(Ao+Va*(t-to))
Y=R*cos(Ao+Va*(t-to))

sin and cos  can stored in precalculated tables.

Positions of all bodies can restored or prognozed on any time without database change.

[Steve Walmsley (OP)]

For orbital movement, I know the bearing of each body, the length of time since the last update and the length of time to orbit the parent body. So change in bearing is (Increment time / orbit time) / 360.

Then I set the new body position based on distance and bearing from parent.

I could calculate as needed, but it is used a lot so its easier to maintain it - especially now I am only saving to the DB occasionally instead of every time.

[littleWolf]

The positions of the celestial bodies in the game is deterministic and easily calculated.
Therefore, they do not need periodic stored in the database.
The calculation of the current position of the bodies at the static source data requires much less resources and time than finding and modifying records in the database.
The position of celestial bodies should be calculated only when needed (e.g. when displaying or calculating targeting and distances) and should not lead to costly write operations.

The polar coordinate system allows the use of three STATIC numbers to describe the motion of any heavenly body for any period of time without loss of accuracy of computation.
 Is the radius of the orbit, the angular velocity (or orbital period) and the start angle.
In contrast to the "current coordinates to the previous point-in-time" these values do not change throughout the game (unless of course will not put the Death Star)

As a programmer, I can advise to use class methods for the calculation of the current coordinates of celestial bodies available in the database of static data. Or use a simple proxy class for recalculation of coordinates (when necessary)

(sorry my bad english)

[db48x]

For orbital movement, I know the bearing of each body, the length of time since the last update and the length of time to orbit the parent body. So change in bearing is (Increment time / orbit time) / 360.

Sure. You could treat the bearing field as the bearing at time=0 (the beginning of the game), and then the current bearing is just (current time / orbit time) / 360. Same formula, fewer writes to the database. It also generalizes much better to elliptical orbits (which I think you've historically avoided due to complexity; fair enough). Comets have a similar formula, but for radius instead of bearing.

On the other hand, I think you're saying that the code currently just reads straight from the database, and this would complicate your life because you now would have to know the current time when you do so. That's fair too, but I think there are ways to minimize the impact. LittleWolf mentions a wrapper class, which would work. I think you could use a view instead, and not have modify your code much at all. The current time is presumably stored in the database somewhere, so a view that computes the current bearing from that timestamp would be easy enough. Then the existing code just selects from the view instead.

Either way I think it's safe to say that we're all salivating at the thought of the next release. Thanks!

[Ynglaur]

:words:

I remember a senior Java lead years ago telling me words to the effect, "Hit disk as infrequently as possible.  Disk time is more precious than CPU time in most applications."

[Steve Walmsley (OP)]

Whether I store the bearing or not is going to make very little difference. There is no writing to disk during play - only when you press the Save button. I am writing several fields in each system body record anyway, so one extra field on those records will affect overall save time by a few milliseconds. Orbital movement is extremely fast now so it won't make much difference there either.

I am calculating some information as I need it, such as colony cost, but with bearing it was just easier to maintain it than remember to calculate it.

BTW I'm not an expert programmer by any means. I am still learning C# as I go   The forerunner to Aurora, Starfire Assistant, was a project to teach myself VB.

[CaptObvious]

Whether I store the bearing or not is going to make very little difference.  There is no writing to disk during play - only when you press the Save button.  I am writing several fields in each system body record anyway, so one extra field on those records will affect overall save time by a few milliseconds.  Orbital movement is extremely fast now so it won't make much difference there either.

I am calculating some information as I need it, such as colony cost, but with bearing it was just easier to maintain it than remember to calculate it.

BTW I'm not an expert programmer by any means.  I am still learning C# as I go   The forerunner to Aurora, Starfire Assistant, was a project to teach myself VB.

Steve, have you thought about reading the sqlite database into a memory DB on load, then writing it back out to a file on save?  You could even implement autosaving without slowing the main game down by backing it up to a separate in-memory database when the save is triggered and writing that one out in a different thread.

There's a great Stack Overflow solution to that here: hxxp: stackoverflow. com/a/11385280

The comment about durability guarantees being lost doesn't matter in this case, as you'd be writing the DB out to disk every now and again.

[Youtoo]

You might want to try mysql.  This is free.  I am a dba as I said in a previous post.  My group managers oracle and mysql.  We get pretty good performance out of mysql, plus there are more performance tips for mysql. 

There are alot more posts for speeding up mysql.  It sounds like a save is 50,000 records or so? That isnt much if you do it right.

hxxp: jotschi. de/2014/02/03/high-performance-mysql-testdatabase/

[Senji]

Why you didn't use an in-memory DB ?
You'll make your executions more quickly and one save in file (and you can change the save at every save if you want ^^ )

If you already use it, sorry for the interruption ^^

[MagusXIX]

***EDIT** I read through everything (shame on me for not doing that first) and saw that this was covered already, at least a little. Sorry for asking twice. Disregard and carry on.

There is no writing to disk during play - only when you press the Save button.

Would it be too much to request an autosave option/feature? Perhaps every production cycle, or once a month? At least once a year, if possible, please.

I'm the sort of person who is going to forget to press the save button. Every. Time.

[littleWolf]

free save is EVIL..   play roguelike (ironmode)

[tenim]

sqlite is in fact the best solution for c# aurora

4 reasons

* sqlite is the fastest db available (yes, even faster than mysql)
* no need for installing a database server (for all other db-systems like mysql, postgres, oracle this is mandatory), only a dll is needed. 
* very easy sql dialect, recursive cte´s are possbile
* sqlite is public domain

i´m working >17 years with access, sqlite and ms-sql and sqlite is by far the easiest one.   the sql-syntax is very gentle and you can do things easy, which are not possible in ms-ssql or jet-sql
* i.  e.   you can "group by" a column which is in the select a aggregate with the "AS" keyword and there is no need to rewrite the complete aggreate, the alias is enough
* you can group by a single column and there is no need to enumerate all other columns from the select statement like ms-ssql it forces, because this is complete pointless -the result doesn´t
   canges after the first grouped column
* recursive cte´s.   it´s fantastic.   i had a sql-query which builds a tree of items and the longest runtime was appr.   30 seconds.   after the rewrite to recursive variation the runtime was 4 seconds!

tenim
 
 


 

[Bughunter]

Steve said above he is already using SQLite for the C# version. While I never worked with the embedded version of MySQL I imagine it would be similar, but there seems to be a catch to "free". Looking at the MySQL webpage I think Steve would have to either get a commercial licence from Oracle or release Aurora under GPL. This would be in line with how it works for the regular MySQL server. This also explains that SQLite seems to be a much more popular choice for hobbyist/indie development I guess.

[Sappersquid]

If the coordinate issue is strongly in doubt, I would strongly recommend not going polar.   Due to the vast scale difference, truncation and handling of floats at large distances gets to be a mess.   This is one reason KSP originally had to have such small system, and even then, issues with fluidity at high values still caused issues.   Quill18 has a good tutorial about procedural star system creation that really discusses this.   By using integer Cartesian you can avoid that.   You just do you gloat point trig and then cast final results back into integer.

[DuraniumCowboy]

On a hardware note, I just upgraded to a high end NVMe SSD drive.  Wow, it makes a difference for all the IO in the game.