.\" Automatically generated by Pod::Man 4.10 (Pod::Simple 3.35) .\" .\" Standard preamble: .\" ======================================================================== .de Sp \" Vertical space (when we can't use .PP) .if t .sp .5v .if n .sp .. .de Vb \" Begin verbatim text .ft CW .nf .ne \\$1 .. .de Ve \" End verbatim text .ft R .fi .. .\" Set up some character translations and predefined strings. \*(-- will .\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left .\" double quote, and \*(R" will give a right double quote. \*(C+ will .\" give a nicer C++. Capital omega is used to do unbreakable dashes and .\" therefore won't be available. \*(C` and \*(C' expand to `' in nroff, .\" nothing in troff, for use with C<>. .tr \(*W- .ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p' .ie n \{\ . ds -- \(*W- . ds PI pi . if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch . if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\" diablo 12 pitch . ds L" "" . ds R" "" . ds C` "" . ds C' "" 'br\} .el\{\ . ds -- \|\(em\| . ds PI \(*p . ds L" `` . ds R" '' . ds C` . ds C' 'br\} .\" .\" Escape single quotes in literal strings from groff's Unicode transform. .ie \n(.g .ds Aq \(aq .el .ds Aq ' .\" .\" If the F register is >0, we'll generate index entries on stderr for .\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index .\" entries marked with X<> in POD. Of course, you'll have to process the .\" output yourself in some meaningful fashion. .\" .\" Avoid warning from groff about undefined register 'F'. .de IX .. .nr rF 0 .if \n(.g .if rF .nr rF 1 .if (\n(rF:(\n(.g==0)) \{\ . if \nF \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} . \} .\} .rr rF .\" .\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2). .\" Fear. Run. Save yourself. No user-serviceable parts. . \" fudge factors for nroff and troff .if n \{\ . ds #H 0 . ds #V .8m . ds #F .3m . ds #[ \f1 . ds #] \fP .\} .if t \{\ . ds #H ((1u-(\\\\n(.fu%2u))*.13m) . ds #V .6m . ds #F 0 . ds #[ \& . ds #] \& .\} . \" simple accents for nroff and troff .if n \{\ . ds ' \& . ds ` \& . ds ^ \& . ds , \& . ds ~ ~ . ds / .\} .if t \{\ . ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u" . ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u' . ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u' . ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u' . ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u' . ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u' .\} . \" troff and (daisy-wheel) nroff accents .ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V' .ds 8 \h'\*(#H'\(*b\h'-\*(#H' .ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#] .ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H' .ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u' .ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#] .ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#] .ds ae a\h'-(\w'a'u*4/10)'e .ds Ae A\h'-(\w'A'u*4/10)'E . \" corrections for vroff .if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u' .if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u' . \" for low resolution devices (crt and lpr) .if \n(.H>23 .if \n(.V>19 \ \{\ . ds : e . ds 8 ss . ds o a . ds d- d\h'-1'\(ga . ds D- D\h'-1'\(hy . ds th \o'bp' . ds Th \o'LP' . ds ae ae . ds Ae AE .\} .rm #[ #] #H #V #F C .\" ======================================================================== .\" .IX Title "UR::Manual::Tutorial 3pm" .TH UR::Manual::Tutorial 3pm "2019-01-02" "perl v5.28.1" "User Contributed Perl Documentation" .\" For nroff, turn off justification. Always turn off hyphenation; it makes .\" way too many mistakes in technical documents. .if n .ad l .nh .SH "NAME" UR::Manual::Tutorial \- Step\-by\-step guide to building a set of classes for a simple database schema .SH "Overview" .IX Header "Overview" We'll use the familiar \*(L"Music Database\*(R" example used in many \s-1ORM\s0 tutorials: .PP Our database has the following basic entities and relationships: .IP "\(bu" 2 One artist can have many CDs .IP "\(bu" 2 One \s-1CD\s0 belongs to one artist .IP "\(bu" 2 one \s-1CD\s0 can have many tracks .IP "\(bu" 2 one track belongs to one \s-1CD\s0 .ie n .SH "The ""ur"" command-line program" .el .SH "The ``ur'' command-line program" .IX Header "The ur command-line program" The tool for working with \s-1UR\s0 from the command line is 'ur' . It is installed with the \s-1UR\s0 module suite. .PP Just type \*(L"ur\*(R" and hit enter, to see a list of valid ur commands: > ur Sub-commands for ur: init \s-1NAMESPACE\s0 [\s-1DB\s0] initialize a new \s-1UR\s0 app in one command define ... define namespaces, data sources and classes describe CLASSES-OR-MODULES show class properties, relationships, meta-data update ... update parts of the source tree of a \s-1UR\s0 namespace list ... list objects, classes, modules sys ... service launchers test ... tools for testing and debugging .PP The \*(L"ur\*(R" command works a lot like the \*(L"svn\*(R" command: it is the entry point for a list of other subordinate commands. .IP "\(bu" 2 Typing something like \*(L"ur browser\*(R" will run the browser tool. .IP "\(bu" 2 Typing something like \*(L"ur define\*(R" will give another list, of even more granular commands which are under \*(L"ur define\*(R": .PP .Vb 5 \& > ur define \& Sub\-commands for ur define: \& namespace NSNAME create a new namespace tree and top\-level module \& db URI NAME add a data source to the current namespace \& class \-\-extends=? [NAMES] Add one or more classes to the current namespace .Ve .PP At any point, you can put '\-\-help' as a command line argument and get some (hopefully) helpful documentation. .PP In many cases, the output also resembles svn's output where the first column is a character like 'A' to represent something being added, 'D' for deleted, etc. .PP (\s-1NOTE:\s0 The \*(L"ur\*(R" command, uses the Command \s-1API,\s0 an \s-1API\s0 for objects which follow the command-pattern. See UR::Command for more details on writing tools like this. .SH "Define a UR Namespace" .IX Header "Define a UR Namespace" A \s-1UR\s0 namespace is the top-level object that represents your data's class structure in the most general way. For this new project, we'll need to create a new namespace, perhaps within a testing directory. .PP .Vb 1 \& ur define namespace Music .Ve .PP And you should see output like this: .PP .Vb 4 \& A Music (UR::Namespace) \& A Music::Vocabulary (UR::Vocabulary) \& A Music::DataSource::Meta (UR::DataSource::Meta) \& A Music/DataSource/Meta.sqlite3\-dump (Metadata DB skeleton) .Ve .PP showing that it created 3 classes for you, Music, Music::Vocabulary and Music::DataSource::Meta, and shows what classes those inherit from. In addition, it has also created a file to hold your metadata. Other parts of the documentation give a more thorough description of Vocabulary and Metadata classes. .SH "Define a Data Source" .IX Header "Define a Data Source" A \s-1UR\s0 DataSource is an object representing the location of your data. It's roughly analogous to a Schema class in DBIx::Class, or the \*(L"Base class\*(R" in Class::DBI. .PP Note: Because \s-1UR\s0 can be used with objects which do \s-1NOT\s0 live in a database, using a data source is optional, but is the most common case. .PP Most ur commands operate in the context of a Namespace, including the one to create a datasource, so you need to be within the Music's Namespace's directory: .PP .Vb 1 \& cd Music .Ve .PP and then define the datasource. We specify the data source's type as a sub-command, and the name with the \-\-dsname argument. For this example, we'll use a brand new SQLite database. For some other, perhaps already existing database, give its connect string instead. .PP .Vb 1 \& ur define db dbi:SQLite:/var/lib/music.sqlite3 Example .Ve .PP which generates this output: .PP .Vb 3 \& A Music::DataSource::Example (UR::DataSource::SQLite,UR::Singleton) \& ...connecting... \& ....ok .Ve .PP and creates a symlink to the database at: Music/DataSource/Example.sqlite3 .PP and shows that it created a class for your data source called Music::DataSource::Example, which inherits from UR::DataSource::SQLite. It also created an empty database file and connected to it to confirm that everything is \s-1OK.\s0 .SH "Create the database tables" .IX Header "Create the database tables" Here are the table creation statements for our example database. Put them into a file with your favorite editor and call it example\-db.schema.txt: .PP .Vb 10 \& CREATE TABLE artist ( \& artist_id INTEGER NOT NULL PRIMARY KEY, \& name TEXT NOT NULL \& ); \& CREATE TABLE cd ( \& cd_id INTEGER NOT NULL PRIMARY KEY, \& artist_id INTEGER NOT NULL CONSTRAINT CD_ARTIST_FK REFERENCES artist(artist_id), \& title TEXT NOT NULL, \& year INTEGER \& ); \& CREATE TABLE track ( \& track_id INTEGER NOT NULL PRIMARY KEY, \& cd_id INTEGER NOT NULL CONSTRAINT TRACK_CD_FK REFERENCES cd(cd_id), \& title TEXT NOT NULL \& ); .Ve .PP This new SQLite data source assumes the database file will have the pathname Music/DataSource/Example.sqlite3. You can populate the database schema like this: .PP .Vb 1 \& sqlite3 DataSource/Example.sqlite3 < example\-db.schema.txt .Ve .SH "Create your data classes" .IX Header "Create your data classes" Now we're ready to create the classes that will store your data in the database. .PP You could write those classes by hand, but it's easiest to start with an autogenerated group built from the database schema: .PP .Vb 1 \& ur update classes\-from\-db .Ve .PP is the command that performs all the magic. You'll see it go through several steps: .IP "1. Find all the defined datasources within the current namespace" 2 .IX Item "1. Find all the defined datasources within the current namespace" .PD 0 .IP "2. Query the data sources about what tables, columns, constraints and foreign keys are present" 2 .IX Item "2. Query the data sources about what tables, columns, constraints and foreign keys are present" .IP "3. Load up all the classes in the current namespace" 2 .IX Item "3. Load up all the classes in the current namespace" .IP "4. Figure out what the differences are between the database schema and the class structure" 2 .IX Item "4. Figure out what the differences are between the database schema and the class structure" .IP "5. Alter the class metadata to match the database schema" 2 .IX Item "5. Alter the class metadata to match the database schema" .IP "6. Use the new class metadata to write headers on the Perl module files in the namespace" 2 .IX Item "6. Use the new class metadata to write headers on the Perl module files in the namespace" .PD .PP There will now be a Perl module for each database table. For example, in Cd.pm: .PP .Vb 1 \& package Music::Cd; \& \& use strict; \& use warnings; \& \& use Music; \& class Music::Cd { \& table_name => \*(AqCD\*(Aq, \& id_by => [ \& cd_id => { is => \*(AqINTEGER\*(Aq }, \& ], \& has => [ \& artist => { is => \*(AqMusic::Artist\*(Aq, id_by => \*(Aqartist_id\*(Aq, constraint_name => \*(AqCD_ARTIST_FK\*(Aq }, \& artist_id => { is => \*(AqINTEGER\*(Aq }, \& title => { is => \*(AqTEXT\*(Aq }, \& year => { is => \*(AqINTEGER\*(Aq, is_optional => 1 }, \& ], \& schema_name => \*(AqExample\*(Aq, \& data_source => \*(AqMusic::DataSource::Example\*(Aq, \& }; \& \& 1; .Ve .PP The first few lines are what you would see in any Perl module. The keyword \&\f(CW\*(C`class\*(C'\fR tells the \s-1UR\s0 system to define a new class, and lists the properties of the new class. Some of the important parts are that instances of this class come from the Music::DataSource::Example datasource, in the table \&'\s-1CD\s0'. This class has 4 direct properties (cd_id, artist_id, title and year), and one indirect property (artist). Instances are identified by the cd_id property. .PP Methods are automatically created to match the property names. If you have an instance of a \s-1CD,\s0 say \f(CW$cd\fR, you can get the value of the title with \&\f(CW\*(C`$cd\->title\*(C'\fR. To get back the artist object that is related to that \s-1CD,\s0 \&\f(CW\*(C`$cd\->artist\*(C'\fR. .SH "CRUD (Create, Read, Update, Delete)" .IX Header "CRUD (Create, Read, Update, Delete)" .SS "Create" .IX Subsection "Create" Creating new object instances is done with the create method; its arguments are key-value pairs of properties and their values. .PP .Vb 1 \& #!/usr/bin/perl \& \& use strict; \& use Music; \& \& my $obj1 = Music::Artist\->create(name => \*(AqElvis\*(Aq); \& \& my $obj2 = Music::Artist\->create(name => \*(AqThe Beatles\*(Aq); \& \& UR::Context\->commit(); .Ve .PP And that's it. After this script runs, there will be 2 rows in the Artist table. .PP Just a short aside about that last line... All the changes to your objects while the program runs (creates, updates, deletes) exist only in memory. The current \*(L"Context\*(R" manages that knowledge. Those changes are finally pushed out to the underlying data sources with that last line. .SS "Read" .IX Subsection "Read" Retrieving object instances from the database is done with the \f(CW\*(C`get()\*(C'\fR method. A \f(CW\*(C`get()\*(C'\fR with no arguments will return a list of all the objects in the table. .PP .Vb 1 \& @all_cds = Music::Cd\->get(); .Ve .PP If you know the \*(L"id\*(R" (primary key) value of the objects you're interested in, you can pass that \*(L"id\*(R" value as a single argument to get: .PP .Vb 1 \& $cd = Music::Cd\->get(3); .Ve .PP An arrayref of identity values can be passed-in as well. Note that if you query is going to return more than one item, and it is called in scalar context, it will generate an exception. .PP .Vb 1 \& @some_cds = Music::Cd\->get([1, 2, 4]); .Ve .PP To filter the return list by a property other than the \s-1ID\s0 property, give a list of key-value pairs: .PP .Vb 1 \& @some_cds = Music::Cd\->get(artist_id => 3); .Ve .PP This will return all the CDs with the artist \s-1ID 5, 6\s0 or 10. .PP .Vb 1 \& @some_cds = Music::Cd\->get(artist_id => [5, 6, 10]); .Ve .PP \&\fBget()\fR filters support operators other than strict equality. This will return a list of CDs with artist \s-1ID 2\s0 and have the word 'Ticket' somewhere in the title. .PP .Vb 1 \& @some_cds = Music::Cd\->get(artist_id=> 2, title => { operator => \*(Aqlike\*(Aq, value => \*(Aq%Ticket%\*(Aq} ); .Ve .PP To search for \s-1NULL\s0 fields, use undef as the value: .PP .Vb 1 \& @cds_with_no_year = Music::Cd\->get(year => undef); .Ve .SS "get_or_create" .IX Subsection "get_or_create" \&\f(CW\*(C`get_or_create()\*(C'\fR is used to retrieve an instance from the database if it exists, or create a new one if it does not. .PP .Vb 1 \& $possibly_new = Music::Artist\->get_or_create(name => \*(AqThe Band\*(Aq); .Ve .SS "Update" .IX Subsection "Update" All the properties of an object are also mutators. To change the object's property, just call the method for that property with the new value. .PP .Vb 1 \& $cd\->year(1990); .Ve .PP Remember that any changes made while the program runs are not saved in the database until you commit the changes with \f(CW\*(C`UR::Context\->commit\*(C'\fR. .SS "Delete" .IX Subsection "Delete" The \f(CW\*(C`delete()\*(C'\fR method does just what it says. .PP .Vb 4 \& @all_tracks = Music::Track\->get(); \& foreach my $track ( @all_tracks ) { \& $track\->delete(); \& } .Ve .PP Again, the corresponding database rows will not be removed until you commit. .SH "Relationships" .IX Header "Relationships" After running ur update classes, it will automatically create indirect properties for all the foreign keys defined in the schema, but not for the reverse relationships. You can add other relationships in yourself and they will persist even after you run ur update classes again. For example, there is a foreign key that forces a track to be related to one \s-1CD.\s0 If you edit the file Cd.pm, you can define a relationship so that CDs can have many tracks: .PP .Vb 10 \& class Music::Cd { \& table_name => \*(AqCD\*(Aq, \& id_by => [ \& cd_id => { is => \*(AqINTEGER\*(Aq }, \& ], \& has => [ \& artist => { is => \*(AqMusic::Artist\*(Aq, id_by => \*(Aqartist_id\*(Aq, constraint_name => \*(AqCD_ARTIST_FK\*(Aq }, \& artist_id => { is => \*(AqINTEGER\*(Aq }, \& title => { is => \*(AqTEXT\*(Aq }, \& year => { is => \*(AqINTEGER\*(Aq }, \& tracks => { is => \*(AqMusic::Track\*(Aq, reverse_as => \*(Aqcd\*(Aq, is_many => 1 }, # This is the new line \& ], \& schema_name => \*(AqExample\*(Aq, \& data_source => \*(AqMusic::DataSource::Example\*(Aq, \& }; .Ve .PP This tells the system that there is a new property called 'tracks' which returns items of the class Music::Track. It links them to the acting \s-1CD\s0 object through the Track's cd property. .PP After that is in place, you can ask for a list of all the tracks belonging to a \s-1CD\s0 with the line .PP .Vb 1 \& @tracks = $cd\->tracks() .Ve .PP You can also define indirect relationships through other indirect relationships. For example, if you edit Artist.pm to add a couple of lines: .PP .Vb 10 \& class Music::Artist { \& table_name => \*(AqARTIST\*(Aq, \& id_by => [ \& artist_id => { is => \*(AqINTEGER\*(Aq }, \& ], \& has => [ \& name => { is => \*(AqTEXT\*(Aq }, \& cds => { is => \*(AqMusic::Cd\*(Aq, reverse_as => \*(Aqartist\*(Aq, is_many => 1 }, \& tracks => { is => \*(AqMusic::Track\*(Aq, via => \*(Aqcds\*(Aq, to => \*(Aqtracks\*(Aq, is_many => 1}, \& ], \& schema_name => \*(AqExample\*(Aq, \& data_source => \*(AqMusic::DataSource::Example\*(Aq, \& }; .Ve .PP This defines a relationship 'cds' to return all the CDs from the acting artist. It also defines a relationship called 'tracks' that will, behind the scenes, first look up all the CDs from the acting artist, and then find and return all the tracks from those CDs. .PP Additional arguments can be passed to these indirect accessors to get a subset of the data .PP .Vb 2 \& @cds_in_1990s = $artist\->cds(year => { operator => \*(Aqbetween\*(Aq, \& value => [1990,1999] } ); .Ve .PP would get all the CDs from that artist where the year is between 1990 and 1999, inclusive. .PP Note that is_many relationships should always be named with plural words. The system will auto-create other accessors based on the singular name for adding and removing items in the relationship. For example: .PP .Vb 1 \& $artist\->add_cd(year => 1998, title => \*(AqCool Jams\*(Aq ); .Ve .PP would create a new Music::Cd object with the given year and title. The cd_id will be autogenerated by the system, and the artist_id will be automatically set to the artist_id of \f(CW$artist\fR. .SH "Custom SQL" .IX Header "Custom SQL" It's possible to use \fBget()\fR with custom \s-1SQL\s0 to retrieve objects, as long as the select clause includes all the \s-1ID\s0 properties of the class. To find Artist objects that have no CDs, you might do this: .PP .Vb 8 \& my @artists_with_no_cds = \& Music::Artist\->get(sql => \*(Aqselect artist.artist_id, \& count(cd.artist_id) \& from artist \& left join cd on cd.artist_id = artist.artist_id \& group by artist.artist_id \& having count(cd.artist_id) = 0\*(Aq \& ); .Ve