Skip to main content


 

Renaming a table


Hi !friendica Developers,
since I looked into the notifications when they briefly stopped working or 24 hours on my server, I'd like to rename the notify table to either notif or notification, what is the best way of doing this?
  • Renaming the table in DBStructure? I believe this would delete all the data when I just need a renaming.
  • Renaming the table in a post-update query? I'm not sure about the synchronization with the DBStructure change. We would need a pre-update query instead.
#database
This entry was edited (2 weeks ago)
I don't think that we currently do have a mechanism for this.

My first thought is that you'd have to create a new table, move the data into it, then drop the old table.

It's a good idea, but the update can't miss a step in this process or it's back to delete and create in the same movement with no data retention.

New idea: keep the old name in DBStructure with a simple pointer to the new name. This would prompt the updater to rename the table to the new name if it still exists.

If it doesn't then it wouldn't do anything.

For example:
  1.  $dbstructure = [
  2.   ...
  3.   'notify' => 'notification',
  4.   'notification' => [
  5.   {notification table description}
  6.   ],
  7.   ...
  8.  ];

Possibly we can create post- and pre- update functions.

But I wouldn't like to open just another topic. I would like to finish some open tasks first.

BTW: Currently the notifications seem to be partly broken. The name of the item owner is missing: " Hyp❄️l🎄te Pet❄️van kommentierte s Beitrag"

Could someone has a look at it?

My notifications are showing properly with both the author and the owner names.

Csn you just comment on this post? I have to test something ;-)


@lnxw48a1 Yeah, but I need them without addressing me with an "@" since this is another type.


Okay, bug fixed.


Possibly we can create post- and pre- update functions.

Yes this would be very useful

In fact we need three different types of update functions:

  • Functions that are called before the structure update
  • Functions that are called directly after the structure update
  • Functions that are called after the structure update - but people can already start working again. These functions are non blocking.

Sounds like a job for a well thought-out third-party library, maybe the Symfony upgrade system or from Laravel or CakePHP.

The above comment was meant for
In fact we need three different types of update functions:

tbh, out of federation code, everything else is a job for a well thought-out third-party library :-P

That's what I'm currently trying to sneak past Michael "I can do everything myself" Vogel.

I'm not a big fan of being dependent on third party libraries when the task doesn't contain security relevant stuff like cryptography or complicated stuff like OpenID, OAuth, ... then I prefer doing this on my own.

Take for example this database stuff. Of course there could be some library that could do nearly the same like the stuff that we are doing right now. But then there will be something that isn't done in a way that we need it, or it is much too complicate.

And: I must confess I do like building such stuff and I really, really hate to rely on stuff where I have to handle with lengthy documentations and such stuff.

Thing is: We are here for fun. And for me "fun" means peaking and poking in such stuff as well. The work with the "worker" and the new database routines also helped in understanding much database stuff that is helpful at other places.

*closing half a dozen tabs of third-party database abstraction/migration projects including a Github New Issue screen*

I hear what you're saying, but I feel like we should instead be working on implementing ActivityPub and rely on other people's past work on common tasks. Because there's nobody else who can do a Friendica ActivityPub implementation, but many people have already worked on Markdown lexers, database management tools, email sending and CSS frameworks.

I don't think that I will work on ActivityPub soon. Not because I don't have the time to do so, but because I do not understand the specifications. I will need some German speaking guy with very much patience (and technical understanding) to explain what I have to code there. Or I need some English speaking guy who will translate these "word deserts" into understandable stuff with many examples and very few words.

I'm totally on your side concerning stuff like Markdown converters, mail stuff and so on. This is something where you have to handle some standards and you cannot test them easily. Additionally I like to keep my hands away from any frontend stuff. And I will be really glad when we find a replacement for the BBCode parser.

Database stuff - on the other hand - is one of my favorites. I just love to do this stuff.

Thanks for clarifying your areas of enjoyment so my reforming hands can stay clear of them.

What about looking at Mastodon's implementation of ActivityPub? Would it be in a more understandable language to you?

As for the BBCode parser, I've discovered that the PHP project actually has had at least 3 libraries, PECL bbcode, PEAR HMTL_BBCodeParser and PEAR HTML_BBCodeParser2, but they apparently don't support attribute-style tags like the [share] and [attachment] tags.

I guess I will need some PHP. There is some work done at Hubzilla. So i could have a look there.

Can't the BBCode parsers be extended? (With some class magic, callback functions or whatever?)

P.S.: Yeah, I'm some hard headed guy in some points ;-)

Like I said when I joined Friendica late 2016, I used a custom BBCode lexer library for my own website that a friend wrote for it. In the same vein as a SAX, instead of using regular expressions, it parses the input text letter by letter which gives it more flexibility at the cost of intuitiveness. For example, each line break is converted according to the context (Open tag? Which one? Etc...).

For example, it was made to handle infinite tags nesting and generate nice HTML with adequate <p> tags instead of haphazardly sprinkled <br /> tags, as well as [img] tags with optional parameters for the alignment (float left or right) for example.

I will bring it to Friendica in time, when we're done with the 3-4 refactoring tasks we currently are performing at once, as I'm reluctant to add anything new to this codebase before it's in a nice clean state. It may very well never happen, but I personally having fun making it nicer step by step.

Test nested reply with latest develop.

It seems to work again.

I just checked the database and this reply has incorrectly been attached to the parent item.

That reply was placed correctly.

Yep, another self-fixing issue.

BTW: Concerning this whole renaming of table: I prefer a whole redesign instead. Many of our tables for example are having auto increment id fields. This is (in my opinion) a bad database design when there are fields that can serve as a unique key. See for example "config". The combination of "cat" and "k" is unique. You don't need this "id" field there.

Same mostly goes with tables that are related to other tables like "sign". This is related with the "item" table in a 1:1 relation. So you don't need "id" there.

And I guess for "notify" we can define better primary keys as well. So we shouldn't simply rename tables but create them new.

Are we talking about Friendica version 4? 😛

Speaking of database stuff... When I run mysqltuner on my database it tells me that we are doing a lot of joins without indexes. The proposed solution is to raise the join_buffer_size or always use indexes with joins.
I cannot raise the join_buffer_size any further. It is already set to 16MB per thread. Which is a ridiculously high setting. :-) So, can we do something about the join queries?

@Steffen K9 I know, we have to improve the database stuff. But the problem is that the system is so full of features and users won't give them up, I guess.

Right. Features are cool! :-D

@Steffen K9 and they are making the system slow.

Do you have a list of index-less joins? We aren't doing that many joins anyway, so this should be pretty easy.

I have enabled the log of joins w/o indexes yesterday. So yes, I can provide that information soon.

By having this list, we shouldn't blindly add indexes. We have to check the queries for their quality as well.