チケット #1: Ticket#1

ファイル Ticket#1, 13.4 KB (登録者: ikarashi, 5 年 前)

作業パッチ

行番号 
1Index: createPostgreSQLTables.sql
2===================================================================
3--- createPostgreSQLTables.sql  (リビジョン 217)
4+++ createPostgreSQLTables.sql  (作業コピー)
5@@ -42,6 +42,15 @@
6        FOREIGN KEY(role_id) REFERENCES roles(role_id)
7 );
8 
9+CREATE TABLE user_tags (
10+       user_tag_id     SERIAL,
11+       user_id                 INTEGER,
12+       tag_id                  INTEGER,
13+       PRIMARY KEY(user_tag_id, user_id, tag_id),
14+       FOREIGN KEY(user_id) REFERENCES users(user_id),
15+       FOREIGN KEY(tag_id) REFERENCES tags(tag_id)
16+);
17+
18 CREATE TABLE user_bookmarks (
19        user_bookmark_id        SERIAL,
20        user_id                         INTEGER,
21@@ -158,6 +167,8 @@
22 INSERT INTO user_roles (user_id, role_id) VALUES ( 1, 1 );
23 INSERT INTO user_roles (user_id, role_id) VALUES ( 1, 2 );
24 
25+INSERT INTO user_tags (user_id, tag_id) VALUES ( 1, 1 );
26+
27 INSERT INTO user_bookmarks (
28                user_id, bookmark_id, user_bookmark_ts, user_bookmark_comment
29        ) VALUES ( 1, 1, DEFAULT, '技術サイト' );
30Index: dropPostgreSQLTables.sql
31===================================================================
32--- dropPostgreSQLTables.sql    (リビジョン 217)
33+++ dropPostgreSQLTables.sql    (作業コピー)
34@@ -6,6 +6,7 @@
35 DROP TABLE user_bookmark_tags;
36 DROP TABLE user_bookmark_roles;
37 DROP TABLE user_bookmarks;
38+DROP TABLE user_tags;
39 DROP TABLE user_roles;
40 DROP TABLE tags;
41 DROP TABLE bookmarks;
42Index: root/template/admin/gcusertags_center.tt
43===================================================================
44--- root/template/admin/gcusertags_center.tt    (リビジョン 0)
45+++ root/template/admin/gcusertags_center.tt    (リビジョン 0)
46@@ -0,0 +1,20 @@
47+<div id="gcusertags_center">
48+<div align="right"><a href="[% c.uri_for('/admin') %]">管理 Top</a></div>
49+<h1>ユーザ-タグの関連付け 結果</h1>
50+[% resultnum %] 件を関連付けしました．
51+
52+</div> <!-- end of admin_center -->
53+
54+[%#
55+This Program is distributed under version 1.0 of the Rinza Public
56+License Agreement, that is bundled with this package in the file
57+LICENSE, and is available through the website at the following URL:
58+http://www.tyzoh.jp/rinza/licenses/LICENSE-1.0.txt.
59+
60+This is the Original Program.
61+The Initial Developer of the Original Program is Nihon Unisys, Ltd.
62+The Original Program is copyrighted (C) 2007 by Nihon Unisys, Ltd. with
63+all rights reserved.
64+There is NO WARRANTY OF ANY KIND by the Initial Developer of the
65+Original Program.
66+%]
67Index: root/template/admin/gcusertags.tt
68===================================================================
69--- root/template/admin/gcusertags.tt   (リビジョン 0)
70+++ root/template/admin/gcusertags.tt   (リビジョン 0)
71@@ -0,0 +1,19 @@
72+[% INCLUDE top.tt %]
73+<div class="left" id="left">[% INCLUDE left.tt %]</div><!-- end of left -->
74+<div class="center" id="center">[% INCLUDE admin/gcusertags_center.tt %]</div><!-- end of center -->
75+<div class="right" id="right">[% INCLUDE right.tt %]</div><!-- end of right -->
76+[% INCLUDE bottom.tt %]
77+
78+[%#
79+This Program is distributed under version 1.0 of the Rinza Public
80+License Agreement, that is bundled with this package in the file
81+LICENSE, and is available through the website at the following URL:
82+http://www.tyzoh.jp/rinza/licenses/LICENSE-1.0.txt.
83+
84+This is the Original Program.
85+The Initial Developer of the Original Program is Nihon Unisys, Ltd.
86+The Original Program is copyrighted (C) 2007 by Nihon Unisys, Ltd. with
87+all rights reserved.
88+There is NO WARRANTY OF ANY KIND by the Initial Developer of the
89+Original Program.
90+%]
91Index: showPostgreSQLTables.sql
92===================================================================
93--- showPostgreSQLTables.sql    (リビジョン 217)
94+++ showPostgreSQLTables.sql    (作業コピー)
95@@ -3,6 +3,7 @@
96 SELECT * FROM bookmarks;
97 SELECT * FROM tags;
98 SELECT * FROM user_roles;
99+SELECT * FROM user_tags;
100 SELECT * FROM user_bookmarks;
101 SELECT * FROM user_bookmark_tags;
102 SELECT * FROM user_bookmark_roles;
103Index: addPostgreSQLUserTags_for0_2.sql
104===================================================================
105--- addPostgreSQLUserTags_for0_2.sql    (リビジョン 0)
106+++ addPostgreSQLUserTags_for0_2.sql    (リビジョン 0)
107@@ -0,0 +1,21 @@
108+CREATE TABLE user_tags (
109+       user_tag_id     SERIAL,
110+       user_id                 INTEGER,
111+       tag_id                  INTEGER,
112+       PRIMARY KEY(user_tag_id, user_id, tag_id),
113+       FOREIGN KEY(user_id) REFERENCES users(user_id),
114+       FOREIGN KEY(tag_id) REFERENCES tags(tag_id)
115+);
116+
117+/*
118+This Program is distributed under version 1.0 of the Rinza Public
119+License Agreement, that is bundled with this package in the file
120+LICENSE, and is available through the website at the following URL:
121+http://www.tyzoh.jp/rinza/licenses/LICENSE-1.0.txt.
122+
123+The Initial Developer of the Original Program is Nihon Unisys, Ltd.
124+The Original Program is copyrighted (C) 2007 by Nihon Unisys, Ltd.
125+with all rights reserved.
126+There is NO WARRANTY OF ANY KIND by the Initial Developer of the
127+Original Program.
128+*/
129Index: lib/SecureSBM/Controller/Admin.pm
130===================================================================
131--- lib/SecureSBM/Controller/Admin.pm   (リビジョン 217)
132+++ lib/SecureSBM/Controller/Admin.pm   (作業コピー)
133@@ -207,6 +207,7 @@
134                                $udb->user_bookmarks->delete_all;
135                                $udb->user_roles->delete_all;
136                                $udb->user_profiles->delete_all;
137+                               $udb->user_tags->delete_all;
138                                $udb->delete;
139                                $c->stash->{resultmessage} = 'ユーザー ID [' . $user_id . ']を削除しました．';
140                        }
141@@ -652,6 +653,7 @@
142                if( $tdb = $c->model('SecureSBMDB::Tag')->search({tag_id => $tag_id}) ){
143                        for my $t ($tdb->all){
144                                $t->user_bookmark_tags->delete_all;
145+                               $t->user_tags->delete_all;
146                        }
147                        $tdb->delete_all;
148                        $c->stash->{resultmessage} = 'タグ ID [' . $tag_id . ']を削除しました．';
149@@ -915,6 +917,53 @@
150 
151 ######################################################
152 #
153+#      users と tags テーブルの関連付け
154+#
155+######################################################
156+
157+=head2 gcUserTags
158+
159+       users と tags テーブルの関連付け for upgrade from version 0.2
160+
161+=cut
162+
163+sub gcUserTags : Local {
164+       my ( $self, $c ) = @_;
165+
166+       my $num = 0;
167+
168+       $c->stash->{pagetitle} = 'Secure-SBM Users-Tags Garbage Collection';
169+       $c->stash->{template} = 'admin/gcusertags.tt';
170+
171+       $c->model('SecureSBMDB::UserTag')->delete_all;
172+       foreach my $udb ($c->model('SecureSBMDB::User')->all){
173+               my $uid = $udb->user_id;
174+               foreach my $ubdb ($udb->user_bookmarks->all){
175+                       foreach my $ubtdb ($ubdb->user_bookmark_tags->all){
176+                               my $tid = $ubtdb->tag_id;
177+                               if( ! $c->model('SecureSBMDB::UserTag')->find({
178+                                       user_id => $uid,
179+                                       tag_id  => $tid
180+                                       })
181+                               ){
182+                                       if( $c->model('SecureSBMDB::UserTag')->create({
183+                                               user_id => $uid,
184+                                               tag_id  => $tid
185+                                               })
186+                                       ){
187+                                               $num++;
188+                                       }
189+                               }
190+                       }
191+               }
192+       }
193+
194+       $c->stash->{resultnum} = $num;
195+
196+} # end of gcUserTags()
197+
198+######################################################
199+#
200 #              お知らせ更新
201 #
202 ######################################################
203Index: lib/SecureSBM/Controller/Personal.pm
204===================================================================
205--- lib/SecureSBM/Controller/Personal.pm        (リビジョン 217)
206+++ lib/SecureSBM/Controller/Personal.pm        (作業コピー)
207@@ -295,7 +295,7 @@
208                        # タグの準備
209                        my @tags = _splitTags( $data{bookmark_tags} );
210                        # タグ登録
211-                       _registTags($c, $ubid, @tags);
212+                       _registTags($c, $uid, $ubid, @tags);
213 
214                        # ロールへの関連付け
215                        ## UserBookmarkRole の削除
216@@ -359,7 +359,7 @@
217 
218 # タグの登録と関連付けを行う．
219 sub _registTags : Private {
220-       my ( $c, $ubid, @tags ) = @_;
221+       my ( $c, $uid, $ubid, @tags ) = @_;
222 
223        foreach my $v (@tags) {
224                $c->log->debug('*** tag:[' . $v . ']');
225@@ -379,6 +379,13 @@
226                                        }
227                                );
228 
229+                       $c->model('SecureSBMDB::UserTag')->
230+                               find_or_create(
231+                                       {
232+                                               user_id => $uid,
233+                                               tag_id  => $tid
234+                                       }
235+                               );
236                }
237        }
238 } # end of _registTags()
239@@ -454,7 +461,7 @@
240                $ubdb->user_bookmark_tags->delete_all;
241 
242                # タグの登録と関連付け
243-               _registTags( $c, $ubid, @tags );
244+               _registTags( $c, $uid, $ubid, @tags );
245 
246                # コメントの更新
247                $ubdb->update( {user_bookmark_comment => $data{bookmark_comment}} );
248@@ -493,6 +500,7 @@
249        $c->res->redirect('/personal');
250 } # end of editBookmark()
251 
252+# bookmark に関連づけられた tag を得る
253 sub _getTags : Private {
254        my ( $c, $user_bookmark_tag_db ) = @_;
255        my @tags;
256@@ -518,6 +526,17 @@
257        return ($aname cmp $bname);
258 } # end of _cmpname() for sort
259 
260+=head2 getTags
261+
262+       ユーザが使用している Tag の一覧を XML で返す
263+
264+       <!ELEMENT tags (tagentry*)> <!-- 全体のエントリ -->
265+       <!ELEMENT tagentry (id,name)> <!-- タグの各エントリ -->
266+       <!ELEMENT id (#PCDATA)> <!-- タグの ID 番号 -->
267+       <!ELEMENT name (#PCDATA)> <!-- タグの名前 -->
268+
269+=cut
270+
271 sub getTags : Path('/gettags') {
272        my ( $self, $c ) = @_;
273        my @tags;
274@@ -527,9 +546,12 @@
275        $tags = sprintf( "<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" );
276        $tags .= sprintf( "<tags>\n" );
277        if( my $userbookmark = $udb->user_bookmarks ){
278-               foreach my $ub ($userbookmark->all){
279-                       my $ubtdb = $ub->user_bookmark_tags;
280-                       push(@tags, _getTags( $c, $ubtdb ));
281+               foreach my $t ($udb->tags->all){
282+                       my $tag = {
283+                               id => $t->tag_id,
284+                               name => $t->tag_name
285+                       };
286+                       push( @tags, $tag );
287                }
288        }
289        @tags = sort _cmpname (@tags);
290Index: lib/SecureSBMDB.pm
291===================================================================
292--- lib/SecureSBMDB.pm  (リビジョン 217)
293+++ lib/SecureSBMDB.pm  (作業コピー)
294@@ -25,6 +25,7 @@
295                Bookmark
296                UserRole
297                UserBookmark
298+               UserTag
299                UserBookmarkTag
300                UserBookmarkRole
301                RoleOwner
302Index: lib/SecureSBMDB/UserTag.pm
303===================================================================
304--- lib/SecureSBMDB/UserTag.pm  (リビジョン 0)
305+++ lib/SecureSBMDB/UserTag.pm  (リビジョン 0)
306@@ -0,0 +1,67 @@
307+package SecureSBMDB::UserTag;
308+
309+use base qw/DBIx::Class/;
310+
311+# Load required DBIC stuff
312+__PACKAGE__->load_components(qw/PK::Auto Core/);
313+# Set the table name
314+__PACKAGE__->table('user_tags');
315+# Set columns in table
316+__PACKAGE__->add_columns(qw/
317+       user_tag_id
318+       user_id
319+       tag_id
320+       /);
321+# Set the primary key for the table
322+__PACKAGE__->set_primary_key(qw/
323+       user_tag_id
324+       user_id
325+       tag_id
326+/);
327+
328+#
329+# Set relationships:
330+#
331+
332+# belongs_to():
333+#   args:
334+#     1) Name of relationship, DBIC will create accessor with this name
335+#     2) Name of the model class referenced by this relationship
336+#     3) Column name in *this* table
337+__PACKAGE__->belongs_to(user => 'SecureSBMDB::User', 'user_id');
338+__PACKAGE__->belongs_to(tag => 'SecureSBMDB::Tag', 'tag_id');
339+
340+=head1 NAME
341+
342+SecureSBMDB::UserTag - A model object representing the JOIN between a user and
343+a tag.
344+
345+=head1 DESCRIPTION
346+
347+This is an object that represents a row in the 'user_tags' table of your
348+application database.  It uses DBIx::Class (aka, DBIC) to do ORM.
349+
350+You probably won't need to use this class directly -- it will be automatically
351+used by DBIC where joins are needed.
352+
353+For Catalyst, this is designed to be used through MyApp::Model::SecureSBMDB.
354+Offline utilities may wish to use this class directly.
355+
356+=cut
357+
358+=head1 LICENSE
359+
360+This Program is distributed under version 1.0 of the Rinza Public
361+License Agreement, that is bundled with this package in the file
362+LICENSE, and is available through the website at the following URL:
363+http://www.tyzoh.jp/rinza/licenses/LICENSE-1.0.txt.
364+
365+The Initial Developer of the Original Program is Nihon Unisys, Ltd.
366+The Original Program is copyrighted (C) 2007 by Nihon Unisys, Ltd.
367+with all rights reserved.
368+There is NO WARRANTY OF ANY KIND by the Initial Developer of the
369+Original Program.
370+
371+=cut
372+
373+1;
374Index: lib/SecureSBMDB/Tag.pm
375===================================================================
376--- lib/SecureSBMDB/Tag.pm      (リビジョン 217)
377+++ lib/SecureSBMDB/Tag.pm      (作業コピー)
378@@ -25,6 +25,7 @@
379 #     2) Name of the model class referenced by this relationship
380 #     3) Column name in *foreign* table
381 __PACKAGE__->has_many(user_bookmark_tags => 'SecureSBMDB::UserBookmarkTag', 'tag_id');
382+__PACKAGE__->has_many(user_tags => 'SecureSBMDB::UserTag', 'tag_id');
383 
384 # many_to_many():
385 #   args:
386@@ -32,7 +33,7 @@
387 #     2) Name of has_many() relationship this many_to_many() is shortcut for
388 #     3) Name of belongs_to() relationship in model class of has_many() above
389 #   You must already have the has_many() defined to use a many_to_many().
390-__PACKAGE__->many_to_many(user_bookmarks => 'user_bookmark_tags', 'user_bookmark');
391+__PACKAGE__->many_to_many(users => 'user_tags', 'user');
392 
393 
394 =head1 NAME
395Index: lib/SecureSBMDB/User.pm
396===================================================================
397--- lib/SecureSBMDB/User.pm     (リビジョン 217)
398+++ lib/SecureSBMDB/User.pm     (作業コピー)
399@@ -27,6 +27,7 @@
400 #     2) Name of the model class referenced by this relationship
401 #     3) Column name in *foreign* table
402 __PACKAGE__->has_many(user_roles => 'SecureSBMDB::UserRole', 'user_id');
403+__PACKAGE__->has_many(user_tags => 'SecureSBMDB::UserTag', 'user_id');
404 __PACKAGE__->has_many(user_bookmarks => 'SecureSBMDB::UserBookmark', 'user_id');
405 __PACKAGE__->has_many(role_owners => 'SecureSBMDB::RoleOwner', 'user_id');
406 __PACKAGE__->has_many(user_profiles => 'SecureSBMDB::UserProfile', 'user_id');
407@@ -38,6 +39,7 @@
408 #     3) Name of belongs_to() relationship in model class of has_many() above
409 #   You must already have the has_many() defined to use a many_to_many().
410 __PACKAGE__->many_to_many(roles => 'user_roles', 'role');
411+__PACKAGE__->many_to_many(tags => 'user_tags', 'tag');
412 __PACKAGE__->many_to_many(bookmarks => 'user_bookmarks', 'bookmark');
413 __PACKAGE__->many_to_many(own_roles => 'role_owners', 'role');
414