Skip to content
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

SQLite: Do not use AUTOINCREMENT keyword when creating database to no longer prevent the reuse of doc_ids over the lifetime of the database. #43

Closed
Sohail2949000 opened this issue Dec 2, 2024 · 4 comments
Assignees
Milestone

Comments

@Sohail2949000
Copy link

Dear Sir,

We would like to express our appreciation for the excellent software you have developed. It has been a valuable tool for our team.

However, we have noticed a minor issue with the row numbering system. When a row is deleted, the subsequent row numbers do not automatically adjust, which can affect the overall appearance and usability of the software, especially for tracking work experience.

We suggest adding a new, visible row number column that can be updated dynamically, even after rows are deleted. This would ensure accurate and consistent numbering, improving the overall user experience.

We believe that this enhancement would significantly enhance the software's functionality and usability.

Thank you for your time and consideration. We look forward to your response.

@rffrasca rffrasca self-assigned this Dec 5, 2024
@rffrasca
Copy link
Owner

rffrasca commented Dec 5, 2024

Hello again @Sohail2949000,
I appreciate the kind words. I'm glad PDFKeeper is serving you well.

I did some reading in the SQLite online documentation (https://www.sqlite.org/autoinc.html). I found something that explains the behavior you're seeing. According to the documentation, if the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

When the docs table is created, autoincrement does appear after integer primary key which will cause the behavior noted above. Also, it is not recommended to use AUTOINCREMENT so it should be removed. Based on the above information, I am going to test altering the docs table to eliminate the AUTOINCREMENT keyword and see if the row number is filled automatically with a ROWID one more than the largest ROWID currently in use when a new document record is created. If this is successful, I will create a fix script and remove the AUTOINCREMENT keyword from the code that creates the DOCS table.

@rffrasca rffrasca added the bug label Dec 5, 2024
@Sohail2949000
Copy link
Author

Okay Sir hope it will be solved

Regards.

@rffrasca rffrasca removed the bug label Dec 30, 2024
@rffrasca rffrasca added this to the 11.1.0 milestone Jan 4, 2025
@rffrasca rffrasca moved this to Backlog in PDFKeeper 11.2.0 Jan 4, 2025
@rffrasca
Copy link
Owner

rffrasca commented Jan 4, 2025

@Sohail2949000 - After doing some more research, it turns out that the autoincrement cannot be easily removed from an existing database. After giving this some thought, I am going to try the following approach. For a new database, the autoincrement will not be specified when the docs table is created. For an existing database, there will be a check for autoincrement. If found, then will check for the docs_after_delete trigger. If the trigger does not exist, then it will be created.

The trigger will perform the following after a delete:
create trigger docs_after_delete after delete on docs begin
update sqlite_sequence set seq = (select max(doc_id) from docs) where name='docs';
end;

This will be added in v11.1.0. At this time, I don't have an estimate yet when this version will be released.

@rffrasca
Copy link
Owner

Hello @Sohail2949000 - Since you're now using MySQL, is this still a concern?

@rffrasca rffrasca changed the title Enhancing User Experience: A Suggestion for Row Numbering SQLite: Remove the AUTOINCREMENT keyword when docs table is created to allow the reusing of deleted ID's and create an after-delete trigger for existing databases. Jan 18, 2025
@rffrasca rffrasca removed the status in PDFKeeper 11.2.0 Jan 18, 2025
@rffrasca rffrasca modified the milestones: 11.1.0, 11.0.2 Jan 18, 2025
@rffrasca rffrasca moved this to Ready in PDFKeeper 11.1.0 Jan 18, 2025
@rffrasca rffrasca changed the title SQLite: Remove the AUTOINCREMENT keyword when docs table is created to allow the reusing of deleted ID's and create an after-delete trigger for existing databases. SQLite: Do not specify the AUTOINCREMENT keyword in docs table when creating database to no longer prevent the reuse of doc_ids over the lifetime of the database. Jan 19, 2025
@rffrasca rffrasca changed the title SQLite: Do not specify the AUTOINCREMENT keyword in docs table when creating database to no longer prevent the reuse of doc_ids over the lifetime of the database. SQLite: Do not use AUTOINCREMENT keyword when creating database to no longer prevent the reuse of doc_ids over the lifetime of the database. Jan 19, 2025
@rffrasca rffrasca moved this from Ready to In progress in PDFKeeper 11.1.0 Jan 19, 2025
rffrasca added a commit that referenced this issue Jan 19, 2025
…Lite. #43

Signed-off-by: Robert F. Frasca <rffrasca@gmail.com>
@rffrasca rffrasca moved this from In progress to In review in PDFKeeper 11.1.0 Jan 19, 2025
@rffrasca rffrasca moved this from In review to Done in PDFKeeper 11.1.0 Feb 4, 2025
@rffrasca rffrasca closed this as completed by moving to Done in PDFKeeper 11.1.0 Feb 4, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
No open projects
Status: Done
Development

No branches or pull requests

2 participants