Użytkownik "Stachu 'Dozzie' K." <d...@g...eat.some.screws.spammer.invalid> napisał:

>>> Może za mało SQLa ;)
>>
>> Tak ? No to zaprogramuj drzewo w zwyklym klasycznym SQL-lu ;)
>
> http://www.slideshare.net/billkarwin/sql-antipattern
s-strike-back
> Slajdy 48 i późniejsze.

No popatrz popatrz... (przejrzalem tylko poczatkowe chyba 20)
Grzebales mi po kodzie sprzed prawie 10 lat ? (sprzed wypalenia ?:).

import Attr
from Query import *


TYPEID_CONTAINER = -1
TYPEID_OBJECT = 0
TYPEID_INTEGER = 1
TYPEID_REAL = 2
TYPEID_LOGICAL = 3
TYPEID_DATE = 4
TYPEID_TEXT = 5
TYPEID_PARAGRAPH = 6
TYPEID_BINARY = 7


def createBase(server, database, user=None, password=None):
structure = [
'''
CREATE TABLE AttributeTypes
(
ID INTEGER NOT NULL UNIQUE,
CODE VARCHAR(16) NOT NULL UNIQUE,
Name VARCHAR(40) NOT NULL UNIQUE,
Description VARCHAR(40),
FUN_ToValue VARCHAR(40),
FUN_FromValue VARCHAR(40),
CONSTRAINT AttributeTypesPrimKey PRIMARY KEY (ID)
)
''',
'''
CREATE TABLE AttributeDefinitions
(
ID INTEGER NOT NULL UNIQUE,
CODE VARCHAR(16) NOT NULL UNIQUE,
Name VARCHAR(40) NOT NULL UNIQUE,
Description VARCHAR(40),
TypeID INTEGER NOT NULL,
CONSTRAINT AttributeDefinitionsPrimKey PRIMARY KEY (ID),
CONSTRAINT AttributeDefinitionsForiKey FOREIGN KEY (TypeID) REFERENCES
AttributeTypes (ID)
)
''',
'''
CREATE TABLE AttributeValues
(
ID INTEGER NOT NULL UNIQUE,
Value VARCHAR(80),
BinaryID INTEGER,
DefID INTEGER NOT NULL,
CONSTRAINT AttributeValuesPrimKey PRIMARY KEY (ID),
CONSTRAINT AttributeValuesForiKey FOREIGN KEY (DefID) REFERENCES
AttributeDefinitions (ID)
)
''',
'''
CREATE INDEX AttributeValuesIdx_Value ON AttributeValues (Value)
''',
'''
CREATE TABLE AttributeTrees
(
ID INTEGER NOT NULL UNIQUE,
Tree INTEGER NOT NULL,
Lft FLOAT NOT NULL UNIQUE,
Rgt FLOAT NOT NULL UNIQUE,
ValueID INTEGER NOT NULL,
CONSTRAINT AttributeTreesOrderOk CHECK (Lft < Rgt),
CONSTRAINT AttributeTreesPrimKey PRIMARY KEY (ID),
CONSTRAINT AttributeTreesForiKey FOREIGN KEY (ValueID) REFERENCES AttributeValues
(ID)
)
''',
'''
CREATE INDEX AttributeTreesIdx_Tree ON AttributeTrees (Tree)
''',
'''
CREATE VIEW Attributes AS
SELECT
NodeTrees.ID AS ID,
NodeTrees.Lft AS Lft,
NodeTrees.Rgt AS Rgt,
NodeValues.ID AS ValueID,
NodeValues.Value AS Value,
NodeValues.BinaryID AS BinaryID,
NodeDefinitions.ID AS DefID,
NodeDefinitions.CODE AS DefCODE,
NodeDefinitions.Name AS DefName,
NodeTypes.ID AS TypeID,
NodeTypes.CODE AS TypeCODE,
NodeTypes.Name AS TypeName,
ParentTrees.ID AS ParentID,
ParentTrees.Lft AS ParentLft,
ParentTrees.Rgt AS ParentRgt,
ParentValues.ID AS ParentValueID,
ParentValues.Value AS ParentValue,
ParentValues.BinaryID AS ParentBinaryID,
ParentDefinitions.ID AS ParentDefID,
ParentDefinitions.CODE AS ParentDefCODE,
ParentDefinitions.Name AS ParentDefName,
ParentTypes.ID AS ParentTypeID,
ParentTypes.CODE AS ParentTypeCODE,
ParentTypes.Name AS ParentTypeName,
NodeTrees.Tree AS Tree
FROM (AttributeTrees AS NodeTrees
LEFT JOIN AttributeTrees AS ParentTrees
ON (SELECT MAX(Lft) FROM AttributeTrees
WHERE Lft < NodeTrees.Lft AND Rgt > NodeTrees.Lft) = ParentTrees.Lft)
LEFT JOIN (AttributeValues AS NodeValues
JOIN (AttributeDefinitions AS NodeDefinitions
JOIN AttributeTypes AS NodeTypes
ON NodeDefinitions.TypeID = NodeTypes.ID)
ON NodeValues.DefID = NodeDefinitions.ID)
ON (NodeTrees.ValueID = NodeValues.ID)
LEFT JOIN (AttributeValues AS ParentValues
JOIN (AttributeDefinitions AS ParentDefinitions
JOIN AttributeTypes AS ParentTypes
ON ParentDefinitions.TypeID = ParentTypes.ID)
ON ParentValues.DefID = ParentDefinitions.ID)
ON ParentTrees.ValueID = ParentValues.ID

[...]

AK