Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Paul Makepeace

On Fri, Apr 27, 2001 at 07:57:04AM +0100, Piers Cawley wrote:
 David Cantrell [EMAIL PROTECTED] writes:
  Unfortunately no.  You'd have to:
LOCK the table
SELECT the maximum id currently in use and add one
INSERT with that id
UNLOCK the table
  so all your other queries will block until the table is unlocked.
 
 Create a 'seq' table associated with each table that needs
 autoincrement.
 
 LOCK that table.
 SELECT the one entry that is the next value in the sequence.
 UPDATE the entry with an incremented value.
 UNLOCK the sequence table
 
 INSERT into data table using the id you got from the other table.
 Which looks to me like it should be faster.

The real answer is since you are using a database you care about speed
so spend three minutes reading the docs/google for your particular
database vendor and implement whatever strategy is suggested
(AUTO_INCREMENT, ROWID, oid, SEQUENCE, before-row-insert triggers,
etc).

Paul



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Robin Szemeti

On Fri, 27 Apr 2001, you wrote:

 The real answer is since you are using a database you care about speed
 so spend three minutes reading the docs/google for your particular
 database vendor and implement whatever strategy is suggested
 (AUTO_INCREMENT, ROWID, oid, SEQUENCE, before-row-insert triggers,
 etc).

but of course .. however the topic was (somewhere along the thread)
related to portable methods to try and keep from having to change all the
SQL between different db version.

Anyway .. for 90% of web apps its its a 100:1 read/write ratio .. lots of
people browse .. occasionally one of em places a bid/ makes a purchase so
the blocking anly occurs occasionally, ... if you do it sensibly (get all
the data ready .. {lock, get max, insert, release}  the lock period is
tiny. and not really an issue.

however I stll prefer my method (implement it as a 'library' wrapper
class on DBI with the appropriate (AUTO_INCREMENT, ROWID, oid, SEQUENCE
or whatevr) technique and a library::insert($sql,@args) method) and then
you just have to plug in the approprate wrapper between your app and
todays choice of DB and in theory the app can stay the same.

-- 
Robin Szemeti

The box said requires windows 95 or better
So I installed Linux!



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Paul Makepeace

On Fri, Apr 27, 2001 at 09:38:45AM +0100, Robin Szemeti wrote:
 but of course .. however the topic was (somewhere along the thread)
 related to portable methods to try and keep from having to change all the
 SQL between different db version.

Why do this? Unless you're using the db in a toy capacity (which is
fair enough: see PHP) it's like using Java when you could be using
custom DSP hardware.

[side note: I did just see a bizarre thread in macosx-dev where
one guy claimed his FFT code was executing faster in Java than C
because its interpreter used runtime info to optimize it. Search on
'informal benchmarks']

 the blocking anly occurs occasionally, ... if you do it sensibly (get all
 the data ready .. {lock, get max, insert, release}  the lock period is
 tiny. and not really an issue.

There are enough assumptions here to make me again suggest you use
a vendor-specific route :-)

 however I stll prefer my method (implement it as a 'library' wrapper
 class on DBI with the appropriate (AUTO_INCREMENT, ROWID, oid, SEQUENCE
 or whatevr) technique and a library::insert($sql,@args) method) and then
 you just have to plug in the approprate wrapper between your app and
 todays choice of DB and in theory the app can stay the same.

If it was that simple, someone would've done it -- DBI is a very
mature and competent module. Go check out Automatic Key or Sequence
Generation in the DBD driver feature summaries and you'll see why
it's hard to encapsulate this: they're all sooo different.

Paul



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Robin Szemeti

On Fri, 27 Apr 2001, you wrote:
 On Fri, Apr 27, 2001 at 09:38:45AM +0100, Robin Szemeti wrote:
  but of course .. however the topic was (somewhere along the thread)
  related to portable methods to try and keep from having to change all the
  SQL between different db version.
 
 Why do this? Unless you're using the db in a toy capacity (which is
 fair enough: see PHP) it's like using Java when you could be using
 custom DSP hardware.

well somewhere along the thread the topic was keeping your db and app
sorta independent (ala tangram) for the 'we like it .. does it work on
Postgres as well as MySQL' days that come along and you'd rather not
change too much... but agreed for serious heavy duty work a fienly tuned
db schema using the best bits of whatever db you happen to have is the
way to go. 

 
 [side note: I did just see a bizarre thread in macosx-dev where
 one guy claimed his FFT code was executing faster in Java than C
 because its interpreter used runtime info to optimize it. Search on
 'informal benchmarks']

uh huh .. but he's a Java programmer .. his C could be *REALLY* bad ;) ..
favourite Java quote 'If javas garbage collector is damn good, how come
the whole thing doesn't delete itself upon execution?'

  the blocking anly occurs occasionally, ... if you do it sensibly (get all
  the data ready .. {lock, get max, insert, release}  the lock period is
  tiny. and not really an issue.
 
 There are enough assumptions here to make me again suggest you use
 a vendor-specific route :-)

yes .. but the verndor specific code lives in the wrapper class ...

 
  however I stll prefer my method (implement it as a 'library' wrapper
  class on DBI with the appropriate (AUTO_INCREMENT, ROWID, oid, SEQUENCE
  or whatevr) technique and a library::insert($sql,@args) method) and then
  you just have to plug in the approprate wrapper between your app and
  todays choice of DB and in theory the app can stay the same.
 
 If it was that simple, someone would've done it -- DBI is a very
 mature and competent module. Go check out Automatic Key or Sequence
 Generation in the DBD driver feature summaries and you'll see why
 it's hard to encapsulate this: they're all sooo different.

sure .. but with a wrapper that implements a vendor specific technique
and say  a library_mysql, library_postgres etc I just plug in the
appropriate one between the app and DBI and it works for me ...its just
another layer of (plugable) abstraction.  doesnt seem to slow it down
that much ...
(and if speed was that imoprtant I'd do it in C++[1] anyway :)


[1] .. well except I'm crap at it so it probably wouldnt work .. come to
think of it some of my perl doesnt work .. hmmm.

-- 
Robin Szemeti

The box said requires windows 95 or better
So I installed Linux!



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Leon Brocard

Paul Makepeace sent the following bits through the ether:

 If it was that simple, someone would've done it -- DBI is a very
 mature and competent module

That's the problem. It seems like people aren't content with writing
their own, slightly different, templating system, and have moved onto
many different DBI abstraction layers to cope with all databases at
the same time. The OO-DB persistence modules all have their own way of
doing it too...

Leon
-- 
Leon Brocard.http://www.astray.com/
Iterative Software..http://yapc.org/Europe/

... Did anyone see my lost carrier?



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Robin Szemeti

On Fri, 27 Apr 2001, you wrote:
 Paul Makepeace sent the following bits through the ether:
 
  If it was that simple, someone would've done it -- DBI is a very
  mature and competent module
 
 That's the problem. It seems like people aren't content with writing
 their own, slightly different, templating system, and have moved onto
 many different DBI abstraction layers to cope with all databases at
 the same time. The OO-DB persistence modules all have their own way of
 doing it too...

I know .. I know .. damm programmers keep messing with things until they
break them ...

-- 
Robin Szemeti

The box said requires windows 95 or better
So I installed Linux!



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Mark Fowler

On Fri, 27 Apr 2001, Robin Szemeti wrote:
 On Fri, 27 Apr 2001, someone who Robin's attrib to fscked up wrote:

  [side note: I did just see a bizarre thread in macosx-dev where
  one guy claimed his FFT code was executing faster in Java than C
  because its interpreter used runtime info to optimize it. Search on
  'informal benchmarks']

 uh huh .. but he's a Java programmer .. his C could be *REALLY* bad ;) ..
 favourite Java quote 'If javas garbage collector is damn good, how come
 the whole thing doesn't delete itself upon execution?'

Don't see why this isn't possible.  The idea is that you factor out *all*
really unlikely cases (how you know this is based on past performance) and
catch them all with some simple test.  Then you (more expensively, but who
cares since this happens only once in a blue moon) deal with it and work
out exactly what was the problem.

Another example, you could use a processor level exception to catch those
pesky divide by zero errors (which is very expensive if it fires, but much
much cheaper each time round than explictly checking it.)  The advantage is
that a compiler doesn't have this information lying around - you can't
tell from the code how often cases come up;  You need to profile at run
time as you go.

Sorry if this is all babble.  I haven't had much sleep.

Later.

Mark.

-- 
 who *still* hasn't got round to getting a new .signature




Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Leon Brocard

Mark Fowler sent the following bits through the ether:

 Don't see why this isn't possible

Indeed. All you need to do this is:

  o bytecode and a virtual machine
  o some way of instrumenting said VM to save interesting info
  o ability to rewrite bytecode on fly

OK, I give you Perl, the Perl debugger, and B::Generate. First one to
optimise Perl code (maybe replacing bits of Perl with XS on the fly?)
gets a pat on the back.

Leon, sleeep
-- 
Leon Brocard.http://www.astray.com/
Iterative Software..http://yapc.org/Europe/

... I don't suffer from insanity. I enjoy every minute of it



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Robin Szemeti

On Fri, 27 Apr 2001, you wrote:
 On Fri, 27 Apr 2001, Robin Szemeti wrote:
  On Fri, 27 Apr 2001, someone who Robin's attrib to fscked up wrote:
 
   [side note: I did just see a bizarre thread in macosx-dev where
   one guy claimed his FFT code was executing faster in Java than C
   because its interpreter used runtime info to optimize it. Search on
   'informal benchmarks']
 
  uh huh .. but he's a Java programmer .. his C could be *REALLY* bad ;) ..
  favourite Java quote 'If javas garbage collector is damn good, how come
  the whole thing doesn't delete itself upon execution?'
 
 Don't see why this isn't possible.  The idea is that you factor out *all*
 really unlikely cases (how you know this is based on past performance) and
 catch them all with some simple test.  Then you (more expensively, but who
 cares since this happens only once in a blue moon) deal with it and work
 out exactly what was the problem.

my basic point was: that given that the FFT code uses similar technicques
in both its C and Java variants the C variant will win hands down.  if
you're going to throw in completley new techniques then sure you could
skew it the other way around .. but in the end (assuming that both
codesets use similar basic principles) you will not beat the speed of C
with anything other than hand optimised assembler. Now that is a fact.

-- 
Robin Szemeti

The box said requires windows 95 or better
So I installed Linux!



Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Mike Jarvis

Friday, April 27, 2001, 1:09:05 PM, Robin Szemeti wrote:
RS but in the end (assuming that both
RS codesets use similar basic principles) you will not beat the speed of C
RS with anything other than hand optimised assembler. Now that is a fact.

Sounds much more like a function of the compiler to me.  A
really good Fortran compiler would turn out faster code than a bad c
compiler.

Granted, it is a *lot* easier to write a fast c compiler, since you're
already halfway to assembler anyway.

Now if you're just comparing any fully compiled language with java,
you're absolutely right.

-- 
mike





Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Mike Jarvis

Friday, April 27, 2001, 2:20:21 PM, Paul Makepeace wrote:

PM On Fri, Apr 27, 2001 at 01:51:38PM -0500, Mike Jarvis wrote:
 Sounds much more like a function of the compiler to me.  A
 really good Fortran compiler would turn out faster code than a bad c
 compiler.

PM But that isn't saying much. People tend not to use really bad things if
PM they can help it.

You haven't been to the same client sites I have if you believe that
statement.  It always amazes me what moronic things businesses will
insist that you work with.

 Now if you're just comparing any fully compiled language with java,
 you're absolutely right.

PM Hmm.
PM http://www.omnigroup.com/mailman/archive/macosx-dev/2001-April/013885.html

Must you always drag facts into the argument?

-- 
mike





Re: MySQL - Oracle wrapper/compat. libs

2001-04-27 Thread Simon Cozens

On Fri, Apr 27, 2001 at 06:50:18PM +0100, Leon Brocard wrote:
 OK, I give you Perl, the Perl debugger, and B::Generate. First one to
 optimise Perl code (maybe replacing bits of Perl with XS on the fly?)
 gets a pat on the back.

I think NI-S is working on it; see recent perl6-language discussion about
tying.

-- 
 I never thought I'd say this, but you're getting very strange.
Thank God: I thought it was everybody else.
- J-P Stacey



Re: MySQL - Oracle wrapper/compat. libs

2001-04-25 Thread David Cantrell

On Tue, Apr 24, 2001 at 04:27:42PM -0700, Paul Makepeace wrote:
 On Tue, Apr 24, 2001 at 12:28:42PM +0100, Dominic Mitchell wrote:
  Don't forget that even if you could automatically change the API over,
  you'd still have to change all the SQL in the API as well.  Which is
  probably just as difficult a task, given how much SQL can vary from
  product to product...
 
 IME, the SQL only significantly varies when you're doing the kind of
 SQL that could earn you a serious DBA label or you're working in a
 bank. MySQL has fairly limited SQL capabilities which mapping onto
 Oracle shouldn't be hard. The reverse obviously isn't true.

Trouble is, they all have non-standard extensions, which are *really* handy
and which you *will* use if you don't know any better.  For example, MySQL
has AUTO_INCREMENT fields which are dead useful for id fields; the closest
Oracle equivalent would be using a sequence.

-- 
David Cantrell | [EMAIL PROTECTED] | http://www.cantrell.org.uk/david/

   Rip, Mix, Burn, unless you're using our latest and greatest
 operating system which we couldn't be arsed to complete



Re: MySQL - Oracle wrapper/compat. libs

2001-04-25 Thread Mark Fowler

On Wed, 25 Apr 2001, David Cantrell wrote:


 Trouble is, they all have non-standard extensions, which are *really* handy
 and which you *will* use if you don't know any better.  For example, MySQL
 has AUTO_INCREMENT fields which are dead useful for id fields; the closest
 Oracle equivalent would be using a sequence.


Why you say don't know better, what should I use instead of this.  Is
there any sensible way to do this in bog standard SQL that won't have a
massive perfomance hit on mysql?

Later.

Mark.

-- 
 mark still hasn't replaced his sig.





Re: MySQL - Oracle wrapper/compat. libs

2001-04-25 Thread Leon Brocard

Mark Fowler sent the following bits through the ether:

 Why you say don't know better, what should I use instead of this.  Is
 there any sensible way to do this in bog standard SQL that won't have a
 massive perfomance hit on mysql?

The nice thing about SQL is that there are so many standards to choose
from.

Leon
-- 
Leon Brocard.http://www.astray.com/
Iterative Software..http://yapc.org/Europe/

... Acoustic - What you play pool with



Re: MySQL - Oracle wrapper/compat. libs

2001-04-25 Thread David Cantrell

On Wed, Apr 25, 2001 at 12:50:05PM +0100, Mark Fowler wrote:
 On Wed, 25 Apr 2001, David Cantrell wrote:
 
  Trouble is, they all have non-standard extensions, which are *really* handy
  and which you *will* use if you don't know any better.  For example, MySQL
  has AUTO_INCREMENT fields which are dead useful for id fields; the closest
  Oracle equivalent would be using a sequence.
 
 Why you say don't know better, what should I use instead of this.

The portable way to do it is the slow way.  That's why the vendors went
and extended the language.  The real problem is that they generally don't
document very well what's standard and what's proprietary, so you need to
be familiar with at least a couple of rdbms's to know what's what.

TBH though, I would use postgresql instead of mysql anyway.  It's far more
capable, but is also closer to the Oracle way of doing things.

Is
 there any sensible way to do this in bog standard SQL that won't have a
 massive perfomance hit on mysql?

Unfortunately no.  You'd have to:
  LOCK the table
  SELECT the maximum id currently in use and add one
  INSERT with that id
  UNLOCK the table
so all your other queries will block until the table is unlocked.

-- 
David Cantrell | [EMAIL PROTECTED] | http://www.cantrell.org.uk/david/

   Rip, Mix, Burn, unless you're using our latest and greatest
 operating system which we couldn't be arsed to complete



Re: MySQL - Oracle wrapper/compat. libs

2001-04-24 Thread Dominic Mitchell

On Tue, Apr 24, 2001 at 02:58:23AM -0700, Paul Makepeace wrote:
 Here's a perl question (OK, not really).. Is anyone aware of a
 compatibility/wrapper library which a developer could use to take an
 app using the MySQL API and with some (ideally) minimal munging turn
 it into Oracle OCI or Pro*C code?
 
 I'm faced with converting a couple of apps that have MySQL support to
 use OCI and it's, er, frightening to say the least (OCI manual is 1,054
 PDF pages. It is in the best academic traditions for Intimidatingly
 Enormous Tomes deemed an introduction).

Don't forget that even if you could automatically change the API over,
you'd still have to change all the SQL in the API as well.  Which is
probably just as difficult a task, given how much SQL can vary from
product to product...

-Dom



Re: MySQL - Oracle wrapper/compat. libs

2001-04-24 Thread James Powell

On Tue, Apr 24, 2001 at 12:28:42PM +0100, Dominic Mitchell wrote:
 On Tue, Apr 24, 2001 at 02:58:23AM -0700, Paul Makepeace wrote:
  Here's a perl question (OK, not really).. Is anyone aware of a
  compatibility/wrapper library which a developer could use to take an
  app using the MySQL API and with some (ideally) minimal munging turn
  it into Oracle OCI or Pro*C code?
  
  I'm faced with converting a couple of apps that have MySQL support to
  use OCI and it's, er, frightening to say the least (OCI manual is 1,054
  PDF pages. It is in the best academic traditions for Intimidatingly
  Enormous Tomes deemed an introduction).
 
 Don't forget that even if you could automatically change the API over,
 you'd still have to change all the SQL in the API as well.  Which is
 probably just as difficult a task, given how much SQL can vary from
 product to product...
 
 -Dom

At least it's not as bad as Oracle - MySQL (although that would be a
less likely scenario).

There was a link on mysql.com about mysql being added to the Oracle
migration kit that might be helpful (although probably not).

jp



Re: MySQL - Oracle wrapper/compat. libs

2001-04-24 Thread Paul Makepeace

On Tue, Apr 24, 2001 at 12:28:42PM +0100, Dominic Mitchell wrote:
 Don't forget that even if you could automatically change the API over,
 you'd still have to change all the SQL in the API as well.  Which is
 probably just as difficult a task, given how much SQL can vary from
 product to product...

IME, the SQL only significantly varies when you're doing the kind of
SQL that could earn you a serious DBA label or you're working in a
bank. MySQL has fairly limited SQL capabilities which mapping onto
Oracle shouldn't be hard. The reverse obviously isn't true.

I have had stuff I've written on Access work on Oracle with very little
effort, the only glitch is suffering Access being case-sensitive with
its column names (SQL non-compliant in other words).

The purpose of this is a MySQL - Oracle migration for apps that have
MySQL support who want to Go Big. I don't see there being a big market
for Oracle - MySQL frankly.

Paul