This project started on 10/13/2013 with the goal of writing a .NET framework that can be used to create Reddit bots.
The database has been designed so that all CRUD operations are done using stored procedures and views. Using views allows the underlying table structure to change without affecting the application. Using stored procedures for CRUD decouples reporting, logging, and several other tasks from the application layer.
For now the schemas are used for organizational purposes and not for security or authentication. The database currently uses four schemas:
|
» |
dbo |
This is the main schema where resultant data should be persisted. Development has just started. |
|
» |
Reporting |
This schema is for our reporting and logging infrastructure. Development is nearly complete. |
|
» |
Queue |
A download/process queue will be part of the application and will use this schema to batch queue pages for downloading and processing. The goal is to have a service application save chunks of raw, unparsed json data to the database and then process/parse that data at a later time. This is being done with the idea that many pages could be queued for download, like the entire site. Downloading could be done constantly while a processing application asynchronously parses the data being saved by the downloading service. |
|
» |
Raw |
This schema represents a single Reddit page. |
|
DBO |
|
|
Author |
Columns represent data collected for individual authors. |
|
Page |
Columns represent data collected for a single page. |
|
Reporting |
|
|
LogInfo |
Table for saving logs. Has foreign key to LogType table |
|
LogType |
Listing of each type of log that can be created. |
|
Errorlog |
Table for saving error logs. Has foreign key to ErrorType table. |
|
ErrorType |
Listing of each type of error log that can be created. |
|
Queue |
|
|
Download |
Table for persisting data that has been downloaded. |
|
Process |
Table for listing current process jobs. Has foreign key to ProcessType. |
|
ProcessType |
Listing of each type of process that can be used. |
|
Raw |
|
|
PostData |
Table that represents the metadata captured inside of a single Reddit post. |
|
MediaEmbed |
Columns represent data collected for a single page. |
|
Media |
|
· Stored procedures interface exclusively with views and not directly with tables.
· Every action taken by a stored procedure is logged inside of the LogInfo table.
· Errors are logged inside of the ErrorLog table.
· Stored procedures that are called to delete rows will not physically delete. Instead the DateDeleted cell is populated with the date and time of when the deletion was called. As views are developed, they will ignore rows that have a value inside the DateDeleted column.
The application is an n-tier design with each layer performing one specific task and that is decoupled from the layers above.
· DATA: Data Layer
This will provide access to the database from the application scope. Because of this limited scope, only EDMX files should exist in this tier.
· DAL: Data Access Layer
This layer is used to access the DATA layer. Data transfer objects (DTOs) such as t4 files and DbContext classes exist in this tier.
· BLL: Business Logic Layer
All methods that require database data should be grouped in this tier.
· Service: Service Layer
This layer is for our service application. The service application’s responsibility is to download json data from Reddit, parse it into a model, and push it out to the database.
· Presentation: Presentation Layer
This layer is our front-end. It will be created using ASP.NET MVC 4 WebAPI. No futher details regarding what will be included on this tier. Some ideas are a Service application config/control page, database action page (for setting the system agent and backing up), and other functionality.