1
use bonsaidb::core::actionable::async_trait;
2
use futures::StreamExt;
3
use sqlx::{postgres::PgArguments, Arguments, Connection, Executor, PgPool, Row, Statement};
4

            
5
use crate::{
6
    execute::{Backend, BackendOperator, Measurements, Metric, Operator},
7
    model::Product,
8
    plan::{
9
        AddProductToCart, Checkout, CreateCart, FindProduct, Load, LookupProduct, OperationResult,
10
        ReviewProduct,
11
    },
12
};
13

            
14
pub struct Postgres {
15
    pool: PgPool,
16
}
17

            
18
#[async_trait]
19
impl Backend for Postgres {
20
    type Operator = PostgresOperator;
21
    type Config = String;
22

            
23
    fn label(&self) -> &'static str {
24
        "postgresql"
25
    }
26

            
27
4
    async fn new(url: Self::Config) -> Self {
28
24
        let pool = PgPool::connect(&url).await.unwrap();
29

            
30
4
        let mut conn = pool.acquire().await.unwrap();
31
12
        conn.execute(r#"DROP SCHEMA IF EXISTS commerce_bench CASCADE"#)
32
12
            .await
33
4
            .unwrap();
34
8
        conn.execute(r#"CREATE SCHEMA commerce_bench"#)
35
8
            .await
36
4
            .unwrap();
37
8
        conn.execute("SET search_path='commerce_bench';")
38
8
            .await
39
4
            .unwrap();
40
4
        conn.execute(
41
4
            r#"CREATE TABLE customers (
42
4
            id SERIAL PRIMARY KEY,
43
4
            name TEXT, 
44
4
            email TEXT, 
45
4
            address TEXT, 
46
4
            city TEXT, 
47
4
            region TEXT, 
48
4
            country TEXT, 
49
4
            postal_code TEXT, 
50
4
            phone TEXT
51
4
        )"#,
52
8
        )
53
8
        .await
54
4
        .unwrap();
55
4
        conn.execute(
56
4
            r#"CREATE TABLE products (
57
4
                    id SERIAL PRIMARY KEY,
58
4
                    name TEXT
59
4
                )"#,
60
8
        )
61
8
        .await
62
4
        .unwrap();
63
8
        conn.execute(r#"CREATE INDEX products_by_name ON products(name)"#)
64
8
            .await
65
4
            .unwrap();
66
4
        conn.execute(
67
4
            r#"CREATE TABLE product_reviews (
68
4
                product_id INTEGER NOT NULL,-- REFERENCES products(id),
69
4
                customer_id INTEGER NOT NULL,-- REFERENCES customers(id),
70
4
                rating INTEGER NOT NULL,
71
4
                review TEXT
72
4
            )"#,
73
8
        )
74
8
        .await
75
4
        .unwrap();
76
8
        conn.execute(r#"CREATE INDEX product_reviews_by_product ON product_reviews(product_id)"#)
77
8
            .await
78
4
            .unwrap();
79
8
        conn.execute(r#"CREATE UNIQUE INDEX product_reviews_by_customer ON product_reviews(customer_id, product_id)"#)
80
8
            .await
81
4
            .unwrap();
82
4
        conn.execute(
83
4
            r#"CREATE MATERIALIZED VIEW 
84
4
                    product_ratings 
85
4
                AS 
86
4
                    SELECT 
87
4
                        product_id,
88
4
                        sum(rating)::int as total_rating, 
89
4
                        count(rating)::int as ratings
90
4
                    FROM
91
4
                        product_reviews
92
4
                    GROUP BY product_id
93
4
            "#,
94
8
        )
95
8
        .await
96
4
        .unwrap();
97
4
        conn.execute(
98
4
            r#"CREATE TABLE categories (
99
4
                    id SERIAL PRIMARY KEY,
100
4
                    name TEXT
101
4
                )"#,
102
8
        )
103
8
        .await
104
4
        .unwrap();
105
4
        conn.execute(
106
4
            r#"CREATE TABLE product_categories (
107
4
                    product_id INTEGER,-- REFERENCES products(id),
108
4
                    category_id INTEGER-- REFERENCES categories(id)
109
4
                )"#,
110
8
        )
111
8
        .await
112
4
        .unwrap();
113
4
        conn.execute(
114
4
            r#"CREATE INDEX product_categories_by_product ON product_categories(product_id)"#,
115
8
        )
116
8
        .await
117
4
        .unwrap();
118
4
        conn.execute(
119
4
            r#"CREATE INDEX product_categories_by_category ON product_categories(category_id)"#,
120
8
        )
121
8
        .await
122
4
        .unwrap();
123
4
        conn.execute(
124
4
            r#"CREATE TABLE orders (
125
4
                    id SERIAL PRIMARY KEY,
126
4
                    customer_id INTEGER -- REFERENCES customers(id)
127
4
                )"#,
128
8
        )
129
8
        .await
130
4
        .unwrap();
131
4
        conn.execute(
132
4
            r#"CREATE TABLE order_products (
133
4
                    order_id INTEGER NOT NULL,-- REFERENCES orders(id),
134
4
                    product_id INTEGER NOT NULL -- REFERENCES products(id)
135
4
                )"#,
136
8
        )
137
8
        .await
138
4
        .unwrap();
139
4
        conn.execute(
140
4
            r#"CREATE TABLE carts (
141
4
            id SERIAL PRIMARY KEY,
142
4
            customer_id INTEGER
143
4
        )"#,
144
8
        )
145
8
        .await
146
4
        .unwrap();
147
4
        conn.execute(
148
4
            r#"CREATE TABLE cart_products (
149
4
                cart_id INTEGER,
150
4
                product_id INTEGER
151
4
            )"#,
152
8
        )
153
8
        .await
154
4
        .unwrap();
155
4

            
156
4
        Self { pool }
157
8
    }
158

            
159
13
    async fn new_operator_async(&self) -> Self::Operator {
160
13
        PostgresOperator {
161
13
            sqlite: self.pool.clone(),
162
13
        }
163
13
    }
164
}
165

            
166
pub struct PostgresOperator {
167
    sqlite: PgPool,
168
}
169

            
170
impl BackendOperator for PostgresOperator {}
171

            
172
#[async_trait]
173
impl Operator<Load> for PostgresOperator {
174
4
    async fn operate(
175
4
        &mut self,
176
4
        operation: &Load,
177
4
        _results: &[OperationResult],
178
4
        measurements: &Measurements,
179
4
    ) -> OperationResult {
180
4
        let measurement = measurements.begin("postgresql", Metric::Load);
181
19
        let mut conn = self.sqlite.acquire().await.unwrap();
182
5
        let mut tx = conn.begin().await.unwrap();
183
4
        let insert_category = tx
184
8
            .prepare("INSERT INTO commerce_bench.categories (id, name) VALUES ($1, $2)")
185
8
            .await
186
4
            .unwrap();
187
40
        for (id, category) in &operation.initial_data.categories {
188
40
            let mut args = PgArguments::default();
189
40
            args.reserve(2, 0);
190
40
            args.add(*id);
191
40
            args.add(&category.name);
192
76
            tx.execute(insert_category.query_with(args)).await.unwrap();
193
        }
194

            
195
4
        let insert_product = tx
196
8
            .prepare("INSERT INTO commerce_bench.products (id, name) VALUES ($1, $2)")
197
8
            .await
198
4
            .unwrap();
199
4
        let insert_product_category = tx
200
4
            .prepare("INSERT INTO commerce_bench.product_categories (product_id, category_id) VALUES ($1, $2)")
201
4
            .await
202
4
            .unwrap();
203
489
        for (&id, product) in &operation.initial_data.products {
204
489
            let mut args = PgArguments::default();
205
489
            args.reserve(2, 0);
206
489
            args.add(id);
207
489
            args.add(&product.name);
208
963
            tx.execute(insert_product.query_with(args)).await.unwrap();
209
1475
            for &category_id in &product.category_ids {
210
986
                let mut args = PgArguments::default();
211
986
                args.reserve(2, 0);
212
986
                args.add(id);
213
986
                args.add(category_id);
214
1964
                tx.execute(insert_product_category.query_with(args))
215
1964
                    .await
216
986
                    .unwrap();
217
            }
218
        }
219

            
220
4
        let insert_customer = tx
221
8
            .prepare("INSERT INTO commerce_bench.customers (id, name, email, address, city, region, country, postal_code, phone) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)")
222
8
            .await
223
4
            .unwrap();
224
704
        for (id, customer) in &operation.initial_data.customers {
225
704
            let mut args = PgArguments::default();
226
704
            args.reserve(9, 0);
227
704
            args.add(*id);
228
704
            args.add(&customer.name);
229
704
            args.add(&customer.email);
230
704
            args.add(&customer.address);
231
704
            args.add(&customer.city);
232
704
            args.add(&customer.region);
233
704
            args.add(&customer.country);
234
704
            args.add(&customer.postal_code);
235
704
            args.add(&customer.phone);
236
1399
            tx.execute(insert_customer.query_with(args)).await.unwrap();
237
        }
238

            
239
4
        let insert_order = tx
240
8
            .prepare("INSERT INTO commerce_bench.orders (id, customer_id) VALUES ($1, $2)")
241
8
            .await
242
4
            .unwrap();
243
4
        let insert_order_product = tx
244
4
            .prepare(
245
4
                "INSERT INTO commerce_bench.order_products (order_id, product_id) VALUES ($1, $2)",
246
4
            )
247
4
            .await
248
4
            .unwrap();
249
1245
        for (&id, order) in &operation.initial_data.orders {
250
1245
            let mut args = PgArguments::default();
251
1245
            args.reserve(2, 0);
252
1245
            args.add(id);
253
1245
            args.add(order.customer_id);
254
2465
            tx.execute(insert_order.query_with(args)).await.unwrap();
255
13064
            for &product_id in &order.product_ids {
256
11819
                let mut args = PgArguments::default();
257
11819
                args.reserve(2, 0);
258
11819
                args.add(id);
259
11819
                args.add(product_id);
260
23355
                tx.execute(insert_order_product.query_with(args))
261
23355
                    .await
262
11819
                    .unwrap();
263
            }
264
        }
265

            
266
4
        let insert_review = tx
267
8
            .prepare("INSERT INTO commerce_bench.product_reviews (product_id, customer_id, rating, review) VALUES ($1, $2, $3, $4)")
268
8
            .await
269
4
            .unwrap();
270
550
        for review in &operation.initial_data.reviews {
271
550
            let mut args = PgArguments::default();
272
550
            args.reserve(4, 0);
273
550
            args.add(review.product_id);
274
550
            args.add(review.customer_id);
275
550
            args.add(review.rating as u32);
276
550
            args.add(&review.review);
277
1090
            tx.execute(insert_review.query_with(args)).await.unwrap();
278
        }
279
4
        tx.execute(
280
4
            "SELECT setval('commerce_bench.orders_id_seq', COALESCE((SELECT MAX(id)+1 FROM commerce_bench.orders), 1), false)",
281
8
        )
282
8
        .await
283
4
        .unwrap();
284
4

            
285
8
        tx.commit().await.unwrap();
286
4
        // Make sure all ratings show up in the view.
287
8
        conn.execute("REFRESH MATERIALIZED VIEW commerce_bench.product_ratings")
288
8
            .await
289
4
            .unwrap();
290
4
        // This makes a significant difference.
291
8
        conn.execute("ANALYZE").await.unwrap();
292
4
        measurement.finish();
293
4

            
294
4
        OperationResult::Ok
295
8
    }
296
}
297
#[async_trait]
298
impl Operator<CreateCart> for PostgresOperator {
299
389
    async fn operate(
300
389
        &mut self,
301
389
        _operation: &CreateCart,
302
389
        _results: &[OperationResult],
303
389
        measurements: &Measurements,
304
389
    ) -> OperationResult {
305
389
        let measurement = measurements.begin("postgresql", Metric::CreateCart);
306
483
        let mut conn = self.sqlite.acquire().await.unwrap();
307
497
        let mut tx = conn.begin().await.unwrap();
308
389
        let statement = tx
309
389
            .prepare("insert into commerce_bench.carts (customer_id) values (null) returning id")
310
35
            .await
311
389
            .unwrap();
312

            
313
505
        let result = tx.fetch_one(statement.query()).await.unwrap();
314
769
        tx.commit().await.unwrap();
315
389
        let id: i32 = result.get(0);
316
389
        measurement.finish();
317
389

            
318
389
        OperationResult::Cart { id: id as u32 }
319
778
    }
320
}
321
#[async_trait]
322
impl Operator<AddProductToCart> for PostgresOperator {
323
951
    async fn operate(
324
951
        &mut self,
325
951
        operation: &AddProductToCart,
326
951
        results: &[OperationResult],
327
951
        measurements: &Measurements,
328
951
    ) -> OperationResult {
329
951
        let cart = match &results[operation.cart.0] {
330
951
            OperationResult::Cart { id } => *id,
331
            _ => unreachable!("Invalid operation result"),
332
        };
333
951
        let product = match &results[operation.product.0] {
334
951
            OperationResult::Product { id, .. } => *id,
335
            _ => unreachable!("Invalid operation result"),
336
        };
337

            
338
951
        let measurement = measurements.begin("postgresql", Metric::AddProductToCart);
339
1264
        let mut conn = self.sqlite.acquire().await.unwrap();
340
1226
        let mut tx = conn.begin().await.unwrap();
341
951
        let statement = tx
342
951
            .prepare(
343
951
                "insert into commerce_bench.cart_products (cart_id, product_id) values ($1, $2)",
344
951
            )
345
48
            .await
346
951
            .unwrap();
347
951

            
348
951
        let mut args = PgArguments::default();
349
951
        args.reserve(2, 0);
350
951
        args.add(cart);
351
951
        args.add(product);
352
951

            
353
1221
        tx.execute(statement.query_with(args)).await.unwrap();
354
1870
        tx.commit().await.unwrap();
355
951
        measurement.finish();
356
951

            
357
951
        OperationResult::CartProduct { id: product }
358
1902
    }
359
}
360
#[async_trait]
361
impl Operator<FindProduct> for PostgresOperator {
362
1942
    async fn operate(
363
1942
        &mut self,
364
1942
        operation: &FindProduct,
365
1942
        _results: &[OperationResult],
366
1942
        measurements: &Measurements,
367
1942
    ) -> OperationResult {
368
1942
        let measurement = measurements.begin("postgresql", Metric::FindProduct);
369
2546
        let mut conn = self.sqlite.acquire().await.unwrap();
370
1942
        let statement = conn
371
1942
            .prepare(
372
1942
                r#"
373
1942
                SELECT 
374
1942
                    id, 
375
1942
                    name, 
376
1942
                    category_id,
377
1942
                    commerce_bench.product_ratings.total_rating as "total_rating: Option<i32>",
378
1942
                    commerce_bench.product_ratings.ratings as "ratings: Option<i32>"
379
1942
                FROM 
380
1942
                    commerce_bench.products 
381
1942
                LEFT OUTER JOIN commerce_bench.product_categories ON 
382
1942
                    commerce_bench.product_categories.product_id = id 
383
1942
                LEFT OUTER JOIN commerce_bench.product_ratings ON
384
1942
                    commerce_bench.product_ratings.product_id = id
385
1942
                WHERE name = $1
386
1942
                GROUP BY id, name, category_id, commerce_bench.product_ratings.total_rating, commerce_bench.product_ratings.ratings
387
1942
            "#,
388
1942
            )
389
39
            .await
390
1942
            .unwrap();
391
1942

            
392
1942
        let mut args = PgArguments::default();
393
1942
        args.reserve(1, 0);
394
1942
        args.add(&operation.name);
395
1942

            
396
1942
        let mut results = conn.fetch(statement.query_with(args));
397
1942
        let mut id: Option<i32> = None;
398
1942
        let mut name = None;
399
1942
        let mut category_ids = Vec::new();
400
1942
        let mut total_rating: Option<i32> = None;
401
1942
        let mut rating_count: Option<i32> = None;
402
6249
        while let Some(row) = results.next().await {
403
4307
            let row = row.unwrap();
404
4307
            id = Some(row.get(0));
405
4307
            name = Some(row.get(1));
406
4307
            total_rating = row.get(2);
407
4307
            rating_count = row.get(3);
408
4307
            if let Some(category_id) = row.get::<Option<i32>, _>(2) {
409
3929
                category_ids.push(category_id as u32);
410
3929
            }
411
        }
412
1942
        let rating_count = rating_count.unwrap_or_default();
413
1942
        let total_rating = total_rating.unwrap_or_default();
414
1942
        measurement.finish();
415
1942
        OperationResult::Product {
416
1942
            id: id.unwrap() as u32,
417
1942
            product: Product {
418
1942
                name: name.unwrap(),
419
1942
                category_ids,
420
1942
            },
421
1942
            rating: if rating_count > 0 {
422
1283
                Some(total_rating as f32 / rating_count as f32)
423
            } else {
424
659
                None
425
            },
426
        }
427
3884
    }
428
}
429
#[async_trait]
430
impl Operator<LookupProduct> for PostgresOperator {
431
1868
    async fn operate(
432
1868
        &mut self,
433
1868
        operation: &LookupProduct,
434
1868
        _results: &[OperationResult],
435
1868
        measurements: &Measurements,
436
1868
    ) -> OperationResult {
437
1868
        let measurement = measurements.begin("postgresql", Metric::LookupProduct);
438
2504
        let mut conn = self.sqlite.acquire().await.unwrap();
439
1868
        let statement = conn
440
1868
            .prepare(
441
1868
                r#"
442
1868
                    SELECT 
443
1868
                        id, 
444
1868
                        name, 
445
1868
                        category_id,
446
1868
                        commerce_bench.product_ratings.total_rating as "total_rating: Option<i32>",
447
1868
                        commerce_bench.product_ratings.ratings as "ratings: Option<i32>"
448
1868
                    FROM 
449
1868
                        commerce_bench.products 
450
1868
                    LEFT OUTER JOIN commerce_bench.product_categories ON 
451
1868
                        commerce_bench.product_categories.product_id = id 
452
1868
                    LEFT OUTER JOIN commerce_bench.product_ratings ON
453
1868
                        commerce_bench.product_ratings.product_id = id
454
1868
                    WHERE id = $1
455
1868
                    GROUP BY id, name, category_id, commerce_bench.product_ratings.total_rating, commerce_bench.product_ratings.ratings
456
1868
                "#,
457
1868
            )
458
30
            .await
459
1868
            .unwrap();
460
1868

            
461
1868
        let mut args = PgArguments::default();
462
1868
        args.reserve(1, 0);
463
1868
        args.add(operation.id);
464
1868

            
465
1868
        let mut results = conn.fetch(statement.query_with(args));
466
1868
        let mut id: Option<i32> = None;
467
1868
        let mut name = None;
468
1868
        let mut category_ids = Vec::new();
469
1868
        let mut total_rating: Option<i32> = None;
470
1868
        let mut rating_count: Option<i32> = None;
471
6014
        while let Some(row) = results.next().await {
472
4146
            let row = row.unwrap();
473
4146
            id = Some(row.get(0));
474
4146
            name = Some(row.get(1));
475
4146
            total_rating = row.get(2);
476
4146
            rating_count = row.get(3);
477
4146
            if let Some(category_id) = row.get::<Option<i32>, _>(2) {
478
3781
                category_ids.push(category_id as u32);
479
3781
            }
480
        }
481
1868
        let rating_count = rating_count.unwrap_or_default();
482
1868
        let total_rating = total_rating.unwrap_or_default();
483
1868

            
484
1868
        measurement.finish();
485
1868
        OperationResult::Product {
486
1868
            id: id.unwrap() as u32,
487
1868
            product: Product {
488
1868
                name: name.unwrap(),
489
1868
                category_ids,
490
1868
            },
491
1868
            rating: if rating_count > 0 {
492
1210
                Some(total_rating as f32 / rating_count as f32)
493
            } else {
494
658
                None
495
            },
496
        }
497
3736
    }
498
}
499

            
500
#[async_trait]
501
impl Operator<Checkout> for PostgresOperator {
502
99
    async fn operate(
503
99
        &mut self,
504
99
        operation: &Checkout,
505
99
        results: &[OperationResult],
506
99
        measurements: &Measurements,
507
99
    ) -> OperationResult {
508
99
        let cart = match &results[operation.cart.0] {
509
99
            OperationResult::Cart { id } => *id as i32,
510
            _ => unreachable!("Invalid operation result"),
511
        };
512

            
513
99
        let measurement = measurements.begin("postgresql", Metric::Checkout);
514
136
        let mut conn = self.sqlite.acquire().await.unwrap();
515
129
        let mut tx = conn.begin().await.unwrap();
516
        // Create a new order
517
99
        let statement = tx
518
99
            .prepare(r#"INSERT INTO commerce_bench.orders (customer_id) VALUES ($1) RETURNING ID"#)
519
39
            .await
520
99
            .unwrap();
521
99
        let mut args = PgArguments::default();
522
99
        args.reserve(1, 0);
523
99
        args.add(operation.customer_id);
524
115
        let result = tx.fetch_one(statement.query_with(args)).await.unwrap();
525
99
        let order_id: i32 = result.get(0);
526

            
527
99
        let statement = tx
528
99
            .prepare(r#"
529
99
                WITH products_in_cart AS (
530
99
                    DELETE FROM commerce_bench.cart_products WHERE cart_id = $1 RETURNING $2::int as order_id, product_id
531
99
                )
532
99
                INSERT INTO commerce_bench.order_products (order_id, product_id) SELECT * from products_in_cart;"#)
533
38
            .await
534
99
            .unwrap();
535
99
        let mut args = PgArguments::default();
536
99
        args.reserve(2, 0);
537
99
        args.add(cart);
538
99
        args.add(order_id);
539
119
        tx.execute(statement.query_with(args)).await.unwrap();
540

            
541
99
        let statement = tx
542
99
            .prepare(r#"DELETE FROM commerce_bench.carts WHERE id = $1"#)
543
44
            .await
544
99
            .unwrap();
545
99
        let mut args = PgArguments::default();
546
99
        args.reserve(1, 0);
547
99
        args.add(cart);
548
118
        tx.execute(statement.query_with(args)).await.unwrap();
549
99

            
550
99
        measurement.finish();
551
99

            
552
99
        OperationResult::Ok
553
198
    }
554
}
555

            
556
#[async_trait]
557
impl Operator<ReviewProduct> for PostgresOperator {
558
65
    async fn operate(
559
65
        &mut self,
560
65
        operation: &ReviewProduct,
561
65
        results: &[OperationResult],
562
65
        measurements: &Measurements,
563
65
    ) -> OperationResult {
564
65
        let product = match &results[operation.product_id.0] {
565
            OperationResult::Product { id, .. } => *id,
566
65
            OperationResult::CartProduct { id, .. } => *id,
567
            _ => unreachable!("Invalid operation result"),
568
        };
569
65
        let measurement = measurements.begin("postgresql", Metric::RateProduct);
570
104
        let mut conn = self.sqlite.acquire().await.unwrap();
571
93
        let mut tx = conn.begin().await.unwrap();
572
65
        let statement = tx
573
65
            .prepare(
574
65
                r#"INSERT INTO commerce_bench.product_reviews (
575
65
                        product_id, 
576
65
                        customer_id, 
577
65
                        rating, 
578
65
                        review)
579
65
                    VALUES ($1, $2, $3, $4)
580
65
                    ON CONFLICT (customer_id, product_id) DO UPDATE SET rating = $3, review = $4"#,
581
65
            )
582
36
            .await
583
65
            .unwrap();
584
65

            
585
65
        let mut args = PgArguments::default();
586
65
        args.reserve(4, 0);
587
65
        args.add(product);
588
65
        args.add(operation.customer_id);
589
65
        args.add(operation.rating as u32);
590
65
        args.add(&operation.review);
591
65

            
592
78
        tx.execute(statement.query_with(args)).await.unwrap();
593
125
        tx.commit().await.unwrap();
594
65
        // Make this rating show up
595
126
        conn.execute("REFRESH MATERIALIZED VIEW commerce_bench.product_ratings")
596
126
            .await
597
65
            .unwrap();
598
65
        measurement.finish();
599
65

            
600
65
        OperationResult::Ok
601
130
    }
602
}