Re: [sqlite] thousand separator for printing large numbers

Dominique Devienne wrote: > But there's got to be a better way, no? A database stores data. Formatting the data for the user is not the job of the database but of the actual application. > Couldn't SQLite's built-in printf gain a thousand-separator formatting > argument, which doesn't need to be

Re: [sqlite] thousand separator for printing large numbers

Dominique Devienne wrote: > On Fri, Feb 10, 2017 at 6:53 PM, Stephen Chrzanowski > wrote: >> The date and time are stored as a number, not >> "Friday, February 10, 2017 12:43:33pm". > > And that's exactly why SQLite has date and time functions. > Notably the one converting a number of seconds sinc

Re: [sqlite] thousand separator for printing large numbers

James K. Lowden wrote: > I doubt you'll win that argument. You should have checked before writing this. ;-) http://www.sqlite.org/cgi/src/info/064445b12f99f76e Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://

Re: [sqlite] Extension Loading

Green Fields wrote: > conn.EnableExtensions(true); > conn.LoadExtension("mod_spatialite"); > > works fine, but I need to use the SQL function > > conn.EnableExtensions(true); > cmd.CommandText = @"SELECT load_extension('mod_spatialite');"); > int i = cmd.ExecuteNonQuery(); > > raises > > System.Dat

Re: [sqlite] sqlite3_blob_bytes64() ?

Olivier Mascia wrote: > What is the purpose of sqlite3_bind_blob64() and sqlite3_bind_zeroblob64()? To allow 64-bit types (but not necessarily 64-bit values). IIRC some language binding needed this. Regards, Clemens ___ sqlite-users mailing list sqlit

Re: [sqlite] sqlite3 hangs on query

Jens-Heiner Rechtien wrote: > SELECT count(*) > FROM spacefavorite, album_asset, albums, assetProfileLinks, > avatarCacheReferences, > cacheReferences, comment, conflicts, coreInfo, coreMD5, errors, flags, > importSource, missingBinariesOnOz, profileRegistration, quota_exceeded, > r

Re: [sqlite] Thread safety of serialized mode

Jens Alfke wrote: > With clock speed having stalled, the only way to take advantage of > modern CPUs (and GPUs!) is to go parallel. But "go parallel" does not necessarily imply threads. There are many ways to allow code running on different CPUs(/cores) to communicate with each other (e.g., files

Re: [sqlite] Group contiguous rows (islands)

Rossel, Jonathan wrote: > Other database engines have solutions for this task (like windowing in > postgre) but I wonder if there is an efficient recipe in SQLite. SQLite does not have windowing functions. So the most efficient method would be to read the data with a simple ORDER BY, and do the g

Re: [sqlite] Thread safety of serialized mode

Cecil Westerhof wrote: > 2017-02-15 8:58 GMT+01:00 Clemens Ladisch : >> Threading is the most extreme method of achieving parallelism, and >> therefore should be used only as the last resort. (I'd compare it to >> assembly code in this regard.) > > ​At the moment

Re: [sqlite] WAL journal mode & sqlite3_backup_step()

Olivier Mascia wrote: > A good approach ... is to drive the backup by a single call to > sqlite3_backup_step() This is indeed what you should do with WAL. > The only downside is that I loose the capability to monitor (or inform users > if needed) of the backup progress. That was never the prim

Re: [sqlite] Thread safety of serialized mode

Bob Friesenhahn wrote: > Does anyone have an idea about this specific problem that we encountered? > > It is not clear to me if this is a threading issue, or memory corruption issue It's probably memory corruption causes by a threading issue. The original mail said: > Any thread may acquire and u

Re: [sqlite] Thread safety of serialized mode

Jens Alfke wrote: > PS: I’m not aware of _any_ current CPUs that can increment main memory > in one instruction, atomically or not. X86(-64) has always had "INC [mem]" and "LOCK INC [mem]". And MSP430 calls itself RISC, but is so orthogonal that any operand can be in memory. Regards, Clemen

Re: [sqlite] Thread safety of serialized mode

Jens Alfke wrote: > A read-modify-write cycle on an address in main memory is intrinsically > _not_ atomic on a multiprocessor system, not unless the CPU goes through > some expensive efforts to make it so (cache invalidation, bus locking, > etc.) Most modern CPUs have caches, and any _normal_ mem

Re: [sqlite] SQLite as a Delphi unit (was: SQLite Options)

Stephen Chrzanowski wrote: > Ever since I found SQLite3 and a decent wrapper that does exactly what I've > needed it to do, I've ALWAYS wanted to do a direct port of the Amalgamation > into a Delphi/Pascal unit so I can just include it and have the > functionality built in, period, built by the Pas

Re: [sqlite] WAL Checkpoint Blocking Behavior

Matt Fichman wrote: > I plan to disable WAL autocheckpoints and manually run permissive WAL > checkpoints on a second thread (as hinted at by the docs). > > If I do this, can a WAL checkpoint on the checkpoint thread still block or > significantly delay queries/updates from the main thread? The do

Re: [sqlite] Non-unique columns in unique index

Deon Brewis wrote: > Is there way to add non-unique columns in a unique index? > > I would like to use the same index to enforce unique constraints, as well as > giving a covered result for other queries. > > CREATE UNIQUE INDEX indx ON Foo(UniqueCol) INCLUDE (ExtraCol) CREATE TABLE Foo ( Uniq

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > I have to call sqlite3_blob_write to partially modify blobs. > > Wouldn't it have been better if there was a function, for example, > writeblob(tbl.blob_fld, offset, portion_data)? SQLite could do the > same that sqlite3_blob_write is doing when writeblob result is > assigned to the s

Re: [sqlite] Deleting records from a large table

Dave Blake wrote: > For deleting a large number of records (tmp_keep is small), this works > fine: > DELETE FROM table1 WHERE id1 NOT IN (SELECT id1 FROM tmp_keep); > > But this becomes inefficient when tmp_keep is large. SQLite usually creates a temporary index for the values in the IN clause. Ho

Re: [sqlite] Index usefulness for GROUP BY

Jeffrey Mattox wrote: > is my index on weekday worthwhile, time-wise and space-wise? (Query > speed is not a big issue for me, and the DB is relatively small Indexes are optimizations. In a small DB, the effect is probably not noticeable, which implies that you should not bother. Where exactly

Re: [sqlite] Deleting records from a large table

Dave Blake wrote: > Yes the id1 are integer primary keys. In both tables? If yes, then there is not much you could do, and the problem probably are all the modifications done to the actual table and its indexes. Regards, Clemens ___ sqlite-users maili

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > On 03/01/2017 23:41, Clemens Ladisch wrote: >> It would certainly be possible to add your own user-defined SQL function >> to call sqlite3_blob_write(). > > I think this should be added to sqlite itself. So far, nobody except you has requested it. > Writ

Re: [sqlite] Massively multithreaded SQLite queries

Andrew Brown wrote: > I find that we spend a lot of time in __raw_spin_lock Please try to find out (stack trace, etc.) which particular resource is protected by that lock. If, for example, this would happen to be file lock operations, then you would need to use fewer transactions (by wrapping mul

Re: [sqlite] Multiple tables or not

Joshua Grauman wrote: > would it make sense to split the table into two tables, where the > first column (the ID) is identical, but the first table only has the > first 3 columns and the second table has the rest of the columns. > Would that make reading the smaller table with only the 3 columns >

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > So far sqlite is failing, because the handle returned by > sqlite3_blob_write is invalidated by the updates to the other fields, This would happen regardless of whether you call sqlite3_blob_write() yourself or through an SQL function. > and sqlite3_blob_open/sqlite3_blob_close are

Re: [sqlite] BUG: blob handles are invalidated in too many tables (was: Can sqlite3_blob_write be done through SQL?)

Yuri wrote: > even updates to other tables invalidate the blob handle. Please see the > example below. This is a bug. Apparently, it was introduced in commit f17ef37897da9bca, where the function invalidateIncrblobCursors() forgets to check the pgnoRoot parameter, which means that blob cursors ar

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Simon Slavin wrote: > You’re going to have to wait for the developer team to fix the bug, or change the row IDs so that they are unique in the entire database. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://m

Re: [sqlite] Need some help running sqlite3 command line

Jacob Sylvia wrote: > Basically, I cant figure out how to update a text field in the sqlite > config database from the command line... sqlite3 config.db "UPDATE SomeTable SET SomeColumn = 'value' WHERE ID = 42" You need to know the database file name, the table name, the column name(s), the new v

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

Yuri wrote: > Updating an integer field in the same row shouldn't affect the blob field. Integer fields have a variable size (0 to 8 bytes): http://www.sqlite.org/fileformat2.html#record_format Regards, Clemens ___ sqlite-users mailing list sqlite-user

Re: [sqlite] Need some help running sqlite3 command line

Jacob Sylvia wrote: > The problem lies in the 'value' part. Because the text has line breaks / > carriage returns, I can't figure out how to do it appropriately from the > command line. Just use line breaks: $ sqlite3 ":memory:" "select 'first line > second line';" first line second line

Re: [sqlite] sqlite3 feature or regression

Vermes Mátyás wrote: > http://comfirm.hu/pub/sqlite3-regression.rb > db.execute("select szamla,megnevezes from proba") do |row| > ... > db.execute( "update proba set megnevezes=? where szamla=?", > row[1]+"*", row[0] ) The equivalent Python code would be: for row in db.exe

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Simon Slavin wrote: > I’ve seen many amusing examples of using Common Table Expressions to > solve Sudoko puzzles. Has anyone tried using one to suggest the best > next move for Minesweeper ? https://en.wikipedia.org/wiki/Minesweeper_(video_game)#Computational_complexity > have SQLite suggest a

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

Brian Curley wrote: > What I wonder though is if CTEs could actually serve as a stand-in for the > lack of Dynamic SQL Recursive CTEs make SQL Turing complete. But they cannot do everything. For example, when you want to do a pivot operation, the number of columns is determined by the data, and

Re: [sqlite] SQLite Recursive Common Table Expression suggestion

James K. Lowden wrote: > Clemens Ladisch wrote: >> Recursive CTEs make SQL Turing complete. >> >> But they cannot do everything. > > Isn't that a contradiction? Being able to emulate a Turing machine (or a register machine) means that there exists _some_ represen

Re: [sqlite] Is this safe use of SELECT in an INSERT?

Graham Holden wrote: > insert or ignore into Servers values ('MyServer',12345,( select > count (*) from Servers ) ) > > is the "count (*)" guaranteed to be the count before the insert? Yes. But if any row was ever deleted, that value might alread by used. You could make serverIdx au

Re: [sqlite] SQL help: by-range aggregation

Dominique Devienne wrote: > On Wed, Mar 8, 2017 at 3:16 PM, Hick Gunter wrote: >> Alternatively create a "range" table, insert your defined ranges and >> join/subquery to the original query. > > That's the easy part [...] > but the join/subquery you mention is still leaving me scratching my head..

Re: [sqlite] Can sqlite3_blob_write be done through SQL?

Yuri wrote: > commit of the subsequent unrelated transaction fails with error=5 > (SQLITE_BUSY) if the previous open sqlite3_blob object hasn't been > closed. > > This fails: > > sqlite3_blob_open // table b with rowids unique in db > sqlite3_blob_write // table b > BEGIN > UPDATE // table a > UPDA

Re: [sqlite] How to configure size of shared-cache in SQLite?

Qiu Xiafei wrote: > SQLite provide a shared-cache mode (https://www.sqlite.org/sharedcache.html) > which will help to reduce cache size among multiple connections within a > process. But how to configure the size of the shared cache? There is not special shared cache; the shared-cache mode just sh

Re: [sqlite] How to use parameterized queries in SQLite.Net

Chris Locke wrote: > From a newbie's point of view, how is this better (if doing it in 'hard > coded' format like below) than writing this code: > > command.CommandText = string.format("INSERT INTO trend_data (tag_key, > value, value_timestamp) VALUES ({0}, {1}, {2})",2,234.56,now); Using paramete

Re: [sqlite] feature req: PLEASE why the heck COMMENT fields are not supporte in years!!

PICCORO McKAY Lenz wrote: > an important feature in a DB its the column field that gives to developers > metadata info INDEPENDENT of the tecnologies used, due by this way with a > simple text editor in generated script developer can read and use minimal > info for understanding structure ... Ther

Re: [sqlite] bounded id

Jan Danielsson wrote: > I can't make the rowid a uint32_t, but that's essentially the > behavior I'm looking for CREATE TABLE transfers ( ID INTEGER PRIMARY KEY CHECK (ID BETWEEN 0 AND 4294967295), [...] ); But if you want the values to wrap around after old ones have been deleted, you

Re: [sqlite] Please help to create a connection to SQLite database!

Evgeniy Buzin wrote: > "Settings "Prism.Mvvm.ViewModelLocator.AutoWireViewModel" property has > called exception...". That "..." contains relevant information. > System.Windows.Markup.XamlParseException > > What is the reason of this error? Something related with XAML parsing. So not related wi

Re: [sqlite] Drop view “automatically” when its associated table is dropped?

Dominique Devienne wrote: > On Wed, Mar 22, 2017 at 12:52 PM, R Smith wrote: >> There is no way to easily implement an automatic view-of-table >> dropper. (I've tried to do something like this for an sqlite tool long ago). > > Yes but... You can run a simple query on the view, like "select rowid f

Re: [sqlite] about sqlite db.delete the file

z...@tofocus.cn wrote: >recently, I have a prolbem, that my android(4.3) application use sqlite db > to store the data. > my had two thread visit the db, and offten happen delete db table, so my > data lose. > [...] > 03-18 14:25:04.194 2345-2384/? E/SQLiteLog: (26) statement aborts at 0:

Re: [sqlite] What are the rules for defining names for tables and columns?

em...@n0code.net wrote: > I’ve scoured the archives and the sqlite documentation but can’t find the > definitive rules > for defining table and column names. Everything is allowed, except names beginning with "sqlite_". > I did see we can’t use keywords The documentation disagrees: http://www.s

Re: [sqlite] Second beta for SQLite 3.18.0.

Daniel Polski wrote: > Can using "PRAGMA optimize" in one thread create a situation where > "BEGIN IMMEDIATE TRANSACTION" in another thread fails? While the optimization is done, the database is locked, just as with any other transaction that does writes. So any other thread has to use a timeout.

Re: [sqlite] core using sqlite 3.17.0 on solaris

Richard Hipp wrote: > #ifdef sqlite3Parser_ENGINEALWAYSONSTACK > - unsigned char zSpace[sizeof(yyParser)]; /* Space for parser engine object > */ > + /* Space to hold the Lemon-generated Parser object */ > + sqlite3_uint64 zSpace[sizeof(yyParser)/sizeof(sqlite_uint64)]; > #endif The yyParse

[sqlite] BUG: CLI generates invalid SQL for strings with many newlines

Hi, since commit 68f6dc7af1013f29, newlines in the .dump output are escaped with char(). This can make the resulting SQL too complex: $ sqlite3 test.db "create table t(x); insert into t values(replace(printf('%1000s', ''), ' ', char(10)));" $ sqlite3 test.db .dump | sqlite3 Error: near line 4:

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

James K. Lowden wrote: > Clemens Ladisch wrote: > >> since commit 68f6dc7af1013f29, newlines in the .dump output are >> escaped with char(). > > Why not use vis(3) instead? Because vis() is a nonstandard function that is not available everywhere, and in any case

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

Charles Leifer wrote: > This bit me... I fat-fingered a command and deleted my database. I had a > backup dump taken earlier in the day. Go to restore it and all of a sudden > this error starts cropping up. > > What to do? From the link the in the first post: sed -e "s/'||char(10)||'/\\n/g" < w

Re: [sqlite] BUG: CLI generates invalid SQL for strings with many newlines

James K. Lowden wrote: >>> Why not use vis(3) instead? >> >> Because vis() is a nonstandard function that is not available >> everywhere, > > "everywhere" is a high standard, but vis is freely available and > included or packaged with almost anything not Windows. It is not included in the distribu

Re: [sqlite] recursive clause

Cem Dayanik (Ibtech-Software Infrastructure) wrote: > CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress > INTEGER) > CREATE TABLE Instances (TypeId INTEGER, Address INTEGER) > CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, > TotalSize INTEGER) > >

Re: [sqlite] sqlite db file descriptor access/manipulation

Valery Reznic wrote: > I am trying to find a way to get access to sqlite db file descriptor, after > sqlite3_open. > I want to be able set O_CLOEXEC flag to this fd, andr make sqlite use highest > possible fd. There is sqlite3_file_control(), but it does not give you access to the file handle.

Re: [sqlite] Testing sqlite db to see if exists & ready

dave boland wrote: > Being a little paranoid, I like to insure that the db file exists SQLite automatically creates an empty DB if you try to open a nonexistent file, so you do not actually need to do anything. > and what state it is in (unconfigured, so needs to be made > ready; or ready to acce

Re: [sqlite] Testing sqlite db to see if exists & ready

David Raymond: >https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers > >For the sqlite3 module connection object: on a bad exit from an >exception it will do a rollback, on a clean exit it will do a commit, >and run .close() either way. It does not run .close(). Reg

Re: [sqlite] Testing sqlite db to see if exists & ready

Simon Slavin wrote: > On 7 Apr 2017, at 6:58am, Clemens Ladisch wrote: >> David Raymond: >>> https://docs.python.org/2/reference/datamodel.html#with-statement-context-managers >>> >>> For the sqlite3 module connection object: on a bad exit from an >>>

Re: [sqlite] Create view from a single table as one to many

Joseph L. Casale wrote: > I have a table t1 that I am trying to create a view from where the > view will produce many rows for each row in t1. The resulting data > is a set of rows which assign a text string for each bit flag present in > a column in t1 from an enum I reference. > > How does one co

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

Keith Maxwell wrote: > Can anyone please explain the results of the query below? > > sqlite> WITH > ...> t1(X) AS ( > ...> SELECT 1 > ...> UNION ALL > ...> SELECT X+1 FROM t1 > ...> LIMIT 9 > ...> ) > ...> ,t2(Y) AS ( >

Re: [sqlite] Which pragmas are persistent?

Jens Alfke wrote: > I'd like to know which pragmas I need to reissue every time the > database connection is opened, and which I only need to issue when > initializing a new database. http://stackoverflow.com/documentation/sqlite/5223/pragma-statements/18507/pragmas-with-permanent-effects says the

Re: [sqlite] SAVEPOINT savepoint-name [DEFERRED|IMMEDIATE/EXCLUSIVE]?

Olivier Mascia wrote: > "When a SAVEPOINT is the outer-most savepoint and it is not within > a BEGIN...COMMIT then the behavior is the same as BEGIN DEFERRED > TRANSACTION." > > What are the specific reasons for SAVEPOINT to be limited to BEGIN > DEFERRED in that case? A BEGIN without a type is DE

Re: [sqlite] Is it possible to create the Stored Procedure (SP) in Sqlite?

Olivier Mascia wrote: > As far as I understood, SQLite will parse and compile the trigger text > as part of each statement using them. No bytecode compilation upfront, > nor storage of it. SQLite parses all triggers (and all other schema objects) when it reads the schema (see "struct Trigger" and

Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Howard Kapustein wrote: > I'm setting SQLite logging a warning > Warning 0x11C: automatic index on PackageIdByAll(_PackageID) > > WITH ... > PackageIdByAll(_PackageID) AS (...) > SELECT * > FROM ... > INNER JOIN PackageIdByAll AS cte_p ON cte_p._PackageID=pkg._PackageID > > Why? > > Package

Re: [sqlite] Performances and Foreign keys

Bubu Bubu wrote: > Can someone tell me if there can really be performance issues when one uses > foreign keys in their database? The documentation warns against a case where some operations can be slow if you do not have an index on the child key

Re: [sqlite] Best practices for huge batch inserts with WAL?

Scott Hess wrote: > WRT #3, you could also consider journal_mode off (or memory, if your code > requires transactions to work right). In that case, the database state is > indeterminate if you have an app-level crash, but you should be fine if you > make it to the end. It would be a better idea t

Re: [sqlite] 0x11C: automatic index warning for CTEs ?

Howard Kapustein wrote: >> you might have to change the query itself > > The question is how? The database has estimated that even with the cost of creating the temporary index, this way is the fastest. So it's unlikely that there is another way that would be an improvement. Anyway, try using th

Re: [sqlite] Increasing performance of query

Gabriele Lanaro wrote: > Are there any tips to make sure that the cache is used properly? The default is 2000 pages (8 MB with the default page size), and in most cases should be increased. But what works best in your specific case depends on the hardware, the OS, the software, the database schem

Re: [sqlite] Semantics regarding command instances and queries in the C# client

Joseph L. Casale wrote: > that practice looks a bit ugly. Show some example. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Controlling the lifetime of shared-cache, in-memory SQLite databases.

Randall Smith wrote: > A shared-cache, in-memory database is "persistent" across connections > from the same process that do not overlap in time? No. says: | The database is automatically deleted and memory is reclaimed when the | last connectio

Re: [sqlite] Semantics regarding command instances and queries in the C# client

Joseph L. Casale wrote: > Each row from the CSV required several tables with relationships to be > populated. One could > certainly abstract this out into an api, but that can have impacts on > performance for large > batch processing if you are creating parameters for every insert rather than >

Re: [sqlite] Thread notification for new record in a table.

Rowan Worth wrote: > Is the database being updated by a separate process, or by another thread > in the same process? > [...] > If the latter then there's plenty of ways the thread updating the database > can notify the waiting thread, depending on your platform(s) and > language(s) of choice. If

Re: [sqlite] .DUMP displays floats differently from SELECT

Tony Papadimitriou wrote: > Apparently, this was an intentional change by this check-in : > > [7359fcac] Increase the number of significant digits in floating point > literals on ".dump" output from the shell. > > I don't know what problem this change actually solved Scott Robinson already mentio

Re: [sqlite] SQLiteDiskIOException: disk I/O error (code 1034)

Rowan Worth wrote: > These days (the past 8 years?) at least there's the SQLITE_FCNTL_LAST_ERRNO > parameter to sqlite3_file_control() allowing the underlying cause to be > introspected, I just feel like it was a mistake to ever mask that cause. That error code is behind a FCNTL because it is not

Re: [sqlite] foreign key constraint failure

Mark Wagner wrote: > Is there a way to get sqlite to tell which foreign key constraint is > causing a failure? No; to make the implementation of deferred constraints easier, it keeps track only of the number of remaining foreign key failures, not of their origin. Regards, Clemens ___

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

Richard Hipp wrote: > ** ^When a table is referenced by a [SELECT] but no column values are > ** extracted from that table (for example in a query like > ** "SELECT count(*) FROM tab") then the [SQLITE_READ] authorizer callback > ** is invoked once for that table with a NULL column name. The docum

Re: [sqlite] Proposition: introduce a new SQLITE_READ_TABLE Authorizer Action Code

Richard Hipp wrote: > On 5/11/17, Clemens Ladisch wrote: >> Richard Hipp wrote: >>> ** ^When a table is referenced by a [SELECT] but no column values are >>> ** extracted from that table (for example in a query like >>> ** "SELECT count(*) FROM tab&quo

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

Nelson, Erik - 2 wrote: > for aggregate functions, is xFinal called if there's an error? Yes; it's always called when SQLite cleans up the context. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.

Re: [sqlite] Bulk load strategy

Joseph L. Casale wrote: > I am deferring index creation to after the load. > The load proceeds along quickly to about 150k records where I encounter > statements > which perform modifications to previous entries. Without an index, searching for a previous entry is likely to involve a scan through

Re: [sqlite] sqlite3_create_function xFinal argument called when there's an error?

Nelson, Erik - 2 wrote: > should any result be set in xFinal? Like sqlite3_result_error? Or the > previously-returned xStep error is sufficient? The sqlite3_result_xxx() documentation documents what happens when you call it multiple times (i.e., the later call overrides the earlier value). You

Re: [sqlite] Index storage space

xTom Byars wrote: > I’m aware that a column value of 1 will use less bytes in an sqlite database > than a value of 4875874568437607. Does this also apply to indexes? Yes; tables and indexes use the same record format in which the integers 0 a

Re: [sqlite] explain query plan for 'where ID between ...'

xTom Byars wrote: > Returns > > SEARCH TABLE Tbl USING PRIMARY KEY (ID>? AND ID > Should that be (ID>=? AND ID<=?) explain query plan select * from t where x between 1 and 42; 0|0|0|SEARCH TABLE t USING PRIMARY KEY (x>? AND x= 1 and stops with > 42. The EQP output does not bother to always use th

Re: [sqlite] UTF8 LIKE stranges

Vlczech - Tomáš Volf wrote: > CREATE TABLE people ( > firstname TEXT, > surname TEXT > ); > INSERT INTO people('Tomáš', 'Surname'); > > "SELECT * FROM people WHERE firstname LIKE ?" > For binding I use: sqlite3_bind_text(stmt, 1, name.c_str(), -1, > SQLITE_STATIC); SQLITE_STATIC works only i

Re: [sqlite] Group by Literals

Denis Burke wrote: > The SQLite documentation (http://www.sqlite.org/lang_select.html) says the > GROUP BY clause accepts [expr]. And [expr] can be composed of a literal. > What I cannot find is what SQLite does (or should do) with a literal in the > GROUP BY clause. SQL-92 doesn't allow it: | Ea

Re: [sqlite] Memory Issue - GLOBs and Windows Active Mapped File

Jamie wrote: > this is not the normal Windows File Caching that you would typically > see. File Caching would be under STANDBY Mapped File To rule out SQLite's mmap, execute "PRAGMA mmap_size = 0", and then confirm with the output of "PRAGMA mmap_size".

Re: [sqlite] FTS5 performance problem.

John Found wrote: > the execution time increases by a factor of 100 > > select count() from PostFTS where PostFTS match 'innermost' and ThreadID = 6; This is the same as: select count() from PostFTS where PostFTS match 'innermost' and PostFTS match 'ThreadID:6'; It might be better to use a si

Re: [sqlite] FTS5 performance problem.

John Found wrote: > Pseudocode: ?1 = keyword + ' ThreadID:' + num2str(threadID); > > select count() from PostFTS where PostFTS match ?1; That works. > ?1 = keyword > ?2 = threadID > select count() from PostFTS where PostFTS match ?1 ThreadID:?2; That is not valid SQL. You have to create a singl

Re: [sqlite] CLI option to NOT autocheckpoint WAL databases

Howard Kapustein wrote: > Is it possible to use the CLI to read a WAL database and exit without > modifying the database? The checkpoint-on-close feature is not affected by PRAGMA wal_autocheckpoint. The NO_CKPT_ON_CLOSE DB config flag is the only mechanism to prevent it from inside the connectio

Re: [sqlite] Does prepare do arithmetic?

x wrote: > The explain for > > Select * from Tbl where Col = (select min(OtherCol) from OtherTbl); > > I’m having problems with. EXPLAIN Select * from Tbl where Col = (select min(OtherCol) from OtherTbl); addr opcode p1p2p3p4 p5 comment -

Re: [sqlite] Importing from single-insert-statement SQL dump is 61 times slower than importing from SQL dump with one statement per row

Sarge Borsch wrote: > time xzdec something.sql.xz | sqlite3 something.db This measures only xzdec; it does not catch anything that sqlite3 does after xzdec has finished and closed the pipe. > IMO sqlite needs some optimisation for this case when there’s a huge > INSERT statement, because the spee

Re: [sqlite] Sqlite Linux support

Radhakant Kumar wrote: > On windows i got 3.18.0 and on Linux 3.6.20. Which Linux distribution are you using? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sq

Re: [sqlite] Last character "]" gets corrupted in SQLITE rarely

Pavel Machala wrote: > I am struggling with this very random and rare issue where last character > of string - always “]” in multiple columns is replaced with one or more > unexpected characters. SQLite does not change the contents of strings, and treats them exactly like blobs (unless you excplic

Re: [sqlite] WAL checkpoint starved?

Daniel Polski wrote: > Any ideas why I can end up with that large WAL file Sounds like checkpoint starvation. Does the checkpoint call actually succeed? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailingl

Re: [sqlite] memory usage after close database on linux arm

Stephane Guibert wrote: > I get memory usage not freed at close. > > the return is SQLITE_OK at each step: > > do{ > sqlite3_open_v2(...,&db,SQLITE_OPEN_READWRITE,0); > sqlite3_exec(db,"PRAGMA journal_mode = MEMORY",0,0,0); > sqlite3_prepare_v2(db... > sqlite3_step( statement Begin //BEGIN EXCLUSI

Re: [sqlite] [PATCH] fix (a little bit more of) `.mode column` with utf-8

Yuriy M. Kaminskiy wrote: > +** Compute a utf-8 string length (in characters rather than bytes) Counting characters is an improvement. But the number of characters is not necessarily the same as the number of column positions required to display the string. If you want to handle CJK characters l

Re: [sqlite] performance issue on a read only database

rv.gauth...@free.fr wrote: > I noticed that the first SELECT statement after a BEGIN TRANSACTION takes at > least 14 ms. > All subsequent queries in the same transaction are taking near 0 ms. > > If I omit the BEGIN TRANSACTION, all queries are taking at least 14 ms. Because then you get an autom

Re: [sqlite] Problem building amalgamation

Jeff Archer wrote: > I have just grabbed the sqlite3.c and sqlite3.h from another (working) > project and dropped then into a new project and I am seeing compile errors. > > New project is a C++ project SQLite is not C++ but C. Regards, Clemens ___ sql

Re: [sqlite] How to search for fields with accents in UTF-8 data?

CC wrote: > I imported a CSV file where data are encoded in UTF-8. > > Some of the characters (like Î) are not available in the ASCII table, so I > can't use the CLI sqlite3.exe to search. The latest version of sqlite3.exe might work. Anyway, to check that whatever tool you're using uses Unicode

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > Is there a C API way to get a total count or notification as each statement > is prepared by sqlite_prepare_v2 within sqlite3_exec? sqlite3_trace_v2() with SQLITE_TRACE_PROFILE is called at the end of each statement. Regards, Clemens __

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > linker says -> "undefined reference to `sqlite3_trace_v2'" Then your distribution's package appears to be compiled with SQLITE_OMIT_TRACE. Just add sqlite3.c to your project. Regards, Clemens ___ sqlite-users mailing list sqlite-users@

Re: [sqlite] sqlite3_exec statement count including create/drop?

petern wrote: > Regarding sqlite3_trace_v2(db, SQLITE_TRACE_PROFILE,...). Calls to > sqlite3_sql() on the third trace callback parameter (cast to sqlite_stmt*) > do retrieve the correct part of the the original sqlite3_exec input SQL > string except for ill formed SQL. Apparently the SQLITE_TRACE

Re: [sqlite] UTF8-BOM not disregarded in CSV import

Mahmoud Al-Qudsi wrote: > with `.import ……`, SQLite3 includes a BOM (UTF-8) as part of the first > column of the first record. The Unicode Standard 9.0 says in section 3.10: | When represented in UTF-8, the byte order mark turns into the byte | sequence . Its usage at the beginning of a UTF-8 data

Re: [sqlite] Providing incrementing column to query

Paul Sanderson wrote: > I Have a number of queries to which I want to supply an incrementing column, > some of these queries involve without rowid tables. > > Is there a way of doing this with a SQL query? First, define a sort order, and ensure that it does not have duplicates. Then use a correlat

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