https://kotlinlang.org logo
#exposed
Title
# exposed
l

Lbenyehu

08/18/2021, 12:39 PM
Hi guys! I didn’t find any explanation how to create a table (postgres in my case) with partitions… someone know??
r

rcd27

08/20/2021, 1:01 PM
Is this Postgres-specific feature?
l

Lbenyehu

08/22/2021, 9:42 AM
I think it is SQL feature
r

rcd27

08/23/2021, 9:27 AM
Don't know then. I had specific postgres feature, implemented it via standard Java SQL interface...
l

Lbenyehu

08/23/2021, 9:56 AM
Can you share it with me?
r

rcd27

08/23/2021, 7:08 PM
Sure, no problem. Here I'm sending some raw queries for Postgres to use its features.
Copy code
private fun preparePostgresForPartialTextSearch() {
        //region create-extension
        val createExtensionQuery = """
            CREATE EXTENSION pg_trgm;
        """.trimIndent()

        try {
            val createExtensionStatement = connection!!.createStatement()
            createExtensionStatement.execute(createExtensionQuery)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating extension in [$databaseName] database")
        }
        //endregion

        //region create indices for eng_short_name
        val createIndicesForEngItems = """
            CREATE INDEX engitems_trgm_idx ON allitems
             USING gin (eng_short_name gin_trgm_ops);
        """.trimIndent()

        try {
            val createIndicesForEngItemsStatement = connection!!.createStatement()
            createIndicesForEngItemsStatement.execute(createIndicesForEngItems)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating engitems_trgm_idx for [allitems] table")
        }
        //endregion

        //region create indices for ru_short_name
        val createIndicesForRuItems = """
            CREATE INDEX ruitems_trgm_idx ON allitems
             USING gin (ru_short_name gin_trgm_ops);
        """.trimIndent()

        try {
            val createIndicesForRuItemsStatement = connection!!.createStatement()
            createIndicesForRuItemsStatement.execute(createIndicesForRuItems)
        } catch (e: SQLException) {
            log().error(e.message ?: "Error during creating ruitems_trgm_idx for [allitems] table")
        }
        //endregion

        log().info("Postgres seems to be prepared for partial text search")
    }
Setup is like this:
Copy code
private val database: Database by lazy {
        Database.connect(
            "jdbc:<postgresql://localhost:5432/$databaseName>",
            driver = "org.postgresql.Driver",
            user = "user",
            password = "password"
        )
    }

    private val connection: Connection?

    init {
        log().info("Database initiated: ${database.name}")

        val url = "jdbc:<postgresql://localhost:5432/$databaseName>"
        val user = "user"
        val password = "password"

        connection = try {
            DriverManager.getConnection(url, user, password)
        } catch (e: SQLException) {
            log().error("Error while trying to connect to database: ${e.message}")
            null
        }
        prepareAllItemsTable()
        preparePostgresForPartialTextSearch()
    }
l

Lbenyehu

09/01/2021, 1:49 PM
wow! thanks!
🤝 1
3 Views