hostage/src/de/tudarmstadt/informatik/hostage/logging/DatabaseHandler.java

package de.tudarmstadt.informatik.hostage.logging;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.ArrayList;
import java.util.List;

import de.tudarmstadt.informatik.hostage.logging.Record.TYPE;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "recordManager";

	// Contacts table names
	private static final String TABLE_RECORDS = "records";
	private static final String TABLE_BSSIDS = "bssids";

	// Contacts Table Columns names
	private static final String KEY_ID = "_id";
	private static final String KEY_ATTACK_ID = "attack_id";
	private static final String KEY_PROTOCOL = "protocol";
	private static final String KEY_TYPE = "type";
	private static final String KEY_TIME = "timestamp";
	private static final String KEY_LOCAL_IP = "localIP";
	private static final String KEY_LOCAL_HOSTNAME = "localHostName";
	private static final String KEY_LOCAL_PORT = "localPort";
	private static final String KEY_REMOTE_IP = "remoteIP";
	private static final String KEY_REMOTE_HOSTNAME = "remoteHostName";
	private static final String KEY_REMOTE_PORT = "remotePort";
	private static final String KEY_BSSID = "_bssid";
	private static final String KEY_SSID = "ssid";
	private static final String KEY_PACKET = "packet";
	
	// Database sql create statements
	private static final String CREATE_RECORD_TABLE = "CREATE TABLE " + TABLE_RECORDS + "(" + KEY_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ATTACK_ID + " INTEGER," + KEY_PROTOCOL + " TEXT,"
			+ KEY_TYPE + " TEXT," + KEY_TIME + " INTEGER," + KEY_LOCAL_IP
			+ " BLOB," + KEY_LOCAL_HOSTNAME + " TEXT," + KEY_LOCAL_PORT + " INTEGER," + KEY_REMOTE_IP
			+ " BLOB," + KEY_REMOTE_HOSTNAME + " TEXT," + KEY_REMOTE_PORT + " INTEGER," 
			+ KEY_BSSID + " TEXT," +  KEY_PACKET + " TEXT," 
			+ "FOREIGN KEY("+ KEY_BSSID +") REFERENCES " + TABLE_BSSIDS + "("+KEY_BSSID+")" + ")";
	
	private static final String CREATE_BSSID_TABLE = "CREATE TABLE " + TABLE_BSSIDS + "(" + KEY_BSSID
			+ " TEXT PRIMARY KEY," + KEY_SSID + " TEXT" + ")";

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_BSSID_TABLE);
		db.execSQL(CREATE_RECORD_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORDS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BSSIDS);
		
		// Create tables again
		onCreate(db);
	}

	// Adding new record
	public void addRecord(Record record) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues bssidValues = new ContentValues();
		bssidValues.put(KEY_BSSID, record.getBSSID());
		bssidValues.put(KEY_SSID, record.getSSID());
		
		ContentValues recordValues = new ContentValues();
		recordValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		recordValues.put(KEY_PROTOCOL, record.getProtocol().toString());
		recordValues.put(KEY_TYPE, record.getType().name()); // Log Type
		recordValues.put(KEY_TIME, record.getTimestamp()); // Log Timestamp
		recordValues.put(KEY_LOCAL_IP, record.getLocalIP().getAddress()); // Log Local IP
		recordValues.put(KEY_LOCAL_HOSTNAME, record.getLocalIP().getHostName());
		recordValues.put(KEY_LOCAL_PORT, record.getLocalPort()); // Log Local Port
		recordValues.put(KEY_REMOTE_IP, record.getRemoteIP().getAddress()); // Log Remote IP
		recordValues.put(KEY_REMOTE_HOSTNAME, record.getRemoteIP().getHostName());
		recordValues.put(KEY_REMOTE_PORT, record.getRemotePort()); // Log Remote Port
		recordValues.put(KEY_BSSID, record.getBSSID());
		recordValues.put(KEY_PACKET, record.getPacket()); // Log Packet
		


		// Inserting Rows
		db.insertWithOnConflict(TABLE_BSSIDS, null, bssidValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.insert(TABLE_RECORDS, null, recordValues);
		db.close(); // Closing database connection
	}
	
	private Record createRecord(Cursor cursor){
		Record record = new Record();
		try {
		record.setID(Integer.parseInt(cursor.getString(0)));
		record.setAttack_id(cursor.getLong(1));
		record.setProtocol(cursor.getString(2));
		record.setType(cursor.getString(3).equals("SEND") ? TYPE.SEND : TYPE.RECEIVE);
		record.setTimestamp(cursor.getLong(4));
		record.setLocalIP(InetAddress.getByAddress(cursor.getString(6), cursor.getBlob(5)));
		record.setLocalPort(Integer.parseInt(cursor.getString(7)));
		record.setRemoteIP(InetAddress.getByAddress(cursor.getString(9), cursor.getBlob(8)));
		record.setRemotePort(Integer.parseInt(cursor.getString(10)));
		record.setBSSID(cursor.getString(11));
		record.setPacket(cursor.getString(12));
		record.setSSID(cursor.getString(13));
		} catch (UnknownHostException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return record;
	}

	// Getting single record
	public Record getRecord(int id) {
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " WHERE " + KEY_ID + " = " + id;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);
		Record record = null;
		if (cursor.moveToFirst()){
			record = createRecord(cursor);
			Log.i("DatabaseHandler", record == null ? "null" : "not null");
		} 
		
        cursor.close();
		db.close();
		// return contact
		return record;
	}

	// Getting All Records
	public ArrayList<Record> getAllRecords() {
		ArrayList<Record> recordList = new ArrayList<Record>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);				
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
        cursor.close();
		db.close();
		// return record list
		return recordList;
	}
	
	// Getting record Count
    public int getRecordCount() {
        String countQuery = "SELECT  * FROM " + TABLE_RECORDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
       int result = cursor.getCount();
       cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
	// Getting record Count
    public int getAttackCount() {
        String countQuery = "SELECT  * FROM " + TABLE_RECORDS + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
	// Getting record Count
    public int getAttackPerProtokolCount(String protocol) {
        String countQuery = "SELECT  * FROM " + TABLE_RECORDS + " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
    //Get the smallest AttackId
    public int getSmallestAttackId(){
    	String selectQuery = "SELECT MIN(" + KEY_ATTACK_ID +") FROM " + TABLE_RECORDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);        
        int result;
        
        if (cursor.moveToFirst()) {
        	result = cursor.getInt(0);
        } else{
        	result = -1;
        }        
    	cursor.close();
    	db.close();
    	return result;
    }
    
    //Get the smallest AttackId
    public int getHighestAttackId(){
    	String selectQuery = "SELECT MAX(" + KEY_ATTACK_ID +") FROM " + TABLE_RECORDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        int result;
        
        if (cursor.moveToFirst()) {
        	result = cursor.getInt(0);
        } else{
        	result = -1;
        }        
    	cursor.close();
    	db.close();
    	return result;
    }
    
	// Getting first Record for each AttackId
    public ArrayList<Record> getRecordOfEachAttack() {
    	ArrayList<Record> recordList = new ArrayList<Record>();
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
		
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}       
        cursor.close();
 
        // return count
        db.close();
        return recordList;
    }
    
	// Getting first Record for each AttackId greater than a given id
    public ArrayList<Record> getRecordOfEachAttack(int attack_id) {
    	ArrayList<Record> recordList = new ArrayList<Record>();
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " WHERE " + KEY_ATTACK_ID + " > " + attack_id + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
		
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext()); 
		}       
        cursor.close();
 
        // return count
        db.close();
        return recordList;
    }
    
	// Getting first Record for each AttackId greater than a given id
    public Record getRecordOfAttackId(int attack_id) {
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " WHERE " + KEY_ATTACK_ID + " = " + attack_id + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        Record record = null;
        
		if (cursor.moveToFirst()) {
				record = createRecord(cursor);
		}
        cursor.close();
 
        // return count
        db.close();
        return record;
    }
    
    public boolean bssidSeen(String protocol, String BSSID){
        String countQuery = "SELECT  * FROM " + TABLE_RECORDS + " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " AND " + KEY_BSSID + " = " + "'" + BSSID + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
        db.close();
        return result > 0;        
    }
    
    public String[] getAllBSSIDS(){
        String selectQuery = "SELECT  * FROM " + TABLE_BSSIDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        String[] bssidList = new String[cursor.getCount()];
        int counter = 0;
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				bssidList[counter] = cursor.getString(0);
			} while (cursor.moveToNext());
		}       
        cursor.close();
        db.close();
        return bssidList;
    }
    
    public String getSSID(String bssid){
    	String selectQuery = "SELECT "+ KEY_SSID +" FROM " + TABLE_BSSIDS + " WHERE " + KEY_BSSID + " = " + "'" + bssid + "'";
    	SQLiteDatabase db = this.getReadableDatabase();
    	Cursor cursor = db.rawQuery(selectQuery, null);
    	String ssid = null;
    	if(cursor.moveToFirst()){
    		ssid = cursor.getString(0);
    	}
        cursor.close();
        db.close();
        return ssid;
    }
    
    public void deleteByBSSID(String bssid){
    	SQLiteDatabase db = this.getReadableDatabase();
    	db.delete(TABLE_RECORDS, KEY_BSSID + " = ?", new String[]{bssid});
    	db.close();
    }
    
    public void deleteByDate(long date){
    	SQLiteDatabase db = this.getReadableDatabase();
    	String deleteQuery = "DELETE  FROM " + TABLE_RECORDS + " WHERE " + KEY_TIME + " < " + date;
    	db.execSQL(deleteQuery);
    	db.close();
    }
    
    //Delete all Data from Database
    public void clearData(){
    	SQLiteDatabase db = this.getReadableDatabase();
        db.delete(TABLE_RECORDS, null, null);
        db.close();
    }
}