Alzabo
view release on metacpan
or search on metacpan
Build.PL
view on Meta::CPAN
118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 | ? $opts {root}
: %Alzabo::Config::CONFIG
? Alzabo::Config::root_dir()
: find_possible_root()
);
return $root_dir if $opts {automated};
print <<'EOF';
Please select a root directory for Alzabo (schema files will be stored
under this root.
EOF
return Module::Build->prompt( ' Alzabo root?' , $root_dir );
}
sub find_possible_root
{
my @dirs ;
|
Changes
view on Meta::CPAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 0.92
BUG FIXES:
- Reverse engineering with MySQL broke when the tables were returned
_without_ the schema name.
- Enable subselects for MySQL, though there's still no good API for
subselects.
- Pass any unknown command line options through to Module::Build, for
example things like "--install-base" . Reported by Lars Dieckow. RT
Ticket
- Fix a possible bug in Alzabo::Config::available_schemas. Reported by
Otto Hirr. RT Ticket
0.91 Mar 25, 2007
|
Changes
view on Meta::CPAN
93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | 0.89 Jun 20, 2006
ENHANCEMENTS:
- Improved schema diffs for Postgres, particularly in the area of
comparing two columns. Now we attempt to determine if two columns
are logically equivalent, even if they might have minor variations
(INT vs INTEGER type name, 'f' versus 'false' for BOOLEAN default ,
etc.).
- Added Alzabo::SQLMaker->distinct_requires_order_by_in_select for the
benefit of Pg, which requires that anything in the ORDER BY clause
show up in the SELECT when you SELECT DISTINCT. This change is
experimental, and may go away in future versions.
- Removed support for passing order_by and group_by as a hash
reference. This was deprecated in 0.59.
BUG FIXES:
- When reverse engineering a Postgres schema, Alzabo did not look for
|
Changes
view on Meta::CPAN
606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 | Previously, this could only be done as a double array reference, like:
[ [ left_outer_join => $table_A , $table_B ] ]
- Various doc fixes and rewriting, most notably in Alzabo.pm.
BUG FIXES:
- A join using multiple aliases to the same table would fail with an
error message like "Cannot use column (Foo.bar_id) in select unless its table is included in the FROM clause".
- Remove the long-ago deprecated next_row() and next_rows() methods.
- Postgres 7.3 allows identifiers to be up to 63 characters. This
broke the code that handled sequenced columns for Postgres. Patch
by Josh Jore.
- If you tried to create a relationship between two tables, and the
"table_to" table already had a column of the same name as the
|
Changes
view on Meta::CPAN
882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 | Identifiers are always quoted when using Alzabo::Create::* with
Postgres, however.
- Did a fair amount of profiling in order to optimize Alzabo's data
fetching. In general, Alzabo::Runtime::* operations should be
faster.
- Added Alzabo::Runtime::Column->alias which is useful when executing
queries via the Alzabo::Runtime::Schema and Alzabo::Runtime::Table
-> select methods.
BUG FIXES:
- Alzabo::MethodMake generated "lookup column/table" methods will
return if there is no matching entry in the related table, which is
important when the two tables are independent. Previously it would
have been a runtime error (attempting to call a method on an
undefined value).
- Fix warning from Row->update. Patch by Ilya Martynov.
|
Changes
view on Meta::CPAN
957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 | Alzabo::Config::root_dir() every time you load Alzabo. An attempt
to load a schema without first defining the root_dir will throw an
exception. Based on a patch from Ilya Martynov.
BUG FIXES:
- Allow UNIQUE as a column attribute for Postgres. Reported by Dan
Martinez.
- Add DISTINCT back as an exportable function from the SQLMaker
subclasses. It may be useful when calling -> select and ->function.
- Fixed a bug that prevented things from being deleted from the cache
storage.
- Fixed a variety of problems related to handling Postgres tables and
columns with mixed or upper case names. This included a bug that
prevented them from being reverse engineered properly. Reported by
Terrence Brannon.
- Fixed a bug when altering a Postgres column name that caused Alzabo
|
Changes
view on Meta::CPAN
1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 | choosing them yourself. For most relationships, Alzabo will simply
do the right thing, adding a column to one of the tables as needed.
- The problems running the tests with Postgres should now be fixed.
- Fix stupid and inefficient internal handling of "SELECT DISTINCT"
queries. Now Alzabo simply lets the database handle this, the way
it should have in the first place.
- The Alzabo::Runtime::Schema and Alzabo::Runtime::Table ->function
and -> select methods now allow you to select scalars so you can do
things like SELECT 1 FROM Foo WHERE ... in order to test for the
existence of a row.
- Added Alzabo::Table->primary_key_size method, which indicates how
many columns participate in the table's primary key.
- Added Alzabo::Runtime::Schema->row_count. Suggested by Daniel
Gaspani.
- Alzabo now detects older versions of schemas and transparently
|
Changes
view on Meta::CPAN
1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 | BUG FIXES:
- Adding a column that is not-nullable or has a default to a table
under Postgres was causing an error with Postgres 7.2.1. It seems
likely that with earlier versions of Postgres, this was simply
failing silently. Patch by Daniel Gaspani.
- Fixed buggy handling of joins that had a table with a multi-column
primary key as the "distinct" parameter.
- Calling the Alzabo::Runtime::Schema-> join method with no 'select'
parameter and a 'join' parameter that was an array reference of
array references would fail.
- Avoid an uninit value in Alzabo::MethodMaker. Reported by Daniel
Gaspani.
- If you created a cursor inside an eval {} block, the cursor contained
an object whose DESTROY method would overwrite $@ as it went out of
scope when the eval block exited. This could basically make it look
like an exception had disappeared. Thanks to Brad Bowman for an
|
Changes
view on Meta::CPAN
1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 | - Fix detection of primary key changes for schema diffs.
- Handle NOT IN for where conditions.
---------------------------------------------------------------------------
0.63 Feb 18, 2002
ENHANCEMENTS:
- Calling Alzabo::Runtime::Row-> select or
Alzabo::Runtime::Row->select_hash with no arguments returns the
values for all of the columns in the table. Suggested by Jeremy
R. Semeiks.
- The Alzabo::Runtime::Row->id method has been renamed to id_as_string
for the benefit of those crazy people who like to use "id" as a
column name and want Alzabo::MethodMaker to be able to create such a
method. Suggested by Alexei Barantsev.
- Changed the Alzabo::Create::Schema->sync_backend method so that if
there was no corresponding schema in the RDBMS, then it will
|
Changes
view on Meta::CPAN
1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 | - Document that Alzabo supports COALESCE and NULLIF for Postgres.
- Added Alzabo::ObjectCache::Sync::Mmap which uses Cache::Mmap. This
is just slightly slower than using SDBM_File.
- New table alias feature for making queries that join against a table
more than once. An example:
my $foo_alias = $foo_tab ->alias;
my $cursor = $schema -> join ( select => $foo_tab ,
tables => [ $foo_tab , $bar_tab , $foo_alias ],
where => [ [ $bar_tab ->column( 'baz' ), '=' , 10 ],
[ $foo_alias ->column( 'quux' ), '=' , 100 ] ],
order_by => $foo_alias ->column( 'briz' ) );
In this query, we want to get all the entries in the foo table based
on a join between foo and bar with certain conditions. However, we
want to order the results by a _different_ criteria than that used
for the join . This doesn't necessarily happen often, but when it
does its nice to be able to do it. In SQL, this query would look
|
Changes
view on Meta::CPAN
1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 | 0.61 Dec 25, 2001
ENHANCEMENTS:
- Improve documentation for new Alzabo::Create::Schema->sync_backend
method and note its caveats.
- It is now possible to use SQL functions as part of order_by clauses. For example:
my $cursor = $schema -> select ( select => [ COUNT( '*' ), $id_col ],
tables => [ $foo_tab , $bar_tab ],
group_by => $id_col ,
order_by => [ COUNT( '*' ), 'DESC' ] );
- Allow a call to Alzabo::Runtime::Table->insert without a values
parameter. This is potentially useful for tables where the primary
key is sequenced and the other columns have defaults or are
NULLable. Patch by Ilya Martynov.
BUG FIXES:
- A call to the schema class's select or function methods that had
both an order_by and group_by parameter would fail because it tried
to process the order by clause before the group by clause.
- When thawing potential row objects, Alzabo was trying to stick them
into the cache, which may have worked before but not now, and should
be avoided anyway.
- The parent and children methods created by Alzabo::MethodMaker were
incorrect (and unfortunately the tests of this feature were hosed
too).
|
Changes
view on Meta::CPAN
1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 | - When passing order_by specifications, it is now possible to do this:
order_by => [ $col1 , $col2 , 'DESC' , $col3 , 'ASC' ]
which allow for multiple levels of sorting as well as being much
simpler to remember.
- It is now possible to do something like
$table -> select ( select => [ 1, $column ] ... );
and have it work. In this case, every row returned by the cursor
will have 1 as its first element.
- Added Alzabo::MySQL and Alzabo::PostgreSQL POD pages. These pages
document how Alzabo does (or does not) support various RDBMS
specific features.
- Remove Alzabo::Util. Use Class::Factory::Util from CPAN instead.
Class::Factory::Util is a slight revision of Alzabo::Util that has
|
Changes
view on Meta::CPAN
1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 | order_by => { columns => ..., sort => ... }
has been deprecated in favor of a simpler syntax.
---------------------------------------------------------------------------
0.59 Nov 17, 2001
ENHANCEMENTS:
- Got rid of the post_select_hash hook and combined it with
post_select, which now receives a hash reference. Suggested by Ilya
Martynov.
- Run all hooks inside Alzabo::Schema->run_in_transaction method to
ensure database integrity in cases where your hooks might
update/ delete /insert data. Suggested by Ilya Martynov.
- Added new Alzabo::Runtime::Table-> select method. This is just like
the existing ->function method, but returns a cursor instead of the
entire result set.
- Added a 'limit' parameter to the ->function method (also works for
the -> select method).
- Added new Alzabo::Runtime::Schema-> select method. This is like the
method of the same name in the table class but it allows for joins.
- Added new potential rows, which are objects with (largely) the same
interface as regular rows, but which are not (yet) inserted into the
database. They are created via the new
Alzabo::Runtime::Table->potential_row method. Thanks to Ilya
Martynov for suggestions and code for this feature.
- Added Alzabo::Runtime::Row->schema method. Suggested by Ilya
Martynov.
|
Changes
view on Meta::CPAN
1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 | - Alzabo was not handling the BETWEEN operator in where clauses
properly. Patch by Eric Hillman.
- Passing in something like this to rows_where:
( where => [ $col_foo , '=' , 1,
$col_bar , '=' , 2 ] )
worked when it shouldn't.
- Trying to do a select that involved a group by and a limit was not
being allowed.
INCOMPATIBILITIES:
- Got rid of the post_select_hash hook and combined it with
post_select, which now receives a hash reference.
---------------------------------------------------------------------------
0.58 Oct 18, 2001
ENHANCEMENTS:
- Added new insert_hooks, update_hooks, select_hooks, and delete_hooks
options to Alzabo::MethodMaker. Suggested by Ilya Martynov.
- Moved all the important document for the object caching system into
Alzabo::ObjectCache, including the import options for all of the
various modules.
- Added Alzabo::ObjectCache::Sync::RDBMS &
Alzabo::ObjectCache::Store::RDBMS. The former finally allows
synchronization of multiple processes across multiple machines!
|
Changes
view on Meta::CPAN
1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 | - The way cardinality and dependency was being represented in the
schema graphs was sometimes backward and sometimes just broken.
- Fixed Alzabo::ObjectCache::Store::BerkeleyDB->clear, which was not
actually doing anything. Added tests that catch this.
- The lookup_tables option, which was deprecated in 0.57, was not
being allowed at all.
- Calls to select_hash on cached rows were not going through the cache
checking routines, possibly returning expired data. Added tests for
this.
- Eliminate race condition in Alzabo::ObjectCache::Sync::BerkeleyDB.
- The Alzabo::Runtime::Row->rows_by_foreign_key method wasn't doing
quite what it said. In cases where there was a 1..1 or n..1
relationship to columns that were not the table's primary key, a
cursor would be returned instead of a single row. Reported by Ilya
Martynov.
- Alzabo::MethoMaker could generate 'subroutine foo redefined'
warnings . Reported by Ilya Martynov.
- Fixed clear method for all Alzabo::ObjectCache::Store::* modules.
DEPRECATIONS:
- The insert and update options for Alzabo::MethodMaker have been
deprecated. They have been replaced by the new insert_hooks and
update_hooks options, along with new select_hooks and delete_hooks
options.
INCOMPATIBILITIES:
- If you specify give the 'all' parameter to MethodMaker, 'insert' and
'update' are no longer included.
---------------------------------------------------------------------------
0.57 Oct 9, 2001
|
Changes
view on Meta::CPAN
1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 | - You can now use SQL functions pretty much anywhere you would want (in inserts, updates, where clauses, etc). See the "Using SQL
Functions" section in the Alzabo.pm docs for more details.
- As a corollary to the above, the Alzabo::Runtime::Table->function
method has been created to replace the old
Alzabo::Runtime::Table->func method. This new method takes
advantage of the new system for using SQL functions and is simpler
and more flexible. It allows you to perform all sorts of aggregate
selects.
- Added the Alzabo::Runtime::Row->select_hash method. Requested by
Dana Powers.
DEPRECATIONS:
- The Alzabo::Runtime::Table->func method has been deprecated.
BUG FIXES:
- When adding an AUTO_INCREMENT column to an existing MySQL table, the
SQL generated would cause an error. This has been fixed. However,
|
Changes
view on Meta::CPAN
2063 2064 2065 2066 2067 2068 2069 2070 2071 2072 2073 2074 2075 2076 2077 2078 2079 2080 2081 2082 2083 | Alzabo::ObjectCache:: MemoryStore => Alzabo::ObjectCache::Store::Memory
Alzabo::ObjectCache:: DBMSync => Alzabo::ObjectCache::Sync::DB_File
Alzabo::ObjectCache:: IPCSync => Alzabo::ObjectCache::Sync::IPC.pm
Alzabo::ObjectCache:: NullSync => Alzabo::ObjectCache::Sync::Null.pm
ENHANCEMENTS:
- Document order by clauses for joins.
- Document limit clauses for joins and single table selects.
- Expand options for where clauses to allow 'OR' conditionals as well
as subgroupings of conditional clauses.
- If you set prefetch columns for a table, these are now fetched along
with other data for the table in a cursor, reducing the number of
database SELECTs being done.
- Added Alzabo::Create::Schema->clone method. This allows you to
clone a schema object (except for the name, which must be changed as
|
Changes
view on Meta::CPAN
2231 2232 2233 2234 2235 2236 2237 2238 2239 2240 2241 2242 2243 2244 2245 2246 2247 2248 2249 2250 2251 | then another row was inserted with the same primary key. Note to
self: premature optimization is the root of all evil.
---------------------------------------------------------------------------
0.33 Feb 21, 2001
- The linking table methods generated by Alzabo::MethodMaker were
broken. Fixed this.
- Changed how order by clauses can be passed to select operations.
Also changed the docs, which were way out of sync with the changes
in this area.
- Attempting to update more than one value at once was broken. Fixed
this.
- Added Alzabo::Runtime::Table->func method to allow arbitrary column
aggregate functions like MAX, MIN, AVG, etc.
- Fixed schema creator bug. It was not possible to change a column's
|
Changes
view on Meta::CPAN
2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 2402 2403 2404 2405 2406 2407 | However, the new DBMSync module will probably scale better, and
performance should be about the same for smaller applications. To
sync => 'Alzabo::ObjectCache::DBMSync' );
4. If you run without any caching at all then the
Alzabo::Runtime::Row class's behavior has changed somewhat. In
particular, selects or updates against a deleted object will always
throw an Alzabo::Exception::NoSuchRow exception. Before, the
behavior wasn't very well defined .
Please read the section on clearing the cache in the
Alzabo::ObjectCache module, as this is an important concept. By
default , the caching and syncing modules will just grow unchecked.
You need to clear at the appropriate points (usually your
application's entry points) in order to keep them under control.
---------------------------------------------------------------------------
|
Changes
view on Meta::CPAN
2508 2509 2510 2511 2512 2513 2514 2515 2516 2517 2518 2519 2520 2521 2522 2523 2524 2525 2526 2527 2528 2529 | - Doesn't fail on install for Mason components if no Mason component
extension was given . Thanks _again_ to Randal for working with me
on this in IRC late at night.
---------------------------------------------------------------------------
0.10_4 Oct 10, 2000
- Fix Makefile.PL bug
- Auto select a column when adding a relation ( if there is a logical
one to select ).
---------------------------------------------------------------------------
0.10_3
- Fix bug with deleting foreign key objects from tables.
---------------------------------------------------------------------------
0.10
|
Changes
view on Meta::CPAN
2608 2609 2610 2611 2612 2613 2614 2615 2616 2617 2618 2619 2620 2621 2622 2623 2624 2625 2626 2627 2628 | the construct $table ->rows_where( where => { foo => undef } )
properly.
---------------------------------------------------------------------------
0.08
- Lazy column evaluation had made it possible to create an
Alzabo::Runtime::Row object that did not correspond to any data in
the database if its table object did specify any rows to prefetch.
This would have only been discovered later by calling the select
method on a non-primary key column. This hole was plugged.
- As a corollary to the above change methods in Alzabo::Runtime::Table
that produce rows now always return an empty list or undef when the
rows cannot be made because the specified primary key doesn't exist.
Previously, the rows_by_where_clause method did this while others
would cause an exception either during the object creation or later,
depending upon the situation described above.
- GENERAL NOTE: I probably used exceptions too much, as in the above
|
MANIFEST
view on Meta::CPAN
59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | lib/Alzabo/SQLMaker/MySQL.pm
lib/Alzabo/SQLMaker/PostgreSQL.pm
lib/Alzabo/Table.pm
lib/Alzabo/Utils.pm
LICENSE
Makefile.PL
MANIFEST This list of files
mason/widgets/edit_field_checkbox
mason/widgets/edit_field_text_input
mason/widgets/edit_field_textarea
mason/widgets/fk_to_one_select
mason/widgets/insert
mason/widgets/insert_or_update
mason/widgets/update
META.yml
README
t/01-compile.t
t/01-driver.t
t/02-create.t
t/03-runtime.t
t/04-rev-engineer.t
|
SIGNATURE
view on Meta::CPAN
78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | SHA1 9286403266901d80a226476bb43cf0c1f10826b1 lib/Alzabo/Runtime/UniqueRowCache.pm
SHA1 ce71db21e316956a7a2cd98824c0a7d18dc1ab83 lib/Alzabo/SQLMaker.pm
SHA1 2bc532caa834986aa22ab277454f9231d2066731 lib/Alzabo/SQLMaker/MySQL.pm
SHA1 7ada47ae40a0606442200d4cfd9640f846f44c23 lib/Alzabo/SQLMaker/PostgreSQL.pm
SHA1 ac979e736d929e412e119e8271eeb3693046acfa lib/Alzabo/Schema.pm
SHA1 9f63a5bdc1342935eb5a78e2eec25b76ae05b923 lib/Alzabo/Table.pm
SHA1 8b97a915e10af7ac222b88107286a5d577f43e81 lib/Alzabo/Utils.pm
SHA1 f847fb97ccb893a2d49081016b4d26837d7e118a mason/widgets/edit_field_checkbox
SHA1 1ec65e465abc63eef84dbd78b6b26b53de69c230 mason/widgets/edit_field_text_input
SHA1 2a39cb0dacfa7c5bd3f4b0192e2034125283d9fb mason/widgets/edit_field_textarea
SHA1 a7a0e78c774733204a23ac2ba71e7faf6433ce7e mason/widgets/fk_to_one_select
SHA1 6c5c2cbe53d95d8a38d7164f0351bcb808ea677c mason/widgets/insert
SHA1 316becaea6da6008a0aa54350344e22f47ddb1c5 mason/widgets/insert_or_update
SHA1 ef79ea559a1a6cb262d5245cfa596fbf28a18986 mason/widgets/update
SHA1 eff25867aade830f62008e9c5ab0479ed30b8d69 t/01-compile.t
SHA1 94036f06e5f7a955326e8da71488f01c53b1f215 t/01-driver.t
SHA1 313c1ce15ce1b3720d5704ffbd823a43e0f5e833 t/02-create.t
SHA1 7eec7b4cd0bdccecf807ae241def67a1970f2a20 t/03-runtime.t
SHA1 0a68944ae8d4231f82b9873bc34436e41f7ec909 t/04-rev-engineer.t
SHA1 d22a8f365a9cc9853d7f690a62660c0892940b6e t/05a-rules-mysql.t
SHA1 cf1c5704e531920b7235a403e1c079c177b40803 t/05b-rules-pg.t
|
lib/Alzabo/Design.pod
view on Meta::CPAN
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | It is the sole interface by which actual data is retrieved, updated,
or deleted in a table.
The various C<RowState> classes are used in order to change a row's
behavior depending on whether it is live, live and cached, potential,
or deleted.
|
lib/Alzabo/Driver.pm
view on Meta::CPAN
786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 | |
lib/Alzabo/Driver.pm
view on Meta::CPAN
868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 | For backwards compatibility, this is also available as C<next_hash()>.
Returns a hash containing the next row of data for statement or an
empty list if no more data is available. All the keys of the hash
will be lowercased.
Throws: L<C<Alzabo::Exception::Driver>|Alzabo::Exceptions>
|
lib/Alzabo/Intro.pod
view on Meta::CPAN
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 |
my $person = $person_t ->row_by_pk( pk => 42 );
my $cursor =
$person ->rows_by_foreign_key
( foreign_key =>
$person_t ->foreign_keys_by_table( $credit_t ) );
print $person -> select ( 'name' ), " was in the following films:\n\n" ;
while ( my $credit = $cursor -> next )
{
my $movie =
$credit ->rows_by_foreign_key
( foreign_key =>
$credit_t ->foreign_keys_by_table( $movie_t ) )-> next ;
my $job =
$credit ->rows_by_foreign_key
( foreign_key =>
$credit_t ->foreign_keys_by_table( $job_t ) )-> next ;
print $movie -> select ( 'title' ), " released in " , $movie -> select ( 'release_year' ), "\n" ;
print ' ' , $job ->( 'job' ), "\n" ;
}
A more sophisticated version of this code would take into account that
a person can do more than one job in the same movie.
The method names are quite verbose, so let's redo the example using
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>:
|
lib/Alzabo/Intro.pod
view on Meta::CPAN
208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 | if ( $data {parent_location_id} )
{
my $parent_location_id = $data {parent_location_id};
my $location_t = $schema ->table( 'Location' );
while ( my $location =
$location_t ->row_by_pk( pk => $parent_location_id ) )
{
die "Insert into location would create loop"
if $location -> select ( 'parent_location_id' ) == $data {location_id};
$parent_location_id = $location -> select ( 'parent_location_id' );
}
}
}
Once again, let's rewrite the code to use
L<C<Alzabo::MethodMaker>|Alzabo::MethodMaker>:
sub update_location
{
my $self = shift ;
|
lib/Alzabo/Intro.pod
view on Meta::CPAN
241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 | |
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | linking_tables
lookup_columns
row_columns
self_relations
tables
table_columns
insert_hooks
update_hooks
select_hooks
delete_hooks
);
sub import
{
my $class = shift ;
validate( @_ , { schema => { type => SCALAR },
class_root => { type => SCALAR,
optional => 1 },
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 | if ( $self ->{opts}{row_columns} )
{
$self ->make_row_column_methods( $t );
}
if ( grep { $self ->{opts}{ $_ } } qw( foreign_keys linking_tables lookup_columns ) )
{
$self ->make_foreign_key_methods( $t );
}
foreach ( qw( insert update select delete ) )
{
if ( $self ->{opts}{ "$_\_hooks" } )
{
$self ->make_hooks( $t , $_ );
}
}
}
}
sub eval_schema_class
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 | my $name = $self ->_make_method
( type => 'row_column' ,
class => $self ->{row_class},
returns => 'scalar value/takes new value' ,
code => sub { my $self = shift ;
if ( @_ )
{
$self ->update( $col_name => $_ [0] );
}
return $self -> select ( $col_name ); },
column => $c ,
) or next ;
$self ->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name ,
group => 'Methods that update/return a column value' ,
spec => [ { type => SCALAR } ],
description =>
"returns the value of the " . $c ->name . " column for a row. Given a value, it will also update the row first." ,
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 | @reverse_pairs = map { [ $_ ->[0], $_ ->[1]->name ] } $fk ->column_pairs;
}
my $table = $fk ->table_from;
my $name = $self ->_make_method
( type => 'self_relation' ,
class => $self ->{row_class},
returns => 'single row' ,
code => sub { my $self = shift ;
my @where = map { [ $_ ->[0], '=' , $self -> select ( $_ ->[1] ) ] } @pairs ;
return $table ->one_row( where => \ @where , @_ ); },
foreign_key => $fk ,
parent => 1,
) or last ;
if ( $name )
{
$self ->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name ,
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 | ) );
}
$name = $self ->_make_method
( type => 'self_relation' ,
class => $self ->{row_class},
returns => 'row cursor' ,
code =>
sub { my $self = shift ;
my %p = @_ ;
my @where = map { [ $_ ->[0], '=' , $self -> select ( $_ ->[1] ) ] } @reverse_pairs ;
if ( $p {where} )
{
@where = ( '(' , @where , ')' );
push @where ,
Alzabo::Utils::is_arrayref( $p {where}->[0] ) ? @{ $p {where} } : $p {where};
delete $p {where};
}
return $table ->rows_where( where => \ @where ,
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 | return unless $fk ->table_to->primary_key_size == $fk ->table_to->columns;
return unless ( $fk ->table_to->primary_key_size ==
( $fk ->table_from->primary_key_size + $fk_2 ->table_to->primary_key_size ) );
my $s = $fk ->table_to->schema;
my @t = ( $fk ->table_to, $fk_2 ->table_to );
my $select = [ $t [1] ];
my $name = $self ->_make_method
( type => 'linking_table' ,
class => $self ->{row_class},
returns => 'row cursor' ,
code =>
sub { my $self = shift ;
my %p = @_ ;
if ( $p {where} )
{
$p {where} = [ $p {where} ] unless Alzabo::Utils::is_arrayref( $p {where}[0] );
}
foreach my $pair ( $fk ->column_pairs )
{
push @{ $p {where} }, [ $pair ->[1], '=' , $self -> select ( $pair ->[0]->name ) ];
}
return $s -> join ( tables => [[ @t , $fk_2 ]],
select => $select ,
%p ); },
foreign_key => $fk ,
foreign_key_2 => $fk_2 ,
) or return ;
$self ->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name ,
group => 'Methods that follow a linking table' ,
description =>
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 | my $col_name = $_ ->name;
my $name = $self ->_make_method
( type => 'lookup_columns' ,
class => $self ->{row_class},
returns => 'scalar value of column' ,
code =>
sub { my $self = shift ;
my $row = $self ->rows_by_foreign_key( foreign_key => $fk , @_ );
return unless $row ;
return $row -> select ( $col_name ) },
foreign_key => $fk ,
column => $_ ,
) or next ;
$self ->{row_class}->add_method_docs
( Alzabo::MethodDocs->new
( name => $name ,
group => 'Methods that follow a lookup table' ,
description =>
"returns the value of " . ( join '.' , $fk ->table_to->name, $col_name ) . " for the given row by following the foreign key relationship" ,
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 | my $hooks =
$self ->_hooks_doc_string( $self ->{row_class}, 'pre_update' , 'post_update' );
$self ->{row_class}->add_class_docs
( Alzabo::ClassDocs->new
( group => 'Hooks' ,
description => "$hooks" ,
) );
}
sub make_select_hooks
{
my $self = shift ;
my ( $pre , $post ) = ( '' , '' );
$pre = " \$s->pre_select(\\\@cols);\n"
if $self ->{row_class}->can( 'pre_update' );
$post = " \$s->post_select(\\\%r);\n"
if $self ->{row_class}->can( 'post_update' );
eval <<"EOF";
{
package $self->{row_class};
sub select
{
my \$s = shift;
my \@cols = \@_;
return \$s->schema->run_in_transaction( sub {
$pre
my \@r;
my %r;
if (wantarray)
{
\@r{ \@cols } = \$s->SUPER::select(\@cols);
}
else
{
\$r{ \$cols[0] } = (scalar \$s->SUPER::select(\$cols[0]));
}
$post
return wantarray ? \@r{\@cols} : \$r{ \$cols[0] };
} );
}
sub select_hash
{
my \$s = shift;
my \@cols = \@_;
return \$s->schema->run_in_transaction( sub {
$pre
my \%r = \$s->SUPER::select_hash(\@cols);
$post
return \%r;
} );
}
}
EOF
Alzabo::Exception::Eval->throw( error => $@ ) if $@;
my $hooks =
$self ->_hooks_doc_string( $self ->{row_class}, 'pre_select' , 'post_select' );
$self ->{row_class}->add_class_docs
( Alzabo::ClassDocs->new
( group => 'Hooks' ,
description => "$hooks" ,
) );
}
sub make_delete_hooks
{
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 | |
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 | post-execution hooks to wrap around a number of methods. This allows
you to do data validation on inserts and updates as well as giving you
a chance to filter incoming or outgoing data as needed. For example,
this can be used to convert dates to and from a specific RDBMS
format .
All hooks are inside a transaction which is rolled back if any part of
the process fails.
It should be noted that Alzabo uses both the C<<
Alzabo::Runtime::Row-> select >> and C<< Alzabo::Runtime::Row-> delete >>
methods internally. If their behavior is radically altered through
the use of hooks, then some of Alzabo's functionality may be broken. Given this, it may be safer to create new methods to fetch and massage
data rather than to create post- select hooks that alter data.
Each of these hooks receives different parameters, documented below:
|
lib/Alzabo/MethodMaker.pm
view on Meta::CPAN
1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 | |
lib/Alzabo/MySQL.pod
view on Meta::CPAN
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | |
lib/Alzabo/QuickRef.pod
view on Meta::CPAN
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | |
lib/Alzabo/QuickRef.pod
view on Meta::CPAN
178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 | |
lib/Alzabo/Runtime/Column.pm
view on Meta::CPAN
75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 | Takes the following parameters:
|
lib/Alzabo/Runtime/ForeignKey.pm
view on Meta::CPAN
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 | {
my $self = shift ;
my $row = shift ;
my @update = grep { $_ ->nullable } $self ->columns_to;
return unless $self ->to_is_dependent || @update ;
my @where = map { [ $_ ->[1], '=' , $row -> select ( $_ ->[0]->name ) ] } $self ->column_pairs;
my $cursor = $self ->table_to->rows_where( where => \ @where );
while ( my $related_row = $cursor -> next )
{
next if $DELETED { $related_row ->id_as_string };
if ( $self ->to_is_dependent)
{
|
lib/Alzabo/Runtime/JoinCursor.pm
view on Meta::CPAN
117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 | |
lib/Alzabo/Runtime/Row.pm
view on Meta::CPAN
18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | Params::Validate::validation_options
( on_fail => sub { params_exception join '' , @_ } );
$VERSION = 2.0;
BEGIN
{
no strict 'refs' ;
foreach my $meth ( qw( select select_hash update refresh delete
id_as_string is_live is_potential is_deleted ) )
{
*{ __PACKAGE__ . "::$meth" } =
sub { my $s = shift ;
$s ->{state}-> $meth ( $s , @_ ) };
}
}
use constant NEW_SPEC => { table => { isa => 'Alzabo::Runtime::Table' }, pk => { type => SCALAR | HASHREF,
|
lib/Alzabo/Runtime/Row.pm
view on Meta::CPAN
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 | );
my $fk = delete $p {foreign_key};
if ( $p {where})
{
$p {where} = [ $p {where} ] unless Alzabo::Utils::is_arrayref( $p {where}[0] );
}
push @{ $p {where} },
map { [ $_ ->[1], '=' , $self -> select ( $_ ->[0]->name ) ] } $fk ->column_pairs;
return $fk ->is_one_to_many ? $fk ->table_to->rows_where( %p ) : $fk ->table_to->one_row( %p );
}
sub id_as_string_ext
{
|
lib/Alzabo/Runtime/Row.pm
view on Meta::CPAN
277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 | |
lib/Alzabo/Runtime/Row.pm
view on Meta::CPAN
314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 | Finally, there is an "in cache" state, which is identical to the
"live" state, except that it is used for object's that are cached via
the
L<C<Alzabo::Runtime::UniqueRowCache>|Alzabo::Runtime::UniqueRowCache>
class.
|
lib/Alzabo/Runtime/Row.pm
view on Meta::CPAN
440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 | |
lib/Alzabo/Runtime/RowCursor.pm
view on Meta::CPAN
101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | Alzabo::Runtime::RowCursor - Cursor that returns C<Alzabo::Runtime::Row> objects
|
lib/Alzabo/Runtime/RowState/Deleted.pm
view on Meta::CPAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | BEGIN
{
no strict 'refs' ;
foreach my $meth ( qw( select select_hash refresh update delete id_as_string ) )
{
*{__PACKAGE__ . "::$meth" } =
sub { $_ [1]->_no_such_row_error };
}
}
sub is_potential { 0 }
sub is_live { 0 }
|
lib/Alzabo/Runtime/RowState/InCache.pm
view on Meta::CPAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | BEGIN
{
no strict 'refs' ;
foreach my $meth ( qw( select select_hash ) )
{
my $super = "SUPER::$meth" ;
*{__PACKAGE__ . "::$meth" } =
sub { my $s = shift ;
$s ->refresh( @_ ) unless $s ->_in_cache( @_ );
$s -> $super ( @_ );
};
}
|
lib/Alzabo/Runtime/RowState/Live.pm
view on Meta::CPAN
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | return if isa_alzabo_exception( $e , 'Alzabo::Exception::NoSuchRow' );
rethrow_exception $e ;
}
}
unless ( keys %{ $row ->{data} } > keys %{ $row ->{pk} } )
{
my $sql = ( $row ->schema->sqlmaker->
select ( ( $row ->table->primary_key)[0] )->
from( $row ->table ) );
$class ->_where( $row , $sql );
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return
unless defined $row ->schema->driver->one_row( sql => $sql ->sql,
bind => $sql -> bind );
|
lib/Alzabo/Runtime/RowState/Live.pm
view on Meta::CPAN
81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 | $class ->_get_data( $row , @pre );
}
sub _get_data
{
my $class = shift ;
my $row = shift ;
my %data ;
my @select ;
foreach my $col ( @_ )
{
if ( exists $row ->{data}{ $col } )
{
$data { $col } = $row ->{data}{ $col };
}
else
{
push @select , $col ;
}
}
return %data unless @select ;
my $sql = ( $row ->schema->sqlmaker->
select ( $row ->table->columns( @select ) )->
from( $row ->table ) );
$class ->_where( $row , $sql );
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
my %d ;
@d { @select } =
$row ->schema->driver->one_row( sql => $sql ->sql,
bind => $sql -> bind )
or $row ->_no_such_row_error;
while ( my ( $k , $v ) = each %d )
{
$row ->{data}{ $k } = $data { $k } = $v ;
}
return %data ;
|
lib/Alzabo/Runtime/RowState/Live.pm
view on Meta::CPAN
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 | my $row = shift ;
my %p = @_ ;
return $row ->{id_string} if exists $row ->{id_string};
$row ->{id_string} = $row ->id_as_string_ext( pk => $row ->{pk},
table => $row ->table );
return $row ->{id_string};
}
sub select
{
my $class = shift ;
my $row = shift ;
my @cols = @_ ? @_ : map { $_ ->name } $row ->table->columns;
my %data = $class ->_get_data( $row , @cols );
return wantarray ? @data { @cols } : $data { $cols [0] };
}
sub select_hash
{
my $class = shift ;
my $row = shift ;
my @cols = @_ ? @_ : map { $_ ->name } $row ->table->columns;
return $class ->_get_data( $row , @cols );
}
sub update
|
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 | sub one_row
{
return shift -> join ( @_ )-> next ;
}
use constant JOIN_SPEC => { join => { type => ARRAYREF | OBJECT, optional => 1 },
tables => { type => ARRAYREF | OBJECT,
optional => 1 },
select => { type => ARRAYREF | OBJECT,
optional => 1 },
where => { type => ARRAYREF,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
distinct => { type => ARRAYREF | OBJECT,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
|
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 | if ( $p {order_by} )
{
$p {order_by} =
Alzabo::Utils::is_arrayref( $p {order_by} )
? $p {order_by}
: $p {order_by}
? [ $p {order_by} ]
: undef ;
}
my @select_tables = ( $p { select } ?
( Alzabo::Utils::is_arrayref( $p { select } ) ?
@{ $p { select } } : $p { select } ) :
$p {distinct} ?
@{ $p {distinct} } :
@tables );
my $sql = Alzabo::Runtime::sqlmaker( $self , \ %p );
my @select_cols ;
if ( $p {distinct} )
{
my %distinct = map { $_ => 1 } @{ $p {distinct} };
@select_cols = ( 'DISTINCT' ,
map { ( $_ ->primary_key,
$_ ->prefetch ?
$_ ->columns( $_ ->prefetch ) :
() ) }
@{ $p {distinct} }
);
foreach my $t ( @select_tables )
{
next if $distinct { $t };
push @select_cols , $t ->primary_key;
push @select_cols , $t ->columns( $t ->prefetch ) if $t ->prefetch;
}
if ( $p {order_by} && $sql ->distinct_requires_order_by_in_select )
{
my %select_cols = map { $_ => 1 } @select_cols ;
push @select_cols , grep { ref } @{ $p {order_by} };
}
@select_tables = ( @{ $p {distinct} }, grep { ! $distinct { $_ } } @select_tables );
}
else
{
@select_cols =
( map { ( $_ ->primary_key,
$_ ->prefetch ?
$_ ->columns( $_ ->prefetch ) :
() ) }
@select_tables );
}
$sql -> select ( @select_cols );
$self ->_join_all_tables( sql => $sql ,
join => $p { join } );
Alzabo::Runtime::process_where_clause( $sql , $p {where} ) if exists $p {where};
Alzabo::Runtime::process_order_by_clause( $sql , $p {order_by} )
if $p {order_by};
$sql ->limit( ref $p {limit} ? @{ $p {limit} } : $p {limit} ) if $p {limit};
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
my $statement = $self ->driver->statement( sql => $sql ->sql,
bind => $sql -> bind );
if ( @select_tables == 1)
{
return Alzabo::Runtime::RowCursor->new
( statement => $statement ,
table => $select_tables [0]->real_table,
);
}
else
{
return Alzabo::Runtime::JoinCursor->new
( statement => $statement ,
tables => [ map { $_ ->real_table } @select_tables ],
);
}
}
sub row_count
{
my $self = shift ;
my %p = @_ ;
return $self ->function( select => Alzabo::Runtime::sqlmaker( $self , \ %p )->COUNT( '*' ),
%p ,
);
}
sub function
{
my $self = shift ;
my %p = @_ ;
my $sql = $self ->_select_sql( %p );
my $method =
Alzabo::Utils::is_arrayref( $p { select } ) && @{ $p { select } } > 1 ? 'rows' : 'column' ;
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self ->driver-> $method ( sql => $sql ->sql,
bind => $sql -> bind );
}
sub select
{
my $self = shift ;
my $sql = $self ->_select_sql( @_ );
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self ->driver->statement( sql => $sql ->sql,
bind => $sql -> bind );
}
use constant _SELECT_SQL_SPEC => { join => { type => ARRAYREF | OBJECT, optional => 1 },
tables => { type => ARRAYREF | OBJECT,
optional => 1 },
select => { type => SCALAR | ARRAYREF | OBJECT,
optional => 1 },
where => { type => ARRAYREF,
optional => 1 },
group_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
having => { type => ARRAYREF,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
optional => 1 },
};
sub _select_sql
{
my $self = shift ;
my %p = validate( @_ , _SELECT_SQL_SPEC );
$p { join } ||= delete $p {tables};
$p { join } = [ $p { join } ] unless Alzabo::Utils::is_arrayref( $p { join } );
my @tables ;
if ( Alzabo::Utils::is_arrayref( $p { join }->[0] ) )
|
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 |
@tables = values %{ { map { $_ => $_ }
grep { Alzabo::Utils::safe_isa( 'Alzabo::Table' , $_ ) }
map { @$_ } @{ $p { join } } } };
}
else
{
@tables = grep { Alzabo::Utils::safe_isa( 'Alzabo::Table' , $_ ) } @{ $p { join } };
}
my @funcs = Alzabo::Utils::is_arrayref( $p { select } ) ? @{ $p { select } } : $p { select };
my $sql = ( Alzabo::Runtime::sqlmaker( $self , \ %p )->
select ( @funcs ) );
$self ->_join_all_tables( sql => $sql ,
join => $p { join } );
Alzabo::Runtime::process_where_clause( $sql , $p {where} )
if exists $p {where};
Alzabo::Runtime::process_group_by_clause( $sql , $p {group_by} )
if exists $p {group_by};
|
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 | SELECT ... FROM table_A
LEFT OUTER JOIN table_B ON ...
AND (table_B.size > 2 AND table_B.name != 'Foo' )
These restrictions are only allowed when performing an outer join ,
since there is no point in using them for regular inner joins. An
inner join restriction has the same effect when included in the
"WHERE" clause.
If the more multiple array reference of specifying tables is used and
no "select" parameter is provided, then the order of the rows returned
from calling L<C<< Alzabo::Runtime::JoinCursor-> next ()
>>|Alzabo::Runtime::JoinCursor/ next > is not guaranteed. In other
words, the array that the cursor returns will contain a row from each
table involved in the join , but the which row belongs to which table
cannot be determined except by examining the objects. The order will
be the same every time L<C<< Alzabo::Runtime::JoinCursor-> next ()
>>|Alzabo::Runtime::JoinCursor/ next > is called, however. It may be
easier to use the L<C<< Alzabo::Runtime::JoinCursor->next_as_hash() >>|Alzabo::Runtime::JoinCursor/next_as_hash> method in this case.
|
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 | |
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 | |
lib/Alzabo/Runtime/Schema.pm
view on Meta::CPAN
1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 | The object returned from the table functions more or less exactly like
a table object. When using this table to set where clause or order by
(or any other) conditions, it is important that the column objects for
these conditions be retrieved from the alias object.
For example:
my $foo_alias = $foo ->alias;
my $cursor = $schema -> join ( select => $foo ,
join => [ $foo , $bar , $foo_alias ],
where => [ [ $bar ->column( 'baz' ), '=' , 10 ],
[ $foo_alias ->column( 'quux' ), '=' , 100 ] ],
order_by => $foo_alias ->column( 'briz' ) );
If we were to use the C< $foo > object to retrieve the 'quux' and 'briz' columns then the join would simply not work as expected.
It is also possible to use multiple aliases of the same table in a join , so that this will work properly:
|
lib/Alzabo/Runtime/Table.pm
view on Meta::CPAN
342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 | sub _make_sql
{
my $self = shift ;
my %p = @_ ;
logic_exception "Can't make rows for tables without a primary key"
unless $self ->primary_key;
my $sql = ( Alzabo::Runtime::sqlmaker( $self ->schema, \ %p )->
select ( $self ->primary_key,
$self ->prefetch ? $self ->columns( $self ->prefetch ) : () )->
from( $self ) );
return $sql ;
}
sub _cursor_by_sql
{
my $self = shift ;
|
lib/Alzabo/Runtime/Table.pm
view on Meta::CPAN
400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 | );
}
sub row_count
{
my $self = shift ;
my %p = @_ ;
my $count = Alzabo::Runtime::sqlmaker( $self ->schema, \ %p )->COUNT( '*' );
return $self ->function( select => $count , %p );
}
sub function
{
my $self = shift ;
my %p = @_ ;
my $sql = $self ->_select_sql( %p );
my $method =
Alzabo::Utils::is_arrayref( $p { select } ) && @{ $p { select } } > 1 ? 'rows' : 'column' ;
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self ->schema->driver-> $method ( sql => $sql ->sql,
bind => $sql -> bind );
}
sub select
{
my $self = shift ;
my $sql = $self ->_select_sql( @_ );
$sql ->debug(\ *STDERR ) if Alzabo::Debug::SQL;
print STDERR Devel::StackTrace->new if Alzabo::Debug::TRACE;
return $self ->schema->driver->statement( sql => $sql ->sql,
bind => $sql -> bind );
}
_SELECT_SQL_SPEC => { select => { type => SCALAR | ARRAYREF | OBJECT },
where => { type => ARRAYREF | OBJECT,
optional => 1 },
order_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
group_by => { type => ARRAYREF | HASHREF | OBJECT,
optional => 1 },
having => { type => ARRAYREF,
optional => 1 },
limit => { type => SCALAR | ARRAYREF,
optional => 1 },
quote_identifiers => { type => BOOLEAN,
optional => 1 },
};
sub _select_sql
{
my $self = shift ;
my %p = validate( @_ , _SELECT_SQL_SPEC );
my @funcs = Alzabo::Utils::is_arrayref( $p { select } ) ? @{ $p { select } } : $p { select };
my $sql = Alzabo::Runtime::sqlmaker( $self ->schema, \ %p )-> select ( @funcs )->from( $self );
Alzabo::Runtime::process_where_clause( $sql , $p {where} )
if exists $p {where};
Alzabo::Runtime::process_group_by_clause( $sql , $p {group_by} )
if exists $p {group_by};
Alzabo::Runtime::process_having_clause( $sql , $p {having} )
if exists $p {having};
|
lib/Alzabo/Runtime/Table.pm
view on Meta::CPAN
844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 | This parameter can take one of two different values . The simplest
form is to just give it a single column object or SQL function.
Alternatively, you can give it an array reference to a list of column
objects, SQL functions and strings like this:
order_by => [ $col1 , COUNT( '*' ), $col2 , 'DESC' , $col3 , 'ASC' ]
It is important to note that you cannot simply use any arbitrary SQL function as part of your order by clause. You need to use a function that is exactly the same as one that was given as part of the "select"
parameter.
|
lib/Alzabo/Runtime/Table.pm
view on Meta::CPAN
978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 | |
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 | my $placeholder = do { my $x = 1; bless \ $x , 'Alzabo::SQLMaker::Placeholder' };
sub placeholder { $placeholder }
sub last_op
{
return shift ->{last_op};
}
sub select
{
my $self = shift ;
Alzabo::Exception::Params->throw( error => "The select method requires at least one parameter" )
unless @_ ;
$self ->{sql} .= 'SELECT ' ;
if ( lc $_ [0] eq 'distinct' )
{
$self ->{sql} .= ' DISTINCT ' ;
shift ;
}
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 | push @sql , $string ;
}
}
elsif ( ! ref $elt )
{
push @sql , $elt ;
}
else
{
Alzabo::Exception::SQL->throw
( error => 'Arguments to select must be either column objects,' .
' table objects, function objects, or plain scalars' );
}
}
$self ->{sql} .= join ', ' , @sql ;
$self ->{type} = 'select' ;
$self ->{last_op} = 'select' ;
return $self ;
}
sub from
{
my $self = shift ;
$self ->_assert_last_op( qw( select delete function ) );
my $spec =
$self ->{last_op} eq 'select' ? { type => OBJECT | ARRAYREF } : { can => 'alias_name' };
validate_pos( @_ , ( $spec ) x @_ );
$self ->{sql} .= ' FROM ' ;
if ( $self ->{last_op} eq 'delete' )
{
$self ->{sql} .=
join ', ' , map { ( $self ->{quote_identifiers} ?
$self ->{driver}->quote_identifier( $_ ->name ) :
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 | {
$sql .= $elt ->name . ' AS ' . $elt ->alias_name;
}
$self ->{tables}{ $elt } = 1;
}
$self ->{sql} .= $sql ;
}
if ( $self ->{type} eq 'select' )
{
foreach my $t ( keys %{ $self ->{column_tables} } )
{
unless ( $self ->{tables}{ $t } )
{
my $err = 'Cannot select column ' ;
$err .= 'unless its table is included in the FROM clause' ;
Alzabo::Exception::SQL->throw( error => $err );
}
}
}
$self ->{last_op} = 'from' ;
return $self ;
}
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 | if ( $comp eq 'BETWEEN' )
{
Alzabo::Exception::SQL->throw
( error => "The BETWEEN comparison operator requires an additional argument" )
unless @_ == 1;
my $rhs2 = shift ;
Alzabo::Exception::SQL->throw
( error => "The BETWEEN comparison operator cannot accept a subselect" )
if grep { Alzabo::Utils::safe_isa( $_ , 'Alzabo::SQLMaker' ) } $rhs , $rhs2 ;
$self ->{sql} .= ' BETWEEN ' ;
$self ->{sql} .= $self ->_rhs( $rhs );
$self ->{sql} .= " AND " ;
$self ->{sql} .= $self ->_rhs( $rhs2 );
return ;
}
if ( $comp eq 'IN' || $comp eq 'NOT IN' )
{
$self ->{sql} .= " $comp (" ;
$self ->{sql} .=
join ', ' , map { Alzabo::Utils::safe_isa( $_ , 'Alzabo::SQLMaker' )
? '(' . $self ->_subselect( $_ ) . ')'
: $self ->_rhs( $_ ) } $rhs , @_ ;
$self ->{sql} .= ')' ;
return ;
}
Alzabo::Exception::Params->throw
( error => 'Too many parameters to Alzabo::SQLMaker->condition method' )
if @_ ;
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 | Alzabo::Exception::SQL->throw
( error => "Cannot compare a column to a NULL with '$comp'" );
}
}
elsif ( ref $rhs )
{
$self ->{sql} .= " $comp " ;
if ( $rhs ->isa( 'Alzabo::SQLMaker' ) )
{
$self ->{sql} .= '(' ;
$self ->{sql} .= $self ->_subselect( $rhs );
$self ->{sql} .= ')' ;
}
else
{
$self ->{sql} .= $self ->_rhs( $rhs );
}
}
}
sub _rhs
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 | return ( $self ->{quote_identifiers} ?
$self ->{driver}->quote_identifier( $rhs ->table->alias_name, $rhs ->name ) :
$rhs ->table->alias_name . '.' . $rhs ->name );
}
else
{
return $self ->_bind_val( $rhs );
}
}
sub _subselect
{
my $self = shift ;
my $sql = shift ;
push @{ $self ->{ bind } }, @{ $sql -> bind };
return $sql ->sql;
}
sub order_by
{
my $self = shift ;
$self ->_assert_last_op( qw( select from condition group_by ) );
Alzabo::Exception::SQL->throw
( error => "Cannot use order by in a '$self->{type}' statement" )
unless $self ->{type} eq 'select' ;
validate_pos( @_ , ( { type => SCALAR | OBJECT,
callbacks =>
{ 'column_or_function_or_sort' =>
sub { Alzabo::Utils::safe_can( $_ [0], 'table' ) ||
Alzabo::Utils::safe_isa( $_ [0], 'Alzabo::SQLMaker::Function' ) ||
$_ [0] =~ /^(?:ASC|DESC)$/i } } }
) x @_ );
$self ->{sql} .= ' ORDER BY ' ;
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 | $self ->{last_op} = 'order_by' ;
return $self ;
}
sub group_by
{
my $self = shift ;
$self ->_assert_last_op( qw( select from condition ) );
Alzabo::Exception::SQL->throw
( error => "Cannot use group by in a '$self->{type}' statement" )
unless $self ->{type} eq 'select' ;
validate_pos( @_ , ( { can => 'table' } ) x @_ );
foreach my $c ( @_ )
{
unless ( $self ->{tables}{ $c ->table } )
{
my $err = 'Cannot use column (' ;
$err .= join '.' , $c ->table->name, $c ->name;
$err .= ") in $self->{type} unless its table is included in the FROM clause" ;
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 | sub get_limit
{
shift ()->_virtual;
}
sub sqlmaker_id
{
shift ()->_virtual;
}
sub distinct_requires_order_by_in_select { 0 }
sub _virtual
{
my $self = shift ;
my $sub = ( caller (1))[3];
$sub =~ s/.*::(.*?)$/$1/;
Alzabo::Exception::VirtualMethod->throw( error =>
"$sub is a virtual method and must be subclassed in " . ref $self );
}
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 | |
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 | L<C<from()>| "from (Alzabo::Table object, ...)" >
|
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 | |
lib/Alzabo/SQLMaker.pm
view on Meta::CPAN
1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 | |
lib/Alzabo/SQLMaker/MySQL.pm
view on Meta::CPAN
305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 | );
$MADE_FUNCTIONS = 1;
}
sub init
{
1;
}
sub select
{
my $self = shift ;
for ( my $i = 0; $i <= $#_ ; $i ++ )
{
if ( Alzabo::Utils::safe_isa( $_ [ $i ], 'Alzabo::SQLMaker::Function' ) &&
|
lib/Alzabo/SQLMaker/MySQL.pm
view on Meta::CPAN
335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 | Alzabo::Utils::safe_isa( $_ [ $i + 1 ], 'Alzabo::SQLMaker::Function' ) &&
$_ [ $i + 1 ]->as_string( $self ->{driver}, $self ->{quote_identifiers} ) =~
/^\s *IN BOOLEAN MODE/i )
{
$_ [ $i ] .= ' ' . $_ [ $i + 1]->as_string( $self ->{driver}, $self ->{quote_identifiers} );
splice @_ , $i + 1, 1;
}
}
}
$self ->SUPER:: select ( @_ );
}
sub condition
{
my $self = shift ;
|
lib/Alzabo/SQLMaker/MySQL.pm
view on Meta::CPAN
407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 | |
lib/Alzabo/SQLMaker/PostgreSQL.pm
view on Meta::CPAN
219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | $self ->{last_op} = 'limit' ;
return $self ;
}
sub get_limit
{
return undef ;
}
sub distinct_requires_order_by_in_select { 1 }
sub sqlmaker_id
{
return 'PostgreSQL' ;
}
1;
|
mason/widgets/edit_field_checkbox
view on Meta::CPAN
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | $row => undef
$class => $m ->base_comp->attr_if_exists( 'checkbox_class_default' )
</ %args >
< %init >
my $true ;
my $col_name = ref $column ? $column ->name : $column ;
if ( defined $row )
{
$true = $row -> select ( $col_name );
}
else
{
$true = $column -> default ;
}
</ %init >
|
mason/widgets/edit_field_text_input
view on Meta::CPAN
58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | $size => $m ->base_comp->attr_if_exists( 'text_input_size_default' ) || 30
$maxlength => $size
</ %args >
< %init >
my $val ;
my $col_name = ref $column ? $column ->name : $column ;
if ( defined $row )
{
$val = $row -> select ( $col_name );
}
$val = '' unless defined $val ;
$maxlength =
$column -> length && $column -> length < $maxlength ? $column -> length :
( ! $column ->is_character ? 10 : $maxlength );
if ( $maxlength > $size && exists $ARGS {size} )
{
|
mason/widgets/edit_field_textarea
view on Meta::CPAN
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 | $cols => $m ->base_comp->attr_if_exists( 'textarea_cols_default' ) || 40
$wrap => $m ->base_comp->attr_if_exists( 'textarea_wrap_default' ) || 'multiple'
</ %args >
< %init >
my $val ;
my $col_name = ref $column ? $column ->name : $column ;
if ( defined $row )
{
$val = $row -> select ( $col_name );
}
$val = '' unless defined $val ;
</ %init >
|
mason/widgets/fk_to_one_select
view on Meta::CPAN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | < %doc >
</ %doc >
< select name= "<% $col_from->name | h %>" class= "<% $class %>" >
% unless ( $fk ->from_is_dependent ) {
<option value= "" ></option>
% }
% foreach my $item ( @foreign_rows ) {
<option value= "<% $item->select( $col_to->name ) %>" <% defined $current && $item -> select ( $col_to ->name ) == $current ? 'selected="selected"' : '' %>><% $item -> select ( $display_column ->name ) | h %></option>
% }
</ select >
< %args >
$fk
$row => undef
@foreign_rows => ()
$class => $m ->base_comp->attr_if_exists( 'fk_to_one_select_class_default' )
$display_column => undef
</ %args >
< %init >
my @col_from = $fk ->columns_from;
return if @col_from > 1;
my $col_from = shift @col_from ;
my $col_to = $fk ->columns_to;
@foreign_rows = $col_to ->table->all_rows->all_rows unless @foreign_rows ;
my $current ;
$current = $row -> select ( $col_from ->name ) if $row ;
$display_column = $col_to unless defined $display_column ;
</ %init >
|
t/03-runtime.t
view on Meta::CPAN
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 | my $emp_t = $s ->table( 'employee' );
my $dep_t = $s ->table( 'department' );
my $proj_t = $s ->table( 'project' );
my $emp_proj_t = $s ->table( 'employee_project' );
my %dep ;
eval_ok( sub { $dep {borg} = $dep_t ->insert( values => { name => 'borging' } ) },
"Insert borging row into department table" );
is( $dep {borg}-> select ( 'name' ), 'borging' ,
"The borg department name should be 'borging'" );
{
my @all = $dep {borg}-> select ;
is( @all , 3,
"select with no columns should return all the values" );
is( $all [1], 'borging' ,
"The second value should be the department name" );
my %all = $dep {borg}->select_hash;
is( keys %all , 3,
"select_hash with no columns should return two keys" );
ok( exists $all {department_id},
"The returned hash should have a department_id key" );
ok( exists $all {name},
"The returned hash should have a department_id key" );
is( $all {name}, 'borging' ,
"The value of the name key be the department name" );
}
$dep {lying} = $dep_t ->insert( values => { name => 'lying to the public' } );
my $borg_id = $dep {borg}-> select ( 'department_id' );
delete $dep {borg};
eval_ok( sub { $dep {borg} = $dep_t ->row_by_pk( pk => $borg_id ) },
"Retrieve borg department row via row_by_pk method" );
isa_ok( $dep {borg}, 'Alzabo::Runtime::Row' ,
"Borg department" );
is( $dep {borg}-> select ( 'name' ), 'borging' ,
"Department's name should be 'borging'" );
eval { $dep_t ->insert( values => { name => 'will break' ,
manager_id => 1 } ); };
my $e = $@;
isa_ok( $e , 'Alzabo::Exception::ReferentialIntegrity' ,
"Exception thrown from attempt to insert a non-existent manager_id into department" );
my %emp ;
eval_ok( sub { $emp {bill} = $emp_t ->insert( values => { name => 'Big Bill' ,
dep_id => $borg_id ,
smell => 'robotic' ,
cash => 20.2,
} ) },
"Insert Big Bill into employee table" );
my %data = $emp {bill}->select_hash( 'name' , 'smell' );
is( $data {name}, 'Big Bill' ,
"select_hash - check name key" );
is( $data {smell}, 'robotic' ,
"select_hash - check smell key" );
is( $emp {bill}->is_live, 1,
"->is_live should be true for real row" );
eval { $emp_t ->insert( values => { name => undef ,
dep_id => $borg_id ,
smell => 'robotic' ,
cash => 20.2,
} ); };
|
t/03-runtime.t
view on Meta::CPAN
207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 | eval { $emp {bill}->update( dep_id => undef ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::Params' ,
"Exception thrown from attempt to update dep_id to NULL for an employee" );
{
my $updated = $emp {bill}->update( cash => undef , smell => 'hello!' );
ok( $updated , 'update() did change values' );
ok( ! defined $emp {bill}-> select ( 'cash' ),
"Bill has no cash" );
}
{
my $updated = $emp {bill}->update( cash => undef , smell => 'hello!' );
ok( ! $updated , 'update() did not change values' );
}
ok( $emp {bill}-> select ( 'smell' ) eq 'hello!' ,
"smell for bill should be 'hello!'" );
eval { $emp {bill}->update( name => undef ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::NotNullable' ,
"Exception thrown from attempt to update a non-nullable column to NULL" );
eval_ok( sub { $dep {borg}->update( manager_id => $emp {bill}-> select ( 'employee_id' ) ) },
"Set manager_id column for borg department" );
eval_ok( sub { $emp {2} = $emp_t ->insert( values =>
{ name => 'unit 2' ,
smell => 'good' ,
dep_id => $dep {lying}-> select ( 'department_id' ) } ) },
"Create employee 'unit 2'" );
my $emp2_id = $emp {2}-> select ( 'employee_id' );
delete $emp {2};
my $cursor ;
my $x = 0;
eval_ok( sub { $cursor =
$emp_t ->rows_where
( where => [ $emp_t ->column( 'employee_id' ), '=' , $emp2_id ] );
while ( my $row = $cursor -> next )
{
|
t/03-runtime.t
view on Meta::CPAN
256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 | $emp {2} = $row ;
}
},
"Retrieve 'unit 2' employee via rows_where method and cursor" );
is( $x , 1,
"Check count of rows found where employee_id == $emp2_id" );
is( $cursor ->count, 1,
"Make sure cursor's count() is accurate" );
is( $emp {2}-> select ( 'name' ), 'unit 2' ,
"Check that row found has name of 'unit 2'" );
{
my $row ;
eval_ok( sub { $row =
$emp_t ->one_row
( where =>
[ $emp_t ->column( 'employee_id' ), '=' , $emp2_id ] ) },
"Retrieve 'unit 2' employee via one_row method" );
is( $row -> select ( 'name' ), 'unit 2' ,
"Check that the single row returned has the name 'unit 2'" );
}
{
my $row ;
eval_ok( sub { $row =
$emp_t ->one_row
( where =>
[ $emp_t ->column( 'employee_id' ), '=' , $emp2_id ],
quote_identifiers => 1,
) },
"Retrieve 'unit 2' employee via one_row method with quote_identifiers" );
is( $row -> select ( 'name' ), 'unit 2' ,
"Check that the single row returned has the name 'unit 2'" );
}
my %proj ;
$proj {extend} = $proj_t ->insert( values => { name => 'Extend' ,
department_id => $dep {borg}-> select ( 'department_id' ) } );
$proj {embrace} = $proj_t ->insert( values => { name => 'Embrace' ,
department_id => $dep {borg}-> select ( 'department_id' ) } );
$emp_proj_t ->insert( values => { employee_id => $emp {bill}-> select ( 'employee_id' ),
project_id => $proj {extend}-> select ( 'project_id' ) } );
$emp_proj_t ->insert( values => { employee_id => $emp {bill}-> select ( 'employee_id' ),
project_id => $proj {embrace}-> select ( 'project_id' ) } );
my $fk = $emp_t ->foreign_keys_by_table( $emp_proj_t );
my @emp_proj ;
my @cursor_counts ;
eval_ok( sub { $cursor = $emp {bill}->rows_by_foreign_key( foreign_key => $fk );
while ( my $row = $cursor -> next )
{
push @emp_proj , $row ;
push @cursor_counts , $cursor ->count;
} },
"Fetch rows via ->rows_by_foreign_key method (expect cursor)" );
is( scalar @emp_proj , 2,
"Check that only two rows were returned" );
is( $emp_proj [0]-> select ( 'employee_id' ), $emp {bill}-> select ( 'employee_id' ),
"Check that employee_id in employee_project is same as bill's" );
is( $emp_proj [0]-> select ( 'project_id' ), $proj {extend}-> select ( 'project_id' ),
"Check that project_id in employee_project is same as extend project" );
foreach (1..2)
{
is( $cursor_counts [ $_ - 1], $_ ,
"cursor->count should be 1..2" );
}
my $emp_proj = $emp_proj [0];
$fk = $emp_proj_t ->foreign_keys_by_table( $emp_t );
my $emp ;
eval_ok( sub { $emp = $emp_proj ->rows_by_foreign_key( foreign_key => $fk ) },
"Fetch rows via ->rows_by_foreign_key method (expect row)" );
is( $emp -> select ( 'employee_id' ), $emp_proj -> select ( 'employee_id' ),
"The returned row should have bill's employee_id" );
$x = 0;
my @rows ;
eval_ok( sub { $cursor = $emp_t ->all_rows;
$x ++ while $cursor -> next
},
"Fetch all rows from employee table" );
is( $x , 2,
|
t/03-runtime.t
view on Meta::CPAN
352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 | is( $x , 2,
"Only 2 rows should be found after cursor reset" );
{
my $cursor ;
eval_ok( sub { $cursor =
$s -> join ( join => [ $emp_t , $emp_proj_t , $proj_t ],
where =>
[ $emp_t ->column( 'employee_id' ), '=' ,
$emp {bill}-> select ( 'employee_id' ) ],
order_by => $proj_t ->column( 'project_id' ),
quote_identifiers => 1,
) },
"Join employee, employee_project, and project tables where employee_id = bill's employee id with quote_identifiers" );
my @rows = $cursor -> next ;
is( scalar @rows , 3,
"3 rows per cursor ->next call" );
is( $rows [0]->table->name, 'employee' ,
"First row is from employee table" );
is( $rows [1]->table->name, 'employee_project' ,
"Second row is from employee_project table" );
is( $rows [2]->table->name, 'project' ,
"Third row is from project table" );
my $first_proj_id = $rows [2]-> select ( 'project_id' );
@rows = $cursor -> next ;
my $second_proj_id = $rows [2]-> select ( 'project_id' );
ok( $first_proj_id < $second_proj_id ,
"Order by clause should cause project rows to come back" .
" in ascending order of project id" );
}
{
my $cursor ;
eval_ok( sub { $cursor =
$s -> join ( join => [ $emp_t , $emp_proj_t , $proj_t ],
where =>
[ [ $proj_t ->column( 'project_id' ), '=' ,
$proj {extend}-> select ( 'project_id' ) ],
'or' ,
[ $proj_t ->column( 'project_id' ), '=' ,
$proj {embrace}-> select ( 'project_id' ) ],
],
order_by => $proj_t ->column( 'project_id' ) ) },
"Join employee, employee_project, and project tables with OR in where clause" );
1 while $cursor -> next ;
is( $cursor ->count, 2,
"join with OR in where clause should return two sets of rows" );
}
|
t/03-runtime.t
view on Meta::CPAN
432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 | is( $rows [1]->table->name, 'employee_project' ,
"Second row is from employee_project table" );
is( $rows [2]->table->name, 'project' ,
"Third row is from project table" );
}
{
my $p_alias = $proj_t ->alias;
eval_ok( sub { $cursor = $s -> join ( select => [ $p_alias , $proj_t ],
join => [ $p_alias , $emp_proj_t , $proj_t ],
where => [ [ $p_alias ->column( 'project_id' ), '=' , 1 ],
[ $proj_t ->column( 'project_id' ), '=' , 1 ] ],
) },
"Join employee_project and project table (twice) using aliases" );
my @rows = $cursor -> next ;
is( scalar @rows , 2,
"2 rows per cursor ->next call" );
|
t/03-runtime.t
view on Meta::CPAN
470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 | is( $rows [1]->table->name, 'employee_project' ,
"Second row is from employee_project table" );
is( $rows [2]->table->name, 'project' ,
"Third row is from project table" );
}
$cursor = $s -> join ( join => [ $emp_t , $emp_proj_t , $proj_t ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ],
order_by => [ $proj_t ->column( 'project_id' ), 'desc' ] );
@rows = $cursor -> next ;
my $first_proj_id = $rows [2]-> select ( 'project_id' );
@rows = $cursor -> next ;
my $second_proj_id = $rows [2]-> select ( 'project_id' );
ok( $first_proj_id > $second_proj_id ,
"Order by clause should cause project rows to come back in descending order of project id" );
$cursor = $s -> join ( join => [ $emp_t , $emp_proj_t , $proj_t ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ],
order_by => [ $proj_t ->column( 'project_id' ), 'desc' ] );
@rows = $cursor -> next ;
$first_proj_id = $rows [2]-> select ( 'project_id' );
@rows = $cursor -> next ;
$second_proj_id = $rows [2]-> select ( 'project_id' );
ok( $first_proj_id > $second_proj_id ,
"Order by clause (alternate form) should cause project rows to come back in descending order of project id" );
eval_ok( sub { $cursor = $s -> join ( select => [ $emp_t , $emp_proj_t , $proj_t ],
join => [ [ $emp_t , $emp_proj_t ],
[ $emp_proj_t , $proj_t ] ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ] ) },
"Join with join as arrayref of arrayrefs" );
@rows = $cursor -> next ;
is( scalar @rows , 3,
"3 rows per cursor ->next call" );
is( $rows [0]->table->name, 'employee' ,
|
t/03-runtime.t
view on Meta::CPAN
511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 | is( $rows [1]->table->name, 'employee_project' ,
"Second row is from employee_project table" );
is( $rows [2]->table->name, 'project' ,
"Third row is from project table" );
{
my $cursor ;
eval_ok( sub { $cursor = $s -> join ( join => [ [ $emp_t , $emp_proj_t ],
[ $emp_proj_t , $proj_t ] ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ] ) },
"Same join with no select parameter" );
my @rows = $cursor -> next ;
@rows = sort { $a ->table->name cmp $b ->table->name } @rows ;
is( scalar @rows , 3,
"3 rows per cursor ->next call" );
is( ( grep { $_ ->table->name eq 'employee' } @rows ), 1,
"First row is from employee table" );
is( ( grep { $_ ->table->name eq 'employee_project' } @rows ), 1,
"Second row is from employee_project table" );
is( ( grep { $_ ->table->name eq 'project' } @rows ), 1,
"Third row is from project table" );
}
eval { $s -> join ( select => [ $emp_t , $emp_proj_t , $proj_t ],
join => [ [ $emp_t , $emp_proj_t ],
[ $emp_proj_t , $proj_t ],
[ $s ->tables( 'outer_1' , 'outer_2' ) ] ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ] ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::Logic' ,
"Exception thrown from join with table map that does not connect" );
eval_ok( sub { @rows = $s -> join ( join => $emp_t ,
where => [ $emp_t ->column( 'employee_id' ), '=' , 1 ] )->all_rows },
"Join with a single table" );
is( @rows , 1,
"Only one row should be returned" );
is( $rows [0]-> select ( 'employee_id' ), 1,
"Returned employee should be employee number one" );
{
$s ->table( 'outer_2' )->insert( values => { outer_2_name => 'will match something' ,
outer_2_pk => 1 },
);
$s ->table( 'outer_2' )->insert( values => { outer_2_name => 'will match nothing' ,
outer_2_pk => 99 },
|
t/03-runtime.t
view on Meta::CPAN
568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 | );
$s ->table( 'outer_1' )->insert( values => { outer_1_name => 'test2 (has no matching join row)' ,
outer_2_pk => undef },
);
{
my $cursor ;
eval_ok( sub { $cursor =
$s -> join
( select => [ $s ->tables( 'outer_1' , 'outer_2' ) ],
join =>
[ left_outer_join =>
$s ->tables( 'outer_1' , 'outer_2' ) ]
) },
"Do a left outer join" );
my @sets = $cursor ->all_rows;
is( scalar @sets , 2,
"Left outer join should return 2 sets of rows" );
unless ( defined $sets [0]->[1] )
{
my $set = shift @sets ;
push @sets , $set ;
}
is( $sets [0]->[0]-> select ( 'outer_1_name' ), 'test1 (has matching join row)' ,
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets [0]->[1]-> select ( 'outer_2_name' ), 'will match something' ,
"The second row in the first set should have the name 'will match something'" );
is( $sets [1]->[0]-> select ( 'outer_1_name' ), 'test2 (has no matching join row)' ,
"The first row in the second set should have the name 'test12 (has no matching join row)'" );
ok( ! defined $sets [1]->[1],
"The second row in the second set should not be defined" );
}
{
my $cursor ;
eval_ok( sub { $cursor =
$s -> join
( select => [ $s ->tables( 'outer_1' , 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s ->tables( 'outer_1' , 'outer_2' ),
[ $s ->table( 'outer_2' )->column( 'outer_2_pk' ),
'!=' , 1 ],
] ],
order_by =>
$s ->table( 'outer_1' )->column( 'outer_1_name' )
) },
"Do a left outer join" );
my @sets = $cursor ->all_rows;
is( scalar @sets , 2,
"Left outer join should return 2 sets of rows" );
is( $sets [0]->[0]-> select ( 'outer_1_name' ), 'test1 (has matching join row)' ,
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets [0]->[1], undef ,
"The second row in the first set should be undef" );
is( $sets [1]->[0]-> select ( 'outer_1_name' ), 'test2 (has no matching join row)' ,
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets [1]->[1], undef ,
"The second row in the second set should be undef" );
}
{
my $fk = $s ->table( 'outer_1' )->foreign_keys_by_table( $s ->table( 'outer_2' ) );
my $cursor ;
eval_ok( sub { $cursor =
$s -> join
( select => [ $s ->tables( 'outer_1' , 'outer_2' ) ],
join =>
[ [ left_outer_join =>
$s ->tables( 'outer_1' , 'outer_2' ),
$fk ,
[ $s ->table( 'outer_2' )->column( 'outer_2_pk' ),
'!=' , 1 ],
] ],
order_by =>
$s ->table( 'outer_1' )->column( 'outer_1_name' )
) },
"Do a left outer join" );
my @sets = $cursor ->all_rows;
is( scalar @sets , 2,
"Left outer join should return 2 sets of rows" );
is( $sets [0]->[0]-> select ( 'outer_1_name' ), 'test1 (has matching join row)' ,
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets [0]->[1], undef ,
"The second row in the first set should be undef" );
is( $sets [1]->[0]-> select ( 'outer_1_name' ), 'test2 (has no matching join row)' ,
"The first row in the second set should have the name 'test1 (has matching join row)'" );
is( $sets [1]->[1], undef ,
"The second row in the second set should be undef" );
}
{
my $cursor ;
eval_ok( sub { $cursor =
$s -> join
( select => [ $s ->tables( 'outer_1' , 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s ->tables( 'outer_1' , 'outer_2' ) ] ]
) },
"Attempt a right outer join" );
my @sets = $cursor ->all_rows;
is( scalar @sets , 2,
"Right outer join should return 2 sets of rows" );
unless ( defined $sets [0]->[0] )
{
my $set = shift @sets ;
push @sets , $set ;
}
is( $sets [0]->[0]-> select ( 'outer_1_name' ), 'test1 (has matching join row)' ,
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets [0]->[1]-> select ( 'outer_2_name' ), 'will match something' ,
"The second row in the first set should have the name 'will match something'" );
ok( ! defined $sets [1]->[0],
"The first row in the second set should not be defined" );
is( $sets [1]->[1]-> select ( 'outer_2_name' ), 'will match nothing' ,
"The second row in the second set should have the name 'test12 (has no matching join row)'" );
}
{
my $cursor ;
my $fk = $s ->table( 'outer_1' )->foreign_keys_by_table( $s ->table( 'outer_2' ) );
eval_ok( sub { $cursor =
$s -> join
( select => [ $s ->tables( 'outer_1' , 'outer_2' ) ],
join =>
[ [ right_outer_join =>
$s ->tables( 'outer_1' , 'outer_2' ), $fk ] ]
) },
"Attempt a right outer join, with explicit foreign key" );
my @sets = $cursor ->all_rows;
is( scalar @sets , 2,
"Right outer join should return 2 sets of rows" );
unless ( defined $sets [0]->[0] )
{
my $set = shift @sets ;
push @sets , $set ;
}
is( $sets [0]->[0]-> select ( 'outer_1_name' ), 'test1 (has matching join row)' ,
"The first row in the first set should have the name 'test1 (has matching join row)'" );
is( $sets [0]->[1]-> select ( 'outer_2_name' ), 'will match something' ,
"The second row in the first set should have the name 'will match something'" );
ok( ! defined $sets [1]->[0],
"The first row in the second set should not be defined" );
is( $sets [1]->[1]-> select ( 'outer_2_name' ), 'will match nothing' ,
"The second row in the second set should have the name 'test12 (has no matching join row)'" );
}
}
my $id = $emp {bill}-> select ( 'employee_id' );
$emp {bill}-> delete ;
eval { $emp {bill}-> select ( 'name' ); };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::NoSuchRow' ,
"Exception thrown from attempt to select from deleted row object" );
{
my $row =
$emp_proj_t ->row_by_pk
( pk =>
{ employee_id => $id ,
project_id => $proj {extend}-> select ( 'project_id' ) } );
is( $row , undef ,
"make sure row was deleted by cascading delte" );
}
is( $dep {borg}-> select ( 'manager_id' ), 1,
"The manager_id for the borg department will be 1 because the object does not the database was changed" );
$dep {borg}->refresh;
my $dep_id = $dep {borg}-> select ( 'department_id' );
$emp_t ->insert( values => { name => 'bob' , smell => 'awful' , dep_id => $dep_id } );
$emp_t ->insert( values => { name => 'rachel' , smell => 'horrid' , dep_id => $dep_id } );
$emp_t ->insert( values => { name => 'al' , smell => 'bad' , dep_id => $dep_id } );
{
my @emps ;
eval_ok ( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'name' ) ] )->all_rows },
"Select all employee rows with arrayref to order_by" );
is( scalar @emps , 4,
"There should be 4 rows in the employee table" );
is( $emps [0]-> select ( 'name' ), 'al' ,
"First row name should be al" );
is( $emps [1]-> select ( 'name' ), 'bob' ,
"Second row name should be bob" );
is( $emps [2]-> select ( 'name' ), 'rachel' ,
"Third row name should be rachel" );
is( $emps [3]-> select ( 'name' ), 'unit 2' ,
"Fourth row name should be 'unit 2'" );
}
{
my @emps ;
eval_ok ( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'name' ) ],
quote_identifiers => 1,
)->all_rows },
"Select all employee rows with arrayref to order_by with quote_identifiers" );
is( scalar @emps , 4,
"There should be 4 rows in the employee table" );
is( $emps [0]-> select ( 'name' ), 'al' ,
"First row name should be al" );
is( $emps [1]-> select ( 'name' ), 'bob' ,
"Second row name should be bob" );
is( $emps [2]-> select ( 'name' ), 'rachel' ,
"Third row name should be rachel" );
is( $emps [3]-> select ( 'name' ), 'unit 2' ,
"Fourth row name should be 'unit 2'" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by => $emp_t ->column( 'name' ) )->all_rows },
"Select all employee rows with column obj to order_by" );
is( scalar @emps , 4,
"There should be 4 rows in the employee table" );
is( $emps [0]-> select ( 'name' ), 'al' ,
"First row name should be al" );
is( $emps [1]-> select ( 'name' ), 'bob' ,
"Second row name should be bob" );
is( $emps [2]-> select ( 'name' ), 'rachel' ,
"Third row name should be rachel" );
is( $emps [3]-> select ( 'name' ), 'unit 2' ,
"Fourth row name should be 'unit 2'" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by => [ $emp_t ->column( 'name' ) ] )->all_rows },
"Select all employee rows with arrayref to order_by" );
is( scalar @emps , 4,
"There should be 4 rows in the employee table" );
is( $emps [0]-> select ( 'name' ), 'al' ,
"First row name should be al" );
is( $emps [1]-> select ( 'name' ), 'bob' ,
"Second row name should be bob" );
is( $emps [2]-> select ( 'name' ), 'rachel' ,
"Third row name should be rachel" );
is( $emps [3]-> select ( 'name' ), 'unit 2' ,
"Fourth row name should be 'unit 2'" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'smell' ) ] )->all_rows },
"Select all employee rows with arrayref to order_by (by smell)" );
is( scalar @emps , 4,
"There should be 4 rows in the employee table" );
is( $emps [0]-> select ( 'name' ), 'bob' ,
"First row name should be bob" );
is( $emps [1]-> select ( 'name' ), 'al' ,
"Second row name should be al" );
is( $emps [2]-> select ( 'name' ), 'unit 2' ,
"Third row name should be 'unit 2'" );
is( $emps [3]-> select ( 'name' ), 'rachel' ,
"Fourth row name should be rachel" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'smell' ), 'desc' ] )->all_rows },
"Select all employee rows order by smell (descending)" );
is( $emps [0]-> select ( 'name' ), 'rachel' ,
"First row name should be rachel" );
is( $emps [1]-> select ( 'name' ), 'unit 2' ,
"Second row name should be 'unit 2'" );
is( $emps [2]-> select ( 'name' ), 'al' ,
"Third row name should be al" );
is( $emps [3]-> select ( 'name' ), 'bob' ,
"Fourth row name should be bob" );
}
eval_ok( sub { $count = $emp_t ->row_count },
"Call row_count for employee table" );
is( $count , 4,
"The count should be 4" );
eval_ok( sub { $count = $emp_t ->function( select => COUNT( $emp_t ->column( 'employee_id' ) ) ) },
"Get row count via ->function method" );
is( $count , 4,
"There should still be just 4 rows" );
{
my $one ;
eval_ok( sub { $one = $emp_t ->function( select => 1 ) },
"Get '1' via ->function method" );
is( $one , 1,
"Getting '1' via ->function should return 1" );
}
{
my $statement ;
eval_ok( sub { $statement = $emp_t -> select ( select => COUNT( $emp_t ->column( 'employee_id' ) ) ) },
"Get row count via even spiffier new ->select method" );
isa_ok( $statement , 'Alzabo::DriverStatement' ,
"Return value from Table->select method" );
$count = $statement -> next ;
is( $count , 4,
"There should still be just 4 rows" );
}
{
my $st ;
eval_ok( sub { $st = $emp_t -> select ( select => 1 ) },
"Get '1' via ->select method" );
is( $st -> next , 1,
"Getting '1' via ->select should return 1" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'smell' ), 'desc' ],
limit => 2 )->all_rows },
"Get all employee rows with ORDER BY and LIMIT" );
is( scalar @emps , 2,
"This should only return 2 rows" );
is( $emps [0]-> select ( 'name' ), 'rachel' ,
"First row should be rachel" );
is( $emps [1]-> select ( 'name' ), 'unit 2' ,
"Second row is 'unit 2'" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'smell' ), 'desc' ],
limit => [2, 2] )->all_rows },
"Get all employee rows with ORDER BY and LIMIT (with offset)" );
is( scalar @emps , 2,
"This should only return 2 rows" );
is( $emps [0]-> select ( 'name' ), 'al' ,
"First row should be al" );
is( $emps [1]-> select ( 'name' ), 'bob' ,
"Second row is bob" );
}
$emp_t ->set_prefetch( $emp_t ->columns( qw( name smell ) ) );
my @p = $emp_t ->prefetch;
is( scalar @p , 2,
"Prefetch method should return 2 column names" );
is( scalar ( grep { $_ eq 'name' } @p ), 1,
"One column should be 'name'" );
|
t/03-runtime.t
view on Meta::CPAN
976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 | "And the other should be 'smell'" );
is( $emp_t ->row_count, 4,
"employee table should have 4 rows" );
{
my @emps = $emp_t ->all_rows( order_by =>
[ $emp_t ->column( 'smell' ), 'desc' ],
limit => [2, 2] )->all_rows;
my $smell = $emps [0]-> select ( 'smell' );
is( $emp_t ->row_count( where => [ $emp_t ->column( 'smell' ), '=' , $smell ] ), 1,
"Call row_count method with where parameter." );
$emps [0]-> delete ;
eval { $emps [0]->update( smell => 'kaboom' ); };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::NoSuchRow' ,
"Exception thrown from attempt to update a deleted row" );
my $row_id = $emps [1]->id_as_string;
|
t/03-runtime.t
view on Meta::CPAN
1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 | smell => 'c' ,
dep_id => $dep_id } );
my $eid_c = $emp_t ->column( 'employee_id' );
{
my @emps = $emp_t ->rows_where( where => [ [ $eid_c , '=' , 9000 ],
'or' ,
[ $eid_c , '=' , 9002 ] ] )->all_rows;
@emps = sort { $a -> select ( 'employee_id' ) <=> $b -> select ( 'employee_id' ) } @emps ;
is( @emps , 2,
"Do a query with 'or' and count the rows" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"First row returned should be employee id 9000" );
is( $emps [1]-> select ( 'employee_id' ), 9002,
"Second row returned should be employee id 9002" );
}
{
my @emps = $emp_t ->rows_where( where => [ [ $emp_t ->column( 'smell' ), '!=' , 'c' ],
'and' ,
(
'(' ,
[ $eid_c , '=' , 9000 ],
'or' ,
[ $eid_c , '=' , 9002 ],
')' ,
),
] )->all_rows;
is( @emps , 1,
"Do another complex query with 'or' and subgroups" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"The row returned should be employee id 9000" );
}
{
my @emps = $emp_t ->rows_where( where => [ (
'(' ,
[ $eid_c , '=' , 9000 ],
'and' ,
[ $eid_c , '=' , 9000 ],
')' ,
|
t/03-runtime.t
view on Meta::CPAN
1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 | '(' ,
[ $eid_c , '=' , 9000 ],
'and' ,
[ $eid_c , '=' , 9000 ],
')' ,
),
] )->all_rows;
is( @emps , 1,
"Do another complex query with 'or', 'and' and subgroups" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"The row returned should be employee id 9000" );
}
{
my @emps = $emp_t ->rows_where( where => [ $eid_c , 'between' , 9000, 9002 ] )->all_rows;
@emps = sort { $a -> select ( 'employee_id' ) <=> $b -> select ( 'employee_id' ) } @emps ;
is( @emps , 3,
"Select using between should return 3 rows" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"First row returned should be employee id 9000" );
is( $emps [1]-> select ( 'employee_id' ), 9001,
"Second row returned should be employee id 9001" );
is( $emps [2]-> select ( 'employee_id' ), 9002,
"Third row returned should be employee id 9002" );
}
{
my @emps ;
eval_ok( sub { @emps = $emp_t ->rows_where( where => [ '(' , '(' ,
[ $eid_c , '=' , 9000 ],
')' , ')'
] )->all_rows },
"Nested subgroups should be allowed" );
is( @emps , 1,
"Query with nested subgroups should return 1 row" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"The row returned should be employee id 9000" );
}
$emp_t ->insert( values => { name => 'Smelly' ,
smell => 'a' ,
dep_id => $dep_id ,
} );
{
my @emps = eval { $emp_t ->rows_where( where => [ LENGTH( $emp_t ->column( 'smell' ) ), '=' , 1 ] )->all_rows };
|
t/03-runtime.t
view on Meta::CPAN
1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 | my @emps ;
eval_ok( sub { @emps = $emp_t ->rows_where( where => [ '(' ,
[ $emp_t ->column( 'employee_id' ), '=' , 9000 ],
')' ,
],
order_by => $emp_t ->column( 'employee_id' ) )->all_rows },
"Query with subgroup followed by order by" );
is( @emps , 1,
"Query with subgroup followed by order by should return 1 row" );
is( $emps [0]-> select ( 'employee_id' ), 9000,
"The row returned should be employee id 9000" );
}
my @smells = $emp_t ->function( select => [ $emp_t ->column( 'smell' ), COUNT( $emp_t ->column( 'smell' ) ) ],
group_by => $emp_t ->column( 'smell' ) );
my %smells = map { $_ ->[0] => $_ ->[1] } @smells ;
is( @smells , 6,
"Query with group by should return 6 values" );
is( $smells {a}, 2,
"Check count of smell = 'a'" );
is( $smells {b}, 1,
"Check count of smell = 'b'" );
is( $smells {c}, 1,
"Check count of smell = 'c'" );
is( $smells {awful}, 1,
"Check count of smell = 'awful'" );
is( $smells {good}, 1,
"Check count of smell = 'good'" );
is( $smells {horrid}, 1,
"Check count of smell = 'horrid'" );
{
my $statement = $emp_t -> select ( select => [ $emp_t ->column( 'smell' ), COUNT( $emp_t ->column( 'smell' ) ) ],
group_by => $emp_t ->column( 'smell' ) );
my @smells = $statement ->all_rows;
%smells = map { $_ ->[0] => $_ ->[1] } @smells ;
is( @smells , 6,
"Query with group by should return 6 values - via ->select" );
is( $smells {a}, 2,
"Check count of smell = 'a' - via ->select" );
is( $smells {b}, 1,
"Check count of smell = 'b' - via ->select" );
is( $smells {c}, 1,
"Check count of smell = 'c' - via ->select" );
is( $smells {awful}, 1,
"Check count of smell = 'awful' - via ->select" );
is( $smells {good}, 1,
"Check count of smell = 'good' - via ->select" );
is( $smells {horrid}, 1,
"Check count of smell = 'horrid' - via ->select" );
}
@rows = $emp_t ->function( select => $emp_t ->column( 'smell' ),
where => [ LENGTH( $emp_t ->column( 'smell' ) ), '=' , 1 ],
order_by => $emp_t ->column( 'smell' ) );
is( @rows , 4,
"There should only be four rows which have a single character smell" );
is( $rows [0], 'a' ,
"First smell should be 'a'" );
is( $rows [1], 'a' ,
"Second smell should be 'a'" );
is( $rows [2], 'b' ,
"Third smell should be 'b'" );
is( $rows [3], 'c' ,
"Fourth smell should be 'c'" );
{
my $statement = $emp_t -> select ( select => $emp_t ->column( 'smell' ),
where => [ LENGTH( $emp_t ->column( 'smell' ) ), '=' , 1 ],
order_by => $emp_t ->column( 'smell' ) );
my @rows = $statement ->all_rows;
is( @rows , 4,
"There should only be four rows which have a single character smell - via ->select" );
is( $rows [0], 'a' ,
"First smell should be 'a' - via ->select" );
is( $rows [1], 'a' ,
"Second smell should be 'a' - via ->select" );
is( $rows [2], 'b' ,
"Third smell should be 'b' - via ->select" );
is( $rows [3], 'c' ,
"Fourth smell should be 'c' - via ->select" );
}
@rows = $emp_t ->function( select => $emp_t ->column( 'smell' ),
where => [ LENGTH( $emp_t ->column( 'smell' ) ), '=' , 1 ],
order_by => $emp_t ->column( 'smell' ),
limit => 2,
);
is( @rows , 2,
"There should only be two rows which have a single character smell - with limit" );
is( $rows [0], 'a' ,
"First smell should be 'a' - with limit" );
is( $rows [1], 'a' ,
"Second smell should be 'a' - with limit" );
{
my $statement = $emp_t -> select ( select => $emp_t ->column( 'smell' ),
where => [ LENGTH( $emp_t ->column( 'smell' ) ), '=' , 1 ],
order_by => $emp_t ->column( 'smell' ),
limit => 2,
);
my @rows = $statement ->all_rows;
is( @rows , 2,
"There should only be two rows which have a single character smell - with limit via ->select" );
is( $rows [0], 'a' ,
"First smell should be 'a' - with limit via ->select" );
is( $rows [1], 'a' ,
"Second smell should be 'a' - with limit via ->select" );
}
my $extend_id = $proj {extend}-> select ( 'project_id' );
my $embrace_id = $proj {embrace}-> select ( 'project_id' );
foreach ( [ 9000, $extend_id ], [ 9000, $embrace_id ],
[ 9001, $extend_id ], [ 9002, $extend_id ] )
{
$emp_proj_t ->insert( values => { employee_id => $_ ->[0],
project_id => $_ ->[1] } );
}
@rows = $s ->function( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ) );
is( @rows , 2,
"Only two projects should be returned from schema->function" );
is( $rows [0][0], 'Embrace' ,
"First project should be Embrace" );
is( $rows [1][0], 'Extend' ,
"Second project should be Extend" );
is( $rows [0][1], 1,
"First project should have 1 employee" );
is( $rows [1][1], 3,
"Second project should have 3 employees" );
{
my $statement = $s -> select ( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ) );
my @rows = $statement ->all_rows;
is( @rows , 2,
"Only two projects should be returned from schema->select" );
is( $rows [0][0], 'Embrace' ,
"First project should be Embrace - via ->select" );
is( $rows [1][0], 'Extend' ,
"Second project should be Extend - via ->select" );
is( $rows [0][1], 1,
"First project should have 1 employee - via ->select" );
is( $rows [1][1], 3,
"Second project should have 3 employees - via ->select" );
}
@rows = $s ->function( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ),
limit => [1, 1],
);
is( @rows , 1,
"Only one projects should be returned from schema->function - with limit" );
is( $rows [0][0], 'Extend' ,
"First project should be Extend - with limit" );
is( $rows [0][1], 3,
"First project should have 3 employees - with limit" );
{
my $statement = $s -> select ( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ),
limit => [1, 1],
);
my @rows = $statement ->all_rows;
is( @rows , 1,
"Only one projects should be returned from schema->select - with limit via ->select" );
is( $rows [0][0], 'Extend' ,
"First project should be Extend - with limit via ->select" );
is( $rows [0][1], 3,
"First project should have 3 employees - with limit via ->select" );
}
{
my @rows = $s ->function( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ),
order_by => [ COUNT( $proj_t ->column( 'name' ) ), 'DESC' ] );
is( @rows , 2,
"Only two projects should be returned from schema->function ordered by COUNT(*)" );
is( $rows [0][0], 'Extend' ,
"First project should be Extend" );
is( $rows [1][0], 'Embrace' ,
"Second project should be Embrace" );
is( $rows [0][1], 3,
"First project should have 3 employee" );
is( $rows [1][1], 1,
"Second project should have 1 employees" );
}
{
my @rows = $s ->function( select => [ $proj_t ->column( 'name' ),
COUNT( $proj_t ->column( 'name' ) ) ],
join => [ $emp_proj_t , $proj_t ],
group_by => $proj_t ->column( 'name' ),
order_by => [ COUNT( $proj_t ->column( 'name' ) ), 'DESC' ],
having => [ COUNT( $proj_t ->column( 'name' ) ), '>' , 2 ],
);
is( @rows , 1,
"Only one project should be returned from schema->function ordered by COUNT(*) HAVING COUNT(*) > 2" );
is( $rows [0][0], 'Extend' ,
"First project should be Extend" );
is( $rows [0][1], 3,
"First project should have 3 employee" );
}
{
my @rows ;
eval_ok( sub { @rows = $s ->function( select => 1,
join => [ $emp_proj_t , $proj_t ],
) },
"Call schema->function with scalar select" );
is( @rows , 4,
"Should return four rows" );
}
{
my $st ;
eval_ok( sub { $st = $s -> select ( select => 1,
join => [ $emp_proj_t , $proj_t ],
) },
"Call schema->select with scalar select" );
my @rows = $st ->all_rows;
is( @rows , 4,
"Should return four rows" );
}
my $p1 = $proj_t ->insert( values => { name => 'P1' ,
department_id => $dep_id ,
} );
my $p2 = $proj_t ->insert( values => { name => 'P2' ,
department_id => $dep_id ,
} );
eval_ok( sub { $cursor = $s -> join ( distinct => $dep_t ,
join => [ $dep_t , $proj_t ],
where => [ $proj_t ->column( 'project_id' ), 'in' ,
map { $_ -> select ( 'project_id' ) } $p1 , $p2 ],
) },
"Do a join with distinct parameter set" );
@rows = $cursor ->all_rows;
is( scalar @rows , 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows [0]-> select ( 'department_id' ), $dep_id ,
"Returned row's department_id should be $dep_id" );
{
eval_ok( sub { $cursor =
$s -> join ( distinct => $emp_proj_t ,
join => [ $emp_t , $emp_proj_t ],
where => [ $emp_t ->column( 'employee_id' ), 'in' , 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor ->all_rows;
is( scalar @rows , 1,
"Setting distinct should cause only a single row to be returned" );
is( $rows [0]-> select ( 'employee_id' ), 9001,
"Returned row's employee_id should be 9001" );
}
{
eval_ok( sub { $cursor =
$s -> join
( distinct => [ $emp_t , $emp_proj_t ],
join => [ $emp_t , $emp_proj_t ],
where =>
[ $emp_t ->column( 'employee_id' ), 'in' , 9000, 9001 ],
) },
"Do a join with distinct parameter set to a table with a multi-col PK" );
@rows = $cursor ->all_rows;
is( scalar @rows , 3,
"Setting distinct should cause only three rows to be returned" );
ok( ( grep { $_ ->[0]-> select ( 'employee_id' ) == 9000 } @rows ),
"Returned rows should include employee_id 9000" );
ok( ( grep { $_ ->[0]-> select ( 'employee_id' ) == 9001 } @rows ),
"Returned rows should include employee_id 9001" );
}
{
$proj_t ->insert( values => { name => 'P99' ,
department_id => $dep {lying}-> select ( 'department_id' ),
} );
eval_ok( sub { $cursor = $s -> join ( distinct => $dep_t ,
join => [ $dep_t , $proj_t ],
order_by => $proj_t ->column( 'name' ),
) },
"Do a join with distinct and order_by not in select" );
@rows = $cursor ->all_rows;
if ( $rdbms eq 'pg' )
{
is( scalar @rows , 5, "distinct should cause only five rows to be returned" );
}
else
{
is( scalar @rows , 2, "distinct should cause only two rows to be returned" );
}
is( $rows [0]-> select ( 'department_id' ), $dep {borg}-> select ( 'department_id' ),
'first row is borg department' );
is( $rows [-1]-> select ( 'department_id' ), $dep {lying}-> select ( 'department_id' ),
'last row is lying department' );
undef $cursor ;
}
my $start_id = 999_990;
foreach ( [ qw( OB1 bad ) ],
|
t/03-runtime.t
view on Meta::CPAN
1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 | {
$emp_t ->insert( values => { employee_id => $start_id ++,
name => $_ ->[0],
smell => $_ ->[1],
dep_id => $dep_id } );
}
@rows = $emp_t ->rows_where( where => [ $emp_t ->column( 'employee_id' ), 'BETWEEN' ,
999_990, 999_996 ],
order_by => [ $emp_t ->columns( 'name' , 'smell' ) ] )->all_rows;
is( $rows [0]-> select ( 'name' ), 'OB1' ,
"First row name should be OB1" );
is( $rows [0]-> select ( 'smell' ), 'bad' ,
"First row smell should be bad" );
is( $rows [1]-> select ( 'name' ), 'OB1' ,
"Second row name should be OB1" );
is( $rows [1]-> select ( 'smell' ), 'worse' ,
"Second row smell should be bad" );
is( $rows [2]-> select ( 'name' ), 'OB2' ,
"Third row name should be OB2" );
is( $rows [2]-> select ( 'smell' ), 'bad' ,
"Third row smell should be bad" );
is( $rows [3]-> select ( 'name' ), 'OB2' ,
"Fourth row name should be OB2" );
is( $rows [3]-> select ( 'smell' ), 'worse' ,
"Fourth row smell should be worse" );
is( $rows [4]-> select ( 'name' ), 'OB3' ,
"Fifth row name should be OB3" );
is( $rows [4]-> select ( 'smell' ), 'awful' ,
"Fifth row smell should be awful" );
is( $rows [5]-> select ( 'name' ), 'OB3' ,
"Sixth row name should be OB3" );
is( $rows [5]-> select ( 'smell' ), 'bad' ,
"Sixth row smell should be bad" );
@rows = $emp_t ->rows_where( where => [ $emp_t ->column( 'employee_id' ), 'BETWEEN' ,
999_990, 999_996 ],
order_by => [ $emp_t ->column( 'name' ), 'desc' , $emp_t ->column( 'smell' ), 'asc' ] )->all_rows;
is( $rows [0]-> select ( 'name' ), 'OB3' ,
"First row name should be OB3" );
is( $rows [0]-> select ( 'smell' ), 'awful' ,
"First row smell should be awful" );
is( $rows [1]-> select ( 'name' ), 'OB3' ,
"Second row name should be OB3" );
is( $rows [1]-> select ( 'smell' ), 'bad' ,
"Second row smell should be bad" );
is( $rows [2]-> select ( 'name' ), 'OB2' ,
"Third row name should be OB2" );
is( $rows [2]-> select ( 'smell' ), 'bad' ,
"Third row smell should be bad" );
is( $rows [3]-> select ( 'name' ), 'OB2' ,
"Fourth row name should be OB2" );
is( $rows [3]-> select ( 'smell' ), 'worse' ,
"Fourth row smell should be worse" );
is( $rows [4]-> select ( 'name' ), 'OB1' ,
"Fifth row name should be OB1" );
is( $rows [4]-> select ( 'smell' ), 'bad' ,
"Fifth row smell should be bad" );
is( $rows [5]-> select ( 'name' ), 'OB1' ,
"Sixth row name should be OB1" );
is( $rows [5]-> select ( 'smell' ), 'worse' ,
"Sixth row smell should be worse" );
if ( $rdbms eq 'mysql' )
{
my $emp ;
eval_ok( sub { $emp = $emp_t ->insert( values => { name => UNIX_TIMESTAMP(),
dep_id => $dep_id } ) },
"Insert using SQL function UNIX_TIMESTAMP()" );
like( $emp -> select ( 'name' ), qr/\d+/ ,
"Name should be all digits (unix timestamp)" );
eval_ok( sub { $emp ->update( name => LOWER( 'FOO' ) ) },
"Do update using SQL function LOWER()" );
is( $emp -> select ( 'name' ), 'foo' ,
"Name should be 'foo'" );
eval_ok( sub { $emp ->update( name => REPEAT( 'Foo' , 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp -> select ( 'name' ), 'FooFooFoo' ,
"Name should be 'FooFooFoo'" );
eval_ok( sub { $emp ->update( name => UPPER( REPEAT( 'Foo' , 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp -> select ( 'name' ), 'FOOFOOFOO' ,
"Name should be 'FOOFOOFOO'" );
$emp_t ->insert( values => { name => 'Timestamp' ,
dep_id => $dep_id ,
tstamp => time - 100_000 } );
my $cursor ;
eval_ok( sub { $cursor =
$emp_t ->rows_where( where =>
[ [ $emp_t ->column( 'tstamp' ), '!=' , undef ],
[ $emp_t ->column( 'tstamp' ), '<' , UNIX_TIMESTAMP() ] ] ) },
"Do select with where condition that uses SQL function UNIX_TIMESTAMP()" );
my @rows = $cursor ->all_rows;
is( scalar @rows , 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows [0]-> select ( 'name' ), 'Timestamp' ,
"That row should be named Timestamp" );
my $snuffle_id = $emp_t ->insert( values => { name => 'snuffleupagus' ,
smell => 'invisible' ,
dep_id => $dep_id } )-> select ( 'employee_id' );
@rows = $emp_t ->rows_where( where => [ MATCH( $emp_t ->column( 'name' ) ), AGAINST( 'abathraspus' ) ] )->all_rows;
is( @rows , 0,
"Make sure that fulltext search doesn't give a false positive" );
@rows = $emp_t ->rows_where( where => [ MATCH( $emp_t ->column( 'name' ) ), AGAINST( 'snuffleupagus' ) ] )->all_rows;
is( @rows , 1,
"Make sure that fulltext search for snuffleupagus returns 1 row" );
is( $rows [0]-> select ( 'employee_id' ), $snuffle_id ,
"Make sure that the returned row is snuffleupagus" );
my $rows = $emp_t ->function( select => [ $emp_t ->column( 'employee_id' ), MATCH( $emp_t ->column( 'name' ) ), AGAINST( 'snuffleupagus' ) ],
where => [ MATCH( $emp_t ->column( 'name' ) ), AGAINST( 'snuffleupagus' ) ] );
my ( $id , $score ) = @$rows ;
is( $id , $snuffle_id ,
"Returned row should still be snuffleupagus" );
like( $score , qr/\d+(?:\.\d+)?/ ,
"Returned score should be some sort of number (integer or floating point)" );
ok( $score > 0,
"The score should be greater than 0 because the match was successful" );
eval_ok( sub { @rows = $emp_t ->all_rows( order_by => [ IF( 'employee_id < 100' ,
$emp_t ->column( 'employee_id' ),
$emp_t ->column( 'smell' ) ),
$emp_t ->column( 'employee_id' ),
],
)->all_rows },
"Order by IF() function" );
is( @rows , 16,
"Seventeen rows should have been returned" );
is( $rows [0]-> select ( 'employee_id' ), 3,
"First row should be id 3" );
is( $rows [-1]-> select ( 'employee_id' ), 999993,
"Last row should be id 999993" );
eval_ok( sub { @rows = $emp_t ->all_rows( order_by => RAND() )->all_rows },
"order by RAND()" );
is ( @rows , 16,
"This should return 16 rows" );
}
elsif ( $rdbms eq 'pg' )
{
my $emp ;
eval_ok( sub { $emp = $emp_t ->insert( values => { name => NOW(),
dep_id => $dep_id } ) },
"Do insert using SQL function NOW()" );
like( $emp -> select ( 'name' ), qr/\d+/ ,
"Name should be all digits (Postgres timestamp)" );
eval_ok( sub { $emp ->update( name => LOWER( 'FOO' ) ) },
"Do update using SQL function LOWER()" );
is( $emp -> select ( 'name' ), 'foo' ,
"Name should be 'foo'" );
eval_ok( sub { $emp ->update( name => REPEAT( 'Foo' , 3) ) },
"Do update using SQL function REPEAT()" );
is( $emp -> select ( 'name' ), 'FooFooFoo' ,
"Name should be 'FooFooFoo'" );
eval_ok( sub { $emp ->update( name => UPPER( REPEAT( 'Foo' , 3) ) ) },
"Do update using nested SQL functions UPPER(REPEAT())" );
is( $emp -> select ( 'name' ), 'FOOFOOFOO' ,
"Name should be 'FOOFOOFOO'" );
$emp_t ->insert( values => { name => 'Timestamp' ,
dep_id => $dep_id ,
tstamp => time - 100_000 } );
my $cursor ;
eval_ok( sub { $cursor =
$emp_t ->rows_where( where =>
[ [ $emp_t ->column( 'tstamp' ), '!=' , undef ],
[ $emp_t ->column( 'tstamp' ), '<' , NOW() ] ] ) },
"Do select with where condition that uses SQL function NOW()" );
my @rows = $cursor ->all_rows;
is( scalar @rows , 1,
"Only one row should have a timestamp value that is not null and that is less than the current time" );
is( $rows [0]-> select ( 'name' ), 'Timestamp' ,
"That row should be named Timestamp" );
}
my $p_emp ;
eval_ok( sub { $p_emp = $emp_t ->potential_row },
"Create potential row object" );
is( $p_emp ->is_live, 0,
"potential_row should ! ->is_live" );
is( $p_emp -> select ( 'smell' ), 'grotesque' ,
"Potential Employee should have default smell, 'grotesque'" );
{
my $updated = $p_emp ->update( cash => undef , smell => 'hello!' );
ok( $updated , 'update() did change values' );
ok( ! defined $p_emp -> select ( 'cash' ),
"Potential Employee cash column is not defined" );
}
{
my $updated = $p_emp ->update( cash => undef , smell => 'hello!' );
ok( ! $updated , 'update() did not change values' );
}
is( $p_emp -> select ( 'smell' ), 'hello!' ,
"smell for employee should be 'hello!' after update" );
$p_emp ->update( name => 'Ilya' );
is( $p_emp -> select ( 'name' ), 'Ilya' ,
"New employee got a name" );
$p_emp ->update( dep_id => $dep_id );
is( $p_emp -> select ( 'dep_id' ), $dep_id ,
"New employee got a department" );
eval { $p_emp ->update( wrong => 'column' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::Params' ,
"Exception thrown from attempt to update a column which doesn't exist" );
eval { $p_emp ->update( name => undef ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::NotNullable' ,
"Exception thrown from attempt to update a non-NULLable column in a potential row to null" );
eval_ok( sub { $p_emp ->make_live( values => { smell => 'cottony' } ) },
"Make potential row live" );
is( $p_emp -> select ( 'name' ), 'Ilya' ,
"Formerly potential employee row object should have same name as before" );
is( $p_emp -> select ( 'smell' ), 'cottony' ,
"Formerly potential employee row object should have new smell of 'cottony'" );
eval_ok ( sub { $p_emp -> delete },
"Delete new employee" );
eval_ok( sub { $p_emp = $emp_t ->potential_row( values => { cash => 100 } ) },
"Create potential row object and set some fields " );
is( $p_emp -> select ( 'cash' ), 100,
"Employee cash should be 100" );
eval { $emp_t ->rows_where( where => [ $eid_c , '=' , 9000,
$eid_c , '=' , 9002 ] ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception::Params' ,
"Exception from where clause as single arrayref with <>3 elements" );
{
|
t/03-runtime.t
view on Meta::CPAN
1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 | $s -> join ( join => [ $emp_t , $emp_proj_t , $proj_t ],
where => [ $emp_t ->column( 'employee_id' ), '=' , 9001 ] ) },
"Join with join as arrayref of arrayrefs" );
my @rows = $cursor -> next ;
is( scalar @rows , 3,
"3 rows per cursor ->next call" );
is( ( grep { defined } @rows ), 3,
"Make sure all rows are defined" );
is( $rows [0]-> select ( 'employee_id' ), 9001,
"First rows should have employee_id == 9001" );
is( $rows [0]-> select ( 'name' ), 'bob9001' ,
"First rows should have employee with name eq 'bob9001'" );
is( $rows [2]-> select ( 'name' ), 'Extend' ,
"First rows should have project with name eq 'Extend'" );
}
{
my $foo = $emp_t ->column( 'employee_id' )->alias( as => 'foo' );
my $st = $emp_t -> select ( select => $foo );
my %h = $st ->next_as_hash;
is( exists $h {foo}, 1,
"next_as_hash should return a hash with a 'foo' key" );
}
$s ->disconnect;
}
|
t/07-methodmaker.t
view on Meta::CPAN
141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 | eval { $s ->Location_t->insert( values => { location_id => 666,
location => 'post_die' } ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by post_insert" );
is( $e ->error, 'POST INSERT TEST' ,
"pre_insert error message should be POST INSERT TEST" );
my $tweaked = $s ->Location_t->insert( values => { location_id => 54321,
location => 'insert tweak me' } );
is ( $tweaked -> select ( 'location' ), 'insert tweaked' ,
"pre_insert should change the value of location to 'insert tweaked'" );
eval { $tweaked ->update( location => 'pre_die' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown from pre_update" );
is( $e ->error, 'PRE UPDATE TEST' ,
"pre_update error message should be PRE UPDATE TEST" );
eval { $tweaked ->update( location => 'post_die' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by post_update" );
is( $e ->error, 'POST UPDATE TEST' ,
"post_update error message should be POST UPDATE TEST" );
$tweaked ->update( location => 'update tweak me' );
is ( $tweaked -> select ( 'location' ), 'update tweaked' ,
"pre_update should change the value of location to 'update tweaked'" );
eval { $tweaked -> select ( 'pre_sel_die' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by pre_select" );
is( $e ->error, 'PRE SELECT TEST' ,
"pre_select error message should be PRE SELECT TEST" );
$tweaked ->update( location => 'post_sel_die' );
eval { $tweaked -> select ( 'location' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by post_select" );
is( $e ->error, 'POST SELECT TEST' ,
"post_select error message should be POST SELECT TEST" );
eval { $tweaked ->select_hash( 'location' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by post_select" );
is( $e ->error, 'POST SELECT TEST' ,
"post_select error message should be POST SELECT TEST" );
$tweaked ->update( location => 'select tweak me' );
is( $tweaked -> select ( 'location' ), 'select tweaked' ,
"post_select should change the value of location to 'select tweaked'" );
my %d = $tweaked ->select_hash( 'location' );
is( $d {location}, 'select tweaked' ,
"post_select_hash should change the value of location to 'select tweaked'" );
$s ->ToiletType_t->insert( values => { toilet_type_id => 1,
material => 'porcelain' ,
quality => 5 } );
my $t = $s ->Toilet_t->insert( values => { toilet_id => 1,
toilet_type_id => 1 } );
is( $t ->material, 'porcelain' ,
"New toilet's material method should return 'porcelain'" );
is( $t ->quality, 5,
|
t/07-methodmaker.t
view on Meta::CPAN
270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 | $e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by pre_update" );
eval { $p_row ->update( location => 'post_die' ); };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by post_update" );
$p_row ->update( location => 'update tweak me' );
is ( $p_row -> select ( 'location' ), 'update tweaked' ,
"pre_update should change the value of location to 'update tweaked'" );
eval { $p_row -> select ( 'pre_sel_die' ) };
$e = $@;
isa_ok( $e , 'Alzabo::Exception' ,
"Exception thrown by pre_select" );
$p_row ->update( location => 'select tweak me' );
is( $p_row -> select ( 'location' ), 'select tweaked' ,
"post_select should change the value of location to 'select tweaked'" );
%d = $p_row ->select_hash( 'location' );
is( $d {location}, 'select tweaked' ,
"post_select_hash should change the value of location to 'select tweaked'" );
$p_row ->make_live;
is( $p_row ->location_id, 999,
"Check that live row has same location id" );
my $alias = $s ->Toilet_t->alias;
can_ok( $alias , 'toilet_id_c' );
is( $alias ->toilet_id_c->name, $s ->Toilet_t->toilet_id_c->name,
"Alias column has the same name as real table's column" );
|
t/07-methodmaker.t
view on Meta::CPAN
551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 | my $p = shift ;
Alzabo::Exception->throw( error => "PRE INSERT TEST" ) if $p ->{ values }->{location} eq 'pre_die' ;
$p ->{ values }->{location} = 'insert tweaked' if $p ->{ values }->{location} eq 'insert tweak me' ;
}
sub post_insert
{
my $self = shift ;
my $p = shift ;
Alzabo::Exception->throw( error => "POST INSERT TEST" ) if $p ->{row}-> select ( 'location' ) eq 'post_die' ;
}
}
{
sub pre_update
{
my $self = shift ;
my $p = shift ;
Alzabo::Exception->throw( error => "PRE UPDATE TEST" ) if $p ->{location} && $p ->{location} eq 'pre_die' ;
|
t/07-methodmaker.t
view on Meta::CPAN
573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 | $p ->{location} = 'update tweaked' if $p ->{location} && $p ->{location} eq 'update tweak me' ;
}
sub post_update
{
my $self = shift ;
my $p = shift ;
Alzabo::Exception->throw( error => "POST UPDATE TEST" ) if $p ->{location} && $p ->{location} eq 'post_die' ;
}
sub pre_select
{
my $self = shift ;
my $cols = shift ;
Alzabo::Exception->throw( error => "PRE SELECT TEST" ) if grep { $_ eq 'pre_sel_die' } @$cols ;
}
sub post_select
{
my $self = shift ;
my $data = shift ;
Alzabo::Exception->throw( error => "POST SELECT TEST" ) if exists $data ->{location} && $data ->{location} eq 'post_sel_die' ;
$data ->{location} = 'select tweaked' if exists $data ->{location} && $data ->{location} eq 'select tweak me' ;
}
sub pre_delete
{
my $self = shift ;
Alzabo::Exception->throw( error => "PRE DELETE TEST" ) if $self -> select ( 'location' ) eq 'pre_del_die' ;
}
sub post_delete
{
my $self = shift ;
}
}
1;
|
t/09-storable.t
view on Meta::CPAN
46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 | smell => 'bb' ,
dep_id => 1 } );
my $ser ;
eval_ok( sub { my $row = $emp_t ->row_by_pk( pk => 98765 );
$ser = Storable::freeze( $row ) },
"Freeze employee" );
my $eid ;
eval_ok( sub { my $row = Storable::thaw( $ser );
$eid = $row -> select ( 'employee_id' ) },
"Thaw employee" );
is( $eid , 98765,
"Employee survived freeze & thaw" );
eval_ok( sub { my $row = $emp_t ->row_by_pk( pk => 98765 );
$ser = Storable::nfreeze( $row ) },
"NFreeze employee" );
my $smell ;
eval_ok( sub { my $row = Storable::thaw( $ser );
$smell = $row -> select ( 'smell' ) },
"Thaw employee" );
is( $smell , 'bb' ,
"Employee survived nfreeze & thaw" );
eval_ok( sub { my $p_row = $emp_t ->potential_row( values => { name => 'Alice' } );
$ser = Storable::freeze( $p_row ) },
"Freeze potential employee" );
my $name ;
eval_ok( sub { my $p_row = Storable::thaw( $ser );
$name = $p_row -> select ( 'name' ) },
"Thaw potential employee" );
is( $name , 'Alice' ,
"Potential employee survived freeze & thaw" );
}
|