YAFLogo

parkerseal
  • parkerseal
  • 65% (Friendly)
  • YAF Camper Topic Starter
7 years ago
At the moment, "Host Settings / Host Setup / Use file table" isn't ticked so our attachments are all stored as files in the uploads folder.

I'd like to change this, and store the attachments in SQL server, mainly so that I can use full-text search on PDFs.

I assume that this means that yaf_Attachment.Filedata will need to be populated with the file contents. Does anyone already have a script that will do this?

View All Posts by User
Sponsor
tha_watcha
  • tha_watcha
  • 100% (Exalted)
  • YAF.NET Project Lead 🤴 YAF Version: 4.0.0 rc 2
7 years ago

At the moment, "Host Settings / Host Setup / Use file table" isn't ticked so our attachments are all stored as files in the uploads folder.

I'd like to change this, and store the attachments in SQL server, mainly so that I can use full-text search on PDFs.

Originally Posted by: parkerseal 

But when you store attachments in the db you wont be able to search through such files. The files are stored as raw data

I assume that this means that yaf_Attachment.Filedata will need to be populated with the file contents. Does anyone already have a script that will do this?

Originally Posted by: parkerseal 

No such script does not exist, but you don't need to convert old attachments

View All Posts by User
parkerseal
  • parkerseal
  • 65% (Friendly)
  • YAF Camper Topic Starter
7 years ago
Thanks.

But when you store attachments in the db you wont be able to search through such files. The files are stored as raw data

Oh yes I can, although I may need to tweak the YAF search! I've got SQL searching PDF data, with help from this article .

What I've done so far:

[list]

  • Install the Adobe PDF Ifilter and make sure SQL recognises it
  • Put some pdf data in FileContent for a few yaf_attachment records:
    • update yaf_Attachment 
set FileData=
   (select pdf.bulkcolumn from openrowset(bulk 'E:\temp\Newsletter 2014-2.pdf',single_blob) pdf )
where AttachmentID=172
  • Add a computed column "FileType" to yaf_attachment to show the file extension
    • [FileType]  AS (lower(reverse(substring(reverse([filename]),(1),charindex('.',reverse([filename])))))
  • Use the SQL fulltext wizard to create a fulltext index on filedata / filetype
    • [/list]

    I can then run queries like:

     select * from yaf_Attachment where contains(FileData,'flooding')
    and they work fine - searching the pdf data in FileData

    So far so good!

    View All Posts by User
    Similar Topics
    Users browsing this topic