[sqlite] Query optimizer not doing so well with my queries

Richard Warburton wrote: > For example, this takes minutes: CPU Time: user 263.759513 sys 2.237379 > SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID, > s.RatePlanUID FROM StayV s > WHERE NOT EXISTS (SELECT NULL FROM InvoiceItemV i WHERE i.PriceUID IS NULL > AND i.StayUID=s.UID); > >

[sqlite] SQLite3 json functions with PHP on OS X

Fehmi Ozuseven wrote: > I need json functions badly in SQLite. [...] > But also I need to do that via PHP. [...] The last thing I tried is > downloading php-7.0.5.tar.bz2 and trying compile sqlite in ext folder by > myself. [...] > Surprisingly it was work but it says SQLite Library Version 3.8.10.

[sqlite] Fwd: odd schema resulting from creating a table out of a join with a subselect

Hinrichsen, John wrote: > Could someone point me to documentation on how the schema generation > works when using a subselect in a join? : | The name of a result column is the value of the "AS" clause for that | column, if there is an AS clause. If the

[sqlite] Using SQLite for storing photo's

Cecil Westerhof wrote: > what is the best way to make a backup? With the backup API: . (Also available as .backup in the shell.) Regards, Clemens

[sqlite] The LIKE optimization breaks user defined like functions

Domingo Alvarez Duarte wrote: > Today I decided to see why my applications stop using index when using > my user defined "like" function The optimization replaces the LIKE with two comparisons. Installing a user-defined function implies that your new LIKE works differently, so this optimization m

[sqlite] UPDATE/open/close blocked while executing SELECT

Detlef Golze wrote: > I attach the database with the URI option cache=shared. This makes all threads share a single connection. Don't do it. Regards, Clemens

[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2

t18970237136 at web.de wrote: > after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the > following error > > SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > > The problem seems to be that the tables have been created using the following > SQL: > CREAT

[sqlite] SQLite 3.12.1 reports "database corruption" with existing databases created with SQLite 3.9.2

t18970237136 at web.de wrote: >>> after updating to 1.0.100.0 which uses SQLite 3.12.1, we are getting the >>> following error >>> >>> SQLite error (1): AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY > > The problem is ... the "[" and "]" in this syntax: > CREATE TABLE Customer ( > [I

[sqlite] sqlite3.OperationalError: database or disk is full

Yadav, Rohit wrote: > I ... pointed SQLITE_TMPDIR, TMPDIR, TMP, TEMP to the newly created directory. > ... but it looks like sqlite is still using /tmp directory to write temporary > files. How exactly did you change the environment variables, and how did you ensure that the SQLite library sees t

[sqlite] BUG?

Stephan Beal wrote: > On Thu, Apr 21, 2016 at 4:12 PM, jrhgame wrote: >> SELECT julianday('2016-04-15 12:10:10') ==>2457494.00706 >> SELECT datetime(2457494.00706) ==>2016-04-15 12:10:09 > > fwiw, i've done lots and lots of testing with round-trip conversions > between those two formats

[sqlite] BUG?

Stephan Beal wrote: > On Fri, Apr 22, 2016 at 8:43 AM, Clemens Ladisch > wrote: >> You can get problems only if >> - you are not using enough precision, or >> - the number does not represent a full second, but some random point >> somewhere in the middle betw

[sqlite] SQLITE_CANTOPEN on Android

Richard Hipp wrote: > On 4/22/16, Christian Werner wrote: >> On 04/22/2016 03:46 PM, Richard Hipp wrote: >>> Why isn't /var/tmp or /tmp usable on Android? >> >> There ain't no "/var/tmp" nor "/tmp" on droids. Best of all worst >> alternatives >> is to use the application's own directory or better

[sqlite] SQLITE_CANTOPEN on Android

Martin Trnovec wrote: > D?a 24.04.2016 o 8:39 Clemens Ladisch nap?sal(a): >> Android (and Chromium on Android) just use SQLITE_TEMP_STORE=3 for this >> reason >> >> Apparently, Martin's SQLite library was compiled differently. > > That's right we have our

[sqlite] Controlling of check-pointing of WAL Dbs

Albert Banaszkiewicz wrote: > 1. Is there a way to control (i.e. disable) check-pointing happening at DB > closure ? The internal sqlite3WalClose() function does a checkpoint if it is able to get an exclusive lock (which implies that there are no other readers/ writers). So to prevent this check

[sqlite] How change the tmp vacuum temporary files

Andrea Peri wrote: > After some searches, I found the trouble is due to the vacuum command > that is going to use > the /var/tmp folder for temporary files. > > I like to say to the sqlite to use instead the /tmp. > [...] > Unfortunatelly I do not found in the documentation no one alternative > to

[sqlite] Table entries disappearing for unknown reason.

Vin?cius da Silva wrote: > #pragma omp parallel for > for( int i = 0; i < dispatchedThreads; ++i ) Does OMP guarantee that there is exactly one thread for each loop iteration? > sqlite3_exec( m_db, "PRAGMA synchronous = OFF", NULL, NULL, NULL ); >

[sqlite] Table entries disappearing for unknown reason.

Dominique Devienne wrote: > On Tue, Feb 2, 2016 at 10:32 AM, Clemens Ladisch > wrote: > >> Vin?cius da Silva wrote: >>> #pragma omp parallel for >>> for( int i = 0; i < dispatchedThreads; ++i ) >> >> Does OM

[sqlite] Table entries disappearing for unknown reason.

Vin?cius da Silva wrote: > after commenting the pragmas, Which ones? omp or SQLite? > SQLiteManager is throwing > an exception because stepping the statement in endTransaction() is > returning SQLITE_IOERR_DELETE_NOENT > in the parallel loop.

[sqlite] Table entries disappearing for unknown reason.

Vin?cius da Silva wrote: > Each thread access the SQLiteManager array m_dbs, indexed by its unique > index provided by openmp. Since SQLiteManager opens a connection in its > init() method each thread has a private connection. The tests are passing > when I set the parallel loops for one thread onl

[sqlite] Table entries disappearing for unknown reason.

Vin?cius da Silva wrote: > Using BEGIN EXCLUSIVE TRANSACTION in place of BEGIN TRANSACTION does not > change the result I meant, try executing them so that they are guaranteed to conflict, like this: db1.init("same_file"); db2.init("same_file"); db1.beginTransactionExclusive(); db2.beginT

[sqlite] Question about LIMIT 1 fitness on lookups by unique index

Paul wrote: > CREATE TABLE bar( > id INTEGER PRIMARY KEY, > foo TEXT UNIQUE, > ) > > SELECT 1 FROM bar WHERE foo = ? LIMIT 1; > > is statement builder is smart enough to render "LIMIT 1" suggestion useles No, the LIMIT 1 logic is never removed. > if we take into account the fact th

[sqlite] What's the reason for alias names not being visible in subqueries?

Paul wrote: > I am curious what is a particular reason that aliased columns in a query not > visible to sub-queries? Because the SQL standard says so. > Of course it is possible to duplicate expression in sub-query ... > But this would not be as efficient as to access result of already evaluated

[sqlite] What's the reason for alias names not being visible in subqueries?

Paul wrote: > CREATE TABLE parent( > CREATE TABLE child_1( > CREATE TABLE child_2( > CREATE TABLE parent_child_1_link( > CREATE TABLE parent_child_2_link( > > now, depending on the child_type in the parent I want to select ... Show your query. Or at least example data and the desired result. Re

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

david at andl.org wrote: > 1. Type name (string) to use in CREATE TABLE. > 2. Affinity. > 3. Datatype that will be returned by C API calls > 4. Which C API call to use to get and put data values. > > My current choices are: > Bool: TINYINT, INTEGER, value_int Why not "BOOLEAN"? (This would result

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

Chris Prakoso wrote: > My question is whether anybody had successfully implemented multiple > rows Insert/Update. This is possible in SQL: INSERT INTO MyTable(ID, Value) VALUES (1, 'hello'), (2, 'world'); UPDATE MyTable SET Value = 'the same value' WHERE ID IN (1, 2); -- rather verbose;

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Chris Prakoso wrote: > I've been testing the performance of my Insert/Update using > Transaction and without, and I found that it is quicker when I don't > use it. Show the code. Regards, Clemens

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Chris Prakoso wrote: > public bool UpdateData(string tableName, Dictionary > fields, List whereKeys) > { > ... > using (SQLiteTransaction transaction = > conn.BeginTransaction()) > { > ... >

[sqlite] C# + SQLite - How do you do Multiple Rows Inserts/Updates?

Chris Prakoso wrote: > insert into test (field1,field2) values (1,"two"),(2,"three") > > SQL Error: near ",": syntax error You might want to update to a tool that is not years out of date. Regards, Clemens

[sqlite] Andl: choices for generic types: bool, binary, number/decimal, date/datetime, text/varchar

david at andl.org wrote: >> If you do not know what type is _actually_ stored, call >> sqlite3_column_type() first. > > That looks like one call to get the type, a choice of 3 calls for INTEGER, > REAL or TEXT and a choice of 3 conversion/casts to get it to the final type If you want to get out a

[sqlite] Version 3.11.0 beta

Dominique Devienne wrote: > On Thu, Feb 11, 2016 at 1:10 PM, Olivier Mascia wrote: >>> Le 11 f?vr. 2016 ? 10:48, Olivier Mascia a ?crit : >>> It looks like the appropriate character set mapping behavior on Windows >> is still not quite right in the command line utility. >> >> If I'm changing shel

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

Richard Hipp wrote: > If you can provide solid suggestions for how to deal with the > unicode issue on windows consoles within the next day or so, we might > be able to incorporate changes into 3.11.0. But beyond that, I think > we'll need to push this off until 3.12.0. > > If y'all think that wai

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

Olivier Mascia wrote: >> Le 11 f?vr. 2016 ? 23:13, Clemens Ladisch a ?crit : >> - stdin from the console > > Convert from the codepage returned by GetConsoleCP() to UTF8. >> - stdout to the console > > Convert from UTF8 to the codepage returned by GetConsoleOutpu

[sqlite] Unicode issue on windows consoles. Was: Version 3.11.0 beta

Olivier Mascia wrote: > it looks evident that the command-line tool of sqlite3 is meant to be > compiled as a narrow I/O program. It uses the standard main() and not > wmain(), ... and does not attempt to use the wide chars in or out > channels. That's just because UTF-8 is the most portable encod

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > I noticed my queries going very slowly after changing a join to a left > join, examination of the query plan showed that the optimiser was choosing > a poor plan. It chooses a _correct_ plan. > It only occurs when the left join is on a views, if explicit > tables are used inst

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: >> It chooses a _correct_ plan. > > Really? With query 1) to select a song it first scans the song_artist table > that could contain many thousands of records. That does not seem right. The result is correct. This would not necessarily be the case if it executed an outer join as

[sqlite] Windows A and W APIs dual support

Olivier Mascia wrote: > Are there Windows platforms, supported by SQLite source code of course, where > the 'W' version of the APIs are not available? Once upon a time, SQLite supported Windows 95/98/Me. Nowadays, the code is still there, but untested. Regards, Clemens

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > 3) SELECT song.*, song_artist.*, artist.* > FROM song LEFT JOIN song_artist ON song.idSong = song_artist.idSong > JOIN artist ON song_artist.idArtist = artist.idArtist > WHERE song.idSong =1 > > This ... has the same join combination as 1). No. 1) has "song LEFT JOIN (song_arti

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > What I see as wrong is that in 1) (and 4) ) we have a query of the form > A LEFT JOIN B WHERE clause involving index fields on A > > yet the optimiser does not search A, the outer table, first using the index. EXPLAIN QUERY PLAN SELECT song.*, songartistview.* FROM song LEFT JO

[sqlite] Bug: LEFT JOIN on view confusing the query optimiser

Dave Blake wrote: > Is there anything I can do to get the optimiser to perform 1) with the same > efficiency as 2)? See point 3 of ; the optimizer has problems when you use a subquery (i.e., a view) at the right side of a left join. If you really

[sqlite] Correlated subquery throwing an error

Gary Briggs wrote: >> SELECT >> a.id AS a_id, >> (SELECT b.id >>FROM foo AS b >>WHERE b.id!=a.id >> AND distance(a.x,a.y,b.x,b.y)<=25 >>ORDER BY b.val, distance(a.x,a.y,b.x,b.y)) >> FROM foo AS a > > This is the bit that doesn't seem to work; having > distance(a.x,a.y,b.x,b.y)

[sqlite] Performance comparison between SQLite and SQL Server?

admin at shuling.net wrote: > I am just curious whether there is a performance comparison between SQLite > and SQL Server? That depends on the data, and the software, and the hardware. In other way: you have to measure yourself. > Surely SQL Server will perform better on huge database with thous

[sqlite] Correlated subquery throwing an error

R Smith wrote: > you need to alias a subquery always when it is used in a join Not in SQLite. > you need to explicitly limit a subquery that is a field and must only > ever return 1 result if the where clause is ambiguous about it Not in SQLite. (It ignores superfluous rows, and returns NULL if

[sqlite] empty page corrupt

sanhua.zh wrote: > I find some db corrupted and try to find out the reason. Are you using a network file system or virtual machine? What type of disk? http://www.sqlite.org/howtocorrupt.html Regards, Clemens

[sqlite] Wish list: better ALTER TABLE for setting default

Christian Schmitz wrote: > I would like to see more alter table things. Currently, ALTER TABLE implements only those changes that can be done without rewriting any of the table data. > ALTER TABLE testTable SET DEFAULT FOR testField TO 0 Not all values in this column might actually be stored on

[sqlite] A question

??? wrote: > sql4:SELECT ... FROM ... WHERE b.tileId=557467343 AND ... > Execute sql4 consumes 500-600ms, returned 5847 records > > sql5:SELECT ... FROM ... WHERE b.tileId=67430683 AND ... > Execute sql5 consumes 500-600ms, returned 14 records > > the records returned of sql5 is less than sql4, but

[sqlite] MIN/MAX query

David Bicking wrote: > I have a table > I L V > 1 1 A > 1 2 A > 1 3 A > 1 4 B > 1 5 B > 1 6 A > 1 7 A > 2 1 C > 2 2 C > I want to return the minimal and maximum L for each "group" of V in a given I. > > The result I want: > I MinL MaxL V > 1 13 A > 1 4

[sqlite] Multiple Column index

Michele Pradella wrote: > CREATE TABLE test (DateTime BIGINT,CarPlate VARCHAR(255)); > CREATE INDEX indexA ON test(DateTime); > CREATE INDEX indexB ON test(CarPlate); > CREATE INDEX indexAB ON test(DateTime,CarPlate); > > EXLPAIN QUERY PLAN SELECT * FROM test WHERE (DateTime>1)AND(DateTime<100) AND

[sqlite] Multiple Column index

(please don't top-post) Michele Pradella wrote: > so there's no way to use that kind of double column index on a select like > explain query plan select * from test where (CarPlate LIKE 'AA000%') AND > (DateTime>1); > because at least one field have to do with operator = correct? No, the number

[sqlite] Multiple Column index

Michele Pradella wrote: >> Michele Pradella wrote: >>> so there's no way to use that kind of double column index on a select like >>> explain query plan select * from test where (CarPlate LIKE 'AA000%') AND >>> (DateTime>1); >>> because at least one field have to do with operator = correct? >> >>

[sqlite] EXPLAIN QUERY PLAN for non-SELECT statements?

nomad at null.net wrote: > I wanted to see how SELECT queries within triggers are using indexes > (or not) which of course lead me to https://www.sqlite.org/eqp.html: > > EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may > also be appear with other statements that read data f

[sqlite] How to remove duplicate records

Dominique Devienne wrote: >Always ran into Error: only a single result allowed for a SELECT that >is part of an expression. --DD In a join, you can directly use a subquery with multiple columns: ... JOIN (SELECT ...) ... Regards, Clemens

[sqlite] Encrypt the SQL query

admin at shuling.net wrote: > In my C++ program, I will invoke SQLite to execute SQL queries. But these > queries are just stored as normal string constants in C++ so it is easy to > be decoded via reverse engineering method. How could _any_ obfuscation not be decoded with reverse engineering? R

[sqlite] How to modify page_size

Jim Wang wrote: > How could I modify the page_size of the Data base? The documentation says: | The page_size pragma will only cause an immediate change in the page | size if it is issued while the database is still empty, prior to the | first CR

[sqlite] Can SQLite know from the statement string if it is row producing or not?

Bart Smissaert wrote: > I am interested to know from the statement string if the statement is > invalid, row producing (could produce rows) or non row producing. says about sqlite3_column_count(): | Return the number of columns in the result set retu

[sqlite] hard links and SQLite

Felipe Gasper wrote: > To prevent race conditions where a 2nd process accesses a newly-created > SQLite file before the creator process can set up the schema, ... And what happens when the 2nd process tries to access the database just before the first creates it? > I?m seeing some corruption in o

[sqlite] Performance of newer versions

Gheorghe Marinca wrote: > the library seemed to held, be contended on an internal lock (critical > section). Which lock? This probably happens when you are using the same database connection from multiple threads, or multiple connections in shared-cache mode. Neither one is something that you sh

[sqlite] Performance of newer versions

Simon Slavin wrote: > More recent versions introduced another mode (WAL) > in which any number of queries can take place without locking the database. That would be file locking, not a critical section. This appears to be a problem with using the SQLite C API. Regards, Clemens

[sqlite] UPSERT

chromedout64 at yahoo.com wrote: > Will SQLite ever get an UPSERT command similar to other DBMS? These other DMBSes use a client/server architecture and want to achieve high concurrency. SQLite has no client/server communication overhead, so there is no large downside to using two SQL statements

[sqlite] Wrong Index Select with Large Table Joined to Small Table

Denis Burke wrote: > Your suggestion to make it a partial index makes sense. When would > you NOT want to add the "where [indexedColumn] IS NOT NULL"? Seems > like it would always be helpful. If the database cannot prove that a query will never search for a NULL value, it cannot use that index.

[sqlite] why is searching for a range of rowids in an FTS table a slow operation?

skywind mailing lists wrote: > this is a slow query for FTS tables: > > SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; > > As far as I know both queries are fast on normal tables. Where is the > difference / what is the reason for the performance difference? Virtual table modules must implemen

[sqlite] SQLite crashing

Igor Korot wrote: > Upon exiting from the application When exactly? Is DllMain() involved in any way? Regards, Clemens

[sqlite] SQLite crashing

Igor Korot wrote: > The DLL where SQLite pointer is alocated there is a DLLMain. And what does it do? > BOOL APIENTRY DLLMain() > { > } Is it really empty? > MainFrame::~MainFrame() > { > delete m_db; // this is where the crash happens > } When exactly is this destructor called? Can you s

[sqlite] SQLite crashing

Igor Korot wrote: > sqlite.dll!sqlite3_mutex_enter(sqlite3_mutex * p) Line 19996 + 0xc > bytesC > sqlite.dll!sqlite3Close(sqlite3 * db, int forceZombie) Line 726 + 0xc > bytesC > sqlite.dll!sqlite3_close(sqlite3 * db) Line 772 + 0xe bytesC > sqlite.dll!SQLiteDat

[sqlite] SQLite crashing

Igor Korot wrote: > sqlite.dll!sqlite3_mutex_enter(sqlite3_mutex * p) Line 19996 + 0xc > bytesC > sqlite.dll!sqlite3Close(sqlite3 * db, int forceZombie) Line 726 + 0xc > bytesC > sqlite.dll!sqlite3_close(sqlite3 * db) Line 772 + 0xe bytesC > sqlite.dll!SQLiteDat

[sqlite] SQLite crashing

Igor Korot wrote: > 1>odbccp32.lib(dllload.obj) : error LNK2019: unresolved external > symbol __imp___vsnprintf referenced in function > _StringVPrintfWorkerA at 20 Looks like a bug in the MSVC libraries: http://stackoverflow.com/questions/4596212/c-odbc-refuses-to-statically-link-to-libcmt-lib-un

[sqlite] Why does the stored schema strings have their beginning upper-cased?

Olivier Mascia wrote: > I have been puzzled about its habit to convert to upper case the beginning of > each schema statement > > Is there a functional reason to do this? The documentatin says: | The sqlite_master.sql column stores SQL text t

[sqlite] CREATE TABLE parser

Marco Bambini wrote: > I developed a CREATE TABLE parser in C Did you duplicate the SQLite SQL parser? Or is your parser supposed to have differences? > I would like to stress test it before releasing it as open source on GitHub. Without the source, finding errors would not be as easy. Regards

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

?? wrote: > Here(http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor) said "Do > not try to implement a scrolling window using LIMIT and OFFSET. Doing > so will become sluggish as the user scrolls down toward the bottom of > the list.?. But the page also said "This information is obsolete? "Obsol

[sqlite] SQLITE Porting on FreeRTOS with STM32

Nitin Bhosale wrote: > I am trying to port sqlite3 VFS implementation on freertos(FATFS on > stm32 ARM CortexM4). I have created an amalgamation with sqlite3 > version 3.12 base code. > > When I run the below code it runs to create a database and a query > "create a table cars". But when query "ins

[sqlite] SQLite Corruption By Writing NULL Data

sanhua.zh wrote: > I am debugging db corruption. After I get some corrupted db, I found that > they all corrupted by writing null data. > > 0x1000f8000 + 2778664 sqlite3KnownError,main.c,line 3192 > 0x1000f8000 + 2554560 unixWrite,os_unix.c,line 3335 > 0x1000f8000 + 2821984 sqlite3WalCheckpo

[sqlite] Understanding conditional triggers

Sam Carleton wrote: > I am working on a trigger that only needs to be fired when the value for > the column 'Selected' is 1. How exactly do I write that trigger? Here is > what I have come up with: > > ... WHEN (NEW.Selected = 1) ... Looks OK. What is the problem with that? Regards, Clemens

[sqlite] How to use sqlite3_blob_open when the blob column is empty?

zhangkai wrote: > insert into device(ieee) values(12345); > int ret = sqlite3_blob_open(db, > "main", > "device", > "endpoint", > 12345, > 1, > &blob); The documentation says: | This function fails ...

[sqlite] obtainingthe number of rows returned by a query (with a limit)

Paul Sanderson wrote: > I am trying to determine before a query is executed how many rows will > be returned. the following query works as expected > > select count(*) from table > > but > > select count(*) from table limit 100 > > still returns the number of rows in the table not the number of row

[sqlite] Fastest way to find whether at least one row has a certain column value

Tim Streater wrote: > On 05 Mar 2016 at 13:05, Clemens Ladisch wrote: >> But why do you want to determine the number of rows in the first place? > > In my case I want to know whether at least one row exists that has a certain > column which has a given value. At th

[sqlite] obtainingthe number of rows returned by a query (with a limit)

Paul Sanderson wrote: > I have users who need to execute queries that generate a large number > of rows, I have other users that create queries that generate a large > number of rows by accident (i.e. cross joins). I have no control over > what they enter but I want to do something to warn them tha

[sqlite] How to read data from WAL?

Sairam Gaddam wrote: > I want to make note of those changes and replicate someplace else. And I > found that WAL is the correct place where changes to the database are > present. So my main aim is to get those changes and replicate after further > processing. If you're really sure that the databas

[sqlite] Correlated subquery refers to wrong rowid - bug?

In the question David Paton asks about a query like this: create table a(id integer primary key); create table b(id integer primary key); insert into a values (1), (2); insert into b values (1); select

[sqlite] Insert or Replace Trouble

Tilsley, Jerry M. wrote: > CREATE TABLE messages (id numeric); > > insert or replace into messages values (1); > > Why does this result in multiple rows of the same value? INSERT OR REPLACE deletes any old row that would cause a UNIQUE constraint violation. Without a UNIQUE (or PRIMARY KEY) const

[sqlite] sqlite3_update_hook() clarification

Sairam Gaddam wrote: > The documentation says that the function sqlite3_update_hook() is called > whenever a row is updated, deleted or inserted No. It says that this function is called to register a callback function that is called for these updates. > And I don't find any definition for this c

[sqlite] backup memory database not working

asdf asdf wrote: > Backing up a file database works well;not so memory. > > I described the issue here: > http://stackoverflow.com/questions/35834529/sqlite-backup-memory-database-c There is an error in your code. Which you have not shown. Regards, Clemens

[sqlite] backup memory database not working

asdf asdf wrote: > - create a memory db > - attach a file db > - backup memory db > this fails. An attached database stays separate, i.e., its data is not merged into the backup. To backup the file DB, give its name (and not "main") to sqlite3_backup_init(). Regards, Clemens

[sqlite] backup memory database not working

asdf asdf wrote: > My intention: > - loading a file db into a memory db. This is not done with ATTACH, but by making a backup of the file DB with the memory DB as destination. Regards, Clemens

[sqlite] Creating system table

Igor Korot wrote: > There is a notion of system table vs. user table in the DBMS. System tables are tables with a name beginning with "sqlite_". > My question is: what should I do if I want to create a system table? This is possible only by (ab)using the writable_schema pragma: CREATE TABLE x

[sqlite] Creating system table

Igor Korot wrote: > OK, so how many system tables SQLite has? This is documented in the documentation: http://www.sqlite.org/fileformat2.html#intschema Regards, Clemens

[sqlite] Minor SQLDIFF issue

tonyp at acm.org wrote: > Doing SQLDIFF against a non-existent database (e.g., due to a typo) produces > a diff which is a series of DROP TABLE ... and also creates an empty file by > that name. sqlite3_open() automatically creates the database file if it does not yet exist. (This is the only wa

[sqlite] problem with CURRENT_TIMESTAMP

Luca Ferrari wrote: > On Mon, Mar 14, 2016 at 10:52 AM, Richard Hipp wrote: >> I think your system clock was set incorrectly when you did the initial >> insert. > > I'm running on localhost and did not touch the clock. Then the obvious explanation is that there is a bug in your insertion code.

[sqlite] CAST STRING => INTEGER

Cezary H. Noweta wrote: > Is there some more-or-less official list posted somewhere with things which > is better not to be done? The documentation is quite clear that INTEGERs have 64 bits. So trying to use integers above 9223372036854775807 is something that is better not to be done. Regards

[sqlite] CAST STRING => INTEGER

Cezary H. Noweta wrote: > On 2016-03-14 12:03, Clemens Ladisch wrote: >> The documentation is quite clear that INTEGERs have 64 bits. So trying >> to use integers above 9223372036854775807 is something that is better >> not to be done. > > How could somebody use integer

[sqlite] .import command/NULL values

Bernard McNeill wrote: > If it is required to import NULL values into an Sqlite table field, can > this be done No; the .import commands imports everything as text. (This can result in numbers in columns that have numeric affinity.) To get NULL values, UPDATE the table afterwards. Regards, Clem

[sqlite] .import command/NULL values

Bernard McNeill wrote: > Any particular reason why the shell '.import' command cannot interpret || > as a NULL for the field? > Does || have another use/interpretation? Everything is a string. The data between the two separators is an empty string. Regards, Clemens

[sqlite] Sqlite do not use index collate nocase

Domingo Alvarez Duarte wrote: > In one database I created an index with collate nocase but it seems that > sqlite do not recognize it as a candidate index for a select. > > CREATE TABLE 'items' ( > 'by' text, [...] > ); > CREATE INDEX "items_user_idx" ON "items"("by" COLLATE NOCASE); > > explai

[sqlite] Calling some predefined SQL function from another custom SQL function?

Olivier Mascia wrote: > Writing a scalar SQL function, is there a C-level way to call some > other scalar SQL function? The only way is to build an SQL statement from within the function and execute it. > I think it is obvious I could build a SQL statement from within the > function and execute i

[sqlite] Calling some predefined SQL function from another custom SQL function?

Olivier Mascia wrote: >> Le 31 mars 2016 ? 11:03, Clemens Ladisch a ?crit : >>> I think it is obvious I could build a SQL statement from within the >>> function and execute it. But it sounds costly to involve the parser >>> (yes, it's fast) for that, isn

[sqlite] Fastest way to backup/copy database?

Rob Willett wrote: > We?re trying to backup a 10GB live running database ?as-fast-as-we- > possibly-can? without stopping updates coming in. How much memory do you have? I guess you can't simply read the entire database file to force it into the file cache? In WAL mode, a writer does not block r

[sqlite] SQL Help: Finding case insensitive matches

Dominique Devienne wrote: > Imagine I have a single table containing a single text column, of user names. > I'm trying to find user names which differ only by case. > > select u1.user, u2.user from os_users u1, os_users u2 where upper(u1.user) = > upper(u2.user) and u1.user <> u2.user; > select

[sqlite] sqlite3_prepare*() statement argument detailed

E.D. wrote: > The sqlite3 documentation specifies, that sqlite3_prepare*() compile > "the first statement" from the sqlite3_stmt ** argument. This argument > ought to be explained thoroughly. It is explained thoroughly on . Regards, Clemens

[sqlite] foreign_key_check mystery

James K. Lowden wrote: > I seem to be getting a foreign key check anomaly. I've checked the > constraint mentioned in the error message (and the other one, just in > case). Am I overlooking something, or has this been fixed since 3.8.4.1? > > sqlite> pragma foreign_key_check; > Error: foreign key

[sqlite] Reading german characters from sqlite using c#

Kishore uddavolu wrote: > Data is being retrieved using c# using system.data.sqlite-sqlitedatareader. > We are able to retrive non German characters. Problem is with reading > German characters. We tried with text and nvarchar types.but German > characters are showing as question mark. SQLite use

Re: [sqlite] alter table and .schema output

Luca Ferrari wrote: > CREATE TABLE pratica_protocollo( > ... > note varchar( 2048 ) -- note per l'integrazione > ); > > ALTER TABLE pratica_protocollo ADD COLUMN cage_attribuzione_anno >integer; > ALTER TABLE pratica_protocollo ADD COLUMN cage_attribuzione_numero >integer; > > and the .sc

Re: [sqlite] alter table and .schema output

Luca Ferrari wrote: > Is there a "correct" way to annotate SQL schema? Other databases > provides special commands (e.g., PostgreSQL ADD COMMENT), but I don't > see nothing in SQLIte3 syntax except the SQL '--' one. If those annotations are to be queried, put them into a table. Otherwise, if you

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