> >-----Ursprüngliche Nachricht----- >Von: RSmith [mailto:rsm...@rsweb.co.za] >Gesendet: Mittwoch, 25. Juni 2014 21:54 >An: sqlite-users@sqlite.org >Betreff: Re: [sqlite] Sequential numbers > > >On 2014/06/25 21:38, Dave Wellman wrote: >> Hi Petite, >> Many thanks fo rthsuggestion, it works a treat!. >> >> Hi Simon, >> Thanks for the thoughts but in this particular processing that is not >> going to happen (delete a few rows). In this processing we always >> empty the table completely before re-populating it. >> >> I've gone with the TRIGGEr solution, it works really well. >> > >Hi Dave, as a side note - if you never remove rows, AND in stead of emptying >the table (Truncation) you actually DROP and Re-CREATE it... then a standard >AUTOINCREMENT rowid alias is >almost guaranteed to actually produce you >sequential numbering from 1 onwards. It really only starts doing funnies when >you remove rows - but if you don't break the chain, the rowid >won't either. I >know the documentation does not "guarantee" it simply because the behaviour >can go haywire after amendments to the table, but I have yet to see a rowid >NOT start at 1 >in a brand-new table, or indeed just randomly "miss a beat" as >you add rows. never happens - until that first row is deleted. > >Sometimes however one doesn't intend to delete things, but it happens, so I >think the trigger solution by Mr. Bee is still safer - though this would >equally mess up the sequence if a >deletion happens - BUT, you can just >manually go change the values in that column then until they are happy again >and the trigger should perform business-as-usual from then on. > >Just another thought, >Have a great day! >
SQLite will do that automagically http://www.sqlite.org/compile.html#omit_truncate_optimization A default build of SQLite, if a DELETE statement has no WHERE clause and operates on a table with no triggers, an optimization occurs that causes the DELETE to occur by dropping and recreating the table. Dropping and recreating a table is usually much faster than deleting the table content row by row. This is the "truncate optimization". _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ----------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users