sql_constant.go

package repository

const (
	createAppVersionTable = `
		CREATE TABLE IF NOT EXISTS APP_VERSION (
			VERSION TEXT PRIMARY KEY,
			CREATED_AT TIMESTAMP NOT NULL DEFAULT(datetime(CURRENT_TIMESTAMP, 'localtime'))
		)
	`
	InsertAppVersion = `
		INSERT INTO APP_VERSION (VERSION) VALUES (:version)
	`
	GetLatestAppVersion = `
		SELECT
			VERSION
		FROM (
			SELECT
				VERSION
			FROM
				APP_VERSION
			ORDER BY CREATED_AT DESC
		) LIMIT 1
	`
	CountVersions = `
		SELECT
			COUNT(*) AS COUNT
		FROM
			APP_VERSION
	`
)

const (
	createMasterUserTable = `
		CREATE TABLE IF NOT EXISTS MASTER_USER (
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			USERNAME TEXT NOT NULL UNIQUE,
			USER_PASSWORD TEXT NOT NULL,
			CREATED_AT TIMESTAMP NOT NULL DEFAULT(datetime(CURRENT_TIMESTAMP, 'localtime')),
			UPDATED_AT TIMESTAMP NOT NULL DEFAULT(datetime(CURRENT_TIMESTAMP, 'localtime'))
		)
	`
	InsertMasterUser = `
		INSERT INTO MASTER_USER (USERNAME, USER_PASSWORD) VALUES (:userName, :userPassword)
	`
	GetMasterUserByUserName = `
		SELECT
			ID,
			USERNAME,
			USER_PASSWORD,
			CREATED_AT,
			UPDATED_AT
		FROM
			MASTER_USER
		WHERE 1=1
			AND USERNAME = :userName
	`
	GetMasterUserById = `
		SELECT
			ID,
			USERNAME,
			USER_PASSWORD,
			CREATED_AT,
			UPDATED_AT
		FROM
			MASTER_USER
		WHERE 1=1
			AND ID = :id
	`
	GetAllUsers = `
		SELECT
			ID,
			USERNAME,
			USER_PASSWORD,
			CREATED_AT,
			UPDATED_AT
		FROM
			MASTER_USER
		WHERE 1=1
	`
	DropUserById = `
		DELETE FROM MASTER_USER
		WHERE 1=1
			AND ID = :id
	`
)

const (
	createPasswordListTable = `
		CREATE TABLE IF NOT EXISTS PASSWORD_LIST(
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			USER_PK INTEGER NOT NULL,
			KEY_VALUE TEXT NOT NULL,
			PASSWORD TEXT NOT NULL,
			CREATED_AT TIMESTAMP NOT NULL DEFAULT(datetime(CURRENT_TIMESTAMP, 'localtime')),
			UPDATED_AT TIMESTAMP NOT NULL DEFAULT(datetime(CURRENT_TIMESTAMP, 'localtime'))
		)
	`
	InsertPassword = `
		INSERT INTO PASSWORD_LIST (USER_PK, KEY_VALUE, PASSWORD) VALUES (:userPk, :key, :password)
	`
	GetPasswordByUserPkAndKey = `
		SELECT
			ID,
			USER_PK,
			KEY_VALUE,
			PASSWORD,
			CREATED_AT,
			UPDATED_AT
		FROM
			PASSWORD_LIST
		WHERE 1=1
			AND USER_PK = :userPk
			AND KEY_VALUE = :key
	`
	GetAllPasswords = `
		SELECT
			ID,
			USER_PK,
			KEY_VALUE,
			PASSWORD,
			CREATED_AT,
			UPDATED_AT
		FROM
			PASSWORD_LIST
		WHERE 1=1
			AND USER_PK = :userPk
	`
	UpdatePasswordByUserPkAndKey = `
		UPDATE
			PASSWORD_LIST
		SET
			PASSWORD = :password
			AND
			UPDATED_AT = datetime(CURRENT_TIMESTAMP, 'localtime')
		WHERE 1=1
			AND USER_PK = :userPk
			AND KEY_VALUE = :key 
	`
)

const (
	createPasswordListDetailTable = `
		CREATE TABLE IF NOT EXISTS PASSWORD_LIST_DETAIL (
			ID INTEGER PRIMARY KEY AUTOINCREMENT,
			PASSWORD_LIST_KEY INTEGER NOT NULL,
			DESCRIPTION TEXT NOT NULL
		)
	`
	InsertDetailByPasswordListKey = `
		INSERT INTO PASSWORD_LIST_DETAIL (PASSWORD_LIST_KEY, DESCRIPTION) VALUES (:passwordListKey, :description)
	`
)