DBD::Oracle help for portable DB handle code/module!

[gmpassos]

Hello Monks,

I'm writing a Perl Module, that I will release soon, called HDB. HDB is an easy, fast and powerfull way to access a DB without use SQL or DBI directly. you just use some methods, variables and conditions based in Perl concept.

The best thing of HDB is that you don't need to know what DB you are using, you just create the HDB object (connection) and HDB make all the work around between DB differences. In other words, your code for DB will be portable between DB types and OS.

For now HDB works for HDB::MySQL and HDB::SQLite. But I want to release the first version with more DB types avaliable. One of them is Oracle. Since I don't have Oracle here, and isn't a free thing, and I'm not a Oracle Geek, I want some help/informations to make the HDB::Oracle work around.

INFORMATIONS THAT I NEED: (Please, reply with this part)

Q1) How I make this 2 REGEX querys, like in MySQL:
select * from foo where(bar REGEX "^[aeiou]+$") ;
select * from foo where(bar REGEX "^[^aeiou]+$") ;
A:

Q2) How I LOCK and UNLOCK a table? When I unlock will 
unlock all the tables (like in MySQL) or just one?
A:

Q3) What types are suported? Based in MySQL, what is the 
similar:
  VARCHAR(100)  = ?
  VARCHAR(150)  = ?
  TEXT          = ?
  MEDIUMTEXT    = ?
  TEXT          = ?
  SMALLINT      = ?
  FLOAT         = ?
  DOUBLE        = ?
  FLOAT(10)     = ?
  FLOAT(10,4) UNSIGNED = ?
  BOOLEAN       = ?

** Please, include the maximal size of each (varchar,integer,float,dou
+ble,etc...)

Q4) How I make an AUTO_INCREMENT column? For example, and
column called ID where when a row is inserted in the table, 
this col will be improved automatically with the next ID. 
And need to be an PRIMARY KEY.
A:

Q5) How to set a columns as PRIMARY KEY?
A:

Q6) Is the LIKE resource enable? It works like that?:
select * from foo where(bar LIKE "%text_in_the_midle%") ;
A:

Q7) How to get the list of tables?
A:

Q8) How to get columns of a table and the types of them?
A:

Q9) How to get the maximal value of a integer column?:
select max(ID) as ID from foo ;
A:

I will be very glad for any reply! If you know any answer, please post it.

For who want to see some of HDB use:

  use HDB ;


  my $HDB = HDB->new(
  type => 'sqlite' ,
  file => 'test.db' ,
  ) ;

  ... or ...

  my $HDB = HDB->new(
  type => 'mysql' ,
  host => 'some.domain.com' ,
  user => 'foo' ,
  pass => 'bar' ,
  ) ;

  $HDB->create('users',[
  'user' => 100 ,
  'name' => 100 ,
  'age' => 'int(200)' ,
  'more' => 1024*4 ,
  ]);

  $HDB->insert( 'users' , {
  user => 'joe' ,
  name => 'joe tribianny' ,
  age  => '30' ,
  more => '*' ,
  } ) ;

  ... or ...

  $HDB->update( 'users' , 'user == joe' , age  => '40' ) ;

  ... or ...

  $HDB->users->insert( {
  user => 'joe' ,
  name => 'joe tribianny' ,
  age  => '30' ,
  more => \%hash ,
  } ) ;

  my %hash_decoded = $HDB->select('users' , 'name =~ joe' , col => 'mo
+re' , return => '$$%' ) ;

  ...

  my @sel = $HDB->select('users' , 'name =~ joe' , return => '@%' ) ;

  foreach my $sel_i ( @sel ) {
    my %cols = %$sel_i ;
    ...
  }

  ... or ...

  my @sel = $HDB->select('users' , 'name != ross' , return => '@$' ) ;

  foreach my $sel_i ( @sel ) {
    my @cols = split("::",$sel_i) ;
    ...
  }

  ...

  my @tables = $HDB->tables ;

  ...

  my @sel = $HDB->cmd("select * from foo", '@@') ;

  ...
  
  # Using DBI:
  my $sth = $HDB->dbh->prepare("INSERT INTO users VALUES (?, ?, ? , ? 
+, ?)") ;
  $sth->execute("foo", "barr", "foo\@mail.com" , '' , 1) ;

Graciliano M. P.
"The creativity is the expression of the liberty".