Re: [sqlite] cygwin and sqlite

[Jim Showalter]

The original idea of the i86 segmented architecture was that OSs would 
be written that created zillions of bite-sized threads, each with its 
own small address space. Nobody wrote an OS to take advantage of that, 
and instead the ridiculous "far pointer" was invented to desegment the 
deliberately segmented address space. But some of the original 
threads-are-good/processes-are-bad thinking still remained in the OS.

- Original Message - 
From: "Beau Wilkinson" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, September 22, 2009 11:41 AM
Subject: Re: [sqlite] cygwin and sqlite


> >The point I was trying to make is that the majority of commands 
> >cygwin
>>supports don't have to do any forking. They could just be method
>>calls. Pipes would be parameters going in and return values coming
>>out, all running in a single process. If fork() is specifically 
>>called
>>in a script, then the slower approach would have to be used, but 
>>most
>>of the time it would not.
>
> Yes, in theory that's true, but Cygwin strives to be a lightweight 
> connecting layer between the Unix-like facilities of Windows and the 
> wealth of Unix code available. Wherever these "Unix-like" features 
> turn out to not actually not be so "Unix-like," the model breaks 
> down (e.g. fork()), and Cygwin has to be larger and more intrusive. 
> There are many other architectures in use out there. The many 
> Unix-like tools that run natively on Windows are, in some sense, an 
> example of another POSIX / Windows hybridization archietcture. But 
> this is not the architecture of Cygwin.
>
> Incidentally, I think the fundamental problem is the asinine way 
> Windows treats processes - vs. - threads. Microsoft seems to 
> encourage multithreading, not just by making processes relatively 
> heavy, but also in their documentation and examples and in the 
> overall direction of their R efforts.
>
> My opinion is that processes should be lightweight, because they 
> offer a much more predictable and useful way to effect parallel 
> processing than threads. I always try to use processes over threads 
> when I detect the need for parallelism, even in Windows. Other 
> Windows developers consider my process-spawning to be awkward, or 
> even "a hack," but as someone with Unix experience this seems 
> natural, and the end results are typically more deterministic.
>
> - Original Message -
> From: "Pavel Ivanov" 
> To: "General Discussion of SQLite Database" 
> 
> Sent: Tuesday, September 22, 2009 6:58 AM
> Subject: Re: [sqlite] cygwin and sqlite
>
>
>>> But after all these years I wonder why they don't fix the fork
>>> problem?
>>> MacOS runs on Intel processors. Windows runs on Intel processors.
>>> Surely
>>> they could learn how it *should* be done by studying things like
>>> the
>>> Open Source Java code?
>>
>> You seem to forget the basics. It's not the processor who makes
>> fork()
>> possible, it's OS. Unix kernel implemented fork() and Unix kernel
>> implemented process management in the way that makes implementation
>> of
>> fork() very quick and easy. Windows didn't implement fork() and it
>> implemented process management in the way making fork() impossible.
>> I'd say it's a superior achievement on cygwin side that they were
>> able
>> to implement fork() somehow at all. Just a simple fact: you execute
>> some code that uses memory in some way then you call fork() on Unix
>> and you already have 2 absolutely different processes that can
>> access
>> the same data in memory. On windows there's no way to start a new
>> process so that it can access the same data as first process unless
>> you thought about that beforehand, placed all your data into shared
>> memory (which is a lot harder to work with, btw) and made another
>> process to read the same shared memory. Also there's no way to 
>> start
>> new process on Windows so that it executes the same code as first
>> process from the point where second process was started...
>> So I'd better not complained but tried to understand the roots of
>> the problem...
>>
>> And if you want the comparable speed of scripts on both platforms I
>> suggest you to look into perl (or python, whatever you prefer). The
>> both have native implementations and their speed should be
>> comparable
>> to each other. That said of course if you don't start a lot of
>> processes from scripts and don't try to run command line
>> utilities...
>>
>> Pavel
>>
>> On Mon, Sep 21, 2009 at 10:29 PM, John  
>> wrote:
>>> Pavel Ivanov wrote:
> MacBook Mac OS X 10.5.8
> 2 GHz Intel Core Duo
> 1 GB memory:
> 17 minutes 46 seconds.
>
> IBM ThinkPad
> Windows XP (latest patches)
> 1.70 GHz, 512 MB memory:
> 6 hours 25 minutes 57 seconds

 Windows is very slow in starting new processes if compared to any
 Unix
 system (especially if compared 

Re: [sqlite] cygwin and sqlite

[Jim Showalter]

The point I was trying to make is that the majority of commands cygwin 
supports don't have to do any forking. They could just be method 
calls. Pipes would be parameters going in and return values coming 
out, all running in a single process. If fork() is specifically called 
in a script, then the slower approach would have to be used, but most 
of the time it would not.

- Original Message - 
From: "Pavel Ivanov" 
To: "General Discussion of SQLite Database" 
Sent: Tuesday, September 22, 2009 6:58 AM
Subject: Re: [sqlite] cygwin and sqlite


>> But after all these years I wonder why they don't fix the fork 
>> problem?
>> MacOS runs on Intel processors. Windows runs on Intel processors. 
>> Surely
>> they could learn how it *should* be done by studying things like 
>> the
>> Open Source Java code?
>
> You seem to forget the basics. It's not the processor who makes 
> fork()
> possible, it's OS. Unix kernel implemented fork() and Unix kernel
> implemented process management in the way that makes implementation 
> of
> fork() very quick and easy. Windows didn't implement fork() and it
> implemented process management in the way making fork() impossible.
> I'd say it's a superior achievement on cygwin side that they were 
> able
> to implement fork() somehow at all. Just a simple fact: you execute
> some code that uses memory in some way then you call fork() on Unix
> and you already have 2 absolutely different processes that can 
> access
> the same data in memory. On windows there's no way to start a new
> process so that it can access the same data as first process unless
> you thought about that beforehand, placed all your data into shared
> memory (which is a lot harder to work with, btw) and made another
> process to read the same shared memory. Also there's no way to start
> new process on Windows so that it executes the same code as first
> process from the point where second process was started...
> So I'd better not complained but tried to understand the roots of 
> the problem...
>
> And if you want the comparable speed of scripts on both platforms I
> suggest you to look into perl (or python, whatever you prefer). The
> both have native implementations and their speed should be 
> comparable
> to each other. That said of course if you don't start a lot of
> processes from scripts and don't try to run command line 
> utilities...
>
> Pavel
>
> On Mon, Sep 21, 2009 at 10:29 PM, John  wrote:
>> Pavel Ivanov wrote:
 MacBook Mac OS X 10.5.8
 2 GHz Intel Core Duo
 1 GB memory:
 17 minutes 46 seconds.

 IBM ThinkPad
 Windows XP (latest patches)
 1.70 GHz, 512 MB memory:
 6 hours 25 minutes 57 seconds
>>>
>>> Windows is very slow in starting new processes if compared to any 
>>> Unix
>>> system (especially if compared Windows + 512 MB and Unix + 1 GB). 
>>> In
>>> cygwin starting new processes even slower because for some reason
>>> emulating fork() involves starting 2 processes one of which dies
>>> immediately. And bash scripts use processes a lot especially with 
>>> Unix
>>> paradigm when for each small action you start new program (like 
>>> sed,
>>> awk, test, true and whole lot of others). Thus bash scripts on 
>>> cygwin
>>> will be slow unavoidably.
>>> But I'm digressing. This is subject for some other mailing list. 
>>> :)
>>
>> I'm blacklisted apparently on the cygwin mailing list for when a
>> couple of years ago I complained rather unflatteringly about how 
>> slow
>> it was/is when I was writing a simple expenses program (that works 
>> in
>> seconds on my Mac). I forgot about that. A badge of honor in my 
>> opinion.
>>
>> But after all these years I wonder why they don't fix the fork 
>> problem?
>> MacOS runs on Intel processors. Windows runs on Intel processors. 
>> Surely
>> they could learn how it *should* be done by studying things like 
>> the
>> Open Source Java code?
>>
>> It looks like I won't be able to distribute my stopgap cygwin code 
>> on
>> Windows. I need to start speed reading my Java/Swing books I guess 
>> in
>> my quest for my program to write once, run anywhere.
>>
>>> Pavel
>>>
>>> On Fri, Sep 18, 2009 at 3:26 AM, John  
>>> wrote:
 Pavel Ivanov wrote:
>> At least I think that is what you suggest, and think it just
>> may work! But I could be wrong!
> Yes, that's exactly what I suggest.
>
> Pavel
 It worked! Fortunately I had already parameterized SQLITE3 as a
 preference variable so I could have the same scripts run easily 
 on Mac
 OS and Windows. There are dozens of sqlite3 calls throughout the 
 scripts.

 My whole set of scripts that process raw data and load the 
 database by
 reading text files seem to work.

 cygwin is as slow as I recall, however. I was writing expense 
 scripts a
 few years ago and abandoned it for MacOS Unix. I moved 100% to 
 

Re: [sqlite] cygwin and sqlite

[Jim Showalter]

I thought you were exaggerating, but this weekend I wrote a script for 
work that used cygwin. It's unbelievably slow compared to the DOS 
script it replaced, and it performs mostly the same operations as the 
DOS script. Which is weird, because they wouldn't have to spawn off 
separate Windows processes at all--they could interpret the commands 
and call methods on a shared DLL, all in the same process.

It's absurd that you were blacklisted for pointing out that naked 
emperor over there behind the bushes.

- Original Message - 
From: "John" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 21, 2009 7:29 PM
Subject: Re: [sqlite] cygwin and sqlite


> Pavel Ivanov wrote:
>>> MacBook Mac OS X 10.5.8
>>> 2 GHz Intel Core Duo
>>> 1 GB memory:
>>> 17 minutes 46 seconds.
>>>
>>> IBM ThinkPad
>>> Windows XP (latest patches)
>>> 1.70 GHz, 512 MB memory:
>>> 6 hours 25 minutes 57 seconds
>>
>> Windows is very slow in starting new processes if compared to any 
>> Unix
>> system (especially if compared Windows + 512 MB and Unix + 1 GB). 
>> In
>> cygwin starting new processes even slower because for some reason
>> emulating fork() involves starting 2 processes one of which dies
>> immediately. And bash scripts use processes a lot especially with 
>> Unix
>> paradigm when for each small action you start new program (like 
>> sed,
>> awk, test, true and whole lot of others). Thus bash scripts on 
>> cygwin
>> will be slow unavoidably.
>> But I'm digressing. This is subject for some other mailing list. :)
>
> I'm blacklisted apparently on the cygwin mailing list for when a
> couple of years ago I complained rather unflatteringly about how 
> slow
> it was/is when I was writing a simple expenses program (that works 
> in
> seconds on my Mac). I forgot about that. A badge of honor in my 
> opinion.
>
> But after all these years I wonder why they don't fix the fork 
> problem?
> MacOS runs on Intel processors. Windows runs on Intel processors. 
> Surely
> they could learn how it *should* be done by studying things like the
> Open Source Java code?
>
> It looks like I won't be able to distribute my stopgap cygwin code 
> on
> Windows. I need to start speed reading my Java/Swing books I guess 
> in
> my quest for my program to write once, run anywhere.
>
>> Pavel
>>
>> On Fri, Sep 18, 2009 at 3:26 AM, John  wrote:
>>> Pavel Ivanov wrote:
> At least I think that is what you suggest, and think it just
> may work! But I could be wrong!
 Yes, that's exactly what I suggest.

 Pavel
>>> It worked! Fortunately I had already parameterized SQLITE3 as a
>>> preference variable so I could have the same scripts run easily on 
>>> Mac
>>> OS and Windows. There are dozens of sqlite3 calls throughout the 
>>> scripts.
>>>
>>> My whole set of scripts that process raw data and load the 
>>> database by
>>> reading text files seem to work.
>>>
>>> cygwin is as slow as I recall, however. I was writing expense 
>>> scripts a
>>> few years ago and abandoned it for MacOS Unix. I moved 100% to Mac 
>>> OS.
>>> (except for this project which I want to work on Mac, linux, and
>>> Windows; my next goal is recoding it in Java with its Swing GUI, 
>>> but I'm
>>> just learning Java and Swing, but I'm on my way...).
>>>
>>> Observed elapsed times on my two notebook computers for the same 
>>> scripts
>>> to load the database (using sqlite3 calls and lots of sed and awk
>>> processing of thousands of lines of input data):
>>>
>>> MacBook Mac OS X 10.5.8
>>> 2 GHz Intel Core Duo
>>> 1 GB memory:
>>> 17 minutes 46 seconds.
>>>
>>> IBM ThinkPad
>>> Windows XP (latest patches)
>>> 1.70 GHz, 512 MB memory:
>>> 6 hours 25 minutes 57 seconds
>>>
>>> Fortunately, sqlite .dump and restoring from the resultant sql 
>>> will be
>>> able to be used for most of the heavy lifting when I'm done. 
>>> Changes to
>>> the data will come in small increments over time from then on. My 
>>> dumpit
>>> and restoreit scripts each take only seconds on both platforms for 
>>> the
>>> full set of current data.
>>>
>>> Thanks!
>>>
 On Thu, Sep 17, 2009 at 1:18 PM, John  
 wrote:
> Pavel Ivanov wrote:
>>> I'd rather avoid building sqlite3 under cygwin. I would like
>>> to keep as much as possible in native code, compromising only
>>> on cygwin to run my scripts.
>> And this is root of your problem. Using mix of cygwin-native
>> applications with windows-native applications will always have 
>> such
>> problem.
>>
>>> When installing cygwin, you it offers you the choice to switch
>>> to default text file type to DOS (\r\n). Should I try that?
>> Don't do that. This mode of operation is not supported much and 
>> not
>> recommended by cygwin developers and it reportedly will 
>> significantly
>> slow down cygwin's operation.
>>
>>> So I guess my question here is, do 

Re: [sqlite] SQLite database on a certain high-performance "SSD"

[Jim Showalter]

SSDs aren't necessarily faster than disks:

http://en.wikipedia.org/wiki/Solid-state_drive#Disadvantages

- Original Message - 
From: "Mark" 
To: 
Sent: Monday, September 21, 2009 1:53 PM
Subject: [sqlite] SQLite database on a certain high-performance "SSD"


> I've currently got a loaner high-performance flash-based "SSD" 
> (let's
> just say it doesn't connect to any disk controllers) that I'm 
> testing
> for performance. I've run my application against it, and I believe 
> that
> I should see numbers MUCH higher than I do. When I run my test app 
> on a
> normal SATA 7200 RPM disk, I get a certain performance, and on the 
> "SSD"
> I get about 1/10th that speed. On an array of SAS disks I get 
> numbers
> that are about 5x faster than my SATA disk, so my software itself 
> isn't
> (I believe) the bottleneck.
>
> I'm wondering if anyone has any tips for "optimizing" for this sort 
> of
> storage solution.
>
> Also, if anyone has any quick-and-dirty test setups they'd like me 
> to
> run through on this rig, just let me know :)
>
> ___
> 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] List of active savepoints names

[Jim Showalter]

I half get his point--if it's hard to program, then only
really good programmers will do it.

But that argument has been made against every advance in software
development since the profession began. Patchcord-pluggers made fun of
switch-flippers. Switch-flippers made fun of microcoders. Microcoders
made fun of assembly-language programmers. Assembly-language
programmers made fun of high-level-language programmers.
High-level-language programmers made fun of modelers. And everyone
makes fun of ruby programmers.

But this argument is basically bullshit, because it misses a key
truth: software has improved in power, usability, and ubiquity. When
only a handful of people could program a computer, then only a handful
of useful things were doable with computers. Was that desirable? Or is
it better that we have zillions of powerful applications written by
all manner of programmers available all over the place? If the masses
can program, then you get masses of programs, and you can let market
forces weed out the bad ones.

Linus' argument basically boils down to real men don't use numerically
controlled machine tools. Back when men were men, they built cars by
hand, the way god intended cars to be built. The way his grandpappy
made cars, dammit, snarls manly Linus as he rocks on his porch,
whittlin' and spittin' chaw.

- Original Message - 
From: "Roger Binns" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 21, 2009 10:42 AM
Subject: Re: [sqlite] List of active savepoints names


> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Lukas Gebauer wrote:
>> I am not creating and releasing savepoints!
>
> Yes you are.  The SQLite library is not magically doing them on a 
> whim.
> Your code is asking for them.
>
>>> You can create a custom wrapper for savepoints which may have
>>> the stack of calls.
>>
>> Why I must duplicate code what is inside Sqlite too?
>
> There is no code inside SQLite that returns the list of savepoints 
> so there
> is nothing to duplicate.  There is a data structure of savepoints.
>
> Adding this code and API seems simple enough and superficially it 
> is.
> However once added it cannot be removed (dynamic linking would 
> break), has
> to be documented and tested, and adds some bloat to SQLite.  It 
> would also
> constrain future changes to underlying data structures.  For example 
> if the
> public API returns a flat list, it would complicate things if the 
> internal
> data structure would be better off as a tree.
>
> Although it may not seem like it, one of the most important jobs of 
> project
> maintainers is saying "no".  Here is Linus Torvalds on the subject:
>
> http://linuxmafia.com/faq/Kernel/linus-im-a-bastard-speech.html
>
>> Sqlite have savepoint stack inside (AFAIK). I really not know why 
>> is
>> a problem to list this stack through some API. (But maybe it really
>> is a problem, I don't know, but nobody tell this yet...)
>
> This code will print out the names to stderr.  You should be able to 
> do the
> equivalent from your debugger.
>
> void print_savepoints(sqlite3 *db)
> {
>  int i;
>  for(i=0;inSavepoint;i++)
>fprintf(stderr, "%s\n", db->pSavepoint[i]->zName);
> }
>
> They are also in a tree like structure so it isn't quite that 
> simple.
>
>> Yes, I can create my own wrapper, what can work with my code only,
>
> It is your code that needs debugging :-)
>
>> Just because I must duplicate
>> lot of code for decide if savepoint is or not is created,
>
> If you have random code generating random SQL then it is harder, but 
> that is
> a bad design anyway.  Generally you'll have a function that when 
> called
> generates savepoints and another for rolling them back.  But even 
> with
> random code, the authorizer interface can tell you when savepoint 
> SQL is
> prepared.
>
>> And now compare it with simple API funtion what just list existing
>> savepoint stack inside Sqlite. It is simple, trivial, useful... and
>> working right at any case.
>
> And constrains data structures, requires documentation and testing, 
> can
> never be removed and adds bloat.  And in most cases still isn't 
> useful by
> itself - you would still need to add yet another wrapper around that
> function depending on what you want to happen (print it indented as 
> a tree,
> convert to utf16 or ascii, combine names separated by commas, stdout 
> or
> stderr, gui dialog etc).
>
>> This is why I am asking for. Thanks!
>
> A simple rule of thumb is to look at how useful features would be to 
> other
> SQLite users.  Note that you are the only one to have asked for this 
> feature
> and not one other person has agreed with you on its need!  The 
> responses
> have generally been along the lines of suggesting you organize your 
> code so
> that you do not need it.  But even if it was added as a debugging 
> aid, do
> you expect people to compile up two versions of SQLite - one with 
> debugging
> aids on and another 

Re: [sqlite] looking for a solution of Object/Relation Mapping with sqlite

[Jim Showalter]

How complicated are your objects and queries? I've worked with 
Hibernate (a lot, actually), and it's good for some things and 
horrible for others and a menace in the hands of the wrong people. 
There simply is no substitute for understanding your schema and your 
queries. If you have a good understanding of those, then Hibernate 
(and other ORMs) can be useful for generating code you would otherwise 
have to write yourself--sometimes a lot of code--but someone with no 
grasp of relational databases can create perfectly reasonable object 
graphs that produce dreadful performance. I know of a project that 
used EBJ 3.0 (the JPA spec that was based on Hibernate) to create a 
query with 25 left outer joins.

If your objects and queries are simple, there's no harm in coding the 
database access by hand.

- Original Message - 
From: "Wenbo Zhao" 
To: 
Sent: Thursday, September 10, 2009 8:12 PM
Subject: [sqlite] looking for a solution of Object/Relation Mapping 
with sqlite


> Hi, all
> I have been looking for a simple and practical solution of 
> Object/Relation
> Mapping over JDBC
> to use with sqlite.   I think this should be a common problem for 
> all users
> who write app with
> sqlite in java.
>
> On the net, it seems everybody is talking about Hibernate in this 
> field.
> I'm new in the JxEE thing
> and I'm not going to write that big app.  So the Hibernate official 
> web
> really terrified me with the
> long long list of documents and so many packages to learn.  The 
> start up is
> 3 days long !
> comparing to sqlite's 5minute startup, it's not acceptable to me.
>
> I think for most app, read/write db at most several times per 
> seconds, there
> should be something
> simple and good, just like sqlite in db.
> Can anybody give me a hint ?
>
> -- 
>
> Best Regards,
> ZHAO, Wenbo
>
> ===
> ___
> 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] One more SQL statement question

[Jim Showalter]

The not-in subselect could be expensive, depending on table size. If 
it turns out to be expensive, you might consider reference counting.

- Original Message - 
From: "Dennis Volodomanov" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 09, 2009 5:17 PM
Subject: Re: [sqlite] One more SQL statement question


>> sqlite> create trigger UpdateData after update on tablea begin
>>...> delete from tableb where id=old.refb and id not in ( select
>> refb from tablea );
>
> Yep, that's exactly what I need.
>
> Thank you!
>
>   Dennis
>
> ___
> 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] Importing data into SQLite - text files are not really portable

[Jim Showalter]

Microsoft also supports an XML standard for import/export. Whenever 
possible, it should be used instead of CSV. It's not the 
line-terminators that are the problem--the problem is CSV itself. 
That's why Microsoft upgraded to supporting XML. They only support CSV 
for backward-compatibility reasons.

- Original Message - 
From: "Jean-Denis Muys" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, September 09, 2009 6:09 AM
Subject: Re: [sqlite] Importing data into SQLite - text files are not 
really portable


>
> On 9/8/09 22:24 , various people wrote:
>
>> Unfortunately, the 3 main families of small computer operating 
>> systems
>> have 3 different definitions of what a text file is...
>>
>> DOS/Windows (PC): lines are terminated with CR+LF
>> Unix: lines are terminated with LF
>> Macintosh: lines are terminated with CR
> [...]
>> FYI: Mac excel does not separate rows with \r, but inserts a ^M 
>> instead.
> [...]
>> From: "Kavita Raghunathan" 
>>
>> Yes, this works. Must have been my original csv file.
>> I was using mac based excel and I'll now try using the windows 
>> based
>> excel.
>
> For the record, the Mac has not been using CR line terminations for 
> many
> years now (2001).
>
> Microsoft, in its not very high wisdom, proposes in the latest Mac 
> version
> of Excel, 4 export formats that might correspond, with useless 
> names:
>
> 1- the main format at the top of the menu is named "Comma Separated 
> Values
> (.csv)"
> It's a comma -separated, CR-terminated format, with characters 
> encoded in
> MacRoman ( http://en.wikipedia.org/wiki/Mac_OS_Roman).
> As both line-termination and character-encoding conventions have 
> stopped
> being used for many years, it's despicable from Micros__t to 
> continue to
> name it as they do, and to propose it as the main choice.
>
> 2- secondary format named "Tab delimited text (.txt)"
> Same as 1-, except the separator is now a tab character.
> Totally useless format
>
> 3- secondary format named "Windows Comma-separated (.csv)"
> Line terminations: CR-LF (Windows convention)
> Character encoding: CP1502 (Windows extension to ISO-8859-1)
> This is the most useful format, as it's likely to work for 
> interoperability
> with the Windows world.
>
> 4- secondary format named "MS-Dos Comma-separated (.csv)"
> Line terminations: CR
> Character encoding: CP850 ( http://en.wikipedia.org/wiki/CP850)
> A totally obsolete variant.
>
> Notably missing would be for example the *current* version: line 
> terminated
> with LF and UTF8 encoding.
>
> I will stop there. Ranting on MS is bad for my nerves.
>
> Jean-Denis
>
>
>
>
> ___
> 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] Booleans in SQLite

[Jim Showalter]

Oracle doesn't have a native boolean type. You have to use INTEGER and 
interpret it.

MySQL doesn't have a boolean type (it's just a synonym for TINYINT).

SQL Server doesn't have a boolean type. You have to use BIT and 
interpret it.

- Original Message - 
From: "Mark Hamburg" 
To: "General Discussion of SQLite Database" 
Sent: Monday, September 07, 2009 10:27 AM
Subject: Re: [sqlite] Booleans in SQLite


> The real argument for adding boolean support is not about space but
> about compatibility with dynamic languages with a boolean type that
> are exploiting SQLite's dynamic typing of values. Without a boolean
> type in SQLite, a glue layer has to guess whether a 0 means zero or
> false or a "NO" means the string "NO" or false or...
>
> Mark
>
> ___
> 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] Problems encountered on upgrade from SQLite2 to -3

[Jim Showalter]

At the risk of throwing gasoline on a fire...

I didn't react badly to Rod's original post. Yes, it could have been 
worded more diplomatically, but why so thin-skinned about it? He 
reported some issues. The response was that they weren't issues. Can 
you see how that might be construed as unhelpful?

As for:

"You see, the fact is that most people ported their applications from 
SQLite2 to SQLite3 back in 2004."

while that may very well be true, it sure didn't help Rod, did it? 
What he asked for was a migration guide (actually, he just asked for 
some clarifying language in the documentation). Just because most 
people don't need a migration guide because they already ported 
doesn't mean that a user who hasn't ported doesn't need a migration 
guide. That's arguing from the specific to the general, one of the 
classic logical fallicies.

- Original Message - 
From: "D. Richard Hipp" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, September 03, 2009 8:05 AM
Subject: Re: [sqlite] Problems encountered on upgrade from SQLite2 
to -3


>
> On Sep 3, 2009, at 10:43 AM, Rod Dav4is wrote:
>
>>*re applied affinity:* If that is what is meant, then the 
>> document
>> should say it, instead of leaving it to the reader's imagination.
>>Since column typing was superfluous in version2, it seems that 
>> the
>> version3 adoption of typing, as defined, would perhaps be an 
>> upgrade
>> compatibility issue, no?
>
>
> I might be wrong, but I'm guessing you'll find the people here will
> help you more if you take the chip off of your shoulder and ask 
> nicely.
>
> You see, the fact is that most people ported their applications from
> SQLite2 to SQLite3 back in 2004.  A dare say that most current 
> readers
> of this mailing list didn't discover SQLite until after SQLite3 was
> already well established, and hence have no memory of what SQLite2 
> did
> or how it was different from SQLite3.  So porting from SQLite2 to
> SQLite3 is not a topic that is a high priority to people here.  And
> hence, they tend to respond unsupportively when addressing a 
> complaint
> by a user who is clearly miffed that SQLite3 does not work exactly 
> the
> way SQLite2 used to work.
>
> I suggest a do-over.
>
> Rod, I suggest you re-register for this mailing list under a 
> different
> name, then log on and send a request that is worded something like 
> this:
>
>"Hi!  I'm porting an older application from SQLite2 to SQLite3 
> and
> am running into a couple of compatibility issues.  [explain the two
> problems here.]  Can somebody suggest ways of either (1) getting
> SQLite3 to work more like SQLite2 used to work, or (2) how I can
> change my code to work the way SQLite3 expects?  Thanks!"
>
> Note that the sample request in the previous paragraph does not
> contain an impatient claim that SQLite3 is broken and needs fixing.
> And in particular, it does not contain such a claim coming from
> someone who does not understand how SQLite3 works.
>
> I think if you try my do-over suggestion you will find the people 
> here
> will be nice, friendly, and much, much more helpful.
>
> D. Richard Hipp
> d...@hwaci.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

Re: [sqlite] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

It doesn't collect those statistics automatically, as part of query 
plan optimization?

- Original Message - 
From: "Dan Kennedy" <danielk1...@gmail.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, August 14, 2009 11:37 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:34 PM, Jim Showalter wrote:
>
>> How will that help him fix this problem, if the problem is that
>> SQLite's query optimizer is selecting a suboptimal index to use, 
>> and
>> there is no way to specify which index to use?
>
> The statistics collected by the ANALYZE command will be used by
> SQLite to (hopefully) select the optimal index.
>
> ___
> 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] Multiple indexes in SQLite, and selecting which to use

[Jim Showalter]

How will that help him fix this problem, if the problem is that 
SQLite's query optimizer is selecting a suboptimal index to use, and 
there is no way to specify which index to use?

- Original Message - 
From: "Dan Kennedy" 
To: "General Discussion of SQLite Database" 
Sent: Friday, August 14, 2009 11:15 PM
Subject: Re: [sqlite] Multiple indexes in SQLite, and selecting which 
to use


>
> On Aug 15, 2009, at 1:08 PM, His Nerdship wrote:
>
>>
>> Good day,
>> We have a puzzling problem with a large (1GB+) database.
>> Most of our queries are based on 3 columns, say X, Y and Z.
>> X is always the first in the index.  However, sometimes the query
>> involves a
>> small range of Y and a larger range of Z, and sometimes the
>> reverse.  We
>> first had an index based on X, Y & Z (in that order), and noticed
>> that the
>> results are fast when there was one X, one Y and many Z's.  I check
>> if the
>> range is a single value, and if it is, I change the SQL to a
>> straight '=',
>> e.g:
>>  ..WHERE X = x AND Y = y AND Z BETWEEN z1 AND z20;
>>
>> According to Mike Owens, using an equality or IN operator on Y
>> allows Z to
>> be indexed, speeding up the search.  If Y is a range and we use
>> "BETWEEN y1
>> AND y2" on it, then Z will not be indexed.  This is what we found -
>> the
>> second search was much slower.
>>
>> However because sometimes the numbers are reversed, such that there
>> are many
>> Y's and few Z's, we added another index based on X, Z and Y, in 
>> that
>> order.
>> In this case, though, it didn't make any difference.  It seems like
>> SQLite
>> does not select the correct index to use - it uses XYZ instead of 
>> XZY.
>
> You might just need to run the ANALYZE command to collect database
> statistics.
>
>   http://www.sqlite.org/lang_analyze.html
>
> Use EXPLAIN QUERY PLAN to see the strategy SQLite is using for each
> query.
>
>   http://www.sqlite.org/lang_explain.html
>
>
> Dan.
>
> ___
> 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] SQLJet - pure Java implementation of SQLite

[Jim Showalter]

Perhaps it would be better to translate the object code.

It's also possible to translate bytecode (for example, from Java to 
.NET).

- Original Message - 
From: "Fred Williams" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, August 12, 2009 5:42 AM
Subject: Re: [sqlite] SQLJet - pure Java implementation of SQLite


> That's the problem any more it seems.  Everyone "assumes" unlimited 
> CPU and
> memory :-)
>
> Give a guy a Cray and he'll write one hell of a "Solitaire"!
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Roger Binns
> Sent: Wednesday, August 12, 2009 12:09 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLJet - pure Java implementation of SQLite
>
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Fred Williams wrote:
>> I say there is no known translation that
>> would allow the three SQLite, "Small, Fast, Reliable" adjectives to
>> translate into any regurgitated language output, with the exception 
>> of
>> compiling SQLite source with a C++ compiler :-)
>
> If you read Bernstein's retrospective on qmail, one of his 
> suggestions is to
> write code in a higher level language (especially more secure in the 
> sense
> that C isn't such as preventing buffer & integer overflows etc) that 
> is then
> transcoded to C.  (Some conspiracy theorists looking at his code 
> claimed he
> actually did do this :-)
>
> So in theory given unlimited CPU and memory it should be possible to 
> take
> the SQLite C code and turn it into something higher level and then 
> turn that
> back into the more verbose platforms (.NET, Java).  The big 
> advantage of
> this approach (after the upfront work) is that keeping up with 
> SQLite
> progress is easy.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAkqCTmwACgkQmOOfHg372QSCUwCeKD+V+e7yjYgbcF9e+lXhAbbE
> U9cAoIzXPblugsswnhbgnmTNYSrkdMj7
> =VYQ3
> -END PGP SIGNATURE-
> ___
> 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] "Bad CPU type in executable"?

[Jim Showalter]

I really really hate top-posting.

- Original Message - 
From: "Dr. David Kirkby" 
To: "General Discussion of SQLite Database" 
Sent: Saturday, August 08, 2009 11:28 PM
Subject: Re: [sqlite] "Bad CPU type in executable"?


> J. King wrote:
>> On Wed, 05 Aug 2009 12:50:15 -0400, Jean-Denis Muys 
>> 
>> wrote:
>>
>>> You're top-posting, it's evil, the thread is becoming messy. That 
>>> said...

- Original Message - 

Bottom-posting drives me insane.

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

Re: [sqlite] FTS and postfix search

[Jim Showalter]

You can lazy-init the table of substrings and substring matches when 
you save words, so you only use space for substrings that appear in 
words that have been saved in your database. That avoids entering 
substrings that never occur in English, and substrings that occur in 
English but not in any words you are searching.

- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Thursday, August 06, 2009 8:59 AM
Subject: Re: [sqlite] FTS and postfix search


>I hadn't thought about it until now, but there's no reason you can't 
>treat the table of substrings as substrings appearing anywhere in a 
>word, and join each substring of a word to the substrings table.
>
> For 26 letters, you have > 17k three-letter substrings and almost 
> 500k four-letter substrings, so this technique probably is limited 
> to three-letter substrings. But that's not bad--it means that if 
> character sequences are evenly distributed in words (they're not, 
> but assume they are for a moment), then the set of all possible 
> words is cut into 1/>17,000 on the first search. There are only 
> about 600,000 words in the English language, so with even 
> distribution your first search cuts the results to ~34 words. But 
> uneven character-sequence distributions will skew that--some 
> sequences will result in more words than others. Sill, even if you 
> get back 500 words to search for the full match, that's better than 
> searching all of your words. (You also have to store all one-letter 
> and two-letter substrings, but those don't take up much additional 
> room.)
>
> You would have to have a separate table that has the multiple joins 
> (one per substring) in your words, because now each word can 
> potentially have M substrings in it, so M foreign keys.
>
> For your example, Motor/motor would match mot, oto, and tor. You 
> would use a rule to arbitrarily match the first N letters in a the 
> search term, so if someone searched for "%motor%", that would be a 
> search in the substrings table for "mot". This would return the 
> primary key for "mot". Then you search the substring-matches table 
> for all words that have that substring, and this gives you back a 
> list of words containing "mot". Then you search that result set for 
> "%motor%". Similarly, if someone searchs for "%oto%", you get the 
> substring key for "oto" and find words containing that substring.
>
> For all I know, full-text-search engines may do something like this.
>
> - Original Message - 
> From: "Lukas Haase" <lukasha...@gmx.at>
> To: <sqlite-users@sqlite.org>
> Sent: Thursday, August 06, 2009 6:54 AM
> Subject: Re: [sqlite] FTS and postfix search
>
>
>> Hi Jim,
>>
>> and thank you for the great idea. But I thought it would be 
>> possible to
>> search '*word*' - but this is not possible with this method either.
>>
>> Is there any chance for searching '*word*' quickly?
>>
>> Regards,
>> Luke
>>
>> Jim Showalter schrieb:
>>> You could store the words reversed (in addition to storing them in
>>> forward order). Then like 'xxx%' would be fast.
>>>
>>> This would double your disk footprint, but could give you the 
>>> search
>>> performance you're looking for.
>>>
>>> If that's too goofy, you could create a table of all one, two, and
>>> three-character word endings, and join to it from all of your 
>>> words
>>> (stored in forward order). Then search first for the primary key 
>>> of
>>> the word ending you want to search for, then search your words for
>>> that key.
>>>
>>> Index the join.
>>>
>>> - Original Message - 
>>> From: "Lukas Haase" <lukasha...@gmx.at>
>>> To: <sqlite-users@sqlite.org>
>>> Sent: Wednesday, August 05, 2009 6:16 PM
>>> Subject: Re: [sqlite] FTS and postfix search
>>>
>>>
>>>> Wes Freeman schrieb:
>>>>> I clearly am not in the right mindset to be answering list 
>>>>> emails.
>>>>> Please ignore my response (it's too late now)--back to my 
>>>>> stressful
>>>>> deadline.
>>>> :-)
>>>>
>>>>> Strange that it's implemented for prefix and not postfix?
>>>> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or 
>>>> LIKE
>>>> 'xxx%' can be performed easy because only the beginning of words
>>>&g

Re: [sqlite] FTS and postfix search

[Jim Showalter]

I hadn't thought about it until now, but there's no reason you can't 
treat the table of substrings as substrings appearing anywhere in a 
word, and join each substring of a word to the substrings table.

For 26 letters, you have > 17k three-letter substrings and almost 500k 
four-letter substrings, so this technique probably is limited to 
three-letter substrings. But that's not bad--it means that if 
character sequences are evenly distributed in words (they're not, but 
assume they are for a moment), then the set of all possible words is 
cut into 1/>17,000 on the first search. There are only about 600,000 
words in the English language, so with even distribution your first 
search cuts the results to ~34 words. But uneven character-sequence 
distributions will skew that--some sequences will result in more words 
than others. Sill, even if you get back 500 words to search for the 
full match, that's better than searching all of your words. (You also 
have to store all one-letter and two-letter substrings, but those 
don't take up much additional room.)

You would have to have a separate table that has the multiple joins 
(one per substring) in your words, because now each word can 
potentially have M substrings in it, so M foreign keys.

For your example, Motor/motor would match mot, oto, and tor. You would 
use a rule to arbitrarily match the first N letters in a the search 
term, so if someone searched for "%motor%", that would be a search in 
the substrings table for "mot". This would return the primary key for 
"mot". Then you search the substring-matches table for all words that 
have that substring, and this gives you back a list of words 
containing "mot". Then you search that result set for "%motor%". 
Similarly, if someone searchs for "%oto%", you get the substring key 
for "oto" and find words containing that substring.

For all I know, full-text-search engines may do something like this.

- Original Message - 
From: "Lukas Haase" <lukasha...@gmx.at>
To: <sqlite-users@sqlite.org>
Sent: Thursday, August 06, 2009 6:54 AM
Subject: Re: [sqlite] FTS and postfix search


> Hi Jim,
>
> and thank you for the great idea. But I thought it would be possible 
> to
> search '*word*' - but this is not possible with this method either.
>
> Is there any chance for searching '*word*' quickly?
>
> Regards,
> Luke
>
> Jim Showalter schrieb:
>> You could store the words reversed (in addition to storing them in
>> forward order). Then like 'xxx%' would be fast.
>>
>> This would double your disk footprint, but could give you the 
>> search
>> performance you're looking for.
>>
>> If that's too goofy, you could create a table of all one, two, and
>> three-character word endings, and join to it from all of your words
>> (stored in forward order). Then search first for the primary key of
>> the word ending you want to search for, then search your words for
>> that key.
>>
>> Index the join.
>>
>> - Original Message - 
>> From: "Lukas Haase" <lukasha...@gmx.at>
>> To: <sqlite-users@sqlite.org>
>> Sent: Wednesday, August 05, 2009 6:16 PM
>> Subject: Re: [sqlite] FTS and postfix search
>>
>>
>>> Wes Freeman schrieb:
>>>> I clearly am not in the right mindset to be answering list 
>>>> emails.
>>>> Please ignore my response (it's too late now)--back to my 
>>>> stressful
>>>> deadline.
>>> :-)
>>>
>>>> Strange that it's implemented for prefix and not postfix?
>>> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or 
>>> LIKE
>>> 'xxx%' can be performed easy because only the beginning of words
>>> need to
>>> be compared.
>>>
>>> However, there /is/ a way to also do postfix searches. I have the
>>> *same*
>>> database in *.hlp format and with WinHelp it's possible to search
>>> '*otor' (and others) with almost zero CPU and time consumption. 
>>> I'd
>>> be
>>> curious how they did this.
>>>
>>> For a solution for SQLite I would accept a small performance 
>>> penalty
>>> in
>>> that case (but very few secs max); additionally I would also 
>>> accept
>>> the
>>> index being bigger.
>>>
>>> Regards,
>>> Luke
>>>
>>>> Wes
>>>>
>>>> On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase<lukasha...@gmx.at>
>>>> wrote:
>>>>> Wes Freeman schrieb:
>>>>>> Why not LIKE '%otor'?
>>>>> SELECT topic_title FROM topics
&g

Re: [sqlite] FTS and postfix search

[Jim Showalter]

Sorry--I read my emails arrival order, not reverse chronological--so I 
didn't see that John had already solved it.

- Original Message - 
From: "John Machin" 
To: "General Discussion of SQLite Database" 
Sent: Wednesday, August 05, 2009 6:40 PM
Subject: Re: [sqlite] FTS and postfix search


> On 6/08/2009 11:16 AM, Lukas Haase wrote:
>> Wes Freeman schrieb:
>
>>
>>> Strange that it's implemented for prefix and not postfix?
>>
>> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE
>> 'xxx%' can be performed easy because only the beginning of words 
>> need to
>> be compared.
>>
>> However, there /is/ a way to also do postfix searches. I have the 
>> *same*
>> database in *.hlp format and with WinHelp it's possible to search
>> '*otor' (and others) with almost zero CPU and time consumption. I'd 
>> be
>> curious how they did this.
>
> In memory: maybe a suffix tree.
>
> In a database: have a column with the words stored backwards. SELECT 
> ...
> WHERE back_word LIKE "roto%"
> ___
> 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] FTS and postfix search

[Jim Showalter]

Forgot to conclude by saying the search gives you a list of words that 
you then need to further reduce by the actual number of characters you 
want to search by.

That's why storing them in reverse order might be preferable.

Also, just thought of something--if you store them in reverse order, 
you don't need to also store them in forward order. Just reverse the 
strings before displaying them.

- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Wednesday, August 05, 2009 7:04 PM
Subject: Re: [sqlite] FTS and postfix search


> You could store the words reversed (in addition to storing them in 
> forward order). Then like 'xxx%' would be fast.
>
> This would double your disk footprint, but could give you the search 
> performance you're looking for.
>
> If that's too goofy, you could create a table of all one, two, and 
> three-character word endings, and join to it from all of your words 
> (stored in forward order). Then search first for the primary key of 
> the word ending you want to search for, then search your words for 
> that key.
>
> Index the join.
>
> - Original Message - 
> From: "Lukas Haase" <lukasha...@gmx.at>
> To: <sqlite-users@sqlite.org>
> Sent: Wednesday, August 05, 2009 6:16 PM
> Subject: Re: [sqlite] FTS and postfix search
>
>
>> Wes Freeman schrieb:
>>> I clearly am not in the right mindset to be answering list emails.
>>> Please ignore my response (it's too late now)--back to my 
>>> stressful
>>> deadline.
>>
>> :-)
>>
>>> Strange that it's implemented for prefix and not postfix?
>>
>> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE
>> 'xxx%' can be performed easy because only the beginning of words 
>> need to
>> be compared.
>>
>> However, there /is/ a way to also do postfix searches. I have the 
>> *same*
>> database in *.hlp format and with WinHelp it's possible to search
>> '*otor' (and others) with almost zero CPU and time consumption. I'd 
>> be
>> curious how they did this.
>>
>> For a solution for SQLite I would accept a small performance 
>> penalty in
>> that case (but very few secs max); additionally I would also accept 
>> the
>> index being bigger.
>>
>> Regards,
>> Luke
>>
>>> Wes
>>>
>>> On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase<lukasha...@gmx.at> 
>>> wrote:
>>>> Wes Freeman schrieb:
>>>>> Why not LIKE '%otor'?
>>>> SELECT topic_title FROM topics
>>>> WHERE topic LIKE '%otor%'
>>>> ORDER BY topic_title ASC;
>>>>
>>>> This is very, very slow, especially on my > 100 MB database. 
>>>> "Realtime"
>>>> search in the GUI is a requirement. This is exactly the reason 
>>>> why I
>>>> want to use FTS instead of LIKE...
>>>>
>>>> Regards,
>>>> Luke
>>>>
>>>>> Wes
>>>>>
>>>>> On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase<lukasha...@gmx.at> 
>>>>> wrote:
>>>>>> Hi,
>>>>>>
>>>>>> It's me again, sorry. The next big problem concerning FTS. I 
>>>>>> have the
>>>>>> requirement to do postfix searches, like:
>>>>>>
>>>>>> SELECT topic_title FROM topics
>>>>>> WHERE topic MATCH '*otor'
>>>>>> ORDER BY topic_title ASC;
>>>>>>
>>>>>> should find Motor, motor, Monotor etc. But this does not seem 
>>>>>> to work.
>>>>>> Is there any chance to get this working?
>>>>>>
>>>>>> Best regards,
>>>>>> Luke
>>>>>>
>>>>>> ___
>>>>>> 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
>>>
>>
>> ___
>> 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] FTS and postfix search

[Jim Showalter]

You could store the words reversed (in addition to storing them in 
forward order). Then like 'xxx%' would be fast.

This would double your disk footprint, but could give you the search 
performance you're looking for.

If that's too goofy, you could create a table of all one, two, and 
three-character word endings, and join to it from all of your words 
(stored in forward order). Then search first for the primary key of 
the word ending you want to search for, then search your words for 
that key.

Index the join.

- Original Message - 
From: "Lukas Haase" 
To: 
Sent: Wednesday, August 05, 2009 6:16 PM
Subject: Re: [sqlite] FTS and postfix search


> Wes Freeman schrieb:
>> I clearly am not in the right mindset to be answering list emails.
>> Please ignore my response (it's too late now)--back to my stressful
>> deadline.
>
> :-)
>
>> Strange that it's implemented for prefix and not postfix?
>
> Well, an explanation is easy: Same as with LIKE, LIKE 'xxx' or LIKE
> 'xxx%' can be performed easy because only the beginning of words 
> need to
> be compared.
>
> However, there /is/ a way to also do postfix searches. I have the 
> *same*
> database in *.hlp format and with WinHelp it's possible to search
> '*otor' (and others) with almost zero CPU and time consumption. I'd 
> be
> curious how they did this.
>
> For a solution for SQLite I would accept a small performance penalty 
> in
> that case (but very few secs max); additionally I would also accept 
> the
> index being bigger.
>
> Regards,
> Luke
>
>> Wes
>>
>> On Wed, Aug 5, 2009 at 8:58 PM, Lukas Haase 
>> wrote:
>>> Wes Freeman schrieb:
 Why not LIKE '%otor'?
>>> SELECT topic_title FROM topics
>>> WHERE topic LIKE '%otor%'
>>> ORDER BY topic_title ASC;
>>>
>>> This is very, very slow, especially on my > 100 MB database. 
>>> "Realtime"
>>> search in the GUI is a requirement. This is exactly the reason why 
>>> I
>>> want to use FTS instead of LIKE...
>>>
>>> Regards,
>>> Luke
>>>
 Wes

 On Wed, Aug 5, 2009 at 7:47 PM, Lukas Haase 
 wrote:
> Hi,
>
> It's me again, sorry. The next big problem concerning FTS. I 
> have the
> requirement to do postfix searches, like:
>
> SELECT topic_title FROM topics
> WHERE topic MATCH '*otor'
> ORDER BY topic_title ASC;
>
> should find Motor, motor, Monotor etc. But this does not seem to 
> work.
> Is there any chance to get this working?
>
> Best regards,
> Luke
>
> ___
> 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
>>
>
> ___
> 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] ANN: SQLite 3.6.16.C#

[Jim Showalter]

Yes, C# supports native calls. You just call pinvoke.

But a pure C# implementation allows it to run in Silverlight and other 
C# applications where native calls are not allowed.

http://sqlite.phxsoftware.com/forums/t/1642.aspx

- Original Message - 
From: "Konrad J Hambrick" 
To: "General Discussion of SQLite Database" 
Sent: Sunday, August 02, 2009 12:19 PM
Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#


>
> Noah --
>
> This is a wonderful accomplishment !
>
> However, I have a question ...
>
> Doesn't C# support native function calls ?
>
> If so, other than scratching an itch, what does a native port to C# 
> do ?
>
> Thanks.
>
> -- kjh
>
> Noah Hart wrote:
>> Richard sent me a gentle reminder that read in part:
>>
>> 
>> Please also note that the SQLite source code is in the public 
>> domain, but
>> the "SQLite" name is not.  SQLite is a registered trade mark.  If I 
>> don't
>> defend the trademark, then I could lose it.  So, I really do need 
>> to insist
>> that you not use the name "SQLite" for your product.
>> 
>>
>>
>> This is an excellent reminder, and until this is done, I've removed 
>> access
>> to the source code and will terminate this google code project. 
>> I'll post
>> an announcement in the future when the new project is ready.
>>
>> Also, if anyone has an ideal about what to call it ...
>> Regards,
>>
>> Noah Hart
>>
>>
>>
>> Noah Hart wrote:
>>> I am pleased to announce that the C# port is done to the point 
>>> where
>>> others can look at it.
>>>
>>> The project is located at http://code.google.com/p/sqlitecs
>>>
>>> Enjoy,
>>>
>>> Noah Hart
>>>
>>
> ___
> 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] ANN: SQLite 3.6.16.C#

[Jim Showalter]

Max's point is that JITs automatically compile down to native code, 
thus a screwdriver is turned into a monkey wrench, therefore you only 
need the screwdriver.

There are other reasons besides native vs. VM why C#/Java would be 
slower than C. For example, C doesn't have the overhead of virtual 
dispatch. And JIT'ing value-type generics in C# can bloat the code.

I *like* managed languages--arguing against having the runtime take 
care of memory allocation reminds me of programmers arguing 40 years 
ago that hand-written assembly was superior to high-level compilers. 
That may have been true initially, and may still be true for 
specialized cases (video drivers?), but in general it's poppycock.

- Original Message - 
From: "Fred Williams" <f.willi...@verizon.net>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, August 02, 2009 11:23 AM
Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#


>I have at least a screwdriver and monkey wrench to go with my hammer 
>in my
> computer software tool bag.  Observing the fastener at hand allows 
> me to
> pick the proper tool.  Then if that doesn't work, there's always the 
> hammer.
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Kosenko Max
> Sent: Sunday, August 02, 2009 6:32 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#
>
>
>
> I don't know why you have decided that C# or Java isn't competitive 
> to
> native
> compiled code.
> After JIT there is no VM between Java/.NET and OS.
>
> While C has better compiler than C# it still don't have JIT engine 
> that can
> optimize your code for specific hardware you're running on right 
> now. Or it
> can recompile code based on statistics. Options unavailable to 
> native code.
> And after all having managed implementation gives better control on 
> code,
> simpler code and so on. So in reality there should be strong reason 
> for NOT
> using managed implementations for whatever. But it's only rising 
> now...
>
>
> Jim Showalter-4 wrote:
>>
>> Could we not disparage different OSs and languages?
>>
>> A fair comparison of performance isn't between a C and C#
>> implementation of SQLite, but between a C# and Java implementation 
>> of
>> SQLite. Both C# and Java are managed languages that run atop a VM 
>> that
>> runs atop an OS. C is down on the metal. I would expect the C 
>> version
>> to be faster.
>>
>> I don't know the background of why this programmer did a port 
>> directly
>> to C# instead of binding C# to the existing C library, but I assume 
>> he
>> had his reasons.
>>
>> - Original Message -
>> From: "Fred Williams" <f.willi...@verizon.net>
>> To: "General Discussion of SQLite Database" 
>> <sqlite-users@sqlite.org>
>> Sent: Saturday, August 01, 2009 6:08 AM
>> Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#
>>
>>
>>>
>>> Hummm... Guess there is a reason there are no implementations of 
>>> C#
>>> external
>>> to the Mickeysoft world :-)
>>>
>>> Guess if I had a lot of time to kill I could port it to Delphi...
>>>
>>> BTW, what's the memory footprint?
>>>
>>> Fred
>>>
>>> -Original Message-
>>> From: sqlite-users-boun...@sqlite.org
>>> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Kosenko Max
>>> Sent: Saturday, August 01, 2009 6:22 AM
>>> To: sqlite-users@sqlite.org
>>> Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#
>>>
>>>
>>>
>>> Seems like I've misunderstood your performance results. And they 
>>> are
>>> 3-5times
>>> slower than original...
>>>
>>> -
>>> Best Regards.
>>> Max Kosenko.
>>> --
>>> View this message in context:
>>> http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24768252.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-users mailing list
>> sqlite-

Re: [sqlite] ANN: SQLite 3.6.16.C#

[Jim Showalter]

Could we not disparage different OSs and languages?

A fair comparison of performance isn't between a C and C# 
implementation of SQLite, but between a C# and Java implementation of 
SQLite. Both C# and Java are managed languages that run atop a VM that 
runs atop an OS. C is down on the metal. I would expect the C version 
to be faster.

I don't know the background of why this programmer did a port directly 
to C# instead of binding C# to the existing C library, but I assume he 
had his reasons.

- Original Message - 
From: "Fred Williams" 
To: "General Discussion of SQLite Database" 
Sent: Saturday, August 01, 2009 6:08 AM
Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#


>
> Hummm... Guess there is a reason there are no implementations of C# 
> external
> to the Mickeysoft world :-)
>
> Guess if I had a lot of time to kill I could port it to Delphi...
>
> BTW, what's the memory footprint?
>
> Fred
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Kosenko Max
> Sent: Saturday, August 01, 2009 6:22 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: SQLite 3.6.16.C#
>
>
>
> Seems like I've misunderstood your performance results. And they are
> 3-5times
> slower than original...
>
> -
> Best Regards.
> Max Kosenko.
> --
> View this message in context:
> http://www.nabble.com/ANN%3A--SQLite-3.6.16.C--tp24764742p24768252.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi-master replication with updated Versioning extension

[Jim Showalter]

MD5 hashes can still collide. How does this implementation deal with 
hash collisions?

- Original Message - 
From: "Alexey Pechnikov" 
To: "General Discussion of SQLite Database" 
Sent: Thursday, July 30, 2009 7:11 AM
Subject: Re: [sqlite] Multi-master replication with updated Versioning 
extension


> Hello!
>
> On Thursday 30 July 2009 17:25:15 P Kishor wrote:
>> > I haven't looked at your work in depth, but I am interested in 
>> > this. I
>> > have implemented a very simple versioning system with TRIGGERs 
>> > whereby
>> > every change (INSERT, UPDATE, DELETE) in a column in a table is 
>> > stored
>> > in a versions table along with its primary key, allowing me to go 
>> > back
>> > and examine any version and roll back to it, if desired.
>
> Yes, the primary key field is good enough for master-slave 
> replication but not for
> multi-master because each master has self sequence counter. And full 
> record
> hash may be used for master-slave replication on tables without 
> primary keys.
>
> As table-independant way extension use ROWID field and md5 hash of 
> all fields.
> So multi-master is possible with some restrictions such as 
> non-unique records
> is denied. But may be ROWID + hash of record can help for this 
> situation too.
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Denormalisation

[Jim Showalter]

Try writing apps for cellphones and both space and time become 
important again!

- Original Message - 
From: "Simon Slavin" 
To: "General Discussion of SQLite Database" 
Sent: Monday, July 27, 2009 3:33 PM
Subject: Re: [sqlite] Denormalisation


>
> On 27 Jul 2009, at 10:44pm, CityDev wrote:
>
>> Over the intervening years I can't ever remember denormalising data
>> (even
>> when dealing with eg 13 million insurance customers in a table). Is
>> it OK
>> nowadays to say always aim to be fully normalised - modern RDBMSs 
>> are
>> usually powerful enough to cope with most anything?
>
> Performance/space/time/money payoff calculation.  The smallest 
> mundane
> laptop these days comes with a 120 Gig hard disk.  No real need to
> economise on file size, so if you need fast processing you might use
> lots of space.  On the other hand if you need to cart your entire
> dataset around on a USB Flash drive you might want to save space.
>
> The advantage is that most programmers don't have to care which way
> they go: they write the program any way it occurs to them.  The
> disadvantage of this is that when they hit the first job where the
> difference does matter, they don't know what to do about it.
>
> 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 lock behavior in shared-cache mode

[Jim Showalter]

Does SQLite support MVCC 
(http://en.wikipedia.org/wiki/Multiversion_concurrency_control)? It 
sounds like it doesn't. Maybe it should--that's a very nice way to 
support unblocked reads while still getting mutex for writes.

- Original Message - 
From: "Dan" 
To: "General Discussion of SQLite Database" 
Sent: Monday, July 27, 2009 9:27 AM
Subject: Re: [sqlite] SQLite lock behavior in shared-cache mode


>
> While compiling any statement (sqlite3_prepare_v2()), or while 
> stepping
> (sqlite3_step()) a statement that accesses the main database, a 
> mutex
> associated with the in-memory cache of the main database will be 
> held.
>
> Dan.
>
>
>
> On Jul 27, 2009, at 11:16 PM, Alessandro Merolli wrote:
>
>> Hi,
>>
>> I'm using SQLite latest version (3.6.16) with shared-cache enable 
>> in
>> a process that has around 5 threads. Database connections for each
>> thread are created with the same main database file. After that, 
>> each
>> connection is attached to a particular database file (one for each
>> thread) using the same schema name. Final structure is similar to 
>> the
>> following:
>>
>> Main database file: main.db
>> Thread-1 database file: thread1.db
>> Thread-2 database file: thread2.db
>> Thread-3 database file: thread3.db
>> Thread-4 database file: thread4.db
>> Thread-5 database file: thread5.db
>>
>> Thread-1 connection is opened with the main.db file and attaches 
>> the
>> thread1.db as "extradb" schema name;
>> Thread-2 connection is opened with the main.db file and attaches 
>> the
>> thread2.db as "extradb" schema name;
>> Thread-3 connection is opened with the main.db file and attaches 
>> the
>> thread3.db as "extradb" schema name;
>> Thread-4 connection is opened with the main.db file and attaches 
>> the
>> thread4.db as "extradb" schema name;
>> Thread-5 connection is opened with the main.db file and attaches 
>> the
>> thread5.db as "extradb" schema name;
>>
>> Every SQL statement submitted to the process and passed to one of
>> these threads can read global informations maintained in the 
>> main.db
>> database file ("main" schema) and write/read particular 
>> informations
>> in the "extradb" schema in such a way that one thread does not need 
>> to
>> wait for another thread to write its information, since each thread
>> has the "extradb" schema attached to a particular database file.
>>
>> Shared-cache is used for 2 reasons:
>> - to improve main.db database file data access; and,
>> - use the "Unlock Notification Feature" (sqlite3_unlock_notify) to
>> avoid many SQLITE_LOCKED errors based on the code provided in 
>> http://www.sqlite.org/unlock_notify.html
>> .
>>
>> In my understanding, the expected behavior should be:
>> - If a SQL statement with only read (heavy) operations is passed to
>> Thread-1; and,
>> - Another SQL statement with a write (and some reads) operations is
>> passed to Thread-3;
>> - Both should run in parallel in a multi-core system.
>>
>> But, it seems that the Thread-3 is waiting for the Thread-1 to 
>> finish
>> its work before continue.
>> This behavior is turning the solution into a non-scalable solution.
>>
>> As far as I could debug (and understand) using Visual Studio 2005, 
>> it
>> seems that Thread-3 (in the above example) is waiting in for a lock 
>> in
>> the sqlite3BtreeEnterAll function. See the piece of the call stack
>> below:
>>
>>> sqlite3.dll!winMutexEnter(sqlite3_mutex * p=0x012fb2d8)  Line 
>>> 15159
>>  sqlite3.dll!sqlite3BtreeEnterAll(sqlite3 * db=0x1bb05ac8)  Line
>> 36706 + 0x11 bytes
>>  sqlite3.dll!sqlite3LockAndPrepare(sqlite3 * db=0x, const
>> char * zSql=0x00c0, int nBytes=180, int saveSqlFlag=1,
>> sqlite3_stmt * * ppStmt=0x, const char * * 
>> pzTail=0x0965f63c)
>> Line 9672
>>  sqlite3.dll!sqlite3_prepare_v2(sqlite3 * db=0x1bb05ac8, const char
>> * zSql=0x00c0, int nBytes=180, sqlite3_stmt * * 
>> ppStmt=0x013a9094,
>> const char * * pzTail=0x0965f63c)  Line 9747 + 0x1f bytes
>>  apr_dbd_sqlite3.dll!sqlite3_blocking_prepare_v2(sqlite3 *
>> db=0x, const char * zSql=, int nSql=, sqlite3_stmt * *
>> ppStmt=0x, const char * * pz=0x)  Line 247 + 0x10
>> bytes
>>  apr_dbd_sqlite3.dll!dbd_sqlite3_select(apr_pool_t *
>> pool=0x013a9050, apr_dbd_t * sql=0x029cc040, apr_dbd_results_t * *
>> results=0x0965f688, const char * query=0x00c0, int seek=0) 
>> Line
>> 307 + 0x33 bytes
>>  libaprutil-1.dll!apr_dbd_select(const apr_dbd_driver_t *
>> driver=0x00a66270, apr_pool_t * pool=0x013a9050, apr_dbd_t *
>> handle=0x029cc040, apr_dbd_results_t * * res=0x0965f688, const char 
>> *
>> statement=0x00c0, int random=0)  Line 319 + 0x22 bytes
>>
>> While Thread-1 call stack looks like this:
>>
>>> sqlite3.dll!sqlite3VdbeExec(Vdbe * p=0x1c471318)  Line 52862
>>  sqlite3.dll!sqlite3Step(Vdbe * p=0x)  Line 49388 + 0x7 
>> bytes
>>  sqlite3.dll!sqlite3_step(sqlite3_stmt * pStmt=)  Line 49449 + 0x7
>> bytes
>>  

Re: [sqlite] a system for arbitrarily tagging rows in a table

[Jim Showalter]

You can have the tags in a separate table that has a foreign-key to 
the table with the rows in in you want to tag, and then do a select 
from that table to find the tagged rows. If you need to search for 
multiple tags at a time, each requires a join: select t from tagged as 
t join t.tags as tag1 join t.tags as tag2 where tag1 = some value and 
tag2 = some value.

- Original Message - 
From: "Jay A. Kreibich" 
To: ; "General Discussion of SQLite Database" 

Sent: Friday, July 24, 2009 9:21 PM
Subject: Re: [sqlite] a system for arbitrarily tagging rows in a table


> On Fri, Jul 24, 2009 at 09:20:29PM -0500, P Kishor scratched on the 
> wall:
>> I am trying to develop a "tagging" system, whereby each row in a 
>> table
>> can be tagged with arbitrary number of tags.
>
>  This smells of a Relational division problem.  If you're dealing 
> with
>  tags you might want to have a look at that (Celko has a few good
>  articles on it).  Since SQL lacks a native Relational division
>  operator, chances are a solution in that direction is going to be
>  more complex -- at least for this problem.  But any time I've done
>  tags or attributes, sooner or later I find myself needing to do a
>  division.  They come in handy any time you say "my data is vertical
>  but I need it horizontal."  You might want to read up on them just 
> to
>  have that knowledge available.
>
>
>> TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT);
>> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT);
>> TABLE foo_tag (f_id INTEGER, t_id INTEGER);
>
>
>> I have the following solution. Could I do better or differently?
>
>  I'm not sure about "better", but here's different:
>
> sqlite> SELECT foo.f_name, tag.t_name
>   ...> FROM tag AS target
>   ...>   NATURAL JOIN foo_tag AS target_ft
>   ...>   NATURAL JOIN foo
>   ...>   NATURAL JOIN foo_tag
>   ...>   NATURAL JOIN tag
>   ...> WHERE target.t_name = 'bar'
>   ...>   AND tag.t_id != target.t_id
>   ...> ORDER BY foo.f_name, tag.t_name;
>
>  This basically folds your IN sub-select back into the main query.
>  We join "foo" to the tag table in two directions... one to find
>  the search target tag id and the other to produce the output.
>
>  "tag AS target" with the first WHERE clause should return one row.
>  We join that through "foo_tag AS target_ft" to get a list of foo 
> ids
>  that have the search target tag.  We then build the normal output
>  list by joining that back through the foo_tag bridge table to the
>  tags, and throw out any rows with an output tag id that matches the
>  search target tag id.
>
>  Simple!
>
>  Best of all, the target only appears in the query once, and your 
> name
>  convention means we can use NATURAL JOINs to keep things clean.  It
>  also totally falls apart if you need to search on more than one 
> tag.
>  That's where Relational division comes in.
>
>   -j (who had to draw a picture to get it right)
>
> -- 
> Jay A. Kreibich < J A Y  @  K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs.  We 
> have
> a protractor."   "I'll go home and see if I can scrounge up a ruler
> and a piece of string."  --from Anathem by Neal Stephenson
> ___
> 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] Installing SQLite

[Jim Showalter]

Jay, your email below is superb. It's dispassionate, accurate, 
diplomatic, and informative.

I was also disappointed to see this email list go the way of so many 
others over the past day or so, but it's easy enough to fix it. Stop 
calling someone else a doofus for selecting a particular OS to work 
with, stop using the word "sucks" to describe a deficiency in the 
build system and instead offer constructive criticism and suggestions 
for improvement (as Jay does below), and stop calling people trolls 
even if their email is a bit of a flame.

In the short time I've subscribed to this list, I've been amazed at 
the technical acumen of the people who reply to questions. The replies 
I've gotten for my questions--even stupid ones--have been very 
helpful, and some of the in-depth discussions of particular features 
(like iterating over chunks of blobs) are fascinating.

- Original Message - 
From: "Jay A. Kreibich" 
To: "Jay A. Kreibich" 
Cc: "General Discussion of SQLite Database" 
Sent: Thursday, July 23, 2009 9:07 AM
Subject: Re: [sqlite] Installing SQLite


>
>  Grrr I didn't meant to send this just yet.  But since I did, I
>  guess I need to finish it.
>
> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched 
> on the wall:
>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched 
>> on the wall:
>>
>> > I think the OP just has the wrong expectations.
>>
>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>   download, I have to agree that the current build and distribution
>>   state of SQLite is... let's just say "less than ideal."  I've 
>> been
>>   writing a lot of documentation on just this issue, and unless you
>>   want a perfect vanilla install, there are definitely a lot of 
>> hoops
>>   you have to jump through compared to most open-source projects of
>>   similar design.
>>
>>   A few versions ago we transitioned from a traditional UNIX style
>>   project, complete with "configure" script, to having the 
>> amalgamation
>>   be the "standard" distribution.  I've always felt like that
>>   transition is incomplete, and we've never gotten back to where we
>>   were before.
>>
>>   The amalgamation works well enough if what you want is mostly
>>   defaults.  The issue is that, while you can change a few of the
>>   #defines for numeric defaults, most of the more interesting build
>>   options won't work with the amalgamation.  Only that's it.  As 
>> the
>>   website clearly states, there is no other supported option.
>
>  The "by the file" distribution is bad enough, but you're totally 
> out
>  of luck if you need to go to the tree for some of the really 
> complex
>  build options.  Of course, the "by the file" distribution is there
>  and available for download because a lot of people still need it, 
> but
>  apparently not enough to justify keeping it updated.  That's a bit 
> of
>  a contradiction... for a piece of software that prides itself on 
> its
>  testing systems, the end-users sees a whole lot of "there but not
>  supported; it might work it might not; you're on your own" stuff.
>  That's normally a big red flag in my book.  Stuff should be there, 
> be
>  supported, and be documented, or it shouldn't.  The current 
> situation
>  is only easily understandable if you've been following SQLite for a 
> few
>  years.
>
>  And the OP is right... the build docs suck.  There are no build 
> docs
>  for most downloads, just a archive file with source.  No Makefiles, 
> no
>  nothing.  There is no "how to build" on the documentation page 
> (just
>  docs on build options) and the Wiki pages on building and the
>  amalgamation are so out of date that they have negative value, 
> doing
>  little more than confusing people.
>
>  I realize that you might take the argument that anyone smart enough
>  need to build the SQLite engine into an application should be able 
> to
>  figure that out, and with enough time, maybe that's right a fair
>  percentage of the time.  On the other hand, the whole point of 
> moving
>  to the amalgamation was to simplify putting SQLite into an
>  application.  I think overall it does this, but only if you're
>  working from the default everything.  The amalgamation might be
>  easier or better (for some definition of those terms) if you
>  understand it, but it is radically different from nearly every 
> other
>  open source project out there.  A bit of hand holding, even for
>  experienced developers, is not out of place.
>
>  SQLite is a great product, but there is still a lot of room for
>  improvement on the packaging, distribution, and "productization"
>  of the code.
>
>
>  And even if his post was a bit negative, I'm a little disappointed 
> at
>  the community response.  We're here to help, not criticize.  If 
> that
>  was my introduction to the product and its users, you can be sure I
>  wouldn't be coming back.  If you're not going to 

[sqlite] Overhead of indexes?

[Jim Showalter]

I have everything working now, and am just tidying up. I want to add 
indexes, if needed, but this is for a cellphone app, and I'm worried 
about using up space, particularly when the data is unlikely to be 
more than a couple thousand rows in total. But there is a query that 
might be slow without indexes, so it's kind of a catch-22.

The query looks like this:

select distinct * from tbl where a <> 0 order by a desc, b desc, c 
desc, d desc limit 2;

where a, b, c, and d are all type INTEGER.

What is the overhead in O notation for adding an index to a column in 
SQLite? 

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

Re: [sqlite] Subqueries

[Jim Showalter]

I recommend starting with a smaller query and adding to it. For 
example, can you do a select count from the table? Then can you do a 
select * from the table? Then can you do a select * with an order by? 
And so forth, building up the query one piece at a time until it does 
what you want.

I'm not smart enough to write complex (or even pretty simple) queries 
in one go. Iterative development works well for me.

- Original Message - 
From: "Hubboo" 
To: 
Sent: Tuesday, July 21, 2009 7:55 AM
Subject: Re: [sqlite] Subqueries


>
> Thanks for your reply igor but i get this error
>
> Likely SQL syntax error: select * from Academic
> 
where AcNum = ( 

> select AcNum from Interest 

> where AcNum not in (select AcNum from Author) 

> group by AcNum 

> order by count(*) desc limit 1 

> ); [ near "AcNum": syntax error ]
> Exception Name: NS_ERROR_FAILURE
> Exception Message: Component returned failure code: 0x80004005
> (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]
>
>
>
> Igor Tandetnik wrote:
>>
>> Hubboo  wrote:
>>> Q. Among the academics who have no papers, who has the greatest
>>> number of interests..
>>>
>>> Database looks like
>>>
>>> Department(DeptNum, Descrip, Instname, DeptName, State, Postcode)
>>> Academic(AcNum, DeptNum, FamName, GiveName, Initials, Title)
>>> Paper(PaNum, Title)
>>> Author(PaNum, AcNum)
>>> Field(FieldNum, ID, Title)
>>> Interest(FieldNum, AcNum, Descrip)
>>
>> Try this:
>>
>> select * from Academic
>> where AcNum = (
>> select AcNum from Interest
>> where AcNum not in (select AcNum from Author)
>> group by AcNum
>> order by count(*) desc limit 1
>> );
>>
>> Igor Tandetnik
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> -- 
> View this message in context: 
> http://www.nabble.com/Subqueries-tp24587437p24589275.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] Resolve: problem getting triggers to work

[Jim Showalter]

Android team screwed up.

I filed:

http://code.google.com/p/android/issues/detail?id=3302
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Updated info on getting trigger to work

[Jim Showalter]

I dumped the SQL being executed to a file:

drop table if exists Words;
create table Words (_id integer primary key autoincrement, text text 
not null unique);
create trigger ut_Words_cannotChangeWordTextOnUpdate before update on 
Words for each row begin select raise(rollback, 'update on table Words 
violates constraint ut_Words_cannotChangeWordTextOnUpdate') where 
OLD.text <> NEW.text;end;
insert into Words (text) values ("word1");
select * from Words;
update Words set text='different_word' where _id=1;

and then ran sqlite3, sending it the contents of the file:

C:\Installs\SQLite>sqlite3 db2 < schema2.txt

The trigger works as expected.

1|word1
SQL error near line 6: update on table Words violates constraint 
ut_Words_cannotChangeWordTextOnUpdate

Unfortunately, it still doesn't work when called via the Android Java 
API over SQLite. Digging into that now. 

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

Re: [sqlite] Problem getting trigger to work

[Jim Showalter]

It's an update.

The Java code for my DataAccessor (a lightweight wrapper over 
Android's wrapper over SQLite) checks the ID. If the ID is set to -1, 
it's an insert, otherwise it's an update.

A Word (word2) has been previously saved, and its ID has been saved to 
word2Id.

The test code is doing this:

Word differentTextWord = new Word();
differentTextWord.setId(word2Id); <<< reuse existing ID
differentTextWord.setText(word2.getText() + "_different");

boolean caughtExpectedException = false;

try
{
dataAccessor.saveWord(differentTextWord);
}
catch (SQLiteConstraintException e)
{
caughtExpectedException = true;
}

assertTrue(caughtExpectedException); <<<< this fails

I don't know how to test this with the SQLite console, because it's 
actually running on the Android emulator.


- Original Message - 
From: "Simon Slavin" <slav...@hearsay.demon.co.uk>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, July 17, 2009 11:00 PM
Subject: Re: [sqlite] Problem getting trigger to work


>
> On 18 Jul 2009, at 4:32am, Jim Showalter wrote:
>
>> create table words
>> (
>>_id integer primary key autoincrement,
>>wordText text not null unique
>> );
>>
>> I have triggers that work, which I got from
>> http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers.
>>
>> Now I'm trying to modify the update trigger to prevent saving a 
>> word
>> with the same ID but different text:
>>
>> create trigger ut_words_cannotChangeWordTextOnUpdate
>> before update on words
>> for each row
>> begin
>>select raise(
>>rollback,
>>'update on table WORDS violates constraint
>> ut_words_cannotChangeWordTextOnUpdate')
>>where OLD.wordText <> NEW.wordText;
>> end;
>
> What command are you using to make the modification ?  Is it an 
> INSERT
> or an UPDATE ?
>
> Have you tried executing the appropriate command in the sqlite3
> command-line application ?
>
> 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] Problem getting trigger to work

[Jim Showalter]

I have this simple schema:

create table words
(
_id integer primary key autoincrement,
wordText text not null unique
);

I have triggers that work, which I got from 
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers.

Now I'm trying to modify the update trigger to prevent saving a word 
with the same ID but different text:

create trigger ut_words_cannotChangeWordTextOnUpdate
before update on words
for each row
begin
select raise(
rollback,
'update on table WORDS violates constraint 
ut_words_cannotChangeWordTextOnUpdate')
where OLD.wordText <> NEW.wordText;
end;

The schema seems to load without a problem, and I'm able to run my 
unit tests up to the point where I save a word with the same ID but 
with the wordText set to the existing word's wordText + "_different" 
(in other words, different text).

I expect a SQLiteConstraintException to be thrown, but no exception is 
thrown. It saves the modified word with the same ID, which it 
shouldn't be doing.

Yes, the trigger is being installed.

Is the trigger written incorrectly, or should I be looking somewhere 
else for the cause? If elsewhere, where? 

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

Re: [sqlite] Raise is not working

[Jim Showalter]

I'm defining the text of the error message in the schema, in the 
definition of raise, in the trigger definition:

create trigger fki_definitions_words_id before insert on definitions
for each row
begin
select raise (rollback, 'insert on table definitions violates 
foreign-key constraint fki_definitions_words_id')
where (select _id from words where _id = NEW.owningWordId ) is
null;

The text I expect to see in the thrown exception is:

'insert on table definitions violates foreign-key constraint 
fki_definitions_words_id'

but that's not happening.



- Original Message - 
From: "Simon Slavin" <slav...@hearsay.demon.co.uk>
To: "Jim Showalter" <j...@jimandlisa.com>; "General Discussion of 
SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 7:44 PM
Subject: Re: [sqlite] Raise is not working


>
> On 13 Jul 2009, at 2:17am, Jim Showalter wrote:
>
>> However, calling insertOrThrow raises SQLiteConstraintException, 
>> which
>> does not contain the text of the raise specified in the schema.
>> Instead, it just has:
>> error code 19: constraint failed
>>
>>
>>
>> Is there any way to get SQLite to honor the message in the raise in
>> the schema definition?
>
> You're the second person to report this problem recently.  Can I ask 
> how you're getting the text of the error message ?  If you just look 
> up 19 it will obviously give you the standard error text for 19, but 
> if you call the functions at
>
> http://www.sqlite.org/c3ref/errcode.html
>
> you should get the right text.
>
> Simon. 

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

Re: [sqlite] Raise is not working

[Jim Showalter]

However, calling insertOrThrow raises SQLiteConstraintException, which 
does not contain the text of the raise specified in the schema. 
Instead, it just has:
error code 19: constraint failed



Is there any way to get SQLite to honor the message in the raise in 
the schema definition?


- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:10 PM
Subject: Re: [sqlite] Raise is not working


> Nevermind--for whatever reason, Google saw fit to do this:
>
>/**
> * Convenience method for inserting a row into the database.
> *
> * @param table the table to insert the row into
> * @param nullColumnHack SQL doesn't allow inserting a completely
> empty row,
> *so if initialValues is empty this column will
> explicitly be
> *assigned a NULL value
> * @param values this map contains the initial column values for
> the
> *row. The keys should be the column names and the
> values the
> *column values
> * @return the row ID of the newly inserted row, or -1 if an 
> error
> occurred
> */
>public long insert(String table, String nullColumnHack,
> ContentValues values) {
>try {
>return insertWithOnConflict(table, nullColumnHack, 
> values,
> null);
>} catch (SQLException e) {
>Log.e(TAG, "Error inserting " + values, e);
>return -1;
>}
>}
> - Original Message - 
> From: "Jim Showalter" <j...@jimandlisa.com>
> To: "General Discussion of SQLite Database" 
> <sqlite-users@sqlite.org>
> Sent: Sunday, July 12, 2009 6:03 PM
> Subject: [sqlite] Raise is not working
>
>
>> Schema:
>>
>> create table words (_id integer primary key autoincrement, wordtext
>> text not null unique);
>>
>> create table definitions (_id integer primary key autoincrement,
>> owningWordId integer not null unique, deftext text not null);
>>
>> create trigger fki_definitions_words_id before insert on 
>> definitions
>> for each row
>> begin
>>select raise (rollback, 'insert on table definitions violates
>> foreign-key constraint fki_definitions_words_id')
>>where (select _id from words where _id = NEW.owningWordId ) is
>> null;
>>
>> end;
>>
>> Call db.insert, passing it a definition that has the owningWordId
>> set
>> to -1, and the insert returns a -1 instead of throwing.
>>
>> Because it doesn't throw, I don't have the error message "insert on
>> table definitions violates foreign-key constraint
>> fki_definitions_words_id" to work from. Information is simply lost.
>>
>> Why isn't it raising an exception?
>>
>> ___
>> 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] Raise is not working

[Jim Showalter]

Nevermind--for whatever reason, Google saw fit to do this:

/**
 * Convenience method for inserting a row into the database.
 *
 * @param table the table to insert the row into
 * @param nullColumnHack SQL doesn't allow inserting a completely 
empty row,
 *so if initialValues is empty this column will 
explicitly be
 *assigned a NULL value
 * @param values this map contains the initial column values for 
the
 *row. The keys should be the column names and the 
values the
 *column values
 * @return the row ID of the newly inserted row, or -1 if an error 
occurred
 */
public long insert(String table, String nullColumnHack, 
ContentValues values) {
try {
return insertWithOnConflict(table, nullColumnHack, values, 
null);
} catch (SQLException e) {
Log.e(TAG, "Error inserting " + values, e);
return -1;
}
}
- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:03 PM
Subject: [sqlite] Raise is not working


> Schema:
>
> create table words (_id integer primary key autoincrement, wordtext
> text not null unique);
>
> create table definitions (_id integer primary key autoincrement,
> owningWordId integer not null unique, deftext text not null);
>
> create trigger fki_definitions_words_id before insert on definitions
> for each row
> begin
>select raise (rollback, 'insert on table definitions violates
> foreign-key constraint fki_definitions_words_id')
>where (select _id from words where _id = NEW.owningWordId ) is
> null;
>
> end;
>
> Call db.insert, passing it a definition that has the owningWordId 
> set
> to -1, and the insert returns a -1 instead of throwing.
>
> Because it doesn't throw, I don't have the error message "insert on
> table definitions violates foreign-key constraint
> fki_definitions_words_id" to work from. Information is simply lost.
>
> Why isn't it raising an exception?
>
> ___
> 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] Raise is not working

[Jim Showalter]

Should have included the code that calls:

long id = mDatabase.insert(tableName, null, contentValues);

if (id == -1)
{
throw new AppException(message, ErrorCodes.INSERT_FAILED);
}

The AppException is thrown. But when insert returns -1, that just 
means there was an error. Where is the specific error code?

Is Android swallowing raised exceptions and turning them all into 
just -1?

- Original Message - 
From: "Jim Showalter" <j...@jimandlisa.com>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 6:03 PM
Subject: [sqlite] Raise is not working


> Schema:
>
> create table words (_id integer primary key autoincrement, wordtext
> text not null unique);
>
> create table definitions (_id integer primary key autoincrement,
> owningWordId integer not null unique, deftext text not null);
>
> create trigger fki_definitions_words_id before insert on definitions
> for each row
> begin
>select raise (rollback, 'insert on table definitions violates
> foreign-key constraint fki_definitions_words_id')
>where (select _id from words where _id = NEW.owningWordId ) is
> null;
>
> end;
>
> Call db.insert, passing it a definition that has the owningWordId 
> set
> to -1, and the insert returns a -1 instead of throwing.
>
> Because it doesn't throw, I don't have the error message "insert on
> table definitions violates foreign-key constraint
> fki_definitions_words_id" to work from. Information is simply lost.
>
> Why isn't it raising an exception?
>
> ___
> 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] Raise is not working

[Jim Showalter]

Schema:

create table words (_id integer primary key autoincrement, wordtext 
text not null unique);

create table definitions (_id integer primary key autoincrement, 
owningWordId integer not null unique, deftext text not null);

create trigger fki_definitions_words_id before insert on definitions
for each row
begin
select raise (rollback, 'insert on table definitions violates 
foreign-key constraint fki_definitions_words_id')
where (select _id from words where _id = NEW.owningWordId ) is 
null;

end;

Call db.insert, passing it a definition that has the owningWordId set 
to -1, and the insert returns a -1 instead of throwing.

Because it doesn't throw, I don't have the error message "insert on 
table definitions violates foreign-key constraint 
fki_definitions_words_id" to work from. Information is simply lost.

Why isn't it raising an exception? 

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

Re: [sqlite] Number truncation problem in SQLite 3

[Jim Showalter]

I have found the problem. There was an incrementing trigger left in 
the database accidentally.

- Original Message - 
From: "John Machin" <sjmac...@lexicon.net>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Sunday, July 12, 2009 4:25 PM
Subject: Re: [sqlite] Number truncation problem in SQLite 3


> On 13/07/2009 8:40 AM, Roger Binns wrote:
>> -BEGIN PGP SIGNED MESSAGE-
>> Hash: SHA1
>>
>> Jim Showalter wrote:
>>> create table words (_id integer primary key autoincrement, 
>>> wordtext
>>> text not null unique, timestamp integer not null);
>>>
>>> public class Word
>>> {
>>> long _id;
>>> String wordtext;
>>> long timestamp;
>>> }
>>>
>>> timestamp:
>>> before save: 1247435151517
>>> after save : 1247435160847
>
> The "after" number is greater than the "before" number. That's
> truncation? Looks like some more information is required e.g. a 
> small
> piece of code (preferably runnable) that shows what exactly was 
> done,
> with debug prints in appropriate places.
>
>>>
>>> 64-bit max is: 9223372036854775807, so it should fit.
>>
>> http://catb.org/esr/faqs/smart-questions.html
>>
>> Your mailer headers show that you are using Windows. On Windows
>> (including a 64 bit environment) long is 32 bits.
>
> Ummm, each of the quoted numbers takes up 41 bits (unsigned).
>
> Cheers,
> John
> ___
> 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] Number truncation problem in SQLite 3

[Jim Showalter]

create table words (_id integer primary key autoincrement, wordtext 
text not null unique, timestamp integer not null);

public class Word
{
long _id;
String wordtext;
long timestamp;
}

timestamp:
before save: 1247435151517
after save : 1247435160847

64-bit max is: 9223372036854775807, so it should fit.

What am I doing wrong? 

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