Re: [sqlite] Order of operations when using TRIGGERs

On Oct 23, 2009, at 5:05 AM, Simon Slavin wrote: > I'm looking for assurance that things work the way I think they do, > and will continue to do so in future versions. > > I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT > commands do not know or set the value for the ROWID

Re: [sqlite] Order of operations when using TRIGGERs

On 23 Oct 2009, at 3:00am, D. Richard Hipp wrote: > On Oct 22, 2009, at 9:11 PM, Simon Slavin wrote: >> > >> So now my original query comes down to this: if a column's value is >> generated by SQLite rather than being supplied by my INSERT command, >> i.e. if the column has a is INTEGER PRIMARY

Re: [sqlite] Order of operations when using TRIGGERs

On Oct 22, 2009, at 9:11 PM, Simon Slavin wrote: > > So now my original query comes down to this: if a column's value is > generated by SQLite rather than being supplied by my INSERT command, > i.e. if the column has a is INTEGER PRIMARY KEY, or has a DEFAULT > value, can I rely on being able to

Re: [sqlite] Order of operations when using TRIGGERs

On 23 Oct 2009, at 1:23am, D. Richard Hipp wrote: > On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote: >> >> In SQLite, ROWID _is_ the unique identifier for each record. Any >> field declared INTEGER PRIMARY KEY is simply an alias for ROWID. >> "Position in the set", whatever it is, has nothing

Re: [sqlite] Inner Join Performance Issue

An example from my own data: explain query plan select * from categories where cat_name = ? order | from | detail -- 0 | 0| TABLE categories WITH INDEX sqlite_autoindex_categories_1

Re: [sqlite] Order of operations when using TRIGGERs

On Oct 22, 2009, at 7:11 PM, Igor Tandetnik wrote: > > In SQLite, ROWID _is_ the unique identifier for each record. Any > field declared INTEGER PRIMARY KEY is simply an alias for ROWID. > "Position in the set", whatever it is, has nothing to do with it. A clarification: The ROWID is always

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

On Mon, Oct 19, 2009 at 11:25 AM, John Crenshaw wrote: > On the other hand, we could add a tree inside each segment to index the > doclist. The term would be looked up as normal at a cost of O(log nT). > After that though, if the docid is known, it could be looked up at

Re: [sqlite] Order of operations when using TRIGGERs

Rich Shepard wrote: > On Thu, 22 Oct 2009, Simon Slavin wrote: > >> I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT >> commands do not know or set the value for the ROWID column. Can I >> reliably fetch the value for this column from 'NEW.' ? Or can I

Re: [sqlite] Order of operations when using TRIGGERs

On Thu, 22 Oct 2009, Simon Slavin wrote: > I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT commands > do not know or set the value for the ROWID column. Can I reliably fetch > the value for this column from 'NEW.' ? Or can I rely only on values > which are explicitly set in the

[sqlite] Order of operations when using TRIGGERs

I'm looking for assurance that things work the way I think they do, and will continue to do so in future versions. I'm using a TRIGGER that is triggered AFTER INSERT. The INSERT commands do not know or set the value for the ROWID column. Can I reliably fetch the value for this column from

Re: [sqlite] Inner Join Performance Issue

On 22 Oct 2009, at 9:55pm, Ralf wrote: > I just read, that with an operator "like '%a%'" SQLite won't use an > Index. > Is this the case? The '%' at the beginning of that operand indicates that the first character of the string can be anything. An index is of no use if you don't know

Re: [sqlite] Inner Join Performance Issue

I just ran EXPLAIN, how can I tell if the Indexes are used? I just read, that with an operator "like '%a%'" SQLite won't use an Index. Is this the case? Thanks Ralf > -Ursprüngliche Nachricht- > Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] Im Auftrag

Re: [sqlite] commit time

thanks for the discussion. i'll keep my eyes open for lock contention. i'm going to start w/ the simple approach first and see how it goes. thanks tom -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Crenshaw Sent:

Re: [sqlite] Detecting real updates

On 22 Oct 2009, at 3:51pm, Marian Aldenhoevel wrote: > Is there a way to have sqlite3 detect the actual changes after I did a > number of INSERT OR UPDATE statements? Using a trigger maybe? > > If so I could keep the intermediate storage nicely organized and still > not incur a lot of

Re: [sqlite] Spam filtering. Was: Data migration tool certification

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 D. Richard Hipp wrote: > Is there a > different mailing list manager we should consider switching to? What I have found most effective is having the ability to moderate the first posts from new users. That way you don't care who joins but do get to

Re: [sqlite] Spam filtering. Was: Data migration tool certification

On Thu, 22 Oct 2009, D. Richard Hipp wrote: > To: General Discussion of SQLite Database > From: D. Richard Hipp > Subject: [sqlite] Spam filtering. Was: Data migration tool certification > > > On Oct 22, 2009, at 10:16 AM, hfdabler wrote: >> >> I have

Re: [sqlite] SQLite DB Structure

Firstly I'm not an SQLite expert but, now you know that, I would guess SQLite marks deleted records waiting until some clean-up process removes them and re-writes the active data. I would therefore look for some bit that is set. Try setting up a new database, adding records - taking a copy, then

Re: [sqlite] .import on a .csv file

On Thu, 22 Oct 2009, Simon Davies wrote: > One solution is to replace your existing separators (,) with a character > that does not exist in your data, specify that character as the separator > to sqlite, and you should be good to go .import. I found the solution was to change all field

Re: [sqlite] .import on a .csv file

2009/10/22 Scott Baker : > I'm trying to .import a CSV file and I can't quite figure out the syntax. > > I created a table, and then did: > > .separator , > .import /tmp/foo.csv mytable > > This works sort of, unless my data has , in it. Something like "last, > first". Because

Re: [sqlite] .import on a .csv file

Do you have any new lines, returns, or tabs in any of the real data? Can you prove it? Is this a 1 off thing or are you going to do this routinely? There has been a lot of discussion on this list about importing csv data and the hardships of writing a good csv importer. If this is a one off,

[sqlite] .import on a .csv file

I'm trying to .import a CSV file and I can't quite figure out the syntax. I created a table, and then did: .separator , .import /tmp/foo.csv mytable This works sort of, unless my data has , in it. Something like "last, first". Because it tries to split at that , and then the number of rows

Re: [sqlite] manipulating arguments (in C)

Hi Rob, >Perhaps this might lead you in the right direction Jean-Christophe ... > >#include Thank you for your answer. The va_* construct isn't portable, AFAIK. Various compilers (headers, libraries) may (and did) implement it in different and potentially incompatible ways. Since in my

Re: [sqlite] Problem about write data into the DB

The problem has solved, it's actually the permission configuration issue about write data to the table. the apache hasn't the permission to write data into the table, I modified the rights, then everything is OK now. Thanks! 2009/10/21 邓超 > Hi sqlite-users, > I deployed a

[sqlite] Detecting real updates

Hi, My application is downloading data from the internet, parses and transforms it and (currently) stores it in a sqlite3-database. The data parses out into a small variable number of records with just two fields each. Whenever that data changes, either the number of records or the actual

Re: [sqlite] manipulating arguments (in C)

Perhaps this might lead you in the right direction Jean-Christophe ... #include #include #include Str_t s_format( Str_t fmt, ... ){ va_list ap ; Int_tnx ; Byt_tbuf[1] ; Str_tqptr ; va_start( ap, fmt ); nx = vsnprintf( buf, 0, fmt, ap ) ; va_end( ap ) ;

[sqlite] Spam filtering. Was: Data migration tool certification

On Oct 22, 2009, at 10:16 AM, hfdabler wrote: > > I have been using the Talend software for a few months now ... Needless to say, "hfdabler" has been permanently banned from this list. Not that that matters any since "hfdabler" is likely a throw- away email address and he had already

[sqlite] Data migration tool certification

Hello, I have been using the Talend software for a few months now and am very happy. I have seen the page on the Talend certification and the exam on the website ( http://www.talend.com/partners/index.php ). I wonder what it takes to take the exam, if you need to know well Talend. Also is the

Re: [sqlite] Rollback over pending queries

--- edz...@volcanomail.com wrote: > I wonder if an automatic rollback, as described in > //www.sqlite.org/c3ref/busy_handler.html, is more powerful than a > rollback programmed in SQL. Particularly if it is able to rollback > pending queries from other cursors in the same connection. The >

Re: [sqlite] commit time

Dangerous and disturbing this puzzle is. Only a bug could have locked those connections. If I discover anything useful I'll report it separately (no need to hijack this topic for that.) John -Original Message- From: sqlite-users-boun...@sqlite.org