Bonjour à tous,
Ci-joint un petit patch pour categorie.class et les scripts de
migrations et de table MySQL associés.
Il corrige le problème d'unicité des label dans l'arbre des
catégories. J'ai du rajouter un champ à la table llx_categories pour
identifier le parent plus simplement, et modifier ses contraintes
(l'anciennes contrainte d'unicité liait label, type et entité, sans
prendre en compte le parent puisqu'il n'existait pas dans la table).
En parlant de cela, j'ai bien compris l'intérêt des tables
d'association catégories/objets (sociétés, produits, etc...) puisqu'un
catégories peut comprendre plusieurs objets et un objet appartenir à
plusieurs catégories.
Cependant, la table d'association de catégorie avec elle même
m'échappe quelque peut: qu'une catégorie puisse avoir plusieurs filles
me parait en effet évident, mais il me semble qu'une fille ne puisse
avoir qu'une ou pas de mère. L'utilisation d'une table d'association
me parait donc superflu.
Je n'ai pas retouché toute la classe (mon objectif était de parvenir à
remettre en route le module magento), mais je pense qu'on pourrait
éviter une multitude de join et de difficultées liées à cette table
d'association en utilisant un champ répertoriant le parent plutôt
qu'une table d'association.
Cordialement,
AnthonyP
translation:
Hello everyone,
Attached a small patch categorie.class, migration scripts and MySQL
table associated.
It corrects the problem unicity of the label in the tree of
categories. I had to add a field to the table to identify the parent
llx_categories more simply, and modify its constraints (the old unique
constraint bound label, type and entity, without caring of the parent
because there was not in the table).
Speaking of which, I understand the interest tables Association
classes / objects (companies, products, etc. ...) because a category
may include multiple objects and an object belong to multiple
categories.
However, the association table with itself eludes me somewhat: a
category can have many daughters seems to me evident but it seems to
me that a daughter can have one or no parent. So the use of an
association table seems to me superfluous.
I have not reworked the whole class (my goal was to back on the road
the magento module), but I think we could avoid a multitude of issues
and of joins regarding this association table using a field rather
than listing the parent in an association table.
Cordially,
AnthonyP
### Eclipse Workspace Patch 1.0
#P dolibarr
Index: htdocs/categories/class/categorie.class.php
===================================================================
RCS file:
/sources/dolibarr/dolibarr/htdocs/categories/class/categorie.class.php,v
retrieving revision 1.15
diff -u -r1.15 categorie.class.php
--- htdocs/categories/class/categorie.class.php 11 Mar 2011 17:42:39 -0000
1.15
+++ htdocs/categories/class/categorie.class.php 25 May 2011 12:53:35 -0000
@@ -50,6 +50,7 @@
var $description;
var $socid;
var $type; // 0=Product,
1=Supplier, 2=Customer/Prospect, 3=Member
+ var $parentId;
var $cats=array(); // Tableau en memoire des
categories
var $motherof = array(); // Tableau des correspondances id_fille
-> id_mere
@@ -109,6 +110,7 @@
{
$res = $this->db->fetch_array($resql);
$this->id_mere = $res['fk_categorie_mere'];
+ $this->parentId = $res['fk_categorie_mere'] ?
$res['fk_categorie_mere'] : 0;
return $this->id;
}
else
@@ -131,6 +133,7 @@
// Clean parameters
if (empty($this->visible)) $this->visible=0;
+ $this->parentId = ($this->id_mere) != "" ?
intval($this->id_mere) : 0;
if ($this->already_exists())
{
@@ -144,13 +147,13 @@
{
$sql.= "fk_soc,";
}
- $sql.= "visible, type) ";
+ $sql.= "visible, type, fk_parent_id) ";
$sql.= "VALUES ('".$this->db->escape($this->label)."',
'".$this->db->escape($this->description)."',";
if ($conf->global->CATEGORY_ASSIGNED_TO_A_CUSTOMER)
{
$sql.= ($this->socid != -1 ? $this->socid : 'null').",";
}
- $sql.= "'".$this->visible."',".$this->type.")";
+ $sql.= "'".$this->visible."',".$this->type.",".$this->parentId
.")";
$res = $this->db->query ($sql);
@@ -204,6 +207,7 @@
// Clean parameters
$this->label=trim($this->label);
$this->description=trim($this->description);
+ $this->parentId = ($this->id_mere) != "" ?
intval($this->id_mere) : 0;
$this->db->begin();
@@ -243,6 +247,7 @@
$sql .= ", fk_soc = ".($this->socid != -1 ?
$this->socid : 'null');
}
$sql .= ", visible = '".$this->visible."'";
+ $sql .= ", fk_parent_id = ".$this->parentId;
$sql .= " WHERE rowid = ".$this->id;
dol_syslog("Categorie::update sql=".$sql);
@@ -803,14 +808,29 @@
}
/**
- * \brief Check if no category with same label already
exists
+ * \brief Check if no category with same label already
exists for this cat's parent or root and for this cat's type
* \return boolean 1 if already exist, 0
otherwise, -1 if error
*/
function already_exists()
{
- $sql = "SELECT count(c.rowid)";
- $sql.= " FROM ".MAIN_DB_PREFIX."categorie as c,
".MAIN_DB_PREFIX."categorie_association as ca";
- $sql.= " WHERE c.label = '".$this->db->escape($this ->
label)."' AND type=".$this->type;
+ if($this->id_mere != "")
+ {
+ $sql = "SELECT COUNT(c.rowid)";
+ $sql.= " FROM ".MAIN_DB_PREFIX."categorie as c ";
+ $sql.= " JOIN ".MAIN_DB_PREFIX."categorie_association
as ca";
+ $sql.= " ON c.rowid=ca.fk_categorie_fille";
+ $sql.= " WHERE ca.fk_categorie_mere=".$this->id_mere;
+ $sql.= " AND c.label='".$this->label."'";
+ }
+ else
+ {
+ $sql = "SELECT COUNT(c.rowid)";
+ $sql.= " FROM ".MAIN_DB_PREFIX."categorie as c ";
+ $sql.= " JOIN ".MAIN_DB_PREFIX."categorie_association
as ca";
+ $sql.= " ON c.rowid!=ca.fk_categorie_fille";
+ $sql.= " WHERE c.type=".$this->type;
+ $sql.= " AND c.label='".$this->label."'";
+ }
dol_syslog("Categorie::already_exists sql=".$sql);
$res = $this->db->query($sql);
if ($res)
Index: htdocs/install/mysql/migration/3.0.0-3.1.0.sql
===================================================================
RCS file:
/sources/dolibarr/dolibarr/htdocs/install/mysql/migration/3.0.0-3.1.0.sql,v
retrieving revision 1.39
diff -u -r1.39 3.0.0-3.1.0.sql
--- htdocs/install/mysql/migration/3.0.0-3.1.0.sql 6 May 2011 07:21:28
-0000 1.39
+++ htdocs/install/mysql/migration/3.0.0-3.1.0.sql 25 May 2011 12:53:35
-0000
@@ -30,6 +30,10 @@
ALTER TABLE llx_projet MODIFY model_pdf varchar(255);
ALTER TABLE llx_propal MODIFY model_pdf varchar(255);
+-- Add a field for categorie used to complete constraint (unique label on all
tree bug)
+ALTER TABLE llx_categorie ADD fk_parent_id INT DEFAULT NULL AFTER import_key;
+ALTER TABLE llx_categorie DROP INDEX uk_categorie_ref;
+ALTER TABLE llx_categorie ADD UNIQUE INDEX uk_categorie_ref( label, TYPE ,
entity, fk_parent_id ) ;
-- Delete old constants
DELETE FROM llx_const WHERE __DECRYPT('name')__ = 'MAIN_MENU_BARRETOP';
Index: htdocs/install/mysql/tables/llx_categorie.sql
===================================================================
RCS file:
/sources/dolibarr/dolibarr/htdocs/install/mysql/tables/llx_categorie.sql,v
retrieving revision 1.3
diff -u -r1.3 llx_categorie.sql
--- htdocs/install/mysql/tables/llx_categorie.sql 24 Feb 2011 09:57:04
-0000 1.3
+++ htdocs/install/mysql/tables/llx_categorie.sql 25 May 2011 12:53:35
-0000
@@ -29,7 +29,8 @@
description text, -- description of
the category
fk_soc integer DEFAULT NULL,
-- attribution of the category has a company (for product only)
visible tinyint DEFAULT 1 NOT NULL, -- determine if
the products are visible or not
- import_key varchar(14) -- Import key
+ import_key varchar(14), -- Import key
+ fk_parent_id integer DEFAULT 0 -- Parent category id
(0 for root, null undefined)
)ENGINE=innodb;
--
@@ -46,4 +47,4 @@
-- 1 : first company category type
-- 2 : second company category type
-- 3 : etc...
---
\ No newline at end of file
+--
_______________________________________________
Dolibarr-dev mailing list
[email protected]
https://lists.nongnu.org/mailman/listinfo/dolibarr-dev
