Databases get recreated when data exists

[SeanFarrow-RSK]

Describe the bug
When using the sql scripts to create databases to use with the Ado job store, the databases get recreated whether or not data exists in the tables.

I suspect this is due to the way tables are checked for existence. Currently, the existence of the raw table is checked, but we don't check whether any records exist in the table that may or may not exist.
Version used
Latest in the main branch.
With which version did you encounter this behavior.
3.5.0+
To Reproduce
Run the database scripts to create a database for hte ado job store (the database provider should not matter here). Then use quartz and add some jobs and triggers. Next, re-run the program, as the tables exist they will be dropped and re-created.

Expected behavior
If tables exist with data in, I would expect them not to be dropped and recreated.

[jafin]

@SeanFarrow-RSK are you certain it is quartz doing this? AFAIK there are NO schema manipulation routines INSIDE Quartz.net. There are SQL scripts provided to execute externally, but the Quartz.NET library does not execute these at runtime.

Looking at the SQL Scripts they do perform a drop/create. So if you were to rerun the script ,

quartznet/database/tables/tables_sqlServer.sql

Line 57 in a0e5bc1

DROP TABLE [dbo].[QRTZ_LOCKS];

for example. It would destroy existing tables and recreate them. But these really only should be executed once to setup the schema.

[SeanFarrow-RSK]

I’m pretty sure Quartz isn’t taking in to account the presents of data, yes. For example, if you look at line 29 of the tables_sqlServer.sql: IF OBJECT_ID(N'[dbo].[QRTZ_CALENDARS]', N'U') IS NOT NULL DROP TABLE [dbo].[QRTZ_CALENDARS]; GO This is only checking whether the table exists, but not whether data exists inside the existing table. What I would propose is to add a check for a single record in the if statement above. Thanks, Sean. ​We are the first IdentityServer partner to become a Certified B Corporation™. ​Head to our mission statement to read more about the ways we’re using business as a force for good. ​ ​Rock Solid Knowledge Ltd is a company registered in England and Wales under number 6811209. Registered office: C2, Vantage Office Park, Old Gloucester Road, Bristol, BS16 1GW, United Kingdom ​Vat registered: GB948 1966 72 From: Jason Finch ***@***.***> Sent: Monday, November 6, 2023 9:50 AM To: quartznet/quartznet ***@***.***> Cc: Sean Farrow ***@***.***>; Mention ***@***.***> Subject: Re: [quartznet/quartznet] Databases get recreated when data exists (Issue #2179) @SeanFarrow-RSK<https://github.com/SeanFarrow-RSK> are you certain it is quartz doing this? AFAIK there are NO schema manipulation aspects INSIDE quartz. There are SQL scripts provided to execute externally, but the Quartz.NET library does not execute these at runtime. — Reply to this email directly, view it on GitHub<#2179 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BA6KLZLUTXUQDPQ52OXPVQLYDCXETAVCNFSM6AAAAAA67DGSU2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOJUGQ2DIMJWG4>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[jafin]

But I'm sure the Quartz library isn't running these scripts. I can only assume your app or external process is? Those scripts exist to setup the schema once. They shouldn't be rerun. Perhaps there is scope to submit a PR to separate drops from creates, but to your original issue, these scripts should only be run once by whatever mean you have executed them by, as per the docs https://www.quartz-scheduler.net/documentation/quartz-3.x/quick-start.html#trying-out-the-application

[SeanFarrow-RSK]

Indeed, I’ve got code that now separates drops from creates for all databases. This PR also takes in to the case where data exists in the tables, which is what we were running up against. I’ll submit this either later today or on Monday. Thanks, Sean. ​We are the first IdentityServer partner to become a Certified B Corporation™. ​Head to our mission statement to read more about the ways we’re using business as a force for good. ​ ​Rock Solid Knowledge Ltd is a company registered in England and Wales under number 6811209. Registered office: C2, Vantage Office Park, Old Gloucester Road, Bristol, BS16 1GW, United Kingdom ​Vat registered: GB948 1966 72 From: Jason Finch ***@***.***> Sent: Monday, November 6, 2023 11:20 AM To: quartznet/quartznet ***@***.***> Cc: Sean Farrow ***@***.***>; Mention ***@***.***> Subject: Re: [quartznet/quartznet] Databases get recreated when data exists (Issue #2179) But I'm sure the Quartz library isn't running these scripts. I can only assume your app or external process is? Those scripts exist to setup the schema once. They shouldn't be rerun. Perhaps there is scope to submit a PR to separate drops from creates, but to your point. These scripts should only be run once by whatever mean you have executed them by, as per the docs https://www.quartz-scheduler.net/documentation/quartz-3.x/quick-start.html#trying-out-the-application — Reply to this email directly, view it on GitHub<#2179 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BA6KLZJU2TATUH3UVAJALLLYDDBXZAVCNFSM6AAAAAA67DGSU2VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTOOJUGYYDAMBTGI>. You are receiving this because you were mentioned.Message ID: ***@***.***>