Re: [Bodington-developers] Wtd: Expertise on uploaded_files table structure

[Matthew B. <mat...@ou...>]

In message <441...@ou...>
bod...@li... writes:
> Jon Maber wrote:
> > A change is needed urgently - removing the constraint would be the mo=
st=20
> > obvious first step. Was there a bug report that led to the addition o=
f=20
> > the constraint? Is there another way round that bug?
>=20
> Matthew would have to answer that. We had a lot of problems with=20
> Uploaded File behaviour, most notably where they all disappear from the=
=20
> display periodically, and our uploaded files table got itself pretty=20
> messed up. Matthew rewrote large chunks of code to sort that out, and=20
> we've had very few bug reports (they were fixed).

We had duplicate files, appearing which is what this index is designed to=
 fix
although the Java has also changed (belt and braces).

> There is a difference between our version of the index and that on Bod:
>=20
> Ours has the following:
>=20
> CREATE UNIQUE INDEX uploaded_files_unq_name ON uploaded_files=20
> (parent_uploaded_file_id, name)
> GO

Nulls don't end up in the index on PostgreSQL so it allows multiple entri=
es for
the root. Sounds like this is a PostgreSQL/MSSQL/HSQLDB difference.

> -- Only PostgreSQL supports partial indexes.
> --CREATE UNIQUE INDEX  uploaded_files_one_root ON uploaded_files=20
> (resource_id )
> --	WHERE parent_uploaded_file_id IS NULL
> --GO

This is to only allow one root folder for each resource as some of the re=
source
had two or more in our WebLearn deployment.

Matthew