Re: [sqlite] Deletion slow?

e 200 records does sound excessive (for a regular PC with database file stored on a hard drive), even considering that three indexes need to be updated. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cg

Re: [sqlite] Deletion slow?

On 2/5/2013 8:35 AM, John Drescher wrote: Put the delete in a transaction. It's a single statement, it runs in a single implicit transaction. An explicit transaction shouldn't make any difference. -- Igor Tandetnik ___ sqlite-users ma

Re: [sqlite] update to limits infomation

t. I'm not sure what point you are trying to make here. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] update to limits infomation

On 2/4/2013 8:34 PM, Igor Tandetnik wrote: On 2/4/2013 8:22 PM, YAN HONG YE wrote: I hava a table like this: id,name,score,rank 1,anna,80,0 2,qera,65,0 6,kero,90,0 10,rosa,95,0 what I would like to do is to update the rank position. update mytable set rank = (select count(*) from mytable t2

Re: [sqlite] update to limits infomation

On 2/4/2013 8:22 PM, YAN HONG YE wrote: I hava a table like this: id,name,score,rank 1,anna,80,0 2,qera,65,0 6,kero,90,0 10,rosa,95,0 what I would like to do is to update the rank position. update mytable set rank = (select count(*) from mytable t2 where t2.score <= mytable.score); -- I

Re: [sqlite] sorting two distinct groups

, what is the meaning of calls.last? Are you updating this field for all calls for a firm whenever a new call is inserted? Perhaps you are looking for something like this: select f.id from firms f left join calls c on (f.id = c.firm_id) group by f.id order by min(c.

Re: [sqlite] sorting two distinct groups

s than any other value, so rows for which there are no calls will sort at the top. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select max(x), y from table

::= ... | | ... 7.11 ::= ::= -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select max(x), y from table

by any SQL standard; and I'm not aware of any DBMS that would interpret the statement the way you want (which doesn't mean none such exists, of course). All in all, It seems unlikely that such a proposal would be entertained. -- Igor Tandetnik __

Re: [sqlite] Tracking changes with sha1 hashes

On 2/1/2013 10:21 AM, Amit Chaudhuri wrote: If I only issue select queries on my two input databases, can I expect the sha1 hash to stay the same over time? Yes. You can even mark the database file as read-only and use sqlite3_open_v2 with SQLITE_OPEN_READONLY flag. -- Igor Tandetnik

Re: [sqlite] Determine if an index has been created

On 1/31/2013 5:45 PM, Paul Sanderson wrote: Is it possible to ascertain if an index on a particular column has already been created. PRAGMA index_list(YourTable), then for each index, PRAGMA index_info(IndexName) -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] (no subject)

hic inapplicable, hopefully steering the query planner towards a more helpful index). Also, you can write the query a bit more compactly: select fileref from rtable where vsc > 0 and isgraphic = 1 and md5 not in (select md5 from rtable where isgraphic = 1 a

Re: [sqlite] FTS problem with 'NOT term' queries

inary operator, denoting set difference. You are trying to use it as a unary operator. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unique combination of concatenated column query

e resultset grows at most polynomially with the size of the database and the length of the query. But your hypothetical resultset may potentially contain O(2^N) rows - that is, it may have to grow exponentially with the size of the database. Therefore, it cannot be produced by a SQL query. QED. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] faster query - help

left join music_files using file_id where YourFilterGoesHere order by date_uploaded limit 10; An index on files(date_uploaded) would likely prove beneficial. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cg

Re: [sqlite] SELECT and UPDATE in single query

d and might as well keep the original design. If you really don't want to do that for some reason, you could create an AFTER UPDATE trigger on the table, which would call a custom function, passing each key to it as the status is being updated. -- Igor

Re: [sqlite] SELECT and UPDATE in single query

more efficiently? Something like this perhaps: update queue set status = 1 where status = 0 and key < (select key from queue where status=0 order by key limit 1 offset N); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org h

Re: [sqlite] Can these 2 queries be combined into one?

r.ISOCode, 'DEU'), lm.MaxChars, coalesce(tr.Description, '') as Translation, lm.Description FROM LanguageMaster lm LEFT JOIN Translations tr ON (tr.MasterID = lm.ID AND tr.ISOCode = 'DEU'); -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

different than using a small constant value. What kind of magic do you believe "huge numbers" possess that small numbers don't? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem whit sqlite_prepare_v2() in 3.7.5 versionandlater

according to the "Debug" or "Release" mode used. I'm not sure what you mean by "problems". Visual Studio sets the working directory to whatever you have specified under Project | Properties | Debugging. -- Igor Tandetnik __

Re: [sqlite] Problem whit sqlite_prepare_v2() in 3.7.5 version andlater

atter of SQLite using the right database file sometimes, but creating a blank new one other times. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] special aggregate feature vs query optimization

actly one row. Try it, see for yourself. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to decrease IO usage

u are using SQLite as a key-value store. Perhaps you might be happier with a real key-value store, like Berkeley DB or LevelDB, which would be optimized specifically for such use case. http://en.wikipedia.org/wiki/NoSQL#Key.E2.80.93value_stores_on_solid_state_or_rotating_disk -- Igor Tand

Re: [sqlite] Problem with sqlite3prepare16_v2

On 1/6/2013 7:10 PM, Walter wrote: sqlite3_prepare16_v2 (vMdb, ws.c_str (), ws.size (), &stmt, &tail); The third parameter of sqlite3_prepare16_v2 is the length of the string *in bytes*, not in characters. You are effectively passing only half the statement. -- Igor T

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

be sure, measure it both ways. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE - returning new or existing rowid

ountries in the world. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full trace(s) of the CREATE, INSERT and SELECT functionalities of Sqlite

your forum, please let us know the steps involved http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger question

exactly what you are trying to achieve. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trigger question

(new.name, instr(new.name, ' ') + 1) || ' ' || substr(new.name, 1, instr(new.name, ' ') - 1)); end; Though in this case, I'd personally have two separate columns instead, say firstName and lastName. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select statements using date function such as date('-1 day')

to subtract one day from. Make it date('now', '-1 day') -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Instead of triggers

uestion is: does sqlite engine know the *old value of primary field*? Yes of course - one for each row of the view being updated. > If yes, HOW? Is trigger executing once or for each row? Yes it is. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cursor question about sqlite3

ingCursor -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] strange behavior with integer with where clause

e '0' in this row, not a numeric value 0. All strings compare greater than all numbers. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Updating database views ( instead of triggers ) using typed datasets and system.data.sqlite

n the view visibly affected, which value should sqlite3_changes return? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed?

that, syntactically, they are not literals. The syntax only allows parameter placeholders where a literal (like 123 or 'xyz') may legitimately appear. That is not at all in contention. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-u

Re: [sqlite] parameters in a view, disallowed? (docs issue)

On 12/20/2012 4:10 PM, Larry Brasfield wrote: Igor Tandetnik wrote: [regarding where parameters allowed, "where literals are"] > > How did you discern this? I know from experience where parameters work (SELECT, INSERT and similar; also ATTACH as one of my projects happens to

Re: [sqlite] parameters in a view, disallowed?

I briefly scanned the rest at http://sqlite.org/lang.html to confirm that their syntax doesn't involve expressions. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parameters in a view, disallowed?

ters). None of the remaining statements allow for literals in their syntax, so the issue is moot for them. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ordering result sets

hand for case when f.nexttime IS NULL then 1 else 0 end or equivalently case when f.nexttime IS NOT NULL then 0 else 1 end BTW NULL = CHR$(0) vs '' = '' YES??? No. NULL is NULL - it doesn't compare equal to anything, not even to itself. For details, see http://e

Re: [sqlite] ordering result sets

27;' at the > beginning... Because that's what you asked for. Allow me to quote: "order results firstly by earlest *non-null/empty string* next time" (emphasis mine). You do realize that NULL and empty string are two distinct values, right? -- Igor Tandetnik _

Re: [sqlite] multiple outstanding sqlite3_stmt objects

connection. They are largely independent of each other. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] complex update

lect min(locationid) from Locations where name = (select name from Locations L where L.locationid = Products.locationId) ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sq

Re: [sqlite] ordering result sets

expression is true). The other two expressions are each designed to sort one of these groups without affecting the other. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite :Is there any subtle distinction between UNIQUE andDISTINCT?

think along these lines? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String not valid DateTime

sults into a C# DataTable.) Change your date format to '2012-12-09 22:51:24'. For more details, see http://sqlite.org/lang_datefunc.html "Time Strings" section. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org ht

Re: [sqlite] just a test

Clive Hayward wrote: > Igor's messages sometimes get marked as spam by gmail. Now trying to send from a different email address. Please let me know if this still gets marked as spam. Thanks. -- Igor Tandetnik ___ sqlite-users mailing lis

Re: [sqlite] dates and optimizations

can still eyeball them for debugging purposes. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to find table exists or not

ement to read that record from the table. If sqlite3_prepare fails, then the table doesn't exist. If it succeeds, run the statement as you normally would. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

Re: [sqlite] Custom collation method works for comparison, not for ORDER BY

fer. Your test program, of course, always happens to pass NUL-terminated strings. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] what diffrent with this two sql command?

lled correlated subquery: the results of the subquery depend on the current row in the outer query being processed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] get records for foreign keys which come from various places

select firm_id from calls1) or id in (select firm_id from calls2); -- or select * from firms where id in ( select firm_id from calls1 union select firm_id from calls2); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] get diffrent column C from table

't understand. In your example, which rows do you want returned, and why? > how to get correct result? Describe what exactly you mean by "correct result". -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory using: is it normal

ory used growths by 16Mb > > sq3lite_finalize - Memory used doesn't reduce Yes, this is normal. You are looking at the page cache. See also http://sqlite.org/pragma.html#pragma_cache_size -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite

Re: [sqlite] Foreign keys needing an index

tand > why under normal circumstances (large table) this > would be desirable. But is there anything which would /require/ it ? It's not the index that's necessary, but the UNIQUE constraint. It just so incidentally happens that SQLite uses indexes to enfor

Re: [sqlite] Audit trail question...

le. > Because the TRIGGER statement is a stored procedure I need to find a way to > access this variable. Can I use a custom function or > variable for this? Yes, you should be able to use a custom function for this. > How is this done? http://sqlite.org/c3ref/create_func

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 28

exists:b=c I don't understand. In your example, which rows do you want returned, and why? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prefix joins

do the join. The closest you can get is something along these lines: where a.c1 between b.c1 and b.c1 || x'FF'; This should use a full scan on b, and an index on a.c1 if available. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sq

Re: [sqlite] Please help. Read only process being blocked by writerprocess.

at any point in time. In WAL mode, a database may have multiple readers AND a single writer at any point in time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A bug concerning SQLite - no suport for Extended ASCII(filepath)

nvert to UTF-16. Better still, set up a Unicode build and handle all strings in UTF-16, which is the "native" encoding of Windows API, and is also directly supported by SQLite. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sq

Re: [sqlite] creating indexes on empty columns

determining that a column has empty values in all rows, any faster than looking at every row? Also, what constitutes an "empty value" - NULL, empty string, zero-length blob, integer 0, something else? -- Igor Tandetnik ___ sqlite-users ma

Re: [sqlite] SQLite converts all unicode characters into ANSI

t would you recommend, > instead? http://www.sqlite.org/cvstrac/wiki?p=ManagementTools I used SQLite 3 Explorer (http://www.singular.gr/sqlite/) and SQLite Manager (http://sqlite-manager.googlecode.com/) -- Igor Tandetnik ___ sqlite-users mailing list sqli

Re: [sqlite] SQLite converts all unicode characters into ANSI

here, even *before* you actually run your statements? Then it can't possibly be SQLite's problem - the text hasn't even reached SQLite at this point. At best, it could be a problem with SQLite Manager (which is a third-party application not developed or maintained by SQLite dev

Re: [sqlite] sqlite3 db open/close

. The behavior then is undefined. "No malfunction/corruption" is one of many possible manifestations of undefined behavior. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

re inserts or > queries, as Simon suggested, and then un-double them on the way out if data > was being retrieved from the database to be written somewhere else. You don't need this last part. The text you get from SELECT is the original, without escaping. -- Igor Tandetnik

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

rry about sufficient buffer size, and b) it recognizes %q and %Q format specifiers, which are like %s but escape single quotes appropriately. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

or do I not have this right? Is > there some reason for preferring one version over the other if both are > valid? It's all the same to SQLite. For a programmer, a query that explicitly lists the columns might be easier to read - you don't need to consult the table definition to figur

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

stahl...@dbs.uni-hannover.de wrote: > Quoting Igor Tandetnik : >> stahl...@dbs.uni-hannover.de wrote: >>> Consider these two tables: >>> >>> CREATE TABLE tab1 (x INTEGER PRIMARY KEY); >>> CREATE TABLE tab2 (x PRIMARY KEY REFERENCES tab1); &g

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

ing. > Also I don't really care *how* this is fixed As best I can tell, so far there's no agreement that it's broken, so discussing how to fix it seems a bit premature. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about date question

base? > and how to get the subtraction of two column? I'll tell you - *after* you change supplierDate column to a format that date/time functions recognize. I showed you how. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

Ngrading FROM T93C_ADL WHERE IMPLANTATIONgrading = '' and fmn like '85495%'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 59, Issue 7

ected: select date(supplierDate,'+2 days') from t93c_adl; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newest 3 entries

l' select id, eventdate, eventtype FROM eventlog WHERE eventtype in ('special') order by eventdate desc limit 3; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] couldn't use is null function

YAN HONG YE wrote: > SELECT * FROM ADL WHERE Project_grading is null; > can't select anything. So there's no row in ADL where Project_grading is in fact NULL. What makes you believe differently? -- Igor Tandetnik ___ sqlite-users mai

Re: [sqlite] Compiling SQLite3 with MSVC 2010

cessed by a C compiler, unless explicitly configured otherwise. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERT OR IGNORE safety?

Ivan Shmakov wrote: >>>>>> Igor Tandetnik writes: >> Note that ending up in the IGNORE branch of INSERT OR IGNORE >> statement doesn't constitute failure, but normal execution. The same >> is true for INSERT ... SELECT statement where SELECT returns zer

Re: [sqlite] INSERT OR IGNORE safety?

ote that ending up in the IGNORE branch of INSERT OR IGNORE statement doesn't constitute failure, but normal execution. The same is true for INSERT ... SELECT statement where SELECT returns zero rows so nothing is actually inserted. -- Igor Tandetnik

Re: [sqlite] SUGGESTION: "now" as alias for "strftime('%s','now')"

Григорий Григоренко wrote: > Compare: > MS SQL: CURRENT_TIMESTAMP SQLite does in fact accept CURRENT_TIMESTAMP in DEFAULT clause. Does this satisfy your requirements? http://sqlite.org/lang_createtable.html#tablecoldef -- Igor Tandetnik ___

Re: [sqlite] Calling "ROLLBACK" outside transaction

but the usage will be general. You could use sqlite3_get_autocommit to detect whether a transaction is in effect (autocommit is on when there is no explicit transaction, off when there is a transaction in progress). -- Igor Tandetnik ___ sqlite-users mai

Re: [sqlite] How to differentiate between sqlite database empty char* strings and DBNULL char* string?

Frank Chang wrote: > Good afternoon, Is it possible to differentiate between sqlite database > empty char* strings and DBNULL char* strings? If so, what is the est way to > do that? Thank you. You might be looking for sqlite3_column_type. -- Igor

Re: [sqlite] Union all writting on /var/tmp

4; Could the /var/tmp position that it writes to, be changed to another directory? http://sqlite.org/c3ref/temp_directory.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to select " char in sqlite

uot;select id,partnumber,' '||pic||' \" height=220/>' as img,pcs from engine where > id>7; ">> n.html Same problem. Define "couldn't work". What outcome do you observe, what outcome do you expect, and how do the two differ? -- Igor Ta

Re: [sqlite] insert if in SQL cmd

and access columns of an SQLite statement, but doesn't allow you to obtain today's date nor implement condition checks? Apart from that, what exactly do you hope to achieve by trying to access values in a row *after* you've just deleted it? This doesn't make any sense

Re: [sqlite] datetime, its customary column affinity, and storage class info

row have its > own storage class? Generally, yes. > Is it a separate bit of data associated with (and stored somewhere for) every > single item? Yes. SQLite file format is documented here: http://sqlite.org/fileformat2.html > Is there a way I can ask > sqlite what's the storag

Re: [sqlite] TRUNCATE TABLE alias for DELETE FROM

work. TRUNCATE TABLE is not a feature of SQL92. It was first standardized in SQL:2008, if Wikipedia (http://en.wikipedia.org/wiki/SQL:2008) is to be believed. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org

Re: [sqlite] A question about prepared statements

red, executed > and finalized; > 4. access column data via stmt A > 5. COMMIT http://www.sqlite.org/c3ref/column_blob.html If the SQL statement does not currently point to a valid row... the result is undefined. -- Igor Tandetnik ___ sqlite-us

Re: [sqlite] Find first non-NULL values of several columns

> own table Or else, everything in a single table, say, Readings(timestamp, sensorId, value). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A question about prepared statements

form of a cursor. Each subsequent sqlite3_step call advances this cursor forward by one row. > 4. If there is a cursor, maybe there is a way to disconnect a cursor from > stmt keeping the single record still available? No, short of making a copy of every column's value. -- Igor Tandet

Re: [sqlite] Find first non-NULL values of several columns

one that represents a sparse table more efficiently). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Find first non-NULL values of several columns

Igor Tandetnik wrote: > If the values of b, c and so on have a known upper bound, then you can write > something like > > select min(a*1000 + b), min(a*1000 + c), ..., min(a*1000 + f) from v; I mean, select min(a*1000 + b) % 1000, ... or the same with shifts and masks: select m

Re: [sqlite] Find first non-NULL values of several columns

origial statement is likely more efficient, as it can stop scanning early, whereas my variant will look at every record. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Remote connection to SQLite db

Abhinav wrote: > Does SQL lite have an option of processes connecting to it from remote hosts > with a port number? No. SQLite is an embedded database, not a client/server one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqli

Re: [sqlite] Creating a view

) from TBL; -- or select ref1.RetItem, ref2.RetItem, ref3.RetItem, ref4.RetItem, ref5.RetItem, ref6.RetItem from TBL join REFTABLE ref1 on (nR1=ref1.id) join REFTABLE ref2 on (nR2=ref2.id) join REFTABLE ref3 on (nR3=ref3.id) join REFTABLE ref4 on (nR4=ref4.id) join REFTABLE ref5 on (nR5

Re: [sqlite] find sequential groups

date final2 set group_count = (select count(*) from final2 t2 where t2.group_marker = final2.group_marker); Here, the condition of the subquery mentions a value from the outer table, so it's evaluated anew for every row being updated. See also: http://en.wikipedia.

Re: [sqlite] find sequential groups

t2 where t2.ID < t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/c

Re: [sqlite] EXT : find sequential groups

ups, not two as your approach would end up with. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] find sequential groups

esc limit 1 ) ); In prose, for each record count the number of records below it (inclusive) that are first-in-group; where first-in-group in turn is defined as "a record such that the next record down by ID has a different Value, or there is no smaller ID at all"

Re: [sqlite] inserting record datinto mutliple tables with a composite primary key

, i4 integer not null, ... unique (i1, i2, i3, i4) ); Now, you can insert a record while leaving 'id' column out, and it will be automatically assigned a unique integer value, which you can retrieve with sqlite3_last_insert_rowid. You can then use that ID when inserting record

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

't need to be contiguous. Nor does the difference between two neighboring elements have to be exactly one - it just have to be a positive number. At least as defined at http://en.wikipedia.org/wiki/Longest_increasing_subsequence E.g. for a sequence of (1, 100, 2, 4) your algorithm find

Re: [sqlite] 5. Re: Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF

On 10/15/2012 4:29 PM, Frank Chang wrote: Igor Tandetnik, So what is the purpose of this whole exercise Following the project gurus's example sequence of -- 1,2,3,4,3,5,6,7,8,10 -- the numeric sorted ascending subsequence is found to be 1,2,3,4,5,6,7,8,10 using an automatic var

Re: [sqlite] Sqlite, Is it possible to calculate the length of the longest increasing subsequence using an UDF?

of the exercise, if you don't mind me asking? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

<    3   4   5   6   7   8   9   10   11   12   >