Raised This Month: $119 Target: $400
 29% 

PostgreSQL Database Interface


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Peace-Maker
SourceMod Plugin Approver
Join Date: Aug 2008
Location: Germany
Old 03-04-2014 , 14:26   PostgreSQL Database Interface
Reply With Quote #1

This extension provides PostgreSQL database support to SourceMod. There is a bug report open for this, but i don't use pgsql so i can't test it in a production environment. Putting it up here to see if someone got a use for it and confirms it does what you expect.

The extension is currently compiled against postgresql 9.3.2.
It registers a "pgsql" database driver. To use this, just set the "driver" key in the databases.cfg section to "pgsql" like this:

Code:
    "pgtest"
    {
        "driver" "pgsql"
        "host" "localhost"
        "database" "my_database"
        "user" "user"
        "pass" "password"
    }
Installation
1. Drag & Drop

Due to syntax differences in the SQL flavours between MySQL, sqlite and PostgreSQL you can't be sure that all plugins will run out of the box with pgsql. You might need to slightly alter your queries depending on the used database driver. Just like you already have to do with different sql syntax between mysql and sqlite!

Latest builds against SM 1.10 and PostgreSQL 9.6.9 HERE.

This extension is compiled against SourceMod 1.6 and won't run on SM 1.5. This is due to some refactoring of the DBI system in 1.6 and me being too lazy to backport the extension to 1.5!
Attached Files
File Type: dll dbi.pgsql.ext.dll (115.0 KB, 79 views)
File Type: so dbi.pgsql.ext.so (179.8 KB, 81 views)
File Type: zip sm-pgsql.zip (2.66 MB, 190 views)
__________________

Last edited by Peace-Maker; 05-26-2018 at 07:31.
Peace-Maker is offline
hamza47sohail
Senior Member
Join Date: Jan 2014
Old 03-08-2014 , 15:21   Re: PostgreSQL Database Interface
Reply With Quote #2

Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?
hamza47sohail is offline
Oshizu
Veteran Member
Join Date: Nov 2012
Location: Warsaw
Old 03-08-2014 , 16:24   Re: PostgreSQL Database Interface
Reply With Quote #3

Quote:
Originally Posted by hamza47sohail View Post
Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?
Using google is not hard
http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL
https://www.scriptrock.com/articles/postgres-vs-mysql/
https://www.udemy.com/blog/mysql-vs-postgresql/
__________________
...
Oshizu is offline
Drixevel
AlliedModders Donor
Join Date: Sep 2009
Old 03-08-2014 , 17:44   Re: PostgreSQL Database Interface
Reply With Quote #4

Quote:
Originally Posted by hamza47sohail View Post
Sorry if this is a newb question but what is the big difference between MySql and PostgreSQL
Also do they use the same sql queries, format etc?
Aside from the links posted by Oshizu, just stick with MySQL if you don't already know what PostgreSQL is or how it works.
Drixevel is offline
SysQo
Junior Member
Join Date: Nov 2015
Old 01-10-2016 , 06:50   Re: PostgreSQL Database Interface
Reply With Quote #5

Can i recompile with 9.4 driver easily?
SysQo is offline
printf
Junior Member
Join Date: Apr 2013
Old 10-19-2016 , 11:24   Re: PostgreSQL Database Interface
Reply With Quote #6

(This is a repost from a PM I sent, in case anyone else has this issue)

Hello Peace-Maker,

I was wondering about the current state of the PostgreSQL driver for sourcemod. When I do threaded queries against it, the server crashes and gives me this error.

Quote:
Assertion Owner() != GetCurrentThreadId() failed.
The driver appears similar in structure to the MySQL driver, so I assumed that threading was implemented.

Thank you for your time,
printf

P.S. I forgot to mention that I also rebased it on 1.7-dev. The patches applied okay, and I didn't think there would be problems. (Since there's not much recent commit history to the MySQL driver)

EDIT2: It also happens when I rebase to 1.6-dev.

Last edited by printf; 10-20-2016 at 13:50.
printf is offline
Peace-Maker
SourceMod Plugin Approver
Join Date: Aug 2008
Location: Germany
Old 10-24-2016 , 05:11   Re: PostgreSQL Database Interface
Reply With Quote #7

This is compiled against PostgreSQL 9.6.0. Give it a try and see if it still crashes! It's compiled against SM master so requires SM 1.8+.
https://github.com/alliedmodders/sourcemod/pull/32
Attached Files
File Type: dll dbi.pgsql.ext.dll (783.5 KB, 27 views)
File Type: so dbi.pgsql.ext.so (627.1 KB, 29 views)
__________________

Last edited by Peace-Maker; 10-24-2016 at 05:16.
Peace-Maker is offline
printf
Junior Member
Join Date: Apr 2013
Old 10-27-2016 , 21:12   Re: PostgreSQL Database Interface
Reply With Quote #8

Quote:
Originally Posted by Peace-Maker View Post
This is compiled against PostgreSQL 9.6.0. Give it a try and see if it still crashes! It's compiled against SM master so requires SM 1.8+.
https://github.com/alliedmodders/sourcemod/pull/32
It still crashes for some reason, I have the backtrace if you need it:
Code:
(gdb) bt
#0  0xf774aae9 in __kernel_vsyscall ()
#1  0xf7470687 in __GI_raise ([email protected]=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:56
#2  0xf7473ab3 in __GI_abort () at abort.c:89
#3  0xf74697c7 in __assert_fail_base (fmt=0xf75a6df4 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", 
   [email protected]=0xea3dc23c "Owner() != GetCurrentThreadId()", 
   [email protected]=0xea3dc1f8 "/home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h", 
   [email protected]=140, [email protected]=0xea3dc2f7 <ke::Lockable::Lock()::__PRETTY_FUNCTION__> "void ke::Lockable::Lock()") at assert.c:92
#4  0xf7469877 in __GI___assert_fail (assertion=0xea3dc23c "Owner() != GetCurrentThreadId()", 
   file=0xea3dc1f8 "/home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h", 
   line=140, function=0xea3dc2f7 <ke::Lockable::Lock()::__PRETTY_FUNCTION__> "void ke::Lockable::Lock()") at assert.c:101
#5  0xea3c2d07 in ke::Lockable::Lock (this=0xea634390) at /home/jannik/dev/sourcemod-peace/public/amtl/amtl/am-thread-utils.h:140
#6  0xea3c2c02 in PgDatabase::LockForFullAtomicOperation (this=0x9459050) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:323
#7  0xea3c27e8 in PgDatabase::SetLastIDAndRows (this=0x9459050, insertID=0, affectedRows=0) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:168
#8  0xea3c22c4 in PgQuery::PgQuery (this=0xea633708, db=0x9459050, res=0xea633668) at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgBasicResults.cpp:305
#9  0xea3c29a9 in PgDatabase::DoQuery (this=0x9459050, query=0x97a7918 "SELECT SteamID, ccname, ccmsgcol, ccuse FROM players WHERE ccuse != 0") at /home/jannik/dev/sourcemod-peace/extensions/pgsql/pgsql/PgDatabase.cpp:252
#10 0xedb5e0e2 in TQueryOp::RunThreadPart() () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#11 0xedb84520 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#12 0xedb84578 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#13 0xedb844e4 in g_DbgReporter () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#14 0xedb84578 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#15 0xedb844e4 in g_DbgReporter () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#16 0xedb84520 in g_DBMan () from /servers/hlds/SteamApps/common/Counter-Strike Source Dedicated Server/cstrike/addons/sourcemod/bin/sourcemod.logic.so
#17 0x097a77f0 in ?? ()
printf is offline
Peace-Maker
SourceMod Plugin Approver
Join Date: Aug 2008
Location: Germany
Old 11-01-2016 , 01:32   Re: PostgreSQL Database Interface
Reply With Quote #9

I can't frequently fail to upload files to the forum. Try this one.
__________________
Peace-Maker is offline
printf
Junior Member
Join Date: Apr 2013
Old 11-02-2016 , 02:21   Re: PostgreSQL Database Interface
Reply With Quote #10

Quote:
Originally Posted by Peace-Maker View Post
I can't frequently fail to upload files to the forum. Try this one.
Thank you, that appears to have fixed the issues with threaded queries.

I've converted all of my queries to work in PostgreSQL, but I still get odd things like this in parsing:

Code:
row number 135 is out of range 0..134
...
row number 4 is out of range 0..3
...
row number 4292 is out of range 0..4291
Is there another bug, or is this because MySQL indexes starting from 1 and PostgreSQL indexes starting from 0?
printf is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 19:19.


Powered by vBulletin®
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Theme made by Freecode