Recent

Author Topic: Firebird-Table as federated MySQL-Table?  (Read 2983 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Firebird-Table as federated MySQL-Table?
« on: February 20, 2019, 09:14:03 pm »
Hiho,

before you start in on me: Yes, i've read the MySQL-Manual, that MySQL accepts only its own brethren for a federated table,
but during my research i found  a reference to DBIx::MyServer
http://web.archive.org/web/20141113060908/http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/dbixmyserver.html
https://metacpan.org/pod/distribution/DBIx-MyServer/examples/myserver.pl

The Author explicitly claims that you can create federated MySQL-Tables to other DBMS (SQLite, PG etc.), basically any DBMS you have a DBD-Diver for.
And yes, i've seen his Security-Warning, if you open up this server to listen outside of localhost
And what do you know? There is a DBD-Driver for Firebird
https://metacpan.org/pod/DBD::Firebird

Why am i asking?
I have a scenario with an application which uses a MySQL-Server. I've no Access to the source of this App. I've only Access to the MySQL-Server (via Workbench).
Porting/moving that App to Firebird is no option (commercial Software, but the MySQL-server belongs to me).
As we all know, MySQL has no Event-System contrary to Firebird.
Since i'm thinking about writing a Viewing-App for this MySQL-Server, i know i have a few options:

1) I don't know if this app uses a socket-broadcast to update its own viewer (Separate App which comes with the core-program, Windows only). I could use a port-sniffer to find out if yes, and which message is sent. If this App uses COM to contact its own viewer, then i'm toast with this approach. And plainly, i don't believe the App uses sockets.
2) Use a timer to refresh my Viewer in a defined interval. I admit, i'm loath to use this approach. It just doesn't make sense to me, firing off Update-requests every 5 seconds, just to catch new Data 10 minutes later.
3) Somehow manage MySQL to accept Firebird. Why? Because we all know Firebird has this Event-System, where you can send an Event back through the connection. In this case i could write some simple Triggers in the MySQL-Database, which updates this linked through federated Firebird-Table, which itself fires off a Trigger sending its event back.

Anyone heard of this DBIx::MyServer-Thingy? Any experience with it? Opinions?

EDIT: Ah, yes. I've seen that this thing is from 2010, so no idea if it even works with current incarnations of MySQL or Firebird
« Last Edit: February 20, 2019, 09:33:19 pm by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: Firebird-Table as federated MySQL-Table?
« Reply #1 on: February 22, 2019, 10:20:26 pm »
Hiho,

before you start in on me: Yes, i've read the MySQL-Manual, that MySQL accepts only its own brethren for a federated table,
but during my research i found  a reference to DBIx::MyServer
http://web.archive.org/web/20141113060908/http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/dbixmyserver.html
https://metacpan.org/pod/distribution/DBIx-MyServer/examples/myserver.pl

The Author explicitly claims that you can create federated MySQL-Tables to other DBMS (SQLite, PG etc.), basically any DBMS you have a DBD-Diver for.
And yes, i've seen his Security-Warning, if you open up this server to listen outside of localhost
And what do you know? There is a DBD-Driver for Firebird
https://metacpan.org/pod/DBD::Firebird

Why am i asking?
I have a scenario with an application which uses a MySQL-Server. I've no Access to the source of this App. I've only Access to the MySQL-Server (via Workbench).
Porting/moving that App to Firebird is no option (commercial Software, but the MySQL-server belongs to me).
As we all know, MySQL has no Event-System contrary to Firebird.
Since i'm thinking about writing a Viewing-App for this MySQL-Server, i know i have a few options:

1) I don't know if this app uses a socket-broadcast to update its own viewer (Separate App which comes with the core-program, Windows only). I could use a port-sniffer to find out if yes, and which message is sent. If this App uses COM to contact its own viewer, then i'm toast with this approach. And plainly, i don't believe the App uses sockets.
2) Use a timer to refresh my Viewer in a defined interval. I admit, i'm loath to use this approach. It just doesn't make sense to me, firing off Update-requests every 5 seconds, just to catch new Data 10 minutes later.
3) Somehow manage MySQL to accept Firebird. Why? Because we all know Firebird has this Event-System, where you can send an Event back through the connection. In this case i could write some simple Triggers in the MySQL-Database, which updates this linked through federated Firebird-Table, which itself fires off a Trigger sending its event back.

Anyone heard of this DBIx::MyServer-Thingy? Any experience with it? Opinions?
EDIT: Ah, yes. I've seen that this thing is from 2010, so no idea if it even works with current incarnations of MySQL or Firebird
If it still works, it's too risky!!! It could break security or downgrade performance of your main database server.

As an alternative, you could replicate some tables between MySQL and Firebird to accomplish your task.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: Firebird-Table as federated MySQL-Table?
« Reply #2 on: February 23, 2019, 07:15:54 am »
Thx valdir.
It's one of the most important reasons for me to not use that thing.
It's just frustrating that mysql doesn't have that event-system.
I just found out, that even SQLite has a Data change notification callback (albeit not implemented in FPC)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018