Skip to content

Example usage

Jump to bottom
Michal Tuček edited this page Apr 20, 2021 · 6 revisions

Example usage

This page will show you how to use Sequeltalk.

Table of contents

ERD

Let's say we have some eshop with the following MySQL database:

MySQL ERD

You can find the MySQL script for this DB here.

Specification of a SQL structure

Based on the ERD we can specify which tables and which columns we want to use:

<?php

use Sequeltak\App;
use Sequeltak\Collection;
use Sequeltak\Column;
use Sequeltak\Helper;
use Sequeltak\SmalltalkDataType;
use Sequeltak\Table;

require '../vendor/autoload.php';

$app = new App();

$app->addTable(new Table('billing_information', [
    new Column('country', SmalltalkDataType::STRING),
    new Column('city', SmalltalkDataType::STRING),
    new Column('street', SmalltalkDataType::STRING),
    new Column('zipCode', SmalltalkDataType::STRING),
    new Column('user_id', SmalltalkDataType::OBJECT,'user', 'u') // Foreign key
]));

$app->addTable(new Table('user', [
    new Column('firstName', SmalltalkDataType::STRING),
    new Column('lastName', SmalltalkDataType::STRING),
    new Column('birthdate', SmalltalkDataType::DATE)
]));

$app->addTable(new Table('product', [
    new Column('name', SmalltalkDataType::STRING),
    new Column('price', SmalltalkDataType::NUMBER),
    new Column('minAge', SmalltalkDataType::NUMBER),
    new Column('onStock', SmalltalkDataType::NUMBER)
]));

$app->addTable(new Table('coupon', [
    new Column('code', SmalltalkDataType::STRING),
    new Column('expirationDate', SmalltalkDataType::DATE),
    new Column('sale', SmalltalkDataType::NUMBER)
]));

$app->addTable(new Table('order_item', [
    new Column('quantity', SmalltalkDataType::NUMBER),
    new Column('unitPrice', SmalltalkDataType::NUMBER),
    new Column('product_id', SmalltalkDataType::OBJECT, 'product', 'p') // Foreign key
]));

$app->addTable(new Table('order', [
    new Column('createdAt', SmalltalkDataType::DATE),
    new Column('state', SmalltalkDataType::NUMBER),
    new Column('user_id', SmalltalkDataType::OBJECT, 'user', 'u') // Foreign key
]));

$app->addTable(new Table('invoice', [
    new Column('issueDate', SmalltalkDataType::DATE),
    new Column('dueDate', SmalltalkDataType::DATE),
    new Column('payDate', SmalltalkDataType::DATE),
    new Column('billing_information_id', SmalltalkDataType::OBJECT, 'billingInformation', 'bi') // Foreign key
]));

$app->run();

Generated row data

With these settings, the app will take rows from each table, select only the specified columns and return data with object schema (what attributes of what type the object needs):

"-------------- BillingInformation object schema --------------"
BillingInformation
	country: String
	city: String
	street: String
	zipCode: String
	user_id: Object
"-------------- BillingInformation records --------------"

bi1 := bi1 new.
bi1 country: 'Czechia'; city: 'Prague'; street: 'Roháčova 1126'; zipCode: '130 00'; user: u1.

...

bi6 := bi6 new.
bi6 country: 'Czechia'; city: 'Prague'; street: 'Kozácká 290/7'; zipCode: '101 00'; user: u1.

"-------------- User object schema --------------"
User
	firstName: String
	lastName: String
	birthdate: Date
"-------------- User records --------------"

u1 := u1 new.
u1 firstName: 'John'; lastName: 'Doe'; birthdate: '01 13 2000' asDate.

...

u5 := u5 new.
u5 firstName: 'Roman'; lastName: 'Zednický'; birthdate: '07 15 1982' asDate.

"-------------- Product object schema --------------"
Product
	name: String
	price: Number
	minAge: Number
	onStock: Number
"-------------- Product records --------------"

p1 := p1 new.
p1 name: 'Alcoholic beverage'; price: 119.99; minAge: 18; onStock: 120.

...

p5 := p5 new.
p5 name: 'Apple - MacBook Pro - 13" Display with Touch '; price: 45000; onStock: 32.

"-------------- Coupon object schema --------------"
Coupon
	code: String
	expirationDate: Date
	sale: Number
"-------------- Coupon records --------------"

c1 := c1 new.
c1 code: 'AAAAAA'; expirationDate: '02 01 2021' asDate; sale: 5.

...

c5 := c5 new.
c5 code: 'EEEEEE'; expirationDate: '06 01 2021' asDate; sale: 15.

"-------------- OrderItem object schema --------------"
OrderItem
	quantity: Number
	unitPrice: Number
	product_id: Object
"-------------- OrderItem records --------------"

oi1 := oi1 new.
oi1 quantity: 2; unitPrice: 1299.99; product: p3.

...

oi22 := oi22 new.
oi22 quantity: 1; unitPrice: 500000; product: p4.

"-------------- Order object schema --------------"
Order
	createdAt: Date
	state: Number
	user_id: Object
"-------------- Order records --------------"

o1 := o1 new.
o1 createdAt: '03 01 2021' asDate; state: 2; user: u1.

...

o20 := o20 new.
o20 createdAt: '03 20 2021' asDate; state: 2; user: u4.

"-------------- Invoice object schema --------------"
Invoice
	issueDate: Date
	dueDate: Date
	payDate: Date
	billing_information_id: Object
"-------------- Invoice records --------------"

i1 := i1 new.
i1 issueDate: '03 01 2021' asDate; dueDate: '04 01 2021' asDate; payDate: '03 02 2021' asDate; billingInformation: bi1.

...

i19 := i19 new.
i19 issueDate: '03 20 2021' asDate; dueDate: '04 20 2021' asDate; payDate: '03 28 2021' asDate; billingInformation: bi4.

Relations

Unfortunately, the relations are not generated automatically but we need to specify them manually with the help of Collection class.

$oi2p = new Collection('OrderItems -> Products', 'p', 'oi', [
    [15, 17, 18, 19, 20, 21],
    [14, 16],
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
    [22],
    [13]
], 'orderItems');

$oi2p->printCollection();

$oi2o = new Collection('OrderItems -> Orders', 'o', 'oi', [
    [1],
    [2],
    [3],
    [4],
    [5],
    [6],
    [7],
    [8],
    [9],
    [10],
    [11],
    [12],
    [13, 14],
    [15, 16],
    [17],
    [18],
    [19],
    [20],
    [21],
    [22],
], 'orderItems');

$oi2o->printCollection();

$o2u = new Collection('Orders -> Users', 'u', 'o', [
    [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12],
    [13, 14],
    [15, 16, 17, 18, 19],
    [20],
], 'orders');

$o2u->printCollection();

$o2u = new Collection('Invoices -> Orders', 'o', 'i', [
    0 => [1],
    1 => [2],
    2 => [3],
    3 => [4],
    4 => [5],
    5 => [6],
    6 => [7],
    7 => [8],
    8 => [9],
    9 => [10],
    11  => [11],
    12  => [12],
    13  => [13],
    14  => [14],
    15  => [15],
    16  => [16],
    17  => [17],
    18  => [18],
    19  => [19]
], 'invoice', 'state: 2');

$o2u->printCollection();

Generated relation data

"-------------- OrderItems -> Products --------------"

p1 orderItems add: oi15; add: oi17; add: oi18; add: oi19; add: oi20; add: oi21.
p2 orderItems add: oi14; add: oi16.
p3 orderItems add: oi1; add: oi2; add: oi3; add: oi4; add: oi5; add: oi6; add: oi7; add: oi8; add: oi9; add: oi10; add: oi11; add: oi12.
p4 orderItems add: oi22.
p5 orderItems add: oi13.


"-------------- OrderItems -> Orders --------------"

o1 orderItems add: oi1.
o2 orderItems add: oi2.
o3 orderItems add: oi3.
o4 orderItems add: oi4.
o5 orderItems add: oi5.
o6 orderItems add: oi6.
o7 orderItems add: oi7.
o8 orderItems add: oi8.
o9 orderItems add: oi9.
o10 orderItems add: oi10.
o11 orderItems add: oi11.
o12 orderItems add: oi12.
o13 orderItems add: oi13; add: oi14.
o14 orderItems add: oi15; add: oi16.
o15 orderItems add: oi17.
o16 orderItems add: oi18.
o17 orderItems add: oi19.
o18 orderItems add: oi20.
o19 orderItems add: oi21.
o20 orderItems add: oi22.


"-------------- Orders -> Users --------------"

u1 orders add: o1; add: o2; add: o3; add: o4; add: o5; add: o6; add: o7; add: o8; add: o9; add: o10; add: o11; add: o12.
u2 orders add: o13; add: o14.
u3 orders add: o15; add: o16; add: o17; add: o18; add: o19.
u4 orders add: o20.


"-------------- Invoices -> Orders --------------"

o1 invoice add: i1; state: 2.
o2 invoice add: i2; state: 2.
o3 invoice add: i3; state: 2.
o4 invoice add: i4; state: 2.
o5 invoice add: i5; state: 2.
o6 invoice add: i6; state: 2.
o7 invoice add: i7; state: 2.
o8 invoice add: i8; state: 2.
o9 invoice add: i9; state: 2.
o10 invoice add: i10; state: 2.
o12 invoice add: i11; state: 2.
o13 invoice add: i12; state: 2.
o14 invoice add: i13; state: 2.
o15 invoice add: i14; state: 2.
o16 invoice add: i15; state: 2.
o17 invoice add: i16; state: 2.
o18 invoice add: i17; state: 2.
o19 invoice add: i18; state: 2.
o20 invoice add: i19; state: 2.

Custom collections

We also want to create a collection of individual object instances.

Since we're using the Collection class instead of specifying the array of consecutive numbers, we can use a Helper method getIndexArray().

$users = new Collection('User collection', 'Users', 'u', [
    Helper::getIndexArray(5)
], null, null, false);

$users->printCollection();

$products = new Collection('Product collection', 'Products', 'p', [
    Helper::getIndexArray(5)
], null, null, false);

$products->printCollection();

$coupons = new Collection('Coupon collection', 'Coupons', 'c', [
    Helper::getIndexArray(5)
], null, null, false);

$coupons->printCollection();

$orders = new Collection('Order collection', 'Orders', 'o', [
    Helper::getIndexArray(20)
], null, null, false);

$orders->printCollection();

$orderItems = new Collection('OrderItem collection', 'OrderItems', 'oi', [
    Helper::getIndexArray(22)
], null, null, false);

$orderItems->printCollection();

$invoices = new Collection('Invoice collection', 'Invoices', 'i', [
    Helper::getIndexArray(19)
], null, null, false);

$invoices->printCollection();

Generated custom collections data

"-------------- User collection --------------"

Users := Set new.
Users add: u1; add: u2; add: u3; add: u4; add: u5.


"-------------- Product collection --------------"

Products := Set new.
Products add: p1; add: p2; add: p3; add: p4; add: p5.


"-------------- Coupon collection --------------"

Coupons := Set new.
Coupons add: c1; add: c2; add: c3; add: c4; add: c5.


"-------------- Order collection --------------"

Orders := Set new.
Orders add: o1; add: o2; add: o3; add: o4; add: o5; add: o6; add: o7; add: o8; add: o9; add: o10; add: o11; add: o12; add: o13; add: o14; add: o15; add: o16; add: o17; add: o18; add: o19; add: o20.


"-------------- OrderItem collection --------------"

OrderItems := Set new.
OrderItems add: oi1; add: oi2; add: oi3; add: oi4; add: oi5; add: oi6; add: oi7; add: oi8; add: oi9; add: oi10; add: oi11; add: oi12; add: oi13; add: oi14; add: oi15; add: oi16; add: oi17; add: oi18; add: oi19; add: oi20; add: oi21; add: oi22.


"-------------- Invoice collection --------------"

Invoices := Set new.
Invoices add: i1; add: i2; add: i3; add: i4; add: i5; add: i6; add: i7; add: i8; add: i9; add: i10; add: i11; add: i12; add: i13; add: i14; add: i15; add: i16; add: i17; add: i18; add: i19.

Conclusion

Even tho this tool is not the perfect one it can really save you some time when you need to create objected DB in Smalltalk while already having a relation DB in MySQL ready and filled with data.

Clone this wiki locally