Category Archives: Database

Comparision of NoSQL databases mongoDB and apache hadoop

NoSQL

Not only SQL (NoSQL) databases using different kind of technics for storing and retrieving data compared to classical relational databases like DB2 or Oracle. They emphasis more on scaling and storing different kind of data then on ACID or CAP support.

Type of NoSQL databases

NoSQL databases can be divided into five different    types:

  • Column
    • Tuple of three values: Unique name, value and timestamp
  • Document
    • semi structured data in documents
  • Key Value
    • dictionary of key value pairs
  • Graph
    • graph oriented system of nodes with properties and edges
  • Multi Model
    • mixture of data models with one backend

MongoDB belongs to the document based NoSQL databases.

HBase as part of apache Hadoop belongs to the column based NoSQL databases.

MongoDB

MongoDB is a open source document database mainly written in C++ as  native application.  The MongoDB Inc. is the company behind that product and offers commercial versions, suppport, etc.

License is A GPL 3.0

Features

  • Document oriented Database
  • aggregation
  • Sharding
  • Replication
  • Indexing
  • automatic fail-over

Apache Hadoop

The Apache Hadoop is a umbrella for different software projects to store and process distributed large data sets. It’s open source and maintained by the apache community.

License is Apache 2.0 license

HDFS

The Hadoop distributed file system (HDFS) is a distributed storage system written in java suitable for storing large files in  a scalable and fault tolerance cluster.

HBase

HBase is a distributed, non relational database modeled after Google BigTable implementation.

Facebook used it for their messages to store over 100 PB on data.

Features

  • Linear and modular scalability.
  • Strictly consistent reads and writes.
  • Automatic and configurable sharding of tables
  • Automatic failover support between RegionServers.
  • Convenient base classes for backing Hadoop MapReduce jobs with Apache HBase tables.
  • Easy to use Java API for client access.
  • Block cache and Bloom Filters for real-time queries.
  • Query predicate push down via server side Filters
  • Thrift gateway and a REST-ful Web service that supports XML, Protobuf, and binary data encoding options
  • Extensible jruby-based (JIRB) shell
  • Support for exporting metrics via the Hadoop metrics subsystem to files or Ganglia; or via JM

Hive

data warehouse extensions for HBase to use HiveQL as like like query language

Ambari

Web based tool to monitor Hadoop installations

Pig

Ability to create MapReduce programs with the PigLatin language to analyze large datasets

Chukwa

Monitoring solution for distributed systems

Zoopkeper

Distributed configuration system

API

For MongoDB exists

For Hadoop exists

  • HBase
    • CLI
    • Java
    • REST
  •  Hive
    • CLI
    • REST

Integrate Hadoop into mongodb

There exists an adapter to use hadoops map reduce functions for aggregations on data. Hadoop jobs extract the data from mongodb, aggregate them and write back to mongodb.

decision criteria to choose the right tool

  • MongoDB
    • best suited as operation database
    • not for data analysis or data processing
  •  Hadoop
    • has many data analysis functionalities
    • use for large amount of read only data

References

Using Vaadin UI with Spring Boot for Spring Data Backend based on MongoDB

With the new Beta 1 of the  Vaadin Addon Vaadin Spring Boot you can use Spring Boot as base framework for your Vaadin UI. The following example application use Spring Data as service layer on top of a mongoDB as NoSQL database. Blackboard is used as generic event sub system inside the Vaadin UI. Complete Source Code is available on github.

Pre Requisites

  • Installed Java 8.x SDK
  • Installed Maven 3.x
  • Running MongoDB 2.x

Source Code

Maven

used maven pom.xml



	4.0.0

	de.schaeftlein.dev.vaadin
	demo-spring-vaadin
	0.0.1-SNAPSHOT
	jar

	demo
	Demo project for Spring Boot with Vaadin

	
		org.springframework.boot
		spring-boot-starter-parent
		1.2.2.RELEASE
		 
	

	
		UTF-8
		demo.DemoApplication
		1.8
		7.4.0
	

	
		
			org.vaadin.addons
			blackboard
			2.2.0
		
		
			com.vaadin
			vaadin-spring-boot
			1.0.0.beta1
		
		
			com.vaadin
			vaadin-themes
			${vaadin.version}
		
		
			com.vaadin
			vaadin-client-compiled
			${vaadin.version}
		
		
			org.springframework.boot
			spring-boot-starter-web
		
		
			org.springframework.boot
			spring-boot-starter-data-mongodb
		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
	

	
		
			
				org.springframework.boot
				spring-boot-maven-plugin
			
		
	


Command Line Runner

Simple class for starting tomcat as embedded web container for our little application on port 8080

package demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

Simple class for initiate mongodb with some test data

package demo;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import demo.data.Customer;
import demo.data.CustomerRepository;

@SpringBootApplication
public class MongoDBApplication implements CommandLineRunner {

	@Autowired
	private CustomerRepository repository;

	public static void main(String[] args) {
		SpringApplication.run(MongoDBApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {

		repository.deleteAll();

		// save a couple of customers
		repository.save(new Customer("Alice", "Smith"));
		repository.save(new Customer("Bob", "Smith"));

		// fetch all customers
		System.out.println("Customers found with findAll():");
		System.out.println("-------------------------------");
		for (Customer customer : repository.findAll()) {
			System.out.println(customer);
		}
		System.out.println();

		// fetch an individual customer
		System.out.println("Customer found with findByFirstName('Alice'):");
		System.out.println("--------------------------------");
		System.out.println(repository.findByFirstName("Alice"));

		System.out.println("Customers found with findByLastName('Smith'):");
		System.out.println("--------------------------------");
		for (Customer customer : repository.findByLastName("Smith")) {
			System.out.println(customer);
		}

	}

}

MongoDB Spring Data Repository

Entity class

package demo.data;

import org.springframework.data.annotation.Id;

public class Customer {

	@Id
	private String id;

	private String firstName;
	private String lastName;

	public Customer() {
	}

	public Customer(String firstName, String lastName) {
		this.firstName = firstName;
		this.lastName = lastName;
	}

	@Override
	public String toString() {
		return String.format("Customer[id=%s, firstName='%s', lastName='%s']",
				id, firstName, lastName);
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getId() {
		return id;
	}

}

Repository class as service layer

package demo.data;

import java.util.List;

import org.springframework.data.mongodb.repository.MongoRepository;

public interface CustomerRepository extends MongoRepository<Customer, String> {

	public Customer findByFirstName(String firstName);

	public List<Customer> findByLastName(String lastName);

}

Vaadin UI

main application class of the vaadin application

package demo.ui;

import org.springframework.beans.factory.annotation.Autowired;

import com.vaadin.annotations.Theme;
import com.vaadin.navigator.Navigator;
import com.vaadin.server.VaadinRequest;
import com.vaadin.spring.annotation.SpringUI;
import com.vaadin.spring.navigator.SpringViewProvider;
import com.vaadin.ui.Button;
import com.vaadin.ui.CssLayout;
import com.vaadin.ui.Panel;
import com.vaadin.ui.UI;
import com.vaadin.ui.VerticalLayout;
import com.vaadin.ui.themes.ValoTheme;

import demo.ui.event.EventSystem;

@Theme("valo")
@SpringUI
public class MyVaadinUI extends UI {

	@Autowired
	private SpringViewProvider viewProvider;
	
	@Autowired
	EventSystem eventSystem;

	@Override
	protected void init(VaadinRequest request) {
		initLayout();
                registerEvents();
	}

       private void registerEvents() {
		eventSystem.registerEvent(ReloadEntriesEvent.ReloadEntriesListener.class, ReloadEntriesEvent.class);
	}

	private void initLayout() {
		final VerticalLayout root = new VerticalLayout();
		root.setSizeFull();
		root.setMargin(true);
		root.setSpacing(true);
		setContent(root);

		final CssLayout navigationBar = new CssLayout();
		navigationBar.addStyleName(ValoTheme.LAYOUT_COMPONENT_GROUP);
	
		navigationBar.addComponent(createNavigationButton("Default View",
                DefaultView.VIEW_NAME));		
        navigationBar.addComponent(createNavigationButton("MongoDB View",
                MongoDBUIView.VIEW_NAME));
		
		root.addComponent(navigationBar);

		final Panel viewContainer = new Panel();
		viewContainer.setSizeFull();
		root.addComponent(viewContainer);
		root.setExpandRatio(viewContainer, 1.0f);

		Navigator navigator = new Navigator(this, viewContainer);
		navigator.addProvider(viewProvider);
	}

	private Button createNavigationButton(String caption, final String viewName) {
        Button button = new Button(caption);
        button.addStyleName(ValoTheme.BUTTON_SMALL);
        button.addClickListener(event -> getUI().getNavigator().navigateTo(viewName));
        return button;
    }
}

default view as welcome page

package demo.ui;

import javax.annotation.PostConstruct;

import com.vaadin.navigator.View;
import com.vaadin.navigator.ViewChangeListener.ViewChangeEvent;
import com.vaadin.spring.annotation.SpringView;
import com.vaadin.ui.Label;
import com.vaadin.ui.VerticalLayout;

@SpringView(name = DefaultView.VIEW_NAME)
public class DefaultView extends VerticalLayout implements View {
	/*
	 * This view is registered automatically based on the @SpringView annotation. 
	 * As it has an empty string as its view name, it will be shown when navigating to the Homepage
	 */
    public static final String VIEW_NAME = "";

    @PostConstruct
    void init() {
        addComponent(new Label("Welcome View"));
    }

    @Override
    public void enter(ViewChangeEvent event) {
        // the view is constructed in the init() method()
    }
}

mongo db view with a table and a edit form

package demo.ui;

import java.util.List;

import javax.annotation.PostConstruct;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;

import com.vaadin.event.ItemClickEvent;
import com.vaadin.navigator.View;
import com.vaadin.navigator.ViewChangeListener.ViewChangeEvent;
import com.vaadin.spring.annotation.SpringView;
import com.vaadin.ui.AbstractLayout;
import com.vaadin.ui.Alignment;
import com.vaadin.ui.Button;
import com.vaadin.ui.HorizontalLayout;
import com.vaadin.ui.Table;
import com.vaadin.ui.VerticalLayout;

import demo.data.Customer;
import demo.data.CustomerRepository;
import demo.ui.event.EventSystem;
import demo.ui.event.ReloadEntriesEvent;

@SpringView(name = MongoDBUIView.VIEW_NAME)
public class MongoDBUIView extends VerticalLayout implements View,ReloadEntriesEvent.ReloadEntriesListener{
	public static final String VIEW_NAME = "mongodbui";
	private static final Log LOG = LogFactory.getLog(MongoDBUIView.class);
	
	private Table entityTable;
	private String selectedId;
	private Customer selectedCustomer;
	
	private Button deleteButton;
	private Button editButton;
	
    @Autowired
    private MongoDBContainer mongodbContainer;
    
    @Autowired
    private CustomerForm editForm;
    
    @Autowired
    private CustomerRepository service;
    
    @Autowired
    private EventSystem eventSystem;
	
	@PostConstruct
	void init() {
		registerEvents();
		initData();
		initLayout();
	}

	private void registerEvents() {
		eventSystem.addListener(this);
    }
	
	@SuppressWarnings("serial")
	private void initLayout(){
		setMargin(true);
		setSpacing(true);
		// vaadin table 
        entityTable = new Table();
        entityTable.setContainerDataSource(mongodbContainer);
        entityTable.setVisibleColumns(MongoDBContainer.PROPERTIES);
        entityTable.setColumnHeaders(MongoDBContainer.HEADERS);
        entityTable.setSelectable(true);
        entityTable.setWidth("100%");
        entityTable.setHeight("300px");

        // table select listener
        entityTable.addItemClickListener(new ItemClickEvent.ItemClickListener() {
            @Override
            public void itemClick(ItemClickEvent event) {
                selectedId = (String) event.getItemId();
                selectedCustomer =  mongodbContainer.getItem(selectedId).getBean();

               LOG.info("Selected item id {"+ selectedId+"}");
            }
        });
        // button bar
        final AbstractLayout buttonBar = initButtonBar();
        buttonBar.setWidth("100%");
        
        // edit Form
        editForm.setVisible(false);
        
        addComponent(entityTable);
        addComponent(buttonBar);
        addComponent(editForm);
	}
	
    @SuppressWarnings("serial")
	private AbstractLayout initButtonBar() {
        final HorizontalLayout buttonBar = new HorizontalLayout();

        buttonBar.setSpacing(true);

        final Button addButton = new Button("Add entry");
        editButton = new Button("Edit Entry");
        deleteButton = new Button("Delete entry");

        buttonBar.addComponent(addButton);
        buttonBar.addComponent(editButton);
        buttonBar.addComponent(deleteButton);

        buttonBar.setComponentAlignment(addButton, Alignment.MIDDLE_LEFT);
        buttonBar.setComponentAlignment(editButton, Alignment.MIDDLE_CENTER);
        buttonBar.setComponentAlignment(deleteButton, Alignment.MIDDLE_RIGHT);

        addButton.addClickListener(new Button.ClickListener() {
            @Override
            public void buttonClick(Button.ClickEvent event) {
                editForm.setVisible(true);
            }
        });
        editButton.addClickListener(new Button.ClickListener() {
            @Override
            public void buttonClick(Button.ClickEvent event) {
                editSelectedEntry();
            }
        });
        deleteButton.addClickListener(new Button.ClickListener() {
            @Override
            public void buttonClick(Button.ClickEvent event) {
                removeSelectedEntry();
            }
        });

        return buttonBar;
    }
    
    private void editSelectedEntry() {
        if (selectedId != null) {
        	LOG.info("Edit Customer with id "+selectedId);
            editForm.setData(selectedCustomer);
            editForm.setVisible(true);
        }
    }
    
    private void removeSelectedEntry() {
        if (selectedId != null) {
        	LOG.info("Delete Customer with id "+selectedId);
            service.delete(selectedId);
            eventSystem.fire(new ReloadEntriesEvent());
        }
    }
	
	private void initData() {
		// load data
        List<:Customer> all = service.findAll();
        LOG.info(all);
        // clear table
        mongodbContainer.removeAllItems();
        // set table data
        mongodbContainer.addAll(all);
    }
	
	@Override
	public void enter(ViewChangeEvent event) {
		// the view is constructed in the init() method()
	}

	@Override
	public void reloadEntries(ReloadEntriesEvent event) {
        LOG.info("Received reload event. Refreshing entry table!");
        initData();
        entityTable.markAsDirty();
	}

}

table container definition

package demo.ui;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.vaadin.data.util.BeanContainer;

import demo.data.Customer;


@Component
@Scope("prototype")
public class MongoDBContainer extends BeanContainer<String, Customer> {

	private static final long serialVersionUID = 3090067422968423191L;

	public static final String BEAN_ID = "id";

    public static final Object[] PROPERTIES = {BEAN_ID, "firstName", "lastName"};

    public static final String[] HEADERS = {"ID", "First Name", "Last Name"};

    public MongoDBContainer() {
        super(Customer.class);
        setBeanIdProperty(BEAN_ID);
    }
}

edit form

package demo.ui;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.vaadin.ui.Button;
import com.vaadin.ui.FormLayout;
import com.vaadin.ui.HorizontalLayout;
import com.vaadin.ui.TextField;

import demo.data.Customer;
import demo.data.CustomerRepository;
import demo.ui.event.EventSystem;
import demo.ui.event.ReloadEntriesEvent;


@Component
@Scope("prototype")
public class CustomerForm extends FormLayout {

    private Logger log = LoggerFactory.getLogger(CustomerForm.class);

    @Autowired
    private CustomerRepository customerService;

    @Autowired
    private EventSystem eventSystem;

    private String id = null;
    private TextField firstName = new TextField("First Name:");
    private TextField lastName = new TextField("Last Name:");

    public CustomerForm() {
        initForm();
    }
    
    public void setData(Customer customer){
    	id = customer.getId();
    	firstName.setValue(customer.getFirstName());
    	lastName.setValue(customer.getLastName());
    }

    private void initForm() {
        addComponent(firstName);
        addComponent(lastName);

        final Button commit = new Button("Commit");
        final Button cancel = new Button("Cancel");

        cancel.addClickListener(new Button.ClickListener() {
            @Override
            public void buttonClick(Button.ClickEvent event) {
                clearAndHide();
            }
        });
        commit.addClickListener(new Button.ClickListener() {
            @Override
            public void buttonClick(Button.ClickEvent event) {
                commitForm();
                fireCommitEvent();
                clearAndHide();
            }
        });

        final HorizontalLayout buttonBar = new HorizontalLayout();

        buttonBar.addComponent(commit);
        buttonBar.addComponent(cancel);

        addComponent(buttonBar);
    }

    private void commitForm() {
        if(id!=null){
        	log.info("Update user with id {}, name {} and address {}", id, firstName.getValue(), lastName.getValue());
        	Customer customer = customerService.findOne(id);
        	customer.setFirstName(firstName.getValue());
        	customer.setLastName(lastName.getValue());
        	customerService.save(customer);
        }
        else {        	
        	log.info("Creating user with name {} and address {}", firstName.getValue(), lastName.getValue());
        	customerService.save(new Customer(firstName.getValue(), lastName.getValue()));
        }
    }

    private void clearAndHide() {
        firstName.setValue("");
        lastName.setValue("");
        id = null;
        setVisible(false);
    }

    private void fireCommitEvent() {
        log.info("Fire commit event!");
        eventSystem.fire(new ReloadEntriesEvent());
    }
} 

Vaadin Event System

Event sub system System

package demo.ui.event;

import java.io.Serializable;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;

import com.github.wolfie.blackboard.Blackboard;
import com.github.wolfie.blackboard.Event;
import com.github.wolfie.blackboard.Listener;

@Component
@Scope("session")
public class EventSystem implements Serializable {
	private static final long serialVersionUID = 7829012291289167478L;
	private Blackboard blackboard = new Blackboard();

    public EventSystem() {
        init();
    }

    private void init() {
        blackboard.enableLogging();
    }

    public void registerEvent(Class<? extends Listener> listenerClass, Class<? extends Event> eventClass) {
        blackboard.register(listenerClass, eventClass);
    }

    public <T extends Listener> void addListener(T listener) {
        blackboard.addListener(listener);
    }

    public <T extends Event> void fire(T event) {
        blackboard.fire(event);
    }
}

Custom Reload Event with Listener

package demo.ui.event;

import com.github.wolfie.blackboard.Event;
import com.github.wolfie.blackboard.Listener;
import com.github.wolfie.blackboard.annotation.ListenerMethod;

public class ReloadEntriesEvent implements Event {

	public interface ReloadEntriesListener extends Listener {
		@ListenerMethod
		public void reloadEntries(ReloadEntriesEvent event);
	}

	public ReloadEntriesEvent() {
	}
}

Testing

JUnit Test for Spring Data Repository

package demo;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import demo.data.Customer;
import demo.data.CustomerRepository;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = MongoDBApplication.class)
public class MongoDBApplicationTests {
	
	@Autowired
	CustomerRepository repo;
	
	private final static Log LOG = LogFactory.getLog(MongoDBApplicationTests.class);

	@Test
	public void foundAlice(){
		Customer alice = repo.findByFirstName("Alice");
		Assert.assertNotNull(alice);
		LOG.info(alice);
	}
}

Running

Using Command Line Runner

Inside Eclipse run the “demo.DemoApplication” class and open the url http://localhost:8080/ to see the welcome page.

Welcome page

Switch to the mongodb view

mongodb view

Select a row and click on “Edit entry” to change data or “delete entry” to delete selected customer. With “Add entry” you can create a new customer in the mongoDB.

Common pitfalls installing Oracle XE 11 under Oracle Linux 6

First thing to check is the hostname inside /etc/hosts to ensure the install process can find the net listener. During the installation of Oracle Linux you set the hostname for example to oracle-vm.localdomain. Open with nano /etc/hosts and keep sure you have a line like this.

127.0.0.1 oracle-vm.localdomain localhost localhost.localdomain localhost4 localhost4.localdomain4

Next thing is to keep sure you have shared memory configured. Enter df -k and see if a line starts with shmfs. If not then enter

mount -t tmpfs shmfs -o size=2048m /dev/shm

and change your fstab to have shared memory available after next boot:

shmfs /dev/shm tmpfs size=2048m 0 0

Oracle XE for Linux is only available for X64 systems. Enter uname -a and see if it ends with

x86_64 x86_64 x86_64 GNU/Linux

If not reinstall your system with the x64 version of your linux distribution.

Keep sure your system is up to date with yum update.

After Download of Oracle Database Express Edition 11g unzip the File to a folder. Install the rpm with

rpm -ivH Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm

and enter

/etc/init.d/oracle-xe configure

Check if the install process outputs ends with

Starting Oracle Net Listener…Done
Configuring database…Done
Starting Oracle Database 11g Express Edition instance…Done
Installation completed successfully.

or the log files under

/u01/app/oracle/product/11.2.0/xe/config/log.

On success you have a icon called “Get started with Oracle… .desktop”. Click on the icon and mark the link as trust worthy. It will open a browser

http://localhost:9000/apex/f?p=4950

If asked for a user name and password use SYSTEM and your chosen password as credentials.

Migrating HSQLDB to MySQL

HSQLDB migration to MySQL is not out of the box supported by mysql. The MySQL Workbench can only handle with dumps made by other mysql server. HSQLDB Transfer Tool is no longer bundled with the standard hsqldb jar file in the 1.8.x or later releases. After downloading and building with

ant hsqldbutil

you get in lib an additional hsqldbutil jar. With the hsqldb.jar, the hsqldbutil.jar and and mysql connector jar in my classpath i tried the Database Manager (From Tools menu choose transfer) from hsql with no luck. The MySQL Migration Toolkit has reached the EOL phase but works perfect for me. After downloading add the hsqldb jar to the lib folder C:\Program Files (x86)\MySQL\MySQL Tools for 5.0\java\lib. I update as well the mysql connector to the latest one.

  1. Start your hsqldb server from command line
    1. java -cp hsqldb-1.8.0.10.jar org.hsqldb.Server -database <path to your hsqldb files>\<database name>
  2. Start MySQL Migration Toolkit
  3. Choose direct migration
  4. Choose as Source a generic jdbc
  5. Enter “org.hsqldb.jdbcDriver” as classname
  6. Enter “jdbc:hsqldb:hsql://localhost” as connection string
  7. Enter “sa” as username and leave password empty
  8. click next
  9. Configure your mysql connection
  10. click next
  11. choose public as schema
  12. click next several time till screen “object creation options”
  13. choose “create script file..” instead of “create objects online”
  14. click next several time till screen “data mapping options”
  15. choose “create script file..” instead of “transfer data online”
  16. click next several time till screen “Summary”
  17. click finish

With the sql you can use tools like phpmyadmin or the mysql workbench to import your data into the mysql server.

Generating MySQL Database Documentation for Confluence with MySQL Workbench Plugin

The MySQL Workbench can nicely  generate a Entity Relationship model from your existing MySQL Database. After Installing click in the column labelled “Data Modelling” on the link below to “Create EER model from existing database”. Follow the wizard to have a generated diagram including your existing tables and their related information. Don’t forget to save the new ER model as *.mwb file.

For Documentation in a  project is Confluence as Wiki very good compared to other ones like MediaWiki.

I have written a small plugin to MySQL Workbench as Lua Script to generate Database Model Documentation as Confluence Markup. If you are interested look here for Documentation about the available classes to use in your own scripts.

Part of the generated output from the sample database world from MySQL shown as result in Confluence:

Howto:

  1. Download this Script here or from github
  2. Start your MySQL Workbench
  3. Choose from the “scripting” menu the entry “Install Plugin/Module…”
  4. Change Drop down in the Dialog to File type “lua Files..”
  5. Choose downloaded File and click open
  6. Restart Workbench
  7. Open previously generated ER model file *.mwb
  8. Choose from the “Plugins” menu the entry “Catalog” and their the new entry “Confluence Markup Exporter…”
  9. Markup is now in the clipboard
  10. Open in Confluence an existing page or create a new onw
  11. Click on “Edit” button and go to tab “Wiki Markup”
  12. Paste the generated documentation and click on “save” button