Re: [sqlite] Foreign keys + NATURAL JOIN

Thanks. This leads me to the next question. Why does the statement below yield a cartesian product? SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Slw! Why does the statement below NOT yield a cartesian product? SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes

Re: [sqlite] Foreign keys + NATURAL JOIN

Kristoffer Danielsson wrote: > Q: Does foreign keys affect NATURAL JOINs? > > I haven't tested this. Does this NATURAL JOIN produce an inner join or a > cartesian product? The presence of foreign key constraints has no effect on the results of any queries, using natural joins or otherwise. A

Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Sat, Oct 17, 2009 at 7:16 PM, Clark Christensen wrote: > Sorry for top-posting... > > What's running on the the server?  A Perl CGI script?  Apache HTTPD?   > mod-perl? Although I have Apache mod_perl installed, I am running a plain vanilla Perl cgi script for now. > > Is

[sqlite] Foreign keys + NATURAL JOIN

Q: Does foreign keys affect NATURAL JOINs? SQLite 3.6.18 sample (NO foreign keys): CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL); CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL); SELECT * FROM a NATURAL JOIN b; -- Cartesian product! SQLite 3.6.19 sample (using

Re: [sqlite] suggestions for avoiding "database locked" on ajax

Sorry for top-posting... What's running on the the server? A Perl CGI script? Apache HTTPD? mod-perl? Is the AJAX exchange asyncronous? Are you sure the first AJAX exchange is finished when the second one fires? Does the AJAX request wait for a 200 response? Assuming Perl, are you

Re: [sqlite] reach into a memory database and retrieve a record by it's oid

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ron Arts wrote: > Is there a (relatively easy) way to hack up a function call that would > just retrieve one record by it's oid (primary integer key), and return it > in a form that would allow using the sqlite3_columm() functions? > > Something

[sqlite] reach into a memory database and retrieve a record by it's oid

Hi, maybe I should ask this on sqlite-dev, but I thought I'd try it here first. Is there a (relatively easy) way to hack up a function call that would just retrieve one record by it's oid (primary integer key), and return it in a form that would allow using the sqlite3_columm() functions?

Re: [sqlite] Creating a spatial index for a large number of points- sorry for the text formating

John, thank you for the comments ! Maybe I wasn't clear - the 10TB data is separated. It contains a lot of other data that I don't dream of storing in a database. But this bulk data is structured in fixed-length records, each record containing a vector of floating point values and some

Re: [sqlite] SQLite encription

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Francisc Romano wrote: > Is it possible to encript SQLite databases so that they cannot be referenced > outside the program that uses it? The SQLite database file has to be accessible to the program using SQLite so no matter what you do, a malicious

Re: [sqlite] Why FTS3 has the limitations it does

Agreed, HUGE thanks for FTS. Hopefully my original post didn't come off ungrateful. I was just confused by limitations that looked like they could have been removed during the initial design (at least more easily than they can now.) Scott's reply helps me understand this better, and perhaps

Re: [sqlite] Creating a spatial index for a large number of points- sorry for the text formating

I doubt SQLite is the right tool for this job, for a number of reasons. First, if the data is as simple as you say, you are probably better off writing your logic as straight C, rather than SQL. SQLite is VERY fast, but there is still an incredible amount of overhead in executing a query, in

Re: [sqlite] INTERSECT?

> I wasn't sure SQLite supported count(distinct). The docs don't seem to > mention it. But yes, apparently it does and the statement can be simplified > this way. Docs mention it though in very vague way. Here http://www.sqlite.org/lang_aggfunc.html in the second paragraph: "In any aggregate

Re: [sqlite] INTERSECT?

Pavel Ivanov wrote: >> select * from foo f1 where >> (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and >> f2.b in (...) )) = >>length_of_b_list >> and b in (...); > > Shouldn't this be simplified like this? > > select * from foo f1 where > (select count(distinct b)

Re: [sqlite] suggestions for avoiding "database locked" on ajax

On Fri, 16 Oct 2009, Roger Binns wrote: > To: General Discussion of SQLite Database > From: Roger Binns > Subject: Re: [sqlite] suggestions for avoiding "database locked" on ajax > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > P Kishor

Re: [sqlite] INTERSECT?

> select * from foo f1 where > (select count(*) from (select distinct b from foo f2 where f1.a = f2.a and > f2.b in (...) )) = >length_of_b_list > and b in (...); Shouldn't this be simplified like this? select * from foo f1 where (select count(distinct b) from foo f2 where f1.a = f2.a and

Re: [sqlite] Creating a spatial index for a large number of points - sorry for the text formating

Sorry for the formatting - it looked better when I sent it from Yahoo's web interface. - Original Message From: Igor Conom To: sqlite-users@sqlite.org Sent: Sat, October 17, 2009 9:03:54 AM Subject: [sqlite] Creating a spatial index for a large number of points

[sqlite] Creating a spatial index for a large number of points

Hello everybody! I need to create a spatial index for a large number of points and I would really like to hear your comments. Thank you in advance for your time. There are some very large files containing scientific data, stored in a very simple format (by very large I mean things in the

Re: [sqlite] SQLite encription

On 17 Oct 2009, at 1:11am, Francisc Romano wrote: > Is it possible to encript SQLite databases so that they cannot be > referenced > outside the program that uses it? > If not, can you set a user and password for a SQLite database? The head honcho of SQLite runs a company that has a product

[sqlite] SQLite encription

Hello! Is it possible to encript SQLite databases so that they cannot be referenced outside the program that uses it? If not, can you set a user and password for a SQLite database? Thank you! ___ sqlite-users mailing list sqlite-users@sqlite.org