resetting SQLite auto-increment values

Got a LiveCode personal license? Are you a beginner, hobbyist or educator that's new to LiveCode? This forum is the place to go for help getting started. Welcome!

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller

Post Reply
glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

resetting SQLite auto-increment values

Post by glenn9 » Sat Jun 05, 2021 8:03 am

Dear all,

I'm wanting to reset the auto-increment number in column 'nr' so that it is in sync with the 'rowid' column after a record is deleted.

Below screenshot shows the rowid = 3, but the autoincrement nr = 4 after record 3 was deleted.
DB.png
I've tried resetting the 'nr' column with this code:

Code: Select all

 openDB
   put "UPDATE sqlite_sequence SET seq = (SELECT MAX(nr) FROM thinkDB)" into tSQL #
   put revdb_querylist(,,gConID,tSQL) into tList
   revExecuteSQL gConID,tSQL
   closeDB

but sadly with no luck!

Grateful for any suggestions or hints!

Thanks,

Glenn

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: resetting SQLite auto-increment values

Post by AxWald » Sat Jun 05, 2021 8:41 am

Hi,

tried manually. Judging by the statement that SQLite Manager created, this should work:

Code: Select all

UPDATE `main`.`sqlite_sequence` SET `seq` = '1' WHERE  `name` = 't_table'
(You forgot a WHERE clause/ row selector ...)

Besides, using autoincrement for anything but invisible id's isn't this good an idea. This always leads to headaches.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: resetting SQLite auto-increment values

Post by glenn9 » Sat Jun 05, 2021 9:45 am

Hi AxWald,

I've probably got the syntax wrong but have put the code into LC as follows:

Code: Select all

   
   openDB
  
   put "UPDATE `main`.`sqlite_sequence` SET `seq` = '1'" & CR & "WHERE  `name` = 'thinkDB'" into tSQL
   
   
   put revdb_querylist(,,gConID,tSQL) into tList
   revExecuteSQL gConID,tSQL
   closeDB
but autoincrement is still out of sync?

(I've also tried a table without an autoincrement column but I still have the same issue)

Not sure what I'm doing wrong!

Thanks

Glenn

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: resetting SQLite auto-increment values

Post by AxWald » Sat Jun 05, 2021 12:02 pm

Hi,

a.) "thinkDB" really is the table as it should be?
b.) You missed a space before "WHERE", and no need for a line break here.
c.) "revdb_queryList()" will not run an "UPDATE ..." statement, use "revdb_execute()" instead. Or "revExecuteSQL" as command, as you did in the following line. So skip this line here.
d.) What are the return values of the functions/ cmds? Always check these for errors!
e.) Does it change the value in `sqlite_sequence`.`seq`?

Assuming "thinkDB" actually is the table concerned, try this:

Code: Select all

/* First: Reset sequence:   */
   put "UPDATE `main`.`sqlite_sequence` SET `seq` = '1' WHERE `name` = 'thinkDB'" into tSQL
   get revdb_execute(gConID,tSQL)
      if it is not a number then
      answer "Error! Reason:" & CR & it
      exit whatEver
   end if

/* Second: Check if it worked:   */
   put "SELECT `seq` FROM `main`.`sqlite_sequence` WHERE `name` = 'thinkDB'" into tSQL
   get revdb_queryList(tab,CR,gConID,tSQL)
   if it begins with "revdberr" then
      answer "Error! Reason:" & CR & it
      exit whatEver
   end if
   put it     --  it must be "1"!
Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: resetting SQLite auto-increment values

Post by glenn9 » Sat Jun 05, 2021 1:10 pm

Hi AxWald,

Many thanks for your interest and help

your code works as expected in that I see the change of value in `sqlite_sequence`.`seq`, but the autoincrement of the database stubbornly refuses to update!

I'm guessing its the way that I've set up things that is causing the problem... but I can's see where I'm going wrong!

Not sure if its of help but have uploaded my stack just in case its of help or interest.

Thanks again,

Glenn
Attachments
Stack file.zip
(2.66 KiB) Downloaded 136 times
database file.zip
(504 Bytes) Downloaded 132 times

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: resetting SQLite auto-increment values

Post by marksmithhfx » Sat Jun 05, 2021 4:19 pm

glenn9 wrote:
Sat Jun 05, 2021 1:10 pm
Hi AxWald,

Many thanks for your interest and help

your code works as expected in that I see the change of value in `sqlite_sequence`.`seq`, but the autoincrement of the database stubbornly refuses to update!
Hi Glenn, as I understand it, autoincrement always chooses a number that is one higher than the highest rowid ever used in the database. If you don't use autoincrement, then SQLite assigns a rowid that is one higher than the current max rowid in use. So, for the second condition, for a new row, rowid = max(rowid) + 1. In the first case, SQLite tracks maxrowid over the life of the database, and any new record is assigned maxrowid + 1 where maxrowid is the maximum rowid ever seen. The sole purpose of autoincrement in SQLite is to not reuse previously used record numbers (whether they have been deleted or not).

You can access the rowid of any row directly as "rowid", "_rowid_" or "oid". Also, when you insert a new row, you can get the rowid assigned by SQLite using "SELECT last_insert_rowid()".

I don't know if this helps, as I don't know what you are attempting to do with nr, but maybe it will.

Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: resetting SQLite auto-increment values

Post by AxWald » Sat Jun 05, 2021 5:01 pm

Hi,

as Mark said. You cannot coerce SQLite to fill in missing ids "in between" if you use AUTOINCREMENT - independent of the value of "sqlite_sequence.seq", a new id will be always > max(id). So, if you deleted records at the end of the table, just setting the "seq" to 0 or 1 will cause max(id)+1 to be used for the next INSERT. This will also correct the "seq" value.

As mentioned, this isn't for fields with values that are visible. Use it this way:

Code: Select all

`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
as a proper id - visible only in code, used to link records in different tables. This is a very important feature - these ids should never be changed, else all hells will break loose. Been there, survived that. Barely.

If you need a field with numbers nicely ascending, without gaps, you'll have to code it yourself. Depending of your needs there's quite some methods for this.

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: resetting SQLite auto-increment values

Post by marksmithhfx » Sat Jun 05, 2021 8:31 pm

glenn9 wrote:
Sat Jun 05, 2021 8:03 am
I'm wanting to reset the auto-increment number in column 'nr' so that it is in sync with the 'rowid' column after a record is deleted.

Below screenshot shows the rowid = 3, but the autoincrement nr = 4 after record 3 was deleted.

DB.png
Glenn, now I see your problem. You are mistaking the first column you see (the one with values 1-4) as the rowid. It's not. The "nr" column you created is in fact an alias for rowid in an SQLite database. They are the same thing. Try doing this... create a new db with fields: dt, keyword, detail. Add 5 records as before, and delete record 3. Then run this:

Code: Select all

SELECT
	rowid,
	dt,
	keyword,
	detail
FROM
	thinkDB;
and this is what you'll get:
Screen Shot 2021-06-05 at 8.30.48 PM.png
So if that is not the result you are looking for, perhaps if we knew a bit more about what you are attempting to achieve we could make a suggestion.

Best,
Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

glenn9
Posts: 220
Joined: Wed Jan 15, 2020 10:45 pm
Location: Europe

Re: resetting SQLite auto-increment values

Post by glenn9 » Sun Jun 06, 2021 9:52 pm

AxWald, Mark,

Many thanks for your help and suggestions - now I get it!!

I was originally wanting sqlite to restore sequential numbering following a record deletion, and now realise that this isn't possible within SQLite if the deleted record isn't the last one added. It was this that was confusing me and which you have helped me understand - thank you.

Knowing this, I think I can now work out how to get the same effect through importing the SQLite data into LC and then manipulating the data within LC.

Thank you again.

Regards,

Glenn

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 931
Joined: Thu Nov 13, 2008 6:48 am
Location: London, UK

Re: resetting SQLite auto-increment values

Post by marksmithhfx » Thu Jun 10, 2021 9:32 pm

glenn9 wrote:
Sun Jun 06, 2021 9:52 pm
Knowing this, I think I can now work out how to get the same effect through importing the SQLite data into LC and then manipulating the data within LC.

Hi Glenn, let us know how you get on. There is always more than 1 way to skin a cat :D

Cheers,
Mark
macOS 12.6.5 (Monterey), Xcode 14.2, LC 10.0.0, iOS 15.6.1
Targets: Mac, iOS

Post Reply

Return to “Getting Started with LiveCode - Complete Beginners”