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

package de.tudarmstadt.informatik.hostage.logging;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

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 name
	private static final String TABLE_RECORDS = "records";

	// 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_PORT = "localPort";
	private static final String KEY_REMOTE_IP = "remoteIP";
	private static final String KEY_REMOTE_PORT = "remotePort";
	private static final String KEY_PACKET = "packet";

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

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_RECORD_TABLE = "CREATE TABLE " + TABLE_RECORDS + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY," + KEY_ATTACK_ID + " INTEGER," + KEY_PROTOCOL + " TEXT,"
				+ KEY_TYPE + " TEXT," + KEY_TIME + " TEXT," + KEY_LOCAL_IP
				+ " Text," + KEY_LOCAL_PORT + " INTEGER," + KEY_REMOTE_IP
				+ " TEXT," + KEY_REMOTE_PORT + " INTEGER," + KEY_PACKET
				+ " TEXT" + ")";
		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);

		// Create tables again
		onCreate(db);
	}

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

		ContentValues values = new ContentValues();
		values.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		values.put(KEY_PROTOCOL, record.getProtocol());
		values.put(KEY_TYPE, record.getType()); // Log Type
		values.put(KEY_TIME, record.getTimestamp()); // Log Timestamp
		values.put(KEY_LOCAL_IP, record.getLocalIP()); // Log Local IP
		values.put(KEY_LOCAL_PORT, record.getLocalPort()); // Log Local Port
		values.put(KEY_REMOTE_IP, record.getRemoteIP()); // Log Remote IP
		values.put(KEY_REMOTE_PORT, record.getRemotePort()); // Log Remote Port
		values.put(KEY_PACKET, record.getPacket()); // Log Packet

		// Inserting Row
		db.insert(TABLE_RECORDS, null, values);
		db.close(); // Closing database connection
	}

	// Getting single record
	public SQLRecord getRecord(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_RECORDS, new String[] { KEY_ID,
				KEY_ATTACK_ID, KEY_PROTOCOL, KEY_TYPE, KEY_TIME, KEY_LOCAL_IP,
				KEY_LOCAL_PORT, KEY_REMOTE_IP, KEY_REMOTE_PORT, KEY_PACKET },
				KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null,
				null, null);
		if (cursor != null)
			cursor.moveToFirst();

		SQLRecord record = new SQLRecord(Integer.parseInt(cursor.getString(0)),
				Integer.parseInt(cursor.getString(1)), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5),
				Integer.parseInt(cursor.getString(6)), cursor.getString(7),
				Integer.parseInt(cursor.getString(8)), cursor.getString(9));
		
        cursor.close();
		db.close();
		// return contact
		return record;
	}

	// Getting All Records
	public ArrayList<SQLRecord> getAllRecords() {
		ArrayList<SQLRecord> contactList = new ArrayList<SQLRecord>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS;

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

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				SQLRecord record = new SQLRecord();
				record.setID(Integer.parseInt(cursor.getString(0)));
				record.setAttack_id(Integer.parseInt(cursor.getString(1)));
				record.setProtocol(cursor.getString(2));
				record.setType(cursor.getString(3));
				record.setTimestamp(cursor.getString(4));
				record.setLocalIP(cursor.getString(5));
				record.setLocalPort(Integer.parseInt(cursor.getString(6)));
				record.setRemoteIP(cursor.getString(7));
				record.setRemotePort(Integer.parseInt(cursor.getString(8)));
				record.setPacket(cursor.getString(9));
				
				// Adding record to list
				contactList.add(record);
			} while (cursor.moveToNext());
		}
        cursor.close();
		db.close();
		// return record list
		return contactList;
	}
	
	// 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;
    }
    
    
    public SQLRecord getFirstEntry(){
    	//TODO Fixen oder löschen
    	SQLiteDatabase db = this.getReadableDatabase();
    	Cursor cursor = db.query(TABLE_RECORDS, new String[] { KEY_ID,
				KEY_ATTACK_ID, KEY_PROTOCOL, KEY_TYPE, KEY_TIME, KEY_LOCAL_IP,
				KEY_LOCAL_PORT, KEY_REMOTE_IP, KEY_REMOTE_PORT, KEY_PACKET },
				KEY_ID + "=?", new String[] { "min(" + KEY_ID + ")" }, null,
                null, null, null);
		
    	if (cursor != null)
			cursor.moveToFirst();
 
		SQLRecord record = new SQLRecord(Integer.parseInt(cursor.getString(0)),
				Integer.parseInt(cursor.getString(1)), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5),
				Integer.parseInt(cursor.getString(6)), cursor.getString(7),
				Integer.parseInt(cursor.getString(8)), cursor.getString(9));    
		return record;
    }
    
    //Delete all Data from Database
    public void clearData(){
    	SQLiteDatabase db = this.getReadableDatabase();
        db.delete(TABLE_RECORDS, null, null);
        db.close();
    }
}