Skip to content

v1

[!IMPORTANT] Previous database was not migrated, but a new database was created from ground up...

So, you won't find any migration sql here.

  • Used by: v1.0.0 => latest
  • Purpose: Initial Schema For Application

Entire Database Schema (v1)

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 NOT NULL notes that you wanna take for that record. more like be some information about account on that platform
created_at Text DEFAULT CURRENT_TIMESTAMP --
updated_at Text DEFAULT CURRENT_TIMESTAMP --

Setup SQL (v1)

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 NOT NULL, 
    `created_at` TEXT DEFAULT CURRENT_TIMESTAMP, 
    `updated_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

COMMIT;

Migration SQL (v0 -> v1)

-- Migration is not avaliable. As Database was recreated, but you still use below sql to migrated.
-- It is destructive migration, It destory history for passwords completely. (createdat & updateat data will lost forever).

/*
BEGIN TRANSACTION;

UPDATE `passwords` SET `notes` = '' WHERE `notes` IS NULL;

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 NOT NULL, 
    `created_at` TEXT DEFAULT CURRENT_TIMESTAMP, 
    `updated_at` TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO `new_table_passwords` (`domain`, `username`, `passwords`, `notes`)
    SELECT `domain`, `username`, `password`, `notes` FROM `passwords`;

DROP TABLE `passwords`;

ALTER TABLE `new_table_passwords` RENAME TO `passwords`;

COMMIT;
*/

Revert SQL (v0 <- v1)

BEGIN TRANSACTION;

DROP TABLE `passwords`;

COMMIT;

Changes Made

IMPORTANT:- Changes were not made to previous database schema. But, an entirely a new database schema was builded from ground up..

You still will see similarity with previous schema (v0) as this schema (v1) is builded inspired by that schema.

Key Changes:-

  • Now, all field are TEXT instead of VARCHAR & DATE
  • notes field is now, not nullable from v1. To stay consistent with other fields.
  • createdat & updatedat field is now created_at & updated_at respectively.