Re: [sqlite] Latin-1 characters cannot be supported for Unicode

http://www.joelonsoftware.com/articles/Unicode.html . -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Latin-1 characters cannot be supported for Unicode

is not in fact a valid byte sequence in any Unicode encoding - neither UTF-8 nor UTF-16 nor any other. If you want Unicode data in your database, then store Unicode data, and not ANSI, in your database. -- Igor Tandetnik ___ sqlite-users mailing list sqlite

Re: [sqlite] Check if file exists in SQL syntax?

- but it provides a way for you to create your own custom functions. So you can write one. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] following a trail of references

, previousid, location from path join mytable on (path.previousid = mytable.id) ) select * from path; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] decoding a bitmask

- so of course it only checks out when the other side of the comparison is also a one-bit value. You want CASE WHEN visits.transition & 0x0080 THEN 'Blocked' ELSE '' END -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

.PlanDate = '2014-02-13' order by (strftime('%s', r2.End) - strftime('%s', r2.Start)) desc limit 10 ); It'll probably be noticeably slower than your unrolled query, though. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http

Re: [sqlite] Make a database read-only?

on their hard drive in the privacy of their home? In any case, you can't really stop them from doing whatever they want with their own file, even if that file started life as a copy of yours. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users

Re: [sqlite] [SQL Query] Top 10 of 5 different groups sorted by length

query uses neither views nor CTE. I'm a little curious about how a comment on the former could be construed to reflect in any way on the latter. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin

Re: [sqlite] Make a database read-only?

On 10/14/2014 10:12 AM, John Hascall wrote: Some code you may find useful to enforce the readonly byte Of course, anyone smart enough to change the byte from read-only to read-write before making changes, would also be smart enough to set it back afterwards. -- Igor Tandetnik

Re: [sqlite] Make a database read-only?

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

Re: [sqlite] Search query alternatives.

UNION ALL - it's much cheaper (this is assuming you insist on keeping multiple tables). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inmemory database in sqlite

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

Re: [sqlite] Inmemory database in sqlite

for in memory databases right ? None, naturally. That would defeat the whole purpose - the database would no longer be in-memory. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite

Re: [sqlite] WHERE expression with operators from text functions?

, instr(tail || '+', '+')-1), substr(tail, instr(tail || '+', '+') + 1) from split where tail != '' ) select * from mytable where not exists ( select str from split where str is not null and value not like '%' || str || '%' ); -- Igor Tandetnik

Re: [sqlite] group question

conditions on the underlying table rows, before aggregation is performed. If you want to only report groups containing exactly three rows, use HAVING clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi

Re: [sqlite] WHERE expression with operators from text functions?

ccepted". While possible (if I recall correctly, SQL with CTE is Turing-complete), I would not recommend it in practice. Do use FTS, it was designed for this kind of queries. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http:/

Re: [sqlite] Clarification on sqlite handling of mixed aggregate and non-aggregate columns

On 10/29/2014 5:42 PM, Baruch Burstein wrote: SELECT max(a), b FROM t WHERE a<50; Is there some way to filter *after* this is applied? Wrap it in another select: select * from ( SELECT max(a) maxa, b FROM t WHERE a<50 ) where b is not null; -- Igor Tan

Re: [sqlite] Stuck and drowning trying to work out some SQL.

null end) Start_Time, min(case State when 'Closed' then Time_Event else null end) End_Time from Table_1 group by Disruption_id; Might be faster as it doesn't require joins and works in a single pass. Both queries would benefit from an index on Disruption_id. -- Igor Tandetnik

Re: [sqlite] Stuck and drowning trying to work out some SQL.

On 11/1/2014 11:52 AM, Luuk wrote: Is the 'else null' part needed??, or can it be deleted Yes, it can be removed. CASE expression returns null when no case matches. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http

Re: [sqlite] How to check if a record exists

t/present). May work faster than the variant using count() if there are many records satisfying the condition (EXISTS stops as soon as it finds the first matching record), but that may not be a consideration in your case (DatasetID sounds like primary key)

Re: [sqlite] SQLite where clause tree

/ * /\ 5 4 This tree makes no sense whatsoever - it somehow has a literal as an inner node, with two children. In a correct expression tree, literals would be in the leaves and operators in the inner nodes, with each subtree representing one operand. -- Igor Tandetnik

Re: [sqlite] Triggers and CTE's

ect wk + 1 from Weeks limit 10) select wk from Weeks; This works both ways. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Triggers and CTE's

On 11/11/2014 8:37 PM, Richard Hipp wrote: On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik <i...@tandetnik.org> wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: Looks like a bug to me. The statement works standalone, but not within a trigger. There are many limitations and restri

Re: [sqlite] cnt(x) what do the brackets signify

On 11/12/2014 12:52 PM, Paul Sanderson wrote: I have googled but can't see what cnt(x) actually signifies - cnt is not a function The same thing it signifies in "CREATE TABLE cnt(x);" "cnt" is an "ephemeral" table with a single colum

Re: [sqlite] [SQLite]Basic queries

code on win7 32bits OS. Though I haven't specified any compile option, in this case, For which OS platform is the generated binary? for 32bits win OS or 64bits win OS? Depends on which compiler you built it with - 32-bit or 64-bit. -- Igor Tandetnik

Re: [sqlite] Is this a bug? autoincrement in int primary key vs integer primary key

On 11/16/2014 10:51 AM, Paul Sanderson wrote: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY No it's not a bug. AUTOINCREMENT is only allowed on INTEGER PRIMARY KEY. Which part of the error message do you find unclear? For details, see http://www.sqlite.org/autoinc.html

Re: [sqlite] Serializing an object's vector or array using sqlite3 in c++

On 11/21/2014 12:52 AM, Thane Michael wrote: I've been searching for a way to serialize an object's vector using sqlite3 There's nothing in sqlite3 that would help (or hinder) this task. What made you believe otherwise? -- Igor Tandetnik

Re: [sqlite] Testing the 'I' in ACID

On 11/25/2014 5:32 PM, Simon Slavin wrote: SQLite doesn't support massive concurrency because it locks the entire database during changes. Not entirely true. WAL mode allows one writer working concurrently with multiple readers. -- Igor Tandetnik

Re: [sqlite] Testing the 'I' in ACID

as the article defines "serializable" as "behaving as if executed serially", transactions in SQLite are trivially shown to be serializable - because they are, in fact, forced to be executed serially. -- Igor Tandetnik ___ sqlite-users

Re: [sqlite] creating trigger to handle multiple types of insert

, ...) VALUES( (case when new.EventNodeId < 0 then null else new.EventNodeId end), ...); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] appending the output of a query

rouped in the right order, but it's very likely to work in practice. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] appending the output of a query

, parent, FileName FROM rtable WHERE ID = 510 UNION ALL SELECT rcte.level + 1 as level, rtable.parent, rtable.FileName FROM rcte JOIN rtable ON rcte.parent = rtable.ID WHERE rtable.FileName <> '.') SELECT FileName FROM rcte ORDER BY level desc; -- Igor Tan

Re: [sqlite] How do I update multiple rows in a single sql statement

row in temp_table matches one row in some_table. In other words, it updates no rows, or all rows - never just some. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I update multiple rows in a single sql statement

in the current row of temp_table - it's either always true, or always false. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I update multiple rows in a single sql statement

three-conjuncts condition twice - once in SET id=, and again in WHERE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I update multiple rows in a single sql statement

or REPLACE INTO may sometimes be pressed into service). But I, for one, kinda miss UPDATE ... FROM. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/9/2014 10:38 AM, James K. Lowden wrote: If the subquery to the right of the SET clause produces more than one row, the statement fails. Are you sure? Normally, a scalar subquery doesn't fail when the resultset contains more than one row - it just silently produces the value from the

Re: [sqlite] Encrypted database

precisely so that it can't be opened by other tools, only by your application, haven't you? If not, what was the goal of the exercise? If you want the database to be accessible via any SQLite database management tool, don't encrypt it. -- Igor Tandetnik

Re: [sqlite] Trigger not working with empty table [reproducible]

you meant something like WHEN new.last_price NOT IN (SELECT last_price ...) -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this date supposed to be less than or more than the other?

On 1/2/2015 4:54 PM, J Decker wrote: select * from messages where received < datetime( 'now', '-3600' ) datetime( 'now', '-3600' ) returns NULL; the second parameter is not a valid modifier string. Most comparisons with NULL values report false. -- Igor Tandet

Re: [sqlite] Time Zone Conversions

ore clauses to taste else '+0' end) || ' hours'); But first, you would need to change your timestamp format to one recognized by SQLite, e.g. '2014-04-11 02:00:00' -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://

Re: [sqlite] Error while executing query: no such column: t1.*B.Switch-Tower-Sector

ATE t2 SET [*B.ANT_ORIENTATION] = (SELECT t2.ANT_ORIENTATION FROM t2 WHERE t2.[*SSID-CELLID-SECTOR] = t1.[*B.Switch-Tower-Sector]); Before you can use a table name elsewhere, you must introduce it in a FROM clause (or INSERT INTO, UPDATE or DELETE clause). -- Igor

Re: [sqlite] aggregate functions with DISTINCT

On 1/12/2015 9:53 AM, Dominique Devienne wrote: My little brain has no idea how the "a;b:c/c,d" came about from the input rows, so I don't find it logical at all myself... Simple, really. For each ('x', '@') row, string_agg adds '@x' to the resulting string (except the separator is omitted

Re: [sqlite] aggregate functions with DISTINCT

Clemens example. What do you mean by "order of the "value" column"? The order in which rows happened to be inserted into the table? Why should that order matter for anything? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] adding two tables together

to t1 so that t1 now hasa|b|c|d|e|f insert into t1 select 'a', 'b', 'c', * from t0; -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Truncation of floating point numbers in SQLite?

, round-trip through a string representation at any point? I'm pretty sure that, if you put a value into a column with correct affinity using sqlite3_bind_double, you'd get the exact same value out using sqlite3_column_double (signalling NaN possibly excepted). -- Igor Tandetnik

Re: [sqlite] Truncation of floating point numbers in SQLite?

it "signaling"), depending on how FPU is configured; which in turn could happen merely by passing it to a function, or returning from one. Quiet NaNs should round-trip just fine. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlit

Re: [sqlite] Custom functions, variable parameters, and SELECT triggers

this. Why can't the trigger call myFunc(new.colA, new.colB)? You can write a variadic custom function (one that can be called with an arbitrary number of arguments), if that's what you are asking. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-use

Re: [sqlite] expected performance of selecting large numbers of records

ed is a quick count, then I would suggest adding an ON INSERT and ON DELETE triggers that would update a count stored in a separate, singleton table. I'm not sure what you mean by "dump an approximate snapshot". -- Igor Tandetnik ___ sqlite

Re: [sqlite] regarding looping in vdbe for sqlite table joins!

tables in joining operation. The relationship between em and lo is one-to-one. The engine is most likely looping over one, and looking up rows in the other by primary key. Same with idv and mny. -- Igor Tandetnik ___ sqlite-users mailing list sqlite

Re: [sqlite] Bug in SQLite FLOAT values

On 2/7/2015 8:47 AM, Abdul Aziz wrote: Thanks for replybut I am now using VARCHARS, then how this is working? not generating any errors? When Tim said "Read this", he meant it. http://www.sqlite.org/datatype3.html answers your questions (but only if you read it). -- Igor

Re: [sqlite] SQL Help

On 2/9/2015 7:55 AM, R.Smith wrote: Which of course works fine if you have a predeterminable set of columns to extract, such as Months in the above case. How can I do this sort of thing (i'm only interested in similar results, the method is not important) for an indetermintate set of columns?

Re: [sqlite] binding multiple values in a query

These approaches use stock capabilities of SQLite and don't require heroic efforts (like patching source or implementing a virtual table). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT ... FOR UPDATE

On 5/24/2016 1:34 PM, Scott Doctor wrote: SELECT field FROM table FOR UPDATE; My question is, looking through the sqlite documentation, it is not clear how sqlite will handle that statement. SQLite will report a syntax error. That's pretty easy to confirm experimentally. -- Igor Tandetnik

Re: [sqlite] Fwd: Messages posted on Nabble not getting to list

, and their support fixed it. Like you, I'm not an admin or anything, just a regular lurker. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] error during sqlite_bind

On 4/7/2015 8:04 PM, Kumar Suraj wrote: > if ( (rv = sqlite3_prepare_v2(sqlHandle->db, command, strlen(command), > , NULL) ) != SQLITE_OK ) command: undeclared identifier. -- Igor Tandetnik

[sqlite] error during sqlite_bind

e preparing is not the query you think you are preparing. Print "command" and strlen(command) right before sqlite3_prepare_v2 call, to double-check. -- Igor Tandetnik

[sqlite] building SQLite DLL with Visual C++

On 4/21/2015 11:01 AM, Jay Smith wrote: > Before I sent the last message I had signed up to become a user. > My previous message was bounced. WHY I, for one, have received both your original and this new message. -- Igor Tandetnik

[sqlite] CSV excel import

quot;"". False. You have conveniently neglected to mention the very next sentence in the document: "If fields are not enclosed with double quotes, then double quotes may not appear inside the fields." Thus, "" is unambiguously an empty string, and """""" is unambiguously a string consisting of two double quotes. There are many real problems with CSV - no need to make up imaginary ones. -- Igor Tandetnik

[sqlite] CSV excel import

r in their interpretation of this line. There is no particular set of values that the parser "must" end up with, assuming you use the word "must" with the meaning specified in RFC 2119. -- Igor Tandetnik

[sqlite] how to detect when a table is modified ?

er to a user data structure ? Essentially, yes. It's an arbitrary value of your choice, that is passed right back to your callback, as its first argument. From the documentation: "The first argument to the callback is a copy of the third argument to sqlite3_update_hook()" -- Igor Tandetnik

[sqlite] Connection string

rstand SQLite database file format, or to know how to use SQLite API? This is precisely the problem that ODBC was designed to solve. By making "no ODBC driver" a requirement, you are painting yourself into a corner.-- Igor Tandetnik

[sqlite] order by not working in combination with random()

l" for all the calculations necessary to produce a single row - not for the whole SELECT statement, as you seem to have previously implied. -- Igor Tandetnik

[sqlite] why I don't get an error ?

On 8/26/2015 11:51 PM, Nicolas J?ger wrote: > my error is obvious, but why sqlite doesn't return an error ? http://sqlite.org/datatype3.html -- Igor Tandetnik

[sqlite] why I don't get an error ?

gid type system, with which to compare. -- Igor Tandetnik

[sqlite] autoincrement field

On 8/27/2015 5:25 PM, Levente Kovacs wrote: > Is there any way to get the 'id' of newly inserted row? http://www.sqlite.org/c3ref/last_insert_rowid.html http://www.sqlite.org/lang_corefunc.html#last_insert_rowid -- Igor Tandetnik

[sqlite] maybe bug in regexp and replace with newlines?

in SELECT clause. The reason it doesn't work is that, with most regular expression engines, unless certain flags are used, "." (period) doesn't match newline characters. -- Igor Tandetnik

[sqlite] NOP INSERT still writes to the DB/journal

no-op inserts. -- Igor Tandetnik

[sqlite] maybe bug in regexp and replace with newlines?

r, I suspect it gets converted to the string '10' -- Igor Tandetnik

[sqlite] Selecting total and not used in one query

(Total - Used) AS Free select count(*) Total, sum(used is not null) Used, sum(used is null) Free, sum(any_boolean_condition) CountSatisfyingCondition from proverbs; -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

On 12/14/2015 2:21 PM, Bart Smissaert wrote: > Not sure if I need to call sqlite3_free after running sqlite3_result_text or > if sqlite3_free should be an argument (last one) in sqlite3_result_text. That depends on how the memory was obtained that the second argument points to. --

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

ast parameter - the pointer returned by sqlite3_value_text is only guaranteed to be valid until the custom function returns. > or it could be a pointer to a locally declared variable In this case, you would also use SQLITE_TRANSIENT. -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

he other, as far as I can tell. I suspect you need sqlite3_result_text16 instead. Also lPos-2 looks wrong. Can't the substring be found at lPos == 1 ? -- Igor Tandetnik

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

function, so that SQLite knows that the string is in fact Unicode. -- Igor Tandetnik

[sqlite] batch or one by one?

gether. If you look at the implementation of sqlite3_get_table, it works by calling _prepare and _step and so on. It's not some kind of alternative interface to SQLite, merely a wrapper. -- Igor Tandetnik

[sqlite] batch or one by one?

r code. -- Igor Tandetnik

[sqlite] GROUP BY with self join

Items join Descriptions group by NAME; -- or select NAME, (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1) > 0), (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2) > 0) from Items; The first one would probably work faster. -- Igor Tandetnik

[sqlite] Can this be sorted?

On 2/15/2015 6:54 PM, Bart Smissaert wrote: > Result is shown below. > > *Drug**Sensitive* > *Resistant**Ratio*Ertapenem 10201Meropenem301Pip/Tazobactam301 > Cefalexin/Cefdrxl4070.85Gentamicin310.75CiprofloxacinS1570.68Amoxicillin2115 >

[sqlite] Trigger help or how to update id based on column content

trieve a unique RateType in step 1, then it should be good enough to identify a unique record to update in step 2. Why do you believe an extra condition of (RateType = '10%') is necessary? -- Igor Tandetnik

[sqlite] Trigger help or how to update id based on column content

n't know how to grab that without this step. Where is this information coming from? How do you know that RateType = '10%' corresponds to 0.20 multiplier? How is your hypothetical trigger supposed to know that? -- Igor Tandetnik

[sqlite] Trigger help or how to update id based on column content

ive, add conditions in the last WHERE clause to taste. -- Igor Tandetnik

[sqlite] misleading note in the documentation for WAL

hen the > log file will not be restarted? "Begins a transaction" != "is making use of the WAL". -- Igor Tandetnik

[sqlite] Reference material for SQLITE3

On 2/21/2015 1:43 PM, russ lyttle wrote: > I'm new to sqlite and would like some intermediate to advanced tutorial > and reference material. http://sqlite.org/docs.html -- Igor Tandetnik

[sqlite] Backup API and WAL

step sees is the same data that a regular SELECT issued on the same connection would see; it reflects all changes made by any committed transactions. Some of that data may be physically located in the main DB file, and some of it in the WAL file. -- Igor Tandetnik

[sqlite] Err

On 2/24/2015 12:42 PM, Jonathan Camilleri wrote: > Unusual output when trying a SQL Select statement from the command line So what's unusual about it? What specifically seems to be the problem? -- Igor Tandetnik

[sqlite] Sqlite subqueries

On 2/24/2015 4:37 PM, russ lyttle wrote: > I'm trying to create a field in a table to hold the name of a second > table, then retrieve that name for use. You can't. SQL doesn't work this way. Reconsider your design. -- Igor Tandetnik

[sqlite] Sqlite subqueries

indicator - a value that indicates which of the three tables this row originated from. Now, in table "a" store this indicator where you planned to store the table name. -- Igor Tandetnik

[sqlite] Sqlite subqueries

s as to which table in b.db > to use. What failure mode do you envision that would be avoided by this design? -- Igor Tandetnik

[sqlite] sqlite3_column_count and sqlite3_data_count

row (sqlite3_step was called, and returned SQLITE_ROW). Why both are needed, I'm not sure. -- Igor Tandetnik

[sqlite] With recursive question

e that you made the root a parent of itself. Forces the query to make an extra check to avoid infinite recursion. -- Igor Tandetnik

[sqlite] With recursive question

ileId, FileDirID ancestor, '' as path from Files union all select FileId, ParentID, '/' || DirName || path from FileDirs join Dirs on (ancestor = DirId) where DirID != 0 ) select FileId, FileName, path from FileDirs join Files using (FileId) where ancestor = 0; -- Igor Tandetnik

[sqlite] Suggestions for Fast Set Logic?

yword='animal' and rating > 3; ? -- Igor Tandetnik

[sqlite] Adding PRAGMA=UTF-8 makes INSERT slower?

when you say you "switched to UTF-8" - you probably haven't. -- Igor Tandetnik

[sqlite] Comparing same data in two tables in two separate databases

On 7/21/2015 8:54 PM, Hayden Livingston wrote: > I would like to compare two tables (of the same name) in two separate > database files. Are you looking for ATTACH DATABASE ( http://www.sqlite.org/lang_attach.html ) ? -- Igor Tandetnik

[sqlite] changed time zone

able (with _putenv or SetEnvironmentVariable ) and call _tzset -- Igor Tandetnik

[sqlite] changed time zone

On 7/23/2015 9:10 AM, Bruno Schw?gli (CTModule AG) wrote: > TZ is not used and is not set in our environment. It is, however, used and set by C run-time, which is in turn used by SQLite. In any case, _tzset() is how you tell C run-time to re-read time zone settings from the OS. -- I

[sqlite] Possible substr() optimization?

On 7/23/2015 10:17 AM, Staffan Tylen wrote: > Please consider this: > > create table tbl1 (col1 text primary key); > insert ... > select * from tbl1 where substr(col1,1,1)='a'; Make it WHERE col1>='a' and col1 < 'b' This should use the index. -- Igor Tandetnik

[sqlite] Possible substr() optimization?

. ('b' BETWEEN 'a' and 'b') is true. -- Igor Tandetnik

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

other words, it's equivalent to SELECT fpath FROM home WHERE fpath NOT IN (SELECT fpath FROM work) AND home.ftype = 'f?; -- Igor Tandetnik

[sqlite] Read strings as they were

do you declare it with INTEGER type? Make it TEXT. > INSERT INTO 'test' (`testcol`) VALUES (''); > SELECT * from 'test'; > > and I get > .0 Can't reproduce. I get . The problem is somewhere in the part of the setup you haven't shown. -- Igor Tandetnik

  1   2   3   4   5   6   7   8   9   10   >