Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

On Fri, Oct 25, 2013 at 6:54 PM, Mike Clagett wrote: > Yet the following oddity still remains: when we create the index > programmatically before adding the rows, it doesn't seem to have any > effect. Both interactively in the Firefox tool and programmatically from >

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

On 25 Oct 2013, at 11:54pm, Mike Clagett wrote: > Both interactively in the Firefox tool and programmatically from the second > phase of my application it takes about an average 18ms to do a select -- the > same amount of time without the index even being there at

Re: [sqlite] Text file import by column number

On Fri, 25 Oct 2013 12:11:42 -0700 "Joe.Fisher" wrote: > Is it possible to import data by column number without using any > delimiters? > > We have massive text based data files where each record of the data > is segmented and consistent (Col 1-3, 4-9, 10-12, etc.).

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

Hi -- Okay. I've finally gotten my execution time down from 30 minutes to 50 seconds in the preliminary one-time insertion-heavy scenario and from 20 minutes to 2 minutes in the subsequent read-heavy scenario.22 minutes of speedup in the first scenario was contributed by the indexing of

Re: [sqlite] Is there a function that return an autoimcremented value?

Igor, On Fri, Oct 25, 2013 at 3:26 PM, Igor Tandetnik wrote: > On 10/25/2013 5:53 PM, Igor Korot wrote: > >> If I do something like this: >> >> CREATE TRIGGER AFTER INSERT >> { >> SELECT max( current_rank ) AS a FROM leagueplayers WHERE leagueid = 1; >> UPDATE leagueplayers

Re: [sqlite] Is there a function that return an autoimcremented value?

On 10/25/2013 5:53 PM, Igor Korot wrote: If I do something like this: CREATE TRIGGER AFTER INSERT { SELECT max( current_rank ) AS a FROM leagueplayers WHERE leagueid = 1; UPDATE leagueplayers SET current_rank = a WHERE leagueid = 1 AND current_rank IS NULL; } Something along these lines might

Re: [sqlite] Is there a function that return an autoimcremented value?

Hi, guys, On Fri, Oct 25, 2013 at 6:19 AM, Igor Tandetnik wrote: > On 10/24/2013 10:48 PM, Igor Korot wrote: > >> INSERT INTO leagueplayers SELECT players.playerid, %d, ... ORDER BY >> players.rank; >> >> Now my language of choice is C++ and what I'm looking for is a way to

Re: [sqlite] Text file import by column number

On 25 Oct 2013, at 8:11pm, Joe.Fisher wrote: > Is it possible to import data by column number without using any delimiters? > > We have massive text based data files where each record of the data is > segmented and consistent (Col 1-3, 4-9, 10-12, etc.). > > I've

Re: [sqlite] Text file import by column number

On 10/25/2013 3:11 PM, Joe.Fisher wrote: Is it possible to import data by column number without using any delimiters? We have massive text based data files where each record of the data is segmented and consistent (Col 1-3, 4-9, 10-12, etc.). I've never had experience importing without some

[sqlite] Text file import by column number

Is it possible to import data by column number without using any delimiters? We have massive text based data files where each record of the data is segmented and consistent (Col 1-3, 4-9, 10-12, etc.). I've never had experience importing without some delimiter. Joe Fisher Oregon State

Re: [sqlite] Trigger with WHEN clause error

Thank you very much for the help, it has solved my issue and now I know what for 'old' and 'new' stand in triggers Sincerely Louis Jean-Richard s my problem On 25. 10. 13 20:19, Richard Hipp wrote: On Fri, Oct 25, 2013 at 2:10 PM, Louis Jean-Richard < l_jean_rich...@bluewin.ch> wrote:

Re: [sqlite] Trigger with WHEN clause error

On Fri, Oct 25, 2013 at 2:10 PM, Louis Jean-Richard < l_jean_rich...@bluewin.ch> wrote: > CREATE TRIGGER atable_updated AFTER UPDATE ON atable > FOR EACH ROW WHEN last_update != date('now') > I think you meant to say "old.last_update" instead of just "last_update". -- D. Richard Hipp

Re: [sqlite] Trigger with WHEN clause error

On 10/25/2013 2:10 PM, Louis Jean-Richard wrote: CREATE TRIGGER atable_updated AFTER UPDATE ON atable FOR EACH ROW WHEN last_update != date('now') You have to say which row last_update should come from: old.last_update or new.last_update -- Igor Tandetnik

[sqlite] Trigger with WHEN clause error

Hello, I stumbled on a problem with triggers that I have condensed into the following test script: -- bug.script CREATE TABLE atable (cutoff_date CHAR(10), last_update CHAR(10)) ; INSERT INTO atable(cutoff_date, last_update) VALUES('2013-10-11', '2013-10-11') ; SELECT * FROM atable;

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

On Fri, Oct 25, 2013 at 10:05 AM, Mike Clagett wrote: > I believe the code that I use to insert a record may somehow check to see > if it's already there first, which might explain why the index would have > improved performance. > Yes, that would definitely explain a

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

Hi -- I'm doublechecking my assertion. I removed the index generation from the code and regenerated the table and it does indeed seem to be taking 30 minutes. I believe the code that I use to insert a record may somehow check to see if it's already there first, which might explain why the

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

On Fri, Oct 25, 2013 at 9:27 AM, Mike Clagett wrote: > > We are seeing something strange, however, that I was wondering if you had > any thoughts on. Our program generates a schema programmatically at the > beginning of a run, if it isn't already there, and then

Re: [sqlite] Trying to figure out how to circumvent sqlite3_win32_mbcs_to_utf8

Hi -- Once again thank you for all your help. It was instrumental in helping identify the problem. Once I added an empty while loop that completed the processing of some key query results, the SELECT statements showed up in my profiling output and we immediately identified the one that was

Re: [sqlite] Is there a function that return an autoimcremented value?

On 10/24/2013 10:48 PM, Igor Korot wrote: INSERT INTO leagueplayers SELECT players.playerid, %d, ... ORDER BY players.rank; Now my language of choice is C++ and what I'm looking for is a way to populate the current_rank and original_rank in the leagueplayers table. They should come up as

Re: [sqlite] Is there a function that return an autoimcremented value?

Stephan Beal wrote: > On Fri, Oct 25, 2013 at 12:51 PM, Clemens Ladisch wrote: >> CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY); >> INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...; >> INSERT INTO leagueplayers(playerid, leagueid,

Re: [sqlite] Is there a function that return an autoimcremented value?

On Fri, Oct 25, 2013 at 12:51 PM, Clemens Ladisch wrote: > CREATE TEMP TABLE t(playerid, leagueid, auto_rank INTEGER PRIMARY KEY); > INSERT INTO t(playerid, leagueid) SELECT players.playerid, %d FROM ...; > INSERT INTO leagueplayers(playerid, leagueid, current_rank,

Re: [sqlite] Is there a function that return an autoimcremented value?

Igor Korot wrote: > On Fri, Oct 25, 2013 at 2:12 AM, Clemens Ladisch wrote: >> Igor Korot wrote: >>> what I'm looking for is a way to populate the current_rank and >>> original_rank in the leagueplayers table. They should come up as >>> auto-incremented values. >>> Is there a

Re: [sqlite] Is there a function that return an autoimcremented value?

Hi, Clemens, On Fri, Oct 25, 2013 at 2:12 AM, Clemens Ladisch wrote: > Igor Korot wrote: > > what I'm looking for is a way to populate the current_rank and > > original_rank in the leagueplayers table. They should come up as > > auto-incremented values. > > Autoincrementing

Re: [sqlite] Is there a function that return an autoimcremented value?

Igor Korot wrote: > what I'm looking for is a way to populate the current_rank and > original_rank in the leagueplayers table. They should come up as > auto-incremented values. Autoincrementing works only for INTEGER PRIMARY KEY columns. > Is there a way to do that or I will have to change the

Re: [sqlite] Is this a proper syntax?

Sorry, I forgot. I work almost exclusively with virtual tables... Gunter -Ursprüngliche Nachricht- Von: Igor Korot [mailto:ikoro...@gmail.com] Gesendet: Freitag, 25. Oktober 2013 09:57 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Is this a proper syntax? Hi, Hick,

Re: [sqlite] Is this a proper syntax?

Hi, Hick, On Fri, Oct 25, 2013 at 12:52 AM, Hick Gunter wrote: > How did you populate the first two fields of the leagueplayers relation? > > Since SQLite doesn't do ALTER TABLE ADD COLUMN there must have been an old > leagueplayers table without the new fields. > SQLite

Re: [sqlite] Is this a proper syntax?

How did you populate the first two fields of the leagueplayers relation? Since SQLite doesn't do ALTER TABLE ADD COLUMN there must have been an old leagueplayers table without the new fields. So why not INSERT INTO SELECT o.playerid,o.leagueid,p.rank,p.rank from o LEFT JOIN players p ON

Re: [sqlite] Is this a proper syntax?

On Thu, 24 Oct 2013 21:40:32 -0400, Igor Tandetnik wrote: >On 10/24/2013 9:17 PM, David Bicking wrote: >> But I think sqlite would notice that the subquery was the same in the >> UPDATE statement that Igor T gave and not run it twice > >I don't believe SQLite is quite that