I've been doing some tests write a Perl Module to interface with a MySQL DB running SugarCRM. I've been doing DBI to Sugar for at least 10 years to simply support our CRM system. Whenever I can squeeze in some time I update the API I hacked out. Sometimes a sales person gives me a Excel file and I import those as leads. Sometimes I run reports, etc.

As I made some changes today I started to notice that I've had abstracted so much within the object itself that it may be possible to remove any reference to any Sugar items. If so, I'm thinking there are already DBI modules that do similar type distractions.

The first method that is not Sugar hard coded would be r2h (row2hash). I wrote this method for a Pi2 projects wher eI needed to get rows from a DB fast without writing a bunch of code.

It looks like this

# Pass these items
# DBI object
# table name
# id column name
# and id looking for
# Example to get a row with id from user table
# my $data = $r2h->($db, 'users', 'id', 10);
my $r2h  = sub {
  my $db = shift;
  my $table_name = shift;
  my $key_name = shift;
  my $id = shift;
  my $data = undef;
  eval {
    my $res = $db->prepare(
      "select * from `$table_name` where `$key_name`=? limit 1")
      or die $db->errstr();
    $res->execute($id) or die $res->errstr();
    my $name = $res->{'NAME'};
    while(my @c = $res->fetchrow_array()) {
      my $x = 0;
      foreach my $n (@{$name}) {
        $data->{$n} = $c[$x];
        $x++;
      }

      $data->{'HEADERS'} = $name;
    }
    $res->finish();

  };
  if($@) {
    $data = undef;
  }

  return $data;
};
# r2h's in other modules return an array instead
# ($data, $err) so that caller can distinguish between no
# entry in the table and a real error.
[download]

I saw the pattern when I started creating notes to enter into Sugar.

The more I use that data format with the col names as an array the more my scripts stop calling Sugar->prepare() (Like DBI).

Here is an example where I created a simply method to create a lead with the r2h data structure.

sub create_lead {
  my $self = shift;
  my $uuid = undef;
  my $data = undef;
  eval { 

    $uuid = $self->gen_uuid();
    
    # This is where I saw the abstraction.
    # I built $get_factory off of the table descriptions
    $data = $get_factory->('leads');
    $data->{'id'}               = $uuid;
    $data->{'first_name'}       = $uuid;
    $data->{'last_name'}        = $uuid;
    $data->{'date_entered'}     = $get_now->();
    $data->{'date_modified'}    = $get_now->();
    $data->{'created_by'}       = 1;
    $data->{'modified_user_id'} = 1;
    $data->{'description'}      = 'Sugar::create_lead()';

  };
  if($@) { 
    $self->{'ERRSTR'} = $@; 
    $uuid = undef;
    $data = undef;
  }

  return $data;
}
[download]

The script can now update the hash items and then call Sugar->save_lead()

sub save_lead {
  my $self = shift;
  my $data = shift;
  my $t_name = $self->get_table_name('leads');

  eval { 

    my $count = 0;
    die "No id in data!\n" unless $data->{'id'};

    # Get our database handle;
    my $dbh = $self->get_db();
    if(not $dbh) {
      $dbh = $self->connect() 
        or die $self->errstr();
    }

    # Validate this id does exist
    my $res = $dbh->prepare(
        "select count(*) from `$t_name` where id=?")
        or die $dbh->errstr();
    $res->execute($data->{'id'}) or die $res->errstr();
    $res->bind_columns(\$count) or die $res->errstr();
    while($res->fetchrow()) { }
    $res->finish();

    # We need to create one!
    if(not $count) {
      $res = $dbh->prepare(
          "insert into `$t_name` (id,description,date_entered,date_mod
+ified,created_by,modified_user_id) values (?,?,?,?,?,?) ")
          or die $dbh->errstr();
      $res->execute($data->{'id'}, 'Sugar::save_lead()', $get_now->(),
+$get_now->(), 1,1) or die $res->errstr();
    }



    $data->{'date_modified'} = $get_now->();

    # Build our SQL statement and add values to array
    my $sql = "update `$t_name` set \n";
    my @v;

    foreach my $header (@{$data->{'HEADERS'}}) {
      next if $header eq 'id';
      $sql .= "  `$header`=?,\n";
      push @v, $data->{$header};

    }
    chop($sql);
    chop($sql);
    $sql .= "\n where id=?;\n";

    # Do update
    my $res = $dbh->prepare($sql) or die $dbh->errstr();
    $res->execute(@v, $data->{'id'}) or die $res->errstr();


  };
  if($@) {
    $self->{'ERRSTR'} = $@;
    return 0;
  }

  return 1;
}
[download]

I originally was doing updates on each values, but my database is remote and it was too slow. Instead I build the SQL. Does DBI provide a better method than I chose to build $sql?

What I've written to assist me on data imports and reports I am beginning to see how it can be agnostic. save_lead() can become save('leads', $lead_data); That is not specific to any database app.

r2h can use any column to grab a row. The only problem I've ran into is that sometimes I need to do conditionals (where). For now I'll simple pass the conditionals as a string and then an array to satisfy those.

r2h(..) {
  my $dbh = shift;
  my $table_name = shift;
  my $col_name = shift;
  my $v = shift;
  my $where = shift;
  my @w_conds = @_;
  my $sql = ...
  if($where) {
    $sql .= "($where) and `$col_name`= ?".
   
  } else {
     $sql .= "where `$col_name` = ?";
  }
  ...
}
[download]

These are just some ideas I had and I'm thinking this work has been done. Possibly where I create a document that defines the database and the module uses that document.

When I do imports I import into tmp tables then on mysql cli I do import everything placed in the tmp to the real table. In my Sugar object I allowed the script to specify that it wants to use a temp table for a real table. The module then generates a random name, does a 'create X like Y' and then places the new table name in a hash value to the key of the original name.

create_tmp_table('leads');
.. creates leads_XXXXX in MySQL DB
.. methods like save_lead() will create the row or update the row in t
+hat table instead.
[download]