Re: [sqlite] Writing double into a socket file
Hi John, Re. Javascript being slow you may be interested in EJScript which is an Embedded Javascript implementation with a Native Code Compiler. See: http://www.ejscript.org/products/ejs/doc/guide/ejs/language/overview.html and http://www.ejscript.org I have not (yet) used it so can't comment further. I also use JSON with SQLite and have C/C++ code to parse and build a tree (DOM) from JSON. Along with some simple JSON Path style lookup. And re. Sockets and doubles. I've written a layer that basically sends/receives variants using sockets. If you are familiar with Boost::Any it is like that, however I use different implementation. Wednesday, June 11, 2008, 10:48:31 AM, you wrote: JS> We use an application server I wrote which handles HTTP, serves file and JS> has embedded Sqlite for the RPCs. The RPC can deliver its result either JS> in XML for widely distributed applications or as JSON if it is JS> responding to a WWW browser in AJAX mode. JS> We keep a local library of SQL RPCs so that SQl never appears on the JS> network and we have immunity from injection attacks. It also means that JS> we can cache compiled SQL, a useful performance win. JS> We use the Expat parser in remote programs using the XML format. A JS> wrapper makes it a verifying parser to ensure well formed XML. JS> The server is multi threaded and maintains a pool of live threads so it JS> can respond quickly and assign multiple threads to one browser JS> connection. Shared cache in Sqlite and some extra caching to maintain JS> multiple open databases and results makes Sqlite behave like a simple to JS> use enterprise DB server, but without the overhead of extra processes. JS> We use mutexes for synchronization, set up as read and write locks and JS> avoid the POSIX file locks. JS> We installed Javascript as a procedural language to be used by Sqlite JS> instead of PL/SQL but that is not a great success (v. slow) and we are JS> going to experiment with using Python. JS> Based on our experience you should be very happy with your Sqlite based JS> RPC capability. JS> Alex Katebi wrote: >> John & John, >> >>Actually my API used to be XML using SCEW a DOM like XML parser that uses >> Expat. >> >>For my particular application RPC made more sense to me. What could be >> easier than a function call? Another advantage was that I did not have to >> create any functions. I am just using SQLite's C API. Now the users of my >> application can query any table on the server side using select. Since my >> application is a network server, and network debugging capability is >> crucial. >> The only ugliness is that select locks the tables. I wish D. Hipp would give >> us an option for pStmt to create a temporary table of the select result set >> and delete that temp table after finalize automatically. This way a client >> can sit on a prepare/step for a long time. >> >>I solved the endian issue pretty easy by sending the type code. >> >> Thanks, >> -Alex >> >> >> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> >> wrote: >> >>> Alex Katebi wrote: Yes I need to do it as 8 byte buffer. Convert the endianess to the >>> network then back to host for 8 byte integer. I think XML is great for command validation and CLI auto typing, help >>> etc. Besides parsing issue, XML can not handle binary data directly. >>> As John pointed out, XML is not intended to handle binary data >>> directly. We use XML as a transfer medium for binary data and simply >>> base64 encode it before encapsulation. >>> >>> >>> John Elrick >>> Fenestra Technologies >>> ___ >>> 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 JS> ___ JS> sqlite-users mailing list JS> sqlite-users@sqlite.org JS> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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] Writing double into a socket file
Mutliple threads in one process let me perform load balancing and share the Sqlite cache. Cache sharing can be a big win. Alex Katebi wrote: > Hi John, > > Yes the negative with the single thread is single CPU utilization. Then > again you can run 4 or more servers for a quad CPU. For security I could > either use SSH Port Forwarding or use a MD5 implementation in my > client/server code. > > Thanks, > -Alex > > > > On Tue, Jun 10, 2008 at 11:25 PM, John Stanton <[EMAIL PROTECTED]> wrote: > >> Alex, >> >> Thankyou for the comments. I use non-blocking fd's also but implement >> multiple threads to take advantage of multiple processor servers. Since >> threads carry quite a bit of baggage your single thread approach would >> probably be superior on a single processor machine. >> >> It has been experience that a well conceived single thread process gives >> the best performance on a single processor, as one would expect. >> >> I would hand out my code except that the whole things incorporates extra >> processors such as an embedded server page processor and and compiler >> plus a security shell and would be a handful to maintain and document in >> great detail but if you are interested in chunks of code contact me. >> >> Alex Katebi wrote: >>> John, >>> >>>My server uses epoll( ) and runs non-blocking in a single thread. I >> did >>> some google and found out that people who need a very fast server that is >>> highly scalable are using this model. non-blocking seems to be more >>> complicated at first glance but it actually makes the server design much >>> simpler. I am planning to release my code as open source when it is more >>> complete. >>> >>> Thanks, >>> -Alex >>> >>> On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> >> wrote: We use an application server I wrote which handles HTTP, serves file and has embedded Sqlite for the RPCs. The RPC can deliver its result either in XML for widely distributed applications or as JSON if it is responding to a WWW browser in AJAX mode. We keep a local library of SQL RPCs so that SQl never appears on the network and we have immunity from injection attacks. It also means that we can cache compiled SQL, a useful performance win. We use the Expat parser in remote programs using the XML format. A wrapper makes it a verifying parser to ensure well formed XML. The server is multi threaded and maintains a pool of live threads so it can respond quickly and assign multiple threads to one browser connection. Shared cache in Sqlite and some extra caching to maintain multiple open databases and results makes Sqlite behave like a simple to use enterprise DB server, but without the overhead of extra processes. We use mutexes for synchronization, set up as read and write locks and avoid the POSIX file locks. We installed Javascript as a procedural language to be used by Sqlite instead of PL/SQL but that is not a great success (v. slow) and we are going to experiment with using Python. Based on our experience you should be very happy with your Sqlite based RPC capability. Alex Katebi wrote: > John & John, > >Actually my API used to be XML using SCEW a DOM like XML parser that uses > Expat. > >For my particular application RPC made more sense to me. What could >> be > easier than a function call? Another advantage was that I did not have >> to > create any functions. I am just using SQLite's C API. Now the users of >> my > application can query any table on the server side using select. Since >> my > application is a network server, and network debugging capability is > crucial. > The only ugliness is that select locks the tables. I wish D. Hipp would give > us an option for pStmt to create a temporary table of the select result set > and delete that temp table after finalize automatically. This way a client > can sit on a prepare/step for a long time. > >I solved the endian issue pretty easy by sending the type code. > > Thanks, > -Alex > > > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED] > wrote: > >> Alex Katebi wrote: >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the >> network >>> then back to host for 8 byte integer. >>> I think XML is great for command validation and CLI auto typing, help >> etc. >>> Besides parsing issue, XML can not handle binary data directly. >>> >> As John pointed out, XML is not intended to handle binary data >> directly. We use XML as a transfer medium for binary data and simply >> base64 encode it before encapsulation. >> >> >> John Elrick >> Fenestra Technologies >> ___ >> sqlite-users mailing
Re: [sqlite] Writing double into a socket file
We make a JSON object of the selected rows and send it to a client in one network access to minimize network traffic. Suitable for clients with Javascript. Packaging of the object can suit the client. Limits are set to avoid choking the client. Alex Katebi wrote: > Dennis, > > After your explanation the prefixing doesn't look so bad. > > The client might do lots of queries before it exits. But I can drop that > temp table when client does finalize. I have to have a state machine for > clients so they don't crash the server by misusing the API anyways. > > Thank you! > -Alex > > > > On Wed, Jun 11, 2008 at 9:36 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > >> Alex Katebi wrote: >>> However there are two things I don't like about this method. One is that >> I >>> need to parse the prepared statments and prefix queries, and replace the >>> table name with the temp table name in the surrogate query. Two is that >> if >>> the client does lots of queries before disconnecting from the server it >>> can waste whole lot of memory. Three is that it is a hack. >>> >> Alex, >> >> There should be no need to parse the queries other than to check for >> "select" at the beginning to identify it as a select statement. All >> select statements would be prefixed with the same prefix string, "create >> tmp.result as", regardless of the complexity of the original query. You >> are simply storing the result set of the query into a temp table. >> >> Since the temporary result table is always the same, the surrogate query >> is always the same, "select * from from tmp.result". There is no need to >> replace any table names. >> >> I imagined the temporary memory database would be closed, and hence its >> memory released, after each query completes. The memory footprint >> wouldn't grow with each query, it would be the same as that required for >> the query with the largest result set. >> >> It is not a hack of any sort. It simply uses the public APIs to quickly >> copy the result data into a private table to avoid locking the entire >> database while those results are slowly scanned by a client. >> >> Dennis Cote >> >> ___ >> 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] Writing double into a socket file
Hi John, Yes the negative with the single thread is single CPU utilization. Then again you can run 4 or more servers for a quad CPU. For security I could either use SSH Port Forwarding or use a MD5 implementation in my client/server code. Thanks, -Alex On Tue, Jun 10, 2008 at 11:25 PM, John Stanton <[EMAIL PROTECTED]> wrote: > Alex, > > Thankyou for the comments. I use non-blocking fd's also but implement > multiple threads to take advantage of multiple processor servers. Since > threads carry quite a bit of baggage your single thread approach would > probably be superior on a single processor machine. > > It has been experience that a well conceived single thread process gives > the best performance on a single processor, as one would expect. > > I would hand out my code except that the whole things incorporates extra > processors such as an embedded server page processor and and compiler > plus a security shell and would be a handful to maintain and document in > great detail but if you are interested in chunks of code contact me. > > Alex Katebi wrote: > > John, > > > >My server uses epoll( ) and runs non-blocking in a single thread. I > did > > some google and found out that people who need a very fast server that is > > highly scalable are using this model. non-blocking seems to be more > > complicated at first glance but it actually makes the server design much > > simpler. I am planning to release my code as open source when it is more > > complete. > > > > Thanks, > > -Alex > > > > On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> > wrote: > > > >> We use an application server I wrote which handles HTTP, serves file and > >> has embedded Sqlite for the RPCs. The RPC can deliver its result either > >> in XML for widely distributed applications or as JSON if it is > >> responding to a WWW browser in AJAX mode. > >> > >> We keep a local library of SQL RPCs so that SQl never appears on the > >> network and we have immunity from injection attacks. It also means that > >> we can cache compiled SQL, a useful performance win. > >> > >> We use the Expat parser in remote programs using the XML format. A > >> wrapper makes it a verifying parser to ensure well formed XML. > >> > >> The server is multi threaded and maintains a pool of live threads so it > >> can respond quickly and assign multiple threads to one browser > >> connection. Shared cache in Sqlite and some extra caching to maintain > >> multiple open databases and results makes Sqlite behave like a simple to > >> use enterprise DB server, but without the overhead of extra processes. > >> We use mutexes for synchronization, set up as read and write locks and > >> avoid the POSIX file locks. > >> > >> We installed Javascript as a procedural language to be used by Sqlite > >> instead of PL/SQL but that is not a great success (v. slow) and we are > >> going to experiment with using Python. > >> > >> Based on our experience you should be very happy with your Sqlite based > >> RPC capability. > >> > >> Alex Katebi wrote: > >>> John & John, > >>> > >>>Actually my API used to be XML using SCEW a DOM like XML parser that > >> uses > >>> Expat. > >>> > >>>For my particular application RPC made more sense to me. What could > be > >>> easier than a function call? Another advantage was that I did not have > to > >>> create any functions. I am just using SQLite's C API. Now the users of > my > >>> application can query any table on the server side using select. Since > my > >>> application is a network server, and network debugging capability is > >>> crucial. > >>> The only ugliness is that select locks the tables. I wish D. Hipp would > >> give > >>> us an option for pStmt to create a temporary table of the select result > >> set > >>> and delete that temp table after finalize automatically. This way a > >> client > >>> can sit on a prepare/step for a long time. > >>> > >>>I solved the endian issue pretty easy by sending the type code. > >>> > >>> Thanks, > >>> -Alex > >>> > >>> > >>> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED] > > > >>> wrote: > >>> > Alex Katebi wrote: > > Yes I need to do it as 8 byte buffer. Convert the endianess to the > network > > then back to host for 8 byte integer. > > I think XML is great for command validation and CLI auto typing, help > etc. > > Besides parsing issue, XML can not handle binary data directly. > > > As John pointed out, XML is not intended to handle binary data > directly. We use XML as a transfer medium for binary data and simply > base64 encode it before encapsulation. > > > John Elrick > Fenestra Technologies > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > >>> ___ > >>> sqlite-users
Re: [sqlite] Writing double into a socket file
Dennis, After your explanation the prefixing doesn't look so bad. The client might do lots of queries before it exits. But I can drop that temp table when client does finalize. I have to have a state machine for clients so they don't crash the server by misusing the API anyways. Thank you! -Alex On Wed, Jun 11, 2008 at 9:36 AM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > However there are two things I don't like about this method. One is that > I > > need to parse the prepared statments and prefix queries, and replace the > > table name with the temp table name in the surrogate query. Two is that > if > > the client does lots of queries before disconnecting from the server it > > can waste whole lot of memory. Three is that it is a hack. > > > > Alex, > > There should be no need to parse the queries other than to check for > "select" at the beginning to identify it as a select statement. All > select statements would be prefixed with the same prefix string, "create > tmp.result as", regardless of the complexity of the original query. You > are simply storing the result set of the query into a temp table. > > Since the temporary result table is always the same, the surrogate query > is always the same, "select * from from tmp.result". There is no need to > replace any table names. > > I imagined the temporary memory database would be closed, and hence its > memory released, after each query completes. The memory footprint > wouldn't grow with each query, it would be the same as that required for > the query with the largest result set. > > It is not a hack of any sort. It simply uses the public APIs to quickly > copy the result data into a private table to avoid locking the entire > database while those results are slowly scanned by a client. > > Dennis Cote > > ___ > 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] Writing double into a socket file
Alex Katebi wrote: > However there are two things I don't like about this method. One is that I > need to parse the prepared statments and prefix queries, and replace the > table name with the temp table name in the surrogate query. Two is that if > the client does lots of queries before disconnecting from the server it > can waste whole lot of memory. Three is that it is a hack. > Alex, There should be no need to parse the queries other than to check for "select" at the beginning to identify it as a select statement. All select statements would be prefixed with the same prefix string, "create tmp.result as", regardless of the complexity of the original query. You are simply storing the result set of the query into a temp table. Since the temporary result table is always the same, the surrogate query is always the same, "select * from from tmp.result". There is no need to replace any table names. I imagined the temporary memory database would be closed, and hence its memory released, after each query completes. The memory footprint wouldn't grow with each query, it would be the same as that required for the query with the largest result set. It is not a hack of any sort. It simply uses the public APIs to quickly copy the result data into a private table to avoid locking the entire database while those results are slowly scanned by a client. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Alex, Thankyou for the comments. I use non-blocking fd's also but implement multiple threads to take advantage of multiple processor servers. Since threads carry quite a bit of baggage your single thread approach would probably be superior on a single processor machine. It has been experience that a well conceived single thread process gives the best performance on a single processor, as one would expect. I would hand out my code except that the whole things incorporates extra processors such as an embedded server page processor and and compiler plus a security shell and would be a handful to maintain and document in great detail but if you are interested in chunks of code contact me. Alex Katebi wrote: > John, > >My server uses epoll( ) and runs non-blocking in a single thread. I did > some google and found out that people who need a very fast server that is > highly scalable are using this model. non-blocking seems to be more > complicated at first glance but it actually makes the server design much > simpler. I am planning to release my code as open source when it is more > complete. > > Thanks, > -Alex > > On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> wrote: > >> We use an application server I wrote which handles HTTP, serves file and >> has embedded Sqlite for the RPCs. The RPC can deliver its result either >> in XML for widely distributed applications or as JSON if it is >> responding to a WWW browser in AJAX mode. >> >> We keep a local library of SQL RPCs so that SQl never appears on the >> network and we have immunity from injection attacks. It also means that >> we can cache compiled SQL, a useful performance win. >> >> We use the Expat parser in remote programs using the XML format. A >> wrapper makes it a verifying parser to ensure well formed XML. >> >> The server is multi threaded and maintains a pool of live threads so it >> can respond quickly and assign multiple threads to one browser >> connection. Shared cache in Sqlite and some extra caching to maintain >> multiple open databases and results makes Sqlite behave like a simple to >> use enterprise DB server, but without the overhead of extra processes. >> We use mutexes for synchronization, set up as read and write locks and >> avoid the POSIX file locks. >> >> We installed Javascript as a procedural language to be used by Sqlite >> instead of PL/SQL but that is not a great success (v. slow) and we are >> going to experiment with using Python. >> >> Based on our experience you should be very happy with your Sqlite based >> RPC capability. >> >> Alex Katebi wrote: >>> John & John, >>> >>>Actually my API used to be XML using SCEW a DOM like XML parser that >> uses >>> Expat. >>> >>>For my particular application RPC made more sense to me. What could be >>> easier than a function call? Another advantage was that I did not have to >>> create any functions. I am just using SQLite's C API. Now the users of my >>> application can query any table on the server side using select. Since my >>> application is a network server, and network debugging capability is >>> crucial. >>> The only ugliness is that select locks the tables. I wish D. Hipp would >> give >>> us an option for pStmt to create a temporary table of the select result >> set >>> and delete that temp table after finalize automatically. This way a >> client >>> can sit on a prepare/step for a long time. >>> >>>I solved the endian issue pretty easy by sending the type code. >>> >>> Thanks, >>> -Alex >>> >>> >>> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> >>> wrote: >>> Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > As John pointed out, XML is not intended to handle binary data directly. We use XML as a transfer medium for binary data and simply base64 encode it before encapsulation. John Elrick Fenestra Technologies ___ 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 >> > ___ > 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
Re: [sqlite] Writing double into a socket file
John, My server uses epoll( ) and runs non-blocking in a single thread. I did some google and found out that people who need a very fast server that is highly scalable are using this model. non-blocking seems to be more complicated at first glance but it actually makes the server design much simpler. I am planning to release my code as open source when it is more complete. Thanks, -Alex On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> wrote: > We use an application server I wrote which handles HTTP, serves file and > has embedded Sqlite for the RPCs. The RPC can deliver its result either > in XML for widely distributed applications or as JSON if it is > responding to a WWW browser in AJAX mode. > > We keep a local library of SQL RPCs so that SQl never appears on the > network and we have immunity from injection attacks. It also means that > we can cache compiled SQL, a useful performance win. > > We use the Expat parser in remote programs using the XML format. A > wrapper makes it a verifying parser to ensure well formed XML. > > The server is multi threaded and maintains a pool of live threads so it > can respond quickly and assign multiple threads to one browser > connection. Shared cache in Sqlite and some extra caching to maintain > multiple open databases and results makes Sqlite behave like a simple to > use enterprise DB server, but without the overhead of extra processes. > We use mutexes for synchronization, set up as read and write locks and > avoid the POSIX file locks. > > We installed Javascript as a procedural language to be used by Sqlite > instead of PL/SQL but that is not a great success (v. slow) and we are > going to experiment with using Python. > > Based on our experience you should be very happy with your Sqlite based > RPC capability. > > Alex Katebi wrote: > > John & John, > > > >Actually my API used to be XML using SCEW a DOM like XML parser that > uses > > Expat. > > > >For my particular application RPC made more sense to me. What could be > > easier than a function call? Another advantage was that I did not have to > > create any functions. I am just using SQLite's C API. Now the users of my > > application can query any table on the server side using select. Since my > > application is a network server, and network debugging capability is > > crucial. > > The only ugliness is that select locks the tables. I wish D. Hipp would > give > > us an option for pStmt to create a temporary table of the select result > set > > and delete that temp table after finalize automatically. This way a > client > > can sit on a prepare/step for a long time. > > > >I solved the endian issue pretty easy by sending the type code. > > > > Thanks, > > -Alex > > > > > > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> > > wrote: > > > >> Alex Katebi wrote: > >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the > >> network > >>> then back to host for 8 byte integer. > >>> I think XML is great for command validation and CLI auto typing, help > >> etc. > >>> Besides parsing issue, XML can not handle binary data directly. > >>> > >> As John pointed out, XML is not intended to handle binary data > >> directly. We use XML as a transfer medium for binary data and simply > >> base64 encode it before encapsulation. > >> > >> > >> John Elrick > >> Fenestra Technologies > >> ___ > >> 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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
We use an application server I wrote which handles HTTP, serves file and has embedded Sqlite for the RPCs. The RPC can deliver its result either in XML for widely distributed applications or as JSON if it is responding to a WWW browser in AJAX mode. We keep a local library of SQL RPCs so that SQl never appears on the network and we have immunity from injection attacks. It also means that we can cache compiled SQL, a useful performance win. We use the Expat parser in remote programs using the XML format. A wrapper makes it a verifying parser to ensure well formed XML. The server is multi threaded and maintains a pool of live threads so it can respond quickly and assign multiple threads to one browser connection. Shared cache in Sqlite and some extra caching to maintain multiple open databases and results makes Sqlite behave like a simple to use enterprise DB server, but without the overhead of extra processes. We use mutexes for synchronization, set up as read and write locks and avoid the POSIX file locks. We installed Javascript as a procedural language to be used by Sqlite instead of PL/SQL but that is not a great success (v. slow) and we are going to experiment with using Python. Based on our experience you should be very happy with your Sqlite based RPC capability. Alex Katebi wrote: > John & John, > >Actually my API used to be XML using SCEW a DOM like XML parser that uses > Expat. > >For my particular application RPC made more sense to me. What could be > easier than a function call? Another advantage was that I did not have to > create any functions. I am just using SQLite's C API. Now the users of my > application can query any table on the server side using select. Since my > application is a network server, and network debugging capability is > crucial. > The only ugliness is that select locks the tables. I wish D. Hipp would give > us an option for pStmt to create a temporary table of the select result set > and delete that temp table after finalize automatically. This way a client > can sit on a prepare/step for a long time. > >I solved the endian issue pretty easy by sending the type code. > > Thanks, > -Alex > > > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> > wrote: > >> Alex Katebi wrote: >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the >> network >>> then back to host for 8 byte integer. >>> I think XML is great for command validation and CLI auto typing, help >> etc. >>> Besides parsing issue, XML can not handle binary data directly. >>> >> As John pointed out, XML is not intended to handle binary data >> directly. We use XML as a transfer medium for binary data and simply >> base64 encode it before encapsulation. >> >> >> John Elrick >> Fenestra Technologies >> ___ >> 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] Writing double into a socket file
Dennis, My servers main connection itself is in :memory:, but your suggestion will still work. However there are two things I don't like about this method. One is that I need to parse the prepared statments and prefix queries, and replace the table name with the temp table name in the surrogate query. Two is that if the client does lots of queries before disconnecting from the server it can waste whole lot of memory. Three is that it is a hack. However, server can keep track of client temp tables and drop them after finalize. Parsing might not be as bad as I think. Right now this seems to be the only choice. Thanks, -Alex On Tue, Jun 10, 2008 at 4:33 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > The only ugliness is that select locks the tables. I wish D. Hipp would > give > > us an option for pStmt to create a temporary table of the select result > set > > and delete that temp table after finalize automatically. This way a > client > > can sit on a prepare/step for a long time. > > > > Alex, > > Your application can do this by itself without any changes to the SQLite > core. You can attach a :memory: database as tmp to your main database, > then prefix your client's query with "create table tmp.result as", and > then return the result of a surrogate query "select * from tmp.result" > instead of the actual result of the client's query. This will only hold > the lock on the main database while the temp table is created, since > memory database don't use any locking because they are private to a > single connection. The client can then scan through the surrogate > results at its leisure. When the client is done you can close the memory > database. > > HTH > Dennis Cote > ___ > 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] Writing double into a socket file
Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > As John pointed out, XML is not intended to handle binary data directly. We use XML as a transfer medium for binary data and simply base64 encode it before encapsulation. John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Hi, Alex Katebi wrote: > I am trying to implement remote procedure calls (RPC) for SQLite API to be > used in my application. > In particular sqlite3_column_double( ) returns a floating point double. > How can I write this double value into a TCP socket? > I have tried writing 8 bytes as integer values but the received valued at > the other end of the socket is incorrect. > I don't have a lot of experience with real numbers. Can someone help? > Have a look at YAMI (http://www.msobczak.com/prog/yami/). It has the ability to send/receive following data types: eString = 1; /* ASCII string */ eWString = 2; /* wide string*/ eInt = 3; /* integer*/ eDouble = 4; /* double */ eByte= 5; /* opaque byte*/ eBinary = 6; /* opaque binary data */ > Thanks, > -Alex -- Regards, Hakki Dogusan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Alex, the whole point of using XML is to avoid using binary data in an RPC situation and to tag the data and thus make extensions less traumatic. The radix change overhead is insignificant compared to the network cost and the bonus is complete freedom from endian dilemmas. The biggest performance improvement we get is by careful use of sockets and avoiding process and thread creation and deletion. For example using sendfile or TransmitFile avoids multiple levels of buffer shadowing. Our RPC implementation is fast and robust and not bloated like XML/RPC. Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > > > On Tue, Jun 10, 2008 at 12:31 PM, John Stanton <[EMAIL PROTECTED]> wrote: > >> Can you explain how you are trying to write to the socket and how you >> are receiving? >> >> If you use write or send you just supply a pointer to the value and a >> length to write, viz - written = write(sokfd, (char *)fptr, 8); where >> fptr is a pointer to your floating point number. >> >> We use Sqlite embedded as RPCs but actually encapsulate the data as XML >> and send floating point or other numbers in text form so that there can >> never be byte ordering or format issues. SQL also maps quite nicely to >> XML. The downside is the complexity of the XML parser at the receive end. >> >> Alex Katebi wrote: >>> I am trying to implement remote procedure calls (RPC) for SQLite API to >> be >>> used in my application. >>> In particular sqlite3_column_double( ) returns a floating point double. >>> How can I write this double value into a TCP socket? >>> I have tried writing 8 bytes as integer values but the received valued at >>> the other end of the socket is incorrect. >>> I don't have a lot of experience with real numbers. Can someone help? >>> >>> Thanks, >>> -Alex >> > ___ >>> 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
D. Hipp, I have since identified and fixed my problem, thanks to our user group! A lot of the networking gear like IP routers use big-endian machines since this is the format used by the network protocol control messages. PowerPC is an example. Thanks, -Alex On Mon, Jun 9, 2008 at 10:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 9, 2008, at 9:58 PM, Russell Leighton wrote: > > > > > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > > > >> "Alex Katebi" <[EMAIL PROTECTED]> > >> wrote in message > >> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > >>> I am trying to implement remote procedure calls (RPC) for SQLite API > >>> to be used in my application. > >>> In particular sqlite3_column_double( ) returns a floating point > >>> double. How can I write this double value into a TCP socket? > >> > >> How do you write an int, or a string, into a socket? A double > >> wouldn't > >> be much different: at the end of the day, it's just an 8-byte buffer. > >> > > > > Aren' t there aligment and endian issues as well as potential floating > > point representations between platforms? > > > > > Endianness might be a problem, though these days it is becoming > increasingly difficult to find a big-endian processor. I don't think > different floating point representations are an issue since I am not > aware of any modern machine that does anything other than IEEE754. > SQLite assumes IEEE754 floating point representation, so if you have a > machine that uses something different, SQLite won't work on it (or at > least it won't have a compatible file format.) I have never yet heard > of this being a problem for anyone. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Writing double into a socket file
Arjen, Thanks! On Tue, Jun 10, 2008 at 1:39 PM, Arjen Markus <[EMAIL PROTECTED]> wrote: > > I am going to guess yes. > > > > On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton > > <[EMAIL PROTECTED]> > > wrote: > > > > >> > >> Aren' t there aligment and endian issues as well as potential floating > >> point representations between platforms? > >> > > As most computers nowadays use the IEEE standard to represent > floating-point numbers this problem is much less complicated > than it used to be. I think only endian problems are left and > these are (relatively) easy to deal with. > > Regards, > > Arjen > ___ > 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] Writing double into a socket file
Yes I need to do it as 8 byte buffer. Convert the endianess to the network then back to host for 8 byte integer. I think XML is great for command validation and CLI auto typing, help etc. Besides parsing issue, XML can not handle binary data directly. On Tue, Jun 10, 2008 at 12:31 PM, John Stanton <[EMAIL PROTECTED]> wrote: > Can you explain how you are trying to write to the socket and how you > are receiving? > > If you use write or send you just supply a pointer to the value and a > length to write, viz - written = write(sokfd, (char *)fptr, 8); where > fptr is a pointer to your floating point number. > > We use Sqlite embedded as RPCs but actually encapsulate the data as XML > and send floating point or other numbers in text form so that there can > never be byte ordering or format issues. SQL also maps quite nicely to > XML. The downside is the complexity of the XML parser at the receive end. > > Alex Katebi wrote: > > I am trying to implement remote procedure calls (RPC) for SQLite API to > be > > used in my application. > > In particular sqlite3_column_double( ) returns a floating point double. > > How can I write this double value into a TCP socket? > > I have tried writing 8 bytes as integer values but the received valued at > > the other end of the socket is incorrect. > > I don't have a lot of experience with real numbers. Can someone help? > > > > Thanks, > > -Alex > > ___ > > 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] Writing double into a socket file
> I am going to guess yes. > > On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton > <[EMAIL PROTECTED]> > wrote: > >> >> Aren' t there aligment and endian issues as well as potential floating >> point representations between platforms? >> As most computers nowadays use the IEEE standard to represent floating-point numbers this problem is much less complicated than it used to be. I think only endian problems are left and these are (relatively) easy to deal with. Regards, Arjen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
I am going to guess yes. On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton <[EMAIL PROTECTED]> wrote: > > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > > > "Alex Katebi" <[EMAIL PROTECTED]> > > wrote in message > > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > >> I am trying to implement remote procedure calls (RPC) for SQLite API > >> to be used in my application. > >> In particular sqlite3_column_double( ) returns a floating point > >> double. How can I write this double value into a TCP socket? > > > > How do you write an int, or a string, into a socket? A double wouldn't > > be much different: at the end of the day, it's just an 8-byte buffer. > > > > Aren' t there aligment and endian issues as well as potential floating > point representations between platforms? > > > ___ > 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] Writing double into a socket file
Alex Katebi wrote: > Peter, >I am using my own implementation. I found RPC and others too complicated > to use and it did not give me enough control on the transport layer. I need > my socket to be non-blocking and I am using epoll( ) which is very > efficient. > XDR is a separate specification and can be used independently of RPC. Python has a library for dealing with it. XDR isn't simple, but it does provide the ability to pass complex structures and types between different kinds of machines. You can even create things like linked lists that can be sent over the wire. If that doesn't work for you, there are other options like XML, which comes with it's own complexity and cost. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Igor, You are right. The answer is to use 8 byte buffer and don't forget to account for the endianess. I had a bug in my code. On Mon, Jun 9, 2008 at 8:52 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Alex Katebi" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > I am trying to implement remote procedure calls (RPC) for SQLite API > > to be used in my application. > > In particular sqlite3_column_double( ) returns a floating point > > double. How can I write this double value into a TCP socket? > > How do you write an int, or a string, into a socket? A double wouldn't > be much different: at the end of the day, it's just an 8-byte buffer. > > Igor Tandetnik > > > > ___ > 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] Writing double into a socket file
Peter, I am using my own implementation. I found RPC and others too complicated to use and it did not give me enough control on the transport layer. I need my socket to be non-blocking and I am using epoll( ) which is very efficient. Thanks! -Alex On Mon, Jun 9, 2008 at 10:53 PM, Peter A. Friend <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > I am trying to implement remote procedure calls (RPC) for SQLite API to > be > > used in my application. > > In particular sqlite3_column_double( ) returns a floating point double. > > How can I write this double value into a TCP socket? > > I have tried writing 8 bytes as integer values but the received valued at > > the other end of the socket is incorrect. > > I don't have a lot of experience with real numbers. Can someone help? > > > Are you using something like ONC or Sun RPC, or are you rolling your > own? XDR already handles these types of problems for you. > > Peter > > ___ > 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] Writing double into a socket file
Alex Katebi wrote: > I am trying to implement remote procedure calls (RPC) for SQLite API to be > used in my application. > In particular sqlite3_column_double( ) returns a floating point double. > How can I write this double value into a TCP socket? > I have tried writing 8 bytes as integer values but the received valued at > the other end of the socket is incorrect. > I don't have a lot of experience with real numbers. Can someone help? > Are you using something like ONC or Sun RPC, or are you rolling your own? XDR already handles these types of problems for you. Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
On Jun 9, 2008, at 9:58 PM, Russell Leighton wrote: > > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > >> "Alex Katebi" <[EMAIL PROTECTED]> >> wrote in message >> news:[EMAIL PROTECTED] >>> I am trying to implement remote procedure calls (RPC) for SQLite API >>> to be used in my application. >>> In particular sqlite3_column_double( ) returns a floating point >>> double. How can I write this double value into a TCP socket? >> >> How do you write an int, or a string, into a socket? A double >> wouldn't >> be much different: at the end of the day, it's just an 8-byte buffer. >> > > Aren' t there aligment and endian issues as well as potential floating > point representations between platforms? > Endianness might be a problem, though these days it is becoming increasingly difficult to find a big-endian processor. I don't think different floating point representations are an issue since I am not aware of any modern machine that does anything other than IEEE754. SQLite assumes IEEE754 floating point representation, so if you have a machine that uses something different, SQLite won't work on it (or at least it won't have a compatible file format.) I have never yet heard of this being a problem for anyone. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Hello Alex, How about a text representation? CSV over the network using the HTTP protocol or something like that. C Monday, June 9, 2008, 8:40:39 PM, you wrote: AK> I am trying to implement remote procedure calls (RPC) for SQLite API to be AK> used in my application. AK> In particular sqlite3_column_double( ) returns a floating point double. AK> How can I write this double value into a TCP socket? AK> I have tried writing 8 bytes as integer values but the received valued at AK> the other end of the socket is incorrect. AK> I don't have a lot of experience with real numbers. Can someone help? AK> Thanks, AK> -Alex AK> ___ AK> sqlite-users mailing list AK> sqlite-users@sqlite.org AK> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Best regards, Tegmailto:[EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > "Alex Katebi" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED] >> I am trying to implement remote procedure calls (RPC) for SQLite API >> to be used in my application. >> In particular sqlite3_column_double( ) returns a floating point >> double. How can I write this double value into a TCP socket? > > How do you write an int, or a string, into a socket? A double wouldn't > be much different: at the end of the day, it's just an 8-byte buffer. > Aren' t there aligment and endian issues as well as potential floating point representations between platforms? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
"Alex Katebi" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I am trying to implement remote procedure calls (RPC) for SQLite API > to be used in my application. > In particular sqlite3_column_double( ) returns a floating point > double. How can I write this double value into a TCP socket? How do you write an int, or a string, into a socket? A double wouldn't be much different: at the end of the day, it's just an 8-byte buffer. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
