SQL anyone?

[iPlayer]

Hey there,

So I'm getting pretty close to porting functionality that depends on accessing a remote SQL database.

In ES I used sqlite3 for local databases and pypyodbc for remote databases. The latter was chosen as the one written in pure python, supporting Python 2.5 and being one python file - thus it was simple to make them friends with ES. However, I suppose using ODBC is not fancy when it comes to Linux servers.

I decided to check what SP supports and didn't find anything related to at least something that can interact with MySQL, let alone other databases. No mysql.connector, no pyodbc/pypyodbc, no pymysql.

So what's the native support for remote databases?
Why not include
cx_Oracle for Oracle
PyODBC specifically for MSSQL (if you run MSSQL, I can assume you have no problems with running your SRCDS on Windows)
PyMySQL or Apache MySQL Connector for MySQL
py-postgresql for PostgreSQL

And then, on top of this, SQLAlchemy?

At least PyMySQL and SQLAlchemy!

Because if there's no native support for remote SQL DBs now, well, what will we end up with? Every single plugin comes packed with its own version of PyMySQL?

[decompile]

Because if there's no native support for remote SQL DBs now, well, what will we end up with? Every single plugin comes packed with its own version of PyMySQL?

I would love having a native support for mysql dbs

[necavi]

There was talk of PyMySQL + SQLAlchemy but I wanted to make sure that everyone was onboard with it before adding them. Since then I've had almost no time to work on anything SP, unfortunately, but I'm entirely certain that these both could be added.

[iPlayer]

I didn't know there already was a talk about SQLAlchemy, and I'm glad I'm not the only one who needs it. Well, will be looking forward to seeing it shipped. But for now I'll have to somehow embed it by myself.

[Doldol]

Well there is a site-packages directory for this, it'd be silly to include any of the choices in the default SP installation/build package.

This isn't really part of the core functionality of SP, if you wanted to make it so it is then you'd need to come up with a threaded wrapper for it anyway.
I'd much prefer it if SP stuck to it's core functionality, it has that flexibility as it's not SM where 3rd-party libraries are not so easily integrated.

If you go down the route of including every package you think might be used then you'll never end. For databases alone, what if someone prefers NoSQL? I use MongoDB extensively in SP addons for example. So ship pymongo and pymysql and etc, etc? (You'll also have the pain of keeping this updated or in the best case linked in github.)

Edit: The only thing that is actually really needed is pip, so that needed packages can be installed programmatically without any real hassle (Saw there was talk of this, don't know how far progress on this is).

[iPlayer]

Well, I'll hapilly ship my own plugin with PyMySQL and SQLAlchemy, but what happens if every other plugin comes with its own stuff to the site-packages directory? One plugin uses PyMySQL vX.X, another one vY.Y, the third one vZ.Z etc. They will override each other and we will end up with conflicting plugins.

If we can't ship every existing SQL/NoSQL driver, we can still include SQLAlchemy. It supports like more drivers than one could ever need. It will cover all SQL-based DBs at once.


Edit: Saw you mentioned pip. You mean using pip directly from SRCDS console?

[Doldol]

Well, I'll hapilly ship my own plugin with PyMySQL and SQLAlchemy, but what happens if every other plugin comes with its own stuff to the site-packages directory? One plugin uses PyMySQL vX.X, another one vY.Y, the third one vZ.Z etc. They will override each other and we will end up with conflicting plugins.

If we can't ship every existing SQL/NoSQL driver, we can still include SQLAlchemy. It supports like more drivers than one could ever need. It will cover all SQL-based DBs at once.


Edit: Saw you mentioned pip. You mean using pip directly from SRCDS console?

Well you should aim to keep your plugin working with the most recent version of whatever library you need, if you still depend on a specific version you're a. Likely doing it wrong. & b. Still able to use the plugin's directory & import locally.
Besides I don't know any version of any widely used library that completely breaks imports etc while remaining under the same import name.

PIP: The console wouldn't make that much sense in this context, using it from within your plugin would be more appropriate (hell for this I could see an SP specific wrapper, maybe with the additional task of keeping these packages updated.):

Syntax: Select all

import pip
pip.main(['install', package_name])

[iPlayer]

What if we have two plugins and a lib

• Plugin A. Depends on the lib not older than version 1.0, shipped with version 1.0;
• Plugin B. Depends on the same lib, but version v2.0, because it needs the very latest features. Shipped with lib v2.0;
• Lib. Version 2.0 is backwards compatible with version 1.0.

If you install plugin A, then plugin B, everything's gonna be fine. You will end up with the lib v2.0, both plugins work well with it.
But if you install plugin A over plugin B, you will end up with the lib v1.0, and plugin B will break.

Nobody's doing it wrong here, both plugins work perfectly with the most recent version of the lib. It's just that one of the plugins was shipped with an older lib.

Even if plugin B doesn't break, we will still have implicit uncontrollable lib upgrades/downgrades every time you install a new plugin.

[satoon101]

Implementing pip is certainly planned. And instead of having a plugin use pip itself, we will have plugins use a requirements.txt or requirements.ini file which will utilize pip for non-SP specific packages. Also, plugins should never include custom packages or site-packages themselves.

And we only include in SP what SP actually uses itself. If there is a site-package that a lot of people might want, but it isn't necessary for SP's usage, it will not be included in SP itself.

[iPlayer]

Alright then, satoon. Guess for now I'll just install all this stuff for myself, and by the time I release the plugin to public, I hope pip would be implemented.

A bit off-topic: if I can't include a custom package myself, I believe I should post it in the Module/Package Submissions forum.
Then, say, it gets declined and makes its way into the Custom Packages forum instead of being shipped with SP. What's then? How can I use it in my plugins if it's not shipped and I can't include it by myself?

[satoon101]

That's exactly what Mahi has done with his EasyPlayer custom package. Then, you just require it for your plugin. Not sure where the issue is with this...

In the future, once we have the Source.Python Plugin Manager fully up and running, you will just use the requirements.txt or requirements.ini (still not 100% sure on the implementation, yet) to show any required packages, whether that be directly SP related custom packages or 3rd party packages on pypi. Also, SP custom packages will have their own section on SPPM so that they don't get mixed in with plugins.

[iPlayer]

Thanks, it's all clear for me now.