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
|
package YalpInputs.YalpPGSqlInput;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.*;
import java.sql.*;
import org.omg.CosNaming.*;
import org.omg.CosNaming.NamingContextPackage.*;
import org.omg.CORBA.*;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import YalpInterfaces.*;
public class YalpInputPluginImpl extends InputPluginInterfacePOA {
private String db;
private String dbuser;
private String dbpasswd;
private Statement stat;
private Connection con;
private PluginInfo pluginInfo;
private String log4jFile = "log4j_input_plugin.conf";
private static Logger logger =
Logger.getLogger("Yalp.InputPlugins.PGSqlInput.YalpInputPluginImpl");
private ORB orb;
public YalpInputPluginImpl()
{
PropertyConfigurator.configureAndWatch(log4jFile);
logger.debug("YalpInputPluginImpl()");
}
public void setORB(ORB _orb)
{
orb = _orb;
String db = "jdbc:postgresql://localhost:5432/yalp";
String dbYalpUser = "yalp";
String dbPasswd = "yalp";
try{
dbuser = dbYalpUser;
dbpasswd = dbPasswd;
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection(db,dbuser,dbpasswd);
System.out.println("YalpPGSqlInput: db connection established");
stat= con.createStatement();
} catch (SQLException e) {
System.out.println("Exception in PGSqlInput Constructor: "+e);
} catch (ClassNotFoundException e) {
System.out.println("Exception in PGSqlInput Constructor: "+e);
}
}
/*
* submits changes to yalpMediaDatabase
*
* @param change
* describes the change to commit
* @return int
* -1 if failed
*/
public void changeMedia (Media toChange, Action todo, YalpErrorHolder err) {
/* t.b.d. alter this to new database design
try{
String sql1,sql2,sql3;
int Return;
switch (change.updateType){
// if updateType is UPDATE
case UPDATE:
sql1="update \"medien\" set \"type\"='"+change.type+"', \"title\"='"+change.title+"', \"author\"='"+change.author+"', \"album\"='"+change.album+"', \"category\"='"+change.category+"', \"year\"='"+change.year+"', \"duration\"='"+change.duration+"', \"aBitrate\"="+change.aBitrate+", \"vBitrate\"="+change.vBitrate+", \"resolution\"='"+change.resolution+"', \"lastEdit\"='"+new java.util.Date(System.currentTimeMillis()).toString()+"' where \"id\"= "+change.id+" ;";
System.out.println(sql1);
Return=stat.executeUpdate(sql1);
break;
// if updateType is INSERT INTO
case INSERT:
sql1="insert into \"medien\" values(nextval('medienId'),'"+change.type+"','"+change.title+"','"+change.author+"','"+change.album+"','"+change.category+"','"+change.year+"','"+change.duration+"',"+change.aBitrate+","+change.vBitrate+",'"+change.resolution+"',"+change.ownerId+",'"+new java.util.Date(System.currentTimeMillis()).toString()+"','"+change.path+"','"+change.name+"');";
System.out.println(sql1);
Return=stat.executeUpdate(sql1);
break;
// if updateType is DELETE
case DELETE:
sql1="delete from \"medien\" where \"id\"= "+change.id+" ;";
System.out.println(sql1);
Return=stat.executeUpdate(sql1);
break;
default : return -1;
}
// perform operation on table an return number of updated rows
}catch(SQLException e){
System.out.println("Exception in PGSqlInput.changeMedia: "+e);
}
*/
}
/*
* returns an ArrayList of Results which are matching to the commited Find object
*
* @param media
* Object which describes search criterias
* @return ArrayList<Result>
* List with Results matching search criteria
*/
public void search(String str, MediaType[] types, MediasHolder result, YalpErrorHolder err) {
System.out.println("juhu: searching for: "+str);
/* t.b.d. alter this to new database design
try{
ArrayList <String > searchWords=stringCut(media.str);
String query= new String();
Boolean first;
for (int i=0;i<searchWords.size();i++){
first=true;
if (i!=0)query = query+") intersect ";
query= query+"select * from \"medien\" where (";
if (media.audio){
first=false;
query=query+"\"type\" = 'audio' ";
}
if (media.video){
if (first)first=false;
else query=query+"or ";
query=query+"\"type\" = 'video' ";
}
if (!first)query=query+") and (";
query=query+"\"title\" Ilike '%"+searchWords.get(i)+"%' or \"author\" Ilike '%"+searchWords.get(i)+"%' or \"album\" Ilike '%"+searchWords.get(i)+"%'or \"year\" Ilike '%"+searchWords.get(i)+"%'or \"category\" Ilike '%"+searchWords.get(i)+"%'";
}
query=query+")order by \"id\";";
ArrayList<Result> resultList=new ArrayList<Result>();
Statement stat= con.createStatement();
ResultSet result=stat.executeQuery(query);
while(result.next()){
resultList.add( new Result(result.getInt(1),result.getString(2),result.getString(3),result.getString(4),result.getString(5),result.getString(6),result.getString(7),result.getString(8),result.getInt(9),result.getInt(10),result.getString(11),result.getInt(12),result.getString(13),result.getString(14),result.getString(15)));
}
return resultList;
}catch(SQLException e){
System.out.println("Exception in PGSqlInput.search: "+e);
return new ArrayList<Result>();
}
*/
}
/*
* cuts Strings and returns an ArrayList of the cutted Strings
*
* @param str
* String to cut
* @return ArrayList<String>
* ArrayList with cutted Strings
*/
private ArrayList <String> stringCut (String str){
int i=0,j=0;
ArrayList<String> list= new ArrayList<String>();
while(j!=-1){
j=str.indexOf(" ",i);
if (j!=-1){
list.add(new String (str.substring(i,j)));
i=j+1;
} else list.add(new String (str.substring(i,str.length())));
}
return list;
}
/*
* returns number of medias in database
*
* @return String
* Number of medias in database
*/
public void getNumOfMedias(IntHolder num, YalpErrorHolder err) {
try{
ResultSet result=this.stat.executeQuery("select count (\"id\") from \"medien\";");
result.next();
num = new IntHolder(new Integer(result.getString(1)));
}catch(SQLException e){
System.out.println("exception in PGSql Input getNumOfMedias: "+e);
YalpError error = new YalpError();
error.code = YalpErrorCode.ERROR_SQL;
error.level= YalpErrorLevel.ERROR_LEVEL_ERROR;
error.descr= e.toString();
err = new YalpErrorHolder(error);
}
}
/*
* returns plugin information
* @param PluginInfoHolder info holder for PluginInformation
* @param YalpErrorHolder err holder for error information
*/
public void getInfo(PluginInfoHolder info, YalpErrorHolder err)
{
info = new PluginInfoHolder(pluginInfo);
}
}
|
