Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > My assumption > was that after the zeroblob(N), there was enough room in the main DBs > pages, such that the subsequent blob open+write+close did not need to > generate any "page churn" (i.e. journal activity) and could write directly > to the pages created on initial in

Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > On Mon, Jun 11, 2018 at 4:27 PM Clemens Ladisch wrote: >> It does write to the same pages, but those pages must be copied to the >> rollback journal so that they can be restored if the transaction is >> rolled back. (Or are the two passes inside

Re: [sqlite] Check Constraint

Cecil Westerhof wrote: > I want to create a field that only has values that consist of letters, > numbers end '-'. So no spaces, quotes or special characters like: '@%$!'. > What is the best way to write this check constraint? The GLOB operator has inverted character classes. So the field is vali

Re: [sqlite] Performance of writing blobs

Dominique Devienne wrote: > In JOURNAL mode, new data goes to DB file directly, and modified pages go to > the JOURNAL file. > And since here this is INSERT-only, from empty tables, I assumed pages copied > to the JOURNAL > file should be minimal. Yes. You can check the journal size with PRAGMA

Re: [sqlite] Database is malformed but no further information

Chris Brody wrote: > My understanding is that mobile apps are not 100% predictable since > they may be randomly suspended or terminated, at any point of time. But SQLite is designed so that even killing the process will not corrupt the database, as long as the storage device works correctly. > On

Re: [sqlite] Unexpected sqlite3_trace_v2 sqlite3_expanded_sql output.

Lodewijk Duymaer van Twist wrote: > I'm tracing queries that my application makes using sqlite3_trace_v2 and > sqlite3_expanded_sql in the callback. The application uses sqlite 3.22.0. > > At some point the software will call sqlite3_exec with the following > statement: > BEGIN TRANSACTION; > DELE

Re: [sqlite] insert or replace performance with self "references" column

Allen wrote: > create table Transactions (Id integer primary key not null, Parent references > Transactions(id), Body varchar); > create index Parent_Index on Transactions (Parent); > > EXPLAIN QUERY PLAN insert or replace into Transactions (Id, Parent, Body) > values (?1, ?2, ?3); > 23 0 0 SCAN

Re: [sqlite] CASE and NULL

Andy Goth wrote: > The expression "x = NULL" is meaningless since it will always evaluate > to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will > never accomplish anything. > [...] > So I'm wondering: can we do better? The expression "x = x" will fail for NULL, but succeed for e

Re: [sqlite] To JSON or not to JSON

Cecil Westerhof wrote: > my SQLite database contains for example: > > "5BF19111-9FD5-48CA-B919-A09411346A87""[ > ""The journey of a thousand miles > must begin with a single step. > > - Lao Tzu"", > ""Welke stap kun je vandaag zetten, > om dat verre doel te bereiken?"" > ]""2018-07-07"

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

rob.sql...@robertwillett.com wrote: > gcc: internal compiler error: Killed (program cc1) This is a compiler bug. Check if updating gcc to a current version helps. > Please submit a full bug report, > with preprocessed source if appropriate. > See for instructions. Otherwise, do this. Regards

Re: [sqlite] I'm getting a compiler error on a SQLIte build on Ubuntu 16.04 (which surprises me)

Rob Willett wrote: > I removed two instances of -O2 from the Makefile and, lo and behold, it > compiles. > > Sadly my database to check is approx 80GB which could be interesting. The bottleneck is I/O speed; it does not matter whether sqlite3_analyzer uses ten or twenty microseconds before waiti

Re: [sqlite] sqlite on freertos/fatfs : SQLITE_NOTADB during create table

Bram Peeters wrote: > He reads a page from the file in sqlite3PagerSharedLock, but the file is > still 0 so the page is all zeros. An empty file and a file filled with zeros are two different things. Does the file system return SQLITE_IOERR_SHORT_READ? Regards, Clemens

Re: [sqlite] Error: foreign key mismatch - "loan" referencing "user"

Markos wrote: > CREATE TABLE user ( > id_user integer PRIMARY KEY, > ... > CREATE TABLE loan ( > ... > FOREIGN KEY(id_admin_loan, id_admin_devolution) REFERENCES user(id_user, > id_user) I do not understand what this is trying to accomplish. Why not two single-column FK constraint

Re: [sqlite] Foreign Key error

J Decker wrote: > CREATE TABLE `option4_name` (`name_id` char(36) NOT NULL,`name` > varchar(255) NOT NULL default '' CONSTRAINT `name` UNIQUE) > FOREIGN KEY (`name_id`) REFERENCES `option4_name`(`name_id`) > foreign key mismatch - "option4_map" referencing "option4_name" name_id must be the pri

Re: [sqlite] Safe sqlite over remote filesystem?

Wout Mertens wrote: > I know that multi-writer sqlite and NFS don't play well with each other. > > However, I wonder if some constraints could be added that would make this > situation safe. NFS locking implementations tend to be bad. However, there are other file operations that are properly syn

Re: [sqlite] Safe sqlite over remote filesystem?

Simon Slavin wrote: > On 14 Aug 2018, at 3:09pm, Clemens Ladisch wrote: >> However, there are other file >> operations that are properly synchronized, e.g., it is not possible for two >> NFS clients to create a directory with the same name. > > You are correct. But t

Re: [sqlite] PRAGMA case_sensitive_like

Chris Locke wrote: > Is there a reason for it being write only? This pragma just installs a different LIKE() function, and there is no easy mechanism to read the function pointer back. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailing

Re: [sqlite] Invalid Blob Length Error Message

Casey Rodarmor wrote:> Hi all, > unrecognized token: "x'01234' > > I know now that the problem was that the blob's length was not a > multiple of two. However, the error message didn't give me an > indication of that. Would it be possible to make the error message > more explicit, to help users deb

Re: [sqlite] Why trigger (UPDATE) does not work?

Csányi Pál wrote: > CREATE TRIGGER YearsRemainingMonthsDays AFTER UPDATE OF EndDate ON > MyLengthOfService BEGIN UPDATE MyLengthOfService SET Years = ( with ... says: | The WITH clause cannot be used within a CREATE TRIGGER. Regards, Clemens

Re: [sqlite] Strategies to reduce page-loads?

t...@qvgps.com wrote: > The larger the db, the slower is the fetching! > > My assumption is, that in the big db, these 1000 lines are just spread over a > much higher count of pages. > So more page-loads resulting in more time. Correct. > We changed page_size to the maximum value of 64k and it b

Re: [sqlite] sqlite3_column_* with error handling

Keith Medcalf wrote: > 6) If the column type is SQLITE_BLOB > a) Retrieve the column value pointer using column_blob > b) If the returned pointer is NULL, then an error has occurred "The return value from sqlite3_column_blob() for a zero-length BLOB is a NULL pointer." Regards, Cleme

Re: [sqlite] sqlite3_column_* with error handling

Keith Medcalf wrote: > In the case of a BLOB if a NULL pointer is returned the error code must > be retrieved and then if and only if the bytes counter is greater than > 0 is the error valid. > > Does this mean that if you are retrieving the value of a blob via the > colmn_blob interface you should

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

Urs Wagner wrote: > Simon Slavin: >> On 12 Sep 2018, at 2:04pm, Urs Wagner wrote: >>> The following code is returning 0. Why? >>> >>> ExecuteStoreQuery("PRAGMA foreign_keys;").First(); >> >> You coerce the result of the call into an integer. Can you make the call >> and display (or use a debugge

Re: [sqlite] foreign_keys = 0N with Entity Framework 6.2.0

Urs Wagner wrote: > I get three lines With some other tool, or executed through the EF? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to import TSV table with double quote in it without having to escape double quote

Peng Yu wrote: > I don't want to escape the quote charaters in the input. Is there still > a way to import quote characters into a sqlite3 table? The CSV import hardcodes " as quote. You could try the ASCII import instead. Regards, Clemens ___ sqlite-

Re: [sqlite] Bug report: Window functions in VIEWs broken in 3.25.1

Bjoern Hoehrmann wrote: > Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that > Window functions in VIEWS behave differently from PostgreSQL 9.6 and > from what I expect. > > DROP TABLE IF EXISTS example; > CREATE TABLE example(t INT, total INT); > INSERT INTO example VALUES

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

Djelf wrote: > INSERT INTO t (v1,v2,v3,v4) VALUES > (1,1,1,0),(1,1,0,1),(2,1,0,10),(3,1,0,100),(3,1,0,1000); > > SELECT > v1,v2,sum(v3+v4) OVER (PARTITION BY v2 ORDER BY v1) as val > FROM t > GROUP BY v1,v2; > > v1v2 val > 1 1 1 > 2 1 10010 > 3 1

Re: [sqlite] sqlite 3.25.1 windows function. So it should be?

Djelf wrote: > SQLSTATE[42803]: Grouping error: 7 ERROR: column "t.v3" must appear in the > GROUP BY clause or be used in an aggregate function > > It seems to me that sqlite should issue a similar message. This is allowed for compatibility with MySQL. And there is a case with min()/max() where

Re: [sqlite] sqlite3_get_table() failed with error "database is locked"

ldl wrote: > Multiple applications called sqlite3_get_table() to read data in a same db > file at the same time, got error "database is locked" Somebody has a write lock. > Why read-only access is locked? Because one access is not read only. > How to resolve the issue? Find out who locked it.

Re: [sqlite] Calling sqlite3_create_module from a DLL

Deon Brewis wrote: > I have a DLL that makes a series of sqlite3_create_function_v2 calls. > It all works fine and the DLL is usable. > > I've tried adding a sqlite3_create_module into the same DLL, but I get > an assert in: > > sqlite3_mutex_try > over here: > assert( sqlite3GlobalConfig.mutex

Re: [sqlite] .separator and .mode commands interactions

Luc Charansonney wrote: > sqlite> .separator tabs sqlite> select 1, 2; 1tabs2 > sqlite> .import mydata.txt mytable > Error: multi-character column separators not allowed for import You should have used ".separator \t". > So I fall back on my feet by using .mode instead of .separator: > sqli

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > i.e. how to select only the groups that contain > some value in the set of values in a column not > specified in group by clause. > > select > (select group_concat(b) from t t1 where t1.a = t2.a) as list > from t t2 > where b = ?1; Similarly: select gro

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > Clemens Ladisch wrote: >> select >> group_concat(b) as list >> from t >> where a in (select a >> from t >> where b = ?1) >> group by a; >> >> But you will not be able to avoid the

Re: [sqlite] Filtering groups by non-grouped field.

I wrote: > But you need to find some aggregate function that can do the filtering. HAVING SUM(b = ?1) (In a database with proper booleans: "HAVING SUM(CASE WHEN b = ? THEN 1 END) > 0") Regards, Clemens ___ sqlite-users mailing list sqlite-users@maili

Re: [sqlite] Filtering groups by non-grouped field.

E.Pasma wrote: > select group_concat(b) as list > from t > group by a > having count(b=?1) > ; In SQLite, a boolean expression returns 0 when false, and count(0) is 1. You have to generate a NULL for failed matches, or use another function like sum() or max() that can filter out zeros. Regards,

Re: [sqlite] Filtering groups by non-grouped field.

John Found wrote: > Also, it seems max(b = ?1) will do the trick as well as count(b = ?1) > > And here another question appears. What is more efficient? In SQLite, both are equally efficient. Use whatever makes the query easier to understand. Regards, Clemens ___

Re: [sqlite] Why operations with in-memory DB are much faster than with on-disk DB?

Yuri wrote: > I noticed that my DB import process is much slower when run on the DB on > disk, vs. in memory. It's possible that you forgot to wrap a single transaction around all changes. Otherwise, you get an automatic transaction for every command, which requires disk synchronization every tim

Re: [sqlite] Exception handling during create table from a virtual one

Max Vlasov wrote: > I have a virtual table that raises an unhandled exception during a create > table SQLite is written in C. The C language does not have exceptions. > Аfter the exception is thrown > If I continue the execution You must return from the callback function normally, or

Re: [sqlite] Help!

am...@juno.com wrote: > how do I make a field wrap the text. In other words, rather than having > the text in a field keep going from right to left, when I hit the right > hand margin, how do I make the text automatically go to the next line. The purpose of SQLite is to store data, and to give it

Re: [sqlite] curious discovery about geopoly module

Graham Hardman wrote: > SQLiteForExcel [1] https://github.com/govert/SQLiteForExcel "sqlite3.dll is a copy of SQLite version 3.11.1" > "no such module: geopoly" > > I guess that makes sense in one way, but it begs the question of why the > shell and my version of SQLiteExpert find a way to unders

Re: [sqlite] Displaying row count

David Fletcher wrote:> Hi all, > Is there a mode in the sqlite shell, or some fancy extension, that will > display a row > number when outputting results? No. You'd have to modify the shell, or add the row_number() window function to the query. Regards, Clemens ___

Re: [sqlite] forming sqlite3_statements using stored sql

David Fletcher wrote: > create table if not exists StmtSQL ( > StmtNameTEXT NOT NULL UNIQUE, > SQL TEXT NOT NULL); > > The GetPreparedStmt() function retrieves the SQL from table, creates a new > sqlite3_statement object (or retrieves this from a cache).

Re: [sqlite] Help!

am...@juno.com wrote: > how to take a database in database (not structure) form--and copy and paste > it into an e-mail A database file is binary; the easiest way would be to attach it. If you want to paste it into the e-mail itself, you have to convert it to text somehow. Either create a bunch

Re: [sqlite] HELP!

am...@juno.com wrote: > I work for a company that has a many locations with more than one > person in every location. I want to share the databases I have built > using SQLITE with some of the people in each location. Do any of you > good people know is SQLITE on the cloud? It's not; SQLite is fil

Re: [sqlite] Library Files on my Computer

Giovanni Giusti wrote: > they are offsite What do you mean with that? Do you get an error message when you try to access them? > and end in .sqlite-wal If there is no corresponding .sqlite file (without the "-wal"), then those files are not actual SQLite database files. Regards, Clemens __

Re: [sqlite] Claimed vulnerability in SQLite: Info or Intox?

Dominique Devienne wrote: > I'd like an official stance on SQLite itself please. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/

Re: [sqlite] i Know i should use 'AS', but ....

Luuk wrote: >sqlite> .mode column >sqlite> .headers on >sqlite> select 1 as X,date() as d union all select 2,date() union all >select 3,datetime(); >X   d >--  -- >1   2018-12-25 >2   2018-12-25 >3   2018-12-25 The value is longer than the column wi

Re: [sqlite] ROLLBACK TO stmt within committed transaction cause write operation to the WAL file.

sanhua.zh wrote: >BEGIN IMMEDIATE; >SAVEPOINT s1; >INSERT INTO t VALUES(2); >ROLLBACK TO SAVEPOINT s1; >COMMIT; >SOMETHING appended into the end of WAL file. >BUT why? We should have nothing to write. It's information about the WAL file itself. The WAL file must be initialized at some time, so i

Re: [sqlite] sqlite trig/geometry error

Brent Wood wrote: > Is there an easy way to tell where the sqlite math functions are coming from? Are you using the sqlite3 command-line shell, or something else? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http:

Re: [sqlite] SQLITE 3.26.0 compiler warning

Gary Sanders wrote: > shell.c(16466): warning C4996: 'strdup': The POSIX name for this item is > deprecated. strdup() is now in the dynamic memory TR: . The 'correct' way to get it is: #ifdef __STDC_ALLOC_LIB__ #define __STDC_WANT_LIB_EXT2

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: > I could use the inner join for the "entrys" join and the "items" join > but not the "entry-items" join because each entry can have more than > one item. WITH a(id, name) AS (VALUES (1, 'A')), b(id, name) AS (VALUES (1, 'B1'), (1, 'B2')) SELECT * FROM a INNER JOIN b

Re: [sqlite] Query Planning Knowledge

Andy Bennett wrote: >> foreign key constraints > > my experience with other engines taught me that it makes experimenting at the > monitor harder. Then don't use them. :) But do you actually want 'wrong' data? > Are there any efficiency benefits or is it just there to enforce data > integrity?

Re: [sqlite] SQLite linked to plugin binary

Carsten Müncheberg wrote: > A host application (which is a black box for me I cannot change) is loading > my plugin binaries which each have SQLite linked statically. When two > plugins now access the same database file from the process of the host > application none of the serialization mechanisms

Re: [sqlite] WAL mode readonly errors to SELECT statements

Robert Searle wrote: > We have recently started trying to provide read-only access to the database > (service run as user with group/other read access permissions under Linux, > service not database owner) and occasionally get either > SQLITE_READONLY_RECOVERY or SQLITE_READONLY_CANTINIT responses

Re: [sqlite] Displaying hierarchical structure

Bart Smissaert wrote: > ID PARENT_ID FOLDER RANK > --- > 1 0 Main1 > 2 1 CC 1-02 > 3 1 BB 1-03 > 4 1

Re: [sqlite] Checking differences in tables

Jose Isaias Cabrera wrote: > t_20190208 (a PRIMARY KEY, b, c, d, e). > > I create a new table, > > t (a PRIMARY KEY, b, c, d, e) > > and insert a set of "new data", which contains changes that happened since > yesterday > after the new set of data was created. Right now, I bring the data out into

Re: [sqlite] sqlite 3.37.1: void function returns value

Peter da Silva wrote: > I am pretty sure that the code is not legal C Indeed; C99 and C11 say in 6.3.2.2: | The (nonexistent) value of a void expression (an expression that has | type void) shall not be used in any way [...] and in 6.8.6.4: | A return statement with an expression shall not appear

Re: [sqlite] Choosing one value from the group with priority

Ivan Krylov wrote: > select * from test where id in (1,2) group by id; Please note that this is not standard SQL; SQLite allows to SELECT columns that are not mentioned in the GROUP BY clause, but they get their values from a random row in the group.

Re: [sqlite] Retrieve tables accessed by query

Zach Wasserman wrote: > Is anyone aware of an API I can use to determine which tables are accessed > by a given query? https://www.sqlite.org/c3ref/set_authorizer.html Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

Rocky Ji wrote: > But everyone advices against nested select statements. Who? I've heard rumors that older version of the Oracle query optimizer did worse with subqueries than with joins, but such advice is not necessarily correct for SQLite. SQL is set-based language, and queries that are writt

Re: [sqlite] How to refer to `this` table?

Rocky Ji wrote: > CREATE TABLE Aliases ( > alias_id INTEGER PRIMARY KEY AUTOINCREMENT, > real_name TEXT NOT NULL, > aka TEXT NOT NULL, > CONSTRAINT xyz UNIQUE (real_name, aka), > CONSTRAINT noCircularRef_A CHECK ( > real_name NOT IN (SELECT aka FROM Aliases) > ), > CONSTRAINT noCi

Re: [sqlite] BigInt loss accuracy

Derek Wang wrote: > sqlite biggest int is supposedly 9,223,372,036,854,775,807 (9 and a bit > Quintillion), but when the number is larger than 1E+17, it loses some > accuracy when retrieving. In plain SQL, everything works fine up to the limit: create table t(i notoriously big integer); with

Re: [sqlite] Foreign Key constraint fails due to unrelated field

Jonathan Moules wrote: > UPDATE lookups set error_code=3 and exp_content_type='ogc_except' WHERE > content_hash = '0027f2c9b80002a6'; This fails because "3 and exp_content_type='ogc_except'" is interpreted as a boolean expression. To update multiple fields, separate them with commas: UPDATE l

Re: [sqlite] Recursive CTE on tree with doubly linked items

heribert wrote: > I've a tree with doubly linked items. I want to get all siblings of a tree > node. If you want them in order, you have to walk through the linked list: WITH SiblingsOf3 AS ( SELECT * FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree

Re: [sqlite] Developer questions about the Online Backup API

Simon Slavin wrote: > If the source database is changed while the Online Backup API is > running, it returns to the beginning of the database and starts again. The backup API must create a consistent snapshot of the source database, i.e., the result must be the exact state at some point in time wh

Re: [sqlite] bug report: UPSERT / INSERT ON CONFLICT PK Autoincrement

Stanislav Zabka wrote: > When conflict occurs, no import performs, but PK is incremented nevertheless. says: | Note that "monotonically increasing" does not imply that the ROWID | always increases by exactly one. One is the usual increment. However, | if an in

Re: [sqlite] filling a key/value table

Simon Slavin wrote: > I wanted to speak against including a BLOB field in a compound PRIMARY KEY. That depends on the size of the blob. If it contains 'normal'-sized values, it's just as efficient as other types. Regards, Clemens ___ sqlite-users mail

Re: [sqlite] Is there a way to select using cid?

Peng Yu wrote: > There are cid's for each table. Is there a way to use "select" with > cid's instead of their names? > > select * from pragma_table_info('test'); > cid nametypenotnull dflt_value pk > -- -- -- -- -- --

Re: [sqlite] Clear sqlite3 terminal enhancement

Jeffrey Walton wrote: > When working in the Linux terminal we can clear the scrollback with > the 'clear' command; and we can delete all history and scrollback with > the 'reset' command. I am not able to do the same within the sqlite3 > terminal. Those are programs run from the shell. So you can

Re: [sqlite] export tables

Mohsen Pahlevanzadeh wrote: > I need to export some tables with dot command, How I do it? https://www.sqlite.org/cli.html#csv_export Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-

Re: [sqlite] How to use ORDER BY on FTS5 table ?

PM Nik Jain wrote: > A SCAN is being performed on a fts5 table. I am not sure but I > think that means no index. > > sqlite> explain query plan select * from productsfts p where p.attributes > match '50'limit 6; > `--SCAN TABLE productsfts AS p VIRTUAL TABLE INDEX 1: Everything except "INDE

Re: [sqlite] Help with sqlite3_value_text

x wrote: >> As long as you use _value_bytes after _text you're fine... so if any >> conversion did take place the value will be right of the last returned >> string type. > > Could you explain that to me? I’m not sure why any conversion takes place > and, on reading the text below, I would’ve thoug

Re: [sqlite] Row values with IN

Simon Slavin wrote: > I think that the documentation doesn't sufficiently explain the problem. > Or maybe the use of IN or lists deserves its own page rather than being > buried in the extremely long page on expressions. says: | For a row-value IN operator, t

Re: [sqlite] Read/Write cycle

manojkumar schnell wrote: > What is the maximum read/write cycle? The database puts no limit on how often you can read or update data. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cg

Re: [sqlite] SQLite with single writer on Windows network share

Simon Slavin wrote: > setting the journal mode of the database to WAL will ... certainly lead to data corruption; WAL requires shared memory, which cannot work over a network filesystem. Regards, Clemens ___ sqlite-users mailing list sqlite-users@maili

Re: [sqlite] SQLite with single writer on Windows network share

Andrew Moss wrote: > ... an SQLite database hosted on a windows network share (using server > 2012 R2 or later). We are well aware this is not advisable There are three possible sources of network filesystem data corruption: 1) Bad locking implementations. Some Unix-y network filesystems prod

Re: [sqlite] Bug in table_info pragma

Simon Slavin wrote: > If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to > evaluate CURRENT_TIMESTAMP, find a string value like > '2019-05-17 12:10:43', and store that string in the schema. This keyword behaves magically. ANSI SQL-92 says: | The default value inserted in the colum

Re: [sqlite] Bug in table_info pragma

Simon Slavin wrote: > On 17 May 2019, at 1:33pm, Clemens Ladisch wrote: >> This keyword behaves magically. ... as far as the SQL standard is concerned. > Mmmm. In that case, to implement this properly you need to store > a default-type flag alongside the default value. Th

Re: [sqlite] RFE: allow parameters in PRAGMA statements

Wout Mertens wrote: > I am using the user_version pragma for implementing an event-handling > database. I'd like to prepare the statement to update it, e.g. `PRAGMA > user_version = ?`. > > However, sqlite3 won't let me do that, so I just run the text query every > time with the number embedded. <

Re: [sqlite] "unable to use function highlight in the requested context" in group by

Damian Adrian wrote: > While using the FTS5 highlight() function in a group by query like this one: > > SELECT > group_concat(highlight(entries, 1, '>', '<')) > FROM entries > WHERE entries MATCH 'an*' > GROUP BY id; > > I get "Error: unable to use function highlight in the requested context".

Re: [sqlite] BLOB and TEXT comparisons

Charles Leifer wrote: > SELECT SUBSTR(?, 1, 3) == ? > > However, if I mix the types, e.g. sqlite3_bind_text("abcde") and > sqlite3_bind_blob("abc") then the comparison returns False. > > Fom a byte-to-byte perspective, this comparison should always return True. > > What's going on? Apparently, not

Re: [sqlite] Determining valid statement placeholders.

test user wrote: > I want my library to be able to detect the problem programatically. > > I think SQLite internally knows how many placeholders are in the query at > parse time. > > My question is how can I get the data via the API At the moment, there is no such mechanism in the API. You could

Re: [sqlite] Does it make sense to COMMIT/ROLLBACK?

Igor Korot wrote: > If I execute "BEGIN TRANSACTION" and for whatever reason the call will fail > will I get an error on COMMIT/ROLLBACK execution? sqlite> begin immediate; Error: database is locked sqlite> rollback; Error: cannot rollback - no transaction is active Regards, Clemens

Re: [sqlite] Poll: Include the recent sqlite3_column_name() fix in the upcoming 3.20.0 release?

Richard Hipp wrote: > Cons: (5) The change might cause breakage for legacy applications that > depend on the older (arguably buggy) behavior. (6) This seems like a > big change to receive so little beta exposure prior to the official > release. I doubt that there are many applications that both d

Re: [sqlite] Problem with the new pointer-passing interface

Ulrich Telle wrote: > In the comment of the sqlite3_bind_pointer() routine I found this note: > > The T parameter should be a static string, preferably a string literal. > > In my case this is quite cumbersome, since in my wrapper I have to extract > the value of parameter T from a temporary string

Re: [sqlite] SQLITE PHP syntax issues - INSERT INTO db

Edmondo Borasio wrote: > //If I input the $NewID manually as a string (for example '6', *it works > fine* and updates the db correctly) > *$query1="INSERT INTO > Table"."(ID,name,surname)"."VALUES('6','newName','newSurname');"; * > > //However if I try to use the $NewID variable from above *it does

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

Lars Frederiksen wrote: > CREATE TABLE gms( > gms_id INTEGER PRIMARY KEY, > gms_verb TEXT NOT NULL > ); > > FDTable1.Append; > FDTable1.FieldByName('gms_verb').AsString:= Edit1.Text; > FDTable1.Post; > > But when I put a string in the table 'gms_verb' I only get the primary key > number - the strin

Re: [sqlite] TEXT shows as (WIDEMEMO) in DBGrid

Lars Frederiksen wrote: > I have followed 2 tutorials about SQLite, and none of these mentioned the > VARCHAR() possibility Because SQLite pretty much ignores column types. Interpreting "VARCHAR" this way is how FireDAC does things; you have to look into the FireDAC documentation. > But I al

Re: [sqlite] Optimizing searches across several indexes

Wout Mertens wrote: > I have a table with a bunch of data (in json). I want to search on several > values, each one is indexed. However, if I search for COND1 AND COND2, the > query plan is simply > > SEARCH TABLE data USING INDEX cond1Index (cond1=?) > > Is this normal? Yes. A query can use only

Re: [sqlite] Packing integer primary key with field bits

x wrote: > I’m thinking about this more from the gain in speed rather than saving space. Database performance is usually limited by I/O, i.e., you gain speed by saving space. > I have done my homework on this So what are the results of your measurements? Regards, Clemens __

Re: [sqlite] What's the level of B+-Tree ?

ni james wrote: > In the "SQLite File Format" document, the BTree layout is described, > but now I want to know how to get the BTree level (which is the 'K' > value mentioned in the Documentation)? At the end of section 1.5, a "K" is defined. But I don't think that is the same K. Anyway, the doc

Re: [sqlite] What's the level of B+-Tree ?

james ni wrote: > As in the example that I provided, there are 4 cells in a single btree > page. So there must be some mechanism to determine hoe many keys that > one cell can own. One key per cell: | Within an interior b-tree page, each key and the pointer to its | immediate left are combined int

Re: [sqlite] What's the level of B+-Tree ?

james ni wrote: > the INSERT speed is becoming slower and slower; > > the number of syscalls are increasing quickly; Insert the largest values last. Increase the cache size: . Decrease the amount of data stored in the index. (This is unlikely

Re: [sqlite] Fwd: How can I make this faster?

J Decker wrote: > So I have this sql script that inserts into a single table, and it is VERY > slow. Wrap everything into a single transaction. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglist

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

sanhua.zh wrote: > All 1. 2. 3. steps are run sequentially, which means that the step 2 > runs after step 1 finished and step 3 runs after step 2 finished > theoretically . > Also, I can make sure the memory order between threads. > > Then, is it a safe way to use sqlite connection ? Yes. Multi-t

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué wrote: > Serialized accesses from multiple threads is OK when the connection is > in the "Multi-thread" or "Serialized" threading modes, but not in the > "Single-thread" threading mode. says: | 1. *Single-thread*. In this mode, all mutexes are d

Re: [sqlite] Is it safe to use same sqlite connection sequentially between threads ?

Gwendal Roué wrote: >> Le 16 août 2017 à 08:38, Clemens Ladisch a écrit : >> Gwendal Roué wrote: >>> Serialized accesses from multiple threads is OK when the connection is >>> in the "Multi-thread" or "Serialized" threading modes, but not in t

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

sanhua.zh wrote: > 1. Conn A: Open, PRAGMA journal_mode=WAL > 2.ConnB: Open, PRAGMA journal_mode=WAL > 3.ConnA: CREATE TABLE sample (i INTEGER); > 4.ConnB: PRAGMA table_info('sample') > > Firstly, both thread 1 and 2 do initialization for their own conn, which is > to read to schema into memory. >

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

Jay Kreibich wrote: > On Aug 18, 2017, at 5:33 AM, Clemens Ladisch wrote: >> sanhua.zh wrote: >>> 1. Conn A: Open, PRAGMA journal_mode=WAL >>> 2.ConnB: Open, PRAGMA journal_mode=WAL >>> 3.ConnA: CREATE TABLE sample (i INTEGER); >>> 4.ConnB: PRAGMA tabl

Re: [sqlite] offset of file position is beyond EOF

Jacky Lam wrote: > I recently meet a case that the file position offset of a pager is beyond > EOF position of db. says: | The lseek() function shall allow the file offset to be set beyond the end | of the existing data in the

  1   2   3   4   5   6   7   8   9   10   >