On Wed, Aug 7, 2013 at 9:05 PM, Peter Haworth <p...@lcsql.com> wrote: > On Wed, Aug 7, 2013 at 11:20 AM, Dr. Hawkins <doch...@gmail.com> wrote: > >> On Wed, Aug 7, 2013 at 10:21 AM, Peter Haworth <p...@lcsql.com> wrote: >> It seems that a query that produces a string half a dozen lines that >> are already sql transactions, just needing BEGIN/END wrapper, would >> necessarily be faster than looping around on the received data.
> Selecting 350 rows with 350 SELECT statements is always slower than > selecting all the rows with 350 SELECT statements. It's a single SELECT, but after doing that from the remote/file database, I haven't found any way other than 350 UPDATE statements to put in in the in-memory SQLite database. I make changes on the in-memory database, and have a lurking process that watches for lack of keystrokes and synchronizes periodically. So it will find a few rows with their "changed" column set from a single SELECT, which data then immediately gets written to the remote. I can loop through each line of the remote, but I'm thinking that it would be cleaner to do my query in a way that fills out the strings, so that rather than a few rows of "A<t>B<t>C" I get "UPDATE aTable SET valA='A', valB='B', valC='C' WHERE key='thisKey';" all ready to wrap in BEGIN/END and submit. And I'm thinking that I could replace any tab&tab with tab&"NULL"&tab before the insertion into postgres issue. All in all, the maintainability and my being able to see things clearly is the most important--the time to do the looping seems small compared to the lagtime on the remote query. >> Right now, the various UPDATEs all get wrapped into a single >> transaction with BEGIN/END. >> >> I can't wrap the selects like that, due to the bug on the return values: >> >> SELECT val1,val2 FROM tableA >> SELECT val1 from tableB >> >> where tableA is all A and B is all B yields >> >> A,A <- this is correct >> B, A <- the A is left over from the prior query with more >> columns. >> > What bug? I have never experienced that. ' I found this with postgres queries; the later lines get padded to the same number of items with the results of the earlier items! > Alos , putting the SELECT > within the BEGIN/END stops anyone else updating the db at the same time, > although sounds like that;s not an issue , at last theoretically. Correct. If two people are entering data, and enter different values, one is wrong. That level of user error is beyond anything I can solve . . . (A disclaimer to "fire your dumbest employee" ? :) ) >> > As for the NULL issue, you could do two selects, one for rows where the >> > column IS NULL and one for rows where the column is NOT NULL, and format >> > the UPDATE statements accordingly. >> >> But I wouldn't know which columns were NULL until I got the results of >> the query . . . >> > > Of course you wouldn't, that's the point of the separate SELECT statements. But if I have five columns that *could* be NULL, that's 32 combinations of SELECT, or 10 SELECTs that get reassembled . . . and then an ugly assembly of each row. >> The "remote" would be a postgres server either on the same localnet, >> or wrapped with authentication over https. It may be insufficient >> imagination, but I'm not seeing how an attacker that can inject >> doesn't already have far more serious access . . . >> >> Yes, insufficient imagination is what it is. I would think you would want > to protect yourself from any hacks if you're dealing with bankruptcy > information. Oh, I definitely want it protected--but it seems that I'm protecting against someone who already has full access to the internal network, including the program. When writing user-entered data (or calculated data; for that matter), I use a preSQL() function to wrap text in quotes, escape quotes, etc. (But the current issue is straight db to db). thanks again -- Dr. Richard E. Hawkins, Esq. (702) 508-8462 _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
