Skip to content

v2

  • Used by: v2.0.0 => latest
  • Purpose: Add URL features to appilcation.

Entire Database Schema (v2)

Tables

Tables Description
passwords For storing password entity.

Passwords Table (passwords)

Fields Property Constraints Description
id Integer PRIMARY KEY, AUTOINCREMENT --
domain Text NOT NULL domain/platform name to which password enitity is associated with.
username Text NOT NULL username on that domain / platform. email can be even used as a value.
password Text NOT NULL password for that specfic username on that specfic domain / platform.
notes Text --- notes that you wanna take for that record. more like be some information about account on that platform
url Text --- link to a platform/domain/website.. can be a application package name, uri, https:// url.
created_at Text DEFAULT CURRENT_TIMESTAMP --
updated_at Text DEFAULT CURRENT_TIMESTAMP --

Setup SQL (v2)

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS `passwords` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `domain` TEXT NOT NULL,
    `username` TEXT NOT NULL,
    `password` TEXT NOT NULL,
    `notes` TEXT,
    `url` TEXT,
    `created_at` TEXT DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TEXT DEFAULT CURRENT_TIMESTAMP,
);

COMMIT;

Migration SQL (v1 -> v2)

BEGIN TRANSACTION;

-- Step 1: Create new table with v2 schema
CREATE TABLE IF NOT EXISTS `new_table_passwords` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `domain` TEXT NOT NULL,
    `username` TEXT NOT NULL,
    `password` TEXT NOT NULL,
    `notes` TEXT,
    `url` TEXT,
    `created_at` TEXT DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Copy existing data from old table
INSERT INTO `new_table_passwords`
    (`id`, `domain`, `username`, `password`, `notes`, `created_at`, `updated_at`)
SELECT
    `id`,
    `domain`,
    `username`,
    `password`,
    `notes`,
    `created_at`,
    `updated_at`
FROM `passwords`;

-- Step 3: Convert empty notes ('') to NULL
UPDATE `new_table_passwords`
SET `notes` = NULL
WHERE `notes` = '';

-- Step 4: Generate URL based on domain
UPDATE `new_table_passwords`
SET `url` = 'https://local.' || `domain`
WHERE `url` IS NULL;

-- Step 5: Remove old (v1) table
DROP TABLE `passwords`;

-- Step 6: Rename new table to original name
ALTER TABLE `new_table_passwords` RENAME TO `passwords`;

COMMIT;

Revert SQL (v1 <- v2)

BEGIN TRANSACTION;

-- Step 1: Create new table with v1 schema
CREATE TABLE IF NOT EXISTS `old_table_passwords` (
    `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `domain` TEXT NOT NULL,
    `username` TEXT NOT NULL,
    `password` TEXT NOT NULL,
    `notes` TEXT NOT NULL,
    `created_at` TEXT DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Copy existing data from v2 table & Make null values in `notes` as empty strings.
INSERT INTO `old_table_passwords`
    (`id`, `domain`, `username`, `password`, `notes`, `created_at`, `updated_at`)
SELECT
    `id`, `domain`, `username`, `password`,
    COALESCE(`notes`, ''),
    `created_at`, `updated_at`
FROM `passwords`;

-- Step 3: Remove old (v2) table
DROP TABLE `passwords`;

-- Step 4: Rename new table to original name
ALTER TABLE `old_table_passwords` RENAME TO `passwords`;

COMMIT;

Changes Made

The Url field is introduce in v2 will allow users to store url & kind of have a link to platform/website/app, whatever.

This will make the passcodes app & the platform (password entity was of) more integrated & connected together.

Key Changes:-

  • notes field is now nullable.
  • A new url field has been introduced for storing platform links.
  • url field can store:
    • https:// website links
    • Android application package names
    • generic URIs