[sqlite] Help with join

[jeff archer]

Here is my existing schema.  I would like to change this and remove the 
"NumDefects" from the [Analysis] table.
To do this I need to update the [vwAnalysis] view to determine the number of 
defects from the [Defects] table by the AnalysisID.
I can't seem to get the right select for the new version of the view.
 
CREATE TABLE [Analyzers] 
(AnalyzerID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,Name  VARCHAR NOT NULL UNIQUE
);
CREATE TABLE [Analysis] 
(AnalysisID    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,AnalyzerID    INTEGER NOT NULL REFERENCES [Analyzers] DEFERRABLE INITIALLY 
DEFERRED
,ScanID    INTEGER NOT NULL REFERENCES [Scans] DEFERRABLE INITIALLY DEFERRED
,Timestamp DATETIME NOT NULL
,EndTime   DATETIME NOT NULL DEFAULT CURRENT_TIME
,NumDefects    INTEGER DEFAULT 0
,Result    VARCHAR
);
CREATE TABLE [Defects] 
(DefectID  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
,ImageID   INTEGER NOT NULL REFERENCES [Images] DEFERRABLE INITIALLY 
DEFERRED
,AnalysisID    INTEGER NOT NULL REFERENCES [Analysis] DEFERRABLE INITIALLY 
DEFERRED
,X REAL NOT NULL DEFAULT 0.0
,Y REAL NOT NULL DEFAULT 0.0
,W REAL NOT NULL DEFAULT 0.0
,H REAL NOT NULL DEFAULT 0.0
);
CREATE VIEW [vwAnalysis] AS 
SELECT [Analyzers].[AnalyzerID] 
 , [Analyzers].[Name] AS [Analyzer] 
 , [Analysis].[AnalysisID] 
 , [Analysis].[ScanID] 
 , [Analysis].[Timestamp] 
 , [Analysis].[EndTime] 
 , [Analysis].[NumDefects] 
 , [Analysis].[Result] 
FROM   [Analysis] 
JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID] 
ORDER BY [Analysis].[Timestamp];

 
This is what I would like to do but I do not understand how to make the JOIN 
correctly.
 
SELECT [Analyzers].[AnalyzerID] 
 , [Analyzers].[Name] AS [Analyzer] 
 , [Analysis].[AnalysisID] 
 , [Analysis].[ScanID] 
 , [Analysis].[Timestamp] 
 , [Analysis].[EndTime]
 , COUNT(DefectID) AS NumDefects                                        
<<== 
count of Defects that match the AnalysisID
 , [Analysis].[Result] 
FROM   [Analysis] 
JOIN [Analyzers] ON [Analyzers].[AnalyzerID] = [Analysis].[AnalyzerID]
ORDER BY [Analysis].[Timestamp];
 

Thanks in advance for your help with this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma omit_readlock

[Richard Hipp]

On Thu, Feb 17, 2011 at 9:37 PM, Pavel Ivanov  wrote:

> > I'd appreciate it if anyone could let me know if this pragma still
> > works and how to use it if so.
>
> You can get away without pragma. Just implement your own VFS which
> will redirect all calls except lock-related to the standard VFS.
> Lock-related methods would be implemented as no-op and thus won't
> cause any performance problems.
>

That VFS already exists and is loaded by default in Unix builds.  Its name
is "unix-none".  If open the database connection using sqlite3_open_v2() and
specify "unix-none" as the VFS, no locking ever occurs.



>
>
> Pavel
>
> On Thu, Feb 17, 2011 at 4:36 PM, Mike Lin  wrote:
> > Dear all,
> >
> > I have a large SQLite database with genomic data which is strictly
> > read-only at this point, and in direct spite of the FAQ I intend to
> > query it concurrently on a network FS. Our parallel FS ought to be
> > able to handle a heavy read workload from our cluster, based on past
> > experience, but acquiring read locks seems to cause a bottleneck. I
> > have found a few mentions of an experimental 'pragma omit_readlock'
> > which seems just right for this situation, but I have not been having
> > luck making it work so far.
> >
> > I also found the following June 2009 e-mail to this list from Joerg
> > Hoinkis that seemed to make some progress by digging into the SQLite
> > source, but did not get any replies. I have similarly tried some
> > obvious things like opening my database with SQLITE_OPEN_READONLY and
> > making sure my file's permissions are 444.
> >
> > I'd appreciate it if anyone could let me know if this pragma still
> > works and how to use it if so.
> >
> > Thanks,
> > Mike Lin
> >
> >
> >> Hi!
> >>
> >> I'm trying to access a readonly database without locks, using the
> >> amalgamation of SQLite 3.6.14.2
> >>
> >> After I opened the database with flags = SQLITE_OPEN_READONLY I perform
> >>
> >> sqlite3_exec (db, "PRAGMA omit_readlock=ON", 0, 0, 0);
> >>
> >> I traced that down into the amalgamation to the point where the database
> >
> >> flag is set in flagPragma...
> >>
> >> db->flags |= p-mask;
> >>
> >> So I can be sure the pragma syntax is correct.
> >>
> >>
> >> Now when I execute a SELECT statement, it is parsed and I run into
> >> pagerSharedLock, but
> >> the pager->noReadlock variable is 0. As a result the database file gets
> >> locked.
> >>
> >>
> >> It look like the initialization of the pager->noReadlock flag, but that
> >> happens when the database is
> >> opened.
> >
> > I messed up the last sentence, better:
> >
> > It looks like the initialization of the pager->noReadlock flag only
> > happens
> > when the database is opened, but by that time the omit_readlock pragma is
> > not active.
> >
> >>
> >> Am I doing something wrong or did this ("Very experimental") feature got
> >
> >> lost in past refactorings?
> >>
> >>
> >> Thanks for listening & in advance to any hints
> >
> > Joerg
> > ___
> > 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
>



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

Re: [sqlite] pragma omit_readlock

[Pavel Ivanov]

> I'd appreciate it if anyone could let me know if this pragma still
> works and how to use it if so.

You can get away without pragma. Just implement your own VFS which
will redirect all calls except lock-related to the standard VFS.
Lock-related methods would be implemented as no-op and thus won't
cause any performance problems.


Pavel

On Thu, Feb 17, 2011 at 4:36 PM, Mike Lin  wrote:
> Dear all,
>
> I have a large SQLite database with genomic data which is strictly
> read-only at this point, and in direct spite of the FAQ I intend to
> query it concurrently on a network FS. Our parallel FS ought to be
> able to handle a heavy read workload from our cluster, based on past
> experience, but acquiring read locks seems to cause a bottleneck. I
> have found a few mentions of an experimental 'pragma omit_readlock'
> which seems just right for this situation, but I have not been having
> luck making it work so far.
>
> I also found the following June 2009 e-mail to this list from Joerg
> Hoinkis that seemed to make some progress by digging into the SQLite
> source, but did not get any replies. I have similarly tried some
> obvious things like opening my database with SQLITE_OPEN_READONLY and
> making sure my file's permissions are 444.
>
> I'd appreciate it if anyone could let me know if this pragma still
> works and how to use it if so.
>
> Thanks,
> Mike Lin
>
>
>> Hi!
>>
>> I'm trying to access a readonly database without locks, using the
>> amalgamation of SQLite 3.6.14.2
>>
>> After I opened the database with flags = SQLITE_OPEN_READONLY I perform
>>
>> sqlite3_exec (db, "PRAGMA omit_readlock=ON", 0, 0, 0);
>>
>> I traced that down into the amalgamation to the point where the database
>
>> flag is set in flagPragma...
>>
>> db->flags |= p-mask;
>>
>> So I can be sure the pragma syntax is correct.
>>
>>
>> Now when I execute a SELECT statement, it is parsed and I run into
>> pagerSharedLock, but
>> the pager->noReadlock variable is 0. As a result the database file gets
>> locked.
>>
>>
>> It look like the initialization of the pager->noReadlock flag, but that
>> happens when the database is
>> opened.
>
> I messed up the last sentence, better:
>
> It looks like the initialization of the pager->noReadlock flag only
> happens
> when the database is opened, but by that time the omit_readlock pragma is
> not active.
>
>>
>> Am I doing something wrong or did this ("Very experimental") feature got
>
>> lost in past refactorings?
>>
>>
>> Thanks for listening & in advance to any hints
>
> Joerg
> ___
> 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] Speed up count(distinct col)

[Yuzem]

BareFeetWare-2 wrote:
> 
> Oh, I see, so the "complication" is that you would have to change the
> columns used in your bash script. That shouldn't be a big problem, but
> I'll leave the bash script to you.
> 
It isn't a big problem, the "complication" was to adapt all the tables and
inserts and column declarations only for testing.


BareFeetWare-2 wrote:
> 
> If for some reason rewriting the insert command in the bash script is
> insurmountable, you can just create a view in SQL to match the
> expectations of the bash script. That view can funnel each insert to the
> underlying SQL schema table columns using an "instead of insert" trigger.
> Let me know if you need more info on this.
> 
That's not necessary, I will adapt your code to my database schema.


BareFeetWare-2 wrote:
> 
> or you can change the delete trigger to remove statistics that drop to a
> zero count:
> 
> begin immediate
> ;
> drop trigger if exists "Movie People delete"
> ;
> create trigger "Movie People delete"
> on "Movie People"
> after delete
> begin
> insert or replace into "Capacity People Statistics" (Capacity_ID,
> People_ID, Count)
> select
>   old.Capacity_ID
> , old.People_ID
> , (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
>   where Capacity_ID = old.Capacity_ID and People_ID = 
> old.People_ID
>   )
> ;
> delete from "Capacity People Statistics"
> where Count = 0
> ;
> end
> ;
> commit
> ;
> 
Oh, I see, that makes a lot of sense, that was one of the problem I had,
this way I can delete directors without using a distinct, great!


BareFeetWare-2 wrote:
> 
> Great, I'm glad we finally got there :-)
> 
Thank you very much for all your help, I will try to adapt the code to my
schema, I will let you know if I meet any problem.
Thanks again!
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30954516.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] pragma omit_readlock

[Mike Lin]

Dear all,

I have a large SQLite database with genomic data which is strictly
read-only at this point, and in direct spite of the FAQ I intend to
query it concurrently on a network FS. Our parallel FS ought to be
able to handle a heavy read workload from our cluster, based on past
experience, but acquiring read locks seems to cause a bottleneck. I
have found a few mentions of an experimental 'pragma omit_readlock'
which seems just right for this situation, but I have not been having
luck making it work so far.

I also found the following June 2009 e-mail to this list from Joerg
Hoinkis that seemed to make some progress by digging into the SQLite
source, but did not get any replies. I have similarly tried some
obvious things like opening my database with SQLITE_OPEN_READONLY and
making sure my file's permissions are 444.

I'd appreciate it if anyone could let me know if this pragma still
works and how to use it if so.

Thanks,
Mike Lin


> Hi!
>
> I'm trying to access a readonly database without locks, using the
> amalgamation of SQLite 3.6.14.2
>
> After I opened the database with flags = SQLITE_OPEN_READONLY I perform
>
> sqlite3_exec (db, "PRAGMA omit_readlock=ON", 0, 0, 0);
>
> I traced that down into the amalgamation to the point where the database

> flag is set in flagPragma...
>
> db->flags |= p-mask;
>
> So I can be sure the pragma syntax is correct.
>
>
> Now when I execute a SELECT statement, it is parsed and I run into
> pagerSharedLock, but
> the pager->noReadlock variable is 0. As a result the database file gets
> locked.
>
>
> It look like the initialization of the pager->noReadlock flag, but that
> happens when the database is
> opened.

I messed up the last sentence, better:

It looks like the initialization of the pager->noReadlock flag only
happens
when the database is opened, but by that time the omit_readlock pragma is
not active.

>
> Am I doing something wrong or did this ("Very experimental") feature got

> lost in past refactorings?
>
>
> Thanks for listening & in advance to any hints

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

Re: [sqlite] FTS slowdown with matchinfo

[Iker Arizmendi]

Dan Kennedy wrote:
> On 02/17/2011 05:41 AM, Iker Arizmendi wrote:
>> Dan Kennedy wrote:
>>> Can you make the database available for download? And
>>> supply the exact query you are using too? I'd like to
>>> know why this is. Thanks.
>>>
>>> Dan.
>>>
>> You can find a tarball of the DB file here:
>>
>>  http://www.research.att.com/people/Arizmendi_Iker/geo.db.tgz
>>
>> This query runs in around 1.2 seconds:
>>
>>  SELECT length(content)
>>  FROM locateme
>>  WHERE locateme MATCH 'newark OR new OR brunswick';
>>
>> And this one in around 8.5 minutes:
>>
>>  SELECT length(matchinfo(locateme, 'x'))
>>  FROM locateme
>>  WHERE locateme MATCH 'newark OR new OR brunswick';
>>
> 
> The database uses a custom tokenizer - "stopwords" - so I can't
> run the queries directly. If I dump the data into a regular fts3
> table using the default tokenizer and then run your queries with
> 3.7.5 they both run in pretty much the same amount of time. Both
> much quicker than 1 second on a Linux PC.
> 
> There was a bug causing excessive calls to realloc() fixed a
> little while ago, although from memory I don't think it would
> have hit this case. The symptoms are similar though, so I could
> easily be wrong on that.
> 
> Suggest upgrading to 3.7.5 to see if that clears the problem.
> 
> If you can get this slowdown with 3.7.5 and one of the built in
> tokenizers, please post so I can look again.
> 
> Thanks,
> Dan.

Upgrading to 3.7.5 (from 3.7.4) did the trick.

Thanks again!

Iker





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

[sqlite] Problem with sqlite

[Michel Di Croci]

Hi everyone,

I'm in the same situation as this person,

http://www.mail-archive.com/sqlite-users@sqlite.org/msg08106.html

I would like to know if it's feasible to user column alias in the same
query... This old suggestion is correct in this example, but i have very
long request so that's why I'm wondering if there's a way I could spare some
typing and also, since it would be for teaching purpose, I prefer personnaly
to take longer steps and have more readibility.

Thanks,

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

Re: [sqlite] Question regarding SQLITE_CANTOPEN

[Jay A. Kreibich]

On Thu, Feb 17, 2011 at 04:01:39PM +0100, Sven L scratched on the wall:
> 
> sqlite3* db = NULL;
> if (sqlite3_open_v2(path, , SQLITE_OPEN_READWRITE, NULL) == 
> SQLITE_CANTOPEN)
> {
> // Ok, database does not exist. Still, db != NULL...?
> ASSERT(db != NULL);
> }
>  
> Database cannot be opened, but we get an open handle to it... Please explain!

  http://sqlite.org/c3ref/open.html

 ...A database connection handle is usually returned in *ppDb, even if
 an error occurs. The only exception is that if SQLite is unable to
 allocate memory to hold the sqlite3 object, a NULL will be written
 into *ppDb instead of a pointer to the sqlite3 object. If the
 database is opened (and/or created) successfully, then SQLITE_OK
 is returned. Otherwise an error code is returned. The
 sqlite3_errmsg() or sqlite3_errmsg16() routines can be used to
 obtain an English language description of the error following a
 failure of any of the sqlite3_open() routines.


   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Pivot table from multiple columns

[Joe Bennett]

Cool, thank you! This works perfect... Now I have to disect it and figure
this syntax out more... Thanks to all for the education!



-Joe

On Wed, Feb 16, 2011 at 5:15 PM, Simon Davies
wrote:

> On 16 February 2011 23:00, Joe Bennett  wrote:
> > OK, this looks like it concatenated Column_1 and Column_2 and returns the
> > count of the new unique concatenated pair? What I am looking for (and I
> > apologize for not being clear) is a list of the unique values (Column
> > 1 and 2 appended) and their count... I'll try to demonstrate the expected
> > example from the table example I gave below:
> >
> > *Result**Count(result)*
> >
> > Value A   2
> > Value B   1
> > Value C   2
> > Value D   2
> > Value E   1
> >
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> create table tst( c1 integer, c2 integer );
> sqlite> insert into tst values( 1, 3 );
> sqlite> insert into tst values( 2, 1 );
> sqlite> insert into tst values( 3, 4 );
> sqlite> insert into tst values( 4, 5 );
> sqlite>
> sqlite> select val, count( val ) from  ( select c1 as val from tst
> union all select c2 from tst ) group by val;
> 1|2
> 2|1
> 3|2
> 4|2
> 5|1
> sqlite>
>
> Regards,
> Simon
>  ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding SQLITE_CANTOPEN

[Richard Hipp]

On Thu, Feb 17, 2011 at 10:01 AM, Sven L  wrote:

>
> sqlite3* db = NULL;
> if (sqlite3_open_v2(path, , SQLITE_OPEN_READWRITE, NULL) ==
> SQLITE_CANTOPEN)
> {
>// Ok, database does not exist. Still, db != NULL...?
>ASSERT(db != NULL);
> }
>
> Database cannot be opened, but we get an open handle to it... Please
> explain!
>

The handle returned so that you can call sqlite3_errmsg(db).



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



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

[sqlite] Question regarding SQLITE_CANTOPEN

[Sven L]

sqlite3* db = NULL;
if (sqlite3_open_v2(path, , SQLITE_OPEN_READWRITE, NULL) == SQLITE_CANTOPEN)
{
// Ok, database does not exist. Still, db != NULL...?
ASSERT(db != NULL);
}
 
Database cannot be opened, but we get an open handle to it... Please explain!
 
Thanks.   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referring to a column alias in the same query.

[Igor Tandetnik]

Michel Di Croci  wrote:
> I'm in the same situation as this person,
> 
> http://www.mail-archive.com/sqlite-users-CzDROfG0BjIdnm+yROfE0A@public.gmane.org/msg08106.html
> 
> I would like to know if it's feasible to user column alias in the same
> query...

Only via a subselect. Something like this:

select some-expr(alias1, alias2) from (
  select expr1 alias1, expr2 alias2 from ... where ...
);

-- 
Igor Tandetnik

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

Re: [sqlite] SQLite on Custom OS

[Simon Slavin]

I can only answer two of those.

> -> Suppose if I have a empty data base(test.db) created by Shell.c file 
> can I use the same database(test.db) in the custom OS?

The format of a SQLite database file is the same on all platforms.  You can 
create one on one platform, move it to another platform and edit it, then move 
it to another platform and read it.

> -> How can we access the database in a PC or ODBC client remotely??

Mount the drive using whatever file sharing software your platform supports.

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

[sqlite] SQLite on Custom OS

[Anil A Kumar]

Hello All,

I am pretty new to SQLite and in the process of porting SQLite to a custom 
OS.
After going through the  link  ->  http://www.sqlite.org/custombuild.html 
I learnt that implementation is required in Sqlite.c -> sqlite_initialize( 
)

now the questions are:
-> Is it required to follow all the interfaces given between ** Begin file 
os_win.c** and ** End of os_win.c *** in sqlite.c for custom OS?

-> when I compile the file in the custom OS and run the sample program I 
am getting error message "can't open database: no such vfs:"
 what could be the problem?? any solutions?

-> Suppose if I have a empty data base(test.db) created by Shell.c file 
can I use the same database(test.db) in the custom OS?

-> what is the  'approximate' time/efforts required to port Sqlite into a 
custom OS??

-> How can we access the database in a PC or ODBC client remotely??



Please give these details.

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

Re: [sqlite] How to use sqlite and pthread together?

[Richard Hipp]

On Wed, Feb 16, 2011 at 4:56 PM, Hailiang Shen  wrote:

> Dear All,
>
> I am trying to apply multiple threads with sqlite to just query (no insert,
> update, delete operation) to compute objective values in optimization. But
> I
> cannot get it correct. I compiled pthread to a dll for use with sqlite in
> VC++. I am using separate database connection for each thread.
>

Using threads is like running with scissors - You are likely to get hurt and
so the best approach is to not do it.

If you want to run queries in parallel, I suggest putting each query in a
separate process.

If your knowledge of threads is so limited that you don't know how to enable
them and you are trying to use pthreads on windows, then your chances of
getting hurt are compounded.  This is all the more reason to use separate
processes, not threads, for parallelism.


>
>
>
> Any sample codes would be best.
>
>
>
> Thanks,
>
> Hailiang
>
>
>
> /**/
>
> Hailiang Shen
>
> Ph.D. Candidate
>
> Water Resources Engineering
>
> University of Guelph
>
> 315,  Engineering bldg.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] How to use sqlite and pthread together?

[Hailiang Shen]

Dear All, 

I am trying to apply multiple threads with sqlite to just query (no insert,
update, delete operation) to compute objective values in optimization. But I
cannot get it correct. I compiled pthread to a dll for use with sqlite in
VC++. I am using separate database connection for each thread. 

 

Any sample codes would be best. 

 

Thanks,

Hailiang 

 

/**/

Hailiang Shen

Ph.D. Candidate

Water Resources Engineering

University of Guelph

315,  Engineering bldg.

 

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

[sqlite] Referring to a column alias in the same query.

[Michel Di Croci]

Hi everyone,

I'm in the same situation as this person,

http://www.mail-archive.com/sqlite-users@sqlite.org/msg08106.html

I would like to know if it's feasible to user column alias in the same
query... This old suggestion is correct in this example, but i have very
long request so that's why I'm wondering if there's a way I could spare some
typing and also, since it would be for teaching purpose, I prefer personnaly
to take longer steps and have more readibility.

Thanks,

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

Re: [sqlite] foreign key on delete no action

[BareFeetWare]

>> 3. For backwards parsing compatibility, am I better off just leaving the 
>> action blank instead of explicitly writing "on delete no action"?
> 
> Yes. Good plan.

Great, thanks for the definitive response :-)

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] FTS slowdown with matchinfo

[Dan Kennedy]

On 02/17/2011 05:41 AM, Iker Arizmendi wrote:
> Dan Kennedy wrote:
>>
>> Can you make the database available for download? And
>> supply the exact query you are using too? I'd like to
>> know why this is. Thanks.
>>
>> Dan.
>>
>
> You can find a tarball of the DB file here:
>
>  http://www.research.att.com/people/Arizmendi_Iker/geo.db.tgz
>
> This query runs in around 1.2 seconds:
>
>  SELECT length(content)
>  FROM locateme
>  WHERE locateme MATCH 'newark OR new OR brunswick';
>
> And this one in around 8.5 minutes:
>
>  SELECT length(matchinfo(locateme, 'x'))
>  FROM locateme
>  WHERE locateme MATCH 'newark OR new OR brunswick';
>

The database uses a custom tokenizer - "stopwords" - so I can't
run the queries directly. If I dump the data into a regular fts3
table using the default tokenizer and then run your queries with
3.7.5 they both run in pretty much the same amount of time. Both
much quicker than 1 second on a Linux PC.

There was a bug causing excessive calls to realloc() fixed a
little while ago, although from memory I don't think it would
have hit this case. The symptoms are similar though, so I could
easily be wrong on that.

Suggest upgrading to 3.7.5 to see if that clears the problem.

If you can get this slowdown with 3.7.5 and one of the built in
tokenizers, please post so I can look again.

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