use OtoDB::MySQL;

# Logic to iterate through units is the responsibility of the user.
# These structures are used for the round-robin approach in this 
# example.

my $curr_unit = 0;

my @units = (
    # unit, connection string, username, password
    ['srv1','DBI:mysql:otodb:localhost','root',''],
    ['srv2','DBI:mysql:otodb:10.1.6.212','root','sandwich']
);

 
# Instantiate the object.
# next_unit() handles iteration over the array of servers above.
# In a 'real-world' example, you might delegate these duties to an
# external server, a global library, etc.

my $m = OtoDB::MySQL->new( units=> \&next_unit );


# Create the data models.
# 'fields' are just MySQL column names/types

$m->create(
    model=>'library',
    fields=>{
        libid=>'int not null',
        name=>'varchar(30)',
        street=>'varchar(50)',
        city=>'varchar(30)'
    } );

# The 'oto_ref' column creates a link to the 'library' model.
# All references are required fields (i.e. 'not null')

$m->create(model=>'book',
    fields=>{
        title=>'varchar(30)',
        author=>'varchar(30)'
    },
    oto_ref=>'library'
);

 

# Now, we add some data
# $key = [ entity, unit (server), unique ID of record ]

my $key = $m->add(
    entity=>'library',
    data=> {

        # genid() creates a universal 'user' ID
        libid=> $m->genid( @{$units[0]} ), 
        name=> 'Library A',
        street=> '131 Street',
        city=> 'City One'
    }
);


# Link to 'library' using $key from previous add()

$m->add(
    entity=>'book',
    data=> {
        title=>'of mice and men',
        author=>'steinbeck'
    },
    oto_ref=>$key
);
 

# Add books to a second library
# Use first server as the ID server. Using a single server makes sure
# that user data IDs will be unique across all units.

my $libid = $m->genid( @{$units[0]} );


# Combine add() calls to satisfy 'oto_ref'.

$m->add(
    entity=>'book',
    data=> {
        title=>'of mice and men',
        author=>'steinbeck'
    },
    oto_ref=> $m->add(
        entity=>'library',
        data=> {
            libid=> $libid,  # unique 'user' ID for libary B     
            name=> 'Library B',
            street=> '111 Street',
            city=> 'City the Third'
        })
);


$m->add(
    entity=>'book',
    data=> {
        title=>'winter of our discontent',
        author=>'steinbeck'
    },
    oto_ref=> $m->add(
        entity=>'library',
        data=> {
            libid=> $libid,  # unique 'user' ID for libary B
            name=> 'Library B',
            street=> '111 Street',
            city=> 'City the Third'
        })
);


# Query data
# Data is conceptually 'flat', or one-to-one. The 'entity'
# parameter is basically a hint. The '<' means that the entity
# to the right is a sub-entity of the entity to the left. Consider:
#      entity => 'library < book < item_detail'
# Because 'book' is a many to 'library', save processing cycles
# by NOT reducing.

my @ents = $m->get(    
    entity=> 'library < book',
    fields=> 'library.city, library.name, library.street, book.title',
    filter=> 'library.name like ?',
    params=> ['%two%'],
    order=> 'book.title asc',
    reduce=>'no'        # 'reduce' assumed if omitted
);

 
# @ents is an array of lightweight objects - OtoDB::MySQL::Entity

foreach my $ent (@ents) {
    print $ent->field('name') . "\t" . $ent->field('city') . "\n";
    print "data:\t" . join('..', $ent->row), "\n";
}


# Set 'reduce' to 'yes' to eliminate duplicates due to data being 
# spread through multiple units. Generally, this will only be
# necessary for 'one' entities, i.e. entities that have 
# duplicates.

@ents = $m->get(
    entity=> 'library',
    fields=> 'library.name, library.street',
    order=> 'library.name desc',
    reduce=>'yes'
);

foreach my $ent (@ents) {
    print $ent->field('name') . "\t" . $ent->field(1) . ":\n";
    print "\tdata:\t", join('..', $ent->row), "\n\n";
}

 
# 'reduce' is specifically turned off. You will see duplicates from
# each unit

@ents = $m->get(
    entity=> 'library',
    fields=> 'library.name, library.street',
    order=> 'library.name asc',
    reduce=>'no'
);

foreach my $ent (@ents) {
    print $ent->field('name') . "\t" . $ent->field(1) . ":\n";
    print "\tdata:\t", join('..', $ent->row), "\n\n";
}

 

# Duplicating an entity
# dup() is given a filter expression, and returns the first entity
# it finds in the data store matching that expression. Here we
# add another book based on our 'library ID'.

$m->add(
    entity=>'book',
    data=> {
        title=>'grapes of wrath',
        author=>'steinbeck'
    },
    oto_ref=> $m->dup(
        entity=>'library',
        filter=>'library.libid = ?',
        params=> [$libid]  # remember this guy from above?
    )
);

 
# Update
# 'fields' is a collection of the column names and the new
# values you want to replace.

$m->update(
    entity =>'library < book',
    fields=>{ # use fully qualified field names
        'library.name'=>'new lib a',
        'book.title'=>'OFMICEANDMEN'
    },
    filter=>'library.name like ? and book.author like ?',
    params=>['%two%', 'steinbeck']
);

 
# Deleting 
# Delete based on a filter. Note: OtoDB does a check to see
# if there are any references from or to and entity, if you
# pass a single entity as a param. It dies if any exist.

$m->del(
    entity =>'library < book',
    filter=>'library.name like ?',
    params=>['%one%']
);


# Delete book matching title at a given library
$m->del(
    entity=> 'library < book',
    filter=> 'library.name like ? and book.title like ?',
    params=> ['%lib%', '%winter%']
);

 
# Increment through units, round-robin. A required subroutine.
# A reference to this routine is passed to the $m object upon
# instantiation, and it's how OtoDB::MySQL gets the next unit for
# data operations.

sub next_unit {

    # get current
    my $n = $curr_unit;

    #increment
    $curr_unit = ++$curr_unit > $#units ? 0 : $curr_unit;
    return $units[$n];
}
[download]