The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
App-AutoCRUD-0.15
River stage zero No dependents
3 ++
/ App::AutoCRUD

NAME

App::AutoCRUD - A Plack application for browsing and editing databases

SYNOPSIS

Quick demo

To see the demo distributed with this application :

  cd examples/Chinook
  plackup app.psgi

Then point your browser to http://localhost:5000.

General startup

Create a configuration file, for example in YAML format, like this :

  app:
    name: Test AutoCRUD

  datasources :
    Source1 :
      dbh:
        connect:
            # arguments that will be passed to DBI->connect(...)
            # for example :
          - dbi:SQLite:dbname=some_file
          - "" # user
          - "" # password
          - RaiseError    : 1
            sqlite_unicode: 1

Create a file crud.psgi like this :

  use App::AutoCRUD;
  use YAML qw/LoadFile/;
  my $config = LoadFile "/path/to/config.yaml";
  my $crud   = App::AutoCRUD->new(config => $config);
  my $app    = $crud->to_app;

Then run the app

  plackup crud.psgi

or mount the app in Apache

  <Location /crud>
    SetHandler perl-script
    PerlResponseHandler Plack::Handler::Apache2
    PerlSetVar psgi_app /path/to/crud.psgi
  </Location>

and use your favorite web browser to navigate through your database.

DESCRIPTION

This module embodies a web application for Creating, Retrieving, Updating and Deleting records in relational databases (hence the 'CRUD' acronym). The 'Auto' part of the name is because the application automatically generates and immediately uses the components needed to work with your data -- you don't have to edit scaffolding code. The 'Plack' part of the name comes from the Plack middleware framework used to implement this application.

To connect to one or several databases, just supply a configuration file with the connnection information, and optionally some presentation information, and then you can directly work with the data. Optionally, the configuration file can also specify many additional details, like table groups, column groups, data descriptions, etc. If more customization is needed, then you can modify the presentation templates, or even subclass some parts of the framework.

This application was designed to be easy to integrate with other web resources in your organization : every table, every record, every search form has its own URL which can be linked from other sources, can be bookmarked, etc. This makes it a great tool for example for adding an admin interface to an existing application : just install AutoCRUD at a specific location within your Web server (with appropriate access control :-).

Some distinctive features of this module, in comparison with other CRUD applications, are :

  • Hyperlinks between records, corresponding to foreign key relationships in the database.

  • Support for update or delete of several records at once.

  • Support for reordering, masking, documenting tables and columns through configuration files -- a cheap way to provide reasonable user experience without investing into a full-fledged custom application.

  • Data export in Excel, YAML, JSON, XML formats

  • Extensibility through inheritance

This application is also meant as an example for showing the power of "Modern Perl", assembling several advanced frameworks such as Moose, Plack and DBIx::DataModel.

CONFIGURATION

The bare minimum for this application to run is to get some configuration information about how to connect to datasources. This can be done directly in Perl, like in the test file t/00_autocrud.t :

  my $connect_options = {
    RaiseError     => 1,
    sqlite_unicode => 1,
  };
  my $config = {
    app => {
      name => "SomeName"
    },
    datasources => {
      SomeDatabase => {
        dbh => {
          connect => [$dbi_connect_string, $user, $passwd, $connect_options],
        },
       },
     },
  };

  # instantiate the app
  my $crud = App::AutoCRUD->new(config => $config);
  my $app  = $crud->to_app;

With this minimal information, the application will just display tables and columns in alphabetical order. However, the configuration may also specify many details about grouping and ordering tables and columns; in that case, it is more convenient to use an external format like YAML, XML or AppConfig. Here is an excerpt from the YAML configuration for Chinook, a sample database distributed with this application (see the complete example under the examples/Chinook directory within this distribution) :

  datasources :
    Chinook :
      dbh:
        connect:
          - "dbi:SQLite:dbname=Chinook_Sqlite_AutoIncrementPKs.sqlite"
          - ""
          - ""
          - RaiseError: 1
            sqlite_unicode: 1

      tablegroups :
        - name: Music
          descr: Tables describing music content
          node: open
          tables :
            - Artist
            - Album
            - Track

        - name: Playlist
          descr: Tables for structuring playlists
          node: open
          tables :
            - Playlist
            - PlaylistTrack
  ...
      tables:
        Track:
          colgroups:
            - name: keys
              columns:
                - name: TrackId
                  descr: Primary key
                - name: AlbumId
                  descr: foreign key to the album where this track belongs
                - name: GenreId
                  descr: foreign key to the genre of this track
                - name: MediaTypeId
                  descr: foreign key to the media type of this track
            - name: Textual information
              columns:
                - name: Name
                  descr: name of this track
                - name: Composer
                  descr: name of composer of this track
            - name: Technical details
              columns:
                - name: Bytes
                - name: Milliseconds
            - name: Commercial details
              columns:
                - name: UnitPrice

The full datastructure for configuration information is documented in App::AutoCRUD::ConfigDomain.

USAGE

Generalities

All pages are presented with a Tree navigator. Tree sections can be folded/unfolded either through the mouse or through navigation keys LEFT and RIGHT. Keys DOWN and UP navigate to the next/previous sections. Typing the initial characters of a section title directly jumps to that section.

Homepage

The homepage displays the application short name, title, and the list of available datasources.

Schema

The schema page, for a given datasource, displays the list of tables, grouped and ordered according to the configuration (if any).

Each table has an immediate hyperlink to its search form; in addition, another link points to the description page for this table.

Table description

The description page for a given table presents the list of columns, with typing information as obtained from the database, and hyperlinks to other tables for which this table has foreign keys.

Search form

The search form allows users to enter search criteria and presentation parameters.

Search criteria

Within a column input field, one may enter a constant value, a list of values separated by commas, a partial word with an ending star (which will be interpreted as a SQL "LIKE" clause), a comparison operator (ex > 2013), or a BETWEEN clause (ex BETWEEN 2 AND 6).

The full syntax accepted for such criteria is documented in SQL::Abstract::FromQuery. That syntax is customizable, so if you want to support additional fancy operators for your database, you might do so by augmenting or subclassing the grammar.

Columns to display

On the right of each column input field is a checkbox to decide if this column should be displayed in the results or not. If the configuration specifies column groups, each column group also has a checkbox to simultaneously check all columns in that group. Finally, there is also a global checkbox to check/uncheck everything. If nothing is checked (which is the default), this will be implicitly interpreted as "SELECT *", i.e. showing everything.

Presentation parameters

Presentation parameters include :

  • pagination information (page size / page index)

  • output format, which is one of :

    html

    Default presentation view

    xlsx

    Export to Excel

    yaml

    YAML format

    json

    JSON format

    xml

    XML format

  • Flag for total page count (this is optional because it is not always important, and on many databases it has an additional cost as it requires an additional call to the database to know the total number of records).

List page

The list page displays a list of records resulting from a search. The generated SQL is shown for information. For columns that related to other tables, there are hyperlinks to the related lists.

Each record has a checkbox for marking this record for update or delete.

Hyperlinks to the next/previous page are provided, but navigation through pages can also be performed with the LEFT/RIGHT arrow keys.

Single record display

The single record page is very similar to the list page, but only displays one single record. The only difference is in the hyperlinks to update/delete/clone operations.

Update

The update page has two modes : single-record or multiple-records

Single-record update

The form shows current values on the right, and has input fields on the left. Only fields with some user input will be sent for update to the database.

Multiple-records update

This form is reached from the "List page", when several records were checked, or when updating the whole result set.

Input fields on the left correspond to the SQL "SET" clause, i.e. they specify values that will be updated within several records simultaneously.

Input fields on the right, labelled "where/and", specify some criteria for the SQL "WHERE" clause.

Needless to say, this is quite a powerful operation which if misused could easily corrupt your data.

Delete

Like updates, delete forms can be either single-record or multiple-records.

Insert

The insert form is very much like the single-record update form, except that there are no "current values"

Clone

The clone form is like an insert form, but pre-filled with the data to clone, except the primary key which is always empty.

ARCHITECTURE

[to be developed]

Classes

Modules are organized in a classical Model-View-Controller structure.

Inheritance and customization

All classes can be subclassed, and the application will automatically discover and load appropriate modules on demand. Presentation templates can also be overridden in sub-applications.

DataModel

This application requires a DBIx::DataModel::Schema subclass for every datasource. If none is supplied, a subclass will be generated and loaded on the fly; but this incurs an additional startup cost, and does not exploit all possibilities of DBIx::DataModel; so apart from short demos and experiments, it is better to statically generate a schema and store it in a file.

An initial schema class can be built, either from a DBI database handle, or from an existing DBIx::Class schema; see DBIx::DataModel::Schema::Generator.

ATTRIBUTES

config

A datatree of information, whose structure should comply with App::AutoCRUD::ConfigDomain.

name

The application name (displayed in most pages). This attribute defaults to the value of the app/name entry in config.

datasources

A hashref of the datasources served by this application. Hash keys are unique identifiers for the datasources (these names will also be used to generate URIs); hash values are instances of the App::AutoCRUD::DataSource class.

dir

The root directory where some application components could be placed (like for example some presentation templates).

This attribute defaults to the value of the dir entry in config, or, if absent, to the current directory.

This directory is associated with the application instance. When components are not found in this directory, they are searched in the directories associated with the application classes (see the share_path attribute below).

share_paths

An arrayref to a list of directories corresponding to the hierarchy of application classes. These directories are searched as second resort, when components are not found in the application instance directory.

readonly

A boolean to restrict actions available to only read from the database. The value of readonly boolean is set in YAML configuration file.

METHODS

new

  my $crud_app = App::AutoCRUD->new(%options);

Creates a new instance of the application. All attributes described above may be supplied as %options.

datasource

  my $datasource = $app->datasource($name);

Returnes the the datasource registered under the given name.

call

This method implements request dispatch, as required by the Plack middleware.

config

  my $data = $app->config(@path);

Walks through the configuration tree, following node names as specified in @path, and returns whatever is found at the end of this path ( either a subtree, or scalar data, or undef if the path leads to nothing ).

try_load_class

    my $class =  $self->try_load_class($name, $namespace);

Invokes "load_class" in Plack::Util; returns the loaded class in case of success, or undef in case of failure.

find_class

  my $class = $app->find_class($subclass_name);

Tries to find the given $subclass_name within the namespaces of the application classes.

is_class_loaded

Checks if the given class is already loaded in memory or not.

CAVEATS

In the current implementation, the slash charater ('/') is interpreted as a separator for primary keys over multiple columns. This means that an embedded slash in a column name or in the value of a primary key could yield unexpected results. This is definitely something to be improved in a future versions, but at the moment I still don't know how it will be solved.

ACKNOWLEDGEMENTS

Some design aspects were borrowed from

Catalyst
Catalyst::Helper::View::TTSite

AUTHOR

Laurent Dami, <dami at cpan.org>

SUPPORT

You can find documentation for this module with the perldoc command.

    perldoc App::AutoCRUD

You can also look for information at:

The source code is at https://github.com/damil/App-AutoCRUD.

SEE ALSO

Catalyst::Plugin::AutoCRUD, WebAPI::DBIC, Plack, http://www.codeplex.com/ChinookDatabase.

TODO

 - column properties
    - noinsert, noupdate, nosearch, etc.

 - edit: select or autocompleter for foreign keys

 - internationalisation
    - 

 - View:
    - default view should be defined in config
    - overridable content-type & headers 

  - search form, show associations => link to join search

  - list foreign keys even if not in DBIDM schema

  - change log

  - quoting problem (FromQuery: "J&B")

  - readonly fields: tabindex -1 (can be done by CSS?)
    in fact, current values should NOT be input fields, but plain SPANs

  - NULL in updates
  - Update form, focus problem (focus in field should deactivate TreeNav)
  - add insert link in table descr

  - deal with Favicon.ico

  - declare in http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

  - multicolumns : if there is an association over a multicolumns key,
     it is not displayed as a hyperlink in /list. To do so, we would need
     to add a line in the display, corresponding to the multicolumn.

LICENSE AND COPYRIGHT

Copyright 2014-2021 Laurent Dami.

This program is free software; you can redistribute it and/or modify it under the terms of the the Artistic License (2.0). You may obtain a copy of the full license at:

http://www.perlfoundation.org/artistic_license_2_0