Mantis Bugtracker          
testlink.org

View Issue Details Jump to Notes ] Issue History ] Print ]
IDProjectCategoryView StatusDate SubmittedLast Update
0004004TestLinkDatabase Generalpublic2010-11-11 12:492014-06-01 08:44
Reporterkinow 
Assigned Tokinow 
PrioritynormalSeverityminorReproducibilityN/A
StatusassignedResolutionopen 
PlatformOSOS Version
Product Version2.0 (planned) 
Fixed in Version 
Summary0004004: Document TestLink database schema documentation
DescriptionDocument TestLink DB Schema using some tool to assist in this task.
TagsNo tags attached.
Database (MySQL,Postgres,etc)N/A
Browser
PHP Version
TestCaseID
QA Team - Task Workflow Status
Attached Files? file icon testlink_schemaspy.zip.001 [^] (4,194,304 bytes) 2010-11-14 04:43
? file icon testlink_schemaspy.zip.002 [^] (4,194,304 bytes) 2010-11-14 04:45
? file icon testlink_schemaspy.zip.003 [^] (551,078 bytes) 2010-11-14 04:49
zip file icon sql-power-architect.zip [^] (1,492,433 bytes) 2010-12-02 01:06
zip file icon aqua-datastudio.zip [^] (295,931 bytes) 2010-12-02 01:31
? file icon schema-spy.zip.001 [^] (3,145,728 bytes) 2010-12-02 01:50
? file icon schema-spy.zip.002 [^] (3,145,728 bytes) 2010-12-02 01:51
? file icon schema-spy.zip.003 [^] (468,262 bytes) 2010-12-02 01:52
zip file icon testlink data model tool comparison matrix.zip [^] (1,161,298 bytes) 2010-12-06 01:55
? file icon testlink_db.mwb [^] (56,772 bytes) 2011-03-30 21:34
png file icon testlink_db.png [^] (472,554 bytes) 2011-03-30 21:35
png file icon db_schema_testlink_1.9.11.png [^] (412,540 bytes) 2014-06-01 08:42

- Relationships
parent of 0006357closedfman Database Schema - TestLink 1.9.x 
related to 0004348closedfman Relationship between the date of req version and project 

-  Notes
(0012439)
fman (administrator)
2010-11-13 16:49
edited on: 2010-11-13 17:01

An important feature that we need to check for existence on choosen tool is possibility to create graphically ( Drag and drop ) sub models, in order to give a top down vision of Schema

Please give a try :
to http://www.sqlpower.ca/page/architect_download_os [^]

I've just downloaded schemaSPy GUI and plan to give a look tomorrow or during the week, to give you my impressions

(0012442)
kinow (reporter)
2010-11-14 04:34

Hi Francisco,

I uploaded the video and the documentation to my site cause I didn't know the maximum allowed size for attachments. I'm uploading it here.

The GUI saves a XML file with the configuration to generate the documentation. The configuration contains things like Database driver location, Meta XML file, database name, etc.

I'm going to download Postgres DB and I've already used SQL Power before (after downloading the installer I realized I already had it installed), it's an excellent tool too!

I'll send you my impressions on this tool too. Thanks!
(0012444)
fman (administrator)
2010-11-14 09:13

OK, thanks for your work.
If we need to increase fileuplad limit here, we can talk to Asiel

Regards
(0012796)
kinow (reporter)
2010-12-02 01:05

Hi Francisco, I played for a while with three tools this week: Aqua Studio, SQL Power Architect and Schema-Spy. Here are my first impressions on each tool:

Aqua Studio: I love this tool. I've been using it since version 4. It is useful specially for SQL queries. It has a ERWIN Diagram generator tool able to generate a ERWIN model of TestLink Database. The model can be used to analyze the tables and its relationships. We just have to reallocate some tables and that's all. It also generates a HTML file with information on each table (no diagram though).

SQL Power Architect: I used this tool some time ago when I tried switch from Mysql DBDesigner. When we generate the model from the database tables it gets really messy. It is very hard to organize the tables and relationships. It also generates a HTML file with information on each table (no diagram though).

Schema Spy: I never used this tool before this issue. It is a small java jar that is able to generate a HTML with diagram and information from the database tables through a XML metadata that we would have to write and update as we changed something in the database. It uses GraphViz to generate the diagram. We can't reallocate the tables and relationships in the database model )-:

I attached the output of these three tools. During this week until next weekend I'll be testing TOAD and looking for more tools to test. BTW, I'm using TestLink 1.9.0 and PostgreSQL.

I'll update this issue as soon as I have something :-)

Bruno
(0012797)
fman (administrator)
2010-12-02 05:44

OK, good job.
Some thoughts:
1. do not search the perfect tools, does not exist.
2. set (yoursels) this week end as dead line
3. do a ranking of tools / comparison matrix with 4 not more (IMHO) features

at least these two has to be on this matrix (you have already mention in some way these
features in your comments)
a. If is not open source, company is willing to give us some licences.
b. diagram can be managed manually to set table position on diagram ?

do not think HTML is a must that we need to search.

Thanks
(0012798)
fman (administrator)
2010-12-02 05:45

Reminder sent to: Julian

Give a look to results (with Andreas) when you will have time.
your feedback is appreciated (is not urgent)
(0012805)
fman (administrator)
2010-12-02 10:39

>> dont such diagrams only make sense if relations between tables are used?
when relation (Referential Integrity) can not be set on DB, has to be documented manually.

Relations (Foreing Keys) exists where possible (at least on Postgres where is simple to enable it). We need to give a look (I've tried with a sound failure time ago) on MySQL but requires use of INNODB.

>>> nothing surprising to me. our database is easy to read without visualization.
lot of users complain about this doc is missed. I agree with you that a mean user with will to understand SCHEMA, can do it without diagrams.
But Remember diagrams are fancy and cool, and people like this kind of things.
(0012807)
kinow (reporter)
2010-12-02 12:40

@fman
I got it. I'll create a comparison matrix with more or less 4, 5 tools and its features, as well as the final diagram. For the commercial tools, I'll drop a message to its companies asking about Open Source licenses. Do you know if we already have the OK for an Open Source license of any company? Like Aquafold, for instance? Just to avoid asking them twice.

@Julian
I had thought only about the documentation goal for these diagrams. But now that you mentioned, I agree that with the diagram we could check whether some relationships can or must be improved.

Cheers
(0012808)
fman (administrator)
2010-12-02 13:45

I've got license from Aqua fold, I will try to send it to you.
(0012812)
fman (administrator)
2010-12-02 17:28

I've no luck trying to use InnoDB help is welcomed
(0012813)
kinow (reporter)
2010-12-02 17:41

I think we could open another issue for this. I would like to try reproduce all relations existing in Postgres in a InnoDB MySQL database. This would give me a better understanding of the Database model (-: What do you guys think?
(0012818)
fman (administrator)
2010-12-02 19:30

Think that having FK on MySQL will be OK.
go and open A PRIVATE issue regarding enabling FK on MySQL (please do search here to understand if similar issue does exist)
(0012821)
kinow (reporter)
2010-12-03 03:56

@fman
I found an old issue related to this discussion. Shall we resurrect it or create a new one?

http://mantis.testlink.org/view.php?id=1601 [^]
(0012824)
fman (administrator)
2010-12-03 06:14

use 1601 -> assign to yourself
Important thing:
creation of innoDB has to be managed with our installation scripts.

thanks
(0012852)
kinow (reporter)
2010-12-06 01:58

Hi guys,

I created a basic data modeling tools comparison matrix for TestLink. The tools analysed were Data Studio, SQL Power Architect, Schema Spy, Toad and DBDesigner. I know it's still very basic, but I can add more information as we decide what else should us look for in a data modeling tool. The enclosed file name is "testlink data model tool comparison matrix.zip".

Cheers
(0014262)
fman (administrator)
2011-03-25 07:18

@kinow
there are details that can not be extracted just from reverse engineering the db.
Please add this details here (node_hierarchy container of all parent/child relatiosn) in order to make thi useful for users
(0014370)
kinow (reporter)
2011-03-30 23:42

Hi all, just uploaded two files, testlink_db.mwb is the MySQL Workbench model with InnoDB relationships. It's huge, but the tool highlights the relationships what helps a lot. The other png file is the diagram exported as image.
(0014729)
kinow (reporter)
2011-04-21 13:34

Node tree
=========

In TestLink database we have an entity called node. A node can represent a:

- Test project
- Test plan
- Test suite
- Test case
- Test case version
- Test case step
- Requirement specification
- Requirement
- Requirement version
- Requirement revision

The table for the nodes entity is `nodes_hierarchy`.

mysql> describe nodes_hierarchy;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(100) | YES | | NULL | |
| parent_id | int(10) unsigned | YES | MUL | NULL | |
| node_type_id | int(10) unsigned | NO | | 1 | |
| node_order | int(10) unsigned | YES | | NULL | |
+--------------+------------------+------+-----+---------+----------------+

The node id is its primary key.

The name is used as a description for the node. If the node is a test project then this field contains the test project name.

The parent_id contains a reference for a parent node's id. This way we can build a hierarchy of nodes (node tree), with a parent node and its children nodes.

The node_type_id is the foreign key for the table `node_types`, that represent the node type entity. This entity tells us, as the name says, what is the type of the node.

And lastly, the node_order field. It is used in conjunction with the parent_id field to define the order of the node in the node tree. Say we have a test suite node with three test case nodes. Each test case will have as parent_id the id of the test suite and the node_order defines the order that the test case appears under the test suite in the tree.

Here is the description of the node_types table.

mysql> describe node_types;
+-------------+------------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+-------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| description | varchar(100) | NO | | testproject | |
+-------------+------------------+------+-----+-------------+----------------+
(0021025)
fman (administrator)
2014-06-01 08:41

Schema for 1.9.11 (NEXT STABLE RELEASE), generated using Postgresql DB (because we have Foreing Key defined), using tool:

DbSchema Free Edition
DbSchema version 6.4.0 build 3991 Java 1.7.0_55
Vendor Oracle Corporation
http://www.dbschema.com [^] (c) Copyright Wise Coders Solutions.
All rights reserved.
Many thanks to Rui Martins for contributions.


see attached file db_schema_testlink_1.9.11.png

- Issue History
Date Modified Username Field Change
2010-11-11 12:49 kinow New Issue
2010-11-11 12:49 kinow Status new => assigned
2010-11-11 12:49 kinow Assigned To => kinow
2010-11-13 16:33 fman View Status public => private
2010-11-13 16:49 fman Note Added: 0012439
2010-11-13 17:01 fman Note Edited: 0012439 View Revisions
2010-11-14 04:34 kinow Note Added: 0012442
2010-11-14 04:43 kinow File Added: testlink_schemaspy.zip.001
2010-11-14 04:45 kinow File Added: testlink_schemaspy.zip.002
2010-11-14 04:49 kinow File Added: testlink_schemaspy.zip.003
2010-11-14 09:13 fman Note Added: 0012444
2010-12-02 01:05 kinow Note Added: 0012796
2010-12-02 01:06 kinow File Added: sql-power-architect.zip
2010-12-02 01:31 kinow File Added: aqua-datastudio.zip
2010-12-02 01:50 kinow File Added: schema-spy.zip.001
2010-12-02 01:51 kinow File Added: schema-spy.zip.002
2010-12-02 01:52 kinow File Added: schema-spy.zip.003
2010-12-02 05:44 fman Note Added: 0012797
2010-12-02 05:45 fman Note Added: 0012798
2010-12-02 10:39 fman Note Added: 0012805
2010-12-02 12:40 kinow Note Added: 0012807
2010-12-02 13:45 fman Note Added: 0012808
2010-12-02 17:28 fman Note Added: 0012812
2010-12-02 17:41 kinow Note Added: 0012813
2010-12-02 19:30 fman Note Added: 0012818
2010-12-03 03:56 kinow Note Added: 0012821
2010-12-03 06:14 fman Note Added: 0012824
2010-12-06 01:55 kinow File Added: testlink data model tool comparison matrix.zip
2010-12-06 01:58 kinow Note Added: 0012852
2011-02-28 11:32 fman View Status private => public
2011-03-23 18:48 fman Relationship added related to 0004348
2011-03-25 07:18 fman Note Added: 0014262
2011-03-30 21:34 kinow File Added: testlink_db.mwb
2011-03-30 21:35 kinow File Added: testlink_db.png
2011-03-30 23:42 kinow Note Added: 0014370
2011-04-21 13:34 kinow Note Added: 0014729
2014-06-01 08:41 fman Note Added: 0021025
2014-06-01 08:42 fman File Added: db_schema_testlink_1.9.11.png
2014-06-01 08:44 fman Relationship added parent of 0006357



Copyright © 2000 - 2020 MantisBT Team
Powered by Mantis Bugtracker