summaryrefslogtreecommitdiffstats
path: root/README
blob: 71623ed4befefa213cbd86e021725a98a7c08fc8 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
NAME
    Jaos::DBI - Jaos DBI object similar to Class::DBI

SYNOPSIS
      # subclassing
      package Users;
      use base qw/Jaos::DBI/;
      use strict;
      use warnings;

      __PACKAGE__->db_dsn('dbi:Pg:dbname=foo');
      __PACKAGE__->db_user('you');
      __PACKAGE__->db_password('secret');
      __PACKAGE__->db_options({RaiseError => 0, AutoCommit => 1, PrintError => 0});

      __PACKAGE__->table('users');
      __PACKAGE__->columns(qw/id nick password email logged_in/);
      __PACKAGE__->primary_column('id');
      __PACKAGE__->virtual_columns(qw/join_id join_name/);
      __PACKAGE__->sequence('users_id_seq');
      __PACKAGE__->base_select('select a.*,b.join_id,b.join_name from foo a join bar b on a.id=b.foo');
      __PACKAGE__->manual_update(1);

      __PACKAGE__->setup(); # initiates database connection callback and accessors
      1;

      # using subclass
      use Users;

      # examine the class properties
      print "table: ", Users->table, "\n";
      print "columns: ", join(',', Users->columns ), "\n";

      # retrieve objects
      # using various search options
      my @objs = Users->search(email => 'foo@bar.com',{ order_by => 'id', limit => '2'});
      my @objs = Users->search_like(email => '%@bar.com',{ order_by => 'id', limit => '2'});
      my @objs = Users->search_ilike(email => '%@bar.com',{ order_by => 'id', limit => '2'});
      my @objs = Users->search_or(email => 'john.doe@bar.com', name => 'john');
      my @objs = Users->search_or_like(email => 'john.doe@%', name => '%john%');
      my @objs = Users->search_or_ilike(email => 'john.doe@%', name => '%john%');
      my @objs = Users->search_where('name in (select name from other_user_table)');
      # all
      my @objs = Users->retrieve_all();
      # by primary column value (primary key)
      my $obj = Users->retrieve($id);

      # insert (or alias create)
      my $user = Users->insert(nick => 'zaphod', email => 'foo@bar.org');
      my $user = Users->create(nick => 'zaphod', email => 'foo@bar.org');
      $user = Users->find_or_create(nick => 'zaphod', email => 'foo@bar.org');

      # modify
      $user->email('newaddress@foo.com'); # setting attribute updates the database
      $user->delete();
      $user->update() unless $user->manual_update;
      $user->update() if $user->is_changed;
      User->delete(nick => 'zaphod', email => 'newaddress@foo.com');

DESCRIPTION
    Jaos::DBI provides a convenient abstraction layer to a database in the
    same vein as Class::DBI and its spinoffs.

    The aim is to keep it simple and avoid the db statement hit from
    Class::DBI and make it easier to use views, complex sql joins for basic
    class definitions, and get rid of the large requirements for external
    modules used by Class::DBI.

METHODS
  setup
    Initializes the Class; creates a database handle callback and sets up
    the accessors for columns, primary_column, and virtual_columns. Must
    have a table name, dsn, user, and pass specified prior to calling
    __PACKAGE__->setup().

  db_dsn
    Get or set the database dsn.

     __PACKAGE__->db_dsn('dbi:Pg:dbname=foo');

  db_user
    Get or set the database user.

     __PACKAGE__->db_user('user');

  db_password
    Get or set the database password.

     __PACKAGE__->db_passwd('secret');

  db_options
    Get or set the database options.

     __PACKAGE__->db_options({ Autocommit => 1});

  base_select
    This sets the basic select statement that is used to retrieve
    information. This can be used to create complex joins or subselects that
    further where clauses can be built from.

     __PACKAGE__->base_select('select a.*,b.field from table a left join table2 b on a.field=b.field');

  table
    Gets or sets the table name for the subclass or instance.

     __PACKAGE__->table('foo');
     my $table = __PACKAGE__->table();

  columns
    Get or set the tables column names.

     __PACKAGE_->columns(qw/one two three/);
     my @columns = __PACKAGE__->columns();

  primary_column
    Get or set the tables primary column;

     __PACKAGE_->primary_column('id);

  virtual_columns
    These are virtual columns that may result from a base_select join.
    Accessors will be created for them just as if they were columns.

     __PACKAGE__->virtual_columns(qw/join_id join_name/);

  manual_update
    Get or set the option to stop auto update on modification. Must call
    __PACKAGE__->update() manually.

  is_changed
    Returns true if object has been modified.

  sequence
    Get or set the tables sequence. This is used to generate unique primary
    keys.

     __PACKAGE__->sequence('users_id_seq');

  sequence_nextval
    Returns the next value for the tables sequence.

     my $new_id = __PACKAGE__->sequence_nextval();

  sequence_currval
    Returns the current value for the tables sequence.

     my $current_id = __PACKAGE__->sequence_currval();

  prepare
    Returns a statement handle for the specified sql;

     __PACKAGE__->prepare('select * from foo');

  insert
    Inserts a row into the database, returning an object representing that
    row. This calls __PACKAGE__->sequence_nextval for the primary column.

     my $new_obj = __PACKAGE__->insert(column1 => $value1, column2 => $value2);

  create
    Alias to insert.

  find_or_create
    This attempts to find a row with the column values specified, or creates
    one. Calls __PACKAGE__->search(@_) and __PACKAGE__->insert(@_) if search
    returns undef.

     my $obj = __PACKAGE__->find_or_create(column1 => $value, column2 => $value);

  search
    Searches the table and returns a list of matching objects.

     my @objs = __PACKAGE__->search(column1 => $value1, column2 => $value2);

  search_like
    Searches the table and returns a list of matching objects using column
    like $value rather than column = $value.

     my @objs = __PACKAGE__->search_like(column1 => "%$value1%", column2 => "%$value2%");

  search_ilike
    same as search_like but insensitive

  search_or
    search where given key/values use or instead of and

  search_or_like
    search where given like key/values use or instead of and

  search_or_ilike
    search insensitive where given ilike key/values use or instead of and

  search_where
    Searches the table and returns a list of matching objects using the
    specified where clause

     my @objs = __PACKAGE__->search_where('id = foo');

  delete
    Deletes either the referring object, if called as $obj->delete, or
    provides a class method to delete all all rows matching the arguments if
    called as __PACKAGE__->delete();

     $obj->delete();
     __PACKAGE__->delete(column1 => $value1, column2 => $value2);

  get
    Get a column value.

     __PACKAGE__->get('column');

  set
    Set a column value. This updates the column in the table row represented
    by the object.

     __PACKAGE__->set('column',$value);

  update
    Commit changes to database. Only useful when manual update is set.

  retrieve_all
    Return all rows in the table;

     my @objs = __PACKAGE__->retrieve_all();

  retrieve
    Return row mathing the specified key against the primary column.

     my $obj = __PACKAGE__->retrieve($id);

  begin_work
    Start transaction. Calls DBI begin_work().

  commit
    Commit transaction. Calls DBI commit().

  rollback
    Rollback transaction. Calls DBI rollback().

AUTHOR
    Jason Woodward <woodwardj@jaos.org>

LICENSE
    This library is free software; you can redistribute it and/or modify it
    under the same terms as Perl itself.