[sqlite] Odd performance issue under Windows

2007-04-25 Thread John Elrick
pening that is creating a better than order of magnitude difference in execution time on five out of seven Windows machines?". Any suggestions for further investigation are appreciated. John Elrick Fenestra Technologies - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Re: Odd performance issue under Windows

2007-04-25 Thread John Elrick
Igor Tandetnik wrote: John Elrick wrote: We tested the procedure under performance analysis on seven different Windows machines, ranging from Windows 2000, Windows XP and Vista. In five of the cases, inserting 128 rows outside of the context of a transaction takes 11 - 17 seconds

Re: [sqlite] Odd performance issue under Windows

2007-04-25 Thread John Elrick
Griggs, Donald wrote: John Elrick wrote: "what the heck is happening that is creating a better than order of magnitude difference in execution time on five out of seven Windows machines?". John, If the database is opened and closed just once for your testing, I think yo

Re: [sqlite] Odd performance issue under Windows

2007-04-25 Thread John Elrick
John Elrick wrote: Griggs, Donald wrote: John Elrick wrote: "what the heck is happening that is creating a better than order of magnitude difference in execution time on five out of seven Windows machines?". John, If the database is opened and closed just once for your

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-25 Thread John Elrick
John Elrick wrote: John Elrick wrote: Griggs, Donald wrote: John Elrick wrote: "what the heck is happening that is creating a better than order of magnitude difference in execution time on five out of seven Windows machines?". John, If the database is opened and closed

Re: [sqlite] Odd performance issue under Windows

2007-04-26 Thread John Elrick
aggressive about committing data to disk than XP Home and Pro and Vista. I am still uncertain what factors or configuration settings could be affecting the performance. Thanks, John Elrick - To unsubscribe, send email

Re: [sqlite] Odd performance issue under Windows

2007-04-26 Thread John Elrick
Griggs, Donald wrote: I may be confused a bit. Regarding: 1) "the described slowdown occurs consistently on Windows XP Home and Pro and on Windows Vista." On its face, I would think this means that Xp Home and Vista do *NOT* have a problem, and that "fast" behaviour represents an

Re: [sqlite] Odd performance issue under Windows

2007-04-26 Thread John Elrick
Nuno Lucas wrote: And you seem to not have noticed this link: http://www.microsoft.com/technet/prodtechnol/windows2000serv/maintain/optimize/wperfch7.mspx I actually did and thank you. I received your message within a minute after I had sent my last one. The wonders of email delays

Re: [sqlite] Odd performance issue under Windows

2007-04-26 Thread John Elrick
Nuno Lucas wrote: On 4/26/07, John Elrick <[EMAIL PROTECTED]> wrote: 2. Settings which cause Media Center to return control more optimistically than Pro or Home. In this case, there would be a hypothetically higher risk of data loss on the Media Center machine. However, the

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-27 Thread John Elrick
John Stanton wrote: The real time with the pragma off is 1.78 seconds. The real time on the "faster" machine is 8.4 seconds. When I set the synchronous pragma to off on the "faster" machine, the time drops to 1.64. John Do your various machines use the same hard disk controller and

Re: [sqlite] Odd performance issue under Windows - correction

2007-04-27 Thread John Elrick
John Stanton wrote: John Elrick wrote: John Stanton wrote: I would look at the disk controller/disk drive hardware and the software driver to see if they are reporting correctly to the OS. Some of your numbers are too fast for regular disk technology and suggest that there are either

Re: [sqlite] Transaction journal corrupted by antivirus

2007-05-03 Thread John Elrick
Voxen wrote: The problem is my application is used by thousand of customers. I cannot ask them to tweak their antivirus. An observation. Thunderbird uses simple text files to store unencrypted messages. I am not aware of anti-virus programs having trouble with those files. Perhaps a

Re: [sqlite] Equal distribution from random rows

2007-05-30 Thread John Elrick
Alex Teslik wrote: Hello, I'm working on a project that requires random images to be chosen from a live database for a dynamic homepage. I found this link from Dr. Hipp that details a very fast approach to selecting random rows:

Re: [sqlite] Equal distribution from random rows

2007-05-31 Thread John Elrick
Alex Teslik wrote: On Wed, 30 May 2007 15:18:18 -0400, John Elrick wrote After running a simple test, I confirmed a suspicion. VACUUM doesn't reorder the ROWIDs, so you still have breaks. My tests show otherwise: SNIP did I do something incorrectly? Not incorrectly

Re: [sqlite] Re: CAST

2007-05-31 Thread John Elrick
es you wish and then store the information in a separate table which in some way mirrors sqlite_master. Introspection would occur via this mechanism and would even move all introspection for any given system behind a common interface. Just a thought. John

Re: [sqlite] Re: CAST

2007-05-31 Thread John Elrick
John Stanton wrote: John Elrick wrote: SNIP Introspection would occur via this mechanism and would even move all introspection for any given system behind a common interface. Just a thought. John Elrick CREATE TABLE already stores the type as its declared type. The user has

Re: [sqlite] Re: CAST

2007-05-31 Thread John Elrick
John Stanton wrote: Sqlite lets you put in anything as the declared type. "DEAD PARROT", "MONGOOSE", "GODZILLA" or "DECIMAL(6,1)" are all acceptable declared types. Sqlite makes the underlying type TEXT if it is not obviously numeric. Thanks for the clarification. I wasn't aware of

Re: [sqlite] Why do you use SQLite? Comments for an article needed

2007-06-07 Thread John Elrick
support community. 3. Mature library 4. Source readily available 5. Zero config. One of our requirements is to ensure minimal burden to our users. 6. Fast. We have some requirements tied to performance, and SQLite met all of them handily. John Elrick Software Designer/Developer Fenestra

Re: [sqlite] Why do you use SQLite? Comments for an article needed

2007-06-08 Thread John Elrick
A.J.Millan wrote: John Elrick wrote: Tim Anderson wrote: We are working on a project for the Census Bureau and needed an embeddable database that was zero configuration for the user and fast. We evaluated SQLite against numerous competitors... IMHO, a bit exaggerated the "num

Re: [sqlite] Update of multiple columns

2007-06-18 Thread John Elrick
T wrote: In case the original question has been clouded by discussions of running a general store and the arson habits of the Hand Crafted Guild, allow me to crystalize my purely SQLite question: I know I can update via: update Table1 set c1 = (select d1 from Table2 where

[sqlite] Need help linking into Delphi Application

2007-06-18 Thread John Elrick
world. I programmed in C back in the '80's, so my skill set there is beyond rusty. Can someone point me to resources so I can learn enough to solve these types of issues on my own in the future? I've tried Google and it hasn't given me anything of value, but I could be asking the wrong question

Re: [sqlite] Need help linking into Delphi Application

2007-06-19 Thread John Elrick
John Elrick wrote: I've been using the Delphi ASGSqlite components with static linking for some time with version 3.3.13. I'd like to move on up to 3.4.0, however, no one seems to have documented how to do this yet. I tried compiling the Amalgamation with Borland C++ 5.0 and it generates

Re: [sqlite] [Delphi] Escaping quote?

2007-06-26 Thread John Elrick
Gilles Ganault wrote: Hello I'm having a problem saving strings into a colum from a Delphi application because they might contain the ( ' ) single quote character: = // Input := 'Let's meet at the pub tonight!'; MyFormat := 'insert into stuff (title) values ('''%s')'; SQL :=

Re: [sqlite] [Delphi] Escaping quote?

2007-06-26 Thread John Elrick
, 0); sqlite3_free(zSQL); Because the %q format string is used, the '\'' character in zText is escaped and the SQL generated is as follows: INSERT INTO table1 VALUES('It''s a happy day!'); Question, does the %q operator offer any advantages over calling Quote

Re: [sqlite] [Delphi] Escaping quote?

2007-06-26 Thread John Elrick
Clay Dowling wrote: John Elrick wrote: // Input := 'Let's meet at the pub tonight!'; MyFormat := 'insert into stuff (title) values (%s)'; SQL := Format(MyFormat, QuotedStr(Input)); try ASQLite3DB1.Database := db; ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName

Re: [sqlite] Unique ids for each record

2007-08-21 Thread John Elrick
Eric Bohlman wrote: Sreedhar.a wrote: SNIP You need artist identifiers to be unique (you could in fact use artist names, since copyright rules require that such names be unique). You also need album names to be unique, but only within artist groups (you're not going to have the *same*

Re: [sqlite] Eliminate duplicate entries

2007-09-04 Thread John Elrick
Scott Derrick wrote: I have an application that is inserting a record every second. There are thousands of periods from a few seconds to hours long where the data in all the columns is identical thus causing hundreds of thousands of duplicate rows. Is there a way to set up the INSERT

Re: [sqlite] Copying Records from one table to another

2007-11-15 Thread John Elrick
LSOpenSubProjects; Did you perhaps mean: insert into LSOpenJobs select * from LSOpenSubProjects where id = 530 ; ? John Elrick - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] sqlite3Explorer

2007-11-25 Thread John Elrick
Joe Wilson wrote: --- Cariotoglou Mike <[EMAIL PROTECTED]> wrote: I wish I could "make it for Unix", but it uses a lot of windows-specific things, plus it is done in Delphi, and since Kylix is practically dead, wlll... I didn't realize it was written in Delphi. Yes, that would be

Re: [sqlite] Improving performance of SQLite. Anyone heard of Devic eSQL?

2007-12-16 Thread John Elrick
he decision makers (management) and decision breakers (engineers with influence) that yours is the safest choice to make. FWIW John Elrick - To unsubscribe, send email to [EMAIL PROTECTED] -

Re: [sqlite] Improving performance of SQLite. Anyone heard ofDevic eSQL?

2007-12-17 Thread John Elrick
James Steward wrote: On Mon, 2007-12-17 at 15:30 -0600, Fred Williams wrote: A hundred or so Visual Basic programmers are cheaper to replace and "maintain" than one good Delphi/C++ programmer. ;-) That is the reason management likes "Visual ." Been there, learned that. Hire the staff

Re: [sqlite] Improving performance of SQLite. Anyone heard ofDevice SQL?

2007-12-17 Thread John Elrick
John Elrick wrote: SNIP When you can hire a forklift operator to program (well, that is), To avoid a misunderstanding...I mean - right off the forklift. I'm sure anyone with the proper motivation can learn to program, but it took me 25 years to realize how little I really knew. John

Re: [sqlite] Improving performance of SQLite. Anyone heardofDevice SQL?

2007-12-17 Thread John Elrick
I didn't write the original about forklift operators. It was Fred Williams, AFAIK. It was. SNIP IMHO, this has gone completely off topic, and I shall hence forth cease to contribute to this, and related threads. I think you may be correct. John

Re: [sqlite] Date arithmetic question

2008-01-18 Thread John Elrick
[EMAIL PROTECTED] wrote: "Virgilio Fornazin" <[EMAIL PROTECTED]> wrote: DATEDIFF should compute the difference by arithmetic subtracting M/Y in month case, if I'm not wrong ex: DateDiff (month, 1-1-2007, 3-30-2007) will return 2 Its that right ? So datediff('month', '2008-02-01

Re: [sqlite] SQLite character comparisons

2008-01-21 Thread John Elrick
Fowler, Jeff wrote: Hello All, Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. My $0.02. If we are

[sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
I have a question for the SQLite experts here as to the most efficient way to retrieve the following: Say we have a table: CREATE TABLE FOO ( PARENT_ID INTEGER, CHILD_ID INTEGER, REVISION INTEGER ); CREATE UNIQUE INDEX FOO_IDX1 ON FOO (PARENT_ID,CHILD_ID,REVISION); note that we could

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
Dennis Cote wrote: > John Elrick wrote: > >> I have a question for the SQLite experts here as to the most efficient >> way to retrieve the following: >> >> SNIP > John, > > Do you mean the last rowid, or do you really mean the last revision? &

Re: [sqlite] Selecting last item in a unique index

2008-02-15 Thread John Elrick
Dennis Cote wrote: > John Elrick wrote: > >> Last rowid. Rowid will be a key linking to other tables. >> >> > > What table will rowid be linked to? Are you linking children rows back > to parent rows? > > Not exactly. This is a theoretical i

[sqlite] Hierarchical Deletion via a Trigger?

2008-02-22 Thread John Elrick
is documented). I can think of a delete query which would also remove the first level, but am having a brain lock on any single query which would walk a chain of arbitrary length. Am I missing something obvious? If not, does anyone have any brilliant ideas? John Elrick Fenestra Technologies

Re: [sqlite] Hierarchical Deletion via a Trigger?

2008-02-22 Thread John Elrick
Igor Tandetnik wrote: > John Elrick <[EMAIL PROTECTED]> wrote: > >> I'm a bit stumped and was curious if anyone had an elegant solution >> for this problem. Assuming the following simplified example, my goal >> is to cascade the deletes until all parent/child

Re: [sqlite] Hierarchical Deletion via a Trigger?

2008-02-22 Thread John Elrick
Nicolas Williams wrote: > On Fri, Feb 22, 2008 at 10:46:00AM -0500, Igor Tandetnik wrote: > >> John Elrick <[EMAIL PROTECTED]> wrote: >> >>> I'm a bit stumped and was curious if anyone had an elegant solution >>> for this problem. Assuming

Re: [sqlite] Hierarchical Deletion via a Trigger?

2008-02-22 Thread John Elrick
Nicolas Williams wrote: > On Fri, Feb 22, 2008 at 09:24:06AM -0700, Dennis Cote wrote: > >> As Nicolas said, SQL:1999 defines a standard method of doing this. It >> uses a WITH RECURSIVE clause as a prefix to a SELECT. It hasn't been >> widely implemented, but I believe that IBM's DB2

Re: [sqlite] Reducing size of timestamps

2008-02-22 Thread John Elrick
Rich Rattanni wrote: > All: > > I was wondering if there was any way to reduce the 'cost' of storing a > timestamp on entries in a SQLite database. I performed a hexdump of > the file and it showed me the timestamp is stored as a 19-byte ASCII > string. One quick thing I thought of was to store

[sqlite] FTS3 and 5.5.6 Amalgamation

2008-02-28 Thread John Elrick
link to whatever has happened or needs be done would be in order? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

2008-03-04 Thread John Elrick
Gilles Ganault wrote: > On Tue, 4 Mar 2008 09:00:31 -0500, "Igor Tandetnik" > <[EMAIL PROTECTED]> wrote: > >> There's no difference as far as SQLite is concerned. Length >> specification, if present, is parsed but not enforced. >> > > Yes, that's what I read too, but obviously, like

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

2008-03-04 Thread John Elrick
P Kishor wrote: > I think you have answered you question yourself. See below -- > > On 3/4/08, Gilles Ganault <[EMAIL PROTECTED]> wrote: > >> Hello >> >> I thought that SQLite treated any numeric data the same, but some >> Delphi components seem to handle TEXT data as BLOB/MEMO, and

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

2008-03-04 Thread John Elrick
Gilles Ganault wrote: > On Tue, 04 Mar 2008 09:27:14 -0500, John Elrick > <[EMAIL PROTECTED]> wrote: > >> FYI, we rolled our own adapters for a Delphi project. We had no use for >> the TDataset capabilities and removed the overhead. I would be happy to >&

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

2008-03-04 Thread John Elrick
enting the wheel, but you quickly find out you are not. TDataSource/TDataLink was a good idea in its time, but it is way out of date and, in my experience, creates as many problems as it solves once you start building more complex user-centric GUIs. FWIW John Elrick Fenestra Technologies __

Re: [sqlite] [Delphi7] TEXT vs. (VAR)CHAR?

2008-03-04 Thread John Elrick
Gilles Ganault wrote: > On Tue, 04 Mar 2008 14:09:51 -0500, John Elrick > <[EMAIL PROTECTED]> wrote: > >> You can't. You lose the db aware aspect, but what I'm saying is it >> isn't as "bad" as I used to think. >> > > OK, I don't mind no

Re: [sqlite] Generating new rowid algo

2008-03-10 Thread John Elrick
Brad Stiles wrote: > > >>> I wanted to know the algorithm used by sqlite to generate the new >>> rowid. Assume there can be N distinct rowid's possible, now insert N >>> records, followed by random deletion of some records. Now what rowid >>> will be assigned to a new row that is added?

Re: [sqlite] Testing the rowid algorithm. Was: Generating new rowid alg o

2008-03-11 Thread John Elrick
[EMAIL PROTECTED] wrote: > "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: > >> "B V, Phanisekhar" <[EMAIL PROTECTED]> wrote in >> message news:[EMAIL PROTECTED] >> >>> So is it possible >>> that SQLITE will give an error when attempting to insert a record even >>> if there are free ROWID's?

Re: [sqlite] How to speed up my queries?

2008-06-04 Thread John Elrick
Christophe Leske wrote: > There is virtually no difference in using indices or not in my query. > > I also tried to reformulate my statement in order not to use BETWEEN but > a sandwiched > and < statement: > > SELECT * FROM Cities WHERE class_dds<11 and (longitude_DDS BETWEEN > 6.765103 and

Re: [sqlite] prepare peformances

2008-06-09 Thread John Elrick
Chiming in a thought, but isn't there a subtle difference between the two? As I am reading Toms post, sqlite3_prepare would be called once and the parameters would be bound up to 384 times The call to sqlite3_exec should finalize the prepared query and re-prepare it 384 times. I would expect

Re: [sqlite] Writing double into a socket file

2008-06-10 Thread John Elrick
gt; As John pointed out, XML is not intended to handle binary data directly. We use XML as a transfer medium for binary data and simply base64 encode it before encapsulation. John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@

Re: [sqlite] Using SQLite as an application file format (C++)

2008-08-18 Thread John Elrick
ations tend to be rather expensive and can outweigh a disk access by an order of magnitude. John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference in performance between 32 and 64 bit versionsof SQLite?

2008-09-01 Thread John Elrick
ainst an equivalent 32 bit AMD 4G workstation. These workstations will > be processing very large text based log files (> 16G each). We will be > using Python 2.52 as our SQLite scripting tool. Given the potential expense, wouldn't it be justifiable to invest in a single test machine to

Re: [sqlite] Power Loss and database files corruption

2008-10-01 Thread John Elrick
isk itself isn't corrupted. FWIW John Elrick Calamani, Olga wrote: > Yes I do! and I clearly understand that is not possible to eliminate > corruption problems, but I'd like to set up the system in the best way ... > > Olga > > -Original Message- > From: [EMAIL PROT

[sqlite] Typo on website

2008-10-02 Thread John Elrick
.' John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Explain Query Plan

2008-10-27 Thread John Elrick
Need some help understanding exactly what EXPLAIN QUERY PLAN is returning. I'm getting the following from the following. Can someone explain what the "order" "from" and "detail" should be telling me? Thanks, John Elrick Fenestra Techologies Scenario 1: I

Re: [sqlite] insert speeds slowing down as database size increases( newb)

2008-10-29 Thread John Elrick
Eduardo Morras wrote: > At 13:10 29/10/2008, you wrote: > >> Look up the implications of Sqlite's ACID feature and the use of >> transactions. COMMITs are tied to disk rotation speed. On our Sqlite >> databases where we look for performance we use 15,000 rpm disks and are >> diligent in

Re: [sqlite] Unhappy with performance

2008-10-31 Thread John Elrick
Marian Aldenhoevel wrote: > Hi, > > >> Are you able to benchmark it using an actual PC's local hard drive? >> Just for comparison. To be fair, you'd have to use the same build of >> sqlite, or at one that was built the same way. >> > > That would be quite an effort. > > For a quick

Re: [sqlite] Unhappy with performance

2008-10-31 Thread John Elrick
Marian Aldenhövel wrote: > Hi, > > >> FWIW, I ran your simple example on a Windows XP machine through the Ruby >> driver and got 8 seconds for the update. >> > > Scaling that down to the hardware being used, which is a 486-clone with > a 16bit bus showing as running at 31 BogoMIPS in

Re: [sqlite] Sqlite3Explorer Sqlite Report Designer

2008-12-12 Thread John Elrick
) nothing else will give us the productivity coupled with the performance we need. John Elrick Fenestra Technologies Timothy A. Sawyer wrote: > If you want something that is Windows based like you describe, why not use > Java to build a Swing GUI? Delphi and Pascal are a little antiqua

Re: [sqlite] SQLite version 3.6.10

2009-01-15 Thread John Elrick
ow a pattern I use for any software release. SQLite has earned the reputation of being highly reliable and stable. A single spat of multiple releases is a statistical blip, not a pattern. Keep up the good work, John Elrick Senior IT Specialist Fe

Re: [sqlite] Effective way to use RTree to find the neighborhood ?? ?

2009-01-22 Thread John Elrick
ot; issues. How big are the files? Can you copy them locally for your queries? Could you put some kind of server on the same machine as the files and poll the server instead? HTH John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.or

[sqlite] Alpha numeric collation

2009-02-23 Thread John Elrick
they were indeed numerics: 1 4 9 10 51 a Thanks for any feedback, including "nope, you have to roll your own". John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailma

Re: [sqlite] Alpha numeric collation

2009-02-23 Thread John Elrick
> 10 > 51 > a Thanks very much for your assistance Simon. The first case may indeed work, the field is currently varchar but there is nothing preventing me from making it integer, and the second case gives me some insights I hadn't considered.

[sqlite] Conditional expressions and evaluation

2009-02-23 Thread John Elrick
y.f = 'foo' that the sub-select is considered "not needed" and therefore is not executed? John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Conditional expressions and evaluation

2009-02-23 Thread John Elrick
D. Richard Hipp wrote: > On Feb 23, 2009, at 1:39 PM, John Elrick wrote: > > >> A clarification question...given the query: >> >> create table x (x_id integer, f varchar); >> create table y (y_id integer, x_id integer, f varchar); >> >> insert into x

[sqlite] Using result of subquery both as value and conditional test...

2009-02-24 Thread John Elrick
, 'Hello world'); select case when (select y_value from y where y_id = x_id) is null then 'darn' else (select y_value from y where y_id = x_id) end from x Is there any way to eliminate the second (select y_value from y where y_id = x_id)? If so, what would the query look like? John Elrick

Re: [sqlite] Using result of subquery both as value and conditional test...

2009-02-24 Thread John Elrick
D. Richard Hipp wrote: > On Feb 24, 2009, at 2:15 PM, John Elrick wrote: > > SNIP >> >> Is there any way to eliminate the second (select y_value from y where >> y_id = x_id)? If so, what would the query look like? >> >> > > SELECT coalesce((

[sqlite] Scrolling cursor with multiple keys...

2009-02-25 Thread John Elrick
h I have overlooked? Thanks, John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Scrolling cursor with multiple keys...

2009-02-25 Thread John Elrick
John Elrick wrote: > I have a situation where I need to retrieve the 'next' item in a table > sorted by an arbitrary number of keys. My current planned solution is to > create a table for the sorting which is recreated as needed with the > appropriate keys. As a simplified exampl

Re: [sqlite] nullable select fields

2009-03-10 Thread John Elrick
have multiple possibilities and combinations, my own preference would be to have a dynamically created SQL select statement. We do that for some of our more complicated object relationships. John Elrick Fenestra Technologies ___ sqlite-users mailing list s

Re: [sqlite] WHERE clause doesn't seem to work right

2009-03-13 Thread John Elrick
teger, the comparison should be done as a numeric comparison, in which case 3 is less than 100. HTH, John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WHERE clause doesn't seem to work right

2009-03-13 Thread John Elrick
alphanumeric but which respects numeric sequencing: b a 300 30a 9 sorts as: 9 300 30a a b HTH John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] datetime as integer

2009-03-16 Thread John Elrick
MikeW wrote: > Timothy A. Sawyer writes: > > >> I stand corrected on the math >> >> Bottom line is that different applications use different baseline values for >> > epoch (beginning of time) > >> and you must know that baseline value >> --Original Message-- >>

Re: [sqlite] Improving query performance

2009-04-02 Thread John Elrick
D. Richard Hipp wrote: > On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > >> explain query plan >> select DISTINCT RESPONSES.RESPONSE_OID >> from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS >> where >> SEQUENCE_ELEMENTS.SEQUENCE_E

Re: [sqlite] General SQL question...

2009-04-02 Thread John Elrick
Igor Tandetnik wrote: > John Elrick <john.elr...@fenestra.com> wrote: > >> The following two queries appear to be functionally equivalent...that >> is to say the results they produce are identical. Is there any >> intrinsic advantage to one over the other? If

Re: [sqlite] Transaction isolation

2009-05-18 Thread John Elrick
|i| db.execute('replace into shelf (key, value) values(?,?)', i, i) end } puts 'done' ---- By executing the select outside of the context of the transaction, I obtain the desired transaction isolation. FWIW, John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Transaction isolation

2009-05-18 Thread John Elrick
SNIP >> I say this because your example implies that the Python wrapper starts >> the transaction automatically inside the execute, and I would not be >> surprised if it did so BEFORE executing the SQL parameter. >> > > The cursor() method that I call on the conn for the SELECT should give

Re: [sqlite] Transaction isolation

2009-05-18 Thread John Elrick
John Elrick wrote: > SNIP > >>> I say this because your example implies that the Python wrapper starts >>> the transaction automatically inside the execute, and I would not be >>> surprised if it did so BEFORE executing the SQL parameter. >>> >

Re: [sqlite] Transaction isolation

2009-05-18 Thread John Elrick
D. Richard Hipp wrote: > On May 18, 2009, at 1:13 PM, John Elrick wrote: > > >> John Elrick wrote: >> >>> SNIP >>> >>> >>>>> I say this because your example implies that the Python wrapper >>>>&

Re: [sqlite] most efficient way to get 1st row

2009-05-21 Thread John Elrick
John Stanton wrote: > Igor Tandetnik wrote: > >> John Stanton wrote: >> >> >>> Shane Harrelson wrote: >>> >>> Additionally, it's important to note that the LIMIT/OFFSET clause is not standard SQL >>> What makes you

Re: [sqlite] most efficient way to get 1st row

2009-05-21 Thread John Elrick
Sam Carleton wrote: > > > John Elrick wrote: >> John Stanton wrote: >> >>> Igor Tandetnik wrote: >>> >>>> John Stanton <jo...@viacognis.com> wrote: >>>> >>>>> Shane Harrelson wrote: >>>

Re: [sqlite] most efficient way to get 1st row

2009-05-21 Thread John Elrick
Sam Carleton wrote: > > SNIP I take it the brain removal was necessary for the politician to attain higher office? >>> I cannot see that being the case, they get worse off as they climb >>> the ranks, not better off! >>> >> >> I meant the politician having brain

Re: [sqlite] corrupt database recovery

2009-05-27 Thread John Elrick
Gene Allen wrote: > Ok...it's happened again and I've decided that I need to track this down > once and for all! > > Here is what I'm seeing: I get errors when I do a integrity_check (see > below), but I can .dump it to a text file and then .read it into another > database ok. > > It seems to me

Re: [sqlite] corrupt database recovery

2009-05-27 Thread John Elrick
> From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John Elrick > Sent: Wednesday, May 27, 2009 10:59 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] corrupt database recovery > > Gene Allen wrote: > &g

Re: [sqlite] SQlite performance on multi process env

2009-05-28 Thread John Elrick
e forced processes dividing the work among three cores -- leaving the fourth core for operating system processing. You gain nothing by throwing more processes at the problem. John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlit

Re: [sqlite] Join performance in SQLite

2009-05-30 Thread John Elrick
D. Richard Hipp wrote: > There has been a recent flurry of comments about SQLite at > > http://www.reddit.com/r/programming/comments/8oed5/how_sqlite_is_tested/ > http://news.ycombinator.com/item?id=633151 > > One of the criticisms of SQLite is that it is slow to do joins. That > is

Re: [sqlite] Should we upgrade the SQLite to 6.6.14.2 from 3.59

2009-06-01 Thread John Elrick
e run, I have complete confidence in Sqlite, but would still never think of a non-essential upgrade with less than a month to test. FWIW, John Elrick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

2009-06-04 Thread John Elrick
John Machin wrote: > On 5/06/2009 12:59 AM, Griggs, Donald wrote: > >> Regarding: >>I could start the id initially with 10 to allocate >> >> That WOULD allow for a bunch of bull.;-) >> > > Don't horse about with IDs with attached meaning; it's a cow of a > concept whose

Re: [sqlite] Getting last inserted rowid?

2009-06-04 Thread John Elrick
Nuno Lucas wrote: > On Wed, Jun 3, 2009 at 11:22 PM, Nikolaus Rath wrote: > >> Nuno Lucas writes: >> >>> On Wed, Jun 3, 2009 at 2:41 AM, Nikolaus Rath wrote: >>> Nuno Lucas writes:

Re: [sqlite] Getting last inserted rowid?

2009-06-06 Thread John Elrick
SNIP >> processed by a complied virtual machine. Threads switch at the >> machine code level, so a thread could switch between the processing >> of the 'g' and the 'i' of the first 'begin'. >> > > SQLite actually maintains a mutex per connection. Every API call > acquires this mutext and

Re: [sqlite] Working with a new DB every 5 minutes

2009-06-08 Thread John Elrick
Mohit Sindhwani wrote: > Hi Everyone, > > I'm having a problem that I'm trying to find an elegant solution to. I > have a database that stores real-time information - this information is > replaced by new values every 5 minutes and has about 30,000 entries. > Once a new database is made

Re: [sqlite] Working with a new DB every 5 minutes

2009-06-09 Thread John Elrick
Simon Slavin wrote: > On 8 Jun 2009, at 8:07pm, Mohit Sindhwani wrote: > > >> I'm having a problem that I'm trying to find an elegant solution >> to. I >> have a database that stores real-time information - this information >> is >> replaced by new values every 5 minutes and has about

Re: [sqlite] SQlite3 - SQL injection using ruby

2009-06-14 Thread John Elrick
dave lilley wrote: > I am using sqlite3 with ruby and hope I'm not out of place here in ask for > some help on how to stop or reduce injection threats via sql statements made > by a user be it accidental or deliberate. > > I want to build a select query from user entered data and then return rows

Re: [sqlite] SQlite3 - SQL injection using ruby

2009-06-15 Thread John Elrick
dave lilley wrote: > Sorry for posting twice but... > > how does the method you have given me differ to mine? > > eg lets say this is the scenario > > uservar = "delete * from customers where * = *" > > >>> e.g. stmt = "select * from customers where cust_no = #{uservar}" >>> row =

Re: [sqlite] SQlite3 - SQL injection using ruby

2009-06-15 Thread John Elrick
dave lilley wrote: > Many thanks John so if i take that example and push it out so i can have 1 > method that can return a SQL select statement on any table, field and search > criteria i would only need to do this? > > In ruby it would be > > make_SQL (table, field, criteria) >stmt =

Re: [sqlite] SQlite3 - SQL injection using ruby

2009-06-16 Thread John Elrick
dave lilley wrote: > Many thanks to all who have replied, > > I know understand the difference and shall use that approach to creating my > queries. > > regarding the "ruby way" it was more how I saw saving code typing by > injection different table, field and user data into one query thus saving

  1   2   3   >