Table bible_context cannot be created [#471064]

After installation, all tables are created except bible_context and the corresponding sequence.
Database is PostgreSQL 8.1.

The following errors found in error log when enabling the module:

[Sun May 24 01:54:16 2009] [error] PHP Warning:  pg_query(): Query failed: ERROR:  in valid byte sequence for encoding "UTF8": 0xd4da
[Sun May 24 01:54:16 2009] [error] \xcc\xe1\xca\xbe:  This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". in /srv/www/web10/web/includes/database.pgsql.inc on line 139
[Sun May 24 01:54:16 2009] [error] PHP Warning:   in /srv/www/web10/web/includes/database.pgsql.inc on line 160

Comments

Comment #1

MikeLee_Birdy CreditAttribution: MikeLee_Birdy commented 24 May 2009 at 08:25

Dear Sir:
I'm not so sure that how postgresql works. Please ref http://drupal.org/node/434802 to see if this could help.
MikeLee

Comment #2

thinkact CreditAttribution: thinkact commented 30 May 2009 at 18:27

Hi Mike,

Thank you for your prompt reply! I can see the actual error after applying the patch as suggested.

After some trial and error, this great module now finally shows its great features!

The text datatype cannot define with a length in PostgreSQL. So, the column "context" in the schema for table "bible_context" in file "bible.install" line 103 has to be changed from
```
'type' => 'text', 'length' => 500, 'not null' => TRUE, 'default' => '',
```
to
```
'type' => 'text', 'size' => 'normal', 'not null' => TRUE, 'default' => '',
```
In PostgreSQL 8.1, index does not support the keyword "DESC". The index "idx_vlb" in "bible_context" in file "bible.install" line 109 can be changed from
```
'idx_vlb' => array('verse', 'linemark DESC', 'bid')
```
to
```
'idx_vlb' => array('verse', 'linemark', 'bid')
```
However, I'm not sure whether it is still applicable for the queries.
PostgreSQL is quite restrict on SQL syntax. It follows the SQL92 standard. The function "FIELD()" for ORDER BY is not supported.
As a temporary solution, I've made a hard-coded sort order by changing the following lines in "bible.module" near line 1067:
```
            $bid_list = 'FIELD(bid';
            foreach ($bibles as $bid) {
              $bid_list .= ', '.$bid;
            }
            $bid_list .= ')';
```
to
```
            //$bid_list = 'FIELD(bid';
            //foreach ($bibles as $bid) {
            //  $bid_list .= ', '.$bid;
            //}
            //$bid_list .= ')';
            $bid_list = 'bid';
```
There should be better solution, I guess.
From experts-exchange, there's a workaround to create a plsql function "field" to simulate the same function. However, I'm afraid not all PostgreSQL has plsql installed by default.

Henry

Comment #3

MikeLee_Birdy CreditAttribution: MikeLee_Birdy commented 30 May 2009 at 23:59

Dear Henry:
It's so great that you've finish this hard work....
I'll try to make this moudle become "sql-independent". But I'm not so well understand how PostgreSQL works. If you can tell me some way to modify so that the "sql-indendent" can work perfectly, I'll put them in the next version.

Let me explains what the above 3 section work:
1. The context length sets to 500, because come bible context has the VERY LONG verse. For the sql structure consideration, I have to set it 500.
And, I'm not sure that if make this change "size => normal" is suitable for mysql?
2. The "linemark" is some section title in some bible. For example, some context will add a "The first day creation" before GEN:1:1. So this index "idv_vlb" try to make sure that the verse can be arrange by the correct order.
3. This function is for multi-version read. Please test this function after you've import more than one bible. Since this section is to restrict that show user-select bible only, so I'm not sure that if code change like this.... I guess no matter what user choose the version, there's will show all versions.

I hope this explain can do some help. If you got any solution for PostgreSQL, please let me know.
MikeLee