Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Updates and clarifications to the built-in SQL function documentation. Mark key sentences in that documentation as requirements. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
34346a758d4d4de6f7ba62d739a6bcd4 |
User & Date: | drh 2009-12-07 23:47:16.000 |
Context
2009-12-08
| ||
02:04 | Further updates to the SQL function documentation. (check-in: e265a59cf0 user: drh tags: trunk) | |
2009-12-07
| ||
23:47 | Updates and clarifications to the built-in SQL function documentation. Mark key sentences in that documentation as requirements. (check-in: 34346a758d user: drh tags: trunk) | |
20:51 | Corresponding to SQLite version 3.6.21. (check-in: 81b9488b48 user: drh tags: trunk, release) | |
Changes
Changes to pages/lang.in.
︙ | ︙ | |||
1539 1540 1541 1542 1543 1544 1545 | application may define additional functions written in C and added to the database engine using the [sqlite3_create_function()] API.</p> <table border=0 cellpadding=10> <tcl> funcdef {abs(X)} {} { | | > | | > > | | | | | | | | < | | | | | | | > | | | | | | | > > > > | | | | | | | | | | | | | | | | | | > > | < > > > > | | > | > > > > | > | > > > > | | < < < | | | | | | | | | | | | | > > | | | | | > | | | | | | | | | | | | > | | | | | | | | | < < | | | | 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 1767 1768 1769 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 | application may define additional functions written in C and added to the database engine using the [sqlite3_create_function()] API.</p> <table border=0 cellpadding=10> <tcl> funcdef {abs(X)} {} { ^The abs(X) function returns the absolute value of the numeric argument X. ^Abs(X) returns NULL if X is NULL. ^(Abs(X) return 0.0 if X is a string or blob that cannot be converted to a numeric value.)^ ^If X is the integer -9223372036854775807 then abs(X) throws an integer overflow error since there is no equivalent positive 64-bit two complement value. } funcdef {changes()} {} { ^The change() function returns the number of database rows that were changed or inserted or deleted by the most recently complete SQL. ^This is a wrapper around the [sqlite3_changes()] C/C++ function. } funcdef {coalesce(X,Y,...)} {} { ^The coalesce() function returns a copy of its first non-NULL argument, or NULL if all arguments are NULL. ^Coalesce() must be at least 2 arguments. } funcdef {glob(X,Y)} {} { ^The glob(X,Y) function is equivalent to the expression "<b>Y GLOB X</b>". Note that the X and Y arguments are reversed in the glob() function relative to the infix [GLOB] operator. ^If the [sqlite3_create_function()] interface is used to override the glob(X,Y) function with an alternative implementation then the [GLOB] operator will invoke the alternative implementation. } funcdef {ifnull(X,Y)} {} { ^The ifnull() function returns a copy of its first non-NULL argument, or NULL if both arguments are NULL. ^Ifnull() must have exactly 2 arguments. ^The ifnull() function is equivalent to [coalesce()] with two arguments. } funcdef {hex(X)} {} { ^The hex() function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal rendering of the content of that blob. } funcdef {last_insert_rowid()} {} { ^The last_insert_rowid() function returns the [ROWID] of the last row insert from the database connection which invoked the function. ^The last_insert_rowid() SQL function is a wrapper around the [sqlite3_last_insert_rowid()] C/C++ interface function. } funcdef {length(X)} {} { ^The length(X) function returns the string length of X in characters if X is a string, or in bytes if X is a blob. ^If X is NULL then length(X) is NULL. ^If X is numeric then length(X) returns the length of a string representation of X. } funcdef {like(X,Y) like(X,Y,Z)} {} { ^The like() function is used to implement the "<b>Y LIKE X [ESCAPE Z]</b>" expression. ^If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. ^Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix [LIKE] operator. ^The [sqlite3_create_function()] interface can be used to override the like() function and thereby change the operation of the [LIKE] operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the [LIKE] operator depending on whether or not an ESCAPE clause was specified. } funcdef {load_extension(X) load_extension(X,Y)} {} { ^The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y. ^The result of load_extension() is always a NULL. ^If Y is omitted then the default entry point of <b>sqlite3_extension_init</b> is used. ^The load_extension() function raises an exception if the extension fails to load or initialize correctly. <p>^The load_extension() function will fail if the extension attempts to modify or delete a SQL function or collating sequence. ^The extension can add new functions or collating sequences, but cannot modify or delete existing functions or collating sequences because those functions and/or collating sequences might be used elsewhere in the currently running SQL statement. To load an extension that changes or deletes functions or collating sequences, use the [sqlite3_load_extension()] C-language API.</p> } funcdef {lower(X)} {} { ^The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. ^The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension. } funcdef {ltrim(X) ltrim(X,Y)} {} { ^The ltrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the left side of X. ^If the Y argument is omitted, ltrim(X) removes spaces from the left side of X. } funcdef {max(X,Y,...)} {*maxCoreFunc *max {max() SQL function}} { ^The multi-argument max() function returns the argument with the maximum value, or return NULL if any argument is NULL. ^The multi-argument max() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. ^If none of the arguments to max() define a collating function, then the BINARY collating function is used. Note that <b>max()</b> is a simple function when it has 2 or more arguments but operates as an [minAggFunc | aggregate function] if given only a single argument. } funcdef {min(X,Y,...)} {*minCoreFunc *min {min() SQL function}} { ^The multi-argument min() function returns the argument with the minimum value. ^The multi-argument min() function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. ^If none of the arguments to min() define a collating function, then the BINARY collating function is used. Note that <b>min()</b> is a simple function when it has 2 or more arguments but operates as an [maxAggFunc | aggregate function] if given only a single argument. } funcdef {nullif(X,Y)} {} { ^The nullif(X,Y) function returns its first argument if the arguments are different and NULL if the arguments are the same. The nullif(X,Y) function searches its arguments from left to right for an argument that defines a collating function and uses that collating function for all string comparisons. ^If neither argument to nullif() defines a collating function then the BINARY is used. } funcdef {quote(X)} {} { ^The quote(X) function returns a string which is the value of its argument suitable for inclusion into another SQL statement. Strings are surrounded by single-quotes with escapes on interior quotes as needed. BLOBs are encoded as hexadecimal literals. } funcdef {random()} {} { ^The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. } funcdef {randomblob(N)} {} { ^The randomblob(N) function return an N-byte blob containing pseudo-random bytes. ^If N is less than 1 then a 1-byte random blob is returned. <p>Hint: applications can generate globally unique identifiers using this function together with [hex()] and/or [lower()] like this:</p> <blockquote> hex(randomblob(16))<br></br> lower(hex(randomblob(16))) </blockquote> } funcdef {replace(X,Y,Z)} {} { ^The replace(X,Y,Z) function returns a string formed by substituting string Z for every occurrance of string Y in string X. ^The [BINARY] collating sequence is used for comparisons. ^If Y is an empty string then return X unchanged. ^If Z is not initially a string, it is cast to a UTF-8 string prior to processing. } funcdef {round(X) round(X,Y)} {} { ^The round(X,Y) function returns a string representation of the floating-point value X rounded to Y digits to the right of the decimal point. ^If the Y argument is omitted, the X value is truncated to an integer. } funcdef {rtrim(X) rtrim(X,Y)} {} { ^The rtrim(X,Y) function returns a string formed by removing any and all characters that appear in Y from the right side of X. ^If the Y argument is omitted, rtrim(X) removes spaces from the right side of X. } funcdef {soundex(X)} {} { ^The soundex(X) function returns a string that is the soundex encoding of the string X. ^The string "?000" is returned if the argument is NULL. This function is omitted from SQLite by default. It is only available if the -DSQLITE_SOUNDEX=1 compiler option is used when SQLite is built. } funcdef {sqlite_source_id()} {} { ^The sqlite_source_id() function returns a string that identifies the specific version of the source code that was used to build the SQLite library. ^The string returned by sqlite_source_id() begins with the date and time that the source code was checked in and is follows by an SHA1 hash that uniquely identifies the source tree. ^This function is an SQL wrapper around the [sqlite3_sourceid()] C interface. } funcdef {sqlite_version()} {} { ^The sqlite_version() function returns the version string for the SQLite library that is running. ^This function is an SQL wrapper around the [sqlite3_libversion()] C-interface. } funcdef {substr(X,Y,Z) substr(X,Y)} {} { ^The substr(X,Y,Z) function returns a substring of input string X that begins with the Y-th character and which is Z characters long. ^If Z is omitted then substr(X,Y) returns all character through the end of the string X beginning with the Y-th. ^The left-most character of X is number 1. ^If Y is negative the the first character of the substring is found by counting from the right rather than the left. ^If Z is negative then the abs(Z) characters preceeding the Y-th character are returned. ^If X is string then characters indices refer to actual UTF-8 characters. ^If X is a BLOB then the indices refer to bytes. } funcdef {total_changes()} {} { ^The total_changes() function returns the number of row changes caused by INSERT, UPDATE or DELETE statements since the current database connection was opened. ^This function is a wrapper around the [sqlite3_total_changes()] C/C++ interface. } funcdef {trim(X) trim(X,Y)} {} { ^The trim(X,Y) function returns a string formed by removing any and all characters that appear in Y from both ends of X. ^If the Y argument is omitted, trim(X) removes spaces from both ends of X. } funcdef {typeof(X)} {} { ^The typeof(X) function returns a string that indicates the [datatype] of the expression X: "null", "integer", "real", "text", or "blob". } funcdef {upper(X)} {} { ^The upper(X) function returns a copy of input string X in which all lower-case ASCII characters are converted to their upper-case equivalent. } funcdef {zeroblob(N)} {} { ^The zeroblob(N) function returns a BLOB consisting of N bytes of 0x00. SQLite manages these zeroblobs very efficiently. Zeroblobs can be used to reserve space for a BLOB that is later written using [sqlite3_blob_open() | incremental BLOB I/O]. ^This SQL function is implemented using the [sqlite3_result_zeroblob()] routine from the C/C++ interface. } </tcl> </table> <tcl> ############################################################################## |
︙ | ︙ |
Changes to scan_test_cases.tcl.
︙ | ︙ | |||
60 61 62 63 64 65 66 | set FILELIST [lrange $argv 1 end] sqlite3 db docinfo.db proc output_one_record {} { global filename linenumber type requirement SRCCAT SRCCLASS regsub -all {\s+} [string trim $requirement] { } requirement regsub -all {\s?\*/$} $requirement {} requirement | | > > | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | set FILELIST [lrange $argv 1 end] sqlite3 db docinfo.db proc output_one_record {} { global filename linenumber type requirement SRCCAT SRCCLASS regsub -all {\s+} [string trim $requirement] { } requirement regsub -all {\s?\*/$} $requirement {} requirement if {![regexp {(R-[-\d]+)\s*(.*)} $requirement all reqno reqtext]} { return } if {$reqtext!=""} { set nreqno R-[md5-10x8 $reqtext] if {[string match $reqno* $nreqno]} { set reqno $nreqno } else { puts stderr "$filename:$linenumber: requirement number mismatch;\ $reqno should be $nreqno" |
︙ | ︙ |