Re: [sqlite] Typical suffixes of sqlite database files
At 01:17 21/10/2016, you wrote: What suffixes to sqlite database files do you use or see in the wild? I routinely/always use .sq3 but that's only a personal convention. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Drop Existing Table Results in 'no such table'
I am trying to rename a table by editing the sqlite_master directly. I do know there are a ‘ALTER TABLE … RENAME TO …’ syntax to do that. I just want to try it in this way. But I find that the ‘no such table’ error occurs while I try to drop the renamed table. Only I close the connection or start a new connection, the renamed table can be dropped. It seems that the memory inside this connection out of date and 'PRAGMA schema_version=X+1’ do not update it. I use theprocedure wrote at the bottom ofhttps://www.sqlite.org/lang_altertable.html. It may be a tricky way, but it may not be wrong. Does any one know about this? Here is my test code: SQLite version 3.14.0 2016-07-26 15:17:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite .open test sqlite CREATE TABLE A (i int); sqlite select * from sqlite_master; table|A|A|2|CREATE TABLE A (i int) sqlite BEGIN sqlite PRAGMA schema_version; 1 sqlite PRAGMA writable_schema=ON; sqlite UPDATE sqlite_master set name='B', tbl_name='B', sql='CREATE TABLE B (i int)' where name='A'; sqlite PRAGMA schema_version=2; sqlite PRAGMA writable_schema=OFF; sqlite COMMIT; sqlite DROP TABLE B; Error: no such table: B sqlite .exit ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typical suffixes of sqlite database files
Hello, I'm asking this for further discussion of a deficiencies of emacs sql-mode w/ sqlite databases (https://debbugs.gnu.org/cgi/bugreport.cgi?bug=23566). What suffixes to sqlite database files do you use or see in the wild? There are for sure .db (e.g. firefox) .sqlite[23]? .fossil (Fossil) .fslchout (Fossil) What else do you see? Ideally with source (application / framework) Thanks. rolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Risk of damaging a non-sqlite file with `sqlite3 `?
Hello, I'm asking this for further discussion of a deficiencies of emacs sql-mode w/ sqlite databases (https://debbugs.gnu.org/cgi/bugreport.cgi?bug=23566). If the cmd line tool sqlite3 is used, is it possible to damage a file, given as sqlite database file argument to sqlite3? Just sqlite3 existing.file with any (non sqlite database file) file works for me and present me the ordinary sqlite3 cmd line prompt. I'm able to make selects but as far as I do something, that needs the database file (reading data or altering it) I get the error: Error: file is encrypted or is not a database After exiting sqlite3, I find some.file unmodified. While this is fine (and sane) (and expected) this is just some anecdotal data by me. It would help me in the above mentioned discussion, if I could point to a more trusted source, that the sqlite developer activly care about (by magic bytes et. al.) not mangling non sqlite database files erroneously given as sqlite database file argument to sqlite3. rolf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] spellfix1 is unable to match words beginning with 'Kn' and 'Gn'
Is this the right list to report issues with the spellfix1 extension? Here's a complete repro case: [deepthought ~] ls -l newdb ls: newdb: No such file or directory [deepthought ~] sqlite3 newdb SQLite version 3.14.2 2016-09-12 18:50:49 Enter ".help" for usage hints. sqlite> SELECT LOAD_EXTENSION('spellfix1.dylib'); sqlite> CREATE VIRTUAL TABLE IF NOT EXISTS fuzzy USING spellfix1; sqlite> INSERT INTO fuzzy (word) VALUES ('Knight'); sqlite> SELECT word, distance FROM fuzzy WHERE word = 'Knight'; Knight| sqlite> SELECT word, distance FROM fuzzy WHERE word MATCH 'Knight'; sqlite> SELECT word, distance FROM fuzzy WHERE word MATCH 'Night'; Knight|100 sqlite> Is this something configurable? Is it a bug? Thanks! Tom ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 consecutive rises in value
Thanks, will give that a try as well, but I am happy with the solution form R Smith. Only one relatively simple SQL and quite fast. Took about 1.5 secs to process 5 rows. RBS On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut < jean-luc.hain...@unamur.be> wrote: > > What about this one? > > create table TABLE1(Seq integer primary key autoincrement, > Id integer, Date date, Value integer); > insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...; > > select distinct T1.Id-- only one per Id > from TABLE1 T1, TABLE1 T2, TABLE1 T3 -- very fast join on > indexed rowid > where T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2 -- consecutive triples > andT1.Id = T2.Id and T3.Id + T1.Id -- same Id > andT1.Value < T2.Value and T2.Value < T3.Value; -- ascending Values > > Works if: > - the rows are inserted in ascending values of (Id,Date) (if they don't, > just create and load a temp table with the sorted rows of TABLE1) > - and if the rows are unique on (Id,Date). > > If rowid column Seq cannot be added to source TABLE1, load data in temp > table TABLE2 with columns (Seq,Id,Date,Value). > > Probably not as fast as a purely procedural algorithm and less elegant > than previous proposals, > but probably faster than previous proposals and more elegant than a purely > procedural one! > > J-L Hainaut > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2 consecutive rises in value
What about this one? create table TABLE1(Seq integer primary key autoincrement, Id integer, Date date, Value integer); insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...; select distinct T1.Id-- only one per Id from TABLE1 T1, TABLE1 T2, TABLE1 T3 -- very fast join on indexed rowid where T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2 -- consecutive triples andT1.Id = T2.Id and T3.Id + T1.Id -- same Id andT1.Value < T2.Value and T2.Value < T3.Value; -- ascending Values Works if: - the rows are inserted in ascending values of (Id,Date) (if they don't, just create and load a temp table with the sorted rows of TABLE1) - and if the rows are unique on (Id,Date). If rowid column Seq cannot be added to source TABLE1, load data in temp table TABLE2 with columns (Seq,Id,Date,Value). Probably not as fast as a purely procedural algorithm and less elegant than previous proposals, but probably faster than previous proposals and more elegant than a purely procedural one! J-L Hainaut ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [System.Data.SQLite] Using sqlite3.dll instead of Interop.dll - what's the trade-off?
A follow-up on this (and a late thanks!): I've used your answer to write some build instructions for Ubuntu and Raspbian at http://blog.wezeku.com/2016/10/09/using-system-data-sqlite-under-linux-and-mono/ I've also modified the F# SQLProvider type provider so that it no longer always loads Mono.Data.SQLite under Mono, but allows you to specify that System.Data.SQLite should be loaded regardless of whether the platform is .NET or Mono: http://fsprojects.github.io/SQLProvider/core/sqlite.html#SQLiteLibrary /John > There are various compile-options and extensions baked into the > "SQLite.Interop.dll" that are not enabled and/or included by > default with "sqlite3.dll". > > One that is somewhat important, is the "vtshim" extension. It > is required if you want to implement a virtual table in managed > code. It's also fairly tightly integrated into the resulting > "SQLite.Interop.dll", by necessity. > > It is possible to compile the "SQLite.Interop.dll" for Linux, > Mac OS X, and probably other POSIX compliant systems, using > the following build script: > > https://urn.to/r/7C > > -- > Joe Mistachkin @ https://urn.to/r/mistachkin ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizations to recover early from D state of the process
On 19 Oct 2016, at 2:22pm, Rutuja Shahwrote: > I observed that the process goes into D state and takes > around 5 to 15 minutes to recover. There is nothing in SQLite that could put a process in TASK_UNINTERRUPTIBLE mode. SQLite doesn't know much about your storage system and certainly doesn't try to talk to such a low level of your operating system. It seems possible that you have a hardware fault or a bug in your storage system. Are you storing your data on a disk inside the computer running SQLite ? Or is it using some kind of storage across a network ? Are you calling the SQLite C API directly or are you using a SQLite library ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Optimizations to recover early from D state of the process
Rutuja Shah wrote: > the process goes into D state and takes around 5 to 15 minutes to recover. There appears to be something wrong with your storage system. Are you using a local disk, without RAID, not in a VM? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Optimizations to recover early from D state of the process
Hi, Requirement 1: Data dumped to the DB every 15 minutes. One table per day, data is stored for the last 7 days. 2 such tables needed. Total number of tables - 7 days * 2 tables = 14 Average number of rows inserted per transaction (per 15 minutes per table) - 1,20,000 Requirement 2: Data dumped to the DB every 15minutes. One table to store data for 1 hour. Total number of tables to store data for last 7 days - 7 * 24 = 168 Average number of rows inserted per transaction (per 15 minutes) - 30,00,000 Version sqlite-3.8.8.1, WAL mode and SYNCHRONOUS=NORMAL I wrote a C program which performs the above operations every 15 minutes. I observed that the process goes into D state and takes around 5 to 15 minutes to recover. On seeing the strace of the process, I observed multiple file operations assuming that data is being dumped into the database file. Also, I have created index at the time of table creation. Are there any optimizations to reduce the time the process is in D state? Should I consider sqlite or move to some other DB for such heavy write operations? Thanks Rutuja ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Introducing Bedrock: SQLite for the Enterprise
Hm, that's an interesting case that I don't quite follow. But you could definitely use Bedrock on 2 nodes, and Bedrock would handle realtime replication from one to the other. This would provide an instant offsite backup. Granted, if there were only 2 nodes, you couldn't take one offline, as the other one wouldn't have quorum. If your goal is to not just have a backup, but also copy that backup to tape, I actually recommend a 3 node deployment. This way you can take one node offline and the remaining two still have quorum. Now, another crazy idea would be to have a 2 node cluster, where one of the nodes is a "permaslave". Permaslaves don't participate in quorum, so taking it offline wouldn't affect the master -- which would go forward exactly as if it were alone (because from a quorum perspective, it is, and thus it always represents its own quorum). This *might* "just work" out of the box, I haven't tried it. But it's an interesting case! -david On Wed, Oct 19, 2016 at 6:17 AM, Stephen Chrzanowskiwrote: > So am I to understand you just "server-ized" SQLite with Bedrock? > > Looks rather interesting. I was just talking to my IT manager about how I > can take something like a SQLite backup and put it somewhere else so it'll > eventually get to tape, but, if I run a node on my local machine, run one > on the 'primary' and another on the 'backup' of the primary, it'd satisfy > quorum, and I'd be thinking less of backups. (But its still a thought) > > Is there a way to disable the check for quorum and either let the split > brain happen, or at least make the executive decision to which is the > primary data source at all times? In the tool I'll be writing, it COULD > happen that only one node would be available. We typically run our servers > here at work in primary/secondary fashion, no tertiary, so if the primary > goes away, and its only the secondary, then my software would go down, > which is something I obviously want to avoid. We also do typically one-way > replication. > > Is there a mechanism that will allow me to run the Backup API to dump the > database on a particular node? > > > On Tue, Oct 18, 2016 at 8:45 PM, David Barrett > wrote: > > > Love SQLite? Wish you could use it to power your enterprise SaaS or web > > service? Now you can! Check out Expensify Bedrock, our distributed > > transaction layer built atop SQLite, powering Expensify's millions of > > users. More information is here: > > > > http://bedrockdb.com > > > > > > Keep all the power and simplicity of SQLite, but wrapped in a package > that > > provides network accessibility, WAN-optimized replication, and > distributed > > ACID transactions. Under continuous development and operation for the > past > > 8 years, now it's open sourced and ready for your production use. > > > > Thank you to the SQLite team for not only producing such an incredible > > database, but helping with our countless questions and demanding > > requirements. I'm ecstatic to share this with you, and I hope you enjoy > it > > too! > > > > -david > > Founder and CEO of Expensify > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users