package data;

import crypto.CryptoConstants;
import crypto.Cryptography;

import java.security.PublicKey;
import java.sql.*;

/**
 * @author Gary Ye
 * @version 11/30/13
 */
public class SQLiteManager {
    // suggestion: using the jdbc pooling method described here:
    // http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html
    private String url;

    public SQLiteManager(String db) {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        this.url = "jdbc:sqlite:" + db;
    }

    public PublicKey getPublicKeyFromId(String receiverId) {
        String query = "SELECT public_key FROM user";
        Connection connection = null;
        Statement statement = null;
        ResultSet rs = null;
        System.out.println(CryptoConstants.asymm_keylength / 8);
        byte[] publicKeyBytes = new byte[CryptoConstants.asymm_keylength / 8];

        System.out.println(publicKeyBytes.length);

        for(int i = 0; i < publicKeyBytes.length; i++)
            publicKeyBytes[i] = (byte)0;

        try {
            connection = DriverManager.getConnection(url);
            statement = connection.createStatement();
            rs = statement.executeQuery(query);
            while (rs.next()) {
                publicKeyBytes = rs.getBlob("public_key").getBytes(0, CryptoConstants.asymm_keylength / 8);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        } finally {
            try {
                if (rs != null) rs.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException ignored) {
            }
        }

        return Cryptography.getPublicKeyFromBytes(publicKeyBytes);
    }

    public void insertUser(User user){
        // String query = String.format("INSERT INTO user VALUES ()
    }
}

@@ -68,16 +68,39 @@
 raphy;%0A%0A
+import java.io.Writer;%0A
 import j
@@ -141,16 +141,44 @@
 a.sql.*;
+%0Aimport java.util.ArrayList;
 %0A%0A/**%0A *
@@ -1963,19 +1963,16 @@
     
- //
  String 
 quer
@@ -1971,57 +1971,980 @@
 ing 
-query = String.format(%22INSERT INTO user VALUES ()
+sql = %22INSERT INTO user VALUES(?,?,?) %22;%0A        try (Connection connection = DriverManager.getConnection(url);%0A             PreparedStatement pstmt = connection.prepareStatement(sql);%0A            )%7B%0A            Blob publicKeyBlob, symmetricKeyBlob;%0A            pstmt.setString(1, user.getId());%0A%0A            publicKeyBlob = connection.createBlob();%0A            publicKeyBlob.setBytes(0, user.getKeyPair().getPublic().getEncoded());%0A            pstmt.setBlob(2, publicKeyBlob);%0A%0A            symmetricKeyBlob = connection.createBlob();%0A            symmetricKeyBlob.setBytes(0, user.getSecretKey().getEncoded());%0A            pstmt.setBlob(3, symmetricKeyBlob);%0A%0A            pstmt.executeUpdate();%0A        %7D catch (SQLException ex) %7B%0A            System.out.println(%22Unexpected exception: %22 + ex.toString());%0A        %7D%0A    %7D%0A%0A    /**%0A     * Load the names of all users%0A     * @return the name of all users%0A     */%0A    public ArrayList%3CString%3E loadUsers()%7B%0A        return null;
 %0A   

Insert into User works now