In TS2012 you may notice a couple of files in your UserData directory. These are ASSETS.SQL and ASSETS.TDX. ASSETS.SQL is an SQLITE version 3 database file that is used by Trainz to map out asset dependencies that you have installed. This means that for every asset that you have in your installation, there will be an entry for it and all the dependencies for that asset. This is used by TADDaemon when Trainz is loading an asset into the game. Trainz will ask TADDaemon for an asset by it’s KUID value and TADDaemon will respond with the list of dependencies. There’s also an ASSETS.TDX file that is rebuilt when asset validation occurs but I haven’t found the relationship for that file in correlation with ASSETS.SQL. It appears to me at this point that ASSETS.TDX is a custom persistence mapping used by Trainz to maintain additional asset, like a fast look-aside list. When you repair the database using TrainzUtil or Content Manager ASSETS.TDX is reconstructed as part of that effort.
Since ASSETS.SQL is an SQLITE3 database, we can use any of the tools that come with SQLITE to analyze, backup or compact the database.
Warning: Working With the ASSETS.SQL file is not recommended unless you wish to learn more about how Trainz operates. You can render your Trainz installation unusable by doing something incorrectly. I strongly suggest that you backup your Trainz installation before even attempting any investigation on the Assets files in the UserData directory. I also won’t be responsible for any data loss that will occur if you experiment using what I show here.
For example, to compact the SQLITE3 Database you can do the following:
cd UserData
mkdir save
copy assets.sql save
echo .dump | sqlite3 assets.sql | sqlite3 new.sql
copy new.sql assets.sql
del new.sql
This compacts the ASSETS.SQL database file by dumping it and creating a new database from the dump. I releases space and rebuilds the indexes in the database file. On my system, doing this after a few weeks of installing and rebuilding the database released about 10MB of storage in the database file and sped up the transactions (as observed by the diagnostic database windows) on average of 5 to 10%.
You can get the SQLITE3 tools here: http://www.sqlite.org/download.html Look for the pre-compiled binaries for Windows.
Since we can investigate what’s in the SQLITE3 database it is possible to adjust the database indexes that are created and other parameters to improve the performance in the database in terms of inserting new asset dependencies or speed up query times.
If we look at ASSETS.SQL there are only two database tables defined: Dependencies and TADDatabaseVersion. TADDatabaseVersion contains only one row, with one attribute VERSION an INT = 2.
Dependencies is also fairly simple, it has three attributes: Parent a BIGINT, Dependency a BIGINT and DependencyBase another BIGINT. There are three indexes on Dependencies: DependenciesParentIndex on Parent, DependenciesDependencyIndex on Dependency and finally DependenciesDependencyBaseIndex on DependencyBase. So, three indexes with rudimentary coverage.
Indexes are used in database systems to improve access or lookup times based upon usage patterns. Surmising the three indexes used, there’s some trivial queries going on within Trainz, or specifically TADDaemon against ASSETS.SQL. We can do quick lookups based upon an AssetID from a parent perspective but there’s no index for correlation for example on a parent and it’s dependencies. What you also have to realize is that these are stored as BIGINT which has a possible value range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807). and is stored using 8 Bytes.
A KUID is packed into a BIGINT. Even the old Auran negative number KUIDs, i.e., -10:200 are stored as well but curiously not as a Parent but only as a Dependency. So it would appear that N3V is not mapping Builtin Assets using this mechanism.
Since we know a KUID has a structure we can deduce how these BIGINT fields are packed but I won’t go into that in this blog posting. I only wanted to point out what these to files are or appear to be. From an Engineering perspective it’s encouraging to See N3V actually leverage an off the shelf framework like SQLITE3 but it’s also discouraging to see them still hold on to proprietary data formats like the .TDX and Jet Archives (.JA) files. Maybe we’ll see over time some additional responsibilities put onto ASSETS.TDX but right now it looks like it’s only used for fairly low level rudimentary support, Asset Dependency relationships and nothing else. So Kudos to N3V for leveraging an Open Source Database, it’s a good start..
Errata: SQLITE Analysis of ASSETS.SQL after compaction on my system:
Page size in bytes……………….. 1024
Pages in the whole file (measured)…. 27115
Pages in the whole file (calculated).. 27115
Pages that store data…………….. 27115 100.0%
Pages on the freelist (per header)…. 0 0.0%
Pages on the freelist (calculated)…. 0 0.0%
Pages of auto-vacuum overhead……… 0 0.0%
Number of tables in the database…… 3
Number of indices………………… 3
Number of named indices…………… 3
Automatically generated indices……. 0
Size of the file in bytes…………. 27765760
Bytes of user payload stored………. 8786471 31.6%
*** Page counts for all tables with their indices ********************
DEPENDENCIES…………………….. 27113 99.993%
TADDATABASEVERSION……………….. 1 0.004%
SQLITE_MASTER……………………. 1 0.004%
*** All tables and indices *******************************************
Percentage of total database………. 100.0%
Number of entries………………… 1308102
Bytes of storage consumed…………. 27765760
Bytes of payload…………………. 22053176 79.4%
Average payload per entry…………. 16.86
Average unused bytes per entry…….. 0.34
Average fanout…………………… 99.00
Fragmentation……………………. 4.8%
Maximum payload per entry…………. 136
Entries that use overflow…………. 0 0.0%
Index pages used…………………. 110
Primary pages used……………….. 27005
Overflow pages used………………. 0
Total pages used…………………. 27115
Unused bytes on index pages……….. 14481 12.9%
Unused bytes on primary pages……… 430209 1.6%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 444690 1.6%
*** All tables *******************************************************
Percentage of total database………. 40.3%
Number of entries………………… 327030
Bytes of storage consumed…………. 11194368
Bytes of payload…………………. 8787057 78.5%
Average payload per entry…………. 26.87
Average unused bytes per entry…….. 0.85
Average fanout…………………… 99.00
Fragmentation……………………. 3.0%
Maximum payload per entry…………. 136
Entries that use overflow…………. 0 0.0%
Index pages used…………………. 110
Primary pages used……………….. 10822
Overflow pages used………………. 0
Total pages used…………………. 10932
Unused bytes on index pages……….. 14481 12.9%
Unused bytes on primary pages……… 262336 2.4%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 276817 2.5%
*** All indices ******************************************************
Percentage of total database………. 59.7%
Number of entries………………… 981072
Bytes of storage consumed…………. 16571392
Bytes of payload…………………. 13266119 80.1%
Average payload per entry…………. 13.52
Average unused bytes per entry…….. 0.17
Fragmentation……………………. 6.0%
Maximum payload per entry…………. 14
Entries that use overflow…………. 0 0.0%
Primary pages used……………….. 16183
Overflow pages used………………. 0
Total pages used…………………. 16183
Unused bytes on primary pages……… 167873 1.0%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 167873 1.0%
*** Table DEPENDENCIES and all its indices ***************************
Percentage of total database………. 99.993%
Number of entries………………… 1308096
Bytes of storage consumed…………. 27763712
Bytes of payload…………………. 22052587 79.4%
Average payload per entry…………. 16.86
Average unused bytes per entry…….. 0.34
Average fanout…………………… 99.00
Fragmentation……………………. 4.8%
Maximum payload per entry…………. 28
Entries that use overflow…………. 0 0.0%
Index pages used…………………. 110
Primary pages used……………….. 27003
Overflow pages used………………. 0
Total pages used…………………. 27113
Unused bytes on index pages……….. 14481 12.9%
Unused bytes on primary pages……… 428891 1.6%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 443372 1.6%
*** Table DEPENDENCIES w/o any indices *******************************
Percentage of total database………. 40.3%
Number of entries………………… 327024
Bytes of storage consumed…………. 11192320
Bytes of payload…………………. 8786468 78.5%
Average payload per entry…………. 26.87
Average unused bytes per entry…….. 0.84
Average fanout…………………… 99.00
Fragmentation……………………. 3.0%
Maximum payload per entry…………. 28
Entries that use overflow…………. 0 0.0%
Index pages used…………………. 110
Primary pages used……………….. 10820
Overflow pages used………………. 0
Total pages used…………………. 10930
Unused bytes on index pages……….. 14481 12.9%
Unused bytes on primary pages……… 261018 2.4%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 275499 2.5%
*** Indices of table DEPENDENCIES ************************************
Percentage of total database………. 59.7%
Number of entries………………… 981072
Bytes of storage consumed…………. 16571392
Bytes of payload…………………. 13266119 80.1%
Average payload per entry…………. 13.52
Average unused bytes per entry…….. 0.17
Fragmentation……………………. 6.0%
Maximum payload per entry…………. 14
Entries that use overflow…………. 0 0.0%
Primary pages used……………….. 16183
Overflow pages used………………. 0
Total pages used…………………. 16183
Unused bytes on primary pages……… 167873 1.0%
Unused bytes on overflow pages…….. 0
Unused bytes on all pages…………. 167873 1.0%