[CALUG] Database question

[Daniel Fox]

If I understand you correctly, what you need is to compare the current 
status of your table (in memory) with the original (back in the db) and 
update the db copy from the memory copy. There is stuff out there that 
includes that functionality, but what I know about is proprietary (sp?).

You could just walk the table in memory and issue a complete update on each 
row. Easy to implement; wasteful of bandwidth and cycles. The alternate 
would be to keep a "shadow" copy of the data as fetched, and do the compare. 
More memory, more cycles, and a lot more work to implement. Which is to say 
"no, I don't know of anything easy."

If you were in Java you could use Hibernate; that'd hide a lot of the 
complexity. You also didn't address whether you'd need to insert new data as 
well as update existing. That'd introduce further complexity. Are there 
multiple users all hitting the db at the same time? "Dirty read" problems 
(where you and another user want to perform different updates on the same 
row) are a pain.

Hope I've helped, at least to frame the problem a little better. It's just 
that I know so little about Linux, so when you walk in my neighborhood I'm 
eager to help. Your other correspondents are correct in that the lack of 
transactional support and row locking are limitations.

--dan fox


>From: "Jason C. Miller" <jason.c.miller at gmail.com>
>To: "Hugh Brown" <brownclan at gmail.com>
>CC: lug at calug.com
>Subject: Re: [CALUG] Database question
>Date: Mon, 21 Aug 2006 10:45:03 -0400
>
>Unfortunately, given operational constraints, SQLite is the only option.
><:(
>
>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/58740c9b/attachment.html
>_______________________________________________
>Columbia, Maryland Linux User's Group (CALUG) mailing list
>CALUG Website: http://www.calug.com
>Email postings to: lug at calug.com
>Change your list subscription options: 
>http://calug.com/mailman/listinfo/lug