github.com/grafviktor/keep-my-secret@v0.9.10-0.20230908165355-19f35cce90e5/internal/storage/sql/sql_statements.go (about)

     1  package sql
     2  
     3  const sqlCreateUserTable = `
     4  CREATE TABLE IF NOT EXISTS user (
     5  	id INTEGER PRIMARY KEY AUTOINCREMENT,
     6  	login VARCHAR(100) UNIQUE,
     7  	password TEXT NOT NULL,
     8  	restore_password TEXT,
     9  	data_key TEXT
    10  );
    11  `
    12  
    13  const sqlCreateSecretTable = `
    14  CREATE TABLE IF NOT EXISTS secret (
    15  	id INTEGER PRIMARY KEY AUTOINCREMENT,
    16  	secret_type VARCHAR(10),   -- card, file, pass, note (left non-normalized)
    17  	title TEXT,
    18  	login TEXT,
    19  	password TEXT,
    20  	note TEXT,
    21  	file_name TEXT,
    22      file BINARY,
    23  	cardholder_name TEXT,
    24  	card_number TEXT,
    25  	expiration TEXT,
    26  	cvv TEXT,
    27  	user_id BIGINT,
    28  	CONSTRAINT fk_secret_user_id FOREIGN KEY(user_id)
    29  		REFERENCES users(id)
    30  		ON DELETE CASCADE
    31  );
    32  `
    33  
    34  var sqlInsertUser = `
    35  INSERT INTO user
    36  		(login, password, restore_password, data_key)
    37  	VALUES
    38  		($1, $2, $3, $4)
    39  	RETURNING id;
    40  `
    41  
    42  var sqlSelectUser = `
    43  SELECT id, login, password, restore_password, data_key FROM user WHERE login = $1;
    44  `
    45  
    46  var sqlInsertSecret = `
    47  INSERT INTO secret (
    48  		secret_type, -- card, file, pass, note (left non-normalized)
    49  		title,
    50  		login,
    51  		password,
    52  		note,
    53          file,
    54  		file_name,
    55  		cardholder_name,
    56  		card_number,
    57  		expiration,
    58  		cvv,
    59  		user_id
    60  	)
    61  	VALUES
    62  		($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, (SELECT id FROM user WHERE login = $12))
    63  	RETURNING id;
    64  `
    65  
    66  var sqlUpdateSecret = `
    67  UPDATE secret SET
    68  		secret_type = $1,
    69  		title = $2,
    70  		login = $3,
    71  		password = $4,
    72  		note = $5,
    73  		file_name = $6,
    74  		cardholder_name = $7,
    75  		card_number = $8,
    76  		expiration = $9,
    77  		cvv = $10
    78  	WHERE id = $11
    79  	AND user_id = (SELECT id FROM user WHERE login = $12);
    80  `
    81  
    82  var sqlGetSecretByID = `
    83  SELECT
    84      id,
    85      secret_type,
    86  	title,
    87  	login,
    88  	password,
    89  	note,
    90  	file,
    91  	file_name,
    92  	cardholder_name,
    93  	card_number,
    94  	expiration,
    95  	cvv
    96  FROM secret
    97  		WHERE id = $1
    98  		  AND user_id = (
    99  	SELECT id FROM user WHERE login = $2
   100  );
   101  `
   102  
   103  var sqlFindSecretsByUser = `
   104  SELECT
   105      id,
   106      secret_type,
   107  	title,
   108  	login,
   109  	password,
   110  	note,
   111  	file,
   112  	file_name,
   113  	cardholder_name,
   114  	card_number,
   115  	expiration,
   116  	cvv
   117  FROM secret
   118  	WHERE user_id = (
   119  		SELECT id FROM user WHERE login = $1
   120  );
   121  `
   122  
   123  var sqlDeleteSecret = `
   124  DELETE FROM secret
   125  WHERE
   126      id = $1
   127  AND
   128      user_id = (
   129          SELECT id FROM user WHERE login = $2
   130      );
   131  `