SQL-request generator for particular database of Greek myths lore
Table of Contents
This project is a system management user interface based on the lore of Hades' Greek hell.
This is an example of how to list things you need to use the software and how to install them.
- Open IntelliJ Idea
- Click on "Get from VCS" button
- Enter "https://github.com/Mimmey/SqlGenerator.git" in the URL field, choose cloning path and click "Clone"
- Run project You've just generated dmlScript.sql.
- Run PostgreSQL
- Run ddlCreation.sql in PostgreSQL
- Run dmlScript.sql
- Enjoy
- You can run indexEffectivityExplaining.sql if you want to create indices and look at their effectivity.
- Also you can delete all DB with ddlDeletion.sql
The database is a system by which the Greek god Hades can control the afterlife. The following entities are defined in our database:
Souls, monsters and users are persons.
Souls enter hell, each of which has a certain list of events that happen to this soul during life. The occurrence of events related to judgment and combination with multiple types of sins. According to the highest standards of the types of sins. If it so happens that the torture is removed, i.e. the entry in the torture id field in the sin_type table is null, or the torture does not have an executing monster, i.e. the monster_id field has a torture value of zero, i.e. the state of torture that will follow after the severity of the disease has occurred in her life. Sin, which suffered in the end by an accident, we will call observable. If the identified sin is not found, then the soul remains undistributed and requires an exclusive decree. This algorithm is represented as a distribution_of_soul_by_algo(soul_identifier) The date of death of the soul must be greater than the date of birth, and the age of death must not exceed 100 years.
Users process complaints, correlate entries from the list of events and types of sins, assign specific tortures to sin types, and also attribute each torture to the executing monster. Users can be authorized, while only one user can be authorized in the system (in this case, it is impossible to authorize as “DELETED”, “UNAUTHORIZED” and “AUTO”). When interacting with the database, the handler_id in the relevant tables is automatically set according to the user who is currently authorized (or the action is marked as “UNAUTHORIZED” (handler_id = 3)). If a user is deleted from the system, they are marked as DELETED (id = 1) in all records. There is also a default handler AUTO (id = 2). Its id = 2 is set to the handler_id field of a soul when torture is attributed to it according to the weight of sin algorithm. Otherwise, there is the id of the handler that attributed the torture to the soul by an exceptional decree.
Underworld level. According to the canons of the main levels, there are three: Tartarus, Asphodel Meadow and Elysium
Sublocation of the level of the underworld.
After determining the torture, the soul is placed in the location in which the monster performing the assigned torture is located. As soon as the monster is transferred from one location to another, all the souls that are attached to it through torture follow it. The monster can only be found in Tartarus locations.
For each type of sin, the user assigns torture. The torture has a name, the id of the executing monster, and the id of the user who last assigned the monster to execute this torture.
Each event is associated with a certain type of sin. Each type of sin has the id field of the user who entered this type of sin into the system, the weight of the sin type (from 0 to 1), and the id field of the user who last assigned torture to this type of sin.
Statuses are the states of complaints and events. When initializing the database, the main status values are ”Not Processed”, “Approved”, “Rejected”
Each soul can apply for a transfer to work (instead of torture, the soul can choose to work). The complaint is considered and satisfied / not satisfied. If, based on the results of its consideration, a decision is made to transfer the soul to work, the is_distributed field of this soul is set to false, after which the soul awaits distribution to work.
Events - records of key incidents in the life of souls. Each event has a description, the id of the soul that played a key role in the event (if there were several such souls, a separate entry is created for each), the date of the event (required between the dates of birth and death of the soul), as well as the status and id of the user who correlated the event and some types of sins
Each soul can file a complaint about a transfer to work. If the complaint is satisfied, then the soul ceases to be tortured and begins to work. Work can only take place outside Tartarus.
For each event, a certain number of types of sins are determined, which, according to the handler user, took place in this event. All pairs of type "event - type of sin" are entered in the corresponding list. After an event is added to the list, it automatically becomes “processed”, and when all records corresponding to the event are deleted, it goes back to the unprocessed state.
If no job has been assigned to the soul yet, but the job transfer request has been approved, the user must manually transfer the soul and add the soul-job pair to the appropriate list. After the pair is added to the list, the soul automatically becomes “allocated” and “working”, and when the entry corresponding to the soul is deleted, it goes back to the unallocated state.
You can also see a datalogical model of DB:
You can control Hades' hell with common SQL selects and inserts. Firstly, you have to register:
INSERT INTO person (_name) VALUES ('NON-HANDLED');
INSERT INTO _user (person_id) VALUES (integer person_id);
Authorize as a user:
interface_authorize(integer id);For all main interactable entities there is an opportunity of saving id of last interacting user in field handler_id. About another ways of usage you can read in the Entities description
- Fork the Project
- Create your Feature Branch (
git checkout -b feature/AmazingFeature) - Commit your Changes (
git commit -m 'Add some AmazingFeature') - Push to the Branch (
git push origin feature/AmazingFeature) - Open a Pull Request
Olga Motyleva - motyolya@gmail.com
Project Link: https://github.com/Mimmey/SqlGenerator
README.md made using: https://github.com/othneildrew/Best-README-Template