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

gmpassos has asked for the wisdom of the Perl Monks concerning the following question:

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:
[download]

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) ;
[download]

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

Comment on DBD::Oracle help for portable DB handle code/module! Select or Download Code

Replies are listed 'Best First'.
Re: DBD::Oracle help for portable DB handle code/module! by valdez (Monsignor) on Apr 01, 2003 at 14:42 UTC
I suggest to take a look at Rosetta: The Rosetta framework is intended to support complex (or simple) database-using Perl 5 applications that are easily portable across databases because all common product-specific details are abstracted away. Rosetta is designed to natively handle (interface to or implement) a superset of generic RDBMS product features, so that you can do any action that you could before, including standard data manipulation (including complex multi-table selects or updates with subqueries or stored procedure calls), and schema manipulation (tables, views, procedures). At the same time, it is designed to do its work quickly and efficiently. Ciao, Valerio	[reply]
Re: DBD::Oracle help for portable DB handle code/module! by zakb (Pilgrim) on Apr 01, 2003 at 12:12 UTC
Wow! A lot of questions. Let's see... 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]+$") ;` [download] A: You don't. Oracle has no concept of REGEX queries. The best you can do is use the LIKE operator, which gives you the _ and % wildcards. Underscore (_) matches any one character. Percent (%) matches zero or more of any character. You use it like this: `select * from foo where bar like '%xyz%';` Also note, in Oracle, strings must be delimited with single quotes. Q2) How I LOCK and UNLOCK a table? When I unlock will unlock all the tables (like in MySQL) or just one? A: Execute: `LOCK TABLE <table> IN <lockmode> MODE [NOWAIT];` `IN <lockmode> MODE`: lockmode can be ROW SHARE (allows concurrent access but prohibits exclusive locking by other users), ROW EXCLUSIVE (as row share but also prohibits ROW SHARE locking by other users), SHARE (prevents updates), SHARE ROW EXCLUSIVE (prohibits ROW SHARE locking and updates) or EXCLUSIVE (only allows queries to other users). `NOWAIT` means the command will return immediately whether or not the table can be locked; normally, Oracle blocks until a lock is obtained. To unlock, you either COMMIT or ROLLBACK. Q3) What types are suported? Based in MySQL, what is the similar: `VARCHAR(100) = ? VARCHAR(150) = ? TEXT = ? MEDIUMTEXT = ? SMALLINT = ? FLOAT = ? DOUBLE = ? FLOAT(10) = ? FLOAT(10,4) UNSIGNED = ? BOOLEAN = ?` [download] Character types up to 4000 bytes are probably best defined with a VARCHAR2({size}). Numbers - integer or floating point - are defined using NUMBER({precision}, {scale}) - {precision} = total number of digits from 1 to 38, scale = number of digits after the decimal point from -84 to 127. A negative scale rounds e.g. -2 rounds to the nearest hundred. A floating point number is defined as NUMBER(38). There is not a BOOLEAN datatype. You'd have to use a NUMBER(1) I guess. There is a DATE datatype, and a range of LONG datatypes for large blocks of data. 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: You can't have Oracle do this automatically, you'd have to create a sequence and a trigger and have the trigger update the column in the table from the sequence on insert. Q5) How to set a columns as PRIMARY KEY? A: At CREATE TABLE time: `CREATE TABLE <table> (<field> <type> [UNIQUE] [NOT NULL], ... CONSTRAINT <constraint_name> PRIMARY KEY (<field name>));` [download] Q6) Is the LIKE resource enable? It works like that?: `select * from foo where(bar LIKE "%text_in_the_midle%") ;` A: The same. Q7) How to get the list of tables? A: The easiest way is use DBI! The tables method works with DBD::Oracle. Q8) How to get columns of a table and the types of them? A: Again, I would think the table_info method of DBI should work. Q9) How to get the maximal value of a integer column?: `select max(ID) as ID from foo ;` A: The same.	[reply] [d/l] [select]


The stupid question is the question not asked
	PerlMonks