Creating multiple databases - wiltondb/wiltondb GitHub Wiki

On this page:

• Prerequisites
• Physical vs Logical databases
• Creating new database in SSMS
• Browsing database contents in PgAdmin

Prerequisites

1. Install WiltonDB
2. Setup TDS connection from SSMS
3. Setup PostgreSQL connection from PgAdmin

Physical vs Logical databases

WiltonDB provides access to the same underlying DB objects over both TDS (default port 1433) and PostgreSQL (default port 5432) protocols.

While ordinary DB objects like tables or views are represented the same way over both connections, databases themselves are represented differently:

1. All DB objects (including databases themselves) accessible over TDS protocol are stored in a single "physical" PostgreSQL database named wilton

2. When new database is created over TDS port (using create database) a new schema dbname_dbo is created for it in "physical" database.

See details about the multi-db mode used by WiltonDB in Babelfish documentation).

Creating new database in SSMS

Open a connection to WiltonDB from SSMS and run the following SQL snippet in master database:

create database mydb1
use mydb1

create table mytab1(name1 nvarchar(42))
create table mytab2(name2 nvarchar(42))

insert into mytab1 values('foo')
insert into mytab1 values('bar')
insert into mytab1 values('baz')

Refresh the tree on the left and check that mydb1 database is there and its contents can be browsed (DB browsing with SSMS 19.2+ may require additional steps, see #10 for details):

Browsing database contents in PgAdmin

Open a connection to WiltonDB from PgAdmin and navigate to mydb1_dbo schema in wilton database. Check that the same tables with the same data are available there: