[sqlite] sqlite server-process-edition and begin-concurrent

[Marco Bambini]

I am looking for a way to increase concurrency from within cubeSQL.

I am highly interested in a couple of sqlite branches:
-  begin-concurrent seems interesting and updated
-  sqlite server-process-edition branch has been updated on August 2017

Based on documentation seems like that sqlite server-process-edition would be a 
better option for my case but is the branch still maintained and updated?
Are the branches mutually exclusive or can be used both in the same process?
--
Marco Bambini
http://www.sqlabs.com
http://twitter.com/sqlabs



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

Re: [sqlite] SQLite server/file-locking scenario

[Simon Slavin]

On 19 Nov 2013, at 6:40pm, Joshua Grauman  wrote:

> Under what conditions would an open() create a lock that would cause problems?

If you did something with it that would interfere with the processes using 
SQLite's own API.  For instance, if you chose options which involved exclusive 
opening, or locking the file for writing.  But those are unlikely defaults.

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

Re: [sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

Right. I'll go look at the source and confirm or just write it myself 
using POSIX open() which would be easy. I guess I was just asking what 
kind of locks you are talking about since I'm not familiar with file 
locking. In other words, if I did a POSIX open("filename", O_RDONLY); or 
even fopen("filename", "r") under Linux, would I be safe? Under what 
conditions would an open() create a lock that would cause problems?


Josh



On 19 Nov 2013, at 5:05pm, Joshua Grauman  wrote:


Ok, thanks again for the tips, I'll change the ROLLBACK to END.

I'm assuming if I open the file read-only it shouldn't lock, right? I'm using Qt, but I 
send it a read-only parameter which I assume translates into something like a POSIX 
open("filename", O_RDONLY);


I hope a Qt expert can answer this question.  It would be bad of me to assume 
that the default permissions on a Qt open were the same as the default 
permissions on a POSIX open.

Simon.

___
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 server/file-locking scenario

[Simon Slavin]

On 19 Nov 2013, at 5:05pm, Joshua Grauman  wrote:

> Ok, thanks again for the tips, I'll change the ROLLBACK to END.
> 
> I'm assuming if I open the file read-only it shouldn't lock, right? I'm using 
> Qt, but I send it a read-only parameter which I assume translates into 
> something like a POSIX open("filename", O_RDONLY);

I hope a Qt expert can answer this question.  It would be bad of me to assume 
that the default permissions on a Qt open were the same as the default 
permissions on a POSIX open.

Simon.

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

Re: [sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

Ok, thanks again for the tips, I'll change the ROLLBACK to END.

I'm assuming if I open the file read-only it shouldn't lock, right? I'm 
using Qt, but I send it a read-only parameter which I assume translates 
into something like a POSIX open("filename", O_RDONLY);


Josh



On 19 Nov 2013, at 6:00am, Joshua Grauman  wrote:


sqlite3_open_v2("sqlite3.database.filename");
sqlite3_exec("BEGIN IMMEDIATE");
file.open("sqlite3.database.filename");
file.readAll();
file.close();
sqlite3_exec("ROLLBACK");
sqlite3_close();

So does this look like I should always read/write the database properly?


That looks like it should work.  Make sure that your defaults for file.open() 
don't try to lock the file.

I would probably use "END" rather than "ROLLBACK".  SQLite will know 
that you didn't make any changes and will probably execute exactly the 
same code, but I don't like seeing "ROLLBACK" unless something went 
wrong.


Simon.
___
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 server/file-locking scenario

[Simon Slavin]

On 19 Nov 2013, at 6:00am, Joshua Grauman  wrote:

> sqlite3_open_v2("sqlite3.database.filename");
> sqlite3_exec("BEGIN IMMEDIATE");
> file.open("sqlite3.database.filename");
> file.readAll();
> file.close();
> sqlite3_exec("ROLLBACK");
> sqlite3_close();
> 
> So does this look like I should always read/write the database properly?

That looks like it should work.  Make sure that your defaults for file.open() 
don't try to lock the file.

I would probably use "END" rather than "ROLLBACK".  SQLite will know that you 
didn't make any changes and will probably execute exactly the same code, but I 
don't like seeing "ROLLBACK" unless something went wrong.

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

Re: [sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

Thanks again for the responses, very helpful.

Taking into account that I can't just read the database file in my program 
without some sort of locking as was mentioned and explained clearly by 
multiple people (thank you!), I tried two different implementations to see 
which would perform better.


The writing mechanism remained the same in either case. For it I simply 
did (leaving out parameters to simplify):


sqlite3_open_v2();
sqlite3_exec("BEGIN TRANSACTION");
for(int i=0; i

Re: [sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

I don't have time right at this minute to carefully read and respond to 
all the responses to me, but I have to at least at this point say a 
heartfelt *thank you* to all those who have responded to me. I'm blown 
away by how detailed and helpful and patient all the responses are. When I 
have more time in a couple days I'll be reading each response carefully, 
and I'm sure I'll have a followup question or two.


Josh


This is gonna be a long one, but I added (hopefully) some humor to make it
a bit better of a read.

@OP
Just so I'm clear, you're pushing SQL statements from clients to your
server, but then pushing raw data from the server to the client?  Myself,
I'd never push a "Work In Progress" database, or a database that has ANY
file handle open against it, to another machine.  If you're cracking open
the raw SQLite file and reading segments of raw data from it and expecting
that data to exist on a client.. oh boy...  You're in for a world of hurt.

If you're set on sending the raw SQLite database, as suggested, I'd use the
backup API for that database, then present the client with the backed up
copy when then backup is complete.

If, however, pushing 20gb over a traffic congested, 16Mbyte/sec *token
ring*network for a one row update isn't your cup of tea, there are a
few more
options.

Work the database so that when a client updates a particular database, a
time stamp is placed on that row when the last update was done.  A trigger
would do the trick quite easily.  The client would then make a request from
the database to request all data starting from the date/time of last
request.  Client/Server time differences won't be an issue as you'd be
reading the time from the rows and keeping tabs on the date in that new
update field and not on the client system time.

There are pros and cons to this;
- Con: Deleted records would be perhaps a trick.  You wouldn't want to
delete any data as the clients would then never be aware of the delete. So
this has your first serious impact on database size.  You'd also have to
add another field to each table to indicate the state of the row, if it
should be deleted, or if it should be considered valid, but this will put
another impact on database size.  From there you can provide the client
with proper inserts and proper deletes.  Another option is to put the
literal "delete" statements in another table, with appropriate server time
stamp, and when the client makes a request for an update, return the
results ordered on this time stamp.
- Pro: UNDELETE becomes possible with the first CON added.
- Con: If CASCADE deletes are part of the schema, and you rely on deleting
one row to remove several other rows from other tables, it'll be serious
SERIOUS fun to work through that.  If the first CON is done, I suppose a
trigger could be done to flag the state row and just not allow the delete
to happen.  Then, when you actually want to do the database cleanup, you
temporarily turn off the triggers, do your thing, then turn them back on.
- Con: No guarentee that the returned SQL code would be in order of
execution to make things valid on the client side.  Ordering by the time
stamp, even if the millisecond is considered, if two fast inserts happen
but need to happen in a certain order, things could get messed up for the
client.

The second option is to open a socket connection, not for raw data
transmissions, but for sending SQL code from server to client.  On "First
contact" from a client, two things happen.  First, a socket is opened up to
retrieve the raw database your backup API took care of, but no data
transfers happen until a second socket is opened up to listen for SQL
code.  With the two sockets open, you start downloading the raw data on the
first socket, while caching SQL code on the second.  Once the first socket
is done, you attach a database connection to that data, then start pushing
SQL code to the database.

Again, pros and cons;
- Con: You're still dealing with the *token ring* network on first load,
which is going to cause some grief
- Pro: The client only needs to deal with the second socket.  It only ever
has to listen for commands.
- Con: You're still dealing with the *token ring* network .. hell,
that fact doesn't even apply to SQLite.. WHY TOKEN RING MAN!?!?!
- Con: A major revamp of code (Not database) will have to happen on both
the server and client to get the backup and streaming sockets to work and
have the clients aware of what is happening.  You're already using sockets,
so you have the knowledge to get somewhere with this.
- Con: The server is not only going to have to handle the SQL code on
itself, but, it'll also have to hand the code over to 'X' number of clients.
- Con: Establishing which client gets what database might be a bit hard to
do unless you start doing some kind of authentication, which would mean
that the entire database gets put over to the client anyways
- Pro: You'd be using MY IDEA and I wanna see it work when you get 

Re: [sqlite] SQLite server/file-locking scenario

[Stephen Chrzanowski]

This is gonna be a long one, but I added (hopefully) some humor to make it
a bit better of a read.

@OP
Just so I'm clear, you're pushing SQL statements from clients to your
server, but then pushing raw data from the server to the client?  Myself,
I'd never push a "Work In Progress" database, or a database that has ANY
file handle open against it, to another machine.  If you're cracking open
the raw SQLite file and reading segments of raw data from it and expecting
that data to exist on a client.. oh boy...  You're in for a world of hurt.

If you're set on sending the raw SQLite database, as suggested, I'd use the
backup API for that database, then present the client with the backed up
copy when then backup is complete.

If, however, pushing 20gb over a traffic congested, 16Mbyte/sec *token
ring*network for a one row update isn't your cup of tea, there are a
few more
options.

Work the database so that when a client updates a particular database, a
time stamp is placed on that row when the last update was done.  A trigger
would do the trick quite easily.  The client would then make a request from
the database to request all data starting from the date/time of last
request.  Client/Server time differences won't be an issue as you'd be
reading the time from the rows and keeping tabs on the date in that new
update field and not on the client system time.

There are pros and cons to this;
- Con: Deleted records would be perhaps a trick.  You wouldn't want to
delete any data as the clients would then never be aware of the delete. So
this has your first serious impact on database size.  You'd also have to
add another field to each table to indicate the state of the row, if it
should be deleted, or if it should be considered valid, but this will put
another impact on database size.  From there you can provide the client
with proper inserts and proper deletes.  Another option is to put the
literal "delete" statements in another table, with appropriate server time
stamp, and when the client makes a request for an update, return the
results ordered on this time stamp.
- Pro: UNDELETE becomes possible with the first CON added.
- Con: If CASCADE deletes are part of the schema, and you rely on deleting
one row to remove several other rows from other tables, it'll be serious
SERIOUS fun to work through that.  If the first CON is done, I suppose a
trigger could be done to flag the state row and just not allow the delete
to happen.  Then, when you actually want to do the database cleanup, you
temporarily turn off the triggers, do your thing, then turn them back on.
- Con: No guarentee that the returned SQL code would be in order of
execution to make things valid on the client side.  Ordering by the time
stamp, even if the millisecond is considered, if two fast inserts happen
but need to happen in a certain order, things could get messed up for the
client.

The second option is to open a socket connection, not for raw data
transmissions, but for sending SQL code from server to client.  On "First
contact" from a client, two things happen.  First, a socket is opened up to
retrieve the raw database your backup API took care of, but no data
transfers happen until a second socket is opened up to listen for SQL
code.  With the two sockets open, you start downloading the raw data on the
first socket, while caching SQL code on the second.  Once the first socket
is done, you attach a database connection to that data, then start pushing
SQL code to the database.

Again, pros and cons;
- Con: You're still dealing with the *token ring* network on first load,
which is going to cause some grief
- Pro: The client only needs to deal with the second socket.  It only ever
has to listen for commands.
- Con: You're still dealing with the *token ring* network .. hell,
that fact doesn't even apply to SQLite.. WHY TOKEN RING MAN!?!?!
- Con: A major revamp of code (Not database) will have to happen on both
the server and client to get the backup and streaming sockets to work and
have the clients aware of what is happening.  You're already using sockets,
so you have the knowledge to get somewhere with this.
- Con: The server is not only going to have to handle the SQL code on
itself, but, it'll also have to hand the code over to 'X' number of clients.
- Con: Establishing which client gets what database might be a bit hard to
do unless you start doing some kind of authentication, which would mean
that the entire database gets put over to the client anyways
- Pro: You'd be using MY IDEA and I wanna see it work when you get it done
like that. ;)
- Con: You'll have to pay for the plane ticket

A third option would be to treat the data similar to how other major SQL
servers handle the data.  You could create an ODBC driver, or build your
own internal protocols that have the client push information to the server,
then the server does the work against that query, then pushes the results
back in a format understood by the client.
- Pro: No 

Re: [sqlite] SQLite server/file-locking scenario

[Simon Slavin]

On 17 Nov 2013, at 6:17am, Joshua Grauman  wrote:

> Thanks so much for the reply. Sorry for the ignorance, but wouldn't only the 
> sectors (page cache) that are being written need to be cached?

If Unix did that (which it doesn't in any File System I know of) then that 
might be one approach.  But then you need some mechanism so that it keeps old 
pages around until file handles are released and has them all timestamped so it 
knows which ones to serve to which handles.

> And I was trying to read up on how sqlite does atomic writes, but doesn't the 
> way sqlite handles atomic writes guarentee that the file is *always* in a 
> valid state (even from the perspective of other programs that try to read it 
> while being written)?

Imagine the worst possible case: a very long database file and a slow reading 
process.  If you could read the entire database file in an instant, making sure 
your 'read' delayed all other writes, then yes, you would always get an 
uncorrupted copy.  But you can't.  So database file another process may be 
writing various pages to the file while your reading is happening.  And it may 
have the chance to make any number of changes to the file (any number of 
separate transactions) if your reading goes slowly.

So you either need to establish a mutex like you wrote -- perhaps SQLite's own 
locks which are a mutex system -- or to use the SQLite Backup API which will 
continue to re-read the file until it gets a 'clean' copy.

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

Re: [sqlite] SQLite server/file-locking scenario

[Kees Nuyt]

On Sat, 16 Nov 2013 22:17:31 -0800 (PST), Joshua Grauman
 wrote:

>Thanks so much for the reply. Sorry for the ignorance, but wouldn't only 
>the sectors (page cache) that are being written need to be cached? 

Database pages are updated in sqlites page cache, then, being 'dirty',
flushed to the filesystem, which may try to postpone writing to disk and
keep it in the filesystem cache for a while, or write it immediately if
it likes to. Sqlite tries to instruct the filesystem to flush the
filesystem cache to disk at certain moments.

The filesystem image of the database is consistent when there are no
open transactions (everything committed). During  transactions, you have
to assume the filesystem cache is not up to date, may be partially
updated, and is not guaranteed to be consistent.
Only in combination with a journal a consistent version can be
reconstructed.

>And I 
>was trying to read up on how sqlite does atomic writes, but doesn't the 
>way sqlite handles atomic writes guarentee that the file is *always* in a 
>valid state (even from the perspective of other programs that try to read 
>it while being written)?

Not with "PRAGMA synchronous=off;"
http://sqlite.org/pragma.html#pragma_synchronous

To have a consistent image of the database in the filesystem when you
start the copy, you have to make sure the database image in filesystem
is consistent with PRAGMA synchronous=normal; and, like Simon says, lock
the database file with "BEGIN IMMEDIATE" or "BEGIN EXLCUSIVE" to prevent
partioal updates appearing in the image the filesystem has.


>Josh
>
>>
>> On 16 Nov 2013, at 11:37pm, Joshua Grauman  wrote:
>>
>>> Or conversely, that if sqlite has the file open to write, my program 
>>> will read a cached version (if reading and writing happen at the same 
>>> time, I'm fine with the reader getting a slightly stale version). But 
>>> I'm not completely clear on how Linux file locking works... So do I 
>>> need to add a mutex to my program to make sure that a reader doesn't 
>>> get a corrupt database file?
>>
>> Good questions, checking a bad assumption.  There is no such thing as a 
>> 'cached version' of a database file.  Unix doesn't do things like that. 
>> Imagine you had a database file that was 20GB long.  How long do you 
>> think it would take to make a cached version, and where do you think it 
>> would put it ?.
>>
>> So if you're reading a database file without using locking then you're 
>> running the risk of reading some of it before a change and some of it after 
>> the change.  So yes, you need some form of mutex.  Or to use the SQLite 
>> backup API to read the file.  Or to use the normal SQLite API to open the 
>> file read/only and read all the data.
>>
>> Simon.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] SQLite server/file-locking scenario

[RSmith]

Thanks so much for the reply. Sorry for the ignorance, but wouldn't only the sectors (page cache) that are being written need to 
be cached? And I was trying to read up on how sqlite does atomic writes, but doesn't the way sqlite handles atomic writes 
guarentee that the file is *always* in a valid state (even from the perspective of other programs that try to read it while being 
written)?




Do not confuse the atomicity of the "Database" with the state of the "File"... they are in no way the same thing. The database is of 
course quite atomic and quite safe to access whenever and expect to be in a good state if not locked etc. etc. This is achieved 
through one or more files containing data and journals that may very well contain very different things at different times. The 
atomicity of the "file" IO (if that is even a valid thought) is in no way guaranteed or implied or claimed at any point and as Simon 
correctly pointed out, the only way to ensure you are getting a fully up-to-date file is to use the Back-up API.


The Backup API will copy the current complete database ensuring all committed and atomic states and data goes into the new file and 
even provides nice callbacks for progress, cancellation and the like. It is even clever enough to reset and resume the copying 
process if a change occured once it already started backing up, ensuring your copied (target) database file is always fully 
up-to-date, uncorrupted and copy-able (if no other handles to it exist) at the time the backup concluded. This is the only way to 
ensure the *file* is atomic in the way that I think you mean above - and it works rather well. (It's also supported by most wrappers 
in case you are not using the API directly).


Details and examples here:
http://www.sqlite.org/backup.html

Cheers,
Ryan

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

Re: [sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

Thanks so much for the reply. Sorry for the ignorance, but wouldn't only 
the sectors (page cache) that are being written need to be cached? And I 
was trying to read up on how sqlite does atomic writes, but doesn't the 
way sqlite handles atomic writes guarentee that the file is *always* in a 
valid state (even from the perspective of other programs that try to read 
it while being written)?


Josh



On 16 Nov 2013, at 11:37pm, Joshua Grauman  wrote:

Or conversely, that if sqlite has the file open to write, my program 
will read a cached version (if reading and writing happen at the same 
time, I'm fine with the reader getting a slightly stale version). But 
I'm not completely clear on how Linux file locking works... So do I 
need to add a mutex to my program to make sure that a reader doesn't 
get a corrupt database file?


Good questions, checking a bad assumption.  There is no such thing as a 
'cached version' of a database file.  Unix doesn't do things like that. 
Imagine you had a database file that was 20GB long.  How long do you 
think it would take to make a cached version, and where do you think it 
would put it ?.


So if you're reading a database file without using locking then you're running 
the risk of reading some of it before a change and some of it after the change. 
 So yes, you need some form of mutex.  Or to use the SQLite backup API to read 
the file.  Or to use the normal SQLite API to open the file read/only and read 
all the data.

Simon.
___
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 server/file-locking scenario

[Simon Slavin]

On 16 Nov 2013, at 11:37pm, Joshua Grauman  wrote:

> Or conversely, that if sqlite has the file open to write, my program will 
> read a cached version (if reading and writing happen at the same time, I'm 
> fine with the reader getting a slightly stale version). But I'm not 
> completely clear on how Linux file locking works... So do I need to add a 
> mutex to my program to make sure that a reader doesn't get a corrupt database 
> file?

Good questions, checking a bad assumption.  There is no such thing as a 'cached 
version' of a database file.  Unix doesn't do things like that.  Imagine you 
had a database file that was 20GB long.  How long do you think it would take to 
make a cached version, and where do you think it would put it ?.

So if you're reading a database file without using locking then you're running 
the risk of reading some of it before a change and some of it after the change. 
 So yes, you need some form of mutex.  Or to use the SQLite backup API to read 
the file.  Or to use the normal SQLite API to open the file read/only and read 
all the data.

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

[sqlite] SQLite server/file-locking scenario

[Joshua Grauman]

Hello all,

I am writing a server program that reads and writes several different 
sqlite databases. Each client program can do one of the following at a 
time 1) send a file with a bunch of SQL statements that the server will 
run on the appropriate database, or 2) request an entire database file.


Multiple clients will be connecting randomly and trying to read or write 
to a database. There should be roughly about the same number of reads and 
writes, although some more reads.


I have implemented and tested the following scheme:

Retrieving a database file doesn't invoke sqlite at all, but merely opens 
the sqlite database file for reading, reads the file into the socket, and 
then closes the file.


For writing, I open the database, run a "BEGIN TRANSACTION", run the 
individual SQL statements, run a "COMMIT", and then close the database.


I configured the databases with "PRAGMA synchronous = OFF" and "PRAGMA 
journal_mode = MEMORY" to increase speed, and set sqlite3_busy_timeout(db, 
5000); to retry if busy.


In testing, everything seems to work ok. I wrote some sample clients that 
read and wrote as fast as they could to the server at the same time and 
achieved acceptable throughput.


I am concerned however about corner cases, best use, and does things more 
efficiently and quickly if possible.


SO... Here is my main question:

I'm assuming here that (Linux) file caching and sqlite are making sure 
that when I read a file, the database won't be in a 'partially updated and 
invalid state'. In testing, I wasn't able to create an invalid database, 
but I want to be sure that this doesn't happen. So, I'm assuming that if 
the file is opened for read by my program, sqlite won't write to it until 
the file is closed. Or conversely, that if sqlite has the file open to 
write, my program will read a cached version (if reading and writing 
happen at the same time, I'm fine with the reader getting a slightly stale 
version). But I'm not completely clear on how Linux file locking works... 
So do I need to add a mutex to my program to make sure that a reader 
doesn't get a corrupt database file?


Any other suggestions or thoughts? Thanks!

Josh

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

Re: [sqlite] SQLite server using execnet ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/21/2011 12:37 PM, Jay A. Kreibich wrote:
>   Yes, but in something like memcached, the database is not aware of
>   that structure, and can't take advantage of it.

Memcached does understand some types and has atomic increment/decrement etc
but does not understand structure.

>   When storing serialized objects, it is all too common to see code that
>   fetches an object, un-marshals it, alters some simple value, re-marshals
>   the whole object, and then write the whole thing back.

This all circles back to what you are doing and in particular what the OP
was doing.  If you need to do queries, modifications and durability then
what you really need falls under the category of 'database'.  On the other
hand if there is no need for querying or changes then something like
memcached is a great way for a bunch of machines/processes to get the data.

>    Redis 

Redis is a database :-)

>   Yes and no.  Redis, like memcached, is essentially an always in-memory
>   key/value store.

*All* databases are in-memory for practical purposes.  Their working set
will need to be in memory either explicitly due to their implementation, or
implicitly via the operating system or through administration (eg indices).
 If accesses to the working set of data require disk accesses then the
performance will be dismal.  (Some exceptions for data only accessed
sequentially.)

>   Its main selling point is memcached-like speed,

Incidentally MongoDB claims the same thing :-)  The places I use memcached
are where I do not want disk touched.

>... but it is a good fit ...

It looks like we are seeing what happened with the first generation of DVCS.
 Relational representation is being changed to be less
constrained/structured.  There are numerous "databases" with varying and
overlapping sweet spots in terms of querying, persistence, performance,
distribution etc.  I expect we'll see similar shakeouts and end up with a
small number of strong products.,

>   Like SQLite itself, I tend do all my virtual table modules in
>   extremely vanilla C.

Brave :-)  My personal preference is to do the initial development in Python
and then reimplement in C if needed for portability/performance reasons.
The Python development is a lot quicker and then acts as a test suite for
the C implementation.

I wonder how many of the other bindings for SQLite have bothered to
implement virtual tables as that probably holds back usage of virtual tables
a lot.  (A 'hello world' virtual table in Python/APSW is about half a
screenful of code.  An example one I have that represents information about
files on disk is just under a screenful.)

>   I happen to think virtual tables are one of the more powerful features
>   of SQLite, but also one of the most under-utilized features.

Agreed.  Unfortunately it does require that the underlying data be
representable in a relational manner which is also very constraining.

>   Since a big part of writing these is to get them out for other people
>   to use them, 

Where do you publish them?  It is probably also worth trying to encourage a
'contrib' location for SQLite that is more active and in wider use than
http://www.sqlite.org/contrib

>  Working in C avoids adding complexity, like someone working
>  in Java wanting to use your MongoDB module.  I suppose it could be
>  done, but I wouldn't want to be the one trying to make it all work.

MongoDB is client server so this issue does not arise.  (Nor do they have
virtual tables.)  In order to perform "programming" on the server side such
as for map/reduce you have to use Javascript which is slowly becoming the
most popular language for that kind of thing including on the desktop.  (Eg
see node.js and Seed.)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1i/zkACgkQmOOfHg372QT9cgCgyV7NaECzQUrrrDZr9zYri0tq
RkkAoKSuRlclVshN/oIxSXOy0dtXZcot
=xEyA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server using execnet ?

[Petite Abeille]

On Feb 21, 2011, at 9:37 PM, Jay A. Kreibich wrote:

> I was once forced to look at SOAP over SMTP

Ah, yes... double S!

The S stands for Simple
http://wanderingbarque.com/nonintersecting/2006/11/15/the-s-stands-for-simple/


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

Re: [sqlite] SQLite server using execnet ?

[Jay A. Kreibich]

On Sun, Feb 20, 2011 at 08:37:04PM -0800, Roger Binns scratched on the wall:
> On 02/20/2011 06:23 PM, Jay A. Kreibich wrote:
> > On Sun, Feb 20, 2011 at 05:23:09PM -0800, Roger Binns scratched on the wall:
> >> If you want to use SQL then use Postfix.
> > 
> >   I might suggest PostgreSQL instead.
> >   (Sorry, Roger, I couldn't resist.)
> 
> Yeah, long night :-)  However, technically SQL over SMTP is possible and
> would actually work.  And if anyone is insane enough to try that then using
> Postfix and Postgres are a good combination.

  I was once forced to look at SOAP over SMTP.  Thankfully we were able
  to talk them out of it.  As if SOAP wasn't bad enough, I sure didn't
  want to mix it with SMTP.
  
  "Any networking protocol with 'Simple' in the name isn't."

> >> If you need lots of processes on the network to access data quickly then
> >> consider memcached.
> > 
> >   More seriously, in this category you might also consider Redis.
> >   Redis allows your data to have some structure, 
> 
> The Python binding pylibmc does structure the data for you automagically.

  Yes, but in something like memcached, the database is not aware of
  that structure, and can't take advantage of it.
  
  When storing serialized objects, it is all too common to see code that
  fetches an object, un-marshals it, alters some simple value, re-marshals
  the whole object, and then write the whole thing back.

  While this is fine for complex or less frequently accessed objects,
  where the ease of use outweighs the overhead, it seems like a
  lot of work for simpler items, such as lists and basic dictionaries
  or hashes.  Of course, these types of objects are also the most
  frequently modified data-types in many applications.  The best way
  to do key lists in memcached is practically a religious topic.

  Having the database aware of a few very simple and primitive structures 
  can provide significant improvements for these basic, most common
  operations.  For example, Redis can append an element to a list with
  a single O(1) database operation that is independent of list size,
  and the only payload data that crosses the network is the list key
  and the value to insert.  It has a few other tricks, like atomically
  incrementing counter values.

> >   plus it has the ability to persist the data to disk. 
> 
> The moment you talk about persistence you then have significant overlap with
> databases.  My personal favourite is MongoDB but there are loads of others
> such as Cassandra, HBase, Tokyo Cabinet etc.

  Yes and no.  Redis, like memcached, is essentially an always in-memory
  key/value store.  Functionally, it is much more like memcached than
  most of these other examples.  Its main selling point is memcached-like
  speed, without the "cache" aspects of memcached-- you're data stays
  there until you get rid of it, even across restarts.  In many cases,
  that also allows you to get rid of your backing database.  Redis also
  provides just enough internal structure to be useful, without
  really getting in the way, in much the same way that a very basic
  container library provides building-block tools without defining a
  whole class tree.

  Those features definitely bring it closer into the domain of NoSQL
  databases, but I'd argue that's only because memcached is so far
  removed from the rest of these.  Thanks to its caching nature, it
  can't really be considered a "database" at all (useful tool, yes;
  database, not really).


  Each of these products has its place.  I don't mean to sound like
  such a Redis fan-boy, but I've been messing around with it a lot
  lately, and found it to be both extremely simple to setup and
  configure (something most of these other products cannot claim),
  while also extremely useful.  You're not going to use Redis to
  replace something like Cassandra or HBase, but it is a good fit
  for situations where memcached is a good fit, but you want more
  structure and a known life-cycle to your data.

> What programming language are you using to implement the virtual tables?

  Like SQLite itself, I tend do all my virtual table modules in
  extremely vanilla C.
  
  I happen to think virtual tables are one of the more powerful features
  of SQLite, but also one of the most under-utilized features.  As a
  way to relax and explore, I sometimes write virtual table modules to
  bolt together odd data stores or storage formats.  Partly this is
  just for fun, but the process also helps develop a deep understanding
  of the native data model used by these different products, and that
  knowledge is useful for other work I do.  If it opens some eyes and
  helps promote virtual tables, and SQLite in general, that's also a
  great bonus.
 
  As such, much of the virtual table development work I do is somewhat
  isolated, outside of any environment or problem context.  My approach
  tends to be extremely general-purpose, since I'm often approaching
  the problem from a very 

Re: [sqlite] SQLite server using execnet ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/20/2011 06:23 PM, Jay A. Kreibich wrote:
> On Sun, Feb 20, 2011 at 05:23:09PM -0800, Roger Binns scratched on the wall:
>> If you want to use SQL then use Postfix.
> 
>   I might suggest PostgreSQL instead.
>   (Sorry, Roger, I couldn't resist.)

Yeah, long night :-)  However, technically SQL over SMTP is possible and
would actually work.  And if anyone is insane enough to try that then using
Postfix and Postgres are a good combination.

>> If you need lots of processes on the network to access data quickly then
>> consider memcached.
> 
>   More seriously, in this category you might also consider Redis.
>   Redis allows your data to have some structure, 

The Python binding pylibmc does structure the data for you automagically.

>   plus it has the
>   ability to persist the data to disk. 

The moment you talk about persistence you then have significant overlap with
databases.  My personal favourite is MongoDB but there are loads of others
such as Cassandra, HBase, Tokyo Cabinet etc.

I like the ones that don't have a schema the best.

>   I've been playing around with connecting SQLite virtual tables to
>   a Redis server, and it is producing some interesting results.

A while back I implemented a virtual table that talks to CouchDB.  I suspect
you'll have similar issues.  You may find the documentation of interest as
you'll likely encounter similar issues.

  http://apidoc.apsw.googlecode.com/hg/couchdb.html

What programming language are you using to implement the virtual tables?

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1h6+wACgkQmOOfHg372QT/DACfQFOCo/Ku/kHXZGQ0eiXqWDcJ
XQwAnRaBR8/uNgSKNBKXKiG5i/y7G1wm
=1v5a
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server using execnet ?

[Jay A. Kreibich]

On Sun, Feb 20, 2011 at 05:23:09PM -0800, Roger Binns scratched on the wall:

> If you want a close match to the Python object model then use MongoDB.
> 
> If you want to use SQL then use Postfix.

  I might suggest PostgreSQL instead.
  
  Postgres is likely to give you much better results than the
  Postfix SMTP email server.

  (Sorry, Roger, I couldn't resist.)

> If you need lots of processes on the network to access data quickly then
> consider memcached.

  More seriously, in this category you might also consider Redis.
  Redis allows your data to have some structure, plus it has the
  ability to persist the data to disk. 

  I've been playing around with connecting SQLite virtual tables to
  a Redis server, and it is producing some interesting results.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server using execnet ?

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/20/2011 04:55 PM, Stef Mientki wrote:
> still want to see if a simple client/server setup would solve my current 
> problems 

There is no such thing as "simple" client/server.  You have to worry about
issues like authentication and authorization.  You have to deal with naming.
 You have to think about APIs being synchronous or asynchronous.  You have
to worry about state and if state is maintained across connections or
dropped.  You have to worry about new error codes that couldn't occur
before.  You have to deal with race conditions and latency.  Sweeping all
this under the rug will appear simple until you do real deployments and
start painfully encountering and addressing the issues.  That is why
networked databases are not simple.

If the data is not valuable then all that doesn't matter.

> (and I realize that I'm a great optimist)

Indeed :-)

You should look closely at what it is you actually need.

If you want a close match to the Python object model then use MongoDB.

If you want to use SQL then use Postfix.

If you want a stronger binding to SQLite and the ability to operate with and
without a network then consider using SQLite virtual tables with the backend
talking over the network or locally as needed.

If you need lots of processes on the network to access data quickly then
consider memcached.

If you eventually intend to go for Amazon or Google cloud deployments then
look at what they provide to run locally.

If transactions and ACID matter then carefully research what meets your
needs and deploying using as much redundancy and backup as appropriate.

If you can't make your mind up, write a server that provides your data REST
style and make the clients use HTTP.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk1hvnkACgkQmOOfHg372QSFZACfbWZXHwD3+q9xfmfIVAZr9ITO
yHAAn1s3y6w6FV0pW0VPAL1cTfoscB96
=Id/K
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite server using execnet ?

[Stef Mientki]

hello,

knowing that SQllite is not a client/server database,
still want to see if a simple client/server setup would solve my current 
problems for the moment
(because I love the simplicity of SQLlite,
and planned to go to a client / server database in the future)

Now I wonder if anyone has considered  to use Python execnet-module to realize 
a simple SLQlite
client / server application.

If I look at the documentation of execnet,
(and I realize that I'm a great optimist)
it would take between 20 and 50 lines of Python code.

thanks very much for your opinions.
cheers,
Stef Mientki


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

Re: [sqlite] SQLite server

[Olaf Schmidt]

"Doug"  schrieb im
Newsbeitrag news:043201cba22a$f023df40$d06b9d...@poweradmin.com...

> This is where I think the problems will crop up.
> Can you imagine making a
> network round trip for each row fetch, and then for each
> column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and
> bring it back to the client.  And if the response is large, will
> you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)

In this case, not really...
One can perfectly work without the DBConnection
(SQLites DB-Hdl) on the clientside.

Read-Mode:
The only thing the SQLite-API needs in addition to support
an "RPC-AppServer-mode" better (but also worthwhile for
normal, "connected mode", e.g. for back-forward-navigation),
is a new to introduce resultset-container-object (or struct).
Just a "convenience-addition" in analogy to:
  sqlite3_get_table
  sqlite3_free_table
But instead of delivering an Row/Col-array of "sidewards allocated"
UTF8-string-pointers as the 2D-set - it could work in a way
similar to what for example my wrapper implements in its
cRecordset-Class.

I've done (already years ago) my performance-tests for that,
and by far the best (fastest) approach was, to allocate memory
vertically!, meaning in "stripes per column" (respecting the
expected storage-class of the column in question) - and *not*
a recordwise (horizontally) allocated chunk for each row.
For each column I reallocate (growing by a factor of ~1.5,
to avoid reallocs in each and every sqlite-step) usually two
different memory-areas ...:
e.g. for a "double-storage-class-column":
   an ubyte array for Null- and "unexpected storageclass"-Flags
   a double array which holds the non-null values
or for the blobs- and string-storage-class:
   an ubyte array for Null- and "unexpected storageclass"-Flags
   an "UTF8-stream-array", which contains concatenated Field-Values
   a ulong array for the start-offsets within the UTF8-stream
for the integer-storage-class, I assume signed int8 first,
   until the first value appears, which does not fit  into
   signed int8 anymore - in this case an "elevation" of
   the allocated array-area is performed (moving over all
   the previous values in a small cast-loop).
   The stages in this elevation-mechanism are int8, int32, int64 -
   this way especially the boolean-flag-columns (fitting into int8)
   don't take up that much memory.

As mentioned above, there's already a flagging-mechanism
for unexpected storage-class-values, which could either
be "forced" into the expected storage-class (according to
a mapper-set of column-type-descriptions) - or just
converted to string and stored in a "sparse-matrix-area"
of  the resultset, to be able to deliver such values later
on anyways (over a Binary-Lookup on a RowIdx/ColIdx-
key).

So in addition to:
  sqlite3_get_table
  sqlite3_free_table
We could use an accompanying and fast resultset-api as:
  sqlite3_get_resultset
 sqlite3_resultset_get_recordcount
 sqlite3_resultset_get_columncount
 sqlite3_resultset_get_columninfo(hRs, ColIdx, colInfoEnum)
 sqlite3_resultset_get_value(hRs, RowIdx, ColIdx, strgTypeEnum)
  sqlite3_free_resultset

Coming back to the RPC-stuff again:
The idea to have an encapsulation of vertically allocated
memory-stripes per column in such an resultset is not
only fast in normal Desktop-Mode (in delivering a "typed
copy of a Select" - together with a bunch of useful column-
infos, retrievable e.g. over: "Select * From T Where 0"),
the main-advantage of this kind of mem-alloc-scheme
comes into play within additional functions like that:
sqlite3_resultset_serialize(hRs, *pBytes)
sqlite3_resultset_deserialize(*pBytes, *hRs)
Instead of looping over single fields (or records), to build up
a resulting bytestream-serialization, it is now much faster, to loop
over the columnwise allocated chunks and concatenate these
in the resulting serialized byte-stream - and the allocation for
the resulting bytestream can now be done *once* (without
any needed reallocs) since all the column-stripe-sizes are
known beforehand.

As said, all this is not made "out of thin air" - this is
(basically) the implementation I'm working with in my
wrapper-classes in dhRichClient3.dll, which contains
classes for a DCOM-like AppServer-Mode too.
Able to deliver thousands of resultsets per second
over worker-objects, hosted in a threadpool.

What my serializable ResultSet-implementation supports
additionally, is a "CreateTableFromContent" method,
which I use often, to "beam up" a freshly received
serialized resultset-stream directly into a clientside
(usually InMemory-DB-) table.
sqlite3_resultset_deserialize_to_table(*pBytes, DbHdl, tblName)

So, for a reliable (and simple) servermode, Sylvains
suggestion is (from my practical experience) perfect for
SQLite.
Keep the sqlite-api "as is" - 

Re: [sqlite] SQLite server

[Sylvain Pointeau]

I think you can hide those details,
you can perfectly retrieve all rows (or bunch of rows) and then do not make
any network access, but still simulate the sqlite API.

I would keep the connection open, it doesn't block other clients to connect
as well, just the server has to be multi-process.

the idea behind is to keep the existing code and make your application
working with a database located on a server. If you want to go to a real
database server, why not going directly to PostgreSQL?

Best regards,
Sylvain

On Wed, Dec 22, 2010 at 11:52 PM, Doug <pa...@poweradmin.com> wrote:

> This is where I think the problems will crop up.  Can you imagine making a
> network round trip for each row fetch,
> and then for each column fetch of each row (sqlite3_column_type,
> sqlite3_column_double, etc).
>
> To avoid that, you would need to fetch a lot (buffer) of data and bring it
> back to the client.  And if the response is
> large, will you page, or keep the transaction open on the server thus
> blocking all other clients?
>
> The devil is always in the details :)
>
> Doug
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
> Sent: Wednesday, December 22, 2010 7:51 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite server
>
> Why not doing it with DCOM or Corba or what ever even the sockets?
> but hidden behind the same API of SQLite. The "real" sqlite lib will be on
> the server.
> is it called "remote procedure call"?
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Doug]

This is where I think the problems will crop up.  Can you imagine making a
network round trip for each row fetch, 
and then for each column fetch of each row (sqlite3_column_type,
sqlite3_column_double, etc).

To avoid that, you would need to fetch a lot (buffer) of data and bring it
back to the client.  And if the response is
large, will you page, or keep the transaction open on the server thus
blocking all other clients?

The devil is always in the details :)

Doug

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sylvain Pointeau
Sent: Wednesday, December 22, 2010 7:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite server

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The "real" sqlite lib will be on
the server.
is it called "remote procedure call"?
___
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 server

[Sylvain Pointeau]

Why not doing it with DCOM or Corba or what ever even the sockets?
but hidden behind the same API of SQLite. The "real" sqlite lib will be on
the server.
is it called "remote procedure call"?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Simon Slavin]

On 22 Dec 2010, at 2:41pm, Philip Graham Willoughby wrote:

> You would need to reintroduce practically everything you think you can get 
> rid of if you wish to ensure that there is only one process and you are 
> intending to provide a facility for two transactions to be active 
> concurrently.

I agree with your statement but I don't think it's worth doing those things.  
SQLite can still be light, and it's okay for one connection to lock all the 
others out until it is finished.  As long as you support the various types of 
transaction locking (i.e. IMMEDIATE, etc.) and return the appropriate result 
codes, you're okay.

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 22 Dec 2010, at 13:12, Simon Slavin wrote:

> 
> You do mention something worthwhile: if you had a server/client version of 
> SQLite you could get rid of all the code to do with file sharing and locking. 
>  That's quite a lot of code, if you include all the PRAGMAs and related 
> programming that could be ignored.
> 
> You will need some way to keep multiple connections straight -- each one has 
> its own transaction and rollback status.  But you have the advantage that one 
> process knows everything that needs to be known: it can all be handled by one 
> process, that process knows everything it has to know, and it can keep 
> everything it needs in memory.

You would need to reintroduce practically everything you think you can get rid 
of if you wish to ensure that there is only one process and you are intending 
to provide a facility for two transactions to be active concurrently.

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] SQLite server

[jeff archer]

From: Simon Slavin  Date: Tue, 21 Dec 2010 14:19:25 +
>This was my first thought when I considered implementing an SQLite-over-IP 
>protocol: that there was no user model and therefore no need for passwords.  
>Mounting a >database on the server would mean that anyone who knew your IP 
>address, port number and database name could access the data.
>
>So my conclusion was that you would need to implement, in the first case, a 
>simple user privilege model, nominating which users (or which IP addresses) 
>could access >which databases.

In SQLiteServer code the user validation could be handled by a callback into 
the 
application code that is hosting the SQLiteServer amalgamation code.  The 
application code could leave default of NULL and have no user validation or 
supply function and provide appropriate user validation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Simon Slavin]

On 22 Dec 2010, at 12:45am, Jeff Archer wrote:

> A SQLite Server would be userful.  I have considered creating this myself.  I 
> have thought that it would need to be kept zero config and it should be 
> provided 
> in amalgamation form to keep simple to embed in applications.  Wouldn't need 
> incredible throughput as it is a ServerLite.  Would very conveniently avoid 
> the 
> issues of sharing database file across network.

You do mention something worthwhile: if you had a server/client version of 
SQLite you could get rid of all the code to do with file sharing and locking.  
That's quite a lot of code, if you include all the PRAGMAs and related 
programming that could be ignored.

You will need some way to keep multiple connections straight -- each one has 
its own transaction and rollback status.  But you have the advantage that one 
process knows everything that needs to be known: it can all be handled by one 
process, that process knows everything it has to know, and it can keep 
everything it needs in memory.

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

Re: [sqlite] SQLite server

[Jeff Archer]

Just my two cents...

A SQLite Server would be userful.  I have considered creating this myself.  I 
have thought that it would need to be kept zero config and it should be 
provided 
in amalgamation form to keep simple to embed in applications.  Wouldn't need 
incredible throughput as it is a ServerLite.  Would very conveniently avoid the 
issues of sharing database file across network.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Sylvain Pointeau]

and what about using a DCOM like technology to open a distant database?
the sqlite API will stay the same but behind the scene, it will access your
server using a DCOM like technology?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Neville Franks]

Wednesday, December 22, 2010, 1:19:25 AM, you wrote:

SS> On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote:

>> Implementing an SQLite-based server does not obviously enable this in and of 
>> itself. If you could open a database on a remote machine using its filename 
>> as the OP was trying to do it would enable this, but we got into this 
>> discussion when it was discovered that doing so was a bad idea.

SS> This was my first thought when I considered implementing an
SS> SQLite-over-IP protocol: that there was no user model and
SS> therefore no need for passwords.  Mounting a database on the
SS> server would mean that anyone who knew your IP address, port
SS> number and database name could access the data.

SS> So my conclusion was that you would need to implement, in the
SS> first case, a simple user privilege model, nominating which users
SS> (or which IP addresses) could access which databases.

SS> Just to throw into the pot:

SS> It's not that hard to do an extremely simple version of this
SS> using HTTP and either XML or JSON.  Set up a web server with some
SS> PHP or Python pages which take the following parameters:

SS> databasename
SS> command

SS> You call up the right web page with the right values for the
SS> parameters, either as a GET or a PUT.  One web page just executes
SS> the command and returns a result code, result message, etc., in
SS> either XML or JASON, depending on what the programmer likes best. 
SS> A different web page is for executing SELECTs (or some PRAGMAs)
SS> and returns the above plus a table of results.

If you are considering heading down this track I'd suggest seriously
considering using a REST API. I've been doing an evaluation of various
NoSQL Databases recently, mainly with CouchDB and it uses REST quite
effectively. And stick with JSON.

Emulating the WebSQL API may be another option. This is used by
WebKit/Chrome etc. to access SQLite.

And another option could be to use the MySQL tcp/ip interface which I
assume is well documented. I mention this because I am doing some
work with Node.js which has a MySQL interface and am using
XMLHTTPRequest (Ajax) in Javascript in the Browser to access the MySQL
DB on the Web Server via. Node.js.

---
Best regards,
  Neville Franks, http://www.surfulater.com http://blog.surfulater.com
 

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 21 Dec 2010, at 14:19, Simon Slavin wrote:

> 
> On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote:
> 
>> Implementing an SQLite-based server does not obviously enable this in and of 
>> itself. If you could open a database on a remote machine using its filename 
>> as the OP was trying to do it would enable this, but we got into this 
>> discussion when it was discovered that doing so was a bad idea.
> 
> This was my first thought when I considered implementing an SQLite-over-IP 
> protocol: that there was no user model and therefore no need for passwords.  
> Mounting a database on the server would mean that anyone who knew your IP 
> address, port number and database name could access the data.

Worse than that: SQLite will attempt to open any filename you give it as if it 
were a database. I do not know what damage could be done by opening 
non-database files, but I suggest that one could rapidly find out if one 
deployed this.

> So my conclusion was that you would need to implement, in the first case, a 
> simple user privilege model, nominating which users (or which IP addresses) 
> could access which databases.

I recommend you get the web server to authenticate people and use HTTPS, rather 
than trying to handle authentication yourself. You'll still need to handle 
authorisation, but that's the easy bit. Never rely solely on IP security; it is 
very easy to spoof or usurp an IP address.

> Just to throw into the pot:
> 
> It's not that hard to do an extremely simple version of this using HTTP and 
> either XML or JSON.  Set up a web server with some PHP or Python pages which 
> take the following parameters:
> 
> databasename
> command

Like this short script does for MySQL, in fact: 
http://www.bin-co.com/php/scripts/sql2json/ It's probably 20 minutes work at 
most to adapt that to use an SQLite module instead of a MySQL module.

For me, this still does not result in anything near an SQLite server, for the 
same reasons that the above script does not constitute a MySQL server.

To be worth having requires a client embedded in the main SQLite library so 
that I can sqlite3_open remote databases and use ATTACH DATABASE to use any 
combination of local and remote databases as one. Short-lived scripts on a web 
server cannot allow all the API functions to work with a remote database using 
this mechanism: ATTACH_DATABASE, SAVEPOINT, BEGIN TRANSACTION, CREATE TEMPORARY 
*, and most of the C API routines cannot work.

To do it properly you would cut SQLite in two between the SQL Command Processor 
and the Virtual Machine. A long-running program on the remote machine would own 
all the database connections made to that machine, and would appropriately 
authenticate/authorise remote users. ATTACH/sqlite3_open of a remote DB would 
need to perform an sqlite3_open on the remote system and that opened database 
would need to persist until the client used DETACH/sqlite3_close or terminated 
(and for reference, detecting termination is extremely hard). The client would 
need to be sent the remote database's schema so that after that point 
communications from the client are in the form of virtual-machine instructions: 
the client handles the parsing/generation of VDBE programs and the server runs 
them. The user-function-execution engine would need extensions to allow 
user-functions on the client machine to be executed. A globally-installed 
user-function mechanism for the server would also be good. Some kind of 2-phase 
commit protocol would need to be used to co-ordinate transactions across 
multiple machines.

It could be done. But it seems pointless to go to all that effort when 
MySQL/Postgres/DB2/Oracle/MSSQL have the advantages for this problem domain 
that they are designed to do this, have been well tested in this architecture, 
are as simple as they can be, and are fully supported.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] SQLite server

[Petite Abeille]

On Dec 21, 2010, at 2:44 PM, Philip Graham Willoughby wrote:

> It should be significantly easier to define a new storage engine for MySQL 
> that uses the SQLite data format for its tables.

While we are on the subject...

http://dev.mysql.com/tech-resources/articles/dbixmyserver.html

TL;DR: use MySQL client/server protocol to wrap SQLite


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

Re: [sqlite] SQLite server

[Max Vlasov]

On Tue, Dec 21, 2010 at 4:44 PM, Philip Graham Willoughby <
phil.willoug...@strawberrycat.com> wrote:

> On 21 Dec 2010, at 12:16, Max Vlasov wrote:
>
> > Simon, I read both your suggestion and the Richard's good explanation
> about
> > network problems. I think that the idea still deserves to live in some
> form
> > :). I sometimes access sqlite db on a remote computer accessed with
> sqlite
> > shell executed in telnet/ssh. It works and it looks like good design for
> > interaction in terms of network bandwidth. If it can be implemented in
> more
> > friendly way toward the developer, I think it would still be a lighter
> > version of a sql-aware dbms than SQLite/PostgreSQL.
>
> I would like to explore what is meant by 'lighter' in this context, and why
> it is desirable. I have thought about this in the context of what I think it
> might mean; if you mean it to mean something else please let me know.
>
>
Philip, I see your point and unfortunately I can not suggest a similarly
accurate analysis on my side. When I said  "lighter" I seem like partly
meant "more portable". As for client/server sqlite, I'm far from suggesting
anything real, but also far from calling it non-sense :).

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

Re: [sqlite] SQLite server

[Simon Slavin]

On 21 Dec 2010, at 1:44pm, Philip Graham Willoughby wrote:

> Implementing an SQLite-based server does not obviously enable this in and of 
> itself. If you could open a database on a remote machine using its filename 
> as the OP was trying to do it would enable this, but we got into this 
> discussion when it was discovered that doing so was a bad idea.

This was my first thought when I considered implementing an SQLite-over-IP 
protocol: that there was no user model and therefore no need for passwords.  
Mounting a database on the server would mean that anyone who knew your IP 
address, port number and database name could access the data.

So my conclusion was that you would need to implement, in the first case, a 
simple user privilege model, nominating which users (or which IP addresses) 
could access which databases.

Just to throw into the pot:

It's not that hard to do an extremely simple version of this using HTTP and 
either XML or JSON.  Set up a web server with some PHP or Python pages which 
take the following parameters:

databasename
command

You call up the right web page with the right values for the parameters, either 
as a GET or a PUT.  One web page just executes the command and returns a result 
code, result message, etc., in either XML or JASON, depending on what the 
programmer likes best.  A different web page is for executing SELECTs (or some 
PRAGMAs) and returns the above plus a table of results.

On the server set up an authentication database with a table with the following 
columns

IPAddress
database

and have your code check it to see that the requester should have access to 
that database.

Anything else is gravy.  Most of the tricky programming will be doing 
HTMLEntity conversion, and checking for injection exploits.

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 21 Dec 2010, at 12:16, Max Vlasov wrote:

> Simon, I read both your suggestion and the Richard's good explanation about
> network problems. I think that the idea still deserves to live in some form
> :). I sometimes access sqlite db on a remote computer accessed with sqlite
> shell executed in telnet/ssh. It works and it looks like good design for
> interaction in terms of network bandwidth. If it can be implemented in more
> friendly way toward the developer, I think it would still be a lighter
> version of a sql-aware dbms than SQLite/PostgreSQL.

I would like to explore what is meant by 'lighter' in this context, and why it 
is desirable. I have thought about this in the context of what I think it might 
mean; if you mean it to mean something else please let me know.

If it means that the on-disk stored data format (file size) should be as small 
as it is in SQLite today:


It should be significantly easier to define a new storage engine for MySQL that 
uses the SQLite data format for its tables.

If it means that the client binary should be as small as it is in SQLite today:
---

At first glance there is some chance of improving matters. The MySQL client 
library is 3,251,266 bytes vs 752,924 bytes for SQLite on my Linux AMD64 
machine. A cursory analysis suggests that it would be easier and as-effective 
to put the MySQL client on a diet.

I unarchived the MySQL library to see what all the code in it is. Judging by 
the filenames 926,312 bytes is encryption/SSL code and 1,446,920 bytes is 
character-set conversion code. Subtracting the size of the encryption logic and 
all the character set logic except utf8 and ucs2 leaves 987,810 bytes for the 
MySQL client. Of course the connections would not then be secure; this is just 
for the sake of a fairer comparison: the corresponding SSL/encryption code 
would need to be added to any SQLite client library if its connections were to 
be meaningfully secured over a network. Actually a sensible halfway point would 
be to ditch everything but the strongest algorithms (keep AES, SHA-512, RSA and 
drop RC4, MD2, MD4, MD5, DES, BlowFish, TwoFish, etc).

The real difference remaining is therefore about 235k, which I suspect is not 
worth fretting over on any device with a network connection. Building the MySQL 
client with -Os might even be enough to effect that change.

> Another thought, imagine
> that a remote computer contains many sqlite databases and moreover, many
> versions of the same database. It's easy to imagine this like hierarchy of
> sqlite files accessed remotely, but I just can't imagine straightforward way
> to implement this with conventional client/server dbms.

Implementing an SQLite-based server does not obviously enable this in and of 
itself. If you could open a database on a remote machine using its filename as 
the OP was trying to do it would enable this, but we got into this discussion 
when it was discovered that doing so was a bad idea.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

Re: [sqlite] SQLite server

[Gary_Gabriel]

>> Thanks for that, and the analysis that preceded it.  That was my guess,
>> especially point 1, the most significant thing.  
>> Simon, I read both your suggestion and the Richard's good explanation about
>> network problems. I think that the idea still deserves to live in some form
>> :). I sometimes access sqlite db on a remote computer accessed with sqlite
>> shell executed in telnet/ssh. It works and it looks like good design for
>> interaction in terms of network bandwidth. If it can be implemented in more
>> friendly way toward the developer, I think it would still be a lighter
>> version of a sql-aware dbms than SQLite/PostgreSQL. Another thought, imagine
>> that a remote computer contains many sqlite databases and moreover, many
>> versions of the same database. It's easy to imagine this like hierarchy of
>> sqlite files accessed remotely, but I just can't imagine straightforward way
>> to implement this with conventional client/server dbms.
>> 
I agree with this concept and can place test results in the discussion. 
Profiling the application puts a sharper edge on the discussion. 
Consider the viewpoint of a small, flexible, highly skilled team 
dependent on prompt response. Assume that the communication profile 
tends to concentrate the information resources locally at each desktop. 
However team success depends on the ability to exchange focused 
information within a short response time. Further assume that much of 
the success depends on an accurate analysis of the information provided 
from the team members. Distribution of needed assets tends to be 
relatively even throughout the group without repetition..

Internet Messaging does not place information resources centrally; but 
enables a timely exchange between team members of filtered, focused 
assets. I have been testing SQLite under these requirements for the last 
two years with satisfying results. The tests confirm results reported in 
the list. The following article presents the Proof of Concept, and for 
the interested further information on implementation.

Progressing farther could employ concepts such as attaching to minimize 
needs of a client-server architecture while still fulfilling the 
profiled team needs. Team members could query local resources and 
prevent the problems of the db engine given in the previous example. 
Delving into the model as the article suggests shows that this 
architecture is scalable and does not rely on a network server. This 
model moves away from dependence on the network to dependence on 
intelligence to query local resources and share the results. I think 
this model has the adaptability to offer potential to the suppositions 
made by Max.

Search Session Threading based on Spiral Modeling for Internet 
Messaging  Scribd article: 

 


I've gotten a lot of good ideas from this friendly and helpful list. - 
Gary Gabriel





Internet Messaging has been in a test phase for the last two years using 
a small network with SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Max Vlasov]

On Tue, Dec 21, 2010 at 2:24 PM, Simon Slavin  wrote:

>
> Thanks for that, and the analysis that preceded it.  That was my guess,
> especially point 1, the most significant thing.  It's tough to find a decent
> extension for SQLite that enough people want.  The two extensions I see most
> referred to are FTS3 (to provide the full-text search) and rtrees (to define
> distance in terms of two coordinates).
>
>

Simon, I read both your suggestion and the Richard's good explanation about
network problems. I think that the idea still deserves to live in some form
:). I sometimes access sqlite db on a remote computer accessed with sqlite
shell executed in telnet/ssh. It works and it looks like good design for
interaction in terms of network bandwidth. If it can be implemented in more
friendly way toward the developer, I think it would still be a lighter
version of a sql-aware dbms than SQLite/PostgreSQL. Another thought, imagine
that a remote computer contains many sqlite databases and moreover, many
versions of the same database. It's easy to imagine this like hierarchy of
sqlite files accessed remotely, but I just can't imagine straightforward way
to implement this with conventional client/server dbms.

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

Re: [sqlite] SQLite server

[Simon Slavin]

On 21 Dec 2010, at 9:24am, Philip Graham Willoughby wrote:

> My hunch is that if this were done the result would be

Point 1:

> something no-one wants

Point 2:

> : not much smaller than Postgres/MySQL/etc, as complex to use as 
> Postgres/MySQL/etc (more so as most stuff you want to achieve with those can 
> be done with google and copy/paste), and probably significantly slower.

Thanks for that, and the analysis that preceded it.  That was my guess, 
especially point 1, the most significant thing.  It's tough to find a decent 
extension for SQLite that enough people want.  The two extensions I see most 
referred to are FTS3 (to provide the full-text search) and rtrees (to define 
distance in terms of two coordinates).

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

Re: [sqlite] SQLite server

[Philip Graham Willoughby]

On 20 Dec 2010, at 22:18, Simon Slavin wrote:

> 
> On 20 Dec 2010, at 9:02pm, Richard Hipp wrote:
> 
>> (2) Create your own custom mini-SQL-server using SQLite and your own
>> protocol for your applications to talk to that min-server over the network.
> 
> In your opinion (or in the opinion of anyone else intimately familiar with 
> SQLite), is SQLite suited to this ?  It could be done as a standard package, 
> provided and supported by the same team that supports standard SQLite and 
> provided in the same amalgamated form.  The procedure would presumably be 
> something like:
> 
> 1) Pick a pair of ports for the protocol and start the process of getting the 
> IETF to recognise them.
> 2) Devise a protocol to encode all the functions in 
>  so they can be used over IP.
> 3) Do the programming for the server and client.
> 
> Does the team think it's worth doing this ?  Or will the vast majority of 
> people who need networked SQL also need a bunch of other features like user 
> privilages which mean you might as well just use one of the existing 'big' 
> SQLs ?

I'm not "intimately familiar with SQLite" but I do have a lot of experience in 
this kind of work: I was employed by IBM as part of the WebSphere MQ (aka 
MQSeries) development team for 7 years before I set up my own company.

The bulk of the code and almost all of the complexity in such systems is to 
deal with the problem that the data is managed by/in one address-space and 
needs to be used by/in another address-space. User authentication/authorisation 
and I suspect the rest of the 'bunch of other features' are not a big deal 
compared to the pervasive structural differences enforced by dealing with this 
problem (as compared to deliberately not dealing with it). SQLite is 
deliberately designed not to try and solve it. I imagine you would meet a lot 
of resistance if you tried to insert that complexity into SQLite when it has 
been so deliberately avoided: I certainly wouldn't want it slowing down my apps.

Inserting a network between the two address-spaces doesn't change much code in 
the short term, but it does change the balance of where you put processing: try 
to send the minimum data in the minimum number of conversations across the 
network.

Notes on your procedure:

1) You only need to specify the server port; not sure why you think you need 
another. It is hard but not terribly hard to get a recognised port; it helps if 
you ask IANA and not the IETF, as they own the list. You should also question 
whether you require a fixed port as doing so precludes running more than one 
instance per IP address. I would argue that it is better today to use 
unpredictable port numbers and dns-sd to identify where the instance you want 
is available.

2) Protocol design is difficult to do at all and extremely difficult to do 
well. For example, how would you implement sqlite3_create_function-installed 
user functions in such a way that transactions are never blocked for ludicrous 
amounts of time waiting for results. There are other more difficult problems 
but this one is the easiest difficult one to understand.

3) This is the easy bit.

My hunch is that if this were done the result would be something no-one wants: 
not much smaller than Postgres/MySQL/etc, as complex to use as 
Postgres/MySQL/etc (more so as most stuff you want to achieve with those can be 
done with google and copy/paste), and probably significantly slower.

Best Regards,

Phil Willoughby
-- 
Managing Director, StrawberryCat Limited

StrawberryCat Limited is registered in England and Wales with Company No. 
7234809.

The registered office address of StrawberryCat Limited is:

107 Morgan Le Fay Drive
Eastleigh
SO53 4JH

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

[sqlite] SQLite server

[Simon Slavin]

On 20 Dec 2010, at 9:02pm, Richard Hipp wrote:

> (2) Create your own custom mini-SQL-server using SQLite and your own
> protocol for your applications to talk to that min-server over the network.

In your opinion (or in the opinion of anyone else intimately familiar with 
SQLite), is SQLite suited to this ?  It could be done as a standard package, 
provided and supported by the same team that supports standard SQLite and 
provided in the same amalgamated form.  The procedure would presumably be 
something like:

1) Pick a pair of ports for the protocol and start the process of getting the 
IETF to recognise them.
2) Devise a protocol to encode all the functions in 
 so they can be used over IP.
3) Do the programming for the server and client.

Does the team think it's worth doing this ?  Or will the vast majority of 
people who need networked SQL also need a bunch of other features like user 
privilages which mean you might as well just use one of the existing 'big' SQLs 
?

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Mon, 26 Oct 2009 15:10:05 -0400, Reid Thompson
 wrote:
>may be of interest  
>http://sqlrelay.sourceforge.net/

Thanks for the link. Unfortunately, it doesn't seem to support Windows
yet.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Reid Thompson]

On Sat, 2009-10-24 at 16:33 +0200, Gilles Ganault wrote:
> On Sat, 24 Oct 2009 00:38:20 +0200, "Olaf Schmidt"
>  wrote:
> >Hmm in this case, what about:
> >http://www.realsoftware.com/realsqlserver/
> 
> Thanks much for the tip :) I'll give it a shot.
> 
> >So, on what platform(s) do your client-apps need to work?
> >In what (main)language do you develop your client-app?
> 
> Either Python or Delphi. Hopefully, they have connectors for those
> languages.
> 
> Thanks again.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

may be of interest  
http://sqlrelay.sourceforge.net/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Sun, 25 Oct 2009 02:57:52 -0700 (PDT), George Black
 wrote:
>did you try www.SQLitening.com

David Morris just sent a pointer above. Thanks much for the info.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Sat, 24 Oct 2009 17:37:31 -0700 (PDT), David Morris
 wrote:
>Have a peek at : http://www.sqlitening.com/support/index.php
>Different language and could run under Wine on *nix

Thanks for the pointer. I didn't know SQLitening.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Olaf Schmidt]

"David Morris"  schrieb im
Newsbeitrag news:26044356.p...@talk.nabble.com...
>
> Have a peek at : http://www.sqlitening.com/support/index.php
> Different language and could run under Wine on *nix

Ah - yes of course, I left it out here, since it currently is "win only"
(written in PowerBasic). Already played around with it some months
ago - not bad. And IIRC, there was also a C-port planned regarding
the sources - which would allow to compile and run it natively
on more target-platforms.

But yes, the Wine-Layer runs very well these days, especially
if only service-like apps are hosted (with a minimal GUI). The
translation of the winsock-api into the *nix-socket-stack works
very reliable over long uptime-periods, as well as the mappings
and translations into the *nix-filesystems (and all that with much
better performance, compared with VM-based approaches ...
which would also require appropriate Win-OS-licenses per VM).

So, hosting such "IO-related services", originally developed for
win (mainly because "also the clientside has to run on that platform")
on *nix-servers (with a little help of the wine-layer) is now possible
without much hazzle - we do it this way for a few years now,
running our win-compiled service-binaries/layers on debian/wine-
machines.

Olaf




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

Re: [sqlite] SQLite server for Windows/Linux?

[George Black]

did you try www.SQLitening.com


Gilles Ganault-3 wrote:
> 
> Hello
> 
> I know that SQLite is meant as a server-less, file server-based
> solution, but... since Richard Hipp has also written a single-EXE SCM
> solution (www.fossil-scm.org), I wanted to check if someone had
> recently written a similar solution so that SQLite can be used as a
> light, no-brainer DB server?
> 
> SQLite is such a pleasure to use in single-mode that it's too bad  it
> can't be used for moderate use, ie. to share data among 3-4 clients.
> 
> Thank you.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/SQLite-server-for-Windows-Linux--tp26023747p26046463.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite server for Windows/Linux?

[David Morris]

Have a peek at : http://www.sqlitening.com/support/index.php
Different language and could run under Wine on *nix


Olaf Schmidt wrote:
> 
> 
> "Gilles Ganault"  schrieb im
> Newsbeitrag news:10h3e55s5ml6kto9rkvasvv1ejc0ao2...@4ax.com...
> 
>> Maybe someone will come up with a commercial alternative.
>> I for one would gladly pay for a single-EXE, no-brainer solution
>> that can run on Windows and *nix.
> 
> Hmm in this case, what about:
> http://www.realsoftware.com/realsqlserver/
> 
> But then it depends on your used (preferred) "client-access-
> technology" (how easy you can bind it into your client-app).
> 
> On the above site they state, that its usage from the clientside is
> possible either over a C/C++ SDK - or directly over RealBasic-
> SQLite-bindings (dunno if they also deliver an ODBC-driver for
> their Server, to access it over a "standard-interface" from other
> languages too).
> 
> So, on what platform(s) do your client-apps need to work?
> In what (main)language do you develop your client-app?
> 
> Olaf
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/SQLite-server-for-Windows-Linux--tp26023747p26044356.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Sat, 24 Oct 2009 00:38:20 +0200, "Olaf Schmidt"
 wrote:
>Hmm in this case, what about:
>http://www.realsoftware.com/realsqlserver/

Thanks much for the tip :) I'll give it a shot.

>So, on what platform(s) do your client-apps need to work?
>In what (main)language do you develop your client-app?

Either Python or Delphi. Hopefully, they have connectors for those
languages.

Thanks again.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Olaf Schmidt]

"Gilles Ganault"  schrieb im
Newsbeitrag news:10h3e55s5ml6kto9rkvasvv1ejc0ao2...@4ax.com...

> Maybe someone will come up with a commercial alternative.
> I for one would gladly pay for a single-EXE, no-brainer solution
> that can run on Windows and *nix.

Hmm in this case, what about:
http://www.realsoftware.com/realsqlserver/

But then it depends on your used (preferred) "client-access-
technology" (how easy you can bind it into your client-app).

On the above site they state, that its usage from the clientside is
possible either over a C/C++ SDK - or directly over RealBasic-
SQLite-bindings (dunno if they also deliver an ODBC-driver for
their Server, to access it over a "standard-interface" from other
languages too).

So, on what platform(s) do your client-apps need to work?
In what (main)language do you develop your client-app?

Olaf



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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

Thanks for the links

On Fri, 23 Oct 2009 08:48:53 -0400, "Griggs, Donald"
 wrote:
>I know almost nothing about them, but the following may possibly be of
>interest: 
>
>http://sqlrelay.sourceforge.net/

This one looks quite a bit big for my needs :-/

>http://www.it77.de/sqlite/sqlite.htm

"28.05.2004". As it is for professional applications, I need something
supported and under development

>http://www.sqlite.org/cvstrac/wiki?p=ClientServer
"06.12.2004".

Maybe someone will come up with a commercial alternative. I for one
would gladly pay for a single-EXE, no-brainer solution that can run on
Windows and *nix.

Thanks.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Griggs, Donald]

Gilles,

I know almost nothing about them, but the following may possibly be of
interest: 

http://sqlrelay.sourceforge.net/

http://www.it77.de/sqlite/sqlite.htm

http://www.sqlite.org/cvstrac/wiki?p=ClientServer

Donald

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

Re: [sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

On Fri, 23 Oct 2009 03:25:45 -0700, Roger Binns
 wrote:
>The problem is that the moment you have a network accessible service you
>have to deal with administration.  Things like authentication, security,
>log files etc.

In the absolute, yes, but not if SQLite is to be used in a firewalled
LAN just to share data between a couple of hosts. Currently, the only
way to do this is to share the file with CIFS/Samba, while a simple
EXE could handle concurrency without the headaches of CIFS.

>If you are looking for an alternative then I'd suggest couchdb.  It is
>designed for replication from the ground up and you get offline mode for
>free.  It is document oriented rather than relation oriented like SQL
>but for many that is an advantage :-)

Thanks for the tip, but CouchDB is not an SQL server:
http://en.wikipedia.org/wiki/CouchDB

I for one would be very willing to pay for an SQLite server that would
run on Windows and Linux/FreeBSD.

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

Re: [sqlite] SQLite server for Windows/Linux?

[Roger Binns]

Gilles Ganault wrote:
> I wanted to check if someone had
> recently written a similar solution so that SQLite can be used as a
> light, no-brainer DB server?

The problem is that the moment you have a network accessible service you
have to deal with administration.  Things like authentication, security,
log files etc.  Then you have to specify a protocol of some sort and as
you update it over time have to deal with client and server versioning.
 Some people have made networkable variants of SQLite and you'll have
all these issues with them.  At that point you may as well use MySQL,
Postgres or plain ODBC and not worry about the backend.

> SQLite is such a pleasure to use in single-mode that it's too bad  it
> can't be used for moderate use, ie. to share data among 3-4 clients.

Just to be clear SQLite can trivially handle that sort of workload.  It
just can't be done reliably using a network share due to bugs in the
protocols and implementations of network file sharing.

If you are looking for an alternative then I'd suggest couchdb.  It is
designed for replication from the ground up and you get offline mode for
free.  It is document oriented rather than relation oriented like SQL
but for many that is an advantage :-)

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

[sqlite] SQLite server for Windows/Linux?

[Gilles Ganault]

Hello

I know that SQLite is meant as a server-less, file server-based
solution, but... since Richard Hipp has also written a single-EXE SCM
solution (www.fossil-scm.org), I wanted to check if someone had
recently written a similar solution so that SQLite can be used as a
light, no-brainer DB server?

SQLite is such a pleasure to use in single-mode that it's too bad  it
can't be used for moderate use, ie. to share data among 3-4 clients.

Thank you.

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

Re: [sqlite] Sqlite Server

[John Stanton]

There is no Sqlite Server unless you use a third party product.  Sqlite 
is a library which links into your application.


noname wrote:

I am using SQL Server as a back end in my vb6 application i want to switch
over to sqlite server but terrainformatica.com site has not provided rates
for server. so i am confused that should i use it or not i enquired on sites
also but i am getting proper answer from them. My application process huge
data of hospital. please suggest me that what i should do may i can waint
for SQlite server or suggest me other server like SQLite server on which
Developer licence are available. My client should not need to purchase back
end. 



arvind parmar 
[EMAIL PROTECTED]





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite Server

[Olaf Schmidt]

You can use our free XCopy-deployable dhRPCServer for this purpose 
(using our dhSQLite-COM-Wrapper at the server- and the clientside).

Both components are designed, to work together as
optimal as possible. The App-Server has a (runtime-)
adjustable ThreadPool and can serve up to 1 COM-
Requests per second (in case of a simple String-Reflection).

Regarding (Read/Write) SQLite-Access the COM-based 
dhSQLite-Wrapper is used on both, the server- and the 
clientside.
An SQLite-Request then goes as follows:
After establishing a connection to the AppServer, the
client sends an RPC-COM-Request to a serverside DLL.

'gets the serialized content of a serverside Recordset
strSQL = "Select * from Table"
ByteArray = RPCConn.DoRPC("DBServer.dll", "classSQLite", "GetRecordset", _
  TimeOutSeconds, strSQL)
Set ClientRs = New dhSQLite.cRecordset
ClientRs.Content = ByteArray

Now you can use this disonnected clientside Recordset in your
Application and add new records, delete records, change records,
or sort records on it. 
If you want to save your changes from this recordset on the
serverside you will have to do the following:
ByteArray = ClientRs.ContentChangesOnly
RPCConn.DoRPC "DBServer.dll", "classSQLite", "UpdateBatch", _
  TimeOutSeconds, ByteArray

At the serverside (in DBServer.dll, classSQLite, method UpdateBatch):
Set Conn = GetSQLiteConnectionFromPool()
Set Rs = New dhSQLite.cRecordset
Set Rs.ActiveConnection = Conn
Rs.Content = ReceivedByteArray
Rs.UpdateBatch

Due to the fast serialization/deserialization-routines in our SQLite-
Wrapper (and of course the fast performing SQLite-engine itself),
we can reach up to 500 "midrange-SQLite-Selects" per second 
(encrypted and compressed) from concurrent clients (server running 
on a cheap Athlon-X2-processor) and can easily drive a 100MBit-LAN 
into its limits.
 
Try it out, there is a Binary Demo on our site, containing
the server-part (including the sqlite-engine and our wrapper)
and a DB-Demo-Client, wich can be used for MultiUser-Stress-
Tests. There's no setup needed, neither for the serverpart,
nor for the client-demo - all is working with registryless COM-
components "out of the box", leaving no traces in your Win-registry.

In a second ZIP you will find VB6-Sources for both parts 
(Client- and Server-Part).
http://www.thecommon.net/8.html


Olaf Schmidt


noname wrote:
> 
> I am using SQL Server as a back end in my vb6 application i want to switch
> over to sqlite server but terrainformatica.com site has not provided rates
> for server. so i am confused that should i use it or not i enquired on
> sites also but i am getting proper answer from them. My application
> process huge data of hospital. please suggest me that what i should do may
> i can waint for SQlite server or suggest me other server like SQLite
> server on which Developer licence are available. My client should not need
> to purchase back end. 
> 
> 
> arvind parmar 
> [EMAIL PROTECTED]
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Sqlite-Server-tf3823853.html#a10825872
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Sqlite Server

[P Kishor]

Seems like you haven't even bothered to look at the SQLite website.
Here are a few hints --

- There is no such thing as a "SQLite server"
- There is no such thing as a "SQLite server Developer licence"

Rest of the answers are readily available on www.sqlite.org. You
should spend about 30 mins looking at the different parts of the
website and learning about SQLite. That should be enough to tell you
everything you want to know.

You should definitely NOT make a decision for your client based only
on replies to your question on a mailing list.


On 5/27/07, noname <[EMAIL PROTECTED]> wrote:


I am using SQL Server as a back end in my vb6 application i want to switch
over to sqlite server but terrainformatica.com site has not provided rates
for server. so i am confused that should i use it or not i enquired on sites
also but i am getting proper answer from them. My application process huge
data of hospital. please suggest me that what i should do may i can waint
for SQlite server or suggest me other server like SQLite server on which
Developer licence are available. My client should not need to purchase back
end.


arvind parmar
[EMAIL PROTECTED]

--
View this message in context: 
http://www.nabble.com/Sqlite-Server-tf3823853.html#a10825295
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sqlite Server

[noname]

I am using SQL Server as a back end in my vb6 application i want to switch
over to sqlite server but terrainformatica.com site has not provided rates
for server. so i am confused that should i use it or not i enquired on sites
also but i am not getting proper answer from them. My application process
huge data of hospital. please suggest me that what i should do may i can
waint for SQlite server or suggest me other server like SQLite server on
which Developer licence are available. My client should not need to purchase
back end. 


arvind parmar 
[EMAIL PROTECTED]

-- 
View this message in context: 
http://www.nabble.com/Sqlite-Server-tf3823854.html#a10825296
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Sqlite Server

[noname]

I am using SQL Server as a back end in my vb6 application i want to switch
over to sqlite server but terrainformatica.com site has not provided rates
for server. so i am confused that should i use it or not i enquired on sites
also but i am getting proper answer from them. My application process huge
data of hospital. please suggest me that what i should do may i can waint
for SQlite server or suggest me other server like SQLite server on which
Developer licence are available. My client should not need to purchase back
end. 


arvind parmar 
[EMAIL PROTECTED]

-- 
View this message in context: 
http://www.nabble.com/Sqlite-Server-tf3823853.html#a10825295
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite server

[Dave Dyer]

I've revived the sqlite server demo at http://www.it77.de/sqlite/sqlite.htm
and updated the API to sqlite 3.   

This project defines a very simple sqlite server which operates local 
sqlite databases, and a simple client which links with a lightweight
library containing a plug-compatible subset of the full sqlite C api.

Essentially, if you have a simple enough program which uses sqlite,
you can link with this library instead and connect to a remote sqlite
database.

There are many reasons why this simple client/server pair is ONLY
a demo, and not suitable to be deployed outside of your sandbox;
but there are also plenty of reasons why a networked sqlite might
be preferable to a standard networked database such as mysl.

-- OK, that's the announcement, now for the question.  I'm planning
to make this demo available, but if a show of hands indicates enough
immediate interest, this could be set up at sourceforge or elsewhere
as a real project.   Please send ddyer-sqlite at real-me.net a message
(no need to flood the mailing list) if you (a) would like a copy to play
with (b) would be interested in participating in an open source
project to make a deployable network database.  


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite server

[Jolan Luff]

On Wed, Jun 15, 2005 at 10:47:00AM -0700, [EMAIL PROTECTED] wrote:
> It would be nice to have a little server and client library which provided
> access to sqlite databases.  I wrote one for the purpose of serving
> databases, and left the interface open so it could easily be used for
> serving sqlite databases.
> 
> When the client connects (using telnet or the client library), then
> opens a database, the server does not close the database until a timeout
> occurs.  This allows many clients to use databases concurrently, without
> any opens or closes except the initial open and the close after timeout.
> 
> The server is open-source, and available at:
>   http://voydb.sourceforge.net/
>   http://sourceforge.net/projects/voydb/ has the source available.
> 
> I have a newer version which doesn't use Berkeley, but uses an in-memory
> hash table (which it writes to the disk every once in a while).
> 
> Anyhow, what do you think about the idea?

have a look at http://sqlrelay.sourceforge.net/

[sqlite] sqlite server

[brandonlite]

It would be nice to have a little server and client library which provided
access to sqlite databases.  I wrote one for the purpose of serving
databases, and left the interface open so it could easily be used for
serving sqlite databases.

When the client connects (using telnet or the client library), then
opens a database, the server does not close the database until a timeout
occurs.  This allows many clients to use databases concurrently, without
any opens or closes except the initial open and the close after timeout.

The server is open-source, and available at:
  http://voydb.sourceforge.net/
  http://sourceforge.net/projects/voydb/ has the source available.

I have a newer version which doesn't use Berkeley, but uses an in-memory
hash table (which it writes to the disk every once in a while).

Anyhow, what do you think about the idea?

Brandon