Chinook Database

New Post: DROP DATABASE if database exists (SQLServer)

sadkamikaze — Sat, 21 Nov 2009 13:46:24 GMT

If I start CreateDB.sql and database already exists and used (connections to database is opened) database not droped

Solution could be add additional logic

CreateDb.sql LINE:10

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Chinook')

BEGIN

ALTER DATABASE [Chinook]

SET OFFLINE

WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [Chinook]

SET ONLINE;

DROP DATABASE [Chinook];

END

CREATE DATABASE [Chinook];

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Chinook')

BEGIN

--Take database offline ignoring any connection made

ALTER DATABASE [Chinook]

SET OFFLINE

WITH ROLLBACK IMMEDIATE;

--Bring online before delete

ALTER DATABASE [Chinook]

SET ONLINE;

DROP DATABASE [Chinook];

END

CREATE DATABASE [Chinook];

Patch Uploaded: #3717

NelsonCabral — Sun, 30 Aug 2009 11:50:07 GMT

NelsonCabral has uploaded a patch.

Description:
SQLite version 0.1
Commented out two parts :
- initial db drop and create, it looks like I cannot 'create a db' in a sqlite file, as a file = a db
- foreign keys constraints. SQLite doesn't support directly foreign keys, see http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html for a way to implement them with trigger
Easy test with firefox's sqlite manager extension

New Post: SQLite?

lerocha — Sat, 29 Aug 2009 19:19:56 GMT

Yes, sure! If you have already a SQLite version and want to share it, I can include it on the next version. Thanks!

Luis

New Post: SQLite?

NelsonCabral — Sat, 29 Aug 2009 17:14:26 GMT

Are you interested in the SQLite version of your Chinook db?

Created Issue: Indexes based on primary keys should be UNIQUE

ErikEJ — Mon, 10 Aug 2009 14:14:48 GMT

If not, ORM tools like CodeSmith do not work as expected (as a non-unique index indicates that more rows will be returned per key). I had planned to feature Chinook in a blog post, but this must be fixed for the following tables/indexes, before I can do that:

CREATE INDEX IPK_Genre ON Genre(GenreId);
CREATE INDEX IPK_MediaType ON MediaType(MediaTypeId);
CREATE INDEX IPK_Artist ON Artist(ArtistId);
CREATE INDEX IPK_ProductItem ON Album(AlbumId);
CREATE INDEX IPK_Track ON Track(TrackId);
CREATE INDEX IPK_Employee ON Employee(EmployeeId);
CREATE INDEX IPK_Customer ON Customer(CustomerId);
CREATE INDEX IPK_Invoice ON Invoice(InvoiceId);
CREATE INDEX IPK_InvoiceLine ON InvoiceLine(InvoiceLineId);
CREATE INDEX IPK_Playlist ON Playlist(PlaylistId);
CREATE INDEX IPK_PlaylistTrack ON PlaylistTrack(PlaylistId);

should be

CREATE UNIQUE INDEX IPK_Genre ON Genre(GenreId);
CREATE UNIQUE INDEX IPK_MediaType ON MediaType(MediaTypeId);
CREATE UNIQUE INDEX IPK_Artist ON Artist(ArtistId);
CREATE UNIQUE INDEX IPK_ProductItem ON Album(AlbumId);
CREATE UNIQUE INDEX IPK_Track ON Track(TrackId);
CREATE UNIQUE INDEX IPK_Employee ON Employee(EmployeeId);
CREATE UNIQUE INDEX IPK_Customer ON Customer(CustomerId);
CREATE UNIQUE INDEX IPK_Invoice ON Invoice(InvoiceId);
CREATE UNIQUE INDEX IPK_InvoiceLine ON InvoiceLine(InvoiceLineId);
CREATE UNIQUE INDEX IPK_Playlist ON Playlist(PlaylistId);
CREATE UNIQUE INDEX IPK_PlaylistTrack ON PlaylistTrack(PlaylistId);

Likewise, the enclosed chinook.sdf file needs to be recreated.

New Post: Domain model ?

lerocha — Tue, 07 Jul 2009 03:44:47 GMT

No, there is no domain model for it. There is just the physical database schema:

http://chinookdatabase.codeplex.com/Wiki/View.aspx?title=Chinook_Schema

New Post: Domain model ?

fabiomaulo — Thu, 02 Jul 2009 16:28:07 GMT

Do you know is there is, in some place, a domain model of Chinook ?

Source code checked in, #50685

lerocha — Sat, 16 May 2009 23:53:47 GMT

Renaming T4 template include file to use .ttinclude extension and fixing issue to generate files with UTF16 encoding.

Source code checked in, #49579

lerocha — Tue, 28 Apr 2009 05:04:29 GMT

Auto-generating the main T4 template file (chinook.t4) from the ChinookMetadata assembly and refactoring existing T4 templates to include Chinook.t4.

Created Feature: Feature Request: Schema change to allow implementation of inheritance mapping strategies

lerocha — Sun, 26 Apr 2009 22:30:24 GMT

This request is from Rub member of the uNhAddIns project (Unofficial addins for NHibernate): http://code.google.com/p/unhaddins/

Change the schema to allow developers to implement the three basic strategies for inheritance mapping:

1. Table per Class Hierarchy
2. Table per Subclass
3. Table per Concrete Class

For more info, see:
http://www.nhforge.org/doc/nh/en/index.html#inheritance

Created Feature: Support for NHibernate using HiLo generator

lerocha — Mon, 20 Apr 2009 05:15:15 GMT

This request is from Rub member of the uNhAddIns project (Unofficial addins for NHibernate): http://code.google.com/p/unhaddins/

The current scripts use Identity to auto-generate primary keys. Although NHibernate supports Identity, it is not a good solution for it, as described below:
http://nhforge.org/blogs/nhibernate/archive/2008/12/21/identity-the-never-ending-story.aspx

We need to add support for nhibernate HiLo generation. For example, the Genre table would be:

CREATE TABLE Genre
(
GenreId INTEGER NOT NULL, // IDENTITY - no identity here
Name NVARCHAR(120),
PRIMARY KEY(GenreId)
);

Created Feature: Feature Request

lerocha — Mon, 20 Apr 2009 04:55:25 GMT

Feature requests by Evoluteur:
- script to drop all tables (because it needs to drop ref integrity first).
- triggers to update order total.
- a few records in Chinese or Japanese

New Post: Live web demo

evoluteur — Fri, 03 Apr 2009 21:58:11 GMT

Great database for demos. I've just used it for a demo of my CRUD framework. Thanks.

http://evolutility.org/Chinook/Chinook_by_Evolutility.aspx

Updated Release: Chinook Database 1.1 (Jan 04, 2009)

lerocha — Sun, 11 Jan 2009 19:25:55 GMT

This is a sample database available for SQL Server, Oracle and MySQL. It can be created by running a single SQL script. You can see the Chinook database schema here.

The zip file above contains SQL scripts and batch files for each database supported. It also contains the data in XML format, see SampleData\ChinookData.xml, and a SQL Server Compact database SampleData\Chinook.sdf.

What is new?

Support for SQL Server Compact.
Additional customers from multiple countries.
Added a many-to-many relationship (a Playlist contains many Tracks, a Track belongs to many Playlists).
Added a one-to-many relationship between Employee and Customer (support representative).
Added Total field to invoice table.

Supported Database Servers

MySQL
Oracle
SQL Server
SQL Server Compact

How do I Create the Chinook Database?

Download and extract the zip file above. Under the Script folder, there are two files per database server: a SQL script, CreateDB.sql, and a batch file, CreateDB.bat. You can either run the SQL script by using your preferred database tool or run the provided batch file.

The batch files use the database server command tool (mysql.exe for MySQL, sqlcmd.exe for SQL Server, and sqlplus.exe for Oracle). If you are using Windows Vista, it is recommended to open a Command Prompt window as Administrator in order to run these batch files.

For SQL Server Compact, we included the compact database SampleData\Chinook.sdf. If you prefer to create your own compact database, then you can use its CreateTables.sqlce script.

Notes for MySQL Version

The provided script was tested using MySQL Server 5.1.
The CreateDB.bat file uses the localhost server, and the root user with the password p4ssw0rd. Change this file to match your settings before running it.

Notes for Oracle Version

The provided script was tested using Oracle Database 10g Express Edition (XE).
The CreateDB.sql creates a new user chinook, with the password p4ssw0rd. Change this script if you want a different user/password.

Notes for SQL Server Version

The provided script was tested using SQL Server Express 2008.

Notes for SQL Server Compact Version

The provided script was tested using SQL Server Compact 3.5 SP1.
We included the compact database SampleData\Chinook.sdf. If you prefer to create your own compact database, then you can use the CreateTables.sqlce script with SQL Server Management Studio Express tool.

Released: Chinook Database 1.1 (Jan 04, 2009)

Sun, 11 Jan 2009 19:25:55 GMT

What is new?

Support for SQL Server Compact.
Additional customers from multiple countries.
Added a many-to-many relationship (a Playlist contains many Tracks, a Track belongs to many Playlists).
Added a one-to-many relationship between Employee and Customer (support representative).
Added Total field to invoice table.

Supported Database Servers

MySQL
Oracle
SQL Server
SQL Server Compact

How do I Create the Chinook Database?

Notes for MySQL Version

The provided script was tested using MySQL Server 5.1.
The CreateDB.bat file uses the localhost server, and the root user with the password p4ssw0rd. Change this file to match your settings before running it.

Notes for Oracle Version

The provided script was tested using Oracle Database 10g Express Edition (XE).
The CreateDB.sql creates a new user chinook, with the password p4ssw0rd. Change this script if you want a different user/password.

Notes for SQL Server Version

The provided script was tested using SQL Server Express 2008.

Notes for SQL Server Compact Version

The provided script was tested using SQL Server Compact 3.5 SP1.
We included the compact database SampleData\Chinook.sdf. If you prefer to create your own compact database, then you can use the CreateTables.sqlce script with SQL Server Management Studio Express tool.

Updated Release: Chinook Database 1.1 (Jan 04, 2009)

lerocha — Tue, 06 Jan 2009 03:08:40 GMT

What is new?

Support for SQL Server Compact.
Additional customers from multiple countries.
Added a many-to-many relationship (a Playlist contains many Tracks, a Track belongs to many Playlists).
Added a one-to-many relationship between Employee and Customer (support representative).
Added Total field to invoice table.

Supported Database Servers

MySQL
Oracle
SQL Server
SQL Server Compact

How do I Create the Chinook Database?

Notes for MySQL Version

The provided script was tested using MySQL Server 5.1.
The CreateDB.bat file uses the localhost server, and the root user with the password p4ssw0rd. Change this file to match your settings before running it.

Notes for Oracle Version

The provided script was tested using Oracle Database 10g Express Edition (XE).
The CreateDB.sql creates a new user chinook, with the password p4ssw0rd. Change this script if you want a different user/password.

Notes for SQL Server Version

The provided script was tested using SQL Server Express 2008.

Notes for SQL Server Compact Version

The provided script was tested using SQL Server Compact 3.5 SP1.
We included the compact database SampleData\Chinook.sdf. If you prefer to create your own compact database, then you can use the CreateTables.sql script with SQL Server Management Studio Express tool.

Released: Chinook Database 1.1 (Jan 04, 2009)

Tue, 06 Jan 2009 03:08:40 GMT

What is new?

Support for SQL Server Compact.
Additional customers from multiple countries.
Added a many-to-many relationship (a Playlist contains many Tracks, a Track belongs to many Playlists).
Added a one-to-many relationship between Employee and Customer (support representative).
Added Total field to invoice table.

Supported Database Servers

MySQL
Oracle
SQL Server
SQL Server Compact

How do I Create the Chinook Database?

Notes for MySQL Version

The provided script was tested using MySQL Server 5.1.
The CreateDB.bat file uses the localhost server, and the root user with the password p4ssw0rd. Change this file to match your settings before running it.

Notes for Oracle Version

The provided script was tested using Oracle Database 10g Express Edition (XE).
The CreateDB.sql creates a new user chinook, with the password p4ssw0rd. Change this script if you want a different user/password.

Notes for SQL Server Version

The provided script was tested using SQL Server Express 2008.

Notes for SQL Server Compact Version

The provided script was tested using SQL Server Compact 3.5 SP1.
We included the compact database SampleData\Chinook.sdf. If you prefer to create your own compact database, then you can use the CreateTables.sql script with SQL Server Management Studio Express tool.

Closed Issue: Error when creating database for the first time in SQL Server Express

lerocha — Tue, 06 Jan 2009 03:00:44 GMT

When trying to create the Chinook Database for the first time (SQL Express only) we get the following error:

Msg 911, Level 16, State 1, Server VM-MCPD\SQLEXPRESS, Line 13
Could not locate entry in sysdatabases for database 'Chinook'. No entry found with that name. Make sure that the name is entered correctly.

The scripts for the other databases do not have this issue.

Workaround:
Edit the Scripts\SqlServer\CreateDB.sql and add the GO command line right after the CREATE DATABASE line:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Chinook') DROP DATABASE [Chinook];
CREATE DATABASE [Chinook];
GO

Source code checked in, #45543

lerocha — Tue, 06 Jan 2009 03:00:42 GMT

Work Item # 20453: Adding missing GO command in the SQL Server script. The missing command was causing the script to fail when creating the database for the first time.

Created Issue: Error when creating database using SQL Express 2005

lerocha — Mon, 05 Jan 2009 21:30:12 GMT

When trying to create the Chinook Database using SQL Express 2005 we get the following error:

Msg 911, Level 16, State 1, Server VM-MCPD\SQLEXPRESS, Line 13
Could not locate entry in sysdatabases for database 'Chinook'. No entry found with that name. Make sure that the name is entered correctly.

This only happens when:
1. Using SQL Express 2005.
2. It is the first time that the Chinook Database is created on the DB Server.

Workaround:
Edit the Scripts\SqlServer\CreateDB.sql and add the GO command line right after the CREATE DATABASE line:

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Chinook') DROP DATABASE [Chinook];
CREATE DATABASE [Chinook];
GO