Database pointers - Question

[justinbowser]

Where does SMF store the pointers linking messages with attachments?  I can see in the attachment table a pointer to the message but I don't see where in the message table it "knows" if and what attachment to display unless there is another table that keeps such pointers.

[Arantor]

The pointer you see is all it needs.

When you're in a thread, the first query gets the topic details, the second query gets 'the slice of messages we're viewing' (e.g. for the first page, get the first ten posts in the topic). Then for attachments it says, "I'm looking at (list of message ids), any attachments for those messages?"

The database can look in both directions for matches, and that pointer from attachments to messages is all it needs.

[justinbowser]

OK, thanks.  I guess I'm old school.  Back when I started programming (in another life) in the early 70s most of the time you had to do your own DB building and I always used pointers both ways for redundancy when I built a DB.  So if I replace the current attachment table (missing several thousand attachments) with a different "repaired" table then there is nothing else to do besides adding the missing attachments to the attachment directory?

Almost sounds too easy...

[Arantor]

As long as the right pointers exist (I.e. the messages exist and they're the right ones), sure. Bit more tricky if multiple attachment folders are in play.

Also pointers in both directions for a database has been discouraged since the early days of the relational data model in the 1970s.

[justinbowser]

Thanks.  Just a single attachment directory.

Well, that was the era I was programming in so it stands to reason what I know then would become obsolete!

[Arantor]

Then yes, it should generally work the way you expect.

[justinbowser]

OK.  I gave this a try and things didn't work quite right

I copied all of  the attachment files to the attachment directory on the "production" forum
I went into phpMyAdmin and executed the following:

Wiped out the table:

truncate bmwrorg_SMF_BU.backup_bmwrorg_SMF_BU_attachments;

CREATE TABLE bmwrorg_SMF_BU.backup_bmwrorg_SMF_BU_attachments LIKE bmwrorg_convert.bmwrorg_convert_attachments;

Got an error that the table already existed - fine with me

Load data into table from other DB:

INSERT INTO bmwrorg_SMF_BU.backup_bmwrorg_SMF_BU_attachments (ID_ATTACH, ID_THUMB,ID_MSG,ID_MEMBER,attachmentType,filename,file_hash,size,downloads,width,height) SELECT ID_ATTACH, ID_THUMB,ID_MSG, ID_MEMBER,attachmentType,filename,file_hash,size,downloads,width,height FROM bmwrorg_convert.bmwrorg_convert_attachments;

I got an error message complaining about attachmentType so executed:

INSERT INTO bmwrorg_SMF_BU.backup_bmwrorg_SMF_BU_attachments (ID_ATTACH, ID_THUMB,ID_MSG,ID_MEMBER,filename,file_hash,size,downloads,width,height) SELECT ID_ATTACH, ID_THUMB,ID_MSG, ID_MEMBER,filename,file_hash,size,downloads,width,height FROM bmwrorg_convert.bmwrorg_convert_attachments;

Command executed correctly and browsing the table it looks fine.  The attachmentType column is populated by zeros which is the same as all of them I checked on the table in the DB I loaded in.

I still show only a few hundered attachments even though the attachments table has 3633 entries and a copy of the files from the "fixed" attachment directory.

What did I miss?  Do I need to run the "Attachment Integrity Check" under "File Maintenance?"

[Arantor]

I'd suspect you have not inserted into the correct tables; your SQL indicates the "backup" was what you inserted into.

No, there isn't any such guide, but honestly the lookup is done based on the message id as I indicated. If it isn't right, the rest is either data in the wrong table, or the message ids don't align, for which I can't help you.

[justinbowser]

You were quick!  I deleted the inquiry as I realized this and imported into the right table and they show up now.  Arantor, I very much appreciate your help.

I now have more attachments in the converted forum but still not all of them so I am back to my original issue of trying to come up with a way to import just the attachments from the YaBB forum into the new SMF forum.  Any ideas or is there already a utility to accomplish this?

[shawnb61]

If there us only one folder, it might help to ensure that id_folder is 1 for all records.

It would help to understand the problem, background, & steps that led to the current problem.

[shawnb61]

One more note: Do not run the attachment integrity check when things are missing...   It's good at cleaning things up - but not the other way around...   And will delete illogical records, making it harder to repair.

[justinbowser]

shawn - The original issue is that when I ran the conversion from YaBB not all of the attachments were converted so I have been trying to figure out how to recover them.  When I converted only 400 or so attachments out of 8000+ attachments were converted.  I then created a test SMF forum and re-imported and let it run overnight and after 24 hours it was still going so I terminated it.

After that conversion attempt my attachment table was over 120000 and some attachments were duplicated 20+ times!  I wiped out that install and tried it again and stopped the conversion after the attachment directory entries were about a thousand over the number in the YaBB directory.  After looking at attachments in SMF I found that most of entries were duplicates.  Each attachment had between 2 and 5 dups so I manually deleted the duplicate attachments and now the SMF attachment directory has 3839 entries so it appears I am missing around 5000 attachments?

[shawnb61]

OK...  Fallout from a Yabb conversion.  What version of Yabb were you moving from?

Confirming - how many records did you have in the attachments table after the conversion?  (~400?)

And ~8000 files in the file system?

I suspect a better solution would be to find the gap in the converter & re-convert.

[justinbowser]

I converted from YaBB 2.612 to SMF 1.1.14 then upgraded to 2.0.17.

shawn - The first time through ran for almost 24 hours before I killed it and the attachments table was up to over 122,000 lines.  I did an ls | wc -l on the YaBB attachments directory and it counts only 8388 files!  I do ls | wc -l on the smf/attachments directory and get over 122000 files!  Looking at them showed some were 20 dups.  The second time it was 400 and change, this is what I went live with.  When I ran it a third time (on test forum) I killed it after the table was up to around 12k files.  This is when I manually deleted the dups and put the table from the test forum DB into the live forum DB.  I then copied the converted attachments to the live forum attachment directory.

Every time I ran the conversionI had different results with attachments.

I have a post in the conversion board but no help there...

[justinbowser]

I suspect a better solution would be to find the gap in the converter & re-convert.

Could you shed some light on this?