Skip to content

Instantly share code, notes, and snippets.

@maxhq

maxhq/db-layer-new.md

Last active September 8, 2016 15:26
Show Gist options
  • Save maxhq/c0d4a44bc575a96a1842352f5a7b272e to your computer and use it in GitHub Desktop.
Save maxhq/c0d4a44bc575a96a1842352f5a7b272e to your computer and use it in GitHub Desktop.
Download ZIP
New DB layer
Raw
db-layer-new.md

Wishlist

Provide abstractions for

Conclusion / Summary

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.

No. 1 - SQL::Abstract::More

  • (-) 34 dependencies
  • (+) clearly focuses on SQL generation (no DB interaction)
  • (-) more complex syntax than DBIx::Custom
  • (+) supports different LIMIT implementations

No. 2 - DBIx::Custom

  • (+) 3 dependencies
  • (+) nice syntax with variable placeholders
  • (-) JOIN support only via literal strings
  • (-) no LIMIT support
  • (-) docs are spread on CPAN and Github

Code examples

SQL::Abstract::More

$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 >= 5

DBIx::Custom

my $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...
  },
);

Top Candidates

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

Inspiration

Dependencies

DBIx::Custom 0.37 (3 deps)

  • DBI-1.636
  • DBD-SQLite-1.50
  • Object-Simple-3.1702

SQL::Abstract::More 1.28 (34 deps)

  • 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

DBIx::Connector 0.56 (7 deps)

  • 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

Rose::DB 0.778 (48 deps)

  • 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment