123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586 |
- <?php
- namespace App\Commands;
- use App\AppService;
- use YdbPlatform\Ydb\Retry\Backoff;
- use YdbPlatform\Ydb\Retry\RetryParams;
- use YdbPlatform\Ydb\Session;
- use YdbPlatform\Ydb\Ydb;
- use YdbPlatform\Ydb\YdbTable;
- use Symfony\Component\Console\Helper\Table;
- use Symfony\Component\Console\Command\Command;
- use Symfony\Component\Console\Input\InputArgument;
- use Symfony\Component\Console\Input\InputInterface;
- use Symfony\Component\Console\Output\OutputInterface;
- class BasicExampleCommand extends Command
- {
- /**
- * @var string
- */
- protected static $defaultName = 'basic_example_v1';
- /**
- * @var AppService
- */
- protected $appService;
- /**
- * @var YdbPlatform\Ydb\Ydb
- */
- protected $ydb;
- /**
- * @var OutputInterface
- */
- protected $output;
- public function __construct()
- {
- $this->appService = new AppService;
- $this->ydb = $this->appService->initYdb();
- parent::__construct();
- }
- protected function configure()
- {
- $this->setDescription('Run the Basic Example.');
- }
- /**
- * @param InputInterface $input
- * @param OutputInterface $output
- * @return int
- * @throws \Exception
- */
- protected function execute(InputInterface $input, OutputInterface $output)
- {
- $this->output = $output;
- $this->runExample();
- return Command::SUCCESS;
- }
- /**
- * @param mixed $value
- */
- protected function print($value)
- {
- if (is_array($value)) {
- $this->table($value);
- } else {
- $this->output->writeln($value);
- }
- }
- /**
- * @param array $value
- */
- protected function table($array)
- {
- if ($array) {
- $table = new Table($this->output);
- $table
- ->setHeaders(array_keys($array[0]))
- ->setRows($array);
- $table->render();
- }
- }
- protected function runExample()
- {
- $this->runQuery('Create tables',
- function () {
- $this->createTables();
- });
- $this->runQuery('Describe table',
- function () {
- $this->describeTable('seasons');
- });
- $this->runQuery('Fill tables with data',
- function () {
- $this->fillTablesWithData();
- });
- $this->runQuery('Select simple transaction',
- function () {
- $this->selectSimple();
- });
- $this->runQuery('Upsert simple transaction',
- function () {
- $this->upsertSimple();
- });
- $this->runQuery('Bulk upsert',
- function () {
- $this->bulkUpsert();
- });
- $this->runQuery('Select prepared',
- function () {
- $this->selectPrepared(2, 3, 7);
- $this->selectPrepared(2, 3, 8);
- });
- $this->runQuery('Explicit TCL',
- function () {
- $this->explicitTcl(2, 6, 1);
- });
- $this->runQuery('Select prepared',
- function () {
- $this->selectPrepared(2, 6, 1);
- });
- }
- /**
- * @param string $header
- * @param callable $closure
- */
- protected function runQuery($header, $closure)
- {
- $this->print('<info>> ' . $header . ':</info>');
- $this->print('');
- $closure();
- $this->print('');
- }
- protected function createTables()
- {
- $this->ydb->table()->retrySession(function (Session $session) {
- $session->createTable(
- 'series',
- YdbTable::make()
- ->addColumn('series_id', 'UINT64')
- ->addColumn('title', 'UTF8')
- ->addColumn('series_info', 'UTF8')
- ->addColumn('release_date', 'UINT64')
- ->primaryKey('series_id')
- );
- }, true);
- $this->print('Table `series` has been created.');
- $this->ydb->table()->retrySession(function (Session $session) {
- $session->createTable(
- 'seasons',
- YdbTable::make()
- ->addColumn('series_id', 'UINT64')
- ->addColumn('season_id', 'UINT64')
- ->addColumn('title', 'UTF8')
- ->addColumn('first_aired', 'UINT64')
- ->addColumn('last_aired', 'UINT64')
- ->primaryKey(['series_id', 'season_id'])
- );
- }, true);
- $this->print('Table `seasons` has been created.');
- $this->ydb->table()->retrySession(function (Session $session) {
- $session->createTable(
- 'episodes',
- YdbTable::make()
- ->addColumn('series_id', 'UINT64')
- ->addColumn('season_id', 'UINT64')
- ->addColumn('episode_id', 'UINT64')
- ->addColumn('title', 'UTF8')
- ->addColumn('air_date', 'UINT64')
- ->primaryKey(['series_id', 'season_id', 'episode_id'])
- );
- }, true);
- $this->print('Table `episodes` has been created.');
- }
- /**
- * @param string $table
- */
- protected function describeTable($table)
- {
- $data = $this->ydb->table()->retrySession(function (Session $session) use ($table) {
- return $session->describeTable($table);
- }, true);
- $columns = [];
- foreach ($data['columns'] as $column) {
- if (isset($column['type']['optionalType']['item']['typeId'])) {
- $columns[] = [
- 'Name' => $column['name'],
- 'Type' => $column['type']['optionalType']['item']['typeId'],
- ];
- }
- }
- $this->print('Table `' . $table . '`');
- $this->print($columns);
- $this->print('');
- $this->print('Primary key: ' . implode(', ', (array)$data['primaryKey']));
- }
- protected function fillTablesWithData()
- {
- $params = new RetryParams(4000,null,new Backoff(10,1000));
- $this->ydb->table()->retryTransaction(function (Session $session) {
- $prepared_query = $session->prepare($this->getFillDataQuery());
- $prepared_query->execute([
- 'seriesData' => $this->getSeriesData(),
- 'seasonsData' => $this->getSeasonsData(),
- 'episodesData' => $this->getEpisodesData(),
- ]);
- }, false, $params);
- $this->print('Finished.');
- }
- protected function selectSimple()
- {
- $params = new RetryParams(4000,new Backoff(3,20));
- $result = $this->ydb->table()->retryTransaction(function (Session $session) {
- return $session->query('
- $format = DateTime::Format("%Y-%m-%d");
- SELECT
- series_id,
- title,
- $format(DateTime::FromSeconds(CAST(release_date AS Uint32))) AS release_date
- FROM series
- WHERE series_id = 1;');
- }, true, $params);
- $this->print($result->rows());
- }
- protected function upsertSimple()
- {
- $this->ydb->table()->retryTransaction(function (Session $session) {
- return $session->query('
- UPSERT INTO episodes (series_id, season_id, episode_id, title)
- VALUES (2, 6, 1, "TBD");');
- }, true);
- $this->print('Finished.');
- }
- protected function bulkUpsert()
- {
- $this->ydb->retry(function (Ydb $ydb) {
- $table = $ydb->table();
- $table->bulkUpsert(
- 'episodes',
- $this->getEpisodesDataForBulkUpsert(),
- [
- 'series_id' => 'Uint64',
- 'season_id' => 'Uint64',
- 'episode_id' => 'Uint64',
- 'title' => 'Utf8',
- 'air_date' => 'Uint64',
- ]
- );
- }, true);
- $this->print('Finished.');
- }
- /**
- * @param int $series_id
- * @param int $season_id
- * @param int $episode_id
- */
- protected function selectPrepared($series_id, $season_id, $episode_id)
- {
- $result = $this->ydb->table()->retryTransaction(function (Session $session) use ($series_id, $season_id, $episode_id) {
- $prepared_query = $session->prepare('
- DECLARE $series_id AS Uint64;
- DECLARE $season_id AS Uint64;
- DECLARE $episode_id AS Uint64;
- $format = DateTime::Format("%Y-%m-%d");
- SELECT
- title AS `Episode title`,
- $format(DateTime::FromSeconds(CAST(air_date AS Uint32))) AS `Air date`
- FROM episodes
- WHERE series_id = $series_id AND season_id = $season_id AND episode_id = $episode_id;');
- return $prepared_query->execute(compact(
- 'series_id',
- 'season_id',
- 'episode_id'
- ));
- },true);
- $this->print($result->rows());
- }
- /**
- * @param int $series_id
- * @param int $season_id
- * @param int $episode_id
- */
- protected function explicitTcl($series_id, $season_id, $episode_id)
- {
- $this->ydb->table()->retryTransaction(function (Session $session) use ($series_id, $season_id, $episode_id) {
- $prepared_query = $session->prepare('
- DECLARE $today AS Uint64;
- DECLARE $series_id AS Uint64;
- DECLARE $season_id AS Uint64;
- DECLARE $episode_id AS Uint64;
- UPDATE episodes
- SET air_date = $today
- WHERE series_id = $series_id AND season_id = $season_id AND episode_id = $episode_id;');
- $today = strtotime('today');
- $prepared_query->execute(compact(
- 'series_id',
- 'season_id',
- 'episode_id',
- 'today'
- ));
- });
- $this->print('Finished.');
- }
- /**
- * @param int $series_id
- * @param string $title
- * @param int $release_date
- * @param string $series_info
- */
- protected function newSeries($series_id, $title, $release_date, $series_info)
- {
- $release_date = strtotime($release_date);
- return compact('series_id', 'title', 'release_date', 'series_info');
- }
- /**
- * @return array
- */
- protected function getSeriesData()
- {
- return [
- $this->newSeries(
- 1,
- 'IT Crowd',
- '2006-02-03',
- '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.',
- ),
- $this->newSeries(
- 2,
- 'Silicon Valley',
- '2014-04-06',
- '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.',
- ),
- ];
- }
- /**
- * @param int $series_id
- * @param int $season_id
- * @param string $title
- * @param int $first_aired
- * @param int $last_aired
- */
- protected function newSeason($series_id, $season_id, $title, $first_aired, $last_aired)
- {
- $first_aired = strtotime($first_aired);
- $last_aired = strtotime($last_aired);
- return compact('series_id', 'season_id', 'title', 'first_aired', 'last_aired');
- }
- /**
- * @return array
- */
- protected function getSeasonsData()
- {
- return [
- $this->newSeason(1, 1, 'Season 1', '2006-02-03', '2006-03-03'),
- $this->newSeason(1, 2, 'Season 2', '2007-08-24', '2007-09-28'),
- $this->newSeason(1, 3, 'Season 3', '2008-11-21', '2008-12-26'),
- $this->newSeason(1, 4, 'Season 4', '2010-06-25', '2010-07-30'),
- $this->newSeason(2, 1, 'Season 1', '2014-04-06', '2014-06-01'),
- $this->newSeason(2, 2, 'Season 2', '2015-04-12', '2015-06-14'),
- $this->newSeason(2, 3, 'Season 3', '2016-04-24', '2016-06-26'),
- $this->newSeason(2, 4, 'Season 4', '2017-04-23', '2017-06-25'),
- $this->newSeason(2, 5, 'Season 5', '2018-03-25', '2018-05-13'),
- ];
- }
- /**
- * @param int $series_id
- * @param int $season_id
- * @param int $episode_id
- * @param string $title
- * @param int $air_date
- */
- protected function newEpisode($series_id, $season_id, $episode_id, $title, $air_date)
- {
- $air_date = strtotime($air_date);
- return compact('series_id', 'season_id', 'episode_id', 'title', 'air_date');
- }
- /**
- * @return array
- */
- protected function getEpisodesData()
- {
- return [
- $this->newEpisode(1, 1, 1, 'Yesterday\'s Jam', '2006-02-03'),
- $this->newEpisode(1, 1, 2, 'Calamity Jen', '2006-02-03'),
- $this->newEpisode(1, 1, 3, 'Fifty-Fifty', '2006-02-10'),
- $this->newEpisode(1, 1, 4, 'The Red Door', '2006-02-17'),
- $this->newEpisode(1, 1, 5, 'The Haunting of Bill Crouse', '2006-02-24'),
- $this->newEpisode(1, 1, 6, 'Aunt Irma Visits', '2006-03-03'),
- $this->newEpisode(1, 2, 1, 'The Work Outing', '2006-08-24'),
- $this->newEpisode(1, 2, 2, 'Return of the Golden Child', '2007-08-31'),
- $this->newEpisode(1, 2, 3, 'Moss and the German', '2007-09-07'),
- $this->newEpisode(1, 2, 4, 'The Dinner Party', '2007-09-14'),
- $this->newEpisode(1, 2, 5, 'Smoke and Mirrors', '2007-09-21'),
- $this->newEpisode(1, 2, 6, 'Men Without Women', '2007-09-28'),
- $this->newEpisode(1, 3, 1, 'From Hell', '2008-11-21'),
- $this->newEpisode(1, 3, 2, 'Are We Not Men?', '2008-11-28'),
- $this->newEpisode(1, 3, 3, 'Tramps Like Us', '2008-12-05'),
- $this->newEpisode(1, 3, 4, 'The Speech', '2008-12-12'),
- $this->newEpisode(1, 3, 5, 'Friendface', '2008-12-19'),
- $this->newEpisode(1, 3, 6, 'Calendar Geeks', '2008-12-26'),
- $this->newEpisode(1, 4, 1, 'Jen The Fredo', '2010-06-25'),
- $this->newEpisode(1, 4, 2, 'The Final Countdown', '2010-07-02'),
- $this->newEpisode(1, 4, 3, 'Something Happened', '2010-07-09'),
- $this->newEpisode(1, 4, 4, 'Italian For Beginners', '2010-07-16'),
- $this->newEpisode(1, 4, 5, 'Bad Boys', '2010-07-23'),
- $this->newEpisode(1, 4, 6, 'Reynholm vs Reynholm', '2010-07-30'),
- ];
- }
- /**
- * @return array
- */
- protected function getEpisodesDataForBulkUpsert()
- {
- return [
- $this->newEpisode(2, 1, 1, 'Minimum Viable Product', '2014-04-06'),
- $this->newEpisode(2, 1, 2, 'The Cap Table', '2014-04-13'),
- $this->newEpisode(2, 1, 3, 'Articles of Incorporation', '2014-04-20'),
- $this->newEpisode(2, 1, 4, 'Fiduciary Duties', '2014-04-27'),
- $this->newEpisode(2, 1, 5, 'Signaling Risk', '2014-05-04'),
- $this->newEpisode(2, 1, 6, 'Third Party Insourcing', '2014-05-11'),
- $this->newEpisode(2, 1, 7, 'Proof of Concept', '2014-05-18'),
- $this->newEpisode(2, 1, 8, 'Optimal Tip-to-Tip Efficiency', '2014-06-01'),
- $this->newEpisode(2, 2, 1, 'Sand Hill Shuffle', '2015-04-12'),
- $this->newEpisode(2, 2, 2, 'Runaway Devaluation', '2015-04-19'),
- $this->newEpisode(2, 2, 3, 'Bad Money', '2015-04-26'),
- $this->newEpisode(2, 2, 4, 'The Lady', '2015-05-03'),
- $this->newEpisode(2, 2, 5, 'Server Space', '2015-05-10'),
- $this->newEpisode(2, 2, 6, 'Homicide', '2015-05-17'),
- $this->newEpisode(2, 2, 7, 'Adult Content', '2015-05-24'),
- $this->newEpisode(2, 2, 8, 'White Hat/Black Hat', '2015-05-31'),
- $this->newEpisode(2, 2, 9, 'Binding Arbitration', '2015-06-07'),
- $this->newEpisode(2, 2, 10, 'Two Days of the Condor', '2015-06-14'),
- $this->newEpisode(2, 3, 1, 'Founder Friendly', '2016-04-24'),
- $this->newEpisode(2, 3, 2, 'Two in the Box', '2016-05-01'),
- $this->newEpisode(2, 3, 3, 'Meinertzhagen\'s Haversack', '2016-05-08'),
- $this->newEpisode(2, 3, 4, 'Maleant Data Systems Solutions', '2016-05-15'),
- $this->newEpisode(2, 3, 5, 'The Empty Chair', '2016-05-22'),
- $this->newEpisode(2, 3, 6, 'Bachmanity Insanity', '2016-05-29'),
- $this->newEpisode(2, 3, 7, 'To Build a Better Beta', '2016-06-05'),
- $this->newEpisode(2, 3, 8, 'Bachman\'s Earnings Over-Ride', '2016-06-12'),
- $this->newEpisode(2, 3, 9, 'Daily Active Users', '2016-06-19'),
- $this->newEpisode(2, 3, 10, 'The Uptick', '2016-06-26'),
- $this->newEpisode(2, 4, 1, 'Success Failure', '2017-04-23'),
- $this->newEpisode(2, 4, 2, 'Terms of Service', '2017-04-30'),
- $this->newEpisode(2, 4, 3, 'Intellectual Property', '2017-05-07'),
- $this->newEpisode(2, 4, 4, 'Teambuilding Exercise', '2017-05-14'),
- $this->newEpisode(2, 4, 5, 'The Blood Boy', '2017-05-21'),
- $this->newEpisode(2, 4, 6, 'Customer Service', '2017-05-28'),
- $this->newEpisode(2, 4, 7, 'The Patent Troll', '2017-06-04'),
- $this->newEpisode(2, 4, 8, 'The Keenan Vortex', '2017-06-11'),
- $this->newEpisode(2, 4, 9, 'Hooli-Con', '2017-06-18'),
- $this->newEpisode(2, 4, 10, 'Server Error', '2017-06-25'),
- $this->newEpisode(2, 5, 1, 'Grow Fast or Die Slow', '2018-03-25'),
- $this->newEpisode(2, 5, 2, 'Reorientation', '2018-04-01'),
- $this->newEpisode(2, 5, 3, 'Chief Operating Officer', '2018-04-08'),
- $this->newEpisode(2, 5, 4, 'Tech Evangelist', '2018-04-15'),
- $this->newEpisode(2, 5, 5, 'Facial Recognition', '2018-04-22'),
- $this->newEpisode(2, 5, 6, 'Artificial Emotional Intelligence', '2018-04-29'),
- $this->newEpisode(2, 5, 7, 'Initial Coin Offering', '2018-05-06'),
- $this->newEpisode(2, 5, 8, 'Fifty-One Percent', '2018-05-13'),
- ];
- }
- /**
- * @return string
- */
- protected function getFillDataQuery()
- {
- return <<<'EOT'
- DECLARE $seriesData AS List<Struct<
- series_id: Uint64,
- title: Utf8,
- series_info: Utf8,
- release_date: Uint64>>;
- DECLARE $seasonsData AS List<Struct<
- series_id: Uint64,
- season_id: Uint64,
- title: Utf8,
- first_aired: Uint64,
- last_aired: Uint64>>;
- DECLARE $episodesData AS List<Struct<
- series_id: Uint64,
- season_id: Uint64,
- episode_id: Uint64,
- title: Utf8,
- air_date: Uint64>>;
- REPLACE INTO series
- SELECT
- series_id,
- title,
- series_info,
- release_date
- FROM AS_TABLE($seriesData);
- REPLACE INTO seasons
- SELECT
- series_id,
- season_id,
- title,
- first_aired,
- last_aired
- FROM AS_TABLE($seasonsData);
- REPLACE INTO episodes
- SELECT
- series_id,
- season_id,
- episode_id,
- title,
- air_date
- FROM AS_TABLE($episodesData);
- EOT;
- }
- }
|