How to deserialize multiple "XCDR_AUTO" format .dat files in one folder?

Question

I have some .dat SQLite files serialized in the "XCDR_AUTO" format.

I am using RTI Convertor 6.1.1 to deserialize based on https://community.rti.com/static/documentation/connext-dds/6.0.0/doc/manuals/recording_service/converter/converter_configuration.html

Before when each folder has one .dat file, one discovery, one metadata file, the deserializing works well. Now I am trying to deserialize when one folder has multiple .dat files.

Experiment 1

First I generated a lot config files based on what tables are in the original .dat file (in this case A@10, B@10, C@10).

Here is the first one rti_converter_config_xxx-2023-10-24.T055829.xml for the xxx-2023-10-24.T055829.dat

<?xml version="1.0" encoding="utf-8"?>
<dds xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="https://community.rti.com/schema/6.1.1/rti_converter.xsd">
  <converter name="default">
    <input_storage>
      <sqlite>
        <storage_format>XCDR_AUTO</storage_format>
        <database_dir>.</database_dir>
      </sqlite>
    </input_storage>
    <output_storage>
      <sqlite>
        <storage_format>JSON_SQLITE</storage_format>
        <fileset>
          <workspace_dir>/tmp/output/xxx</workspace_dir>
          <filename_expression>xxx-2023-10-24.T055829_data.sqlite3</filename_expression>
        </fileset>
      </sqlite>
    </output_storage>
    <domain_participant name="10">
      <domain_id>10</domain_id>
    </domain_participant>
    <session name="DefaultSession">
      <topic_group name="A@10" participant_ref="10">
        <allow_topic_name_filter>A</allow_topic_name_filter>
      </topic_group>
      <topic_group name="B@10" participant_ref="10">
        <allow_topic_name_filter>B</allow_topic_name_filter>
      </topic_group>
      <topic_group name="C@10" participant_ref="10">
        <allow_topic_name_filter>C</allow_topic_name_filter>
      </topic_group>
    </session>
  </converter>
</dds>

When I run

> cd /tmp/raw/ && rticonverter -cfgFile rti_converter_config_xxx-2023-10-24.T060029.xml -cfgName default

RTI Recording Service (Converter) 6.1.1 starting...
RTI Recording Service started
Stream [A]: total samples written = 756
Stream [B]: total samples written = 1024
Stream [B]: total samples written = 2048
Stream [C]: total samples written = 1024
Stream [B]: total samples written = 3072
...
Stream [C]: total samples written = 74752
Stream [A]: total samples written = 76408
Stream [B]: total samples written = 75776
Stream [C]: total samples written = 76432
Stopping RTI Recording Service
RTI Recording Service stopped

Process finished with exit code 0

Because each .dat file has similar table (topic) names, the new deserialized table will overwrite the old deserialized table in the same deserialized sqlite file. So at the end the deserialized sqlite file only has tables

• A@10 (76408 rows)
• B@10 (75776 rows)
• C@10 (76432 rows)

How to avoid being overwrited and write to different files? Thanks!

Experiment 2

To avoid overwriting, I deleted the rest of .dat file, only leave one there.

However, when I deserialize again, I got error

RTI Recording Service (Converter) 6.1.1 starting...
RTI Recording Service started
create_stream_reader_fwd:SQLiteStorageStreamReader:!Table not found in database files: A@10

ROUTERConnection_createStreamReaderAdapter:(adapter=StorageAdapterPlugin, retcode=0: Invalid StreamReader returned by create_stream_reader())
ROUTERStreamReader_enable:!create stream reader adapter
ROUTERTopicRoute_enableInput:!enable stream reader
ROUTERTopicRoute_processEvent:!enable route input
ROUTERTopicRoute_onConditionTriggered:!process event
create_stream_reader_fwd:SQLiteStorageStreamReader:!Table not found in database files: A@10

But clearly, the A@10 table is there, and has data inside.

Also when I run SQLite's pragma integrity_check;, it returns "ok" which means the file is not broken.

How to deserialize correctly when have multiple .dat file? Thanks!

Answer 1

It turns out each .dat file supposed to have its own metadata file. However, RTI overwrites the metadata when generates those files. So at the end, only one metadata got saved.

So the workaround way I handle is

1. Split this folder to multiple folders and duplicate discovery and metadata to each folder. The folder now looks like

- 2023-10-24.T055829
  - 2023-10-24.T055829.dat
  - discovery
  - metadata
- 2023-10-24.T060029
  - 2023-10-24.T060029.dat
  - discovery
  - metadata
- ...

2. Update file_name (only one row) in each table Files_2_0 in the metadata file to correct name (The metadata is also sqlite file).

One potential way using Python:

import sqlite3
from pathlib import Path

metadata_path = Path("path/to/metadata")
dat_filename = "2023-10-24.T055829.dat"  # Next one would be "2023-10-24.T060029.dat", etc.

with sqlite3.connect(metadata_path) as conn:
    cursor = conn.cursor()
    sql_query = """
        update Files_2_0
        set file_name = ?
        where rowid = (select min(rowid) from Files_2_0)
    """
    cursor.execute(sql_query, (dat_filename,))
    conn.commit()

3. Each folder will have its own RTI converter config.

4. Now I can succeed deserialize each .dat file.