Create user DDL

[Wilsonator123]

• [x] Create the ddl for a user account
• [x] Create the ddl for passwords
• [x] Some form of linker table idk

[Wilsonator123]

https://knexjs.org/

[Sleepy-Git-User]

I may be mistaken but it doesnt appear the ddl I provided to you on the 15/03/2023 has been added in to the main branch or any other branch that I could find @Wilsonator123 . I've attempted to add it myself but it doesnt appear to be executed as I was previously informed. Perhaps the ddl is structured differently to BetterSQL, or maybe the old database needs to be fully removed to add new things.

Which ever the case is. If you can goto either main or LoginFunction branch and add it, that would be grand.

Here is another copy if youve miss placed the previous one:

CREATE TABLE IF NOT EXISTS User ( UserID VARCHAR(128) NOT NULL UNIQUE PRIMARY KEY, Email VARCHAR(320) NOT NULL UNIQUE, Fname TEXT NOT NULL, Lname TEXT NOT NULL, DoB TEXT NOT NULL, Email_Confirmed BIT DEFAULT 0 NOT NULL, Account_Status BIT DEFAULT 0 NOT NULL, );

CREATE TABLE IF NOT EXISTS Password ( UserID VARCHAR(128) PRIMARY KEY, Password VARCHAR(64) NOT NULL, Salt VARCHAR(64) NOT NULL, FOREIGN KEY (UserID) REFERENCES User(UserID) ON DELETE CASCADE );

Many thank you. This is a MAJOR blocker at the moment.

EDIT// Ive updated the ddl to better fit postgres standards but still no success. Ive also deleted the database in docker and regenerated it.

[Wilsonator123]

@Sleepy-Git-User I'm sure you utilised your endless resources to figure out the problem First you have a trailing ',' Second User is a keyword meaning that it may require "User" Third as the documentation suggests 0 is not a bit, but an integer which leads to a type error I have fixed these and create a pull request Please let me know if you struggle again. In future I suggest running the sql file as it will show the errors or consulting google

[Sleepy-Git-User]

None of these errors appear in either my node terminal or server terminal or database terminal. I have done some research and have been informed in production the standard way is to use the PGadmin query tool to add tables rather than doing it programmatically. So using this query I have added the needed tables to the database,

SET search_path TO public;

CREATE TABLE IF NOT EXISTS "User" ( UserID VARCHAR(128) NOT NULL UNIQUE PRIMARY KEY, Email VARCHAR(320) NOT NULL UNIQUE, Fname TEXT NOT NULL, Lname TEXT NOT NULL, DoB DATE NOT NULL, Email_Confirmed BOOLEAN DEFAULT FALSE NOT NULL, Account_Status BOOLEAN DEFAULT FALSE NOT NULL );

CREATE TABLE IF NOT EXISTS "Password" ( UserID VARCHAR(128) PRIMARY KEY, Password VARCHAR(64) NOT NULL, Salt VARCHAR(64) NOT NULL, FOREIGN KEY (UserID) REFERENCES "User" (UserID) ON DELETE CASCADE );

Please enjoy

[Sleepy-Git-User]

Following on from this @Wilsonator123 have you tested and seen if the table is generated. As I am unable to replicate these results.

[Wilsonator123]

One thing you can test is using

docker-compose down -v

When the database launches sometime it doesnt rebuild itself and as such we can use this. I guess in the future we can directly add tables through terminal / pgadmin

You can also check whether the tables already exist

[Wilsonator123]

@Sleepy-Git-User

[Sleepy-Git-User]

Update: Edited the size of the password char value for salt and password to 128