I'm new to Android, and rather new to SQL in general.

I have a data model where I have a Text that consists of TextMetadata as well as a long string, which is the text content itself. So

Text {
  metadata: {
     author: string,
     title: string
     // more associated metadata
  },
  textContent: long string, or potentially array of lines or paragraphs
}

I'd like to load a list of the metadata for all texts on the App's landing page, without incurring the cost of reading all the long strings (or having operations be slowed down because the table has a column with a long string?).

What is the proper pattern here? Should I use two tables, and related them? Or can I use one table/one @Entity, with embedded metadata, and do some fancy stuff in the DAO to just list/sort/operate on the embedded metadata?

Most of my background is with NoSQL databases, so I could be thinking about this entirely wrong. Advice on the general best practices here would be helpful, but I guess I have two core questions:

1. Does having a long/very long string/TEXT column cause performance considerations when operating on that specific table/row?
2. Is there a clean way using Kotlin annotations to express embedded metadata that would make it easy to fetch in the DAO, without having use a long SELECT for each individual column?

CodePudding user response：

To answer your first question, having a long string column in your database table can cause performance considerations when operating on that specific table/row. This is because the database engine needs to load the entire string into memory in order to perform any operations on it. This can be particularly problematic if you have a large number of rows in your table with long strings, as it can cause the database to run out of memory and crash.

To avoid this problem, you can use two tables in your database and relate them using a foreign key. The first table would contain the metadata for each text, while the second table would contain the actual text content. This way, you can query the metadata table to get a list of all the texts without having to load the actual text content into memory.

To answer your second question, you can use the @Embedded and @Relation annotations in Kotlin to express embedded metadata and define relationships between your database tables. The @Embedded annotation can be used to define a nested object within your entity, while the @Relation annotation can be used to define a relationship between two entities.

Here is an example of how you could use these annotations to define your data model:

@Entity
data class Text(
    @PrimaryKey val id: Long,
    val metadata: TextMetadata,
    val textContent: String
)

@Embedded
data class TextMetadata(
    val author: String,
    val title: String
)

You can then use the @Relation annotation in your DAO to define a relationship between the Text and TextMetadata entities. This would allow you to query the TextMetadata table to get a list of all the texts without having to load the actual text content into memory.

CodePudding user response：

It is generally not recommended to store long strings in a SQL database, as this can lead to performance issues. Instead, you can use a technique called "lazy loading" to load the long strings only when they are needed. This involves storing the long strings in a separate table, and using a foreign key to relate the two tables.

To use this technique with Room, you can define two entities: one for the metadata, and one for the long strings. The metadata entity can have a foreign key column that refers to the primary key of the long strings table. In your DAO, you can use a LEFT JOIN query to load the metadata and the associated long strings, and then use the @Relation annotation to tell Room how to map the result of the query to your entity objects.

Here is an example of how this might look in code:

@Entity
data class TextMetadata(
    val author: String,
    val title: String,
    // more associated metadata
    val textContentId: Long
)

@Entity
data class TextContent(
    @PrimaryKey val id: Long,
    val textContent: String
)

data class TextWithContent(
    @Embedded val metadata: TextMetadata,
    @Relation(parentColumn = "textContentId", entityColumn = "id")
    val textContent: TextContent
)

@Dao
interface TextDao {
    @Query("SELECT * FROM text_metadata LEFT JOIN text_content ON text_metadata.textContentId = text_content.id")
    fun getAllTexts(): List<TextWithContent>
}

Using this approach, you can load only the metadata for all texts on the landing page, and then lazy-load the long strings when they are needed. This should improve the performance of your database operations.

As for expressing embedded metadata with Kotlin annotations, you can use the @Embedded annotation to tell Room that a property of your entity represents an embedded object. For example, in the code above, the metadata property of the TextWithContent entity is marked with the @Embedded annotation to indicate that it represents an embedded TextMetadata object.

I hope this helps! Let me know if you have any other questions.