Skip to content

Instantly share code, notes, and snippets.

@klopp
Created December 28, 2020 13:48
Show Gist options
  • Save klopp/c3fd6716e8f6e38db09bc1680c51002e to your computer and use it in GitHub Desktop.
Save klopp/c3fd6716e8f6e38db09bc1680c51002e to your computer and use it in GitHub Desktop.
#!/usr/bin/perl
=pod
Есть таблица mysql, которая описывает древовидную структуру:
CREATE TABLE `tree` (
`id` int(10) unsigned NOT NULL auto_increment,
`parent_id` int(10) unsigned NOT NULL default '0' COMMENT 'указывает на родителя',
PRIMARY KEY (`id`)
)
Напишите на перле функцию print_tree, которая на вход получает id узла, и выводит все нижележащие ветки.
Например, вызов print_tree(10) должен вывести что-то вроде:
id=10, start point, children=6
-id=11, parent_id=10, children=0
-id=12, parent_id=10, children=3
--id=13, parent_id=12, children=0
--id=14, parent_id=12, children=1
---id=15, parent_id=14, children=0
-id=16, parent_id=10, children=0
=cut
use 5.26.0;
use Modern::Perl;
use Carp qw/croak/;
use Const::Fast;
use DBI;
use Log::Any qw/$log/;
use Log::Any::Adapter qw/Stdout/;
# Stub, must be read from config file:
const my %DB_CONF => (
db_user => '__USER__',
db_pwd => '__PASSWORD__',
db_host => '127.0.0.1',
db_port => 3306,
db_name => 'test_base',
db_table => 'tt',
);
const my $PARENT_ID => 1;
my $dbh
= DBI->connect(
"dbi:mysql:dbname=$DB_CONF{db_name};host=$DB_CONF{db_host};port=$DB_CONF{db_port}",
$DB_CONF{db_user}, $DB_CONF{db_pwd} );
croak 'Can not connect to DB' unless $dbh;
my $root
= $dbh->selectrow_hashref(
"SELECT `id`, `name`, `parent_id` FROM `$DB_CONF{db_table}` WHERE `id` = ?",
{}, $PARENT_ID );
_show_tree( $root, 0 );
$dbh->disconnect;
sub _show_tree
{
my ( $parent, $level ) = @_;
my $children
= $dbh->selectall_hashref(
"SELECT `id`, `name`, `parent_id` FROM `$DB_CONF{db_table}` WHERE `parent_id` = ?",
'id', {}, $parent->{id} );
$log->infof(
'%sid = %u, name = "%s", parent_id = %u, children = %u',
( q{ } x $level ),
$parent->{id}, $parent->{name},
$parent->{parent_id} // 0,
# 5.26.0, do not use "scalar keys":
scalar %{$children}
);
while ( my ( undef, $data ) = each %{$children} ) {
_show_tree( $data, $level + 1 );
}
}
=pod
Output:
$ ./tree_test.pl ↑
id = 1, name = "root", parent_id = 0, children = 2
id = 3, name = "cat1-2", parent_id = 1, children = 1
id = 5, name = "cat-3-1", parent_id = 3, children = 0
id = 2, name = "cat1-1", parent_id = 1, children = 1
id = 4, name = "cat-2-1", parent_id = 2, children = 0
=cut
=pod
Таблица tree из задачи выше. Нужно предложить способ проверить целостность данных, и выявить
все строки, у которых parent_id указывает на несуществующую запись.
=cut
=pod
В чём прикол? Просто же.
SELECT * FROM `tt` WHERE `parent_id` NOT IN (SELECT `id` FROM `tt`)
Или, если хотим исключить корневой элемент, у которого родителя нет by design:
SELECT * FROM `tt` WHERE `parent_id` NOT IN (SELECT `id` FROM `tt`) AND `parent_id` <> 0
=cut
=cut
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment