BedrockDB interview on Floss Weekly

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

BedrockDB interview on Floss Weekly

Jungle Boogie
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: [hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

David Barrett-4
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 <[hidden email]>
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: [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

wmertens
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 <[hidden email]> 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 <[hidden email]>
> 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: [hidden email]
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

Chris Locke-3
In reply to this post by David Barrett-4
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 <[hidden email]>
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 <[hidden email]>
> 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: [hidden email]
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

R Smith
In reply to this post by Bart Smissaert

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

Chris Locke-3
Thanks Ryan - a handy summary.  Food for thought.


Thanks,
Chris

On Fri, Oct 27, 2017 at 11:57 AM, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

Warren Young
In reply to this post by David Barrett-4
On Oct 26, 2017, at 12:15 AM, David Barrett <[hidden email]> 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 8888?

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

R Smith
In reply to this post by 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
<https://github.com/Expensify/Bedrock-PHP> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

David Barrett-4
In reply to this post by wmertens
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 <[hidden email]>
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 <[hidden email]>
> 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 <[hidden email]>
> > 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: [hidden email]
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [hidden email]
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: BedrockDB interview on Floss Weekly

David Barrett-4
In reply to this post by Warren Young
On Fri, Oct 27, 2017 at 4:34 AM, Warren Young <[hidden email]> 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 8888?
>

Correct, only insertions done via port 8888 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users