Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table

On 16 Oct 2019, at 7:03pm, Mitar wrote: > On Wed, Oct 16, 2019 at 3:16 PM Hick Gunter wrote: >> 100k distinct column names? Or is that 1 repeats of 10 attributes? > > 100k distinct names. Like each column a different gene expression. Don't do that. It's an abuse of how relational

Re: [sqlite] Getting "chunked" output from a large SELECT operation.

On 16 Oct 2019, at 6:38pm, Randall Smith wrote: > I'm having a situation where the results of a large SELECT operation are > apparently too big to fit in memory. SQLite only stores results if it has to. It would have to if there is no good index for your SELECT terms. Are you actually using

Re: [sqlite] Network file system that support sqlite3 well

On 15 Oct 2019, at 11:47pm, Peng Yu wrote: > Is there a solution that are known to fill in this niche? Thanks. Unfortunately, no. Multiuser SQLite depends on locking being implemented correctly. The developers haven't found any Network File Systems which do this. Unless one of the readers

Re: [sqlite] Table was deleted on macOS

Sorry, I have no other ideas. There is no reason for a table to disappear. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table was deleted on macOS

On 15 Oct 2019, at 10:11pm, t...@qvgps.com wrote: > "no such table: mytable" > > The client sent us the database and the table "mytable" was really gone. The > database also seems to be fine, no errors. Do you mean by that that you ran integrity_check() ? Is everything else there, or did

Re: [sqlite] Last record

On 15 Oct 2019, at 5:38pm, Philippe RIO <51...@protonmail.ch> wrote: > The only way is to make a query for getting the number of records and in the > second query I have to count the number of records retrieved to know if it is > the last one. Assume your intended query specifies an order

Re: [sqlite] Last record

On 15 Oct 2019, at 4:34pm, Philippe RIO <51...@protonmail.ch> wrote: > how could I know if I am reading the last record with > sqlite (sqlite3_step)? Sorry, there's no way to do that for some arbitrary SELECT. Because SQLite itself may not know. SQLite does not always process your query and

Re: [sqlite] Possible bug in storing text values in numeric columns

On 13 Oct 2019, at 10:11pm, Shawn Wagner wrote: > As you can see, the leading zeros in the original string are gone and it's > been converted to an integer. This seems to violate the "lossless and > reversible" constraint. Shouldn't it be kept as text? You defined the column as 'numeric'.

Re: [sqlite] Ensure a snapshot remains readable

On 13 Oct 2019, at 10:23pm, Adam Levy wrote: > My application requires a way to build a "pending state" in the database > while allowing users to query data from both the "official state" and the > "pending state". I am achieving this using sessions and snapshots. You are depending on finicky

Re: [sqlite] SQLite plus the works (was Re: Opposite of SQLite)

On 10 Oct 2019, at 9:03pm, Jose Isaias Cabrera wrote: > 2. The normal SQlite snapshots plus a series of libraries and functions that > can easily be compiled with the original light SQLite. Here's the simple way to start the project without breaking anything. Believers in SQLHeavy (or

Re: [sqlite] Opposite of SQLite

On 10 Oct 2019, at 7:55pm, Ned Fleming wrote: > SQLessLite SQDietStartsMonday ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date time input

On 8 Oct 2019, at 7:39pm, James K. Lowden wrote: > Simon Slavin wrote: > >> Converting data to and from a convenient storage format is not the >> job of a DBMS. > > While I have no quarrel with your specific point about date strings, > this particular statement i

Re: [sqlite] Date time input

On 7 Oct 2019, at 2:17pm, Jose Isaias Cabrera wrote: > I have to ask this question: Why is it that the date function does not take > a '4/5/2019' and returns '2019-04-05'? Those are human formats. SQLite is a database management system. Its job is to store data and allow it to be recalled

Re: [sqlite] Copy-on-write VFS

On 7 Oct 2019, at 10:34am, Fredrik Larsen wrote: > In my head, checkpointing implies copying back all dirty-pages from the > WAL/COW-log to the main db-file. If we never checkpoint, the writes are still > completed, but lives in the WAL-file. We will offcourse merge back pages to > the main

Re: [sqlite] Copy-on-write VFS

On 7 Oct 2019, at 9:56am, Fredrik Larsen wrote: > nowhere does it warn about severe regression with > unbounded WAL-size [snip] There are tons of bad stuff the documentation doesn't warn you about. You might want to read

Re: [sqlite] Online backup of in memory database

On 6 Oct 2019, at 8:03pm, Kadirk wrote: > I expect there might be a way to take backup of sqlite in memory while > updates are still being processed (as in on disk online backup). Maybe > something like copy on write memory for that? You can't clone something which is changing. Obviously.

Re: [sqlite] Online backup of in memory database

On 5 Oct 2019, at 10:43pm, Kadirk wrote: > Whenever an update comes in, > backup process starts over so it won't finish. Any idea how to solve this? How do you expect to take a perfect copy of something which is constantly changing ? Do you expect the changes to stop affecting the database

Re: [sqlite] Error 19 with Caret ^ INSERT

On 6 Oct 2019, at 5:56am, André Borchert wrote: > The odd thing is that the sql statement works just fine when I use DB Browser > for SQLite. Try the SQLite command line tool. That's written by the team which maintains SQLite itself, and it should give the connonically correct result. If it

Re: [sqlite] Copy-on-write VFS

On 4 Oct 2019, at 12:17pm, Fredrik Larsen wrote: > A copy-on-write IO-path where data is split into static and dynamic parts > (think snapshots for storage) would be very helpful for our project. SQLite abstracts changes-only tracking at the transaction level, and provides it as the session

Re: [sqlite] Lookup join

On 1 Oct 2019, at 8:15pm, Fredrik Larsen wrote: > I have run analyze on production data, that should work better, right? Yes, that is the best way to do it. I've been given some databases where the data saved by ANALYZE shows it was run with empty tables. So I sometimes warn people that

Re: [sqlite] Lookup join

When trying to make SQLite pick the right index, please use this sequence. 1) Create your indexes. 2) Put typical example data in your table. 3) Use the ANALYZE command 4) Delete the test data and put in real data (optional) You can do (1) and (2) in any order. But having data in the table

Re: [sqlite] Import XLS file?

On 1 Oct 2019, at 7:58pm, Winfried wrote: > Is there a free (beer|speech) tool to convert an XLS file into CSV, for > Windows or Linux that you would recommend? Not really the topic of this forum. XLS is Microsoft Excel format. Excel has an 'export as CSV' feature. So if you used Excel to

Re: [sqlite] Import XLS file?

On 1 Oct 2019, at 7:47pm, Winfried wrote: > I need to import an XLS file into SQLite. I recommend a two-part solution, because so many programs understand CSV file format. 1) export your XLS data in CSV format 2) Use the SQLite shell tool to import the CSV data into your database For part 2,

Re: [sqlite] disable file locking mechanism over the network

On 27 Sep 2019, at 11:59pm, Roman Fleysher wrote: > From experience, it seems that because SQLite still requests file locks, the > performance increase is not that big. I wonder if there is a way to disable > SQLite's internal file locking mechanism. I know this seems strange to ask. > But

Re: [sqlite] Safe to use SQLite over a sketchy network?

When I first learned the SQLite had problems with Network File Systems I read a ton of stuff to learn why there doesn't seem to be a Network File Systems that implements locking properly. I ended up with … A) It slows access a lot. Even with clever hashing to check for collisions it takes

Re: [sqlite] Safe to use SQLite over a sketchy network?

On 24 Sep 2019, at 10:14pm, Randall Smith wrote: > I have an application where remote users will be connecting to a SQLite DB > over a network connection that seems to be somewhat sketchy (I can't > characterize it well; I'm hearing this second-hand). > > My question is: Do the

Re: [sqlite] Why "UTF-8" and not 'UTF-8' ?

On 23 Sep 2019, at 9:09pm, Richard Hipp wrote: > In any event, you are correct that the behavior can now be disabled > and should be for new applications. Can I get an answer to my 'Subject' header ? Is the parameter to the PRAGMA a string parameter ? If so, can the documentation be changed

[sqlite] Why "UTF-8" and not 'UTF-8' ?

The documentation for PRAGMA includes this command: PRAGMA encoding = "UTF-8"; Why is it "UTF-8" and not 'UTF-8' ? Why is it double quotes rather than a normally-delimited string ? Should either of these work ? PRAGMA encoding = UTF-8;PRAGMA encoding = 'UTF-8'

Re: [sqlite] SQLite - macOS

On 23 Sep 2019, at 5:53pm, Pierre Clouthier wrote: > Can anyone explain how to write UTF-8 in SQLite on the Mac? > > We use this statement: > > sqlite3_exec("PRAGMA encoding = \"UTF-8\";") This is not a SQLite problem, but a problem with escaping the quotes in language you're using to

Re: [sqlite] Group-by and order-by-desc does not work as expected

On 19 Sep 2019, at 1:14pm, Fredrik Larsen wrote: > I have a aggregate query that works as expected when the ordering is > ascending, but uses a TMP B-TREE when changing order to descending, see > stackoverflow link below. For experimental purposes, you might take a backup copy of your database

Re: [sqlite] How to do setdiff but update the original table by the result?

On 18 Sep 2019, at 3:37pm, Peng Yu wrote: > I'd like to perform setdiff. I think this should be relevant. But it > does not update the original table. I want to update the original > table by the result. Simon

Re: [sqlite] [EXTERNAL] Fastest way to SELECT on a set of keys?

On 16 Sep 2019, at 5:58pm, Jens Alfke wrote: > Experimentally, the optimizer seems to choose an index search even with the > simpler query. I ran this on a test database with about 30k rows. In case you forgot I'm just reminding you to run ANALYZE after putting your data and indexes in.

Re: [sqlite] Fastest way to SELECT on a set of keys?

On 13 Sep 2019, at 5:38pm, Jens Alfke wrote: > Does anyone have intuition or actual knowledge about which approach is > better? Or know of a 3rd better approach? My guess is (b), but it will depend on your particular setup. Depends on cache size, storage speed, whether your OS is real or

Re: [sqlite] How to increase performance when inserting a lot of small data into table using indices

On 10 Sep 2019, at 4:02pm, mailing lists wrote: > Insertion really slows down after about 100 000 items have been inserted. I > suppose that the slow down is related to indexing because: > > [...] > c) changing the cache size has only a minor impact SQLite speed does degrade with table size,

Re: [sqlite] what's wrong with this trigger

On 10 Sep 2019, at 2:09am, Rael Bauer wrote: > CREATE TRIGGER notes_ai AFTER INSERT ON notes > BEGIN > update notebooks set notebooks.last_edit = datetime('now') > where notebooks.id = new.notebook_id; > END; > > SQLite Expert reports a near ".": syntax error. Is there something wrong with

Re: [sqlite] SQLITE and the memory

On 8 Sep 2019, at 2:06pm, Philippe RIO <51...@protonmail.ch> wrote: > These 42Mb are measured after a Vacuum. > When I lauched my application it only opens the databases (all) and the > memory is filled by 57mb just for SQLITE ! How are you opening these database ? Do you have 256 connections,

Re: [sqlite] non-aggregate columns in aggregate queries

On 6 Sep 2019, at 11:40pm, Jeff Rogers wrote: > Could a pragma or build-time flag be added to enforce "standard" behavior, > i.e., raising an error rather than handling it as currently documented? I think this is one of the things mentioned in

Re: [sqlite] Query for Many to Many

On 6 Sep 2019, at 7:36am, Rowan Worth wrote: > I was surprised when this behaved differently in other SQL engines. eg. in > SQLite you can write: > > SELECT col1, col2 FROM table1, table2 USING But please don't, for the reason you gave. Not only is it ambiguous but different SQL engines

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

On 4 Sep 2019, at 11:36pm, Peng Yu wrote: > Is there a minimal work example (in software way but not hardware > failure way) to make these extra files stick around upon closing a > sqlite3 session so that I can have a proper test case to make sure I > always delete them? Perform an INSERT

[sqlite] Window functions

I ran into this two-part article, probably on Hacker News: I tried comparing it with but I don't know enough to be able to tell whether the language used in the article is compatible

Re: [sqlite] SQLITE_BUSY, database is locked in "PRAGMA journal_mode"

On 4 Sep 2019, at 12:39pm, test user wrote: > Is it normal to get a `SQLITE_BUSY_RECOVERY` This code should only ever follow a crash, or some operation which has corrupted a database. If your hardware does not crash you should never see it. Something is wrong.

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

On 4 Sep 2019, at 11:18am, Peng Yu wrote: > For now, I just delete the db file if it exists already. So that I don’t need > to call DROP TABLE IF EXISTS mytab. I guess directly deleting the db file can > be faster than the latter. Is it so? The answer will change depending on your hardware

Re: [sqlite] What concurrency level is of sqlite?

On 3 Sep 2019, at 9:13pm, Peng Yu wrote: > In other words, if two processes write to the same sqlite file but to > different tables, will one wait for the other? What if to the same > table but different rows? Thanks. SQLite has only a lock for the entire database. It does not lock tables or

Re: [sqlite] How to create a new table or overwrite an existing table in one command?

On 3 Sep 2019, at 8:57pm, Peng Yu wrote: > If I try to create a table that already exists, sqlite will give me an error. > Is there way to issue one command to create a table, but if there is already > a table with the same name, drop it then create the new table? Thanks. Assuming that the

Re: [sqlite] Endless loop possible with simultaneous SELECT and UPDATE?

On 3 Sep 2019, at 5:34pm, Alexander Vega wrote: > sqlite3_open("database1", ); > sqlite3_open("database1", ); Bear in mind that SQLite is not a server/client DBMS. The database is not kept in memory (unless you arrange this explicitly). All operations have to wait for the storage that holds

Re: [sqlite] sqlite: see: encryption

On 3 Sep 2019, at 7:41am, Vadiraj Villivalam wrote: > As the key store does not allow the key > itself to be exported out, I would like to know if sqlite has a mechanism to > leverage the key store way of en/decrypting it (could be with a callback > implemented by app that interfaces with

Re: [sqlite] INSERT vs BEGIN

On 1 Sep 2019, at 7:27am, Grincheux <51...@protonmail.ch> wrote: > INSERT INTO artists (name) VALUES > ("Gene Vincent") > ("John Lennon") > ("Ringo Starr") > ("Paul McCartney") > . > . > . > ("Moi _ Me"); > > I want to insert 1 000 000 records. SQLite has to parse the entire command line before

Re: [sqlite] Unexpected REINDEX behavior.

On 29 Aug 2019, at 8:12pm, dirdi wrote: > The only difference between both runs: Well, that's nothing. I see no reason for the massive change in timing from what you posted. But someone else might. ___ sqlite-users mailing list

Re: [sqlite] Unexpected REINDEX behavior.

On 29 Aug 2019, at 3:09pm, dirdi wrote: > Does one have an explanation for this behavior? > Is it expected that the REINDEX command produces other results than > "manually" recreation of an index? > If yes, under which circumstances does this happen? > And is there some way to measure the

[sqlite] SQLite vulns

On 27 Aug 2019, at 9:40pm, Jens Alfke wrote: > My question was simply whether SQLite itself is considered safe when > operating on an untrusted database file. A worthwhile question which I discussed a little in an earlier post. The SQLite devs take this question extremely seriously. SQLite

Re: [sqlite] database like file archive

On 27 Aug 2019, at 7:47pm, Jens Alfke wrote: > Archive files often get transferred between people. Using this format for > that purpose would involve opening and reading untrusted SQLite database > files. Is that safe? Could maliciously corrupting the schema or other > metadata of a database

Re: [sqlite] Inverted changesets and UNIQUE constraints

On 26 Aug 2019, at 12:43pm, Dan Kennedy wrote: > When sqlite3changeset_apply() hits a UNIQUE constraint, it puts the change > into a "retry buffer". Then, once it has attempted all changes in the > changeset, it goes back and retries those in the retry buffer. It keeps > retrying like this

Re: [sqlite] ORDER BY is ignored during INSERT INTO

On 25 Aug 2019, at 10:09pm, André Borchert <0xa...@gmail.com> wrote: > I try to copy one table into a second identical one. Once the second table is > created I want to move the content over sorted by ASC. It's worth noting here that the rows of a table do not have any order in SQL. A table

[sqlite] SuperSQLite: a supercharged Python SQLite library

I have no connection with this extension to the APSW Python SQLite wrapper, I just saw a pointer to it. The latest commit is around nine months ago. Perhaps someone who is familiar with Python libraries might highlight anything this does new or unusually well.

Re: [sqlite] SQLite Encryption Extension For Use with With PHP 7.x Code

On 20 Aug 2019, at 8:40pm, Mark Tomlin wrote: > I found the regular sqlite3::open method has an $encryption_key parameter. > > https://www.php.net/manual/en/sqlite3.open.php Yep. > As I am using PDO for the interface into SQLite, I wonder if there is a > similar parameter for that interface.

Re: [sqlite] System.Data.SQLite.EF6 feature request: Timespan support

On 20 Aug 2019, at 12:19am, Barry wrote: > Would it be possible for a future version of SQLite to support TimeSpan > mapping in the entity framework? This is not an aspect of SQLite itself, but of how Entity Framework 6 talks to SQLite. The development team who could affect this change are

Re: [sqlite] Prepared Statement Consistency

On 19 Aug 2019, at 11:22pm, Mitchell Keith Bloch wrote: > I have observed that preparing statements that depend on a table will fail if > the table does not currently exist. It appears that those prepared statements > will, however, continue to function if the table is dropped and subsequently

Re: [sqlite] primary key quinky dink

On 19 Aug 2019, at 7:20pm, dboland9 wrote: > That is what I am using. I'm sorry. I missed that. Yes, you have the right solution now. I don't know where you got "AUTO_INCREMENT" from but it's not part of SQLite. ___ sqlite-users mailing list

Re: [sqlite] primary key quinky dink

On 19 Aug 2019, at 6:56pm, dboland9 wrote: > I have created a number of tables, and two of then are part of a many to many > relationship. Thus, I need to get the last row id. Using the browser, What browser ? No part of the SQLite distribution is called a browser. > I find that: >

Re: [sqlite] What to do with a .journal file

On 18 Aug 2019, at 6:10pm, Robert Weiss wrote: > I had a process that was writing a sqlite database when the computer crashed. > I now have two files: f6.db and f6.db-journal. How do I get the information > in the journal file to be incorporated in the database? > I tried open f6.db in the

Re: [sqlite] CLI .parameter command observations/request

On 16 Aug 2019, at 4:40pm, Martin wrote: > select * from sqlite_parameters You are expected to create this table yourself, either directly or by using one of the parameter commands. See This sequence works. You should be able to use it to

Re: [sqlite] Schema updates across threads in WAL & multithread mode

On 16 Aug 2019, at 7:32pm, Ben Asher wrote: > we make a > schema update (adding a column to a table) on our writer connection Can I ask the maximum number of columns you expect to exist in that table ? I'm working up to trying to convince you to add a row to something instead, but I want to

Re: [sqlite] Getting a notification when a write lock is released.

On 15 Aug 2019, at 10:43pm, test user wrote: > Currently the API lets you set a timeout. Does this just retry again after a > set amount of time? SQLite's built-in busy handler (which it uses unless you tell it to use yours instead) repeatedly backs off and retries until the timeout you set

Re: [sqlite] Documentation update request

On 15 Aug 2019, at 5:20pm, Richard Damon wrote: > You under quote, the faq says it “can not be changed (except under > extra-ordinary conditions).”, and those extra-ordinary conditions are a link > to the second section you mention. I didn't notice that, for some reason. Thanks for the

Re: [sqlite] Will rootpage number ever change?

On 15 Aug 2019, at 3:01pm, Jose Isaias Cabrera wrote: > sqlite> SELECT * FROM sqlite_master WHERE type='table' AND name='PMOTitles'; > type|name|tbl_name|rootpage|sql > table|PMOTitles|PMOTitles|11|CREATE TABLE PMOTitles >( > TitleKey PRIMARY KEY, > Titles >) > > What is the

[sqlite] Documentation update request

says that sqlite_master cannot be changed. tells you how to change it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Programming methodology (was DEF CON (wasL A license plate of NULL))

On 13 Aug 2019, at 9:42pm, Don V Nielsen wrote: > bool is_true (bool tf) > { >if (tf == true) return true; else return false; > } Do you get paid by the line of code ? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 8:20pm, Thomas Kurz wrote: > c) To enable the new DATE interpreation, we do: > CREATE TABLE TEST VERSION=3.34 --> will be stored the same way And now you have a file which can't be edited with old versions of the CLI. However you cut it, you have compatibility problems.

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 6:01pm, Thomas Kurz wrote: >> This would break backward compatibility. It is necessary to be sure that >> database files made with current versions of SQLite can be opened with old >> versions back to 2013. > > This is what I would call "forward compatibility": You expect

[sqlite] A license plate of NULL

Some interesting things are emerging from this year's DEF CON. This one is related to an issue we've often discussed here. I hope you'll indulge this slightly off-charter post.

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 12 Aug 2019, at 1:27pm, Tim Streater wrote: > I don't expect to do that with SQL. My "seconds since the epoch" is based on > converting any particular time to GMT and storing that. That number is then > converted to a date/time with TZ info for display. I'm with Tim. Storing the time zone

Re: [sqlite] Backward compatibility vs. new features (was: Re: dates, times and R)

On 11 Aug 2019, at 11:51pm, Thomas Kurz wrote: > I don't see a huge problem here. Does the database file have a version number > indicating which version the file has been created with? If so, newer SQLite > libraries could easily emulate the old-style behavior by just checking this > version

Re: [sqlite] dates, times and R

On 11 Aug 2019, at 2:45pm, Gabor Grothendieck wrote: > R supports Date and POSIXct (date/time) classes which are represented > internally as days and seconds since the UNIX Epoch respectively; > however, due to the class it knows to display and manipulate them as > dates and datetimes rather

Re: [sqlite] Feature request: import MySQL dumps in CLI

On 7 Aug 2019, at 9:16pm, Thomas Kurz wrote: > Well, that's why I asked for an *import* support. It's widely spread practice > to offer at least import capabilities from other software. This is what the .import function in SQLite's shell tool is for. It reads a well-documented text format.

Re: [sqlite] Feature request: import MySQL dumps in CLI

On 7 Aug 2019, at 5:13pm, Thomas Kurz wrote: > So my suggestion would be to add an import feature to the CLI that allows to > directly import MySQL/MariaDB dumps into an SQLite database keeping as many > information as possible. As SQLite already has a complete SQL parser I expect > much

Re: [sqlite] 3.29.0 .recover command

On 6 Aug 2019, at 7:51pm, Dan Kennedy quoted: >> INSERT INTO 'STATISTICS'('_rowid_', STATDATE, DISKUSED) VALUES( 1, >> 11237795927160, 11868 ); Quoting these entity names using apostrophes looks wrong to me. It may work but someone might read it, know it's official output from a program

Re: [sqlite] THREADSAFE (1, SERIALIZED) vs (2, MULTITHREAD)

On 6 Aug 2019, at 11:34am, test user wrote: > - Does the second request just take longer to return from the FFI call whilst > waiting for the mutex? Yes. You do not need to build backoff-and-retry code into your own software. SQLite does it for you. For every database connection you open,

Re: [sqlite] Doc about sqlite_master

On 5 Aug 2019, at 11:54am, Dominique Devienne wrote: > What other sqlite_* tables are you talking about? > > sqlite_master is AFAIK the only table with a "fixed-name", that's part of the > "public API" as previously discussed. I would argue that sqlite_sequence and sqlite_stat?, as documented

Re: [sqlite] Doc about sqlite_master

On 5 Aug 2019, at 10:34am, Dominique Devienne wrote: > Thus my > suggestion that an explicit page of its own is warranted, > properly indexed then. Given that there are other sqlite_* tables which are documented, perhaps rather than having a page on just sqlite_master, there should be a page

Re: [sqlite] Doc about sqlite_master

On 5 Aug 2019, at 9:25am, Dominique Devienne wrote: > First, is sqlite_master part of the "public API" of SQLite? > I would think so, since many example refer to it. It's documented. Therefore it can't be changed on a whim. So I'd agree that it's part of the public API. The two most

Re: [sqlite] Hidden data

On 4 Aug 2019, at 5:33pm, bitwyse wrote: > Could it be hidden in a Blob? > What program (command?) would diplay it and allow deleting it? > (VACUUMing doesn't remove it.) There's only one program guaranteed to get at every element of a SQLite database file: the sqlite3 shell tool written and

Re: [sqlite] Performance difference between SELECT ... ORDER BY ... ASC/DESC

On 3 Aug 2019, at 8:35pm, mailing lists wrote: > is there in general a performance difference between querying using SELECT … > ORDER BY … ASC or DESC? No. Very close to the same speed. ___ sqlite-users mailing list

Re: [sqlite] Quick way to determine optimal page size?

On 2 Aug 2019, at 8:55pm, Jen Pollock wrote: > Compressing the backups would likely save you a lot more space, and I > suspect it wouldn't be affected that much by page size; presumably empty > space at the ends of pages will compress very well. It might be an interesting exersize to compare

Re: [sqlite] Multiple ATTACHments to the same DB?

On 1 Aug 2019, at 11:32pm, Randall Smith wrote: > Can an ATTACH request encounter a "locked database" error, or do these only > occur when an actual modification is attempted? How are ATTACHments > different from connections? Sorry, hit 'send' too soon. I don't think ATTACH can get a

Re: [sqlite] Multiple ATTACHments to the same DB?

On 1 Aug 2019, at 11:32pm, Randall Smith wrote: > My question is: Can I have 10 or whatever slaves ATTACHed to the same master > at the same the time (that is, constantly ATTACHed while he slave is open), > in the same spirit as having 10 database connections open to the master all > the

Re: [sqlite] Floating point literals

On 1 Aug 2019, at 12:55am, Keith Medcalf wrote: > Columns declared with no affinity behave as if they had been declared with > BLOB infinity and v/v. Okay, so leaving out the affinity just lets it default to BLOB. That clarifies and simplifies things.

Re: [sqlite] Floating point literals

On 31 Jul 2019, at 11:58pm, Keith Medcalf wrote: > it depends on the application of affinity. If you are storing the floating > point value in a column that does not have an affinity (ie, no conversions > are performed), then it is stored exactly (except for NaN). Application of > affinity

Re: [sqlite] Floating point literals

On 31 Jul 2019, at 10:15pm, Eric Reischer wrote: > Is there a way to pass binary representations of floating point numbers to a > SQL query? If sqlite's internal representation of floating point numbers is > 8-byte IEEE doubles, it would be convenient to be able to pass the literal > value

Re: [sqlite] Quick way to determine optimal page size?

On 31 Jul 2019, at 8:28pm, Tony Papadimitriou wrote: > Instead of brute force “pragma page_size=xxx; vacuum;” for each page size and > each database to determine which one produces the smallest file, is there > some quicker way? It might be faster to make a new file, set page size, ATTACH

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

On 31 Jul 2019, at 5:04pm, Larry Brasfield wrote: > I do not personally see the benefit of moving the repeatable read guarantee > to the BEGIN point rather than the first database read after the BEGIN > because only fully committed transactions will be visible anyway -- … I can imagine two

Re: [sqlite] [SPAM?] Re: Explicit "read transaction" with journal_mode=WAL.

On 31 Jul 2019, at 12:57pm, test user wrote: > Is there a standard place where people can request features to be added to > SQLite? Here. You've already done it. The developers of SQLite read this list and will consider the things you wanted to do and whether it's worth providing a better

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

On 31 Jul 2019, at 11:36am, test user wrote: > BEGIN IMMEDIATE forces a "write transaction", but there is no counterpart for > a "read transaction". The difference is that the connection can write as well as read. So it is a lock for reading. ___

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

On 30 Jul 2019, at 9:39pm, test user wrote: > BEGIN IMMEDIATE will start a write transaction, which will block other > writers with SQLITE_BUSY until its complete. This does not apply to WAL mode. You wrote that you were using WAL mode. > What I would like is something like BEGIN READ The

Re: [sqlite] Explicit "read transaction" with journal_mode=WAL.

On 30 Jul 2019, at 6:44pm, test user wrote: > I am using `journal_mode=WAL`. > > What I am trying to do: > > From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the > same snapshot/point in time. This is the effect if you use BEGIN IMMEDIATE instead of just BEGIN. So do

Re: [sqlite] [EXTERNAL] Help with sqlite3TreeViewSelect

On 29 Jul 2019, at 5:44pm, x wrote: > It’s not as easy to get access to sqlite3TreeViewSelect on windows as it > would appear to be in the unix debugger. When I did find out how to access it > I noticed it returned a string that would enable me to do away with reams of > code I wrote to do

[sqlite] Proposal: SQLite on DNA

May I humbly suggest that the development team look into porting to a new platform: " Simply put, at this pace, there soon won’t be enough data storage and compute material to go by. Which is why

Re: [sqlite] [EXTERNAL] Determining valid statement placeholders.

On 22 Jul 2019, at 10:53am, test user wrote: > A new function could be added: > > For query string "SELECT ?, ?10" > > `sqlite3_bind_parameter_valid_indexes() -> [1, 10]` > > How can I propose adding this? As given in my previous response, the SQLite library already contains these two: int

Re: [sqlite] Determining valid statement placeholders.

On 21 Jul 2019, at 8:03pm, test user wrote: > For example: "?, ?10" would have two valid indexes: (1, 10). Please don't use numbers as names. That would be extremely confusing to anyone trying to understand your code. > But how can I know that indexes 2-9 are invalid from the API? You have

Re: [sqlite] Determining valid statement placeholders.

On 21 Jul 2019, at 8:03pm, test user wrote: > Is there a `sqlite3` C FFI API that allows me to determine which index values > are valid to bind to? I think you're talking about one of these two: int sqlite3_bind_parameter_index(sqlite3_stmt*, const char *zName); const char

Re: [sqlite] Query Builder Access

On 20 Jul 2019, at 7:33pm, Revere Perkins wrote: > Is the SQL Query Builder only available in the licensed version? Sorry. SQLite is always completely free, and there's no Query Builder included as part of SQLite. You must be asking about a third-party product. And there are lots of SQL

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