[Database] Create Procedures necessary for interacting with Authors, Followers, Articles, Users, Favorites, and Comments

Afulton11 / blog-database

A CIS 560 Databases project using ASP.net core with an SQL database

2 stars 0 forks source link

[Database] Create Procedures necessary for interacting with Authors, Followers, Articles, Users, Favorites, and Comments #2

Open Afulton11 opened 5 years ago

Afulton11 commented 5 years ago

If there is something you need help with, don't understand, or if you are wondering if you need to do something, just create a comment.

fenrirmitsuki commented 5 years ago

I'm not really understanding what procedures are needed. So far, we have:

Look up the number of articles by a specific author (I could alter this to look up the articles by the given author)(should also be noted that this is looking up by the author's ID number, not username)
Look up all post within a span of time (between x date/time and y date/time)
Look up most recent articles
General template for pagenation (though, realistically, I have to wonder if it's even worthwhile as a template, considering how bare it is).

If I understand what your asking for correctly, are you asking for: procedures to look up:

All articles by a given author
All followers for a given author
All of a given user's favorite articles
All comments for a particular article

Also, I should mention that what I have so far is saved as a txt document in Database/Setup/01 Procedures. I haven't been able to test them with have tables to work with.

Afulton11 commented 5 years ago

yes, so the procedures you wrote in the txt document won't actually work when ran as an sql document as they are not formatted correctly.

From class, we create a procedure by using the following format:

CREATE OR ALTER PROCEDURE Blog.SomeProcedure(
  @SomeVariable SOMETYPE,
  @SomeOtherVariable SOMETYPE,
  ...
)
AS (
  [SELECT|INSERT|UPDATE] *
  [FROM|VALUES] *
)

Also I already have some procedures created in 01_Procedures/Commands and 01_Procedures/Queries if you want to look at some examples.

As for more procedures that we might need (if not already created):

GetTotalPoints
- Counts the total value of all points for a given user (that are not expired)
- Returns the result

Something I might consider adding (in a future group meeting we might discuss this) is returning the total favorites along with a query for an Article. There are a lot of procedures we will need, but we haven't planned out that far ahead yet.

fenrirmitsuki commented 5 years ago

I updated Query tasks yesterday: changed the format of the tasks and added some of the new tasks. Everything look okay?

Afulton11 commented 5 years ago

Yeah commit 03efa10858e3c04bdc10bc600cec021ec8c71148 looks good, just change the file extension to a .sql file and make sure there are no syntax errors showing with server management studio. You should be able to open the file in server management studio without connecting to a database. I will probably setup another meeting for Friday so that we can all meet to discuss possible changes we need to make, our progress we have made, and any other procedures we will need to complete the project.