Re: [sqlite] how to select uncomitted rows?
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.
Re: [sqlite] how to select uncomitted rows?
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 me
Re: [sqlite] how to select uncomitted rows?
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 >>> >> impl
Re: [sqlite] how to select uncomitted rows?
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 <[
Re: [sqlite] how to select uncomitted rows?
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 t
Re: [sqlite] how to select uncomitted rows?
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
>> >> >
>> >>
Re: [sqlite] how to select uncomitted rows?
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
Re: [sqlite] how to select uncomitted rows?
Yap I was wrong about triggers. Triggers are part of the same connection. So I will try your suggestions. I will let you know how I made out. And thanks so much for clearing my mistakes. -Alex On Thu, Apr 17, 2008 at 6:06 PM, Alex Katebi <[EMAIL PROTECTED]> wrote: > I remember trying it before but I will try it again. Maybe I was wrong. I > will let you know. > Thanks! > > On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> > wrote: > > > Alex Katebi wrote: > > > But triggers don't trigger until commit. > > > > > > > That is not true. > > > > Trigger code executes inline with the statement that caused the trigger > > to fire. > > > > Try a few triggers with the command line shell to convince yourself. > > > > Dennis Cote > > ___ > > 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] how to select uncomitted rows?
I remember trying it before but I will try it again. Maybe I was wrong. I will let you know. Thanks! On Thu, Apr 17, 2008 at 4:43 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > But triggers don't trigger until commit. > > > > That is not true. > > Trigger code executes inline with the statement that caused the trigger > to fire. > > Try a few triggers with the command line shell to convince yourself. > > Dennis Cote > ___ > 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] how to select uncomitted rows?
Alex Katebi wrote: > But triggers don't trigger until commit. > That is not true. Trigger code executes inline with the statement that caused the trigger to fire. Try a few triggers with the command line shell to convince yourself. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to select uncomitted rows?
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
Re: [sqlite] how to select uncomitted rows?
Another approach could be to create an in-memory database (and in in-
memory table, like CREATE TABLE last_transaction(id INTEGER);)
and after each write operation save the rowid of the row using
sqlite3_last_insert_rowid (in C) or using SELECT last_insert_rowid();
(SQL) into that table.
---
Marco Bambini
http://www.sqlabs.net
http://www.sqlabs.net/blog/
http://www.sqlabs.net/realsqlserver/
On Apr 17, 2008, at 9:43 PM, Alex Katebi 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
Re: [sqlite] how to select uncomitted rows?
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
Re: [sqlite] how to select uncomitted rows?
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
Re: [sqlite] how to select uncomitted rows?
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
Re: [sqlite] how to select uncomitted rows?
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] how to select uncomitted rows?
Is there a way to select rows that have not been committed yet? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
