Database tables documentation

Sat, 02 Nov 2024 13:49:44 +0100

Hubzilla Support Forum

adminsforum@hubzilla.org

od wrote the following post Sat, 02 Nov 2024 13:49:47 +0100

Database tables documentation

Is there documentation anywhere to describe the purpose of each table in the hubzilla postgresql database?

show all 26 comments

Sat, 02 Nov 2024 14:13:54 +0100

sk@hub.utsukta.org

I could find this section in documentation which briefly describes what each table contains: https://hub.utsukta.org/help/database

Sat, 02 Nov 2024 14:14:30 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

/help/database

Sat, 02 Nov 2024 14:53:33 +0100

od@zotum.net

Channel id incremental count is now over 50. This is because of multiple instances of re-installation, accounts, channels, profiles, deleted and reused.
The database is now a mess. Does not matter as noone else affected.
Interestingly, discovered that even though the administrator account is deleted, able to sign in to a user account.
What needs to be deleted so that:
1. When entering the domain in the web browser, the process starts again with need to create an administrator account
2. All other history is deleted so that the channel id numbers start from 0?

Sat, 02 Nov 2024 16:07:48 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od
How many active accounts and channels are there currently on the hub?

I don't think that the consecutive number of channel IDs will lead to problems.

If a fresh and new admin account is required, simply register a normal fresh account and then enter the value 4096 in the ‘account’ table in the ‘account_roles’ column in the DB. The account is now an administrator account.

Otherwise, only deleting all tables and re-initialising the table with a subsequent update_db would lead to a complete reset of the hub. However, I don't know whether this would cause any subsequent problems (apart from the fact that all hub data would be lost).

Sat, 02 Nov 2024 19:07:18 +0100

od@zotum.net

Now only two, but curious to know which table is responsible for the automatic increase in 'account_id' every time a channel is deleted and then re-created with the same name.

Presumably the command 'DROP TABLE would do the deletion. A quick web search suggests to use a script.

How the command 'update_db' entered?

Is the '4096' value information in some administrator manual?

Sat, 02 Nov 2024 19:36:44 +0100

morph ⌂

morph@hub.morphtown.de

@od

Is the '4096' value information in some administrator manual?

Yes. This is said in the documentation for the case you want to create additional admin accounts.

Sat, 02 Nov 2024 19:42:15 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

Administration
Hubzilla FAQ
Site administration

Sat, 02 Nov 2024 20:00:59 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

How the command 'update_db' entered?

SSH...

util/update_db

Sat, 02 Nov 2024 20:13:48 +0100

Harald Eilertsen

harald@hub.volse.no

@od

but curious to know which table is responsible for the automatic increase in 'account_id' every time a channel is deleted and then re-created with the same name.

This is done automatically by the database, due to the data type of the column.

For Postgres:

CREATE TABLE "account" (
 "account_id" serial  NOT NULL,
 ...

For MySQL/Mariadb:

CREATE TABLE IF NOT EXISTS `account` (
 `account_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 ...

This is to ensure that each row in the table will have a unique id. The actual value of the id is irrelevant, and you should not mess with it.

Sun, 03 Nov 2024 10:50:32 +0100

od@zotum.net

@Der Pepe (Hubzilla) ⁂ ⚝

util/update_db

No idea what this means

In the directory '/var/www/hubzilladomainname/util'

There is no file 'update_db'

With command terminal 'pwd' '.../util''

./udall
Unable to update .../util

Sun, 03 Nov 2024 10:59:48 +0100

Harald Eilertsen

harald@hub.volse.no

@od Run ./util/udall first. This tool was added quite recently.

Sun, 03 Nov 2024 11:13:20 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

Sun, 03 Nov 2024 11:28:09 +0100

od@zotum.net

Unable to update

Sun, 03 Nov 2024 11:31:11 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

Unable to update

Why? Is there an error message?

Sun, 03 Nov 2024 11:34:23 +0100

od@zotum.net

Within hubzilldomainname directory:

"
./install/schema_postgres.sql
./install/schema_postgres.sql: line 1: syntax error near unexpected token `('
./install/schema_postgres.sql: line 1: `CREATE TABLE "abconfig" ('
"

Instruction from 'install/INSTALL.txt'
"
...
-  Database is populated.
        If not, import the contents of "install/schema_xxxxx.sql" with phpmyadmin
        or mysql command line (replace 'xxxxx' with your DB type).
...
"

Sun, 03 Nov 2024 11:43:55 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

@od

psql -U benutzername -d datenbankname -f schema_postgres.sql

Sun, 03 Nov 2024 11:46:48 +0100

od@zotum.net

See error posted, perhaps delay with addition to this thread

Sun, 03 Nov 2024 11:47:13 +0100

Harald Eilertsen

harald@hub.volse.no

@od Are you trying to use mysql to import the postgres database schema?

Btw, these instructions are only if the database has not been populated! Have you checked that first?

Sun, 03 Nov 2024 11:48:40 +0100

Alfred Bühler

abu@hub.alfredbuehler.ch

with phpmyadmin or mysql command line

Sun, 03 Nov 2024 11:54:13 +0100

od@zotum.net

Sorry my mistake. Looks that the documentation should be:

"
...
or database command line (replace 'xxxxx' with your DB type)e.g.
psql -U benutzername -d datenbankname -f schema_postgres.sql
"

Good documentation, yet again not valued, to attract more participants...

Sun, 03 Nov 2024 12:05:16 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

This does not belong in the documentation, but is the responsibility of the administrator (e.g. to find out about the function and operation of a database).

Sun, 03 Nov 2024 12:14:55 +0100

od@zotum.net

Disagree. All knowledge for hubzilla participants should be published in a open access. Let people decide for themselves, with _all_ the information available in the open, when they want to be a casual user, web server administrator, sometimes both.

Esoteric behaviour by programmers will not promote good software.

Sun, 03 Nov 2024 12:31:24 +0100

Harald Eilertsen

harald@hub.volse.no

@od Yeah, the install docs definitely need to be updated as well.

But knowing the basics about your own setup and how to administer the base components of your OS, web proxy, database etc, will be a bit beyond the scope of the Hubzilla docs. For less experienced sysadmins, I would suggest using something like Yunohost or docker that takes care of these things for you.

In fact I think the section you referred to should simply be removed from the docs. It's just confusing as it is incomplete.

Sun, 03 Nov 2024 13:14:36 +0100

Der Pepe (Hubzilla) ⁂ ⚝

pepecyb@hub.hubzilla.hu

I think that these explanations (on .htconfig.php and the database schema) already belong here and are sufficient.

Installing a hub manually is not necessarily something that should be recommended to an inexperienced user. However, if you have the basics, you will be able to follow the instructions and will be told what to do if the automatic setup process fails. How to edit a file (htconfig.php) and save it under a new name does not need to be explained. And the experienced admin should know how to import a database schema (or know where to look this up... namely in the documentation of the database they are using).

In my opinion, there is no harm in explaining simple steps in the user documentation (I even recommend it), but certain prerequisites should be assumed for the admin documentation... or at least the knowledge of where to look for information.

Sun, 03 Nov 2024 19:55:19 +0100

od@zotum.net

Good quality documentation, means more users and programmers. There are many examples: apache, debian, arch.
If extant hubzilla programmers continue to be unable to improve documentation, the quality and number of contributors will continue to be very low after all these years.

Sun, 03 Nov 2024 23:57:31 +0100

Scott M. Stolz

scott@biztechtonics.net

We have several people working on documentation.