[OSSCA] O-RAN에서 SQLAlchemy 리팩토링

AIMLFW-120 이슈는 Training Manager(TS)의 데이터베이스 레이어를 SQLAlchemy를 사용하여 리팩토링하는 작업입니다. 기존 코드에서는 Raw SQL과 pg8000 라이브러리를 사용하여 데이터베이스와 상호작용하고 있었으나, 이를 SQLAlchemy ORM으로 변경하여 코드의 가독성과 유지보수성을 향상시키고자 했습니다.

주요 변경 사항:

SQLAlchemy 모델 정의:

TrainingJobInfo와 FeatureGroupInfo 테이블에 대해 SQLAlchemy 모델을 정의했습니다.

각 테이블의 컬럼은 SQLAlchemy의 Column 클래스를 사용하여 정의했습니다.

SQLAlchemy 세션 관리:

데이터베이스 연결 및 트랜잭션 관리를 위해 sessionmaker를 사용하여 세션을 생성하고 관리했습니다.

데이터베이스 함수 리팩토링:

기존의 Raw SQL 기반 함수들을 SQLAlchemy ORM을 사용하여 리팩토링했습니다.

데이터 조회, 삽입, 업데이트, 삭제 기능을 SQLAlchemy 쿼리로 변환하여 구현했습니다.

코드 예시:


def get_data_extraction_in_progress_trainingjobs(ps_db_obj):
    session = ps_db_obj.get_new_session()
    try:
        results = session.query(TrainingJobInfo).all()
        result_dict = {}
        for row in results:
            steps_state = json.loads(row.steps_state)
            if steps_state[Steps.DATA_EXTRACTION.name] == States.IN_PROGRESS.name:
                result_dict[row.trainingjob_name] = "Scheduled"
        return result_dict
    except Exception as err:
        raise DBException(DB_QUERY_EXEC_ERROR + "get_data_extraction_in_progress_trainingjobs," + str(err))
    finally:
        session.close()

결론:

이번 리팩토링 작업을 통해 코드의 구조가 개선되었으며, SQLAlchemy를 사용함으로써 데이터베이스 조작이 더 간결하고 직관적으로 변경되었습니다. 앞으로 추가적인 기능 확장이나 수정이 필요할 때도 더 쉽게 작업을 진행할 수 있을 것입니다.

Getting Started

Now that you have conceptually grasped GraphQL, let's actually use it in Next.js and see how it differs from the previously used Flux-based state management library. 🙂

Obviously, it is not necessary to use a library to call a GraphQL API. It is possible to simply call the fetch function alone, but using the library provides various functions and has advantages in various aspects such as development experience, so we will go ahead and install the library right away.

Among them, we will use the most used apollo client.

Building a development environment

First, build the next environment using create-next-app and install the apollo and graphql packages.


yarn create next-app [Project Name]
cd [Project Name]
yarn add @apollo/client graphql

graphql: This is a package that allows js to import data through gql.

@apollo/client : It depends on the graphql package and provides several functions to use gql in the client.

Now, if you have installed the desired package and type yarn dev, the script specified in package.json is executed, and you can see that next.js is operating normally by node.js.

Initializing the ApolloClient instance

Let's write code that creates an apollo client instance to use the library.


// ./apollo-client.js

import { ApolloClient, InMemoryCache } from "@apollo/client";

const client = new ApolloClient({
    uri: "https://countries.trevorblades.com",
    cache: new InMemoryCache(),
});

export default client;

Enter the URL of the gql server in uri and put an InMemoryCache instance in cache, which is used when caching data retrieved from the client.

Now, in order to run a query, we need to know what kind of data can be retrieved from the URL we have listed. At this time, Apollo Explorer is used.

Apollo Explorer

If you use Apollo Explorer, you can see what kind of data can be retrieved through the corresponding url like the API specification of the Rest API. First of all, if you create an account through the link and enter https://countries.trevorblades.com, the endpoint we entered, the following screen will appear.

You can check the schema here and run queries easily. (Think of it as a tool similar to the REST API's Postman.)

If you go to the Schema page, the data that can be imported is specified. Of these, we will bring and use the code name emoji from Country.

You can run the query below on the Operations page to check if it is being received normally.


query ExampleQuery {
  countries {
    code
    name
		emoji
  }
}

If the data comes out normally as shown in the image below, it is successful.

Data fetching in Client-Side-Rendering

Data fetching methods in Next.js are largely divided into SSR method through getServerSideProps, SSG method through getStaticProps, and CSR method. Each method differs only in the loading time, but data fetching through gql is the same, so we use CSR Let's write the logic that takes data into account and considers loading.

First of all, you need to wrap the component in _app.js with ApolloProvider.


import { ApolloProvider } from '@apollo/client'
import client from '../apollo-client'
import '../styles/globals.css'

function MyApp({ Component, pageProps }) {
  return (
    <ApolloProvider client={client}>
      <Component {...pageProps} />
    </ApolloProvider>
  )
}


export default MyApp

Now, let's create a ClientOnly component that renders only in the client's environment and a Countries component that gets data.

ClientOnly component


import { useEffect, useState } from "react";

export default function ClientOnly({ children, ...delegated }) {
  const [hasMounted, setHasMounted] = useState(false);

  useEffect(() => {
    setHasMounted(true);
  }, []);

  if (!hasMounted) {
    return null;
  }

  return <div {...delegated}>{children}</div>;
}

Countries component


import { useQuery, gql } from "@apollo/client"

const QUERY = gql`
  query Countries {
    countries {
      code
      name
      emoji
    }
  }
`

export default function Countries() {
  const { data, loading, error } = useQuery(QUERY)

  if (loading) {
    return <h2><a href="#loading" aria-hidden="true" id="loading"></a>Loading...</h2>
  }

  if (error) {
    console.error(error)
    return null
  }

  const countries = data.countries.slice(0, 4)

  return (
    <div >
      {countries.map((country) => (
        <div key={country.code} >
          <h3><a href="#country-name" aria-hidden="true" id="country-name"></a>{country.name}</h3>
          <p>
            {country.code} - {country.emoji}
          </p>
        </div>
      ))}
    </div>
  )
}

If a gql query is passed to a hook called useQuery, data is fetched through the query. It returns an object containing data, loading, and error, and manages the state of asynchronous processing.

Put that component in index.js.


import Head from "next/head"
import styles from "../styles/Home.module.css"
import ClientOnly from "../components/ClientOnly"
import Countries from "../components/Countries"

export default function ClientSide() {
  return (
    <div className={styles.container}>
      <Head>
        <title>Create Next App</title>
        <link rel="icon" href="/favicon.ico" />
      </Head>

      <main className={styles.main}>
        <ClientOnly>
          <Countries />
        </ClientOnly>
      </main>

      <footer className={styles.footer}>
        <a
          href="https://vercel.com"
          target="_blank"
          rel="noopener noreferrer"
        >
          Powered by{" "}
          <img src="/vercel.svg" alt="Vercel Logo" className={styles.logo} />
        </a>
      </footer>
    </div>
  )
}

The data should be output normally as shown below.

concluding

It was good that using the library for gql made it much simpler to implement compared to Redux, which requires writing a lot of code to process data. However, it seems to be abstracted as much as it is convenient, so it would be better to know how it works without understanding the internal logic.

Reference

https://www.apollographql.com/docs/react/get-started#2-initialize-apolloclient

https://www.apollographql.com/blog/apollo-client/next-js/next-js-getting-started/