Re: [sqlite] Typical suffixes of sqlite database files

[Jean-Christophe Deschamps]

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'

[sanhua.zh]

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

[Rolf Ade]

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 `?

[Rolf Ade]

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'

[Thomas David Baker]

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

[Bart Smissaert]

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

[Jean-Luc Hainaut]

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?

[John Reynolds]

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

[Simon Slavin]

On 19 Oct 2016, at 2:22pm, Rutuja Shah  wrote:

> 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

[Clemens Ladisch]

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

[Rutuja Shah]

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

[David Barrett]

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 Chrzanowski 
wrote:

> 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