If the RDMS supports the SUBSTR function you may be able to use that with a CLOB column.
That would allow you to retrieve the data with a series of SQL statements.
On Mon, Feb 20, 2023, 2:45 PM James K. Lowden <jklowden@xxxxxxxxxxxxxxx> wrote:
On Sun, 19 Feb 2023 15:13:23 -0600
Kevin Monceaux <Kevin@xxxxxxxxxxxxxx> wrote:
> On Sun, Feb 19, 2023 at 08:31:57AM -0500, James K. Lowden wrote:
>
> > Can you give me a sense of the kind of information stored in that
> > column?
>
> I tend to use unlimited character varying fields for text fields that
> might contain long-ish strings, though not necessarily ridiculously
> long strings.
>
> The database I was wanting to try out with OCESQL is one loaded with
> some basic title details from the IMDb's title basics data set
> available at:
>
> https://datasets.imdbws.com/
>
> The description of the title.basics.tsv.gz file doesn't specify how
> long any of the string columns might be, so I defined them as
> unlimited character varying columns. The longest column is the
> primaryTitle column. At the moment, the longest primaryTitle in the
> data I have loaded is 419 characters.
Excellent, Kevin, thanks. That's a clear description of a concrete
problem, and good example, too.
Here, IMDB does not specify an upper limit. In theory, the plot
summary for "Bird" could include the history of music back to the
Mesozioc. And short titles went out of fashion with James Michener.
But to see the data, they have to be displayed somewhere, and that
place has some finite size. Even if you're producing HTML and can put
"unlimited" data in a field, you still have to read it into some
buffer N times, until you reach the end.
I would define my database with a title of varchar(500). If a bigger
title comes along, I would deal with it initially just by truncating
the title, and later by altering the database. Because, you know,
YAGNI.
For plots, I would have a separate table with a foreign key back to the
main table, something like;
create table plots (
movie_id integer not NULL,
ordinal integer not NULL,
paragraph varchar(1000) not NULL,
primary key (movie_id, ordinal),
foreign key (movie_id) references (movies.movie_id)
);
where "ordinal" is the paragraph number and varies from 0 to N, where N
is roughly
the plot size % 1000 +1
I might decide to break plot rows by sentence, so it's easier to
display them one at a time.
With that design, I could do something like:
select movie_id, max(ordinal) as N
from plots group by movie_id;
to signal to the application how many plot paragraphs there are. I
could fetch the first one by default and others on demand, or just
join the tables to get everthing everywhere all at once. But with a
known size. :-)
By the way, I didn't invent this idea. Sybase and Microsoft use a
similar techinque for the text of stored procedures, which Sybase first
defined somewhere around 1988.
HTH.
--jkl
