Re: [sqlite] Multiple databases in one DB connection. How to let BEGIN TRANSACTION..COMMIT apply to only one of them?
Well, if the statements within your BEGIN TRANSACTION ... COMMIT only really affects one database (hereafter called the main database) only that database should be locked. If you experience otherwise, it could be because: 1. The locking_mode for the databases has been set to EXCLUSIVE (see http://sqlite.org/pragma.html#pragma_locking_mode) 2. The statements executed against the main database may read from other databases as part of the execution thus holding SHARED locks on the other databases which will prevent writing to them. See an explanation of the locking system in SQLite here (http://www.sqlite.org/lockingv3.html). However, you should still be able to read from the other databases while the transaction is being committed. 3. Inside the transaction against the main database you have overlooked some statement that does in fact write to the other databases. Perhaps you can supply some more details about what takes place inside the transaction against the main database and what you are trying to do with the other databases, if the above explanation doesn't solve your issue. /Frank > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Simon L > Sent: 26 October 2011 14:08 > To: sqlite-users@sqlite.org > Subject: [sqlite] Multiple databases in one DB connection. How to let BEGIN > TRANSACTION..COMMIT apply to only one of them? > > While one of the databases is under a lengthy writing process, I don't want > other databases to be locked by the BEGIN TRANSACTION...COMMIT lock. > Is this possible? Thanks. > ___ > 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] Multiple databases in one DB connection. How to let BEGIN TRANSACTION..COMMIT apply to only one of them?
While one of the databases is under a lengthy writing process, I don't want other databases to be locked by the BEGIN TRANSACTION...COMMIT lock. Is this possible? Thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
On boot up the device will discover the servers and enumerate what they have, then when it is done it will just wait for updates from the servers. I do not know how big the servers are, each server/media library may be different. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard Sent: Tuesday, October 26, 2010 10:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Multiple databases vs. Multiple tables. On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Queries will be done across the servers to aggregate the content. I am > concern about the locking mechanism, if I write to the single database and > I represent the servers as tables I will have no access to read the other > servers/tables, but if I distribute the servers across different DBs I can > write into one and the other ones are open to read. How frequently are data written to the tables? How much data per write? It appears that you want to use separate databases for each server so you might try that and see how well it works for you. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Queries will be done across the servers to aggregate the content. I am > concern about the locking mechanism, if I write to the single database and > I represent the servers as tables I will have no access to read the other > servers/tables, but if I distribute the servers across different DBs I can > write into one and the other ones are open to read. How frequently are data written to the tables? How much data per write? It appears that you want to use separate databases for each server so you might try that and see how well it works for you. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
Queries will be done across the servers to aggregate the content. I am concern about the locking mechanism, if I write to the single database and I represent the servers as tables I will have no access to read the other servers/tables, but if I distribute the servers across different DBs I can write into one and the other ones are open to read. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard Sent: Tuesday, October 26, 2010 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Multiple databases vs. Multiple tables. On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Only one user (the browser). The DB is on a device the same place where > the user (Browser) is. A process will collect the information about the > servers and their contents and story it to the DB at the same time the > user may ask for the contents to display on the screen. Then the next question is wether queries are restricted to each server. If so, you could have a separate database for each. Alternatively, you can define a server table and associate each one with the other data you collect and retrieve. The latter approach is more flexible and allows easier changes if/when your information needs change. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > Only one user (the browser). The DB is on a device the same place where > the user (Browser) is. A process will collect the information about the > servers and their contents and story it to the DB at the same time the > user may ask for the contents to display on the screen. Then the next question is wether queries are restricted to each server. If so, you could have a separate database for each. Alternatively, you can define a server table and associate each one with the other data you collect and retrieve. The latter approach is more flexible and allows easier changes if/when your information needs change. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
Only one user (the browser). The DB is on a device the same place where the user (Browser) is. A process will collect the information about the servers and their contents and story it to the DB at the same time the user may ask for the contents to display on the screen. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rich Shepard Sent: Tuesday, October 26, 2010 9:24 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Multiple databases vs. Multiple tables. On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > in your opinion is it better (performance, maintainability etc...) to > have multiple databases or multiple tables. The problem I am facing is as > follows. I have many media servers containing a large amount of images > music and videos, let's assume 5. I would like to gather the information > (metadata, thumbnails, location etc) and story it in the database. I will > also have a GUI/Browser that will display that information. Now, the > question is it better to use multiple databases, each server = one > database or each server = one table. Daiuusz, I suggest you're asking the wrong questions. How many simultaneous users will access data in the database? Will the database be stored on one server and accessed across the network? It might be that for your application SQLite is not the appropriate tool. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users - This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases vs. Multiple tables.
On Tue, 26 Oct 2010, Dariusz Matkowski wrote: > in your opinion is it better (performance, maintainability etc...) to > have multiple databases or multiple tables. The problem I am facing is as > follows. I have many media servers containing a large amount of images > music and videos, let's assume 5. I would like to gather the information > (metadata, thumbnails, location etc) and story it in the database. I will > also have a GUI/Browser that will display that information. Now, the > question is it better to use multiple databases, each server = one > database or each server = one table. Daiuusz, I suggest you're asking the wrong questions. How many simultaneous users will access data in the database? Will the database be stored on one server and accessed across the network? It might be that for your application SQLite is not the appropriate tool. Rich ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Multiple databases vs. Multiple tables.
Question, in your opinion is it better (performance, maintainability etc...) to have multiple databases or multiple tables. The problem I am facing is as follows. I have many media servers containing a large amount of images music and videos, let's assume 5. I would like to gather the information (metadata, thumbnails, location etc) and story it in the database. I will also have a GUI/Browser that will display that information. Now, the question is it better to use multiple databases, each server = one database or each server = one table. Regards, - This transmission (including any attachments) may contain confidential information, privileged material (including material protected by the solicitor-client or other applicable privileges), or constitute non-public information. Any use of this information by anyone other than the intended recipient is prohibited. If you have received this transmission in error, please immediately reply to the sender and delete this information from your system. Use, dissemination, distribution, or reproduction of this transmission by unintended recipients is not authorized and may be unlawful. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases
On Mar 3, 2008, at 12:55 AM, Kee Wee wrote: > > I would be very interested to follow-up this subject closely, > as we are actually performing the same kind of work as you are > looking for. > > One of the answer you got was that it is not possible to reuse a > prepared > statement from one database to another. > This answer was too quick, and did not include any explanation -> > so I would > rather prefer to ask again the question, and obtain why it is not > possible. > > I would say that the test I have performed until now make this thing > possible. > I agree that this requires to tweak a little bit the way SQLite is > working > (without recompiling anything, only using the internal members of > the opaque > structures). > > If VDBE programmers think that I am getting too deep inside the VDBE > internal structures to handle this problem, (or that it is too > risky for > data integrity), please feel free to comment vigorously on this > subject. > > The work performed was to swap Database Handles. > Following code is included in XMLRAD DacSQLite3 to address exactly > this > problem: > > procedure StmtSwapVdbe(Statement: TDISQLite3StatementHandle; NewDB: > Pointer); > var > Vdbe: PVdbe; > begin > Vdbe := PVdbe(Statement); > if Vdbe.db = NewDB then > Exit; > // Uninstall Vdbe from its actual Vdbe.db > if Vdbe.pPrev <> nil then > Vdbe.pPrev.pNext := Vdbe.pNext > else > Vdbe.db.pVdbe := Vdbe.pNext; > if Vdbe.pNext <> nil then > Vdbe.pNext.pPrev := Vdbe.pPrev; > > // Install Vdbe to the NewDB > Vdbe.db := NewDB; > if Vdbe.db.pVdbe <> nil then > Vdbe.db.pVdbe.pPrev := Vdbe; > Vdbe.pNext := Vdbe.db.pVdbe; > Vdbe.pPrev := nil; > Vdbe.db.pVdbe := Vdbe; > end; > > Once again, if any programmer of the VDBE could comment on this, I > am even > willing to pay money to get audit of the code, and get confirmation > that > this technique is valid and does not put at risk data integrity of > databases. It think this risks database corruption. Vdbe programs hard-code the schema-version number and the locations of tables and indexes in the database file. These might be different for different databases, even those with the same schema. If the table/index locations are different but the schema cookie is the same, you are risking database corruption. Also, prepared statement structures contain some pointers back to the database handle that was used to create them. Not sure what the implications of this are, but it doesn't seem safe. There might be other reasons this is dangerous too. Dan. > -- > View this message in context: http://www.nabble.com/Multiple- > databases-tp15035409p15790869.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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multiple databases
I would be very interested to follow-up this subject closely, as we are actually performing the same kind of work as you are looking for. One of the answer you got was that it is not possible to reuse a prepared statement from one database to another. This answer was too quick, and did not include any explanation -> so I would rather prefer to ask again the question, and obtain why it is not possible. I would say that the test I have performed until now make this thing possible. I agree that this requires to tweak a little bit the way SQLite is working (without recompiling anything, only using the internal members of the opaque structures). If VDBE programmers think that I am getting too deep inside the VDBE internal structures to handle this problem, (or that it is too risky for data integrity), please feel free to comment vigorously on this subject. The work performed was to swap Database Handles. Following code is included in XMLRAD DacSQLite3 to address exactly this problem: procedure StmtSwapVdbe(Statement: TDISQLite3StatementHandle; NewDB: Pointer); var Vdbe: PVdbe; begin Vdbe := PVdbe(Statement); if Vdbe.db = NewDB then Exit; // Uninstall Vdbe from its actual Vdbe.db if Vdbe.pPrev <> nil then Vdbe.pPrev.pNext := Vdbe.pNext else Vdbe.db.pVdbe := Vdbe.pNext; if Vdbe.pNext <> nil then Vdbe.pNext.pPrev := Vdbe.pPrev; // Install Vdbe to the NewDB Vdbe.db := NewDB; if Vdbe.db.pVdbe <> nil then Vdbe.db.pVdbe.pPrev := Vdbe; Vdbe.pNext := Vdbe.db.pVdbe; Vdbe.pPrev := nil; Vdbe.db.pVdbe := Vdbe; end; Once again, if any programmer of the VDBE could comment on this, I am even willing to pay money to get audit of the code, and get confirmation that this technique is valid and does not put at risk data integrity of databases. -- View this message in context: http://www.nabble.com/Multiple-databases-tp15035409p15790869.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] Multiple databases
"Mina R Waheeb" <[EMAIL PROTECTED]> wrote: > Hi, >I have few questions regarding the limitation of multiple databases with > SQLite. > > I have a large number of SQLite DB files with the same structure i > need to query them all (looking for speed), I have tried ATTACH method > and its working fine but there is a limitation on the number of > attached files > > in sqlitelimit.h > /* > ** The maximum number of attached databases. This must be at least 2 > ** in order to support the main database file (0) and the file used to > ** hold temporary tables (1). And it must be less than 32 because > ** we use a bitmask of databases with a u32 in places (for example > ** the Parse.cookieMask field). > */ > > - Is there is any way or patch to increase the number of attached > files more than 32? You could change the unsigned 32-bit integer used for the mask into an unsigned 64-bit integer. This will be tricky, though, as a lot of things will need to change. > > - is SQLite open FD to each attached database or open it on request? Actually, 3 file descriptors per attached database. > > - Once the SQLite library loaded in memory, How much the new > connection cost of the system resources mainly in the memory? That depends on the size of the database schema. > > - If the schema is standard (Will not change), Is there is anyway just > switch between the FD to avoid reparse on startup? sorry maybe this is > a stupid question but i dunno much about SQLite internals. > No. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
Thanky Mr. Kees! This help a lot. This is the way i was thinking too. I have lot things to do though, i am still learning the sqlite! Thank you once again! -- View this message in context: http://www.nabble.com/multiple-databases-tf4480719.html#a12794210 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
On Thu, 20 Sep 2007 02:03:59 -0700 (PDT), you wrote: > >Hello again! >I was really surprised and relieved to see that so many people replied to my >question! I really thank you all! >I would love to come to the conference but i am from Greece. >I am going to explain in details what i am trying to do. >I am indeed using the network simulator 2 in order to simulate a wireless >network of nodes. I want each node to have its own database which will NOT >be a copy of a local db. Each database will be different and independent for >each node. This is and my basic worry. Can i do that? >Additionally, each node will send some messages to other nodes. These >messages will trigger some 'actions' to the local dbs. Let's say that one >node nA wants to make a query to the db of the node nB. Then nA sends a >serialized message (which contain the query) to the node nB and then node nB >deserialises the received message and 'reads' the query and access its >database. This is what i want to simulate. So, can this be done? >Can i attach so many different dbs as the nodes are? >I would prefer to do it through c++ and not through Tcl. >Thank you so much! Assuming each node is an object (class instance) in the simulation program it should be possible to instantiate a database connection (open) in the constructor of the node class and cose it in the destructor. The constructor should check if the database has been initialized before, and if not, define a proper schema. The class methods for receiving messages could contain sqlite3 calls that use the database in some way. You have to take care that every database should have a unique filename, and that a specific node in the simulated network uses the same name all the time. I hope this helps, good luck. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
Hello again! I was really surprised and relieved to see that so many people replied to my question! I really thank you all! I would love to come to the conference but i am from Greece. I am going to explain in details what i am trying to do. I am indeed using the network simulator 2 in order to simulate a wireless network of nodes. I want each node to have its own database which will NOT be a copy of a local db. Each database will be different and independent for each node. This is and my basic worry. Can i do that? Additionally, each node will send some messages to other nodes. These messages will trigger some 'actions' to the local dbs. Let's say that one node nA wants to make a query to the db of the node nB. Then nA sends a serialized message (which contain the query) to the node nB and then node nB deserialises the received message and 'reads' the query and access its database. This is what i want to simulate. So, can this be done? Can i attach so many different dbs as the nodes are? I would prefer to do it through c++ and not through Tcl. Thank you so much! -- View this message in context: http://www.nabble.com/multiple-databases-tf4480719.html#a12793407 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
[EMAIL PROTECTED] wrote: There are no SQLite-specific talks or tutorials this year Well, my talk Thursday afternoon is on using an SQLite database for storing Tcl/Tk scripts which are used for displaying and modifying SQLite databases. So although your statement is strictly correct, I will be saying a lot about SQLite. I call my program GEB, because using SQLite as both the storage mechanism and object of manipulation reminded me of the "self reference at a higher level" that was a recurring theme in the book Gödel Escher Bach: An Eternal Golden Braid. Looking forward to meeting many SQLiters and Tclers, Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
I think that his project is to use simulation software to build a model of a network in software using a simulation package like NS2. It models the network by implementing algorithms, not by setting up hardware. Vitali Lovich wrote: I think you need to clarify your needs a bit, cause it seems somewhat confusing. So you have a wireless network, where all the nodes connect wirelessly to it. What is the database being used for? Is each node accessing and updating a local copy of some database (i.e. what other nodes it can see), or is it accessing a centrally managed database which all the nodes are accessing/updating. Additionally, what exactly are you trying to simulate? Are you trying to test how efficiently databases work over some kind of network (as a previous reply pointed out, this would be pointless with sqlite since sqlite is in-process and local) or are you trying to test out some kind of third algorithm where you just need to use some kind of database to store data. nadiap wrote: Hello, i am a newbie and i would like to ask if it is possible to use sqlite in order to simulate a wireless network where each node will have each own database. I mean, can i attach each node to a database? What shall i do? I am sorry if my question seems naive, but i am just learning Please help me. Thank you - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] multiple databases
nadiap wrote: Hello, i am a newbie and i would like to ask if it is possible to use sqlite in order to simulate a wireless network where each node will have each own database. I mean, can i attach each node to a database? What shall i do? I am sorry if my question seems naive, but i am just learning Please help me. Thank you Sqlite is an excellent choice for your project. You can link Sqlite into your application or use the TCL bound version and produce your simulation using TCL. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] multiple databases
Most likely if you're simulating networks where there is a db in each node then you really want to simulate network traffic based on that db protocol. SQLite is an embedded database that runs in-process and by definition has no network traffic or protocol (except if you count opening a database over a shared file server, but then you're really doing file i/o and not db i/o). I think you need a network database for this type of simulation. Additionally, I'm sure the results will be very different depending on the database since they all have their own protocol with different bandwidth and usage characteristics, so you need to take a lot of care in choosing the right database for your simulation needs and possible would want to try with a few different db engines. HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: nadiap [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 19, 2007 8:33 AM To: sqlite-users@sqlite.org Subject: [sqlite] multiple databases Hello, i am a newbie and i would like to ask if it is possible to use sqlite in order to simulate a wireless network where each node will have each own database. I mean, can i attach each node to a database? What shall i do? I am sorry if my question seems naive, but i am just learning Please help me. Thank you -- View this message in context: http://www.nabble.com/multiple-databases-tf4480719.html#a12776486 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] multiple databases
Hello, i am a newbie and i would like to ask if it is possible to use sqlite in order to simulate a wireless network where each node will have each own database. I mean, can i attach each node to a database? What shall i do? I am sorry if my question seems naive, but i am just learning Please help me. Thank you -- View this message in context: http://www.nabble.com/multiple-databases-tf4480719.html#a12776486 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
