1
Daigoro
How to use Xoops database?
  • 2004/4/16 23:06

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


I've been looking around to try to find some documentation on how to use the database from modules...
However, it appears nothing has been written that describes in details what functions are available in the abstraction-layer between XOOPS and the database.
Perhaps it't just me thats too inexpierenced when it comes to php and MySQL, but I'm pretty sure others would like this information, too.

The questions I'd like to have answered are:


How do you initialize the use of the database?

How do you create, alter/modify and delete tables?

How do you select a specific table?

How do you store data into a table?

How do you retrive data from a table?


A good example would be great.


Regards,

2
Dave_L
Re: How to use Xoops database?
  • 2004/4/17 3:18

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


The core takes care of initializing the database (connecting to the server and selecting the database), so the module doesn't need to do anything in that regard.

The methods for accessing the database are in class/database/mysqldatabase.php.

Here are some examples:

$table $xoopsDB->prefix('chess_pending_games');

$xoopsDB->query("INSERT INTO $table
   SET
      game_type    = '
$gametype',
      color_option = '
$coloroption'
"
);

if (
$xoopsDB->errno()) {
   
trigger_error($xoopsDB->errno() . ':' $xoopsDB->error(), E_USER_ERROR);
}


$table $xoopsDB->prefix('chess_pending_games');

$xoopsDB->query("DELETE FROM $table WHERE pending_game_id='$pending_game_id'");
if (
$xoopsDB->errno()) {
   
trigger_error($xoopsDB->errno() . ':' $xoopsDB->error(), E_USER_ERROR);
}


$pending_games_table $xoopsDB->prefix('chess_pending_games');

$result $xoopsDB->query("SELECT game_type, color_option
   FROM   
$pending_games_table
   WHERE  pending_game_id = '
$pending_game_id'
"
);

if (
$xoopsDB->getRowsNum($result) < 1) {
   
redirect_header(XOOPS_URL.'/modules/chess/'_CHESS_REDIRECT_DELAY_FAILURE_MD_CHESS_GAME_NOT_FOUND);
}

$row $xoopsDB->fetchArray($result);

$xoopsDB->freeRecordSet($result);


The trigger_error() calls are for debugging; I may handle that differently in the final code.

3
Daigoro
Re: How to use Xoops database?
  • 2004/4/17 9:06

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


Dave_L,

Thanks for your quick answer. I tried to look at the class you mentioned, but I'm still a bit confused.

It may just be because I hardly know about databases, but are those query commands you mentioned not pure MySQL?

I had the understanding there were XOOPS wrapper functions, that would encapsulate the real database commands, and thus make it possible to upgrade XOOPS to run from other databases in the furture. Was it just me dreaming?

I've also seen the notion db:something used - mostly in connection with smarty, but people also mention it in other discussions. Can you clerify that a bit?


4
Daigoro
Re: How to use Xoops database?
  • 2004/4/17 10:47

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


It appears it's nessary to add the following

global $xoopsDB;


to have Dave_L's example work.

5
Dave_L
Re: How to use Xoops database?
  • 2004/4/17 15:19

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


The "global" is needed if you're referencing $xoopsDB inside a function.

The "db:" is a Smarty resource type, indicating that the template source is in the database. Another resource type is "file:", which tells Smarty that the template source is a disk file.

I can't answer your question about non-MySQL databases. I've wondered about that myself.

6
Daigoro
Re: How to use Xoops database?
  • 2004/4/18 22:17

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


Thanks Dave. I wasn't aware of the db:/file: option, so it's great to know.

About the database and XOOPS abstraction from it.
Perhaps it's now we should have the discussion about how it should be implemented?

As I'm pretty naive and new to databases, I don't know if this has allready been done, but it not then start here! :)

IMHO there should be a file with a set of database access functions for each database-system supported. That way, the only difference between using MySQL or another database would be the abstraction-layer file loaded - and probably specified in mainfile.php.
Adding a new database would be as simple as adding a new file with that databases name, containing functions for that specific database.


7
mvandam
Re: How to use Xoops database?
  • 2004/4/19 9:36

  • mvandam

  • Quite a regular

  • Posts: 253

  • Since: 2003/2/7 2


Quote:

About the database and XOOPS abstraction from it.
Perhaps it's now we should have the discussion about how it should be implemented?


There are several existing database abstraction libraries already existing... ADOdb, MDB, MDB2, pear DB, several others.

The trouble is, you can make it so all databases have the same 'function calls', but it is still very difficult to write a truly portable *query*. (Very simple queries can be written fairly portably, but a complex query is likely to run into db-specific things.) The problem is how to deal with this.

ADOdb is very popular and a lot of people have had success with this. Unfortunately it is somewhat 'bloated' and the loading time of the ADOdb class can be a performance hit. MDB2 to me looks very promising but it is young and doesn't have nearly the support. With ADOdb, the typical approach I believe is to write the queries in as portable a manner as possible, then wait for one of the users to report a problem, then try to fix it or have separate cases depending on the database system the user is running. Not ideal, but works for the majority of cases.

I'm not sure how the other devs plan to proceed with Xoops. $xoopsDb is kind of an abstraction library itself, but not complete.

8
Daigoro
Re: How to use Xoops database?
  • 2004/4/19 18:05

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


Thanks for the explanation Mvandam.

My imidiate thought when I read it, was "Then there should be a simple generic interface, and then also allow for raw commands to be used."

The generic interface could have functions like:

SetDatabase()

SetTable()
GetTable()
InsertTable()
ModifyTable()
DeleteTable()

SetValue()
GetValue()
InsertValue()
ModifyValue()
DeleteValue()

SetString()
GetString()
InsertString()
ModifyString()
DeleteString()

GetDatabaseType()
SetRaw()
GetRaw()

These are just "un-optimized" idears, but does it make sense to have an abstraction like this?

Looking forward for comments.


9
jackt
Re: How to use Xoops database?
  • 2004/4/19 19:10

  • jackt

  • Just popping in

  • Posts: 31

  • Since: 2002/6/2 2


Daigoro, that looks fine, but if you want to support other db's you have to make it so that there are no "raw" queries. If you have to hardcode an SQL string in there then that string may not be supported by another db and thus may cause an error when using another db. There's a lot of db abstraction layers like mvandam pointed out. To me it would seem more prudent to incorporate one of them than to reinvent the wheel.

About a year ago I suggested MDB (the first version) as a possible database abstraction layer for XOOPS. It was beta then, or maybe alpha. Maybe MDB2 would be ideal for Xoops3. Yeah it's still very immature, but the format of it seems to be the same as MDB1. I was thinking of actually attempting to rewrite a portion or a module in MDB1 or 2 (ADOdb did look bloated to me, although mature) just to see the feasibility and maybe run a profile and see how much it adds to parsetime, but then came the problem of Pear. If you don't have pear installed, then you need to install or if you don't have ssh access install it using the upload method and adding an init path. It seemed like it could be a hassle when you installed this version of XOOPS.

In any event, converting or expanding the database abstraction layer now would be a lot of rewriting and testing. Whether it's something you want to implement for 2.2 or 3 is up to the core dev team. If 2.2 is looking to be the focus of the rest of this year, it might make more sense to do this for Xoops3.

10
Daigoro
Re: How to use Xoops database?
  • 2004/4/19 20:09

  • Daigoro

  • Quite a regular

  • Posts: 223

  • Since: 2003/7/3 2


Jackt, ofcource you are correct that in a perfect world there should be no raw queries, but on the other hand I see no reason to not allow it, under some conditions....
I know developers well enough to know that if they can find a way to do something faster then they would prefer to do so.
The ability to test what kind of database is in use would allow developers to do something like this:

if GetDatabaseType("specific type") {
do raw communications
} else {
do generic communication
}

But back on track...

You are right it will probably be better to use one of the allready existing abstraction layers - but as I understand they are all quiet heavy - probably because they are optimized for best performance on all systems.

I'd be interested to know how easy it would be for you to add MDN1 or 2 to an existing module, and to get it working on the system.


Login

Who's Online

230 user(s) are online (143 user(s) are browsing Support Forums)


Members: 0


Guests: 230


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits