Re: [sqlite] why does gperftools shows sqlite3_memory_used as using the largest amount of memory

Mayank Kumar (mayankum) wrote: > -my application sqlite based runs for months before it might get restarted > -while its running there are places we need to execute the following series :- > - sqlite3_bind_int64(deleteStmt > - sqlite3_step(deleteStmt) > -

Re: [sqlite] Vdbe Program Generation

Prakash Premkumar wrote: > On Fri, Oct 3, 2014 at 7:37 PM, Kees Nuyt wrote: >> On Fri, 3 Oct 2014 18:39:29 +0530, Prakash Premkumar >> wrote: >> >>> Can you please tell me which function is >>> sqlite actually generates the Vdbe >>> program for a give

Re: [sqlite] Curious datetime/strftime BUG?

John wrote: > -4713-11-25 12:00:00<-- 4 digit year > -471-11-25 12:00:00 <-- 3 digit year > -001-12-31 <-- 3 digit year > -001-12-31 <-- 3 digit year > -1975-10-21<-- 4 digit year > -197-10-21 <-- 3 digit year The documentation

Re: [sqlite] Memory Usage

David Muchene wrote: > I was wondering what options I can tune to make sqlite use more memory. You did not mention PRAGMA cache_size, so I guess you are not using it? Regards, Clemens ___

Re: [sqlite] Behavior change: INTEGER PRIMARY KEY and PRAGMA index_list

Paul Quinn wrote: > In 3.7, issuing "PRAGMA index_list(tablename)" to retrieve the details > of an index, sqlite3_column_count() would return 0 for automatic > integer primary key indexes, basically indicating there was no > explicit index created for that tablename. But in 3.8, >

Re: [sqlite] Filling struct Select in sqlite

Prakash Premkumar wrote: > Instead of generating an sql query for a select statement, I would like to > bypass the parser and fill in struct Select by myself based on the data I > have and Pass it to sqlite3Select() function. Can you please give me some > pointers in this direction ? Just do the

Re: [sqlite] Curious datetime/strftime BUG?

John wrote: > On 5/10/2014 19:59, Clemens Ladisch wrote: >> The documentation <http://www.sqlite.org/lang_datefunc.html> says: >> | These functions only work for dates between -01-01 00:00:00 and >> | -12-31 23:59:59. For dates outside that range, the resu

Re: [sqlite] why does gperftools shows sqlite3_memory_used as using the largest amount of memory

Mayank Kumar (mayankum) wrote: > will the memory used by sqlite keep on increasing, if we don't > finalize at all during the life time of the db, as we do more insert/ > delete/replace operations ? Every prepared statement needs some memory (but only a small amount). As long as you are using

Re: [sqlite] Detecting multiple CHECK failures

Peter Haworth wrote: > I'm a great believer in using CHECK constraints to do as much validation as > possible within the database rather than code it in my application. > > However, I think I'm right in saying that as soon as a CHECK constraint > fails, an error is returned to my application so no

Re: [sqlite] Single large table vs. several smaller tables

Drago, William @ MWG - NARDAEAST wrote: > An automatic test system that I designed generates 25 data elements > for each unit tested. [...] > should I lump everything together in one table just like the .csv file > or should I create several smaller tables that group similar parameters? > I'm not

Re: [sqlite] maintaining order state for custom functions in sqlite: is it possible to insure that callback gets records in "ORDER BY" order?

john soprych wrote: > Is there a good way to create functions in sqlite where you can insure that > your function callback will be called in order? No. Your best bet is writing the query in such a way that the function is applied to an already ordered sequence: SELECT myfunc(x) FROM (SELECT

Re: [sqlite] decoding a bitmask

Paul Sanderson wrote: > I have a table with an integer value which is a bitmask. > > 0c01 readonly > 0x02 hidden > 0x04 system > 0x10 directory > 0x20 archive > > I'd like to create a query which would take an attribute, say 0x07 and spit > out "system, hidden, readonly" SELECT substr(CASE WHEN

Re: [sqlite] decoding a bitmask

Paul Sanderson wrote: > (CASE visits.transition & 0xFF00 WHEN 0x0080 THEN 'Blocked' > ELSE '' END || >CASE visits.transition & 0xFF00 WHEN 0x0100 THEN 'Forward_Back' > ELSE '' END || >... > > The query seems to work OK if a single bit is set, but fails (a blank

Re: [sqlite] Lets try this again. Sqlite Python libraries throwing exception on unique constraint.

Jeffrey Parker wrote: > I am working with sqlite3 in python 2.7.8 and I am running into a strange > error where I get the below exception when running an insert into statement > on an empty table. The following code executes fine in Python 2.7.5: import sqlite3 conn=sqlite3.connect(":memory:")

Re: [sqlite] line break, or similar, in query results

Paul Sanderson wrote: > 1|0|texas > 2|1|new york > 3|2|washington > 4|0|tampa > 5|0|atlanta > 6|5|charleston > > I'd like to add a break between groups in the results so it looks somethng > like > > 1|0|texas > 2|1|new york > 3|2|washington > > 4|0|tampa > > 5|0|atlanta > 6|5|charleston SELECT

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

Ross Altman wrote: > I need to host a fixed, unchanging database online, An SQLite database is just a file. > and I want to make sure that anyone who downloads it cannot add to it. It's possible to run "sqlite3 thedata.db .dump > thedata.sql" and then to remove any protection that is still

Re: [sqlite] line break, or similar, in query results

RSmith wrote: > On 2014/10/14 13:09, Clemens Ladisch wrote: >> SELECT CASE WHEN previd = 0 THEN '--' || char(10) END, * >> FROM (SELECT ...); > > This solution from Clemens will work perfectly, and depending on the > kind of OS you use and output method i

Re: [sqlite] strftime() issues

Roy Sigurd Karlsbakk wrote: > Trying to use strftime() to extract current Year-Month seems to go nuts. > > sqlite> select strftime('%s', 'now'); > 1413536061 > sqlite> select strftime('%Y-%m', strftime('%s', 'now')); > 3865-46 SQLite interprets a number as a Julian day number. To have it

Re: [sqlite] Is msvcrt coupling now unavoidable (3.8.7)?

Max Vlasov wrote: > my static linking with Delphi for 3.7.8 version now complains about > _beginthreadex_/_endthreadex_. > Quick search shows than everywhere there's a recommendation to use these > functions instead of API CreateThread if one plans to use Visual c run-time > (msvcrt). Well, by

Re: [sqlite] FTS pagination

supermariobros wrote: > If I am using FTQ that looks like this > SELECT * FROM mail WHERE body MATCH 'sqlite' > can I add to it "WHERE rowid > 5 AND rwoid <10" > or it will significantly slow it down. How much did it slow down when you tested it? Anyway, without index: sqlite> EXPLAIN

Re: [sqlite] FTS pagination

supermariobros wrote: > EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' AND rowid>1000 ORDER BY rowid ASC LIMIT 10; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4: (~0 rows) > > If I understand it correctly it uses indexes

Re: [sqlite] FTS pagination

supermariobros wrote: > Well, they all give exactly the same output. > > sqlite> EXPLAIN QUERY PLAN SELECT rowid FROM activity_text_content WHERE > activity_text_content MATCH 'x' ORDER BY rowid ASC LIMIT 100; > 0|0|0|SCAN TABLE activity_text_content VIRTUAL TABLE INDEX 4:ASC (~0 rows) > >

Re: [sqlite] Performing a SELECT on an enormous database...

Charles Samuels wrote: > On Saturday, October 25, 2014 05:31:35 AM Richard Hipp wrote: >> However, when updating a row, SQLite rewrites the entire row. > > Does this still apply if the column was added due to "alter table X add > column"? I ask because it was my understanding that alter table

Re: [sqlite] Performing a SELECT on an enormous database...

J Decker wrote: > On Sun, Oct 26, 2014 at 10:32 AM, Clemens Ladisch wrote: >> Charles Samuels wrote: >>> it was my understanding that alter table added the extra column "elsewhere". >> >> It adds the extra column "nowhere". When SQLite reads a

Re: [sqlite] [RFE bug] Improve error reporting for foreign keys

Tristan Van Berkom wrote: > locateFKeyIndex() function issuing the not-so-informative > message "foreign key mismatch" [...] > > o When foreign keys are enabled at CREATE TABLE time, it would > be very helpful at this point to issue an error if a foreign > key is declared which refers to

Re: [sqlite] Ordering of group_concat values using subselect in 3.8.7

James Earl wrote: > After upgrading from SQLite 3.8.6 to 3.8.7, I am no longer able to > order group_concat values by using a subselect. For example the > following query with 3.8.6 will give me an ordered string of > items.image values based on items.position (which contains integers): > >

Re: [sqlite] Index usage for order by and where clauses

Baruch Burstein wrote: > If I have an index on table1(colA, colB), will it be used for both the > where and the order by in either of these cases: > > select * from table1 where colA=1 order by colB; > select * from table1 where colB=1 order by colA; $ sqlite3 sqlite> create table table1(colA,

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

Rob Willett wrote: > I’d like to produce an output table based on the above data set that looks a > bit like this. > > Disruption_id | Start Time | End Time > 1 | 1 | 4 > 2 | 1 | 5 > 3 | 1 | 2 > 4 | 2 | 4

Re: [sqlite] Index without backing table

Paul wrote: > Are additional indices, created for WITHOUT ROWID, potentially less > efficient and more cumbersome? For tables with a rowid, the index stores the indexed columns and the rowid. For WITHOUT ROWID tables, the index stores the indexed columns and the primary key. Regards, Clemens

Re: [sqlite] Comparing to NULL in prepared statment

Baruch Burstein wrote: > "SELECT col1 FROM table1 WHERE col2=:val" > > col2 is a textual string, and may sometimes be NULL. If I bind a string to > :val it works fine. If I bind a null-pointer the comparison fails since it > should be using ISNULL. Is there a way to do this correctly with a single

Re: [sqlite] LEFT joins affect query plan for semantically equal queries

vita...@yourcmc.ru wrote: > SELECT * FROM bugs b LEFT JOIN profiles p ON p.userid=b.assigned_to WHERE > p.login_name='vita...@yourcmc.ru' > > Query plan: > SCAN TABLE bugs AS b > SEARCH TABLE profiles AS p USING INTEGER PRIMARY KEY (rowid=?) > > Which is of course very slow. > > Maybe you'll

Re: [sqlite] Is it safe to use backup at shell level when an application is running

Yves Crespin wrote: > if we add a SQLITE_BUSY handle, can we use the sqlite3 .backup when > [some] application is running? > > Is it safe or is there a risk to corrumpt the database or do we need > to change some settings ? Using the backup API is perfectly safe for your data. The only risk is

Re: [sqlite] AUTOINCREMENT BIGINT

Michele Pradella wrote: > I have a question about data type BIGINT BIGINT is not a data type. > from docs (http://www.sqlite.org/datatype3.html) This page says the data types are NULL, INTEGER, REAL, TEXT, and BLOB. > I understand that INTEGER and BIGINT results in the same affinity Yes. >

[sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

Hi, the following query fails to parse, although it should be valid: sqlite> select 1 union select 1 from (select 1 as x) group by x order by 1 collate binary; Error: no such column: x originally reported by Michael Geier here:

Re: [sqlite] bug: "no such column" with UNION + subquery + GROUP BY + ORDER BY + COLLATE

RSmith wrote: > On 2014/11/08 14:21, Clemens Ladisch wrote: >> the following query fails to parse, although it should be valid: >> >>sqlite> select 1 union select 1 from (select 1 as x) group by x order by >> 1 collate binary; >>Error: no such column:

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

RP McMurphy wrote: > Is there a way we can make the w index work with both queries and not > have to run external loops to flatten all the WHERE clauses? sqlite> .timer on sqlite> select count(*) from v wherez = 0 and ...>

Re: [sqlite] Change UPDATE with JOIN sintax

dylan666 wrote: > update Table1 > set Visibility=1 > where ConsumerID in (select * from > Table1 join Table2 > on Table1.ConsumerID = Table2.id > where Table1.visibility = 0 and Table2.visibility = 1) > > Unfortunately I get this error: > only a single result allowed for a SELECT that is part of

Re: [sqlite] Autoincrement with rollback

Koen Van Exem wrote: > Is it a bug or feature that the autoincrement > value is being reused when a rollback is issued? > > The documentation on https://www.sqlite.org/autoinc.html is a bit unclear > > ... it says it prevents reuse of ROWIDs from previously deleted rows. Only a DELETE statement

Re: [sqlite] Multiple threads reading different data

Daniel Polski wrote: > this maybe has to do with me using WAL mode, and that the update is > not yet "processed enough" for the other threads to fetch the new > data (so they still selects the old data), even though the trigger is > set to fire "after update". Is this maybe the case? Yes; other

Re: [sqlite] querying with BLOB in WHERE clause, possible?

bjdodo wrote: > Sorry for resurrecting an old thread. I got the where clause working for > queries with byte array arguments based on this discussion. I cannot find > the way to use byte arrays in where clauses for update and delete > statements. In the Android database API, execSQL() is the only

Re: [sqlite] sliteonline or sqlite in js

Dan Kennedy wrote: > Perhaps not as functional as a native app (so far), but looking really good! > And you can't beat the deployment. Well, it does not replace SQL Fiddle. I understand the desire to avoid storing data on the web server, but it would be nice if the initial schema/query could be

Re: [sqlite] automatic index on sqlite_sq_#######

Yongil Jang wrote: > It is a normal work but I just want to notify that some of automatic index > log message is not easy to recognize which query made this log output. SQLite just delivers the log message to the application; it is the application's job to relate it to whatever it is actually

Re: [sqlite] Why is a b-tree sort required for this query?

Oliver Smith wrote: >> On Sun, Nov 16, 2014 at 2:18 PM, Oliver Smith wrote: >>> ... >>> CREATE TABLE t2c (id INTEGER, name text, t2_id INTEGER, UNIQUE (t2_id, >>> name)); >>> >>> EXPLAIN QUERY PLAN >>> SELECT t1c.t1_id, t1c.id, t2c.t2_id, t2c.id >>> FROM t1c,

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

Thane Michael wrote: > I've been searching for a way to serialize an object's vector using sqlite3 > but are yet find a working solution. The question is how to model the vector in the database. In most cases, you should normalize your tables: class A { int id; double

Re: [sqlite] PRAGMA table_info incomplete

Staffan Tylen wrote: > I've just found out that the column data returned by PRAGMA table_info does > not include columns added using ALTER COLUMN ADD COLUMN. sqlite> create table t(x); sqlite> pragma table_info(t); 0|x||0||0 sqlite> alter table t add y; sqlite> pragma table_info(t); 0|x||0||0

Re: [sqlite] Bug report: USBAN failure

Richard Hipp wrote: > But apparently there is an issue in USBAN in that it does not allow calls > to memcpy() and memset() with NULL pointers even it the count field (the > third parameter) is zero. I couldn't find anything in the memcpy() or > memset() documentation that disallowed this case.

Re: [sqlite] Bug report: USBAN failure

James K. Lowden wrote: > /* Copy N bytes of SRC to DEST. */ > extern void *memcpy (void *__restrict __dest, > __const void *__restrict __src, size_t __n) > __THROW __nonnull ((1, 2)); > > IIUC the declaration specifies the pointer cannot be NULL and the > compiler

Re: [sqlite] Bug report: USBAN failure

Scott Robison wrote: > On Dec 3, 2014 12:57 AM, "Clemens Ladisch" <clem...@ladisch.de> wrote: >> Do you have a standard that allows NULL? The one I quoted does not. > > Note: I'll have to double check my copy of the C90 standard document, but > my re

Re: [sqlite] trying to store a file as a blob. caught on syntax...

Jonathan Leslie wrote: > I'm at a cmd.exe prompt. > > sqlite> INSERT INTO Files (name,contents) VALUES ('tsql.lis',X"$(od -A n -t > x1 tsql.lis|tr -d '\r\n\t ')"); > Error: near ""$(od -A n -t x1 tsql.lis|tr -d '\r\n\t ')"": syntax error > > I'm trying to store the file tsql.lis as a blob, and

Re: [sqlite] [SQLite]Ba​sic query

Shinichiro Yoshioka wrote: > I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++. > But although the compiling was successfully finished, even if I set break > point > on the source code, I can't trace the working line in sqlite3.c correctly. How exactly are you using SQLite in your

Re: [sqlite] Drop Table Behavior

Lukas wrote: > PRAGMA foreign_keys = ON; > > create table a ( id int primary key ); > create table b ( id int primary key ); > create table c ( id int primary key, > aid int, > bid int, > foreign key (aid) references a (id) on delete cascade, >

Re: [sqlite] Online/Hot backup of WAL journalling mode database

Nick wrote: > On 11 Dec 2014, at 20:39, David King wrote: >> Why are you trying to hard to avoid using the backup API? It sounds >> like it does exactly what you want > > Backup API works great if you have periods of no writing. > However, if a process writes during the backup then the API would

Re: [sqlite] Online/Hot backup of WAL journalling mode database

Simon Slavin wrote: > On 12 Dec 2014, at 10:27am, Clemens Ladisch <clem...@ladisch.de> wrote: >> If you write your own backup tool that simply calls >> "sqlite3_backup_step(b, -1)", the entire database is copied in >> a single atomic transaction. > > OP'

Re: [sqlite] SQLITE_FTS3_MAX_EXPR_DEPTH and upgrading sqlite

Ed Willis wrote: > We were on a version which did not have the compile option > SQLITE_FTS3_MAX_EXPR_DEPTH and are moving up to one that does. As it > turns out we have run into a problem with one of our clients where > they hit this limit now where previously the query just worked. Have a look

Re: [sqlite] Row filtering prior to aggregate function execution

Roland Martin wrote: > I have a need to filter the result set before aggregate functions are > performed. Use the WHERE clause. > Unfortunately I cannot query the security system to find out all valid > values and add these values to the where clause. I have to give a value to > the security

Re: [sqlite] Partial index to find maximum

Hick Gunter wrote: >create the primary key index ordered properly > >CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); DESC is not necessary here; SQLite has no problem reading the index in reverse order, if needed. (DESC in an index is useful only when you want to optimize multiple ORDER

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

J Decker wrote: > is this... 2015-01-02 20:47:18 (this is datetime( 'now', '-3600 second' ) > > received = 2015-01-02 13:46:23.818-0800 this is a DATETIME column recorded > in the database SQLite has no DATETIME datatype. This is just a string. > select * from messages where received <

Re: [sqlite] Can I user sqlite on wp8 native project?

Bite Forest wrote: > Which version of sqlite can I compile through vs in c++ code? None. You have to compile SQLite as C, not C++. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Scrolling Cursor implementation best practices (pagination and arbitrary sorting)

Max Vasilyev wrote: > http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor > and want to use WHERE, but what if 'title' is not unique? If the ORDER BY columns are not unique, you cannot know which rows to display on which page. You must be able to uniquely identify rows. > - This is considered

Re: [sqlite] aggregate functions with DISTINCT

Staffan Tylen wrote: > Well, the SELECT is actually over 400 lines long [...] I > can't use SELECT DISTINCT X as that wouldn't give the result I want, and I > can't use SELECT DISTINCT 'ABC' either. So my "Yes, it might work" comment > doesn't actually hold. I see no other way than to use

Re: [sqlite] aggregate functions with DISTINCT

Richard Hipp wrote: > So if you say "group_concat(DISTINCT x,y)" does that mean that the > combination of x and y must be distinct or that only x is distinct? > Are we allowed to say "group_concat(x, DISTINCT y)" or > "group_concat(DISTINCT x, DISTINCT y)". And what does the latter > mean,

Re: [sqlite] aggregate functions with DISTINCT

Dominique Devienne wrote: > On Mon, Jan 12, 2015 at 4:13 PM, Igor Tandetnik wrote: >> 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... >> >>

Re: [sqlite] I got some trouble with sqlite3 and my C++ program

Nicolas Jäger wrote: > do > { > rc = sqlite3_step(stmt); > std::cout << sqlite3_column_text (stmt, 0) <<"," < (stmt, 2) << std::endl; > } while(rc == SQLITE_ROW); sqlite3_step() returns SQLITE_ROW when there is a row, or SQLITE_DONE when there are no more rows, or an error

Re: [sqlite] Streaming group by in union of already sorted tables

Emmanouil Karvounis wrote: > In short, we have two tables that are already sorted on a combination of > two fields (which are their primary keys) and we want to union them and > apply group by on both the aforementioned fields, like so: > > select c1, c2, myagg(*) from ( > select * from tableA >

Re: [sqlite] sqlite3 performance on select count very slow for 16 GB file

Jim Wilcoxson wrote: > If you have a table where rows are inserted but never deleted, and you > have a rowid column, you can use this: > > select seq from sqlite_sequence where name = 'tablename' This works only for an AUTOINCREMENT column. > This will return instantly, without scanning any rows

Re: [sqlite] sqlite give "database or disk full"

jitendar kumar wrote: > where is the temporary files location ?? 1. temp_directory, if set 2. SQLITE_TMPDR, if set 3. TMPDIR, if set 4. /var/tmp 5. /usr/tmp 6. /tmp Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] ordinary CTE containing sum()

James K. Lowden wrote: > 1. Last I checked, SELECT in a column position in the SELECT clause as > in > > select foo (select ...) > > is not permitted by the SQL standard. This example indeed is not valid SQL syntax. However, SELECT in a column position is allowed: select (select 42);

Re: [sqlite] Porting SQLite to another operating system (not supported out of the box)

Janke, Julian wrote: > In my opinion, this means, we must ""only"" write a VFS implementation for > our target platform. What file API is there? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] BLOB type not showing in table description after using create table as

Tiemogo, Idrissa wrote: > When I derive a table from another table containing blob type. > The describing the new table doesn’t show “blob". > > sqlite> create table t1 (p_id int, geometry blob); > sqlite> pragma table_info(t1); > 0|p_id|int|0||0 > 1|geometry|blob|0||0 > sqlite> create table t2 as

[sqlite] windows network and wal mode

Zaumseil Ren? wrote: > I have a separate table for each parameter with time stamp and value. > The time stamp is used as "integer primary key asc". > [...] > It is also possible to go back in time and then start from there. > Currently I remove all values from the tables after the specified time.

[sqlite] Regarding SeekGe and Next opcodes in VDBE

Sairam Gaddam wrote: > sql="create table em(name text primary key,age text,pts text);"\ > "create table l(name text primary key,fame text);"; > > sql = "select * from em,l where l.fame=em.age"; > >4 Once 0 130 00 >5 OpenAutoindex230

[sqlite] Regarding SeekGe and Next opcodes in VDBE

Sairam Gaddam wrote: > On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch wrote: >> Sairam Gaddam wrote: >>> sql="create table em(name text primary key,age text,pts text);"\ >>> "create table l(name text primary key,fame text);"; >&g

[sqlite] Select query becomes distinct on where matches unique with null value

Mike Gladysch wrote: > Select col1, col2, col3, col4, col5, col6 > From table > Where col3 is null and col4 ='test' That is not valid SQL. > 3.8.4.3: 3 rows (expected, ok) > 3.8.7.2: 1 row (wrong) How to reproduce: create table t(x, y, unique(x, y)); insert into t values(null, 1); insert

[sqlite] Problems with pragma journal_mode

Janke, Julian wrote: > PRAGMA journal_mode=WAL; > > Unfortunately, after these lines, the journal mode is not changed. > > -DSQLITE_OS_OTHER=1 Do you have mmap support? says: | WAL normally requires that the VFS support shared-memory primitives. | The built-in

[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints

Nicolas Boullis wrote: > I have a program that does mostly? SELECT requests, and it is very > slow. But I then figured out that, if I rebuild my SQLite database > without PRIMARY KEY/UNIQUE constraints, the program runs much faster > (no measurement yet, but I?d say at least 10? faster). SQLite

[sqlite] Hot journal file won't go away

Drago, William @ CSG - NARDA-MITEQ wrote: > What is the recommended procedure for clearing a hot journal file? Just opening the database should be enough. > I used to do a "SELECT..." but since I upgraded from System.Data.SQLite > 1.0.93.0 to 1.0.96.0 that doesn't work anymore. If the rollback

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

Richard Brinkman wrote: > When a perform the following query: > select random() as x from some_non_empty_table order by x desc limit 20; > I get something like: > -4348240540797173967 > -8823092517172356709 > 4237024158005380173 > 897958093325532613 > -6349939216731113298 > ... > which clearly is

[sqlite] Getting row number in a sorted list.

Simon Slavin wrote: > SELECT rowid FROM NameTable > WHERE name BETWEEN 'P' AND 'P' > > This will execute faster if you have an index on 'name' in NameTable. > > [Yes I know 'P' is lazy. Until you find someone with that name > (presumably Polish) with that name bite me.] If the

[sqlite] : it seems to be only orber by and group

afriendandmore wrote: > The table shall be ordered according to A. (Order by A) > But if C ist true and to the value B1 in this Record, there exists another > Record with B2 and B2 = B1, then B1 should be ordered in the row above B2. Do those two records have the same A values? Regards,

[sqlite] WITH () AS (SELECT ) help

jose isaias cabrera wrote: > WITH EmailData (name,contact,dstamp) AS > ( > SELECT > 'last, first', > 'first.last at xerox.com', > '2015-08-25 11:11:11' > ) > UPDATE LSOpenProjects SET XtraB = EmailData.dstamp, pmuk = EmailData.contact > WHERE pmuk = EmailData.name; > > But, it's not working.

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

Graham Holden wrote: > And while "SELECT random() AS rr ORDER BY rr" is slightly contrived, the > example from J Decker: > > select ItemName,SoldDate, date('now','-1 month') as z from > SoldItemDetails order by > SoldDate > if it were to show the same behaviour (I haven't tested it) might break

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

Domingo Alvarez Duarte wrote: > This assumption is a bit naive : > >> In SQLite, this cannot happen because queries execute infinitely fast >> (as far as the built-in date/time functions are concerned). Nonetheless it's true. says: | the current time

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Dominique Devienne wrote: > how can we programatically reliably discover which (v)tables a view > accesses, staying in documented behavior land? With an authorizer callback: http://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens

[sqlite] Insertion into the large DB drastically slows down at some point

Yuri wrote: > I followed every advise I could find: > * Database is written into memory > * Whole insertion happens in one transaction in one giant BEGIN TRANSACTON; > ... END TRANSACTION; block. > * Foreign keys are deferred: PRAGMA defer_foreign_keys=ON; > * Journal is disabled: PRAGMA

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

Jean-Christophe Deschamps wrote: > At 21:11 27/08/2015, you wrote: >> I think it still comes back to my earlier comment: Would changing it to >> behave more like the most common / expected outcome above be a breaking >> change? > > Looks like "How many zillion devices are going to misbehave if

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

Yahoo! Mail wrote: > sqlite> drop table if exists t1; create table t1(a datetime); begin; with > recursive c(x) as (values(1) union all select x + 1 from c where x < > 10) insert into t1(a) select datetime('now') from c; commit; > > It would take ages to finish and that is logical; it's

[sqlite] explain plan change between SQLite 3.8.3.1 and 3.8.11.1

Dominique Devienne wrote: > One annoyance with the authorizer approach is that you cannot "stack them" Write your own authorizer that implements its own callback list. But the only sure way to prevent anyone else from installing its own authorizer is to change the function name in the SQLite

[sqlite] INSERT OR IGNORE consuming too many rowids

David Baird wrote: > # INSERT INTO "StringIntern" VALUES(5,'c'); <-- undesired behavior > # INSERT INTO "StringIntern" VALUES(3,'c'); <-- desired behavior Works for me. Are you sure you actually executed this actual code? guarantees that a newly inserted row

[sqlite] ABOUT ROWID

??? wrote: > For example, tableA contains two columns: implicit rowid, A_id. > we create index on A_id. > firstly, we used rowid to select the row, cost about 400 seconds; > secondly, we used A_id to select the row, cost about 200 seconds; > thirdly, we also used rowid to

[sqlite] ABOUT ROWID

Hick Gunter wrote: > 2) run each query in a new process (so each one will need to read the data > from disk) This does not help against the file cache of the OS. Regards, Clemens -- > This communication (including any attachments) is intended for the use > of the intended recipient(s) only

[sqlite] last_insert_rowid() and changes() per statement instead of per connection?

Olivier Mascia wrote: > even if using two distinct sqlite3_stmt* handles (linked to a same > sqlite3* handle), two competing threads would get unusable answers > from both these API. Yes. Please note that transactions work on the connection level. > should the design of competing threads

[sqlite] Locked database

Cecil Westerhof wrote: > I have a crontab job which uses a SQLite database. Sometimes this database > is locked because I used SQLite DB Browser, but did not Write or Revert > Changes. It looks like that when a database is locked there is a file with > the database name with -journal appended to

[sqlite] Locked database

Cecil Westerhof wrote: > sqlite3 "${DATABASE}" "begin immediate" 2>/dev/null > errorCode="${?}" > if [[ "${errorCode}" -eq 5 ]] ; then > printf "${DATABASE} is locked\n" > > I saw that when it is locked I get back a 5. Is this always the case? sqlite3 returns the SQLite error code, and 5

[sqlite] Is rowid the fastest?

??? wrote: > CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z); > > In order to testing the efficiency. After I create index on t(x). > And this give me a suprise, now I retrieve by x , I found that its is > faster than its before. An index entry is smaller than a table row, so if you

[sqlite] get blob colums values by sqlite3_get_table?

??? wrote: > int sqlite3_get_table( > char ***pazResult,/* Results of the query */ > > could I get blob colums values by sqlite3_get_table? Yes. But like any other values, they are converted to strings. Regards, Clemens

[sqlite] Missing documentation about BLOB encoding conversions

Michael Kaufmann wrote: >>> It seems that BLOBs are converted from UTF-8 to UTF-16 when >>> sqlite3_column_text16() is called. > > The sequence of calls is: > > 1. sqlite3_step() > 2. sqlite3_column_text16() says: > To cast a BLOB value to TEXT,

[sqlite] SQLite Vulnerabilities reported in NVD

Saurav Sarkar: >Our application is free of any kind of SQL injection Famous last words. :) >as we don't have any input fields. So where does your data come from? Does your application have any interface that an attacker could access? How do you create your SQL statements? Are you always using

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

gunnar wrote: > When I execute a query that has a subquery behind a comparison > operator in its where-clause and the subquery returns an empty result, > then the result of the complete query is exactly how I want it: also > an empty result. Can I assume that this behaviour will stay the same > in

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

Eduardo Morras wrote: > Clemens Ladisch wrote: >> ... WHERE cb_seq_num > ( >> SELECT cb_seq_num >> FROM ordercallback >> WHERE cb_uuid=@CBUUID >> UNION ALL >> SELECT NULL -- at least one result >> LIMIT 1)

[sqlite] Behaviour when subquery behind comparison operator in where-clause returns empty result

gunnar wrote: > The subquery will always return one result or no result. So I only > have to UNION it ALL with the SELECT NULL part. >>> Clemens Ladisch wrote: >>>> ... WHERE cb_seq_num > ( >>>>SELECT cb_seq_num >>>>

  1   2   3   4   5   6   7   8   9   10   >