Basset::DB::Table - used to define database tables, ways to load that data into memory and build queries based upon the table information
Jim Thomason, jim@jimandkoka.com
For example,
my $table = Basset::DB::Table->new(
'name' => 'user',
'primary_column' => 'id',
'autogenerated' => 1,
'definition' => {
'id' => 'SQL_INTEGER',
'username' => 'SQL_VARCHAR',
'password' => 'SQL_VARCHAR',
'name' => 'SQL_VARCHAR'
}
);
print $table->insert_query, "\n"; print $table->update_query, "\n"; print $table->delete_query, "\n";
Basset::DB::Table provides an abstract and consistent location for defining database tables, building queries based upon them, and so on. It is rarely (if ever) used directly in code, but is used extensively in packages which subclass from Basset::Object::Persistent.
Any queries returned by the query methods are simply strings that must be prepared by DBI in order bo be used.
name
The name of the database table.
For example, if you're creating an object to reference the table "foo",
$table->name('foo');$test->is(scalar(__PACKAGE__->name('invalid name')), undef, 'Could not set name for class attribute');
primary_column
Stores the primary column or columns for this table. Either passed a single scalar or an array ref.
$table->primary_column('id'); #id is the primary column $table2->primary_column(['id', 'name']) #id & name are the primary columnsIt is recommended to access the primary columns of a table via the primary_cols method, since that method will always return an array.
$table->primary_cols #returns ('id') $table2->primary_cols #returns ('id', 'name') $table->primary_column #returns 'id' $table2->primary_column #returns ['id', 'name']my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); $test->ok($o->primary_column('id'), "Set primary column"); $test->is($o->primary_column, 'id', "retrieved primary column"); $test->is($o->primary_column('id2'), 'id2', 're-set primary column'); $test->is($o->primary_column, 'id2', 'retrieved reset primary column');
my $a = [qw(id id2)]; $test->ok($a, "created arrayref"); $test->is($a->[0], 'id', 'proper array element 0'); $test->is($a->[1], 'id2', 'proper array element 1'); $test->is($o->primary_column($a), $a, 'set primary column to arrayref');
$test->is(scalar(__PACKAGE__->primary_column('invalid name')), undef, 'Could not set primary column for class attribute');
autogenerated
boolean flag, 1/0
Sometimes, you may have your database auto-generate a column value for you. If you are using unique IDs for instance, it may be easier to have the database manage the auto-generation of new unique IDs for you. Set this flag if that's the case.
#in your db create table foo (id int unsigned not null primary key auto_generated); #in your code $table->name('foo'); $table->primary_column('id'); $table->autogenerated(1);my $o = __PACKAGE__->new();
$test->ok($o, "created object"); $test->is($o->autogenerated(1), 1, "set autogenerated"); $test->is($o->autogenerated(), 1, "accessed autogenerated"); $test->is($o->autogenerated(0), 0, "shut off autogenerated"); $test->is($o->autogenerated, 0, "accessed autogenerated");
$test->is(scalar(__PACKAGE__->autogenerated(1)), undef, "Could not set autogenerated for class");
definition
This is the actual definition of your table. It should be given a hashref, with the keys being your column names, and the values being the sql_type as defined in DBI for that column.
$table->definition( { 'name' => 'SQL_VARCHAR', 'id' => 'SQL_INTEGER' } );Note that the type should be a quoted string containing the value, not the actual constant defined in DBI. If there is no corresponding sql_type for your column (for a MySQL text column, for example), then pass undef.
$table->definition( { 'name' => 'SQL_INTEGER', 'bigcomment' => undef } );Alternatively, if you happen to know the SQL type in advance, you can just pass that along.
$table->definition( { 'name' => SQL_INTEGER, #if DBI was used here 'bigcomment' => undef } ); $table->definition( { 'name' => 4, #if you just know it's 4 'bigcomment' => undef } );You should always use the quoted version unless you've received the numeric type from an authoritative source, such as having it returned from the database as the column type.
Alternatively, if you don't want to use a definition, you can explicitly tell the constructor your non primary columns
$table = Basset::DB::Table->new( 'primary_column' => 'id', 'non_primary_columns' => [qw(name age serial_number)], );That takes the place of using the definition. It does a discover call behind the scenes, but only looks for the columns that you've specified, not everything in the table.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
my $h = {'foo' => 'bar', 'baz' => 'yee'}; $test->ok($h, 'got hashref'); $test->is($h->{'foo'}, 'bar', 'foo is bar'); $test->is($h->{'baz'}, 'yee', 'baz is yee'); $test->is($o->definition($h), $h, "Set definition"); $test->is($o->definition(), $h, 'reset definition'); $test->is(scalar(__PACKAGE__->definition(1)), undef, 'Could not set definition for class');
references
Naturally, since you're using a relational database, you're going to have tables referencing other tables. You can store them in your Basset::DB::Table object inside the references parameter.
$table->references( { 'user_id' => 'user.id', 'food_type' => 'food.type', } );That says that the 'user_id' column in your table is a foreign key into the user table and references its id column. 'food_type' is a foreign key into the food table and references its type column.
Any foreign keys referencing primary columns can be used to auto-join the tables in a multiselect_query.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
my $h = {'foo' => 'bar', 'baz' => 'yee'}; $test->ok($h, 'got hashref'); $test->is($h->{'foo'}, 'bar', 'foo is bar'); $test->is($h->{'baz'}, 'yee', 'baz is yee'); $test->is($o->references($h), $h, "Set references"); $test->is($o->references(), $h, 'reset references'); $test->is(scalar(__PACKAGE__->references(1)), undef, 'Could not set references for class');
extra_select
Okay, as of v1.01 (heh, I finally incremented a version number!) Basset::DB::Table has gotten a power boost. It's now arbitrary out the ying-yang. Much more power in terms of what you can and cannot select, insert, update, etc.
The first of the new toys is extra_select.
Let's assume the following definition:
$table->name('test'); $table->definition( { 'name' => 'SQL_INTEGER', 'bigcomment' => undef } );That means that if you called select_query on that table, you'd get back this:
select test.bigcomment, test.name from testWhich is peachy and marvelous. You can now initialize your object with the values from 'name' and 'bigcomment'. But what if you want more information from the database? Perhaps a value from a function, or some calculation upon the columns? Up until now, you'd have to do that stuff externally in Perl. Either calculating things yourself, or calling arbitrary_sql to get the data you need out of there.
No more. extra_select does what it sounds like, it allows you to pass in extra information to select. Takes a hashref.
$table->extra_select( { 'current_time' => 'NOW()' } );Now, if you called select_query, you'd get back:
select test.bigcomment, test.name, NOW() as current_time from testAnd voila. Instant extra information.
Keep in mind, naturally, that if you want that extra column you're getting out to *go* anywhere, that your object must have a method by that name ("current_time" in this case). Otherwise, the data will be loaded and then silently forgotten.
If you're skipping ahead, you'll see that there are attributes called "db_write_translation", and "db_read_translation". Use whichever thing is appropriate for you.
extra_select only affects select queries.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
my $h = {'foo' => 'bar', 'baz' => 'yee'}; $test->ok($h, 'got hashref'); $test->is($h->{'foo'}, 'bar', 'foo is bar'); $test->is($h->{'baz'}, 'yee', 'baz is yee'); $test->is($o->extra_select($h), $h, "Set extra_select"); $test->is($o->extra_select(), $h, 'reset extra_select'); $test->is(scalar(__PACKAGE__->extra_select(1)), undef, 'Could not set extra_select for class');
db_read_translation
New addition to the various things, since I finally thought of a use for it. The db_read_translation alters your columns as they come back from the database. Takes a hash of the form column => translation
$table->db_read_translation( { 'name' => 'lower(name)' } );And that would change as follows:
print $table->select_query; #prints select table.name as name from tablewith the translation:
print $table->select_query; #prints select lower(table.name) as name from tableUseful if you know at the database level that you'll need your data transformed in some fashion.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
my $h = {'foo' => 'bar', 'baz' => 'yee'}; $test->ok($h, 'got hashref'); $test->is($h->{'foo'}, 'bar', 'foo is bar'); $test->is($h->{'baz'}, 'yee', 'baz is yee'); $test->is($o->db_read_translation($h), $h, "Set db_read_translation"); $test->is($o->db_read_translation(), $h, 'reset db_read_translation'); $test->is(scalar(__PACKAGE__->db_read_translation(1)), undef, 'Could not set db_read_translation for class');
db_write_translation
This is the closest thing to an inverse method to extra_select. db_write_translation takes a hashref which decides how to re-write your insert, update, replace, or delete queries. Or all of them. An example is easiest.
Let's assume the following definition:
$table->name('test'); $table->definition( { 'name' => 'SQL_INTEGER', 'bigcomment' => undef, 'current_time' => 'SQL_DATETIME', } ); update test set current_time = ?, bigcomment = ?, name = ?Then, if you called update_query, you'd get back:
update test set current_time = ?, bigcomment = ?, name = ?And your update_bindables are:
current_time, bigcomment, name, nameHowever, that wouldn't be setting current_time to the proper current time: it's just relaying through the value in the object. So it's up to you, the programmer, to set it yourself.
sub commit { my $self = shift; my ($sec,$min,$hour,$day,$mon,$year) = (localtime(time))[0..5]; $mon++; $year+= 1900; $self->current_time("$year-$mon-$day $hour:$min:$sec"); $self->SUPER::commit(@_); };It works, it's effective, but it's a pain in the butt. More work for you. This is an instance where db_write_translation can come in handy.
$table->db_write_translation( { 'current_time' => { 'A' => { 'val' => 'NOW()', 'binds' => 0 } } } );Now, your update_query is:
update test set current_time = NOW(), bigcomment = ?, name = ?And your update_bindables are:
bigcomment, name, nameVoila. You no longer need to worry about setting current_time, the db does it for you.
The hashref that db_write_translation uses is of a specific format:
method => { query_type => { 'val' => new_value 'binds' => 0/1 } }"method" is obviously the name of the method that's being re-written. "query_type" is the flag to indicate the type of query. "I" for insert, "U" for update, "D" for delete, "R" for replace, or "A" for all. "binds" is a boolean flag, 0 or 1. Set to 0 if you're inserting a new value that doesn't need a binded param, such as "NOW()". Set it to 1 if you're inserting a new value that does need a binded param, such as "LCASE(?)" to insert the value in lower case.
And voila. When the query is constructed, internally it first looks for a re-write of the method for the given query type. If it doesn't find one, it looks for a re-write of type "A" (all queries), if it doesn't find one of those, then it just leaves it alone and preps the query to insert the value in as is, unchanged.
One useful example that I will include, is to make a column read-only:
$table->db_write_translation( { $column => { 'U' => { 'val' => $column, 'binds' => 0 } } } );That way, when an object is committed on an update, $column's value will not change.
Also, please note that return values are not quoted. So you can't use a db_write_translation to set a value that the database wouldn't understand.
'val' => 'some constant value'will fail. Your query would become:
update....set foo = some constant value...which chokes, of course. Use a wrapper to alter the value you pass in at a higher level, or quote it yourself. The db_write_translation only alters your actual SQL statement.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
my $h = {'foo' => 'bar', 'baz' => 'yee'}; $test->ok($h, 'got hashref'); $test->is($h->{'foo'}, 'bar', 'foo is bar'); $test->is($h->{'baz'}, 'yee', 'baz is yee'); $test->is($o->db_write_translation($h), $h, "Set db_write_translation"); $test->is($o->db_write_translation(), $h, 'reset db_write_translation'); $test->is(scalar(__PACKAGE__->db_write_translation(1)), undef, 'Could not set db_write_translation for class');
column_aliases
You can define different aliases for columns as they come out of your table.
$table->select_columns('id'); print $table->select_query; #prints select id from foo $table->column_aliases( { 'id' => 'user_id' } ); print $table->select_query #prints select id as user_id from fooNote that Basset::Object::Persistent assumes that if you're aliasing a column, that the aliased value is your method name. So in this case, any objects using that as a primary table would have a method name of 'user_id' that stores in the 'id' column in the table.
my $aliases = { 'able' => 'aliased_able', 'baker' => 'aliased_baker', 'charlie' => 'aliased_charlie', 'delta' => 'aliased_delta' };
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
$test->is($o->column_aliases($aliases), $aliases, "Set column aliases"); $test->is($o->column_aliases(), $aliases, "Got column aliases");
my $o = __PACKAGE__->new(); $test->ok($o, "Got object"); $test->is(scalar(__PACKAGE__->_cached_queries), undef, "could not call object method as class method"); $test->is(__PACKAGE__->errcode, "BO-08", "proper error code"); $test->is(ref($o->_cached_queries), 'HASH', '_cached_queries is hashref'); $test->is($o->_cached_queries('abc'), 'abc', 'set _cached_queries to abc'); $test->is($o->_cached_queries(), 'abc', 'read value of _cached_queries - abc'); my $h = {}; $test->ok($h, 'got hashref'); $test->is($o->_cached_queries($h), $h, 'set _cached_queries to hashref'); $test->is($o->_cached_queries(), $h, 'read value of _cached_queries - hashref'); my $a = []; $test->ok($a, 'got arrayref'); $test->is($o->_cached_queries($a), $a, 'set _cached_queries to arrayref'); $test->is($o->_cached_queries(), $a, 'read value of _cached_queries - arrayref');
my $o = __PACKAGE__->new(); $test->ok($o, "Got object"); $test->is(scalar(__PACKAGE__->_cached_bindables), undef, "could not call object method as class method"); $test->is(__PACKAGE__->errcode, "BO-08", "proper error code"); $test->is(ref($o->_cached_bindables), 'HASH', '_cached_bindables is hashref'); $test->is($o->_cached_bindables('abc'), 'abc', 'set _cached_bindables to abc'); $test->is($o->_cached_bindables(), 'abc', 'read value of _cached_bindables - abc'); my $h = {}; $test->ok($h, 'got hashref'); $test->is($o->_cached_bindables($h), $h, 'set _cached_bindables to hashref'); $test->is($o->_cached_bindables(), $h, 'read value of _cached_bindables - hashref'); my $a = []; $test->ok($a, 'got arrayref'); $test->is($o->_cached_bindables($a), $a, 'set _cached_bindables to arrayref'); $test->is($o->_cached_bindables(), $a, 'read value of _cached_bindables - arrayref');
*_columns
insert_columns update_columns delete_columns replace_columns select_columnsNormally, when you get back an insert_query, update_query, etc. from the various DB::Table methods here, all columns in the table are included. You can use these methods to restrict the queries to only be called on particular methods.
print $table->insert_query; #prints insert into foo (this, that, those) values (?,?,?) for example $table->insert_columns('this'); print $table->insert-query; #prints insert into foo (this) values (?) for exampleThese methods are not thread-safe.
You also have a set of negative non_*_columns that do an inverse.
print $table->insert_query; #prints insert into foo (this, that, those) values (?,?,?) for example $table->non_insert_columns('this'); print $table->insert-query; #prints insert into foo (that, those) values (?,?,?) for exampleYou may also use both at the same time
print $table->insert_query; #prints insert into foo (this, that, those) values (?,?,?) for example $table->insert_columns('that', 'those'); $table->non_insert_columns('that'); print $table->insert-query; #prints insert into foo (those) values (?,?) for examplelast_insert_query
All databases grab the last inserted ID in a different fashion. last_insert_query allows us to specify the query we use to grab the last inserted ID for a given insert. This should probably be specified in the conf file, but you can do it in the individual modules, if you prefer. Note that this is a trickling class accessor, so you can re-define it as many times as you want, or just use the default specified for Basset::Object::Persistent.
Certain databases don't need differeing queries. MySQL, for instance, is happy with just "SELECT LAST_INSERT_ID()" defined for the super class.
my $o = __PACKAGE__->new(); $test->ok($o, "got object");
$test->ok(! scalar(__PACKAGE__->last_insert_query), "Cannot call object method as class method");
$test->is($o->last_insert_query('foo'), 'foo', "set query to foo"); $test->is($o->last_insert_query(), 'foo', 'got insert query');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
{ my %icols = map {$_, 1} $o->insert_columns();
$test->is($icols{'able'}, 1, 'able is an insert col'); $test->is($icols{'baker'}, 1, 'baker is an insert col'); $test->is($icols{'charlie'}, 1, 'charlie is an insert col'); $test->is($icols{'delta'}, 1, 'delta is an insert col'); $test->is(scalar(keys %icols), 4, 'only 4 insert columns'); }{ my $icols = [qw(able charlie)];
$test->is(join(',',$o->insert_columns($icols)), join(',',@$icols), "set new insert columns"); $test->is(join(',',$o->insert_columns), join(',',@$icols), "got new insert columns"); my %icols = map {$_, 1} $o->insert_columns(); $test->is($icols{'able'}, 1, 'able is an insert col'); $test->is($icols{'baker'}, undef, 'baker is not an insert col'); $test->is($icols{'charlie'}, 1, 'charlie is an insert col'); $test->is($icols{'delta'}, undef, 'delta is not an insert col'); $test->is(scalar(keys %icols), 2, 'only 2 insert columns'); }{ my $icols = [qw()];
$o->insert_columns($icols); my %icols = map {$_, 1} $o->insert_columns(); $test->is($icols{'able'}, 1, 'able is an insert col'); $test->is($icols{'baker'}, 1, 'baker is an insert col'); $test->is($icols{'charlie'}, 1, 'charlie is an insert col'); $test->is($icols{'delta'}, 1, 'delta is an insert col'); $test->is(scalar(keys %icols), 4, '4 insert columns'); }{ $o->insert_columns(undef); my %icols = map {$_, 1} $o->insert_columns();
$test->is($icols{'able'}, 1, 'able is an insert col'); $test->is($icols{'baker'}, 1, 'baker is an insert col'); $test->is($icols{'charlie'}, 1, 'charlie is an insert col'); $test->is($icols{'delta'}, 1, 'delta is an insert col'); $test->is(scalar(keys %icols), 4, '4 insert columns'); }$test->is(scalar($o->insert_columns(['junk'])), undef, 'could not insert unknown column'); $test->is($o->errcode, 'BDT-13', 'proper error code');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
{ my %icols = map {$_, 1} $o->update_columns();
$test->is($icols{'able'}, 1, 'able is an update col'); $test->is($icols{'baker'}, 1, 'baker is an update col'); $test->is($icols{'charlie'}, 1, 'charlie is an update col'); $test->is($icols{'delta'}, 1, 'delta is an update col'); $test->is(scalar(keys %icols), 4, 'only 4 update columns'); }{ my $icols = [qw(able charlie)];
$test->is(join(',',$o->update_columns($icols)), join(',',@$icols), "set new update columns"); $test->is(join(',',$o->update_columns), join(',',@$icols), "got new update columns"); my %icols = map {$_, 1} $o->update_columns(); $test->is($icols{'able'}, 1, 'able is an update col'); $test->is($icols{'baker'}, undef, 'baker is not an update col'); $test->is($icols{'charlie'}, 1, 'charlie is an update col'); $test->is($icols{'delta'}, undef, 'delta is not an update col'); $test->is(scalar(keys %icols), 2, 'only 2 update columns'); }{ my $icols = [qw()];
$o->update_columns($icols); my %icols = map {$_, 1} $o->update_columns(); $test->is($icols{'able'}, 1, 'able is an update col'); $test->is($icols{'baker'}, 1, 'baker is an update col'); $test->is($icols{'charlie'}, 1, 'charlie is an update col'); $test->is($icols{'delta'}, 1, 'delta is an update col'); $test->is(scalar(keys %icols), 4, '4 update columns'); }{ $o->update_columns(undef); my %icols = map {$_, 1} $o->update_columns();
$test->is($icols{'able'}, 1, 'able is an update col'); $test->is($icols{'baker'}, 1, 'baker is an update col'); $test->is($icols{'charlie'}, 1, 'charlie is an update col'); $test->is($icols{'delta'}, 1, 'delta is an update col'); $test->is(scalar(keys %icols), 4, '4 update columns'); }$test->is(scalar($o->update_columns(['junk'])), undef, 'could not update unknown column'); $test->is($o->errcode, 'BDT-13', 'proper error code');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
{ my %icols = map {$_, 1} $o->delete_columns();
$test->is($icols{'able'}, 1, 'able is an delete col'); $test->is($icols{'baker'}, 1, 'baker is an delete col'); $test->is($icols{'charlie'}, 1, 'charlie is an delete col'); $test->is($icols{'delta'}, 1, 'delta is an delete col'); $test->is(scalar(keys %icols), 4, 'only 4 delete columns'); }{ my $icols = [qw(able charlie)];
$test->is(join(',',$o->delete_columns($icols)), join(',',@$icols), "set new delete columns"); $test->is(join(',',$o->delete_columns), join(',',@$icols), "got new delete columns"); my %icols = map {$_, 1} $o->delete_columns(); $test->is($icols{'able'}, 1, 'able is an delete col'); $test->is($icols{'baker'}, undef, 'baker is not an delete col'); $test->is($icols{'charlie'}, 1, 'charlie is an delete col'); $test->is($icols{'delta'}, undef, 'delta is not an delete col'); $test->is(scalar(keys %icols), 2, 'only 2 delete columns'); }{ my $icols = [qw()];
$o->delete_columns($icols); my %icols = map {$_, 1} $o->delete_columns(); $test->is($icols{'able'}, 1, 'able is an delete col'); $test->is($icols{'baker'}, 1, 'baker is an delete col'); $test->is($icols{'charlie'}, 1, 'charlie is an delete col'); $test->is($icols{'delta'}, 1, 'delta is an delete col'); $test->is(scalar(keys %icols), 4, '4 delete columns'); }{ $o->delete_columns(undef); my %icols = map {$_, 1} $o->delete_columns();
$test->is($icols{'able'}, 1, 'able is an delete col'); $test->is($icols{'baker'}, 1, 'baker is an delete col'); $test->is($icols{'charlie'}, 1, 'charlie is an delete col'); $test->is($icols{'delta'}, 1, 'delta is an delete col'); $test->is(scalar(keys %icols), 4, '4 delete columns'); }$test->is(scalar($o->delete_columns(['junk'])), undef, 'could not delete unknown column'); $test->is($o->errcode, 'BDT-13', 'proper error code');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
{ my %icols = map {$_, 1} $o->replace_columns();
$test->is($icols{'able'}, 1, 'able is an replace col'); $test->is($icols{'baker'}, 1, 'baker is an replace col'); $test->is($icols{'charlie'}, 1, 'charlie is an replace col'); $test->is($icols{'delta'}, 1, 'delta is an replace col'); $test->is(scalar(keys %icols), 4, 'only 4 replace columns'); }{ my $icols = [qw(able charlie)];
$test->is(join(',',$o->replace_columns($icols)), join(',',@$icols), "set new replace columns"); $test->is(join(',',$o->replace_columns), join(',',@$icols), "got new replace columns"); my %icols = map {$_, 1} $o->replace_columns(); $test->is($icols{'able'}, 1, 'able is an replace col'); $test->is($icols{'baker'}, undef, 'baker is not an replace col'); $test->is($icols{'charlie'}, 1, 'charlie is an replace col'); $test->is($icols{'delta'}, undef, 'delta is not an replace col'); $test->is(scalar(keys %icols), 2, 'only 2 replace columns'); }{ my $icols = [qw()];
$o->replace_columns($icols); my %icols = map {$_, 1} $o->replace_columns(); $test->is($icols{'able'}, 1, 'able is an replace col'); $test->is($icols{'baker'}, 1, 'baker is an replace col'); $test->is($icols{'charlie'}, 1, 'charlie is an replace col'); $test->is($icols{'delta'}, 1, 'delta is an replace col'); $test->is(scalar(keys %icols), 4, '4 replace columns'); }{ $o->replace_columns(undef); my %icols = map {$_, 1} $o->replace_columns();
$test->is($icols{'able'}, 1, 'able is an replace col'); $test->is($icols{'baker'}, 1, 'baker is an replace col'); $test->is($icols{'charlie'}, 1, 'charlie is an replace col'); $test->is($icols{'delta'}, 1, 'delta is an replace col'); $test->is(scalar(keys %icols), 4, '4 replace columns'); }$test->is(scalar($o->replace_columns(['junk'])), undef, 'could not replace unknown column'); $test->is($o->errcode, 'BDT-13', 'proper error code');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
{ my %icols = map {$_, 1} $o->select_columns();
$test->is($icols{'able'}, 1, 'able is an select col'); $test->is($icols{'baker'}, 1, 'baker is an select col'); $test->is($icols{'charlie'}, 1, 'charlie is an select col'); $test->is($icols{'delta'}, 1, 'delta is an select col'); $test->is(scalar(keys %icols), 4, 'only 4 select columns'); }{ my $icols = [qw(able charlie)];
$test->is(join(',',$o->select_columns($icols)), join(',',@$icols), "set new select columns"); $test->is(join(',',$o->select_columns), join(',',@$icols), "got new select columns"); my %icols = map {$_, 1} $o->select_columns(); $test->is($icols{'able'}, 1, 'able is an select col'); $test->is($icols{'baker'}, undef, 'baker is not an select col'); $test->is($icols{'charlie'}, 1, 'charlie is an select col'); $test->is($icols{'delta'}, undef, 'delta is not an select col'); $test->is(scalar(keys %icols), 2, 'only 2 select columns'); }{ my $icols = [qw(able charlie)]; my $nicols = [qw(able)];
$test->is(join(',',$o->select_columns($icols)), join(',',@$icols), "set new select columns"); $test->is(join(',',$o->select_columns), join(',',@$icols), "got new select columns"); $test->is(join(',',$o->nonselect_columns($nicols)), join(',',@$nicols), "set new non-select columns"); $test->is(join(',',$o->nonselect_columns), join(',',@$nicols), "got new non-select columns"); my %icols = map {$_, 1} $o->select_columns(); $test->is($icols{'able'}, undef, 'able is a select col'); $test->is($icols{'baker'}, undef, 'baker is not an select col'); $test->is($icols{'charlie'}, 1, 'charlie is an select col'); $test->is($icols{'delta'}, undef, 'delta is not an select col'); $test->is(scalar(keys %icols), 1, 'only 1 select column'); }{ my $icols = [qw()];
$o->select_columns($icols); $o->nonselect_columns([]); my %icols = map {$_, 1} $o->select_columns(); $test->is($icols{'able'}, 1, 'able is an select col'); $test->is($icols{'baker'}, 1, 'baker is an select col'); $test->is($icols{'charlie'}, 1, 'charlie is an select col'); $test->is($icols{'delta'}, 1, 'delta is an select col'); $test->is(scalar(keys %icols), 4, '4 select columns'); }{ $o->select_columns(undef); my %icols = map {$_, 1} $o->select_columns();
$test->is($icols{'able'}, 1, 'able is an select col'); $test->is($icols{'baker'}, 1, 'baker is an select col'); $test->is($icols{'charlie'}, 1, 'charlie is an select col'); $test->is($icols{'delta'}, 1, 'delta is an select col'); $test->is(scalar(keys %icols), 4, '4 select columns'); }$test->is(scalar($o->select_columns(['junk'])), undef, 'could not select unknown column'); $test->is($o->errcode, 'BDT-13', 'proper error code');
$test->ok("testing is implied", "testing is implied");
my $o = __PACKAGE__->new(); $test->ok($o, "got object"); $test->is(ref $o->definition, 'HASH', 'definition initialized to hash'); $test->is(ref $o->extra_select, 'HASH', 'extra_select initialized to hash'); $test->is(ref $o->db_write_translation, 'HASH', 'db_write_translation initialized to hash'); $test->is(ref $o->db_read_translation, 'HASH', 'db_read_translation initialized to hash'); $test->is(ref $o->column_aliases, 'HASH', 'column_aliases initialized to hash'); $test->is(ref $o->references, 'HASH', 'references initialized to hash');
cols
Returns the columns defined for this table, in an unspecified order
my @cols = $table->cols();my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_VARCHAR', 'charlie' => 'SQL_DATE', 'delta' => 'SQL_UNKNOWN_TYPE' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %cols = map {$_, 1} $o->cols();
$test->is(scalar(keys %cols), scalar(keys %$def), "proper number of columns"); $test->is($cols{'able'}, 1, 'able is column'); $test->is($cols{'baker'}, 1, 'baker is column'); $test->is($cols{'charlie'}, 1, 'charlie is column'); $test->is($cols{'delta'}, 1, 'delta is column'); $test->is($cols{'edgar'}, undef, 'edgar is not column'); $test->is($cols{'foxtrot'}, undef, 'foxtrot is not column'); $test->is($cols{'goat'}, undef, 'goat is not column');
defs
Returns the column definitions defined for this table, in an unspecified order, but the same order as the columns returned by cols
my @defs = $table->defs();my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_VARCHAR', 'charlie' => 'SQL_DATE', 'delta' => 'SQL_UNKNOWN_TYPE' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my @cols = $o->cols(); my @defs = $o->defs();
$test->is(scalar(@defs), scalar(keys %$def), "proper number of definitions"); $test->is($defs[0], $o->definition->{$cols[0]}, "Definition matches column 0"); $test->is($defs[1], $o->definition->{$cols[1]}, "Definition matches column 1"); $test->is($defs[2], $o->definition->{$cols[2]}, "Definition matches column 2"); $test->is($defs[3], $o->definition->{$cols[3]}, "Definition matches column 3");
is_bindable
Fairly straightforward method, given a column and a query type, will tell you if the column is bindable.
$table->is_bindable('U', 'foo'); #returns 1 or 0, whether or not 'foo' can be bound on an update.Valid query types are 'U', 'I', 'R', 'D', 'S', and 'A'
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is(scalar($o->is_bindable()), undef, 'Cannot bind w/o type'); $test->is($o->errcode, 'BDT-31', 'proper error code for is_bindable (type)'); $test->is(scalar($o->is_bindable('able')), undef, 'Cannot bind w/o col'); $test->is($o->errcode, 'BDT-30', 'proper error code for is_bindable (col)');
$test->is($o->is_bindable('I', 'able'), 1, 'able binds on insert'); $test->is($o->is_bindable('I', 'baker'), 1, 'baker binds on insert'); $test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert'); $test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');
$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update'); $test->is($o->is_bindable('U', 'baker'), 1, 'baker binds on update'); $test->is($o->is_bindable('U', 'charlie'), 1, 'charlie binds on update'); $test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');
$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace'); $test->is($o->is_bindable('R', 'baker'), 1, 'baker binds on replace'); $test->is($o->is_bindable('R', 'charlie'), 1, 'charlie binds on replace'); $test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');
$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete'); $test->is($o->is_bindable('D', 'baker'), 1, 'baker binds on delete'); $test->is($o->is_bindable('D', 'charlie'), 1, 'charlie binds on delete'); $test->is($o->is_bindable('D', 'delta'), 1, 'delta binds on delete');
$test->is($o->is_bindable('S', 'able'), 1, 'able binds on select'); $test->is($o->is_bindable('S', 'baker'), 1, 'baker binds on select'); $test->is($o->is_bindable('S', 'charlie'), 1, 'charlie binds on select'); $test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');
$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all'); $test->is($o->is_bindable('A', 'baker'), 1, 'baker binds on all'); $test->is($o->is_bindable('A', 'charlie'), 1, 'charlie binds on all'); $test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');
my $translator = { 'able' => { 'I' => { 'val' => 'NOW()', 'binds' => 0 }, }, 'baker' => { 'U' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'charlie' => { 'R' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'delta' => { 'D' => { 'val' => 'NOW()', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator), $translator, "Set translator");
$test->is($o->is_bindable('I', 'able'), 0, 'able does not bind on insert'); $test->is($o->is_bindable('I', 'baker'), 1, 'baker binds on insert'); $test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert'); $test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');
$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update'); $test->is($o->is_bindable('U', 'baker'), 0, 'baker does not bind on update'); $test->is($o->is_bindable('U', 'charlie'), 1, 'charlie binds on update'); $test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');
$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace'); $test->is($o->is_bindable('R', 'baker'), 1, 'baker binds on replace'); $test->is($o->is_bindable('R', 'charlie'), 0, 'charlie does not bind on replace'); $test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');
$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete'); $test->is($o->is_bindable('D', 'baker'), 1, 'baker binds on delete'); $test->is($o->is_bindable('D', 'charlie'), 1, 'charlie binds on delete'); $test->is($o->is_bindable('D', 'delta'), 0, 'delta does not bind on delete');
$test->is($o->is_bindable('S', 'able'), 1, 'able binds on select'); $test->is($o->is_bindable('S', 'baker'), 1, 'baker binds on select'); $test->is($o->is_bindable('S', 'charlie'), 1, 'charlie binds on select'); $test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');
$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all'); $test->is($o->is_bindable('A', 'baker'), 1, 'baker binds on all'); $test->is($o->is_bindable('A', 'charlie'), 1, 'charlie binds on all'); $test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');
my $translator2 = { 'able' => { 'S' => { 'val' => 'NOW()', 'binds' => 0 }, }, 'baker' => { 'A' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'charlie' => { 'I' => { 'val' => 'NOW()', 'binds' => 1, }, 'A' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'delta' => { 'A' => { 'val' => '?', 'binds' => 1, } }, };
$test->is($o->db_write_translation($translator2), $translator2, "Set translator again");
$test->is($o->is_bindable('I', 'able'), 1, 'able binds on insert'); $test->is($o->is_bindable('I', 'baker'), 0, 'baker does not bind on insert'); $test->is($o->is_bindable('I', 'charlie'), 1, 'charlie binds on insert'); $test->is($o->is_bindable('I', 'delta'), 1, 'delta binds on insert');
$test->is($o->is_bindable('U', 'able'), 1, 'able binds on update'); $test->is($o->is_bindable('U', 'baker'), 0, 'baker does not bind on update'); $test->is($o->is_bindable('U', 'charlie'), 0, 'charlie does not bind on update'); $test->is($o->is_bindable('U', 'delta'), 1, 'delta binds on update');
$test->is($o->is_bindable('R', 'able'), 1, 'able binds on replace'); $test->is($o->is_bindable('R', 'baker'), 0, 'baker does not bind replace'); $test->is($o->is_bindable('R', 'charlie'), 0, 'charlie does not bind on replace'); $test->is($o->is_bindable('R', 'delta'), 1, 'delta binds on replace');
$test->is($o->is_bindable('D', 'able'), 1, 'able binds on delete'); $test->is($o->is_bindable('D', 'baker'), 0, 'baker does not bind delete'); $test->is($o->is_bindable('D', 'charlie'), 0, 'charlie does not bind on delete'); $test->is($o->is_bindable('D', 'delta'), 1, 'delta binds on delete');
$test->is($o->is_bindable('S', 'able'), 0, 'able does not bind on select'); $test->is($o->is_bindable('S', 'baker'), 0, 'baker does not bind on select'); $test->is($o->is_bindable('S', 'charlie'), 0, 'charlie does not bind on select'); $test->is($o->is_bindable('S', 'delta'), 1, 'delta binds on select');
$test->is($o->is_bindable('A', 'able'), 1, 'able binds on all'); $test->is($o->is_bindable('A', 'baker'), 0, 'baker does not bind on all'); $test->is($o->is_bindable('A', 'charlie'), 0, 'charlie does not bind on all'); $test->is($o->is_bindable('A', 'delta'), 1, 'delta binds on all');
is_selectable
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is(scalar($o->is_selectable), undef, 'Could not determine selectableness w/o value'); $test->is($o->errcode, 'BDT-44', 'proper error code');
$test->is($o->is_selectable('able'), 1, 'selects column'); $test->is($o->is_selectable('7'), 1, 'selects integer'); $test->is($o->is_selectable('7 as seven'), 1, 'selects aliased integer'); $test->is($o->is_selectable('98.6'), 1, 'selects float'); $test->is($o->is_selectable('98.6 as temp'), 1, 'selects aliased float'); $test->is($o->is_selectable('.778'), 1, 'selects decimal started float'); $test->is($o->is_selectable('.778 as small'), 1, 'selects decimal started float'); $test->is($o->is_selectable("'string'"), 1, 'selects single quoted string'); $test->is($o->is_selectable("'string' as alias"), 1, 'selects aliased single quoted string'); $test->is($o->is_selectable('"string"'), 1, 'selects double quoted string'); $test->is($o->is_selectable('"string" as alias'), 1, 'selects aliased double quoted string'); $test->is($o->is_selectable('NOW()'), 1, 'selects empty function'); $test->is($o->is_selectable('NOW() as "now"'), 1, 'selects aliased empty function'); $test->is($o->is_selectable('lc("able")'), 1, 'selects single arg function'); $test->is($o->is_selectable('lc("able") as "lc able"'), 1, 'selects aliased single arg function'); $test->is($o->is_selectable('lc("able")'), 1, 'selects double arg function'); $test->is($o->is_selectable('lc("able") as "lc able"'), 1, 'selects aliased double arg function');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is(scalar($o->db_translate_write()), undef, 'Cannot db_translate_write w/o type'); $test->is($o->errcode, 'BDT-33', 'proper error code for db_translate_write (col)'); $test->is(scalar($o->db_translate_write('I')), undef, 'Cannot db_translate_write w/o cols'); $test->is($o->errcode, 'BDT-32', 'proper error code for db_translate_write (type)');
$test->is($o->db_translate_write('I', 'able'), '?', 'db_translate_write for able on insert'); $test->is($o->db_translate_write('I', 'baker'), '?', 'db_translate_write for baker on insert'); $test->is($o->db_translate_write('I', 'charlie'), '?', 'db_translate_write for charlie on insert'); $test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');
$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update'); $test->is($o->db_translate_write('U', 'baker'), '?', 'db_translate_write for baker on update'); $test->is($o->db_translate_write('U', 'charlie'), '?', 'db_translate_write for charlie on update'); $test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');
$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace'); $test->is($o->db_translate_write('R', 'baker'), '?', 'db_translate_write for baker on replace'); $test->is($o->db_translate_write('R', 'charlie'), '?', 'db_translate_write for charlie on replace'); $test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');
$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete'); $test->is($o->db_translate_write('D', 'baker'), '?', 'db_translate_write for baker on delete'); $test->is($o->db_translate_write('D', 'charlie'), '?', 'db_translate_write for charlie on delete'); $test->is($o->db_translate_write('D', 'delta'), '?', 'db_translate_write for delta on delete');
$test->is($o->db_translate_write('S', 'able'), '?', 'db_translate_write for able on select'); $test->is($o->db_translate_write('S', 'baker'), '?', 'db_translate_write for baker on select'); $test->is($o->db_translate_write('S', 'charlie'), '?', 'db_translate_write for charlie on select'); $test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');
$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all'); $test->is($o->db_translate_write('A', 'baker'), '?', 'db_translate_write for baker on all'); $test->is($o->db_translate_write('A', 'charlie'), '?', 'db_translate_write for charlie on all'); $test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');
{ my @insert_all = $o->db_translate_write('I', sort $o->cols); $test->is($insert_all[0], '?', 'able is ? on insert in multi pass'); $test->is($insert_all[1], '?', 'baker is ? on insert in multi pass'); $test->is($insert_all[2], '?', 'charlie is ? on insert in multi pass'); $test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
my @update_all = $o->db_translate_write('U', sort $o->cols); $test->is($update_all[0], '?', 'able is ? on update in multi pass'); $test->is($update_all[1], '?', 'baker is ? on update in multi pass'); $test->is($update_all[2], '?', 'charlie is ? on update in multi pass'); $test->is($update_all[3], '?', 'delta is ? on update in multi pass'); my @replace_all = $o->db_translate_write('R', sort $o->cols); $test->is($replace_all[0], '?', 'able is ? on replace in multi pass'); $test->is($replace_all[1], '?', 'baker is ? on replace in multi pass'); $test->is($replace_all[2], '?', 'charlie is ? on replace in multi pass'); $test->is($replace_all[3], '?', 'delta is ? on replace in multi pass'); my @delete_all = $o->db_translate_write('D', sort $o->cols); $test->is($delete_all[0], '?', 'able is ? on delete in multi pass'); $test->is($delete_all[1], '?', 'baker is ? on delete in multi pass'); $test->is($delete_all[2], '?', 'charlie is ? on delete in multi pass'); $test->is($delete_all[3], '?', 'delta is ? on delete in multi pass'); my @all_all = $o->db_translate_write('A', sort $o->cols); $test->is($all_all[0], '?', 'able is ? on all in multi pass'); $test->is($all_all[1], '?', 'baker is ? on all in multi pass'); $test->is($all_all[2], '?', 'charlie is ? on all in multi pass'); $test->is($all_all[3], '?', 'delta is ? on all in multi pass'); }my $translator = { 'able' => { 'I' => { 'val' => 'ableprime', 'binds' => 0 }, }, 'baker' => { 'U' => { 'val' => 'bakerprime', 'binds' => 0, }, }, 'charlie' => { 'R' => { 'val' => 'charlieprime', 'binds' => 1, }, }, 'delta' => { 'D' => { 'val' => 'deltaprime', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator), $translator, "Set translator");
$test->is($o->db_translate_write('I', 'able'), 'ableprime', 'db_translate_write for able on insert'); $test->is($o->db_translate_write('I', 'baker'), '?', 'db_translate_write for baker on insert'); $test->is($o->db_translate_write('I', 'charlie'), '?', 'db_translate_write for charlie on insert'); $test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');
$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update'); $test->is($o->db_translate_write('U', 'baker'), 'bakerprime', 'db_translate_write for baker on update'); $test->is($o->db_translate_write('U', 'charlie'), '?', 'db_translate_write for charlie on update'); $test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');
$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace'); $test->is($o->db_translate_write('R', 'baker'), '?', 'db_translate_write for baker on replace'); $test->is($o->db_translate_write('R', 'charlie'), 'charlieprime', 'db_translate_write for charlie on replace'); $test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');
$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete'); $test->is($o->db_translate_write('D', 'baker'), '?', 'db_translate_write for baker on delete'); $test->is($o->db_translate_write('D', 'charlie'), '?', 'db_translate_write for charlie on delete'); $test->is($o->db_translate_write('D', 'delta'), 'deltaprime', 'db_translate_write for delta on delete');
$test->is($o->db_translate_write('S', 'able'), '?', 'db_translate_write for able on select'); $test->is($o->db_translate_write('S', 'baker'), '?', 'db_translate_write for baker on select'); $test->is($o->db_translate_write('S', 'charlie'), '?', 'db_translate_write for charlie on select'); $test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');
$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all'); $test->is($o->db_translate_write('A', 'baker'), '?', 'db_translate_write for baker on all'); $test->is($o->db_translate_write('A', 'charlie'), '?', 'db_translate_write for charlie on all'); $test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');
{ my @insert_all = $o->db_translate_write('I', sort $o->cols); $test->is($insert_all[0], 'ableprime', 'able is ableprime on insert in multi pass'); $test->is($insert_all[1], '?', 'baker is ? on insert in multi pass'); $test->is($insert_all[2], '?', 'charlie is ? on insert in multi pass'); $test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
my @update_all = $o->db_translate_write('U', sort $o->cols); $test->is($update_all[0], '?', 'able is ? on update in multi pass'); $test->is($update_all[1], 'bakerprime', 'baker is bakerprime on update in multi pass'); $test->is($update_all[2], '?', 'charlie is ? on update in multi pass'); $test->is($update_all[3], '?', 'delta is ? on update in multi pass'); my @replace_all = $o->db_translate_write('R', sort $o->cols); $test->is($replace_all[0], '?', 'able is ? on replace in multi pass'); $test->is($replace_all[1], '?', 'baker is ? on replace in multi pass'); $test->is($replace_all[2], 'charlieprime', 'charlie is charlieprime on replace in multi pass'); $test->is($replace_all[3], '?', 'delta is ? on replace in multi pass'); my @delete_all = $o->db_translate_write('D', sort $o->cols); $test->is($delete_all[0], '?', 'able is ? on delete in multi pass'); $test->is($delete_all[1], '?', 'baker is ? on delete in multi pass'); $test->is($delete_all[2], '?', 'charlie is ? on delete in multi pass'); $test->is($delete_all[3], 'deltaprime', 'delta is deltaprime on delete in multi pass'); my @all_all = $o->db_translate_write('A', sort $o->cols); $test->is($all_all[0], '?', 'able is ? on all in multi pass'); $test->is($all_all[1], '?', 'baker is ? on all in multi pass'); $test->is($all_all[2], '?', 'charlie is ? on all in multi pass'); $test->is($all_all[3], '?', 'delta is ? on all in multi pass'); }my $translator2 = { 'able' => { 'S' => { 'val' => 'ableselectorprime', 'binds' => 0 }, }, 'baker' => { 'A' => { 'val' => 'bakerallprime', 'binds' => 0, }, }, 'charlie' => { 'I' => { 'val' => 'charlieinsertprime', 'binds' => 1, }, 'A' => { 'val' => 'charlieallprime', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator2), $translator2, "Set translator again");
$test->is($o->db_translate_write('I', 'able'), '?', 'db_translate_write for able on insert'); $test->is($o->db_translate_write('I', 'baker'), 'bakerallprime', 'db_translate_write for baker on insert'); $test->is($o->db_translate_write('I', 'charlie'), 'charlieinsertprime', 'db_translate_write for charlie on insert'); $test->is($o->db_translate_write('I', 'delta'), '?', 'db_translate_write for delta on insert');
$test->is($o->db_translate_write('U', 'able'), '?', 'db_translate_write for able on update'); $test->is($o->db_translate_write('U', 'baker'), 'bakerallprime', 'db_translate_write for baker on update'); $test->is($o->db_translate_write('U', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on update'); $test->is($o->db_translate_write('U', 'delta'), '?', 'db_translate_write for delta on update');
$test->is($o->db_translate_write('R', 'able'), '?', 'db_translate_write for able on replace'); $test->is($o->db_translate_write('R', 'baker'), 'bakerallprime', 'db_translate_write for baker on replace'); $test->is($o->db_translate_write('R', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on replace'); $test->is($o->db_translate_write('R', 'delta'), '?', 'db_translate_write for delta on replace');
$test->is($o->db_translate_write('D', 'able'), '?', 'db_translate_write for able on delete'); $test->is($o->db_translate_write('D', 'baker'), 'bakerallprime', 'db_translate_write for baker on delete'); $test->is($o->db_translate_write('D', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on delete'); $test->is($o->db_translate_write('D', 'delta'), '?', 'db_translate_write for delta on delete');
$test->is($o->db_translate_write('S', 'able'), 'ableselectorprime', 'db_translate_write for able on select'); $test->is($o->db_translate_write('S', 'baker'), 'bakerallprime', 'db_translate_write for baker on select'); $test->is($o->db_translate_write('S', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on select'); $test->is($o->db_translate_write('S', 'delta'), '?', 'db_translate_write for delta on select');
$test->is($o->db_translate_write('A', 'able'), '?', 'db_translate_write for able on all'); $test->is($o->db_translate_write('A', 'baker'), 'bakerallprime', 'db_translate_write for baker on all'); $test->is($o->db_translate_write('A', 'charlie'), 'charlieallprime', 'db_translate_write for charlie on all'); $test->is($o->db_translate_write('A', 'delta'), '?', 'db_translate_write for delta on all');
{ my @insert_all = $o->db_translate_write('I', sort $o->cols); $test->is($insert_all[0], '?', 'able is ? on insert in multi pass'); $test->is($insert_all[1], 'bakerallprime', 'baker is bakerallprime on insert in multi pass'); $test->is($insert_all[2], 'charlieinsertprime', 'charlie is charlieinsertprime on insert in multi pass'); $test->is($insert_all[3], '?', 'delta is ? on insert in multi pass');
my @update_all = $o->db_translate_write('U', sort $o->cols); $test->is($update_all[0], '?', 'able is ? on update in multi pass'); $test->is($update_all[1], 'bakerallprime', 'baker is bakerallprime on update in multi pass'); $test->is($update_all[2], 'charlieallprime', 'charlie is charlieallprime on update in multi pass'); $test->is($update_all[3], '?', 'delta is ? on update in multi pass'); my @replace_all = $o->db_translate_write('R', sort $o->cols); $test->is($replace_all[0], '?', 'able is ? on replace in multi pass'); $test->is($replace_all[1], 'bakerallprime', 'baker is bakerallprime on replace in multi pass'); $test->is($replace_all[2], 'charlieallprime', 'charlie is charlieallprime on replace in multi pass'); $test->is($replace_all[3], '?', 'delta is ? on replace in multi pass'); my @delete_all = $o->db_translate_write('D', sort $o->cols); $test->is($delete_all[0], '?', 'able is ? on delete in multi pass'); $test->is($delete_all[1], 'bakerallprime', 'baker is bakerallprime on delete in multi pass'); $test->is($delete_all[2], 'charlieallprime', 'charlie is charlieallprime on delete in multi pass'); $test->is($delete_all[3], '?', 'delta is ? on delete in multi pass'); my @all_all = $o->db_translate_write('A', sort $o->cols); $test->is($all_all[0], '?', 'able is ? on all in multi pass'); $test->is($all_all[1], 'bakerallprime', 'baker is bakerallprime on all in multi pass'); $test->is($all_all[2], 'charlieallprime', 'charlie is charlieallprime on all in multi pass'); $test->is($all_all[3], '?', 'delta is ? on all in multi pass'); }my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition"); $test->is($o->name('table'), 'table', 'Set table name');
$test->is(scalar($o->db_translate_read()), undef, 'Cannot db_translate_read w/o col'); $test->is($o->errcode, 'BDT-34', 'proper error code for db_translate_read (col)');
$test->is($o->db_translate_read('able'), 'table.able', 'db_translate_read for able on select'); $test->is($o->db_translate_read('baker'), 'table.baker', 'db_translate_read for baker on select'); $test->is($o->db_translate_read('charlie'), 'table.charlie', 'db_translate_read for charlie on select'); $test->is($o->db_translate_read('delta'), 'table.delta', 'db_translate_read for delta on select');
my $translator = { 'able' => 'ableselectorprime', 'baker' => 'bakerallprime', 'charlie' => 'charlieselectprime', };
$test->is($o->db_read_translation($translator), $translator, "Set translator again");
$test->is($o->db_translate_read('able'), 'ableselectorprime', 'db_translate_read for able on select'); $test->is($o->db_translate_read('baker'), 'bakerallprime', 'db_translate_read for baker on select'); $test->is($o->db_translate_read('charlie'), 'charlieselectprime', 'db_translate_read for charlie on select'); $test->is($o->db_translate_read('delta'), 'table.delta', 'db_translate_read for delta on select');
alias_column
Returns the aliased version of the column if one is defined in the column_aliases hash. Returns the column otherwise.
$table->column_aliases( { 'id' => 'user_id' } ); print $table->alias_column('id'); #prints user_id (uses alias) print $table->alias_column('name'); #prints name (no alias)my $o = __PACKAGE__->new('name' => 'testtable1'); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
my $aliases = { 'able' => 'aliased_able', 'baker' => 'aliased_baker', 'charlie' => 'aliased_charlie', };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->column_aliases($aliases), $aliases, "Set column aliases"); $test->is($o->column_aliases(), $aliases, "Got column aliases");
$test->is(scalar($o->alias_column()), undef, "Could not alias_column w/o column"); $test->is($o->errcode, 'BDT-36', 'proper error code for alias_column (col)');
$test->is($o->alias_column('able'), 'aliased_able', 'properly aliased able'); $test->is($o->alias_column('baker'), 'aliased_baker', 'properly aliased baker'); $test->is($o->alias_column('charlie'), 'aliased_charlie', 'properly aliased charlie'); $test->is($o->alias_column('delta'), 'delta', 'properly aliased able (no alias)');
$test->is($o->alias_column('testtable1.able'), 'aliased_able', 'properly aliased able'); $test->is($o->alias_column('testtable1.baker'), 'aliased_baker', 'properly aliased baker'); $test->is($o->alias_column('testtable1.charlie'), 'aliased_charlie', 'properly aliased charlie'); $test->is($o->alias_column('testtable1.delta'), 'delta', 'properly aliased able (no alias)');
column_for_alias
Returns the non-aliased version of the column if one is defined in the column_aliases hash. Returns the column otherwise.
$table->column_aliases( { 'id' => 'user_id' } ); print $table->alias_column('user_id'); #prints id (undoes alias) print $table->alias_column('name'); #prints name (no alias)my $o = __PACKAGE__->new('name' => 'testtable2'); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
my $aliases = { 'able' => 'aliased_able', 'baker' => 'aliased_baker', 'charlie' => 'aliased_charlie', };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->column_aliases($aliases), $aliases, "Set column aliases"); $test->is($o->column_aliases(), $aliases, "Got column aliases");
$test->is(scalar($o->column_for_alias()), undef, "Could not get column_for_alias w/o alias"); $test->is($o->errcode, 'BDT-35', 'proper error code for column_for_alias (alias)');
$test->is($o->column_for_alias('aliased_able'), 'able', 'properly unaliased able'); $test->is($o->column_for_alias('aliased_baker'), 'baker', 'properly unaliased baker'); $test->is($o->column_for_alias('aliased_charlie'), 'charlie', 'unproperly aliased charlie'); $test->is($o->column_for_alias('delta'), 'delta', 'properly aliased able (no alias)');
$test->is($o->column_for_alias('testtable2.aliased_able'), 'able', 'properly unaliased able'); $test->is($o->column_for_alias('testtable2.aliased_baker'), 'baker', 'properly unaliased baker'); $test->is($o->column_for_alias('testtable2.aliased_charlie'), 'charlie', 'unproperly aliased charlie'); $test->is($o->column_for_alias('testtable2.delta'), 'delta', 'properly aliased able (no alias)');
insert_bindables
Returns the columns in this table that should be bound with values upon an insert.
my @insertables = $table->insert_bindables();my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %bindable = map {$_, 1} $o->insert_bindables();
$test->is($bindable{'able'}, 1, 'able is insert bindable'); $test->is($bindable{'baker'}, 1, 'baker is insert bindable'); $test->is($bindable{'charlie'}, 1, 'charlie is insert bindable'); $test->is($bindable{'delta'}, 1, 'delta is insert bindable');
my $translator = { 'able' => { 'I' => { 'val' => 'lc(?)', 'binds' => 1 }, 'A' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'baker' => { 'I' => { 'val' => 'NOW()', 'binds' => 0 }, 'A' => { 'val' => '?', 'binds' => 1, }, }, 'charlie' => { 'I' => { 'val' => 'NOW()', 'binds' => 0 }, }, 'delta' => { 'A' => { 'val' => 'NOW()', 'binds' => 0 }, }, };
$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');
my %bindable2 = map {$_, 1} $o->insert_bindables();
$test->is($bindable2{'able'}, 1, 'able is insert bindable'); $test->is($bindable2{'baker'}, undef, 'baker is not insert bindable'); $test->is($bindable2{'charlie'}, undef, 'charlie is not insert bindable'); $test->is($bindable2{'delta'}, undef, 'delta is not insert bindable');
replace_bindables
Returns the columns in this table that should be bound with values upon a replace.
my @replaceables = $table->replace_bindables();my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %bindable = map {$_, 1} $o->replace_bindables();
$test->is($bindable{'able'}, 1, 'able is replace bindable'); $test->is($bindable{'baker'}, 1, 'baker is replace bindable'); $test->is($bindable{'charlie'}, 1, 'charlie is replace bindable'); $test->is($bindable{'delta'}, 1, 'delta is replace bindable');
my $translator = { 'able' => { 'R' => { 'val' => 'lc(?)', 'binds' => 1 }, 'A' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'baker' => { 'R' => { 'val' => 'NOW()', 'binds' => 0 }, 'A' => { 'val' => '?', 'binds' => 1, }, }, 'charlie' => { 'R' => { 'val' => 'NOW()', 'binds' => 0 }, }, 'delta' => { 'A' => { 'val' => 'NOW()', 'binds' => 0 }, }, };
$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');
my %bindable2 = map {$_, 1} $o->replace_bindables();
$test->is($bindable2{'able'}, 1, 'able is replace bindable'); $test->is($bindable2{'baker'}, undef, 'baker is not replace bindable'); $test->is($bindable2{'charlie'}, undef, 'charlie is not replace bindable'); $test->is($bindable2{'delta'}, undef, 'delta is not replace bindable');
update_bindables
Returns the columns in this table that should be bound with values upon an update.
my @updatables = $table->update_bindables();my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %bindable = map {$_, 1} $o->update_bindables();
$test->is($bindable{'able'}, 1, 'able is update bindable'); $test->is($bindable{'baker'}, 1, 'baker is update bindable'); $test->is($bindable{'charlie'}, 1, 'charlie is update bindable'); $test->is($bindable{'delta'}, 1, 'delta is update bindable');
my $translator = { 'able' => { 'U' => { 'val' => 'lc(?)', 'binds' => 1 }, 'A' => { 'val' => 'NOW()', 'binds' => 0, }, }, 'baker' => { 'U' => { 'val' => 'NOW()', 'binds' => 0 }, 'A' => { 'val' => '?', 'binds' => 1, }, }, 'charlie' => { 'U' => { 'val' => 'NOW()', 'binds' => 0 }, }, 'delta' => { 'A' => { 'val' => 'NOW()', 'binds' => 0 }, }, };
$test->is($o->db_write_translation($translator), $translator, 'set db_write_translation');
my %bindable2 = map {$_, 1} $o->update_bindables();
$test->is($bindable2{'able'}, 1, 'able is update bindable'); $test->is($bindable2{'baker'}, undef, 'baker is not update bindable'); $test->is($bindable2{'charlie'}, undef, 'charlie is not update bindable'); $test->is($bindable2{'delta'}, undef, 'delta is not update bindable');
delete_bindables
Returns the columns in this table that should be bound with values upon an delete.
my @deletables = $table->delete_bindables();my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %bindable = map {$_, 1} grep {defined} $o->delete_bindables();
$test->is($bindable{'able'}, undef, 'able is not delete bindable'); $test->is($bindable{'baker'}, undef, 'baker is not delete bindable'); $test->is($bindable{'charlie'}, undef, 'charlie is not delete bindable'); $test->is($bindable{'delta'}, undef, 'delta is not delete bindable');
$o->primary_column('able');
my %bindable2 = map {$_, 1} $o->delete_bindables();
$test->is($bindable2{'able'}, 1, 'able is delete bindable'); $test->is($bindable2{'baker'}, undef, 'baker is not delete bindable'); $test->is($bindable2{'charlie'}, undef, 'charlie is not delete bindable'); $test->is($bindable2{'delta'}, undef, 'delta is not delete bindable');
$o->primary_column(['charlie', 'delta']);
my %bindable3 = map {$_, 1} $o->delete_bindables();
$test->is($bindable3{'able'}, undef, 'able is not delete bindable'); $test->is($bindable3{'baker'}, undef, 'baker is not delete bindable'); $test->is($bindable3{'charlie'}, 1, 'charlie is delete bindable'); $test->is($bindable3{'delta'}, 1, 'delta is delete bindable');
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
my %bindable = map {$_, 1} grep {defined} $o->select_bindables;
$test->is($bindable{'able'}, undef, 'able is not select bindable'); $test->is($bindable{'baker'}, undef, 'baker is not select bindable'); $test->is($bindable{'charlie'}, undef, 'charlie is not select bindable'); $test->is($bindable{'delta'}, undef, 'delta is not select bindable');
$o->primary_column('able');
my %bindable2 = map {$_, 1} $o->select_bindables();
$test->is($bindable2{'able'}, 1, 'able is select bindable'); $test->is($bindable2{'baker'}, undef, 'baker is not select bindable'); $test->is($bindable2{'charlie'}, undef, 'charlie is not select bindable'); $test->is($bindable2{'delta'}, undef, 'delta is not select bindable');
$o->primary_column(['charlie', 'delta']);
my %bindable3 = map {$_, 1} $o->select_bindables();
$test->is($bindable3{'able'}, undef, 'able is not select bindable'); $test->is($bindable3{'baker'}, undef, 'baker is not select bindable'); $test->is($bindable3{'charlie'}, 1, 'charlie is select bindable'); $test->is($bindable3{'delta'}, 1, 'delta is select bindable');
insert_query
Returns an insert query for this table.
my $insert_query = $table->insert_query();The query is a full insert with columns defined in the query. You may also pass in an array of columns to use in the insert. Otherwise, all columns defined in the table will be used.
my $insert_qery = $table->insert_query('foo');Returns the insert query but only to be able to insert into column 'foo'. If you try to use a column that is not in the table, you'll get an error.
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->name('mytable'), 'mytable', "set tablename");
{ my @insert = $o->insert_columns;
$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert) . ") values (?, ?, ?, ?)", "got default insert query"); }{ my @insert = $o->insert_columns(['able']);
$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert) . ") values (?)", "got able insert query"); $o->insert_columns(undef); }{ my @insert = $o->insert_columns(['able', 'baker']);
$test->is($o->insert_query, "insert into mytable (" . join(', ', @insert) . ") values (?, ?)", "got able, baker insert query"); $o->insert_columns(undef); }{
$test->is(scalar($o->insert_query('foo')), undef, "Could not get insert query w/invalid column"); $test->is($o->errcode, 'BDT-07', 'proper error code'); }{ $test->is($o->insert_query('able', 'baker'), "insert into mytable (able, baker) values (?, ?)", "got able, baker insert query"); }
{ my $translator = { 'able' => { 'I' => { 'val' => 'lc(?)', 'binds' => 0 }, }, 'baker' => { 'A' => { 'val' => 'uc(?)', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator), $translator, "Set translator"); my @insert = $o->insert_columns; my $q = "insert into mytable (" . join(', ', @insert) . ") values (" . join(', ', map{$_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?'} @insert) . ")"; $test->is($o->insert_query, $q, "got re-written default insert query");}
replace_query
Returns an replace query for this table.
my $replace_query = $table->replace_query();The query is a full replace with columns defined in the query. You may also pass in an array of columns to use in the insert. Otherwise, all columns defined in the table will be used.
my $replace_qery = $table->replace_query('foo');Returns the replace query but only to be able to replace into column 'foo'. If you try to use a column that is not in the table, you'll get an error.
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->name('mytable'), 'mytable', "set tablename");
{ my @replace = $o->replace_columns;
$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace) . ") values (?, ?, ?, ?)", "got default replace query"); }{ my @replace = $o->replace_columns(['able']);
$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace) . ") values (?)", "got able replace query"); $o->replace_columns(undef); }{ my @replace = $o->replace_columns(['able', 'baker']);
$test->is($o->replace_query, "replace into mytable (" . join(', ', @replace) . ") values (?, ?)", "got able, baker replace query"); $o->replace_columns(undef); }{
$test->is(scalar($o->replace_query('foo')), undef, "Could not get replace query w/invalid column"); $test->is($o->errcode, 'BDT-08', 'proper error code'); }{ $test->is($o->replace_query('able', 'baker'), "replace into mytable (able, baker) values (?, ?)", "got able, baker replace query"); }
{ my $translator = { 'able' => { 'R' => { 'val' => 'lc(?)', 'binds' => 0 }, }, 'baker' => { 'A' => { 'val' => 'uc(?)', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator), $translator, "Set translator"); my @replace = $o->replace_columns; my $q = "replace into mytable (" . join(', ', @replace) . ") values (" . join(', ', map{$_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?'} @replace) . ")"; $test->is($o->replace_query, $q, "got re-written default replace query");}
update_query
Returns an update_query query for this table.
my $update_query = $table->update_query();The query is a full update with columns defined in the query. You may also pass in an array of columns to use in the insert. Otherwise, all columns defined in the table will be used.
my $update_query = $table->update_query('foo');Returns the update query but only to be able to update column 'foo'. If you try to use a column that is not in the table, you'll get an error.
Be warned that no where clause is attached
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->name('mytable'), 'mytable', "set tablename");
{ my @update = $o->update_columns;
$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got default update query"); }{ my @update = $o->update_columns(['able']);
$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got able update query"); $o->update_columns(undef); }{ my @update = $o->update_columns(['able', 'baker']);
$test->is($o->update_query, "update mytable set " . join(', ', map {"$_ = ?"} @update), "got able, baker update query"); $o->update_columns(undef); }{
$test->is(scalar($o->update_query('foo')), undef, "Could not get update query w/invalid column"); $test->is($o->errcode, 'BDT-06', 'proper error code'); }{ $test->is($o->update_query('able', 'baker'), "update mytable set able = ?, baker = ?", "got able, baker update query"); }
{ my $translator = { 'able' => { 'U' => { 'val' => 'lc(?)', 'binds' => 0 }, }, 'baker' => { 'A' => { 'val' => 'uc(?)', 'binds' => 0, }, }, };
$test->is($o->db_write_translation($translator), $translator, "Set translator"); my @update = $o->update_columns; my $q = "update mytable set " . join(', ', map{$_ . ' = ' . ($_ eq 'able' ? 'lc(?)' : $_ eq 'baker' ? 'uc(?)' : '?')} @update); $test->is($o->update_query, $q, "got re-written default update query");}
delete_query
returns a delete query for this table.
my $delete_query = $table->delete_queryBe warned that no where clause is attached
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
$test->is($o->name('mytable'), 'mytable', "set tablename");
$test->is($o->delete_query, 'delete from mytable', 'proper delete query');
select_query
Returns an select_query query for this table.
my $select_query = $table->select_query();The query is a full update with columns defined in the query. You may also pass in an array of columns to use in the select. Otherwise, all columns defined in the table will be used.
my $select_query = $table->select_query('foo');Returns the select query but only to be able to select column 'foo'. If you try to use a column that is not in the table, you'll get an error.
Be warned that no where clause is attached
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->name('mytable'), 'mytable', "set tablename");
$test->is(scalar($o->select_query('fake')), undef, 'Could not select unknown column'); $test->is($o->errcode, 'BDT-05', 'proper error code');
{ my @select = $o->select_columns;
$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got default select query");}
{ my @select = $o->select_columns(['able']);
$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got able select query"); $o->select_columns(undef); }{ my @select = $o->select_columns(['able', 'baker']);
$test->is($o->select_query, 'select ' . join(', ', map {"mytable.$_ as $_"} @select) . ' from mytable', "got able baker select query"); $o->select_columns(undef); }{ $test->is($o->select_query('able', 'baker'), 'select mytable.able as able, mytable.baker as baker from mytable', 'got able baker passed select query'); }
{ $test->is($o->select_query('NOW()', '"const1"', "'const2'", 2004), 'select NOW(), "const1", \'const2\', 2004 from mytable', 'got function, constant select query'); }
{ my $translator = { 'able' => 'lc(able)', 'baker' => 'uc(baker)', };
my @select = $o->select_columns(); $test->is($o->db_read_translation($translator), $translator, "Set translator"); $test->is($o->select_query, 'select ' . join(', ', map{($_ eq 'able' ? 'lc(able)' : $_ eq 'baker' ? 'uc(baker)' : "mytable.$_") . " as $_"} @select) . ' from mytable', "got translated select query"); $o->db_read_translation({}); }{ my $extra = { 'current_time' => 'NOW()' };
$test->is($o->extra_select($extra), $extra, "Set extra select"); my @select = ((map {"mytable.$_ as $_"} $o->select_columns), (map {$extra->{$_} . ' as ' . $_} keys %$extra)); $test->is($o->select_query, 'select ' . join(', ', @select) . ' from mytable', "got extra selecting select query"); };multiselect_query
Magic time. The multiselect_query allows you to auto-build and execute select queries across multiple tables. Expects up to two arguments, in a hash.
- tables
The table objects that will be joined in this select statement. You need at least one table, but if you're only selecting one table, you should probably just use its select_query.
- cols
The list of columns to select in the join. If this is not specified, then all columns in all tables will be used. NOTE THAT COLUMN ALIASES WILL NOT BE USED UNLESS YOU PASS THE use_aliases flag.
$table->multiselect_query('tables' => $tables, 'use_aliases' => 1);This is by design, it is assumed that most of the time, you're using a multi select query when doing an arbitrary_sql call to get back massive amounts of data and you need to know the original column name, and the table it was from.
Most of the time, hiding behind Basset's object persistence capabilities are more than sufficient. You can load up objects, manipulate them, write them back out. Everything's peachy. But some of the time, you just need data. Lots of data. And you need it fast. Real fast. Basset doesn't deal well with that.
Let's say you have a table of users and a table (that serves as a log) of login information. Each time the user logs in, you insert an entry into the login table. You want to get a list of all users and the number of times they've logged in.
You can do this with the standard methods.
my $users = Some::User->load_all(); foreach my $user (@$users) { print $user->name, " logged in : ", $user->logininformation, "\n"; #assuming logininformation wrappers what we want }But there's a lot of overhead involved in that and it's not necessarily the fastest way to do it. Sure, in this case, it makes sense. But it might not always. So, instead, you can do a multiselect_query. Let's define the tables for clarity, and we'll even assume they're in different packages.
my $user_table = Basset::DB::Table->new( 'name' => 'user', 'primary_column' => 'id', 'definition' => { 'id' => 'SQL_INTEGER', 'name' => 'SQL_VARCHAR' } ); my $login_table = Basset::DB::Table->new( 'name' => 'login', 'primary_column' => 'id', 'definition' => { 'id' => 'SQL_INTEGER' 'user_id' => 'SQL_INTEGER', 'login_time'=> 'SQL_TIMESTAMP' }, 'references' => { 'user_id' => 'user.id' } ); my $q = Basset::DB::Table->multiselect_query( 'tables' => [$user_table, $login_table], ); print "$q\n";This prints out:
select user.name, user.id, login.login_time, login.user_id, login.id, from user inner join login on user.id = login.user_idSo now we have one query that will get us back all of our data. But we're still yanking back too much. We actually only care about the user and the total login info. We can fix that by specifying the columns we want. Please note that you need to qualify the column names.
my $q = Basset::DB::Table->multiselect_query( 'tables' => [$user_table, $login_table], 'cols' => [qw[user.id user.name count(*)]] ) or die Basset::DB::Table->errstring; print "$q\n";This prints out:
select user.id, user.name, count(*) from user inner join login on user.id = login.user_idCloser, but still not quite there. For one thing, this will ignore any users that have never logged in, since they don't have an entry in the login table. Easy to fix, specify the join type:
my $q = Basset::DB::Table->multiselect_query( 'tables' => [ $user_table, ['left', $login_table] ], 'cols' => [qw[user.id name], 'coalesce(count(*), 0) as count'], ) or die Basset::DB::Table->errstring; print "$q\n";This prints out:
select user.id as id, user.name as name, coalesce(count(*), 0) as count from user left join login on user.id = login.user_idThat's all of the data we want, but we're still missing something - the group by clause. So we attach one. We'll even tack on an order by clause for good measure so we don't need to sort later.
my $q = Basset::DB::Table->attach_to_query( Basset::DB::Table->multiselect_query( 'tables' => [ $user_table, ['left', $login_table] ], 'cols' => [qw[user.id name], 'coalesce(count(*), 0) as count'], ) , { 'group by' => 'user.id, name', 'order by' => 'count', } ); print "$q\n";This prints out:
select user.id as id, user.name as name, coalesce(count(*), 0) as count from user left join login on user.id = login.user_id group by user.id, name order by countAnd voila! We're done. Hand that query off to whatever method it is you use to run sql queries (such as Basset::Object::Persistent's arbitrary_sql method), get back your data, and you're all set.
my $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $o2 = __PACKAGE__->new(); $test->ok($o2, "Created object");
my $o3 = __PACKAGE__->new(); $test->ok($o3, "Created object");
my $o4 = __PACKAGE__->new(); $test->ok($o4, "Created object");
my $def1 = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
my $ref1 = { 'able' => 'table2.aid', 'baker' => 'table4.b', 'charlie' => 'table4.c', };
$test->is($o->definition($def1), $def1, "Set definition"); $test->is($o->references($ref1), $ref1, "Set references"); $test->is($o->name("table1"), "table1", "set table name"); $test->is($o->primary_column("able"), "able", "set table primary id");
my $def2 = { 'aid' => 'SQL_INTEGER', 'bid' => 'SQL_INTEGER', 'cid' => 'SQL_INTEGER', 'did' => 'SQL_INTEGER' };
my $ref2 = { 'aid' => 'table3.ace' };
my $aliases2 = { 'aid' => 'ALIASED_aid', };
$test->is($o2->definition($def2), $def2, "Set definition"); $test->is($o2->references($ref2), $ref2, "Set references"); $test->is($o2->column_aliases($aliases2), $aliases2, "Set aliases"); $test->is($o2->name("table2"), "table2", "set table name"); $test->is($o2->primary_column("aid"), "aid", "set table primary id");
my $def3 = { 'ace' => 'SQL_INTEGER', 'bogey' => 'SQL_INTEGER', };
$test->is($o3->definition($def3), $def3, "Set definition"); $test->is($o3->name("table3"), "table3", "set table name"); $test->is($o3->primary_column("ace"), "ace", "set table primary id");
my $def4 = { 'a' => 'SQL_INTEGER', 'b' => 'SQL_INTEGER', 'c' => 'SQL_INTEGER', 'd' => 'SQL_INTEGER' };
$test->is($o4->definition($def4), $def4, "Set definition"); $test->is($o4->name("table4"), "table4", "set table name"); $test->ok($o4->primary_column([qw(b c)]), "set table primary id");
my @o1select = $o->select_columns(); my @o2select = $o2->select_columns(); my @o3select = $o3->select_columns(); my @o4select = $o4->select_columns();
$test->is(scalar(__PACKAGE__->multiselect_query()), undef, "Could not multiselect w/o tables"); $test->is(__PACKAGE__->errcode, 'BDT-47', 'proper error code');
$test->is(__PACKAGE__->multiselect_query('tables' => $o), "select " . (join(", ",map {"table1.$_ as $_"} @o1select)) . " from table1", 'multi selected single table');
$test->is(__PACKAGE__->multiselect_query('tables' => [$o]), "select " . (join(", ",map {"table1.$_ as $_"} @o1select)) . " from table1", 'multi selected single table in arrayref');
$test->is(__PACKAGE__->multiselect_query('cols' => 'able', 'tables' => $o), "select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1", 'multi selected single table, different cols');
$test->is(__PACKAGE__->multiselect_query('cols' => ['able'], 'tables' => $o), "select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1", 'multi selected single table, different cols in arrayref');
$test->is(__PACKAGE__->multiselect_query('cols' => ['able'], 'tables' => [$o]), "select " . (join(", ",map {"table1.$_ as $_"} ('able'))) . " from table1", 'multi selected single table in arrayref, different cols');
$test->is(__PACKAGE__->multiselect_query('tables' => [$o, $o2]), "select\n" . (join(",\n",map {"\ttable1.$_"} @o1select)) . ",\n" . (join(",\n",map {"\ttable2.$_"} @o2select)) . "\nfrom\n" . "table1 inner join table2 on table1.able = table2.aid", 'multi selected multi table in arrayref');
$test->is(__PACKAGE__->multiselect_query('cols' => ['able', 'baker'], 'tables' => [$o, $o2]), "select\n" . (join(",\n",map {"\t$_"} ('able'))) . ",\n" . (join(",\n",map {"\t$_"} ('baker'))) . "\nfrom\n" . "table1 inner join table2 on table1.able = table2.aid", 'multi selected multi table in arrayref with differing columns');
$test->is(__PACKAGE__->multiselect_query('use_aliases' => 1, 'tables' => [$o, $o2]), "select\n" . (join(",\n",map {"\ttable1.$_ as " . $o2->alias_column($_)} @o1select)) . ",\n" . (join(",\n",map {"\ttable2.$_ as " . $o2->alias_column($_)} @o2select)) . "\nfrom\n" . "table1 inner join table2 on table1.able = table2.aid", 'multi selected multi table in arrayref with aliases');
count_query
Returns a count query ("select count(*) from $table").
my $count_query = $table->count_query();Be warned that no where clause is attached.
my $o = __PACKAGE__->new(); $test->ok($o, "Got object");
$test->is($o->name('count_query_table'), 'count_query_table', 'Set table name'); $test->is($o->count_query, 'select count(1) as count from count_query_table', 'Got count query');
optimize_query
Returns an optimize table query.
my $optimize_query = $table->optimize_query();my $o = __PACKAGE__->new(); $test->ok($o, "Got object"); $test->is($o->name("test table"), "test table", "set table name"); $test->is($o->optimize_query, 'optimize table test table', "got optimize query");
describe_query
Returns an describe table query.
my $describe_query = $table->describe_query();my $o = __PACKAGE__->new(); $test->ok($o, "Got object"); $test->is($o->name("test table"), "test table", "set table name"); $test->is($o->describe_query, 'desc test table', "got desc query");
reference_query
Given a column, returns a count query referencing the other table to determine whether the key is valid.
$table->references( { 'user_id' => 'user.id', 'user_name' => 'user.name' } ); print $table->reference_query('user_id'); #prints select count(1) from user where id = ? print $table->reference_query('login'); #prints nothingmy $def = { 'user_id' => 'SQL_INTEGER', 'user_name' => 'SQL_VARCHAR', };
my $foreign = { 'user_id' => 'user.id', 'user_name' => 'user.name' };
my $o = __PACKAGE__->new( 'definition' => $def, 'references' => $foreign, ); $test->ok($o, "Got object"); $test->is($o->definition, $def, "proper definition"); $test->is($o->references, $foreign, "proper foreign references");
my $f2 = {%$foreign};
$test->is($o->references($f2), $f2, "properly reset foreign reference");
$test->is($o->reference_query('user_id'), "select count(1) as count from user\n where id = ?", "successful reference query"); $test->is($o->reference_query('user_name'), "select count(1) as count from user\n where name = ?", "successful reference query");
$test->is(scalar($o->reference_query('foo')), undef, "got nothing for non-referenced column"); $test->is($o->errcode, "BDT-14", "Proper error code");
is_column
When passed a column name, returns a 1 if it is a column in this table, a 0 if it is not.
print $table->is_column('foo');my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->ok($o->is_column('able'), 'able is column'); $test->ok($o->is_column('baker'), 'baker is column'); $test->ok($o->is_column('charlie'), 'charlie is column'); $test->ok($o->is_column('delta'), 'delta is column'); $test->is($o->is_column('edgar'), 0, 'edgar is not column'); $test->is($o->is_column('foxtrot'), 0, 'foxtrot is not column');
$test->is(scalar($o->is_column), undef, "Cannot call w/o column"); $test->is($o->errcode, "BDT-04", "proper error code");
is_primary
When passed a column name, returns a 1 if it is a primary column in this table, a 0 if it is not
print $table->is_primary('foo');my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->primary_column('able'), 'able', 'Set primary column');;
$test->is(scalar($o->is_primary), undef, 'Cannot determine primary-ness w/o a column'); $test->is($o->errcode, 'BDT-01', 'proper error code');
$test->ok($o->is_primary('able'), 'able is primary'); $test->is($o->is_primary('baker'), 0, 'baker is not primary'); $test->is($o->is_primary('charlie'), 0, 'charlie is not primary'); $test->is($o->is_primary('delta'), 0, 'delta is not primary'); $test->is($o->is_primary('edgar'), 0, 'edgar is not primary'); $test->is($o->is_primary('foxtrot'), 0, 'foxtrot is not primary');
my $primaries = [qw(baker delta)];
$test->is($o->primary_column($primaries), $primaries, 'set primary column');
$test->is($o->is_primary('able'), 0, 'able is not primary'); $test->is($o->is_primary('baker'), 1, 'baker is primary'); $test->is($o->is_primary('charlie'), 0, 'charlie is not primary'); $test->is($o->is_primary('delta'), 1, 'delta is primary'); $test->is($o->is_primary('edgar'), 0, 'edgar is not primary'); $test->is($o->is_primary('foxtrot'), 0, 'foxtrot is not primary');
$test->is(scalar($o->is_primary), undef, "Cannot call w/o column"); $test->is($o->errcode, "BDT-01", "proper error code");
non_primary_cols
Returns a list of all of the non primary columns in the table.
my @nons = $table->non_primary_cols();my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition");
{ my %primary = map {$_, 1} $o->non_primary_cols;
$test->is($primary{'able'}, 1, 'able is not primary column'); $test->is($primary{'baker'}, 1, 'baker is not primary column'); $test->is($primary{'charlie'}, 1, 'charlie is not primary column'); $test->is($primary{'delta'}, 1, 'delta is not primary column'); }{ $o->primary_column('able');
my %primary = map {$_, 1} $o->non_primary_cols; $test->is($primary{'able'}, undef, 'able is primary column'); $test->is($primary{'baker'}, 1, 'baker is not primary column'); $test->is($primary{'charlie'}, 1, 'charlie is not primary column'); $test->is($primary{'delta'}, 1, 'delta is not primary column'); }{ $o->primary_column(['charlie', 'delta']);
my %primary = map {$_, 1} $o->non_primary_cols; $test->is($primary{'able'}, 1, 'able is not primary column'); $test->is($primary{'baker'}, 1, 'baker is not primary column'); $test->is($primary{'charlie'}, undef, 'charlie is primary column'); $test->is($primary{'delta'}, undef, 'delta is primary column'); }primary_cols
Returns a list of all the primary columns in the table.
my @primaries = $table->primary_cols();my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition");
{ my %primary = map {$_, 1} grep {defined} $o->primary_cols;
$test->is($primary{'able'}, undef, 'able is not primary column'); $test->is($primary{'baker'}, undef, 'baker is not primary column'); $test->is($primary{'charlie'}, undef, 'charlie is not primary column'); $test->is($primary{'delta'}, undef, 'delta is not primary column'); }{ $o->primary_column('able');
my %primary = map {$_, 1} $o->primary_cols; $test->is($primary{'able'}, 1, 'able is primary column'); $test->is($primary{'baker'}, undef, 'baker is not primary column'); $test->is($primary{'charlie'}, undef, 'charlie is not primary column'); $test->is($primary{'delta'}, undef, 'delta is not primary column'); }{ $o->primary_column(['charlie', 'delta']);
my %primary = map {$_, 1} $o->primary_cols; $test->is($primary{'able'}, undef, 'able is not primary column'); $test->is($primary{'baker'}, undef, 'baker is not primary column'); $test->is($primary{'charlie'}, 1, 'charlie is primary column'); $test->is($primary{'delta'}, 1, 'delta is primary column'); }foreign_cols
Given a table and an optional list of columns, returns all of the columns in the present table that reference the columns in the second table. If no columns are passed, then the second table's primary columns are assumed.
$table->references( { 'user_id' => 'user.id', 'user_name' => 'user.name' } ); $table->foreign_cols($user_table); #prints user_id $table->foreign_cols($user_table, 'id', 'name'); #prints user_id, user_name $table->foreign_cols($user_table, 'last_name', 'login'); #prints nothing - we have no references to those columnsmy $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->name('tableone'), 'tableone', 'set object 1 name'); $test->ok($o->primary_column(['able', 'baker']), 'set object 1 primary columns');
my $o2 = __PACKAGE__->new(); $test->ok($o2, "Created object 2"); my $def2 = { 'edgar' => 'SQL_VARCHAR', 'foxtrot' => 'SQL_DATE', 'goats' => 'SQL_TIMESTAMP', 'henry' => 'SQL_FLOAT' };
$test->is($o2->definition($def2), $def2, "set object 2 definition"); $test->is($o2->name('tabletoo'), 'tabletoo', "set object 2 name"); $test->ok($o2->primary_column(['edgar', 'foxtrot']), 'set object 2 primary columns');
my $ref1 = { 'able' => 'tabletoo.edgar', 'baker' => 'tabletoo.foxtrot', 'charlie' => 'tablethree.col' };
$test->is($o->references($ref1), $ref1, "Set reference 1");
my $o3 = __PACKAGE__->new(); $test->ok($o3, "Created object 3"); my $def3 = { 'col' => 'SQL_VARCHAR', };
$test->is($o3->definition($def3), $def3, "set object 3 definition"); $test->is($o3->name('tablethree'), 'tablethree', "set object 3 name");
my $o4 = __PACKAGE__->new(); $test->ok($o4, "Created object 4"); $test->is($o4->name('tablefour'), 'tablefour', "set object 4 name");
$test->is(scalar($o->foreign_cols), undef, "Cannot get foreign cols w/o table"); $test->is($o->errcode, 'BDT-45', "Proper error code");
{ my $i = 1; my %f1 = map {$_, $i++} $o->foreign_cols($o2); $test->is($f1{'able'}, 1, 'able is foreign column'); $test->is($f1{'baker'}, 2, 'baker is foreign column'); $test->is(scalar (keys %f1), 2, 'only 2 foreign columns'); }
{ my %f2 = map {$_, 1} $o->foreign_cols($o3); $test->is(scalar (keys %f2), 0, 'object 1 references no primary columns in object 3'); }
{ my %f2 = map {$_, 1} $o->foreign_cols($o3, 'col'); $test->is($f2{'charlie'}, 1, 'charlie is foreign column'); $test->is(scalar (keys %f2), 1, 'only 1 foreign column'); }
referenced_column
Given a column, returns the column it references in a foreign table or sets an error if references nothing.
$table->references( { 'user_id' => 'user.id', 'user_name' => 'user.name' } ); print $table->referenced_column('user_id'); #prints user.id print $table->referenced_column('password'); #prints nothingmy $def = { 'user_id' => 'SQL_INTEGER', 'user_name' => 'SQL_VARCHAR', 'user_extra' => 'SQL_VARCHAR', };
my $foreign = { 'user_id' => 'user.id', 'user_name' => 'user.name' };
my $o = __PACKAGE__->new( 'definition' => $def, 'references' => $foreign, ); $test->ok($o, "Got object"); $test->is($o->definition, $def, "proper definition"); $test->is($o->references, $foreign, "proper foreign references");
my $f2 = {%$foreign};
$test->is($o->references($f2), $f2, "properly reset foreign reference");
$test->is(scalar($o->referenced_column), undef, "Cannot get referenced column w/o column"); $test->is($o->errcode, "BDT-15", "proper error code");
$test->is($o->referenced_column('user_id'), 'user.id', 'user_id properly referenced'); $test->is($o->referenced_column('user_name'), 'user.name', 'user_id properly referenced'); $test->is(scalar($o->referenced_column('user_extra')), undef, 'user_id properly referenced'); $test->is($o->errcode, "BDT-16", "proper error code");
discover_columns
Takes a table name as an argument. Returns a hashref of the columns in that table, suitable to be used in a definition call.
my $definition = Basset::DB::Table->discover_columns('user_table');This should be typically be invoked via the discover flag to the constructor.
my $table = Basset::DB::Table->new( 'discover' => 1 );attach_to_query
Given a query string and a hashref of clauses, attaches the clauses to the query.
my $update_query = $table->attach_to_query( $table->update_query, { 'where' => 'id = ?' } ); Valid clauses are "where", "group by", "having", "order by" and "limit", reflecting the SQL clauses of the same kind.my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition");
$test->is($o->name('mytable'), 'mytable', 'set table name');
$test->is(scalar($o->attach_to_query), undef, "Cannot attach to query w/o query"); $test->is($o->errcode, "BDT-02", "proper error code");
$test->is($o->select_query, 'select mytable.able as able from mytable', 'proper select query'); my $query = $o->select_query; $test->is(scalar($o->attach_to_query($query)), $query, "No clauses returns original query"); $test->is(scalar($o->attach_to_query($query, {})), $query, "Empty clauses returns original query"); $test->is(scalar($o->attach_to_query($query, {'having' => 'having clause'})), undef, "Cannot have having w/o group"); $test->is($o->errcode, "BDT-09", "proper error code");
$test->is($o->attach_to_query($query, {'where' => 'able = ?'}), $query . "\n where able = ?", "attached where clause"); $test->is($o->attach_to_query($query, {'group by' => 'able'}), $query . "\n group by able", "attached group by clause"); $test->is($o->attach_to_query($query, {'order by' => 'baker'}), $query . "\n order by baker", "attached order by clause"); $test->is($o->attach_to_query($query, {'limit' => '5'}), $query . "\n limit 5", "attached limit clause");
$test->is( $o->attach_to_query( $query, { 'where' => 'able = ?', 'group by' => 'baker', } ), $query . "\n where able = ?\n group by baker", "attached where and group by clause");
$test->is( $o->attach_to_query( $query, { 'where' => 'able = ?', 'group by' => 'baker', 'having' => 'count(*) > 1' } ), $query . "\n where able = ?\n group by baker\n having count(*) > 1", "attached where, group by, and having clause");
join_tables
Magic time.
join_tables is used internally by the multiselect_query, but you can use it yourself if you want.
Takes an array of table objects or arrayrefs. arrayrefs must be of the following form:
- join type
The type of join to be performed. Should be a string. "inner", "outer", "left outer", that sort of thing. Defaults to inner. This parameter is optional.
- table object
The table object you're using.
- columns
SQL clauses to override the auto-join. This parameter is optional.
So, for example, if you have a usertable and a movietable, and movie.user references user.id, you could do:
Basset::DB::Table->join_tables( $usertable, $movietable, ) || die Basset::DB::Table->errstring;which returns:
user inner join movie on user.id = movie.userSay that user.movie was a foreign key to movie.id. Then you'd get back:
user inner join movie on user.id = movie.user and user.movie = movie.idI can't say why you'd want to have two tables referencing each other, but it's important to know that it happens.
3 tables is the same thing. Say that movie.genre references genre.id
Basset::DB::Table->join_tables( $usertable, $movietable, $genretable, ) || die Basset::DB::Table->errstring; user inner join movie on movie.user = user.id inner join genre on movie.user = genre.idOkay, say that you want to use a left join between the user table and the movie table.
Basset::DB::Table->join_tables( $usertable, ['left', $movietable], $genretable, ) || die Basset::DB::Table->errstring; user left join movie on movie.user = user.id inner join genre on movie.user = genre.idYou can also join with earlier tables. Say that snack.user references user.id
Basset::DB::Table->join_tables( $usertable, ['left', $movietable], $genretable, $snacktable, ) || die Basset::DB::Table->errstring; user left join movie on movie.user = user.id inner join genre on movie.user = genre.id inner join snack on user.id = snack.userOr, you can override the defaults specified in the table's references. For example, if the references don't exist for the table.
Basset::DB::Table->join_tables( $usertable, ['left', $movietable], $genretable, [$snacktable, 'user.id = snack.user AND user.status = snack.status'], ) || die Basset::DB::Table->errstring; user left join movie on movie.user = user.id inner join genre on movie.user = genre.id inner join snack on user.id = snack.user and user.status = snack.statusmy $o = __PACKAGE__->new(); $test->ok($o, "Created object");
my $o2 = __PACKAGE__->new(); $test->ok($o2, "Created object");
my $o3 = __PACKAGE__->new(); $test->ok($o3, "Created object");
my $o4 = __PACKAGE__->new(); $test->ok($o4, "Created object");
my $def1 = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
my $ref1 = { 'able' => 'table2.aid', 'baker' => 'table4.b', 'charlie' => 'table4.c', };
$test->is($o->definition($def1), $def1, "Set definition"); $test->is($o->references($ref1), $ref1, "Set references"); $test->is($o->name("table1"), "table1", "set table name"); $test->is($o->primary_column("able"), "able", "set table primary id");
my $def2 = { 'aid' => 'SQL_INTEGER', 'bid' => 'SQL_INTEGER', 'cid' => 'SQL_INTEGER', 'did' => 'SQL_INTEGER' };
my $ref2 = { 'aid' => 'table3.ace' };
$test->is($o2->definition($def2), $def2, "Set definition"); $test->is($o2->references($ref2), $ref2, "Set references"); $test->is($o2->name("table2"), "table2", "set table name"); $test->is($o2->primary_column("aid"), "aid", "set table primary id");
my $def3 = { 'ace' => 'SQL_INTEGER', 'bogey' => 'SQL_INTEGER', };
$test->is($o3->definition($def3), $def3, "Set definition"); $test->is($o3->name("table3"), "table3", "set table name"); $test->is($o3->primary_column("ace"), "ace", "set table primary id");
my $def4 = { 'a' => 'SQL_INTEGER', 'b' => 'SQL_INTEGER', 'c' => 'SQL_INTEGER', 'd' => 'SQL_INTEGER' };
$test->is($o4->definition($def4), $def4, "Set definition"); $test->is($o4->name("table4"), "table4", "set table name"); $test->ok($o4->primary_column([qw(b c)]), "set table primary id");
$test->is(scalar(__PACKAGE__->join_tables), undef, "Could not join w/o tables"); $test->is(__PACKAGE__->errcode, "BDT-28", "proper error code BDT-28");
$test->is(__PACKAGE__->join_tables($o), "table1", "Join with one table is same table");
$test->is(__PACKAGE__->join_tables($o, $o2), "table1 inner join table2 on table1.able = table2.aid", "Default joined two tables" );
$test->is(__PACKAGE__->join_tables($o2, $o), "table2 inner join table1 on table1.able = table2.aid", "Reverse joined two tables" );
$test->is(__PACKAGE__->join_tables($o, $o2, $o3), "table1 inner join table2 on table1.able = table2.aid inner join table3 on table2.aid = table3.ace", "Default joined three tables" ); $test->is(__PACKAGE__->join_tables($o, $o2, $o3, $o4), "table1 inner join table2 on table1.able = table2.aid inner join table3 on table2.aid = table3.ace inner join table4 on table1.baker = table4.b and table1.charlie = table4.c", "Default joined four tables" );
$test->is(__PACKAGE__->join_tables($o, [$o2]), "table1 inner join table2 on table1.able = table2.aid", "Default joined two tables w/arrayref table only" );
$test->is(__PACKAGE__->join_tables($o, ['inner',$o2]), "table1 inner join table2 on table1.able = table2.aid", "inner joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['outer',$o2]), "table1 outer join table2 on table1.able = table2.aid", "outer joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['natural',$o2]), "table1 natural join table2", "natural joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['left',$o2]), "table1 left join table2 on table1.able = table2.aid", "left joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['right',$o2]), "table1 right join table2 on table1.able = table2.aid", "right joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['left outer',$o2]), "table1 left outer join table2 on table1.able = table2.aid", "left outer joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, ['right outer',$o2]), "table1 right outer join table2 on table1.able = table2.aid", "right outer joined 2 tables");
$test->is(__PACKAGE__->join_tables($o, [$o4, 'table1.baker = table4.b']), "table1 inner join table4 on table1.baker = table4.b", "joined 2 tables with alternate clause");
$test->is(__PACKAGE__->join_tables($o, ['outer', $o4, 'table1.baker = table4.b']), "table1 outer join table4 on table1.baker = table4.b", "outer joined 2 tables with 3 arg alternate clause");
$test->is(__PACKAGE__->join_tables($o, [$o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]), "table1 inner join table4 on table1.baker = table4.b and table1.baker = table4.c", "joined 2 tables with 2 alternate clauses");
$test->is(__PACKAGE__->join_tables($o, ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]), "table1 outer join table4 on table1.baker = table4.b and table1.baker = table4.c", "outer joined 2 tables with 2 alternate clauses");
$test->is(__PACKAGE__->join_tables(['inner', $o], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]), "table1 outer join table4 on table1.baker = table4.b and table1.baker = table4.c", "outer joined 2 tables with 2 alternate clauses w/ first table array");
$test->is(__PACKAGE__->join_tables(['inner', $o, 'foo = bar'], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]), "table1 outer join table4 on table1.baker = table4.b and table1.baker = table4.c", "outer joined 2 tables with 2 alternate clauses w/ first table array and columns");
$test->is(__PACKAGE__->join_tables([$o, 'foo = bar'], ['outer', $o4, ['table1.baker = table4.b', 'table1.baker = table4.c']]), "table1 outer join table4 on table1.baker = table4.b and table1.baker = table4.c", "outer joined 2 tables with 2 alternate clauses w/ first table array and columns, non-inner");
$test->is(scalar(__PACKAGE__->join_tables($o, $o3)), undef, "Cannot auto-join unreferenced tables"); $test->is(__PACKAGE__->errcode, 'BDT-27', 'proper error code');
many_clause
Convenience method. Given a column and a list of values, returns a foo in (???) clause for use in queries.
print $table->many_clause('id', qw(1 2 3 4)); #prints "id in (?, ?, ?, ?)"You may optionally pass your values in an arrayref, if it's more convenient.
print $table->many_clause('id', [qw(1 2 3 4)]); #prints "id in (?, ?, ?, ?)"Finally, if you pass your values in an arrayref, you may specify the 'not' parameter to build a 'not in' clause
print $table->many_clause('id', 'not', qw(1 2 3 4)); #prints "id not in (?, ?, ?, ?)"my $o = __PACKAGE__->new(); $test->ok($o, "Created object"); my $def = { 'able' => 'SQL_INTEGER', 'baker' => 'SQL_INTEGER', 'charlie' => 'SQL_INTEGER', 'delta' => 'SQL_INTEGER' };
$test->is($o->definition($def), $def, "Set definition"); $test->is($o->definition, $def, "Got definition"); $test->is($o->name('test'), 'test', 'set name');
$test->is(scalar($o->many_clause), undef, "Cannot build many clause w/o column"); $test->is($o->errcode, 'BDT-21', 'proper error code');
$test->is(scalar($o->many_clause('able')), undef, "Cannot build many clause w/o values"); $test->is($o->errcode, 'BDT-22', 'proper error code');
$test->is(scalar($o->many_clause('able', [])), undef, "Cannot build many clause w/o values"); $test->is($o->errcode, 'BDT-22', 'proper error code');
$test->is($o->many_clause('able', 1), 'test.able in (?)', 'built single many clause'); $test->is($o->many_clause('able', 1, 2), 'test.able in (?, ?)', 'built double many clause'); $test->is($o->many_clause('able', 1, 2, 3), 'test.able in (?, ?, ?)', 'built triple many clause');
$test->is($o->many_clause('able', [1]), 'test.able in (?)', 'built single many clause from arrayref'); $test->is($o->many_clause('able', [1, 2]), 'test.able in (?, ?)', 'built double many clause from arrayref'); $test->is($o->many_clause('able', [1, 2, 3]), 'test.able in (?, ?, ?)', 'built triple many clause from arrayref');
$test->is($o->many_clause('able', 'not', [1]), 'test.able not in (?)', 'built single not many clause'); $test->is($o->many_clause('able', 'not', [1, 2]), 'test.able not in (?, ?)', 'built double not many clause'); $test->is($o->many_clause('able', 'not', [1, 2, 3]), 'test.able not in (?, ?, ?)', 'built triple not many clause'); $test->is($o->many_clause('able', 1, [1, 2, 3]), 'test.able not in (?, ?, ?)', 'built triple not many clause w/arbitrary true value');
qualified_name
Given a column name, returns the column name with the table name prepended.
print $user->qualified_name('id'); #prints user.idmy $o = __PACKAGE__->new(); $test->ok($o, "Got object");
$test->is(scalar($o->qualified_name()), undef, 'could not get qualified name w/o column');
$test->is($o->errcode, 'BDT-23', 'Proper error code'); $test->is(scalar($o->qualified_name('foo')), undef, 'could not get qualified name w/o table name'); $test->is($o->errcode, 'BDT-24', 'Proper error code'); $test->is($o->name('test1'), 'test1', 'Set table name'); $test->is($o->qualified_name('foo'), 'test1.foo', 'column foo properly qualified'); $test->is($o->qualified_name('bar'), 'test1.bar', 'column bar properly qualified'); $test->is($o->name('test2'), 'test2', 'changed column name to test2'); $test->is($o->qualified_name('foo'), 'test2.foo', 'column foo properly qualified'); $test->is($o->qualified_name('bar'), 'test2.bar', 'column bar properly qualified'); $test->is($o->qualified_name('test2.foo'), 'test2.foo', 'previously column test2.foo properly qualified'); $test->is($o->qualified_name('test2.bar'), 'test2.bar', 'previously column test2.bar properly qualified');
nonqualified_name
Given a column name, returns the column name without the table name prepended.
print $user->qualified_name('id'); #prints id print $user->qualified_name('user.id'); #prints id$test->is(scalar(__PACKAGE__->nonqualified_name()), undef, "Could not get nonqualified name w/o column"); $test->is(__PACKAGE__->nonqualified_name('foo.bar'), 'bar', 'stripped table name'); $test->is(__PACKAGE__->nonqualified_name('bar'), 'bar', 'returned column w/o table name');
construct_where_clause
The where clause constructor is a class method that takes an arrayref of tables as its first argument, and then an arbitrary set of clauses in a list.
my ($clause, @bindvalues) = Basset::DB::Table->construct_where_clause($tables, @clauses);This is used to hide SQL from your application layer. You can specify arbitrarily complex statements here to build where clauses. The tables array is used to qualify the names of the columns passed. The array will be walked and the first table encounted that has the given column will be used to qualify the name. Hence, if a column exists in multiple tables, you should qualify it to ensure that you get it from the place you expect.
Easily pass in key value pairs.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => 7 ); #returns ('tablename.id = ?', 7)To specify an 'in' clause, pass in an array.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => [7, 8, 9] ); #returns ('tablename.id in (?, ?, ?)', 7, 8, 9)Additional values are joined by AND statements.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => [7, 8, 9], 'status' => 1, ); #returns ('tablename.id in (?, ?, ?) AND tablename.status = ?', 7, 8, 9, 1)You may specify alternative values for columns in a hashref.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => { '>' => 7, '<' => 14, 'status' => 1, ); #returns ('(tablename.id > ? OR tablename.id < ?) AND tablename.status = ?', 7, 14, 1)Groups of sets of values are joined with OR clauses.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, ['id' => 7,'status' => 1,], ['id' => {'>' => 18'}, 'status' => 3], ['status' => 5'], ); #returns ('(tablename.id = ? OR tablename.status = ?) OR (tablename.id > ? AND status = ?) OR (status = ?)', 7, 1, 18, 3, 5)groups may be nested
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => 7, ['id' => {'>' => 20}, ['name' => 'test', status => 5]] ); #returns ('(tablename.id = ?) OR (tablename.id > ? OR (tablename.name = ? AND tablename.status = ?))', 7, 20, test, 5)Column order may not be preserved.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => 7, ['id' => 8], 'name' => 'foo', ); #returns ('(tablename.id = ? AND tablename.name = ?) OR (tablename.id = ?)', 7, 'foo', 8)To group different columns with different and clauses, repeat the clause.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => {'>' => 8}, 'id' => {'<' => 25}, ); #returns ('tablename.id > ? AND tablename.id < ?', 8, 25)Finally, sometimes you just need to have a literal value in there that you can't bind to a place holder. In that case, you want to pass in a reference.
my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => {'>' => \8}, 'id' => {'<' => \25}, ); #returns ('tablename.id > 8 AND tablename.id < 25') This is most useful, obviously, for NULLs. my ($stmt, @values) = Basset::DB::Table->construct_where_clause( $tables, 'id' => {'is' => \'NULL', '=' => 4}, ); #returns ('tablename.id is NULL or tablename.id = ?', 4)$test->ok(! __PACKAGE__->construct_where_clause, "Cannot construct_where_clause w/o tables"); $test->is(__PACKAGE__->errcode, "BDT-48", 'proper error code');
my $t1 = __PACKAGE__->new( 'name' => 't1', 'primary_column' => 'id', 'definition' => { 'id' => 'SQL_INTEGER', 'name' => 'SQL_VARCHAR', } );
$test->ok($t1, 'built first table');
my $t2 = __PACKAGE__->new( 'name' => 't2', 'primary_column' => 'id', 'definition' => { 'id' => 'SQL_INTEGER', 'size' => 'SQL_VARCHAR', } );
$test->ok($t2, 'built second table');
my $one_table = [$t1]; my $two_tables = [$t1, $t2];
$test->ok(! __PACKAGE__->construct_where_clause($one_table), "Cannot construct_where_clause w/o clauses"); $test->is(__PACKAGE__->errcode, "BDT-49", 'proper error code');
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ?', 'proper clause'); $test->is($return[1], '1', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo'); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? AND t1.name = ?', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], 'foo', 'proper value'); }
{ $test->ok(!__PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo', 'bar' => 'baz'), 'could not construct_where_clause w/non-existent column'); $test->is(__PACKAGE__->errcode, 'BDT-50', 'proper error code'); }
{ $test->ok(!__PACKAGE__->construct_where_clause($one_table, 'id' => 1, 'name' => 'foo', ['bar' => 'baz']), 'could not construct_where_clause w/non-existent column'); $test->is(__PACKAGE__->errcode, 'BDT-50', 'proper error code'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => [qw(a b c)]); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id in (?, ?, ?)', 'proper clause'); $test->is($return[1], 'a', 'proper value'); $test->is($return[2], 'b', 'proper value'); $test->is($return[3], 'c', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1}); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id > ?', 'proper clause'); $test->is($return[1], '1', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1, '<' => 10}); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], '(t1.id < ? OR t1.id > ?)', 'proper clause'); $test->is($return[1], '10', 'proper value'); $test->is($return[2], '1', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1, '<' => 10}, 'name' => 'you'); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], '(t1.id < ? OR t1.id > ?) AND t1.name = ?', 'proper clause'); $test->is($return[1], '10', 'proper value'); $test->is($return[2], '1', 'proper value'); $test->is($return[3], 'you', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => {'>' => 1}, 'id' => {'<' => 10}); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id > ? AND t1.id < ?', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], '10', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, ['name' => 'me', 'id' => 3]); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], 'me', 'proper value'); $test->is($return[3], '3', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($one_table, 'id' => 1, ['name' => 'me', 'id' => 3], 'id' => 5); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? AND t1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], '5', 'proper value'); $test->is($return[3], 'me', 'proper value'); $test->is($return[4], '3', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, ['name' => 'me', 'id' => 3], 'id' => 5); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? AND t1.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], '5', 'proper value'); $test->is($return[3], 'me', 'proper value'); $test->is($return[4], '3', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, ['name' => 'me', 'id' => 3], 't2.id' => 5); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? AND t2.id = ? OR (t1.name = ? AND t1.id = ?)', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], '5', 'proper value'); $test->is($return[3], 'me', 'proper value'); $test->is($return[4], '3', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => 1, 'size' => 7); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id = ? AND t2.size = ?', 'proper clause'); $test->is($return[1], '1', 'proper value'); $test->is($return[2], '7', 'proper value'); }
{ my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => \1, 'size' => \7); $test->is(scalar(@return), 1, "got values from constructing where clause"); $test->is($return[0], 't1.id = 1 AND t2.size = 7', 'proper clause'); }
{ my @return = __PACKAGE__->construct_where_clause($two_tables, 'id' => {'is' => \'NULL'}, 'size' => 7); $test->ok(scalar(@return), "got values from constructing where clause"); $test->is($return[0], 't1.id is NULL AND t2.size = ?', 'proper clause'); $test->is($return[1], '7', 'proper value'); }
arbitrary_sql
Wrappers Basset::Object::Persistent's arbitrary_sql method.
my $poclass = __PACKAGE__->pkg->pkg_for_type('persistentobject');
$test->ok($poclass, "Got persistent object class"); $test->ok($poclass->can('arbitrary_sql'), 'persistent class can arbitrary_sql');
