From f8565825622a1ed48bdaa835968a1137b2ffa593 Mon Sep 17 00:00:00 2001 From: drh Date: Sun, 21 Nov 2004 01:02:00 +0000 Subject: [PATCH] AUTOINCREMENT documentation added. Improvements to lang.html. (CVS 2129) FossilOrigin-Name: ac72a1d5518f7b505ae2a1bd3be3d71db461ae7e --- Makefile.in | 13 +- main.mk | 14 +- manifest | 17 ++- manifest.uuid | 2 +- www/autoinc.tcl | 109 ++++++++++++++ www/lang.tcl | 378 ++++++++++++++++++++++++++---------------------- 6 files changed, 344 insertions(+), 189 deletions(-) create mode 100644 www/autoinc.tcl diff --git a/Makefile.in b/Makefile.in index 17a6a4f038..09c533f4df 100644 --- a/Makefile.in +++ b/Makefile.in @@ -408,6 +408,9 @@ arch.html: $(TOP)/www/arch.tcl arch2.gif: $(TOP)/www/arch2.gif cp $(TOP)/www/arch2.gif . +autoinc.html: $(TOP)/www/autoinc.tcl + tclsh $(TOP)/www/autoinc.tcl >autoinc.html + c_interface.html: $(TOP)/www/c_interface.tcl tclsh $(TOP)/www/c_interface.tcl >c_interface.html @@ -420,6 +423,9 @@ capi3ref.html: $(TOP)/www/capi3ref.tcl changes.html: $(TOP)/www/changes.tcl tclsh $(TOP)/www/changes.tcl >changes.html +compile.html: $(TOP)/www/compile.tcl + tclsh $(TOP)/www/compile.tcl >compile.html + copyright.html: $(TOP)/www/copyright.tcl tclsh $(TOP)/www/copyright.tcl >copyright.html @@ -510,11 +516,13 @@ version3.html: $(TOP)/www/version3.tcl # DOC = \ arch.html \ - arch2.gif \ + arch.png \ + autoinc.html \ c_interface.html \ capi3.html \ capi3ref.html \ changes.html \ + compile.html \ copyright.html \ copyright-release.html \ copyright-release.pdf \ @@ -534,6 +542,7 @@ DOC = \ oldnews.html \ omitted.html \ opcode.html \ + pragma.html \ quickstart.html \ speed.html \ sqlite.gif \ @@ -541,7 +550,7 @@ DOC = \ support.html \ tclsqlite.html \ vdbe.html \ - version3.html + version3.html doc: common.tcl $(DOC) mkdir -p doc diff --git a/main.mk b/main.mk index 2d97b27a32..be16417df1 100644 --- a/main.mk +++ b/main.mk @@ -384,6 +384,9 @@ arch.html: $(TOP)/www/arch.tcl arch.png: $(TOP)/www/arch.png cp $(TOP)/www/arch.png . +autoinc.html: $(TOP)/www/autoinc.tcl + tclsh $(TOP)/www/autoinc.tcl >autoinc.html + c_interface.html: $(TOP)/www/c_interface.tcl tclsh $(TOP)/www/c_interface.tcl >c_interface.html @@ -396,6 +399,9 @@ capi3ref.html: $(TOP)/www/capi3ref.tcl changes.html: $(TOP)/www/changes.tcl tclsh $(TOP)/www/changes.tcl >changes.html +compile.html: $(TOP)/www/compile.tcl + tclsh $(TOP)/www/compile.tcl >compile.html + copyright.html: $(TOP)/www/copyright.tcl tclsh $(TOP)/www/copyright.tcl >copyright.html @@ -484,19 +490,18 @@ vdbe.html: $(TOP)/www/vdbe.tcl version3.html: $(TOP)/www/version3.tcl tclsh $(TOP)/www/version3.tcl >version3.html -compile.html: $(TOP)/www/compile.tcl - tclsh $(TOP)/www/compile.tcl >compile.html - # Files to be published on the website. # DOC = \ arch.html \ arch.png \ + autoinc.html \ c_interface.html \ capi3.html \ capi3ref.html \ changes.html \ + compile.html \ copyright.html \ copyright-release.html \ copyright-release.pdf \ @@ -524,8 +529,7 @@ DOC = \ support.html \ tclsqlite.html \ vdbe.html \ - version3.html \ - compile.html + version3.html doc: common.tcl $(DOC) mkdir -p doc diff --git a/manifest b/manifest index 1f7d1a9aa0..c42950d753 100644 --- a/manifest +++ b/manifest @@ -1,6 +1,6 @@ -C Fix\sto\sthe\sdocumentation\son\ssqlite3_create_function.\s\sTicket\s#899.\s(CVS\s2128) -D 2004-11-20T21:02:14 -F Makefile.in e747bb5ba34ccbdd81f79dcf1b2b33c02817c21d +C AUTOINCREMENT\sdocumentation\sadded.\s\sImprovements\sto\slang.html.\s(CVS\s2129) +D 2004-11-21T01:02:00 +F Makefile.in 8291610f5839939a5fbff4dbbf85adb0fe1ac37f F Makefile.linux-gcc a9e5a0d309fa7c38e7c14d3ecf7690879d3a5457 F README a01693e454a00cc117967e3f9fdab2d4d52e9bc1 F VERSION 342b6d5fde93b6d45023e2fee0163dda6464b9d6 @@ -16,7 +16,7 @@ F doc/lemon.html f0f682f50210928c07e562621c3b7e8ab912a538 F doc/report1.txt a031aaf37b185e4fa540223cb516d3bccec7eeac F install-sh 9d4de14ab9fb0facae2f48780b874848cbf2f895 F ltmain.sh f6b283068efa69f06eb8aa1fe4bddfdbdeb35826 -F main.mk 088758a26c59b5bb60c1d8d3185b219fe7944796 +F main.mk 31af0ba9d4d0f03a5db4051a6f2ca527dfae3daf F mkdll.sh 468d4f41d3ea98221371df4825cfbffbaac4d7e4 F mkopcodec.awk 14a794f7b206976afc416b30fe8e0fc97f3434e9 F mkopcodeh.awk 4090944e4de0a2ccb99aa0083290f73bce4db406 @@ -225,6 +225,7 @@ F www/arch2.fig ae2432145c26cfa148fa0116589517ad3cd5fc65 F www/arch2.gif 6f2d47c4e0c5842c0d6b5513fd8249393d7c7003 F www/arch2b.fig d22a2c9642d584b89d4088b1e51e2bb0f7c04bed F www/audit.tcl 90e09d580f79c7efec0c7d6f447b7ec5c2dce5c0 +F www/autoinc.tcl b357f5ba954b046ee35392ce0f884a2fcfcdea06 F www/c_interface.tcl b51b08591554c16a0c3ef718364a508ac25abc7e F www/capi3.tcl 5c1cb163f4d2a54e2d0e22dcc399dd71245c8b89 F www/capi3ref.tcl 682ed717d564a1234badb8a563837547e140a382 @@ -244,7 +245,7 @@ F www/faq.tcl abe360e630d8134bc6242c5e3664969c397eac6e F www/fileformat.tcl 900c95b9633abc3dcfc384d9ddd8eb4876793059 F www/formatchng.tcl bfbf14dbf5181e771d06da7797767b0200b36d8a F www/index.tcl bb67c0d13020641d1fb4c043afa1f40fcc144d88 -F www/lang.tcl b2d8272bc89d94db373b4f1d071b1faa1153109a +F www/lang.tcl 9670e34e0a4a518b7de27a68055f49a790b6a840 F www/lockingv3.tcl f59b19d6c8920a931f096699d6faaf61c05db55f F www/mingw.tcl d96b451568c5d28545fefe0c80bee3431c73f69c F www/nulls.tcl ec35193f92485b87b90a994a01d0171b58823fcf @@ -260,7 +261,7 @@ F www/tclsqlite.tcl 560ecd6a916b320e59f2917317398f3d59b7cc25 F www/vdbe.tcl 095f106d93875c94b47367384ebc870517431618 F www/version3.tcl 092a01f5ef430d2c4acc0ae558d74c4bb89638a0 F www/whentouse.tcl fdacb0ba2d39831e8a6240d05a490026ad4c4e4c -P d10560c75244daebe55da5630ef7e7b84ba7f77a -R 30545a0753dd70ce9583a0fefab75a16 +P 4ab1d012f0f365c46bcabe67eace891eeaa78d70 +R 36dcf878f43a703fc736b88e118828a1 U drh -Z 9264d8e15e126e08fc159b47651406eb +Z ef040ca6aab550074695604039d8de29 diff --git a/manifest.uuid b/manifest.uuid index 73c12be2a0..d9a6fd00a3 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -4ab1d012f0f365c46bcabe67eace891eeaa78d70 \ No newline at end of file +ac72a1d5518f7b505ae2a1bd3be3d71db461ae7e \ No newline at end of file diff --git a/www/autoinc.tcl b/www/autoinc.tcl new file mode 100644 index 0000000000..9332adc850 --- /dev/null +++ b/www/autoinc.tcl @@ -0,0 +1,109 @@ +# +# Run this Tcl script to generate the autoinc.html file. +# +set rcsid {$Id: } +source common.tcl + +if {[llength $argv]>0} { + set outputdir [lindex $argv 0] +} else { + set outputdir "" +} + +header {SQLite Autoincrement} +puts { +

SQLite Autoincrement

+ +

+In SQLite, every row of every table has an integer ROWID. +The ROWID for each row is unique among all rows in the same table. +In SQLite version 2.8 the ROWID is a 32-bit signed integer. +Version 3.0 of SQLite expanded the ROWID to be a 64-bit signed integer. +

+ +

+You can access the ROWID of an SQLite table using one the special column +names ROWID, _ROWID_, or OID. +Except if you declare an ordinary table column to use one of those special +names, then the use of that name will refer to the declared column not +to the internal ROWID. +

+ +

+If a table contains a column of type INTEGER PRIMARY KEY, then that +column becomes an alias for the ROWID. You can then access the ROWID +using any of four different names, the original three names described above +or the name given to the INTEGER PRIMARY KEY column. All these names are +aliases for one another and work equally well in any context. +

+ +

+When a new row is inserted into an SQLite table, the ROWID can either +be specified as part of the INSERT statement or it can be assigned +automatically by the database engine. To specify a ROWID manually, +just include it in the list of values to be inserted. For example: +

+ +
+CREATE TABLE test1(a INT, b TEXT);
+INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');
+
+ +

+If no ROWID is specified on the insert, an appropriate ROWID is created +automatically. The usual algorithm is to give the newly created row +a ROWID that is one larger than the largest ROWID in the table prior +to the insert. If the table is initially empty, then a ROWID of 1 is +used. If the largest ROWID is equal to the largest possible integer +(9223372036854775807 in SQLite version 3.0 and later) then the database +engine starts picking candidate ROWIDs at random until it finds one +that is not previously used. +

+ +

+The normal ROWID selection algorithm described above +will generate monotonically increasing +unique ROWIDs as long as you never use the maximum ROWID value and you never +delete the entry in the table with the largest ROWID. +If you ever delete rows or if you ever create a row with the maximum possible +ROWID, then ROWIDs from previously deleted rows might be reused when creating +new rows and newly created ROWIDs might not be in strictly accending order. +

+ + +

The AUTOINCREMENT Keyword

+ +

+If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly +different ROWID selection algorithm is used. +The ROWID chosen for the new row is one larger than the largest ROWID +that has ever before existed in that same table. If the table has never +before contained any data, then a ROWID of 1 is used. If the table +has previously held a row with the largest possible ROWID, then new INSERTs +are not allowed and any attempt to insert a new row will fail with an +SQLITE_FULL error. +

+ +

+SQLite keeps track of the largest ROWID that a table has ever held using +the special SQLITE_SEQUENCE table. The SQLITE_SEQUENCE table is created +and initialized automatically whenever a normal table that contains an +AUTOINCREMENT column is created. 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. +

+ +

+The behavior implemented by the AUTOINCREMENT keyword is subtly different +from the default behavior. With AUTOINCREMENT, rows with automatically +selected ROWIDs are guaranteed to have ROWIDs that have never been used +before by the same table in the same database. And the automatically generated +ROWIDs are guaranteed to be monotonically increasing. These are important +properties in certain applications. But if your application does not +need these properties, you should probably stay with the default behavior +since the use of AUTOINCREMENT requires additional work to be done +as each row is inserted and thus causes INSERTs to run a little slower. +} +footer $rcsid diff --git a/www/lang.tcl b/www/lang.tcl index 8f8e421203..57d43effad 100644 --- a/www/lang.tcl +++ b/www/lang.tcl @@ -1,7 +1,7 @@ # -# Run this Tcl script to generate the sqlite.html file. +# Run this Tcl script to generate the lang-*.html files. # -set rcsid {$Id: lang.tcl,v 1.79 2004/11/20 08:17:18 danielk1977 Exp $} +set rcsid {$Id: lang.tcl,v 1.80 2004/11/21 01:02:01 drh Exp $} source common.tcl if {[llength $argv]>0} { @@ -12,15 +12,15 @@ if {[llength $argv]>0} { header {Query Language Understood by SQLite} puts { -

SQL As Understood By SQLite

+

SQL As Understood By SQLite

The SQLite library understands most of the standard SQL language. But it does omit some features while at the same time adding a few features of its own. This document attempts to describe precisely what parts of the SQL language SQLite does -and does not support. A list of keywords is -given at the end.

+and does not support. A list of keywords is +also provided.

In all of the syntax diagrams that follow, literal text is shown in bold blue. Non-terminal symbols are shown in italic red. Operators @@ -122,7 +122,9 @@ proc Section {name label} { rename footer_standard footer } set ::section_file [open [file join $outputdir lang_$label.html] w] - header "SQL command \"$name\"" + header "Query Language Understood by SQLite: $name" + puts "

SQL As Understood By SQLite

" + puts "\[Contents\]" puts "

$name

" return } @@ -448,7 +450,7 @@ CREATE [TEMP | TEMPORARY] TABLE [.] AS ( , ) } {column-constraint} { NOT NULL [ ] | -PRIMARY KEY [] [ ] | +PRIMARY KEY [] [ ] [AUTOINCREMENT] | UNIQUE [ ] | CHECK ( ) [ ] | DEFAULT | @@ -492,7 +494,7 @@ YYYY-MM-DD. The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

Specifying a PRIMARY KEY normally just creates a UNIQUE index -on the primary key. However, if primary key is on a single column +on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, @@ -503,7 +505,11 @@ then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER -PRIMARY KEY.

+PRIMARY KEY. An INTEGER PRIMARY KEY column man also include the +keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way +that B-Tree keys are automatically generated. Additional detail +on automatic B-Tree key generation is available +separately.

If the "TEMP" or "TEMPORARY" keyword occurs in between "CREATE" and "TABLE" then the table that is created is only visible to the @@ -798,8 +804,7 @@ puts { with the CREATE INDEX statement. The index named is completely removed from the disk. The only way to recover the index is to reenter the -appropriate CREATE INDEX command. Non-temporary indexes on tables in -an attached database cannot be dropped.

+appropriate CREATE INDEX command.

The DROP INDEX statement does not reduce the size of the database file. Empty space in the database is retained for later INSERTs. To @@ -1543,192 +1548,219 @@ is auto-vacuum mode, enabled using the auto_vacuum pragma.

} +# A list of keywords. A asterisk occurs after the keyword if it is on +# the fallback list. +# +set keyword_list [lsort { + ABORT* + AFTER* + ALL + ALTER + AND + AS + ASC* + ATTACH* + AUTOINCREMENT + BEFORE* + BEGIN* + BETWEEN + BY + CASCADE* + CASE + CHECK + COLLATE + COMMIT + CONFLICT* + CONSTRAINT + CREATE + CROSS + CURRENT_DATE* + CURRENT_TIME* + CURRENT_TIMESTAMP* + DATABASE* + DEFAULT + DEFERRED* + DEFERRABLE + DELETE + DESC* + DETACH* + DISTINCT + DROP + END* + EACH* + ELSE + ESCAPE + EXCEPT + EXCLUSIVE* + EXPLAIN* + FAIL* + FOR* + FOREIGN + FROM + FULL + GLOB* + GROUP + HAVING + IGNORE* + IMMEDIATE* + IN + INDEX + INITIALLY* + INNER + INSERT + INSTEAD* + INTERSECT + INTO + IS + ISNULL + JOIN + KEY* + LEFT + LIKE* + LIMIT + MATCH* + NATURAL + NOT + NOTNULL + NULL + OF* + OFFSET* + ON + OR + ORDER + OUTER + PRAGMA* + PRIMARY + RAISE* + REFERENCES + REINDEX* + RENAME* + REPLACE* + RESTRICT* + RIGHT + ROLLBACK + ROW* + SELECT + SET + STATEMENT* + TABLE + TEMP* + TEMPORARY* + THEN + TO + TRANSACTION + TRIGGER* + UNION + UNIQUE + UPDATE + USING + VACUUM* + VALUES + VIEW* + WHEN + WHERE +}] + + Section {SQLite keywords} keywords puts { -

The following keywords are used by SQLite. Most are either reserved -words in SQL-92 or were listed as potential reserved words. Those which -aren't are shown in italics. Not all of these words are actually used -by SQLite. Keywords are not reserved in SQLite. Any keyword can be used -as an identifier for SQLite objects (columns, databases, indexes, tables, -triggers, views, ...) but must generally be enclosed by brackets or -quotes to avoid confusing the parser. Keyword matching in SQLite is -case-insensitive.

+

The SQL standard specifies a huge number of keywords which may not +be used as the names of tables, indices, columns, or databases. The +list is so long that few people can remember them all. For most SQL +code, your safest bet is to never use any English language word as the +name of a user-defined object.

-

Keywords can be used as identifiers in three ways:

+

If you want to use a keyword as a name, you need to quote it. There +are three ways of quoting keywords in SQLite:

+

+

- + + + + +
'keyword' - Interpreted as a literal string if it occurs in a legal string - context, otherwise as an identifier. -
"keyword" - Interpreted as an identifier if it matches a known identifier - and occurs in a legal identifier context, otherwise as a string. -
[keyword] - Always interpreted as an identifier. (This notation is used - by MS Access and SQL Server.) +
'keyword'A keyword in single quotes is interpreted as a literal string + if it occurs in a context where a string literal is allowed, otherwise + it is understood as an identifier.
"keyword"A keyword in double-quotes is interpreted as an identifier if + it matches a known identifier. Otherwise it is interpreted as a + string literal.
[keyword]A keyword enclosed in square brackets is always understood as + an identifier. This is not standard SQL. This quoting mechanism + is used by MS Access and SQL Server and is included in SQLite for + compatibility.
+
+

-

Fallback Keywords

+

Quoted keywords are unaesthetic. +To help you avoid them, SQLite allows many keywords to be used unquoted +as the names of databases, tables, indices, triggers, views, and/or columns. +In the list of keywords that follows, those that can be used as identifiers +are shown in an italic font. Keywords that must be quoted in order to be +used as identifiers are shown in bold.

-

These keywords can be used as identifiers for SQLite objects without -delimiters.

-} +

+SQLite adds new keywords from time to time when it take on new features. +So to prevent you code from being broken by future enhancements, you should +normally quote any indentifier that is an English language word, even if +you do not have to. +

-proc keyword_list {x} { - puts "

" - foreach k $x { - if {[string index $k 0]=="*"} { - set nonstandard 1 - set k [string range $k 1 end] - } else { - set nonstandard 0 - } - if {$nonstandard} { - puts "$k   " - } else { - puts "$k   " - } +

+The following are the keywords currently recognized by SQLite: +

+ +
+ + +
+} + +set n [llength $keyword_list] +set nCol 5 +set nRow [expr {($n+$nCol-1)/$nCol}] +set i 0 +foreach word $keyword_list { + if {[string index $word end]=="*"} { + set word [string range $word 0 end-1] + set font i + } else { + set font b } - puts "

\n" + if {$i==$nRow} { + puts "		" + set i 1 + } else { + incr i + } + puts "<$font>$word
" } -keyword_list { - *ABORT - AFTER - ASC - *ATTACH - BEFORE - BEGIN - DEFERRED - CASCADE - *CLUSTER - *CONFLICT - *COPY - CROSS - *DATABASE - *DELIMITERS - DESC - *DETACH - EACH - END - EXPLAIN - *EXPLAIN - *FAIL - FOR - FULL - IGNORE - IMMEDIATE - INITIALLY - INNER - *INSTEAD - KEY - LEFT - MATCH - NATURAL - OF - *OFFSET - OUTER - *PRAGMA - *RAISE - *REPLACE - RESTRICT - RIGHT - *ROW - *STATEMENT - *TEMP - TEMPORARY - TRIGGER - *VACUUM - VIEW -} puts { +
-

Normal keywords

- -

These keywords can be used as identifiers for SQLite objects, but -must be enclosed in brackets or quotes for SQLite to recognize them as -an identifier.

-} - -keyword_list { - ALL - AND - AS - BETWEEN - BY - CASE - CHECK - COLLATE - COMMIT - CONSTRAINT - CREATE - DEFAULT - DEFERRABLE - DELETE - DISTINCT - DROP - ELSE - EXCEPT - ESCAPE - FOREIGN - FROM - *GLOB - GROUP - HAVING - IN - *INDEX - INSERT - INTERSECT - INTO - IS - *ISNULL - JOIN - LIKE - LIMIT - NOT - *NOTNULL - NULL - ON - OR - ORDER - PRIMARY - REFERENCES - ROLLBACK - SELECT - SET - TABLE - THEN - TRANSACTION - UNION - UNIQUE - UPDATE - USING - VALUES - WHEN - WHERE -} - -puts { -

Special words

+

Special names

The following are not keywords in SQLite, but are used as names of system objects. They can be used as an identifier for a different type of object.

-} -keyword_list { - *_ROWID_ - *MAIN - OID - *ROWID - *SQLITE_MASTER - *SQLITE_TEMP_MASTER +
+ _ROWID_
+ MAIN
+ OID
+ ROWID
+ SQLITE_MASTER
+ SQLITE_SEQUENCE
+ SQLITE_TEMP_MASTER
+ TEMP
+
} footer $rcsid if {[string length $outputdir]} { footer $rcsid } - -- 2.47.3