/ Development  

A dive on how long a long text is

Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

This time we do a quick dive (on database level) on the ‘Long text’ property that you can create on the property building for an entity. The question we had was: “How long can this property really be?” as you can even make it a rich-text area where you are able to make it a full-blown formatted information block.

longtext_001


Let get right into it…

First make sure you have a property on an entity of type ‘Long text’ like the image in the introduction and also make it available on a form. In my case it is the ‘Category’ entity with the ‘cat_description’ property on the ‘Create’ form!

longtext_002

Now create (in the front-end) a new instance of the ‘Category’ entity and add some rich-text elements to the description. Just add all kind of things so we have something to check in the back-end!

Once created we’ll have a look into the database


For the back-end…

We start with a tool called HeidiSQL. Just download en install it on your local computer (or use the portable version). With this tool we can connect to our PostgreSQL database where AppWorks saves all its data!

longtext_003

The password for my ‘postgres’ user is ‘admin’; created during installation

First we’ll query for the tables that belong to our project ‘AppWorksTips’: SELECT * FROM pg_catalog.pg_tables WHERE tablename LIKE '%appworkstips%';

You can use the <F9> key to execute the query!

longtext_004

Next step is to query our ‘Category’ entity table: SELECT * FROM o2appworkstipsappworkscategory;

Now we get somewhere when we take a look at the ‘cat_description’ column where we played with the rich-text component!

Where this:

longtext_005

Results in this (formatted!) with a character length of 428 (got this number from notepad++)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Hi <em><u>there</u></em>,
<br>
<blockquote>
<p>My name is 'HelloWorld'...
<br>
</p>
</blockquote>These are my <strong>bullets</strong>:
<ul>
<li>Item 1
<br>
</li>
<li>Item 2
<br>
</li>
</ul>
<hr>
<table style="width: 100px;" border="1" cellspacing="1" cellpadding="1" summary="summary">
<caption>caption</caption>
<tbody>
<tr>
<td>
<br>
</td>
<td>
<br>
</td>
</tr>
<tr>
<td>
<br>
</td>
<td>
<br>
</td>
</tr>
</tbody>
</table>
<br>

Now for the great question as we want to know how many characters the ‘cat_description’ column get have. For these we need to know the table column information for that ‘o2appworkstipsappworkscategory’ table.

SELECT COLUMN_NAME, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_precision_radix, numeric_scale, udt_name FROM information_schema.COLUMNS WHERE TABLE_NAME = 'o2appworkstipsappworkscategory';

longtext_006

It’s getting interesting!

We can see the ‘character_maximum_length’ is (NULL), but when we do the same kind of calculation like the other rows (e.g. 160 * 2 / 8) you get 1.073.741.824 * 2 / 8 = 268.435.456‬ characters!

To put it in context; A medium book has 20.000 words with an average word size of 5 chars; That’s 100.000 characters. 268.435.456 / 100.000 = 2684 medium sized books!…I think that should be sufficient for 1 rich-text field.

Also, the this DataType reference page gives you some more information for the other types also!


There we have our easy earned ‘DONE’ this time. Can’t always be complex, but this gives you some nice insides what is happening on database level behind the AppWorks platform. Good to know where to find this kind of information. Have a good one for today and CU on our next post…Cheers!

Don’t forget to subscribe to get updates on the activities happening on this site.