Hmmm, the .sqbpro file format isn't completely stable across versions. As new versions are released, we sometimes adjust things a bit.

That being said, it's XML. We haven't written out formal documentation for it, however the code which does the saving is pretty short and the pieces are commented:

Kind of guessing here, but the combination of reading through that + some experimentation with saving would probably figure out what you want.

Does that help? 😄

Specifically, the browseTableSettings is a Base 64 dump of the object serialisation in a QDataStream. The relevant code for this attribute is this:

    friend QDataStream& operator<<(QDataStream& stream, const BrowseDataTableSettings& object)
    {
        stream << object.sortOrderIndex;
        stream << static_cast<int>(object.sortOrderMode);
        stream << object.columnWidths;
        stream << object.filterValues;
        stream << object.displayFormats;
        stream << object.showRowid;
        stream << object.encoding;
        stream << object.plotXAxis;
        stream << object.plotYAxes;
        stream << object.unlockViewPk;
        stream << object.hiddenColumns;

        return stream;
    }

This is difficult to edit by yourself. I wouldn't know where to touch. What do you actually try to accomplish? You don't know the database structure in advance? I made once a script that generated the project file, but I was always generating a database with the same structure, and I only had to edit the database path.

Thank you all for your answers!

I'll try to clarify my problem. The tables that I dynamically create in my code (in Python) have tables about 100 columns each (CHARACTER (N) or FLOAT). To make it convenient to view and save the screen space, I have to constantly adjust width and save it in the project file * .sqbpro.
In order not to do this manually, I would like to create a * .sqbpro file simultaneously with the database, which would contain this information like that: <MY_TABLE COL_1=WIDTH_1, COL_2=WIDTH_2, ...>.

Yours faithfully,
Vitaliy

While you create your tables dynamically, do they have the same column counts and column names? Maybe you could use a 'standard' lump of XML which you could use across dynamically created .sqbpro file. Use one as a 'template', so to speak.

Field names and their type in tables may differ. However, when I create a table "(CREATE TABLE ...)" in the code, the name of the column and it's optimal width are already defined and clear, so I would like to create a * .sqbpro file in the same code for easy viewing of this table.

Hmm, this is possible but a little tricky. Here is basically how it would work:

1. Create a dummy project file and use that as a template
2. Remove the data in the <browsetable_info data="bla" tag. This data needs to be automatically generated and inserted in the correct location
3. Create a binary file like this:

• 4 bytes specifying the number of tables in the database
• For each table:
  • 4 bytes specifying the length of the table name in UTF-16 encoding
  • The table name in UTF-16 encoding without trailing \0 character
  • 4 bytes like this: 0x00 0x00 0x00 0x01 (sort key)
  • 4 zero bytes (sort order)
  • 4 bytes specifying the number of columns in the table
  • For each column:
    • 4 bytes specifying the column number (starting from 0 for the rowid column)
    • 4 bytes specifying the column width
  • 9 zero bytes (filters, display formats and show rowid column)
  • 4 bytes all 0xFF

4. BASE64 encode this file
5. Put the results into the project file template
6. Set the database file path and name in the project file
7. Open the project file using DB4S

If you have any problems with this, just let me know. If DB4S fails to open you project file you can also email me your results, so I can try them with a debugger attached.

Thank you very much!
I will try to do it.

@MKleusberg @mgrojo Would it make sense for us to adjust our project file saving code, so that instead of us using a base64 serialisation dump we instead write the columns and their lengths in straight XML?

I think @IVitalii is the first person to directly ask us about the format, so changing it in this way (as long as we can still read older project files too) shouldn't negatively affect anyone else. And it'd make the settings more accessible for people in the future also wanting to muck around with the contents. 😄

Lol. I was reading Martin's explanation of how the project file is written and immediately thought "why is it not a sqlite database" ?
Surely a database holding data on a database is just the right use for a database?! Scratches head...

straight XML

XML is great for portability and standard converters, but as the project file is solely for internal use, (just my opinion) I don't see how XML is great benefit?

Well it's already mostly XML except for pretty much the column widths. 😄

It's also looking like it's not solely for internal use so much after all. eg @IVitalii is showing a good use case for external tools to be able to change it.

XML (or json, or whatever) would mean people can manipulate the structure with automation tools. As long as the effort required for us to make the needed change isn't big, we might as well.

If anyone's interested in doing the work to make it happen that is. 😉

Of course, it makes sense to change this binary format for some pure XML tags. The addition of this binary settings to the XML not only makes difficult to generate the file from external tools, but it could represent a compatibility problem in the future. Not only due to adding or changing settings in that part, but also because Qt could change the format: http://doc.qt.io/qt-5/qdatastream.html#versioning Maybe it is improbable that they change the format for the primary types that we are using, but not impossible.

I could try a new pure XML implementation for these settings, but I think it makes sense to wait after our next release.

I could try a new pure XML implementation for these settings, but I think it makes sense to wait after our next release.

Sure, it's not urgent. 😄

I have a feeling it could prove useful down the track though, so hopefully it doesn't get lost among the other ~250 (atm) issues. 😉

I keep myself occupied with the implementation of a new pure XML format. The writing is mostly finished. This is an example of the written XML file passed through xmllint for formatting. What do you think of it? By the way, should a version tag be added or would it be enough testing in the reading for the old browsetable_info tag or the new tags?

<?xml version="1.0" encoding="UTF-8"?>
<sqlb_project>
  <db path="/home/mgr/tmp/sqlitebrowser/demodata_issue489.sqlite" foreign_keys="1"/>
  <window>
    <current_tab id="1"/>
  </window>
  <tab_structure>
    <column_width id="0" width="300"/>
    <column_width id="1" width="0"/>
    <column_width id="2" width="100"/>
    <column_width id="3" width="892"/>
    <column_width id="4" width="0"/>
    <expanded_item id="0" parent="1"/>
    <expanded_item id="1" parent="1"/>
    <expanded_item id="2" parent="1"/>
    <expanded_item id="3" parent="1"/>
  </tab_structure>
  <tab_browse>
    <current_table name="demodata"/>
    <default_encoding codec=""/>
    <tables>
      <table schema="main" name="FK_test" sort_order_index="0" sort_order_mode="0" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="">
        <columns/>
        <plot_y_axes/>
      </table>
      <table schema="main" name="demodata" sort_order_index="0" sort_order_mode="0" show_row_id="0" encoding="" plot_x_axis="Row #" unlock_view_pk="">
        <columns>
          <column index="1" width="54" filter="" display_format="" hidden="0"/>
          <column index="3" width="68" filter="" display_format="" hidden="0"/>
          <column index="4" width="177" filter="" display_format="round(`gains`)" hidden="0"/>
          <column index="5" width="157" filter="" display_format="" hidden="0"/>
          <column index="6" width="62" filter="=True" display_format="" hidden="0"/>
        </columns>
        <plot_y_axes>
          <y_axis name="prices" point_shape="5" colour="#004586" active="1"/>
        </plot_y_axes>
      </table>
      <table schema="main" name="demodates_int" sort_order_index="0" sort_order_mode="0" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="">
        <columns>
          <column index="1" width="100" filter="" display_format="" hidden="0"/>
          <column index="2" width="100" filter="" display_format="" hidden="0"/>
          <column index="3" width="100" filter="" display_format="" hidden="0"/>
          <column index="4" width="100" filter="" display_format="upper(`weekdays`)" hidden="0"/>
          <column index="5" width="100" filter="" display_format="" hidden="0"/>
          <column index="6" width="100" filter="" display_format="" hidden="0"/>
        </columns>
        <plot_y_axes/>
      </table>
      <table schema="main" name="report" sort_order_index="0" sort_order_mode="0" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="">
        <columns>
          <column index="1" width="92" filter="" display_format="" hidden="0"/>
          <column index="2" width="94" filter="" display_format="" hidden="0"/>
        </columns>
        <plot_y_axes>
          <y_axis name="count (up)" point_shape="5" colour="#ff420e" active="1"/>
        </plot_y_axes>
      </table>
    </tables>
    <tab_sql>
      <sql name="SQL 1"/>
      <current_tab id="0"/>
    </tab_sql>
  </tab_browse>
</sqlb_project>

Awesome @mgrojo. This looks good to me. 😄

A version tag is probably a decent idea too. If we never turn out to need it, then it shouldn't hurt. But if it turns out we could use it and haven't included it... ugh. 😉

We might want want to figure out a schema thing too, so things can validate the tags using it. Not sure how to do that personally, and we might not need it right from the start. But it's probably something useful to aim for at some point.

I'll trust you that the new format contains all the information as the old format 😉 If it does, then go for it. The only reason why I did it the way it is now is to save me from parsing a too complicated XML structure which I always find to be a pain.

A version tag probably doesn't really matter for now. We can add it but it's not really necessary I suppose, because we can always add it later and treat a non-existing version tag as version 0 or 1 or whatever. So far there were about 3-5 versions of the project file format and we got by without a version number, just by adding new data at the end and always checking if there is more data to come while parsing. This should be even easier with XML I guess.

We can add it but it's not really necessary I suppose, because we can always add it later and treat a non-existing version tag as version 0 or 1 or whatever.

Good point. Didn't think of that. 😄

I've finally commited the new format. It has evolved a bit, since the column settings cannot be easily merged in one tag. They are now independent as the data structure is in the software.

This is an example:

<?xml version="1.0" encoding="UTF-8"?>
<sqlb_project>
  <db path="/home/mgr/tmp/sqlitebrowser/logs.db" foreign_keys="1"/>
  <window>
    <current_tab id="1"/>
  </window>
  <tab_structure>
    <column_width id="0" width="300"/>
    <column_width id="1" width="0"/>
    <column_width id="2" width="100"/>
    <column_width id="3" width="2473"/>
    <column_width id="4" width="0"/>
    <expanded_item id="0" parent="1"/>
    <expanded_item id="1" parent="1"/>
    <expanded_item id="2" parent="1"/>
    <expanded_item id="3" parent="1"/>
  </tab_structure>
  <tab_browse>
    <current_table name="Technical_Statistics"/>
    <default_encoding codec=""/>
    <browse_table_settings>
      <table schema="main" name="Compact_Error_Logs" sort_order_index="2" sort_order_mode="1" show_row_id="1" encoding="US-ASCII" plot_x_axis="" unlock_view_pk="">
        <column_widths>
          <column index="1" value="199"/>
        </column_widths>
        <filter_values/>
        <display_formats/>
        <hidden_columns/>
        <plot_y_axes/>
      </table>
      <table schema="main" name="Technical_Statistics" sort_order_index="0" sort_order_mode="0" show_row_id="1" encoding="" plot_x_axis="Time" unlock_view_pk="">
        <column_widths>
          <column index="1" value="181"/>
          <column index="2" value="136"/>
          <column index="3" value="59"/>
          <column index="5" value="0"/>
          <column index="11" value="0"/>
        </column_widths>
        <filter_values>
          <column index="5" value="=37"/>
        </filter_values>
        <display_formats>
          <column index="2" value="lower(`Processor_Type`)"/>
        </display_formats>
        <hidden_columns>
          <column index="5" value="1"/>
          <column index="11" value="1"/>
        </hidden_columns>
        <plot_y_axes>
          <y_axis name="Current_CPU" line_style="1" point_shape="5" colour="#004586" active="1"/>
          <y_axis name="Current_Disk" line_style="1" point_shape="5" colour="#ffd320" active="1"/>
          <y_axis name="Current_Memory" line_style="1" point_shape="5" colour="#ff420e" active="1"/>
        </plot_y_axes>
      </table>
    </browse_table_settings>
    <tab_sql>
      <sql name="select_date_from_tech.sql">SELECT `date` FROM `Technical_Statistics`;
</sql>
      <sql name="SQL 1"/>
      <current_tab id="1"/>
    </tab_sql>
  </tab_browse>
</sqlb_project>

@IVitalii It is now probably easier for you to generate the new settings format. You might have already implemented the generation of the old format, which will be still supported in the loading. When you haven't, you will find the new format easier to write. Would you mind, in any case, take a look to the new version? You can use tomorrow's nightly version (Windows and OSX https://nightlies.sqlitebrowser.org/latest) or compile it by yourself.

@MKleusberg I didn't finally add a version tag. As you have explained, it is not really needed. There were also some fields missing 😉 Now all the settings are saved and restored.

Nice, the new format should be a lot easier to manipulate for users 😄 If we don't hear any objections from @IVitalii I would suggest closing this issue then. Ok?

@MKleusberg Of course, I have no objections :)
I should say thanks to all sqlitebrowser's developers for making my live easier (and many others too)!
Thank you very much indeed!

Nice to hear that! Thank you. I'll close it then.