Design database structure and configure connection

[MaximilianGeorgiev]

deadline: 2 weeks

[MaximilianGeorgiev]

Database designed, data model and sql script file to be found in documentation folder

Database implementation: PostgreSQL Project structure: Maven project with Hibernate JPA connection (to be confirmed)

Issues arose with database creation: #8 #9

[MaximilianGeorgiev]

Project structure confirmed, Chen model uploaded, database must be modified. (issue #10 rised)

[MaximilianGeorgiev]

10 resolved

[MaximilianGeorgiev]

with commit #df8780b - created maven project, configured maven dependencies for postgreSQL and hibernate, created a test entity which we are going to use later as well (Role class). Tested and successfully persisted to database.

Created the project's structure with DAO layer and a service layer. A DAO will be a private field inside a Service since DAO handles CRUD operations but a Service may have other functionality besides a database implementation, that's why it's been left open for expansion if needed.

For now - the DAO and the CrudService will handle all entities (with Java generics). This may need to be changed later on in development.

[MaximilianGeorgiev]

Raised issue #12 to implement thorough CRUD functionality

[MaximilianGeorgiev]

Dropped generic DAO design. Using implementation specific DAOs which implement a generic CRUD DAO. Tested all CRUD operations and they work. Will be using Hibernate Query Language where possible. Transactions and queries performed and handled by an EntityManager. Resolved #12

[MaximilianGeorgiev]

DAOs and services for the new classes added. Added a function for prepopulating some databases which do not depend on the user's actions. Awaiting the User, Trip, Ticket tables so I can map the foreign key relations.

[MaximilianGeorgiev]

All classes (except Ticket) have been ORM mapped, tested, added DAOs and service for the other ones as well. The User table has been modified to Users (script.sql updated) since User is a keyword both in Hibernate and in PostgreSQL and it's too big of a hassle. Issue arose: #15 , #16

[MaximilianGeorgiev]

Major ORM rework, all relations should work now but they are to be tested. Added temporary stable version so my colleague can work. Resolved #16 . Raised #17 . ORM issue will remain open until everything is thoroughly tested.

[MaximilianGeorgiev]

Issue #17 resolved, appropriate cascades have been assigned, fetch types will be the default ones unless a change needs to be done. GetByName previously returned List in case there are entities with the same name but it is not possible to have entities with the same name that's why the return type is now Entity.

[MaximilianGeorgiev]

Changes: PurchaseRestriction is no longer a table. The maximum amount of tickets a user can buy will just be an int field in the Trip table. Added the hour of departure, duration, max tickets.

It seems that hibernate created a new session for every DAO which slowed down the application big time. This has been fixed by using a global session. There were a few @OneToMany relations that were missing their mappedBy attribute and hibernate was looking for non-existing fields that's why I explicitly named the fields it should work for. The same goes for the table names. PostgreSQL merges all names to lower case and if we want to keep the current camel case convention it should be explicitly said in the @Table, by using inverted commas and escaping the commas. Added the schema as well so it is maximumly explicit.

Added the new script, issue raised: #18

[MaximilianGeorgiev]

With the latest commit: Removed PurchaseRestriction from the script, renamed joined tables as such: UsersTrip for example. When trying to insert into a joined table with a many to many relationship hibernate looks for the user_id in User table (which doesn't exist and we use Users). There was no way to explicitly map the table name that's why all the tables were renamed.

Fixed the queries - it seems that with hibernate query language and join selects, the entity field is used not the table column.

[MaximilianGeorgiev]

All DAOs now return optional: it is easier to handle null pointer exceptions like this.

[MaximilianGeorgiev]

When an Optional is to be returned, instead of returning .getSingleResult(), the sequence is: .getResultList().getFirst().orElse(null) because .getSingleResult() throws an exception if the value is null and it eliminates the whole purpose of Optionals and the way we handle null checks. We want null to be returnable from the DAO.

[MaximilianGeorgiev]

Routes extracted in a separate table and a trip is created with a fixed route beforehand. There are middle points (attachments) - a new RouteAttachment joined table was created as well. Added a foreign key to the trip called cashier_id so we know which cashier services which trips. Initially it is null and the distributor assigns a cashier for an existing trip.

[MaximilianGeorgiev]

Previously, the customer was buying tickets from either the start or end point of the route. And simply in the 'My tickets' ticket we used to display the location of the customer till the end point. But this is not correct.

With the new script changes:

• The ticket will have 2 location foreign keys (where the ticket is bought and where it leads to). This allows the customer to buy tickets from middle points for example: If the route is Sofia - Varna and there are bus stops in Veliko Tarnovo and Shumen the customer can buy Veliko Tarnovo - Shumen. This was previously not possible.

• Two new tables will be added: "CompanyCashier" and "TripCashier". A company will have multiple cashiers. For every location there will be AT LEAST one cashier. But there will be cashiers for every single location of a CURRENT trip as well. Once the trip is created, a distributor must assign cashiers (from the company's cashiers) to this particular trip. When a ticket is being bought, if the customer is searching for a departure location where no cashier is selected, no result will be displayed. Purchases from middle points are only valid from locations where a cashier is selected for the trip.