[Databankontwerp] Uitgebreide treeview opslaan én uitlezen

[Carharttguy]

Beste

Voor een hobby/leerproject ben ik bezig met een soort van pseudo-compiler die een 'zelf uitgevonden' taal vertaalt naar bekende/grote/veel gebruikte programmeertalen. (Tot nu toe alleen C#, later waarschijnlijk Java en Obj-C erbij)

Omdat dit programma dus een vertaalslag moet maken, heb ik een databank met daarin regels code. Nu wou ik die op een soort van treeview manier opslaan, zodat ik later zelf een kleine editor kan schrijven, waarmee ik gemakkelijk adhv die treeview kan zien waar ik die code opsla, een voorbeeldje van die 'treeview':

-Layout
--Control
---settext
---setheight
---setleft
---....
--Form
---Initialisation
---addcontrol
-Logic
--Variable
---declaration

De treeview kan (en zal) ook wel dieper gaan dan 3. (ongelimiteerd eigenlijk)
Nu heb ik op dat op een wel erg lame manier gedaan. Nu ziet mijn databankstructuur (voor zover je het structuur kan noemen) er zo uit:

id	name	code	platform
1	layout_control_settext	<de overeenkomstige code>	0

'Platform' verwijst naar een andere tabel, genaamd platforms, waar dan bijvoorbeeld 'Desktop' inzit (dat is dan C# code, want ik compileer met mono), maar in de toekomst kan daar ook 'Android' inzitten, dan zal '<de overeenkomstige code>' Java zijn, en er in de kolom platform iets anders zitten. Wat nu nog niet in de tabel zit, maar ik er wel in wil hebben, is een soort van reference code, dus bijvoorbeeld een row, en als die die eruit haalt, dan heb je eigenlijk 5 andere rows gecontamineerd.

Goed, dit is natuurlijk niet goed. Maar ik weet niet zo goed hoe ik het echt 'goed' moet doen, ik dacht aan een structuur zoals:

codeTabel:

id

name

code

platform

treeview tabel

id

parentid

childid

reference tabel

id

codeid

referenceid

Op die manier zou het (denk ik) ook wel gaan, maar dan heb ik nog totaal geen idee hoe ik de juiste code uit die database ga trekken met een query. Ik wil dat dan oplossen met een SQL-query, maar heb totaal geen idee hoe, ik dacht aan een function ofzo, maar ik weet dan ook niet hoe ik juiste parameters kan meegeven dat hij diep genoeg in de treeview kan.

Nu heb ik deze code in mijn programma om de juiste code uit de database te halen:

code:

  Dim ps as MySQLPreparedStatement = con.Prepare("SELECT code FROM code WHERE name = ? AND platform = ?")
  
  ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
  ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_LONGLONG)
  
  ps.Bind(0, Name.Lowercase)
  ps.Bind(1, cInt(platfrm))
  
  Dim rs As RecordSet = ps.SQLSelect
  
  If rs <> nil Then
    Dim ac As new aCode
    ac.Code = rs.Field("code").StringValue
    Return ac
  End if

Maar dat zal natuurlijk hevig veranderen, want als ik bijvoorbeeld wil zoeken naar 'initialisation', nja, dat kan van een form komen, maar natuurlijk ook van een object bijvoorbeeld. (En form zit dan weer onder layout, terwijl een object bij logic hoort).

Dus ik zou een SQL functie moeten hebben die iets doet als getCode('layout', 'object', 'initalisation', 'Desktop'), maarja, het kunnen zo 5 parameters zijn, maar ook 20 (bij wijze van spreken). En heb geen idee hoe ik dat in SQL functies moet oplossen. Kan iemand me een richting geven over hoe ik dit zou oplossen?

Of als iemand het helemaal anders zou aanpakken, dan hoor ik dat ook heel graag natuurlijk, andere inzichten kunnen heel nuttig zijn, daar ik niet zo thuis ben in deze materie, en dus nog veel te leren heb.

Alvast bedankt.

[winkbrace]

In Oracle heb je hiervoor de CONNECT BY clause.
Ik gebruik dit voor een menu structuur met onbekend aantal verdiepingen:

web_menu:

menu_id

parent_menu_id

text

url

SQL:

select sys_connect_by_path(wm.menu_id, ', ') path
,      wm.menu_id
,      wm.parent_menu_id
,      wm.text
from   web_menu wm
connect by prior wm.menu_id = wm.parent_menu_id
start with wm.menu_id = 1

path	menu_id	parent_menu_id	text
, 1	1		root
, 1, 2	2	1	main1
, 1, 2, 3	3	2	main1_sub1
, 1, 2, 4	4	2	main1_sub2
, 1, 5	5	1	main2
, 1, 5, 6	6	5	main2_sub1
, 1, 5, 7	7	5	main2_sub2
, 1, 5, 8	8	5	main2_sub3

Ik heb wel eens een methode gemaakt voor een php mysql class die dit simuleert met wat recursion:

PHP:

    /**
     * fetch array of complete query result
     * @param string $keyColumn
     * @return array
     */
    public function fetchAll($keyColumn = null)
    {
        $array = array();
        $i = 0;
        while ($row = $this->fetch())
        {
            $key = ! empty($keyColumn) ? $row[$keyColumn] : $i++;
            $array[$key] = $row;
        }
            
        return $array;
    }
    

    /**
     * create multi-dimensional array based on a connect by hierarchy
     * underlying rows are inserted at the end of the row as a sub array
     *
     * @param string $keyColumn
     * @param string $parentColumn
     */
    public function connectBy($keyColumn, $parentColumn)
    {
        $this->keyColumn = $keyColumn;
        $this->parentColumn = $parentColumn;
        
        $result = array();
        foreach ($this->fetchAll($keyColumn) as $row)
        {
            if (empty($row[$parentColumn]))
                $result[] = $row;
            else
                $result = $this->insertChild($result, $row);
        }
        
        return $result;
    }
    
    /**
     * recursive help function for connectBy to insert the given row in the right place in the array
     * @param array $array
     * @param array $row
     */
    protected function insertChild($array, $row)
    {
        $loopable = array_filter($array, function($val) { return is_array($val); });
        if (count($loopable) > 0)
        {
            foreach ($loopable as $i => $nrow)
            {
                if ($nrow[$this->keyColumn] == $row[$this->parentColumn])
                {
                    $array[$i][] = $row; // plaats de row ertussen als een level diepere array
                    break;
                }
                // doorloop eventuele dieper liggende arrays
                $array[$i] = $this->insertChild($nrow, $row);
            }
        }
        
        return $array;
    }

[pedorus]

Ik zou hiervoor je favoriete ORM gebruiken (bijv. Entity Framework) en de object in laten laden door de ORM of desnoods iets met recurive sql queries doen als het niet performed..

Die extra tabel lijkt me niet zo logisch, tenzij kinderen meerdere ouders hebben of je cirkelrelaties wil toestaan.

Voorbeeld van standaardstructuur:
SO: Tree stucture table and entity framework
MSDN: Recursive Queries Using Common Table Expressions
Of een klassieker: Crisp's blog: Formatting a multi-level menu using only one query

[Matis]

Als je ook nog volgorde wilt toepassen, zal ik eens kijken naar een Nested Set (indien je wilt met multiple roots).
Voor doctrine gebruik ik zelf de volgende extensie: https://github.com/blt04/doctrine2-nestedset

[Carharttguy]

Hartelijk dank voor de informatie, ik ga even onderzoeken.
@Pedorus, ja ik had al door dat er iets niet compleet logisch is, hoe zou jij het doen? In m'n codetabel een parentid meegeven, en geen extra tabel gebruiken?