Re: [sqlite] Re: Re: "wrong" index chosen, why?

--- Chris Eich <[EMAIL PROTECTED]> wrote: > The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN > trick to disable table reordering: > > sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d, > device_perf_interval dpi WHERE d.device_id=dpi.device_id AND >

[sqlite] LIKE operator case-senisitive for international characters

I have searched many forums without success for the following question: Is there any possibility to use LIKE operator in a SELECT statment - without being case sensitive AND with support for international characters? I am using "PRAGMA case_sensitive_like=OFF;". The LIKE statement is in the

Re: [sqlite] Replicating table data from sqlite to ms sql server

I see...any idea as to how exactly? I really am out of ideas regarding this... :( John Stanton wrote: > > Everything is possible programatically. You communicate from the Sqlite > program as you would from any application program to Sql Server. > > maitong uy wrote: >> The sqlite runs in a

Re: [sqlite] Replicating table data from sqlite to ms sql server

Everything is possible programatically. You communicate from the Sqlite program as you would from any application program to Sql Server. maitong uy wrote: The sqlite runs in a Linux environment and can be accessed through a web server. The ms sql server resides in a windows environment and

[sqlite] RE: RE: Replicating table data from sqlite to ms sql server

I'm using it directly. Another problem is that I also have to do it vice-versa, replicating the data from sql server into sqlite. Samuel R. Neff wrote: > > > Are you using sqlite directly or some wrapper? If you're using a wrapper > then most likely you can write the MSSQL integration code

Re: [sqlite] Re: Re: "wrong" index chosen, why?

The output of EXPLAIN QUERY PLAN doesn't change when I use the CROSS JOIN trick to disable table reordering: sqlite> explain query plan SELECT d.device_type, dpi.* FROM device d, device_perf_interval dpi WHERE d.device_id=dpi.device_id AND dpi.interval_type=1 AND dpi.interval_duration=300;

[sqlite] RE: Replicating table data from sqlite to ms sql server

The program is in C, can it write to an sql server that resides on another server? Griggs, Donald wrote: > > > Regarding: > > I'm new to sqlite and I was wondering if there is a way to > programmatically replicate table data from a database in sqlite server > to another database in ms sql

[sqlite] Re: Re: "wrong" index chosen, why?

Chris Eich <[EMAIL PROTECTED]> wrote: Overview Of The Optimizer (1.0 says "column = expression" not "constant expression", 5.0 says "joins are nested loops") and the EXPLAIN QUERY PLAN output gave me this mental model: for (d in device) for (dpi in

Re: [sqlite] Replicating table data from sqlite to ms sql server

The sqlite runs in a Linux environment and can be accessed through a web server. The ms sql server resides in a windows environment and has its own web server. Can this still be possible? It's more of like having a server to server communication. Is this programmatically possible? John Stanton

Re: [sqlite] Re: "wrong" index chosen, why?

Overview Of The Optimizer (1.0 says "column = expression" not "constant expression", 5.0 says "joins are nested loops") and the EXPLAIN QUERY PLAN output gave me this mental model: for (d in device) for (dpi in index(dpi1, 1, d.device_id, 300)) ... Did I

Re: [sqlite] FTS and upgrades

> > >DELETE sqlite_master WHERE type='f'; > > > > Will all the btree pages orphaned at rootpage for these rows > > not be recyclable until you run a full VACUUM? > > Yes - but the virtual fts1 table doesn't have any btree pages! Fair enough. I wasn't sure what was going on behind the scenes.

Re: [sqlite] Re: "wrong" index chosen, why?

--- Chris Eich <[EMAIL PROTECTED]> wrote: > I realize that but thought that device_id would be included because of the > d.device_id=dpi.device_id term. What am I missing? dpi.device_id is not constant. You'd be better off with this index for your query: CREATE INDEX dpi3 ON

Re: [sqlite] FTS and upgrades

On 7/10/07, Joe Wilson <[EMAIL PROTECTED]> wrote: --- [EMAIL PROTECTED] wrote: > "Scott Hess" <[EMAIL PROTECTED]> wrote: > > If you have an fts1 table f, you could drop f_term and f_content, but > > you won't be able to drop f itself. So you would have to name the > > fts2 version of f

Re: [sqlite] FTS and upgrades

--- [EMAIL PROTECTED] wrote: > "Scott Hess" <[EMAIL PROTECTED]> wrote: > > If you have an fts1 table f, you could drop f_term and f_content, but > > you won't be able to drop f itself. So you would have to name the > > fts2 version of f something else, like f2. > > > > I probably shouldn't tell

[sqlite] Re: Re: "wrong" index chosen, why?

Chris Eich <[EMAIL PROTECTED]> wrote: I realize that but thought that device_id would be included because of the d.device_id=dpi.device_id term. What am I missing? The query selects _all_ values of device_id from device_perf_interval, and for each tries to find a matching record in device.

Re: [sqlite] Re: "wrong" index chosen, why?

I realize that but thought that device_id would be included because of the d.device_id=dpi.device_id term. What am I missing? Chris On 7/10/07, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > and two indexes on the latter table: > > CREATE INDEX dpi1 ON device_perf_interval( >interval_type, >

[sqlite] Re: "wrong" index chosen, why?

Chris Eich <[EMAIL PROTECTED]> wrote: I have a database with the following tables: CREATE TABLE device ( device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, device_type INTEGER NOT NULL, -- lookup in device_type ... ); CREATE TABLE device_perf_interval ( [snip] ); and

[sqlite] "wrong" index chosen, why?

I have a database with the following tables: CREATE TABLE device ( device_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, device_type INTEGER NOT NULL, -- lookup in device_type ... ); CREATE TABLE device_perf_interval ( interval_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

Re: [sqlite] FTS and upgrades

"Scott Hess" <[EMAIL PROTECTED]> wrote: > If you have an fts1 table f, you could drop f_term and f_content, but > you won't be able to drop f itself. So you would have to name the > fts2 version of f something else, like f2. > I probably shouldn't tell you this, but There is a pragma:

Re: [sqlite] FTS and upgrades

If you have an fts1 table f, you could drop f_term and f_content, but you won't be able to drop f itself. So you would have to name the fts2 version of f something else, like f2. It might be reasonable to allow some sort of modified drop for such cases. Perhaps something like: DROP VIRTUAL

RE: [sqlite] FTS and upgrades

Even without having FTS1 loaded, can't you delete the *_content and *_term tables directly and that would be effectively the same as deleting the virtual table? Sam --- We're Hiring! Seeking a passionate developer to join our team building products.

Re: [sqlite] FTS and upgrades

Ok that's exactly what I was asking. Thanks! On 7/9/07, Scott Hess <[EMAIL PROTECTED]> wrote: If you have not compiled in fts1, and try to drop an fts1 table, you'll get an error. I don't think you'll get a crash, but sqlite will simply not know how to deal with the table. I can think of two

Re: [sqlite] sqlite3.c amalgamation > 64k lines

--- Ted Mielczarek <[EMAIL PROTECTED]> wrote: > While working on a Mozilla task related to debugging information, I > ran into a problem triggered by the fact that sqlite3.c was > 32k > lines. It turns out that the "stabs" debugging format uses a short > integer to store source line numbers. We

Re: [sqlite] How to implement connection pooling

We use connection pooling. It took only a few lines of C code to implement. In our case we maintain a global list of open connections and access them by pathname. A mutex is required to protect the list anchor pointer when a new connection is added. We also add pthread read/write locking

Re: [sqlite] storing floating point values in field

You seem to be looking at the output of the floating point to ASCII function in the Sqlite command line shell program, not the floating point number itself. folabi wrote: Thanks for the feedback Chris I ran the sqlite3.exe on the command line and got the same results as you did. I consume

Re: [sqlite] Replicating table data from sqlite to ms sql server

You could register a trigger and write a user function which reads the row you just inserted, updated or deleted and writes it to the othe DB. The user installed Sqlite function can inherit the open database connection as userdata. maitong uy wrote: Hello there, I'm new to sqlite and I was

[sqlite] sqlite3.c amalgamation > 64k lines

Hi, While working on a Mozilla task related to debugging information, I ran into a problem triggered by the fact that sqlite3.c was > 32k lines. It turns out that the "stabs" debugging format uses a short integer to store source line numbers. We fixed the issue by using an unsigned short, but

RE: [sqlite] Multiple connections - stale cache?

My final post on this issue...vxWorks has confirmed a bug in their fstat() implementation. I'm hoping for a quick resolution. Thanks for the help. Mark > > Just a follow-up for the group... > > Richard was able to determine that my platform is having > trouble with an > fstat() call to get

RE: [sqlite] How to implement connection pooling

If you're using a recent version of MONO then I assume you're using System.Data.SQLite from Robert Simpson which is now bundled with MONO, right? I use a custom version of the same provider which has connection pooling implemented in the wrapper. We've found it to be a huge performance

RE: [sqlite] Replicating table data from sqlite to ms sql server

Regarding: I'm new to sqlite and I was wondering if there is a way to programmatically replicate table data from a database in sqlite server to another database in ms sql server? I'm hoping to do this without any user intervention(the application will do it automatically). Is this possible?

[sqlite] SQLite and GreenHills Integrity

Hello, I am evaluating SQLite with GreenHills Integrity RTOS (ghs.com) running on an Atmel ARM9 Processor. SQLite builds sucessfully and appears to work fine. Unfortunatly there is no TCL support on this operating system so I can't run the test scripts. Has anyone tested SQLite under this

[sqlite] ANN: SQLite Maestro 7.7 released

Hi All, SQL Maestro Group is announcing the release of SQLite Maestro 7.7, a powerful Windows GUI tool for SQLite databases administration and development. The new version is immediately available at: http://www.sqlmaestro.com/products/sqlite/maestro/ What's new in version 7.7? 1. The most

[sqlite] How to implement connection pooling

HI , *How we use SQlite:* We use MONO and SQLite 3 for our project which runs on Linux. It is going to be used as an embedded database in a device. *What i want to do:* I want to use connection pooling either using some feature provided by SQLite or someone else *What i want to know:*

Re: [sqlite] storing floating point values in field

Thanks for the feedback Chris I ran the sqlite3.exe on the command line and got the same results as you did. I consume the sqlite library as a .lib file which i compiled from the source code. Do you know of any caveats i should be aware of when compiling the sqlite librarry as a static lib