Grr. Copy/paste error. The create statement was: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, config TEXT);
On Thu, Apr 17, 2008 at 5:20 PM, Scott Hess <[EMAIL PROTECTED]> wrote: > 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
