foodmart

package module

v0.6.1 Latest Latest Go to latest Published: Sep 5, 2026 License: Apache-2.0 Imports: 10 Imported by: 0

Details

Valid go.mod file
Redistributable license
Tagged version
Stable version
Learn more about best practices

Repository

github.com/hydromatic/foodmart-data

Links

Open Source Insights

README ¶

foodmart-data

Foodmart data set as CSV files, published for Go and Rust.

This project contains the Foodmart data set as CSV files, embedded in a Go module and a Rust crate. Neither has any dependencies, and neither links a database; you can read the rows directly, or generate SQL and run it against a database of your choice.

It originated as part of the test suite of the Mondrian OLAP engine.

Schema

Foodmart contains 26 tables:

7 fact tables: sales_fact_1997, sales_fact_1998, sales_fact_dec_1998, inventory_fact_1997, inventory_fact_1998, salary, expense_fact
19 dimension tables: product, customer, time_by_day, employee and more

Together they hold 328,060 rows, about 15MB uncompressed.

The schema is defined in tools/schema.py, and is available at run time as foodmart.Tables in Go and foodmart_data::TABLES in Rust. Both can emit a CREATE TABLE statement for a table. There is a schema diagram in the foodmart-data-hsqldb project; note that it also shows the aggregate tables, which this project does not include (see below).

The files

Each table is a file csv/table.csv. The first line is a header row of column names; the remaining lines are data rows, quoted according to RFC 4180. An empty field represents SQL NULL.

$ head -3 csv/days.csv
day,week_day
1,Sunday
2,Monday

The column types are those of the foodmart-data-hsqldb project, from which these files are taken. They are defined in tools/schema.py, and are available at run time as foodmart.Tables.

Using the data set from Go

package main

import (
	"fmt"
	"log"

	foodmart "github.com/hydromatic/foodmart-data"
)

func main() {
	table, ok := foodmart.Find("employee")
	if !ok {
		log.Fatal("no such table")
	}
	rows, err := table.ReadAll()
	if err != nil {
		log.Fatal(err)
	}
	for _, row := range rows[:10] {
		fmt.Println(row[0] + ":" + row[1])
	}
}

To load the whole data set into a SQL database, use Load. This package has no driver of its own, so you supply the database:

import (
	"context"
	"database/sql"

	foodmart "github.com/hydromatic/foodmart-data"
	_ "modernc.org/sqlite"
)

db, err := sql.Open("sqlite", ":memory:")
if err != nil {
	log.Fatal(err)
}
if err := foodmart.Load(context.Background(), db); err != nil {
	log.Fatal(err)
}

var n int
err = db.QueryRow(`select count(*) from "sales_fact_1997"`).Scan(&n)

Loading all 26 tables takes about half a second.

Using the data set from Rust

let table = foodmart_data::find("employee").unwrap();
for row in table.rows().take(10) {
    println!("{}:{}", row[0], row[1]);
}

The crate embeds each table's CSV text, so there are no files to find at run time. To load the data into a database, generate SQL with create_table_sql and insert_sql:

for table in &foodmart_data::TABLES {
    connection.execute(&table.create_table_sql())?;
    for row in table.rows() {
        connection.execute(&table.insert_sql(&row))?;
    }
}

Aggregate tables

The Foodmart data set also has 11 aggregate tables, whose names start with agg_. This project does not include them: they are 60% of the data by size, and each is a GROUP BY rollup of sales_fact_1997, so you can compute any of them from the data that is here. For example, agg_l_03_sales_fact_1997 is

SELECT "time_id", "customer_id",
    SUM("store_sales"), SUM("store_cost"), SUM("unit_sales"), COUNT(*)
FROM "sales_fact_1997"
GROUP BY "time_id", "customer_id"

If you need the aggregate tables as data, use foodmart-data-hsqldb.

Get foodmart-data

From crates.io

Add the crate to your Cargo.toml:

[dependencies]
foodmart-data = "0.6.1"

From the Go module proxy

$ go get github.com/hydromatic/foodmart-data@v0.6.1

Download and build

$ git clone https://github.com/hydromatic/foodmart-data.git
$ cd foodmart-data
$ go test ./...
$ cargo test

schema.go and src/schema.rs are generated from the SCHEMA table in tools/schema.py. After editing it, regenerate them:

$ ./tools/schema.py

More information

License: Apache License, Version 2.0
Author: Julian Hyde (@julianhyde)
Blog: http://blog.hydromatic.net
Source code: https://github.com/hydromatic/foodmart-data
Issues: https://github.com/hydromatic/foodmart-data/issues
Release notes: CHANGELOG.md

Documentation ¶

Overview ¶

Package foodmart provides the Foodmart data set as embedded CSV files.

The data set originated as part of the test suite of the Pentaho Mondrian OLAP engine. It contains 26 tables: 7 fact tables, such as sales_fact_1997, and 19 dimension tables, such as customer.

Tables describes the schema. Each Table can be read as CSV, or the whole data set can be loaded into a SQL database using Load.

Index ¶

Variables
func FS() fs.FS
func Load(ctx context.Context, db *sql.DB) error
func TableNames() []string
type Column
type Table
- func Find(name string) (Table, bool)

Constants ¶

This section is empty.

Variables ¶

View Source

var Tables = []Table{
	{
		Name: "account",
		Columns: []Column{
			{Name: "account_id", Type: "INTEGER", NotNull: true},
			{Name: "account_parent", Type: "INTEGER", NotNull: false},
			{Name: "account_description", Type: "VARCHAR(30)", NotNull: false},
			{Name: "account_type", Type: "VARCHAR(30)", NotNull: true},
			{Name: "account_rollup", Type: "VARCHAR(30)", NotNull: true},
			{Name: "Custom_Members", Type: "VARCHAR(255)", NotNull: false},
		},
	},
	{
		Name: "category",
		Columns: []Column{
			{Name: "category_id", Type: "VARCHAR(30)", NotNull: true},
			{Name: "category_parent", Type: "VARCHAR(30)", NotNull: false},
			{Name: "category_description", Type: "VARCHAR(30)", NotNull: true},
			{Name: "category_rollup", Type: "VARCHAR(30)", NotNull: false},
		},
	},
	{
		Name: "currency",
		Columns: []Column{
			{Name: "currency_id", Type: "INTEGER", NotNull: true},
			{Name: "date", Type: "DATE", NotNull: true},
			{Name: "currency", Type: "VARCHAR(30)", NotNull: true},
			{Name: "conversion_ratio", Type: "DECIMAL(10,4)", NotNull: true},
		},
	},
	{
		Name: "customer",
		Columns: []Column{
			{Name: "customer_id", Type: "INTEGER", NotNull: true},
			{Name: "account_num", Type: "BIGINT", NotNull: true},
			{Name: "lname", Type: "VARCHAR(30)", NotNull: true},
			{Name: "fname", Type: "VARCHAR(30)", NotNull: true},
			{Name: "mi", Type: "VARCHAR(30)", NotNull: false},
			{Name: "address1", Type: "VARCHAR(30)", NotNull: false},
			{Name: "address2", Type: "VARCHAR(30)", NotNull: false},
			{Name: "address3", Type: "VARCHAR(30)", NotNull: false},
			{Name: "address4", Type: "VARCHAR(30)", NotNull: false},
			{Name: "city", Type: "VARCHAR(30)", NotNull: false},
			{Name: "state_province", Type: "VARCHAR(30)", NotNull: false},
			{Name: "postal_code", Type: "VARCHAR(30)", NotNull: true},
			{Name: "country", Type: "VARCHAR(30)", NotNull: true},
			{Name: "customer_region_id", Type: "INTEGER", NotNull: true},
			{Name: "phone1", Type: "VARCHAR(30)", NotNull: true},
			{Name: "phone2", Type: "VARCHAR(30)", NotNull: true},
			{Name: "birthdate", Type: "DATE", NotNull: true},
			{Name: "marital_status", Type: "VARCHAR(30)", NotNull: true},
			{Name: "yearly_income", Type: "VARCHAR(30)", NotNull: true},
			{Name: "gender", Type: "VARCHAR(30)", NotNull: true},
			{Name: "total_children", Type: "SMALLINT", NotNull: true},
			{Name: "num_children_at_home", Type: "SMALLINT", NotNull: true},
			{Name: "education", Type: "VARCHAR(30)", NotNull: true},
			{Name: "date_accnt_opened", Type: "DATE", NotNull: true},
			{Name: "member_card", Type: "VARCHAR(30)", NotNull: false},
			{Name: "occupation", Type: "VARCHAR(30)", NotNull: false},
			{Name: "houseowner", Type: "VARCHAR(30)", NotNull: false},
			{Name: "num_cars_owned", Type: "INTEGER", NotNull: false},
			{Name: "fullname", Type: "VARCHAR(60)", NotNull: true},
		},
	},
	{
		Name: "days",
		Columns: []Column{
			{Name: "day", Type: "INTEGER", NotNull: true},
			{Name: "week_day", Type: "VARCHAR(30)", NotNull: true},
		},
	},
	{
		Name: "department",
		Columns: []Column{
			{Name: "department_id", Type: "INTEGER", NotNull: true},
			{Name: "department_description", Type: "VARCHAR(30)", NotNull: true},
		},
	},
	{
		Name: "employee",
		Columns: []Column{
			{Name: "employee_id", Type: "INTEGER", NotNull: true},
			{Name: "full_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "first_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "last_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "position_id", Type: "INTEGER", NotNull: false},
			{Name: "position_title", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "department_id", Type: "INTEGER", NotNull: true},
			{Name: "birth_date", Type: "DATE", NotNull: true},
			{Name: "hire_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "end_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "salary", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "supervisor_id", Type: "INTEGER", NotNull: false},
			{Name: "education_level", Type: "VARCHAR(30)", NotNull: true},
			{Name: "marital_status", Type: "VARCHAR(30)", NotNull: true},
			{Name: "gender", Type: "VARCHAR(30)", NotNull: true},
			{Name: "management_role", Type: "VARCHAR(30)", NotNull: false},
		},
	},
	{
		Name: "employee_closure",
		Columns: []Column{
			{Name: "employee_id", Type: "INTEGER", NotNull: true},
			{Name: "supervisor_id", Type: "INTEGER", NotNull: true},
			{Name: "distance", Type: "INTEGER", NotNull: false},
		},
	},
	{
		Name: "expense_fact",
		Columns: []Column{
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "account_id", Type: "INTEGER", NotNull: true},
			{Name: "exp_date", Type: "TIMESTAMP", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: true},
			{Name: "category_id", Type: "VARCHAR(30)", NotNull: true},
			{Name: "currency_id", Type: "INTEGER", NotNull: true},
			{Name: "amount", Type: "DECIMAL(10,4)", NotNull: true},
		},
	},
	{
		Name: "inventory_fact_1997",
		Columns: []Column{
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: false},
			{Name: "warehouse_id", Type: "INTEGER", NotNull: false},
			{Name: "store_id", Type: "INTEGER", NotNull: false},
			{Name: "units_ordered", Type: "INTEGER", NotNull: false},
			{Name: "units_shipped", Type: "INTEGER", NotNull: false},
			{Name: "warehouse_sales", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "warehouse_cost", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "supply_time", Type: "SMALLINT", NotNull: false},
			{Name: "store_invoice", Type: "DECIMAL(10,4)", NotNull: false},
		},
	},
	{
		Name: "inventory_fact_1998",
		Columns: []Column{
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: false},
			{Name: "warehouse_id", Type: "INTEGER", NotNull: false},
			{Name: "store_id", Type: "INTEGER", NotNull: false},
			{Name: "units_ordered", Type: "INTEGER", NotNull: false},
			{Name: "units_shipped", Type: "INTEGER", NotNull: false},
			{Name: "warehouse_sales", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "warehouse_cost", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "supply_time", Type: "SMALLINT", NotNull: false},
			{Name: "store_invoice", Type: "DECIMAL(10,4)", NotNull: false},
		},
	},
	{
		Name: "position",
		Columns: []Column{
			{Name: "position_id", Type: "INTEGER", NotNull: true},
			{Name: "position_title", Type: "VARCHAR(30)", NotNull: true},
			{Name: "pay_type", Type: "VARCHAR(30)", NotNull: true},
			{Name: "min_scale", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "max_scale", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "management_role", Type: "VARCHAR(30)", NotNull: true},
		},
	},
	{
		Name: "product",
		Columns: []Column{
			{Name: "product_class_id", Type: "INTEGER", NotNull: true},
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "brand_name", Type: "VARCHAR(60)", NotNull: false},
			{Name: "product_name", Type: "VARCHAR(60)", NotNull: true},
			{Name: "SKU", Type: "BIGINT", NotNull: true},
			{Name: "SRP", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "gross_weight", Type: "DOUBLE", NotNull: false},
			{Name: "net_weight", Type: "DOUBLE", NotNull: false},
			{Name: "recyclable_package", Type: "BOOLEAN", NotNull: false},
			{Name: "low_fat", Type: "BOOLEAN", NotNull: false},
			{Name: "units_per_case", Type: "SMALLINT", NotNull: false},
			{Name: "cases_per_pallet", Type: "SMALLINT", NotNull: false},
			{Name: "shelf_width", Type: "DOUBLE", NotNull: false},
			{Name: "shelf_height", Type: "DOUBLE", NotNull: false},
			{Name: "shelf_depth", Type: "DOUBLE", NotNull: false},
		},
	},
	{
		Name: "product_class",
		Columns: []Column{
			{Name: "product_class_id", Type: "INTEGER", NotNull: true},
			{Name: "product_subcategory", Type: "VARCHAR(30)", NotNull: false},
			{Name: "product_category", Type: "VARCHAR(30)", NotNull: false},
			{Name: "product_department", Type: "VARCHAR(30)", NotNull: false},
			{Name: "product_family", Type: "VARCHAR(30)", NotNull: false},
		},
	},
	{
		Name: "promotion",
		Columns: []Column{
			{Name: "promotion_id", Type: "INTEGER", NotNull: true},
			{Name: "promotion_district_id", Type: "INTEGER", NotNull: false},
			{Name: "promotion_name", Type: "VARCHAR(30)", NotNull: false},
			{Name: "media_type", Type: "VARCHAR(30)", NotNull: false},
			{Name: "cost", Type: "DECIMAL(10,4)", NotNull: false},
			{Name: "start_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "end_date", Type: "TIMESTAMP", NotNull: false},
		},
	},
	{
		Name: "region",
		Columns: []Column{
			{Name: "region_id", Type: "INTEGER", NotNull: true},
			{Name: "sales_city", Type: "VARCHAR(30)", NotNull: false},
			{Name: "sales_state_province", Type: "VARCHAR(30)", NotNull: false},
			{Name: "sales_district", Type: "VARCHAR(30)", NotNull: false},
			{Name: "sales_region", Type: "VARCHAR(30)", NotNull: false},
			{Name: "sales_country", Type: "VARCHAR(30)", NotNull: false},
			{Name: "sales_district_id", Type: "INTEGER", NotNull: false},
		},
	},
	{
		Name: "reserve_employee",
		Columns: []Column{
			{Name: "employee_id", Type: "INTEGER", NotNull: true},
			{Name: "full_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "first_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "last_name", Type: "VARCHAR(30)", NotNull: true},
			{Name: "position_id", Type: "INTEGER", NotNull: false},
			{Name: "position_title", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "department_id", Type: "INTEGER", NotNull: true},
			{Name: "birth_date", Type: "TIMESTAMP", NotNull: true},
			{Name: "hire_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "end_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "salary", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "supervisor_id", Type: "INTEGER", NotNull: false},
			{Name: "education_level", Type: "VARCHAR(30)", NotNull: true},
			{Name: "marital_status", Type: "VARCHAR(30)", NotNull: true},
			{Name: "gender", Type: "VARCHAR(30)", NotNull: true},
		},
	},
	{
		Name: "salary",
		Columns: []Column{
			{Name: "pay_date", Type: "TIMESTAMP", NotNull: true},
			{Name: "employee_id", Type: "INTEGER", NotNull: true},
			{Name: "department_id", Type: "INTEGER", NotNull: true},
			{Name: "currency_id", Type: "INTEGER", NotNull: true},
			{Name: "salary_paid", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "overtime_paid", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "vacation_accrued", Type: "DOUBLE", NotNull: true},
			{Name: "vacation_used", Type: "DOUBLE", NotNull: true},
		},
	},
	{
		Name: "sales_fact_1997",
		Columns: []Column{
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: true},
			{Name: "customer_id", Type: "INTEGER", NotNull: true},
			{Name: "promotion_id", Type: "INTEGER", NotNull: true},
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "store_sales", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "store_cost", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "unit_sales", Type: "DECIMAL(10,4)", NotNull: true},
		},
	},
	{
		Name: "sales_fact_1998",
		Columns: []Column{
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: true},
			{Name: "customer_id", Type: "INTEGER", NotNull: true},
			{Name: "promotion_id", Type: "INTEGER", NotNull: true},
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "store_sales", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "store_cost", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "unit_sales", Type: "DECIMAL(10,4)", NotNull: true},
		},
	},
	{
		Name: "sales_fact_dec_1998",
		Columns: []Column{
			{Name: "product_id", Type: "INTEGER", NotNull: true},
			{Name: "time_id", Type: "INTEGER", NotNull: true},
			{Name: "customer_id", Type: "INTEGER", NotNull: true},
			{Name: "promotion_id", Type: "INTEGER", NotNull: true},
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "store_sales", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "store_cost", Type: "DECIMAL(10,4)", NotNull: true},
			{Name: "unit_sales", Type: "DECIMAL(10,4)", NotNull: true},
		},
	},
	{
		Name: "store",
		Columns: []Column{
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "store_type", Type: "VARCHAR(30)", NotNull: false},
			{Name: "region_id", Type: "INTEGER", NotNull: false},
			{Name: "store_name", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_number", Type: "INTEGER", NotNull: false},
			{Name: "store_street_address", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_city", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_state", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_postal_code", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_country", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_manager", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_phone", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_fax", Type: "VARCHAR(30)", NotNull: false},
			{Name: "first_opened_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "last_remodel_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "store_sqft", Type: "INTEGER", NotNull: false},
			{Name: "grocery_sqft", Type: "INTEGER", NotNull: false},
			{Name: "frozen_sqft", Type: "INTEGER", NotNull: false},
			{Name: "meat_sqft", Type: "INTEGER", NotNull: false},
			{Name: "coffee_bar", Type: "BOOLEAN", NotNull: false},
			{Name: "video_store", Type: "BOOLEAN", NotNull: false},
			{Name: "salad_bar", Type: "BOOLEAN", NotNull: false},
			{Name: "prepared_food", Type: "BOOLEAN", NotNull: false},
			{Name: "florist", Type: "BOOLEAN", NotNull: false},
		},
	},
	{
		Name: "store_ragged",
		Columns: []Column{
			{Name: "store_id", Type: "INTEGER", NotNull: true},
			{Name: "store_type", Type: "VARCHAR(30)", NotNull: false},
			{Name: "region_id", Type: "INTEGER", NotNull: false},
			{Name: "store_name", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_number", Type: "INTEGER", NotNull: false},
			{Name: "store_street_address", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_city", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_state", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_postal_code", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_country", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_manager", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_phone", Type: "VARCHAR(30)", NotNull: false},
			{Name: "store_fax", Type: "VARCHAR(30)", NotNull: false},
			{Name: "first_opened_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "last_remodel_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "store_sqft", Type: "INTEGER", NotNull: false},
			{Name: "grocery_sqft", Type: "INTEGER", NotNull: false},
			{Name: "frozen_sqft", Type: "INTEGER", NotNull: false},
			{Name: "meat_sqft", Type: "INTEGER", NotNull: false},
			{Name: "coffee_bar", Type: "BOOLEAN", NotNull: false},
			{Name: "video_store", Type: "BOOLEAN", NotNull: false},
			{Name: "salad_bar", Type: "BOOLEAN", NotNull: false},
			{Name: "prepared_food", Type: "BOOLEAN", NotNull: false},
			{Name: "florist", Type: "BOOLEAN", NotNull: false},
		},
	},
	{
		Name: "time_by_day",
		Columns: []Column{
			{Name: "time_id", Type: "INTEGER", NotNull: true},
			{Name: "the_date", Type: "TIMESTAMP", NotNull: false},
			{Name: "the_day", Type: "VARCHAR(30)", NotNull: false},
			{Name: "the_month", Type: "VARCHAR(30)", NotNull: false},
			{Name: "the_year", Type: "SMALLINT", NotNull: false},
			{Name: "day_of_month", Type: "SMALLINT", NotNull: false},
			{Name: "week_of_year", Type: "INTEGER", NotNull: false},
			{Name: "month_of_year", Type: "SMALLINT", NotNull: false},
			{Name: "quarter", Type: "VARCHAR(30)", NotNull: false},
			{Name: "fiscal_period", Type: "VARCHAR(30)", NotNull: false},
		},
	},
	{
		Name: "warehouse",
		Columns: []Column{
			{Name: "warehouse_id", Type: "INTEGER", NotNull: true},
			{Name: "warehouse_class_id", Type: "INTEGER", NotNull: false},
			{Name: "stores_id", Type: "INTEGER", NotNull: false},
			{Name: "warehouse_name", Type: "VARCHAR(60)", NotNull: false},
			{Name: "wa_address1", Type: "VARCHAR(30)", NotNull: false},
			{Name: "wa_address2", Type: "VARCHAR(30)", NotNull: false},
			{Name: "wa_address3", Type: "VARCHAR(30)", NotNull: false},
			{Name: "wa_address4", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_city", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_state_province", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_postal_code", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_country", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_owner_name", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_phone", Type: "VARCHAR(30)", NotNull: false},
			{Name: "warehouse_fax", Type: "VARCHAR(30)", NotNull: false},
		},
	},
	{
		Name: "warehouse_class",
		Columns: []Column{
			{Name: "warehouse_class_id", Type: "INTEGER", NotNull: true},
			{Name: "description", Type: "VARCHAR(30)", NotNull: false},
		},
	},
}

Tables describes every table in the data set, in alphabetical order.

Functions ¶

func FS ¶

func FS() fs.FS

FS returns the CSV files, one per table, named "<table>.csv".

func Load ¶

func Load(ctx context.Context, db *sql.DB) error

Load creates every Foodmart table in db and inserts all rows.

This package has no driver of its own; the caller supplies the database. For example, using a SQLite driver:

db, err := sql.Open("sqlite", ":memory:")
if err != nil {
	return err
}
if err := foodmart.Load(ctx, db); err != nil {
	return err
}

The statements use "?" parameter markers and standard double-quoted identifiers; see Table.InsertSQL. Everything runs in a single transaction, so a failure leaves db unchanged.

func TableNames ¶

func TableNames() []string

TableNames returns the names of all tables, in alphabetical order.

Types ¶

type Column ¶

type Column struct {
	// Name is the column name, as it appears in the CSV header row.
	Name string
	// Type is the SQL type, for example "VARCHAR(30)" or "DECIMAL(10,4)".
	Type string
	// NotNull is whether the column is declared NOT NULL.
	NotNull bool
}

Column is a column of a Table.

type Table ¶

type Table struct {
	// Name is the table name, and also the base name of its CSV file.
	Name string
	// Columns are the table's columns, in CSV column order.
	Columns []Column
}

Table is a table in the Foodmart data set.

The zero value is not useful; obtain tables from Tables or Find.

func Find ¶

func Find(name string) (Table, bool)

Find returns the table with the given name.

func (Table) ColumnNames ¶

func (t Table) ColumnNames() []string

ColumnNames returns the table's column names, in CSV column order.

func (Table) CreateSQL ¶

func (t Table) CreateSQL() string

CreateSQL returns a CREATE TABLE statement for the table, with identifiers quoted using the SQL standard double-quote.

func (Table) InsertSQL ¶

func (t Table) InsertSQL() string

InsertSQL returns an INSERT statement for the table, with one positional parameter marker per column.

The marker is "?", which suits SQLite, MySQL and DuckDB. For a database that numbers its parameters, such as PostgreSQL, build the statement from Table.ColumnNames instead.

func (Table) Open ¶

func (t Table) Open() (fs.File, error)

Open returns the table's CSV file, including its header row. The caller must close it.

func (Table) Path ¶

func (t Table) Path() string

Path returns the table's path within FS, for example "customer.csv".

func (Table) ReadAll ¶

func (t Table) ReadAll() ([][]string, error)

ReadAll returns every data row of the table. The header row is not included. Empty fields represent SQL NULL.

The larger fact tables have over 100,000 rows; to avoid holding them all in memory, use Table.Open and Table.Reader instead.

func (Table) Reader ¶

func (t Table) Reader(f fs.File) (*csv.Reader, error)

Reader returns a CSV reader over f that has consumed the header row, so that the next read returns the first data row. It also checks that the header matches the table's declared columns.

Source Files ¶

View all Source files

?	: This menu
/	: Search site
f or F	: Jump to
y or Y	: Canonical URL