search

glimpseio / Splice

Issues and support files for the Splice app by Glimpse I/O
https://glimpse.io
0 stars 0 forks source link

Insert/update with non-updatable results #19

Open marcprux opened 5 years ago

marcprux commented 5 years ago

Splice uses updatable result sets to perform updates and inserts. However, many database drivers disallow result set modification under various circumstances, such as when there is no primary key in the results. For example, if you try to insert a new record in MySQL on a table that doesn't contain a primary key, you will get the error:

Result Set not updatable (referenced table has no primary keys).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table.

Splice should either identify these scenarios and fallback to attempting the insert using old-fashioned INSERT statements.

Updating is trickier, because while we could update based on the values of all the columns, this could in theory update more than one row, since without any primary keys, there is no guarantee of uniqueness. However, this seems to be what most other SQL database client apps do, so this might be the best route, possibly after first presenting the user with a warning that multiple rows may wind up being updated.

vanHoesel commented 1 year ago

Hey Marc,

I was having a bike ride today, and thinking about our Perl DBI modules and how easy it is to use the same library to use for dozens of DBD's. One of them even being CSV files.

CSV files do not have indexes, no primary keys, and no schema's or foreign keys.

And now you think: "but Splice doesn't support CSV databases", but I'd like too.

Which brings me to a whole new topic ...

Splice is dead ? can it be revived ?