Re: [sqlite] A couple of questions about prepared statements

On 19 Oct 2018, at 3:43pm, Tim Streater wrote: > is it OK to generate the myid and goodtext parts using the usual string > methods from my host language, leaving only badtext as a bound variable, so > that my prepared statement looks like this: > > select somecol from mytable where myid=3 and

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

On 19 Oct 2018, at 5:55am, Yuri wrote: > I noticed that my DB import process is much slower when run on the DB on > disk, vs. in memory. It reads files and runs a massive amount of > inserts/updates. Memory access is much faster than disk access. If you're using actual spinning hard disks,

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

On 19 Oct 2018, at 2:23am, Larry Brasfield wrote: > Jordy Deweer asks about alternatives to System.Data.SQLite because: “I run > into errors a lot, using the System.Data.SQLite libraries. It easily crashes, > trhows exceptions and similar issues...”. System.Data.SQLite is generally considered

Re: [sqlite] Hot-Journal with VFS

On 18 Oct 2018, at 3:27pm, Bob schwanzer wrote: > DB is > opened by 10-20 processes each of which can have multiple threads. What OS are you using ? What programming language are you using ? Are you calling the SQLite C library directly or using a shim ? Does your program close each database

Re: [sqlite] Bug report: bug in datetime conversion function

On 18 Oct 2018, at 2:28pm, Fábio Pfeifer wrote: > When working with Apple iOS databases, I found something strange when dealing > with dates. I suspect you are you are referring to a library routine which is used by lots of iOS software, but can you give us a specific App which exhibits this

Re: [sqlite] Strip leading "The" in alphabetical TOC?

On 18 Oct 2018, at 7:17pm, Charles Leifer wrote: > In the documentation alphabetical listing, it threw me off when I was lookup > up the JSON1 docs and didn't find them under "J", due to the title being "The > JSON1 Extension". Some years ago I wrote a TITLE Collating Sequence for SQLite. I

Re: [sqlite] Fill empty space with random

On 14 Oct 2018, at 3:24pm, J Decker wrote: > B and C would apply if there was a vacuum also; adding data, and deleteting > data, the db ends up with lots of zeros > Also between non-integral pages; messages that are say 700 bytes; so 4096% > 700 is 596; which is all filled with zeros...

Re: [sqlite] Fill empty space with random

On 14 Oct 2018, at 12:56pm, J Decker wrote: > Is there maybe a compile option for sqlite to fill empty space in a db with > random data rather than 0 ? There is not. But (A) It may be an easy change to the source code (B) Your operating system may have a setting to do this automatically to

Re: [sqlite] SQLite mailing list [was: SQLite Windows GUI alternative to Excel?]

On 9 Oct 2018, at 10:38pm, Keith Medcalf wrote: > And just what is NSFW spam? Messages purporting to come from young women. Some including images purporting to be them in various states of undress. Simon. ___ sqlite-users mailing list

Re: [sqlite] SQLite mailing list

On 9 Oct 2018, at 9:49pm, Warren Young wrote: > Also: This list may be an unusually juicy target, given the number of places > SQLite is deployed. The minute SQLite gains any sort of internet connectivity, a hundred thousand man-hours of cracking attempts will be launched. Which is why it's

Re: [sqlite] Question about a query

On 9 Oct 2018, at 2:47pm, Leonardo Inácio de Freitas wrote: > Using SQLite, can you use masks (or regex) (like '% str%') inside > instr / substr, to delimit the output of a select, instead of me > determining the beginning and end of the substring? No. Sorry. You have to use string core

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On 6 Oct 2018, at 8:04pm, Luuk wrote: > Even a simple program (or (gui?)-interface on top of sqlite will not > stop those Excel-whizz-kids from crafting things in Excel. Excel ate the financial business world because companies use Excel to solve a simple problem, then add a feature, then add

Re: [sqlite] SQLite Windows GUI alternative to Excel?

On 6 Oct 2018, at 5:23pm, Warren Young wrote: > What we want is a SQLite-based program along the lines of Access or > FileMaker, preferably with some kind of cloud capability. > > If that wish seems frivolous, realize that we’re not going to get rid of the > spreadsheet-as-database as long as

Re: [sqlite] Persistent snapshots and rollbacks

On 5 Oct 2018, at 6:17pm, Daniel Kraft wrote: > If there is indeed no way to achieve my requirements with SQLite There isn't. I understand what you want and SQLite can't do it. You can simulate it by introducing an extra column in each table and writing your own library to simulate each

Re: [sqlite] Persistent snapshots and rollbacks

On 5 Oct 2018, at 4:39pm, Daniel Kraft wrote: > I need the ability to make multiple changes / commits to my SQLite > database but keep snapshots of previous states and potentially roll back > to those states later on. All of that needs to be persistent, i.e. > survive closing the database and

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

On 4 Oct 2018, at 10:57am, ldl wrote: > Just the same application started in multiple processes doing the same > things. The accesses to the db were all readonly. > > > I print the error from sqlite3_get_table(). The error is "database is locked". Set a timeout of 60,000ms and see whether

Re: [sqlite] Help!

On 2 Oct 2018, at 11:00pm, am...@juno.com wrote: > As such, I would be most appreciative if someone would e-mail me incredibly > explicit directions on how to import a file in SQLite DB to SQLite studio. The two programs should be able to open the same database files. SQLite has only one

Re: [sqlite] Database locked problem

On 30 Sep 2018, at 3:14pm, Lars Frederiksen wrote: > In fact I had the same error some time ago where the solution was to put my > database in a C-drive root folder. But that is where the db is right now!! It is very unusual under Windows to have enough privileges to change files in C:\ .

Re: [sqlite] Crash observation in certain conditions (SQLite v.3.25.2)

On 29 Sep 2018, at 1:53am, Vlad Khokholkov wrote: > Unfortunately, today I download the latest version of SQLite (3.25.2) and > got crash on the first 'sqlite3_prepare_v2' call. Thank you for posting. Can you tell us 1) What the crash error message is. 2) What statement you are passing to

Re: [sqlite] A SQL statement reformatter

On 28 Sep 2018, at 3:49pm, Chris Brody wrote: > Someone should have make this tool 20-30 years ago! They did. There have been others available, I just posted a new one. The

Re: [sqlite] How to retrieve table names for the given string

On 27 Sep 2018, at 2:43pm, Revathi Narayanan wrote: > I tried to execute the query using sqlitemaster. But it's displaying only > table names not column names. sqlite_master does not have column name columns. They're just mentioned in the CREATE statement. You might want to combine it with

[sqlite] A SQL statement reformatter

For those times when you have to understand a poorly-formatted SQL statement: I seem to prefer 'full' mode. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] storing unsigned 64 bit values

On 27 Sep 2018, at 5:10pm, Conor Lennon wrote: > It's declared as a unsigned integer There is no such thing in SQLite. SQLite has an integer type, but it is an 8-byte signed integer. If you don't need to sort on that

Re: [sqlite] storing unsigned 64 bit values

On 27 Sep 2018, at 11:53am, Conor Lennon wrote: > e.g. 18446744073709551615 (one less than 2 to the power of 65) > > I seem to have managed to store this value in a database. What is the affiliation for that column ? Did you declare it as INTEGER or something else ? Simon.

Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb

On 20 Sep 2018, at 10:31pm, Roger Cuypers wrote: > rbuFindMaindb > rbuVfsAccess > sqlite3OsAccess > hasHotJournal > sqlite3PagerSharedLock > zipvfsLockFile Thanks. That's very useful. Your stack includes both zipvfsLockFile and rbuVfsAccess, and I'm not familiar with either of these. So I

Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb

On 19 Sep 2018, at 8:47pm, Roger Cuypers wrote: > the database has a root file. The subfiles are all loaded via separate > connections as far as I know. Sorry, but this makes no sense. Each database file can have only one WAL file. You say that the program is looking through lots of WAL

Re: [sqlite] SQlite 3 - bottleneck with rbuFindMaindb

On 19 Sep 2018, at 7:49pm, Roger Cuypers wrote: > As far as I know it uses journaling with WAL and has a lot of files/tables > (about 400). Excuse the low-end questions, but they might help save us a lot of silly suggestions. Does SQLite have lots of these open at one time ? If so, does it

Re: [sqlite] [EXTERNAL] The "natural" order of the query results.

On 17 Sep 2018, at 7:19am, John Found wrote: > Yes, of course, but you forgot about INDEXED BY clause. It will force using > particular index. So, the query planner will always use exactly this index, > regardless of how optimal it is and > as long as GROUP BY and ORDER BY are working the same

Re: [sqlite] The "natural" order of the query results.

On 16 Sep 2018, at 9:29am, John Found wrote: > Is there some relation between the indexes used in the query, the GROUP BY > fields used > and the order of the result rows, when no "ORDER BY" clause is used? When you ask for GROUP BY, SQLite internally does the same sort of thing as it does

Re: [sqlite] Unique Constraint Failed

On 14 Sep 2018, at 8:56pm, Andrew Stewart wrote: > CREATE TABLE dataStreamRecord ( > fwParameterID INTEGER NOT NULL, > dateTime INTEGER NOT NULL, > data INTEGER NOT NULL, > UNIQUE ( > fwParameterID, > dateTime > ) > ); Well, there is only one UNIQUE

Re: [sqlite] Unique Constraint Failed

On 14 Sep 2018, at 8:29pm, Andrew Stewart wrote: >I am having problems with a database reporting Unique > Constraint Failed when doing an insert. > Table consists of 3 columns: > ID, DateTime, data > Constraint is on ID,DateTime. > >DateTime trying to enter is

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

On 14 Sep 2018, at 6:50pm, Maziar Parsijani wrote: > I have 2 tables with the same rowid now I want to : > select rowid from table1 where table1 like "%smth%" > select * from table2 where rowid =(selected rows before) > > I mean if I could do it in a same query. This is what JOIN is for.

Re: [sqlite] sqlite3_column_* with error handling

On 13 Sep 2018, at 3:49pm, Brian Vincent wrote: > From my testing, it appears that this information isn't exactly correct. > In my tests, when I call sqlite3_column_int64 for example, and it's > successful, sqlite_errcode() immediately afterwards is returning > SQLITE_ROW, which leads me to

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

On 12 Sep 2018, at 2:04pm, Urs Wagner wrote: > The following code is returning 0. Why? Which version of SQLite ? You coerce the result of the call into an integer. Can you make the call and display (or use a debugger to see) exactly what it's returning ? If you do "PRAGMA compile_options"

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

On 10 Sep 2018, at 12:30am, Robert Helmick wrote: > Thanks for your response. I'm using the node.js plugin. I've tried creating > a table then closing the db, and the queries executed successfully, but I > was not able to find the database file that it created. I searched the > folder structure

Re: [sqlite] sql UPDATE schema

On 8 Sep 2018, at 1:48am, Amno Jeeuw wrote: > the application generates the following schema represented on this message > box: > db_snapshot3.png > This command is incorrect. It should end in WHERE id

Re: [sqlite] Error when reading from pre-populated SQLite database in Ionic project

On 6 Sep 2018, at 5:33pm, Robert Helmick wrote: > by default, then creates an empty database when it doesn't find the > pre-populated mydb.db file. This is why it can't find the 'plant' table, > because the newly created blank database obviously doesn't contain a > 'plant' table. However I can

Re: [sqlite] Incorrect result when using WHERE with with correlated sub-select query

On 3 Sep 2018, at 2:32pm, Edson Poderoso wrote: > The following query should return 26 rows, instead I returns 15. Which version of SQLite ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] sqlite3_column_* with error handling

On 5 Sep 2018, at 11:22am, Richard Hipp wrote: > Just call sqlite3_errcode(). It will return SQLITE_OK if the > sqlite3_column function was successful. In respect of the above, I observe the second sentence of If the most recent API call was

Re: [sqlite] sqlite3_column_* with error handling

Your question has been asked earlier this year, and no solution was posted. I'm interested to find out whether things have improved. To summarise the earlier post: 1) sqlite3_column_int() returns the value stored in that column, or 0 if an error occurred.

Re: [sqlite] How to get all SQL statement syntax images?

On 4 Sep 2018, at 5:02am, Rocky Ji wrote: > I found the solutions to my issue, thanks everyone. Well done. > How do I mark this > thread [SOLVED] It's just email, so I think you did. Simon. ___ sqlite-users mailing list

Re: [sqlite] [EXTERNAL] Missing function sqlite3_column_index

On 3 Sep 2018, at 6:15pm, Sebastian wrote: > So is this the reason that the function doesn't exist? That it needs AS > clauses to be useful? More likely, that it is not needed by SQLite itself. sqlite3_column_name() is used inside SQLite, but there's no need for your proposed function.

Re: [sqlite] wal-mode and checkpoint

On 2 Sep 2018, at 2:43pm, Cecil Westerhof wrote: > When I do in sqlitebrowser: >PRAGMA TABLE_INFO(messages) Just for peace of mind, since you are reporting unexpected behaviour, please run an integrity_check. Certain cleaning-up jobs are done only when the last connection to the database

Re: [sqlite] Consider adding aggregate function "string_agg" as synonym of "group_concat"

On 1 Sep 2018, at 11:03pm, Zsbán Ambrus wrote: > Would you consider adding an aggregate function "string_agg" which is > a synonym for the "group_concat" function but takes only two > arguments? Could you look up the definition of the existing function on

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

On 31 Aug 2018, at 2:46pm, J Decker wrote: > There was a voxel engine that was claiming they were going to move to a > morton encoding; and I was working with a different engine, so I built a > simulator to test averge lookup distances; it was far more efficient to > keep sectors of voxels

Re: [sqlite] Best practices for forward conversion of database formats?

On 30 Aug 2018, at 5:51pm, Randall Smith wrote: > Are there accepted or best practices in the industry for handling this in > general, or with SQLite in particular? Can anyone who has implemented this > make useful suggestions? Or are there published resources I am missing? Roger has

Re: [sqlite] Get data in one query

On 29 Aug 2018, at 4:59pm, Simon Slavin wrote: > You can use this structure: > > SELECT (first SELECT), (second SELECT) Better still in your case, you can use SELECT (first SELECT) as Total, (second SELECT) as Late Simon. ___ sql

Re: [sqlite] Get data in one query

On 29 Aug 2018, at 4:56pm, Cecil Westerhof wrote: > Is there a way to get this information in one query? You can use this structure: SELECT (first SELECT), (second SELECT) Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

[sqlite] SQLite with branching

I have no connection with the following project. Described poorly on the web site so here's my own description: This is an extension of SQLite which allows branched versions, each new branch creating one dataset which existed before the new branch and a

Re: [sqlite] How to find records in a table which are not in a second table?

On 28 Aug 2018, at 5:32pm, Richard Hipp wrote: > I suppose: > > SELECT * FROM A WHERE nam NOT IN (SELECT nam FROM B); Depending on how many names the tables have in column, a possible alternative might be to use the EXCEPT compound operator here. Something like SELECT nam FROM A

Re: [sqlite] Blob and Text columns: what's the difference?

On 28 Aug 2018, at 2:50pm, Tim Streater wrote: > What is actually the difference between a column declared as TEXT and one > declared as BLOB in an SQLite database? What does SQLite do to textual data > that I ask it to put into a TEXT column? BLOB data is always handled as a block of a

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

On 26 Aug 2018, at 6:17pm, R Smith wrote: > Ok, enough about what is wrong with it. Here's how it can be fixed: And there you have it, ladies and gentlemen. Around two thousand bucks of consultancy for free. The difference between copy-and-paste and actually understanding the products in

Re: [sqlite] keys

On 25 Aug 2018, at 12:31am, w...@us.net wrote: > "The parent key of a foreign key constraint is not allowed to use the rowid. > The parent key must used named columns only." > > Why is this? Because it's not named. In theory you could later add a column named 'rowid' to mean a BLOB column.

Re: [sqlite] Unsigned

On 22 Aug 2018, at 6:04pm, Jens Alfke wrote: > Bignums make unsigned types irrelevant, if the only reason you need unsigned > is to store 64-bit positive integers. As others have said, "unsigned" is a > constraint, not a type. Agreed. > Bignums would be nice; but you could implement them

Re: [sqlite] bug: compare number with empty string get different resut

On 19 Aug 2018, at 7:56pm, Warren Young wrote: > On Aug 19, 2018, at 10:03 AM, Norman Dunbar wrote: > >> Duck Typing? Never heard that before. > > It’s a pretty common term of art in the software development world: > > https://en.wikipedia.org/wiki/Duck_typing That article was written 20

Re: [sqlite] I Heed Help

SQLite does the information storage and retrieval tasks for you. The questions you are asking are about the user interface. SQLite has no user interface. We cannot program it to do the things you asked about. I think you need to learn how to program, or hire a programmer. That's what

Re: [sqlite] Looking for SQLite app builder

On 17 Aug 2018, at 7:11pm, Bob Sisson wrote: > but acrobat couldn't deal with over 1,000 fields... You would not want a table with over 1,000 fields. That is an extremely unusual way to use a SQL database. However, I do not thing you want to use SQLite directly, and do all the low level

Re: [sqlite] SQLite3 with C

On 15 Aug 2018, at 6:26am, Ricardo Lima wrote: > I don't get any compilation errors and the SQLite source files/header files > are implemented correctly into the code. I know this because I'm using SQLite > Studio ( SQLite GUI) and I see all the attributes from the wallet DB, but > they are

Re: [sqlite] Safe sqlite over remote filesystem?

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 there's still a problem with simulating a mutex system.

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

On 13 Aug 2018, at 1:45pm, Lars Frederiksen wrote: > This is the result: > > sqlite> Select GRAESK, hex(GRAESK), DANSK from gloser; > taß???a ?|CF84CEB1CEB2CEADCF81CEBDCEB120CEB7|taverna "taverna" in greek should be "ταβέρνα". In UTF-8 I get CF84 CEB1 CEB2 CEAD CF81 CEBD CEB1 Examining what

Re: [sqlite] [EXTERNAL] Re: No unicode characters in sqlite3 console prg

On 13 Aug 2018, at 1:23pm, Lars Frederiksen wrote: > I use versio 3.19 > SQLite version 3.19.3 2017-06-08 14:26:16 Lars, Please download the current version of the shell tool from the "Precompiled Binaries for Windows" -- "sqlite-tools-win32-x86-324.zip". section of

Re: [sqlite] No unicode characters in sqlite3 console prg

On 13 Aug 2018, at 1:03pm, Lars Frederiksen wrote: > I use versio 3.19 What operating system ? If you are using Windows, please issue this command before using sqlite3.exe: chcp 65001 Simon. ___ sqlite-users mailing list

Re: [sqlite] No unicode characters in sqlite3 console prg

On 11 Aug 2018, at 5:51pm, Lars Frederiksen wrote: > Is this an error of my windows 10 cmd prompt or is it a general problem that > the sqlite3 console is not able to show unicode in the cmd-window.?? It's a Windows thing. But I don't know how to fix it in Windows 10. Does Windows 10 still

Re: [sqlite] Database locks

On 7 Aug 2018, at 3:04pm, Gerlando Falauto wrote: > What might happen in my case is that reads could take a very long time to > consume/process all the data (30s for instance), and I believe the lock is > held until all data is consumed. > I believe in that case the read would see all the data

Re: [sqlite] Database locks

On 7 Aug 2018, at 1:54pm, Gerlando Falauto wrote: > I just realized I'm using default settings... perhaps I should use WAL mode > instead? How important is it to you that SELECT gets up-to-date information ? If a read happens at the same time as a write: Normal mode: SELECT waits until the

Re: [sqlite] Database locks

On 7 Aug 2018, at 1:22pm, Gerlando Falauto wrote: > Hmm... are you saying the writer could potentially block for up to 10 > seconds? I should have been clearer. The 10 second time is purely for diagnostic purposes, to see if the error goes away. It was chosen to be far longer than any

Re: [sqlite] Database locks

On 7 Aug 2018, at 12:55pm, Gerlando Falauto wrote: > I'm trying to implement a logging system based on SQLite, using python3 > package apsw. > There's one process constantly writing and another one reading. > From time to time I get an exception from the writer, complaining the > database is

Re: [sqlite] "Cheating" at making common tables

On 5 Aug 2018, at 2:40am, Stephen Chrzanowski wrote: > I'm making a small database for a game, and a bunch of the tables follow > the same kind of naming convention due to normalization, like > > {Name_Of_Information}s > {Name_Of_Information}ID as Integer > {Name_Of_Information}Name as Char >

Re: [sqlite] Common index for multiple databases

On 3 Aug 2018, at 7:52pm, John R. Sowden wrote: > My concern in using Sqlite is since the index is embedded into the database > file with various tables, if I am running multiple Sqlite database files, how > do I use a common index for the different database files. Okay. Thanks for that

Re: [sqlite] mmap, madvise, mlock and performance

On 3 Aug 2018, at 8:36pm, Shevek wrote: > We are running a 100Gb sqlite database, which we mmap entirely into RAM. We > are having trouble with parts of the disk file being evicted from RAM during > periods of low activity causing slow responses, particularly before 9am. Has > anybody played

Re: [sqlite] Save text file content in db: lines or whole file?

On 3 Aug 2018, at 8:04pm, Abramo Bagnara wrote: > Some queries will need to extract the whole file, while other queries > will need to extract the text for a range of lines. Can you give us an idea of how many lines you expect per text file ? Minimum and maximum for 90% of the files would be

Re: [sqlite] Common index for multiple databases

On 2 Aug 2018, at 7:44pm, John R. Sowden wrote: > another point that I did not make clear. The accounting programs are not > associated with the technical programs, different people, different security > access. The tech databases and programs are in portable computers that go > out in the

Re: [sqlite] Common index for multiple databases

On 2 Aug 2018, at 6:11pm, John R. Sowden wrote: > I do not want these databases to all reside in one sqlite file. How do I > index each database on this customer account number when each database and > associated index are in separate files? Is this what seems to be referred to > as an

Re: [sqlite] [EXTERNAL] Re: Very, very slow commits - Possibly solved

On 31 Jul 2018, at 4:42pm, Rob Willett wrote: > We have not checked that the order of columns in the index match the ORDER BY > clauses. We never thought of that either, That is going to make a big difference. Well done Gunter. Rob: don't forget that once you have your indexes defined and

Re: [sqlite] Very, very slow commits

On 31 Jul 2018, at 2:59pm, Rob Willett wrote: > We've created a new table based on your ideas, moved the collate into the > table, analysed the database. We did **not** add COLLATE NOCASE to the > columns which are defined as integers. Would that make a difference? What you did is correct. I

Re: [sqlite] Sqlite Sharding HOWTO

On 30 Jul 2018, at 8:38pm, Gerlando Falauto wrote: > Does that apply to the primary key as well? Primary key indexes are unique indexes, since SQLite has to enforce the primary key being unique. Howwever, I do not think there can be such a strong penalty for indexes being UNIQUE. I side

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 10:25am, Dominique Devienne wrote: > The former allows you to get what you want, but as you wrote, you must > rewrite your queries. The latter, > if supported, would allow to move the "function definition" to the column, > and index the vcolumn directly. It's the usual speed

Re: [sqlite] Reducing index size

On 30 Jul 2018, at 9:32am, Eric Grange wrote: > As these are cryptographic GUIDs, the first few bytes of a values are in > practice unique, so in theory I can index just the first few bytes (using > substr()), > this indeed reduces in a much smaller index, but this also requires > adapting all

[sqlite] Efficiency of partial indexes

I have a particular logging task which is time-critical, both in reading and writing. It runs on Linux, but not a fast desktop computer, more like the slow kind built into your WiFi router, with limited solid state storage. I can use any version of the SQLite C API I want, and currently use a

Re: [sqlite] Sqlite Sharding HOWTO

On 29 Jul 2018, at 11:39pm, Gerlando Falauto wrote: > In the current use case thre's a single process. The way I see it, in > the near future it would probably increase to 3-4 processes, > each doing 10-100 writes per second or so. Each write would be around > 1KB-20KB (one single text field, I

Re: [sqlite] How to do autosaving (commit data to disk) within an transaction?

On 29 Jul 2018, at 12:56pm, Robert M. Münch wrote: > I want to add an autosave feature that stores some data at critical > points/specific intervals. This data of course should be commited to disk, to > survive any app crashes. Use a SAVEPOINT instead of COMMIT:

Re: [sqlite] Very, very slow commits

Please try moving your COLLATE clauses into the table definition. e.g. instead of CREATE UNIQUE INDEX "Disruptions_Idx3" ON Disruptions ("version" COLLATE NOCASE ASC, "Disruption_id" COLLATE NOCASE ASC, "location" COLLATE NOCASE ASC); Your table definition should have "version"

Re: [sqlite] Create VIEW passing despite invalid column name specified

There is very little pressure to keep the shell tool small. So the check for views which refer to non-existent tables could be put into there, as part of one of the dot-commands which do checking. As for the code, it doesn't require anything more than SQL commands. There's no need for access

Re: [sqlite] Backup and integrity check questions

On 27 Jul 2018, at 6:02am, Rowan Worth wrote: > (any writes to a DB are automatically propagated to in-progress backups > within the same process). I didn't know that. Thanks. It's clever. Simon. ___ sqlite-users mailing list

Re: [sqlite] Backup and integrity check questions

On 25 Jul 2018, at 10:56pm, Rune Torgersen wrote: > Management wants to know if we're doing it correctly, or if there are > faster/easier ways to do backup (and check). Please excuse me mentioning things I'm sure you are already doing correctly. Your question is useful to many users and this

Re: [sqlite] how to know which database is corrupted

On 24 Jul 2018, at 11:34pm, J Decker wrote: > If the system rebooted; did a screen size change, and terminated the > program, it's possible it coild cause corruption. Step 1: use the command-line tool to fix your existing corruption. Step 2: prevent more corruption. Ignoring the possibbility

Re: [sqlite] how to know which database is corrupted

On 24 Jul 2018, at 8:43pm, J Decker wrote: > I have a database that got corrupted; was working on implementing automatic > recovery It would be a million times better to figure out how the corruption occurs and prevent it. Simon. ___

Re: [sqlite] Immediate mode transaction gets error 5, SQLITE_BUSY when attempting commit

On 24 Jul 2018, at 6:22pm, Andy Dickson wrote: > I have a database with one process (in one thread) writing to it, and another > process (also in a single thread) reading from it only. Do these two processes each have their own connection to the database ? > The read-only process has

Re: [sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...

On 17 Jul 2018, at 1:10pm, R Smith wrote: > What kind of data did you store (maybe some examples if possible) that you > could condense it by ~99% like that? I think that the OP's organisation discovered the 'relational' part of RDBMS and implemented normalisation. To Rob Willett: Ryan

Re: [sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...

On 17 Jul 2018, at 12:20pm, Rob Willett wrote: > Do you have any pointers to stuff we can read up on? We don't understand your > comment "SQLite spent that whole time accessing your 50GB database file in an > apparently random order." and would like to try and get more information > about it.

Re: [sqlite] Any ideas on how long it takes to drop a large table...rather an important question now...

On 17 Jul 2018, at 8:37am, Rob Willett wrote: > I suspect that part of the issue is the VPS provider we use has a rate > limiter on IOPS which is not normally an issue for us, but that might have > slowed it down somewhat. However I don't think that it would have slowed it > down by hours.

Re: [sqlite] SQLite .dump

I'm sorry, but I don't see a question in your post. Are you suggesting that the SQLite command-line tool has a bug ? Are you suggesting that Ajqvue has a bug ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] Please unsubscribe me. Thanks

Click on the link at the bottom of every post to the list, including this one, then scroll to the bottom of the page to find how to unsubscribe. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] CSV import deletes trailing zeroes on text fields

On 12 Jul 2018, at 9:47am, Simon Leo Hafner wrote: > create table foo ( > text bar not null > ); Should be bar TEXT NOT NULL I'm not sure how your line is being parsed, but I can understand it thinking you have not set a column type. Simon.

Re: [sqlite] Lowering totalUsed

On 12 Jul 2018, at 8:30am, Cecil Westerhof wrote: > I am not quit happy with this. Would it be better to split it in two > queries and feed the result of the first to the second? I would guess that it will run faster. How much faster depends on how many rows there are in the table. Naturally

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

On 11 Jul 2018, at 6:01pm, Randall Smith wrote: > (o) Allow humans to view the contents of a DB without custom tools. > (o) Have a way to see what has changed between V1 and V2 of a database, e.g., > for a "change review." SQL is based around Ted Codd's view of relational databases. One of

Re: [sqlite] Minimum Delta Time

On 11 Jul 2018, at 3:25pm, Stephen Chrzanowski wrote: > interest is modifying the Stop part so that at a minimum, there is a 10 > minute delta between the start and end time. So if I start a timer at > 11:00, then stop at 11:01, I want the database to update the end time to > 11:10. To round a

Re: [sqlite] Round-tripping SQLite back and forth between text representation.

On 10 Jul 2018, at 1:52am, Randall Smith wrote: > I'm curious if there is some standard or normal way to convert a SQLite DB to > a text representation, and then recreate the DB content from the text. > Naively, this seems hard or impossible as a general problem, but perhaps I am > missing

Re: [sqlite] CHECK IGNORE?

On 9 Jul 2018, at 10:21pm, Thomas Kurz wrote: > However, this is a bit confusing as e.g. this works fine: > > CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE); There is actually a way to do what you want. You do it with a TRIGGER which does the test and drops the offending row using

Re: [sqlite] CHECK IGNORE?

On 9 Jul 2018, at 10:21pm, Thomas Kurz wrote: > However, this is a bit confusing as e.g. this works fine: > > CREATE TABLE test (col1 TEXT UNIQUE ON CONFLICT IGNORE); By 'works fine' do you mean you didn't get a syntax error, or do you mean that the clause actually does what you want, both to

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