Databases

Narrative & Reflection

Reflection on the Process

Learning & Challenges: I adopted a security mindset by assuming all user input is potentially malicious. I defined an approach where all data is explicitly validated before touching the database to eradicate potential vulnerabilities. Furthermore, the schema design considers future changes; by normalizing the data into relational tables, the system can easily accommodate new features like "User Accounts" or "Multi-location Inventory" without breaking existing functionality. I also verified the schema migration by manually inspecting the database after an upgrade to ensure all existing inventory items were preserved and correctly linked to the new log table.

Course Outcomes Met:

• Outcome 4: Using well-founded database techniques to implement a normalized schema that supports historical tracking and complex queries.
• Outcome 5: Developing a security mindset, anticipating potential exploits through strict input validation and integrity checks to ensure the privacy and security of the stored data.

Upgrading the Database

The original database was too simple, it only knew how many items you had right now. I upgraded it to Version 2 by adding a history table called usage_log. This table stores the item_id, the change_amount, and the timestamp. This allows us to run aggregation queries, such as SELECT SUM(change_amount), to determine total usage over specific timeframes.

• Better Organization: The logs are linked to items by their ID, so changing an item's name doesn't break the history.
• History Tracking: Every time an item is added or removed, it is written down. This lets us see past trends and undo mistakes.

// DatabaseHelper.java
                
// Updated Table for setting unique item ID
private static final String CREATE_INVENTORY_TABLE = "CREATE TABLE " + TABLE_INVENTORY + "(" +
        COLUMN_ITEM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        COLUMN_ITEM_NAME + " TEXT NOT NULL, " +
        COLUMN_ITEM_QUANTITY + " INTEGER NOT NULL, " +
        COLUMN_ITEM_REQUIREDINVENTORY + " INTEGER NOT NULL" + ")";
                
// New Table for Tracking Usage History (v2)
private static final String CREATE_USAGE_LOG_TABLE = "CREATE TABLE " + TABLE_USAGE_LOG + "(" +
        COLUMN_LOG_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
        COLUMN_LOG_ITEM_ID + " INTEGER NOT NULL, " +
        COLUMN_LOG_CHANGE + " INTEGER NOT NULL, " +
        COLUMN_LOG_TIMESTAMP + " INTEGER NOT NULL" + ")";
);
            

Secure Database Operations

In AddItemActivity.java, the application checks for duplicate item names and ensures numeric values are valid before committing changes to the database, preventing data corruption.

// AddItemActivity.java

/**
 * Handles the process of adding or updating an item in the database.
 */
private void addItemToInventory() {
    final String itemName = itemNameEditText.getText().toString().trim();
    final String itemQuantityStr = itemQuantityEditText.getText().toString().trim().replace(",", "");
    final String requiredItemQuantityStr = requiredItemQuantityEditText.getText().toString().trim().replace(",", "");

    if (itemName.isEmpty() || itemQuantityStr.isEmpty() || requiredItemQuantityStr.isEmpty()) {
        Toast.makeText(this, "Please fill in all fields.", Toast.LENGTH_SHORT).show();
        return;
    }

    long itemQuantity;
    long requiredInventory;
    try {
        itemQuantity = Long.parseLong(itemQuantityStr);
        requiredInventory = Long.parseLong(requiredItemQuantityStr);
        if (itemQuantity < 0 || requiredInventory < 0) {
            Toast.makeText(this, "Quantity and Required Inventory cannot be negative.", Toast.LENGTH_SHORT).show();
            return;
        }
    } catch (final NumberFormatException e) {
        Toast.makeText(this, "Quantity and Required Inventory must be valid numbers.", Toast.LENGTH_SHORT).show();
        return;
    }

    // Check for duplicate name
    final InventoryItem existingItem = inventoryViewModel.getItemByName(itemName);
    if (existingItem != null) {
        if (editingItemId == -1) {
            // Adding new item, name taken
            Toast.makeText(this, "Item name already exists. Please use the Edit button on the inventory screen.", Toast.LENGTH_LONG).show();
            return;
        } else if (existingItem.getId() != editingItemId) {
            // Editing item, name taken by another item
            Toast.makeText(this, "Item name already exists.", Toast.LENGTH_LONG).show();
            return;
        }
    }

    boolean success = false;
    String toastMessage = "";

    if (editingItemId != -1) {
        // **EDIT MODE** - Update existing item details
        success = inventoryViewModel.updateItemDetails(editingItemId, itemName, itemQuantity, requiredInventory);
        toastMessage = "Item '" + itemName + "' updated successfully.";
    } else {
        // **ADD MODE** - Add a new item
        success = inventoryViewModel.addItem(itemName, itemQuantity, requiredInventory);
        toastMessage = "New item '" + itemName + "' added successfully.";
    }

    if (success) {
        Toast.makeText(this, toastMessage, Toast.LENGTH_SHORT).show();
        // Navigate back to the OverviewActivity and refresh the list
        final Intent intent = new Intent(AddItemActivity.this, OverviewActivity.class);
        // These flags ensure the OverviewActivity is at the top and will refresh its data in onResume
        intent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP | Intent.FLAG_ACTIVITY_NEW_TASK);
        startActivity(intent);
        finish();
    } else {
        Toast.makeText(this, "Operation failed. Database error.", Toast.LENGTH_LONG).show();
    }
}
            

Data Security & Hashing

To further enhance security, I implemented a hashing utility using SHA-256 with salting. This ensures that sensitive information (such as user passwords for the login screen) is never stored in plain text.

// UserRepository.java

/**
 * Hashes a password using SHA-256 with an optional salt.
 * @param password The plain text password.
 * @param salt The salt string (hex), can be null.
 * @return The hashed password string (hex), or null if hashing fails.
 */
private String hashPassword(final String password, final String salt) {
    try {
        final MessageDigest digest = MessageDigest.getInstance("SHA-256");
        if (salt != null) {
            digest.update(salt.getBytes());
        }
        final byte[] encodedhash = digest.digest(password.getBytes());
        final StringBuilder hexString = new StringBuilder();
        for (byte b : encodedhash) {
            final String hex = Integer.toHexString(0xff & b);
            if (hex.length() == 1) hexString.append('0');
            hexString.append(hex);
        }
        return hexString.toString();
    } catch (NoSuchAlgorithmException e) {
        e.printStackTrace();
        return null;
    }
}