DB<->TSV converter

[taw]

Hi,

I just wrote a converter from DB to TSV.
Or more like extracted it from my db schema analysis code.
You can download it from etwng as usual.

You'll need [J]Ruby and Nokogiri (jgem install nokogiri --pre).

It reads schemas from DB.xsd identically to DbEditor, or at least it's supposed to do so.
That is - everything DbEditor supports, converter supports, and vice versa.

It already supports quite a few NTW tables, and of course all but two ETW tables.

Incidentally it might already be the easiest way to look at NTW db files,
unless someone fixed DbEditor for NTW while I was away, so NTW modders rejoyce!

EDIT: Conversion now works perfectly both ways, with byte-for-byte perfect round trips for all known ETW tables
(except two impossible ones which are not converted at all).

EDIT: It can now convert both single files and whole db/ tree, saving one tsv per db file.

It works on unpacked db tree, it doesn't look inside any .packs. You have to unpack it yourself first. It might be for the best for now.

Format of TSV is:

Metadata line with version number
Header line with names and types of fields (for your use only, converter rereads this data from DB.xsd)
Actual data in tab-separated format.

There's special support for empty db files (see example below), mostly to simplify for mass conversions.

TSV is very strict, all strings must be double quoted and properly escaped etc.
If this is a major problem, I can probably tweak it somewhat.

Feedback very much welcome.

Examples as of now:

samples/db/abilities_tables/abilities
version 1
Agent_Ability_ID(string) Associated_Attribute(optstring) Type(string) Unknown(boolean)
"can_assassinate" "subterfuge" "action" true
"can_attack_land" "command_land" "attack" true
"can_attack_naval" "command_sea" "attack" true
"can_build_fort" "command_land" "normal" true
"can_build_religious" "zeal" "normal" true
"can_convert" "zeal" "normal" false
"can_duel" "duelling_pistols" "action" true
"can_exchange" nil "exchange" true
"Can_Hide_In_Woods" "command_land" "normal" false
"can_receive_duel" "duelling_pistols" "action" true
"can_research" "research" "normal" false
"can_sabotage" "subterfuge" "action" true
"can_sabotage_army" "subterfuge" "action" true
"can_spy" "subterfuge" "action" true

samples/db/agent_spawning_to_policies_tables/agent_spawning_to_policies
version 1, empty, no schema

samples/db/battle_cities_tables/battle_cities
version 1
Culture(string) unknown1(float) unknown2(float) unknown3(float) unknown4(float) unknown5(float) unknown6(float) unknown7(int) unknown8(int)
"European" 0.75 1.25 2100.0 3000.0 500.0 1500.0 400 900
"American" 0.75 1.25 2100.0 3000.0 500.0 1500.0 400 900
"Native" 1.0 1.0 2000.0 2000.0 600.0 600.0 15 15
"Eastern" 0.75 1.25 2100.0 3000.0 500.0 1500.0 400 900

samples/db/wind_levels_tables/wind_levels
version 1
Wind_IDRef(string) Level(string) Westerly_x0020_intensity(float) Northern_x0020_intensity(float) unknown5(int)
"calm_westerly" "wind_level_0" 3.0 0.0 1
"gale_northern" "wind_level_3" 0.0 12.0 8
"light_northern" "wind_level_0" 0.0 3.0 5
"light_westerly" "wind_level_1" 6.0 0.0 2
"moderate_northern" "wind_level_1" 0.0 6.0 6
"moderate_westerly" "wind_level_2" 9.0 0.0 0
"storm_northern" "wind_level_4" 0.0 16.0 9
"storm_westerly" "wind_level_4" 16.0 0.0 4
"strong_northern" "wind_level_2" 0.0 9.0 7
"strong_westerly" "wind_level_3" 12.0 0.0 3

samples/db/pdlc_tables/pdlc
version 4
unknown1(string) unknown2(int) unknown3(optstring) unknown4(int)
"america_elite_units" 10607 "A brand new pack featuring 15 elite units from the American War of Independence. Each comes with their own unique combination of statistics and abilities, and can be used in both single and multiplayer games." 3
"campaign_multiplayer_beta" 10605 "Challenge a friend or enemy to the ultimate multiplayer strategic experience, with the free BETA trial of the 1v1 Multiplayer Campaign for Empire: Total War." 999
"european_elite_units" 10604 "The Elite Units of the West introduces 14 new units for the Western factions. All new infantry and cavalry, with the best weapons and training bring increased battlefield options." 1
"natives" 10606 "An all new Warpath campaign, featuring five new American Indian playable factions, new tribal unit types and technologies!" 2
"east_elite_units" 10608 "A brand new pack featuring 12 elite units for the Ottoman and Indian factions. Each comes with their own unique combination of statistics and abilities, and can be used in both single and multiplayer games." 4

[husserlTW]

There are many tables where the "date" bug if float values is appeared. I think we should submit it to OpenOffice. Are there any details we should mention except the general problem? Also Tomasz is it possible to get as an output only the TSV's in a folder without any sub-folders or db files?

[taw]

There are many tables where the "date" bug if float values is appeared. I think we should submit it to OpenOffice. Are there any details we should mention except the general problem?

Mostly that end results disagrees with preview in OpenOffice, and with Excel, so it's a bug, not any silly feature.

Also Tomasz is it possible to get as an output only the TSV's in a folder without any sub-folders or db files?

Converter reads schema from last subdirectory name on both directions of conversion, and you can have multiple files per last subdirectory (even if vanilla is mostly X_tables/X), so we cannot guess it from file name.

And after conversion db/ files need to be places in such directory structure anyway, so it seemed sensible to just keep it all the way.

Is it a huge problem?

Logic of directory structure and schema guessing is 2 lines of db_unpack/db_unpack:

table_name = file_name.dirname.basename.to_s
current_target = Pathname((target+file_name.relative_path_from(source)).to_s.sub(/\.tsv\z/, ""))

table_name = file_name.dirname.basename.to_s
current_target = Pathname((target+file_name.relative_path_from(source)).to_s + ".tsv")

It's almost the simplest solution possible.

What else did you have in mind?

[husserlTW]

For those that experienced the problem with dates in OpenOffice calc, here is the solution I received from OO :

Your spreadsheets use comma as decimal separator: 3,1459 rather than 3.1459
Option 1: In the import dialog select the decimal column(s) and choose "English(US) as data type.
Option 2: Set an English application locale (option #2 in Tools>Options>LanguageSettings>Languages...). Then do the import with standard settings. When you got correct data you can switch back.
Option 3: In version 3.3 you can specify the assumed locale for the entire import on the import dialog. http://libreoffice.org has been released today. The latest 3.3 release candidate from download.openoffice.org is very stable as well.

Hey Tomasz, is possible to change on floats comma to period?

[taw]

For those that experienced the problem with dates in OpenOffice calc, here is the solution I received from OO :

Hey Tomasz, is possible to change on floats comma to period?

Oh god, this workaround sounds dreadful.

Is the problem gone in 3.3?

EDIT: I assume you meant "changing period to comma", converter already produces standard decimal period like "1.2".
It drops .0 now as well, to reduce this problem.

[husserlTW]

3.3 is in RC stage so I will wait for that. For the moment I work work with 3.2 but I have to change language to US when I work with TSV and then back to Greek when finish. Unless you make the change "period to comma" (you assumed right). Do you use US English?

[taw]

3.3 is in RC stage so I will wait for that. For the moment I work work with 3.2 but I have to change language to US when I work with TSV and then back to Greek when finish. Unless you make the change "period to comma" (you assumed right). Do you use US English?

Yeah, I'm in UK but I use US English locale for everything,
too many programs do stupid things like OpenOffice here on less usual localizations.

If this problem is going to go away anyway if we wait a bit, let's just wait a bit.
I have no idea what else will break if we start messing with number formatting like that.

There's no way to mark .tsv file as being in US locale, is there?

[husserlTW]

I can only change locale language for OpenOffice but then I have to change it back to my language every time, as I suppose must do every other European who does not use as locale US english. Thinking that this does not happen with PFM exported TSVs I tent to believe that a move from you is needed to be done for a permanent solution...

[taw]

I can only change locale language for OpenOffice but then I have to change it back to my language every time, as I suppose must do every other European who does not use as locale US english. Thinking that this does not happen with PFM exported TSVs I tent to believe that a move from you is needed to be done for a permanent solution...

Could you send me a sample from PFM exported TSV which doesn't have this problem?

[husserlTW]

Here take this small one.

[taw]

Here take this small one.

I see, so they essentially find out user's locale somehow, and format based on that.

[taw]

db<->tsv converter now accepts --comma option to produce floating point numbers with comma (3,14).

People who use English locale and other locale with decimal dot (3.14) don't need to do anything.

People on comma locales should use db converter like this:
./db_unpack --comma db/ tsvs/
./db_pack --comma tsvs/ db/

(actually conversion back to db autodetects format regardless of --comma argument,
but feel free to pass --comma option anyway if you want)

[kungfuserge]

You'll need [J]Ruby and Nokogiri (jgem install nokogiri --pre).

and may I ask how do you install this ? I have jruby-1.5.3 on C:

[taw]

and may I ask how do you install this ? I have jruby-1.5.3 on C:

If you've installed jruby already, open cmd.exe (Start Menu > Run > cmd)

Verify jruby works with command: jruby --version

If so, install nokogiri with command: jgem install nokogiri --pre

There's download link on etwng repository - unpack this zip somewhere, and run it from command line
the way described in README.txt

[kungfuserge]

Great ! it worked



I want to convert the building_models & the naval_models folders into TSV
do I have to extract your taw-etwng-8b0fd79 inside Rubys folder (root) ?
using cmd go to db and run command ?

/db_unpack db/ converted/
./db_unpack db/foo_tables/foo converted/foo_tables/foo.tsv

which one of them ?

[taw]

Great ! it worked



I want to convert the building_models & the naval_models folders into TSV
do I have to extract your taw-etwng-8b0fd79 inside Rubys folder (root) ?
using cmd go to db and run command ?
which one of them ?

Ouch, these two tables cannot be represented as tsv and don't currently
have any tool support.

db<->tsv converted doesn't allow you to do anything which you couldn't
do with DbEditor and a lot of copy&pasting to Excel. It supports exactly
the same tables (all of them except these two), using exactly the same DB.xsd.

I might write xml converter for building_models someday, as we know its structure
(it's hierarchical nested data - you cannot use tsv for that).

naval_models table doesn't even have that much.

But in case you want tsv for anything else, unpack *.pack you want to mod,
and then run:

jruby ./db_unpack path/to/this/db/ path/where/you/want/your/tsv/

[husserlTW]

Hey Tomasz, I cannot convert changed TSVs back 2 db. If I don't make any changes conversion is done o.k. Here is the error message:

[taw]

Hey Tomasz, I cannot convert changed TSVs back 2 db. If I don't make any changes conversion is done o.k. Here is the error message:

Yes, db_pack is currently way too strict in what it accepts.
Could you send me the tsv that you get after changes?

[taw]

Hey Tomasz, I cannot convert changed TSVs back 2 db. If I don't make any changes conversion is done o.k. Here is the error message:

It's actually more failful than I thought. To distinguish nil from empty string, converter uses quotes only for string values, not for anything else.

So: nil means nil (00), while "" would be (01 00 00), "nil" would be (01 03 00 6e 69 6c) etc.

This is somewhat abusive of the tsv standard, and many programs will hate me for doing this.

What would be the most sensible way to encode extra value as string, and also deal with escaping special characters?

I could quote twice, or add extra column for optional string, or pick some special nil marker unlikely to happen in real data (ouch, I hate this kind of crap). TSV's lack of metadata is fail :-/

[taw]

I added models_buildings_table packer and unpacker to etwng.

Usage:

jruby models_buildings_unpack db/models_building_tables/models_building tmp/models_building.txt
jruby models_buildings_pack tmp/models_building.txt db/models_building_tables/models_building_new

File format is Ruby object notation just like with groupformations and many of my other mini-converters,
as I was too lazy to do anything more fancy.

Some examples:

  [
    "american_ruin01",
    "buildings\\american_ruin01\\american_ruin01_tech.cs2.parsed",
    1,
    ["EFline_piece01_destruct01_line01", 0, [-7.886, 0.0, 11.316], [9.267, 0.0, 11.316], [0.0, 0.0, 1.0]],
    ["EFline_piece01_destruct01_line02", 0, [9.468, 0.0, -11.022], [-1.883, 0.0, -11.022], [0.0, 0.0, -1.0]],
    ["EFline_piece01_destruct01_line03", 0, [8.735, 0.0, 10.265], [8.735, 0.0, 7.238], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line04", 3, [-7.263, 0.0, -1.867], [-7.263, 0.0, -11.453], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line05", 3, [-5.717, 0.0, -1.867], [-5.717, 0.0, -11.453], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line06", 3, [-4.073, 0.0, -1.867], [-4.073, 0.0, -11.453], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line07", 3, [-7.266, 0.0, 10.646], [-7.266, 0.0, 1.817], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line08", 3, [-5.666, 0.0, 7.449], [-5.666, 0.0, 1.773], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line09", 3, [-4.216, 0.0, 7.449], [-4.216, 0.0, 1.773], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line10", 3, [-2.399, 0.0, 9.462], [7.691, 0.0, 9.462], [0.0, 0.0, 1.0]],
    ["EFline_piece01_destruct01_line11", 3, [-2.399, 0.0, 7.826], [7.691, 0.0, 7.826], [0.0, 0.0, 1.0]],
    ["EFline_piece01_destruct01_line12", 3, [-2.399, 0.0, 6.134], [7.691, 0.0, 6.134], [0.0, 0.0, 1.0]],
    ["EFline_piece01_destruct01_line13", 3, [7.691, 0.0, 4.708], [-2.399, 0.0, 4.708], [0.0, 0.0, -1.0]],
    ["EFline_piece01_destruct01_line14", 3, [7.691, 0.0, 3.069], [-2.399, 0.0, 3.069], [0.0, 0.0, -1.0]],
    ["EFline_piece01_destruct01_line15", 3, [-2.532, 0.0, -1.931], [-2.532, 0.0, -9.499], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line16", 3, [-0.891, 0.0, -1.9], [-0.891, 0.0, -9.468], [1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line17", 3, [9.039, 0.0, -9.468], [9.039, 0.0, -1.9], [-1.0, 0.0, 0.0]],
    ["EFline_piece01_destruct01_line18", 3, [7.657, 0.0, -9.468], [7.657, 0.0, -1.9], [-1.0, 0.0, 0.0]],
  ],
  [
    "american_rural01",
    "buildings\\american_rural01\\american_rural01_tech.cs2.parsed",
    1,
  ],
  [
    "western_fort_armoury_v2",
    "buildings\\western_fort_armoury_v2\\western_fort_armoury_v2_tech.cs2.parsed",
    0,
    ["EFLine_piece01_destruct01_line01", 2, [-11.145, 3.189, 3.467], [-9.605, 3.189, 3.467], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line02", 2, [-8.135, 3.189, 3.467], [-6.555, 3.189, 3.467], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line03", 2, [-5.14, 3.189, 3.467], [-3.6, 3.189, 3.467], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line04", 2, [-2.109, 3.189, 3.467], [-0.569, 3.189, 3.467], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line05", 2, [-9.606, 3.189, -3.517], [-11.146, 3.189, -3.517], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line06", 2, [-6.575, 3.189, -3.517], [-8.115, 3.189, -3.517], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line07", 2, [-3.599, 3.189, -3.517], [-5.139, 3.189, -3.517], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line08", 2, [-0.569, 3.189, -3.517], [-2.109, 3.189, -3.517], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line09", 2, [-11.243, 3.189, -2.917], [-11.243, 3.189, -1.908], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line10", 2, [-11.231, 3.189, 2.226], [-11.231, 3.189, 3.235], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line14", 2, [-11.164, 0.19, 1.822], [-11.164, 0.19, 2.579], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line15", 2, [-11.147, 0.19, -2.157], [-11.147, 0.19, -1.4], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line17", 3, [-5.896, 3.189, -2.577], [-5.896, 3.189, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line18", 3, [-7.098, 3.189, -2.577], [-7.098, 3.189, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line19", 3, [-8.299, 3.189, -2.577], [-8.299, 3.189, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line20", 3, [-9.516, 3.189, -2.577], [-9.516, 3.189, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line21", 3, [-0.258, 3.189, -0.62], [-5.303, 3.189, -0.62], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line22", 3, [-0.246, 3.189, -2.077], [-5.291, 3.189, -2.077], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line23", 3, [-6.281, 0.195, -2.577], [-6.281, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line24", 3, [-7.483, 0.195, -2.577], [-7.483, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line25", 3, [-8.684, 0.195, -2.577], [-8.684, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line26", 3, [-9.901, 0.195, -2.577], [-9.901, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line27", 2, [11.145, 3.189, -3.467], [9.605, 3.189, -3.467], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line28", 2, [8.135, 3.189, -3.467], [6.555, 3.189, -3.467], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line29", 2, [5.14, 3.189, -3.467], [3.6, 3.189, -3.467], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line30", 2, [2.109, 3.189, -3.467], [0.569, 3.189, -3.467], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line31", 2, [9.606, 3.189, 3.517], [11.146, 3.189, 3.517], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line32", 2, [6.575, 3.189, 3.517], [8.115, 3.189, 3.517], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line33", 2, [3.599, 3.189, 3.517], [5.139, 3.189, 3.517], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line34", 2, [0.569, 3.189, 3.517], [2.109, 3.189, 3.517], [0.0, 0.0, 1.0]],
    ["EFLine_piece01_destruct01_line35", 2, [11.243, 3.189, 2.917], [11.243, 3.189, 1.908], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line36", 2, [11.231, 3.189, -2.226], [11.231, 3.189, -3.235], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line37", 3, [5.896, 3.189, 2.577], [5.896, 3.189, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line38", 3, [7.098, 3.189, 2.577], [7.098, 3.189, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line39", 3, [8.299, 3.189, 2.577], [8.299, 3.189, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line40", 3, [9.516, 3.189, 2.577], [9.516, 3.189, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line41", 3, [5.365, 3.189, -0.814], [0.32, 3.189, -0.814], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line42", 3, [5.375, 3.189, -2.109], [0.33, 3.189, -2.109], [0.0, 0.0, -1.0]],
    ["EFLine_piece01_destruct01_line43", 3, [-0.636, 0.195, -2.577], [-0.636, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line44", 3, [-1.838, 0.195, -2.577], [-1.838, 0.195, 2.467], [-1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line45", 2, [11.246, 0.19, -1.822], [11.246, 0.19, -2.579], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line46", 2, [11.229, 0.19, 2.157], [11.229, 0.19, 1.4], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line47", 3, [6.281, 0.195, 2.577], [6.281, 0.195, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line48", 3, [7.483, 0.195, 2.577], [7.483, 0.195, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line49", 3, [8.684, 0.195, 2.577], [8.684, 0.195, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line50", 3, [9.901, 0.195, 2.577], [9.901, 0.195, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line51", 3, [0.636, 0.195, 2.577], [0.636, 0.195, -2.467], [1.0, 0.0, 0.0]],
    ["EFLine_piece01_destruct01_line52", 3, [1.838, 0.195, 2.577], [1.838, 0.195, -2.467], [1.0, 0.0, 0.0]],
  ],

Naval models is still undecoded. If someone makes good use of buildings models converter,
it might motivate me to write one for naval models as well ;-) (or if you decode naval models
yourself, I can write converters real fast, it's figuring out the format step which is slow)