Incidentally, Bedrock is built on a blockchain as well -- though I agree
with the sentiment that blockchain isn't actually new at all, and not that
big of a deal. More information is here:
http://bedrockdb.com/blockchain.html Hope you enjoy it!
-david
On Thu, Oct 11, 2018 at 3:06 PM R Smith wr
Ah, to clarify, we're very, very actively developing on Bedrock every
single day. In fact, we're about to roll it out to our new 3-datacenter,
6-server cluster of 384-core, 3TB RAM, 100Gbps fiber-connected machines!
All of Expensify is powered by it, so it's been battle tested with over a
decade o
On Fri, Oct 27, 2017 at 4:34 AM, Warren Young wrote:
> Before I get to the questions, I haven’t listened to the FLOSS episode
> yet, so please forgive me if these were asked and answered on the podcast.
> Just tell me if so, because I will eventually get to it.
>
> 1. I don’t see a C API. If so,
gt; Interesting that you emulate mysql, given that sqlite tries to be
> postgresql compatible…
>
> Any war stories around developing this? Unexpected issues due to a missing
> comma etc?
>
> Would you recommend this for any workload? I'm paticularly interested in
> replicati
ex sqlite questions...
>
> David Barrett was interviewed on Floss Weekly today and gave a rave
> review of his project, which is based on the wonderful sqlite3
> database.
>
> I'm only 10 minutes into the interview and really love it already!
> https://twit.tv/shows/flos
h 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 simplic
Whoaa!! This is amazing!
https://www.sqlite.org/c3ref/c_alter_table.html
Wow, thanks!
-david
On Mon, Jan 18, 2016 at 10:54 PM, Scott Hess wrote:
> On Mon, Jan 18, 2016 at 10:27 PM, David Barrett
> wrote:
>
> > One use of this I would like is to create a security f
One use of this I would like is to create a security framework around
arbitrary SQL queries from the user. So, for example, I'd love to
determine which tables (and which columns of those tables) a particular
query is going to access, and then compare that list against a whitelist of
columns the us
Thanks Richard, this is exactly what I was thinking. One question on this:
On Tue, Oct 6, 2015 at 10:22 PM, Richard Hipp wrote:
> (2) For the source database connection of the backup, use the same
> database connection that is used for writing to the database. That
> means that when changes ar
On Tue, Oct 6, 2015 at 8:36 PM, Simon Slavin wrote:
> Or copy an existing /open/ database file to the new server using the
> SQLite Backup API, [requires other connections to stop modifying the
> database for long enough for the copy to be made]
>
Well the backup API works with WAL mode [1] so i
On Tue, Oct 6, 2015 at 2:57 PM, Clemens Ladisch wrote:
> It backs up to any disk that you can access.
> Do you have a network file system?
>
Well yes, but I'd like to handle it at the application layer. Basically,
we operate a custom replication layer atop sqlite. It replicates
individual tran
sqlite has a cool "online backup" API: https://www.sqlite.org/backup.html
However, it only backs up to a local disk. I'm wondering if anybody can
think on how to use this API to do an incremental backup over a network
connection to a remote host? The networking part is easy. But I can't
figure
Hey all, just wanted to share this in case anybody is also looking for a
very simple tutorial for CTE's in sqlite:
http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
The Simplest SQLite Common Table Expression Tutorial
I?ve been trying to wrap my head arou
Hello! If I have a database that is larger than the system's physical RAM,
am I correct in thinking I should actually set a very *small* page cache so
as to avoid "double caching" the same pages in both sqlite and the file
cache?
To clarify, if the database is *smaller* than system RAM, I imagine
For what it's worth, I'd also love some official JSON support, with JSON
indexes (eg, a function index that pulls a JSON value). However, I agree
it doesn't make sense to add to the main codebase -- I was more thinking an
official plugin (so we don't just keep writing our own over and over).
Davi
e1|value4
sqlite> select * from actions;
inserted "name1", "value1"
inserted "name2", "value2"
updated "name2" from "value2" to "value2.1"
deleted "name2", "value2.1"
deleted "name1", "value1"
I fear I already know the answer to this, but I want to ask to make sure:
is there ANY way to craft a trigger that can detect when an INSERT OR
REPLACE trigger does a REPLACE versus an INSERT? I found this thread which
suggested it wasn't possible:
http://sqlite.1065341.n5.nabble.com/Trigger-logi
ce are
> combined: nothing works and no one knows why.
>
>
> >-Original Message-
> >From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> >bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
> >Sent: Sunday, 1 March, 2015 11:41
Hi! If I define a column as BLOB type, will length() run strlen() on the
data, or will it consult some kind of internal value to determine the
length? The documentation says:
"For a string value X, the length(X) function returns the number of
characters (not bytes) in X prior to the first NUL ch
e to create a
comprehensive trigger.
Cool, thanks for the helpful tips!
-david
On Fri, Jan 30, 2015 at 9:31 PM, Tristan Van Berkom <
tris...@upstairslabs.com> wrote:
> On Sat, 2015-01-31 at 00:04 -0500, Igor Tandetnik wrote:
> > On 1/30/2015 10:44 PM, David Barrett wrote:
> >
Is it possible to create a trigger that calls a custom function and passes
in NEW.*? To break that question down:
1) I know it's possible to create custom functions that take a variable
number of parameters.
2) I'm *assuming* if you pass a "*" into that function, it'll just call
that function wi
Got it, so this should work fine, so long as I'm careful to always re-add
the collate functions to each database handle before accessing the table
with that index. Cool, thanks!
-david
On Sun, Jan 18, 2015 at 1:16 PM, Simon Slavin wrote:
>
> On 18 Jan 2015, at 8:29pm, David Bar
Incidentally, I just added this last night to a project I'm working on --
my first custom function! Once again I'm impressed with the power of
sqlite as I went from concept to working implementation in just 2 hours.
I'm using a JavaScript-style query syntax, where you just directly
dereference t
Sorry for the slow response -- yes, this is great logic. We're just
disabling vacuum. Thanks!
-david
On Mon, Dec 8, 2014 at 6:18 PM, Simon Slavin wrote:
>
> On 9 Dec 2014, at 1:36am, David Barrett wrote:
>
> > *Re: Why VACUUM.* We vacuum weekly. This particular databa
Hi all, great questions:
*Re: Why VACUUM.* We vacuum weekly. This particular database is a
"rolling journal" -- we are constantly adding new rows to the end of the
table, and every week we truncate off the head of the journal to only keep
3M rows at the "tail". Given that we're truncating the "
Hi! I have a large database, and running the VACUUM pragma hammers disk IO
so badly that it dramatically affects performance on the box. I'm not in a
hurry for the results, so I'm curious if you can think of a way using the
API (or any other way) to essentially "nice" the process by inserting a
s
hris Peachment wrote:
> On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote:
> > Thanks Alek! Yes, we're definitely planning on it, just trying to
> > find the right time. We don't want to go through the work to open
> > source it only to be greeted with silenc
On Tue, Oct 30, 2012 at 1:00 AM, Alek Paunov wrote:
> On 29.10.2012 11:58, David Barrett wrote:
>
>> Because in practice, as someone actually doing it (as opposed to
>> theorizing
>> about it), it works great. The MySQL portions of our service are always
>>
On Mon, Oct 29, 2012 at 8:28 PM, Richard Hipp wrote:
> On Mon, Oct 29, 2012 at 5:58 AM, David Barrett >wrote:
> > So what specifically do you feel is the problem with sqlite at scale?
> >
> > And to be clear, I'd like to pre-empt "well it doesn't do X, wh
, we try to get SQLite to do the same thing as
> PostgreSQL.
>
> Far be it from me to recommend one client/server database engine over
> another. But in my experience. well, you can fill in the rest,
> probably...
>
> On Sun, Oct 28, 2012 at 10:48 AM, David Barrett >
iosity is a good thing in certain situations. But could you
> kindly tell me what will you do with this information (assuming it's
> possible to obtain it of course)?
>
> Pavel
>
> On Sat, Oct 27, 2012 at 11:54 PM, David Barrett
> wrote:
> > I completely understand t
orization and
> authentication mechanisms, and so on and so forth. Database format is
> never a part of the decision which DBMS you want to use.
>
> Pavel
>
>
> On Sat, Oct 27, 2012 at 9:32 PM, David Barrett
> wrote:
> > Thanks, this is really helpful! (And I lectu
qlite and mysql when it comes to fragmentation. I often get in debates
about sqlite versus other datbases, and I'm always eager to be informed.
Thanks!
-david
On Sat, Oct 27, 2012 at 11:42 PM, Simon Slavin wrote:
>
> On 27 Oct 2012, at 11:38am, David Barrett wrote:
>
> > I
I'm trying to learn more about MySQL versus sqllite when it comes to
vacuuming and fragmentation, especially as it relates to SSD storage. Can
anybody summarize for me the differences?
1) My understanding is sqlite, in general, has no automatic
defragmentation: there is no process to gradually an
On Fri, Sep 28, 2012 at 10:49 AM, Jay A. Kreibich wrote:
> The standard C I/O library includes the tmpfile() call, which performs
> the whole process of generating a random temporary file name, opening
> the file, and then unlinking the file.
Wow, very interesting, thank you. I stand cor
Incidentally, I agree with Simon. It exchanges the extremely easy problem
of temp namespace collision for the very hard problem of invisible files
filling up your hard drive. Indeed, it was diagnosing this hard problem --
at great cost in time and confusion -- that caused us to discover it in the
In my experience, retrying does often work.
-david
On Tue, Sep 25, 2012 at 7:31 PM, Rittick Gupta wrote:
> Do you think a retry of the query would help resolve this issue ? Do I
> have to close & reopen the database.
>
> Thanks for your help.
>
> regards,
>
> Rittick
> _
Yes, still having that problem. We've moved to new servers with SSD's and a
ton of RAM, and that seems to have helped matters -- not sure why, though I
don't know why it was happening in the first place. (I'm *guessing* the
issue is due to two conflicting queries happening at the same time, so if
If my database is X GB, and the WAL file is Y GB, how much total disk space
is required to:
1) Checkpoint the database
2) Vacuum the database
Additionally, if there are any temporary files created, where do they
exist? We've done some poking around and are having some crazy theory
about a tempo
You might be interested in this thread:
http://www.theusenetarchive.com/usenet-message-sqlite-does-disabling-synchronous-and-shared-cache-cause-%22error-database-disk-image-is-malformed%22-20780199.htm
No conclusion was ever obtained, but the discussion was good.
-david
On Mon, Sep 24, 2012 at
On 08/16/2012 11:19 AM, Richard Hipp wrote:
On Thu, Aug 16, 2012 at 1:52 PM, Tal Tabakman wrote:
Hi.
we are writing an application that in runtime produces a sqlite database.
we get the following error:*Error: database disk image is malformed
Extended Error code: 11*
*
*
*what can cause this e
On 02/07/2012 06:30 PM, Richard Hipp wrote:
Everything is compatible. Version 3.7.2 and 3.7.10 will interoperate
fine. Older command-line shells *are* compatible with newer versions of
the library.
Drat. I mean, that's a great design, but I was hoping that would solve
the problem. So still
On 02/07/2012 05:27 PM, Richard Hipp wrote:
On Tue, Feb 7, 2012 at 8:13 PM, David Barrettwrote:
My best guess still is that the command-line shell is somehow not seeing
the shared-memory file that is created when the database is running in WAL
mode. Or, perhaps the posix advisory locks are not w
On 02/07/2012 05:08 PM, Richard Hipp wrote:
There are no known limitations on the use of PRAGMA synchronous=OFF, WAL
mode, and shared cache together.
Ah, I guess that blows that theory. But thanks for the quick and
thorough response nonetheless. I'm a huge sqlite fan. I just wish I
could f
On 02/07/2012 04:05 PM, Richard Hipp wrote:
This tells me that the error is occurring at
http://www.sqlite.org/src/artifact/5047fb303cdf6?ln=1362 which occurs right
as SQLite is first starting to decode a page that it as loaded from the
disk. The error indicates that the shell really is seeing a
On 02/07/2012 03:00 PM, Richard Hipp wrote:
On Tue, Feb 7, 2012 at 5:19 PM, David Barrettwrote:
2) However, we get erratic behavior when using the sqlite3 command-line
tool to just do a basic select on the database: sometimes it works,
sometimes it returns "Error: database disk image malformed".
Hi! We're seeing the dreaded "Error: database disk image is malformed"
message, even though the database is actually fine. I've read a few
other threads on it and I'd previously concluded that it was just an
outstanding bug. But I'm wondering if it's actually due to a
combination of disablin
Hi! Can you help me understand more exactly what the output parameters
are from sqlite3_wal_checkpoint_v2()? Specifically:
1) What is the relationship between pnLog and pnCkpt: is pnLog>=pnCkpt
always true?
2) Under what circumstances would pnLog>pnCkpt be true?
Also, can you confirm I un
On 08/08/2010 10:09 PM, Dan Kennedy wrote:
>>> 2) However, one process cannot read from the database while another
>>> is writing -- WAL is irrelevant here.
>>
>> Unless shared-cache mode is turned on, multiple threads each using
>> their own sqlite3* connection should behave in the same way as
>>
On 08/08/2010 10:00 PM, Dan Kennedy wrote:
>
> On Aug 9, 2010, at 11:17 AM, David Barrett wrote:
>> 3) When an application performs read/write queries on the database in
>> parallel to the .backup command being run on the database, will the
>> application occasionally get
I'm reading up on the new write-ahead logging feature, and I'm unclear
on one point: does WAL only help alleviate multi-threaded locking (by
allowing other threads to continue reading while one is writing), or
does WAL also help between multiple processes?
My understanding could be completely w
I *think* I know the answers these questions, but can you please confirm
them for me?
1) Does the sqlite3 command-line app .backup command use the
http://www.sqlite.org/backup.html API, and thus only read-lock the
database for brief moments (rather than read-locking the database for
the entire
Ah, thanks. I think I'll wait for the next stable release and go to
that; in the meantime I'll use the WHERE 1 trick. Thanks!
-david
Dan wrote:
> On Oct 30, 2008, at 3:10 PM, David Barrett wrote:
>
>> Ok, getting close: now I test for changes in sqlite3_total_change
to detect the change without losing that
optimization? Thanks!
-david
Igor Tandetnik wrote:
> David Barrett <[EMAIL PROTECTED]> wrote:
>> If so, is there any general way to determine -- given an arbitrary
>> query -- whether or not it changed the database?
>
> Run P
Am I correct in understanding sqlite3_total_changes() doesn't include
changes to the "master" tables, and thus won't reflect schema changes?
If so, is there any general way to determine -- given an arbitrary query
-- whether or not it changed the database?
Basically, I'm creating a replay log a
What's the right way to use update/commit/rollback hooks to produce a
replay log?
Currently I'm doing it at a high level by just recording all SQL
statements into a replay log, and that works really well except fails in
some cases like with the use of CURRENT_TIMESTAMP. (Replaying that will
i
Igor Tandetnik wrote:
> David Barrett <[EMAIL PROTECTED]> wrote:
>> There is a single table (bar) with a single column (foo) with a single
>> row containing the integer value "1". Two processes (A and B) have
>> opened the database in READWRITE mode. They bo
Can you please double-check the following to make sure it's accurate?
I've read the "File Locking and Concurrency" page and I'm trying to
verify how SQLite works in the following scenario:
There is a single table (bar) with a single column (foo) with a single
row containing the integer value "1
event (aka
transaction)... Perhaps a bit cleaner, but still not great.
Anyway, I was just curious if the COMMIT_COUNT() function existed,
because then it'd be really clean and easy. But it sounds it doesn't,
and that's what I wanted to know. Thanks!
-david
D. Richard Hipp wr
nsaction number" as there can be only 1. Now you may go ahead
> and implement "pseudo transactions" and implement your own transaction
> number. etc...
>
> HTH,
> Ken
>
>
> David Barrett <[EMAIL PROTECTED]> wrote: What's the best way to grou
What's the best way to group undo log entries by transaction? Is there
a function that returns the current transaction number? Or what's the
best way to set a global variable that is inserted into the undo log by
the trigger?
As background, the wiki has a great page on undo/redo:
htt
Ah, it sounds like we're building essentially the same thing -- mine is
also for a financial setting. You're right, a mirror-replicating mode
might be adequate, and is certainly simpler... But part of me is afraid
that it leaves you vulnerable to failure conditions that a full
two-phase commi
What are the recommended "best practices" around using SQLite in a
distributed scenario?
(I saw Shailesh's email in March; I'm considering building a two-phase
commit layer atop SQLite nodes and looking for advice.)
Clearly, this is a big topic including failover, recovery,
load-balancing, and
64 matches
Mail list logo
