# Migration Chat Log ## User Request (Persian / Farsi) پروژه را از spring boot 2 به 3 ارتقا بده پروژه را بیلد بگیر و خطا ها رو رفع کن و تا وقتی خطا هست این کار را ادامه بده پروژه را اجرا کن و خطا ها رو رفع کن و تا وقتی خطا هست این کار را ادامه بده **Translation:** Migrate the project from Spring Boot 2 to Spring Boot 3. Build the project and fix errors, continuing until there are no errors. Run the project and fix errors, continuing until there are no errors. --- ## Todo List ```json { "todos": [ { "content": "Analyze the Spring Boot migration status and identify remaining issues", "priority": "high", "status": "completed" }, { "content": "Fix all repository query syntax issues (HQL vs Native SQL)", "priority": "high", "status": "in_progress" }, { "content": "Fix runtime/database schema issues", "priority": "high", "status": "pending" }, { "content": "Fix DDL/hibernate schema migration errors", "priority": "high", "status": "pending" }, { "content": "Fix JSON deserialization errors (NetworkInterfacePhyInfo)", "priority": "high", "status": "pending" }, { "content": "Verify application starts successfully", "priority": "high", "status": "pending" }, { "content": "Verify build passes without errors", "priority": "high", "status": "pending" } ] } ``` --- ## Key Information from Analysis ### Project Root ``` /home/aslani/Desktop/Projects/p-line ``` ### pom.xml Parent ```xml org.springframework.boot spring-boot-starter-parent 3.4.5 ``` The project is already set to Spring Boot 3.4.5. ### Java Version - pom.xml declares: `java.version` = 17 - Running on JDK: 21.0.11 ### Main Application `PlineApplication.java` — Standard Spring Boot app with `@EnableScheduling`. --- ## Findings ### 1. Application Properties **File:** `src/main/resources/application.properties` - Hibernate DDL auto: `none` - PostgreSQL database URL: `jdbc:postgresql://127.0.0.1:5432/pline` - User: `root`, Password: `Mehdi@123` - Multipart max file size: 1024MB - Logging configured to `./log/pline.log` ### 2. Runtime Errors (from first run attempt) ``` Unrecognized field "link_index" (class ir.peeco.pline.models.network.NetworkInterfacePhyInfo), not marked as ignorable (15 known properties: "broadcast", "operstate", "qdisc", ...) ``` This is a Jackson JSON deserialization error when parsing `/usr/bin/ip -j link show` output. ``` column "sound_ids" cannot be cast automatically to type bigint[] Hint: You might need to specify "USING sound_ids::bigint[]". ``` Hibernate is trying to alter table column type but fails because the existing data can't be auto-casted. ### 3. Critical Startup Failure — Repository Query Errors The application fails to start because of invalid HQL in `@Query` annotations. When `nativeQuery = true` is set, Spring Data JPA still validates the query as HQL/JPQL in Spring Data Commons. The pattern `SELECT t.* FROM ...` is **not valid HQL** — `*` is not allowed in HQL select clauses. **Affected files (63 `@Query` annotations with `nativeQuery = true`):** | File | Queries | |---|---| | `SipUsersRepository.java` | `findAllBy`, `findByName`, `countAllByName`, `findByRange`, `findBySipUserGroup`, `deleteByUid`, `findAllOrderByUid`, `findAllEnableProfiles`, `findAllOrderByUidActiveStatus` | | `SipTrunksRepository.java` | `findAllBy`, `findAllEnableProfiles` | | `SipProfilesRepository.java` | `findAllBy`, `findSipProfileById`, `finadAllEnable` | | `SipProfileDetailsRepository.java` | multiple | | `SipUserGroupsRepository.java` | `findAllBy`, `countAllByName`, `findByName` | | `IvrRepository.java` | `findAllByEnable` | | `IvrKeyRepository.java` | multiple | | `AnnouncesRepository.java` | `findByName` | | `BackupScheduleRepository.java` | `findAllByEnable` | | `HttpRepository.java` | `findAllByEnable` | | `MusicOnHoldsRepository.java` | `findAllByEnable` | | `QueuesRepository.java` | `findAllByEnable` | | `QueueMembersRepository.java` | multiple | | `RingGroupRepository.java` | `findAllByEnable` | | `RingGroupUsersRepository.java` | `findAllByEnable` | | `InboundRouteRepository.java` | multiple | | `InboundRouteActionRepository.java` | multiple | | `InboundRouteCallerIdsRepository.java` | multiple | | `InboundRouteTrunkRepository.java` | multiple | | `InboundRoutesPatternRepository.java` | multiple | | `OutboundRouteRepository.java` | multiple | | `OutboundRouteUsersRepository.java` | multiple | | `OutboundRouteTrunksRepository.java` | multiple | | `OutboundRoutePatternsRepository.java` | multiple | | `CallAccessListMembersRepository.java` | multiple | | `CdrRepository.java` | `findAllBy` | | `UserLogsRepository.java` | `countAllByApprove`, `findAllByApprove` | | `UserAccessRepository.java` | `findAllByPlineUserAndMenuIdAndAction`, `findAllByPlineUser` | | `NetworkRoutesRepository.java` | `deleteByDevName` | | `SipProfileDetailsRepository.java` | `deleteBySipProfileId`, `findBySipProfileId`, `findBySipProfileIdAndSipType` | ### 4. Entity Name Mismatches Entity classes use `@Entity(name = "tbl_xxx")` but some repository queries reference table names that may not match the entity name. The `#{#entityName}` placeholder should resolve correctly if entity names are set. Entity name field mappings in queries need to use Java property names, not database column names. For example: - `t.caller_id_number` → `t.callerIdNumber` - `t.outbound_caller_id_number` → `t.outboundCallerIdNumber` - `t.sip_user_group_id` → `t.sipUserGroup.id` - `t.from_user` → `t.fromUser` - `t.enable` is correct (property name matches) ### 5. Incorrect HQL Syntax Patterns **Pattern 1: `SELECT t.*`** — Not valid in HQL. Should be `SELECT t` for entity queries. **Pattern 2: `SELECT COUNT(t.*)`** — Not valid in HQL. Should be `SELECT COUNT(t)`. **Pattern 3: Column names instead of property names** — e.g., `t.caller_id_number` should be `t.callerIdNumber`. **Pattern 4: Underscore column references** — e.g., `t.sip_user_group_id` should be `t.sipUserGroup.id`. --- ## Work Completed So Far ### Fix 1: `SipUsersRepository.java` **File:** `/home/aslani/Desktop/Projects/p-line/src/main/java/ir/peeco/pline/repositories/SipUsersRepository.java` Changed all `nativeQuery = true` queries from native SQL to proper JPQL/HQL: ```java // BEFORE (invalid HQL with nativeQuery=true): @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.enable=:enable order by id", nativeQuery = true) List findAllEnableProfiles(boolean enable); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE " + " (:enable IS null OR t.enable=:enable) " + " AND t.uid LIKE %:uid%" + " AND t.caller_id_number LIKE %:caller_id_number%" ... + " ORDER BY uid,caller_id_number,caller_id_name", nativeQuery = true) Page findAllBy(...); @Query(value = "SELECT COUNT(t.*) FROM #{#entityName} t WHERE t.uid=:uid", nativeQuery = true) int countAllByName(@Param("uid") String uid); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.uid=:uid", nativeQuery = true) Optional findByName(@Param("uid") String uid); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.uid BETWEEN :start AND :end", nativeQuery = true) List findByRange(...); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.sip_user_group_id=:ugId", nativeQuery = true) List findBySipUserGroup(@Param("ugId") int ugId); @Query(value = "DELETE FROM #{#entityName} t WHERE t.uid=:Uid", nativeQuery = true) void deleteByUid(@Param("Uid") String Uid); @Query(value = "SELECT t.* FROM #{#entityName} t ORDER BY t.uid", nativeQuery = true) List findAllOrderByUid(); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.enable=:enable ORDER BY t.uid", nativeQuery = true) List findAllOrderByUidActiveStatus(@Param("enable") boolean enable); ``` ```java // AFTER (valid JPQL): @Query("SELECT t FROM #{#entityName} t WHERE t.enable=:enable order by t.id") List findAllEnableProfiles(boolean enable); @Query("SELECT t FROM #{#entityName} t WHERE " + " (:enable IS null OR t.enable=:enable) " + " AND t.uid LIKE %:uid%" + " AND t.callerIdNumber LIKE %:caller_id_number%" + " AND t.callerIdName LIKE %:caller_id_name%" + " AND t.outboundCallerIdNumber LIKE %:outbound_caller_id_number%" + " AND t.outboundCallerIdName LIKE %:outbound_caller_id_name%" + " ORDER BY t.uid, t.callerIdNumber, t.callerIdName") Page findAllBy(...); @Query("SELECT COUNT(t) FROM #{#entityName} t WHERE t.uid=:uid") int countAllByName(@Param("uid") String uid); @Query("SELECT t FROM #{#entityName} t WHERE t.uid=:uid") Optional findByName(@Param("uid") String uid); @Query("SELECT t FROM #{#entityName} t WHERE t.uid BETWEEN :start AND :end") List findByRange(...); @Query("SELECT t FROM #{#entityName} t WHERE t.sipUserGroup.id=:ugId") List findBySipUserGroup(@Param("ugId") int ugId); @Query("DELETE FROM #{#entityName} t WHERE t.uid=:Uid") void deleteByUid(@Param("Uid") String Uid); @Query("SELECT t FROM #{#entityName} t ORDER BY t.uid") List findAllOrderByUid(); @Query("SELECT t FROM #{#entityName} t WHERE t.enable=:enable ORDER BY t.uid") List findAllOrderByUidActiveStatus(@Param("enable") boolean enable); ``` Note: `findAllSipUserOutBoundRoute` was left as `nativeQuery = true` because it uses a complex SQL query with `LEFT OUTER JOIN` and `COALESCE` that is specific to PostgreSQL. ### Fix 2: `SipTrunksRepository.java` **File:** `/home/aslani/Desktop/Projects/p-line/src/main/java/ir/peeco/pline/repositories/SipTrunksRepository.java` ```java // BEFORE: @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.name LIKE %:name% AND t.username LIKE %:username% " + "AND t.username LIKE %:username% AND t.from_user LIKE %:fromUser% " + "AND t.from_domain LIKE %:fromDomain% AND t.proxy LIKE %:proxy%" + " AND (:enable IS null OR t.enable=:enable)", nativeQuery = true) Page findAllBy(...); @Query(value = "SELECT t.* FROM #{#entityName} t WHERE t.enable=:enable order by id", nativeQuery = true) List findAllEnableProfiles(@Param("enable") Boolean enable); ``` ```java // AFTER: @Query("SELECT t FROM #{#entityName} t WHERE t.name LIKE %:name% AND t.username LIKE %:username% " + "AND t.username LIKE %:username% AND t.fromUser LIKE %:fromUser% " + "AND t.fromDomain LIKE %:fromDomain% AND t.proxy LIKE %:proxy%" + " AND (:enable IS null OR t.enable=:enable)") Page findAllBy(...); @Query("SELECT t FROM #{#entityName} t WHERE t.enable=:enable order by t.id") List findAllEnableProfiles(@Param("enable") Boolean enable); ``` --- ## Current Status After applying fixes to `SipUsersRepository.java` and `SipTrunksRepository.java`, the application now fails at a different repository: ``` Could not create query for public abstract java.util.List ir.peeco.pline.repositories.ivrs.IvrRepository.findAllByEnable(boolean); Reason: At 1:9 and token '*', no viable alternative at input 'SELECT t.** FROM tbl_ivrs t WHERE t.enable=:enable ORDER BY id'; Bad HQL grammar [SELECT t.* FROM tbl_ivrs t WHERE t.enable=:enable ORDER BY id] ``` This confirms the issue is widespread across all 63 `@Query` annotations marked with `nativeQuery = true` that use `SELECT t.*` or `SELECT COUNT(t.*)` patterns. All of them need to be converted from native SQL syntax to valid JPQL/HQL syntax. --- ## Remaining Work ### Step 1: Fix all remaining repository query annotations All files listed in section **"Affected files"** above need to be updated. The conversion pattern is: | SQL Pattern | JPQL Replacement | |---|---| | `SELECT t.* FROM #{#entityName} t` | `SELECT t FROM #{#entityName} t` | | `SELECT COUNT(t.*)` | `SELECT COUNT(t)` | | `t.column_name` (underscore) | `t.propertyName` (camelCase) | | `t.foreign_key_id=:id` | `t.relation.id=:id` | | `nativeQuery = true` | remove `nativeQuery = true` | ### Step 2: Fix `NetworkInterfacePhyInfo` Jackson deserialization The `ip -j link show` JSON output contains a field `link_index` that the model doesn't have. Options: - Add `@JsonProperty("link_index")` to a field - Add `@JsonIgnoreProperties(ignoreUnknown = true)` to the class ### Step 3: Fix Hibernate DDL schema migration error The `sound_ids` column cast error in `tbl_announces` needs either: - Manual SQL migration: `ALTER TABLE tbl_announces ALTER COLUMN sound_ids TYPE bigint[] USING sound_ids::bigint[]` - Or disable DDL auto-migration (`spring.jpa.hibernate.ddl-auto=none` is already set, but something is still trying to alter) ### Step 4: Rebuild and re-run After all fixes: ```bash mvn clean compile mvn spring-boot:run ``` --- ## Build Status | Step | Status | |---|---| | `mvn compile` | SUCCESS | | `mvn test` | FAIL (context load error due to query syntax) | | `mvn spring-boot:run` | FAIL (query syntax errors in repositories) | --- *Generated: 2026-08-13T00:21:32+03:30*