bodington-developers

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

[Peter C. <Pet...@me...>]

> From: Colin Tatham
> You need a root folder for each resource before you can=20
> upload anything.

OK.

> Without looking the code again, I think the null parent id indicates=20
> that it's the root folder for that resource. I don't remember=20
> it having a blank name...

If you get a chance, could you check the expected name on your (live or
test) install?  It wouldn't surprise me if the name was set to the
resource ID, for example; that would be enough to disambiguate.

> Don't know if I'm being thick, but what are you trying to do?=20

Upload a file through the normal user interface to a pigeonhole; and the
same for a logbook.  Both of these while testing work I've done for
Leeds.  Both work for the first resource that contains uploaded files,
and both fail for subsequent resources.

		- Peter

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

[Peter C. <Pet...@me...>]

> From: bod...@li...=20
> [mailto:bod...@li...] On=20
> Behalf Of Jon Maber
> What is the definition of the unique index=20
> 'uploaded_files_unq_name'? in=20
> your database Peter?

To quote the database scripting tool on my SQL Server 2000 installation:

CREATE UNIQUE INDEX [uploaded_files_unq_name] ON
[bod].[uploaded_files]([parent_uploaded_file_id], [name]) ON [PRIMARY]

		- Peter

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

[Peter C. <Pet...@me...>]

> From: Matthew Buckett
> Nulls don't end up in the index on PostgreSQL

Ah.  Oh.  Oh dear.

> Sounds like this is a PostgreSQL/MSSQL/HSQLDB difference.

It is.  Not sure whether / which way ANSI mandates this, but (as we've
seen) the behaviour cannot be relied upon.

		- Peter

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

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

Peter Crowther wrote:
>> From: Matthew Buckett
>> Nulls don't end up in the index on PostgreSQL
> 
> Ah.  Oh.  Oh dear.
> 
>> Sounds like this is a PostgreSQL/MSSQL/HSQLDB difference.
> 
> It is.  Not sure whether / which way ANSI mandates this, but (as we've
> seen) the behaviour cannot be relied upon.

And MSSQL doesn't support partial indexes either?

CREATE UNIQUE INDEX uploaded_files_unq_name ON uploaded_files 
(parent_uploaded_file_id, name) WHERE parent_uploaded_file_id IS NOT NULL

-- 
  -- Matthew Buckett, VLE Developer
  -- Learning Technologies Group, Oxford University Computing Services
  -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/

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

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

Matthew Buckett wrote:
> Peter Crowther wrote:
> 
>>> From: Matthew Buckett
>>> Nulls don't end up in the index on PostgreSQL
>>
>>
>> Ah.  Oh.  Oh dear.
>>
>>> Sounds like this is a PostgreSQL/MSSQL/HSQLDB difference.
>>
>>
>> It is.  Not sure whether / which way ANSI mandates this, but (as we've
>> seen) the behaviour cannot be relied upon.
> 
> 
> And MSSQL doesn't support partial indexes either?
> 
> CREATE UNIQUE INDEX uploaded_files_unq_name ON uploaded_files
> (parent_uploaded_file_id, name) WHERE parent_uploaded_file_id IS NOT NULL

Ok I pulled out the indexes unless someone has a better solution for MSSQL.

-- 
 -- Matthew Buckett, VLE Developer
 -- Learning Technologies Group, Oxford University Computing Services
 -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/

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

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

Matthew Buckett wrote:
> Matthew Buckett wrote:
> 
>>Peter Crowther wrote:
>>
>>
>>>>From: Matthew Buckett
>>>>Nulls don't end up in the index on PostgreSQL
>>>
>>>
>>>Ah.  Oh.  Oh dear.
>>>
>>>
>>>>Sounds like this is a PostgreSQL/MSSQL/HSQLDB difference.
>>>
>>>
>>>It is.  Not sure whether / which way ANSI mandates this, but (as we've
>>>seen) the behaviour cannot be relied upon.
>>
>>
>>And MSSQL doesn't support partial indexes either?
>>
>>CREATE UNIQUE INDEX uploaded_files_unq_name ON uploaded_files
>>(parent_uploaded_file_id, name) WHERE parent_uploaded_file_id IS NOT NULL
> 
> 
> Ok I pulled out the indexes unless someone has a better solution for MSSQL.
> 

The only solutions I can find all seem to invlove allot of mess
(triggers, views, computed constraints).

http://www.sql-server-performance.com/q&a133.asp

-- 
 -- Matthew Buckett, VLE Developer
 -- Learning Technologies Group, Oxford University Computing Services
 -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/

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

[Colin T. <col...@ou...>]

Peter Crowther wrote:
> If you get a chance, could you check the expected name on your (live or
> test) install?  It wouldn't surprise me if the name was set to the
> resource ID, for example; that would be enough to disambiguate.

Seems to set it to an empty string for the root folder, but I'll have a 
closer look...




-- 
____________________________________
Colin Tatham
VLE Team
Oxford University Computing Services

http://www.oucs.ox.ac.uk/ltg/vle/
http://bodington.org