Re: [sqlite] SQL Statement Help(selecting days).

>On Wed, May 18, 2011 at 4:10 PM, Petite Abeille > wrote: > > On May 18, 2011, at 10:50 PM, Danilo Cicerone wrote: > >> How can I simulate a > >> calendar table(maybe using the strftime funtion)? > > > > Well, you have two broad options: > > > > (1) materialize the

[sqlite] Caveat in parsing create table statement

Dear list, Is there a way to make SQLite accept this kind of constraint: CREATE TABLE tab ( id INTEGER NOT NULL, data INTEGER, CHECK(data = 0 or not exists (select 1 from tab where id = data))); This toy exemple doesn't mean much as it is and the actual situation is a bit more

Re: [sqlite] SQLITE return codes for insert/delete/update/select

>I agree with what you stated but it would have been more clearer if >the result of the update statement was a "RECORD NOT FOUND" return >value since it did not find any that met the query's criteria. How can >you say that the UPDATE was successful when the record you were >looking for does

Re: [sqlite] SQLite as a Logger: How to mimic "rotation of logs"?

Without a view (but with a trigger) and certainly open to improvement (9 is the MAX_ENTRIES parameter): CREATE TABLE "log" ( "id" INTEGER NOT NULL PRIMARY KEY ON CONFLICT REPLACE AUTOINCREMENT, "seq" INTEGER CONSTRAINT "ix1Seq" UNIQUE ON CONFLICT REPLACE, "data" CHAR); CREATE TRIGGER

Re: [sqlite] Determining how many columns were returned in a query

>I happen to have a code path such that the select statement can return >1, 3 >or 5 columns. I know I could go based on count, but if I could do it by >name that would be safer. I had not considered the point that multiple >columns could have the same name, though, so I fully understand why

Re: [sqlite] Determining how many columns were returned in a query

>How about: > >SELECT count() FROM (); You can do that (and variations) but this is a completely distinct statement. I meant that there is no possibility to recover the row count of a result set before it goes to completion (by iterating step), just because the SQLite engine has no idea

Re: [sqlite] Determining how many columns were returned in a query

>How does one go about finding out how many rows a query returns? This is the number of time sqlite3_step can be called successfully until it returns SQLITE_DONE. >Is there a way to find out the id of a particular column? AFAICT column don't have ids. You can read column names or alias using

Re: [sqlite] Issue 608 in sqlite-manager: select date('2011-04-29', quote(-3) || 'day'); shows null after upgrading of firefox to 3.6.17

Change that into: select date('2011-04-29', quote(-3) || ' day'); (note the space before day). Looks like a parsing change. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request: Fuzzy searching

I apologize if double-post, the first one didn't make it to the list. - Hi, >Hi all, I am interested in seeing "fuzzy searching" in SQLite, for >lack of a better term. This type of search would return more results >than LIKE

Re: [sqlite] How to Use an Apostrophe in a Text Field?

>True. I will get rid of the habit of using double quotes for string >literals. >Thanks for information. But most of the databases support this non >standard >behavior. Yeah ... until things break under your feet due to a new version not sticking to the "non-standard" behavior anymore or

Re: [sqlite] How to Use an Apostrophe in a Text Field?

> The apostrophes are escaped by apostrophes. Correct. http://www.sqlite.org/faq.html#q14 > One more way you can do. > >insert into () values ("*Goin' Down > >> the Road Feelin' Bad*"); > >It is double quotes before and after *. Similarly double quotes will >be escaped by one more double

Re: [sqlite] insert help

>My date column is set when the program starts and i do not want it to >change. How is this date column set in the database without inserting anything? > So I have my with and two columns and >. I have say 5 values (1 2 3 4 5) that I wanted inserted >into mytable where the date is equal

Re: [sqlite] insert help

>Newbie here. i'm trying to insert multiple values into a table by a >certain >date and when I use where clause it fails. This is my code "insert >into db >(table) values ('value') where date = 'date range'". Thanks for any help. There is no where clause in insert statements, it wouldn't

Re: [sqlite] Trouble inserting null value from txt file

>It's impossible when you use standard sqlite3 command line utility. In >your particular case you have 2 options: either write your own >application that will recognize some kind of value as null and insert >it instead of plain text, or you can use temporary table like this: Another way is to

Re: [sqlite] read full txt file in one record

>3. edit the database file with a hex editor, replacing "~~" with "0D 0A". That seems pretty dangerous! Rather update the table using the standard replace() function. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] sqlite bug? -- inconsistent time searches

>It's a pain in the arse that one has to pay for copies of the >ISOs. What do they think we're paying them for ? I also find this a perverse effect totally contrary to their mission or at least its spirit: produce good standards for public use. Without free access to reference up-to-date

Re: [sqlite] Lack of "decimal" support

>This page has a lot of info about >Decimal Number support, including >a set of libraries: > >http://speleotrove.com/decimal/ Yes! IBM and Intel are two of the big names having done significant work in this direction and made research and/or results publicly available.

Re: [sqlite] Lack of "decimal" support

>There are many cases where people are doing calculations or using >numbers expecting them to retain all digits. This would allow the BCD >type to be used for that if they really need it. Currency conversions (rarely exact!) or tax (or margin) calculations come to mind as very common uses

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

At 18:46 23/03/2011, you wrote: >Current US national debt is 16 digits. Nothing less? That's where the bug lies. OK, OK, I'm out ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sorting of Korean Language Characters

At 09:43 23/03/2011, you wrote: >I am working on one application which require sorting for Korean Language. > >The Korean Characters sort by Jamo(Hangul Jamo) ie based on KSX1001 >character code. > >Does sqlite3 or any other package support this type of sorting ? >If not , then any clue to carry

[sqlite] Passing key as string in system.data.sqlite

Hi Dear list, I'm looking into system.data.sqlite and I've been asking myself questions about the most portable encoding of a text passphrase to sqlite_[re]key. It seems to me that the only portable way is to pass an UTF-8 string since it's the only encoding invariant wrt byte order and

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

>But what I postulate is that you can't physically write *the same* record >over and over more than 90 times per second on a 5400 rpm drive, >unless the >drive, OS, or filesystem implements something like wear-leveling, >where the >physical location of sectors is constantly changing. It's

Re: [sqlite] UPDATE/INSERTing 1-2k rows slower than expected

>So if the drive is 5400 rpm, 227 is much more than 5400/60=90 and even if >it's 7200 (manufacturers sometimes upgrade drives inside portable hd >without >prior notice), it's still twice as much as 7200/60=120. 5400/60, 7200/60 ... those values rely on the assumption that successive LBAs are

Re: [sqlite] SQLite version 3.7.6 testing

>Your goals make a lot of sense. However I think you can do my second >suggestion. Compile with STAT2 code included by default, but make the >analyze command only build stat1 by default. > >This will result in no change in default behaviour, but means that anyone >wanting to use stat2 can

Re: [sqlite] Trigger to filter out characters

>Could you please show me how to write the condition (to filter out >characters)? Use the strfilter function found in extension-functions.c downloadable from http://www.sqlite.org/contrib/ Once built and loaded, you can use a trigger similar to create trigger if not exists trFilterAB after

Re: [sqlite] slow select from table with data blob

>It seemed strange that a simple "select * from table" that I was doing >was so slow. The table contained about 20 columns (fields) and 300 >rows. The select took about 1.5 seconds. (using SQLite Expert). Does the run time settle at 1.5 s after a few runs or is that a first-run time ? As an

Re: [sqlite] REGEXP parameter order

Hi Igor, >I'm not quite sure what you are talking about, but see the >documentation of like() and glob() functions here: You're right about LIKE and GLOB. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] REGEXP parameter order

> > Sorry for elementary questions but when implementing a REGEXP function > > (I'm using PCRE from pcre.org) I need to know in which order the two > > arguments (target, pattern) will be passed to the function. > > > > I also would like to retain the last pattern used in compiled form > >

[sqlite] REGEXP parameter order

Hi all, Sorry for elementary questions but when implementing a REGEXP function (I'm using PCRE from pcre.org) I need to know in which order the two arguments (target, pattern) will be passed to the function. I also would like to retain the last pattern used in compiled form (connection-wise).

Re: [sqlite] how to put the database in cache without waiting the system do it by himself ?

>when i just launch th application, at the beginning the query can take >around fews seconds... but after some time (10 - 20 minutes), it's take >only few ms ! > >so i guess it's because the windows cache in memory the database file ? > >so how to speed up this time to make windows cache more

Re: [sqlite] Propose minor incompatible API change

>An end user (think: your mom) wants to upgrade her smartphone to the >latest >OS release. That new OS release includes the latest shared library for >SQLite. But in so doing, some percentage of the apps she has downloaded >cease to work. Sure, the problem really is that the apps were

Re: [sqlite] Propose minor incompatible API change

>This is, technically, a compatibility break. On the other hand, there >appear to be vast numbers of smartphone applications that currently depend >on undefined behavior and will suddenly stop working if we don't make this >change. I understand the proposed change will have no incidence for

Re: [sqlite] fill blank fields

>thanks Simon and Igor for your help on this a few weeks ago, but I >need an >addition to > >UPDATE Aircraft SET CN = '*' where CN = '' or CN is null; > >what I need to do is replace blank fields in a specific row, sort of >a double where where statement as in: > >UPDATE Aircraft SET CN = '*'

Re: [sqlite] Rounding Strategy

Scott, >SQLite handles rounding by running the value through the internal >printf with the appropriate precision. As best I can tell the >internal printf adds half a unit at the appropriate position, then >truncates. Yep, truncation is a way to do it but doesn't meet layman expectations in

Re: [sqlite] Rounding Strategy

> > select round(3.05, 1) >3.0 > >Is this expected behavior for SQLite? >The documentation isn't specific on the rounding strategy that is used. > >My personal expectation was that this would round to 3.1. You _expect_ that 3.05 will represent exactly as 3.05 in IEEE, but it that the case?

Re: [sqlite] delete from t1 where not in (null)

Hi, SQLite sounds pretty reasonnable to me: >select "select distinct favicon_id from moz_places"; >select distinct favicon_id from moz_places; Returns NULL >select "deleting: standard method..."; >delete from moz_favicons where id not in (select distinct favicon_id >from moz_places); -- here

Re: [sqlite] SQL query on sort order

Harish, >We have a problem with a sql query. >In a table, a column called "name" contains character data that may >include >alpha, numeric and special characters. It is required to sort in such >a way >that names starting with alpha characters are listed first, then numerals >and finally

Re: [sqlite] cost of sqlite3_open

>It's not contradictory. I say that "real cost" of sqlite3_open is >parsing the schema. Igor says that this cost is actually deferred from >inside sqlite3_open call to the first "substantive" sqlite3_step call. >So you will have to pay this price anyway, just profiler output would >be somewhat

Re: [sqlite] cost of sqlite3_open

Pavel, > > 1) How "expensive" is a call to sqlite3_open. Does a call to > sqlite3_enable_shared_cache make it "cheaper"? > >Its cost depends on the size of your schema as it gets parsed during >open. Isn't this contradictory with an answer by Igor made in a recent thread? >Subject: Re:

Re: [sqlite] SQLITE 3.7.3 bug report (shell) - output in column mode does not align UTF8-strings correctly

At 14:26 26/11/2010, you wrote: >N.b., there is a severe bug (pointers calculated based on truncated >16-bit >values above plane-0) in a popular Unicode-properties SQLite extension. >The extension only attempts covering a few high-plane characters—if >memory >serves, three of thhem in array

Re: [sqlite] Reducing time to create indexes

>Simon, I'll try that and see what difference itmakes That or build a :memory: DB, populate it, build indices and then only back it up to disk using the backup API. That requires you have enough memory available, but should be really fast if you have.

Re: [sqlite] Confused by import of NULL values from CSV file

>I import a CSV flat file into a table (using sqliteman as GUI). The >flat file has NULL values as simply no data between two delimiters (I >use 'tab' as Delimiter). > >The resulting table does not treat 'no data' between 2 delimiters as >NULL. When I run CHECK constraints etc. on it it

Re: [sqlite] SQLITE: sorting

Look in your mailbox. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pragma foreign_keys not persistent?

>As far as I can tell, turning on foreign_keys for a database is not >persistent. It only seems to be valid for that connection/session. So >this means I have to prefix every command that I send to the database >with a command to turn on foreign_keys and I therefore can't issue >one-off

Re: [sqlite] PRAGMA database_list: insert into table?

>But Roger, the "layer sitting in front of SQLite" is a programming >environment which provides its own (black-box) connectivity to SQLite, and >it isn't going to be calling any DLL into which one will have injected >a UDF >library in the manner you have laid out, and it's not going to let the

Re: [sqlite] ON conditions vs. WHERE conditions

> > What is the rationale about placing complex conditions in the ON part > > of an inner join rather than in an WHERE clause? > >Except for outer joins, the difference is purely stylistic. They are >functionally equivalent. In fact, SQLite internally rewrites the >former to the latter, before

[sqlite] ON conditions vs. WHERE conditions

What is the rationale about placing complex conditions in the ON part of an inner join rather than in an WHERE clause? I understand that the ON clause will limit the temporary table processed by a subsequent WHERE clause, while a larger table will be filtered by the WHERE part if no selective

Re: [sqlite] error in sum function

ond what SQL would allow. -- Jean-Christophe Deschamps eMail: <mailto:j...@q-e-d.org>j...@q-e-d.org SnailsTo: 1308, route du Vicot 40230 Saint Jean de Marsacq France GSM: +33 (0)6 15 10 19 29 Home:+3

Re: [sqlite] Reg: In Memory Database Using SQLite

>I'm aware that SQLite offers the functionality of in-memory databases, >but I >don't know how to read the data and send it over the wire to the server or >how to push the data into the in-memory database of the server. The backup API included in SQLite offers the facility to backup/restore

Re: [sqlite] CREATE TABLE work arounds?

>I am parsing fields on the fly and then creating tables, unfortunately >one of the fields is "Order" and is a "special word" in SQL as is not >allowed. Is there a way around this instead of intercepting with perl >s'/Order/Orders/g' Can you wrap every column name inside double quotes or

Re: [sqlite] copy data from one db to another

>What's the best way to copy data from one db to another? > >Given 2 databases with identical schemas, one full of data and the >other empty, the brute force way would be to perform selects on the >source db, then for each row, perform an insert into the destination >db. Is there a more

Re: [sqlite] how to search for asterix character?

>I use vb or in SQLlite Expert the * does not seem to work > >From my testing * doesn't seem to work in the likeif I use '%33' it > returns >everything with test33if I use '33%' it returns everything with >33testif I >use '%33%' it returns everything with 33test, test33 which is the same as >*

Re: [sqlite] how to search for asterix character?

>How do I search for the asterix character * ?? > >This doesn't work: >select field1 from table1 where field1 like '%FH%*%' >as the * character here seems to be ignored. > >Using the latest version of SQLite. You're doing it right. Either you use a wrapper that messes with * in litterals or

Re: [sqlite] network access problem

>Running in sqlite application in Virtual Box, attempt to open a >database with sqlite from a shared network folder >\\Vboxsvr\testdata however the open16 and openv2 (with read >only) both fail --- rc = 14. File opens file if moved locally to >hard drive. > >using latest version of sqlite3

Re: [sqlite] what languages

>I use sqlite from within Autoit V3 (Autoit is a windows-oriented >basic-like language) AutoIt, while a scripting language can be seen and used as a RAD platform. It enjoys good support, up to date SQLite embedding and executables produced can include any file your application needs, like a

Re: [sqlite] Loading Sqlite3 DB into memory

>Is there any way to load a sqlite3 db from a location in memory? >Ideally, I'd like to have a memory pointer (eg, something provided via >malloc), which I could then use as the handle sqlite3 uses to load the >database. > >The reason I'm trying to do this: I have an encrypted database file

Re: [sqlite] recursive select in sqlite

>z> i wonder is there RECURSIVE select function in sqlite? the background >z> for the question are: create table objects (id INTEGER PRIMARY KEY >z> AUTOINCREMENT, name text unique) create table tree(id int, child_id >z> int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is >there

Re: [sqlite] select intersecting intervals

>The Minimal-Perfect-Hash-INTERSECTION-OF-VECTORS approach might benefit >queries against tables having several million rows. What I'm wondering >(and >lack the C skills to find out for myself) is whether SQLite's underlying >algorithms for INTERSECT could be optimized with a minimal perfect

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

> > In my low-concurrency, familly-business context, I have no problem at > > all setting 3 hours timeout using the built-in function, when the > > slowest transaction may only take less than 5 minutes. > >With this condition as a 4th one in your list and with 5th one stating >that you have less

Re: [sqlite] multi processes, so many errores of SQLITE_BUSY and SQLITE_MISUSE

Let me take a reality check for the case of my own usage. If I guarantee that the following conditions are all true: All R^n (Read-Read-...-Read) atomic operations are enclosed in BEGIN transactions. All W^n (Write-Write-...-Write) and RMW (Read-Modify-Write) atomic operations are enclosed

Re: [sqlite] select intersecting intervals

> >I would first create an INTEGER primary key and then place an index on >name, >another on i_from, and another on i_to, and then see if the approach below >has any benefit. > >When I tried this with a geo-queryit was actually slower than the standard >select, and I'm curious if that's always

Re: [sqlite] SELECT question (computing day of week the using strftime() function)

>I need to find out how many specific weekdays (e.g., how many Sundays) >I have in any given range of dates. >My problem: How to use the COUNT function in combination with the >strftime() function. > >$ sqlite3 test.db3 >SQLite version 3.6.20 >sqlite> create table test (date VARCHAR(20), money

Re: [sqlite] Searching with like for a specific start letter

Tim, >But did I say that GLOB uses an index if it has been overloaded? No. I >wrote that if LIKE has been overloaded, queries that contain LIKE >won't use >the index. Typically, GLOB won't have been overridden too just >because LIKE >has been overridden: the rationale for overriding the

[sqlite] Exclusive transactions over network

Hi gurus, I'm aware of the limitations that generally preclude using SQLite over a network. Anyway do you think that doing so with every read or write operation wrapped inside an explicit exclusive transaction can be a safe way to run a DB for a group of 10 people under low load (typically 2Kb

Re: [sqlite] Searching with like for a specific start letter

Tim, >Queries using GLOB do use the index on the column in question (i.e. >optimization is attempted) >Queries using LIKE do not use that index if the LIKE operator has been >overridden. Sorry but GLOB doesn't use an index either if LIKE/GLOB has been overloaded. This is consistent with the

Re: [sqlite] Searching with like for a specific start letter

At 14:31 26/04/2010, you wrote: >If the implementation of SQLite you are using overrides the LIKE operator >(as more than a few do), then SQLite will not make use of an index on the >column in question. Use the GLOB operator instead. I doubt it. GLOB is absolutely nothing more or less than an

Re: [sqlite] Restriction of updating unique column

> > Contrary to what occurs in other engines, SQLite seems to > > assert constraints at insert/delete time, ignoring the fact that > > (insert or delete) trigger will increment or decrement the upper part > > of the tree interval on HI and LO keys (in the case of a nested tree). > >This /should/

Re: [sqlite] Restriction of updating unique column

>I found the the restriction of updating unique column in ver3.6.21. >and same problem is reported in follwoing mail > >Marc-Andre Gosselin wrote: >date: Thu, 16 Jun 2005 >title: "[sqlite] Update unique column" > > > > I discovered a behavior in SQLite 2.8.16 that doesn't conform to > the SQL >

Re: [sqlite] SUBSTR overload and ALTER TABLE

>We just experienced the hard way that overloading certain built-in SQL >function can interfere with core SQL commands if the overloaded function >behaves differently from the built-in function. > >Not surprising, after looking at the sources: > >* ALTER TABLE - alter.c uses SUBSTR and LIKE. >*

Re: [sqlite] Problem with sqlite providing different programs different data

>Both of these tools show a version of the database that is different >from what i see in the command line and they are equivalent in their >discrepancies (they are different from the command line but the same >as each other). > >So heres the basica scenario: > >1. i update my database with the

Re: [sqlite] SQLite template files

Hi Tom, >BTW- if you haven't done so already; it may be of use to the user to add >extensions: VirtualText and Jean-Christophe Deschamps has an extension >for fuzzy search for example. There is no problem. Alexey put it on his website under the extension for Unicode folder. Pleas

Re: [sqlite] All ancestors / descendents of a vertex in sqlite?

>I have a (small) directed graph which I would be able to fins all >ancestors or descendents of a certain vertex (transitive closure?). >So, using this graph: > >CREATE TABLE levels_levels (parent_id INTEGER ,child_id INTEGER, >UNIQUE(parent_id, child_id)); >INSERT INTO "levels_levels"

[sqlite] Result_* functions

Is it allowable/safe to invoke more than once any sqlite3_result_*() function? In other terms, subsequent invokation of any result function will it harmlessly override a previous one? As in: init... // once for all, post null return in anticipation for the various cases where // parameters

Re: [sqlite] Unicode command line bug in Windows version of sqlite3 with patch

>We currently use sqlite 3.6.23. We have a big problem with characters with >accents or other special characters in path to database file, for >example in >Czech Windows XP the "Application Data" folder is translated to "Data >aplikací" so if the accented 'í' is in path the sqlite3.exe writes

Re: [sqlite] regular expressions

>is anybody aware of a possibility to do s.th. like >select * from table where field like '[A|a]%' Unless non-standard compile option and provided you don't issue PRAGMA case_sensitive_like = 1; LIKE is case-insensitive, so LIKE 'A%' is the same as LIKE 'a%' SQLite offers another filtering

Re: [sqlite] if exist

>Yours returns 1 or 0. Mine returns length if found, otherwise 0. That's true but the subject title led me to believe that the OP intended to have a 0 vs. nonzero return for not-exists vs exists condition. ___ sqlite-users mailing list

Re: [sqlite] if exist

>Andrea Galeazzi wrote: > > I've got a table T made up of only two fields: INT id (PRIMARY KEY) and > > INT length. > > I need a statement in order to yield 0 when the key doesn't exist. > >Well, "select 0;" fits your spec (you never said what should be >returned when the key does exist). I

Re: [sqlite] Column types "safe" changes

>Why not just > > update tbl set col1 = col1; > >or perhaps > > update tbl set col1 = cast(col1 as text); > >I'm not sure the former will actually change anything, but the latter >should. Yes my untold question was merely if simpler col = col way could be simply ignored. You're right

Re: [sqlite] Column types "safe" changes

>And, of course, you'll have a table where some rows (old ones) contain >integers and others (new ones) contain text. SQLite is fine with this, >but your client software might not be prepared to deal with it. Some >comparisons might behave in surprising ways. I imagine that in such case, the

Re: [sqlite] Feasability of a Range function

Hi Alexey, >1. See internal sqlite instarray interface: >http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.c >http://sqlite.mobigroup.ru/src/finfo?name=src/test_intarray.h >http://sqlite.mobigroup.ru/src/finfo?name=test/intarray.test > >Note: http://sqlite.mobigroup.ru include official

Re: [sqlite] Feasability of a Range function

>Wouldn't it make more sense for "i in 1..5" to expand to "i >= 1 and i ><= 5"? > >Then it would also work for ordered types that aren't ordinal, such as >rationals >and strings and blobs and dates etc, and it would work for very large >ranges, >since there's no conceptual need to generate all

Re: [sqlite] how to execute an ATTACH DATABASE?

>ATTACH DATABASE ?1 as sysDB AFAIK you can't use parameter binding for anything else than litteral values. It makes sense since it would be impossible for the parser and optimizer to evaluate and produce run-time code for a statement without knowing beforehand which database or column the

Re: [sqlite] Feasability of a Range function

>Ah. You want table-valued functions, like this: > >http://msdn.microsoft.com/en-us/library/ms191165.aspx Thanks Igor, that's what I had in mind. >In any case, SQLite doesn't support table-valued functions. The >closest thing to it is a virtual table: OK, got it, but this is a bit of heavy

Re: [sqlite] Feasability of a Range function

>Why not just > >select some_scalar_function(i) where i between 1 and 5; That's because we then get No such column: i. That was not very important. I would have the use for such possibility but I can live without. My question was just curiosity about whether something along the line could

[sqlite] Feasability of a Range function

I'm trying to determine if a Range(from, to) function can be made as an extension function. Its effect would be to expand, for instance, range(1, 5) into (1, 2, 3, 4, 5) for use in constructs similar to select some_scalar_function(i) where i in range(1, 5); without having to build a

Re: [sqlite] why is underscore like dash?

>sqlite> select * from test where text like '_'; Underscore '_' is LIKE wildcard for any single character, percent '%' matches any substring. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I haven't been able to think of how it would preclude using the index, >but I suspect it's more a matter of needing a similar-but-different >codepath to optimize for the NOT case, rather than a simple "invert >this" codepath relying on the existing case. Which is really just >another way of

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>NULL = 12345 is NULL, NOT NULL is NULL, so subset N is not part of NOT >(col = 12345). You're right of course! (and I was even saying about nulls treated apart) But, in your view, that the set can be non-contiguous for negative/negated conditions would it explain that current code can't

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> So indexes are not used for NOT conditions, as NOT conditions > generally require a full scan, regardless. Yes, it is a simple > reverse of a binary test, but the reverse of a specific indexed > lookup of a known value is a table scan to gather all the unknown > values. Jay, I

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>maybe NOT is implemented the same way as any other >function and so it cannot be optimized using index. That's possible, but other logical operators don't exhibit the same bahavior and will not prevent the use of indexes. That NOT is not being handled at the same _logical_ level than AND and

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>I totally disagree with you. Let's say you have 1,000,000 rows and 100 >of them contain NULL. In this situation selecting NOT NULL will select >almost all rows which means that using index in this case doesn't give >any performance boost. So here using full scan for NOT NULL condition >is better

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

> > "SELECT count(*) WHERE NOT text IS NULL" > > > > requires that the complete text column is loaded. With a stored LOB > > this results in crazy performance. > >How did you find that? What do you mean by "requires loading of the >whole text column"? It pretty much can require even loading of

Re: [sqlite] Is there an optimization for "IS NULL" operator in a SELECT query ?

>It is driving me crazy. I'm working on a web spider where a table >holds the downloaded >webpage. It seems that a select > >"SELECT count(*) WHERE NOT text IS NULL" > >requires that the complete text column is loaded. With a stored LOB >this results in crazy performance. > >Is this optimized in

Re: [sqlite] Diagrams and ROLLBACK TO

Forget the previous post as it's probably wrong. I was misinterpreting the ROLLBACK TO discussion. In fact I was led to believe there were two forms: "rollback to" and "rollback to ". This is not the case. But then, I still have hard time understanding this part: "Instead of cancelling the

[sqlite] Diagrams and ROLLBACK TO

The diagrams in the following pages of the documentation don't allow for the "ROLLBACK TO" construct: lang_savepoint.html and lang_transaction.html lang_savepoint.html also contains a 'tranaction' typo. ___ sqlite-users mailing list

Re: [sqlite] Nesting Read/Write within Transaction?

>It doesn't matter if you are in a transaction or not, changing >rows while inside a fetch loop on the same table may lead >to problems. Sorry I was talking of another process doing asynchronous writes. I didn't understand the warning was towards a single rogue process. Of course letting the

Re: [sqlite] Nesting Read/Write within Transaction?

>The only thing that can bite you is if >you are in process of fetching rows from some select statement and you >update row that was just fetched or update/insert rows that would have >been fetched later by the select statement. As I understand it, simply wrapping every batch operation (Read,

Re: [sqlite] Multi-process read_uncommited equivalent?

Be aware that the backup process will need to restart from zero after each write! Now, if you can setup some kind of IPC between your two processes, then you could have the update process update the disk base and send identical data to the reader process, so the latter can update a memory

Re: [sqlite] Shocked by long updates of the text database, 1000 speed improvement after small modification to the code.

>I will try to do something to that extent using timer and character >counter. >I hoped that I could update the text stored in the database character >by character as fast as they come from the keyboard driver. >Unfortunately updates noticeably slow down the display of typed >characters.

Re: [sqlite] Searching for a LIKE '[0-9]%' equivalence in sqlite

>To to get columns that begin with a number range or any number, I >would use >in SQL server: > >SELECT * FROM mytable WHERE myfield LIKE '[0-9]%' > >This obviously doesn't work in sqlite and I have been searching for an >equivalence. Try this: SELECT * FROM mytable WHERE myfield glob '[0-9]*'

<    1   2   3   4   5   >