Други Развојни Активности

Анализа на перформанси

Извештај за најпопуларни курсеви според оценка

Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат само за тестирање. Базата содржи: 1000 курсеви, 2000 преводи (по 2 преводи по курс), 1000 верзии на курсот (по 1 верзија по курс), 5000 корисници, 200 експерти, 10000 enrollments, 7000 reviews

SQL:

SELECT 
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.version_number AS course_version,
    cv.active AS is_active,
    COUNT(DISTINCT e.id) AS total_enrollments,
    SUM(r.rating) / COUNT(r.id) AS average_rating,
    COUNT(r.id) AS total_reviews
FROM course c
JOIN course_translate ct ON c.id = ct.course_id
JOIN course_version cv ON c.id = cv.course_id
JOIN enrollment e ON cv.id = e.course_version_id
JOIN review r ON e.id = r.enrollment_id
WHERE ct.language = :language
GROUP BY c.id, ct.id, cv.id
ORDER BY SUM(r.rating) / COUNT(r.id) DESC

Индекси:

1. course_translate - Unique (course_id, language)

CREATE UNIQUE INDEX uk_course_translate_course_language 
ON course_translate(course_id, language);

Користење: JOIN на ct.course_id + WHERE филтер на ct.language

Подобрување: Index Scan наместо Seq Scan

---

2. course_version - Index (course_id)

CREATE INDEX idx_course_version_course_id 
ON course_version(course_id);

Користење: JOIN на cv.course_id

Подобрување: Директен lookup

---

3. enrollment - Index (course_version_id)

CREATE INDEX idx_enrollment_course_version_id 
ON enrollment(course_version_id);

Користење: JOIN на e.course_version_id

Подобрување: Избегнува full table scan

---

4. review - Unique (enrollment_id)

CREATE UNIQUE INDEX uk_review_enrollment 
ON review(enrollment_id);

Користење: JOIN на r.enrollment_id

Подобрување: Unique scan

---

5. course - Primary Key (id)

-- Автоматски креиран со PRIMARY KEY
CREATE UNIQUE INDEX course_pkey 
ON course(id);

Користење: Примарна табела PK lookup

---

SQL за анализа на брзината

CREATE OR REPLACE FUNCTION test_popular_courses()
RETURNS void AS $$
DECLARE
  start_time timestamptz;
  end_time timestamptz;
  duration int;
BEGIN
  start_time := clock_timestamp();
  
  PERFORM 
    c.id AS course_id,
    ct.title_short AS course_title,
    cv.version_number AS course_version,
    cv.active AS is_active,
    COUNT(DISTINCT e.id) AS total_enrollments,
    SUM(r.rating)::numeric / COUNT(r.id) AS average_rating,
    COUNT(r.id) AS total_reviews
  FROM course c
  JOIN course_translate ct ON c.id = ct.course_id
  JOIN course_version cv ON c.id = cv.course_id
  JOIN enrollment e ON cv.id = e.course_version_id
  JOIN review r ON e.id = r.enrollment_id
  WHERE ct.language = 'en'
  GROUP BY c.id, ct.id, cv.id
  ORDER BY SUM(r.rating)::numeric / COUNT(r.id) DESC
  LIMIT 20;
  
  end_time := clock_timestamp();
  duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
  
  RAISE NOTICE 'Query executed in: % ms', duration;
END;
$$ LANGUAGE plpgsql;

DROP INDEX IF EXISTS uk_course_translate_course_language;
DROP INDEX IF EXISTS idx_course_version_course_id;
DROP INDEX IF EXISTS idx_enrollment_course_version_id;
DROP INDEX IF EXISTS uk_review_enrollment;

-- run 1: no indexes

SELECT test_popular_courses();

-- run 2: index uk_course_translate_course_language

CREATE UNIQUE INDEX uk_course_translate_course_language 
ON course_translate(course_id, language);
ANALYZE course_translate;

SELECT test_popular_courses();

-- run 3: index uk_course_translate_course_language + idx_course_version_course_id

CREATE INDEX idx_course_version_course_id 
ON course_version(course_id);
ANALYZE course_version;

SELECT test_popular_courses();

-- run 4: index uk_course_translate_course_language + idx_course_version_course_id + idx_enrollment_course_version_id

CREATE INDEX idx_enrollment_course_version_id 
ON enrollment(course_version_id);
ANALYZE enrollment;

SELECT test_popular_courses();

-- run 5: all indexes -> uk_course_translate_course_language + idx_course_version_course_id + idx_enrollment_course_version_id + uk_review_enrollment

CREATE UNIQUE INDEX IF NOT EXISTS uk_review_enrollment 
ON review(enrollment_id);
ANALYZE review;

SELECT test_popular_courses();

DROP FUNCTION test_popular_courses();

Сумарно:

• Без индекси: Seq Scan на сите табели (бавно)
• Со индекси: Index Scan на сите JOIN-ови и WHERE (брзо)
• Перформанси:
  • Без индекси: 51ms
  • Со индекси: 23ms
• Забрзување: 2.2 пати

Безбедност и заштита

JWT Token Authorization (Spring Security)

JWT (JSON Web Token) e stateless начин на автентикација - server НЕ чува информации за активни сесии во база, туку сите потребни податоци се во самиот token кој корисникот го чува локално/cookie. JWT содржи енкодирана json структура на информации (user_id, email, role, expiry...).

Java код во Spring Boot:

@Bean
    public SecurityFilterChain filterChain(HttpSecurity http) throws Exception {
        http
                .csrf(AbstractHttpConfigurer::disable)
                .cors(Customizer.withDefaults())
                .sessionManagement(session -> session.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
                .authorizeHttpRequests(request -> request
                        .requestMatchers("/api/verification-tokens/**").permitAll()
                        .requestMatchers("/api/auth/**").permitAll()
                        .requestMatchers("/api/courses/**").permitAll()
                        .requestMatchers("/api/test/**").permitAll()
                        .requestMatchers("/api/auth/oauth2/**").permitAll()
                        .requestMatchers("/oauth2/**", "/login/oauth2/**").permitAll()
                        .anyRequest().authenticated()
                )
                .exceptionHandling(exception -> exception
                        .authenticationEntryPoint(authenticationEntryPoint)
                )
                .oauth2Login(oauth2 -> oauth2
                        // Use the custom handler instead of defaultSuccessUrl()
                        .successHandler(oauth2SuccessHandler)
                        .failureUrl("http://localhost:5173/login?error")
                )
                .authenticationProvider(authenticationProvider)
                .addFilterBefore(jwtAuthFilter, UsernamePasswordAuthenticationFilter.class);

        return http.build();
    }

Хеширање на пасворди (BCrypt)

Пасвордите на корисниците и експертите се чуваат во база во хеширана форма преку BCrypt, а не како plain text. Ова овозможува сигурно чување на пасвордите.

SQL Injection Prevention (Spring JPA/JPQL)

Преку JPA/JPQL се спречува SQL Injection напад каде корисникот внесува злонамерен код за да манипулира со базата на податоци. Нападите се избегнуваат преку третирање на параметарот како plain data, а не команда.

Безбедно:

    @Query("select u.email from User u where u.id = :userId")
    String getUserEmailById(@Param("userId") Long userId);

Небезбедно:

    String query = "SELECT * FROM users WHERE email = '" + email + "'";

CORS Configuration

CORS е безбеден механизам за заштита од requests од различни домени. Со тоа се заштитуваме од можни злонамерни requests.

Java код во Spring Boot:

@Bean
    public CorsConfigurationSource corsConfigurationSource() {
        CorsConfiguration config = new CorsConfiguration();
        config.addAllowedOriginPattern("http://localhost:*");
        config.addAllowedHeader("*");
        config.addAllowedMethod("*");
        config.setAllowCredentials(true);
        config.addExposedHeader(HttpHeaders.CONTENT_DISPOSITION);

        UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
        source.registerCorsConfiguration("/**", config);
        return source;
    }