Software: Create a database table for storing videos
Closed this issue · 21 comments
We need to store the following CSV file: https://docs.google.com/spreadsheets/d/14f6uBw0FRok1X4-EnSj-A5HVW1AGEZDWvIRnwCLnOh8/edit#gid=0 in a videos table inside of our relational database.
Create the get route that will allow the frontend to query all the videos.
@superkaiba , please work with Victor to implement this GET route in Flask!
Are we getting the data directly from the google sheets API?
No, @ding-ma will create the connection to our SQLServer which is hosted on Azure. Then, we should create a table in this database for storing information about the videos. More specifically, this table will have columns links, start, end, stressful, keywords, video_id. Once the table is created, we drop the data from the spreadsheet inside of this table.
Once the video table is set up, create a GET route in the backend that returns all the videos to the frontend. @liuyyn will be using this rounte for the video selection page. Let me know if something isn't clear.
In what format should we return the videos to the frontend?
You can return in the following format:
{"data": [{ "start": start, "end": end, "stressful": stressful, "keywords": keywords, "link": link}, ...]}
Basically returning all fields in the video database: https://docs.google.com/spreadsheets/d/14f6uBw0FRok1X4-EnSj-A5HVW1AGEZDWvIRnwCLnOh8/edit#gid=0. What do you think @liuyyn? @ding-ma should be done setting up the database tonight I think, you can store all the data that is on the first spreadsheet to the database in a table named video, then create a GET route that gets everything in this table and returns it to the frontend :)
@superkaiba, In the case where start and end are empty, e.g. we play the entire video, you can put -1 for both to signal that we want to play the entire video instead of a specific sequence.
@mlej8 @superkaiba null is better for the case where start and end are not defined. (-1 is used when you have a non-nullable integer type, like C's int). I would replace link with url. Is stressful a boolean? Maybe call it is_stressful.
@superkaiba, The link stored should have the format https://www.youtube.com/embed/tgbNymZ7vqY where the last string (i.e. tgbNymZ7vqY) is the unique video id. In other words, you should add the string embed between .com/ and the video_id so we won't have to process the link in the frontend. Otherwise, every time we fetch the youtube links, we will have to process the link string.
In this case don't forget to use json.dumps() that will make sure None -> null. Both link and url are fine use whatever you'd like. Stressful is a boolean so yes is_stressful is a better name! :)
@liuyyn That's no longer necessary since I'm going to add react-player to add some features Michael wanted. Any youtube link should work.
@mlej8 No need to use json.dumps(), flask's default JSONEncoder already does this. If you return a dictionary from a route, it's automatically encoded using this encoder.
@cherryman What features do you want to add ?
It's for #22, so we can start collecting data as the video starts and stop collecting once the video ends. We also need to disable the navigation while the video is incomplete, and it'll help with the "restart" function. YouTube's iframe API doesn't mesh well with React.
I think it would be good idea to store the youtube video id. It will always be in the link so retrieving it shouldn't be hard and I will need it to do some work for the frontend. Thanks
I need the server, database, username, password, driver and table name to connect to the SQLServer
The username and password are set as environment variables
@archarbar @superkaiba , the tables for storing the video are created. Please use SQLAlchemy to create python objects from the rows in the CSV file containing the videos (https://docs.google.com/spreadsheets/d/14f6uBw0FRok1X4-EnSj-A5HVW1AGEZDWvIRnwCLnOh8/edit#gid=0 ) and add these objects to the video table. Don't forget to db.session.commit(). Please have a look at this before doing it: https://flask-sqlalchemy.palletsprojects.com/en/2.x/quickstart/#a-minimal-application.
Not the same issue, this is the GET all videos route for the video selection page.