general.pm

普通的ETL工具

		my $c = $self->SUPER::translate(@_);
		my $i;
		for ($i=0; $i < $#_; $i+=2)
		{
			$c->over();
			$c->code($_[$i]);
			$c->code(" ? $_[$i+1]");
			$c->newline_off();
			$c->code(" : ");
			$c->newline_on();
		}
		$c->code($_[$i]);
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::ToNumber;
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Returns the numeric value for I<expr>.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&to_number(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'to_number');
	use Class::STL::ClassMembers::Constructor;
	sub translate
	{
		my $self = shift;
		my $c = $self->SUPER::translate(@_);
		$c->code("do{ my \$tmp=$_[0]; \$tmp =~ s/.(?<![0-9])//g; \$tmp; }");
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::ExtractNumeric;
	use base qw(ETL::Pequel3::Type::Macros::ToNumber);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Returns the numeric value for I<expr>. Same as I<&to_number()>.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&extract_numeric(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'extract_numeric');
	use Class::STL::ClassMembers::Constructor;
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::RemoveNonNumeric;
	use base qw(ETL::Pequel3::Type::Macros::ToNumber);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Returns the numeric value for I<expr>. Same as I<&to_number()>.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&remove_non_numeric(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'remove_non_numeric');
	use Class::STL::ClassMembers::Constructor;
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::RemoveNumeric;
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Removes any numeric characters from the string in I<expr>.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&remove_numeric(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'remove_numeric');
	use Class::STL::ClassMembers::Constructor;
	sub translate
	{
		my $self = shift;
		my $c = $self->SUPER::translate(@_);
		$c->code("do{ my \$tmp=$_[0]; \$tmp =~ s/\\d+//g; \$tmp; }");
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::RemoveSpecial;
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Removes any special (non-alphanumeric) characters from the string in I<expr>.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&remove_special(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'remove_special');
	use Class::STL::ClassMembers::Constructor;
	sub translate
	{
		my $self = shift;
		my $c = $self->SUPER::translate(@_);
		$c->code("do{ my \$tmp=$_[0]; \$tmp =~ s/\\W+//g; \$tmp; }");
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::Soundex;
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => "The soundex macro returns a character string containing the phonetic representation of I<expr>. This function lets you compare words that are spelled differently, but sound alike in English.\n\nThe phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:\n\nRetain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.\n\nAssign numbers to the remaining letters (after the first) as follows:\n\nb, f, p, v = 1\n\nc, g, j, k, q, s, x, z = 2\n\nd, t = 3\n\nl = 4\n\nm, n = 5\n\nr = 6\n\n\n\nIf two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first. Return the first four bytes padded with 0."),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&soundex(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'soundex');
	use Class::STL::ClassMembers::Constructor;
	sub translate
	{
		# Retain the first letter of the string and remove all other occurrences of the 
		# following letters: a, e, h, i, o, u, w, y. 
		# Assign numbers to the remaining letters (after the first) as follows: 
		# b, f, p, v = 1
		# c, g, j, k, q, s, x, z = 2
		# d, t = 3
		# l = 4
		# m, n = 5
		# r = 6
		# 
		# If two or more letters with the same number were adjacent in the original 
		# name (before step 1), or adjacent except for any intervening h and w, 
		# then omit all but the first. 
		# Return the first four bytes padded with 0. 

		my $self = shift;
		my $c = $self->SUPER::translate(@_);
		$c->newline_off();
		$c->code("&{sub{");
		$c->code("my \$f = substr($_[0], 0, 1); ");
		$c->code("my \$rest = substr($_[0], 1); ");
		$c->code("\$rest =~ y/bfpvcgjkqsxzdtlmnraehiouwy/111122222222334556        /d; ");
		$c->code("\$rest =~ y///cs; ");	# remove consecutive duplicate chars.
		$c->code("\$rest =~ s/\\s*//g; ");
		$c->code("\$f = substr(\$f . \$rest, 0, 4); ");
		$c->code("\$f . '0' x ( 4 - length( \$f ) );  ");
		$c->newline_on();
		$c->code("}}");
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::Banding; #TODO: test
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'The F<&banding()> macro will return the band number (starting from 1) for I<expr>, depending on the value of I<expr> in relation to the I<band-divisor>. The I<band-divisor> must be a non zero numeric value. The returned band number is calculated as int( ( I<expr> - 1 ) / I<band-divisor> ) + 1.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&banding(E<lt> I<expr> E<gt>, E<lt> I<band-divisor> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'banding');
	use Class::STL::ClassMembers::Constructor;
	sub translate
	{
		my $self = shift;
		my $c = $self->SUPER::translate(@_);
#		$self->PARAM->error->fatalError("[4012] Invalid band divisor -- zero not allowed") 
#			if (!$_[1] || $_[1] == 0);
		$c->code("$_[0] eq '' || $_[0] =~ /^\\s+\$/ ? ''");
		$c->over;
		$c->code(": $_[0] == 0 ? 'U'");
		$c->over;
		$c->code(": int(($_[0] - 1) / $_[1]) + 1");
		$c->back;
		$c->back;
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::UserOption;
	use base qw(ETL::Pequel3::Type::Macros::General::Abstract);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Returns the value for the user-option as specified in I<expr>. Alternate name for the F<&option()> macro.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&user_option(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'user_option');
	use Class::STL::ClassMembers::Constructor;
	sub translate # args: user-option-name
	{
		my $self = shift;
		my $c = $self->SUPER::translate(@_);
		# TODO: unknown option...can check at generation time
		$c->code("\${\$g_USER_OPTION{$_[0]}}[0]");
		return $c;
	}
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::Option;
	use base qw(ETL::Pequel3::Type::Macros::UserOption);
	use Class::STL::ClassMembers 
		Class::STL::ClassMembers::DataMember->new(name => 'description', default => 'Returns the value for the user-option as specified in I<expr>. Alternate name for the F<&user_option()> macro.'),
		Class::STL::ClassMembers::DataMember->new(name => 'usage', default => '&option(E<lt> I<expr> E<gt>)'),
		Class::STL::ClassMembers::DataMember->new(name => 'name', default => 'option');
	use Class::STL::ClassMembers::Constructor;
}
# ----------------------------------------------------------------------------------------------------
{
	package ETL::Pequel3::Type::Macros::General;
	use ETL::Pequel3::Type::Extension;
	use base qw(ETL::Pequel3::Type::Extension::Abstract);
	use Class::STL::ClassMembers;
	use Class::STL::ClassMembers::SingletonConstructor;
	sub new_extra
	{
		my $self = shift;
		$self->catalogue()->macros()->push_front(
        	ETL::Pequel3::Type::Macros::Map->new(),
        	ETL::Pequel3::Type::Macros::Unpack->new(),
        	ETL::Pequel3::Type::Macros::Pack->new(),
        	ETL::Pequel3::Type::Macros::Commify->new(),
        	ETL::Pequel3::Type::Macros::Env->new(),
        	ETL::Pequel3::Type::Macros::Configuration->new(),
        	ETL::Pequel3::Type::Macros::MatchAny->new(),
        	ETL::Pequel3::Type::Macros::Match->new(),
        	ETL::Pequel3::Type::Macros::Select->new(),
        	ETL::Pequel3::Type::Macros::ToNumber->new(),
        	ETL::Pequel3::Type::Macros::ExtractNumeric->new(),
        	ETL::Pequel3::Type::Macros::RemoveNonNumeric->new(),
        	ETL::Pequel3::Type::Macros::RemoveNumeric->new(),
        	ETL::Pequel3::Type::Macros::RemoveSpecial->new(),
        	ETL::Pequel3::Type::Macros::Soundex->new(),
        	ETL::Pequel3::Type::Macros::Banding->new(),
        	ETL::Pequel3::Type::Macros::UserOption->new(),
        	ETL::Pequel3::Type::Macros::Option->new(),
        	ETL::Pequel3::Type::Macros::InputRepeatValue->new(), # &irval(<n>) -- return value for repeat #n;
        	ETL::Pequel3::Type::Macros::OutputRepeatValue->new(), # &orval(<n>) -- return value for repeat #n;
#>			greatest(value, ...) -- return greatest value from list
#>			least(value, ...)
#>COALESCE(value [, ...])The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
#>
#>SELECT COALESCE(description, short_description, '(none)') ...
#>
#>NULLIF(value1, value2)The NULLIF function returns a null value if value1 and value2 are equal; otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:
#>
#>SELECT NULLIF(value, '(none)') ...
#>If value1 is (none), return a null, otherwise return value1. 

#>md5(string text) text Calculates the MD5 hash of string, returning the result in hexadecimal  md5('abc') 900150983cd24fb0 d6963f7d28e17f72 
#>repeat(string text, number int) text Repeat string the specified number of times repeat('Pg', 4) PgPgPgPg 

		);
		return $self;
	}
}
# ----------------------------------------------------------------------------------------------------
1;