Re: [sqlite] SQLite DBaaS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/12 20:32, Ryan Macy wrote:
> Simple is exactly what I'm trying to accomplish Nico. It's a pet
> project to increase my knowledge, yet the high cost of most database
> options on PaaS providers like Heroku leads me to believe that the
> users would welcome a [very] low cost simple DBaaS implementation.
> SQLite also hasn't been in the DBaaS space as far as I can tell, I want
> a first :-)

You won't get very far trying to provide SQLite as a service for others
since there will be so much to invent.  The cost at providers includes
things like multiple servers running so no single system failure causes
problems, backups, replication, system administration, audit trails,
response times, people and machines.  In your case you also want to
convince developers to use a new API, provide new debugging tools,
implement a new identity, authentication and authorization mechanism and
provide controls against malicious code or bugs.  Additionally this has to
be very low cost and presumably the data doesn't matter that much (cost,
only one server at a time etc).  I'd be delighted if you proved my
scepticism wrong (progress depends on people being unreasonable!) but I'm
sure we'll encounter flying bacon first.

However there is another way of meeting similar goals.  Have a look at
Fossil (also by the author of SQLite).

  http://fossil-scm.org/index.html/doc/trunk/www/index.wiki

Of note it is a single binary that has an embedded copy of SQLite, a web
server, and the function is to provide source code control management.
All in a single binary.  When linked statically that binary will run
virtually anywhere (low cost enough for you?)

It doesn't need to provide a lot of the heavyweight identity, auth & auth
and similar things because people just run a copy for themselves.  It also
has replication builtin so that addresses the need for backups and
communicating data with others.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8g3FQACgkQmOOfHg372QQicwCfdsgijdzjePnqAI3CD7Tz5Joq
utIAoKAspR1RA0W/augCra4irvae7OUN
=P5SE
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DBaaS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/12 20:29, Ryan Macy wrote:
> I was trying to say that I don't believe this would have anything to
> do with SQLite, you would have to deal with these issues regardless of
> the solution that is selected.

Those issues (identity, authentication, authorization) are already
addressed in MySQL, Postgres, Oracle and many other databases.  You'd have
to invent a new solution if using SQLite and it will be a lot of work that
they have already done for you.

> That is a good point, I will need to take that into consideration. 
> Multiple queries, and queries that all rely on each other, are items I 
> have been struggling to conceptualize implementation for. It presents
> an interesting problem.

If you love JSON and Python (count me in on both counts) then I strongly
recommend you take a look at MongoDB (and pymongo).  (MongoDB has many
good language bindings and isn't Python specific.)  The query language is
essentially JSON.  You store your data denormalized so you have fewer
issues with data items relying on other items.

Note that MongoDB isn't engineered to be exposed to the raw Internet
either, although it does have authentication and (weak) authorization.  In
general you wouldn't really want to expose any kind of database service
directly to arbitrary clients supplying SQL.  Instead you put up a front
end - REST semantics and JSON are recommended, with your front end code
then accessing the database behind the scenes.

A year ago I was doing lots of work to take Postgres database dumps, feed
them into SQLite and then denormalize the data into MongoDB ...

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8g2SUACgkQmOOfHg372QQMNgCeO7e0hGuEAadXpBFSOKHFmmDn
odAAoKCyRU3Ms5f9/SBxw2amSLrKGZyw
=8OTf
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DBaaS

[Ryan Macy]

Larry I think we moved past this.

I was put off but the bluntness of his response, I'm not trying cause
issues.

Ryan Macy





On 1/25/12 11:29 PM, "Larry Brasfield"  wrote:

>(Top-posting undone for comprehensibility.)
>Ryan Macy wrote:
 Would there be any reason SQLite would have a hard time functioning in
 a database as a service model?
>
>Roger Binns replied:
>[Reasonable explanation of why SQLite as a service is not a good solution,
>including "introduction of latency" and "need to ... authenticate", and
>suggestion that problem is better solved by existing database servers ]
>...
>>> Have you even used SQLite?
>
>To which Ryan Macy replied:
>> This seems inconsequential -- regardless of what method you use you will
>> have to authenticate. Everything has latency. I'm not sure where you're
>> going with this.
>>
>> You came off as a prick, but maybe I didn't offer enough information...
>>Or
>> my question is not intelligent enough for youŠ
>>
>> I appreciate your opinion though, thank you.
>
>Roger's point, which is quite consequential, and politely made, is that
>SQLite solves only a part of the problem that you posed.  As for
>"everything
>has latency", that is not quite the issue to which Roger alluded.  As you
>may discover, latency becomes a real issue in network scenarios, one that
>can create serious difficulties when inadequately handled.  And as you may
>already understand, latency is not always an issue that requires
>attention.
>
>You should learn to keep your emotional reactions to yourself when you
>find
>yourself writing words like "prick".
>
>You provided enough information to show that you have little comprehension
>of the issues that will arise were you to use SQLite as you suggest.
>Roger
>provided a calm, reasonable, but brief hint of what they will be.  That he
>took the time to do this is to his credit, and was an act undeserving of
>your reaction.
>
>Your question was, in fact, an indication that you are either completely
>naive regarding latency and security issues, or that you had very little
>familiarity with SQLite.  Hence his question, "Have you even used SQLite?"
>
>To me, Roger's response reflected an effort to help you rather than any
>kind of belittlement.  You would do well to understand that, even if you
>sometimes feel foolish when those who know more expose your naivety.
>
>Regards,
>-- 
>Larry Brasfield
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SQLite DBaaS

[Ryan Macy]

Simple is exactly what I'm trying to accomplish Nico. It's a pet project
to increase my knowledge, yet the high cost of most database options on
PaaS providers like Heroku leads me to believe that the users would
welcome a [very] low cost simple DBaaS implementation. SQLite also hasn't
been in the DBaaS space as far as I can tell, I want a first :-)

Thanks for your thoughts Nico!

Ryan Macy





On 1/25/12 11:16 PM, "Nico Williams"  wrote:

>If you're building a small web service, SQLite3 will do fine.  If you
>want to scale big you might be able to use SQLite3 for some pieces of
>it, but you can't scale up a web service to thousands of servers with
>tens of cores and one single SQLite3 DB -- that just doesn't work
>given SQLite3's architecture.  The good news is that in web services
>you're constantly rewriting the service, and so you'll have an
>opportunity to move to a different solution at the right time, and if
>using SQLite3 gets you off the ground quickly, then so much the better
>for you.
>
>On the other hand, you might want to consider horizontal scaling from
>day one, in which case you really need to look at databases that
>support horizontal scaling.  There are quite a few.  Most cloud
>vendors offer such services and software, for example.  For example,
>there's Apache's Cassandra, Amazon's DynamoDB, there are NoSQL-type
>databases, ...  Use the right tools for your problem.
>
>Nico
>--
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SQLite DBaaS

[Larry Brasfield]

(Top-posting undone for comprehensibility.)
Ryan Macy wrote:

Would there be any reason SQLite would have a hard time functioning in
a database as a service model?


Roger Binns replied:
[Reasonable explanation of why SQLite as a service is not a good solution,
including "introduction of latency" and "need to ... authenticate", and
suggestion that problem is better solved by existing database servers ]
...

Have you even used SQLite?


To which Ryan Macy replied:

This seems inconsequential -- regardless of what method you use you will
have to authenticate. Everything has latency. I'm not sure where you're
going with this.

You came off as a prick, but maybe I didn't offer enough information... Or
my question is not intelligent enough for youŠ

I appreciate your opinion though, thank you.


Roger's point, which is quite consequential, and politely made, is that
SQLite solves only a part of the problem that you posed.  As for "everything
has latency", that is not quite the issue to which Roger alluded.  As you
may discover, latency becomes a real issue in network scenarios, one that
can create serious difficulties when inadequately handled.  And as you may
already understand, latency is not always an issue that requires attention.

You should learn to keep your emotional reactions to yourself when you find
yourself writing words like "prick".

You provided enough information to show that you have little comprehension
of the issues that will arise were you to use SQLite as you suggest.  Roger
provided a calm, reasonable, but brief hint of what they will be.  That he
took the time to do this is to his credit, and was an act undeserving of
your reaction.

Your question was, in fact, an indication that you are either completely
naive regarding latency and security issues, or that you had very little
familiarity with SQLite.  Hence his question, "Have you even used SQLite?"

To me, Roger's response reflected an effort to help you rather than any
kind of belittlement.  You would do well to understand that, even if you
sometimes feel foolish when those who know more expose your naivety.

Regards,
--
Larry Brasfield
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DBaaS

[Ryan Macy]

>This seems inconsequential

>Huh?  If you are making it available as a service then you have to care
>about authentication.  And identity - how do you tell users apart and keep
>their databases separate?  How will you deal with attacks from malicious
>users?  How will you add a security model to stop people from attaching
>random files, using too much memory or CPU?  What about audit trails so
>that if an account is hacked you can tell what the bad guys did?  And what
>about SQLite's model where there are no per table permissions so a
>connection with access can do anything to any data in database and
>anything else they can attach?
--

I was trying to say that I don't believe this would have anything to do
with SQLite, you would have to deal with these issues regardless of the
solution that is selected. (although users "sharing" a database is out of
the question, so there are some caveats with SQLite functioning in this
manner)

--
>Everything has latency. I'm not sure where you're going with this.

>In the SQLite API you ask for result rows one at a time.  If it takes 25ms
>round trip time to ask for a row then there is no way to get more than 40
>rows per second.

>If you are using a longer distance network as clarified then you'll have
>to batch up result rows but this will present difficulty with transactions
>and locking (do you keep the db open between requests, how do you ensure
>continuing requests hit the same process).

>When using SQLite in process there is essentially no latency as there
>isn't even a context switch.
--


That is a good point, I will need to take that into consideration.
Multiple queries, and queries that all rely on each other, are items I
have been struggling to conceptualize implementation for. It presents an
interesting problem.

--
>You came off as a prick, but maybe I didn't offer enough information...
>Or my question is not intelligent enough for youŠ

>I suggest reading the whole thing, but this is the pertinent part:

>http://catb.org/esr/faqs/smart-questions.html#keepcool



My apologies, I just got put off by the bluntness of your response.



Ryan Macy





On 1/25/12 11:11 PM, "Roger Binns"  wrote:

>-BEGIN PGP SIGNED MESSAGE-
>Hash: SHA1
>
>On 25/01/12 19:52, Ryan Macy wrote:
>> I used API generically (not necessarily SQLites API)
>
>You can't use SQLite's API although it wasn't clear you realised that!
>
>> I am creating an application in python that uses an RESTful API to
>> allow the user connect to my service and submit statements. It will
>> return the result in JSON or XML. I would think I could spawn a new
>> SQLite "instance" for each database the user creates. More or less I
>> wanted to see if anyone else has successfully accomplished this or has
>> pondered the plausibility of this scenario.
>
>If you do that then the database being SQLite is irrelevant.  Any database
>will work.  Unless there is almost no load though you'll want to use a
>server whose operation more clearly matches the usage model.   You should
>note that existing database APIs in web servers have to provide a lot of
>extra functionality and details like connection pooling, timeouts, caching
>etc.
>
>SQLite doesn't work with networked file systems (see the FAQ) so for
>practical purposes you'll only be able to run this on one server.  Not
>much of a service then!
>
>> This seems inconsequential
>
>Huh?  If you are making it available as a service then you have to care
>about authentication.  And identity - how do you tell users apart and keep
>their databases separate?  How will you deal with attacks from malicious
>users?  How will you add a security model to stop people from attaching
>random files, using too much memory or CPU?  What about audit trails so
>that if an account is hacked you can tell what the bad guys did?  And what
>about SQLite's model where there are no per table permissions so a
>connection with access can do anything to any data in database and
>anything else they can attach?
>
>> Everything has latency. I'm not sure where you're going with this.
>
>In the SQLite API you ask for result rows one at a time.  If it takes 25ms
>round trip time to ask for a row then there is no way to get more than 40
>rows per second.
>
>If you are using a longer distance network as clarified then you'll have
>to batch up result rows but this will present difficulty with transactions
>and locking (do you keep the db open between requests, how do you ensure
>continuing requests hit the same process).
>
>When using SQLite in process there is essentially no latency as there
>isn't even a context switch.
>
>> You came off as a prick, but maybe I didn't offer enough information...
>> Or my question is not intelligent enough for youŠ
>
>I suggest reading the whole thing, but this is the pertinent part:
>
>  http://catb.org/esr/faqs/smart-questions.html#keepcool
>
>Roger
>
>
>-BEGIN PGP SIGNATURE-
>Version: GnuPG v1.4.11 (GNU/Linux)
>

Re: [sqlite] SQLite DBaaS

[Nico Williams]

If you're building a small web service, SQLite3 will do fine.  If you
want to scale big you might be able to use SQLite3 for some pieces of
it, but you can't scale up a web service to thousands of servers with
tens of cores and one single SQLite3 DB -- that just doesn't work
given SQLite3's architecture.  The good news is that in web services
you're constantly rewriting the service, and so you'll have an
opportunity to move to a different solution at the right time, and if
using SQLite3 gets you off the ground quickly, then so much the better
for you.

On the other hand, you might want to consider horizontal scaling from
day one, in which case you really need to look at databases that
support horizontal scaling.  There are quite a few.  Most cloud
vendors offer such services and software, for example.  For example,
there's Apache's Cassandra, Amazon's DynamoDB, there are NoSQL-type
databases, ...  Use the right tools for your problem.

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

Re: [sqlite] SQLite DBaaS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/12 19:52, Ryan Macy wrote:
> I used API generically (not necessarily SQLites API)

You can't use SQLite's API although it wasn't clear you realised that!

> I am creating an application in python that uses an RESTful API to
> allow the user connect to my service and submit statements. It will
> return the result in JSON or XML. I would think I could spawn a new
> SQLite "instance" for each database the user creates. More or less I
> wanted to see if anyone else has successfully accomplished this or has
> pondered the plausibility of this scenario.

If you do that then the database being SQLite is irrelevant.  Any database
will work.  Unless there is almost no load though you'll want to use a
server whose operation more clearly matches the usage model.   You should
note that existing database APIs in web servers have to provide a lot of
extra functionality and details like connection pooling, timeouts, caching
etc.

SQLite doesn't work with networked file systems (see the FAQ) so for
practical purposes you'll only be able to run this on one server.  Not
much of a service then!

> This seems inconsequential

Huh?  If you are making it available as a service then you have to care
about authentication.  And identity - how do you tell users apart and keep
their databases separate?  How will you deal with attacks from malicious
users?  How will you add a security model to stop people from attaching
random files, using too much memory or CPU?  What about audit trails so
that if an account is hacked you can tell what the bad guys did?  And what
about SQLite's model where there are no per table permissions so a
connection with access can do anything to any data in database and
anything else they can attach?

> Everything has latency. I'm not sure where you're going with this.

In the SQLite API you ask for result rows one at a time.  If it takes 25ms
round trip time to ask for a row then there is no way to get more than 40
rows per second.

If you are using a longer distance network as clarified then you'll have
to batch up result rows but this will present difficulty with transactions
and locking (do you keep the db open between requests, how do you ensure
continuing requests hit the same process).

When using SQLite in process there is essentially no latency as there
isn't even a context switch.

> You came off as a prick, but maybe I didn't offer enough information...
> Or my question is not intelligent enough for youŠ

I suggest reading the whole thing, but this is the pertinent part:

  http://catb.org/esr/faqs/smart-questions.html#keepcool

Roger


-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8g0nkACgkQmOOfHg372QRe9ACfUrJe987i2E8FDaZHqeKVRS3D
hJoAoNx5bqV9dnbTPk1HsqfnkNiNUBXj
=fhW/
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite DBaaS

[Ryan Macy]

Yes I have used SQLite previously, but not extremely extensively.

I used API generically (not necessarily SQLites API) , the whole idea is a
side project to increase my knowledge. I am creating an application in
python that uses an RESTful API to allow the user connect to my service
and submit statements. It will return the result in JSON or XML. I would
think I could spawn a new SQLite "instance" for each database the user
creates. More or less I wanted to see if anyone else has successfully
accomplished this or has pondered the plausibility of this scenario.


--
The introduction
of latency and the completely different API, the need to identify and
authenticate, authorization etc would make this a very pointless exercise.
There are plenty of existing database servers that meet those needs,
already have remote APIs and ways of overcoming latency etc etc.

Have you even used SQLite?
--



This seems inconsequential -- regardless of what method you use you will
have to authenticate. Everything has latency. I'm not sure where you're
going with this.

You came off as a prick, but maybe I didn't offer enough information... Or
my question is not intelligent enough for youŠ

I appreciate your opinion though, thank you.




On 1/25/12 10:32 PM, "Roger Binns"  wrote:

>-BEGIN PGP SIGNED MESSAGE-
>Hash: SHA1
>
>On 25/01/12 18:48, Ryan Macy wrote:
>> Would there be any reason SQLite would have a hard time functioning in
>> a database as a service model?
>
>SQLite lives in the same process as the code using it.
>
>> Each user would generate a database (or many databases) and connect to
>> it with an API.
>
>The SQLite API is for use in the same process.  If you have to connect to
>a service then it requires a completely different API.  The introduction
>of latency and the completely different API, the need to identify and
>authenticate, authorization etc would make this a very pointless exercise.
> There are plenty of existing database servers that meet those needs,
>already have remote APIs and ways of overcoming latency etc etc.
>
>Have you even used SQLite?
>
>Roger
>-BEGIN PGP SIGNATURE-
>Version: GnuPG v1.4.11 (GNU/Linux)
>
>iEYEARECAAYFAk8gyUYACgkQmOOfHg372QRdaACfbKRwYVMYyybPDxHiiu5oQpMc
>GlgAoLgFa1tgG2fdrIatIw2XT1clmr9V
>=pxXp
>-END PGP SIGNATURE-
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] SQLite DBaaS

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 25/01/12 18:48, Ryan Macy wrote:
> Would there be any reason SQLite would have a hard time functioning in
> a database as a service model?

SQLite lives in the same process as the code using it.

> Each user would generate a database (or many databases) and connect to
> it with an API.

The SQLite API is for use in the same process.  If you have to connect to
a service then it requires a completely different API.  The introduction
of latency and the completely different API, the need to identify and
authenticate, authorization etc would make this a very pointless exercise.
 There are plenty of existing database servers that meet those needs,
already have remote APIs and ways of overcoming latency etc etc.

Have you even used SQLite?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8gyUYACgkQmOOfHg372QRdaACfbKRwYVMYyybPDxHiiu5oQpMc
GlgAoLgFa1tgG2fdrIatIw2XT1clmr9V
=pxXp
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users