"SCRIPT SIMPLE DROP" won't generate DROP statements for system sequences - should it?

[Cipher Punk]

I've run into an interesting problem and suspect that H2 is skipping some necessary things when scripting a database into an SQL file with "SCRIPT SIMPLE DROP TO".

I believe it's not generating DROP commands for SYSTEM_SEQUENCE sequences, which prevents running the generated script on an existing database to restore it - restore will crash upon encountering sequences with the same globally unique ID.

IMHO, this needlessly complicates backup and restore operations. Manually created sequences get DROP SEQUENCE generated for them nicely.

What do you think, should SCRIPT SIMPLE DROP generate DROP statements for things like this?

id INT PRIMARY KEY AUTO_INCREMENT

...because an ID field made like that becomes a system sequence (random example below)...

"ID" INT DEFAULT NEXT VALUE FOR "PUBLIC"."SYSTEM_SEQUENCE_7569DD02_7110_4CEE_A8A0_AD6875A6AFFC" NOT NULL NULL_TO_DEFAULT SEQUENCE "PUBLIC"."SYSTEM_SEQUENCE_7569DD02_7110_4CEE_A8A0_AD6875A6AFFC"

 

[Evgenij Ryazanov]

Hello.

SCRIPT command has many flaws, but system sequences are going to be reimplemented together with identity columns when I or somebody else will find time to write a better implementation.

https://github.com/h2database/h2database/issues/2339

This command, however, is not designed for such use cases. You can use it, but you need to execute DROP ALL OBJECTS before RUNSCRIPT FROM, or you can delete your database, create a new empty one and execute RUNSCRIPT in it.

H2 has BACKUP command for online backups:

https://h2database.com/html/commands.html#backup

Backup can be restored by unpacking of archive (with offline database) or with Restore tool.

[Cipher Punk]

Thank you, these are useful tips - I found a way around the problem with them. :)

As for why I was using SCRIPT instead of BACKUP, it was actually because of transactions. I have a poorly written application that leaves lots of incomplete transactions and I wanted to deliberately exclude the transaction log from backups. :)