Sample Databases, Porting with code

Contents

Sample Database Introduction. 1

SQL Server. 1

Year 2000 Sample Databases. 1

Sample Database Introduction

All database products will have some sort of sample database, it may be distributed with the software or available as a separate download, either from the software creators, or open source provided by the community.

What you want out of a demo is down to you. Some are simple, some are very complex. Some are tiny some are massive. Some use database specific features some are portable as they are.

Just because a demo isn’t available for the database product you are using don’t discount it, the Spludlow Framework can often be used to copy the database between systems in a few lines of code.

SQL Server

Year 2000 Sample Databases

The SQL Server sample databases; “Northwind and pubs Sample Databases for SQL Server 2000” can be downloaded from here: https://www.microsoft.com/en-us/download/details.aspx?id=23654. These databases are simple, portable and have low row counts.

Run the installer and you end up with the directory “C:\SQL Server 2000 Sample Databases”. You can install the 2 sample databases like so:

To install the database "Northwind"

sqlcmd.exe -S DBHOST -i "C:\SQL Server 2000 Sample Databases\instnwnd.sql"

To install the database "pubs"

sqlcmd.exe -S DBHOST -i "C:\SQL Server 2000 Sample Databases\instpubs.sql"

NOTE: If you are not using integrated security you will also need to supply the credentials using -U and -P.

Northwind uses a table with a space in the name, “Order Details”. Not good practice but technically legal. It also stores bitmap images in the Categories and Employees tables (you need to skip the first 78 bytes to get the usable bitmap, OLE header).

Pubs uses user defined datatypes for example “empid” is a char(9).

The “Northwind” Employees: Nancy, Andrew, Janet, Margaret, Steven, Michael, Robert, Laura, and Anne.

Adventure Works

This is a heavy weight demo database that could be used in the real world for a large enterprise. The demo business in question sells bicycles and accessories, the databases models human resources, production, purchase, and sales. The modelling is pretty comprehensive and gets tasty in places including using a central set of tables (“Person” schema) to model people and address that can be employees, customers, or suppliers, to get around duplication in the model.

Here is a list of notable features:

· Schemas – In SQL Server Schemas are organizational containers for tables, you can reference the tables through a namespace and assign permissions to the schema containing the tables rather than individually.

· XML – There are several columns that use the XML data type and also XML Indexes are used (Primary and Secondary).

· Full Text Indexing – Uses SQL Server Full Text Search on some columns including columns holding Word documents as binary.

· Storing Binary – Product Images and Word documents are stored in columns

· Comprehensive Modelling – You may learn something from the model

· Exotic Data Types – The datatypes “hierarchyid” and “geography” are used

· User Defined Data Types – Account and Order Numbers for example also defines “Flag” as a non-null Boolean.

· Views – Plenty of examples

· Stored Procedures & Functions – Plenty of examples

· Triggers – Included is a trigger to audit all database events to the “DatabaseLog” table

· Moderate Row Count – Not a trivial or nightmare amount of data, some tables around 120K rows, total row count around 750K. On file-system around 200Mb.

· Sensible Names – No spaces or daft characters for table and column names

· Low Portability – Some of the functionality used is not available in all database products, porting the database will mean making some sacrifices.

Go here for the right download for your version of SQL Server: https://sqlserversamples.codeplex.com/

I used “AdventureWorks2012-Full Database Backup.zip” against a 2016 database and it worked fine. You can use the Spludlow DAL to restore the database to whatever name you like. The following code will delete any existing database and replace from the backup file:

Spludlow.Data.IDAL database = Spludlow.Data.DAL.Create("HOST-DB");

string filename = @"\\host-db\BackupDump$\AdventureWorks2012-Full Database Backup.bak";

string databaseName = "AdventureWorks";

Spludlow.Data.Database.ReplaceDatabase(database, databaseName, filename);

target.ExecuteNonQuery(@"sp_changedbowner DOMAINORHOST\User'");

You can run this code again to replace the database if you mess it up and just want to start again. The filename should be available from the server, with read permission. The last command will take ownership, when restoring from a backup not made on the same host the database owner will normally get screwed up, try making a database diagram, that will error if you have a duff owner.

I tried using the DAL to transfer it over to MySQL I experienced the following problems:

· Schema Names – The DAL stores table names as “SchemaName.TableName” (just table name if default schema), MySQL doesn’t do schemas so just rename them all from “.” To “_”, the FixTableNamesWithSchema() method will do this.

· Long Key and Index Names – Some of the indexes have real long names that MySQL can’t handle so rename the index names, the FixKeyNames() method will do this.

· Composite Primary Keys with Auto Increment – Some tables have primary keys made up of the parent’s id number and the child’s auto increment id number, MySQL doesn’t like this. As a pure bodge I disabled the auto increments but this is not a fix, it will break the application. DO THIS !!! As a fix the parent ids should be removed from the primary key, remaining as a foreign key. The tables are: Purchasing_PurchaseOrderDetail, Sales_SalesOrderDetail, and Person_EmailAddress. Why did they model it like this? I expect you get some performance gain when querying a parent’s children as the non-clustered primary key starts with the parents id number. If you want portability don’t do this.

· Index too big – There is an index “IX_Production_ProductReview_1” that’s columns lengths are over the limit for MySQL. As a bodge I just deleted this index.

· XML Columns and indexes – MySQL doesn’t support XML to the level of some other databases. The DAL will convert XML column types to text and silently not attempt to create the XML indexes if the target database doesn’t support XML.

· Full Text Indexes – Not yet supported in the MySQL DAL implementation

Bingo Adventure Works in MySQL Workbench.

MySQL

The MySQL sample databases can be downloaded from http://dev.mysql.com/doc/index-other.html

Employees

The main one with a pretty heavy row count. dept_emp 331K, employees 300K, salaries 2.8M, titles, 400K.

cd C:\MySQLSample\employees_db

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u fred --password=fred -t < employees.sql

I got the error “Unknown system variable 'storage_engine'” I just commented out anything to do with storage_engine at the top of the file and it worked.

Sakila

Fairly light row count, tables “rental” and “payments” have around 16K rows each. It uses views, routines, and triggers, it uses a space in a column name “zip code”, and uses the MySQL data type “geometry”.

Due to a bug in MySQL http://bugs.mysql.com/bug.php?id=75301 you can’t use this database as is with the DAL. You can work around this by editing “sakila-schema.sql” and removing the line “FULLTEXT KEY idx_title_description (title,description)” from the “CREATE TABLE film_text” section, make sure you remove the comma from the previous line also, you should end up with:

CREATE TABLE film_text (

film_id SMALLINT NOT NULL,

title VARCHAR(255) NOT NULL,

description TEXT,

PRIMARY KEY (film_id)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

cd C:\MySQLSample\sakila-db

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u fred --password=fred -t < sakila-schema.sql

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe" -u fred --password=fred -t < sakila-data.sql