MySQL Bugs: #4253: add a "herarchical data structure"

Bug #4253	add a "herarchical data structure" - table type
Submitted:	22 Jun 2004 23:20	Modified:	13 Dec 2005 16:36
Reporter:	shimon doodkin	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
i would like to have multi fileded rows in  mysql like a dynamic row. you dont need to define it, evry thing you put in the table it can take.
like auto extendable table.

and i would like to have data interface like xml but not so difficult more like to sql;
like an opp data structure with fast calculations and data access of mysql
i need such thing for a questionnaire application with sections and subsections that are simular.

tableusers->user->answes->answers_group->ans->int_questionid
tableusers->user->answes->answers_group->ans->tinyint_answer
tableusers->user->answes->answers_group->subgroup->ans->int_questionid
tableusers->user->answes->answers_group->subgroup->ans->tinyint_answer
select avg(tableusers->user[]->answes[]->[..]->ans[]->tinyint_answer) where tableusers->user[]->answes[]->[..]->ans[]->int_questionid=3

select an average of answer for each user , for each of answers, for any deph until any group that have a children named ans, get the tinyint_answer value , 
compare int_questionid to 3 if it equals return its value to
average calculation function.

short version of query:
select avg(tinyint_answer) from tableusers->user[]->answes[]->[..]->ans[]where int_questionid=3

in  previous query 'from' was omitted

what do you think about my idea?

please give me a feed back

How to repeat:
imagine

Suggested fix:
data stored will be hierarchically

learn program languages to make your SQL and you will get what you need

i am great programmer.
i have an idea to enchance mysql server.

to preform same style like above querys, you have to write your interfase from program side , it would be great if mysql server would be able to do this.

to make sql server simular to microsoft active directory  with schema and add query ability like i showed it above.

it will make database server more powerfull with herarchy ablilities that have
great useablility with xml.

in simple way to impliment it, it may be a database type or a procedure set, or by any other idea.

it may require changes in protocol to handle herarchilcal data, or maybe use it as php_serialize ..

this is the data structure:

tableusers->user->answes->answers_group->ans->int_questionid
tableusers->user->answes->answers_group->ans->tinyint_answer
tableusers->user->answes->answers_group->subgroup->ans->int_questionid
tableusers->user->answes->answers_group->subgroup->ans->tinyint_answer

this is a simple query on this data structure:

select avg(tableusers->user[]->answes[]->[..]->ans[]->tinyint_answer) where 
tableusers->user[]->answes[]->[..]->ans[]->int_questionid=3

the same query may have a base start point

so short version of query:

select avg(tinyint_answer) from tableusers->user[]->answes[]->[..]->ans[] where
int_questionid=3

select   //operation to preform (standart sql etc.. like select, update, delete)
 avg( // function
tableusers // start point the table to be used
->  // like in c plus plus  (access a sub object )
user[] // access object of type of an associative array or object like in javascript.
// object like in javascript. like you have an object and you may set its
// properties
->    // like in c++ 
answes[] // access an array answes from array users that in table tableusers .
->[..]->ans[]    // access <b>any</b> array that have a 
                      // children "ans" and its type is an array (the [..] is like from pascal array definition )
->  // like in c++ 
tinyint_answer // the destanation value
)
where 
tableusers
->
user[]
->
answes[]->
[..]
->
ans[]
->
int_questionid=3

in trems of file system it should grab all the values of tinyint_answer file and calculate its average from all directories that match 
tableuserso(drive):/user/answes/[any directory]/ans/
where the  value of file int_questionid in that directoriy is 3

Why not request that MySQL be able to store XML documents natively in a manner that supports XQuery as a query language? All the cool kids are doing it (Oracle 9i, Microsoft SQL Server 2005).

Thank you for a feature request. 

All these "object-relational" extensions are completely non-relational (they are against Codd rules and 1NF) and should not be used as a structure to store data in any relational DBMS. Even Oracle that allows object types stores their data in simple relational tables. 

So, if you need this kind of structures, you can create them in your application and map to the appropriate set of relational tables.

I do not think that the feature you proposed is needed or should be implemented in MySQL.

As for a "native" storage for XML and XQuery support, it is the other story and other feature request. You may try to create your "XML storage engine", using MySQL way to implement things...