BasicExampleCommand.php 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586
  1. <?php
  2. namespace App\Commands;
  3. use App\AppService;
  4. use YdbPlatform\Ydb\Retry\Backoff;
  5. use YdbPlatform\Ydb\Retry\RetryParams;
  6. use YdbPlatform\Ydb\Session;
  7. use YdbPlatform\Ydb\Ydb;
  8. use YdbPlatform\Ydb\YdbTable;
  9. use Symfony\Component\Console\Helper\Table;
  10. use Symfony\Component\Console\Command\Command;
  11. use Symfony\Component\Console\Input\InputArgument;
  12. use Symfony\Component\Console\Input\InputInterface;
  13. use Symfony\Component\Console\Output\OutputInterface;
  14. class BasicExampleCommand extends Command
  15. {
  16. /**
  17. * @var string
  18. */
  19. protected static $defaultName = 'basic_example_v1';
  20. /**
  21. * @var AppService
  22. */
  23. protected $appService;
  24. /**
  25. * @var YdbPlatform\Ydb\Ydb
  26. */
  27. protected $ydb;
  28. /**
  29. * @var OutputInterface
  30. */
  31. protected $output;
  32. public function __construct()
  33. {
  34. $this->appService = new AppService;
  35. $this->ydb = $this->appService->initYdb();
  36. parent::__construct();
  37. }
  38. protected function configure()
  39. {
  40. $this->setDescription('Run the Basic Example.');
  41. }
  42. /**
  43. * @param InputInterface $input
  44. * @param OutputInterface $output
  45. * @return int
  46. * @throws \Exception
  47. */
  48. protected function execute(InputInterface $input, OutputInterface $output)
  49. {
  50. $this->output = $output;
  51. $this->runExample();
  52. return Command::SUCCESS;
  53. }
  54. /**
  55. * @param mixed $value
  56. */
  57. protected function print($value)
  58. {
  59. if (is_array($value)) {
  60. $this->table($value);
  61. } else {
  62. $this->output->writeln($value);
  63. }
  64. }
  65. /**
  66. * @param array $value
  67. */
  68. protected function table($array)
  69. {
  70. if ($array) {
  71. $table = new Table($this->output);
  72. $table
  73. ->setHeaders(array_keys($array[0]))
  74. ->setRows($array);
  75. $table->render();
  76. }
  77. }
  78. protected function runExample()
  79. {
  80. $this->runQuery('Create tables',
  81. function () {
  82. $this->createTables();
  83. });
  84. $this->runQuery('Describe table',
  85. function () {
  86. $this->describeTable('seasons');
  87. });
  88. $this->runQuery('Fill tables with data',
  89. function () {
  90. $this->fillTablesWithData();
  91. });
  92. $this->runQuery('Select simple transaction',
  93. function () {
  94. $this->selectSimple();
  95. });
  96. $this->runQuery('Upsert simple transaction',
  97. function () {
  98. $this->upsertSimple();
  99. });
  100. $this->runQuery('Bulk upsert',
  101. function () {
  102. $this->bulkUpsert();
  103. });
  104. $this->runQuery('Select prepared',
  105. function () {
  106. $this->selectPrepared(2, 3, 7);
  107. $this->selectPrepared(2, 3, 8);
  108. });
  109. $this->runQuery('Explicit TCL',
  110. function () {
  111. $this->explicitTcl(2, 6, 1);
  112. });
  113. $this->runQuery('Select prepared',
  114. function () {
  115. $this->selectPrepared(2, 6, 1);
  116. });
  117. }
  118. /**
  119. * @param string $header
  120. * @param callable $closure
  121. */
  122. protected function runQuery($header, $closure)
  123. {
  124. $this->print('<info>> ' . $header . ':</info>');
  125. $this->print('');
  126. $closure();
  127. $this->print('');
  128. }
  129. protected function createTables()
  130. {
  131. $this->ydb->table()->retrySession(function (Session $session) {
  132. $session->createTable(
  133. 'series',
  134. YdbTable::make()
  135. ->addColumn('series_id', 'UINT64')
  136. ->addColumn('title', 'UTF8')
  137. ->addColumn('series_info', 'UTF8')
  138. ->addColumn('release_date', 'UINT64')
  139. ->primaryKey('series_id')
  140. );
  141. }, true);
  142. $this->print('Table `series` has been created.');
  143. $this->ydb->table()->retrySession(function (Session $session) {
  144. $session->createTable(
  145. 'seasons',
  146. YdbTable::make()
  147. ->addColumn('series_id', 'UINT64')
  148. ->addColumn('season_id', 'UINT64')
  149. ->addColumn('title', 'UTF8')
  150. ->addColumn('first_aired', 'UINT64')
  151. ->addColumn('last_aired', 'UINT64')
  152. ->primaryKey(['series_id', 'season_id'])
  153. );
  154. }, true);
  155. $this->print('Table `seasons` has been created.');
  156. $this->ydb->table()->retrySession(function (Session $session) {
  157. $session->createTable(
  158. 'episodes',
  159. YdbTable::make()
  160. ->addColumn('series_id', 'UINT64')
  161. ->addColumn('season_id', 'UINT64')
  162. ->addColumn('episode_id', 'UINT64')
  163. ->addColumn('title', 'UTF8')
  164. ->addColumn('air_date', 'UINT64')
  165. ->primaryKey(['series_id', 'season_id', 'episode_id'])
  166. );
  167. }, true);
  168. $this->print('Table `episodes` has been created.');
  169. }
  170. /**
  171. * @param string $table
  172. */
  173. protected function describeTable($table)
  174. {
  175. $data = $this->ydb->table()->retrySession(function (Session $session) use ($table) {
  176. return $session->describeTable($table);
  177. }, true);
  178. $columns = [];
  179. foreach ($data['columns'] as $column) {
  180. if (isset($column['type']['optionalType']['item']['typeId'])) {
  181. $columns[] = [
  182. 'Name' => $column['name'],
  183. 'Type' => $column['type']['optionalType']['item']['typeId'],
  184. ];
  185. }
  186. }
  187. $this->print('Table `' . $table . '`');
  188. $this->print($columns);
  189. $this->print('');
  190. $this->print('Primary key: ' . implode(', ', (array)$data['primaryKey']));
  191. }
  192. protected function fillTablesWithData()
  193. {
  194. $params = new RetryParams(4000,null,new Backoff(10,1000));
  195. $this->ydb->table()->retryTransaction(function (Session $session) {
  196. $prepared_query = $session->prepare($this->getFillDataQuery());
  197. $prepared_query->execute([
  198. 'seriesData' => $this->getSeriesData(),
  199. 'seasonsData' => $this->getSeasonsData(),
  200. 'episodesData' => $this->getEpisodesData(),
  201. ]);
  202. }, false, $params);
  203. $this->print('Finished.');
  204. }
  205. protected function selectSimple()
  206. {
  207. $params = new RetryParams(4000,new Backoff(3,20));
  208. $result = $this->ydb->table()->retryTransaction(function (Session $session) {
  209. return $session->query('
  210. $format = DateTime::Format("%Y-%m-%d");
  211. SELECT
  212. series_id,
  213. title,
  214. $format(DateTime::FromSeconds(CAST(release_date AS Uint32))) AS release_date
  215. FROM series
  216. WHERE series_id = 1;');
  217. }, true, $params);
  218. $this->print($result->rows());
  219. }
  220. protected function upsertSimple()
  221. {
  222. $this->ydb->table()->retryTransaction(function (Session $session) {
  223. return $session->query('
  224. UPSERT INTO episodes (series_id, season_id, episode_id, title)
  225. VALUES (2, 6, 1, "TBD");');
  226. }, true);
  227. $this->print('Finished.');
  228. }
  229. protected function bulkUpsert()
  230. {
  231. $this->ydb->retry(function (Ydb $ydb) {
  232. $table = $ydb->table();
  233. $table->bulkUpsert(
  234. 'episodes',
  235. $this->getEpisodesDataForBulkUpsert(),
  236. [
  237. 'series_id' => 'Uint64',
  238. 'season_id' => 'Uint64',
  239. 'episode_id' => 'Uint64',
  240. 'title' => 'Utf8',
  241. 'air_date' => 'Uint64',
  242. ]
  243. );
  244. }, true);
  245. $this->print('Finished.');
  246. }
  247. /**
  248. * @param int $series_id
  249. * @param int $season_id
  250. * @param int $episode_id
  251. */
  252. protected function selectPrepared($series_id, $season_id, $episode_id)
  253. {
  254. $result = $this->ydb->table()->retryTransaction(function (Session $session) use ($series_id, $season_id, $episode_id) {
  255. $prepared_query = $session->prepare('
  256. DECLARE $series_id AS Uint64;
  257. DECLARE $season_id AS Uint64;
  258. DECLARE $episode_id AS Uint64;
  259. $format = DateTime::Format("%Y-%m-%d");
  260. SELECT
  261. title AS `Episode title`,
  262. $format(DateTime::FromSeconds(CAST(air_date AS Uint32))) AS `Air date`
  263. FROM episodes
  264. WHERE series_id = $series_id AND season_id = $season_id AND episode_id = $episode_id;');
  265. return $prepared_query->execute(compact(
  266. 'series_id',
  267. 'season_id',
  268. 'episode_id'
  269. ));
  270. },true);
  271. $this->print($result->rows());
  272. }
  273. /**
  274. * @param int $series_id
  275. * @param int $season_id
  276. * @param int $episode_id
  277. */
  278. protected function explicitTcl($series_id, $season_id, $episode_id)
  279. {
  280. $this->ydb->table()->retryTransaction(function (Session $session) use ($series_id, $season_id, $episode_id) {
  281. $prepared_query = $session->prepare('
  282. DECLARE $today AS Uint64;
  283. DECLARE $series_id AS Uint64;
  284. DECLARE $season_id AS Uint64;
  285. DECLARE $episode_id AS Uint64;
  286. UPDATE episodes
  287. SET air_date = $today
  288. WHERE series_id = $series_id AND season_id = $season_id AND episode_id = $episode_id;');
  289. $today = strtotime('today');
  290. $prepared_query->execute(compact(
  291. 'series_id',
  292. 'season_id',
  293. 'episode_id',
  294. 'today'
  295. ));
  296. });
  297. $this->print('Finished.');
  298. }
  299. /**
  300. * @param int $series_id
  301. * @param string $title
  302. * @param int $release_date
  303. * @param string $series_info
  304. */
  305. protected function newSeries($series_id, $title, $release_date, $series_info)
  306. {
  307. $release_date = strtotime($release_date);
  308. return compact('series_id', 'title', 'release_date', 'series_info');
  309. }
  310. /**
  311. * @return array
  312. */
  313. protected function getSeriesData()
  314. {
  315. return [
  316. $this->newSeries(
  317. 1,
  318. 'IT Crowd',
  319. '2006-02-03',
  320. 'The IT Crowd is a British sitcom produced by Channel 4, written by Graham Linehan, produced by Ash Atalla and starring Chris O\'Dowd, Richard Ayoade, Katherine Parkinson, and Matt Berry.',
  321. ),
  322. $this->newSeries(
  323. 2,
  324. 'Silicon Valley',
  325. '2014-04-06',
  326. 'Silicon Valley is an American comedy television series created by Mike Judge, John Altschuler and Dave Krinsky. The series focuses on five young men who founded a startup company in Silicon Valley.',
  327. ),
  328. ];
  329. }
  330. /**
  331. * @param int $series_id
  332. * @param int $season_id
  333. * @param string $title
  334. * @param int $first_aired
  335. * @param int $last_aired
  336. */
  337. protected function newSeason($series_id, $season_id, $title, $first_aired, $last_aired)
  338. {
  339. $first_aired = strtotime($first_aired);
  340. $last_aired = strtotime($last_aired);
  341. return compact('series_id', 'season_id', 'title', 'first_aired', 'last_aired');
  342. }
  343. /**
  344. * @return array
  345. */
  346. protected function getSeasonsData()
  347. {
  348. return [
  349. $this->newSeason(1, 1, 'Season 1', '2006-02-03', '2006-03-03'),
  350. $this->newSeason(1, 2, 'Season 2', '2007-08-24', '2007-09-28'),
  351. $this->newSeason(1, 3, 'Season 3', '2008-11-21', '2008-12-26'),
  352. $this->newSeason(1, 4, 'Season 4', '2010-06-25', '2010-07-30'),
  353. $this->newSeason(2, 1, 'Season 1', '2014-04-06', '2014-06-01'),
  354. $this->newSeason(2, 2, 'Season 2', '2015-04-12', '2015-06-14'),
  355. $this->newSeason(2, 3, 'Season 3', '2016-04-24', '2016-06-26'),
  356. $this->newSeason(2, 4, 'Season 4', '2017-04-23', '2017-06-25'),
  357. $this->newSeason(2, 5, 'Season 5', '2018-03-25', '2018-05-13'),
  358. ];
  359. }
  360. /**
  361. * @param int $series_id
  362. * @param int $season_id
  363. * @param int $episode_id
  364. * @param string $title
  365. * @param int $air_date
  366. */
  367. protected function newEpisode($series_id, $season_id, $episode_id, $title, $air_date)
  368. {
  369. $air_date = strtotime($air_date);
  370. return compact('series_id', 'season_id', 'episode_id', 'title', 'air_date');
  371. }
  372. /**
  373. * @return array
  374. */
  375. protected function getEpisodesData()
  376. {
  377. return [
  378. $this->newEpisode(1, 1, 1, 'Yesterday\'s Jam', '2006-02-03'),
  379. $this->newEpisode(1, 1, 2, 'Calamity Jen', '2006-02-03'),
  380. $this->newEpisode(1, 1, 3, 'Fifty-Fifty', '2006-02-10'),
  381. $this->newEpisode(1, 1, 4, 'The Red Door', '2006-02-17'),
  382. $this->newEpisode(1, 1, 5, 'The Haunting of Bill Crouse', '2006-02-24'),
  383. $this->newEpisode(1, 1, 6, 'Aunt Irma Visits', '2006-03-03'),
  384. $this->newEpisode(1, 2, 1, 'The Work Outing', '2006-08-24'),
  385. $this->newEpisode(1, 2, 2, 'Return of the Golden Child', '2007-08-31'),
  386. $this->newEpisode(1, 2, 3, 'Moss and the German', '2007-09-07'),
  387. $this->newEpisode(1, 2, 4, 'The Dinner Party', '2007-09-14'),
  388. $this->newEpisode(1, 2, 5, 'Smoke and Mirrors', '2007-09-21'),
  389. $this->newEpisode(1, 2, 6, 'Men Without Women', '2007-09-28'),
  390. $this->newEpisode(1, 3, 1, 'From Hell', '2008-11-21'),
  391. $this->newEpisode(1, 3, 2, 'Are We Not Men?', '2008-11-28'),
  392. $this->newEpisode(1, 3, 3, 'Tramps Like Us', '2008-12-05'),
  393. $this->newEpisode(1, 3, 4, 'The Speech', '2008-12-12'),
  394. $this->newEpisode(1, 3, 5, 'Friendface', '2008-12-19'),
  395. $this->newEpisode(1, 3, 6, 'Calendar Geeks', '2008-12-26'),
  396. $this->newEpisode(1, 4, 1, 'Jen The Fredo', '2010-06-25'),
  397. $this->newEpisode(1, 4, 2, 'The Final Countdown', '2010-07-02'),
  398. $this->newEpisode(1, 4, 3, 'Something Happened', '2010-07-09'),
  399. $this->newEpisode(1, 4, 4, 'Italian For Beginners', '2010-07-16'),
  400. $this->newEpisode(1, 4, 5, 'Bad Boys', '2010-07-23'),
  401. $this->newEpisode(1, 4, 6, 'Reynholm vs Reynholm', '2010-07-30'),
  402. ];
  403. }
  404. /**
  405. * @return array
  406. */
  407. protected function getEpisodesDataForBulkUpsert()
  408. {
  409. return [
  410. $this->newEpisode(2, 1, 1, 'Minimum Viable Product', '2014-04-06'),
  411. $this->newEpisode(2, 1, 2, 'The Cap Table', '2014-04-13'),
  412. $this->newEpisode(2, 1, 3, 'Articles of Incorporation', '2014-04-20'),
  413. $this->newEpisode(2, 1, 4, 'Fiduciary Duties', '2014-04-27'),
  414. $this->newEpisode(2, 1, 5, 'Signaling Risk', '2014-05-04'),
  415. $this->newEpisode(2, 1, 6, 'Third Party Insourcing', '2014-05-11'),
  416. $this->newEpisode(2, 1, 7, 'Proof of Concept', '2014-05-18'),
  417. $this->newEpisode(2, 1, 8, 'Optimal Tip-to-Tip Efficiency', '2014-06-01'),
  418. $this->newEpisode(2, 2, 1, 'Sand Hill Shuffle', '2015-04-12'),
  419. $this->newEpisode(2, 2, 2, 'Runaway Devaluation', '2015-04-19'),
  420. $this->newEpisode(2, 2, 3, 'Bad Money', '2015-04-26'),
  421. $this->newEpisode(2, 2, 4, 'The Lady', '2015-05-03'),
  422. $this->newEpisode(2, 2, 5, 'Server Space', '2015-05-10'),
  423. $this->newEpisode(2, 2, 6, 'Homicide', '2015-05-17'),
  424. $this->newEpisode(2, 2, 7, 'Adult Content', '2015-05-24'),
  425. $this->newEpisode(2, 2, 8, 'White Hat/Black Hat', '2015-05-31'),
  426. $this->newEpisode(2, 2, 9, 'Binding Arbitration', '2015-06-07'),
  427. $this->newEpisode(2, 2, 10, 'Two Days of the Condor', '2015-06-14'),
  428. $this->newEpisode(2, 3, 1, 'Founder Friendly', '2016-04-24'),
  429. $this->newEpisode(2, 3, 2, 'Two in the Box', '2016-05-01'),
  430. $this->newEpisode(2, 3, 3, 'Meinertzhagen\'s Haversack', '2016-05-08'),
  431. $this->newEpisode(2, 3, 4, 'Maleant Data Systems Solutions', '2016-05-15'),
  432. $this->newEpisode(2, 3, 5, 'The Empty Chair', '2016-05-22'),
  433. $this->newEpisode(2, 3, 6, 'Bachmanity Insanity', '2016-05-29'),
  434. $this->newEpisode(2, 3, 7, 'To Build a Better Beta', '2016-06-05'),
  435. $this->newEpisode(2, 3, 8, 'Bachman\'s Earnings Over-Ride', '2016-06-12'),
  436. $this->newEpisode(2, 3, 9, 'Daily Active Users', '2016-06-19'),
  437. $this->newEpisode(2, 3, 10, 'The Uptick', '2016-06-26'),
  438. $this->newEpisode(2, 4, 1, 'Success Failure', '2017-04-23'),
  439. $this->newEpisode(2, 4, 2, 'Terms of Service', '2017-04-30'),
  440. $this->newEpisode(2, 4, 3, 'Intellectual Property', '2017-05-07'),
  441. $this->newEpisode(2, 4, 4, 'Teambuilding Exercise', '2017-05-14'),
  442. $this->newEpisode(2, 4, 5, 'The Blood Boy', '2017-05-21'),
  443. $this->newEpisode(2, 4, 6, 'Customer Service', '2017-05-28'),
  444. $this->newEpisode(2, 4, 7, 'The Patent Troll', '2017-06-04'),
  445. $this->newEpisode(2, 4, 8, 'The Keenan Vortex', '2017-06-11'),
  446. $this->newEpisode(2, 4, 9, 'Hooli-Con', '2017-06-18'),
  447. $this->newEpisode(2, 4, 10, 'Server Error', '2017-06-25'),
  448. $this->newEpisode(2, 5, 1, 'Grow Fast or Die Slow', '2018-03-25'),
  449. $this->newEpisode(2, 5, 2, 'Reorientation', '2018-04-01'),
  450. $this->newEpisode(2, 5, 3, 'Chief Operating Officer', '2018-04-08'),
  451. $this->newEpisode(2, 5, 4, 'Tech Evangelist', '2018-04-15'),
  452. $this->newEpisode(2, 5, 5, 'Facial Recognition', '2018-04-22'),
  453. $this->newEpisode(2, 5, 6, 'Artificial Emotional Intelligence', '2018-04-29'),
  454. $this->newEpisode(2, 5, 7, 'Initial Coin Offering', '2018-05-06'),
  455. $this->newEpisode(2, 5, 8, 'Fifty-One Percent', '2018-05-13'),
  456. ];
  457. }
  458. /**
  459. * @return string
  460. */
  461. protected function getFillDataQuery()
  462. {
  463. return <<<'EOT'
  464. DECLARE $seriesData AS List<Struct<
  465. series_id: Uint64,
  466. title: Utf8,
  467. series_info: Utf8,
  468. release_date: Uint64>>;
  469. DECLARE $seasonsData AS List<Struct<
  470. series_id: Uint64,
  471. season_id: Uint64,
  472. title: Utf8,
  473. first_aired: Uint64,
  474. last_aired: Uint64>>;
  475. DECLARE $episodesData AS List<Struct<
  476. series_id: Uint64,
  477. season_id: Uint64,
  478. episode_id: Uint64,
  479. title: Utf8,
  480. air_date: Uint64>>;
  481. REPLACE INTO series
  482. SELECT
  483. series_id,
  484. title,
  485. series_info,
  486. release_date
  487. FROM AS_TABLE($seriesData);
  488. REPLACE INTO seasons
  489. SELECT
  490. series_id,
  491. season_id,
  492. title,
  493. first_aired,
  494. last_aired
  495. FROM AS_TABLE($seasonsData);
  496. REPLACE INTO episodes
  497. SELECT
  498. series_id,
  499. season_id,
  500. episode_id,
  501. title,
  502. air_date
  503. FROM AS_TABLE($episodesData);
  504. EOT;
  505. }
  506. }