[sqlite] A small patch for the SQLite shell in windows.
Thank you Richard.
The patch work very well
Here is a simple test:
E:\sqlite-src-3090200>chcp
?: 936 ---active code page: 936
E:\sqlite-src-3090200>shell
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(x,y);
sqlite> insert into t values('??','??');
sqlite> insert into t values('ABCEDFG','');
sqlite> select length(x),length(y) from t;
2|2
7|4
sqlite> select substr(x,2,1), substr(y,3,1) from t;
?|
B|?
sqlite> select length('');
4
sqlite> select substr('',2,3);
???
sqlite> select instr('','?');
4
From: Richard Hipp
Sent: 2015?12?30? 21:38
To: Quan Yong Zhai
Cc: SQLite mailing list
Subject: Re: [sqlite] A small patch for the SQLite shell in windows.
Please test the alternative patch found at
https://www.sqlite.org/src/info/a0a08b8c0bbd4d71 and let me know
whether or not the alternative patch fixes your problem.
On 12/30/15, Quan Yong Zhai wrote:
>>From: Richard Hipp
>>Sent: 2015?12?30? 20:21
>>To: SQLite mailing list
>>Subject: Re: [sqlite] A small patch for the SQLite shell in windows.
>
>>Please send a unified diff, generated using the -u option to the
>>"diff" command.? Even better would be the "-U 8" option, to include
>>more context.
>
> diff -U 8 src/shell.c /home/nana/shell.c
> diff.txt--cut here--
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Magic number in sqlite source code
Hello Richard ! It's hard to come up with names but as it is on https://www.sqlite.org/src/info/1541607d458069f5 I think that's a good improvement to make the code easier to follow and DRY. This approach has at least 2 benefits: 1- Is easy to see what parts of the code depend on PTRMAP_OVERFLOW1 value (by reading the code or searching) 2- If for some reason a change on the "struct CellInfo" is made that need a different value for PTRMAP_OVERFLOW1, only one place need to be changed. ? Also the macro OvflOffset(X) probably could be defined using the PTRMAP_OVERFLOW1. #define PTRMAP_OVERFLOW1 4 #define OvflOffset(X) ((X)->nSize-PTRMAP_OVERFLOW1) Cheers ! > Wed Dec 30 2015 9:51:52 pm CET CET from "Richard Hipp" >Subject: Re: [sqlite] Magic number in sqlite source code > > On 12/30/15, Richard Hipp wrote: > > >>I'll continue look for an alternative way to make the intent of the >> code clearer. >> >> > See https://www.sqlite.org/src/info/1541607d458069f5 for another > attempt at removing magic numbers. But I don't like it. It seems to > complicate more than it clarifies. My current thinking is that the > code should remain as it is on trunk. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ?
[sqlite] Documentation Typo in FTS Example
On 12/30/2015 03:57 PM, Casey Rodarmor wrote: > From https://sqlite.org/fts3.html: > > CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d); > CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c); > > INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f'); > INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l'); > > > The two inserts will fail, since t2 has 5 columns but 4 values were > supplied. Thanks for this. Now fixed in source control. Dan.
[sqlite] A small patch for the SQLite shell in windows.
>From: Richard Hipp
>Sent: 2015?12?30? 20:21
>To: SQLite mailing list
>Subject: Re: [sqlite] A small patch for the SQLite shell in windows.
>Please send a unified diff, generated using the -u option to the
>"diff" command.? Even better would be the "-U 8" option, to include
>more context.
diff -U 8 src/shell.c /home/nana/shell.c
diff.txt--cut here--
--- src/shell.c 2015-11-03 01:44:00.0 +0800
+++ /home/nana/shell.c 2015-12-28 01:36:42.643546200 +0800
@@ -809,21 +809,42 @@
/*
** This is the callback routine that the shell
** invokes for each row of a query result.
*/
static int shell_callback(
void *pArg,
int nArg,/* Number of result columns */
- char **azArg,/* Text of each result column */
+ char **azArgZ,/* Text of each result column */
char **azCol,/* Column names */
int *aiType /* Column types */
){
int i;
+#if defined(_WIN32) || defined(WIN32)
+ char** azArg = malloc(sizeof(char*)*nArg);
+ for (i = 0;i < nArg;i++) {
+ if (azArgZ[i] == 0)
+ azArg[i] = 0;
+ else {
+ int nlen = MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1,
NULL, 0);
+ assert(nlen > 0);
+ WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
+ (void)MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, buff,
nlen);
+ int ilen = WideCharToMultiByte(CP_ACP, 0, buff, nlen, NULL,
0, NULL, NULL);
+ assert(ilen > 0);
+ char* buff1 = (char*)malloc(ilen);
+ (void)WideCharToMultiByte(CP_ACP, 0, buff, nlen, buff1, ilen,
NULL, NULL);
+ azArg[i] = buff1;
+ free(buff);
+ }
+ }
+#else
+ char** azArg = azArgZ;
+#endif
ShellState *p = (ShellState*)pArg;
switch( p->mode ){
case MODE_Line: {
int w = 5;
if( azArg==0 ) break;
for(i=0; i0 ) fprintf(p->out, "%s", p->colSeparator);
fprintf(p->out,"%s",azArg[i] ? azArg[i] : p->nullValue);
}
fprintf(p->out, "%s", p->rowSeparator);
break;
}
}
+#if defined(_WIN32) || defined(WIN32)
+ for (i = 0;i < nArg;i++) {
+ if (azArg[i])
+ free(azArg[i]);
+ }
+ free(azArg);
+#endif
return 0;
}
/*
** This is the callback routine that the SQLite library
** invokes for each row of a query result.
*/
static int callback(void *pArg, int nArg, char **azArg, char **azCol){
@@ -4247,19 +4275,35 @@
memcpy(zSql+nSql, zLine, nLine+1);
nSql += nLine;
}
if( nSql && line_contains_semicolon([nSqlPrior], nSql-nSqlPrior)
&& sqlite3_complete(zSql) ){
p->cnt = 0;
open_db(p, 0);
if( p->backslashOn ) resolve_backslashes(zSql);
- BEGIN_TIMER;
- rc = shell_exec(p->db, zSql, shell_callback, p, );
- END_TIMER;
+#if defined(_WIN32) || defined(WIN32)
+ int nlen = MultiByteToWideChar(CP_ACP, 0, zSql, -1, 0, 0);
+ assert(nlen > 0);
+ WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
+ (void)MultiByteToWideChar(CP_ACP, 0, zSql, -1, buff, nlen);
+ int ilen = WideCharToMultiByte(CP_UTF8, 0, buff, nlen, NULL, 0, NULL,
NULL);
+ assert(ilen > 0);
+ char* buff1 = (char*)malloc(ilen);
+ (void)WideCharToMultiByte(CP_UTF8, 0, buff, nlen, buff1, ilen, NULL,
NULL);
+ free(buff);
+ BEGIN_TIMER;
+ rc = shell_exec(p->db, buff1, shell_callback, p, );
+ END_TIMER;
+ free(buff1);
+#else
+ BEGIN_TIMER;
+ rc = shell_exec(p->db, zSql, shell_callback, p, );
+ END_TIMER;
+#endif
if( rc || zErrMsg ){
char zPrefix[100];
if( in!=0 || !stdin_is_interactive ){
sqlite3_snprintf(sizeof(zPrefix), zPrefix,
"Error: near line %d:", startline);
}else{
sqlite3_snprintf(sizeof(zPrefix), zPrefix, "Error:");
}
--cut here
[sqlite] Magic number in sqlite source code
Hello Richard ! I just saw this commit https://www.sqlite.org/src/info/6a4cfc7ab62046eb and noticed you've been using magic numbers would it be better to use a macro instead ? I think for other people (and maybe yourself) would be easier to see something like "INFO_SIZE_ADJUST" (or any meaningful name) instead of "4". This also happen on other places, although I understand that is hard to maintain consistency (there is several places where sqlite3 do use macros instead of magic numbers). Cheers ! in src/btree.c 1054 pInfo->nSize = (u16)(>pPayload[pInfo->nLocal] - pCell) + 4; 1079 assert( pPage->childPtrSize==4 ); 1083 pInfo->nSize = 4 + getVarint([4], (u64*)>nKey); 1153 if( pInfo->nSize<4 ) pInfo->nSize = 4; 1191 if( pInfo->nSize<4 ) pInfo->nSize = 4; 1309 Pgno ovfl = get4byte([info.nSize-4]); 3349 && iFrom==get4byte(pCell+info.nSize-4) 5999 ovflPgno = get4byte(pCell + info.nSize - 4); 6000 assert( pBt->usableSize > 4 ); 6001 ovflPageSize = pBt->usableSize - 4; 6858 Pgno ovfl = get4byte([info.nSize-4]); 9164 assert( pc + info.nSize - 4 <= usableSize ); 9166 pgnoOvfl = get4byte([info.nSize - 4]);
[sqlite] Database locked error with only one process?
I read around, and it seems that the consensus is it should only be locked during a multi-thread/multi-process update. However I encountered the error in a python script (single proc/single thread) that was the only reader/writer to the database. It seems that I forgot to con.commit() in a loop that was doing thousands of update statements. It seems to me that there is some limit to how many pending statements there can be before encountering this error. If that is true, and not a bug, then maybe add it to the documentation somewhere? I didn't find any mention of "too many pending statements" as a cause anywhere. Adding a commit() periodically in the loop made it go away.
[sqlite] Database locked error with only one process?
On 30 Dec 2015, at 5:12pm, Jason H wrote: > I read around, and it seems that the consensus is it should only be locked > during a multi-thread/multi-process update. Nope. The database is locked any time a transaction is under way [1]. Your program cannot know if another process is going to try to access the database so it has to lock the database just in case. The lock won't have any effect unless another thread/process gets into it, but your process can't know that. > However I encountered the error in a python script (single proc/single > thread) that was the only reader/writer to the database. It seems that I > forgot to con.commit() in a loop that was doing thousands of update > statements. It seems to me that there is some limit to how many pending > statements there can be before encountering this error. If that is true, and > not a bug, then maybe add it to the documentation somewhere? I didn't find > any mention of "too many pending statements" as a cause anywhere. Adding a > commit() periodically in the loop made it go away. There is no such limitation in SQLite. The limits to the amount of pending statements are operating resources like memory and memory handles. It's possible that the problem you encountered is part of your Python interface to SQLite. So you would need to take this up with the author of the interface (who might be reading this list !). Simon. [1] Transaction doesn't start with default BEGIN. SQLite waits until something actually needs the database to lock it. If you want the database locked right now use BEGIN IMMEDIATE. Simon.
[sqlite] Function patternCompare() not EBCDIC friendly
Roland, I am pleased to see that you are successfully using SQLite on z/OS. Are you using the standard amalgamation? Did you need to apply special mods to SQLite to work on z/OS? Would you mind sharing your build procedure? Few months ago I tried compiling SQLite on z/OS: It worked but the data-bases generated on z/OS contains text data in EBCDIC format, including metadata, which makes those data-bases unusable on other platforms. The opposite was also true in my experience: Data-bases created on other platforms (I tried Linux) are not usable on z/OS for the very same reason. At that time I've spent some time trying to find where SQLite required to be modified to change this behavior, but this resulted a too hard assignment given my limited understanding of SQLite internals. Did you face the same issue? Did you find a solution? Anybody willing to help here giving guidance on what should be changed in SQLite to have text data and meta-data stored in UTF-8 format under z/OS? Thank you, mario On 12/30/2015 04:55 PM, Roland Martin wrote: > I have tested the code change on z/OS and it works. > > Thanks for the quick turnaround! > > Roland Martin > > -Original Message- > From: drhsqlite at gmail.com [mailto:drhsqlite at gmail.com] On Behalf Of > Richard Hipp > Sent: Wednesday, December 30, 2015 9:08 AM > To: SQLite mailing list > Cc: rolandsmartin at gmail.com > Subject: Re: [sqlite] Function patternCompare() not EBCDIC friendly > > On 12/30/15, Roland Martin wrote: >> Working with version 3.9.2 on IBM z/OS case insensitive LIKE queries >> do not work if case does not match. > > Please test and let us know if the > https://www.sqlite.org/src/info/0a99a8c4facf65ec check-in fixes your problem. > > -- > D. Richard Hipp > drh at sqlite.org > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Function patternCompare() not EBCDIC friendly
Hi Mario, I was using SQLite 3.8.6 up to this point on z/OS with a couple of minor code changes which are now included in version 3.9.2. With the fix Richard provided this morning all functionality I have tested so far is working out of the box with no code changes on z/OS. The databases I use are EBCDIC based (not UTF-8) so there is no moving them as-is to Windows or Linux. The way I work around this is to export via: sqlite3 test.db ".dump" > test.sql FTP test.sql to Windows/Linux in ascii mode and then import. The reverse can also be done. I have not tried using it but xlc has the ASCII option documented here: https://www-01.ibm.com/support/knowledgecenter/SSLTBW_1.13.0/com.ibm.zos.r13 .cbcux01/ascii.htm You should experiment with this option, it might get you closer to your goal. With it SQLITE_ASCII will be defined, not SQLITE_EBCDIC since 'A' in the code below (line 9576 in sqlite3.c) would be an ASCII 'A'. #if 'A' == '\301' # define SQLITE_EBCDIC 1 #else # define SQLITE_ASCII 1 #endif The origins of the build scripts below are fuzzy but I believe most of the options come from running configure and then copying out from the makefile. Some xlc specific options have been added from reading and trial and error. Compiling sqlite3.c: xlc \ -DPACKAGE_NAME=\"sqlite\" \ -DPACKAGE_TARNAME=\"sqlite\" \ -DPACKAGE_VERSION=\"3.9.2\" \ -DPACKAGE_STRING=\"sqlite\ 3.9.2\" \ -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; \ -DPACKAGE_URL=\"\" \ -DPACKAGE=\"sqlite\" \ -DVERSION=\"3.9.2\" \ -DSTDC_HEADERS=1 \ -DHAVE_SYS_TYPES_H=1 \ -DHAVE_SYS_STAT_H=1 \ -DHAVE_STDLIB_H=1 \ -DHAVE_STRING_H=1 \ -DHAVE_MEMORY_H=1 \ -DHAVE_STRINGS_H=1 \ -DHAVE_INTTYPES_H=1 \ -DHAVE_STDINT_H=1 \ -DHAVE_UNISTD_H=1 \ -DHAVE_DLFCN_H=1 \ -DLT_OBJDIR=\".libs/\" \ -DHAVE_USLEEP=1 \ -DHAVE_LOCALTIME_R=1 \ -DHAVE_GMTIME_R=1 \ -DHAVE_DECL_STRERROR_R=1 \ -DHAVE_STRERROR_R=1 \ -D_REENTRANT=1 \ -DSQLITE_THREADSAFE=1 \ -DSQLITE_ENABLE_FTS3 \ -DSQLITE_ENABLE_RTREE \ -O3 \ -I. -V \ -DSQLITE_OMIT_MERGE_SORT \ -DSQLITE_MAX_MMAPSIZE=1048576 \ -qTARG=zOSV1R11 \ -q64 \ -qstrict \ -qLANG=EXTENDED \ -qFLOAT=IEEE \ -qnolist \ -qnosource \ -D_POSIX_C_SOURCE=200112L \ -D_XOPEN_SOURCE=600 \ -c \ -o sqlite3.o sqlite3.c Compiling shell.c: xlc \ -DPACKAGE_NAME=\"sqlite\" \ -DPACKAGE_TARNAME=\"sqlite\" \ -DPACKAGE_VERSION=\"3.9.2\" \ -DPACKAGE_STRING=\"sqlite\ 3.9.2\" \ -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; \ -DPACKAGE_URL=\"\" \ -DPACKAGE=\"sqlite\" \ -DVERSION=\"3.9.2\" \ -DSTDC_HEADERS=1 \ -DHAVE_SYS_TYPES_H=1 \ -DHAVE_SYS_STAT_H=1 \ -DHAVE_STDLIB_H=1 \ -DHAVE_STRING_H=1 \ -DHAVE_MEMORY_H=1 \ -DHAVE_STRINGS_H=1 \ -DHAVE_INTTYPES_H=1 \ -DHAVE_STDINT_H=1 \ -DHAVE_UNISTD_H=1 \ -DHAVE_DLFCN_H=1 \ -DLT_OBJDIR=\".libs/\" \ -DHAVE_USLEEP=1 \ -DHAVE_LOCALTIME_R=1 \ -DHAVE_GMTIME_R=1 \ -DHAVE_DECL_STRERROR_R=1 \ -DHAVE_STRERROR_R=1 \ -D_REENTRANT=1 \ -DSQLITE_THREADSAFE=1 \ -DSQLITE_ENABLE_FTS3 \ -DSQLITE_ENABLE_RTREE \ -O3 \ -I. -V \ -DSQLITE_OMIT_MERGE_SORT \ -DSQLITE_MAX_MMAPSIZE=1048576 \ -qTARG=zOSV1R11 \ -q64 \ -qstrict \ -qLANG=EXTENDED \ -qFLOAT=IEEE \ -qnolist \ -qnosource \ -D_POSIX_C_SOURCE=200112L \ -D_XOPEN_SOURCE=600 \ -c \ -o shell.o shell.c Linking the two together for a sqlite3 executable: xlc -q64 -o sqlite3 shell.o sqlite3.o Hope this helps - Roland -Original Message- From: mbezzi [mailto:mbe...@tiscali.it] Sent: Wednesday, December 30, 2015 11:21 AM To: SQLite mailing list; rolandsmartin at gmail.com Subject: Re: [sqlite] Function patternCompare() not EBCDIC friendly Roland, I am pleased to see that you are successfully using SQLite on z/OS. Are you using the standard amalgamation? Did you need to apply special mods to SQLite to work on z/OS? Would you mind sharing your build procedure? Few months ago I tried compiling SQLite on z/OS: It worked but the data-bases generated on z/OS contains text data in EBCDIC format, including metadata, which makes those data-bases unusable on other platforms. The opposite was also true in my experience: Data-bases created on other platforms (I tried Linux) are not usable on z/OS for the very same reason. At that time I've spent some time trying to find where SQLite required to be modified to change this behavior, but this resulted a too hard assignment given my limited understanding of SQLite internals. Did you face the same issue? Did you find a solution? Anybody willing to help here giving guidance on what should be changed in SQLite to have text data and meta-data stored in UTF-8 format under z/OS? Thank you, mario On 12/30/2015 04:55 PM, Roland Martin wrote: > I have tested the code change on z/OS and it works. > > Thanks for the quick turnaround! > > Roland Martin > > -Original Message- > From: drhsqlite at gmail.com [mailto:drhsqlite at gmail.com] On Behalf Of > Richard Hipp > Sent: Wednesday, December 30, 2015 9:08 AM > To: SQLite mailing list > Cc: rolandsmartin at gmail.com > Subject: Re: [sqlite] Function patternCompare() not EBCDIC friendly > > On
[sqlite] A small patch for the SQLite shell in windows.
Hi,
I have a small patch for the SQLite Shell 3.9.2, it convert sql command text to
utf-8 before sending to SQLite engine,
And convert the result text back to default code page after sqlite3_exec.
Before patch( SQLite shell in windows 10 Chinese version):
SQLite version 3.9.2 2015-11-02 18:31:45
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select length('');
6
sqlite> select substr('',1,1);
sqlite> select substr('',2,1);
sqlite> select substr('',3,1);
sqlite> select substr('',4,1);
?
sqlite> select instr('','?');
3
After patch:
sqlite> select length('');
4
sqlite> select substr('',1,1);
?
sqlite> select substr('',2,1);
?
sqlite> select substr('',3,1);
?
sqlite> select substr('',4,1);
?
sqlite> select instr('','?');
2
sqlite>
---diff.txtcut
here---
817c817
< char **azArg,/* Text of each result column */
---
> char **azArgZ,/* Text of each result column */
821a822,842
> #if defined(_WIN32) || defined(WIN32)
> char** azArg = malloc(sizeof(char*)*nArg);
> for (i = 0;i < nArg;i++) {
> if (azArgZ[i] == 0)
> azArg[i] = 0;
> else {
> int nlen = MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1,
> NULL, 0);
> assert(nlen > 0);
> WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
> (void)MultiByteToWideChar(CP_UTF8, 0, azArgZ[i], -1, buff,
> nlen);
> int ilen = WideCharToMultiByte(CP_ACP, 0, buff, nlen, NULL,
> 0, NULL, NULL);
> assert(ilen > 0);
> char* buff1 = (char*)malloc(ilen);
> (void)WideCharToMultiByte(CP_ACP, 0, buff, nlen, buff1, ilen,
> NULL, NULL);
> azArg[i] = buff1;
> free(buff);
> }
> }
> #else
> char** azArg = azArgZ;
> #endif
1042a1064,1070
> #if defined(_WIN32) || defined(WIN32)
> for (i = 0;i < nArg;i++) {
> if (azArg[i])
> free(azArg[i]);
> }
> free(azArg);
> #endif
4255,4257c4283,4301
< BEGIN_TIMER;
< rc = shell_exec(p->db, zSql, shell_callback, p, );
< END_TIMER;
---
> #if defined(_WIN32) || defined(WIN32)
> int nlen = MultiByteToWideChar(CP_ACP, 0, zSql, -1, 0, 0);
> assert(nlen > 0);
> WCHAR* buff = (WCHAR*)malloc(nlen * sizeof(WCHAR));
> (void)MultiByteToWideChar(CP_ACP, 0, zSql, -1, buff, nlen);
> int ilen = WideCharToMultiByte(CP_UTF8, 0, buff, nlen, NULL, 0, NULL,
> NULL);
> assert(ilen > 0);
> char* buff1 = (char*)malloc(ilen);
> (void)WideCharToMultiByte(CP_UTF8, 0, buff, nlen, buff1, ilen, NULL,
> NULL);
> free(buff);
> BEGIN_TIMER;
> rc = shell_exec(p->db, buff1, shell_callback, p, );
> END_TIMER;
> free(buff1);
> #else
> BEGIN_TIMER;
> rc = shell_exec(p->db, zSql, shell_callback, p, );
> END_TIMER;
> #endif
cut
here--
[sqlite] FTS3 - Unexpected SELECT Results
I created a table?with the Porter tokenizer: "CREATE VIRTUAL TABLE fts_translations USING fts3(name, tokenize=porter)" Then I execute a select statement, which before?text substitution looks like this: SELECT [symbols].`id`, [symbols].`rid` FROM [symbols] ?INNER JOIN [symbol_translations] ON [symbol_translations].`symbol_id` = [symbols].`id` ?INNER JOIN [translations] ON [translations].`id` = [symbol_translations].`translation_id` ?INNER JOIN [fts_translations] ON [fts_translations].`docid` = [translations].`id` ?WHERE [fts_translations].`name` MATCH ?1 AND [translations].`locale_id` = ?2 When passing in "Actions1BP80" as the first argument (?1), the result contains three documents with the following values in the `name` column: "Actions1BP80", "Actions2BP80" and "ActionsAtoZBP80". While I expected the first, I didn't expect the other two. Why are they included in?the result? Thanks,G. Laubli
[sqlite] Magic number in sqlite source code
On 12/30/15, Richard Hipp wrote: > > I'll continue look for an alternative way to make the intent of the > code clearer. > See https://www.sqlite.org/src/info/1541607d458069f5 for another attempt at removing magic numbers. But I don't like it. It seems to complicate more than it clarifies. My current thinking is that the code should remain as it is on trunk. -- D. Richard Hipp drh at sqlite.org
[sqlite] Magic number in sqlite source code
On 12/30/15, Domingo Alvarez Duarte wrote: > Hello Richard ! > > I just saw this commit https://www.sqlite.org/src/info/6a4cfc7ab62046eb and > noticed you've been using magic numbers would it be better to use a macro > instead ? > > I think for other people (and maybe yourself) would be easier to see > something like "INFO_SIZE_ADJUST" (or any meaningful name) instead of "4". OVFL_PTR_SZ might be a better name. 4 is the size (in bytes) used by the pointer to the first overflow page that occurs at the end of the on-page record. I spent some time editing the code - substituting OVFL_PTR_SZ for 4 in appropriate places. But after looking at that for a while, I felt like OVFL_PTR_SZ obscured more than it clarified. So I typed "fossil revert" to go back to the code as it stands. I'll continue look for an alternative way to make the intent of the code clearer. > > > This also happen on other places, although I understand that is hard to > maintain consistency (there is several places where sqlite3 do use macros > instead of magic numbers). > > Cheers ! > in src/btree.c > > 1054 pInfo->nSize = (u16)(>pPayload[pInfo->nLocal] - pCell) + 4; > 1079 assert( pPage->childPtrSize==4 ); > 1083 pInfo->nSize = 4 + getVarint([4], (u64*)>nKey); > 1153 if( pInfo->nSize<4 ) pInfo->nSize = 4; > 1191 if( pInfo->nSize<4 ) pInfo->nSize = 4; > 1309 Pgno ovfl = get4byte([info.nSize-4]); > 3349 && iFrom==get4byte(pCell+info.nSize-4) > 5999 ovflPgno = get4byte(pCell + info.nSize - 4); > 6000 assert( pBt->usableSize > 4 ); > 6001 ovflPageSize = pBt->usableSize - 4; > 6858 Pgno ovfl = get4byte([info.nSize-4]); > 9164 assert( pc + info.nSize - 4 <= usableSize ); > 9166 pgnoOvfl = get4byte([info.nSize - 4]); > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Magic number in sqlite source code
On 2015-12-30 12:51 PM, Richard Hipp wrote: > On 12/30/15, Richard Hipp wrote: >> I'll continue look for an alternative way to make the intent of the >> code clearer. > > See https://www.sqlite.org/src/info/1541607d458069f5 for another > attempt at removing magic numbers. But I don't like it. It seems to > complicate more than it clarifies. My current thinking is that the > code should remain as it is on trunk. While kludgy itself, a possible compromise is to still use a named constant / macro but have '4' in the name of the macro, eg like 'SOME_FOO_4' where the SOME_FOO is a semblance of descriptive and the 4 says what the value is so you don't have to look it up. The key thing is that there may be multiple reasons to use the value 4 in a program and the named constant is illustrating which reason it is. If you change the value of the constant then you would also rename this particular constant to match the new value, but the key thing is you have something easily look-upable that shows all the 4 are connected. -- Darren Duncan
[sqlite] Function patternCompare() not EBCDIC friendly
On Wed, Dec 30, 2015 at 9:55 AM, Roland Martin wrote: > I have tested the code change on z/OS and it works. > ?Any chance that I could beg the source and executable code from you? > > Thanks for the quick turnaround! > > Roland Martin > > -- Computer Science is the only discipline in which we view adding a new wing to a building as being maintenance -- Jim Horning Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Function patternCompare() not EBCDIC friendly
I have tested the code change on z/OS and it works. Thanks for the quick turnaround! Roland Martin -Original Message- From: drhsqlite at gmail.com [mailto:drhsql...@gmail.com] On Behalf Of Richard Hipp Sent: Wednesday, December 30, 2015 9:08 AM To: SQLite mailing list Cc: rolandsmartin at gmail.com Subject: Re: [sqlite] Function patternCompare() not EBCDIC friendly On 12/30/15, Roland Martin wrote: > Working with version 3.9.2 on IBM z/OS case insensitive LIKE queries > do not work if case does not match. Please test and let us know if the https://www.sqlite.org/src/info/0a99a8c4facf65ec check-in fixes your problem. -- D. Richard Hipp drh at sqlite.org
[sqlite] Date as integer
??
2015-12-30 2:56 GMT+01:00 Richard Hipp :
> On 12/29/15, Cecil Westerhof wrote:
> > I first had the following table:
> > CREATE TABLE simpleLog (
> >datetimeTEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
> >description TEXT NOT NULL
> > )
> >
> > ?But datetime then takes 19 bytes. I understood you can also use an
> Integer
> > or Real and that this should be more efficient. At the moment I have the
> > following (I do not expect more as one record in a second):
> > CREATE TABLE simpleLog (
> >datetimeINT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
> >description TEXT NOT NULL
> > )
> >
> > And a select is then done by (in my select minute is precision enough):
> > SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime')
> as
> > datetime
> > ,description
> > FROM simpleLog
> > ORDER BY datetime DESC
> >
> > Is this a good way to go, or is there a better way?
>
> What you have should work well.
>
> If you store the date/times as a floating-point Julian Day Number, you
> can omit the 'unixepoch' on query. Use julianday('now') instead of
> strftime('%s','now') on the DEFAULT. That seems a little simpler to
> me, and you get millisecond resolution on the date/times instead of
> just second resolution. But the unix-time format is more familar to
> many programmers, and can be stored in 4 bytes instead of 8.
>
?A resolution of one second is more as enough in this case and Integer is
more efficient as Real. So that is why I choose this solution. If I need a
finer resolution I always can redefine?
?the table. Thanks for the feedback.
--
Cecil Westerhof
[sqlite] Function patternCompare() not EBCDIC friendly
On 12/30/15, Roland Martin wrote: > Working with version 3.9.2 on IBM z/OS case insensitive LIKE queries do not > work if case does not match. Please test and let us know if the https://www.sqlite.org/src/info/0a99a8c4facf65ec check-in fixes your problem. -- D. Richard Hipp drh at sqlite.org
[sqlite] A small patch for the SQLite shell in windows.
Please test the alternative patch found at https://www.sqlite.org/src/info/a0a08b8c0bbd4d71 and let me know whether or not the alternative patch fixes your problem. On 12/30/15, Quan Yong Zhai wrote: >>From: Richard Hipp >>Sent: 2015?12?30? 20:21 >>To: SQLite mailing list >>Subject: Re: [sqlite] A small patch for the SQLite shell in windows. > >>Please send a unified diff, generated using the -u option to the >>"diff" command. Even better would be the "-U 8" option, to include >>more context. > > diff -U 8 src/shell.c /home/nana/shell.c > diff.txt--cut here-- -- D. Richard Hipp drh at sqlite.org
[sqlite] Function patternCompare() not EBCDIC friendly
Working with version 3.9.2 on IBM z/OS case insensitive LIKE queries do not
work if case does not match. The following #if defined(SQLITE_EBCDIC) is
important:
/*
** For LIKE and GLOB matching on EBCDIC machines, assume that every
** character is exactly one byte in size. Also, provde the Utf8Read()
** macro for fast reading of the next character in the common case where
** the next character is ASCII.
*/
#if defined(SQLITE_EBCDIC)
# define sqlite3Utf8Read(A)(*((*A)++))
# define Utf8Read(A) (*(A++))
#else
# define Utf8Read(A) (A[0]<0x80?*(A++):sqlite3Utf8Read())
#endif
Within patternCompare() there are the following checks:
/* "[...]" immediately follows the "*". We have to do a slow
** recursive search in this case, but it is an unusual case. */
assert( matchOther<0x80 ); /* '[' is a single-byte character */
.
.
.
/* At this point variable c contains the first character of the
** pattern string past the "*". Search in the input string for the
** first matching character and recursively contine the match from
** that point.
**
** For a case-insensitive search, set variable cx to be the same as
** c but in the other case and search the input string for either
** c or cx.
*/
if( c<=0x80 ){
.
.
.
if( noCase && c<0x80 && c2<0x80 && sqlite3Tolower(c)==sqlite3Tolower(c2) ){
continue;
}
Since EBCDIC character values are mostly >0x80 these checks can produce
invalid results on IBM z/OS.
Thanks for the help.
Roland Martin
[sqlite] A small patch for the SQLite shell in windows.
On 12/30/15, Quan Yong Zhai wrote: > Hi, > I have a small patch for the SQLite Shell 3.9.2, it convert sql command text > to utf-8 before sending to SQLite engine, Please send a unified diff, generated using the -u option to the "diff" command. Even better would be the "-U 8" option, to include more context. -- D. Richard Hipp drh at sqlite.org
[sqlite] Date as integer
I first had the following table:
CREATE TABLE simpleLog (
datetimeTEXT NOT NULL PRIMARY KEY DEFAULT CURRENT_TIMESTAMP,
description TEXT NOT NULL
)
?But datetime then takes 19 bytes. I understood you can also use an Integer
or Real and that this should be more efficient. At the moment I have the
following (I do not expect more as one record in a second):
CREATE TABLE simpleLog (
datetimeINT NOT NULL PRIMARY KEY DEFAULT (strftime('%s')),
description TEXT NOT NULL
)
And a select is then done by (in my select minute is precision enough):
SELECT strftime('%Y-%m-%d %H:%M', datetime, 'unixepoch', 'localtime') as
datetime
,description
FROM simpleLog
ORDER BY datetime DESC
Is this a good way to go, or is there a better way?
--
Cecil Westerhof
[sqlite] Documentation Typo in FTS Example
>From https://sqlite.org/fts3.html: CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d); CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c); INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f'); INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l'); The two inserts will fail, since t2 has 5 columns but 4 values were supplied.
