Welcome Guest! To enable all features please Login or Register.
Options
View
Go to last post Go to first unread
Offline parkerseal  
#1 Posted : Tuesday, November 7, 2017 4:09:23 AM(UTC)
parkerseal


Rank: YAF Camper

Reputation:

Joined: 12/13/2013(UTC)
Posts: 13

Thanks: 3 times
Was thanked: 1 time(s) in 1 post(s)
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?
Sponsor
Offline tha_watcha  
#2 Posted : Tuesday, November 7, 2017 9:04:53 AM(UTC)
tha_watcha


Rank: YAF.NET Project Lead

Reputation:

Medals: Medal of Honor: Portal Puzzle Master: Portal Puzzle Master for DNNMedal of Honor Key: Given to pillars of the community who are key players in the YAF community and project.DNN Master: DNN Module ManBug Killer Medal of Honor: Medal for being a kick-ass bug killer!Medal of Honor for the Support King: Support King! Amazing asset to the YAF.NET Community.

Joined: 3/6/2010(UTC)
Posts: 3,735
Germany

Thanks: 70 times
Was thanked: 1149 time(s) in 979 post(s)
Originally Posted by: parkerseal Go to Quoted Post
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.


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

Originally Posted by: parkerseal Go to Quoted Post

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?


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

Offline parkerseal  
#3 Posted : Tuesday, November 7, 2017 12:22:04 PM(UTC)
parkerseal


Rank: YAF Camper

Reputation:

Joined: 12/13/2013(UTC)
Posts: 13

Thanks: 3 times
Was thanked: 1 time(s) in 1 post(s)
Thanks.
Quote:
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:
  • Install the Adobe PDF Ifilter and make sure SQL recognises it
  • Put some pdf data in FileContent for a few yaf_attachment records:
    Code:
    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
    Code:
    [FileType]  AS (lower(reverse(substring(reverse([filename]),(1),charindex('.',reverse([filename])))))

  • Use the SQL fulltext wizard to create a fulltext index on filedata / filetype


I can then run queries like:
Code:
 select * from yaf_Attachment where contains(FileData,'flooding')

and they work fine - searching the pdf data in FileData

So far so good!

Edited by user Tuesday, November 7, 2017 12:24:39 PM(UTC)  | Reason: add quote

Rss Feed  Atom Feed
Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error