Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Added the default_cache_size and default_synchronous pragmas. Added additional tests for pragmas. Added a new speedtest script. (CVS 421) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
161c0c5f5db66815e4345c9b5f7a600c |
User & Date: | drh 2002-03-06 22:01:35.000 |
Context
2002-03-06
| ||
22:04 | Beta 2 (CVS 422) (check-in: 6c3fb5470e user: drh tags: trunk) | |
22:01 | Added the default_cache_size and default_synchronous pragmas. Added additional tests for pragmas. Added a new speedtest script. (CVS 421) (check-in: 161c0c5f5d user: drh tags: trunk) | |
03:08 | Optimizations to the processing of integer comparisons. (CVS 420) (check-in: b7a7dae919 user: drh tags: trunk) | |
Changes
Changes to src/btree.c.
1 2 3 4 5 6 7 8 9 10 11 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** $Id: btree.c,v 1.60 2002/03/06 22:01:35 drh Exp $ ** ** This file implements a external (disk-based) database using BTrees. ** For a detailed discussion of BTrees, refer to ** ** Donald E. Knuth, THE ART OF COMPUTER PROGRAMMING, Volume 3: ** "Sorting And Searching", pages 473-480. Addison-Wesley ** Publishing Company, Reading, Massachusetts. |
︙ | ︙ | |||
646 647 648 649 650 651 652 653 654 655 656 657 658 659 | sqliteHashClear(&pBt->locks); sqliteFree(pBt); return SQLITE_OK; } /* ** Change the limit on the number of pages allowed the cache. */ int sqliteBtreeSetCacheSize(Btree *pBt, int mxPage){ sqlitepager_set_cachesize(pBt->pPager, mxPage); return SQLITE_OK; } /* | > > > > > > > > > > > > | 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 | sqliteHashClear(&pBt->locks); sqliteFree(pBt); return SQLITE_OK; } /* ** Change the limit on the number of pages allowed the cache. ** ** The maximum number of cache pages is set to the absolute ** value of mxPage. If mxPage is negative, the pager will ** operate asynchronously - it will not stop to do fsync()s ** to insure data is written to the disk surface before ** continuing. Transactions still work if synchronous is off, ** and the database cannot be corrupted if this program ** crashes. But if the operating system crashes or there is ** an abrupt power failure when synchronous is off, the database ** could be left in an inconsistent and unrecoverable state. ** Synchronous is on by default so database corruption is not ** normally a worry. */ int sqliteBtreeSetCacheSize(Btree *pBt, int mxPage){ sqlitepager_set_cachesize(pBt->pPager, mxPage); return SQLITE_OK; } /* |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** | | | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | ** COPY ** VACUUM ** BEGIN TRANSACTION ** COMMIT ** ROLLBACK ** PRAGMA ** ** $Id: build.c,v 1.86 2002/03/06 22:01:36 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** This routine is called after a single SQL statement has been ** parsed and we want to execute the VDBE code to implement |
︙ | ︙ | |||
1732 1733 1734 1735 1736 1737 1738 | zRight = 0; sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0); }else{ zRight = sqliteStrNDup(pRight->z, pRight->n); sqliteDequote(zRight); } | > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 | zRight = 0; sqliteSetNString(&zRight, "-", 1, pRight->z, pRight->n, 0); }else{ zRight = sqliteStrNDup(pRight->z, pRight->n); sqliteDequote(zRight); } /* ** PRAGMA default_cache_size ** PRAGMA default_cache_size=N ** ** The first form reports the current persistent setting for the ** page cache size. The value returned is the maximum number of ** pages in the page cache. The second form sets both the current ** page cache size value and the persistent page cache size value ** stored in the database file. ** ** The default cache size is stored in meta-value 2 of page 1 of the ** database file. The cache size is actually the absolute value of ** this memory location. The sign of meta-value 2 determines the ** synchronous setting. A negative value means synchronous is off ** and a positive value means synchronous is on. */ if( sqliteStrICmp(zLeft,"default_cache_size")==0 ){ static VdbeOp getCacheSize[] = { { OP_ReadCookie, 0, 2, 0}, { OP_AbsValue, 0, 0, 0}, { OP_Dup, 0, 0, 0}, { OP_Integer, 0, 0, 0}, { OP_Ne, 0, 6, 0}, { OP_Integer, MAX_PAGES,0, 0}, { OP_ColumnCount, 1, 0, 0}, { OP_ColumnName, 0, 0, "cache_size"}, { OP_Callback, 1, 0, 0}, }; Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return; if( pRight->z==pLeft->z ){ sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize); }else{ int addr; int size = atoi(zRight); if( size<0 ) size = -size; sqliteBeginWriteOperation(pParse); sqliteVdbeAddOp(v, OP_Integer, size, 0); sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2); addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0); sqliteVdbeAddOp(v, OP_Ge, 0, addr+3); sqliteVdbeAddOp(v, OP_Negative, 0, 0); sqliteVdbeAddOp(v, OP_SetCookie, 0, 2); sqliteEndWriteOperation(pParse); db->cache_size = db->cache_size<0 ? -size : size; sqliteBtreeSetCacheSize(db->pBe, db->cache_size); } }else /* ** PRAGMA cache_size ** PRAGMA cache_size=N ** ** The first form reports the current local setting for the ** page cache size. The local setting can be different from ** the persistent cache size value that is stored in the database ** file itself. The value returned is the maximum number of ** pages in the page cache. The second form sets the local ** page cache size value. It does not change the persistent ** cache size stored on the disk so the cache size will revert ** to its default value when the database is closed and reopened. ** N should be a positive integer. */ if( sqliteStrICmp(zLeft,"cache_size")==0 ){ static VdbeOp getCacheSize[] = { { OP_ColumnCount, 1, 0, 0}, { OP_ColumnName, 0, 0, "cache_size"}, { OP_Callback, 1, 0, 0}, }; Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return; if( pRight->z==pLeft->z ){ int size = db->cache_size;; if( size<0 ) size = -size; sqliteVdbeAddOp(v, OP_Integer, size, 0); sqliteVdbeAddOpList(v, ArraySize(getCacheSize), getCacheSize); }else{ int size = atoi(zRight); if( size<0 ) size = -size; if( db->cache_size<0 ) size = -size; db->cache_size = size; sqliteBtreeSetCacheSize(db->pBe, db->cache_size); } }else /* ** PRAGMA default_synchronous ** PRAGMA default_synchronous=BOOLEAN ** ** The first form returns the persistent value of the "synchronous" setting ** that is stored in the database. This is the synchronous setting that ** is used whenever the database is opened unless overridden by a separate ** "synchronous" pragma. The second form changes the persistent and the ** local synchronous setting to the value given. ** ** If synchronous is on, SQLite will do an fsync() system call at strategic ** points to insure that all previously written data has actually been ** written onto the disk surface before continuing. This mode insures that ** the database will always be in a consistent state event if the operating ** system crashes or power to the computer is interrupted unexpectedly. ** When synchronous is off, SQLite will not wait for changes to actually ** be written to the disk before continuing. As soon as it hands changes ** to the operating system, it assumes that the changes are permanent and ** it continues going. The database cannot be corrupted by a program crash ** even with synchronous off, but an operating system crash or power loss ** could potentially corrupt data. On the other hand, synchronous off is ** faster than synchronous on. */ if( sqliteStrICmp(zLeft,"default_synchronous")==0 ){ static VdbeOp getSync[] = { { OP_Integer, 0, 0, 0}, { OP_ReadCookie, 0, 2, 0}, { OP_Integer, 0, 0, 0}, { OP_Lt, 0, 5, 0}, { OP_AddImm, 1, 0, 0}, { OP_ColumnCount, 1, 0, 0}, { OP_ColumnName, 0, 0, "synchronous"}, { OP_Callback, 1, 0, 0}, }; Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return; if( pRight->z==pLeft->z ){ sqliteVdbeAddOpList(v, ArraySize(getSync), getSync); }else{ int addr; int size = db->cache_size; if( size<0 ) size = -size; sqliteBeginWriteOperation(pParse); sqliteVdbeAddOp(v, OP_ReadCookie, 0, 2); sqliteVdbeAddOp(v, OP_Dup, 0, 0); addr = sqliteVdbeAddOp(v, OP_Integer, 0, 0); sqliteVdbeAddOp(v, OP_Ne, 0, addr+3); sqliteVdbeAddOp(v, OP_AddImm, MAX_PAGES, 0); sqliteVdbeAddOp(v, OP_AbsValue, 0, 0); if( !getBoolean(zRight) ){ sqliteVdbeAddOp(v, OP_Negative, 0, 0); size = -size; } sqliteVdbeAddOp(v, OP_SetCookie, 0, 2); sqliteEndWriteOperation(pParse); db->cache_size = size; sqliteBtreeSetCacheSize(db->pBe, db->cache_size); } }else /* ** PRAGMA synchronous ** PRAGMA synchronous=BOOLEAN ** ** Return or set the local value of the synchronous flag. Changing ** the local value does not make changes to the disk file and the ** default value will be restored the next time the database is ** opened. */ if( sqliteStrICmp(zLeft,"synchronous")==0 ){ static VdbeOp getSync[] = { { OP_ColumnCount, 1, 0, 0}, { OP_ColumnName, 0, 0, "synchronous"}, { OP_Callback, 1, 0, 0}, }; Vdbe *v = sqliteGetVdbe(pParse); if( v==0 ) return; if( pRight->z==pLeft->z ){ sqliteVdbeAddOp(v, OP_Integer, db->cache_size>=0, 0); sqliteVdbeAddOpList(v, ArraySize(getSync), getSync); }else{ int size = db->cache_size; if( size<0 ) size = -size; if( !getBoolean(zRight) ) size = -size; db->cache_size = size; sqliteBtreeSetCacheSize(db->pBe, db->cache_size); } }else if( sqliteStrICmp(zLeft, "vdbe_trace")==0 ){ if( getBoolean(zRight) ){ db->flags |= SQLITE_VdbeTrace; }else{ |
︙ | ︙ |
Changes to src/main.c.
︙ | ︙ | |||
10 11 12 13 14 15 16 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** | | | 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ** ************************************************************************* ** Main file for the SQLite library. The routines in this file ** implement the programmer interface to the library. Routines in ** other files are for internal use by SQLite and should not be ** accessed by users of the library. ** ** $Id: main.c,v 1.68 2002/03/06 22:01:36 drh Exp $ */ #include "sqliteInt.h" #include "os.h" /* ** This is the callback routine for the code that initializes the ** database. See sqliteInit() below for additional information. |
︙ | ︙ | |||
40 41 42 43 44 45 46 | ** make sure fields do not contain NULLs. Otherwise we might core ** when attempting to initialize from a corrupt database file. */ assert( argc==4 ); switch( argv[0][0] ){ case 'c': { /* Recommended pager cache size */ int size = atoi(argv[3]); | | > | < | 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | ** make sure fields do not contain NULLs. Otherwise we might core ** when attempting to initialize from a corrupt database file. */ assert( argc==4 ); switch( argv[0][0] ){ case 'c': { /* Recommended pager cache size */ int size = atoi(argv[3]); if( size==0 ){ size = MAX_PAGES; } db->cache_size = size; sqliteBtreeSetCacheSize(db->pBe, size); break; } case 'f': { /* File format */ db->file_format = atoi(argv[3]); break; } case 's': { /* Schema cookie */ |
︙ | ︙ |
Changes to src/pager.c.
︙ | ︙ | |||
14 15 16 17 18 19 20 | ** The pager is used to access a database disk file. It implements ** atomic commit and rollback through the use of a journal file that ** is separate from the database file. The pager also implements file ** locking to prevent two processes from writing the same database ** file simultaneously, or one process from reading the database while ** another is writing. ** | | | 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | ** The pager is used to access a database disk file. It implements ** atomic commit and rollback through the use of a journal file that ** is separate from the database file. The pager also implements file ** locking to prevent two processes from writing the same database ** file simultaneously, or one process from reading the database while ** another is writing. ** ** @(#) $Id: pager.c,v 1.44 2002/03/06 22:01:36 drh Exp $ */ #include "sqliteInt.h" #include "pager.h" #include "os.h" #include <assert.h> #include <string.h> |
︙ | ︙ | |||
430 431 432 433 434 435 436 437 438 439 440 441 442 443 | rc = SQLITE_CORRUPT; } return rc; } /* ** Change the maximum number of in-memory pages that are allowed. */ void sqlitepager_set_cachesize(Pager *pPager, int mxPage){ if( mxPage>=0 ){ pPager->noSync = 0; }else{ pPager->noSync = 1; mxPage = -mxPage; | > > > > > > > | 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 | rc = SQLITE_CORRUPT; } return rc; } /* ** Change the maximum number of in-memory pages that are allowed. ** ** The maximum number is the absolute value of the mxPage parameter. ** If mxPage is negative, the noSync flag is also set. noSync bypasses ** calls to sqliteOsSync(). The pager runs much faster with noSync on, ** but if the operating system crashes or there is an abrupt power ** failure, the database file might be left in an inconsistent and ** unrepairable state. */ void sqlitepager_set_cachesize(Pager *pPager, int mxPage){ if( mxPage>=0 ){ pPager->noSync = 0; }else{ pPager->noSync = 1; mxPage = -mxPage; |
︙ | ︙ |
Changes to src/sqliteInt.h.
1 2 3 4 5 6 7 8 9 10 11 12 13 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | /* ** 2001 September 15 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** Internal interface definitions for SQLite. ** ** @(#) $Id: sqliteInt.h,v 1.103 2002/03/06 22:01:36 drh Exp $ */ #include "sqlite.h" #include "hash.h" #include "vdbe.h" #include "parse.h" #include "btree.h" #include <stdio.h> |
︙ | ︙ | |||
151 152 153 154 155 156 157 158 159 160 161 162 163 164 | struct sqlite { Btree *pBe; /* The B*Tree backend */ Btree *pBeTemp; /* Backend for session temporary tables */ int flags; /* Miscellanous flags. See below */ int file_format; /* What file format version is this database? */ int schema_cookie; /* Magic number that changes with the schema */ int next_cookie; /* Value of schema_cookie after commit */ int nTable; /* Number of tables in the database */ void *pBusyArg; /* 1st Argument to the busy callback */ int (*xBusyCallback)(void *,const char*,int); /* The busy callback */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash tblDrop; /* Uncommitted DROP TABLEs */ Hash idxDrop; /* Uncommitted DROP INDEXs */ | > | 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 | struct sqlite { Btree *pBe; /* The B*Tree backend */ Btree *pBeTemp; /* Backend for session temporary tables */ int flags; /* Miscellanous flags. See below */ int file_format; /* What file format version is this database? */ int schema_cookie; /* Magic number that changes with the schema */ int next_cookie; /* Value of schema_cookie after commit */ int cache_size; /* Number of pages to use in the cache */ int nTable; /* Number of tables in the database */ void *pBusyArg; /* 1st Argument to the busy callback */ int (*xBusyCallback)(void *,const char*,int); /* The busy callback */ Hash tblHash; /* All tables indexed by name */ Hash idxHash; /* All (named) indices indexed by name */ Hash tblDrop; /* Uncommitted DROP TABLEs */ Hash idxDrop; /* Uncommitted DROP INDEXs */ |
︙ | ︙ |
Changes to src/vdbe.c.
︙ | ︙ | |||
26 27 28 29 30 31 32 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** | | | 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 | ** type to the other occurs as necessary. ** ** Most of the code in this file is taken up by the sqliteVdbeExec() ** function which does the work of interpreting a VDBE program. ** But other routines are also provided to help in building up ** a program instruction by instruction. ** ** $Id: vdbe.c,v 1.133 2002/03/06 22:01:36 drh Exp $ */ #include "sqliteInt.h" #include <ctype.h> /* ** The following global variable is incremented every time a cursor ** moves, either by the OP_MoveTo or the OP_Next opcode. The test |
︙ | ︙ | |||
4305 4306 4307 4308 4309 4310 4311 | int tos = ++p->tos; int i = pOp->p1; VERIFY( if( NeedStack(p, tos) ) goto no_mem; ) VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; ) memcpy(&aStack[tos], &p->aMem[i].s, sizeof(aStack[tos])-NBFS);; if( aStack[tos].flags & STK_Str ){ zStack[tos] = p->aMem[i].z; | > | | 4305 4306 4307 4308 4309 4310 4311 4312 4313 4314 4315 4316 4317 4318 4319 4320 | int tos = ++p->tos; int i = pOp->p1; VERIFY( if( NeedStack(p, tos) ) goto no_mem; ) VERIFY( if( i<0 || i>=p->nMem ) goto bad_instruction; ) memcpy(&aStack[tos], &p->aMem[i].s, sizeof(aStack[tos])-NBFS);; if( aStack[tos].flags & STK_Str ){ zStack[tos] = p->aMem[i].z; aStack[tos].flags |= STK_Static; aStack[tos].flags &= ~STK_Dyn; } break; } /* Opcode: AggReset * P2 * ** ** Reset the aggregator so that it no longer contains any data. |
︙ | ︙ |
Changes to test/all.test.
1 2 3 4 5 6 7 8 9 10 11 12 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file runs all tests. # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file runs all tests. # # $Id: all.test,v 1.13 2002/03/06 22:01:36 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl rename finish_test really_finish_test proc finish_test {} {memleak_check} if {[file exists ./sqlite_test_count]} { |
︙ | ︙ | |||
33 34 35 36 37 38 39 40 41 42 43 44 45 46 | all.test quick.test malloc.test btree2.test } for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} { foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue source $testfile } if {[info exists Leak]} { lappend LeakList $Leak | > > > > > | 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | all.test quick.test malloc.test btree2.test } for {set Counter 0} {$Counter<$COUNT && $nErr==0} {incr Counter} { if {$Counter%2} { set ::SETUP_SQL {PRAGMA default_synchronous=off;} } else { catch {unset ::SETUP_SQL} } foreach testfile [lsort -dictionary [glob $testdir/*.test]] { set tail [file tail $testfile] if {[lsearch -exact $EXCLUDE $tail]>=0} continue source $testfile } if {[info exists Leak]} { lappend LeakList $Leak |
︙ | ︙ |
Added test/pragma.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | # 2002 March 6 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the PRAGMA command. # # $Id: pragma.test,v 1.1 2002/03/06 22:01:37 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Delete the preexisting database to avoid the special setup # that the "all.test" script does. # db close file delete test.db sqlite db test.db do_test pragma-1.1 { execsql { PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {2000 2000 1 1} do_test pragma-1.2 { execsql { PRAGMA cache_size=1234; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {1234 2000 1 1} do_test pragma-1.3 { db close sqlite db test.db execsql { PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {2000 2000 1 1} do_test pragma-1.4 { execsql { PRAGMA synchronous=OFF; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {2000 2000 0 1} do_test pragma-1.5 { execsql { PRAGMA cache_size=4321; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {4321 2000 0 1} do_test pragma-1.6 { execsql { PRAGMA synchronous=ON; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {4321 2000 1 1} do_test pragma-1.7 { db close sqlite db test.db execsql { PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {2000 2000 1 1} do_test pragma-1.8 { execsql { PRAGMA default_synchronous=OFF; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {2000 2000 0 0} do_test pragma-1.9 { execsql { PRAGMA default_cache_size=123; PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {123 123 0 0} do_test pragma-1.10 { db close sqlite db test.db execsql { PRAGMA cache_size; PRAGMA default_cache_size; PRAGMA synchronous; PRAGMA default_synchronous; } } {123 123 0 0} finish_test |
Changes to test/tester.tcl.
1 2 3 4 5 6 7 8 9 10 11 12 13 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements some common TCL routines used for regression # testing the SQLite library # | | | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | # 2001 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements some common TCL routines used for regression # testing the SQLite library # # $Id: tester.tcl,v 1.22 2002/03/06 22:01:37 drh Exp $ # Make sure tclsqlite was compiled correctly. Abort now with an # error message if not. # if {[sqlite -tcl-uses-utf]} { if {"\u1234"=="u1234"} { puts stderr "***** BUILD PROBLEM *****" |
︙ | ︙ | |||
42 43 44 45 46 47 48 49 50 51 52 53 54 55 | # Create a test database # catch {db close} file delete -force test.db file delete -force test.db-journal sqlite db ./test.db # Abort early if this script has been run before. # if {[info exists nTest]} return # Set the test counters to zero # | > > > | 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 | # Create a test database # catch {db close} file delete -force test.db file delete -force test.db-journal sqlite db ./test.db if {[info exists ::SETUP_SQL]} { db eval $::SETUP_SQL } # Abort early if this script has been run before. # if {[info exists nTest]} return # Set the test counters to zero # |
︙ | ︙ |
Changes to tool/speedtest.tcl.
︙ | ︙ | |||
33 34 35 36 37 38 39 | set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} set delay 1000 exec sync; after $delay; set t [time "exec psql drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format PostgreSQL: $t] exec sync; after $delay; | | | | | 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} set delay 1000 exec sync; after $delay; set t [time "exec psql drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format PostgreSQL: $t] exec sync; after $delay; set t [time "exec mysql -f drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format MySQL: $t] # set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] # set t [expr {[lindex $t 0]/1000000.0}] # puts [format $format {SQLite 2.3.2:} $t] # set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] # set t [expr {[lindex $t 0]/1000000.0}] # puts [format $format {SQLite 2.4 (cache=100):} $t] exec sync; after $delay; |
︙ | ︙ | |||
69 70 71 72 73 74 75 | drop table t1; drop table t2; } close $fd catch {exec psql drh <clear.sql} catch {exec mysql drh <clear.sql} set fd [open 2kinit.sql w] | | > > > < | > > > | 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | drop table t1; drop table t2; } close $fd catch {exec psql drh <clear.sql} catch {exec mysql drh <clear.sql} set fd [open 2kinit.sql w] puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=on; } close $fd exec ./sqlite240 s2k.db <2kinit.sql set fd [open nosync-init.sql w] puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=off; } close $fd exec ./sqlite240 sns.db <nosync-init.sql set ones {zero one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen} set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} proc number_name {n} { |
︙ | ︙ | |||
153 154 155 156 157 158 159 | runtest {100 SELECTs on a string comparison} set fd [open test$cnt.sql w] puts $fd {CREATE INDEX i2a ON t2(a);} puts $fd {CREATE INDEX i2b ON t2(b);} | < | | > > > > > > > > > > > > | | | 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 | runtest {100 SELECTs on a string comparison} set fd [open test$cnt.sql w] puts $fd {CREATE INDEX i2a ON t2(a);} puts $fd {CREATE INDEX i2b ON t2(b);} close $fd runtest {Creating an index} set fd [open test$cnt.sql w] for {set i 0} {$i<5000} {incr i} { set lwr [expr {$i*100}] set upr [expr {($i+1)*100}] puts $fd "SELECT count(*), avg(b) FROM t2 WHERE b>=$lwr AND b<$upr;" } close $fd runtest {5000 SELECTs with an index} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 0} {$i<1000} {incr i} { set lwr [expr {$i*10}] set upr [expr {($i+1)*10}] puts $fd "UPDATE t1 SET b=b*2 WHERE a>=$lwr AND a<$upr;" } puts $fd "COMMIT;" close $fd runtest {1000 UPDATEs without an index} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "UPDATE t2 SET b=$r WHERE a=$i;" } puts $fd "COMMIT;" close $fd runtest {25000 UPDATEs with an index} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "UPDATE t2 SET c='[number_name $r]' WHERE a=$i;" } puts $fd "COMMIT;" close $fd runtest {25000 text UPDATEs with an index} set fd [open test$cnt.sql w] puts $fd "BEGIN;" puts $fd "INSERT INTO t1 SELECT * FROM t2;" puts $fd "INSERT INTO t2 SELECT * FROM t1;" |
︙ | ︙ | |||
228 229 230 231 232 233 234 | runtest {A big INSERT after a big DELETE} set fd [open test$cnt.sql w] puts $fd {BEGIN;} puts $fd {DELETE FROM t1;} | | | 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 | runtest {A big INSERT after a big DELETE} set fd [open test$cnt.sql w] puts $fd {BEGIN;} puts $fd {DELETE FROM t1;} for {set i 1} {$i<=3000} {incr i} { set r [expr {int(rand()*100000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd {COMMIT;} close $fd runtest {A big DELETE followed by many small INSERTs} |
︙ | ︙ |
Added tool/speedtest2.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 | #!/usr/bin/tclsh # # Run this script using TCLSH to do a speed comparison between # various versions of SQLite and PostgreSQL and MySQL # # Run a test # set cnt 1 proc runtest {title} { global cnt set sqlfile test$cnt.sql puts "<h2>Test $cnt: $title</h2>" incr cnt set fd [open $sqlfile r] set sql [string trim [read $fd [file size $sqlfile]]] close $fd set sx [split $sql \n] set n [llength $sx] if {$n>8} { set sql {} for {set i 0} {$i<3} {incr i} {append sql [lindex $sx $i]<br>\n} append sql "<i>... [expr {$n-6}] lines omitted</i><br>\n" for {set i [expr {$n-3}]} {$i<$n} {incr i} { append sql [lindex $sx $i]<br>\n } } else { regsub -all \n [string trim $sql] <br> sql } puts "<blockquote>" puts "$sql" puts "</blockquote><table border=0 cellpadding=0 cellspacing=0>" set format {<tr><td>%s</td><td align="right"> %.3f</td></tr>} set delay 1000 exec sync; after $delay; set t [time "exec psql drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format PostgreSQL: $t] exec sync; after $delay; set t [time "exec mysql -f drh <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format MySQL: $t] # set t [time "exec ./sqlite232 s232.db <$sqlfile" 1] # set t [expr {[lindex $t 0]/1000000.0}] # puts [format $format {SQLite 2.3.2:} $t] # set t [time "exec ./sqlite-100 s100.db <$sqlfile" 1] # set t [expr {[lindex $t 0]/1000000.0}] # puts [format $format {SQLite 2.4 (cache=100):} $t] exec sync; after $delay; set t [time "exec ./sqlite240 s2k.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4:} $t] exec sync; after $delay; set t [time "exec ./sqlite240 sns.db <$sqlfile" 1] set t [expr {[lindex $t 0]/1000000.0}] puts [format $format {SQLite 2.4 (nosync):} $t] # set t [time "exec ./sqlite-t1 st1.db <$sqlfile" 1] # set t [expr {[lindex $t 0]/1000000.0}] # puts [format $format {SQLite 2.4 (test):} $t] puts "</table>" } # Initialize the environment # expr srand(1) catch {exec /bin/sh -c {rm -f s*.db}} set fd [open clear.sql w] puts $fd { drop table t1; drop table t2; } close $fd catch {exec psql drh <clear.sql} catch {exec mysql drh <clear.sql} set fd [open 2kinit.sql w] puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=on; } close $fd exec ./sqlite240 s2k.db <2kinit.sql exec ./sqlite-t1 st1.db <2kinit.sql set fd [open nosync-init.sql w] puts $fd { PRAGMA default_cache_size=2000; PRAGMA default_synchronous=off; } close $fd exec ./sqlite240 sns.db <nosync-init.sql set ones {zero one two three four five six seven eight nine ten eleven twelve thirteen fourteen fifteen sixteen seventeen eighteen nineteen} set tens {{} ten twenty thirty forty fifty sixty seventy eighty ninety} proc number_name {n} { if {$n>=1000} { set txt "[number_name [expr {$n/1000}]] thousand" set n [expr {$n%1000}] } else { set txt {} } if {$n>=100} { append txt " [lindex $::ones [expr {$n/100}]] hundred" set n [expr {$n%100}] } if {$n>=20} { append txt " [lindex $::tens [expr {$n/10}]]" set n [expr {$n%10}] } if {$n>0} { append txt " [lindex $::ones $n]" } set txt [string trim $txt] if {$txt==""} {set txt zero} return $txt } set fd [open test$cnt.sql w] puts $fd "BEGIN;" puts $fd "CREATE TABLE t1(a INTEGER, b INTEGER, c VARCHAR(100));" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd "COMMIT;" close $fd runtest {25000 INSERTs in a transaction} set fd [open test$cnt.sql w] puts $fd "DELETE FROM t1;" close $fd runtest {DELETE everything} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd "COMMIT;" close $fd runtest {25000 INSERTs in a transaction} set fd [open test$cnt.sql w] puts $fd "DELETE FROM t1;" close $fd runtest {DELETE everything} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd "COMMIT;" close $fd runtest {25000 INSERTs in a transaction} set fd [open test$cnt.sql w] puts $fd "DELETE FROM t1;" close $fd runtest {DELETE everything} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd "COMMIT;" close $fd runtest {25000 INSERTs in a transaction} set fd [open test$cnt.sql w] puts $fd "DELETE FROM t1;" close $fd runtest {DELETE everything} set fd [open test$cnt.sql w] puts $fd "BEGIN;" for {set i 1} {$i<=25000} {incr i} { set r [expr {int(rand()*500000)}] puts $fd "INSERT INTO t1 VALUES($i,$r,'[number_name $r]');" } puts $fd "COMMIT;" close $fd runtest {25000 INSERTs in a transaction} set fd [open test$cnt.sql w] puts $fd "DELETE FROM t1;" close $fd runtest {DELETE everything} set fd [open test$cnt.sql w] puts $fd {DROP TABLE t1;} close $fd runtest {DROP TABLE} |