Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

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, that means it’s up to the caller to handle
> quoting and escaping properly to avoid inadvertently creating SQL injection
> vulnerabilities.  Is that true, and if so, is there a plan to solve it?  I
> don’t mind running my queries through an HTTP[-ish] layer, but getting
> quoting and escaping right in hand-written code is a big source of errors
> I’d rather not return to.
>

There is a PHP binding (https://github.com/Expensify/Bedrock-PHP) but none
for C yet -- PRs welcome!  That said because it uses standard HTTP escaping
it should also be compatible with pretty much any HTTP C API -- or it's
very easy to hand roll.  When it comes to escaping, for example, you can
generally put the content directly in the HTTP content body, so Bedrock
introduces no "extra" escaping (beyond what is normally required by SQL, of
course).



> 2. No DBD::BedrockDB yet, I see.  When? :)
>

Heh, we don't have much Perl in our environment, but again, this would be
straightforward to add.



> 3. What happens if a local program attaches to the SQLite DB and queries
> it in the face of active replication?
>

SQLite is crazy hardened and reliable in this scenario.  You are free to
access the underlying SQLite database via the command line if you like, but
the locking behavior will be more restrictive.  BedrockDB not only supports
concurrent reads (via SQLite's standard WAL functionality), but concurrent
*writes* as well (via an experimental "BEGIN CONCURRENT" branch developed
by the SQLite team that we're hammering the kinks out of before its merged
into the mainline).  So use the Bedrock plugin architecture where possible
to get maximum concurrency, and never do writes from the command line as
it'll screw up our replication journal, but you are free to do reads via
the command line when more convenient (and we do it all the time).


4. What happens if a local program *inserts* data via the SQLite
> interface?  Does the data get replicated, or does that happen only if you
> insert via port ?
>

Correct, only insertions done via port  will get replicated, so it's
not recommended (but always possible for extreme maintenance scenarios --
about as often as you'd directly modify a local MySQL file with a hex
editor -- meaning, almost never).



> 5. I think your web pages answer this one implicitly, but let me ask just
> to be sure I’m guessing right: Does the MySQL API smooth out any of the
> semantic and language differences between MySQL and SQLite, or must you
> simply give SQLite-compatible queries over the MySQL interface?  (Expected
> answer: “No.”)
>

No -- we only support SQLite syntax (which is really more modeled off of
Postgres), but in practice the various MySQL clients we've tried don't
actually care about the syntax -- only the protocol -- so it works well
enough in practice.


6. What MySQL API version are you compatible with?  As maintainer of
> MySQL++, I can tell you, there is not just one version. :)
>

Good question, we don't have an exhaustive list.  The versions we use, at
least. :)  But the most recent version of SequelPro, the MySQL command line
client that ships with Ubuntu, and Mode Analytics to start.



> 7. Does using your MySQL API solve problem #1?  That is, if you use one of
> the available ways for constructing guaranteed-sound queries via
> libmysqlclient or one of the many libraries built atop it, does BedrockDB
> get the benefit?
>

I'm not sure what "guaranteed-sound" means, sorry!



> 8. Does your MySQL API solve problem #2?  That is, have you tested it
> against DBD::mysql?
>

I don't think we've tested against that as we don't use a lot of Perl, but
I'd be curious for your results!

-david
Founder and CEO of Expensify (and BedrockDB)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

Thanks for the fantastic questions!  Answers inline:

I had never heard of you guys, this looks amazing! How can you only have
> 222 github stars?!
>

Heh, feel free to give it one more!


> Paxos, so needs at least 3 nodes?
>

It works with any number of nodes, but I recommend 6 as the ideal: two
nodes in each of three different datacenters.  More on how the
synchronization technology works is here:
http://bedrockdb.com/synchronization.html


> How do you use it from an application that normally uses sqlite? Is it a
> drop-in replacement? I use Node.JS…
>

BedrockDB has an extremely simple wire protocol based around the concept of
"plugins".  The "db" plugin is described here: http://bedrockdb.com/db.html,
but there is also a full featured "cache" and *extremely* full featured
"jobs" plugin as well.  The protocol is easy enough that you can "netcat"
in and use it effectively by hand, or you can use our PHP binding:
https://github.com/Expensify/Bedrock-PHP

Interesting that you emulate mysql, given that sqlite tries to be
> postgresql compatible…
>

Our main use for this was to accommodate users who prefer certain clients
like SequelPro or Mode Analytics.  More information on our MySQL emulation
is here: http://bedrockdb.com/mysql.html



> Any war stories around developing this? Unexpected issues due to a missing
> comma etc?
>

Omg, so many war stories.  This has been powering Expensify from day one,
so we have about 8 years of scars as a result.  However, Bedrock gains the
benefit of all that painfully earned experience, and is hardened against a
million real-world edge cases that you won't need to worry about.


> Would you recommend this for any workload? I'm paticularly interested in
> replicating my append-only event log db.


In general I recommend Bedrock for more or less any workload that you would
give to a database (which in my experience, is about any workload involving
persistent data).  I've long wanted to build a "logs" plugin to Bedrock
that would provide full-text indexing ala Elasticsearch (which we use at
Expensify, but have pretty steady problems with), but it hasn't gotten bad
enough yet to worry about.  Thanks for asking!

-david
Founder and CEO of Expensify (and BedrockDB)



On Thu, Oct 26, 2017 at 10:19 PM, Wout Mertens 
wrote:

> I had never heard of you guys, this looks amazing! How can you only have
> 222 github stars?!
>
> Paxos, so needs at least 3 nodes?
>
> How do you use it from an application that normally uses sqlite? Is it a
> drop-in replacement? I use Node.JS…
>
> 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
> replicating my append-only event log db.
>
> Wout.
>
> On Thu, Oct 26, 2017, 8:15 AM David Barrett 
> wrote:
>
> > I'm glad you liked it!  I'd be happy to answer any questions you have
> about
> > http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> > listening!
> >
> > -david
> >
> > On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie 
> > wrote:
> >
> > > Hi All,
> > >
> > > Pardon the usual interruption of complex 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/floss-weekly/episodes/456
> > >
> > > Thanks to David for appearing on the show and of course to the Sqlite3
> > > team for their amazing efforts to make, and maintain the most widely
> > > deployed database engine in the world - maybe even in the galaxy.
> > >
> > > Thanks,
> > > j.b.
> > >
> > > --
> > > ---
> > > inum: 883510009027723
> > > sip: jungleboo...@sip2sip.info
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[R Smith]

Correction:

On 2017/10/27 12:57 PM, R Smith wrote:


[1]: PHP doesn't understand BedrockDB yet, but it understands SQLite / 
MySQL and Bedrock uses SQLite and has a MySQL connector.




I stand corrected, Bedrock does provide a PHP binding. From the website:

Bedrock also provides a PHP binding 
 that looks something like this:


|$bedrock = new Bedrock(); $result = $bedrock->db->query("SELECT 1 AS 
foo, 2 AS bar;"); |



So, it's even better than I claimed before. :)

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[Warren Young]

On Oct 26, 2017, at 12:15 AM, David Barrett  wrote:
> 
> I'm glad you liked it!  I'd be happy to answer any questions you have about
> http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> listening!

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, that means it’s up to the caller to handle 
quoting and escaping properly to avoid inadvertently creating SQL injection 
vulnerabilities.  Is that true, and if so, is there a plan to solve it?  I 
don’t mind running my queries through an HTTP[-ish] layer, but getting quoting 
and escaping right in hand-written code is a big source of errors I’d rather 
not return to.

2. No DBD::BedrockDB yet, I see.  When? :)

3. What happens if a local program attaches to the SQLite DB and queries it in 
the face of active replication?

4. What happens if a local program *inserts* data via the SQLite interface?  
Does the data get replicated, or does that happen only if you insert via port 
?

5. I think your web pages answer this one implicitly, but let me ask just to be 
sure I’m guessing right: Does the MySQL API smooth out any of the semantic and 
language differences between MySQL and SQLite, or must you simply give 
SQLite-compatible queries over the MySQL interface?  (Expected answer: “No.”)

6. What MySQL API version are you compatible with?  As maintainer of MySQL++, I 
can tell you, there is not just one version. :)

7. Does using your MySQL API solve problem #1?  That is, if you use one of the 
available ways for constructing guaranteed-sound queries via libmysqlclient or 
one of the many libraries built atop it, does BedrockDB get the benefit?

8. Does your MySQL API solve problem #2?  That is, have you tested it against 
DBD::mysql?

I think that’s enough for now. :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[Chris Locke]

Thanks Ryan - a handy summary.  Food for thought.


Thanks,
Chris

On Fri, Oct 27, 2017 at 11:57 AM, R Smith  wrote:

>
> On 2017/10/27 11:52 AM, Bart Smissaert wrote:
>
>> Is this BedrockDB something that could be used to connect to a server and
>> run SQL and avoid the problems (mainly slowness) that SQLite would
>> have in this situation?
>>
>
> and
>
> Chris Locke wrote:
> My work environment is mainly Windows servers/users.  SQLite 'works' but is
> obviously unsupported (file locking, etc).
> Could BedrockDb help in this area?  Sounds like it works 'locally' but
> 'networkably' (is that a word?!)  Couldn't find any Windows-friendly builds
> or guides.
> Even assuming it could be set up, it also looks like there aren't .Net
> drivers or 'wrappers' for it?
>
>
>
> This is not directly a client-server architecture, but you can achieve the
> same result by having a server with a DB node and a local node that you
> connect to locally, so you simply talk to your local node which in turn
> communicates to the server. The main difference between that and a
> client-server setup is that you experience no latency whatsoever, to your
> app it's as-if the server exists on the local machine (which is technically
> exactly the case), and of course the full dataset exists in two places,
> locally and on the server (at least 2, but it is recommended to have 3
> places), which may be unwanted if local storage is really tight, but then
> SQLite would also not have worked for you.
>
> [snipped]
> Anyway, that's how we do it.
>
> Cheers,
> Ryan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[R Smith]

On 2017/10/27 11:52 AM, Bart Smissaert wrote:

Is this BedrockDB something that could be used to connect to a server and
run SQL and avoid the problems (mainly slowness) that SQLite would
have in this situation?


and

Chris Locke wrote:
My work environment is mainly Windows servers/users.  SQLite 'works' but is
obviously unsupported (file locking, etc).
Could BedrockDb help in this area?  Sounds like it works 'locally' but
'networkably' (is that a word?!)  Couldn't find any Windows-friendly builds
or guides.
Even assuming it could be set up, it also looks like there aren't .Net
drivers or 'wrappers' for it?



This is not directly a client-server architecture, but you can achieve 
the same result by having a server with a DB node and a local node that 
you connect to locally, so you simply talk to your local node which in 
turn communicates to the server. The main difference between that and a 
client-server setup is that you experience no latency whatsoever, to 
your app it's as-if the server exists on the local machine (which is 
technically exactly the case), and of course the full dataset exists in 
two places, locally and on the server (at least 2, but it is recommended 
to have 3 places), which may be unwanted if local storage is really 
tight, but then SQLite would also not have worked for you.


I'm not sure it will work well for a many-client-server DB setup, since 
each client would have a full dataset and I'm not sure BedrockDB scales 
well in terms of having, say, more than 1000 nodes or such, but that is 
probably better answered by the Bedrock people.


There are client-server wrappers for SQLite, but Bedrock's main aim is 
to make your server data distributed among different servers, not to be 
a client-server interface.


If you want a client-server setup that's easy and free, simply pop an 
Apache service on your server with PHP (See XAMPP for a free stack that 
works in WIndows too), write one simple PHP script that gets a query 
from the POST variables and then responds with CSV format (or JSON or 
whatever you like) to whatever the query wanted. PHP understands SQlite 
and MSSQL and MySQL and Postgres etc, and you can do this in under an 
hour. (I'm happy to hand out working PHP for this if anyone is interested).


Since it's PHP you can later make it more robust to add security keys, 
users, sessions, everything that any ol' DB server offers. The 
connecting app simply makes an http post call to the server, saying hey 
this is me (perhaps adding a session, security tokens etc. later), gimme 
this query please.


Further, implementing your own server-side stored Procedures in PHP is 
too easy. And then later you can move all of this to a Linux server 
(also) if you please with zero effort, then using BedrockDB to keep your 
data replicated and live among all your servers.[1]


Maximum easy, Maximum flexibility, Maximum scalability and Maximum 
compatibility across platforms and completely free.
(You do pay a little bit of processing wait time more for PHP to pack 
your data from the DB cursor into some format before sending it, but 
that is in the order of milliseconds, usually not noticeable to the 
client, and user-clients should never deal with millions of rows of 
data, do that stuff server-side).


Anyway, that's how we do it.

Cheers,
Ryan


[1]: PHP doesn't understand BedrockDB yet, but it understands SQLite / 
MySQL and Bedrock uses SQLite and has a MySQL connector.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[Chris Locke]

My work environment is mainly Windows servers/users.  SQLite 'works' but is
obviously unsupported (file locking, etc).
Could BedrockDb help in this area?  Sounds like it works 'locally' but
'networkably' (is that a word?!)  Couldn't find any Windows-friendly builds
or guides.
Even assuming it could be set up, it also looks like there aren't .Net
drivers or 'wrappers' for it?


Thanks,
Chris

On Thu, Oct 26, 2017 at 7:15 AM, David Barrett 
wrote:

> I'm glad you liked it!  I'd be happy to answer any questions you have about
> http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> listening!
>
> -david
>
> On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie 
> wrote:
>
> > Hi All,
> >
> > Pardon the usual interruption of complex 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/floss-weekly/episodes/456
> >
> > Thanks to David for appearing on the show and of course to the Sqlite3
> > team for their amazing efforts to make, and maintain the most widely
> > deployed database engine in the world - maybe even in the galaxy.
> >
> > Thanks,
> > j.b.
> >
> > --
> > ---
> > inum: 883510009027723
> > sip: jungleboo...@sip2sip.info
> > ___
> > 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

Re: [sqlite] BedrockDB interview on Floss Weekly

[Bart Smissaert]

Is this BedrockDB something that could be used to connect to a server and
run SQL and avoid the problems (mainly slowness) that SQLite would
have in this situation?

RBS

On Fri, Oct 27, 2017 at 10:40 AM, R Smith  wrote:

>
> On 2017/10/27 7:19 AM, Wout Mertens wrote:
>
>> Interesting that you emulate mysql, given that sqlite tries to be
>> postgresql compatible…
>>
>
> It doesn't emulate MySQL, it has a MySQL connector so that you can connect
> it and do queries via your already-MySQL-using app. This is an add-on
> option, not the main mode of operation. I guess one could say the main
> operating mode (sans MySQL connector) really emulates SQLite, which really
> emulates PostGres.
>
> BedrockDB is brilliant.
>
>
>
>
>
>
> ___
> 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] BedrockDB interview on Floss Weekly

[R Smith]

On 2017/10/27 7:19 AM, Wout Mertens wrote:

Interesting that you emulate mysql, given that sqlite tries to be
postgresql compatible…


It doesn't emulate MySQL, it has a MySQL connector so that you can 
connect it and do queries via your already-MySQL-using app. This is an 
add-on option, not the main mode of operation. I guess one could say the 
main operating mode (sans MySQL connector) really emulates SQLite, which 
really emulates PostGres.


BedrockDB is brilliant.





___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BedrockDB interview on Floss Weekly

[Wout Mertens]

I had never heard of you guys, this looks amazing! How can you only have
222 github stars?!

Paxos, so needs at least 3 nodes?

How do you use it from an application that normally uses sqlite? Is it a
drop-in replacement? I use Node.JS…

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
replicating my append-only event log db.

Wout.

On Thu, Oct 26, 2017, 8:15 AM David Barrett  wrote:

> I'm glad you liked it!  I'd be happy to answer any questions you have about
> http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
> listening!
>
> -david
>
> On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie 
> wrote:
>
> > Hi All,
> >
> > Pardon the usual interruption of complex 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/floss-weekly/episodes/456
> >
> > Thanks to David for appearing on the show and of course to the Sqlite3
> > team for their amazing efforts to make, and maintain the most widely
> > deployed database engine in the world - maybe even in the galaxy.
> >
> > Thanks,
> > j.b.
> >
> > --
> > ---
> > inum: 883510009027723
> > sip: jungleboo...@sip2sip.info
> > ___
> > 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

Re: [sqlite] BedrockDB interview on Floss Weekly

[David Barrett]

I'm glad you liked it!  I'd be happy to answer any questions you have about
http://BedrockDB.com, our use of sqlite, or anything else.  Thanks for
listening!

-david

On Wed, Oct 25, 2017 at 4:19 PM, jungle Boogie 
wrote:

> Hi All,
>
> Pardon the usual interruption of complex 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/floss-weekly/episodes/456
>
> Thanks to David for appearing on the show and of course to the Sqlite3
> team for their amazing efforts to make, and maintain the most widely
> deployed database engine in the world - maybe even in the galaxy.
>
> Thanks,
> j.b.
>
> --
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> ___
> 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