Northwind: Access 97 database and its migrated version, compared row by row

Source and method

The source is nwind.mdb, the Northwind sample in Access 97 (Jet 3) format, taken from the public test data of the mdbtools project. It is Microsoft's demonstration database: the companies and people in it are fictitious. The file was only read, never opened in Access, and nothing stored in it was executed.

What is compared

• Tables. Row counts and a SHA-256 checksum of every row, matched by primary key, between the original file (read with mdbtools) and each new database. Each value is first written in a canonical form: amounts with four decimals, dates as YYYY-MM-DD, pictures as their pixels. The documented text clean-up is applied to the original side, and every value it changed is checked against the migration log. As a cross-check, the two readers of the original file (mdbtools and Jackcess) must agree with each other.
• Queries. For every saved query, the complete result set (all columns, all rows, duplicates counted) is compared four ways: (1) with an independent model of the Access query written in Python and computed from the original tables; (2) with the Access SQL text executed on the original file by UCanAccess; (3) with the same view in PostgreSQL; (4) with the same view in the browser engine of the web app. Where a query has an ORDER BY, the order of the sort keys is also compared.
• Calculations. Order totals, invoice figures, sales by category and year, sales by year and quarter and the grand total, compared in the same four ways.
• Constraints. Statements that break a rule of the original database are sent to the new databases, which must refuse them.

Tables: 9 of 9 identical

Every table of the original file was migrated, including Umsätze, a German-named copy of Orders that we found in the file (same 830 rows, same values). It is kept as a read-only archive table. The 17 pictures stored as OLE objects were converted to PNG; their pixels are compared, and the original bytes are kept in legacy_ole_objects (17 of 17 byte-identical to the file).

Saved queries: 22 of 22 equivalent

Each of the 22 saved queries is now a view with the same name in snake case, in both SQLite and PostgreSQL. The three parameter queries became views without parameters; the application supplies the values through a WHERE clause, as listed in db/parameterized.sql. Invoices Filter was run for each of the 830 orders. Open a query to see its original SQL and how it was run.

About the Access SQL column. UCanAccess reads the Access 97 discount column as a whole number, so every discount arrives as 0 (see tool findings). To compare like with like, the views are run for this check on a control copy of the new database in which the discounts are also 0. Real discounts are covered by the Python model, PostgreSQL and the browser.

SELECT DISTINCTROW Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=No));

SELECT DISTINCTROW Categories.CategoryName, Categories.Description, Categories.Picture, Products.ProductID, Products.ProductName, Products.QuantityPerUnit, Products.UnitPrice
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=No));

SELECT DISTINCTROW [Product Sales for 1995].CategoryName, Sum([Product Sales for 1995].ProductSales) AS CategorySales
FROM [Product Sales for 1995]
GROUP BY [Product Sales for 1995].CategoryName;

SELECT [Product List].ProductID, [Product List].ProductName
FROM Products AS [Product List]
WHERE ((([Product List].Discontinued)=No))
ORDER BY [Product List].ProductName;

SELECT City, CompanyName, ContactName, "Customers" AS [Relationship] 
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

PARAMETERS Beginning Date DateTime, Ending Date DateTime;
SELECT DISTINCTROW Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal AS SaleAmount
FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID
WHERE (((Orders.ShippedDate) Between [Beginning Date] And [Ending Date]));

SELECT DISTINCTROW Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country, [FirstName] & " " & [LastName] AS Salesperson, Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN (Products INNER JOIN ((Employees INNER JOIN (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID) ON Shippers.ShipperID = Orders.ShipVia;

SELECT DISTINCTROW Invoices.*
FROM Invoices
WHERE (((Invoices.OrderID)=[Forms]![Orders]![OrderID]));

SELECT DISTINCTROW [Order Details].OrderID, [Order Details].ProductID, Products.ProductName, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
ORDER BY [Order Details].OrderID;

SELECT DISTINCTROW [Order Details].OrderID, Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS Subtotal
FROM [Order Details]
GROUP BY [Order Details].OrderID;

SELECT DISTINCTROW Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Customers.CompanyName, Customers.Address, Customers.City, Customers.Region, Customers.PostalCode, Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductSales
FROM (Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID) INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.ShippedDate) Between #1/1/95# And #12/31/95#))
GROUP BY Categories.CategoryName, Products.ProductName;

SELECT DISTINCTROW Products.ProductName, Products.UnitPrice
FROM Products
WHERE (((Products.UnitPrice)>(SELECT AVG([UnitPrice]) From Products)))
ORDER BY Products.UnitPrice DESC;

SELECT DISTINCTROW Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)<>Yes))
ORDER BY Categories.CategoryName, Products.ProductName;

SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName, Customers.City, Customers.Country
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#));

TRANSFORM Sum(CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT DISTINCTROW Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

SELECT DISTINCTROW Categories.CategoryID, Categories.CategoryName, Products.ProductName, Sum([Order Details Extended].ExtendedPrice) AS ProductSales
FROM Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details Extended] ON Orders.OrderID = [Order Details Extended].OrderID) ON Products.ProductID = [Order Details Extended].ProductID) ON Categories.CategoryID = Products.CategoryID
WHERE (((Orders.OrderDate) Between #1/1/95# And #12/31/95#))
GROUP BY Categories.CategoryID, Categories.CategoryName, Products.ProductName
ORDER BY Products.ProductName;

PARAMETERS Forms!Sales by Year Dialog!BeginningDate DateTime, Forms!Sales by Year Dialog!EndingDate DateTime;
SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, Format([ShippedDate],"yyyy") AS Year
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null And (Orders.ShippedDate) Between [Forms]![Sales by Year Dialog]![BeginningDate] And [Forms]![Sales by Year Dialog]![EndingDate]));

SELECT DISTINCTROW [Order Subtotals].Subtotal AS SaleAmount, Orders.OrderID, Customers.CompanyName, Orders.ShippedDate
FROM Customers INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID) ON Customers.CustomerID = Orders.CustomerID
WHERE ((([Order Subtotals].Subtotal)>2500) AND ((Orders.ShippedDate) Between #1/1/95# And #12/31/95#));

SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null))
ORDER BY Orders.ShippedDate;

SELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal
FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
WHERE (((Orders.ShippedDate) Is Not Null))
ORDER BY Orders.ShippedDate;

SELECT DISTINCTROW TOP 10 Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice
FROM Products
ORDER BY Products.UnitPrice DESC;

Calculations: 5 of 5 equal

The figures printed by the Access reports (invoice subtotal, freight and total; sales by category; sales by year and quarter) come from these calculations. The web app reads the same views.

Grand total of the 2,155 order lines: $1,265,793.02 in the new databases, $1,265,792.86 with the original floating-point arithmetic (difference $0.16, explained in D1).

Constraints: 17 of 17 enforced

The validation rules, required fields, field sizes and relationships of the Access file are now constraints in the database itself, so they hold for any program that writes to it, not only for the forms. Each statement below was sent to SQLite and to PostgreSQL inside a transaction that was then rolled back.

Intentional differences

D1. Discounts are exact percentages; 18 order lines change by one cent

Access evaluates order lines (price × quantity × (1 − discount), rounded to the cent by CCur) in binary floating point. The discount itself is a single-precision number, so 15% is held as 0.15000000596… and 5% as 0.05000000074…, and intermediate amounts such as 9.21375 cannot be held exactly either. On a line whose exact amount falls on a half cent, these tiny errors decide which cent Access shows. The new databases store the discount as the exact percentage and compute every line in whole numbers, with the same round-half-to-even rule that Access applies to an exact half. The result is identical in SQLite, PostgreSQL and the browser.

Compared with our model of the Access arithmetic (double precision, then CCur as implemented by OLE Automation: × 10,000 and round half to even), 18 of 2,155 lines on 17 orders differ by exactly 0.01. The grand total moves from $1,265,792.86 to $1,265,793.02. For lines this close to a half cent the exact Access result depends on the Jet engine's internal order of operations, which cannot be run outside Windows; the new rule removes that dependence.

D2. Text clean-up (148 values)

• C1, spaces at the start or end removed (41 values), for example Lino Rodriguez , Frankfurt a.M. , Antonio del Valle Saavedra , Sweden . One of them, Sweden , would otherwise appear as a separate country in any grouping. Double spaces inside a value were left as they are.
• C2, line breaks stored as LF instead of CR LF (114 values, in two-line addresses and notes). The text is the same; only the end-of-line characters change.

Values per table: customers 10, employees 3, legacy_umsaetze 62, orders 62, suppliers 11. Every change is listed, value by value, in db/migration_log.json. The ship name Galería del gastronómo on orders differs from the customer name Galería del gastrónomo; it is a copy made when the order was entered, so it was not corrected.

Other conversions (no value changed)

• Dates. Access date/time fields became date. No value in the file has a time part, which the migration verifies.
• Pictures. Paintbrush bitmaps wrapped in Access OLE objects became PNG files, pixel for pixel; the original objects are archived unchanged.
• Yes/No fields became booleans (Access stores Yes as −1).
• Supplier home pages keep the Access hyperlink text (label#address#) unchanged; the application shows the label and the address.
• Row order. Views keep the ORDER BY of the Access query. SQLite and PostgreSQL sort text by character code here; on this data the order of the sort keys is the same as in the Access SQL run. The web app sorts lists with the browser's language rules.

Tool findings

• UCanAccess 5.1.3 drops Access 97 discounts. It creates the Discount column (Single) as NUMERIC(100,0) in its in-memory copy, so all 838 non-zero discounts are read as 0. The table cross-check found it: every other column of every table matches. We kept UCanAccess as the engine for the Access SQL text and compared it with a control copy of the new database in which the discounts are 0.
• The SQL text rebuilt by Jackcess omits TOP 10 in Ten Most Expensive Products. The value is a query property in the system table MSysQueries; we read it from there (A4).
• UCanAccess could not load nine of the 22 saved queries as views (two-digit years, parameters, the crosstab, duplicate output names). Each was run from its SQL text with the adaptations below. The SQL logic itself was not rewritten.

Type mapping

Names follow the usual SQL convention (OrderDate → order_date). SQLite tables are declared STRICT, so a column refuses a value of the wrong type.

Reproduce

Everything above is produced by scripts; nothing in this report is typed by hand. From the case-study folder:

python3 migration/build_db.py      # migrate: db/northwind.sqlite, PostgreSQL scripts, migration log
python3 migration/equivalence.py   # all comparisons (about 89 s)
python3 migration/make_report.py   # this page and results.json

Delivered files: db/schema.sqlite.sql, db/views.sqlite.sql, db/schema.postgresql.sql, db/data.postgresql.sql, db/views.postgresql.sql, db/parameterized.sql, db/northwind.sqlite (SHA-256 525cdfd6a4eb016f…, 1,142,784 bytes).