Post Reply 
 
Thread Rating:
  • 2 Votes - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Create a WebERP database Schema
01-22-2012, 11:10 AM (This post was last modified: 01-22-2012 04:08 PM by terryp.)
Post: #1
Create a WebERP database Schema
Method 1: Using SQL::Translator.

SQL::Translator is a group of Perl modules that manipulate structured data definitions (mostly database schemas) in interesting ways, such as converting among different dialects of CREATE syntax (e.g., MySQL-to-Oracle), visualizations of schemas (pseudo-ER diagrams: GraphViz or GD), automatic code generation (using Class::DBI), converting non-RDBMS files to SQL schemas (xSV text files, Excel spreadsheets), serializing parsed schemas (via Storable, YAML and XML), creating documentation (HTML and POD), and more. New to version 0.03 is the ability to talk directly to a database through DBI to query for the structures of several databases.


This is the method I used on my Linux Desktop to create a Schema of the WebERP tables:-

1) Install the latest SQL-Translator from http://search.cpan.org/CPAN/authors/id/R...06.tar.gz. The version my Debian system installed was too old, and it bombed on errors processing the mysqldump file.

2) Create a dump of your database
# mysqldump -u <user> -p<password> -d <database> > mydatabase.sql

3) create the Schema as a png picture
# /usr/local/bin/sqlt-graph -f MySQL --color -o mydatabase_diagram.png mydatabase.sql

Here is a link to one of the Schema pictures I created:-
It's huge at 6530x2634 pixels, so your picture viewer will need to be able to pan.

http://www.portertech.org/test/webERP/my...0x2634.png

Cheers
Terry
Find all posts by this user
Quote this message in a reply
01-22-2012, 12:14 PM
Post: #2
RE: Create a WebERP database Schema using SQL::Translator
Nice one Terry ... I'll put this up at the wiki developer page too.

Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Visit this user's website Find all posts by this user
Quote this message in a reply
01-22-2012, 04:18 PM (This post was last modified: 01-22-2012 04:58 PM by terryp.)
Post: #3
RE: Create a WebERP database Schema
Method 2: Using schemaSpy
This method produces a *much* more refined, and detailed multipage HTML Schema than SQL::Translator does, plus it is multiplatform, being Java based.


Homepage: http://schemaspy.sourceforge.net/

Install the following on Debian Linux, other platforms will have similar programs.
• schemaSpy_5.0.0.jar (248.9 kB) from http://sourceforge.net/projects/schemaspy/
• Java 5 or higher http://www.java.com/en/download/manual.jsp?locale=en, I used jre-6u30-linux-i586.bin
• apt-get install libmysql-java

Run the following command line string to produce the Schema in a directory of files for browser viewing. Note the Mysql database may be on another Host:-

# deb505:/usr/local/src# /usr/local/jre1.6.0_30/bin/java -jar ./schemaSpy_5.0.0.jar -t mysql -host localhost -db <mysql database name> -u <your mysql user> -p <password> -dp /usr/share/java/mysql-connector-java-5.1.6.jar -o /<directory for the Schema web files>

Using database properties:
[./schemaSpy_5.0.0.jar]/net/sourceforge/schemaspy/dbTypes/mysql.properties
Gathering schema details.........................................................................​....................................................(3sec)
Writing/graphing summary..............(16sec)
Writing/diagramming details.........................................................................​.................................................(110sec)
Wrote relationship details of 122 tables/views to directory '/var/www/weberpschema' in 130 seconds.
View the results by opening /var/www/weberpschema/index.html

You can view the very detailed output here, note my database name used in this Schema is "tjporter":-

http://www.portertech.org/test/webERP/we...index.html

Cheers
Terry
Find all posts by this user
Quote this message in a reply
01-22-2012, 04:31 PM
Post: #4
RE: Create a WebERP database Schema
Hi Terry,

Are you happy to have this link on weberp.org to this info??

http://www.weberp.org/WeberpDevelopment

Phil Daintree
webERP Admin
Logic Works Ltd
http://www.logicworks.co.nz
Visit this user's website Find all posts by this user
Quote this message in a reply
01-22-2012, 04:35 PM
Post: #5
RE: Create a WebERP database Schema
(01-22-2012 04:31 PM)phil Wrote:  Hi Terry,

Are you happy to have this link on weberp.org to this info??

http://www.weberp.org/WeberpDevelopment

Hi Phil,
Of course, no need to ask.

Cheers
Terry
Find all posts by this user
Quote this message in a reply
05-13-2013, 07:19 AM (This post was last modified: 05-13-2013 07:20 AM by Farbod.)
Post: #6
Lightbulb RE: Create a WebERP database Schema
Use MySQL Workbench.

MySQL Workbench is a powerful tool developed by MySQL with three primary areas of functionality:
SQL Development: Replaces MySQL query browser. Allows the user to connect to an existing database and edit and execute SQL queries.
Data Modeling: Complete visual database design and modeling.
Database Administration: Replaces MySQL administrator. Graphic interface to start/stop servers, create user accounts, edit configuration files, etc.

It is free and it is very useful.

"Create EER Model from Existing Database"
This action item enables you to create an EER Model from an existing live database. Clicking this action item launches the Reverse Engineer Database. This is a multi-stage wizard that enables you to select a connection to a live server, and select the schema and objects you wish to reverse engineer into your new model. This is a convenient way to see how an existing database is structured.

To Create a WebERP database Schema by MySQL Workbench just with 10 clicks and also for further information about reverse engineering, see:
http://dev.mysql.com/doc/workbench/en/wb...-live.html

P.S: You can download this software from:
http://dev.mysql.com/downloads/tools/workbench/5.2.html

Regards
Farbod
Find all posts by this user
Quote this message in a reply
08-07-2013, 10:09 AM
Post: #7
RE: Create a WebERP database Schema
(05-13-2013 07:19 AM)Farbod Wrote:  Use MySQL Workbench.

MySQL Workbench is a powerful tool developed by MySQL with three primary areas of functionality:
SQL Development: Replaces MySQL query browser. Allows the user to connect to an existing database and edit and execute SQL queries.
Data Modeling: Complete visual database design and modeling.
Database Administration: Replaces MySQL administrator. Graphic interface to start/stop servers, create user accounts, edit configuration files, etc.

It is free and it is very useful.

"Create EER Model from Existing Database"
This action item enables you to create an EER Model from an existing live database. Clicking this action item launches the Reverse Engineer Database. This is a multi-stage wizard that enables you to select a connection to a live server, and select the schema and objects you wish to reverse engineer into your new model. This is a convenient way to see how an existing database is structured.

To Create a WebERP database Schema by MySQL Workbench just with 10 clicks and also for further information about reverse engineering, see:
http://dev.mysql.com/doc/workbench/en/wb...-live.html

P.S: You can download this software from:
http://dev.mysql.com/downloads/tools/workbench/5.2.html

Regards
Farbod

Hi Farbod,
I have just installed MySQL Workbench, and I see what you mean, it is very impressive.
It generated all the webERP tables with links, allowing one to pan over the huge map fairly easily.

Thanks for the heads up.

Cheers
Terry
Find all posts by this user
Quote this message in a reply
11-09-2013, 12:36 AM (This post was last modified: 11-09-2013 12:37 AM by jo lwebuga.)
Post: #8
RE: Create a WebERP database Schema
http://www.portertech.org/test/webERP/we...index.html
This is really good especially when need to know the ERD of webERP
Thank you so much
Find all posts by this user
Quote this message in a reply
11-09-2013, 08:31 AM
Post: #9
RE: Create a WebERP database Schema
Hi Jo,
your link was shortened and didn't resolve, so I've listed it below.

http://www.portertech.org/test/webERP/we...index.html

Cheers
Terry
Find all posts by this user
Quote this message in a reply
11-09-2013, 11:09 AM
Post: #10
RE: Create a WebERP database Schema
Hi, Terry,

It's very nice. Could you update it to 4.11?

Best regards!

Exson
(11-09-2013 08:31 AM)terryp Wrote:  Hi Jo,
your link was shortened and didn't resolve, so I've listed it below.

http://www.portertech.org/test/webERP/we...index.html

Cheers
Terry
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:


User(s) browsing this thread: 1 Guest(s)