[CALUG] Database question

[Frilot, Michael]

Why, what do you estimate is the average number of changes to your
table?  Add a user, remove a user... What kind of volume will you have
to justify protecting the database from doing what a database does?

My concern would be loading the entire table into memory all the time...
That is a greater database load. (unneeded reads)

My other concern would be records changing without you having a write
lock to prevent changes occurring while your app is up.

Best,

Michael

 

-----Original Message-----
From: lug-bounces at calug.com [mailto:lug-bounces at calug.com] On Behalf Of
Jason C. Miller
Sent: Monday, August 21, 2006 11:07 AM
To: Hugh Brown
Cc: lug at calug.com
Subject: Re: [CALUG] Database question

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
_______________________________________________
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