PDA

View Full Version : Anyone also working PostgreSQL support?



wallace
2005-05-28, 01:26
Hi,

I tried start working on PostgreSQL support (using version 2005_05_24)

During porting the SQL schema, I ran into the first problem:

"offset" is a reserved word in PostgreSQL, but used in table "tracks".

Workaround: rename it into "aoffset" in schema and also in "Slim/DataStores/DBI/Track.pm" (hash: %otherColumns).

But afterwards I ran into the first show stopper problem:

2005-05-28 09:56:47.7184 Backtrace:

frame 0: Slim::DataStores::DBI::DBIStore::newTrack (/usr/local/slimserver//Slim/DataStores/DBI/DBIStore.pm line 480)
frame 1: Slim::DataStores::DBI::DBIStore::updateOrCreate (/usr/local/slimserver//Slim/DataStores/DBI/DBIStore.pm line 154)
frame 2: Slim::DataStores::DBI::DBIStore::objectForUrl (/usr/local/slimserver//Slim/Music/Info.pm line 988)
frame 3: Slim::Music::Info::cachePlaylist (/usr/local/slimserver//Slim/Utils/Scan.pm line 603)
frame 4: Slim::Utils::Scan::readList (/usr/local/slimserver//Slim/Utils/Scan.pm line 207)
frame 5: Slim::Utils::Scan::addToList_run (/usr/local/slimserver//Slim/Utils/Scan.pm line 147)
frame 6: Slim::Utils::Scan::addToList (/usr/local/slimserver//Slim/Music/MusicFolderScan.pm line 44)
frame 7: Slim::Music::MusicFolderScan::startScan (/usr/local/slimserver//Slim/Music/Import.pm line 41)
frame 8: Slim::Music::Import::startScan (/usr/local/slimserver/slimserver.pl line 1015)
frame 9: main::checkDataSource (/usr/local/slimserver/slimserver.pl line 540)
frame 10: main::start (/usr/local/slimserver/slimserver.pl line 560)
frame 11: main::main (/usr/local/slimserver/slimserver.pl line 1124)

2005-05-28 09:56:47.7188 Couldn't create track for file:///path/to/sound : Can't insert new Slim::DataStores::DBI::Track: Can't get last insert id at /usr/local/slimserver//Slim/DataStores/DBI/DBIStore.pmline 391
at /usr/local/slimserver//Slim/DataStores/DBI/DBIStore.pm line 391

Can't call method "setDirItems" on an undefined value at
/usr/local/slimserver//Slim/Music/Info.pm line 996 (#1)
(F) You used the syntax of a method call, but the slot filled by the
object reference or package name contains an undefined value. Something
like this will reproduce the error:

$BADREF = undef;
process $BADREF 1,2,3;
$BADREF->process(1,2,3);

Uncaught exception from user code:
Can't call method "setDirItems" on an undefined value at /usr/local/slimserver//Slim/Music/Info.pm line 996.
at /usr/local/slimserver//Slim/Music/Info.pm line 996
Slim::Music::Info::cachePlaylist('file:///mnt/sound/linked', 'ARRAY(0x8de7364)', 1112768601) called at /usr/local/slimserver//Slim/Utils/Scan.pm line 603
Slim::Utils::Scan::readList('file:///mnt/sound/linked', 'ARRAY(0x9b7c8ec)', 0) called at /usr/local/slimserver//Slim/Utils/Scan.pm line 207
Slim::Utils::Scan::addToList_run('ARRAY(0x94a1e70) ') called at /usr/local/slimserver//Slim/Utils/Scan.pm line 147
Slim::Utils::Scan::addToList('ARRAY(0x94a1e70)', '/mnt/sound/linked', 1, 0, 'CODE(0x94a384c)', 0) called at /usr/local/slimserver//Slim/Music/MusicFolderScan.pm line 44
Slim::Music::MusicFolderScan::startScan called at /usr/local/slimserver//Slim/Music/Import.pm line 41
Slim::Music::Import::startScan() called at /usr/local/slimserver/slimserver.pl line 1015
main::checkDataSource() called at /usr/local/slimserver/slimserver.pl line 540
main::start() called at /usr/local/slimserver/slimserver.pl line 560
main::main() called at /usr/local/slimserver/slimserver.pl line 1124


Caused by: CPAN/Class/DBI.pm

sub _auto_increment_value {
my $self = shift;
my $dbh = $self->db_Main;

# the DBI will provide a standard attribute soon, meanwhile...
my $id = $dbh->{mysql_insertid} # mysql
|| eval { $dbh->func('last_insert_rowid') }; # SQLite
$self->_croak("Can't get last insert id") unless defined $id;
return $id;
}

The existing code looks more like a bad hack to me :-(
Anyone know about a similar function call in PostgreSQL?

Or would it not even better to update the DBI interface, looks like the with slimserver shipped one is rather old.

wallace
2005-05-28, 01:29
Attached the sql files

pfarrell
2005-05-28, 08:04
On Sat, 2005-05-28 at 01:26 -0700, wallace wrote:
> I tried start working on PostgreSQL support (using version 2005_05_24)
>[snip]
> Or would it not even better to update the DBI interface, looks like the
> with slimserver shipped one is rather old.

It is great that you are trying to do the port.
I think your question will get better answers over on the
developers list


--
Pat
http://www.pfarrell.com/music/slimserver/slimsoftware.html

kdf
2005-05-28, 13:54
Quoting Pat Farrell <pfarrell (AT) pfarrell (DOT) com>:

> On Sat, 2005-05-28 at 01:26 -0700, wallace wrote:
> > I tried start working on PostgreSQL support (using version 2005_05_24)
> >[snip]
> > Or would it not even better to update the DBI interface, looks like the
> > with slimserver shipped one is rather old.
>
> It is great that you are trying to do the port.
> I think your question will get better answers over on the
> developers list

You'll need to get info from Dan specifially as to why the currently used
version of DBI was chosen. Hopefully it is possible to work around the 'offset'
reserved word somehow, as this might complicate things if the schema starts to
differ to much amongst version. I suppose that DBI should likely have some way
to map reserved words so that its all seemless again.

If you get to working results, please be sure to post what you end up with. It
may be well worth adding to the core code, or at least having someone host it
as a package.

-kdf

Dan Sully
2005-06-05, 22:51
* kdf shaped the electrons to say...

>You'll need to get info from Dan specifially as to why the currently used
>version of DBI was chosen. Hopefully it is possible to work around the 'offset'
>reserved word somehow, as this might complicate things if the schema starts to
>differ to much amongst version. I suppose that DBI should likely have some way
>to map reserved words so that its all seemless again.

That's what was available at the time - and if we upgrade, we have to
recompile for all the platforms we support, and those that we don't have to
upgrade manually. Additionally, I've looked at the recent changelogs for DBI,
and there are no changes that affect us. They mostly have to do with tests &
documentation.

On the Pg issue - you'll probably need to set a sequence at the
Slim::DataStores::DBI::* level for all the auto-increment keys. Something
like: $class->sequence('album_seq_next');

near the top of Album.pm, etc.

-D
--
<phone> i am a sausage fan

robinbowes
2005-06-06, 06:41
On the Pg issue - you'll probably need to set a sequence at the
Slim::DataStores::DBI::* level for all the auto-increment keys. Something like: $class->sequence('album_seq_next');

near the top of Album.pm, etc.

Go here: http://search.cpan.org/~timb/DBI-1.48/DBI.pm and search for "last_insert_id".

From what I can gather, the last_insert_id function is portable but requires different parameters for PgSQL, MySQL, SQLite, etc. I seems eminently possible to code around this, i.e. define parameters if a particular DB is being used.

R.