Scott, Because of the issues that you have raised I realized that multiple configuration commands rolled into one transaction is not a good idea and is not necessary. The router will act on a single command from any CLI session. As far as command action goes it will be connection less towards CLI sessions. When any user asks to show configuration I will show running configuration to that user. I think a complicated design is a bad design.
Thanks! -Alex On Thu, Apr 17, 2008 at 8: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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
