Skip to content

Latest commit

 

History

History
166 lines (116 loc) · 5.42 KB

README.md

File metadata and controls

166 lines (116 loc) · 5.42 KB

Solita Citybike application

Citybike webapp documentation

How to start Azure SQL in cloud

  1. Create your Azure account
  2. Short tutorial: https://www.youtube.com/watch?v=ov27ukpWgdQ
  3. Create resource -> SQL Database + Create -> fill detailed information (From configure you found cheaper options for compute tier) and finally create
  4. After that install Azure Datastudio and make connection to database, here is documentation: https://learn.microsoft.com/en-us/sql/azure-data-studio/quickstart-sql-database?view=sql-server-ver16

Creating Station Table

  1. Importing data, choose INT value for FID, ID and Kapasiteet (all other string(nvarchar(50)), lets change x and y to decimal after import). - Choose CSV file - Give tablename: Station

  2. In CSV file geolocation data is string and we have to modified x and y columns to decimal -> SQL:

alter table dbo.Station alter column x decimal (8,6); alter table dbo.Station alter column y decimal (9,6);

Choose from right-click over Station table (Design) then open new window, choose ID and right is Column Properties, choose Identity Specification. Is identity checked, identity seed: 1, identity increment: 1.

Now we have created database and importet data from CSV to Station table.

19.6.2023:
Updated SQL to receive also picture link (Kuva column). SQL: ALTER TABLE STATION ALTER COLUMN Kuva NVARCHAR(MAX);

Creating Citybitrips Table

  1. Importing data, choose DATE for Departure and Return choose INT for Departure/Return_station_id and duration_sec choose NVARCHAR(50) rest. Table name: Citybiketrips

After that change to decimal Covered_distance_m.

SQL: alter table Citybiketrips alter column Covered_distance_m decimal;

  1. Modify more data here is some examples: https://github.com/gittjar/SQL/blob/main/citybiketrips.sql

Now we have created Citybiketrips table and imported data from CSV.

Create user for database tables

USE master;
CREATE LOGIN UserName1 WITH password='VaikeaSalasana2';
GO
USE YourDabaseName;
CREATE USER UserName1 from LOGIN UserName1;
GO
EXEC sp_addrolemember 'db_datawriter', 'UserName1';
GO
ALTER ROLE db_datawriter ADD MEMBER UserName1;
ALTER ROLE db_datareader ADD MEMBER UserName1;

Connect this user to ConnectionString in API and MVC.

More information: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver16

Create API for frontend

  1. Microsoft documentation: https://learn.microsoft.com/en-us/aspnet/core/tutorials/first-web-api?view=aspnetcore-7.0&tabs=visual-studio-mac

Reposito to Citybike API: https://github.com/gittjar/CitybikeApi

  • "Database first"
  • Connect API to SQL server
  • Create different Models for Database tables
  • Used in this exercise only GET methods for create this API layer
  • Serverside pagination
  • Use in ConnectionString before created SQL user

See running API in Cloud and endpoints.

All stations: https://citybikeapi.azurewebsites.net/api/Stations

Stations by ID: https://citybikeapi.azurewebsites.net/api/Stations/23

Stations by name: https://citybikeapi.azurewebsites.net/api/StationsByName/haaga

CitybiketripsMay2021 server pagination: https://citybikeapi.azurewebsites.net/api/CitybikeTripsMay2021?pageNumber=1&pageSize=500

All citybiketrips: https://citybikeapi.azurewebsites.net/api/CitybikeTrips

Citybike App frontend

  1. This project used Angular 16 in frontend. Angular documentation: https://angular.io

Frontend reposito: https://github.com/gittjar/CitybikeFrontend Cloud: https://solitacitybike.azurewebsites.net

Features

  • Show all stations and detailed information on Google Map
  • Detailed station information show TOP-5 return stations and average distance from station
  • Show TOP-10 Departure stations and TOP-10 Return stations
  • Different sorts for biketrips and free text search

MVCCitybike (CRUD)

  1. Microsoft documentation: https://learn.microsoft.com/en-us/aspnet/core/tutorials/first-mvc-app/start-mvc?view=aspnetcore-7.0&tabs=visual-studio-mac

Reposito to MVCCitybike: https://github.com/gittjar/MVCCitybike

Cloud: https://citybikemvc.azurewebsites.net/

Features

  • Create, Read, Update and Delete stations and citybiketrips
  • Sort stations by city or free text search
  • Sort citybiketrips by date or search by free text search
  • Connected to Azure SQL server
  • Use in ConnectionString before created SQL user

CI/CD pipelines Azure

  1. Applications uses CI/CD pipelines Azure DevOps, see more: https://learn.microsoft.com/en-us/azure/devops/pipelines/get-started/what-is-azure-pipelines?view=azure-devops
  • Automated deploy for MVC every day when changes in development -branch
  • For frontend Development must be approved in Azure when deployed in release pipeline
  • Merge 'develop' to 'to-website' branch works triggered deployment:

git checkout develop
git pull
git checkout to-website
git merge develop

  • API release pipeline manually
  • Location Norway East, all OS: Windows
  • Frontend runtime stack: Node 16, others Dotnet v7.0
  • Publishing mode: Code

E2E tests Cypress

terminal commands

TODO -list

  • Make informative 404 page to Azure
  • Small informative windows in App when loading data
  • Small repairs for mobile layout
  • Information page to App