Re: [sqlite] max() with LIMIT

MIT 10); This only works by accident. There's no requirement that the subselect return rows in any particular order. It can, in principle, choose any ten rows. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

le to get the highest value in a "limited column" when using > LIMIT? You seem to want the tenth smallest ID. Try this: select id from t where id > 0 order by id limit 1 offset 9; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-user

Re: [sqlite] SQLite3 linking error on 64 bit linux machine

You can't link that with your 64-bit program. You'll likely have to build 64-bit SQLite library from sources. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

ected to have a "no such table" error. Which part of the documentation might have led you to expect that? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_step causing Segmentation Fault

course is a complete nonsense. > rc = sqlite3_step(statement); // The app crashes here! This is where SQLite tries to dereference that bogus pointer for the first time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite

Re: [sqlite] Unicode Confusion and Database Size

run .dump command on the old database. Create a new database. Use "PRAGMA encoding" to set it to UTF-16. Run .import command on it using the dump file from the old one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.o

Re: [sqlite] PRAGMA user_version

On 9/2/2011 11:02 PM, Walter wrote: Is there any way to get the user_version from an Attached database PRAGMA attachedName.user_version; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman

Re: [sqlite] PRAGMA user_version

org/pragma.html "A pragma may have an optional database name before the pragma name. The database name is the name of an ATTACH-ed database or it can be "main" or "temp" for the main and the TEMP databases. If the optional database name is omitted,

Re: [sqlite] how to return these data from the DB

time 13 i will get : > > 9 z > 11 i > 13 j > > How do i do it? select * from MyTable where Time between 8 and 13; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite+ICU library and usage of LIKE

operator with no wildcards. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite+ICU library and usage of LIKE

sreekumar...@gmail.com wrote: > The _ operator( match any single char in the string) does not seem to work.. > % is ok.. Show your data and your statement. Explain what outcome you observe, and how it differs from your expectations. -- Igor Tan

Re: [sqlite] Getting the location of a record in an Index

n eloResultTable, I wish to find how far down > the eloResultScore index it is (I basically want to find a player's rank > when ordered by elo). Is there a way to do this? select count(*) from eloResultTable where elo >= (select elo from eloR

Re: [sqlite] Getting the location of a record in an Index

vide a fast way to obtain a number of all the elements smaller than a given element. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE in PRAGMA Table_Info

Thomas Baumann <softwaretoas...@yahoo.de> wrote: > can you please add a column to the result of PRAGMA Table_Info() that > indicates this column is UNIQUE? What should be reported for this table definition: create table FancyUnique(a, b, c, d, unique(a, b), unique(c, d) ); -- Ig

Re: [sqlite] Encoding and Collation

whatever they might be. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Encoding and Collation

upport for 8859? No, not really. But, again, it won't prevent you from storing 8859-encoded strings in the database, and installing a custom collation that understands them, if you are so inclined. Personally, I'd seriously consider switching to UTF-8. -- Igor Tandetnik __

Re: [sqlite] Encoding and Collation

Simon Slavin <slav...@bigfraud.org> wrote: > Though I'm having trouble pointing to a page for the SQLite3 ICU stuff at the > moment. It would be here: http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/icu/README.txt but the server seems to be down at the moment. -- Ig

Re: [sqlite] help with a complicated join of two tables

d. Let's take geo.id = 5, max_age=Cambrian, min_age=Silurian. You say you want a record whose age_bottom is greater than that corresponding to Cambrian, that is 542.; and whose age_top is smaller than that corresponding to Silurian, or 416.. I don't seem to see any such record in your examp

Re: [sqlite] help with a complicated join of two tables

f the min_age. Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom >= (select age_bottom from intervals where name = geo.max_age) an

Re: [sqlite] help to get the value of file change counter

at would have allowed one connection to indicate to another how many changes it has made. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TransactionScope ON CONFLICT

[SensorData]([SensorID], > [TimeStamp]) > VALUES (@p0, @p1);]: columns TimeStamp are not unique" > > In the data base are now 6 rows, that mean all after the failing insert are > not executed be the transaction. Is this per

Re: [sqlite] TransactionScope ON CONFLICT

transaction.Complete(); // end transaction >context.AcceptAllChanges(); And here's where you commit the transaction, regardless of whether or not it completed successfully. -- Igor Tandetnik __

Re: [sqlite] TransactionScope ON CONFLICT

cannot have influence to this loop that you mean. I'm not sure I understand this statement. What kind of "influence" do you want to exert? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] help with a complicated join of two tables

On 9/12/2011 12:02 PM, Mr. Puneet Kishor wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: Something like this: select geo.id, min_age, max_age, age_bottom, age_top, name, color from geo left join intervals i on i.id = ( select id from intervals where age_bottom

Re: [sqlite] Database schema has changed?

, in particular the difference between sqlite3_prepare and sqlite3_prepare_v2). See if there's any way to instruct the binding to clear its cache of prepared statements. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index on BOOLEAN field

et. For example, if there's a small number of "active" or recent records that need to be processed, and a large archive of "processed" records. However, in such a case, you might be even better off splitting the small subset into its own separate table. -- Igor Tandetnik

Re: [sqlite] how to compare time stamp

the table, and how you are retrieving it. Explain the results you observe, and how they differ from your expectations. What exactly do you mean by "not correct"? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org ht

Re: [sqlite] Index on BOOLEAN field

, then the index won't help > much. Actually, the break-even point is roughly 1/10: an index helps if you are selecting 10% or fewer of the records in the table; otherwise, a linear scan is faster. -- Igor Tandetnik ___ sqlite-users mailing list sqlite

Re: [sqlite] help with a complicated join of two tables

On 9/14/2011 2:07 PM, Jan Hudec wrote: On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: select geo.id, min_age, max_age, age_bottom, age_top, name, color >from geo left join intervals i on i.id = ( select id from intervals where age_bottom>= (select age_bottom from int

Re: [sqlite] No error on selecting non-grouped column

"MANAGER" "KING", "PRESIDENT" "TURNER", "SALESMAN" Now, I get a list of the jobs, and a random selection of employees. I would have expected an error here. It's a SQLite-specific extension. Very useful in certain cases. So getting an

Re: [sqlite] Does coalesce terminate early?

ortunately, coalesce doesn't do it in SQLite. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does coalesce terminate early?

customFunction()) I definitely see customFunction() called even when someField is not null. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

' WHERE shortdesc='get hp50 g calculator batteries'; sqlite> sqlite> SELECT DISTINCT shortdesc FROM todolist ORDER BY shortdesc ASC; calc batteries sqlite> Looks OK to me. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regexp within trim function

characters to trim? I've tried \' and \" without success. You include double quotes as-is. You escape an apostrophe by doubling it up: 'Here''s a "quote"' -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select where date < 30 days ago

DATETIME('now','-30 days'); > > but it does not give the intended results. Show your data, show the results you get from the statement, and explain how the observed outcome differs from your expectations. -- Igor Tandetnik ___ sqlite-users mailing lis

Re: [sqlite] select where date < 30 days ago

rd to write where time < strftime('%s', 'now', '-30 days') * 100 -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem LEFT OUTER JOIN and string data fromright table

s lightning fast. > > result of query is > just around 40 rows, (due to Parent filter) You may want an index on A(Parent) then. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem LEFT OUTER JOIN and stringdatafromright table

same column in query which >>> only has B table in it is lightning fast. >>> >>> result of query is >>> just around 40 rows, (due to Parent filter) >> >> You may want an index on A(Parent) then. > > A.Parent is has

Re: [sqlite] INSERT syntax missing key SQL syntax

Jim Michaels <jimm-VsnNql4zhRrV7NJZ79vff+jYdJvx94icpqFO/160wmvqt0dzr+a...@public.gmane.org> wrote: > INSERT is supposed to handle multiple rows for VALUES. Supposed by whom? What is the basis for this claim? -- Igor Tandetnik ___ sql

Re: [sqlite] UPDATE of field deletes record

ual, and should be a standard > feature). Should it be? In which standard is this feature mandated? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem LEFT OUTER JOINandstringdatafromright table

E at all - it's a no-op. #1 uses more fields from ItemsME, so it needs to actually look up and read records from that table. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] julianday and unixepoch

en > when it's not provided? It doesn't. > JD 2452514.50 is > CE 2002 August 28 00:00:00.0 UT Wednesday > > JD 2452514.00 is > CE 2002 August 27 12:00:00.0 UT Tuesday Looks OK to me. What again seems to be the problem? > Or did I miss something? Apparently. -- I

Re: [sqlite] Could someone explain why this query is so slow

gt; is there anyway of speeding this up? A single index on (md5, afo) may help. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Could someone explain why this query is so slow

ter where tbl_name='rtable'; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] c-api document suggestion

the NUL terminator, if you want it to actually be stored in the database. whereas prepare apparently expects it to include the null termination. What makes you believe that? As far as I know, it should still work if you don't include it. -- Igor Tandetnik

Re: [sqlite] c-api document suggestion

Mira Suk <mira@centrum.cz> wrote: > On 9/21/2011 21:22 Igor Tandetnik wrote: > >> You can include the NUL terminator, if you want it to actually be stored >> in the database. > > Actually you can't - if you do all SQL string functions will not work. > to b

Re: [sqlite] DISTINCT on a JOIN

d-in-course3 > course4 number-of-people-involved-in-course4 > number-of-tasks-involved-in-course4 select name, (select count(*) from people where course=courses.id), (select count(*) from tasks where course=courses.id) from courses; -- Igor Tandetnik

Re: [sqlite] c-api document suggestion

null included in size returned No. But your own (if any) is. > are my nuls removed from string size or not ? No. You get back exactly the sequence of bytes you put in. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Do I still need an index if I have a composite Primary Key(or UNIQUE)

ry key? Yes. The order you have them in is will suited for this query. > i.e.: if no additional index is needed, would it still work, if > the primary key was "PRIMARY KEY ( Order, IconID )" ? This index could still be used, but only to satisfy ORDER BY cl

Re: [sqlite] Index usage when querying views

ite's optimizer isn't really that smart - definitely not smart enough to move the condition into the sub-select and duplicate it into each subquery. That's a rather non-trivial transformation. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-user

Re: [sqlite] binary key, blob or text?

hashblob not null collate binary, -- as the raw byte sequence Collation doesn't apply to blobs - they are always compared as binary. > Any gotchas in using blob as keys (unique or otherwise)? None that I know of. -- Igor Tandetnik __

Re: [sqlite] how to disable a trigger

Sam Carleton <scarle...@miltonstreet.com> wrote: > Is there any way to "disable" a trigger in sqlite? DROP TRIGGER -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to disable a trigger

='MyTrigger') begin ... end; where TriggerControl(name text, enabled integer) is a table with a row for each trigger you want to manage. You can effectively turn a trigger on and off with update TriggerControl set enabled=? where name='MyTrigger';

Re: [sqlite] how to disable a trigger

me' and Type = 'trigger' And be careful to run this statmenet *before* you drop the trigger. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to do automatic rollback on any error?

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

Re: [sqlite] ensuring uniqueness of tuples spanning across multipletables?

NTEGER PRIMARY KEY, value INTEGER NOT NULL); create table fooDetails( key INTEGER PRIMARY KEY REFERENCES "foo" (key), L integer not null, value integer not null); That's what a fully normalized schema would look like for your data. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to do automatic rollback on any error?

tial state if any command or COMMIT fails. That's what ROLLBACK is for. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to do automatic rollback on any error?

ransaction is automatically committed. Fruther, since there's only one statement in the transation, there's no difference in behavior between the default ABORT clause and ROLLBACK clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] DateTimeOffset in SQLite

Steffen Mangold <steffen.mang...@balticsd.de> wrote: > how to use DateTimeOffset with Sqlite, if it is possible? What's DateTimeOffset? Offset from what to what? What exactly are you trying to achieve? See if this helps: http://www.sqlite.org/lang_datefunc.html -- Igor

Re: [sqlite] Using COUNT(*) in unusual circumstances

COUNT(*) > give the wrong result ? No. COUNT(*) counts the number of rows, regardless of what those rows contain. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is is possible to optimize this query on a very large datatbase table?

SELECT MIN(rowid) FROM > BlobLastNameTes > t where FieldName = t1.FIELDNAME); This query doesn't do what you seem to think it does. If it works, it's only by accident. You probably want something as simple as select FieldName, min(rowid) from BlobLastNameTest group by FieldName; -- Igor Tandetni

Re: [sqlite] pzTail parameter to sqlite3_prepare_v2()?

hat the original strings from which they were compiled just happened to reside in the same char[] array. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to optimize this query on a very large datatabase table

d out - I missed this detail on the first reading). For that same reason, the whole GROUP BY and sub-select dance is completely pointless. Your query is just a very elaborate and wasteful way to write select FieldName, rowid from BlobLastNameTest; -- Igor

Re: [sqlite] Is it possible to optimize a query on a very large database table?

e, rowid from BlobLastNameTest; Explain the problem you are trying to solve, *not* your proposed solution. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to design this table?

ns do you need to perform on said data? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE LIMIT clause

implementation detail. If you rely on a particular property of a resultset, it's best to request it explicitly, rather than hoping that the implementation just happens to tilt your way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.

Re: [sqlite] Is it possible to optimize this query on a very large database table.

Frank Chang <frank_chan...@hotmail.com> wrote: > Igor Tandetnik, > The fieldname groups in our BlobLastNameTable consist of > multiple rows where each pair of columns [FieldName, > BLOB[Vertices]] is unique. How so? You have FieldName declared as

Re: [sqlite] Faulty acceptance of non-aggregate value that is not ingroup by part of the SELECT statement

n convenient. > The result is strange and misleading and can easily lead to data errors. If you don't like this facility, don't use it in your queries. No one's forcing you. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org ht

Re: [sqlite] Is it possible to optimize this query on a very large database table

//sqlite.org/c3ref/create_function.html , the description of xStep and xFinal parameters, for an explanation of how to set up a custom aggregate function. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Faulty acceptance of non-aggregate value that isnot ingroup by part of the SELECT statement

it could have a practical use to > have an arbitrary value in the group returned together with the total number > of records across all the groups. It's not useful in your specific query, but it is useful in others. Yours is not the only system in the world that uses

Re: [sqlite] Faulty acceptance of non-aggregate value that is notin group by part of the SELECT statement

is behavior off and enforce stricter syntax rules, I wouldn't be against it. I'd likely just never use it. Please feel free to try and convince SQLite developers (of which I'm not) to add such a pragma (but don't expect me to pitch in for the cause). --

Re: [sqlite] Speed of sorting - diff between integer and string

there's less disk I/O to perform. And disk I/O is were the bottleneck is, most of the time. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is it possible to optimize this query on a very largedatabase

ldn't report the result until the whole group is processed and xFinal is called. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create sqlite3_value * from scratch?

cts sqlite3_value from raw data would have to take the type specifier anyway, wouldn't it? You would just be moving the same logic to another place. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OFFSET Performance

Fabian <fabianpi...@gmail.com> wrote: > This query returns the results as expected, and performs well. But as soon > as I raise the OFFSET to a large value (for pagination) the performance > drops drastically. See if this helps: http://www.sqlite.org/cvstrac/wiki?p=Scrolling

Re: [sqlite] Prepare statement in separate function

n size, and can be treated similarly. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OFFSET Performance

Fabian <fabianpi...@gmail.com> wrote: > 2011/10/12 Igor Tandetnik <itandet...@mvps.org> > >> >> See if this helps: http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > > > I tried to implement the method as suggested in the article, but it will &g

Re: [sqlite] Time and date functions

thing like this: between date('now','Weekday 0', '-7 days') and date('now', 'Weekday 0', '-1 days') That's always the nearest previous Sunday through the following Saturday (which may be in the past or in the future). Adjust to taste. -- Igor Tandetnik ___

Re: [sqlite] Time and date functions

fact, Y <= Z. Otherwise the condition won't hold for any X. In your original post, you ended up with Y > Z. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3_step

ke some extra time. These decisions are made by sqlite3_prepare, before the first sqlite3_step. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3_step

Sreekumar TP <sreekumar...@gmail.com> wrote: > I do not have a ORDER BY , only a WHERE clause. So sorting should not be > the cause for the overhead. Show your query, and the output of EXPLAIN QUERY PLAN on it. -- Igor Tandetnik ___

Re: [sqlite] "is not null" and index

this task? > How can indexes be used with "not null" queries? They cannot. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "is not null" and index

null" things there are...if not > many of them this may not be any faster. In fact, this will always be strictly slower than a straightforward table scan. > A count() could be a lot faster though I'd think. How so? I'm not even sure how you would use count()

Re: [sqlite] "is not null" and index

tain NULL in this column, I'd consider splitting the data into two tables - one with three columns (containing all non-NULL rows from the original table) and the other with two columns (containing the remaining rows). -- Igor Tandetnik ___ sqlite-us

Re: [sqlite] "is not null" and index

The fact that all NULL values are clustered together in rows with sequential rowids might have skewed the results in your test. Better locality of reference, fewer pages to read from disk, improved cache utilization. -- Igor Tandetnik ___ sqlite-use

Re: [sqlite] UPDATE question

at least on STAYSPEC(staynum) ), as well as one on STAYHOSP(staynum) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How about a proper forum rather than an e-mail list

to believe that an NNTP newsgroup *is* the proper forum. Can't stand modern Web-based forum interfaces. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql statement to update the data in the table

arentheses: update vpn set password = AES_ENCRYPT((select password from mytable), "abcddsfddafdasfddasd"); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql statement to update the data in the table

On 10/19/2011 7:23 PM, Joanne Pham wrote: update vpn set password = AES_ENCRYPT((select password from vpn) , "abcddsfddafdasfddasd"). I suspect you want update vpn set password = AES_ENCRYPT(password, 'abcddsfddafdasfddasd'); -- Igor

Re: [sqlite] sql statement to update the data in the table

with the author of custom functions AES_ENCRYPT and AES_DECRYPT. They are not part of SQLite proper, you must be using some kind of third party extension library. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi

Re: [sqlite] string conatenated sql statements

ive operation, it's beneficial to run it once and reuse the statement many times with different parameters. Plus the time you save on not having to pre-process the strings, plus the peace of mind knowing that you haven't accidentally missed a spot where such pre-processing would be

Re: [sqlite] string conatenated sql statements

live system, right? A malicious developer with access to the codebase would likely have lots of ways to wreak havoc, with or without prepared statements. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bi

Re: [sqlite] Concurrent readonly access to a large database.

hard drive only has one set of heads. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with binding parameters to LIKE

word FROM words WHERE word LIKE 'word'%' > ORDER BY freq DESC LIMIT 10;"; That can't be right - there's an extra apostrophe before % sign. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UNIQUE constraint

efficiently? I suspect the way you are going to manually filter duplicates will involve inserting them into a moral equivalent of a UNIQUE index. The performace is probably going to be similar to that of just creating a UNIQUE index up front. But only measurements with your particul

Re: [sqlite] Order by creating function is not sorting for input order and alphabet

be passing SQLITE_TRANSIENT as the last parameter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] triggers : NEW keyword with multiple tables

; CREATE TRIGGER my_trigger INSTEAD OF INSERT ON my_view BEGIN INSERT INTO table1(type) VALUES(NEW.table1.type); INSERT INTO table2(type) VALUES(NEW.table2.type); END; Try NEW."table1.type" and NEW."table2.type" -- Igor Tandetnik ___

Re: [sqlite] Remove from mailing list please.

it is again: 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] ORDER BY disregarded for GROUP BY

the ordering when grouping. I'm pretty sure MySQL would produce an error on your query. I don't believe it allows a column that is neither in a GROUP BY clause nor in an argument of an aggregate function. -- Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] Referring to column alias

al within the SELECT. This is by design, blessed by SQL-92 standard. The closest you can get is something like SELECT Total, Total * price FROM (select col1 - col2 as Total, price from tst); This will likely be noticeably slower though. -- Igor Tandetnik ___

<    1   2   3   4   5   6   7   8   9   10   >