wiki:otherdevelopment

Context Navigation

Version 9 (modified by 233062, 3 days ago) ( diff )
--

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

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

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

Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~1000 тренинг сесии (training_sessions), ~900 тренинг профили.

SQL:

CREATE OR REPLACE FUNCTION test_training_report()
RETURNS void AS $$
DECLARE
  start_time timestamptz;
  end_time   timestamptz;
  duration   int;
BEGIN
  start_time := clock_timestamp();

  PERFORM *
  FROM (
      WITH params AS (SELECT 2026::int AS report_year),
      months AS (SELECT generate_series(1,12) AS month_no),
      training_base AS (
          SELECT tu.user_id FROM training_users tu
      ),
      monthly_sessions AS (
          SELECT
              tb.user_id,
              m.month_no,
              COUNT(ts.training_id)            AS sessions_count,
              COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
              COALESCE(SUM(ts.calories), 0)    AS total_calories
          FROM training_base tb
          CROSS JOIN months m
          LEFT JOIN training_sessions ts
              ON ts.training_user_id = tb.user_id
             AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
             AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
          GROUP BY tb.user_id, m.month_no
      )
      SELECT user_id, SUM(sessions_count), SUM(total_calories)
      FROM monthly_sessions
      GROUP BY user_id
  ) t;

  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;

Индекси:

CREATE INDEX idx_training_sessions_user_date
    ON training_sessions(training_user_id, date);

Подобрување: Index Scan наместо Seq Scan при LEFT JOIN по training_user_id + филтрирање по датум

CREATE INDEX idx_training_sessions_year
    ON training_sessions ((EXTRACT(YEAR FROM date)));

Подобрување: Избегнува full table scan за годишен филтер со expression index

CREATE INDEX idx_training_sessions_covering
    ON training_sessions(training_user_id, date, duration, calories);

Подобрување: Covering index — сите потребни колони за агрегатите (SUM duration, SUM calories, COUNT) се читаат директно од индексот без посета на heap страниците

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

CREATE OR REPLACE FUNCTION test_training_report()
RETURNS void AS $$
DECLARE
  start_time timestamptz;
  end_time   timestamptz;
  duration   int;
BEGIN
  start_time := clock_timestamp();

  PERFORM *
  FROM (
      WITH params AS (SELECT 2026::int AS report_year),
      months AS (SELECT generate_series(1,12) AS month_no),
      training_base AS (
          SELECT tu.user_id FROM training_users tu
      ),
      monthly_sessions AS (
          SELECT
              tb.user_id,
              m.month_no,
              COUNT(ts.training_id)            AS sessions_count,
              COALESCE(SUM(ts.duration), 0)    AS total_duration_minutes,
              COALESCE(SUM(ts.calories), 0)    AS total_calories
          FROM training_base tb
          CROSS JOIN months m
          LEFT JOIN training_sessions ts
              ON ts.training_user_id = tb.user_id
             AND EXTRACT(YEAR  FROM ts.date)::int = (SELECT report_year FROM params)
             AND EXTRACT(MONTH FROM ts.date)::int = m.month_no
          GROUP BY tb.user_id, m.month_no
      )
      SELECT user_id, SUM(sessions_count), SUM(total_calories)
      FROM monthly_sessions
      GROUP BY user_id
  ) t;

  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;


-- 4. PERFORMANCE RUNS
-- run 1: no indexes
SELECT test_training_report();

-- run 2: add idx_training_sessions_user_date
CREATE INDEX idx_training_sessions_user_date ON training_sessions(training_user_id, date);
ANALYZE training_sessions;
SELECT test_training_report();

-- run 3: add idx_training_sessions_year (Expression Index)
CREATE INDEX idx_training_sessions_year ON training_sessions ((EXTRACT(YEAR FROM date)));
ANALYZE training_sessions;
SELECT test_training_report();

-- run 4: add covering index
CREATE INDEX idx_training_sessions_covering ON training_sessions(training_user_id, date, duration, calories);
ANALYZE training_sessions;
SELECT test_training_report();

-- Cleanup
DROP FUNCTION test_training_report();

Сумарно: Без индекси: ~67ms Со индекси: ~24ms Забрзување: ~2.8x

Извештај за годишна финансиска активност на корисници

Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат за тестирање. Базата содржи: ~1000 корисници, ~10000 приходи (incomes), ~1000 finance профили.

SQL:

CREATE OR REPLACE FUNCTION test_finance_report()
RETURNS void AS $$
DECLARE
  start_time timestamptz;
  end_time timestamptz;
  duration int;
BEGIN
  start_time := clock_timestamp();

  PERFORM *
  FROM (
      SELECT
          fb.user_id,
          COALESCE(stats.income_count, 0),
          COALESCE(stats.annual_total_income, 0),
          COALESCE(stats.active_months, 0)
      FROM finance_users fb
      LEFT JOIN (
          -- Combined logic into one subquery for efficiency
          SELECT 
              user_id, 
              COUNT(*) AS income_count, 
              SUM(amount) AS annual_total_income,
              COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
          FROM incomes
          WHERE EXTRACT(YEAR FROM date) = 2026
          GROUP BY user_id
      ) stats ON stats.user_id = fb.user_id
  ) t;

  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;

Индекси:

CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);

Подобрување: Index Scan наместо Seq Scan + подобро филтрирање по година

CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));

Подобрување: Избегнува full table scan за годишен филтер

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

CREATE OR REPLACE FUNCTION test_finance_report()
RETURNS void AS $$
DECLARE
  start_time timestamptz;
  end_time timestamptz;
  duration int;
BEGIN
  start_time := clock_timestamp();

  PERFORM *
  FROM (
      SELECT
          fb.user_id,
          COALESCE(stats.income_count, 0),
          COALESCE(stats.annual_total_income, 0),
          COALESCE(stats.active_months, 0)
      FROM finance_users fb
      LEFT JOIN (
          -- Combined logic into one subquery for efficiency
          SELECT 
              user_id, 
              COUNT(*) AS income_count, 
              SUM(amount) AS annual_total_income,
              COUNT(DISTINCT EXTRACT(MONTH FROM date)) AS active_months
          FROM incomes
          WHERE EXTRACT(YEAR FROM date) = 2026
          GROUP BY user_id
      ) stats ON stats.user_id = fb.user_id
  ) t;

  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;


-- 4. PERFORMANCE RUNS
-- run 1: no indexes
SELECT test_finance_report();

-- run 2: add idx_incomes_user_date
CREATE INDEX idx_incomes_user_date ON incomes(user_id, date);
ANALYZE incomes;
SELECT test_finance_report();

-- run 3: add idx_incomes_year (Expression Index)
CREATE INDEX idx_incomes_year ON incomes ((EXTRACT(YEAR FROM date)));
ANALYZE incomes;
SELECT test_finance_report();

-- run 4: add covering index
-- FIX: only one definition of idx_incomes_covering (removed duplicate)
CREATE INDEX idx_incomes_covering ON incomes(user_id, date, amount);
ANALYZE incomes;
SELECT test_finance_report();

-- Cleanup
DROP FUNCTION test_finance_report();

Сумарно: Без индекси: ~41ms Со индекси: ~16ms Забрзување: ~2.6x

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

JWT Token Authorization (Spring Security)

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

Java код во Spring Boot:

@Configuration
@Getter @Setter
@ConfigurationProperties(prefix = "auth")
public class AuthProperties {
    private String secret;
    private int accessTokenMaxAge;
    private int refreshTokenMaxAge;
}


@Configuration
@EnableWebSecurity
public class SecurityConfig {

    private final JwtAuthenticationFilter jwtAuthenticationFilter;

    public SecurityConfig(JwtAuthenticationFilter jwtAuthenticationFilter) {
        this.jwtAuthenticationFilter = jwtAuthenticationFilter;
    }

    @Bean
    public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
        http
                .csrf(AbstractHttpConfigurer::disable)
                .cors(Customizer.withDefaults())
                .sessionManagement(sm -> sm.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
                .httpBasic(AbstractHttpConfigurer::disable)
                .formLogin(AbstractHttpConfigurer::disable)
                .authorizeHttpRequests(auth -> auth
                        .requestMatchers("/api/auth/**").permitAll()
                        .anyRequest().authenticated());

        http.addFilterBefore(jwtAuthenticationFilter, UsernamePasswordAuthenticationFilter.class);
        return http.build();
    }

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

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

public class LegacyPasswordEncoder implements PasswordEncoder {

    private final BCryptPasswordEncoder bcrypt = new BCryptPasswordEncoder();

    @Override
    public String encode(CharSequence rawPassword) {
        return bcrypt.encode(rawPassword);
    }

    @Override
    public boolean matches(CharSequence rawPassword, String encodedPassword) {
        if (encodedPassword == null || encodedPassword.isEmpty()) {
            return false;
        }
        if (encodedPassword.startsWith("$2a$") || encodedPassword.startsWith("$2b$")) {
            return bcrypt.matches(rawPassword, encodedPassword);
        }
        return rawPassword.toString().equals(encodedPassword);
    }
}

SQL Injection Prevention (Spring JPA/JPQL)

Целиот backend користи Spring Data JPA која автоматски генерира параметризирани пропити што спречуваат SQL injection.

Безбедно: Derived Query Methods

// UserRepository.java
public interface UserRepository extends JpaRepository<User, Long> {
    Optional<User> findByEmail(String email);
    Optional<User> findByUsername(String username);
}


// SELECT * FROM users WHERE email = ?
// параметарот се праќа одвоено од SQL командата

Безбедно: @Query со @Param

// TaskRepository.java
// FIX: JPQL field name corrected from t.finished to t.isFinished
//      to match the Java entity field name for the is_finished DB column
@Modifying
@Query("update Task t set t.isFinished = false where t.disciplineUser.userId = :userId")
int resetFinishedForUser(@Param("userId") Long userId);

// Параметарот :userId се врзува безбедно преку JDBC, не преку string concatenation

Безбедно: Collection параметри

// TrainingSessionRepository.java
@java.util.List<TrainingSession> findByTrainingUser_UserIdAndDateIn(
    Long userId,
    Collection<LocalDate> dates
);

CORS Configuration

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

Java код во Spring Boot:

    @Bean
    public CorsConfigurationSource corsConfigurationSource() {
        CorsConfiguration config = new CorsConfiguration();
        config.setAllowedOrigins(List.of(
                "http://localhost:5173",
                "http://127.0.0.1:5173"));
        config.setAllowedMethods(List.of("GET", "POST", "PUT", "PATCH", "DELETE", "OPTIONS"));
        config.setAllowedHeaders(List.of("Authorization", "Content-Type", "Accept", "Origin", "X-Requested-With"));
        config.setAllowCredentials(true);

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

Note: See TracWiki for help on using the wiki.

Download in other formats:

Plain Text