Abstracting the Database

Anon

I want to build a db abstraction layer like what is used at Sourceforge and I have a few questions...

When using straight up mySQL functions, they return database result sets. Is there some kind of common datatype that all database result sets can be cast to before my functions return them so any database can be used?
In the SourceForge code there is notation like this:

@mysql($db, $query);

What does the '@' do? Also, I can't find reference to the mysql() function anywhere in the included pages.

Any help on this is appreciated as I am currently moving a site from mySQL to Oracle.

Anon

No; do the oracle functions work differently?
The '@' suppresses any built-in error messaging from PHP. It's not
specific to the mysql functions, which is why it isn't mentioned there.
You can use it anywhere, but it's most pertinent because certain
kinds of things (e.g. attempting to insert a duplicate unique key)
are treated as errors and normally PHP spits out its own message.
The '@' allows you to suppress this and write your own friendlier
message based on the return value.

Anon

I don't know the answer, not even what @ means...

I just want to receive it by email if some one replies to you, ok?

André (LISBON, PT)

Hunter Hillegas wrote:

I want to build a db abstraction layer like what is used at Sourceforge and I have a few questions...

When using straight up mySQL functions, they return database result sets. Is there some kind of common datatype that all database result sets can be cast to before my functions return them so any database can be used?
In the SourceForge code there is notation like this:

@mysql($db, $query);

What does the '@' do? Also, I can't find reference to the mysql() function anywhere in the included pages.

Any help on this is appreciated as I am currently moving a site from mySQL to Oracle.

Anon

@yes?

where do you put the friendly message in the sintaxt of @?

I've seen something like

mysql_query()
or die ("</TD></TR></TABLE>friendly message here");

is it the same? or is @ php4 specific?

André (LISBON, PT)

Anon

It's in the manual under Error Handling. Here's a brief excerpt:

  All PHP expressions can also be called with the "@" prefix,
  which turns off error reporting for that particular
  expression. If an error occurred during such an expression
  and the track_errors feature is enabled, you can find the
  error message in the global variable $php_errormsg.

Anon

I read the manual.

I just don't get complete answers like yours...

so after i call an "error-fertile-function" with a @ prependind, i can write:

@...
if ($php_errormsg);
my_error_output($php_errormsg);

or did i get it all wrong?

and something else...
is it possible to write blocks like...

...function that might return PHP errors
...execution drops out of the block
...after first error

}
if ($php_errormsg);
my_error_output($php_errormsg);

? is it possible in anyway?

André

Anon

Not quite: you need to look at the return value of the
function to see if it failed:

  if (@(...))
    echo "Everything is fine";
  else
    my_error_output($php_errormsg);

BTW, what manual are you reading? My copy is quite informative
about these things. For example, the <b>mysql_query</b> page
says the following about the return value:

"mysql_query() returns TRUE (non-zero) or FALSE to indicate
whether or not the query succeeded. A return value of TRUE
means that the query was legal and could be executed by the
server. It does not indicate anything about the number of rows
affected or returned. It is perfectly possible for a query to
succeed but affect no rows or return no rows."

Anon

Don't know what BTW means...

About the manual: i'm portuguses, i write good english but fail to understand many things, it's hard and painfull to search!? for answers in php.net, books are expensive, and !!! have to pay to Portugal Telecom for each minute i'm online.

Thanks a lot for your help. The manual is obviously enough for you, and it seems you own a copy of it...

:->

André

Anon

Oh, sorry, 'BTW' is an abbreviation for 'by the way'.

As far as owning a manual, all I own is the html version
that you can get from the php web site. This plain
html version doesn't use php itself, so you can install
it on any drive that supports long file names and run
it locally.

Anon

The Oracle functions are very different from the mySQL ones.

I'm just wondering what datatype my abstraction layer should return so that I can use the DB results in the Web app.

Hunter

Anon

It's more than question of type, the data itself and the model used is quite different. I don't envy you.

Are you trying to cover both versions of oracle, and odbc too?
If you can ignore oracle-pre-8, you can use the fetch-into
functions for oracle and odbc to give you a result set in
an array, which works the same way as the array-fetch
versions of mysql, postgresql, sql server, sybase, and even
informix. Going the other direction (i.e. trying to emulate
oracle's bind model with the others) would be too painful
for words.

So I guess I would say that returning an enumerated array
of column data, or false if no (more) rows are available,
is the lowest-common-denominator approach you are
looking for.

Extending this to oracle-pre-8 is gonna be a pain, too,
since as far as I can tell (I've never actually used it)
you can't find out after the fact how many columns
are in the result, so even though you could loop through
the columns you can't directly find out how many there are.
Sheesh!

Anon

My main focus is to cover Oracle (not pre 8.0) and mySQL, though being able to support other platforms would be nice.

Thanks for the tips. I'll start researching along these lines.

Anon

we are all writing our own database abstraction layers...
I don't think the datatype should depend on mySQL or Oracle, that's the goal of the abstraction layer. It really should depend on how complex our result sets are and/or what functionallity we wish for them...

for my purposes, very html-output-focused, classes must provide:

$db = new my_db(...
$db->connect(...
$table1 = new my_table (...
$query1 = new my_select_query(....
$resultset1 = $query1.execute (...)
echo ($resultset1.record[$i]['name']);

so, beneath the layer I must translate the array KIRK just told you about into my own data format (output - HTML - oriented)

first i'll set columns, names, descriptions, and html formats directly in the query
and so expect to have the same columns, with all it's attributes as a result

this result is of type my_resultset...

now, maybe my db is too simple and my page design too complex... or, my guess, in either side of the layer there's always a "emulation" surface...

try reading my usage sequence as

$db = new my_db(...
$db->connect(...

$movies = new my_table ('movies','stylesheet1')

//beneath the layer fetches this values from database, matching movies against:
$movies->field[1]->name = ...
$movies->field[1]->type = ...
$movies->field[1]->class = ...
$movies->field[1]->headerclass = ...
$movies->field[1]->description = ...
$movies->field[1]->display_lenght = ...
//values can be overriden

$color_movies = new my_select_query("select * from movies where color = true order by date");

//beneath:
$color_movies->set_all_fields($movies);
$color_movies->setcriteria('color = true');
$color_movies->orderby = 'date';
//note all relevant column attributes are read from the $movies object by the set_all_fields method

$color_movies = $query1.execute (...)

with this i should obtain:

$color_movies->table[0] = $movies
$color_movies->tablecount = 1
$color_movies->field[3] = 'description'
$color_movies->field[3]->name = 'Synopsis'
$color_movies->field[3]->type = 'nl2br'
$color_movies->field[3]->display_lenght = 250;
$color_movies->field [3]->display_more = 'link'
$color_movies->field [3]->headerclass = 'text'
$color_movies->field [3]->class = 'text'
$color_movies->fieldcount = ...
$color_movies->recordcount = ...
$color_movies->record[1]['title'] = 'Magnolia'

and in the end

$color_movies->record[1]['description']->renderedhtml = "<p class=text>Loved it<br>it's really great.</p>"

then, output:

//all the <TABLE> html
echo (my_html_table($color_movies, 100, '%', ...);

//<TABLE> and header row(s)
echo (my_html_headerrow($bw_movies, ...);

//for each row, <TR>, and </TABLE> if last
echo (my_html_row($bw_movies->record, ...);

Now, just joking, the html_functions are still site-specific but i'll write a whole site-abstraction-layer and maybe a php-version-abstraction layer.

André