view release on metacpan or  search on metacpan

lib/Catalyst/Manual/Tutorial/10_Appendices.pod  view on Meta::CPAN

        book_id     INTEGER REFERENCES books(id) ON DELETE CASCADE ON UPDATE CASCADE,
        author_id   INTEGER REFERENCES authors(id) ON DELETE CASCADE ON UPDATE CASCADE,
        PRIMARY KEY (book_id, author_id)
    );

    ---
    --- Load some sample data
    ---
    INSERT INTO books (title, rating) VALUES ('CCSP SNRS Exam Certification Guide', 5);
    INSERT INTO books (title, rating) VALUES ('TCP/IP Illustrated, Volume 1', 5);
    INSERT INTO books (title, rating) VALUES ('Internetworking with TCP/IP Vol.1', 4);
    INSERT INTO books (title, rating) VALUES ('Perl Cookbook', 5);
    INSERT INTO books (title, rating) VALUES ('Designing with Web Standards', 5);
    INSERT INTO authors (first_name, last_name) VALUES ('Greg', 'Bastien');
    INSERT INTO authors (first_name, last_name) VALUES ('Sara', 'Nasseh');
    INSERT INTO authors (first_name, last_name) VALUES ('Christian', 'Degu');
    INSERT INTO authors (first_name, last_name) VALUES ('Richard', 'Stevens');
    INSERT INTO authors (first_name, last_name) VALUES ('Douglas', 'Comer');
    INSERT INTO authors (first_name, last_name) VALUES ('Tom', 'Christiansen');
    INSERT INTO authors (first_name, last_name) VALUES ('Nathan', 'Torkington');
    INSERT INTO authors (first_name, last_name) VALUES ('Jeffrey', 'Zeldman');
    INSERT INTO book_authors VALUES (1, 1);
    INSERT INTO book_authors VALUES (1, 2);
    INSERT INTO book_authors VALUES (1, 3);
    INSERT INTO book_authors VALUES (2, 4);
    INSERT INTO book_authors VALUES (3, 5);
    INSERT INTO book_authors VALUES (4, 6);
    INSERT INTO book_authors VALUES (4, 7);
    INSERT INTO book_authors VALUES (5, 8);

=item *

Load the data:

    $ psql -U catappuser -W catappdb -f myapp01_psql.sql
    Password for user catappuser:
    psql:myapp01_psql.sql:8: NOTICE:  CREATE TABLE will create implicit sequence "books_id_seq" for serial column "books.id"
    psql:myapp01_psql.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_pkey" for table "books"
    CREATE TABLE
    psql:myapp01_psql.sql:15: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "book_authors_pkey" for table "book_authors"
    CREATE TABLE
    psql:myapp01_psql.sql:21: NOTICE:  CREATE TABLE will create implicit sequence "authors_id_seq" for serial column "authors.id"
    psql:myapp01_psql.sql:21: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "authors_pkey" for table "authors"
    CREATE TABLE
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    ...

=item *

Make sure the data loaded correctly:

    $ psql -U catappuser -W catappdb
    Password for user catappuser: <catalyst>
    Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with psql commands
           \g or terminate with semicolon to execute query
           \q to quit

    catappdb=> \dt
                 List of relations
     Schema |     Name     | Type  |   Owner
    --------+--------------+-------+------------
     public | authors      | table | catappuser
     public | book_authors | table | catappuser
     public | books        | table | catappuser
    (3 rows)

    catappdb=> select * from books;
     id |               title                | rating
    ----+------------------------------------+--------
      1 | CCSP SNRS Exam Certification Guide |      5
      2 | TCP/IP Illustrated, Volume 1       |      5
      3 | Internetworking with TCP/IP Vol.1  |      4
      4 | Perl Cookbook                      |      5
      5 | Designing with Web Standards       |      5
    (5 rows)

    catappdb=>

=back

=item *

After the steps where you:

    edit lib/MyApp.pm

    create lib/MyAppDB.pm

    create lib/MyAppDB/Book.pm

    create lib/MyAppDB/Author.pm

    create lib/MyAppDB/BookAuthor.pm


=item *

Generate the model using the Catalyst "_create.pl" script:

    $ rm lib/MyApp/Model/DB.pm   # Delete just in case already there
    $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
        create=static components=TimeStamp,PassphraseColumn \
        'dbi:Pg:dbname=catappdb' 'catappuser' 'catalyst' '{ AutoCommit => 1 }'

=back

=item *

Chapter 4: Basic CRUD

Add Datetime Columns to Our Existing Books Table

    $ psql -U catappuser -W catappdb
    ...
    catappdb=> ALTER TABLE books ADD created TIMESTAMP NOT NULL DEFAULT now();

lib/Catalyst/Manual/Tutorial/10_Appendices.pod  view on Meta::CPAN

You can verify that it worked with this command:

    $ psql -U catappuser -W catappdb -c "select * from users"


=back

=back


=head2 MySQL

Use the following steps to adapt the tutorial to MySQL.  Thanks to Jim
Howard for the help and Zsolt Zemancsik for the up to date fixes.

=over 4

=item *

Chapter 3: Catalyst Basics

=over 4

=item *

Install the required software:

=over 4

=item *

The MySQL database server and client utility.

=item *

The Perl L<DBD::MySQL> module

=back

For CentOS users (see
L<Catalyst::Manual::Installation::CentOS4>),
you can use the following commands to install the software and start the MySQL
daemon:

    yum -y install mysql mysql-server
    service mysqld start

For Debian users you can use the following commands to install the software and start the MySQL
daemon:

    apt-get install mysql-client mysql-server
    /etc/init.d/mysql start

B<NOTE:> The tutorial is based on Foreign Keys in database which is supported by InnoDB.
Only MySQL 5.0 and above supports InnoDB storage Engine so you need to have InnoDB support
in you MySQL. You can simply figure out that your install supports it or not:

    # mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input
    statement.

    mysql> SHOW VARIABLES LIKE 'have_innodb';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | have_innodb   | YES   |
    +---------------+-------+
    1 row in set (0.01 sec)

    mysql> exit
    Bye

If the Value is "YES" you can use your setup (Debian based mysql supports it by default).
Else, you need to configure your my.cnf or start your MySQL daemon without --skip-innodb option.

=item *

Create the database and set the permissions:

    # mysql -u root -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE DATABASE `myapp`;
    Query OK, 1 row affected (0.01 sec)

    mysql> GRANT ALL PRIVILEGES ON myapp.* TO 'tutorial'@'localhost' IDENTIFIED BY 'yourpassword';
    Query OK, 0 rows affected (0.00 sec)

    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)

    mysql> exit
    Bye


=item *

Create the C<.sql> file and load the data:

=over 4

=item *

Open the F<myapp01_mysql.sql> in your editor and enter:

    --
    -- Create a very simple database to hold book and author information
    --
    CREATE TABLE IF NOT EXISTS `books` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `title` text CHARACTER SET utf8,
        `rating` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    -- 'book_authors' is a many-to-many join table between books & authors
    CREATE TABLE IF NOT EXISTS `book_authors` (
        `book_id` int(11) NOT NULL DEFAULT '0',
        `author_id` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`book_id`,`author_id`),
        KEY `author_id` (`author_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    CREATE TABLE IF NOT EXISTS `authors` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `first_name` text CHARACTER SET utf8,
        `last_name` text CHARACTER SET utf8,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    ---
    --- Load some sample data
    ---
    INSERT INTO `books` (`id`, `title`, `rating`) VALUES
    (1, 'CCSP SNRS Exam Certification Guide', 5),
    (2, 'TCP/IP Illustrated, Volume 1', 5),
    (3, 'Internetworking with TCP/IP Vol.1', 4),
    (4, 'Perl Cookbook', 5),
    (5, 'Designing with Web Standards', 5);

    INSERT INTO `book_authors` (`book_id`, `author_id`) VALUES
    (1, 1),
    (1, 2),
    (1, 3),
    (2, 4),
    (3, 5),
    (4, 6),
    (4, 7),
    (5, 8);

    INSERT INTO `authors` (`id`, `first_name`, `last_name`) VALUES
    (1, 'Greg', 'Bastien'),
    (2, 'Sara', 'Nasseh'),
    (3, 'Christian', 'Degu'),
    (4, 'Richard', 'Stevens'),
    (5, 'Douglas', 'Comer'),
    (6, 'Tom', 'Christiansen'),
    (7, 'Nathan', 'Torkington'),
    (8, 'Jeffrey', 'Zeldman');

    ALTER TABLE `book_authors`
    ADD CONSTRAINT `book_author_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    ADD CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `books` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

=item *

Load the data:

    mysql -u tutorial -p myapp < myapp01_mysql.sql

=item *

Make sure the data loaded correctly:

    $ mysql -u tutorial -p myapp
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

    mysql> show tables;
    +-----------------+
    | Tables_in_myapp |
    +-----------------+
    | authors         |
    | book_authors    |
    | books           |
    +-----------------+
    3 rows in set (0.00 sec)

    mysql> select * from books;
    +----+------------------------------------+--------+
    | id | title                              | rating |
    +----+------------------------------------+--------+
    |  1 | CCSP SNRS Exam Certification Guide |      5 |
    |  2 | TCP/IP Illustrated, Volume 1       |      5 |
    |  3 | Internetworking with TCP/IP Vol.1  |      4 |
    |  4 | Perl Cookbook                      |      5 |
    |  5 | Designing with Web Standards       |      5 |
    +----+------------------------------------+--------+
    5 rows in set (0.00 sec)

    mysql>

=back

=item *

Update the model:

=over 4

=item *

Delete the existing model:

    rm lib/MyApp/Model/MyAppDB.pm

=item *

Regenerate the model using the Catalyst "_create.pl" script:

    script/myapp_create.pl model DB DBIC::Schema MyApp::Schema create=static \
        dbi:mysql:myapp 'tutorial' 'yourpassword' '{ AutoCommit => 1 }'

=back

=back

=item *

Chapter 5: Authentication

=over 4

=item *