I am not sure whether you can confirm a data directory for PostgreSQL during Ubuntu installation, but anyway, I ended up with the data directory located in the default location.
For some database experiments that I expect to be quite harddisk-consuming, I want to PostgreSQL to store this database on a different volume.
Following the comments on this question on SO and this answer on DBA, I first created a directory for PostgreSQL:
Continue readingAs 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?
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:
| 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.
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:
| 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.
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.
Users can create generic (i.e. database-independent) projects, if they just require the Versioning, UpdateNotifications and Installations features.
Cleaning up the documentation generators mentioned in my previous post.
dbscript 1.01 will include generators for the formats listed in the tables below:
| 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.
| 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 |
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:
Data Diagram (PNG)
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.
Updated XSL style sheets for dbscript documentation generators creating ScrewTurn content.
See here for sample output:
ScrewTurn also allows external Page Providers (instead of stored static pages)
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.
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.
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!
devioblog 