Login | Register
My pages Projects Community openCollabNet

Discussions > dev > Re: Current with Oracle backend

Project highlights: Stable Version: 1.6.1, Development Version: 1.7.6

current
Discussion topic

Back to topic list

Re: Current with Oracle backend

Reply

Author jpportz
Full name Jeremy Portzer
Date 2006-09-06 07:35:26 PDT
Message Jack is correct in that within Oracle, the interim SQL statements that
haven't been committed yet, will still appear properly in the database as
long as you're connected to the same session.

However the issue of checking for just-inserted data is a bit difficult in
Oracle as there is no autonumber type to be used for primary key ID
numbers. Typically the workaround is to use sequences in conjunction with
a stored procedure that increments the sequence, then uses this sequence
number in the insert statement. The SP then returns the primary key back
to the calling statement, which can be used in subsequent operations.

I haven't followed much about Current's database structure lately, so
maybe this isn't feasible, but I hope this is useful. Let me know if you
want sample code for the stored procedure approach.

--Jeremy

On Wed, 6 Sep 2006, Jack Neely wrote:

> The mailing lists please? Mostly because folks out here know more SQL than I.
>
> That entire code path is done in one commit so that if we encounter a
> file system error (out of space, permissions, whatever) we can
> rollback to the last known good state. Otherwise, the database is
> possibly corrupt. So however the database code works, that needs to
> be the final result.
>
> My understanding of transactions (which may be flawed) is that the
> thread that is modifying the database via a transaction will be the
> only thread to *see* those changes until it is committed. Otherwise,
> how does one insert foregn keys into other tables?
>
> Jack
>
> On 9/5/06, Jared Greenwald <jared.greenwald@​oracle.com> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Jack,
> >
> > I'm running into some fundamental issues with the sql code that I was
> > wondering if you could shed some light on.
> >
> > When running the cadmin scan calls, the code is running through adding
> > entries to the package, rpm, dependency (etc.) tables. The problem I'm
> > seeing that that all of the updates to all of these tables seem to be
> > rolled up into a huge transaction that is going to be committed all at
> > once. This would be ok, but there are points in the path of inserting
> > data that you check for data that you just inserted. This is a problem
> > without interim commits, as the data won't always show up.
> >
> > Case and point is adding to the package table in _insertPackageTable
> > (take a look, you'll see what I mean). Another place is
> > _insertRpmTable. For me, I had to add commits after each of the insert
> > sql statements in order for me to be able to find the correct _id that I
> > just inserted.
> >
> > I understand the commit as a whole method, but I'm not sure there's any
> > way around these interim commits.
> >
> > Thoughts?
> >
> > - -Jared
> >
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: dev-unsubscribe@curr​ent.tigris.org
> For additional commands, e-mail: dev-help at current dot tigris dot org
>
>
>
>

--
/-------------------​--------------------​--------------------​----------\
| Jeremy Portzer jeremyp at pobox dot com trilug.org/~jeremy |
| GPG Fingerprint: 712D 77C7 AB2D 2130 989F E135 6F9F F7BC CC1A 7B92 |
\------------------​--------------------​--------------------​-----------/

« Previous message in topic | 5 of 6 | Next message in topic »

Messages

Show all messages in topic

Re: Current with Oracle backend theslack Jack Neely 2006-09-06 06:39:39 PDT
     Re: Current with Oracle backend Pauline Middelink <middelink at polyware dot nl> Pauline Middelink <middelink at polyware dot nl> 2006-09-06 08:05:31 PDT
     Re: A bit confused theslack Jack Neely 2006-09-07 19:01:05 PDT
         Re: A bit confused Jared Greenwald <greenwaldjared at gmail dot com> Jared Greenwald <greenwaldjared at gmail dot com> 2006-09-07 20:49:52 PDT
     Re: Current with Oracle backend jpportz Jeremy Portzer 2006-09-06 07:35:26 PDT
         Re: Current with Oracle backend jpportz Jeremy Portzer 2006-09-12 18:11:07 PDT
Messages per page: