LinuxMCE Forums

General => Developers => Topic started by: hari on December 12, 2007, 07:25:38 pm

Title: Graphical view on pluto_main
Post by: hari on December 12, 2007, 07:25:38 pm
Hi devs,

i did a visio import of "pluto_main":
http://vt100.at/pluto.png
http://vt100.at/pluto.vsd

maybe someone with a big screen and patience can interconnect the foreign keys and align the tables (by repo?)?

best regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: Matthew on December 12, 2007, 11:50:41 pm
i did a visio import of "pluto_main":
http://vt100.at/pluto.png
http://vt100.at/pluto.vsd

maybe someone with a big screen and patience can interconnect the foreign keys and align the tables (by repo?)?

I tried to run sqlt-diagram on the (92MB) pluto_main_build.sql , but the process ran out of memory and quit:
Code: [Select]
#  sqlt-diagram --db=MySQL --output pluto_main_build.v1321-7.png --title pluto_main_build.v1321-7 --color --natural-join-pk pluto_main_build.v1321-7.sql
(2h pass)
Out of memory!

# free
             total       used       free     shared    buffers     cached
Mem:        507680      23724     483956          0        472       8320
-/+ buffers/cache:      14932     492748
Swap:      3919852       5564    3914288

I don't have any machines running Linux with more than 512MB right now. sqlt-diagram claims to map foreign keys in its bitmap ER diagrams. sqlt-graph claims to produce better (clearer) output that is best rendered by GraphViz. They're part of the sqlfairy (http://sqlfairy.sourceforge.net/) package.
Title: Re: Graphical view on pluto_main
Post by: hari on December 13, 2007, 12:11:15 am
thank you for the hint..
Title: Re: Graphical view on pluto_main
Post by: chriss on January 14, 2008, 10:47:46 am
Quote
maybe someone with a big screen and patience can interconnect the foreign keys and align the tables (by repo?)?

Hari, I can do that. Does the DB stay the same in 0710 or are there major differences? If so, I would wait for 0710 to do this task.

Cheers,
/Chriss
Title: Re: Graphical view on pluto_main
Post by: hari on January 14, 2008, 12:58:42 pm
Quote
maybe someone with a big screen and patience can interconnect the foreign keys and align the tables (by repo?)?

Hari, I can do that. Does the DB stay the same in 0710 or are there major differences? If so, I would wait for 0710 to do this task.

Cheers,
/Chriss
i'll try to research that, every change should be documented in the schema tables.

best regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 14, 2008, 03:28:40 pm
Quote from: Hari
maybe someone with a big screen and patience can interconnect the foreign keys and align the tables (by repo?)?

Hari, I can do that. Does the DB stay the same in 0710 or are there major differences? If so, I would wait for 0710 to do this task.

Having the schemas for each of the two different versions (0704 & 0710) would be very useful in adapting existing docs (and code) to the new 0710 schema. So if you could do both, we could see the differences (if any, which I'm sure there are).

I tried to run sqlt-diagram on the (92MB) pluto_main_build.sql , but the process ran out of memory and quit:
Code: [Select]
#  sqlt-diagram --db=MySQL --output pluto_main_build.v1321-7.png --title pluto_main_build.v1321-7 --color --natural-join-pk pluto_main_build.v1321-7.sql
(2h pass)
Out of memory!

# free
             total       used       free     shared    buffers     cached
Mem:        507680      23724     483956          0        472       8320
-/+ buffers/cache:      14932     492748
Swap:      3919852       5564    3914288

I don't have any machines running Linux with more than 512MB right now. sqlt-diagram claims to map foreign keys in its bitmap ER diagrams. sqlt-graph claims to produce better (clearer) output that is best rendered by GraphViz. They're part of the sqlfairy (http://sqlfairy.sourceforge.net/) package.

If you have a fairly fast machine with quite a lot of RAM (>512MB, probably >1GB), do you think you could run sqult-diagram or sqlt-graph on each of them for us?
Title: Re: Graphical view on pluto_main
Post by: hari on January 14, 2008, 08:52:22 pm
Having the schemas for each of the two different versions (0704 & 0710) would be very useful in adapting existing docs (and code) to the new 0710 schema. So if you could do both, we could see the differences (if any, which I'm sure there are).
you don't ask him doing that huge amount of work twice for a graphical diff, do you?
*cough*
Quote
If you have a fairly fast machine with quite a lot of RAM (>512MB, probably >1GB), do you think you could run sqult-diagram or sqlt-graph on each of them for us?
i vote for >2GB ;)

the result's don't help much, you can't rearrange the tables.

regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 14, 2008, 09:00:43 pm
Having the schemas for each of the two different versions (0704 & 0710) would be very useful in adapting existing docs (and code) to the new 0710 schema. So if you could do both, we could see the differences (if any, which I'm sure there are).
you don't ask him doing that huge amount of work twice for a graphical diff, do you?
*cough*

Well, I did as much of one run as I could on my HW before the limited RAM aborted the process. It wasn't any work for me, just the machine, after I searched up the tools to do it. Which I've contributed. If I had a bigger machine, I'd do each one myself (I'd have done it weeks ago).


If you have a fairly fast machine with quite a lot of RAM (>512MB, probably >1GB), do you think you could run sqult-diagram or sqlt-graph on each of them for us?
i vote for >2GB ;)

the result's don't help much, you can't rearrange the tables.

I don't know what you mean  by "rearrange the tables". Realize that I'm not asking for a "graphical diff", I'm asking for a single graph (or data to plot one) from each of the two schema versions. I didn't expect to do a graphical diff, though I probably would have started looking for a tool to do that if I though the data were available. I really just expected to do a "diff" by personally comparing the two different graphs "manually".
Title: Re: Graphical view on pluto_main
Post by: hari on January 14, 2008, 09:01:19 pm
i'll try to research that, every change should be documented in the schema tables.
as i have all my installations synced to the testing repo could somebody please dump the output of "/usr/pluto/bin/sqlCVS" for both a 0704 and a 0710beta?

one can view the changes to the schema on a 0710 beta with some SQL statement like
Code: [Select]
mysql pluto_main
select * from psc_<repo>_schema where PK_psc_<repo>_schema > <schema value from 0704 for repo>
<repo> takes values like constants,dce,designer,document,ir,local,website

best regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: hari on January 14, 2008, 09:05:19 pm
...
sorry, my misunderstanding. I thought you wanted him to do the visio job twice *grin*

here is some sample output, but it does not help in my eyes. It's so huge we have to group the tables:
http://vt100.at/pluto2.png

regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 14, 2008, 09:13:34 pm
...
sorry, my misunderstanding. I thought you wanted him to do the visio job twice *grin*

Aargh, that would be murder. That kind of task is why we've enslaved these metal monsters who suck up all our time :).



here is some sample output, but it does not help in my eyes. It's so huge we have to group the tables:
http://vt100.at/pluto2.png

That's pretty big, but it's not insane. I bet there's some factoring and deprecation that could make it smaller, if not less complex, while retaining high normalization. But it's not really overwhelming, except when all the relations are shown at once, considering everything it's managing. If there were a graph rendering tool that could group tables into clusters in a zoomable scope, that would be good. Or just a tool that de/selected relations of only selected tables, or against SQL statements extracted from the code.
Title: Re: Graphical view on pluto_main
Post by: chriss on January 15, 2008, 10:32:29 am
Hej,

I'm about 40% finished modeling the relationship between tables, maybe I can finish that part today. But the Diagram looks like a mess... cleaning up the chart by grouping the tables and make it look nice may take some more time :(

BTW, I did also think about changes that might come with 0710. I think it might be the easiest way to integrate them manually into the 0704 ER model...

/Chriss
Title: Re: Graphical view on pluto_main
Post by: royw on January 26, 2008, 07:07:16 am
Howdy,

Not sure how useful this is to others, but I wrote a script that reads the structure of a mysql database and then generates a mindmap for viewing with FreeMind 0.9.0 Beta 15 (http://freemind.sourceforge.net/wiki/index.php/Main_Page (http://freemind.sourceforge.net/wiki/index.php/Main_Page)).  The script runs on 0710B2 without any additional requirements.

The script is at:

  http://roy.wright.org/linuxmce/mysql2mm.rb (http://roy.wright.org/linuxmce/mysql2mm.rb)

An example mindmap (ok, it's pluto_media database 0710B2)  is at:

  http://roy.wright.org/linuxmce/pluto_media.mm (http://roy.wright.org/linuxmce/pluto_media.mm)

Basically what this shows you is the linkage of foreign keys to primary keys, with the assumption that FK_name is linked to PK_name.  The script probably needs a little more smarts here.  Also each table description is attached as a note to the table's node.

One thing I like about the mindmaps is being able to collapse what I'm not interested in.  Also it's nice to just add notes as I'm working.

Have fun,
Roy
Title: Re: Graphical view on pluto_main
Post by: hari on January 26, 2008, 11:01:38 am
hey thats awesome ;)

best regards,
Hari
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 26, 2008, 02:10:29 pm
Hej,

I'm about 40% finished modeling the relationship between tables, maybe I can finish that part today. But the Diagram looks like a mess... cleaning up the chart by grouping the tables and make it look nice may take some more time :(

BTW, I did also think about changes that might come with 0710. I think it might be the easiest way to integrate them manually into the 0704 ER model...

Any luck with the remaining 60%?
Title: Re: Graphical view on pluto_main
Post by: chriss on January 27, 2008, 04:16:14 pm
Quote
Any luck with the remaining 60%?

Unfortunately I didn't have that much time during the last week. I can tell you, this is quite a time consuming task ;)
You can watch the current status: http://www.schleiffer.net/linuxmce/pluto_main.png
I'll keep working on that beast and will upload it to the wiki as soon as I'm done.

Chriss
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 27, 2008, 08:03:22 pm
Quote
Any luck with the remaining 60%?

Unfortunately I didn't have that much time during the last week. I can tell you, this is quite a time consuming task ;)
You can watch the current status: http://www.schleiffer.net/linuxmce/pluto_main.png
I'll keep working on that beast and will upload it to the wiki as soon as I'm done.

Cool - every little bit counts. As soon as the Device and Template tables have more relations mapped, it'll be helping me directly. Thanks.
Title: Re: Graphical view on pluto_main
Post by: chriss on January 27, 2008, 08:41:32 pm
As soon as the Device and Template tables have more relations mapped, it'll be helping me directly.

Ok, I can go on with these tables. Tomorrow I'm going to be on the train for a couple of hours, i.e. I can work on that stuff.
Title: Re: Graphical view on pluto_main
Post by: Matthew on January 28, 2008, 03:20:47 am
Maybe royw's mindmap point in this thread (http://forum.linuxmce.org/index.php?topic=3459.msg22903#msg22903) and lon2's data dictionary point in "Re: Database (Pluto_media) ERD" (http://forum.linuxmce.org/index.php?topic=3791.msg22801#msg22801), as well as chriss' ERD points in this thread (http://forum.linuxmce.org/index.php?topic=3459.msg23002#msg23002) suggest a team of people who can work together to wiki a solid beginning of a LMCE DB document.

FWIW, I hope the docs/diagrams include a version number, as 0704 is different from 0710bN, and the different bNs (1, 2, 3 so far, and SVN snapshots), all could be different.
Title: Re: Graphical view on pluto_main
Post by: royw on March 06, 2008, 08:38:54 pm
Howdy,

I've been working on a utility that generates ruby on rails models from the linuxmce databases.  One of the utilities available for rails is railroad (http://railroad.rubyforge.org/ (http://railroad.rubyforge.org/)) which can create graphical views of the data model relationships.  The normal graph for models is dot, which views as a couple of rows.  This doesn't work too well for the 319 models of LinuxMCE.  Controllers are normally displayed using neato, which views most interconnects in the center, fewer interconnects away from the center.  For grins I created a neato view of the models.  This is pretty close to an ERD.  But with 319 models, it doesn't look too useful.  For your viewing pleasure, here are the graphs:

  http://roy.wright.org/linuxmce/models.dot.svg (http://roy.wright.org/linuxmce/models.dot.svg)
  http://roy.wright.org/linuxmce/models.neato.svg (http://roy.wright.org/linuxmce/models.neato.svg)

The size of the SVG files seems to cause firefox some problems, I found that inkscape (http://www.inkscape.org/ (http://www.inkscape.org/)) views them well.  The zoom in/out (+/-) helps.

See Graphviz (http://www.graphviz.org/ (http://www.graphviz.org/)) for details on dot and neato.

BTW, I think I have all the relationships being modeled correctly.  I'm going to start a UI today to give a few of models a more thorough check out.  I don't see anyway to automate generating field validations, so there is a lot of work ahead.

Just to give you a taste of model usage:

>> PlutoMedia::File.find(1520).media_type.Description
=> "LinuxMCE Pictures"

>> PlutoMain::User.find_by_UserName('Roy').user_mode.Description
=> "At Home"

The first is an external join for PK_File == 1520 (the media_type references PlutoMain::MediaType).  The later is a foreign key join (user_mode references PlutoMain::UserMode).  IMO mucho prettier than SQL.   :)

Have fun,
Roy