RE: [sqlite] Date arithmetic question
Writing, adding and using your own functions within SQLite is pretty
easy. That's probably your best bet to solve this problem.
-T
> -Original Message-
> From: Fowler, Jeff [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 17, 2008 11:59 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Date arithmetic question
>
> Guys,
>
> I guess I'm the newest SQLite person on this email list and I
> know I'm definitely the dumbest. It seems like a lot of you
> are trying to justify why two dates that are one minute apart
> can have a function say they're one month apart. Don't look
> at it that way. Back when mainframes and dinosaurs ruled the
> world I used to be a fairly decent programmer, now I run a
> small company, so today I'm coming from a business standpoint
> not a programmer one.
>
> To give some background, we're in the process of embedding
> SQLite into our application, and yes it's an awesome product.
> Our software sits on top of huge data warehouses (hundreds of
> millions of rows) which are typically either Oracle or SQL
> Server. We run queries against these databases and store the
> result sets in SQLite, where we run subsequent queries to
> filter & format the output.
>
> A huge number of businesses are date driven. Publishers want
> to renew subscriptions before they expire. Insurance
> companies need to renew policies. Our largest client sells
> service contracts which have a start & end date. So it's an
> everyday occurrence for a manager to want to know how many
> customers will expire within the next three months, or what
> an average contract length is in months.
>
> My request was for a new date function that returns the
> difference, or "calendar interval" if you prefer, between two
> dates. Without such a function we must say:
> WHERE (strftime('%Y', LaborEndDate)*12+strftime('%m',
> LaborEndDate)) - (strftime('%Y',
> LaborStartDate)*12+strftime('%m', LaborStartDate)) < 3
>
> Wow. This is quite a mouthfull for something that's so
> commonly needed, and it's harder to generate SQL
> automatically when a user clicks a checkbox. Clearly it's far
> simpler (and easier to program) if we could say:
> WHERE datediff('month', LaborEndDate, LaborStartDate) < 3
>
> Datediff also supports years, weeks, days, hours, seconds,
> and milliseconds. It's just a quick & easy way to make life
> easier. But.. of course I know the SQLite team can't slap in
> every enhancement that somebody suggests; so I just wanted to
> explain why this would be useful for some of us. We do have a
> vested interest in the product!
>
> - Jeff
>
>
>
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Thu 1/17/2008 9:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Date arithmetic question
>
>
>
> Gerry Snyder <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > >
> > > So datediff('month', '2008-02-01 23:59:59','2008-01-31
> 00:00:00') should
> > > return 1 even though the difference is really only 1
> second? Seems
> > > goofy to me
> > >
> > >
> >
> > I have been staring at this until I'm getting goofy.
> >
> > Written as it is, isn't the time interval 1 second short of
> two days?
> >
> > If you want an interval of 1 second shouldn't it be
> >
> > datediff('month', '2008-02-01 00:00:00','2008-01-31 23:59:59')
> >
> > ?
> >
> >
> > Gerry, more confused than usual (as usual)
> >
>
> Yeah. I got it backwards. Sorry.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>
>
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Re: SQLite and Columnar Databases
Ahh yes, the obvious answer. Duh. Thanks. :) > -Original Message- > From: Trevor Talbot [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 18, 2007 8:27 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Re: SQLite and Columnar Databases > > On 12/18/07, Tom Briggs <[EMAIL PROTECTED]> wrote: > > >This implies that there are databases that provide > multiple storage > > mechanisms and allow users to choose between the options. > Does such a > > database exist? > > It may not be exactly what you had in mind, but see MySQL: > http://dev.mysql.com/doc/refman/5.1/en/storage-engine-overview.html > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: SQLite and Columnar Databases
> If a DBMS is smart enough, it can automatically pick the best storage > method for performance and you don't have to think about it. > > However, many DBMS are not that smart and so typically users find > themselves making explicit changes to their schemas, specifying the > storage method explicitly, in order to compensate and/or give the > DBMS hints. This implies that there are databases that provide multiple storage mechanisms and allow users to choose between the options. Does such a database exist? Or am I being too literal? -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: Re[2]: [sqlite] Improving performance of SQLite. Anyone heard of DeviceSQL?
> As my father was fond of saying; "Money talks and BS walks." Unfortunately, that rule is decidedly invalid in the commercial software world. Bear in mind that DRH doesn't sell software, which is part of the reason why he and Encirq go about doing things differently. -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
> This model is completely removed from how the data is physically > stored, eg whether in rows first or in columns first, and the > physical store is determined just by the DBMS behind the scenes, and > hence is an implementation detail. The DBMS can arrange how it likes > in order to satisfy the logical model in a way that performs well. > The users do not contort their schemas beyond what is logically clean > in order to gain performance; having a clean schema will let the DBMS > infer this automatically. This may be true in theory, but if it were really true in practice, why would anyone bother building databases with different storage models? How a database does what it does impacts everything from where you should use it to how you design your schema to how you write your queries. And if you think that no one contorts their schemas to make their queries run faster then, umm... Well, I don't think I need to say anything else about that. > According to some comments, Vertica (a column-store maker) is making > the same case that I am, which is just to have a logical clean > schema, and performance benefits will automatically follow from that. They say that because their system performs better when your schema is simpler. It's not just a philosophical belief that simpler is better. > I will also note that a column-based store essentially works like a > heavily indexed row-based store, in which there is an index on every > key or every column, and so all searches, which includes those on > which joins are performed, can/do look in what is otherwise indexes. Conceptually I agree, though I do not think you could build an equally-effective system by indexing a row store. > This is potentially slower for updates (or maybe not), but can be > faster for queries, depending on circumstances. But... But... How the data is stored is an implementation detail that users don't need to worry about, right? So who cares if updates are slower? The logical model is the same, right? Thank you for making my point. :) -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
Heh, no, I've never tried, but I don't see much reason why I couldn't. I was just trying to make the point that labeling SQLite as "good ... for smaller databases" was not a slight. -T > -Original Message- > From: Joe Wilson [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 13, 2007 11:51 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLite and Columnar Databases > > --- Tom Briggs <[EMAIL PROTECTED]> wrote: > >For clarity, my definition of small is about 200GB, so I'm not > > selling SQLite short here... > > Are you able to get decent performance out of sqlite3 for a > 200GB database? > > How much RAM do you have on such a machine? > > > > __ > __ > Looking for last minute shopping deals? > Find them fast with Yahoo! Search. > http://tools.search.yahoo.com/newsearch/category.php?category=shopping > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
Based on my experience with SQLite, it would be a huge undertaking to re-work it to use column-oriented storage. And I don't think it would really fit with SQLite's goal, either; column oriented databases are best suited to aggregate queries against large amounts of data, while SQLite is best at transactional operations against smaller amounts of data. For clarity, my definition of small is about 200GB, so I'm not selling SQLite short here... Don't everyone get all upset at me. :) -T > -Original Message- > From: Yuvaraj Athur Raghuvir [mailto:[EMAIL PROTECTED] > Sent: Wednesday, December 12, 2007 6:12 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite and Columnar Databases > > Hello, > > There seems to be a high interest in columnar databases recently. > > Is there any plan of supporting data organization as a > columnar database in > SQLite? What are the challenges here? > > Regards, > Yuva > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite and Columnar Databases
> Something I will say about this, for people who don't know, is that > this columnar thing is strictly an implementation detail. While I think that this is an oversimplification. That's somewhat like saying that the way you use a sledge hammer is no different than how you use a claw hammer, because they're both hammers. Anyone who tries to hang a picture with a sledge hammer will be rather unhappy with your advice. Though at some level how the data is stored is indeed an implementation detail, to take full advantage of the fact that it is requires re-thinking schema design and in some cases even query design. See http://www.fulltablescan.com/index.php?/archives/44-Compression-as-Norma lization...-Kinda.html for more info. -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance tuning using PRAGMA, other methods
> BTW, several PRAGMAS actually increase performance in my embedded app > case - maybe 15-30% depending upon transaction activity and the way I > structure transaction commits. Specific PRAGMAS that helped include: This is exactly what irritates me about conversations like this - the pragmas you cite as improving performance for you, in your unspecified situation, do so "depending upon" other things you do. Even by your own example changing pragmas alone isn't enough. Nor do you provide enough information for someone else to know whether your settings would be helpful in their situation. In the words of a local talk show host... You're making my point! -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance tuning using PRAGMA, other methods
re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think that your question is too broad to be answerable (unless you're actually attempting to assemble a collection of optimal values in all possible situations for all existing pragmas... ;shrug) re: Q2 - At the risk of sounding crass, tuning queries is, has been and always will be the best way to optimize the performance of any database. I've done a lot of tuning of SQLite and a half dozen other databases, and query design is always what has the most impact. Pragmas, #defines, API usage, etc. are always a distant second in the race for performance gains. -T > -Original Message- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 4:24 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > To the point, the questions are: > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how were they > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > methods of any type would be appreciated. > > > > > > -Original Message- > From: Tom Briggs [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 10:40 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > >Which pragmas will be most effective (and what values you > should use > for each) depends on what you're trying to do with the database. > Synchronous is important if you're writing frequently, for > example, but > won't matter much in a read-only setting. Appropriate values for the > page_size and cache_size pragmas vary depending on whether > the database > is write-mostly or read-mostly and also depending on whether > you want to > optimize for reading or writing. > >So in short, the answer is, it depends. Depends on what you're > trying to tune for, that is. > >-T > > > -Original Message- > > From: Scott Krig [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 1:13 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > > > > What are the 'biggest bang for the buck' sqlite optimization > > techniques > > to apply to a working system to tune performance? > > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > > sqlite performance using PRAGMA's? If so, which ones, how were they > > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > > methods of any type would be appreciated. > > > > Thanks. > > > > > > Scott > > -=- > > > > > > > > Here is a list of the PRAGMA examples from the sqlite documentation: > > > > > > > > PRAGMA auto_vacuum; > > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental; > > > > PRAGMA cache_size; > > PRAGMA cache_size = Number-of-pages; > > > > PRAGMA case_sensitive_like; > > PRAGMA case_sensitive_like = 0 | 1; > > > > PRAGMA count_changes; > > PRAGMA count_changes = 0 | 1; > > > > PRAGMA default_cache_size; > > PRAGMA default_cache_size = Number-of-pages; > > > > PRAGMA default_synchronous; > > > > PRAGMA empty_result_callbacks; > > PRAGMA empty_result_callbacks = 0 | 1; > > > > PRAGMA encoding; > > PRAGMA encoding = "UTF-8"; > > PRAGMA encoding = "UTF-16"; > > PRAGMA encoding = "UTF-16le"; > > PRAGMA encoding = "UTF-16be"; > > > > PRAGMA full_column_names; > > PRAGMA full_column_names = 0 | 1; > > > > PRAGMA fullfsync > > PRAGMA fullfsync = 0 | 1; > > > > PRAGMA incremental_vacuum(N); > > > > PRAGMA legacy_file_format; > > PRAGMA legacy_file_format = ON | OFF > > > > PRAGMA locking_mode; > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > PRAGMA page_size; > > PRAGMA page_size = bytes; > > > > PRAGMA max_page_count; > > PRAGMA max_page_count = N; > > > > PRAGMA read_uncommitted; > > PRAGMA read_uncommitted = 0 | 1; > > > > PRAGMA short_column_names; > > PRAGMA short_column_names = 0 | 1; > > > > PRAGMA synchronous; > > PRAGMA synchronous = FULL; (2) > > PR
RE: [sqlite] Performance tuning using PRAGMA, other methods
Which pragmas will be most effective (and what values you should use for each) depends on what you're trying to do with the database. Synchronous is important if you're writing frequently, for example, but won't matter much in a read-only setting. Appropriate values for the page_size and cache_size pragmas vary depending on whether the database is write-mostly or read-mostly and also depending on whether you want to optimize for reading or writing. So in short, the answer is, it depends. Depends on what you're trying to tune for, that is. -T > -Original Message- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 1:13 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > What are the 'biggest bang for the buck' sqlite optimization > techniques > to apply to a working system to tune performance? > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how were they > used, and what was the performance increase? > Q2)) Other techniques: Any success stories on sqlite optimization > methods of any type would be appreciated. > > Thanks. > > > Scott > -=- > > > > Here is a list of the PRAGMA examples from the sqlite documentation: > > > > PRAGMA auto_vacuum; > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental; > > PRAGMA cache_size; > PRAGMA cache_size = Number-of-pages; > > PRAGMA case_sensitive_like; > PRAGMA case_sensitive_like = 0 | 1; > > PRAGMA count_changes; > PRAGMA count_changes = 0 | 1; > > PRAGMA default_cache_size; > PRAGMA default_cache_size = Number-of-pages; > > PRAGMA default_synchronous; > > PRAGMA empty_result_callbacks; > PRAGMA empty_result_callbacks = 0 | 1; > > PRAGMA encoding; > PRAGMA encoding = "UTF-8"; > PRAGMA encoding = "UTF-16"; > PRAGMA encoding = "UTF-16le"; > PRAGMA encoding = "UTF-16be"; > > PRAGMA full_column_names; > PRAGMA full_column_names = 0 | 1; > > PRAGMA fullfsync > PRAGMA fullfsync = 0 | 1; > > PRAGMA incremental_vacuum(N); > > PRAGMA legacy_file_format; > PRAGMA legacy_file_format = ON | OFF > > PRAGMA locking_mode; > PRAGMA locking_mode = NORMAL | EXCLUSIVE > PRAGMA main.locking_mode=EXCLUSIVE; > > PRAGMA page_size; > PRAGMA page_size = bytes; > > PRAGMA max_page_count; > PRAGMA max_page_count = N; > > PRAGMA read_uncommitted; > PRAGMA read_uncommitted = 0 | 1; > > PRAGMA short_column_names; > PRAGMA short_column_names = 0 | 1; > > PRAGMA synchronous; > PRAGMA synchronous = FULL; (2) > PRAGMA synchronous = NORMAL; (1) > PRAGMA synchronous = OFF; (0) > > PRAGMA temp_store; > PRAGMA temp_store = DEFAULT; (0) > PRAGMA temp_store = FILE; (1) > PRAGMA temp_store = MEMORY; (2) > > PRAGMA temp_store_directory; > PRAGMA temp_store_directory = 'directory-name'; > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL approach to Import only new items, delete other items
> Because my concern is this, I don't know how SQLite will do > > Delete from table where col not in (select from large temp dataset) > > How the delete will actually be walked, if it will create a serverside > cursor and walk the values in the in statement then it will > be fine and > fast, > If however it loads all the values into memory and then walk > the dataset > it would require a large amount of memory, I believe that it will write the results of the subquery to a temporary table, so I doubt that memory usage will be a problem. I'm only about 98% sure though, so no guarantees. :) EXPLAIN will tell you for sure though. > Your suggestion to drop the table and recreate from temp > although a good > idea will probably modify the database which means that if I version > control it, it will create a large amount of changes each > time an import > is run, even though nothing might have been changed. I think you have issues here either way though - one way or another you need to know that nothing changed. You may be able to determine that for free, depending on your approach, or you may need to go out of your way to determine that, it depends on your approach. But I wouldn't assume that you can know this for free and that any solution that requires work to know that is inherently bad. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL approach to Import only new items, delete other items
Your suggested temp table approach is how I would solve this; if everything is properly indexed it won't be too bad. Even if it is bad, it'll be better than updating columns within the table and then deleting rows based on that. Another potential alternative is to: 1. Load all new rows into a temp table 2. Select the old matching rows into a second temp table 3. Insert all the remaining new rows to that second temp table 4. Drop the original table and rename the second temp table That's likely to be slower on small data sets and faster on larger datasets, I think. Depends on how much data is already in the database vs. the amount of data being loaded. -Tom > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 06, 2007 5:41 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQL approach to Import only new items, > delete other items > > Im importing data > > The data has a unique value, call it MD5 for now that could > be a unique > value for the data. > > > > Each record that gets imported is converted to MD5, a lookup > is done on > the table for that MD5, > > if found it must leave it alone, if not found it must insert a new > record... > > > > All the items in the table that was not imported must be deleted... > > > > The only feasible approach I have is to add a column to the > table, like > UPDATE_FLAG for example, > > During the import update_flag gets set to 0, > > Once a record is found update_flag gets set to 1, > > > > At the end of the import all records with update_flag = 0 gets > deleted... > > > > However I did not want to add a column to the table, REASON being, I'm > also version controlling the DB, and when an import occurs and nothing > > Has been added or removed, I don't want modifications to the > DB, as the > import can run many times over. > > > > I was considering the following: > > Create a temp table call it, > > Temp_ids for example > > Then insert into the temp table for each record that was found... > > > > At the end do something like > > Delete from imports where import_id not in (select id from temp_ids) > > > > But that might be horribly slow and memory expensive remembering that > the import table may have millions of records.. > > > > What could the people here suggest to me, > > > > Thanks. > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] (select *) VS (select column1, column2 ...)
In general, it's best to only include the columns you need in the
SELECT clause. And not just with SQLite - that's the best approach when
dealing with any database. SQLite is a bit more forgiving because
there's no network between the client and the database to slow things
down, but that's still a good rule to follow.
In the particular example you cited, I think that the difference
would be so minimal as to be unnoticeable. But there will definitely be
a difference - the sqlite3_prepare call will make it possible to
retrieve any of the 40 columns if you do "select *", while it will only
make available the three you name if you use "select col1, col2, col3".
It can't know what you're going to do after the query is executed, so it
has to prepare for any possibility.
So, yes, there's a difference. Yes, selecting only the columns you
need is more efficient. No, I don't think you'll notice much of a
difference in terms of performance.
-T
> -Original Message-
> From: B V, Phanisekhar [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 31, 2007 7:33 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] (select *) VS (select column1, column2 ...)
>
> Assume I have a table with 40 columns. I would like to know the
> difference between
>
>
>
> Select * from table
>
> Select column1, column2, column3 from table
>
>
>
> While doing SQLITE3_PREPARE, will both take same amount of time?
>
> While doing SQLITE3_STEP, will both take same amount of time?
>
> --
> --
> ---
>
> sqlite3_prepare("Select * from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
> sqlite3_prepare("Select column1, column2, column3 from table");
>
> while(1)
>
> {
>
> iRet = sqlite3_step(pStmt);
>
> if(iRet != SQLITE_ROW)
>
> {
>
> iRet = sqlite3_finalize(pStmt);
>
> break;
>
> }
>
> Sqlite3_column_int(pStmt, column1);
>
> Sqlite3_column_int(pStmt, column2);
>
> Sqlite3_column_int(pStmt, column3);
>
> }
>
> --
> --
> ---
>
>
>
> If I want to extract just the 3 columns (column1, column2,
> column3), and
> use select* from table as sql query, how much impact it will have?
>
>
>
> Why I want to do this is because in some cases I need some particular
> combination in another any other combination of columns to be
> extracted?
> (It's possible for me to do this using "select * from table" but it's
> not possible if I used "select column1, column2, column3 from
> table" as
> I will have to frame another query)
>
>
>
> NOTE: Please don't look at the syntax of sqlite3_prepare I just wrote
> the code to show what I want to do.
>
>
>
> Regards,
>
> Phani
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Update Columns in One Table Using Values From Another Table
Hrmm... I wonder if this would work (complete guess, totally untested) INSERT OR REPLACE INTO core SELECT Core.A, Updates.B, Core.C, Updates.D FROM Core INNER JOIN Updates ON (Core.A = Updates.A) Idea being, I guess, to get the rows that you ultimately want from the sub-select and then use insert or replace to get them into the table. -T > -Original Message- > From: Chris Peachment [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 23, 2007 2:15 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Update Columns in One Table Using > Values From Another Table > > On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: > > >Hi Chris, > > >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: > > >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: > >> > >>>Chris Peachment wrote: > I have a database with more than 200,000 records in the > core table. An update table of similar record count contains > a proper subset of the core table columns. > > I'm looking for a fast method of merging the values in the > two tables such that : > > 1. core table columns are updated, and > 2. non-existent core records are inserted from the update table. > > >>>Will INSERT OR REPLACE do what you want? > >> > >> > >>>Gerry > >> > >> > >>Regrettably no. When an existing core record is found then it > >>is deleted before the insert. That means that all columns are > >>given new values and not just the ones to be updated. > > >That is exactly what INSERT OR REPLACE does. > > >http://www.sqlite.org/lang_insert.html > >http://www.sqlite.org/lang_conflict.html > > > Sorry for the confusion I introduced. I know the behaviour > of INSERT OR REPLACE is as-described, and that is NOT > what I want. I need to keep the non-updated columns. > > Chris > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] like operator
I'm not sure I correctly understand your question, but: escaping the % in your query may be what you're looking for, i.e. delete from table where itemName like '\%.%' escape '\' Maybe. :) -Tom > -Original Message- > From: RaghavendraK 70574 [mailto:[EMAIL PROTECTED] > Sent: Thursday, August 16, 2007 9:03 PM > To: SQLite > Subject: [sqlite] like operator > > Hi, > > we have given a web interface which receive delete request. > Now in the req we get "%" and in the delete impl we do this > delete from table where itemName like xxx.%; > > since the key is % the above statement becomes, > "delete from table where itemName like %.%";And result in > fatal problem of erasing all records. > > Is there any api to deal with like operator for these > conditions, pls help. Hopefully fix will not > degrade performance. > > regrds > ragha > > > ** > > This email and its attachments contain confidential > information from HUAWEI, which is intended only for the > person or entity whose address is listed above. Any use of > the information contained herein in any way (including, but > not limited to, total or partial disclosure, reproduction, or > dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in > error, please notify the sender by phone or email immediately > and delete it! > > ** > *** > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] UNION?
> Also, you may want to consider avoiding performing an IN on a UNION. > As far as I know, SQLite doesn't optimize that, so will build the > entire union before performing the IN. If you instead do the > following, it should be a lot faster (if you have lots of data). But > I may be wrong. Err... I think the sub-query in an IN clause has to be executed before the outer query can be started, so whether you do two sub-queries and UNION them or two sub-queries and separately check the results wouldn't seem to make much difference to me. Either way, both have to be executed before anything else can be done. -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Delete all other distinct rows
I don't think this would be very scalable, but you could do something like: DELETE FROM table WHERE ROWID NOT IN ( SELECT MIN(ROWID) FROM table GROUP BY NAME ) This is totally untested, BTW - just a thought. :) -Tom > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 08, 2007 5:30 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Delete all other distinct rows > > How to delete all other distinct rows except first one. > > > > If I have a table with rows > > > > ID, NAME > > > > 1, SOME NAME > > 2, SOME NAME > > 3, SOME NAME > > 4, ANOTHER NAME > > 5, ANOTHER NAME > > > > > > The delete should work even if you don't know what the value > of name is, > so simply for anything that is duplicate. > > > > The distinct delete should delete rows 2, 3, 5 and just keep > 1 and 4, is > there a single SQL statement that can achieve this? > > > > Thanks > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Problem: Can't See Tables in Database
Are you linking SQLite dynamically or statically? If dynamically,
check that it's actually loading the correct version of the shared
library - there may be an older/different version of SQLite installed on
your system that is getting loaded at run-time.
-Tom
> -Original Message-
> From: Lee Crain [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 03, 2007 12:25 PM
> To: sqlite-users@sqlite.org
> Cc: [EMAIL PROTECTED]
> Subject: RE: [sqlite] Problem: Can't See Tables in Database
>
> Tom Briggs,
>
>
>
> Thank you for your response. I attempted to send you a
> response complete
> with screenshots to eliminate any ambiguity but it was returned unsent
> because it was too large for your mail daemon.
>
>
>
> I have taken care to make certain that the "sqlite3.exe"
> executable, all
> databases (highlighted in red), and my import files (*.txt)
> are all in the
> same directory to avoid path issues. Please see the screenshot below:
>
>
>
>
>
> DELETED
>
>
>
>
>
> --
>
>
>
> I've created a very simple database named "DBm" with one
> table "t" and 2
> fields, a varchar(10) and a smallint. Please see the screenshot below:
>
>
>
> DELETED - the screenshot showed the following:
>
>
>
> D:\DATA\SQLite>sqlite3 DBm
>
> SQLite version 3.3.17
>
> Enter ".help" for instructions
>
> sqlite> .tables
>
> t
>
> sqlite> .schema
>
> CREATE TABLE t( one varchar( 10 ), two smallint );
>
> sqlite>
>
>
>
>
>
> --
>
>
>
>
>
> Then, using the examples in the SQLite documentation, I ran
> the example
> code:
>
>
>
>
>
>
>
> int main( )
>
> {
>
> sqlite3 *db;
>
> char *zErrMsg = 0;
>
> int rc;
>
>
>
> // Test Open
>
> rc = sqlite3_open( "DBm", & db );
>
> if( rc )
>
> {
>
> assert( false );
>
> fprintf( stderr, "Can't open database: %s\n",
> sqlite3_errmsg(
> db );
>
> sqlite3_close( db );
>
> Sleep( 5000 );
>
> return( 1 );
>
> }
>
>
>
> // Test SELECT
>
> rc = sqlite3_exec( db, "SELECT * from t", callback, 0,
> );
>
> if( rc!=SQLITE_OK )
>
> {
>
> fprintf( stderr, "SQL error: %s\n", zErrMsg );
>
> Sleep( 15000 );
>
> sqlite3_free( zErrMsg );
>
> return( 1 );
>
> }
>
>
>
>
>
>
>
> The failure occurs on the sqlite3_exec( ) call.
>
>
>
>
>
> --
>
>
>
>
>
> And I receive this message (see screenshot):
>
>
>
>
>
> DELETED
>
>
>
> "SQL error: no such table: t"
>
>
>
>
>
> --
>
>
>
> What I'm attempting to do is about as vanilla an application as I can
> think of.
>
>
>
> I am open to any other suggestions as to what the problem
> might be. I hope
> you are correct that the problem will turn out to be
> something very small.
>
>
>
> Sincerely,
>
>
>
> Lee Crain
>
> Senior Software Engineer
>
> DAZ 3D Productions
>
> 801-495-1777, x759
>
> [EMAIL PROTECTED]
>
>
>
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Problem: Can't See Tables in Database
Are the database files you're trying to open in the same directory as
the executable? What happens if you path the full path and file name to
sqlite3_open? What is the full command line used to start the sqlite3
command prompt?
The problem will turn out to be something very simple... Think small.
:)
-Tom
> -Original Message-
> From: Lee Crain [mailto:[EMAIL PROTECTED]
> Sent: Monday, July 02, 2007 5:47 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Problem: Can't See Tables in Database
>
> I have 2 databases created and populated:
>
> > DBLee, my test database
>
> > MiniMain, a subset copy of one of our production databases
>
>
>
> From the sqlite3 command prompt, I can run queries against
> both databases
> and see the results. I can also enter the ".tables" command
> and see the
> correct list of tables in each database.
>
>
>
> DBLee
>
> sqlite> .tables
>
> tb12 tb22
>
> sqlite>
>
>
>
>
>
> MiniMain
>
> sqlite> .tables
>
> Categories InstallerFiles Items RequiredItems
>
> ContentFilesItemCategories RelatedItems
>
> sqlite>
>
>
>
> _
>
>
>
> I have written a small test program in C++ to perform proof
> of concepts.
> The code is failing to execute any query against the MiniMain database
> with the error message "SQL error: no such table:
> Categories". Categories
> is one of 7 tables. The same failure results for all 7 tables
> and for all
> simple queries ("Select.").
>
>
>
> This is my source code:
>
>
>
> int main( )
>
> {
>
> sqlite3 *db;
>
> char *zErrMsg = 0;
>
> int rc;
>
>
>
> // Test Open
>
> //rc = sqlite3_open( "DBLee", & db );
>
> rc = sqlite3_open( "MiniMain", & db );
>
> if( rc )
>
> {
>
> assert( false );
>
> fprintf( stderr, "Can't open database: %s\n",
> sqlite3_errmsg(
> db ) );
>
> sqlite3_close( db );
>
> Sleep( 5000 );
>
> return( 1 );
>
> }
>
>
>
> // Test SELECT
>
> //rc = sqlite3_exec( db, "SELECT * from tb12", callback,
> 0,
> );
>
> rc = sqlite3_exec( db, "SELECT * from Categories", callback, 0,
> );
>
> if( rc!=SQLITE_OK )
>
> {
>
> assert( false );
>
> fprintf( stderr, "SQL error: %s\n", zErrMsg );
>
> Sleep( 15000 );
>
> sqlite3_free( zErrMsg );
>
> return( 1 );
>
> }
>
>
>
>
>
>
>
> The commented out lines execute correctly. The "SELECT * FROM
> Categories"
> line always fails.
>
>
>
> What could be the problem?
>
>
>
> Thanks,
>
>
>
> Lee Crain
>
> Senior Software Engineer
>
> DAZ 3D Productions
>
> 801-495-1777, x759
>
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] sqlite3_temp_directory in main.c
I can see your point, I guess, though I can't say that it seems like a major issue to me. Just out of curiosity, why aren't the defaults derived in os_win.x and os_unix.c sufficient? > -Original Message- > From: weiyang wang [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 06, 2007 11:17 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] sqlite3_temp_directory in main.c > > the concern here is to configure the default temp directory in library > compile time, on the OS porting layer. > in current version, the default value of > sqlite3_temp_directory is hardcoded > as 0 in main.c for all platforms. > would it be better to make it configrable for different > platforms and use > PRAGMA to overide the defult in runtime. > > i am looking forward to your opiniions. > > thanks again. > > wang > > On 6/6/07, Tom Briggs <[EMAIL PROTECTED]> wrote: > > > > > > Why not just use PRAGMA temp_store_directory, as the comments > > directly above that line suggest? > > > > > -Original Message- > > > From: weiyang wang [mailto:[EMAIL PROTECTED] > > > Sent: Wednesday, June 06, 2007 7:09 AM > > > To: sqlite-users > > > Subject: [sqlite] sqlite3_temp_directory in main.c > > > > > > hi, > > > > > > i found the following line in main.c > > > > > > char *sqlite3_temp_directory = 0; > > > > > > which seems to remove the possibilities that the customer > > > platform could > > > specify sqlite3_temp_directory. > > > > > > i suggest that this line is removed. > > > > > > any comments? > > > > > > thanks in advance. > > > > > > wang > > > > > > > > > > -- > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > > > > -- > --- > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3_temp_directory in main.c
Why not just use PRAGMA temp_store_directory, as the comments directly above that line suggest? > -Original Message- > From: weiyang wang [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 06, 2007 7:09 AM > To: sqlite-users > Subject: [sqlite] sqlite3_temp_directory in main.c > > hi, > > i found the following line in main.c > > char *sqlite3_temp_directory = 0; > > which seems to remove the possibilities that the customer > platform could > specify sqlite3_temp_directory. > > i suggest that this line is removed. > > any comments? > > thanks in advance. > > wang > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Does sqlite3_step searches for a row in the table / or some results buffer?
The insert will not be allowed until the query is closed, so you can't get into this situation. -T > -Original Message- > From: B V, Phanisekhar [mailto:[EMAIL PROTECTED] > Sent: Wednesday, June 06, 2007 8:01 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Does sqlite3_step searches for a row in the > table / or some results buffer? > > Assume a query > > "select * from table " > > > > Let there be 10 rows in the table > > Hence there will be 10 rows in the result corresponding to the above > query. We can get all these 10 rows by calling sqlite3_step 10 times. > > Assume after 3 sqlite3_step calls, we insert a row into this > table. Now > after insertion we call sqlite3_step for the instruction > "select * from > table". Will this newly added row also get returned in this case? > > > > Sqlite3_step is supposed to execute the statement pStmt, > either until a > row of data is ready, the statement is completely executed or an error > occurs. > > > > > > Regards, > > Phani > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sorted index
When querying the table be sure to put the indexed column(s) in the ORDER BY clause though - otherwise the index won't do you any good. :) -T > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 05, 2007 9:20 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Sorted index > > B-Tree indices are in sorted sequence. Just raise an index > on the column. > > [EMAIL PROTECTED] wrote: > > > > > > I would like to maintain a sorted list of ~3000 entries. > > I know that I can create a table and the SELECT from it > with the ORDER BY clause > > in order to sort it. > > However I do not want the overhead of doing this after > adding a new entry. > > It would be good if I could create an index that was > sorted, and then when I > > add a new entry to the table it would automatically be > inserted in the index at > > the correct position. > > Is this possibe? > > If not, can anyone suggest any other solution, baring in > mind that RAM memory > > needs to be kep to a minimum? > > > > Thanks > > Clive > > > > > > > > > -- > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > --- > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Concurrency
> If you require ACID type data integrity and have a single > disk there is > no such thing as a "high concurrency database". They all Then don't blame me if he's asking the wrong questions. :) -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Concurrency
I have no suggestions (I'm not an embedded systems guy), but your initial comment implied that there were other options. If there aren't then your original statement is invalid and there's nothing to discuss. :) -T > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Friday, June 01, 2007 8:27 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Concurrency > > Tom, > > > > I don't want to use > > > other database, because I think Sqlite is great for an > > > embedded system that I > > > am using. > > > >I think that your own questions about concurrency prove this > > incorrect. If you need high concurrency and you don't like retries, > > SQLite is not the database for you. > > Then what database would you suggest instead Sqlite for use > in embedded (low > memory, cpu: 200 Mhz) system? > __ > _ > Najhitrejši brezplačni klicni dostop :: Varno in zanesljivo > po internetu > Obiščite http://www.dialup386.com/ > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Concurrency
> I don't want to use > other database, because I think Sqlite is great for an > embedded system that I > am using. I think that your own questions about concurrency prove this incorrect. If you need high concurrency and you don't like retries, SQLite is not the database for you. -T - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is there a way to turn off -journal files?
The short answer is that you can not (as far as I'm aware, anyway) turn of journal files, though it's a common question and I'm sure you can find a lot of information about it by searching the archives. TEMP_STORE controls where "truly" temp files (e.g. those needed for sorting, and I believe for tables declared as temporary) are stored. Journal files are not really temporary - they need to persist if the process should die in order for recovery to be performed. They also need to be stored in a predictable location, again so that changes can be consistently recovered in the case of a crash, so they are always stored in the same directory as the database itself. Thus TEMP_STORE has no impact on their creation or location. -Tom > -Original Message- > From: Dave Gierok [mailto:[EMAIL PROTECTED] > Sent: Monday, January 15, 2007 11:23 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Is there a way to turn off -journal files? > > I'm building the Sqlite lib using the preprocessor definition > TEMP_STORE=3, but I still notice that temporary '-journal' > files are being created/deleted when running queries that > will modify the DB. I thought that the TEMP_STORE=3 was > supposed to disable these temporary files that get created. > > How can I build/use Sqlite such that these temporary files do > not get created? I am running on Xbox360 and perf/storage > are critical because the DB can be stored to a memory card > (not much storage). > > Thanks, > Dave Gierok > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] attach in transaction
Would attaching a database mid-transaction, then making changes in the newly-attached database, make it impossible to create the correct master journal file for the overall transaction? Just a curious shot in the dark. -Tom > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Monday, January 08, 2007 10:43 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] attach in transaction > > [EMAIL PROTECTED] wrote: > > > > Can someone tell me why attach cannot be called within transaction? > > I do not recall. > > Clearly a DETACH will not work inside a transaction if the > table being detached has been accessed or modified within that > transaction. But ATTACH could work, I would think. > > Perhaps we disallowed ATTACH so that people would not expect > an automatic DETACH if the transaction rolls back? > > The prohibition against running ATTACH within transaction > first appeared in version 3.0.1. Presumably in version 3.0.0 > you could ATTACH within a transaction. > > If you want to experiement, you can comment out the check > for begin within a transaction in the attach.c source file, > recompile, try doing various attaches within transactions, > and see what bad things happen. This might give us a clue > as to why it is restricted. Usually we do not prohibit > things without good reason, I just do not recall what that > reason is. Perhaps the original reason no longer applies > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical "power" operator?
> This is quick simple and portable to your application only. > You can not > use a 3rd party GUI database browser that is statically linked to a > different SQLite library (such as the standard distribution) > to view or > modify your database. You must add specific support for any desired > operation to your application (or turn your application into > another GUI > database browser to be able to do everything the 3rd party > tools can do). To quote a local radio DJ: You're making my point! If you want the Super SQLite GUI Tool and Swiss Army Knife 3000 to have a POWER function, contact the maintainer of said tool - it's an application problem. If you want your application to have the POWER function, roll your own build of SQLite. Those are application problems, not database problems. And quite frankly I think the real problem in your example is static linking - If a 3rd party tool links in SQLite statically, then you're basically stuck with whatever version they choose, with whatever extensions they add - in other words, you're using *their* embedded database, not SQLite. Which is probably as it should be, but one shouldn't expect SQLite to solve problems for said application. One other thing I just thought of: is the POWER function (or operator) standard SQL? I'm honestly not sure, off the top of my head, but I'm guessing that that factors in here somewhere. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical 'power' operator?
I'm sure that anything is possible, but I do not think that would be a practical approach. :) What seems to "easiest" is relative - I find working directly with the SQLite codebase very, very easy. But I'm a C guy. The whole Tcl thing gives me the willies. So I wouldn't generalize things according to language anyway. -Tom > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 21, 2006 12:56 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Mathematical 'power' operator? > > Maybe a dumb queston, but: > As it looks it is easiest to work with SQLite from Tcl, is it possible > to code in Tcl and call that from VB/VBA? > > RBS > > > Well put. If Sqlite were turned into a junior Oracle, DB2 > or PostgreSQL > > then someone else would have to create a new Sqlite to handle the > > lightweight embedded RDBMS role! > > > > It is very simple to add functions to Sqlite, and since it > is a library > > you link into your application there is no reason not to > have your own > > Sqlite-local library which adds all the functions needed by your > > application. Many of the features people want to add to Sqlite are > > better added by the addition of a specifically targetted application > > layer. > > > > Those persons wanting the simplicity of Sqlite and all the > functionality > > of PostgrSQL might do better to re-assess their goals and > save time by > > using PostgreSQL and coming to terms the fact that the > extra complexity > > is the price to pay for the added functionality. > > > > In our applications we have done just that and have the advantage of > > simple SQL, excellent performance and small footprint in > our distributed > > applications. We use PostgreSQL where its enterprise features are > > necessary to handle large numbers of concurrent users. We > thereby avoid > > underkill and overkill. > > > > The add-on functions, and application interfaces are better being > > contributed software than to bloat Sqlite distributions and > be a boat > > anchor on its continued development. > > > > Tom Briggs wrote: > >> > >> > >> > >>>In the case of SQLite, I (arguably) have to use a 3rd party > >>>management > >>>tool, for which my custom functions are no longer available. I'm > >>>curious how others handle this. > >>> > >>>A. You don't need or use any custom SQL functionality > >>>B. You don't use a 3rd party SQLite management tool > >>>C. Something else I haven't thought of? > >> > >> > >>I think that the key point you're missing here is that > SQLite is not > >> intended to be standalone database system like the other > products you > >> mentioned (Access, Oracle, etc.) - it is an embeddable > database library. > >> It happens to have a convenient command line interface > that allows it to > >> be used as a standalone database, but that's just a shell > (pun intended) > >> that allows you to get to the library itself. The 3rd party "front > >> ends" to which you refer are really application consumers of SQLite > >> itself - not add-ons to or features of SQLite. In other > words: it's a > >> development tool, not a database. > >> > >>Now, as for a "power" function: we had exactly the same > need when we > >> first started using SQLite. Our solution: we added it. > The source code > >> is freely available, after all. Adding a new function to > the code is > >> shockingly straightforward; from there you simply compile > your version > >> of the library and use that in your application(s). > Quick, simple and > >> portable, both across platforms and applications using > your version of > >> the library. > >> > >>-Tom > >> > >> > >> > >> > >> > -- > --- > >> To unsubscribe, send email to [EMAIL PROTECTED] > >> > -- > --- > >> > > > > > > > -- > --- > > To unsubscribe, send email to [EMAIL PROTECTED] > > > -- > --- > > > > > > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Mathematical "power" operator?
> In the case of SQLite, I (arguably) have to use a 3rd party > management > tool, for which my custom functions are no longer available. I'm > curious how others handle this. > > A. You don't need or use any custom SQL functionality > B. You don't use a 3rd party SQLite management tool > C. Something else I haven't thought of? I think that the key point you're missing here is that SQLite is not intended to be standalone database system like the other products you mentioned (Access, Oracle, etc.) - it is an embeddable database library. It happens to have a convenient command line interface that allows it to be used as a standalone database, but that's just a shell (pun intended) that allows you to get to the library itself. The 3rd party "front ends" to which you refer are really application consumers of SQLite itself - not add-ons to or features of SQLite. In other words: it's a development tool, not a database. Now, as for a "power" function: we had exactly the same need when we first started using SQLite. Our solution: we added it. The source code is freely available, after all. Adding a new function to the code is shockingly straightforward; from there you simply compile your version of the library and use that in your application(s). Quick, simple and portable, both across platforms and applications using your version of the library. -Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Hrm... I didn't realize that #define was limited to values less than 32. That being the case, you probably can't avoid starting multiple transactions. -Tom > -Original Message- > From: Serena Lien [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 04, 2006 9:57 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Optimize performance - reading from > multiple database files, processing and writing to separate > results database file? > > Yes, I could indeed determine which databases I want to > attach to, but there > is a limit to the number of databases you can attach (I think > it's 32?) and > I might have more than that. So I thought the safer route was > to attach as I > go.. > > Serena. > > > On 10/4/06, Tom Briggs <[EMAIL PROTECTED]> wrote: > > > > > >Do you know all of the databases that you want to attach > to at the > > start of processing? If so, there's no reason you can't > simply attach > > them all beforehand, start your transaction, and complete all your > > processing. If you have to decide dynamically, based on > the data, which > > databases you'll need, then you have no choice but to start and end > > multiple transactions in order to attach the databases as you go. > > > >-Tom > > > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Optimize performance - reading from multiple database files, processing and writing to separate results database file?
Do you know all of the databases that you want to attach to at the start of processing? If so, there's no reason you can't simply attach them all beforehand, start your transaction, and complete all your processing. If you have to decide dynamically, based on the data, which databases you'll need, then you have no choice but to start and end multiple transactions in order to attach the databases as you go. -Tom > -Original Message- > From: Serena Lien [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 04, 2006 8:59 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Optimize performance - reading from > multiple database files, processing and writing to separate > results database file? > > Hello, > > I have many databases, all in separate files. I want to > choose a subset of > them > based on some query, and read the input data one at a time, process it > somehow > and write the results to another database file. > > The problems are caused by: > - May have a large number of inputs (eg > 32 databases to attach) > - May be a lot of data (eg > 1GB total), too much to copy > the sources into > a > table in an in-memory database, then processing each row in > this table. > - Don't particularly want to duplicate the source data, by > copying sources > into > a temporary database on disk, unless there is no better method > - Need to use transactions when inserting processed data > into a table in > the results database > > I have tried the following: > - Attach results database > - Begin transaction > - Attach first source database and select its data > - Process data and bind results for insertion into results > db, call step > - Detach source database and attach next source database, repeat etc > - End transaction > But this won't work, because trying to attach a database > gives the error: > Cannot > attach database within transaction! But I really need transactions for > performance because I am inserting a lot of data (more rows than I am > reading > from my inputs) > > I would appreciate any suggestions on how best to do this. > > Serena. > - To unsubscribe, send email to [EMAIL PROTECTED] -
