[sqlite] LSM INT key problem

[ingo]

Both SQlite.exe and lsm.dll are compiled on Win10 with Mingwin64.
Lsm by copying sqlite3.h and sqlite3ext.h to the lsm1 directory and
then: make lsm.so TCCX="gcc -g -O2" and rename to lsm.dll

Creating a lsm table with an INT key results in the following:

SQLite version 3.28.0 2019-04-16 19:49:53
[...]
sqlite> .load lsm
sqlite> CREATE VIRTUAL TABLE test USING lsm1 (
   ...>   'test.lsm', idx, INT, d
   ...> );
Error: key type should be INT, TEXT, or BLOB

sqlite> CREATE VIRTUAL TABLE test USING lsm1 (
   ...>   'test.lsm', idx, INTEGER, d
   ...> );
Error: key type should be INT, TEXT, or BLOB

Using TXT or BLOB there is no error. Did I go wrong somewhere?

Ingo

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [O] A strange problem with org-babel and SQLite

[Cecil Westerhof]

Oops, wrong group. Sorry. :'-(

2018-08-31 12:21 GMT+02:00 Cecil Westerhof :

> 2018-08-31 11:17 GMT+02:00 Robert Klein :
>
>> Hi Cecil,
>>
>> On Fri, 31 Aug 2018 10:47:50 +0200
>> Cecil Westerhof  wrote:
>>
>> > I have a strange problem with org-babel and SQLite.
>> >
>> > I have a database that is created with:
>> > CREATE TABLE "quotes" (
>> > quoteID TEXT   PRIMARY KEY,
>> > quote   TEXT NOT NULL  UNIQUE,
>> > lastUsedTEXT,
>> > totalUsed   INT  DEFAULT 'unused'
>> > )
>> >
>> > When using:
>> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
>> > SELECT   lastUsed
>> > ,totalUsed
>> > FROM quotes
>> > ORDER BY lastused  ASC
>> > ,totalUsed DESC
>> > LIMIT40
>> > #+END_SRC
>> >
>> > Everything is fine. But when I use (add the quote field in the
>> > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
>> > SELECT   quote
>> > ,lastUsed
>> > ,totalUsed
>> > FROM quotes
>> > ORDER BY lastused  ASC
>> > ,totalUsed DESC
>> > LIMIT40
>> > #+END_SRC
>> >
>> > I get:
>> > executing Sqlite code block...
>> > Wrote /tmp/babel-27920y_/ob-input-2792BTG
>> > org-babel-read: End of file during parsing
>> >
>> > What could be the problem?
>> >
>>
>> does it work outside of org/babel/emacs, that is, when you use the
>> query in a command line sqlite session, does it work?  “quote” is also
>> a function in sqlite, so this might be your issue.
>>
>
> Yes, in sqlite3 and sqlitebrowser it works without problems.
> In org-babel even 'SELECT *' goes wrong.
>
> --
> Cecil Westerhof
>



-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [O] A strange problem with org-babel and SQLite

[Cecil Westerhof]

2018-08-31 11:17 GMT+02:00 Robert Klein :

> Hi Cecil,
>
> On Fri, 31 Aug 2018 10:47:50 +0200
> Cecil Westerhof  wrote:
>
> > I have a strange problem with org-babel and SQLite.
> >
> > I have a database that is created with:
> > CREATE TABLE "quotes" (
> > quoteID TEXT   PRIMARY KEY,
> > quote   TEXT NOT NULL  UNIQUE,
> > lastUsedTEXT,
> > totalUsed   INT  DEFAULT 'unused'
> > )
> >
> > When using:
> > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > SELECT   lastUsed
> > ,totalUsed
> > FROM quotes
> > ORDER BY lastused  ASC
> > ,totalUsed DESC
> > LIMIT40
> > #+END_SRC
> >
> > Everything is fine. But when I use (add the quote field in the
> > select): #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
> > SELECT   quote
> > ,lastUsed
> > ,totalUsed
> > FROM quotes
> > ORDER BY lastused  ASC
> > ,totalUsed DESC
> > LIMIT40
> > #+END_SRC
> >
> > I get:
> > executing Sqlite code block...
> > Wrote /tmp/babel-27920y_/ob-input-2792BTG
> > org-babel-read: End of file during parsing
> >
> > What could be the problem?
> >
>
> does it work outside of org/babel/emacs, that is, when you use the
> query in a command line sqlite session, does it work?  “quote” is also
> a function in sqlite, so this might be your issue.
>

Yes, in sqlite3 and sqlitebrowser it works without problems.
In org-babel even 'SELECT *' goes wrong.

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 fossil repository problem solved

[Richard Hipp]

On 5/8/18, Domingo Alvarez Duarte  wrote:
>
> Still it seems that there is more than one fossil server and they seem
> to lag from the main one.
>

There are three.  They sync with each other hourly, via cron job.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 fossil repository problem solved

[Domingo Alvarez Duarte]

Hello !

I just retried again and now it got updated to the same content I can 
see on the web.



Still it seems that there is more than one fossil server and they seem 
to lag from the main one.



Cheers !

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I encounter a problem when build sqlite for iOS

[Jens Alfke]

If building for an Apple platform, it's much easier to just link with 
libSQLite3.dylib, as most apps do. It'll shrink your app binary by about 1MB 
too.

—Jens


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I encounter a problem when build sqlite for iOS

[Richard Hipp]

I have checked in changes (written by Dan) that should address your
concerns.  The next release of SQLite will probably run the Makefile
to completion without errors in your environment.

NB:  This is not an error in SQLite.  The problem is in a Makefile
such that the Makefile does not play well with your iOS sdk.  Or,
perhaps we should say that your sdk is using the SQLite Makefile in a
way that the Makefile was never intended to be used.  I am sorry it is
giving you trouble.  View this situation as an opportunity to expand
you skill set by learning to operate the Makefile from the
command-line.  This may be frustrating at first if you have never done
that kind of thing before, but it is a skill that will pay you
countless dividends throughout your career if you will take the time
to learn it now.

On 1/30/18, Kingle Zhuang <kingle.zhu...@ringcentral.com> wrote:
> Environment:
> Mac + Xcode9.2
>
> Steps:
> 1. Using sqlite build script to generate static libs & header files
> [Attachments: build-sqlite.zip]
> 2. Build Archs: i386, x86_64, armv7, armv7s, arm64
> 3. Run configure to generate makefile & using iOS sdk to build it.
>
> Many guys encountered such problem & still not found good solutions:
> https://github.com/sqlcipher/sqlcipher/issues/240
> https://github.com/cocos2d/cocos2d-x/issues/17907
> https://github.com/HaxeFlixel/flixel/issues/2112
>
>
> On 1/30/18, 10:30 PM, "drhsql...@gmail.com on behalf of Richard Hipp"
> <drhsql...@gmail.com on behalf of d...@sqlite.org> wrote:
>
> On 1/30/18, Kingle Zhuang <kingle.zhu...@ringcentral.com> wrote:
> > shell.c:7098:9: error: 'system' is unavailable: not available on iOS
> > x = system(zCmd);
>
> Is the command-line shell in any way useful to iOS?  Are you able to
> run the command-line shell on iOS?  Can you explain to us how you do
> that, because that is not something we developers know how to do.
>
>
> > ^
> >
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
> > note: 'system' has been explicitly marked unavailable here
> > int  system(const char *) __DARWIN_ALIAS_C(system);
> >  ^
> > 1 error generated.
> > make: *** [sqlite3-shell.o] Error 1
> > /Applications/Xcode.app/Contents/Developer/usr/bin/gcc -arch i386
> > -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\"
> > -DPACKAGE_VERSION=\"3.20.0\" -DPACKAGE_STRING=\"sqlite\ 3.20.0\"
> >
> -DPACKAGE_BUGREPORT=\"https://urldefense.proofpoint.com/v2/url?u=http-3A__www.sqlite.org=DwIBaQ=fxtm8VyUXgCVDKhi9yIVsVsjq1ocZ-LZVehzdzjopw0=-h5cCTUebAX5iBiy5e8p2yBktDE1lF2eG68a_tt_aQw=OT8nseoReS0uIgwGyYrCRQCUxUTM-fBKQt63TH_9DaQ=G4xhtVG-js9npBS1WwIo2CfizAJFz9SuHaaaz_zCPII=\;
> -DPACKAGE_URL=\"\"
> > -DPACKAGE=\"sqlite\" -DVERSION=\"3.20.0\" -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_FDATASYNC=1
> > -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1
> > -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -I.   -I/include
> -isysroot
> >
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
> > -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3
> > -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -Os
> > -DSQLITE_ENABLE_LOCKING_STYLE=1 -DSQLITE_ENABLE_API_ARMOR
> > -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_MAX_MMAP_SIZE=0
> > -DSQLITE_OMIT_AUTORESET -DSQLITE_OMIT_BUILTIN_TEST
> > -DSQLITE_OMIT_LOAD_EXTENSION -I/include -isysroot
> >
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
> > -MT sqlite3-shell.o -MD -MP -MF .deps/sqlite3-shell.Tpo -c -o
> > sqlite3-shell.o `test -f 'shell.c' || echo './'`shell.c
> > shell.c:7098:9: error: 'system' is unavailable: not available on iOS
> > x = system(zCmd);
> > ^
> >
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
> > note: 'system' has been explicitly marked unavailable here
> > int  system(const char *) __DARWIN_ALIAS_C(system);
> >  ^
> > 1 error gene

Re: [sqlite] I encounter a problem when build sqlite for iOS

[Richard Hipp]

On 1/30/18, Kingle Zhuang  wrote:
> shell.c:7098:9: error: 'system' is unavailable: not available on iOS
> x = system(zCmd);

Is the command-line shell in any way useful to iOS?  Are you able to
run the command-line shell on iOS?  Can you explain to us how you do
that, because that is not something we developers know how to do.


> ^
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
> note: 'system' has been explicitly marked unavailable here
> int  system(const char *) __DARWIN_ALIAS_C(system);
>  ^
> 1 error generated.
> make: *** [sqlite3-shell.o] Error 1
> /Applications/Xcode.app/Contents/Developer/usr/bin/gcc -arch i386
> -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\"
> -DPACKAGE_VERSION=\"3.20.0\" -DPACKAGE_STRING=\"sqlite\ 3.20.0\"
> -DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\"
> -DPACKAGE=\"sqlite\" -DVERSION=\"3.20.0\" -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_FDATASYNC=1
> -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1
> -DHAVE_DECL_STRERROR_R=1 -DHAVE_STRERROR_R=1 -I.   -I/include -isysroot
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
> -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3
> -DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -Os
> -DSQLITE_ENABLE_LOCKING_STYLE=1 -DSQLITE_ENABLE_API_ARMOR
> -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_MAX_MMAP_SIZE=0
> -DSQLITE_OMIT_AUTORESET -DSQLITE_OMIT_BUILTIN_TEST
> -DSQLITE_OMIT_LOAD_EXTENSION -I/include -isysroot
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
> -MT sqlite3-shell.o -MD -MP -MF .deps/sqlite3-shell.Tpo -c -o
> sqlite3-shell.o `test -f 'shell.c' || echo './'`shell.c
> shell.c:7098:9: error: 'system' is unavailable: not available on iOS
> x = system(zCmd);
> ^
> /Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
> note: 'system' has been explicitly marked unavailable here
> int  system(const char *) __DARWIN_ALIAS_C(system);
>  ^
> 1 error generated.
> make: *** [sqlite3-shell.o] Error 1
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I encounter a problem when build sqlite for iOS

[Kingle Zhuang]

shell.c:7098:9: error: 'system' is unavailable: not available on iOS
x = system(zCmd);
^
/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
 note: 'system' has been explicitly marked unavailable here
int  system(const char *) __DARWIN_ALIAS_C(system);
 ^
1 error generated.
make: *** [sqlite3-shell.o] Error 1
/Applications/Xcode.app/Contents/Developer/usr/bin/gcc -arch i386 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.20.0\" -DPACKAGE_STRING=\"sqlite\ 3.20.0\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE_URL=\"\" 
-DPACKAGE=\"sqlite\" -DVERSION=\"3.20.0\" -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_FDATASYNC=1 -DHAVE_USLEEP=1 
-DHAVE_LOCALTIME_R=1 -DHAVE_GMTIME_R=1 -DHAVE_DECL_STRERROR_R=1 
-DHAVE_STRERROR_R=1 -I.   -I/include -isysroot 
/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
 -D_REENTRANT=1 -DSQLITE_THREADSAFE=1 -DSQLITE_ENABLE_FTS3 
-DSQLITE_ENABLE_RTREE -DSQLITE_ENABLE_EXPLAIN_COMMENTS -Os 
-DSQLITE_ENABLE_LOCKING_STYLE=1 -DSQLITE_ENABLE_API_ARMOR 
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_MAX_MMAP_SIZE=0 
-DSQLITE_OMIT_AUTORESET -DSQLITE_OMIT_BUILTIN_TEST -DSQLITE_OMIT_LOAD_EXTENSION 
-I/include -isysroot 
/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk
 -MT sqlite3-shell.o -MD -MP -MF .deps/sqlite3-shell.Tpo -c -o sqlite3-shell.o 
`test -f 'shell.c' || echo './'`shell.c
shell.c:7098:9: error: 'system' is unavailable: not available on iOS
x = system(zCmd);
^
/Applications/Xcode.app/Contents/Developer/Platforms/iPhoneSimulator.platform/Developer/SDKs/iPhoneSimulator11.2.sdk/usr/include/stdlib.h:195:6:
 note: 'system' has been explicitly marked unavailable here
int  system(const char *) __DARWIN_ALIAS_C(system);
 ^
1 error generated.
make: *** [sqlite3-shell.o] Error 1
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unlikely data security problem in sqlite_stat tables

[Simon Slavin]

The problem occurs only with non-default compilation settings, and depends on 
an incorrect assumption by the programmer, so it is unlikely that it would 
cause a problem under normal circumstances.  It is related to the following 
commands:

DROP TABLE TableName — removes related rows from sqlite_stat tables
DELETE FROM TableName — does not remove related rows from sqlite_stat tables
VACUUM — does not modify sqlite_stat tables

Given compilation setting SQLITE_ENABLE_STAT4 (which is not the default), 
there’s a possible data security problem relating to sqlite_stat3 and 
sqlite_stat4 tables.  These tables include columns named "sample" which contain 
copies of data from tables.

If a TABLE is DROPped from the database, then rows related to it are removed 
from the sqlite_stat tables.  This means there is no security problem.  (I’ve 
just realised I didn’t check that the same was done when DROPping individual 
INDEXes.)

However there is a convenient command, which sqlite has optimizations for, 
which has a similar effect.  This is the use of the DELETE FROM command with no 
WHERE clause.  This removes all data from the table, but it leaves any "sample" 
values in sqlite_stat tables.  A programmer who did the following

DELETE FROM HomePhoneNumbers;
VACUUM;

might reasonably think that this would remove all copies of the data from the 
database file.  Especially if they did the VACUUM with this specific objective.

If this is considered a security matter then a modification to the optimized 
DELETE without WHERE clause command, or to VACUUM, could remove the problem.  
I’m sure the dev team can pick an appropriate way to do it.  Or perhaps the 
documentation should remind people that DELETE without WHERE is just another 
DELETE command, and does not necessarily remove all copies of data from the 
database file.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Leona Struckhoff]

Yah! when i did the .open leona.rmgc and then entered pragma 
integrity_check;I got OK 
thank you

  From: Jens Alfke <j...@mooseyard.com>
 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 
 Sent: Tuesday, May 23, 2017 5:55 PM
 Subject: Re: [sqlite] Is this a problem?
   

> On May 23, 2017, at 3:31 PM, Leona Struckhoff <moma...@yahoo.com> wrote:
> 
> I placed the Leona.rmcg file in the same folder as the sqlite3.exeI double 
> clicked on sqlite3.exeI noticed my comand prompt had sqlite and not sqlite3.  
> Your examples below have sqlite3.
> I entered the following:sqlite> sqlite3 Leona.rmgc  ...> PRAGMA 
> integrity_check;Error: near "sqlite3": syntax errorsqlite> sqlite3 
> [Leona.rmgc]  ...> PRAGMA integrity_check;Error: near "sqlite3": syntax 
> errorsqlite>

It’s been a long time since I used Windows, but I think that if you 
double-click the EXE you will be running the SQLite command interpreter in the 
window that comes up. So you don’t need to use the “sqlite3” command. 

Instead, type “.open” (with a period!), then a space, and then the full path to 
the database file. That should open the database. Then you can type “pragma 
integrity_check;”.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Jens Alfke]

> On May 23, 2017, at 3:31 PM, Leona Struckhoff  wrote:
> 
> I placed the Leona.rmcg file in the same folder as the sqlite3.exeI double 
> clicked on sqlite3.exeI noticed my comand prompt had sqlite and not sqlite3.  
> Your examples below have sqlite3.
> I entered the following:sqlite> sqlite3 Leona.rmgc   ...> PRAGMA 
> integrity_check;Error: near "sqlite3": syntax errorsqlite> sqlite3 
> [Leona.rmgc]   ...> PRAGMA integrity_check;Error: near "sqlite3": syntax 
> errorsqlite>

It’s been a long time since I used Windows, but I think that if you 
double-click the EXE you will be running the SQLite command interpreter in the 
window that comes up. So you don’t need to use the “sqlite3” command. 

Instead, type “.open” (with a period!), then a space, and then the full path to 
the database file. That should open the database. Then you can type “pragma 
integrity_check;”.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Virgilio Fornazin]

The command prompt here means your CMD.EXE (Windows) or *nix shell... you
must use sqlite3.exe [dbfile] to get the sqlite shell to use PRAGMA
integrity_check;

*command prompt>* sqlite3 [database filename]
sqlite3> PRAGMA integrity_check;
sqlite3> .quit

On Tue, May 23, 2017 at 7:40 PM, Leona Struckhoff <moma...@yahoo.com> wrote:

> Rootsmagic is a free geneology software, so you can not enter direct
> commands.  They have a integrity check option in their database tools.  And
> it comes back ok.  If there is a command line option, I have not found it
> yet.
> LOL..My monitor is not upside down and I could read it.
>
>
>   From: Keith Medcalf <kmedc...@dessus.com>
>  To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>  Sent: Tuesday, May 23, 2017 5:00 PM
>  Subject: Re: [sqlite] Is this a problem?
>
>
> I would suggest following up with RootMagic and see if they have
> overloaded one of the builtin collation sequences (NOCASE comes to mind)
> with something else -- perhaps something that knows how to do Case
> Insensitive and Accent Insensitive across the entire Unicode spectrum.
>
> Can you issue direct SQL Queries to RootMagic, or does it only use
> "pre-canned" queries (pre-canned includes anything where you cannot type an
> SQL statement to execute) such as QBE interfaces.
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of moma...@yahoo.com
> > Sent: Tuesday, 23 May, 2017 11:08
> > To: sqlite-users@mailinglists.sqlite.org
> > Subject: [sqlite] Is this a problem?
> >
> > I am new to SQite.  I am not new to queries. I am a 28 year QA Tester. I
> > have been doing queries and macros for the past 25 years.
> >
> > I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan
> > 2017)database in SQLite Spy (Version 1.9.11 Win32):
> >
> > --integritycheck.sql
> > PRAGMA integrity_check;
> >
> > I expected to get an OK to be returned.
> >
> > I actually got the following results:
> > multiple rows of “row ??? missing from index idsSourceTemplateName”
> > multiple rows of “row ??? missing from index idxPlaceName”
> > multiple rows of “row ??? missing from index idxSurnameGiven”
> > “row ??? missing from idx Surname”
> > Multiple rows of “row ??? missing from index idxGiven”
> >
> > Yet when I do an integrity check in Rootsmagic 7, it comes back OK.
> > I am kinda confused. RootsMagic says it is ok but SQLite says there are
> > problems.
> >
> > OR, am I doing something wrong in SQLite?
> >
> > If I am not doing anything wrong in SQLite, how do I fix this?
> > If I fix this in SQLite, now I am afraid I won’t get an ok in RootsMagic
> > on their integrity check.
> >
> >
> > Sent from Mail for Windows 10
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Leona Struckhoff]

Rootsmagic is a free geneology software, so you can not enter direct commands.  
They have a integrity check option in their database tools.  And it comes back 
ok.  If there is a command line option, I have not found it yet.
LOL..My monitor is not upside down and I could read it.


  From: Keith Medcalf <kmedc...@dessus.com>
 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 
 Sent: Tuesday, May 23, 2017 5:00 PM
 Subject: Re: [sqlite] Is this a problem?
   

I would suggest following up with RootMagic and see if they have overloaded one 
of the builtin collation sequences (NOCASE comes to mind) with something else 
-- perhaps something that knows how to do Case Insensitive and Accent 
Insensitive across the entire Unicode spectrum.

Can you issue direct SQL Queries to RootMagic, or does it only use "pre-canned" 
queries (pre-canned includes anything where you cannot type an SQL statement to 
execute) such as QBE interfaces.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of moma...@yahoo.com
> Sent: Tuesday, 23 May, 2017 11:08
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Is this a problem?
> 
> I am new to SQite.  I am not new to queries. I am a 28 year QA Tester. I
> have been doing queries and macros for the past 25 years.
> 
> I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan
> 2017)database in SQLite Spy (Version 1.9.11 Win32):
> 
> --integritycheck.sql
> PRAGMA integrity_check;
> 
> I expected to get an OK to be returned.
> 
> I actually got the following results:
> multiple rows of “row ??? missing from index idsSourceTemplateName”
> multiple rows of “row ??? missing from index idxPlaceName”
> multiple rows of “row ??? missing from index idxSurnameGiven”
> “row ??? missing from idx Surname”
> Multiple rows of “row ??? missing from index idxGiven”
> 
> Yet when I do an integrity check in Rootsmagic 7, it comes back OK.
> I am kinda confused. RootsMagic says it is ok but SQLite says there are
> problems.
> 
> OR, am I doing something wrong in SQLite?
> 
> If I am not doing anything wrong in SQLite, how do I fix this?
> If I fix this in SQLite, now I am afraid I won’t get an ok in RootsMagic
> on their integrity check.
> 
> 
> Sent from Mail for Windows 10
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Leona Struckhoff]

I downloaded sqlite-tools-win32-x86-319.zip from this site: 
https://sqlite.org/download.html
I unzipped it.sqldiff.exe, sqlite3.exe and sqlite3_analyzer.exe were placed in 
the folder
I placed the Leona.rmcg file in the same folder as the sqlite3.exeI double 
clicked on sqlite3.exeI noticed my comand prompt had sqlite and not sqlite3.  
Your examples below have sqlite3.
I entered the following:sqlite> sqlite3 Leona.rmgc   ...> PRAGMA 
integrity_check;Error: near "sqlite3": syntax errorsqlite> sqlite3 [Leona.rmgc] 
  ...> PRAGMA integrity_check;Error: near "sqlite3": syntax errorsqlite>
I wasn't sure if i needed the [ ] around the file name so it did it with and 
without.  I got the same results.


  From: Simon Slavin <slav...@bigfraud.org>
 To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 
 Sent: Tuesday, May 23, 2017 4:32 PM
 Subject: Re: [sqlite] Is this a problem?
   

On 23 May 2017, at 6:07pm, moma...@yahoo.com wrote:
> 
> I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan 
> 2017)database in SQLite Spy (Version 1.9.11 Win32):

Neither of these matter.  Neither of these tell you which version of SQLite 
they’re running.  However, the fact that one of them reports corruption does 
suggest there’s something worth investigating.

Please download the SQLite shell tool from "Precompiled Binaries" for your 
platform on the SQLite download page:

<https://sqlite.org/download.html>

Instructions for use here:

<https://sqlite.org/cli.html>

But basically …

command prompt> sqlite3 [database filename]
sqlite3> PRAGMA integrity_check;
sqlite3> .quit

Use this tool to execute the pragma you were using: "PRAGMA integrity_check;".  
If it reports your database as corrupt it is definitely corrupt.  If not, 
you’re okay.

At that point you had better contact RootMagic for support.  SQLite can make an 
uncorrupt database from yours, but it might lose data because you don’t know 
what data was lost or overwritten.  RootMagic support are best placed to 
investigate what went wrong and tell you what to do about it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Keith Medcalf]

I would suggest following up with RootMagic and see if they have overloaded one 
of the builtin collation sequences (NOCASE comes to mind) with something else 
-- perhaps something that knows how to do Case Insensitive and Accent 
Insensitive across the entire Unicode spectrum.

Can you issue direct SQL Queries to RootMagic, or does it only use "pre-canned" 
queries (pre-canned includes anything where you cannot type an SQL statement to 
execute) such as QBE interfaces.

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of moma...@yahoo.com
> Sent: Tuesday, 23 May, 2017 11:08
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] Is this a problem?
> 
> I am new to SQite.  I am not new to queries. I am a 28 year QA Tester. I
> have been doing queries and macros for the past 25 years.
> 
> I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan
> 2017)database in SQLite Spy (Version 1.9.11 Win32):
> 
> --integritycheck.sql
> PRAGMA integrity_check;
> 
> I expected to get an OK to be returned.
> 
> I actually got the following results:
> multiple rows of “row ??? missing from index idsSourceTemplateName”
> multiple rows of “row ??? missing from index idxPlaceName”
> multiple rows of “row ??? missing from index idxSurnameGiven”
> “row ??? missing from idx Surname”
> Multiple rows of “row ??? missing from index idxGiven”
> 
> Yet when I do an integrity check in Rootsmagic 7, it comes back OK.
> I am kinda confused. RootsMagic says it is ok but SQLite says there are
> problems.
> 
> OR, am I doing something wrong in SQLite?
> 
> If I am not doing anything wrong in SQLite, how do I fix this?
> If I fix this in SQLite, now I am afraid I won’t get an ok in RootsMagic
> on their integrity check.
> 
> 
> Sent from Mail for Windows 10
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this a problem?

[Simon Slavin]

On 23 May 2017, at 6:07pm, moma...@yahoo.com wrote:
> 
> I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan 
> 2017)database in SQLite Spy (Version 1.9.11 Win32):

Neither of these matter.  Neither of these tell you which version of SQLite 
they’re running.  However, the fact that one of them reports corruption does 
suggest there’s something worth investigating.

Please download the SQLite shell tool from "Precompiled Binaries" for your 
platform on the SQLite download page:



Instructions for use here:



But basically …

command prompt> sqlite3 [database filename]
sqlite3> PRAGMA integrity_check;
sqlite3> .quit

Use this tool to execute the pragma you were using: "PRAGMA integrity_check;".  
If it reports your database as corrupt it is definitely corrupt.  If not, 
you’re okay.

At that point you had better contact RootMagic for support.  SQLite can make an 
uncorrupt database from yours, but it might lose data because you don’t know 
what data was lost or overwritten.  RootMagic support are best placed to 
investigate what went wrong and tell you what to do about it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Is this a problem?

[momakid]

I am new to SQite.  I am not new to queries. I am a 28 year QA Tester. I have 
been doing queries and macros for the past 25 years.

I ran the following query over my RootMagic 7 (Version 7.22.3.0 – 24 Jan 
2017)database in SQLite Spy (Version 1.9.11 Win32):

--integritycheck.sql
PRAGMA integrity_check;

I expected to get an OK to be returned.

I actually got the following results:
multiple rows of “row ??? missing from index idsSourceTemplateName”
multiple rows of “row ??? missing from index idxPlaceName”
multiple rows of “row ??? missing from index idxSurnameGiven”
“row ??? missing from idx Surname”
Multiple rows of “row ??? missing from index idxGiven”

Yet when I do an integrity check in Rootsmagic 7, it comes back OK.
I am kinda confused. RootsMagic says it is ok but SQLite says there are 
problems.

OR, am I doing something wrong in SQLite?

If I am not doing anything wrong in SQLite, how do I fix this?
If I fix this in SQLite, now I am afraid I won’t get an ok in RootsMagic on 
their integrity check.


Sent from Mail for Windows 10

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem or not problem around 'begin immediate'

[Keith Medcalf]

This is a bug in Python, or more correctly it is a luser-error (error code 1D 
10T).

If you wish to control transactions explicitly, you need to open the connection 
with isolation_level=None.

Otherwise, the sqlite3 (pysqlite) library issues "automagically created" 
"begin" statements for you (oftentimes incorrectly).  For example, the 1D 10T 
talk of not opening transactions for "read only" operations, which is not 
correct.  Even read only operations are contained within a transaction, it may 
just be a transaction that you do not explicitly start or commit.  So, if you 
issue an explicit "BEGIN" statement when operating in automagical mode (that 
is, when isolation_level is any value OTHER THAN None), you may experience 
error AHBL.

This has nothing to do with SQLite itself, it is a 1D 10T error in the 
interface module design.

(the error thrown in correct -- you cannot start a transaction within a 
transaction)

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Big Stone
> Sent: Friday, 25 November, 2016 12:27
> To: sqlite-users@mailinglists.sqlite.org
> Subject: [sqlite] problem or not problem around 'begin immediate'
> 
> there is some discussion about an issue-or-not-an-issue on bugs.python.org
> 
> https://bugs.python.org/issue28518
> 
> #
> 
> conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
> conn.execute('begin immediate')
> 
> Throws:
> 
> sqlite3.OperationalError: cannot start a transaction within a transaction
> 
> This didn't happen in previous versions
> 
> #
> Maybe a sqlite developer comment would help settle the case ?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem or not problem around 'begin immediate'

[Roger Binns]

On 25/11/16 12:02, Richard Hipp wrote:
> Rather, I presume
> that Python has recently started using the sqlite3_stmt_readonly()
> interface in a new way.

The bigger picture may be helpful.  There is a third party module
developed under the name "pysqlite" which has a long and storied
history.  At some point a copy was folded into Python as a module named
"sqlite3".  There are periodic copies of code changes between the two.

Python has a database API specification named DB-API (PEP 249).  This
mandates common behaviour no matter what the underlying database.
Transactions are expected to be started automatically, committed
automatically (under some circumstances I think), and commit / rollback
are methods on a cursor object.  This presumably matches how Postgres,
Oracle etc function.

Since SQLite doesn't work that way, the pysqlite authors did it
manually.  The execution code would manually parse each statement,
determine what kind of statement it was (makes changes means silently
start a transaction) and behave appropriately.  Needless to say, parsing
statements had various bugs.  Eventually they decided to use
sqlite3_stmt_readonly() instead of parsing, which is how the current
situation arose.  ie the API is used to try and simulate the behaviour
of other databases.

pysqlite does have an option (off by default) to avoid all this silent
transaction stuff.

Roger



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem or not problem around 'begin immediate'

[Richard Hipp]

On 11/25/16, Richard Hipp  wrote:
> Clarification will be checked in shortly and will appear in the 3.16.0
> release.

FWIW, the documentation clarification change is here:
https://www.sqlite.org/src/timeline?c=a4205a83

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] problem or not problem around 'begin immediate'

[Richard Hipp]

On 11/25/16, Big Stone  wrote:
> there is some discussion about an issue-or-not-an-issue on bugs.python.org
>
> https://bugs.python.org/issue28518
>
> #
>
> conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
> conn.execute('begin immediate')
>
> Throws:
>
> sqlite3.OperationalError: cannot start a transaction within a transaction
>
> This didn't happen in previous versions
>
> #
> Maybe a sqlite developer comment would help settle the case ?

I don't have a login for the python bug tracker so I cannot comment
there.  But I think I see the problem.  This is as Aviv Polivoda
remarks at https://bugs.python.org/issue28518#msg279808

I think this is a error in sqlite as the documentation says:
"ransaction control statements such as BEGIN, COMMIT, ROLLBACK,
SAVEPOINT, and RELEASE cause sqlite3_stmt_readonly() to return true,"

Except it is not a bug in SQLite, but rather an ambiguity in the
documentation.  In his quote, Aviv omitted the second clause from the
documentation:  "since the statements themselves do not actually
modify the database but rather they control the timing of when other
statements modify the database."  (Full text here:
https://www.sqlite.org/c3ref/stmt_readonly.html)

For a plain BEGIN statement, there are no changes to the database
file, so sqlite3_stmt_readonly() does indeed return TRUE.  But for a
BEGIN IMMEDIATE statement, there are database changes, because the
extra IMMEDIATE keyword causes the statement to go ahead and start
transaction "immediately".

So technically, the documentation is correct, though I can certainly
understand that it is misleading and ambiguous as currently worded.
Clarification will be checked in shortly and will appear in the 3.16.0
release.

Note also that behavior of sqlite3_stmt_readonly() has not changed
since that interface was first added for the 3.7.5 release on
2011-02-01.  So this is not an SQLite regression.  Rather, I presume
that Python has recently started using the sqlite3_stmt_readonly()
interface in a new way.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] problem or not problem around 'begin immediate'

[Big Stone]

there is some discussion about an issue-or-not-an-issue on bugs.python.org

https://bugs.python.org/issue28518

#

conn = sqlite3.connect(':memory:', isolation_level='IMMEDIATE')
conn.execute('begin immediate')

Throws:

sqlite3.OperationalError: cannot start a transaction within a transaction

This didn't happen in previous versions

#
Maybe a sqlite developer comment would help settle the case ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I solved the problem with gridview and database

[Tim Streater]

On 13 Jun 2016 at 14:15, jumper  wrote:

> Can someone please tell me how to stop the previous post so everyone
> doesn't see it anymore?
> thank you
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Follow the URL in the line above this one.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I solved the problem with gridview and database

[John McKown]

On Mon, Jun 13, 2016 at 8:15 AM, jumper  wrote:

> Can someone please tell me how to stop the previous post so everyone
> doesn't see it anymore?
> thank you
>
>
​This is a e-mail based forum. Your request is the equivalent of asking the
post office to "undeliver" an already delivered mass mailing. Can't be
done.​


-- 
The unfacts, did we have them, are too imprecisely few to warrant our
certitude.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I solved the problem with gridview and database

[R Smith]

You cannot remove a post once it is posted.

If you would like to unsubscribe so YOU do not see these replies 
anymore, you can do so by simply following the links at the bottom of 
every message.



On 2016/06/13 3:15 PM, jumper wrote:
Can someone please tell me how to stop the previous post so everyone 
doesn't see it anymore?

thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] I solved the problem with gridview and database

[jumper]

Can someone please tell me how to stop the previous post so everyone 
doesn't see it anymore?

thank you
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fossil sqlite clone problem

[Domingo Alvarez Duarte]

Thank you again for the answer !  

Now I understand the problem, maybe fossil could give an extra bit of info
when it's invoked like "fossil update" maybe something like:  

You are now at "branch_name" and it's behind "trunk" by X commits.  

?  
>  Tue Feb 09 2016 6:46:13 pm CET CET from "Richard Hipp"  
>  
>>Thanks for the answer !
>> 
>> Although it seems a bit weird this behavior of get stuck on one specific
>> branch.
>> 

>  The check-in you were stuck on was originally on trunk. But when an
> error was discovered in that check-in, it was diverted to a branch.
> You rode the diversion onto the branch.
>
>  



?

[sqlite] Fossil sqlite clone problem

[Stephan Beal]

On Tue, Feb 9, 2016 at 6:16 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Thanks for the answer !
>
> Although it seems a bit weird this behavior of get stuck on one specific
> branch.
>

You might have done 'update tip' at some point when that was the newest.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Fossil sqlite clone problem

[Domingo Alvarez Duarte]

Thanks for the answer !  

Although it seems a bit weird this behavior of get stuck on one specific
branch.  

Cheers !  
>  OTOH, if that tag is checked out, which your output indicates is the case,
>
>
>>then 'update' will apply no changes because that's the last commit in that
>> branch. You'll need to 'fossil update trunk' once to get back to the
>>trunk.
>> 
>> 

>  Stephan has this right: You are stuck on a branch. You should always
> do "fossil update trunk" to get to the latest, and avoid getting
> side-tracked onto a dead-end branch.
> 
> -- 
> 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] Fossil sqlite clone problem

[Domingo Alvarez Duarte]

?  
>  OTOH, if that tag is checked out, which your output indicates is the case,
> then 'update' will apply no changes because that's the last commit in that
> branch. You'll need to 'fossil update trunk' once to get back to the trunk.
>
>  



Thanks "fossil update trunk" solved the problem !  

Now the question that remain is why it got stuck there ?  

Cheers !

[sqlite] Fossil sqlite clone problem

[Domingo Alvarez Duarte]

Can you try doing this one from the sqlite tree:  
>  
> fossil pull --verily
> 
> in the past that's helped people reporting problems about a repo silently
> failing to pull past a certain version.
>
>  



?  

fossil pull --verilyPull from http://www.sqlite.org/src
Round-trips: 2?? Artifacts sent: 0? received: 28
Pull done, sent: 1090? received: 1579893? ip: 67.18.92.124  

After the above I also executed the command bellow and the respository still
not update.  

fossil update
Autosync:? http://wwwsqlite.org/src
Round-trips: 1?? Artifacts sent: 0? received: 0
Pull done, sent: 285? received: 2206? ip: 67.18.92.124
--
-
checkout: ec653cbcae74ecab4ec9df0548d5836e81dc5d14 2016-01-30
14:53:06 UTC
tags: clear-subtype-fail
comment:? Take care to clear the subtype on VDBE registers when the
value
? of the register is overwritten with new content.
(user: drh)
changes:? None. Already up-to-date  

Cheers !  

?

[sqlite] Fossil sqlite clone problem

[Stephan Beal]

On Tue, Feb 9, 2016 at 5:38 PM, Stephan Beal  wrote:

> fossil pull --verily
>
> in the past that's helped people reporting problems about a repo silently
> failing to pull past a certain version.
>

OTOH, if that tag is checked out, which your output indicates is the case,
then 'update' will apply no changes because that's the last commit in that
branch. You'll need to 'fossil update trunk' once to get back to the trunk.


https://www.sqlite.org/src/timeline?c=2016-01-30+14:53:06

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Fossil sqlite clone problem

[Stephan Beal]

On Tue, Feb 9, 2016 at 5:20 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> I frequently follow the updates of fossil and sqlite but I noticed that my
> sqlite clone stop updating the source tree at this commit :
>

Can you try doing this one from the sqlite tree:

fossil pull --verily

in the past that's helped people reporting problems about a repo silently
failing to pull past a certain version.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Fossil sqlite clone problem

[Domingo Alvarez Duarte]

Hello !  

I frequently follow the updates of fossil and sqlite but I noticed that my
sqlite clone stop updating the source tree at this commit :  

fossil status
repository:?? /sqlite3/../sqlite.fossil
local-root:?? /sqlite3/
config-db:?? /.fossil
checkout: ec653cbcae74ecab4ec9df0548d5836e81dc5d14 2016-01-30
14:53:06 UTC
parent:?? 96b780209cc95c3f3769bb880591380d94bfe38d 2016-01-30
14:17:10 UTC
tags: clear-subtype-fail
comment:? Take care to clear the subtype on VDBE registers when the
value
? of the register is overwritten with new content.
(user: drh)  

I usually use this scripts to update my cloned respositories:  

./do-update-fossil.sh  

fossil update
make
strip fossil
cp fossil $HOME/bin  

./do-update-sqlite.sh  

fossil update
make
strip sqlite3
cp sqlite3 $HOME/bin  

?  

Someone can give any help on this isssue ?  

Cheers !  

?  

?

[sqlite] Fossil sqlite clone problem

[Richard Hipp]

On 2/9/16, Domingo Alvarez Duarte  wrote:
> Thanks for the answer !
>
> Although it seems a bit weird this behavior of get stuck on one specific
> branch.

The check-in you were stuck on was originally on trunk.  But when an
error was discovered in that check-in, it was diverted to a branch.
You rode the diversion onto the branch.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Fossil sqlite clone problem

[Richard Hipp]

On 2/9/16, Domingo Alvarez Duarte  wrote:
>
> fossil pull --verilyPull from http://www.sqlite.org/src
> Round-trips: 2   Artifacts sent: 0  received: 28
> Pull done, sent: 1090  received: 1579893  ip: 67.18.92.124
>
> After the above I also executed the command bellow and the respository still
> not update.
>
> fossil update

This should be:  "fossil update trunk"

You got detoured onto a branch.


> Autosync:  http://wwwsqlite.org/src
> Round-trips: 1   Artifacts sent: 0  received: 0
> Pull done, sent: 285  received: 2206  ip: 67.18.92.124
> --
> -
> checkout: ec653cbcae74ecab4ec9df0548d5836e81dc5d14 2016-01-30
> 14:53:06 UTC
> tags: clear-subtype-fail
> comment:  Take care to clear the subtype on VDBE registers when the
> value
>   of the register is overwritten with new content.
> (user: drh)
> changes:  None. Already up-to-date
>
> Cheers !
>
>
>
> ___
> 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] Fossil sqlite clone problem

[Richard Hipp]

On 2/9/16, Stephan Beal  wrote:
> On Tue, Feb 9, 2016 at 5:38 PM, Stephan Beal  wrote:
>
>> fossil pull --verily
>>
>> in the past that's helped people reporting problems about a repo silently
>> failing to pull past a certain version.
>>
>
> OTOH, if that tag is checked out, which your output indicates is the case,
> then 'update' will apply no changes because that's the last commit in that
> branch. You'll need to 'fossil update trunk' once to get back to the trunk.
>

Stephan has this right:  You are stuck on a branch.  You should always
do "fossil update trunk" to get to the latest, and avoid getting
side-tracked onto a dead-end branch.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] SQLite and Entity problem

[Ajey Joshi]

Hello

Exception when using SQLite, Entity FW and programmatically configuring 
DBConfiguration.

(BTW it works perfectly fine if settings are done in app.config instead of 
programmatic. But as a library developer I do not want to ask clients  to put 
some config settings in their app.config. I have also seen the way to 
programmatically add config settings in application config at run-time but I do 
not want to use that approach.)


public class SQLitDbProviderFactoryResolver : IDbProviderFactoryResolver
{
public DbProviderFactory ResolveProviderFactory(DbConnection  
connection)
{
return System.Data.SQLite.SQLiteFactory.Instance;
}
}

public class SqliteConfiguration : DbConfiguration
{
public SqliteConfiguration()
{
SetDefaultConnectionFactory(new  
System.Data.Entity.Infrastructure.SqlConnectionFactory());
SetProviderFactoryResolver(new SQLitDbProviderFactoryResolver());

{
SetProviderFactory("System.Data.SQLite", 
SQLiteFactory.Instance);
SetProviderServices("System.Data.SQLite",  
(DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
}
}
}

[DbConfigurationType(typeof(SqliteConfiguration))]
public class SqliteDbContext : DbContext
{ . . . . .  . }

Exception:

System.TypeInitializationException was unhandled by user code
  HResult=-2146233036
  Message=The type initializer for 'LibraryProject.StudentResources' threw an 
exception.
  Source=LibraryProject
  TypeName=LibraryProject.StudentResources
  StackTrace:
   at LibraryProject.StudentResources.GetStudents()
   at SQLiteAndEntity.MainWindow..ctor() in 
c:\Users\aj135406\Desktop\SQLiteAndEntity\SQLiteAndEntity\MainWindow.xaml.cs:line
 28
  InnerException: System.Data.DataException
   HResult=-2146233087
   Message=An exception occurred while initializing the database. See the 
InnerException for details.
   Source=EntityFramework
   StackTrace:
at 
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action 
action)
at 
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
at 
System.Data.Entity.Internal.LazyInternalContext.b__4(InternalContext
 c)
at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput 
input)
at 
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1
 action)
at 
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
at System.Data.Entity.Internal.InternalContext.Initialize()
at 
System.Data.Entity.Internal.LazyInternalContext.get_ObjectContext()
at 
System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery[TElement](String 
sql, Boolean streaming, Object[] parameters)
at 
System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerator[TElement](String
 sql, Boolean streaming, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type 
elementType, String sql, Boolean streaming, Object[] parameters)
at 
System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator()
at System.Data.Entity.Infrastructure.DbRawSqlQuery`1.GetEnumerator()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 
source)
at LibraryProject.SqliteDbContext..ctor() in 
c:\Users\aj135406\Desktop\SQLiteAndEntity\LibraryProject\SqliteDbContext.cs:line
 68
at LibraryProject.StudentResources..cctor() in 
c:\Users\aj135406\Desktop\SQLiteAndEntity\LibraryProject\SqliteDbContext.cs:line
 109
   InnerException: System.Data.Entity.Core.EntityCommandCompilationException
HResult=-2146232005
Message=An error occurred while preparing the command definition. 
See the inner exception for details.
Source=EntityFramework
StackTrace:
 at 
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext
 context, DbQueryCommandTree  tree)
 at 
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext
 context, DbQueryCommandTree tree,  Type elementType, MergeOption mergeOption, 
Boolean streaming, Span span, IEnumerable`1 compiledQueryParameters, 
AliasGenerator aliasGenerator)
 at 
System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1
 forMergeOption)
 at 
System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.b__a()
 at 
System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 
func, IDbExecutionStrategy executionStrategy, Boolean  startLocalTransaction, 
Boolean releaseConnectionOnSuccess)
 at 

Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/21 17:18, James K. Lowden wrote:

...to get web payment forms to allow, for the love of God, spaces in credit 
card numbers. --jkl


Now there's a worthy cause. Ditto for phone numbers (though they mostly are more lenient today). Also to allow hashes and dashes in 
the address field. I spent quite a while on the phone to some American online company trying to buy stuff using a credit card for 
which I needed to supply my address for verification purposes... and in some countries we have addresses like 19A 
Hem#Strassen-34'B(N), etc.  So as long as some of those characters are not allowed in the field, I cannot possibly reproduce an 
address that matches my bank's version of it, and needless to say, cannot pay anyone with such input field restrictions.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Tim Streater]

On 21 Sep 2014 at 16:18, James K. Lowden  wrote: 

> Really?  HM Revenue and Customs doesn't require you to distinguish
> between your given and family names once a year?

Search me. As long as I get my tax adviser to file my tax return once a year, 
and send them dosh twice a year, they don't seem to care.

> Your drivers license doesn't say "Surname" on it anywhere?

I don't have one of them. I have a "Driving Licence", and no, the word 
"Surname" does not appear on it. What does is "1", followed by my surname, and 
"2" followed by my first and middle names (and various other fields).

> I hope we've finally exhausted this topic.  Now, if you really want to
> usefully direct your righteous outrage, please join me in my petition
> to get web payment forms to allow, for the love of God, spaces in
> credit card numbers.

Well I'll vote for that, as it's trivial to implement. You can go to 
http://epetitions.direct.gov.uk and start it there.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Sat, 20 Sep 2014 20:21:29 +0100
Simon Slavin  wrote:

> > Your suggestion essentially amounts to "names are not
> > decomposable, so keep one version for the user and one for the
> > system."
> 
> Sorry, I don't think I got that across effectively.  If I make up a
> database that stores names, I'm storing them pretty-much for two main
> reasons: to know the name and to sort by name on displays and
> printouts.  So I keep one column for the whole name ("to know the
> name") and one for the name in the order I want to see it sorted ("to
> sort by names").  Two things you need, make two columns.  I see no
> reason to split name up into three artificial units.

I think you got your point across just fine.  "no reason to split name
up into three artificial units" is the same as "names are not
decomposable".  

The units aren't artificial, else the history of Europe would surely
have been different.  Names if nothing else have legal meaning.  There's
a case in court in Tennessee currently because, in that state,  the
couple can't put just any old thing in the "last name" of the birth
certificate.  

I can well appreciate that might not matter to your database, which is
really my point.  What we're really dealing with here is a question of
problem domain: what does the name *mean* in the context of the
database in question. For you, evidently, it's the string by which its
owner identifies himself.  You take what he gives you, and you
reproduce it once for sorting purposes.  Because not every database has
such relaxed needs, your advice is not generally applicable.  

You say, 

> I don't understand why anyone would want to sort on firstname

I guess you've never produced a club membership or other informal
listing by first name.  I thought that was pretty common in those clubs
I've heard about over there, with the Chesterfield couches and brandy
and cigars by the fireplace, old chum?  

That also suggests you've never had to reconcile two databases in which
the people's names were the only identifying information.  Some years
ago a friend of mine integrated 50 separate state databases of doctors
for the AMA.  Much hilarity ensued, as I'm sure you can imagine.  The
job would surely have been harder if last names hadn't been
identified.  

> I suspect that those thousands of databases had their fields defined
> in the 1970s, along with checking to see that all three fields
> weren't blank and didn't have anything but letters in them.  And that
> such things would only be done in the US.  

Really?  HM Revenue and Customs doesn't require you to distinguish
between your given and family names once a year?  Your drivers license
doesn't say "Surname" on it anywhere?  

> If you want to assess their success, ask yourself how many times
> you've seen NO-MIDDLE-NAME in official records.  Or someone who
> apparently has the lastname of OMALLEY.  Or someone who apparently
> has a lastname of Al-Haj and is therefore sorted into the 'A's.

These are not necessarily database limitations or database design
issues, as you know.  Just because some COBOL system running IMS in
1975 didn't admit nonalphanumeric data, doesn't mean that in 2014 we
can't distinguish last name from first.  

> Anyone who worked for a big company these days and created such a
> database should get called in and told to do it again properly.

The day that happens, the lunatics will have been put in charge of the
asylum, because "properly" will have lost all meaning.  

I hope we've finally exhausted this topic.  Now, if you really want to
usefully direct your righteous outrage, please join me in my petition
to get web payment forms to allow, for the love of God, spaces in
credit card numbers.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/20 23:23, Simon Slavin wrote:

...calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel


Oh you know him? We go way back... old Tim Biscuits we used to call him. It was fun watching the undertakers figure out how to get 
all that on his gravestone after that plan to blow up the empire state went terribly awry in the test phase. People still mistake 
that stone for a karaoke machine.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 11:09pm, Mark Halegua  wrote:

> I'm going to have a separate table for pennames.  Lester del Rey will be last 
> name del Rey, 
> first name Lester.
> 
> My problem will be the following:
> 
> Therer are a number of writers who used several non de plummes, however there 
> are some 
> credited authors who are house names (Maxwell Grant is one, with no fewer 
> than three 
> writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
> Tinsley, and 
> others) and it is this I'm having trouble normalizing.  Thinking for those 
> house names I'm 
> going to have to refer to the story or issue to fully credit the corect 
> writer,  Any thoughts 
> here are welcome.

I suspect that you may need a many-to-many relationship.  You have already 
listed a case where one author used many pen-names, and a case where different 
people wrote with the same pen-name.  To make it worse, some works are written 
by someone writing under their own name.  Inconceivable.

If you need to capture the whole truth in your data columns, taking into 
account all combinations which happened, no matter how rare, then each work 
published needs two fields: real author and pen-name.  Each of which, of 
course, should be foreign keys into two other files.  Often the texts shown in 
those two fields will match each-other.  But one might be an informal version 
of the other, for example "Francis H. Burns" in the person file but "Frank 
Burns" in the pen-name file because that's the way the name is usually printed.

Another way to capture the whole truth would be to establish the many-to-many 
relationship between people and pen-names, and have each work point at a record 
in the middle file of that relationship.

If you only need to capture 99% of the truth, and can insert a note for a few 
works explaining anything unusual, then it may be possible to store only the 
pen-name for each work.  In your pen-name file you can list what would normally 
be just the name of the one person who used that pen-name, however you might 
want to establish a format where there could be multiple names separated by 
commas or semicolons or something.

I hate making choices like these.  Good luck picking one which turns out to be 
the one your users will find most convenient.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

On Friday, September 19, 2014 08:07:06 PM Simon Slavin wrote:
> No, no he's just working on US Pulp Magazines.  All pulp writers have
> traditional names.  He's not going to have any trouble.
> 
> Except, of course, with Daniel Keys Moran.  Who doesn't use his first name
> except when writing.  And Marion Zimmer Bradley, whose name should be
> sorted under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted
> under 'B'.  And Margaret St. Clair whose name is correctly pronounced and
> sorted as if it was Margaret Sinclair.
> 
> And Lester del Rey.  Whose name should be sorted under 'd' (his wife was
> Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was
> Ramon Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y
> Alvarez del Rey y de los Uerdes.

I'm going to have a separate table for pennames.  Lester del Rey will be last 
name del Rey, 
first name Lester.

My problem will be the following:

Therer are a number of writers who used several non de plummes, however there 
are some 
credited authors who are house names (Maxwell Grant is one, with no fewer than 
three 
writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
Tinsley, and 
others) and it is this I'm having trouble normalizing.  Thinking for those 
house names I'm 
going to have to refer to the story or issue to fully credit the corect writer, 
 Any thoughts 
here are welcome.

> 
> Of course, the author Eando Binder was actually two people (Edward and Oscar
> Binder) but that doesn't count.
Actually, it does, but it will be taken care of in the pennames table.  And, 
for a period, one of 
the Binder brothers left and the remaining Binder continued to write as Eando 
Binder.

Mark
 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 9:42pm, Petite Abeille  wrote:

> Your last name contains invalid characters
> http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Ah yes, John Graham-Cumming.  One of those sneaky non-standard-format foreign 
names.  Probably a terrorist.

On 20 Sep 2014, at 9:27pm, John Hascall  wrote:

> The problem with your suggestion of 'two uses => two fields' is that no 
> sooner do you do that then somebody comes up with additional uses, for 
> example, formal greeting, informal greeting, the appropriate form for 
> government form X123, and so on

Banks I've worked with include a field called "Salutation" which says things 
like "Dear Ms. White" or "Dear Mrs. White" depending on the account-holder's 
preference.

Financial and legal institutions, by the way, are the toughest at getting this 
right.  They have a legal requirement to know the account-holder's 
commonly-used form of name.  Not some twisted abbreviated version of it.  If 
the account-holder calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel that's 
what they have to have in their database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[John Hascall]

The problem with your suggestion of 'two uses => two fields' is that no sooner 
do you do that then somebody comes up with additional uses, for example, formal 
greeting, informal greeting, the appropriate form for government form X123, and 
so on

John Hascall
IT Services
Iowa State Univ.

> On Sep 20, 2014, at 2:21 PM, Simon Slavin  wrote:
> 
> 
>> On 20 Sep 2014, at 7:42pm, James K. Lowden  wrote:
>> 
>> I'm saying more than one sort order is often needed.  If you don't
>> distinguish among the components of the person's name, you can't sort
>> by those components.  
> 
> I don't understand why anyone would want to sort on firstname or middle name. 
>  I suppose having them indexed might be useful for searching, but apart from 
> very specific census questions like "How popular were certain names in the 
> 1990s ?" I see no reason to do it.
> 
>> You offered the OP sweeping contrarian advise on how to represent names
>> in a database, referring him to one odd source having nothing to do
>> with databases.
> 
> That one odd source is so widely quoted nobody else has bothered to write 
> anything on the subject.  A search on "People have exactly one canonical full 
> name" gives me 1,830 hits and that's just the quotes rather than the links 
> and references.
> 
>> Your suggestion essentially amounts to "names are not
>> decomposable, so keep one version for the user and one for the
>> system."
> 
> Sorry, I don't think I got that across effectively.  If I make up a database 
> that stores names, I'm storing them pretty-much for two main reasons: to know 
> the name and to sort by name on displays and printouts.  So I keep one column 
> for the whole name ("to know the name") and one for the name in the order I 
> want to see it sorted ("to sort by names").  Two things you need, make two 
> columns.  I see no reason to split name up into three artificial units.
> 
> Searching by name is going to require LIKE and Soundex or something like it.  
> I've seen a database where a third column was used to store the soundex 
> encoding of the full name.  (Might have been something else like Soundex.  It 
> was a long time ago.)  In SQLite you could probably feed that column with 
> TRIGGERs.
> 
>> Given the thousands of databases out there that use first,
>> middle, and last names as columns -- with apparent success -- and
>> theoretical and practical problems arising from your alternative, I
>> suggest your advice is ill-founded.  
> 
> I suspect that those thousands of databases had their fields defined in the 
> 1970s, along with checking to see that all three fields weren't blank and 
> didn't have anything but letters in them.  And that such things would only be 
> done in the US.  Certainly anyone who designed a database that way in England 
> (where I live) would have to immediately write a page full of instructions 
> about how to squeeze many names I see around here into those three fields.
> 
> If you want to assess their success, ask yourself how many times you've seen 
> NO-MIDDLE-NAME in official records.  Or someone who apparently has the 
> lastname of OMALLEY.  Or someone who apparently has a lastname of Al-Haj and 
> is therefore sorted into the 'A's.
> 
> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Petite Abeille]

On Sep 20, 2014, at 9:21 PM, Simon Slavin  wrote:

> Anyone who worked for a big company these days and created such a database 
> should get called in and told to do it again properly.

Along these same lines:

Your last name contains invalid characters
http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Falsehoods Programmers Believe About Names
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

How do you like the vCard specification?

http://tools.ietf.org/html/rfc6350

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 7:42pm, James K. Lowden  wrote:

> I'm saying more than one sort order is often needed.  If you don't
> distinguish among the components of the person's name, you can't sort
> by those components.  

I don't understand why anyone would want to sort on firstname or middle name.  
I suppose having them indexed might be useful for searching, but apart from 
very specific census questions like "How popular were certain names in the 
1990s ?" I see no reason to do it.

> You offered the OP sweeping contrarian advise on how to represent names
> in a database, referring him to one odd source having nothing to do
> with databases.

That one odd source is so widely quoted nobody else has bothered to write 
anything on the subject.  A search on "People have exactly one canonical full 
name" gives me 1,830 hits and that's just the quotes rather than the links and 
references.

> Your suggestion essentially amounts to "names are not
> decomposable, so keep one version for the user and one for the
> system."

Sorry, I don't think I got that across effectively.  If I make up a database 
that stores names, I'm storing them pretty-much for two main reasons: to know 
the name and to sort by name on displays and printouts.  So I keep one column 
for the whole name ("to know the name") and one for the name in the order I 
want to see it sorted ("to sort by names").  Two things you need, make two 
columns.  I see no reason to split name up into three artificial units.

Searching by name is going to require LIKE and Soundex or something like it.  
I've seen a database where a third column was used to store the soundex 
encoding of the full name.  (Might have been something else like Soundex.  It 
was a long time ago.)  In SQLite you could probably feed that column with 
TRIGGERs.

> Given the thousands of databases out there that use first,
> middle, and last names as columns -- with apparent success -- and
> theoretical and practical problems arising from your alternative, I
> suggest your advice is ill-founded.  

I suspect that those thousands of databases had their fields defined in the 
1970s, along with checking to see that all three fields weren't blank and 
didn't have anything but letters in them.  And that such things would only be 
done in the US.  Certainly anyone who designed a database that way in England 
(where I live) would have to immediately write a page full of instructions 
about how to squeeze many names I see around here into those three fields.

If you want to assess their success, ask yourself how many times you've seen 
NO-MIDDLE-NAME in official records.  Or someone who apparently has the lastname 
of OMALLEY.  Or someone who apparently has a lastname of Al-Haj and is 
therefore sorted into the 'A's.

Anyone who worked for a big company these days and created such a database 
should get called in and told to do it again properly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 17:40:52 +0100
Simon Slavin  wrote:

> > Problems arising from the schema you suggest:
> > 
> > 1.  select by last name
> > 2.  select by first name
> > 3.  duplicate detection[1]
> > 4.  "however they want" is unknown and idiosyncratic
> > 5.  "whatever order" may be more than one
> 
> For 1, why are you selecting by last name ?  The person I named above
> has the surname "Nielsen Hayden".  Which would you be selecting and
> why ?

Some small confusion here.  By "last name" I mean surname, not "last
string in name delimited by white space."  

First name: Wernher
Last name:  von Braun

You say it shouldn't sort by "von", but that's a cultural choice, too,
cf. the Manhattan phone book.  

> For 2, match the string entered with the beginning of the 'name'
> field, probably using LIKE and a percent character.

So you're assuming the beginning of the "name" column -- entered
"however they want" is the first name.  I guess in a literal sense
that's true.  You're going to have trouble with Fran, Frank, and
Francis, though.  That you have to use pattern matching for equality
suggests a problem, if not an error.  

> For 5, are you telling me you don't know how you want the name
> sorted ?  

No, I'm saying more than one sort order is often needed.  If you don't
distinguish among the components of the person's name, you can't sort
by those components.  

You offered the OP sweeping contrarian advise on how to represent names
in a database, referring him to one odd source having nothing to do
with databases.  Your suggestion essentially amounts to "names are not
decomposable, so keep one version for the user and one for the
system."  Given the thousands of databases out there that use first,
middle, and last names as columns -- with apparent success -- and
theoretical and practical problems arising from your alternative, I
suggest your advice is ill-founded.  

I'm aware that different cultures use a variety of systems.  When
people in those cultures encounter computerized databases and large
bureaucracies, they deal.  They adapt.  Family tradition might reckon
17 names and three hyphens, but the birth certificate will hold only
three or so, and that forms the legal basis for the baby's
identification in society.  

You don't have to go very far before the straightjacket doesn't fit,
but for practical purposes it doesn't matter. I know a woman "Peg" who
was born "Margaret". Which name appears in which database depends on its
purpose.  In the email system she's Peg; on her paycheck it says
Margaret.  

I imagine the application exists that requires the name be rendered
just as the person prefers.  Usually, though, databases are used for
sorting, searching, and (most important) identifying.  Those purposes
are better served by distinguishing conventionally among the parts of a
person's name.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 11:42:26 -0700
Roger Binns  wrote:

> You do realise there are more people in the US than just those born
> in the country with good old fashioned roman alphabet 26 ascii
> letters?  

Yes.  Did I mention ASCII?  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Klaas V]

Simon wrote about parts of names like e.g. the Dutch/Flemish 'van'

They should definitely not be capitalised

Is not always true. Especially in northern Belgium names are often spelled like 
Van (often even connected with the last name)  and I did personally the same to 
see which of the two e-mail clients I used at work so the NOCASE tag may cause 
inconsistencies as well.

Anyway to split a name in two, three or maybe even more parts is not a bad 
idea. The solution with a number of commas is a very good one.

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 7:42pm, Roger Binns  wrote:

> On 19/09/14 07:58, James K. Lowden wrote:
>> I wonder what "problems" you're talking about.  Do you think the IRS,
>> the Social Security Administration, the DMV, the passport agency, your
>> birth certificate, and your local bank are just doing it wrong? 
> 
> You do realise there are more people in the US than just those born in the
> country with good old fashioned roman alphabet 26 ascii letters?  And yes
> they do get it wrong:

No, no he's just working on US Pulp Magazines.  All pulp writers have 
traditional names.  He's not going to have any trouble.

Except, of course, with Daniel Keys Moran.  Who doesn't use his first name 
except when writing.  And Marion Zimmer Bradley, whose name should be sorted 
under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted under 'B'. 
 And Margaret St. Clair whose name is correctly pronounced and sorted as if it 
was Margaret Sinclair.

And Lester del Rey.  Whose name should be sorted under 'd' (his wife was 
Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was Ramon 
Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y Alvarez del 
Rey y de los Uerdes.

Of course, the author Eando Binder was actually two people (Edward and Oscar 
Binder) but that doesn't count.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Roger Binns]

On 19/09/14 07:58, James K. Lowden wrote:
> I wonder what "problems" you're talking about.  Do you think the IRS,
> the Social Security Administration, the DMV, the passport agency, your
> birth certificate, and your local bank are just doing it wrong? 

You do realise there are more people in the US than just those born in the
country with good old fashioned roman alphabet 26 ascii letters?  And yes
they do get it wrong:

  http://www.usatoday.com/story/news/nation/2013/09/13/long-last-name/2810603/

Or try having the name Chloé in the US.  Heck my friend Stephane had a hard
enough time as everyone assumed he couldn't spell his own name, and
corrected it to Stephanie!  And his name is actually Stéphane, but don't
think any of those agencies you listed would acknowledge that.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[John McKown]

On Fri, Sep 19, 2014 at 11:40 AM, Simon Slavin  wrote:
>
> On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:
>
>> On Fri, 19 Sep 2014 02:02:30 +0100
>> Simon Slavin  wrote:
>>
>>> By the way I wanted to warn you about starting any project with first
>>> name, middle name and last name fields.  This leads to problems, and
>>> I would go to some lengths to avoid it if possible.  It would be
>>> better to provide two columns:
>>>
>>> name(their name, however they want it to be shown)
>>> nameInSortOrder (their name, in whatever order you feel it should be
>>> sorted)
>>
>> I wonder what "problems" you're talking about.
>
> First, if you automatically generate letters, a letter to "Patrick Nielsen 
> Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His 
> surname is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" 
> should be addressed "Dear Mr Fitz William".
>
> Then, if you sort the names into order, you're going to find "Patrick Nielsen 
> Hayden" under H, whereas you should find him under N.
>

This is an interesting problem. Which the Unicode people addressed
with the "non-breaking space" character of U+00A0. Of course, entering
this particular space character is not as simple as just hitting the
space bar.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:

> On Fri, 19 Sep 2014 02:02:30 +0100
> Simon Slavin  wrote:
> 
>> By the way I wanted to warn you about starting any project with first
>> name, middle name and last name fields.  This leads to problems, and
>> I would go to some lengths to avoid it if possible.  It would be
>> better to provide two columns:
>> 
>> name(their name, however they want it to be shown)
>> nameInSortOrder (their name, in whatever order you feel it should be
>> sorted)
> 
> I wonder what "problems" you're talking about.

First, if you automatically generate letters, a letter to "Patrick Nielsen 
Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His surname 
is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" should be 
addressed "Dear Mr Fitz William".

Then, if you sort the names into order, you're going to find "Patrick Nielsen 
Hayden" under H, whereas you should find him under N.

> Problems arising from the schema you suggest:
> 
> 1.  select by last name
> 2.  select by first name
> 3.  duplicate detection[1]
> 4.  "however they want" is unknown and idiosyncratic
> 5.  "whatever order" may be more than one

For 1, why are you selecting by last name ?  The person I named above has the 
surname "Nielsen Hayden".  Which would you be selecting and why ?

For 2, match the string entered with the beginning of the 'name' field, 
probably using LIKE and a percent character.

For 3, check to see if either field is a duplicate.

For 4, type in their name however they wrote it on the form you're copying.

For 5, are you telling me you don't know how you want the name sorted ?  In 
that case, perhaps there's no need to retain a sorting field at all.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Keith Medcalf]

Most systems that encompass non-western style names will use different terms:

formalname and familyname in preference to firstname and lastname.  FirstName, 
MiddleName, LastName imply ordering which does not necessarily hold.  Calling 
them FormalName, AncestorName, FamilyName more aptly describes the use of the 
various bits without implying the ordering.  Then a CommonName field is used to 
hold the display variant as the person would usually prefer to see it (which 
may be Formal Ancestor Family as in western style names, Family Formal Ancestor 
as in most Eastern style names, or Family Ancestor Formal as used in some other 
Eastern cultures).  Often a FamiliarName is provided so that the name by which 
a person wants to be addressed can be stored.  This is necessary even for 
proper handling of western names where, for example, someone might want to use:

formalname = Robert
ancestorname = Richard
familyname = Smith
familliarName = Dick
CommonName = R Dick Smith

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of James K. Lowden
>Sent: Friday, 19 September, 2014 08:58
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] An order by problem, maybe a bug?
>
>On Fri, 19 Sep 2014 02:02:30 +0100
>Simon Slavin <slav...@bigfraud.org> wrote:
>
>> By the way I wanted to warn you about starting any project with first
>> name, middle name and last name fields.  This leads to problems, and
>> I would go to some lengths to avoid it if possible.  It would be
>> better to provide two columns:
>>
>> name(their name, however they want it to be shown)
>> nameInSortOrder (their name, in whatever order you feel it should be
>> sorted)
>
>I wonder what "problems" you're talking about.  Do you think the IRS,
>the Social Security Administration, the DMV, the passport agency, your
>birth certificate, and your local bank are just doing it wrong?  I've
>filled out address forms in four languages I can think of, and every
>one had a place for my first name and last name.  I've never seen one
>that seems like it would present a problem for "Patrick McKenzie".
>
>Problems arising from the schema you suggest:
>
>1.  select by last name
>2.  select by first name
>3.  duplicate detection[1]
>4.  "however they want" is unknown and idiosyncratic
>5.  "whatever order" may be more than one
>
>If you print a list of "however they want" ordered by "whatever order",
>the result will look very strange and be hard to use, because the sort
>order will not be apparent in the listing.
>
>The issues raised in the linked page apply mostly to application
>development.  The database's job is to capture the facts.  Just get the
>person's name -- in its discrete parts -- in the database and be done
>with it.
>
>--jkl
>
>[1] Yes, I know names aren't unique.  Duplicate detection isn't only
>about rejection; it's also about noticing similarity to avoid, say,
>assigning a new identifier to the same person.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 02:02:30 +0100
Simon Slavin  wrote:

> By the way I wanted to warn you about starting any project with first
> name, middle name and last name fields.  This leads to problems, and
> I would go to some lengths to avoid it if possible.  It would be
> better to provide two columns:
> 
> name(their name, however they want it to be shown)
> nameInSortOrder (their name, in whatever order you feel it should be
> sorted)

I wonder what "problems" you're talking about.  Do you think the IRS,
the Social Security Administration, the DMV, the passport agency, your
birth certificate, and your local bank are just doing it wrong?  I've
filled out address forms in four languages I can think of, and every
one had a place for my first name and last name.  I've never seen one
that seems like it would present a problem for "Patrick McKenzie". 

Problems arising from the schema you suggest:

1.  select by last name
2.  select by first name
3.  duplicate detection[1]
4.  "however they want" is unknown and idiosyncratic
5.  "whatever order" may be more than one

If you print a list of "however they want" ordered by "whatever order",
the result will look very strange and be hard to use, because the sort
order will not be apparent in the listing.  

The issues raised in the linked page apply mostly to application
development.  The database's job is to capture the facts.  Just get the
person's name -- in its discrete parts -- in the database and be done
with it.  

--jkl

[1] Yes, I know names aren't unique.  Duplicate detection isn't only
about rejection; it's also about noticing similarity to avoid, say,
assigning a new identifier to the same person.  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[FarSight Data Systems]

Thanks, I'll keep it in min.  In this case, howevery, I don't think that will 
be an issue.  All of 
the names are from American published pulp magazines,  writers, artists, and 
editors.

Mark

On Friday, September 19, 2014 02:02:30 AM Simon Slavin wrote:
> On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:
> > that resolved it.  I didn't know you needed to put the desc with both
> > columns.
> > 
> > It means another table I had thought was properly ordered wasn't.
> > 
> > Thank you.
> 
> You're welcome.  Glad you figured it out.
> 
> By the way I wanted to warn you about starting any project with first name,
> middle name and last name fields.  This leads to problems, and I would go
> to some lengths to avoid it if possible.  It would be better to provide two
> columns:
> 
> name(their name, however they want it to be shown)
> nameInSortOrder (their name, in whatever order you feel it should be sorted)
> 
> For the second field your name might appear as "Halegua, Mark" and someone
> with a middle name might appear as "Smith, Mark Edward".  The comma is
> needed because some people have a surname which is two separate words, e.g.
> Patrick Nielsen Hayden.  Given the way SQLite works you would want to
> declare the field nameInSortOrder as having COLLATE NOCASE.
> 
> This is especially important if you are storing names which don't all have
> Western-style 'given-name surname' format.  For instance, you may see these
> words between the first and last parts of people's names: "bin", "ben",
> "ibn", "bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not
> middle names.  They mean "son of" or "daughter of" or "from" or other
> things.  They should definitely not be capitalised, except for "O'".  And
> you don't sort on them at all.
> 
> Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted
> together, not as if the name begins with the letters 'MAC'.  It's a
> convention that they all be sorted at the beginning of the 'M' listings,
> ignoring the difference between "Mc" and "Mac".
> 
> For a longer list of reasons, see this article:
> 
>  mes/>
> 
> and for those who like that, there's a similar
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:

> that resolved it.  I didn't know you needed to put the desc with both columns.
> 
> It means another table I had thought was properly ordered wasn't.
> 
> Thank you.

You're welcome.  Glad you figured it out.

By the way I wanted to warn you about starting any project with first name, 
middle name and last name fields.  This leads to problems, and I would go to 
some lengths to avoid it if possible.  It would be better to provide two 
columns:

name(their name, however they want it to be shown)
nameInSortOrder (their name, in whatever order you feel it should be sorted)

For the second field your name might appear as "Halegua, Mark" and someone with 
a middle name might appear as "Smith, Mark Edward".  The comma is needed 
because some people have a surname which is two separate words, e.g. Patrick 
Nielsen Hayden.  Given the way SQLite works you would want to declare the field 
nameInSortOrder as having COLLATE NOCASE.

This is especially important if you are storing names which don't all have 
Western-style 'given-name surname' format.  For instance, you may see these 
words between the first and last parts of people's names: "bin", "ben", "ibn", 
"bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not middle names.  
They mean "son of" or "daughter of" or "from" or other things.  They should 
definitely not be capitalised, except for "O'".  And you don't sort on them at 
all.

Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted together, 
not as if the name begins with the letters 'MAC'.  It's a convention that they 
all be sorted at the beginning of the 'M' listings, ignoring the difference 
between "Mc" and "Mac".

For a longer list of reasons, see this article:



and for those who like that, there's a similar



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

Simon,
that resolved it.  I didn't know you needed to put the desc with both columns.

It means another table I had thought was properly ordered wasn't.

Thank you.

Mark


On Friday, September 19, 2014 12:50:57 AM Simon Slavin wrote:
> On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:
> > Here are the commands:
> > 
> > select * from contributors order by contrib_lname, contrib_fname; (works
> > properly) select * from contributors order by contrib_lname,
> > contrib_fname desc; (get the same order as above)
> 
> Note that the DESC you provided above applies only to the first name.  If
> you need DESC to apply to the last name too, you want
> 
> select * from contributors order by contrib_lname DESC, contrib_fname desc
> 
> > select * from contributors order by contrib_lname desc; (this works but
> > obviosiosly doesn't sub-order the contrib_fname)
> 
> If the above doesn't solve your problem, please ...
> 
> tell us which version of SQLite you're using.
> 
> give us a couple of INSERT commands which lead to results which are the
> wrong way around.  It should be possible to demonstrate this with just two
> INSERTs.  If it isn't, please try to give us as few as possible rows of
> data to demonstrate the problem.
> 
> If this is a result retrieved by something in one of your programs, please
> try the same commands with one of the SQLite shell tools, downloadable from
> the SQLite site.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:

> Here are the commands:
> 
> select * from contributors order by contrib_lname, contrib_fname; (works 
> properly)
> select * from contributors order by contrib_lname, contrib_fname desc; (get 
> the same order 
> as above)

Note that the DESC you provided above applies only to the first name.  If you 
need DESC to apply to the last name too, you want

select * from contributors order by contrib_lname DESC, contrib_fname desc

> select * from contributors order by contrib_lname desc; (this works but 
> obviosiosly doesn't 
> sub-order the contrib_fname)

If the above doesn't solve your problem, please ...

tell us which version of SQLite you're using.

give us a couple of INSERT commands which lead to results which are the wrong 
way around.  It should be possible to demonstrate this with just two INSERTs.  
If it isn't, please try to give us as few as possible rows of data to 
demonstrate the problem.

If this is a result retrieved by something in one of your programs, please try 
the same commands with one of the SQLite shell tools, downloadable from the 
SQLite site.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] An order by problem, maybe a bug?

[Mark Halegua]

I've come upon a problem in sqlite3.

Here's the table:

CREATE TABLE contributors(
contrib_id integer primary key, 
contrib_lname char not null, 
contrib_fname char, 
contrib_mname char,  
writer int, 
artist int, 
editor int)

I've inserted several names.  When I order by contrib_lname, contrib_fname I 
get the 
correct order, however when I say desc, I get the same order as without.  It's 
only when I 
don't include the contrib_fname does it come out in last name order, however, 
there are a 
couple of entries with same last names but different first names and they won't 
necessarily 
sort properly if I don't include the contrib_fname column.

Why am I not getting the results I'm expecting?

Here are the commands:

select * from contributors order by contrib_lname, contrib_fname; (works 
properly)
select * from contributors order by contrib_lname, contrib_fname desc; (get the 
same order 
as above)
select * from contributors order by contrib_lname desc; (this works but 
obviosiosly doesn't 
sub-order the contrib_fname)

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite with Entity Framework problem

[Joe Mistachkin]

Christiano Borchardt wrote:
> 
> My SQL server has a table with a primary key of type smallint which is an
> identity column. This is the AUTOINCREMENT equivalent on SQLite. However
> AUTOINCREMENT in SQlite only allows the Integer type.
> When the entity framework loads the entity for this table it crashes
> because the type returned by SQLite data provider is an Int64 while the
> expected by EF is Int16.
> 

I'm not sure why Entity Framework would use an Int16 for that.  What is the
schema of the database table in question?  Also, could you include the code
for the associated entity class?

> 
> The version of SQLite I'm using is 1.0.92.0 and the entity framework is
> 6.0.0.0.
> 

Is the version of Entity Framework exactly 6.0.0.0 or is it something like
6.0.0.2?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite with Entity Framework problem

[Christiano Borchardt]

Hi there,

We have a system which uses SQL server as a primary database. We are
thinking about replacing the entity framework provider so we can use SQLite
in-memory for integration tests.

I have done few tests and generally System.Data.SQlite works fine. The only
exception I have so far is:

My SQL server has a table with a primary key of type smallint which is an
identity column. This is the AUTOINCREMENT equivalent on SQLite. However
AUTOINCREMENT in SQlite only allows the Integer type.
When the entity framework loads the entity for this table it crashes
because the type returned by SQLite data provider is an Int64 while the
expected by EF is Int16.

Is there a way to tweak or by pass this behavior?

The version of SQLite I'm using is 1.0.92.0 and the entity framework is
6.0.0.0.

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug Report SQLITE JDBC driver 3.7.2: problem calling "wasNull" function in the ResultSet.

[Burkhard Losch]

Hi,

if the user calls "getObject" before calling wasNull then wasNull returns 
either the "wasNull" value of the previous called get call, or 
it throws an Exception, if it is called the first time.


How to replicate this bug:

DBConnection conn = 
DatabaseMetaData md = conn.getMetaData();

ResultSet rs = md.getTables(null, null, null, new String[] {"TABLE});


if (rs.next()) {
   Object anyData = rs.getObject(1)
   // the following statement will throw an exception: "column -1 out of 
bounds [1,10]"
   if (rs.wasNull()) {
  ... 
   }
 
}


I saw in the source code that getObject does not set the lastCol variable 
in the ResultSet class "RS".


The expected behavior is that the getObject function sets the "lastCol" 
variable so that cosecutive function calls to "wasNull" do not fail with 
an exception.


Would be nice to hear from you.

Regards,
Burkhard.
E-world 2014
11.-13..2.2013 Essen/Germany

Besuchen Sie uns auf der E-world 2014 | Halle 3 | Stand 131
Wir freuen uns auf Ihren 
Besuch.
 Burkhard Losch - KISTERS AG - Charlottenburger Allee 5 - 52068 Aachen - Germany
Handelsregister Aachen, HRB-Nr. 7838 | Vorstand: Klaus Kisters, Hanns Kisters | 
Aufsichtsratsvorsitzender: Dr. Thomas Klevers
Tel.: +49 241 9671 -112 | Mobil: +49 176 19671041 | E-Mail: 
burkhard.lo...@kisters.de | WWW: http://www.kisters.de

Diese E-Mail enthält vertrauliche und/oder rechtlich geschützte Informationen. 
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten 
haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. 
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht 
gestattet. 
This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received this e-mail in error) please 
notify the sender immediately and destroy this e-mail. Any unauthorised 
copying, disclosure or distribution of the material in this e-mail is strictly 
forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regarding data source problem

[Sujay Saboo]

Hi, i hv installed sqlite setup for .net framework 4.0. On one machine i
can view sqlite in data source bt in another pc same is not showing. Am i
missing sumthing. Please suggest.

Regards,
Sujay Saboo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Григорий Григоренко]

Понедельник, 13 мая 2013, 17:03 +01:00 от Simon Davies 
:
>On 13 May 2013 16:52, Simon Slavin < slav...@bigfraud.org > wrote:
>>
>> On 13 May 2013, at 3:54pm, Григорий Григоренко < grigore...@mail.ru > wrote:
>>
>>> sample database is:
>>>
>>> PRAGMA FOREIGN_KEYS=1;
>>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name);
>>> INSERT INTO cat VALUES (1, 'Alice');
>>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id));
>>> INSERT INTO owner VALUES(1);
>>>
>>> This script fails to drop tables with  'foreign key constraint failed':
>>>
>>> SAVEPOINT edit;
>>> PRAGMA FOREIGN_KEYS=0;
>>> DROP TABLE cat;
>>> DROP TABLE owner;
>>> RELEASE edit;
>>> PRAGMA FOREIGN_KEYS=1;
>>
>> You are DROPping your tables in the wrong order.  The 'owner' table refers 
>> to the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' 
>> table which refers to a table which doesn't exist.  Swap the order of the 
>> DROPs and your database will not risk that kind of disaster.
>>
>>> This script works OK:
>>>
>>> PRAGMA FOREIGN_KEYS=0;
>>> DROP TABLE cat;
>>> DROP TABLE owner;
>>> PRAGMA FOREIGN_KEYS=1;
>>
>> Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
>> you're going to do the RELEASE.  If you left out the second DROP, then 
>> issued a RELEASE, the database would be corrupt, so SQLite issues the error 
>> message to warn you about it.  The version without the SAVEPOINT never has 
>> to worry about you doing that.
>>
>> Simon.
>
>http://www.sqlite.org/pragma.html#pragma_foreign_keys :
>
>"...foreign key constraint enforcement may only be enabled or disabled
>when there is no pending BEGIN or SAVEPOINT. "

Ups. My fault, must have read docs more carefully.
Thank you.

>
>Regards,
>Simon
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Simon Davies]

On 13 May 2013 16:52, Simon Slavin  wrote:
>
> On 13 May 2013, at 3:54pm, Григорий Григоренко  wrote:
>
>> sample database is:
>>
>> PRAGMA FOREIGN_KEYS=1;
>> CREATE TABLE cat(id INTEGER PRIMARY KEY, name);
>> INSERT INTO cat VALUES (1, 'Alice');
>> CREATE TABLE owner(pet INTEGER REFERENCES cat(id));
>> INSERT INTO owner VALUES(1);
>>
>> This script fails to drop tables with  'foreign key constraint failed':
>>
>> SAVEPOINT edit;
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> RELEASE edit;
>> PRAGMA FOREIGN_KEYS=1;
>
> You are DROPping your tables in the wrong order.  The 'owner' table refers to 
> the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' table 
> which refers to a table which doesn't exist.  Swap the order of the DROPs and 
> your database will not risk that kind of disaster.
>
>> This script works OK:
>>
>> PRAGMA FOREIGN_KEYS=0;
>> DROP TABLE cat;
>> DROP TABLE owner;
>> PRAGMA FOREIGN_KEYS=1;
>
> Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
> you're going to do the RELEASE.  If you left out the second DROP, then issued 
> a RELEASE, the database would be corrupt, so SQLite issues the error message 
> to warn you about it.  The version without the SAVEPOINT never has to worry 
> about you doing that.
>
> Simon.

http://www.sqlite.org/pragma.html#pragma_foreign_keys:

"...foreign key constraint enforcement may only be enabled or disabled
when there is no pending BEGIN or SAVEPOINT. "

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Simon Slavin]

On 13 May 2013, at 3:54pm, Григорий Григоренко  wrote:

> sample database is:
> 
> PRAGMA FOREIGN_KEYS=1;
> CREATE TABLE cat(id INTEGER PRIMARY KEY, name); 
> INSERT INTO cat VALUES (1, 'Alice');
> CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); 
> INSERT INTO owner VALUES(1);
> 
> This script fails to drop tables with  'foreign key constraint failed':
> 
> SAVEPOINT edit;
> PRAGMA FOREIGN_KEYS=0;
> DROP TABLE cat;
> DROP TABLE owner;
> RELEASE edit; 
> PRAGMA FOREIGN_KEYS=1;

You are DROPping your tables in the wrong order.  The 'owner' table refers to 
the 'cat' table.  If you DROP 'cat' you would be left with an 'owner' table 
which refers to a table which doesn't exist.  Swap the order of the DROPs and 
your database will not risk that kind of disaster.

> This script works OK:
> 
> PRAGMA FOREIGN_KEYS=0;
> DROP TABLE cat;
> DROP TABLE owner;
> PRAGMA FOREIGN_KEYS=1;

Your problem with the one with SAVEPOINT is that SQLite doesn't know when 
you're going to do the RELEASE.  If you left out the second DROP, then issued a 
RELEASE, the database would be corrupt, so SQLite issues the error message to 
warn you about it.  The version without the SAVEPOINT never has to worry about 
you doing that.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign Key constraint problem while dropping tables inside transaction

[Григорий Григоренко]

 Hi,

sample database is:

PRAGMA FOREIGN_KEYS=1;
CREATE TABLE cat(id INTEGER PRIMARY KEY, name); 
INSERT INTO cat VALUES (1, 'Alice');
CREATE TABLE owner(pet INTEGER REFERENCES cat(id)); 
INSERT INTO owner VALUES(1);

This script fails to drop tables with  'foreign key constraint failed':

SAVEPOINT edit;
PRAGMA FOREIGN_KEYS=0;
DROP TABLE cat;
DROP TABLE owner;
RELEASE edit; 
PRAGMA FOREIGN_KEYS=1;

This script works OK:

PRAGMA FOREIGN_KEYS=0;
DROP TABLE cat;
DROP TABLE owner;
PRAGMA FOREIGN_KEYS=1;


Why?

--  Григорий Григоренко
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Store sqlite database problem

[Fehmi Noyan ISI]

This question is more C related than SQlite...
You were advised to use www.programming.com in your previous posts, portal has 
a good programming forum.




 From: Newbie89 <sh_ta...@hotmail.com>
To: sqlite-users@sqlite.org 
Sent: Thursday, May 9, 2013 1:40 AM
Subject: [sqlite] Store sqlite database problem
 

How to store data for 1 minute then update the database?any idea to do
it?using sqlite and c language...is it just use delete and insert query to
make it? 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Store-sqlite-database-problem-tp68745.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Store sqlite database problem

[Newbie89]

How to store data for 1 minute then update the database?any idea to do
it?using sqlite and c language...is it just use delete and insert query to
make it? 



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Store-sqlite-database-problem-tp68745.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert into table problem

[Clemens Ladisch]

yanhong ye wrote:
>> insert into table3(name,mark,mdate) select name,mark,now() from table2
>> where  table2.name=table1.name;
>
> Not work, I don't know what's problem.

Did you actually read the error messages?

sqlite> create table table1(name);
sqlite> create table table2(name,mark);
sqlite> create table table3(name,mark,mdate);
sqlite> insert into table3(name,mark,mdate) select name,mark,now() from table2
   ...> where  table2.name=table1.name;
Error: no such function: now
sqlite> insert into table3(name,mark,mdate) select name,mark,current_timestamp 
from table2
   ...> where  table2.name=table1.name;
Error: no such column: table1.name

You have to add table1 to the FROM clause, if you want to have a join.
Or not.  Maybe you should execute "DROP TABLE table2;".  It's impossible
to know without you telling us what problem you want to solve.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] insert into table problem

[yanhong ye]

>
>insert into table3(name,mark,mdate) select name,mark,now() from table2
>where  table2.name=table1.name;

Not work, I don't know what's problem.


_______
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

I can confirm that the problem only exist when the database is encrypted.

On Sat, Sep 15, 2012 at 11:00 AM, Serge Fournier  wrote:

> I work with an encrypted database and it look like it could be part of the
> problem.
>
>
> On Wed, Sep 12, 2012 at 3:05 PM, Serge Fournier  wrote:
>
>> Doing more testing on this I can say that I have the same problems with
>> Windows 7.
>>
>>
>> On Wed, Sep 12, 2012 at 8:08 AM, Serge Fournier wrote:
>>
>>> The thing here is that my code was working perfectly with the prior
>>> version of the library and Windows 7.
>>> The same code with 1.0.82 and Windows 8 doesn't work anymore.
>>>
>>> I cannot use your solution, because the way my appliation works; it
>>> creates the database at first run and
>>> miust be able to run on many computers.
>>>
>>> I've tried recreating the database; and so many things; the only thing
>>> that works is to only
>>> open the database once in my application which is no a good solution for
>>> me since I have to
>>> remove some functions like VACUUM etc...
>>> Thanks.
>>> On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
>>> brandonskypime...@gmail.com> wrote:
>>>
 I've experienced this problem in the past. According to
 here,
 I found a solution.

 *Brandon's Tips on How to Unlock SQLite Databases*

1. Open a command line prompt window.
2. Type *sqlite* *dbname*.sqlite in the command line prompt,
 replacing
"dbname" with the SQLite database name.
3. In the secondary prompt that appears, type *.backup* full *dbname*
.sqlite, replacing "dbname" with the SQLite database name.
4. Type *.exit *to return to the ordinary command prompt.
5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
 *dbname*
.sqlite in the command line, replacing "dbname" with the SQLite
 database
name.
6. Try the task you were attempting to do before you did all of the
above instructions.
7. *You are done!*

 *Brandon Sky Pimenta*


 On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
 wrote:

 > Hi,
 >
 > I'm not sure if it's Windows 8 or the latest version of the library
 > (1.0.82) but I have a new
 > problem with the same code that was working before.
 >
 > Simply put, i I open a database and then close it in an application
 then I
 > reopen it again,
 > I cannot write to it anymore; it is always locked. I tried doing a
 close,
 > dispose and shutdown
 > to my connection and then reopning it and do a simple vaccum; it tell
 me
 > that there's still
 > sql queries in progress when none were sent to the database.
 >
 > I cannot test this in Windows 7 yet, but I'm wandering if it's a
 problem
 > with Windows 8; or
 > a bug in the new version of System.Data.SQlite.
 >
 > I use Visual Studio 2010.
 >
 > Thanks.
 > ___
 > sqlite-users mailing list
 > sqlite-users@sqlite.org
 > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
 >
 ___
 sqlite-users mailing list
 sqlite-users@sqlite.org
 http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

>>>
>>>
>>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Doing more testing on this I can say that I have the same problems with
Windows 7.

On Wed, Sep 12, 2012 at 8:08 AM, Serge Fournier  wrote:

> The thing here is that my code was working perfectly with the prior
> version of the library and Windows 7.
> The same code with 1.0.82 and Windows 8 doesn't work anymore.
>
> I cannot use your solution, because the way my appliation works; it
> creates the database at first run and
> miust be able to run on many computers.
>
> I've tried recreating the database; and so many things; the only thing
> that works is to only
> open the database once in my application which is no a good solution for
> me since I have to
> remove some functions like VACUUM etc...
> Thanks.
> On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
> brandonskypime...@gmail.com> wrote:
>
>> I've experienced this problem in the past. According to
>> here,
>> I found a solution.
>>
>> *Brandon's Tips on How to Unlock SQLite Databases*
>>
>>1. Open a command line prompt window.
>>2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
>>"dbname" with the SQLite database name.
>>3. In the secondary prompt that appears, type *.backup* full *dbname*
>>.sqlite, replacing "dbname" with the SQLite database name.
>>4. Type *.exit *to return to the ordinary command prompt.
>>5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
>> *dbname*
>>.sqlite in the command line, replacing "dbname" with the SQLite
>> database
>>name.
>>6. Try the task you were attempting to do before you did all of the
>>above instructions.
>>7. *You are done!*
>>
>> *Brandon Sky Pimenta*
>>
>>
>> On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
>> wrote:
>>
>> > Hi,
>> >
>> > I'm not sure if it's Windows 8 or the latest version of the library
>> > (1.0.82) but I have a new
>> > problem with the same code that was working before.
>> >
>> > Simply put, i I open a database and then close it in an application
>> then I
>> > reopen it again,
>> > I cannot write to it anymore; it is always locked. I tried doing a
>> close,
>> > dispose and shutdown
>> > to my connection and then reopning it and do a simple vaccum; it tell me
>> > that there's still
>> > sql queries in progress when none were sent to the database.
>> >
>> > I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
>> > with Windows 8; or
>> > a bug in the new version of System.Data.SQlite.
>> >
>> > I use Visual Studio 2010.
>> >
>> > Thanks.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

The thing here is that my code was working perfectly with the prior version
of the library and Windows 7.
The same code with 1.0.82 and Windows 8 doesn't work anymore.

I cannot use your solution, because the way my appliation works; it creates
the database at first run and
miust be able to run on many computers.

I've tried recreating the database; and so many things; the only thing that
works is to only
open the database once in my application which is no a good solution for me
since I have to
remove some functions like VACUUM etc...
Thanks.
On Tue, Sep 11, 2012 at 3:18 PM, Brandon Pimenta <
brandonskypime...@gmail.com> wrote:

> I've experienced this problem in the past. According to
> here,
> I found a solution.
>
> *Brandon's Tips on How to Unlock SQLite Databases*
>
>1. Open a command line prompt window.
>2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
>"dbname" with the SQLite database name.
>3. In the secondary prompt that appears, type *.backup* full *dbname*
>.sqlite, replacing "dbname" with the SQLite database name.
>4. Type *.exit *to return to the ordinary command prompt.
>5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite
> *dbname*
>.sqlite in the command line, replacing "dbname" with the SQLite database
>name.
>6. Try the task you were attempting to do before you did all of the
>above instructions.
>7. *You are done!*
>
> *Brandon Sky Pimenta*
>
>
> On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier 
> wrote:
>
> > Hi,
> >
> > I'm not sure if it's Windows 8 or the latest version of the library
> > (1.0.82) but I have a new
> > problem with the same code that was working before.
> >
> > Simply put, i I open a database and then close it in an application then
> I
> > reopen it again,
> > I cannot write to it anymore; it is always locked. I tried doing a close,
> > dispose and shutdown
> > to my connection and then reopning it and do a simple vaccum; it tell me
> > that there's still
> > sql queries in progress when none were sent to the database.
> >
> > I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
> > with Windows 8; or
> > a bug in the new version of System.Data.SQlite.
> >
> > I use Visual Studio 2010.
> >
> > Thanks.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Did you try it with a Windows 8? I have a feeling that the problem is more
with this OS
then the library.

Here's something simple that doesn't work:

SQLconnect.ConnectionString = "Data Source=" & Application.StartupPath &
"\data.db;"

 SQLconnect.SetPassword(infoConnection)

SQLconnect.Open()

sqlconnect.close()
sqlconnect.dispose()


Dim SQLconnect As New SQLite.SQLiteConnection

Dim SQLcommand As New SQLite.SQLiteCommand

Dim SQLreader As SQLite.SQLiteDataReader

Dim SQLquery As String

SQLconnect.ConnectionString = "Data Source=" & Application.StartupPath &
"\data.db;




SQLconnect.SetPassword(infoConnection)

SQLconnect.Open()

SQLquery = "VACUUM"

Try

SQLcommand = SQLconnect.CreateCommand

SQLcommand.CommandText = SQLquery

SQLcommand.ExecuteNonQuery()

SQLcommand.Dispose()

Catch sqlexception As SQLite.SQLiteException

MessageBox.Show(sqlexception.Message, "Error!", MessageBoxButtons.OK,
MessageBoxIcon.Error)

Catch ex As Exception

MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon
.Error)

End Try

It gives the "Still SQL queries to process" error.


On Tue, Sep 11, 2012 at 10:07 PM, Joe Mistachkin wrote:

>
> Serge Fournier wrote:
> >
> > Simply put, it's only possible to open a database once in the application
> > for writing; it's like the lock
> > doesn't get released until the application is closed.
> >
>
> I'm unable to reproduce the issue you describe here.  Could you show us
> some
> sample code that demonstrates the issue?
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Brandon Pimenta]

I've experienced this problem in the past. According to
here,
I found a solution.

*Brandon's Tips on How to Unlock SQLite Databases*

   1. Open a command line prompt window.
   2. Type *sqlite* *dbname*.sqlite in the command line prompt, replacing
   "dbname" with the SQLite database name.
   3. In the secondary prompt that appears, type *.backup* full *dbname*
   .sqlite, replacing "dbname" with the SQLite database name.
   4. Type *.exit *to return to the ordinary command prompt.
   5. Type *mv* *dbname*.sqlite old.sqlite, then *mv* backup.sqlite *dbname*
   .sqlite in the command line, replacing "dbname" with the SQLite database
   name.
   6. Try the task you were attempting to do before you did all of the
   above instructions.
   7. *You are done!*

*Brandon Sky Pimenta*


On Tue, Sep 11, 2012 at 10:35 AM, Serge Fournier  wrote:

> Hi,
>
> I'm not sure if it's Windows 8 or the latest version of the library
> (1.0.82) but I have a new
> problem with the same code that was working before.
>
> Simply put, i I open a database and then close it in an application then I
> reopen it again,
> I cannot write to it anymore; it is always locked. I tried doing a close,
> dispose and shutdown
> to my connection and then reopning it and do a simple vaccum; it tell me
> that there's still
> sql queries in progress when none were sent to the database.
>
> I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
> with Windows 8; or
> a bug in the new version of System.Data.SQlite.
>
> I use Visual Studio 2010.
>
> Thanks.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Joe Mistachkin]

Serge Fournier wrote:
> 
> Simply put, it's only possible to open a database once in the application
> for writing; it's like the lock
> doesn't get released until the application is closed.
> 

I'm unable to reproduce the issue you describe here.  Could you show us some
sample code that demonstrates the issue?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Yes, all command have been disposed; everything works until I close the
connection. Then when
I open it again it the same application; either I get a "database is
locked" when trying to write to it
or I get "SQL queries are still processing" when I just opened it and send
a VACUUM pragma to it.

Simply put, it's only possible to open a database once in the application
for writing; it's like the lock
doesn't get released until the application is closed.
On Tue, Sep 11, 2012 at 5:59 PM, Joe Mistachkin wrote:

>
> Serge Fournier wrote:
> >
> > Simply put, I open a database and then close it in an application then I
> > reopen it again,
> > I cannot write to it anymore; it is always locked. I tried doing a close,
> > dispose and shutdown
> > to my connection and then reopning it and do a simple vaccum; it tell me
> > that there's still
> > sql queries in progress when none were sent to the database.
> >
>
> Have all the SQLiteDataReader and SQLiteCommand objects been properly
> disposed?  What is the exception you are seeing?
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data>SQLite locking problem

[Joe Mistachkin]

Serge Fournier wrote:
> 
> Simply put, I open a database and then close it in an application then I
> reopen it again,
> I cannot write to it anymore; it is always locked. I tried doing a close,
> dispose and shutdown
> to my connection and then reopning it and do a simple vaccum; it tell me
> that there's still
> sql queries in progress when none were sent to the database.
> 

Have all the SQLiteDataReader and SQLiteCommand objects been properly
disposed?  What is the exception you are seeing?

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data>SQLite locking problem

[Serge Fournier]

Hi,

I'm not sure if it's Windows 8 or the latest version of the library
(1.0.82) but I have a new
problem with the same code that was working before.

Simply put, i I open a database and then close it in an application then I
reopen it again,
I cannot write to it anymore; it is always locked. I tried doing a close,
dispose and shutdown
to my connection and then reopning it and do a simple vaccum; it tell me
that there's still
sql queries in progress when none were sent to the database.

I cannot test this in Windows 7 yet, but I'm wandering if it's a problem
with Windows 8; or
a bug in the new version of System.Data.SQlite.

I use Visual Studio 2010.

Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query function release problem

[Igor Tandetnik]

YAN HONG YE  wrote:
> char *sql;
> sprintf(sql,"select * from  myprivate;");

Start by changing this to

sql = "select * from  myprivate;";

> free(sql);
> free(result);

... and dropping these two. You should only free() what you've malloc'ed, and 
you haven't malloc'ed either of them.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query function release problem

[Simon Davies]

On 2 August 2012 08:28, YAN HONG YE  wrote:
.
.
.
> when I use command
> " jii select ccc"
> it shows a problem:
>
> "jii.exe has encountered a problem and needs to close.  We are sorry for the 
> inconvenience."
>
> I couldn't resolve it.
>
> void query_table(sqlite3 *l_db)
> {
> char **result;
> char buffer2[324]="0";
> int row,column;
> char *sql;
>
> char *error;
> int i;
>
> sprintf(sql,"select * from  myprivate;");

Perhaps you intended sql to have been malloc'd here...

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query function release problem

[YAN HONG YE]

You could download the origin zip source file by the link:

www.yycred.com/sqlte_crypto1.zip

when I use command 
" jii select ccc" 
it shows a problem:

"jii.exe has encountered a problem and needs to close.  We are sorry for the 
inconvenience."

I couldn't resolve it.


void query_table(sqlite3 *l_db) 
{
char **result;
char buffer2[324]="0";
int row,column;
char *sql;

char *error;
int i;

sprintf(sql,"select * from  myprivate;");
sqlite3_get_table(l_db,sql);


for(i=0;i<(row+1)*column;i++)
{
if(i<2 || i%3==0)
{
printf("%-24s|",result[i]);

continue;
}
if((i+1)%3 == 0)
{
//printf("hello\n");
printf("%-8s;\n",result[i]);

continue;
}

//printf("%s\n",result[i]);
char *ppu=ldll(sqlkey,result[i],2);
printf("%-24s|",ppu,2);
free(ppu);

}
sqlite3_free_table(result); 
printf("d\n");
free(sql);
free(result);


}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such function problem

[Rob Richardson]

This is a C question, not an SQLite question.  But I'll answer it anyway.

You included the function call in your quoted string, so your compiler thinks 
it's just random text.  You need:

sprint(sql, "insert into student select 3, %s, 22;", ldll("bb"));

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] no such function problem

[YAN HONG YE]

I write a function in my program:

char *ldll(char *s1)
{
return s1;
}

and use it to modify my sqlite db:

sprintf(sql,"insert into student select 3,ldll('bb'),22;");

but the error msg is "no such function: ldll"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Static linking. Was: Problem with SQLite when deployed.

[Richard Hipp]

We were just having a discussion on a different thread on the relative
merits of static versus dynamic linking.

Notice that if the application where statically linked (if that were
possible in C#) then all the grief that Peter is having would have never
come up.  The application would just work, and everybody would be happy.

On Fri, May 25, 2012 at 10:56 AM, Peter Walburn <
peter.walb...@omega-data.com> wrote:

> Hi,
>
> I have an application written in C# .Net 4.0 Framework.  I use SQLite
> within the application.  I have recently updated from an older version of
> SQLite to the latest version as I have moved to .Net Framework 4.0 and I
> received error messages about Mixed Mode Frameworks.
>
> Anyway, I do the development on an 64-Bit Windows 7 operating system.  I
> use Installshield 2010 Express to create an installation for the
> application.  The application works ok on the Windows 7 PC, but when
> installed on a different PC (or on a Virtual Client PC using VMWare), I
> always receive a message such as:
>
> Unhandled exception has occurred in your application.  If you click
> Continue, the application will ignore this error and attempt to continue.
> If you click Quit, the application will close immediately.
>
> Unable to load DLL 'SQLite.Interop.dll': The specified module could not be
> found. (Exception from HRESULT: 0x8007007e).
>
> I have tried to post this email about 5 times and it is always returned
> saying: "The message's content type was not explicitly allowed."
>
>
>
> Peter Walburn
> Software Engineer
> E-mail: peter.walb...@omega-data.com
> Units 44-46 Howe Moss Avenue, Kirkhill Industrial Estate,
> Dyce, Aberdeen AB21 0GP
> Tel: +44 (0)1224 772763
> Fax: +44 (0)1224 772783
> www.omega-data.com
>
>
>
>
>
> This is an email from Omega Data Services Ltd, a company registered in
> Edinburgh, Scotland, with company number SC192323. Registered Office:
> Maclay Murray & Spens, 66 Queens Road, Aberdeen, AB15 4YE. Tel:
> 01224-356130. Website: www.omega-data.com .
> This email and any files transmitted are confidential and intended solely
> for the individual or entity to whom they are addressed. Any views or
> opinions expressed or presented are those of the author(s) and may not
> necessarily represent those of the company and no representation is given
> nor liability accepted for the accuracy or completeness of any information
> contained in this email unless expressly stated to the contrary. If you are
> not the intended recipient or have received this email in error, you may
> not use, disseminate, forward, print or copy it, but please notify the
> sender that you have received it in error and remove the message from your
> system immediately.
> Whilst we have taken reasonable precautions to ensure that this email and
> any attachments have been checked for viruses, we cannot guarantee that
> they are virus free, and we cannot accept liability for any damage
> sustained as a result of software viruses. We would advise that you carry
> out your own virus checks, especially before opening an attachment.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Nick]

Hi Michael, Kreith, Jonas,

Thanks for your response. I just tried to launch the binary manually,
and it worked very fast. I didn't figure out by myself that i
should've tried to launch the application outside of Visual Studio (I
was thinking that in "Release" mode VS doesn't slow down the execution
by debugging instruments). Sorry for raising a dust with all this.


2012/5/25 Black, Michael (IS) <michael.bla...@ngc.com>:
> Usng your sqlite3.exe
> CPU Time: user 2.156250 sys 2.078125
>
> Using your sqlite3.console.exe
> CPU Time: user 1.375000 sys 0.140625
>
> I'm afraid I don't see the problem since the pre-built is slower than your 
> executable for me.
>
>
> Michael D. Black
> Senior Scientist
> Advanced Analytics Directorate
> Advanced GEOINT Solutions Operating Unit
> Northrop Grumman Information Systems
>
>
>
> From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
> behalf of Nick [eveningn...@gmail.com]
> Sent: Thursday, May 24, 2012 5:49 PM
> To: General Discussion of SQLite Database
> Subject: EXT :Re: [sqlite] SQLite SELECT performance problem
>
>
> The sizes of the executable files are almost identical - there's a few
> kilobytes difference.
> I have attached the original (downloaded from sqlite.org) sqlite3.exe,
> a compiled-by-myself sqlite3console.exe. And the source code. Also
> there's import tables dump (import tables are also very similar for
> both executables). I hope you can see the difference in the speed:
>
> http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar
>
> There is also a database file. but it is quite large (71 Mb). You can
> download it here, to be able to test the SQL query:
>
> http://dl.dropbox.com/u/74970714/database.sqlite
>
> Thanks to anyone who can help!
>
> 2012/5/25 Simon Slavin <slav...@bigfraud.org>:
>>
>> On 24 May 2012, at 11:13pm, Nick <eveningn...@gmail.com> wrote:
>>
>>> In my initial message I described some proof-of-concept that I've done.
>>>
>>> I downloaded sqlite3.exe (An SQLite command line tool) from the
>>> SQLite's website. I executed my query and I had to wait 4 seconds for
>>> it to complete.
>>>
>>> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
>>> altogether (and got again the command line tool, but now i've built it
>>> by myself using Visual Studio) and executed the same query. It took 15
>>> seconds this time.
>>
>> I'm very sorry I missed that.  I have now re-read your original post.
>>
>> Can you compare the size of the two executable files for us ?  Are the 
>> various versions you're compiling (I understand you've tried several 
>> different compilation options) all definitely bigger than the one supplied 
>> on the SQLite site ?  It might give us something to investigate.  Also, I 
>> don't know how to do this under Windows, but do you have a way to check 
>> whether the versions made by Visual Studio address any DLLs or other 
>> libraries ?
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Black, Michael (IS)]

Usng your sqlite3.exe
CPU Time: user 2.156250 sys 2.078125

Using your sqlite3.console.exe
CPU Time: user 1.375000 sys 0.140625

I'm afraid I don't see the problem since the pre-built is slower than your 
executable for me.


Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems



From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Nick [eveningn...@gmail.com]
Sent: Thursday, May 24, 2012 5:49 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] SQLite SELECT performance problem


The sizes of the executable files are almost identical - there's a few
kilobytes difference.
I have attached the original (downloaded from sqlite.org) sqlite3.exe,
a compiled-by-myself sqlite3console.exe. And the source code. Also
there's import tables dump (import tables are also very similar for
both executables). I hope you can see the difference in the speed:

http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar

There is also a database file. but it is quite large (71 Mb). You can
download it here, to be able to test the SQL query:

http://dl.dropbox.com/u/74970714/database.sqlite

Thanks to anyone who can help!

2012/5/25 Simon Slavin <slav...@bigfraud.org>:
>
> On 24 May 2012, at 11:13pm, Nick <eveningn...@gmail.com> wrote:
>
>> In my initial message I described some proof-of-concept that I've done.
>>
>> I downloaded sqlite3.exe (An SQLite command line tool) from the
>> SQLite's website. I executed my query and I had to wait 4 seconds for
>> it to complete.
>>
>> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
>> altogether (and got again the command line tool, but now i've built it
>> by myself using Visual Studio) and executed the same query. It took 15
>> seconds this time.
>
> I'm very sorry I missed that.  I have now re-read your original post.
>
> Can you compare the size of the two executable files for us ?  Are the 
> various versions you're compiling (I understand you've tried several 
> different compilation options) all definitely bigger than the one supplied on 
> the SQLite site ?  It might give us something to investigate.  Also, I don't 
> know how to do this under Windows, but do you have a way to check whether the 
> versions made by Visual Studio address any DLLs or other libraries ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Keith Medcalf]

I tried and got this:

D:\Temp\Test>timethis custom\sqlite3console database.sqlite < query.sql

TimeThis :  Command Line :  custom\sqlite3console database.sqlite
TimeThis :Start Time :  Thu May 24 18:42:51 2012
TimeThis :  End Time :  Thu May 24 18:42:54 2012
TimeThis :  Elapsed Time :  00:00:03.421

D:\Temp\Test>timethis custom\sqlite3 database.sqlite < query.sql

TimeThis :  Command Line :  custom\sqlite3 database.sqlite
TimeThis :Start Time :  Thu May 24 18:42:59 2012
TimeThis :  End Time :  Thu May 24 18:43:06 2012
TimeThis :  Elapsed Time :  00:00:07.078

D:\Temp\Test>timethis sqlite database.sqlite < query.sql

TimeThis :  Command Line :  sqlite database.sqlite
TimeThis :Start Time :  Thu May 24 18:43:09 2012
TimeThis :  End Time :  Thu May 24 18:43:13 2012
TimeThis :  Elapsed Time :  00:00:03.609

This latter is my build of the command line tools which is 800K.

This is on a 2Ghz AMD Turion.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Jonas Malaco Filho
> Sent: Thursday, 24 May, 2012 18:15
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite SELECT performance problem
> 
> I've tested your database and query, using the official sqlite3.exe and the
> versions I compile myself (Microsoft Visual Studio 2010, x86 and x64):
> 
>- Official (x86): ~5s (CPU Time: user 1.965613 sys 2.527216)
>- Compiled by me (x86): ~1s (CPU Time: user 1.154407 sys 0.187201)
>- Compiled by me (x64): ~1s (CPU Time: user 0.904806 sys 0.218401)
> 
> My SQLite compilation options differ from the default values (so this does
> not address the weird behavior you described), but maybe it can still be
> useful for you:
> 
>- x86: /DSQLITE_DEFAULT_CACHE_SIZE=2000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
>/DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=4096
>/DSQLITE_DEFAULT_TEMP_CACHE_SIZE=2000 /DSQLITE_THREADSAFE=0
>/DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3
> /DSQLITE_ENABLE_FTS3_PARENTHESIS
>/DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
>- x64: /DSQLITE_DEFAULT_CACHE_SIZE=4000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
>/DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=8192
>/DSQLITE_DEFAULT_TEMP_CACHE_SIZE=4000 /DSQLITE_THREADSAFE=0
>/DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3
> /DSQLITE_ENABLE_FTS3_PARENTHESIS
>/DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
> 
> Both use:
> 
>- Full Optimization (/Ox)
>- (Inlining) Any Suitable (/Ob2)
>- (Enable Intrinsic Functions) Yes (/Oi)
>- Favor fast code (/Ot)
>- Use MFC in a Static Library
> 
> Everything is always in my Dropbox:
> https://www.dropbox.com/sh/ecyzbn5kz4nf7ft/JHGD62iezx
> 
> 
> 
> *Jonas Malaco Filho*
> 
> 
> 
> 2012/5/24 Simon Slavin <slav...@bigfraud.org>
> 
> >
> > On 24 May 2012, at 11:49pm, Nick <eveningn...@gmail.com> wrote:
> >
> > > The sizes of the executable files are almost identical - there's a few
> > > kilobytes difference.
> > > I have attached the original (downloaded from sqlite.org) sqlite3.exe,
> > > a compiled-by-myself sqlite3console.exe. And the source code.
> >
> > Attachments don't work on this list (we don't want everyone sending us
> > their homework) but I hope the information you've provided gives someone a
> > hint about what's going on.
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Keith Medcalf]

And don't forget /D_CRT_DISABLE_PERFCRIT_LOCKS
And make sure you link with the static library (/MT) not the dynamic runtime.

Visual Studio no longer has a single threaded library.  You have to define 
_CRT_DISABLE_PERFCRIT_LOCKS in order to get the compiler to generate code and 
use library routines devoid of extra spinlocks, critical sections, and mutexes 
(ie, to get the same performance as you would get linking with the standard 
LIBC.LIB single-threaded library).

Generated code is still of "Microsoft Quality" though (if you want proper 
instruction scheduling and optimization that actual optimizes anything, then 
use a real compiler, such as gcc or watcom).  I only use MSVC in order to 
integrate the runtimes with other things which use the MS compilers, not 
because they are of any worth.

Also, depending on what the data looks like that you are averaging the 
sum/count average builtin to sqlite may not be very accurate.  You may want to 
do use a running approximation as it is computationally insensitive to the data 
values, ordering, and magnitudes.


typedef struct meanCtx meanCtx;
struct meanCtx
{
long double m;
sqlite_int64 n;
};

static void meanStep(sqlite3_context *context, int argc, sqlite3_value **argv)
{
meanCtx* p = sqlite3_aggregate_context(context, sizeof(meanCtx));
if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL)
{
p->n++;
if (p->n == 1)
p->m = (long double)sqlite3_value_double(argv[0]);
else
p->m += ((long double)sqlite3_value_double(argv[0]) - p->m) / (long 
double)p->n;
}
}

static void meanFinal(sqlite3_context *context)
{
meanCtx* p = sqlite3_aggregate_context(context, 0);
if (p && p->n > 0)
sqlite3_result_double(context, p->m);
}

You can look at http://www.dessus.com/files/sqlite.zip which is a zip of my 
build directory that has an autoload extension (SQLFunc.c) compiled in that 
adds various running statistical aggregates (I make no warranty as to 
correctness by the way -- if you find there is an error please let me know -- 
my brother is the mathematician, not me).  

All aggregates use successive approximation series computation methods.   Also 
includes some useful Windoze Security APIs (get username, check security 
groups, etc) and a hack-in of the test_spellfix1.c module which is also an 
autoloaded extension if you leave the /DINCL_SPELLFIX in the compiler options.  
I'm going to replace that with unifuzz.c recently mentioned since I like that 
better, and extend the selection process and autoinit process.  Eventually I'll 
make a proper makefile.  It has all the options recognized in the amalgamation 
code included.

No warranties expressed or implied.  If it breaks or you break it then you own 
both halves.

---
()  ascii ribbon campaign against html e-mail
/\  www.asciiribbon.org


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: Thursday, 24 May, 2012 14:10
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite SELECT performance problem
> 
> On Thu, May 24, 2012 at 3:59 PM, Nick <eveningn...@gmail.com> wrote:
> 
> >
> > Any way I could make my C program execute this query as fast as the
> > prebuilt command line tool does it?
> >
> 
> 
> Have you tried compiling with the -DSQLITE_THREADSAFE=0 option?
> 
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Jonas Malaco Filho]

I've tested your database and query, using the official sqlite3.exe and the
versions I compile myself (Microsoft Visual Studio 2010, x86 and x64):

   - Official (x86): ~5s (CPU Time: user 1.965613 sys 2.527216)
   - Compiled by me (x86): ~1s (CPU Time: user 1.154407 sys 0.187201)
   - Compiled by me (x64): ~1s (CPU Time: user 0.904806 sys 0.218401)

My SQLite compilation options differ from the default values (so this does
not address the weird behavior you described), but maybe it can still be
useful for you:

   - x86: /DSQLITE_DEFAULT_CACHE_SIZE=2000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
   /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=4096
   /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=2000 /DSQLITE_THREADSAFE=0
   /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS
   /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3
   - x64: /DSQLITE_DEFAULT_CACHE_SIZE=4000 /DSQLITE_DEFAULT_FOREIGN_KEYS=1
   /DSQLITE_DEFAULT_MEMSTATUS=0 /DSQLITE_DEFAULT_PAGE_SIZE=8192
   /DSQLITE_DEFAULT_TEMP_CACHE_SIZE=4000 /DSQLITE_THREADSAFE=0
   /DSQLITE_TEMP_STORE=2 /DSQLITE_ENABLE_FTS3 /DSQLITE_ENABLE_FTS3_PARENTHESIS
   /DSQLITE_ENABLE_FTS4 /DSQLITE_ENABLE_RTREE /DSQLITE_ENABLE_STAT3

Both use:

   - Full Optimization (/Ox)
   - (Inlining) Any Suitable (/Ob2)
   - (Enable Intrinsic Functions) Yes (/Oi)
   - Favor fast code (/Ot)
   - Use MFC in a Static Library

Everything is always in my Dropbox:
https://www.dropbox.com/sh/ecyzbn5kz4nf7ft/JHGD62iezx



*Jonas Malaco Filho*



2012/5/24 Simon Slavin 

>
> On 24 May 2012, at 11:49pm, Nick  wrote:
>
> > The sizes of the executable files are almost identical - there's a few
> > kilobytes difference.
> > I have attached the original (downloaded from sqlite.org) sqlite3.exe,
> > a compiled-by-myself sqlite3console.exe. And the source code.
>
> Attachments don't work on this list (we don't want everyone sending us
> their homework) but I hope the information you've provided gives someone a
> hint about what's going on.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Simon Slavin]

On 24 May 2012, at 11:49pm, Nick  wrote:

> The sizes of the executable files are almost identical - there's a few
> kilobytes difference.
> I have attached the original (downloaded from sqlite.org) sqlite3.exe,
> a compiled-by-myself sqlite3console.exe. And the source code.

Attachments don't work on this list (we don't want everyone sending us their 
homework) but I hope the information you've provided gives someone a hint about 
what's going on.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Nick]

The sizes of the executable files are almost identical - there's a few
kilobytes difference.
I have attached the original (downloaded from sqlite.org) sqlite3.exe,
a compiled-by-myself sqlite3console.exe. And the source code. Also
there's import tables dump (import tables are also very similar for
both executables). I hope you can see the difference in the speed:

http://dl.dropbox.com/u/74970714/Custom%20built%20SQLite3.exe%20Performance%20problem.rar

There is also a database file. but it is quite large (71 Mb). You can
download it here, to be able to test the SQL query:

http://dl.dropbox.com/u/74970714/database.sqlite

Thanks to anyone who can help!

2012/5/25 Simon Slavin :
>
> On 24 May 2012, at 11:13pm, Nick  wrote:
>
>> In my initial message I described some proof-of-concept that I've done.
>>
>> I downloaded sqlite3.exe (An SQLite command line tool) from the
>> SQLite's website. I executed my query and I had to wait 4 seconds for
>> it to complete.
>>
>> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
>> altogether (and got again the command line tool, but now i've built it
>> by myself using Visual Studio) and executed the same query. It took 15
>> seconds this time.
>
> I'm very sorry I missed that.  I have now re-read your original post.
>
> Can you compare the size of the two executable files for us ?  Are the 
> various versions you're compiling (I understand you've tried several 
> different compilation options) all definitely bigger than the one supplied on 
> the SQLite site ?  It might give us something to investigate.  Also, I don't 
> know how to do this under Windows, but do you have a way to check whether the 
> versions made by Visual Studio address any DLLs or other libraries ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Simon Slavin]

On 24 May 2012, at 11:13pm, Nick  wrote:

> In my initial message I described some proof-of-concept that I've done.
> 
> I downloaded sqlite3.exe (An SQLite command line tool) from the
> SQLite's website. I executed my query and I had to wait 4 seconds for
> it to complete.
> 
> Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
> altogether (and got again the command line tool, but now i've built it
> by myself using Visual Studio) and executed the same query. It took 15
> seconds this time.

I'm very sorry I missed that.  I have now re-read your original post.

Can you compare the size of the two executable files for us ?  Are the various 
versions you're compiling (I understand you've tried several different 
compilation options) all definitely bigger than the one supplied on the SQLite 
site ?  It might give us something to investigate.  Also, I don't know how to 
do this under Windows, but do you have a way to check whether the versions made 
by Visual Studio address any DLLs or other libraries ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Nick]

I tried defining "SQLITE_TEMP_STORE 2" as well. Unfortunately it
doesn't influence the speed much, in my case...

2012/5/25 Jonas Malaco Filho :
> Why TEMP_STORE=1 (file by default) and not TEMP_STORE=2 (memory by default)?
>
> *Jonas Malaco Filho*
>
> 2012/5/24 Simon Slavin 
>
>>
>> On 24 May 2012, at 8:59pm, Nick  wrote:
>>
>> > So why does a prebuilt, downloaded from the sqlite website, command
>> > line tool takes only 4 seconds, while the same tool, built by me,
>> > takes 4 times longer time to execute?
>>
>> I'm wondering whether the speed increase is related to figuring out the
>> parameters in the command.  Purely out of interest, and not because I know
>> it's faster, have you tried executing the query using sqlite3_exec() rather
>> than _prepare(), _step(), _finalize() ?  Don't do a lot of reworking of
>> your code unless it turns out to be faster.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite SELECT performance problem

[Nick]

In my initial message I described some proof-of-concept that I've done.

I downloaded sqlite3.exe (An SQLite command line tool) from the
SQLite's website. I executed my query and I had to wait 4 seconds for
it to complete.

Then I downloaded sqlite3.c, sqlite3.h and shell.c, compiled them
altogether (and got again the command line tool, but now i've built it
by myself using Visual Studio) and executed the same query. It took 15
seconds this time.

I can't understand why.. I set the code optimization level to "max" in
the Visual Studio's Settings.
I also executed in the original (downloaded from sqlite.org)
sqlite3.exe a command:

  pragma compile_options;

and made sure all these options (#defines) were set in my own built of
sqlite3.exe



2012/5/25 Simon Slavin :
>
> On 24 May 2012, at 8:59pm, Nick  wrote:
>
>> So why does a prebuilt, downloaded from the sqlite website, command
>> line tool takes only 4 seconds, while the same tool, built by me,
>> takes 4 times longer time to execute?
>
> I'm wondering whether the speed increase is related to figuring out the 
> parameters in the command.  Purely out of interest, and not because I know 
> it's faster, have you tried executing the query using sqlite3_exec() rather 
> than _prepare(), _step(), _finalize() ?  Don't do a lot of reworking of your 
> code unless it turns out to be faster.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users