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
12
    async fn new_operator_async(&self) -> Self::Operator {
160
12
        PostgresOperator {
161
12
            sqlite: self.pool.clone(),
162
12
        }
163
12
    }
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
23
        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
46
        for (id, category) in &operation.initial_data.categories {
188
46
            let mut args = PgArguments::default();
189
46
            args.reserve(2, 0);
190
46
            args.add(*id);
191
46
            args.add(&category.name);
192
88
            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
454
        for (&id, product) in &operation.initial_data.products {
204
454
            let mut args = PgArguments::default();
205
454
            args.reserve(2, 0);
206
454
            args.add(id);
207
454
            args.add(&product.name);
208
893
            tx.execute(insert_product.query_with(args)).await.unwrap();
209
1535
            for &category_id in &product.category_ids {
210
1081
                let mut args = PgArguments::default();
211
1081
                args.reserve(2, 0);
212
1081
                args.add(id);
213
1081
                args.add(category_id);
214
2128
                tx.execute(insert_product_category.query_with(args))
215
2128
                    .await
216
1081
                    .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
320
        for (id, customer) in &operation.initial_data.customers {
225
320
            let mut args = PgArguments::default();
226
320
            args.reserve(9, 0);
227
320
            args.add(*id);
228
320
            args.add(&customer.name);
229
320
            args.add(&customer.email);
230
320
            args.add(&customer.address);
231
320
            args.add(&customer.city);
232
320
            args.add(&customer.region);
233
320
            args.add(&customer.country);
234
320
            args.add(&customer.postal_code);
235
320
            args.add(&customer.phone);
236
632
            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
470
        for (&id, order) in &operation.initial_data.orders {
250
470
            let mut args = PgArguments::default();
251
470
            args.reserve(2, 0);
252
470
            args.add(id);
253
470
            args.add(order.customer_id);
254
743
            tx.execute(insert_order.query_with(args)).await.unwrap();
255
4723
            for &product_id in &order.product_ids {
256
4253
                let mut args = PgArguments::default();
257
4253
                args.reserve(2, 0);
258
4253
                args.add(id);
259
4253
                args.add(product_id);
260
7248
                tx.execute(insert_order_product.query_with(args))
261
7248
                    .await
262
4253
                    .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
222
        for review in &operation.initial_data.reviews {
271
222
            let mut args = PgArguments::default();
272
222
            args.reserve(4, 0);
273
222
            args.add(review.product_id);
274
222
            args.add(review.customer_id);
275
222
            args.add(review.rating as u32);
276
222
            args.add(&review.review);
277
431
            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
457
    async fn operate(
300
457
        &mut self,
301
457
        _operation: &CreateCart,
302
457
        _results: &[OperationResult],
303
457
        measurements: &Measurements,
304
457
    ) -> OperationResult {
305
457
        let measurement = measurements.begin("postgresql", Metric::CreateCart);
306
703
        let mut conn = self.sqlite.acquire().await.unwrap();
307
677
        let mut tx = conn.begin().await.unwrap();
308
457
        let statement = tx
309
457
            .prepare("insert into commerce_bench.carts (customer_id) values (null) returning id")
310
28
            .await
311
457
            .unwrap();
312

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

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

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

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

            
353
1764
        tx.execute(statement.query_with(args)).await.unwrap();
354
2209
        tx.commit().await.unwrap();
355
1111
        measurement.finish();
356
1111

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

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

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

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

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

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

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

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

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

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

            
550
115
        measurement.finish();
551
115

            
552
115
        OperationResult::Ok
553
230
    }
554
}
555

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

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

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

            
600
87
        OperationResult::Ok
601
174
    }
602
}