New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

The description of project file format *.sqbpro #1306

Closed
IVitalii opened this Issue Jan 25, 2018 · 22 comments

Comments

Projects
None yet
5 participants
@IVitalii
Copy link

IVitalii commented Jan 25, 2018

Details for the issue

Hello!

I want to create a * .sqbpro (XML) project file with the database to automatically set the width of the columns, not in the normal manual mode. It seems that this information is contained in the tag <browsetable_info data = "???">. Where can I find the description for * .sqbpro format?

Yours faithfully,
Vitaliy

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jan 25, 2018

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:

void MainWindow::saveProject()
{
QString filename = FileDialog::getSaveFileName(this,
tr("Choose a filename to save under"),
tr("DB Browser for SQLite project file (*.sqbpro)"),
db.currentFile());
if(!filename.isEmpty())
{
// Make sure the file has got a .sqbpro ending
if(!filename.endsWith(".sqbpro", Qt::CaseInsensitive))
filename.append(".sqbpro");
QFile file(filename);
file.open(QFile::WriteOnly | QFile::Text);
QXmlStreamWriter xml(&file);
xml.writeStartDocument();
xml.writeStartElement("sqlb_project");
// Database file name
xml.writeStartElement("db");
xml.writeAttribute("path", db.currentFile());
xml.writeAttribute("foreign_keys", db.getPragma("foreign_keys"));
xml.writeEndElement();
// Window settings
xml.writeStartElement("window");
xml.writeStartElement("current_tab"); // Currently selected tab
xml.writeAttribute("id", QString::number(ui->mainTab->currentIndex()));
xml.writeEndElement();
xml.writeEndElement();
// Database Structure tab settings
xml.writeStartElement("tab_structure");
for(int i=0;i<ui->dbTreeWidget->model()->columnCount();i++) // Widths of tree view columns
{
xml.writeStartElement("column_width");
xml.writeAttribute("id", QString::number(i));
xml.writeAttribute("width", QString::number(ui->dbTreeWidget->columnWidth(i)));
xml.writeEndElement();
}
saveDbTreeState(ui->dbTreeWidget, xml); // Expanded tree items
xml.writeEndElement();
// Browse Data tab settings
xml.writeStartElement("tab_browse");
xml.writeStartElement("current_table"); // Currently selected table
xml.writeAttribute("name", ui->comboBrowseTable->currentText());
xml.writeEndElement();
xml.writeStartElement("default_encoding"); // Default encoding for text stored in tables
xml.writeAttribute("codec", defaultBrowseTableEncoding);
xml.writeEndElement();
{ // Table browser information
QByteArray temp;
QDataStream stream(&temp, QIODevice::WriteOnly);
stream << browseTableSettings;
xml.writeStartElement("browsetable_info");
xml.writeAttribute("data", temp.toBase64());
xml.writeEndElement();
}
xml.writeEndElement();
// Execute SQL tab data
xml.writeStartElement("tab_sql");
for(int i=0;i<ui->tabSqlAreas->count();i++) // All SQL tabs content
{
xml.writeStartElement("sql");
xml.writeAttribute("name", ui->tabSqlAreas->tabText(i));
xml.writeCharacters(qobject_cast<SqlExecutionArea*>(ui->tabSqlAreas->widget(i))->getSql());
xml.writeEndElement();
}
xml.writeStartElement("current_tab"); // Currently selected tab
xml.writeAttribute("id", QString::number(ui->tabSqlAreas->currentIndex()));
xml.writeEndElement();
xml.writeEndElement();
xml.writeEndElement();
xml.writeEndDocument();
file.close();
addToRecentFilesMenu(filename);
}
}

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? 😄

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Jan 25, 2018

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.

@mgrojo mgrojo added the question label Jan 25, 2018

@IVitalii

This comment has been minimized.

Copy link
Author

IVitalii commented Jan 26, 2018

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

@chrisjlocke

This comment has been minimized.

Copy link
Contributor

chrisjlocke commented Jan 26, 2018

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.

@IVitalii

This comment has been minimized.

Copy link
Author

IVitalii commented Jan 26, 2018

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.

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Jan 26, 2018

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
  1. BASE64 encode this file
  2. Put the results into the project file template
  3. Set the database file path and name in the project file
  4. 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.

@IVitalii

This comment has been minimized.

Copy link
Author

IVitalii commented Jan 26, 2018

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

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jan 26, 2018

@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. 😄

@chrisjlocke

This comment has been minimized.

Copy link
Contributor

chrisjlocke commented Jan 26, 2018

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...

@chrisjlocke

This comment has been minimized.

Copy link
Contributor

chrisjlocke commented Jan 26, 2018

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?

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jan 27, 2018

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. 😉

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Jan 28, 2018

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.

@mgrojo mgrojo added the enhancement label Jan 28, 2018

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Jan 28, 2018

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. 😉

@mgrojo mgrojo self-assigned this Feb 21, 2018

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Feb 21, 2018

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>
@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Feb 22, 2018

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.

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Feb 22, 2018

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.

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented Feb 22, 2018

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. 😄

mgrojo added a commit that referenced this issue Mar 3, 2018

New XML format for browse data table settings in project files
Browse data table settings are now saved in the project files using a pure
tagged XML format. This would make easier the generation of our project
files by external tools or direct human edition of the project files.

Compatibility with the older format is preserved in the loading. Previous
software versions have been tested with the new format, and they are also
able to load the new project files ignoring these new tags.

See related issue #1306
@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Mar 3, 2018

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.

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Mar 3, 2018

@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.

@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented Apr 29, 2018

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?

@IVitalii

This comment has been minimized.

Copy link
Author

IVitalii commented Apr 30, 2018

@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!

@mgrojo

This comment has been minimized.

Copy link
Contributor

mgrojo commented Apr 30, 2018

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

@mgrojo mgrojo closed this Apr 30, 2018

mgrojo added a commit that referenced this issue Jul 7, 2018

Issue #1458: fix tab_sql save and loading in project files
This issue was introduced in 211f734
(issue #1306).

browse_table_settings node was not closed, and consequently tab_sql node
was left inside tab_browse but the loading case for tab_sql was still
expecting it outside tab_browse. Since this was an unintended change the
tab_sql node is reverted now to its former location.

This means that all the nightly versions since
211f734 until this fix generate project
files with incorrect format and later versions will also not be able to
restore the SQL tabs for those project files unless manually edited.

Nevertheless, the loading code was also broken for tab_sql since there
was an incorrect skipCurrentElement call before getting to the tab_sql tag.

@mgrojo mgrojo referenced this issue Jul 8, 2018

Closed

project sqbpro files do not populate SQL #1458

4 of 10 tasks complete
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment