mORMot Open Source

PierceNg

Member

From: Singapore

Registered: 2023-01-29

Posts: 10

Website

mvc-blog using Postgres - external content FTS4/5 table exception

Hi,

I modified the mvc-blog example's MVCServerFirebirdIbx.dpr to use Postgres.  Totally same code, only changing TsqlDBIbxConnectionProperties to TSqlDBPostgresConenctionProperties. The program runs - I can see the tables being created in the database - but then fails with following:

EModelException: TOrmArticleSearch is an external content FTS4/5 table but source Article is not a local SQLite3 table: FTS search will be unavailable
  $00000000005A7BB1  InitializeTable,  line 8940 of ../../src/orm/mormot.orm.core.pas
  $00000000007C4570  CreateMissingTables,  line 1603 of ../../src/orm/mormot.orm.sqlite3.pas
  $0000000000402A8C  $main,  line 54 of MVCServerPostgreSQL.lpr

What should I look at to fix?

Program built with Lazarus 2.2.4 with FPC 3.3.1 on Ubuntu 20.04. Postgres is official Postgres 15-Alpine Docker image running on the same host.

The standalone SQLite server works.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: mvc-blog using Postgres - external content FTS4/5 table exception

FTS4/5 is a SQLite3 specific feature.
If you want to use another DB, you need to disable this feature.

ttomas

Member

Registered: 2013-03-08

Posts: 135

Re: mvc-blog using Postgres - external content FTS4/5 table exception

Look at MVCModel.pas for {$ifdef/ifndef  USEFIREBIRD
External database can't use FTS feature of SQLite, TOrmArticleSearch is not defined for USEFIREBIRD.

PierceNg

Member

From: Singapore

Registered: 2023-01-29

Posts: 10

Website

Re: mvc-blog using Postgres - external content FTS4/5 table exception

FTS4/5 is a SQLite3 specific feature.
If you want to use another DB, you need to disable this feature.

Look at MVCModel.pas for {$ifdef/ifndef  USEFIREBIRD
External database can't use FTS feature of SQLite, TOrmArticleSearch is not defined for USEFIREBIRD.

Thank you both. From @ab reply, I saw that and fixed that part accordingly. The app runs. But it fails to insert the fake articles. Blog info, author info, tags etc were inserted, but the articles weren't, due to a Postgres insertion exception. The relevant log entries are here: https://gist.github.com/PierceNg/4e4e24 … 19c8458385

The key line should be this:

20230216 12033029  ! EXC   	ESqlDBPostgres {Message:"TSqlDBPostgresStatement.ExecutePrepared: 
Invalid array type ftBlob on bound parameter #10",
Statement:{Sql:"insert into public.Article (ID,CreatedAt,ModifiedAt,Title,Content_,ContentHtml,Author,AuthorName,PublishedMonth,abstract,Tags)

Parameter #10 should be 'abstract'.

In Postgres, the columns show up as follows:

- id, bigint NOT NULL
- createdat, bigint
- modifiedat, bigint
- title, text
- content_, text
- contenthtml, integer
- author, bigint
- authorname, text
- publishedmonth, bigint
- abstract, text
- tags, bytea

Last edited by PierceNg (2023-02-16 12:38:21)