Provide abstractions for
- sequences (auto increment)
- limits and offsets
- value binding
- MySQL's REPLACE INTO for Oracle (or INSERT ... ON DUPLICATE KEY UPDATE)
Two favourites for SQL generation: SQL::Abstract::More and DBIx::Custom.
DBIx::Connector can be used for DB interaction (DBI wrapper) as it supports forking, auto-reconnection and transactions.
- (-) 34 dependencies
- (+) clearly focuses on SQL generation (no DB interaction)
- (-) more complex syntax than DBIx::Custom
- (+) supports different LIMIT implementations
- (+) 3 dependencies
- (+) nice syntax with variable placeholders
- (-) JOIN support only via literal strings
- (-) no LIMIT support
- (-) docs are spread on CPAN and Github
$sqla = SQL::Abstract::More->new(sql_dialect => 'Oracle');
$sqla->select(
-columns => ['col1', 'col2'],
-from => [-join => qw/Foo fk=pk Bar/],
-where => {
bar => [
{"=" => undef},
{"<" => 5}
]
},
# -where => \['bar IS NULL OR bar < ?', 'foo'], # same
-limit => 10,
-offset => 5,
);Result:
SELECT * FROM (SELECT subq_A.*, ROWNUM rownum__index FROM (
SELECT col1, col2
FROM Foo
INNER JOIN Bar ON Foo.fk = Bar.pk
WHERE bar IS NULL OR bar < 5
) subq_A WHERE ROWNUM <= 10) subq_B WHERE rownum__index >= 5my $dbi = DBIx::Custom->connect(...);
my $result = $dbi->select(
['col1', 'col2'],
table => 'Foo',
where => [
['bar IS NULL OR bar < :title'],
{title => 'foo'}
],
join => ['INNER JOIN Bar ON Foo.fk = Bar.pk'],
after_build_sql => sub {
# LIMIT clause would have to be added here...
},
);| Rating | Module | Last Update | Comment |
|---|---|---|---|
| +++ | DBIx::Custom | 2016-05 | Long term maintenance; nice syntax; only 3 deps; templating for bind variables; query can be modified using (after_build_sql); can use DBIx::Connector; docs could be better |
| +++ | SQL::Abstract::More | 2016-07 | Extends SQL::Abstract; 34 deps; LIMIT support; easy syntax |
| +++ | DBIx::Connector | 2016-03 | Long term maintenance; 7 deps; Supports forking, connection loss, transactions |
| ++ | Rose::DB | 2016-06 | Mature but low activity; 48 deps; transaction support; LIMIT support; sequence support; "datetime" support |
| + | DBIx::Lite | 2016-08 | Chained method syntax; |
| o | DBIx::ThinSQL | 2016-05 | Flexible query generation; quoting; outdated docs |
| o | Fey | 2015-06 | Query generation using Perl objects, but poor abstraction (e.g. LIMIT); operates on schema objects instead of strings (columns, tables) |
| o | SQL::Abstract | 2014-10 | Abstraction through data structures; Syntax bit weird at places: @where = (-and => [event_date => {'>=', '2/13/99'}, event_date => {'<=', '4/24/03'} ]); |
| - | DBIx::Abstract | 2014-01 | DEPRICATED (sic!) |
| - | SQL::QueryBuilder::OO | 2016-03 | Syntax too noisy |
| - | DBIx::Sunny | 2014-05 | Only supports MySQL and SQlite |
| - | Otogiri | 2016-02 | Based on DBIx::Sunny |
| - | SQL::Maker | 2014-12 | Only MySQL-like LIMIT handling |
| - | DBIx::Simple | 2011-01 | Outdated |
- https://metacpan.org/source/DAVEBAIRD/SQL-Abstract-Limit-0.141/lib/SQL/Abstract/Limit.pm
- https://metacpan.org/source/RSANDBERG/DBIx-IO-1.07/IO/OracleIO.pm
- https://metacpan.org/source/JSIRACUSA/Rose-DB-0.778/lib/Rose/DB/Oracle.pm
- DBI-1.636
- DBD-SQLite-1.50
- Object-Simple-3.1702
- YAML-Tiny-1.69
- File-Remove-1.57
- Test-Requires-0.10
- Module-ScanDeps-1.21
- JSON-PP-2.27400 (upgraded from 2.27203)
- CPAN-Meta-2.150010 (upgraded from 2.140640)
- Module-Build-0.4220 (upgraded from 0.4205)
- Module-Install-1.16
- Module-Runtime-0.014
- Try-Tiny-0.27
- Module-Implementation-0.09
- Params-Validate-1.24
- Dist-CheckConflicts-0.11
- Package-Stash-XS-0.28
- Package-Stash-0.37
- Sub-Exporter-Progressive-0.001012
- Variable-Magic-0.60
- B-Hooks-EndOfScope-0.21
- namespace-clean-0.27
- Clone-0.38
- Hash-Merge-0.200
- Sub-Uplevel-0.2600
- Test-Warn-0.30
- Test-Exception-0.43
- Test-Deep-1.120
- Class-Method-Modifiers-2.12
- Role-Tiny-2.000003
- Devel-GlobalDestruction-0.13
- Moo-2.002004
- MRO-Compat-0.12
- SQL-Abstract-1.81
- XSLoader-0.24 (upgraded from 0.17)
- Exporter-Tiny-0.042
- List-MoreUtils-0.416
- JSON-PP-2.27400 (upgraded from 2.27203)
- CPAN-Meta-2.150010 (upgraded from 2.140640)
- Module-Build-0.4220 (upgraded from 0.4205)
- DBI-1.636
- Sub-Identify-0.12
- SUPER-1.20141117
- Test-MockModule-0.11
- JSON-PP-2.27400 (upgraded from 2.27203)
- CPAN-Meta-2.150010 (upgraded from 2.140640)
- Module-Build-0.4220 (upgraded from 0.4205)
- Sub-Install-0.928
- Params-Util-1.07
- Data-OptList-0.110
- Sub-Exporter-0.987
- SQL-ReservedWords-0.8
- Rose-Object-0.860
- Class-Factory-Util-1.7
- Module-Runtime-0.014
- Dist-CheckConflicts-0.11
- Try-Tiny-0.27
- Module-Implementation-0.09
- Params-Validate-1.24
- Variable-Magic-0.60
- Sub-Exporter-Progressive-0.001012
- B-Hooks-EndOfScope-0.21
- Package-Stash-XS-0.28
- Package-Stash-0.37
- namespace-clean-0.27
- Sub-Identify-0.12
- namespace-autoclean-0.28
- Class-Singleton-1.5
- DateTime-TimeZone-2.01
- Scalar-List-Utils-1.45 (upgraded from 1.38)
- DateTime-Locale-1.05
- DateTime-1.36
- Sub-Name-0.19
- Package-DeprecationManager-0.17
- DateTime-Format-Strptime-1.68
- DateTime-Format-Builder-0.81
- ExtUtils-Helpers-0.025
- ExtUtils-Config-0.008
- ExtUtils-InstallPaths-0.011
- Module-Build-Tiny-0.039
- Convert-NLS_DATE_FORMAT-0.06
- DateTime-Format-Oracle-0.06
- DateTime-Format-Pg-0.16012
- Clone-PP-1.06
- DateTime-Format-MySQL-0.06
- DBI-1.636
- Rose-DateTime-0.540
- Time-Clock-1.03
- Sub-Uplevel-0.2600
- Test-Exception-0.43
- Carp-Clan-6.06
- Bit-Vector-7.4