I agree with Keith and disagree with Simon. Regarding the current state for sequence generators that are specific to user databases, storing that state inside the same databases where they are used is exactly the right place.
Everything necessary to understand a database should be stored in that database, this is the correct way to do it, there is no wall being broken. Sequence generators are NOT private, just as the ROWIDs of SQLite tables are NOT private either. Users can and do directly access this information. A sequence generator is nothing more than an abstraction over a variable in a database (a SQL "table" is also a variable in a database) combined with an atomic routine to return its current value plus optionally increment that variable. A table with an auto-increment column is nothing more than an ordinary table with an on-insert triggered action to invoke said routine and use its value to modify the incoming row. Some DBMSs don't store sequence generator state as their own thing, sometimes they just use the actual row value of the table being auto-incremented itself. Either way the principle is the same. Some DBMSs perform the sequence state variable increment in an autonomous transaction that automatically commits before the main one, so that even if the transaction using the sequence generator rolls back, the sequence generator doesn't repeat the same values; others just keep this increment in the main transaction and so also can be rolled back and repeat. That's all that standard SQL/etc sequence generators are, regular user data, and one shouldn't be fooled into thinking they are something else. -- Darren Duncan On 2015-05-20 4:21 PM, Keith Medcalf wrote: > All relational database engines store configuration data within the users' > database. Many of them just hide it behind varying layers of logically > imposed complication. For example, you could simulate SQL Servers' > obfuscation by simply changing the name of the primary database alias from > "main" to "master" in the SQLite code, and then requiring that all "user" > tables are stored in an attached database. > >> -----Original Message----- >> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- >> bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin >> Sent: Wednesday, 20 May, 2015 14:38 >> To: sqlite-users at mailinglists.sqlite.org >> Subject: Re: [sqlite] AUTOINC vs. UUIDs >> >> On 20 May 2015, at 8:52pm, Kees Nuyt <k.nuyt at zonnet.nl> wrote: >> >>> The autoincrement clause causes an entry in the sqlite_sequence >>> table. >> >> It's interesting that SQLite uses tables inside the user database for >> private purposes like this. A certain wall is broken when the designers >> choose this option. SQLite does it for sqlite_master, sqlite_sequence, >> sqlite_stat*, and probably others I've forgotten. >> >> SQLite is handicapped by having no permanent data storage location. It >> has nowhere to store configuration information apart from inside the >> user's databases or as compilation settings. This is very unusual but, I >> think, contributes a lot to how portable SQLite is: no need to understand >> folder structure or safe places to keep configuration information; >> increased startup-speed; reduced code size, fewer file handles, slightly >> reduced memory. >> >> Simon.
