LinuxMCE Forums
General => Developers => Topic started 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
-
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:
# 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.
-
thank you for the hint..
-
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
-
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
-
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:
# 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?
-
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*
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
-
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".
-
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
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
-
...
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
-
...
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.
-
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
-
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
-
hey thats awesome ;)
best regards,
Hari
-
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%?
-
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
-
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.
-
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.
-
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.
-
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