[sqlite] SQLite queries
On Thu, 7 May 2015 11:25:33 -0500 John McKown wrote: > On Thu, May 7, 2015 at 10:01 AM, Stephen Chrzanowski > wrote: > > > Although I can understand the sarcasm you're sending out, a > > client/server infrastructure would be an interesting task for > > SQLite to do, but to answer the OP, no, SQLite isn't inherently > > designed to be a client/server. > > > > ?And not really that difficult. Embed SQLite into some "server code" > to which a client connects, probably via TCPIP or some other IPC > method.? The client connects to the server, which uses the embedded > SQLite to access the appropriate SQLite data base file. I would guess > that the server could even be multi-threaded with each thread > controlling a separate SQLite data base file. Do you mean create a NewSQL Shared-Nothing distributed database with SQlite3, using something like nanomsg or 0MQ for distribute workload and messages, and Graph Theory for manage the whole headless distributed server? Don't try to do that. It's far complex than you think and even Pandora won't open it. I see now Stephen answer and he cite some of the problems you may encounter at "There are MANY things to be taken care of,.." paragraph. --- --- Eduardo Morras
[sqlite] SQLite queries
> > Although I can understand the sarcasm you're sending out, a > > client/server infrastructure would be an interesting task for > > SQLite to do, but to answer the OP, no, SQLite isn't inherently > > designed to be a client/server. > ?And not really that difficult. Embed SQLite into some "server code" to > which a client connects, probably via TCPIP or some other IPC method.? The > client connects to the server, which uses the embedded SQLite to access > the appropriate SQLite data base file. I would guess that the server could > even be multi-threaded with each thread controlling a separate SQLite data > base > file. That is exactly what a client/server database is. It is an embeded database engine (DB/2, Microsoft SQL Server, Oracle, PostGres, etc, etc, etc) that only works against files located on the local filesystem, wrapped up (embeded) in some sort of RPC mechanism to marshal procedure calls and data from the client, and execute those calls on the database engine and return the result to the caller. If you embedded SQLite in a "Server" application, then you would have a Client/Server SQLite database. As the saying goes, inside every big program is a little program trying to get out ...
[sqlite] SQLite queries
On 6 May 2015, at 12:56pm, Amit Golhani wrote: > -database should support requirement of HA and distributed system > - provision for database synchronization and replication should be there as a > part of database package without considering any third party tool > -client server architecture > - data synchronization with Oracle, MySQL, MS SQL etc. for storing long > duration information. SQLite does not do these things therefore, if they are required, you should not use it. Simon.
[sqlite] SQLite queries
I prefer my integers unsinged ;) SQLIte: - only signed integers up to 64 bit - arrays and structures only if you are willing to extract them from blobs via user written virtual tables - no date/time type, but you can store microsecond resolution timestamps in 64bit integers -Urspr?ngliche Nachricht- Von: Amit Golhani [mailto:AMIT.GOLHANI at lntebg.com] Gesendet: Mittwoch, 06. Mai 2015 13:57 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] SQLite queries ... -it should support singed ... integers, ... ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] SQLite queries
On Thu, May 7, 2015 at 12:25 PM, John McKown wrote: > On Thu, May 7, 2015 at 10:01 AM, Stephen Chrzanowski > wrote: > > > Although I can understand the sarcasm you're sending out, a client/server > > infrastructure would be an interesting task for SQLite to do, but to > answer > > the OP, no, SQLite isn't inherently designed to be a client/server. > > > > ?And not really that difficult. Embed SQLite into some "server code" to > which a client connects, probably via TCPIP or some other IPC method.? The > client connects to the server, which uses the embedded SQLite to access the > appropriate SQLite data base file. I would guess that the server could even > be multi-threaded with each thread controlling a separate SQLite data base > file. > > If you're referring to a central repo, then yeah, SQLite could be made to compete with other major DBMS systems. But the pre-coffee "me" was thinking more about a type of synchronized clustering type of technology, in that, each client running the "server" has not only a copy of the database it can access 100% locally, but, shares the results of each transaction done, both in-bound and out-bound, via TCP. So even if there is a disconnection on the network, say, a laptop goes home for the evening and not hooked into a VPN, work can still be done on that database. The client software treats the database as its only source, but, on the "server" side of things, it is checking to see if there are any other "servers" on the LAN and tries to make a connection to sync up any data that might be out there. Once the machine gets back into the office, sees that there are other clients out there, it can sync the data back to the other clients. The "client software" wouldn't have to be running UNLESS the client software is running the "server code" inside itself. Essentially, a multi-threaded application, where one thread does all client stuff, and another that does server stuff, but the application as a whole treats both threads as completely different entities, and establishes connections via normal calls. There are MANY things to be taken care of,such as data collisions, who has priority over the data, "full record locking" (As in all information about a particular subject, say, an insurance claim which would hold information about the customer, the policy details, comments about the policy, etc), and the such. It wouldn't be an easy task, no, but, the post-cafinated me thinks that with a TONNE of work, it can be done. And if I get bored when on night shift at work, I just might peddle at something like that in a language absolutely no one on this forum (Other than Bogdan) uses. > > > > > > But, think of how torrents work. Everyone is a server, everyone is a > > client. Everyone has a connection to every other database, but, it has > its > > own working copy to query against. If Client A updates a table, the > > statement is sent out to every other "server" it has a connection to. > Any > > differences in the data can be synced from one to another. Even with > > MySQL, the client has to pull the information it needs from the database > to > > get its information and work with it locally in RAM (usually) then push > the > > data back if there is anything to be done. Instead of the queries going > to > > a MySQL server, you'd be talking to a server sitting right on your > > machine. Any queries from any other clients are done in another > > process/thread to the server database. (I'm actually kind of getting > > excited about this kind of coding, honestly.) > > > > ?I'm not an expert in this area, but your description sounds a bit like a > stored procedure to me. http://en.wikipedia.org/wiki/Stored_procedure > Basically, you define the "procedure" in the data base itself, and the code > runs on the server, perhaps even in the same process as the server. This > saves the cost of doing the data transmission / reception between client > and server. > > No, I'm talking about data stored in applications assigned RAM after the database communication transaction, outside of any database. So I make a query to the database, and whatever results come up are dumped onto a form, an array, or what have you. Work is done on the data, within the UI/Memory client, outside of the DBMS, then whatever data is affected is pushed back into the database. >From my course back in the early 2000s when I did training to be an MSDBA (Which I never did the actual certification for, unfortunately -- Money constraints), Stored Procedures can look and act like a view, but, have the knowledge of a language, variables and variable scope, and what not, to deal with information all within the engine of the DBMS, which means the client makes the request, the server/SP does the work, the server feeds the data back to the client. The SP has the ability to do any additional transactions to get the results, actually process the data, and then push the data out. For example, with MySQL and MS
[sqlite] SQLite queries
On Thu, May 7, 2015 at 10:01 AM, Stephen Chrzanowski wrote: > Although I can understand the sarcasm you're sending out, a client/server > infrastructure would be an interesting task for SQLite to do, but to answer > the OP, no, SQLite isn't inherently designed to be a client/server. > ?And not really that difficult. Embed SQLite into some "server code" to which a client connects, probably via TCPIP or some other IPC method.? The client connects to the server, which uses the embedded SQLite to access the appropriate SQLite data base file. I would guess that the server could even be multi-threaded with each thread controlling a separate SQLite data base file. > > But, think of how torrents work. Everyone is a server, everyone is a > client. Everyone has a connection to every other database, but, it has its > own working copy to query against. If Client A updates a table, the > statement is sent out to every other "server" it has a connection to. Any > differences in the data can be synced from one to another. Even with > MySQL, the client has to pull the information it needs from the database to > get its information and work with it locally in RAM (usually) then push the > data back if there is anything to be done. Instead of the queries going to > a MySQL server, you'd be talking to a server sitting right on your > machine. Any queries from any other clients are done in another > process/thread to the server database. (I'm actually kind of getting > excited about this kind of coding, honestly.) > ?I'm not an expert in this area, but your description sounds a bit like a stored procedure to me. http://en.wikipedia.org/wiki/Stored_procedure Basically, you define the "procedure" in the data base itself, and the code runs on the server, perhaps even in the same process as the server. This saves the cost of doing the data transmission / reception between client and server. > > This isn't to say that this kind of infrastructure would be CRAZY to do, > but it isn't impossible, and the hurdles to get it done can be overcome. > So although a gas burning electric stove seems odd, getting the gas to boil > water to push the turbine to generate electricity to run the burners does > fit the particular scenario. > Well, that's "cheating" by marrying an electric generator with a normal electric oven. And I do hope the burners have good thermal insulation from the supporting wood frame. > > And a cubical sphere would be so freak'n awesome. If you get enough, it'd > redefine "office pool". :] > > -- If someone tell you that nothing is impossible: Ask him to dribble a football. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] SQLite queries
Although I can understand the sarcasm you're sending out, a client/server infrastructure would be an interesting task for SQLite to do, but to answer the OP, no, SQLite isn't inherently designed to be a client/server. But, think of how torrents work. Everyone is a server, everyone is a client. Everyone has a connection to every other database, but, it has its own working copy to query against. If Client A updates a table, the statement is sent out to every other "server" it has a connection to. Any differences in the data can be synced from one to another. Even with MySQL, the client has to pull the information it needs from the database to get its information and work with it locally in RAM (usually) then push the data back if there is anything to be done. Instead of the queries going to a MySQL server, you'd be talking to a server sitting right on your machine. Any queries from any other clients are done in another process/thread to the server database. (I'm actually kind of getting excited about this kind of coding, honestly.) This isn't to say that this kind of infrastructure would be CRAZY to do, but it isn't impossible, and the hurdles to get it done can be overcome. So although a gas burning electric stove seems odd, getting the gas to boil water to push the turbine to generate electricity to run the burners does fit the particular scenario. And a cubical sphere would be so freak'n awesome. If you get enough, it'd redefine "office pool". :] On Thu, May 7, 2015 at 10:38 AM, John McKown wrote: > > > ?Which is why I didn't even bother to consider answering the original > question. One requirement was "an embedded client/server data base". Unless > I am really confused, this would be like asking for a gas burning electric > stove made of wood. Or a cubical sphere. But I'm getting(getting?) > sarcastic again. > >
[sqlite] SQLite queries
On 5/6/15, Amit Golhani wrote: > -client server architecture SQLite is an embedded SQL database. Client/Server is a completely different thing intended to solve a completely different problem. -- D. Richard Hipp drh at sqlite.org
[sqlite] SQLite queries
On Thu, May 7, 2015 at 8:56 AM, Richard Hipp wrote: > On 5/6/15, Amit Golhani wrote: > > -client server architecture > > SQLite is an embedded SQL database. Client/Server is a completely > different thing intended to solve a completely different problem. > ?Which is why I didn't even bother to consider answering the original question. One requirement was "an embedded client/server data base". Unless I am really confused, this would be like asking for a gas burning electric stove made of wood. Or a cubical sphere. But I'm getting(getting?) sarcastic again. > -- > D. Richard Hipp > drh at sqlite.org > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- If someone tell you that nothing is impossible: Ask him to dribble a football. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] SQLite queries
Hello, This is Amit Golhani from Larsen & Toubro India, currently I am evaluating features of SQLite to meet our product requirements, which are mainly as follows: -database should recover automatically without human intervention in case of abrupt power failure/ crash/ termination of program -database should meet all requirement of ACID -database should support requirement of HA and distributed system - provision for database synchronization and replication should be there as a part of database package without considering any third party tool -it should support singed and unsigned 8,16,24,32,64,128 bit integers, 32 and 64 bit float, structure ,array, blob,GUID, Wide characters, string (variable length),data, time (upto microseconds atleast) etc. -client server architecture -encryption - concurrent connections from threads/processes - data synchronization with Oracle, MySQL, MS SQL etc. for storing long duration information. -In-memory,on-disk and hybrid configuration Please let me know whether SQLite can meet all above mentioned requirements. We are planning embedded data base for storing data model of the supervisory control and data acquisition application. Best Regards, Amit Golhani, M.Tech. (Microwaves), Manger- Design, IPR & DSIR Coordinator Larsen & Toubro,Electrical & Automation Company, Power Electronics & Automation Technology Centre, A-600,TTC Industrial Area,Shil-Mahape Road, Mahape, Navi Mumbai-400710, India Phone: (+91) 22 6722 6479 Mobile: (+91) 9870048046 Larsen & Toubro Limited www.larsentoubro.com This Email may contain confidential or privileged information for the intended recipient (s). If you are not the intended recipient, please do not use or disseminate the information, notify the sender and delete it from your system.
[sqlite] sqlite queries execute faster in sqlite manager addon but take a lot of time to execute using a java program
Hi One of my sqlite query executes faster in sqlite manager( firefox addon ) but take a lot of time to execute using a java program. Here is the snippet PreparedStatement mStatement; ResultSet mResults; CachedRowSetImpl impl=null; Connection mconn = null; try { Class.forName("org.sqlite.JDBC"); mconn = DriverManager.getConnection("jdbc:sqlite:"+ "database.db"); mStatement = mconn.prepareStatement(jobQuery); long startTime = System.currentTimeMillis(); mResults = mStatement.executeQuery(); long endTime = System.currentTimeMillis(); System.out.println(jobQuery); System.out.println("Time taken to execute query ==>" +(endTime - startTime)); impl = new CachedRowSetImpl(); impl.populate(mResults); mStatement.close(); mResults.close(); } catch (SQLException e) { e.printStackTrace(); } Is there anything that I am missing while connecting using java ? Any help appreciated. Sridhar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users