# CRUD + Sql::abstract perl web



## ProFTP (Jul 26, 2009)

realization of CRUD + Sql::abstract

I wrote under MVC Catalyst

comment, how is realization? are there errors?


```
package MyApp::Model::ExtraDBI;

use strict;
use warnings;
use base qw( Catalyst::Model Class::Accessor);
use Class::C3::Adopt::NEXT;
use HTML::Entities::Numbered;

__PACKAGE__->mk_accessors(qw/bad_fields_type all_fields_type/);

sub new {
    my ($self, $c) = @_;
    $self = $self->next::method(@_);
}

sub no_sql {
 my $self = shift;
 $self->{no_sql} = 1;
   return $self;
}

sub no_bad {
 my $self = shift;
 $self->{no_bad} = 1;
   return $self;
}

####
#   Add out fields
###

sub _add_sql_fields {
	my ($self) = @_;

	if ($self->{no_sql} == 1) {
	$self->{no_sql} = undef;
		return;
	}
	
  	if ($self->sql_fields_type eq 'arrey') {
		 if (!  $self->{sql_arrey_out} ) {
		  $self->{sql_arrey_out} = [];
		 }    	
		  push @{ $self->{sql_arrey_out} } , $self->{key};  # is $self->fails_type  arrey  
		  
   } 
   
   	if ($self->sql_fields_type eq 'hash') {
 	 	$self->{sql_hash_out}->{$self->{key}} = $self->{value};  # $self->fails_type  HASH   key = faild, value = name
  	}

}



sub _add_bad_fields {
	my ($self) = @_;

	if ($self->{no_bad} == 1) {
		$self->{no_bad} = undef;
		return;
	}
	
  	if ($self->bad_fields_type eq 'arrey') {
		 if (!  $self->{bad_arrey_out} ) {
		  $self->{bad_arrey_out} = [];
		 }    	
		  push @{ $self->{bad_arrey_out} } , $self->{key};  # is $self->fails_type  arrey  
		  
   } 
   
   if ($self->bad_fields_type eq 'hash') {
    	# my $hash_out;
 	 	$self->{bad_hash_out}->{$self->{key}} = $self->{value};  # $self->fails_type  HASH   key = faild, value = name
  	}

}


sub _add_all_fields {
	my ($self) = @_;

	if ($self->{no_sql} == 1) {
	$self->{no_sql} = undef;
		return;
	}	
	
  	if ($self->all_fields_type eq 'arrey') {

		 if (! @{ $self->{all_arrey_out} } ) {
			  $self->{all_arrey_out} = []; 
		 }    	
  	
		  push @{ $self->{all_arrey_out} }, $self->{key};  # is $self->fails_type  arrey  
   } 
   
   if ($self->all_fields_type eq 'hash') {
#  	my $hash_out;

 	 	$self->{all_hash_out}->{$self->{key}} = $self->{value};  # $self->fails_type  HASH   key = faild, value = name
  	}
  	
  	# return;

}

####
#   Clean text, remove bad tag, etc
###


sub _del_blanks_end_began {
	my $self = shift;
	$self->{value} =~ s/^\s+//;
	$self->{value} =~ s/\s+$//;
	return $self;
}


sub _cleaning {
my $self = shift;

    $self->{value} =~ s!\0!!g;
    $self->{value} =~ s|&|;|g;
    $self->{value} =~ s|<!--||g; 
    $self->{value} =~ s|-->||g; 
    $self->{value} =~ s|<script||ig; 
    $self->{value} =~ s|>||g;
    $self->{value} =~ s|<||g;
    $self->{value} =~ s|"||g; 
    $self->{value} =~ s|  | |g; 
    $self->{value} =~ s!\|!|!g; 
    $self->{value} =~ s|\n||g; 
    $self->{value} =~ s|\$||g; 
    $self->{value} =~ s|\r||g; 
    $self->{value} =~ s|\_\_(.+?)\_\_||g; 
    $self->{value} =~ s|\\||g; 
    $self->{value} =~ s|\'||g; 
    $self->{value} =~ s|!||g; 
  
  return $self;

} 


sub _clean_html {
my $self = shift;
   $self->{value} = name2decimal($self->{value});
  return $self;
} 
 

####
#   Valid fields
###
sub head_text {
 my $self = shift;

  $self->{key} = shift; 
  $self->{value} = shift; 
  	
	$self->_del_blanks_end_began;
	$self->_cleaning;
	
	$self->_add_all_fields();
   
    return $self->{value} if (defined wantarray);
}

sub cut_xss {

 my $self = shift;
 
   $self->{key} = shift if @_; 
   $self->{value} = shift if @_;
  	
	$self->_del_blanks_end_began;
	$self->_clean_html;
  
    return $self->{value} if (wantarray);    	
	 return $self;
}


sub valid_id {
 my $self = shift;

  $self->{key} = shift; 
  $self->{value} = shift; 

  	
	$self->_del_blanks_end_began();
	
	$self->_add_all_fields();


	if (! $self->{value} =~ /^\d+$/) {		
		$self->_add_bad_fields();		
   	$self->{value} = undef;		
	}   
    return $self->{value} if (defined wantarray);

}



sub int_check {
 my $self = shift;

  $self->{key} = shift; 
  $self->{value} = shift;
	
	$self->_del_blanks_end_began();

	$self->{value} = $self->{value} eq 'on' ? '1' : '0';  
	
	$self->_add_all_fields();

   return $self->{value} if (wantarray);
  return $self->{value};
 
}



sub one_die {
 my $self = shift;

  $self->{key} = shift; 
  $self->{value} = shift; 

	$self->_del_blanks_end_began();
	
	$self->_add_all_fields();

	if (!$self->{value} == 1) {
		$self->_add_bad_fields();		
   	$self->{value} = undef;
	}	
	  return $self->{value} if (defined wantarray);
}

sub zero_die {
 my $self = shift;

  $self->{key} = shift; 
  $self->{value} = shift; 

	$self->_del_blanks_end_began();
	
	$self->_add_all_fields();

	if (!$self->{value} == 0) {
		$self->_add_bad_fields();		
   	$self->{value} = undef;
	}	
	  return $self->{value} if (defined wantarray);
}


sub exist_die {
 my $self = shift;

  $self->{key} = shift if @_; 
  $self->{value} = shift if @_;
	
  	$self->_add_all_fields();

	if (! $self->{value}) {
		$self->_add_bad_fields();		
   	$self->{value} = undef;
   	$self->{key} = undef;
	}

    return $self->{value} if (wantarray);    	
	 return $self;
 
}

####
#   Out fields all and bad 
###

sub out_all {
my $self = shift;

	if ( defined $self->{all_arrey_out} && $self->all_fields_type eq 'arrey') {
  		return $self->{all_arrey_out};
	} 
	
	if ( defined $self->{all_hash_out} && $self->all_fields_type eq 'hash' ) {
      return $self->{all_hash_out};
	}
	
}

sub out_bad {
my $self = shift;

	if ( defined @{ $self->{bad_arrey_out} } && $self->bad_fields_type eq 'arrey') {
  		return $self->{bad_arrey_out};
	} 
	
	if ( defined $self->{bad_hash_out} && $self->bad_fields_type eq 'hash' ) {
      return $self->{bad_hash_out};
	}

}

sub error_valid {
my $self = shift;

if (  $self->{bad_arrey_out}  || $self->{bad_hash_out} ) {
return 1;
} else {
return undef;
}

}


1;
```


----------



## ProFTP (Jul 26, 2009)

*example as works:*


```
my ( $self, $c, $edit_co ) = @_;

	$c->stash->{template} = 'add_section.tt';

	my $f = $c->model('ExtraDBI')->new;  
	$f->all_fields_type('hash'); 
	$f->bad_fields_type('array');           

	$f->cut_xss( 'name_co', $c->request->params->{name_content} )->exist_die; 

	$f->cut_xss( 'heading_name_co', $c->request->params->{name_head_content} )
	  ->exist_die;

	$f->cut_xss( 'keys_co', $c->request->params->{content_keys} )->exist_die;
	$f->cut_xss( 'text_co', $c->request->params->{content_text} )->exist_die;

	if ( $c->check_user_roles("moder_se") ) {
		$f->int_check( 'hiden_g_co',
			$c->request->params->{type_hiden_guest_content} );
		$f->int_check( 'close_co', $c->request->params->{type_close_content} );
		$f->int_check( 'active_co',
			$c->request->params->{type_active_content} );

	}

	$f->int_check( 'hiden_co',  $c->request->params->{type_hiden_content} );
	$f->int_check( 'voting_co', $c->request->params->{type_voting_content} );
	$f->int_check( 'forbi_comm_co', $c->request->params->{forbi_comm_co} );

	my $sp;

	if ( $c->request->params->{type_section_privat} eq 'on' ) {
		$sp = 'AND privat_se = 1';
	}
	else {
		$sp = 'AND privat_se = 0';

		$f->no_sql->int_check( 'privat_se', 'on' );
	}

	if ( !$edit_co && !$c->request->params->{section_child2} ) {
		$c->request->params->{section_child2} =
		  $c->request->params->{type_section_privat} eq '1' ? 1 : 35;
	}

	if (
		$f->no_sql->valid_id(    
       			'parent_se_id', $c->request->params->{section_child2}
		)
	  )
	{

		my $dbh = $c->model('DBI')->dbh;
		my $sth = $dbh->prepare(
			"SELECT id_se,
						 id_un,
						 close_se,
						 active_se,
						 forbi_content_se,
						 privat_se
				   FROM section
				  WHERE id_se = ?
					$sp
				  LIMIT 1"
		);
		$sth->execute( $c->request->params->{section_child2} );
		my $section = $sth->fetchrow_hashref();
		$sth->finish();

		if ( $f->exist_die( 'id_se', $section->{id_se} ) ) {  

			if ( !$c->check_user_roles('moder_se') ) {

				if (   $section->{active_se} == 0
					&& $section->{id_un} != $c->user->{user}->{id} )
				{
					$f->no_sql->zero_die( 'active_se', 0 );
				}

				$f->no_sql->zero_die( 'forbi_content_se',
					$section->{forbi_content_se} );

			}
		}
	}

	if ($edit_co) {
		$f->no_sql->exist_die( 'no_edit_id_co',
			$c->request->params->{edit_id_co} );

		if ( !$c->check_user_roles('moder_se') ) {

			my $dbh = $c->model('DBI')->dbh;
			my $sth = $dbh->prepare(
				"SELECT id_co,
						 close_co,
						 id_un
						 
				   FROM content
				  WHERE id_co = ?

				  LIMIT 1"
			);
			$sth->execute( $c->request->params->{edit_id_co} );
			my $section = $sth->fetchrow_hashref();
			$sth->finish();

			$f->no_sql->zero_die( 'close_co', $section->{close_se} );

			if ( $section->{id_un} == $c->user->{user}->{id} ) {
				$f->no_sql->zero_die( 'id_un_no_co', 0 );
			}

		}

	}

	if ( !$f->error_valid ) {
              # ÐµÑÐ»Ð¸ Ð¾ÑˆÐ¸Ð±Ð¾Ðº Ð½ÐµÑ‚Ñƒ
		my $hash = $f->out_all;
		my $type_sql;
		my $where; 
		if ($edit_co) {  
			$type_sql = 'update'; 
			$where->{id_co} = $c->request->params->{edit_id_co};
			$where->{id_un} = $c->user->{user}->{id}
			  if ( !$c->check_user_roles('moder_co') );
			$hash->{modified} = time;

		}

		if ( !$edit_co ) {  

			if ( !$c->check_user_roles("moder_se") ) {

				$hash->{hiden_g_co} = 0;
				$hash->{close_co}   = 0;
				$hash->{active_co}  = 0;

			}

			$type_sql		= 'insert';
			$hash->{created} = time;
			$hash->{id_un}   = $c->user->{user}->{id};

		}
		use SQL::Abstract;
		my $sql = SQL::Abstract->new;

		my ( $stmt, @bind ) = $sql->$type_sql( 'content', $hash, $where );

		my $dbh = $c->model('DBI')->dbh;
		my $sth = $dbh->prepare($stmt);
		$sth->execute(@bind);
		$sth->finish();

		my $lastid = $dbh->{mysql_insertid} unless ($edit_co); 
		my $url;
		my $redirect_id =
		  $edit_co ? $c->request->params->{edit_id_co} : $lastid;

		if ( $c->request->params->{type_redirect} eq 'on' ) {
			$url = '/profile/edit_pesonal_content/' . $redirect_id;
		}
		else {
			$url = '/view_content/' . $redirect_id;
		}

		$c->response->redirect( $c->uri_for($url) );
		$c->detach();

	}
	else { 

		my $out_all = $f->out_all; 

		my $out_bad = $f->out_bad;  
                # Ñ‚Ð°Ð¼ Ð³Ð´Ðµ Ð±Ñ‹Ð»Ð° Ð¾ÑˆÐ¸Ð±ÐºÐ°

		$c->stash->{bad_form} = 1; 
                   
		while ( my ( $key, $value ) = each( %{$out_all} ) ) {  

			$c->stash->{ $key . '_current' } = $value;
		}

		foreach ( @{$out_bad} ) { 
			$_ .= $_ . '_error' if ( $_ eq 'id_se' );
			$c->stash->{$_} = 1;

		}

		if ( !$edit_co ) {
			$c->forward( 'add_content',
				[ $c->request->params->{section_child2} ] );
		}
		else {
			$c->forward( 'edit_pesonal_content',
				[ $c->request->params->{section_child2} ] );
		}
		$c->detach();

	}
```


----------

