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

Hide all messages in topic

All messages in topic

Re: Current with Oracle backend

Reply

Author jpportz
Full name Jeremy Portzer
Date 2006-09-12 18:11:07 PDT
Message Sorry, this message got caught in a queue until now. Please ignore.

--Jeremy

On Wed, 6 Sep 2006, Jeremy Portzer wrote:

>
> 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 |
\------------------​--------------------​--------------------​-----------/

Re: A bit confused

Reply

Author Jared Greenwald <greenwaldjared at gmail dot com>
Full name Jared Greenwald <greenwaldjared at gmail dot com>
Date 2006-09-07 20:49:52 PDT
Message Yea, the directory listed in the current.conf file is /export/current.
 After originally encountering this problem, I ran chown -R
apache:apache /export/current ; chmod -R 777 /export/current

I would have expected this to clear up the problem.

-Jared

On 9/7/06, Jack Neely <jjneely at gmail dot com> wrote:
> The path you gave should be where Current would be putting the
> files...not reading them from...if I understand what your problem is.
>
> Check that apache has writes the change into all the directories above
> and that SELinux has a knife in it.
>
> Jack
>
> On 9/7/06, Jared Greenwald <jared.greenwald@​oracle.com> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > So, I've almost worked through re-coding all of the sql statements to
> > work with Oracle in the scan path, but I've run into a problem that I
> > don't seem to understand.
> >
> > current.log:
> > ....
> > Sep 07 13:41:26 CRITICAL: File
> > "/usr/lib/python2.3/​site-packages/curren​t/db/currentdb.py", line 275, in
> > _scanFilesystem
> > os.path.walk(row[0], filesystemWalker, fs_set)
> > Sep 07 13:41:26 CRITICAL: File "/usr/lib/python2.3/​posixpath.py", line
> > 290, in walk
> > walk(name, func, arg)
> > Sep 07 13:41:26 CRITICAL: File "/usr/lib/python2.3/​posixpath.py", line
> > 282, in walk
> > func(arg, top, names)
> > Sep 07 13:41:26 CRITICAL: File
> > "/usr/lib/python2.3/​site-packages/curren​t/db/currentdb.py", line 256, in
> > filesystemWalker
> > raise exception.CurrentDB("Cannot read %s" % filename)
> > Sep 07 13:41:26 CRITICAL: CurrentDB: Cannot read
> > php-oracle-4.3.9-3.10.src.rpm
> > Sep 07 13:41:26 DEBUG2: API Result = <Fault 1000: 'Cannot read
> > php-oracle-4.3.9-3.1​0.src.rpm'>
> > Sep 07 13:41:26 WARNING: Fault: <Fault 1000: 'Cannot read
> > php-oracle-4.3.9-3.1​0.src.rpm'>
> >
> > # ls -l /export/current/www/​oracle_add_on_i386
> > ...
> > - -rwxrwxrwx 1 apache apache 5014715 Aug 28 13:42
> > php-oracle-4.3.9-3.10.src.rpm
> >
> >
> > I don't see what the permission problem is...
> >
> > - -Jared
> > -----BEGIN PGP SIGNATURE-----
> > Version: GnuPG v1.4.5 (GNU/Linux)
> > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
> >
> > iD8DBQFFAIVBu4z7Xptg​TUYRAmuJAJsGjT9jCPuY​0ZuAzBLrHyx3h+KUQwCe​LCez
> > r/oO2hP/hbQXLlFc1dSEOrM=
> > =wSXg
> > -----END PGP SIGNATURE-----
> >
>
> --------------------​--------------------​--------------------​---------
> To unsubscribe, e-mail: dev-unsubscribe@curr​ent.tigris.org
> For additional commands, e-mail: dev-help at current dot tigris dot org
>
>

Re: A bit confused

Reply

Author theslack
Full name Jack Neely
Date 2006-09-07 19:01:05 PDT
Message The path you gave should be where Current would be putting the
files...not reading them from...if I understand what your problem is.

Check that apache has writes the change into all the directories above
and that SELinux has a knife in it.

Jack

On 9/7/06, Jared Greenwald <jared.greenwald@​oracle.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> So, I've almost worked through re-coding all of the sql statements to
> work with Oracle in the scan path, but I've run into a problem that I
> don't seem to understand.
>
> current.log:
> ....
> Sep 07 13:41:26 CRITICAL: File
> "/usr/lib/python2.3/​site-packages/curren​t/db/currentdb.py", line 275, in
> _scanFilesystem
> os.path.walk(row[0], filesystemWalker, fs_set)
> Sep 07 13:41:26 CRITICAL: File "/usr/lib/python2.3/​posixpath.py", line
> 290, in walk
> walk(name, func, arg)
> Sep 07 13:41:26 CRITICAL: File "/usr/lib/python2.3/​posixpath.py", line
> 282, in walk
> func(arg, top, names)
> Sep 07 13:41:26 CRITICAL: File
> "/usr/lib/python2.3/​site-packages/curren​t/db/currentdb.py", line 256, in
> filesystemWalker
> raise exception.CurrentDB("Cannot read %s" % filename)
> Sep 07 13:41:26 CRITICAL: CurrentDB: Cannot read
> php-oracle-4.3.9-3.10.src.rpm
> Sep 07 13:41:26 DEBUG2: API Result = <Fault 1000: 'Cannot read
> php-oracle-4.3.9-3.1​0.src.rpm'>
> Sep 07 13:41:26 WARNING: Fault: <Fault 1000: 'Cannot read
> php-oracle-4.3.9-3.1​0.src.rpm'>
>
> # ls -l /export/current/www/​oracle_add_on_i386
> ...
> - -rwxrwxrwx 1 apache apache 5014715 Aug 28 13:42
> php-oracle-4.3.9-3.10.src.rpm
>
>
> I don't see what the permission problem is...
>
> - -Jared
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFFAIVBu4z7Xptg​TUYRAmuJAJsGjT9jCPuY​0ZuAzBLrHyx3h+KUQwCe​LCez
> r/oO2hP/hbQXLlFc1dSEOrM=
> =wSXg
> -----END PGP SIGNATURE-----
>

Re: Current with Oracle backend

Reply

Author Pauline Middelink <middelink at polyware dot nl>
Full name Pauline Middelink <middelink at polyware dot nl>
Date 2006-09-06 08:05:31 PDT
Message On Wed, 06 Sep 2006 around 09:39:39 -0400, 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?

I too have a hard time understanding this. The intermediate commits
play havoc with the intended result as Jack describe above. For
all intent and purpose the process doing the transaction should have
a clean view of all inserted data.

<pauze>

If what was said is true, my PACKAGE table should have duplicates when a
seach failed to turn up the requested data and so _insertPackageTable
will do a second insert. However, settingi a unique index over all fields
in PACKAGES results in no problems, thus no duplicates. (This database
has 108 machines and 9228 unique packages, so any problems would have
turned up i think.)

The only thing you must not do is run 2 scans at the same time. If
that would happen, and one of them should roll back, the other one
is in deep, deep trouble. (dangling package_id's)

Can you give more information about the database engine you are using?
I myself use mysql 4.1 with InnoDB, and I think Jack uses sqlite.

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

    Met vriendelijke groet,
        Pauline Middelink
--
GPG Key fingerprint = 2D5B 87A7 DDA6 0378 5DEA BD3B 9A50 B416 E2D0 C3C2
For more details look at my website http://www.polyware.​nl/~middelink
Attachments

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 |
\------------------​--------------------​--------------------​-----------/

Re: Current with Oracle backend

Reply

Author theslack
Full name Jack Neely
Date 2006-09-06 06:39:39 PDT
Message 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
>
Messages per page: