annotate lib/project_man.pl @ 3:17ce4f3bffa2 default tip

Uploaded
author jesse-erdmann
date Tue, 24 Jan 2012 18:33:41 -0500
parents 1437a2df99c0
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
0
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
1 #!/project/bioperl/perl-5.10.1-sles11/bin/perl -w
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
2
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
3 ##
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
4 #
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
5 # This file requires that a database connection be configured and
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
6 # connected to the scalar $dbh
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
7 #
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
8 ##
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
9
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
10 if (!defined($project_table_name)) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
11 $project_table_name = "TAPDANCE_project_master";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
12 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
13 return 1;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
14
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
15 sub project_table_check {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
16 my $create_string = "CREATE TABLE IF NOT EXISTS " . $project_table_name . " (name VARCHAR(255) NOT NULL, insert_gen TIMESTAMP, cis_call TIMESTAMP)";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
17 my $sth = $dbh->prepare($create_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
18 $sth->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
19 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
20
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
21
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
22 sub project_exists {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
23 my ($proj_name) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
24 &project_table_check();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
25 my $test_string = "SELECT COUNT(name) FROM " . $project_table_name . " WHERE name = '" . $proj_name . "'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
26 my $sth = $dbh->prepare($test_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
27 $sth->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
28 (@row) = $sth->fetchrow_array;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
29 return $row[0] > 0;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
30 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
31
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
32 sub update_project {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
33 &project_table_check();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
34 my ($proj_name, $insert_gen, $cis_call) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
35 my $update_string = "UPDATE " . $project_table_name . " SET";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
36 my $prefix = " ";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
37 if ($insert_gen) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
38 $update_string = $update_string . $prefix . "insert_gen = NOW()";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
39 $prefix = ", "
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
40 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
41 if ($cis_call) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
42 $update_string = $update_string . $prefix . "cis_call = NOW()";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
43 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
44 $update_string = $update_string . " WHERE name = '" . $proj_name . "'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
45 my $sth = $dbh->prepare($update_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
46 $sth->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
47 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
48
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
49 sub insert_project {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
50 &project_table_check();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
51 my ($proj_name, $insert_gen, $cis_call) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
52 my $insert_string = "INSERT INTO " . $project_table_name;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
53 my $insert_cols_string = " (name";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
54 my $insert_vals_string = " VALUES('" . $proj_name . "'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
55 if ($insert_gen) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
56 $insert_cols_string = $insert_cols_string . ", insert_gen";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
57 $insert_vals_string = $insert_vals_string . ", NOW()";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
58 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
59 if ($cis_call) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
60 $insert_cols_string = $insert_cols_string . ", cis_call";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
61 $insert_vals_string = $insert_vals_string . ", NOW()";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
62 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
63 $insert_cols_string = $insert_cols_string . ") ";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
64 $insert_vals_string = $insert_vals_string . ") ";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
65 $insert_string = $insert_string . $insert_cols_string . $insert_vals_string;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
66 my $sth = $dbh->prepare($insert_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
67 $sth->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
68 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
69
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
70 sub delete_project {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
71 &project_table_check();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
72 my ($proj_name) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
73 my $delete_string = "DELETE FROM " . $project_table_name . " WHERE name = '" . $proj_name . "'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
74 my $sth = $dbh->prepare($delete_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
75 $sth->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
76 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
77
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
78 sub set_project_status {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
79 my ($proj_name, $insert_gen, $cis_call) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
80 if (&project_exists($proj_name)) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
81 &update_project($proj_name, $insert_gen, $cis_call);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
82 } else {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
83 &insert_project($proj_name, $insert_gen, $cis_call);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
84 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
85 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
86
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
87 sub get_project_list {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
88 my ($user_name, $query_type, @tags) = @_;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
89 &project_table_check();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
90 my $select_string = "SELECT name FROM " . $project_table_name . " WHERE name LIKE '" . $user_name . "%'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
91 my $sth = $dbh->prepare($select_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
92 $sth->execute();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
93 my @projects = ();
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
94 while ((@row) = $sth->fetchrow_array) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
95 if ($query_type eq "all") {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
96 push @projects, $row[0];
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
97 } else {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
98 my $conjunctive;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
99 #if ($query_type eq "union") {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
100 #$conjunctive = "AND";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
101 #} elsif ($query_type eq "join") {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
102 $conjunctive = "OR";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
103 #}
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
104 $select_string = "SELECT COUNT(*) from metadata_$row[0] where descriptor LIKE '%";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
105 $select_string = $select_string . join("%' " . $conjunctive . " descriptor LIKE '%", @tags) . "%'";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
106 #print $select_string . "\n";
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
107 my $sth2 = $dbh->prepare($select_string);
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
108 $sth2->execute;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
109 while ((@row2) = $sth2->fetchrow_array) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
110 if ($row2[0] > 0) {
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
111 push @projects, $row[0];
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
112 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
113 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
114 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
115 }
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
116 return \@projects;
1437a2df99c0 Uploaded
jesse-erdmann
parents:
diff changeset
117 }