I am using SQLite 3. I have deleted all of the rows of a member table
but one. The one row that remains is for the administrative user and
has an id of 0. I''d sure like to zero out the autoincrement counter.
The documentation for SQLite contains the following warning:
The content of the SQLITE_SEQUENCE table can be modified using
ordinary UPDATE, INSERT, and DELETE statements. But making
modifications to this table will likely perturb the AUTOINCREMENT key
generation algorithm. Make sure you know what you are doing before you
undertake such changes.
OK. I have been warned. It certainly seems like an easy procedure.
The thing about perturbing, "the AUTOINCREMENT key generation
algorithm" scares me. Do I really need to be concerned? Is there a
better way to accomplish this objective?
Thanks for any input.
... doug
Well, I am not a SQLite developer, but now I am -very- curious as to what that would do. Can you afford to make a copy of the SQLite file and -try-.. And then report back to us? ;-) -- Posted via http://www.ruby-forum.com/.
> Can you afford to make a copy of the SQLite file and -try-.. And then > report back to us? ;-)I am pleased to report that it seems to work just fine. The warning still scares me. I''m afraid that I am going to get unexpectedly bitten some day. The warning is expressly directed to those who do not know what they''re doing and I certainly fall into that camp! :) ... doug
Doug Jolley wrote:>> Can you afford to make a copy of the SQLite file and -try-.. And then >> report back to us? ;-) > > I am pleased to report that it seems to work just fine. The warning > still scares me. I''m afraid that I am going to get unexpectedly > bitten some day. The warning is expressly directed to those who do > not know what they''re doing and I certainly fall into that camp! :) > > ... dougAs a very wild guess, if you are autoincrementing things that aren''t plain numbers, the generation algorithm is a little trickier than just "Oh ... +1 !". But again - Me no SQLite expert. -- Posted via http://www.ruby-forum.com/.
2009/9/23 doug <ddjolley-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > I am using SQLite 3. I have deleted all of the rows of a member table > but one. The one row that remains is for the administrative user and > has an id of 0. I''d sure like to zero out the autoincrement counter. > The documentation for SQLite contains the following warning: > > The content of the SQLITE_SEQUENCE table can be modified using > ordinary UPDATE, INSERT, and DELETE statements. But making > modifications to this table will likely perturb the AUTOINCREMENT key > generation algorithm. Make sure you know what you are doing before you > undertake such changes. > > OK. I have been warned. It certainly seems like an easy procedure. > The thing about perturbing, "the AUTOINCREMENT key generation > algorithm" scares me. Do I really need to be concerned? Is there a > better way to accomplish this objective? >I think that as a matter of principle it is considered bad form to assign any significance to the id field. If you need users to have a number that means something you could have a separate column for this purpose. For the admin user it might be better to have a role field which indicates this. Using an id of 0 means that you can only ever have one admin and can never change the admin user to somebody else. (You could change the name of the admin user to A N Other, but you could not make an existing user (who may have other related data in the db) be the administrator, if I am explaining myself well enough. It may seem like inefficiency and extra work, but in the long run you may agree. I made this mistake myself and had to rework it later. Colin Colin
> I think that as a matter of principle it is considered bad form to > assign any significance to the id field. If you need users to have a > number that means something you could have a separate column for this > purpose. For the admin user it might be better to have a role field > which indicates this. Using an id of 0 means that you can only ever > have one admin and can never change the admin user to somebody else. > (You could change the name of the admin user to A N Other, but you > could not make an existing user (who may have other related data in > the db) be the administrator, if I am explaining myself well enough.Good points. I see it as being a judgement call. I originally started by adopting the convention that the administrative user would have the login, ''admin''. Then, I thought that it would be nice to allow anyone to be the administrator. I also preferred comparing integers rather than strings any time that I needed to test whether a particular user was the administrative user. I also think that I was probably influenced by the fact that Unix assigns the 0 id to the root user. Requiring that the single administrative user have the 0 id doesn''t bother me. However, as I say, it''s a judgement call and your points are well taken. Thanks for the input. ... doug
Doug Jolley wrote:>> I think that as a matter of principle it is considered bad form to >> assign any significance to the id field. �If you need users to have a >> number that means something you could have a separate column for this >> purpose. �For the admin user it might be better to have a role field >> which indicates this. �Using an id of 0 means that you can only ever >> have one admin and can never change the admin user to somebody else. >> (You could change the name of the admin user to A N Other, but you >> could not make an existing user (who may have other related data in >> the db) be the administrator, if I am explaining myself well enough. > > Good points. I see it as being a judgement call. I originally > started by adopting the convention that the administrative user would > have the login, ''admin''. Then, I thought that it would be nice to > allow anyone to be the administrator.Then users should have a field indicating their role. It is inappropriate to use the primary key for this. [...]> Requiring that the single administrative user have the 0 id > doesn''t bother me.It should. This sort of thing is never a good idea.> However, as I say, it''s a judgement call and your > points are well taken. Thanks for the input. > > ... dougIt''s not a judgement call. You''re doing something that is known to be bad practice. Don''t do it. Ever. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.