package com.ptaack.seobot;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.ptaack.helpers.db.ConnectionProducer;
import com.ptaack.seobot.model.Proxy;
public class SeoBotDatabase {
public static void init() {
Statement st;
String sql;
try {
Main.conn = ConnectionProducer.createSqliteConn(Configuration.DBNAME);
st = Main.conn.createStatement();
sql = "create table if not exists anonymproxy (" +
" id integer not null primary key autoincrement" +
",ip nvarchar(11) not null" +
",port integer not null" +
",dead integer not null)";
st.execute(sql);
sql = "create table if not exists useragent (" +
" id integer not null primary key autoincrement" +
",uaname nvarchar(255) not null)";
st.execute(sql);
int uacount = st.executeQuery("select count(*) from useragent").getInt(1);
if (uacount == 0) {
sql = "insert into useragent (uaname) values ('%s')";
try {
BufferedReader reader = new BufferedReader(new FileReader("useragent"));
String agent;
while ((agent = reader.readLine()) != null) {
st.execute(String.format(sql, agent));
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void addAnonymProxy(Proxy proxy) {
Statement st;
String sql = "insert into anonymproxy (ip, port) values ('%s', %d)";
try {
st = Main.conn.createStatement();
st.execute(String.format(sql, proxy.getIp(), proxy.getPort()));
} catch (SQLException e) {
e.printStackTrace();
}
}
public static Object getRandomObject(String table) {
Statement st;
ResultSet rs;
Object result = null;
String sql = "select * from " + table + " order by random() limit 1";
try {
st = Main.conn.createStatement();
rs = st.executeQuery(sql);
if (rs.isClosed()) {
System.err.println("Not found object. Please check table: " + table);
System.exit(1);
}
if (table.toLowerCase().equals("anonymproxy"))
if (rs.getBoolean("dead"))
getRandomObject(table);
else
result = new Proxy(String.format("%s:%d", rs.getString("ip"), rs.getInt("port")));
else if (table.toLowerCase().equals("useragent"))
result = rs.getString("uaname");
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public static boolean isDublicate(Proxy proxy) {
Statement st;
ResultSet rs;
boolean result = false;
String sql = "select count(*) from anonymproxy where (ip='%s') and (port=%d)";
try {
st = Main.conn.createStatement();
rs = st.executeQuery(String.format(sql, proxy.getIp(), proxy.getPort()));
result = (rs.getInt(1) > 0) ? true : false;
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
public static void markAsDead(Proxy proxy) {
String sql = "update anonymproxy set dead = true where ip='%s' and port=%d";
Statement st;
try {
st = Main.conn.createStatement();
st.execute(String.format(sql, proxy.getIp(), proxy.getPort()));
} catch (SQLException e) {
e.printStackTrace();
}
}
}