Just to be clear on "try it out", I mean something like the following, where A) is in one shell, and B) in another.
A) ...> ./sqlite3 test.db A) sqlite> CREATE TABLE t (id INTEGER AUTOINCREMENT PRIMARY KEY NOT NULL, config TEXT); B) ...> ./sqlite3 test.db B) sqlite> BEGIN; A) sqlite> BEGIN; A) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection a'); B) sqlite> INSERT INTO t (id, config) VALUES (null, 'connection b'); B) SQL error: database is locked -scott On Thu, Apr 17, 2008 at 5:17 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > What will happen if you use BEGIN is that multiple users can get into > the configuration mode, but once one user gets past BEGIN and runs > anything which updates the database, the updates in other transactions > will start throwing SQLITE_LOCKED. Spin up two sqlite3 command-line > tools against the same database and check it out. > > If you use BEGIN IMMEDIATE, then this problem won't occur, because > multiple threads can't get past BEGIN IMMEDIATE on the same database > in the first place :-). > > What you have is basically a revision-control problem. If you let > multiple users configure at the same time, you're going to handle > merging the config changes in a sensible way and handle conflicts. > > -scott > > > On Thu, Apr 17, 2008 at 5:08 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: >> Scott, >> >> Every user will have thier own sqlite connection. So multiple users are >> allowed for configuration. There will be one router connection to actually >> act on the commited configurations. The router will act on >> individual configuration rows. The router and the users interact with each >> other via the database file only. They run on seperate processes. All the >> router sees is configuration rows being inserted deleted or updated by >> whomever. It does not care. So here is my next question. If I have temporary >> triggers for the CLI users to keep track of the uncommited rows. Then I have >> another temoprary trigger for the router to act on the configurations after >> being committed, would this work? Would the temporary trigger in the router >> connection actually trigger? I guess I need to try this out. >> >> This is my own home project. I am my own boss. Once I have my design >> figured out I will post it for analysis. >> >> Thanks, >> -Alex >> >> >> On Thu, Apr 17, 2008 at 7:26 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >> >>> OK. I suspect that you might find exposing the SQLite transaction >>> semantics as part of your user interface may be ... unsatisfactory. >>> For instance, by keeping a long-lived transaction in this way, you >>> cannot set any of the _other_ data in the config and commit it. This >>> would include other users, so, for instance, while one user is >>> configuring something really complex, like firewall rules, another >>> user would not be able to set the timezone, or turn on logging, or >>> something like that. I don't know, this may be satisfactory, but it >>> seems like a regrettable thing to design into the system at such a low >>> level (not letting multiple people configure so that they don't screw >>> up is good, but not allowing it just because your design didn't allow >>> it, less good). >>> >>> As an alternative, you might consider layering your config-management >>> over something like the undo/redo example (*). Since this is more >>> explicit (_you_ craft the structures in terms of SQLite, rather than >>> relying on SQLite's internal semantics), when upper management comes >>> to you with some crazy feature request which does not conform to the >>> SQL transaction model, you'll be able to change things without too >>> much pain. >>> >>> -scott >>> >>> (*) http://www.sqlite.org/cvstrac/wiki?p=UndoRedo >>> >>> >>> On Thu, Apr 17, 2008 at 3:56 PM, Alex Katebi <[EMAIL PROTECTED]> >>> wrote: >>> > I am glad you asked. I am designing an interactive command line >>> > interface to an ip router. A user will begin a transaction and start >>> > configuring. At any time he can query for his configurations since the >>> > begining of the transaction. When he is satisfied with his configuration >>> he >>> > will commit the configuration. After this his query should show nothing >>> > until he begins another transaction. Also he might press the ?mark key >>> on >>> > his keyboard at anytime for help information or tab key for automatic >>> > command token completion. >>> > >>> > So I will have to know what are the list of commands since the beginning >>> > of his transaction. >>> > >>> > >>> > >>> > On Thu, Apr 17, 2008 at 3:53 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >>> > >>> >> I don't mean in a separate database table - I mean in an in-memory >>> >> hashtable or array or something of the sort. Depending on what the >>> >> real goal you're trying to accomplish is, you might use triggers to >>> >> call custom function to accomplish this. >>> >> >>> >> You presumably desire to get this information in the interests of >>> >> implementing a solution to a problem. You should perhaps post asking >>> >> for suggestions on how to solve the problem. I think the question >>> >> itself probably indicates that there's a disconnect in how you're >>> >> trying to model the problem, but without knowing what the problem is, >>> >> it's hard to do much. >>> >> >>> >> -scott >>> >> >>> >> On Thu, Apr 17, 2008 at 12:43 PM, Alex Katebi <[EMAIL PROTECTED]> >>> >> wrote: >>> >> > The reason I did not keep track in a seperate table was because I >>> wanted >>> >> to >>> >> > do it using triggers. But triggers don't trigger until commit. >>> >> > >>> >> > On Thu, Apr 17, 2008 at 3:36 PM, Scott Hess <[EMAIL PROTECTED]> wrote: >>> >> > >>> >> >> Until the data is committed, it's not really in the database. If >>> you >>> >> >> crash, it will be rolled back. So if it's really important to know >>> >> >> what data has been written to the database but not committed, why >>> >> >> don't you just track what you're writing to the database in an >>> >> >> in-memory data structure of some sort? Or, to save space, just >>> track >>> >> >> the rowid of the rows you modify. >>> >> >> >>> >> >> -scott >>> >> >> >>> >> >> >>> >> >> On Thu, Apr 17, 2008 at 12:33 PM, Alex Katebi <[EMAIL PROTECTED] >>> > >>> >> >> wrote: >>> >> >> > Hi Richard, >>> >> >> > >>> >> >> > create table t1 (name); >>> >> >> > insert into t1 values ('Alex'); >>> >> >> > begin; >>> >> >> > insert into t1 values ('Richard'); >>> >> >> > select * from t1; >>> >> >> > >>> >> >> > How can I select only the second row in the above example? >>> >> >> > If there is not an easy way to do this I would probably have to >>> use >>> >> >> another >>> >> >> > connection then diff the two selects right? >>> >> >> > >>> >> >> > Thanks, >>> >> >> > -Alex >>> >> >> > >>> >> >> > >>> >> >> > >>> >> >> > >>> >> >> > >>> >> >> > On Thu, Apr 17, 2008 at 2:38 PM, D. Richard Hipp <[EMAIL PROTECTED]> >>> >> wrote: >>> >> >> > >>> >> >> >> >>> >> >> >> On Apr 17, 2008, at 2:35 PM, Alex Katebi wrote: >>> >> >> >> > Is there a way to select rows that have not been committed yet? >>> >> >> >> > >>> >> >> >> >>> >> >> >> No. SQLite doesn't really commit rows. It commits pages. A >>> >> >> >> single page might hold multiple rows, only some of which might >>> >> >> >> have changed. Or a single row might span multiple pages. >>> >> >> >> >>> >> >> >> >>> >> >> >> 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 >>> >> >> > >>> >> >> _______________________________________________ >>> >> >> 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 >>> 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
