gS31aSix

11. Требуется создать таблицы в боевом и офисном шардах:
2
3-- События бриллиантов
4CREATE TABLE IF NOT EXISTS `#dbPrefix#_Cluster2`.`DiamondAccountEvent` (
5  `user_id` INT UNSIGNED NOT NULL,
6  `event_id` INT UNSIGNED NOT NULL,
7  `event_type` TINYINT UNSIGNED NOT NULL,
8  `event_payload` TEXT NOT NULL,
9  `context` TEXT NOT NULL,
10  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
11  PRIMARY KEY (`user_id`, `event_id`),
12  KEY (`created_at`)
13) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
14В названии таблицы присутствует плейсхолдер:
15#dbPrefix# - префикс базы данных (на каждой dbs-ке имеется несколько таких баз)
16
17Итого - 500 db-префиксов, в боевом шарде должно быть создано 500 таблиц.
18
19dbs-ки боя:
20
21return array(
22    'dbs48' => array('host' => '10.5.4.92', 'registrationOpened' => true),
23    'dbs46' => array('host' => '10.5.4.130', 'registrationOpened' => true),
24    'dbs51' => array('host' => '10.5.4.83', 'registrationOpened' => true),
25    'dbs44' => array('host' => '10.5.4.136', 'registrationOpened' => true),
26    'dbs43' => array('host' => '10.5.4.134', 'registrationOpened' => true),
27    'dbs52' => array('host' => '10.5.4.85', 'registrationOpened' => true),
28    'dbs53' => array('host' => '10.5.4.88', 'registrationOpened' => true),
29    'dbs40' => array('host' => '10.5.4.132', 'registrationOpened' => true),
30    'dbs31' => array('host' => '10.5.4.22', 'registrationOpened' => true),
31    'dbs38' => array('host' => '10.5.4.53', 'registrationOpened' => true),
32    'dbs42' => array('host' => '10.5.4.135', 'registrationOpened' => true),
33    'dbs32' => array('host' => '10.5.4.48', 'registrationOpened' => true),
34    'dbs50' => array('host' => '10.5.4.80', 'registrationOpened' => true),
35    'dbs37' => array('host' => '10.5.4.51', 'registrationOpened' => true),
36    'dbs33' => array('host' => '10.5.4.56', 'registrationOpened' => true),
37    'dbs35' => array('host' => '10.5.4.41', 'registrationOpened' => true),
38    'dbs34' => array('host' => '10.5.4.49', 'registrationOpened' => true),
39    'dbs14' => array('host' => '10.5.4.28', 'registrationOpened' => true),
40    'dbs13' => array('host' => '10.5.4.86', 'registrationOpened' => true),
41    'dbs8' => array('host' => '10.5.4.25', 'registrationOpened' => true),
42    'dbs6' => array('host' => '10.5.4.84', 'registrationOpened' => true),
43    'dbs5' => array('host' => '10.5.4.137', 'registrationOpened' => true),
44    'dbs2' => array('host' => '10.5.4.90', 'registrationOpened' => true),
45    'dbs12' => array('host' => '10.5.4.89', 'registrationOpened' => true),
46    'dbs10' => array('host' => '10.5.4.27', 'registrationOpened' => true),
47    'dbs4' => array('host' => '10.5.4.24', 'registrationOpened' => true),
48    'dbs41' => array('host' => '10.5.4.133', 'registrationOpened' => true),
49    'dbs17' => array('host' => '10.5.4.82', 'registrationOpened' => true),
50    'dbs23' => array('host' => '10.5.4.23', 'registrationOpened' => true),
51    'dbs24' => array('host' => '10.5.4.34', 'registrationOpened' => true),
52    'dbs25' => array('host' => '10.5.4.33', 'registrationOpened' => true),
53    'dbs28' => array('host' => '10.5.4.36', 'registrationOpened' => true),
54    'dbs21' => array('host' => '10.5.4.29', 'registrationOpened' => true),
55    'dbs16' => array('host' => '10.5.4.93', 'registrationOpened' => true),
56    'dbs29' => array('host' => '10.5.4.37', 'registrationOpened' => true),
57    'dbs9' => array('host' => '10.5.4.26', 'registrationOpened' => true),
58    'dbs30' => array('host' => '10.5.4.44', 'registrationOpened' => true),
59    'dbs18' => array('host' => '10.5.4.71', 'registrationOpened' => true),
60    'dbs26' => array('host' => '10.5.4.32', 'registrationOpened' => true),
61    'dbs22' => array('host' => '10.5.4.30', 'registrationOpened' => true),
62    'dbs15' => array('host' => '10.5.4.21', 'registrationOpened' => true),
63    'dbs7' => array('host' => '10.5.4.87', 'registrationOpened' => true),
64    'dbs3' => array('host' => '10.5.4.62', 'registrationOpened' => true),
65    'dbs45' => array('host' => '10.5.4.77', 'registrationOpened' => true),
66);
67dbs-ки офиса:
68
69return array(
70    'dbs3' => array(
71        'host' => '192.168.2.35:3307',
72        'registrationOpened' => true,
73    ),
74    'dbs4' => array(
75        'host' => '192.168.2.35:3308',
76        'registrationOpened' => true,
77    ),
78    'dbs5' => array(
79        'host' => '192.168.2.35:3309',
80        'registrationOpened' => true,
81    ),
82    'dbs6' => array(
83        'host' => '192.168.2.35:3310',
84        'registrationOpened' => true,
85    ),
86);
87Для офиса - 5 db-префиксов, должно быть создано 5 таблиц в сумме
88
892. Требуется создать базу `Diamonds` на db2 (10.5.4.3) и хоме (192.168.2.13), а также таблицы к ней:
90
91CREATE DATABASE Diamonds CHARSET=utf8 COLLATE='utf8_unicode_ci';
92-- Проекция бриллиантовых событий
93CREATE TABLE `Diamonds`.`DiamondAccount` (
94  `user_id` INT UNSIGNED PRIMARY KEY,
95  `max_event_id` INT UNSIGNED NOT NULL,
96  `diamonds_available` INT UNSIGNED NOT NULL,
97  `diamonds_on_deposit_hold` INT UNSIGNED NOT NULL,
98  `diamonds_on_withdraw_hold` INT UNSIGNED NOT NULL,
99  `coins_withdraw_requests_count` INT UNSIGNED NOT NULL
100) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
101
102-- От этого поля отталкивается крон, обрабатывающий вывод в монеты
103CREATE INDEX `idx_diamonds_coins_requests` ON `Diamonds`.`DiamondAccount` (`coins_withdraw_requests_count`);
104
105-- Запрос в админку на вывод в деньги
106CREATE TABLE `Diamonds`.`DiamondMoneyWithdraw` (
107  `user_id` INT UNSIGNED NOT NULL,
108  `withdraw_request_id` VARCHAR(255) NOT NULL,
109  `money_amount` INT UNSIGNED NOT NULL,
110  `diamond_cost` INT UNSIGNED NOT NULL,
111  `status` TINYINT UNSIGNED NOT NULL,
112  `moderator_id` INT UNSIGNED NULL,
113  `processed_at` TIMESTAMP NULL,
114  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
115  PRIMARY KEY (`user_id`, `withdraw_request_id`)
116) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
117
118-- Задание для крона на зачисление бриллиантов по дарению гифта
119CREATE TABLE `Diamonds`.`GiftDiamondConversionTask` (
120  `id` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
121  `gift_id` INT UNSIGNED NOT NULL,
122  `gift_type` TINYINT UNSIGNED NOT NULL,
123  `anketa_id_from` INT UNSIGNED NOT NULL,
124  `anketa_id_to` INT UNSIGNED NOT NULL,
125  `paid_real_amount` INT UNSIGNED NOT NULL,
126  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
127  `deposit_submitted_at` TIMESTAMP NULL,
128  `deposit_executed_at` TIMESTAMP NULL
129) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
130
131-- От этого поля отталкивается крон, обрабатывающий конвертацию гифтов в бриллианты
132CREATE INDEX `idx_diamonds_deposit_tasks` ON `Diamonds`.`GiftDiamondConversionTask` (`deposit_executed_at`);
133
134-- Задание для крона на вывод в монеты.
135CREATE TABLE `Diamonds`.`DiamondCoinsWithdraw` (
136  `user_id` INT UNSIGNED,
137  `withdraw_request_id` VARCHAR(255) NOT NULL,
138  `operation_id` INT UNSIGNED NULL,
139  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
140  `executed_at` TIMESTAMP NULL,
141  PRIMARY KEY (`user_id`, `withdraw_request_id`)
142) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;