0

TL;DR Music Streaming app - allows users to upload their own files. Files will be stored in a FileSet. I want to pass in more than one FILE(array of fileRecords), with one FILESET, and insert into the appropriate tables.

Sorry if this is a little confusing - basically I am working with 3 tables:

File table

CREATE TABLE file
(
    -- Most tables should have a table_id & table_cuid
    file_id         BIGSERIAL   NOT NULL ,
    file_cuid       VARCHAR     NOT NULL,

    -- This section is specific to this table
    user_id         BIGINT      NOT NULL,

    -- File fields
    filename        VARCHAR     NOT NULL,
    last_modified   TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    size_in_bytes   VARCHAR     NOT NULL,
    mime_type       VARCHAR     NOT NULL,

    -- Most tables should also have timestamps
    created_timestamp  TIMESTAMP WITHOUT TIME ZONE  NOT NULL,
    updated_timestamp  TIMESTAMP WITHOUT TIME ZONE  NOT NULL,

    -- Constraints
    PRIMARY KEY (file_id),
    FOREIGN KEY (user_id)   REFERENCES users (user_id),
    CONSTRAINT file_cuid_unique  UNIQUE     (file_cuid)
)

FileSet Table

CREATE TABLE fileset
(
    -- Most tables should have a table_id & table_cuid
    fileset_id         BIGSERIAL   NOT NULL ,
    fileset_cuid       VARCHAR     NOT NULL,

    user_id         BIGINT      NOT NULL,

    -- Fileset fields TBD

    created_timestamp  TIMESTAMP WITHOUT TIME ZONE  NOT NULL,
    updated_timestamp  TIMESTAMP WITHOUT TIME ZONE  NOT NULL,

    -- Constraints
    PRIMARY KEY (fileset_id),
    FOREIGN KEY (user_id)   REFERENCES users (user_id),
    CONSTRAINT cuid_unique_fileset  UNIQUE     (fileset_cuid)
)

and finally a File_FileSet_Rel Table

CREATE TABLE file_fileset_rel
(
    file_id         BIGSERIAL   NOT NULL,
    fileset_id      BIGSERIAL   NOT NULL,

    FOREIGN KEY (file_id)  REFERENCES file   (file_id),
    FOREIGN KEY (fileset_id)   REFERENCES fileset (fileset_id),
    UNIQUE      (file_id, fileset_id)
)

Every song will have one FileSet associated with it, and that fileset has one/multiple files associated.

The function below (file_upload_process) allows me to add the File to the table (returning file_id), as well as adding the Fileset to the table (returning fileset_id), and finally the function inserts both of these values into the final table file_fileset_rel

file_upload_process.sql

CREATE OR REPLACE FUNCTION file_upload_process(
  _file_cuid VARCHAR,
  _fileset_cuid VARCHAR,
  _cognito_id VARCHAR,
  _name VARCHAR,
  _last_modified TIMESTAMP,
  _size DECIMAL,
  _type VARCHAR
)
RETURNS TABLE(
  fileId BIGINT,
  fileSetID BIGINT
)
AS $file_upload_process$
DECLARE
v_user_id BIGINT;
v_file_id BIGINT;
v_fileset_id BIGINT;
BEGIN

-- Create Song/ Update song
  -- store user_id
  v_user_id := (
    SELECT * FROM get_user_id(_cognito_id)
  );

  -- --Insert In FILE table
  v_file_id := (
    SELECT *
    FROM create_file(
    _file_cuid,
    v_user_id,
    _name,
    _last_modified,
    _size,
    _type
    )
  );

  --Insert into fileset table
  v_fileset_id := (
  SELECT * FROM create_fileset(_fileset_cuid, v_user_id)
  );

  RETURN QUERY
  INSERT INTO file_fileset_rel(file_id, fileset_id)
  VALUES (
    v_file_id,
    v_fileset_id
  )
  RETURNING file_fileset_rel.file_id,
            file_fileset_rel.fileset_id;
END;
$file_upload_process$ LANGUAGE PLPGSQL;

Which calls the following functions within:

create_file.sql

CREATE OR REPLACE FUNCTION create_file(
  _file_cuid VARCHAR,
  _user_id BIGINT,
  _name VARCHAR,
  _last_modified TIMESTAMP,
  _size DECIMAL,
  _type VARCHAR
)
RETURNS TABLE (
  _file_id BIGINT
)
AS $create_file$
BEGIN
  RETURN QUERY
-- Create Song/ Update song
  INSERT INTO file(
    file_cuid,
    user_id,
    filename,
    last_modified,
    size_in_bytes,
    mime_type,
    created_timestamp,
    updated_timestamp
  )
  VALUES(
    _file_cuid,
    _user_id,
    _name,
    _last_modified,
    _size,
    _type,
    now(),
    now()
  )
  RETURNING "file".file_id;
END;
$create_file$ LANGUAGE PLPGSQL;

And the create_fileset function:

    CREATE OR REPLACE FUNCTION create_fileset(
  _fileset_cuid VARCHAR,
  _user_id BIGINT
)
RETURNS TABLE (
  _fileset_id BIGINT
)
AS $create_fileset$
BEGIN
-- Create Song/ Update song
  RETURN QUERY
  INSERT INTO fileset(
    fileset_cuid,
    user_id,
    created_timestamp,
    updated_timestamp
  )
  VALUES(
    _fileset_cuid,
    _user_id,
    now(),
    now()
  )
  RETURNING "fileset".fileset_id;
END;
$create_fileset$ LANGUAGE PLPGSQL;

The issue I'm having is that this function only allows you to add 1 file at a time, users could upload 5-10 files for 1 song (1 fileset) and so I need a way to loop through the array of fileRecords and store the values of those fields in the FILE table columns - finally allowing me to add multiple records to the file_fileset_rel table.

If anyone has easier ways to do all of this, or more importantly a solution to the MANY vs One insert would be a huge help.

EDIT: Example of CURRENT body being sent to lambda - before passing to postgresql function URL -> fileset/{filesetCuid}/file

body: {
"cognitoId" : "9dc766a0-c5c9-4455-932e-46c243c80266",
"fileRecord" : {
    "fileCuid" : "someotherfilecuid23",
    "name" : "file_name",
    "last_modified" : "06/03/1995",
    "size" : 530,
    "type" : "mp3/audio"
}
}

End goal - send multiple files: URL -> fileset/{filesetCuid}/file

body: {
"cognitoId" : "9dc766a0-c5c9-4455-932e-46c243c80266",
"fileRecords" : [{
    "fileCuid" : "someotherfilecuid23",
    "name" : "file_name",
    "last_modified" : "06/03/1995",
    "size" : 530,
    "type" : "mp3/audio"
}, {...}, {...}]
}
6
  • Why the mysql tag? Do you need a solution for both DBMS products? Commented Sep 27, 2019 at 18:41
  • I figured it may not be a language-specific issue, or rather, that the solution in mysql would shed some light on how I might do it in postgresql Commented Sep 27, 2019 at 18:53
  • It would be helpful to see the structure of the json you are starting with. Commented Sep 27, 2019 at 19:20
  • Apologies - editted to reflect. Commented Sep 27, 2019 at 20:24
  • Every song will have one FileSet associated with it, and that fileset has one/multiple files associated. Not sure why you have this linking table in the first place since you apparently don't need a ManyToMany relation. Seems you could simply use a file.fileset_id that would be declared as FOREIGN KEY (fileset_id) REFERENCES fileset (fileset_id). (And why not simply call the primary keys id, as usual?) Commented Sep 27, 2019 at 23:46

1 Answer 1

1

There is an elegant solution you can adapt from this PostgREST issue. You could also use a LOOP in your file_upload_process.sql that wraps all your function calls.

The former is better because, it's a nice all-in-one upsert logic:

  • it relies on the target table's definition (e.g. DEFAULT values are set on the fly)
  • you can directly pass a JSON array (i.e. you may omit values that are not required)
  • you can handle conflicts on your UNIQUE constraints (and retrieve the existing row's id automatically!)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.