Author Topic: Graphical view on pluto_main  (Read 2907 times)

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Graphical view on pluto_main
« 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
rock your home - http://www.agocontrol.com home automation

Matthew

  • Douchebag
  • Addicted
  • *
  • Posts: 567
    • View Profile
Re: Graphical view on pluto_main
« Reply #1 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 package.

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #2 on: December 13, 2007, 12:11:15 am »
thank you for the hint..
rock your home - http://www.agocontrol.com home automation

chriss

  • Veteran
  • ***
  • Posts: 140
    • View Profile
Re: Graphical view on pluto_main
« Reply #3 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

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #4 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
rock your home - http://www.agocontrol.com home automation

Matthew

  • Douchebag
  • Addicted
  • *
  • Posts: 567
    • View Profile
Re: Graphical view on pluto_main
« Reply #5 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 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?
« Last Edit: January 14, 2008, 03:32:30 pm by Matthew »

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #6 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
rock your home - http://www.agocontrol.com home automation

Matthew

  • Douchebag
  • Addicted
  • *
  • Posts: 567
    • View Profile
Re: Graphical view on pluto_main
« Reply #7 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".

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #8 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
rock your home - http://www.agocontrol.com home automation

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #9 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
rock your home - http://www.agocontrol.com home automation

Matthew

  • Douchebag
  • Addicted
  • *
  • Posts: 567
    • View Profile
Re: Graphical view on pluto_main
« Reply #10 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.

chriss

  • Veteran
  • ***
  • Posts: 140
    • View Profile
Re: Graphical view on pluto_main
« Reply #11 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

royw

  • Guru
  • ****
  • Posts: 261
    • View Profile
Re: Graphical view on pluto_main
« Reply #12 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).  The script runs on 0710B2 without any additional requirements.

The script is at:

  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

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

hari

  • Administrator
  • LinuxMCE God
  • *****
  • Posts: 2420
    • View Profile
    • ago control
Re: Graphical view on pluto_main
« Reply #13 on: January 26, 2008, 11:01:38 am »
hey thats awesome ;)

best regards,
Hari
« Last Edit: January 26, 2008, 02:15:37 pm by hari »
rock your home - http://www.agocontrol.com home automation

Matthew

  • Douchebag
  • Addicted
  • *
  • Posts: 567
    • View Profile
Re: Graphical view on pluto_main
« Reply #14 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%?