#11

Re: Structure and relational model of the XOOPS database

2009/8/24 12:05 Search & Replace is also a very good and very fast tool for searches.

#12

Re: Structure and relational model of the XOOPS database

2009/10/1 12:20 Is it that when module is implemented to XOOPS, the database for the module is generated automatically by XOOPS?

I thought this because there's some strange solutions in the database of one module I'm looking for. I used that DBDesigner and "reverse-engineered" the table structure from the XOOPS database.

In the tables of this module and also in XOOPS tables, there's this strange kind of thing (example):

Two tables A and B. Both are referencing to each other's primary keys with 1:N relation like explained in DBDesigner's online documentation (one - to - many relation One row in the source table matches many rows in the destination table.)

For example xoops_banner and xoops_bannerfinish tables have such referential "connection" between each other. Both have their primary keys also as foreign keys and they refer to each other by them. Confusing.

Why is it done like this? Are there no referential integrity set in the XOOPS database generally?

Thanks in advance if anyone can lighten me a bit about this issue.

BR

Mikko V

#13

Re: Structure and relational model of the XOOPS database

2009/10/1 13:39 Quote:Every module contains a /sql/mysql.sql file (appointed in xoops_version.php), which contains the table structure for the module's specific tables. These tables are created at module install and removed with uninstall.
Quote:No, all tables are MyISAM tables and referential integrity is only possible with the InnoDB storage engine, which is not always available.
Quote:Such things are not possible between two tables.
If the (unique) primary keys (are used to) point to each other you have a one to one relation.
Quote:I think you are misinterpreting the functions between these two tables. AFAIK When a banner has had its required display times it is copied to the other table and deleted in the first table and the 2 bid are both PRIMARY KEY, but are unrelated.
They do have both a 1 to many relation with bannerclient trough their cid field. One client can have many running and finished banners.

#14

Re: Structure and relational model of the XOOPS database

2009/10/2 11:26 Thanks for replying to these questions.

Quote:

Okay. Is there somewhere .sql file for XOOPS table structure?

Quote:

Yes, but how the references then work between the tables in the XOOPS database? I'm not too familiar with MyISAM even if I tried to study it a bit with Google.

Quote:

That's what I thought too.

Quote:

Here's a screenshot from DBDesigner 4. I used Reverse-engineer to get the table structure from the XOOPS database. I selected only few tables to be fetched to describe my issue.

Screenshot

I have numbers for every "connection" between tables.

1. Relations between "xoops_newsblock" and "xoops_bannerfinish" tables. Key used is "bid" for both ways.

2. Relations between "xoops_newsblock" and "xoops_banner" tables. Key used is "bid" for both ways.

3. Relations between "xoops_banner" and "xoops_bannerfinish" tables. Key used is "bid" for both ways. Below of these tables (marked with "3." also), there are Relation Editors for these two relations. We can see they are 1:N relations and both have "bid" as the "foreign key". (same goes to number 1 and number 2 relations in the picture")

4. Relations between "xoops_bannerclient" to "xoops_banner" and to "xoops_bannerfinish" tables. Key used is "cid" for both like you explained.


Here's documentation for DBDesigner, and a precise link to Relation Editor part

Online documentation of DBDesigner 4, Relation Editor

Online documentation of DBDesigner 4

In the first link, we can see these


1:n one - to - many relation
Explanation: One row in the source table matches many rows in the destination table.

Example: A relation between an [order] table and a [orderproduct] table. One order can have many products and a product in the [orderproduct] table is only part of one order.


1:n (Non Identifying) one - to - many relation, FK not in PK
Explanation: One row in the source table matches many rows in the destination table but the Foreign Key in the destination table is not in the Primary Key Index.

Example: A relation between a [payment] table and an [order] table. Each row in the [order] table as a [payment] assigned to. Only [idorder] is the Primary Key field in the [order] table.


So is this just a flaw in DBDesigner that it misinterpretes these relations? Because according to the software, they are 1:N relations. And to me, it's strange. Isn't it that if that is the case, they are actually "same" tables (and why aren't 1:1 relations used then?) Confusing.

BR

Mikko V

edit. Hmm, now I thought that is it with MyISAM that you have to always do the Cartesian product with the two tables which should have relations between each other? And with WHERE clause pick up the correct rows.

If this is correct, then DBDesigner possibly can't guess which tables really references to each other and it just puts every table referencing to each other which have the same keys??? And the functionality of the database is just in code where the database is really used.

Is it so?

#15

Re: Structure and relational model of the XOOPS database

2009/10/9 13:00 Is anyone able to answer my questions in my last post ?

Thank you

BR

Mikko V

#16

Re: Structure and relational model of the XOOPS database

2009/10/9 14:00 I think the designer is looking for similar names and thinks there is a relation. Some existing relations may not be found eg uid and user_id.

But as in case 1 it is not. In fact the bid name is a shortcut for banner id and block id and is thus totally unrelated.
- 2 is the same case.
- 3 unrelated as explained before.
- 4 as explained before.
Quote:The cartesian product has nothing to do with table relations, but is the result of a query on two or more tables with no join condition.
Quote:
The database is fully functional, but the relations are done in the PHP code. That's why I told you to examine the sources.

#17

Re: Structure and relational model of the XOOPS database

2009/10/9 22:02 Hi how are you? I know the XOOPS database being from phpnuke is quiet old, it is an old system of relational management before there was inner and outer joins and havings and so on mysql.

But I was wondering if I can borrow you for a small project magi we would like in 2.5.0 add complete database support for the methods of the database managerment for example ingress, postgress, firebird, mssql, oracle, (These classes for XOOPS where released with 2.4.0.1 (beta).. for example.

But while I was making them from the php.net as these systems all obey normally transactional injections sql. There seems to be and I know also support for dbase and other file type which use a completely different system of table management.

Currently you can make these support this but I want to restructure and refactorise the management of sql and database data so both type a ODBC type connection to anything including - that popular system dbase and the other file type handlers..

If your interested in helping with it at some point let me know. Some ways to do this if some of you are already looking into this is making it so the modeler which phppp built from some of the framework when constructing the sql.. Uses the database class for an index of commands to use in the SQL provider like a construct or modeler further for the SQL.

There is a broad range of options but some of them need some way of hooking simple file based handling for limits and so on that not all SQL supports... This is also when the hook will start being introduced..

This way with a class structure for modeling sql itself built in, you can also conversely install modules by having simuletype variable types so if you have a SQL file for MySQL the installer for example will remodel it for an install of a ingress or mssql database with even the variable construct being taken into account of the SQL Provider/modeler.

#18

Re: Structure and relational model of the XOOPS database

2009/10/13 10:03 Thanks for the reply again!

Quote:

Ah, now I got it. So DBDesigner is pretty useless in this case.

Quote:

Yes, okay that's what I meant with my edit part of the last post. That the relations between tables are done in PHP code because they can't be done in SQL side (because of MyISAM).

Guess I have to try to check all the source files...

wishcraft, I'm sorry that I have to decline since I really don't have time to any extra stuff at the moment. :(