Enhancing User Experience: A Suggestion for Row Numbering

[Sohail2949000]

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]

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.

[Sohail2949000]

Okay Sir hope it will be solved

Regards.

[rffrasca]

@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.