More Sample Databases and Free Datasets

October 12, 2011

As I prepared my previous post on SQL Server sample databases, I also came across sample databases for other SQL databases.

PgFoundry provides a couple of database samples for PostgreSql. Real-world applications such as OpenNMS also use PostgreSql.

For MySql, I found the AdventureWorks database for MySql on SourceForge. This question on SO also covers sample databases for MySql.

Of course, a lot of real-world applications use MySql as their datastore, such as Bugzilla, MediaWiki, WordPress, or Joomla.

One question on SO asked for freely available datasets, and one answer points to an amazing list of datasets available on the web.

Makes me wonder whether there exists a comprehensive list of freely available data, all tagged with hierarchical categories?


Integrated Database Versioning and Documentation with dbscript 1.02

July 11, 2010

dbscript 1.02 adds support for edmx-based projects. If have previously written about generating documentation out of edmx files, and these documentation generators are now part of dbscript.

This is the updated table of Documentation Generators in dbscript 1.02:

Generated Documentation

MS SQL Server Oracle PostgreSql edmx
dbscript HTML view view view
dbscript Single HTML view view view view
view
HTML (single file) view view view view
view
MediaWiki view view view view
view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view view view view
view

The XSLT files on which the Documentation Generators depend for their functionality are now computed by a separate tool I wrote for this purpose, making the generated documentation more consistent throughout the different formats and databases.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Other features

  • Data diagrams can now be restricted to a certain Object Selection resulting in a diagram showing only a part of the full model.
  • Update notifications are implemented via JSONP
  • Some fixes

dbscript is available for download here.


Integrated Database Versioning and Documentation with dbscript 1.01

February 16, 2010

I have written about the planned features for dbscript 1.01 in recent posts, and the new version was released yesterday.

This is the updated table of Documentation Generators in dbscript 1.01:

Generated Documentation

MS SQL Server Oracle PostgreSql
dbscript HTML view
dbscript Single HTML view
HTML (single file) view view view
MediaWiki view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view

The Documentation Generators in the first two lines are part of the dbscript web application (see demo links above).

This means that a developer can import a database schema (or upload a SQL DDL script) which will be stored as a version of a schema or database, define a Documentation Generator, and immediately view all information on this schema inside dbscript without requiring a third party application.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Documentation Contents

All XSLTs have been cleaned up to include information about table constraints and indexes, view indexes and triggers, and database triggers.

The information about table indexes is now displayed in separate tables, rather than inside the columns table.

Documentation also includes descriptions of database objects and columns.

Generic Projects

Users can create generic (i.e. database-independent) projects, if they just require the Versioning, UpdateNotifications and Installations features.

Other features

  • Administrators can now delete a whole project with all its information in one go.
  • Acknowledgements page
  • Creating a Documentation Generator adds default Generator Parts for easier setup

dbscript is available for download here.


Documentation Generators in dbscript 1.01

February 11, 2010

Cleaning up the documentation generators mentioned in my previous post.

dbscript 1.01 will include generators for the formats listed in the tables below:

Generated Documentation

MS SQL Server Oracle PostgreSql
dbscript HTML
dbscript Single HTML
HTML (single file) view view view
MediaWiki view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view

This table lists the XSLT style sheets shipped with dbscript.

Users are free to create their own style sheets or adapt existing style sheets for their use.

Generated Data Diagrams

MS SQL Server Oracle PostgreSql
PNG view view view
Dia view view view

Documentation samples are based on these freely available demo projects:

MS SQL Server AdventureWorks (2005)
Oracle Oracle Demo Schema
PostgreSql OpenNMS

Version 0.98 of dbscript Released

September 9, 2009

The latest version 0.98 of dbscript supports PostgreSQL databases in its documentation generation capabilities.

After importing the database dictionary (via direct connection using ADO.Net and Npgsql) can document a PostgreSQL database in all currently supported documentation format:

MediaWiki

Data Diagram (PNG)

HTML

ScrewTurn wiki

Integration support for PostgreSQL had some consequences: More and more functionality is handled separately for each database engine.

Database import was obviously the first one, since the data access classes (SqlConnection, SqlCommand) in .Net are different for every database library. Same goes for the database dictionary, which is best retrieve from the native system catalogs.

For import and upload, data access classes have been introduced to distinguish the different object types and their properties of each database engine. I mention work on the data access classes in a series of articles already.

In version 0.98, XML generation and object script generation are implemented separately. This results in XSL style sheets being now related to certain a database engine.

For Oracle, XML and object script generation have been updated, and the XSL style sheets have been adjusted to Oracle-specific objects and properties. The results were documented earlier.

The latest version of dbscript is available for download here.

Please leave comments and feedback.


Generating Database Documentation for ScrewTurn wikis

August 29, 2009

Updated XSL style sheets for dbscript documentation generators creating ScrewTurn content.

See here for sample output:

MS SQL AdventureWorks

Oracle Demo Schema

PostgreSQL OpenNMS

ScrewTurn also allows external Page Providers (instead of stored static pages)

MS SQL AdventureWorks (by Page Provider)


Creating HTML documentation of PostgreSQL databases

August 10, 2009

dbscript ships with a couple of XSLT style sheets which transform an XML representation of a database schema into MediaWiki, HTML, or, if you create them on your own, any format you wish.

After writing the previous post on PostgreSQL support in dbscript, I fixed the XSLT for HTML generation, created a Documentation Generator in dbscript, and this is the resulting HTML documentation of the OpenNMS data model.


Creating MediaWiki Documentation of PostgreSQL Databases

August 7, 2009

After hacking away the last couple of days, dbscript finally is able to perform another trick: Importing database schemas from PostgreSQL databases using the Npgsql data provider.

Internally, the database abstraction layer is now completely based on interfaces as sketched in a previous post. Rather than asking a Database Engine object, “can you do this,  and can you do this?” giving boolean answers, we ask the object: do you implement a functionality and all the required methods, which is a simple “is” operation.

The other aspect is that the newly created data access layer simplifies handling the differences between the database engines (object types, table columns, etc).

These differences also resulted in a reference from XSLT style sheets to a Database Engine to allow more specific content creation in Document Generators.

So here are the new documentation samples, based on the data model of OpenNMS:

The data diagram of the OpenNMS database schema:

PostgreSQL is the 3rd database engine supported by dbscript, after MS SQL Server and Oracle.

Please note that this version still needs some polish. The most current stable version is available for download here.


Installing PostgreSQL 8.3 on Windows

June 19, 2009

I wanted to try out OpenNMS for monitoring our server infrastructure, and need to install PostgreSQL as described in the Installation manual.

Everything worked as described, except for one item during the installation of PostgreSQL: Trying to execute the command

initdb -E UTF-8 -U postgres ..\data

resulted in the error message

initdb: could not access directory "../data": No such file or directory

It turns out that initdb seems to have a problem with spaces in directory names (“Program Files”). Replacing the path with short filenames (“Program~1″) solves the problem Defining a path without any spaces solves the problem:

initdb -E UTF-8 -U postgres \databases\postgresql\8.3\data

Success!


Follow

Get every new post delivered to your Inbox.

Join 65 other followers