Database is exported and reimported on each query execution to support SQLITE Temp Tables functionality

[twoxfh]

As a user of SQLiteViz I have noticed the default main database filename changes constantly. If you run the following command on SqliteViz you will notice the database filename always changes with each query execution, however, this does not happen with the default Sql-js example. Is this a Bug or a Feature?

select * from pragma_database_list

It appears open gets called from reopen through the worker on execution of each query. If you check for this.db === null on open, a new file is not generated, however, then createDb is not always called and custom javascript db functions are not added.

[lana-k]

We reopen the database on each query execution in order to simplify working with temporary tables:
#53

[twoxfh]

We reopen the database on each query execution in order to simplify working with temporary tables:
#53

Thank you. Possibly DROP TABLE IF EXISTS can used in that scenario? My issue with this functionality is the entire database connection is reset, so attached databases are also lost.

@saaj could this address your issue in #53?

`
DROP TABLE IF EXISTS temp.HOUSE;
CREATE TABLE temp.house
(
name TEXT,
points INTEGER
);
INSERT INTO house VALUES
('Gryffindor', 100),
('Hufflepuff', 90),
('Ravenclaw', 95),
('Slytherin', 80);

select * from temp.house;
`

[saaj]

No, even though one can emulate temporary tables with DROP TABLE IF EXISTS, transactions, DELETE or what not. Current solution (connection per query) is optimal for:

• providing users temporary tables as an (expected) SQL feature
• providing isolation between tabs (e.g. transactions don't span across tabs)
• simplicity of behaviour and implementation

If there's a (experimentation, development, etc) need to modify SQLite connection's state the modification can be applied per connection. Or provided at user's disposal in SQL (e.g. ATTACH).

[twoxfh]

In my use, I currently have to attach databases on each query execution which also makes it hard for the multi-database object listing flashing on the side. I can only assume the reloading of the main database will get compounded as my databases grow towards gigabyte sizes.

I certainly appreciate the intention of how the sql connection is currently implemented, but not sure of a solution to my usecase. Would it be possible to configure connection reloading, for example a url parameter?

[saaj]

The application's design and trade-offs obviously cater to only its existing features. Currently sqliteviz operates on single SQLite database and doesn't support ATTACH (in any meaningful way).

Hence a discussion about new feature X that one wants to propose and/or build should start with describing what X is, why it's useful for end users of sqliteviz, and what design changes are needed (if any, and if known of course, otherwise that's up for discussion). Demonstrating a draft from a fork is definitely a good option. However basing such discussion on revision of existing design decision while X is unknown is kind of backwards. At least because we may not be on the same page about X's usefulness to end users and cost of maintenance. So I suggest to start from the top of what you're building.

Would it be possible to configure connection reloading, for example a url parameter?

It depends on X. Introducing feature config flags (build or runtime) might significantly impact development and testing -- for every new or existing feature you need to keep in mind that the application might work in mode 1 and mode 2. You may need to double subset of the test suite and so on.

[twoxfh]

It’s not obvious user data is constantly exported and reimported on each execution which to me is a flaw in the implementation of the feature with mitigation’s noted above. I do not know of another product that has this as part of their implementation for a similar feature. The discussion and mutual understanding of a path forward before a proposal will streamline the effort and direction. IF this feature stays I suggest this flaw be documented and users be warned in a clearer way.

I think this change should be backed out until connection support is available, currently I feel it's misleading. A more appropriate implementation in the future may use browser storage where a connection can be reset without losing all the data.

[twoxfh]

If SQL-js implements sql-js/sql.js#481 it looks like we could store the main database on the memory file system and close/open the database without having to export and reimport. This would leave attached databases still needing to be reconnected, however, it solves concerns about swapping large data in and out loaded through CSVs.