[CALUG] Database question

[Jason C. Miller]

On second look, I don't think that the transaction is the route that I'm
looking for.  It has the commit() and rollback() functionality, but still
requires that individual records be update()d, insert()ed, and delete()d (if
I'm understanding it correctly).  I'm looking to minimize database
accesses.  Here's some pseudocode for the mechanism that I'm looking to
employ...

---------------------------------------------------------------------------------------------------------
app.getUsers()   // open() database, select * from user, close(), store
results in memory

app.addUser("me");                     // changes values in memory
app.dropUser("you");                   // changes values in memory
app.updateUser("whatever");       // changes values in memory

if ( app.changes_are_good() )
  {
    app.commitChanges()
    // open() database, "sync up" with values modified in memory, close()
  }
---------------------------------------------------------------------------------------------------------

Basically, I'm looking to somehow pass the contents of a table into the
database and have it sync between that and its stored table.  I
know...sounds waaayyy too convenient, but ya never know.  :)  Are there any
tricks to do something like this?

                                    -jason "databases byte" miller







On 8/21/06, Hugh Brown <brownclan at gmail.com> wrote:
>
>
>
> On 8/21/06, Jason C. Miller <jason.c.miller at gmail.com> wrote:
> >
> > Does anybody know of any queries or tricks to update an entire table in
> > the
> > fewest operations possible?  Before anyone freaks out and starts yelling
> > UPDATE() at me, let me explain the scenario and also mention that I'm
> > hardly
> > a DB/SQL guru.  I've always known just enough to get by.
> >
> > Here's the scenario...
> >
> > Table A: (users)
> >
> > id | name | location | level
> > --------------------------------------
> > 0 | nobody | nowhere | 3
> > 1 | somebody | somewhere | 2
> > 2 | anybody | anywhere | 1
> >     .
> >     .
> >     .
> >
> > When a person makes changes to these values from my interface, they are
> > actually doing it in some structures in memory that are initially an
> > exact
> > copy of the database table.  Once they are happy with their changes,
> > they
> > will run a commit() which I will later implement to go through and make
> > all
> > of the changes to the table from the changes that were made to the copy
> > in
> > memory.  Normally, I would do this incrementally by writing a loop that
> > would check for insert()s, delete()s, and update()s.  However, if
> > there's
> > some SQL voodoo that I could use to do some kind of a sync() between the
> >
> > virtual table and the database table, I'd much prefer that.  Does
> > something
> > like this exist?  I did some searching on the web but, as usual, don't
> > know
> > the EXACT words to search for as not to get bombarded with
> > 90,000,000,000
> > irrelevant results.
> >
> > Ideas?
> >
> >                                                                  -jason
> >
> > P.S. I'm using SQLite
>
>
>
>
> Up until the "using SQLite," I was thinking "postgres and BEGIN
> TRANSACTION;"
>
> With transaction support, you do your changes and if something is borked
> then postgres complains and the transaction fails.  You then  run
> "ROLLBACK;" and the changes are removed.  If everything goes well, you run
> "COMMIT;" and the changes are made.
>
> With SQLite, I think you are talking about writing transaction support.
> I'm not a DBA guru either, but I think maybe it's time to move up to a
> heavier weight db engine.
>
> Hugh
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://calug.com/pipermail/lug/attachments/20060821/d1a00a3e/attachment.html