Ryangel's vB3 Journal -> vBJournal 1.0.0 Importer

[Oblivion Knight]

Introducing.. Jim Port! :D


Who is Jim Port?
Jim Port is the name that I gave to the import script..
Journal Import -> admincp/jimport.php -> Jim Port


What can Jim Port do for me?
He will automatically do the following:
Backup Ryangel's Journal database tables
Revert database changes made by Ryangel's Journal
Remove Ryangel's Journal templates
Remove Ryangel's Journal phrases
Install vBJournal database tables
Alter database tables for vBJournal compatability
Transfer data from Ryangel's Journal to vBJournal
Install vBJournal phrases
Install vBJournal templates

Are there any known issues to using Jim Port?
Obviously, the database tables aren't 100% compatible with each other.. Jim Port cannot identify which of the journal entries is the journal's latest, and therefore no data is inserted in to the latest entry title or date fields. This was a worthy sacrifice as the majority of users will update their journals on a common basis anyway, and once a new entry is posted the latest entries will continue to function as intended.

Jim Port currently cannot strip the slashes from entry or comment titles and text. As such, journal.php must be modified slightly in order to display these correctly and the edits have been documented in the readme. These edits will not have any side-effects on any new comments or entries made beyond this point.

Also, during the beta testing phase it was reported that any deleted journals (ie. if a user had deleted all of their entries) would still show up as empty, but the comments from these deleted entries would continue to be displayed in the Recent Comments section of the journal. There is no easy fix for this other than manual deletion..


What versions of Ryangel's Journal is Jim Port compatible with?
To cut a long story short, I don't know 100%. However, as long as you are running a ported vB3 version of Ryangel's Journal and not the original vB2 version, then this should work as it was intended.. I tested it on version 1.1d and at least one of the beta testers tried it on version 1.2 and both appeared to import flawlessly.


What versions of vBulletin is Jim Port compatible with?
It should work on ANY version of 3.0.0..


Jim Port looks similar to the v3arcade Installer, are they twins?
Yup, the journal import script is heavily based on the v3arcade Installer by John. I've tried to get in touch with him on numerous occasions about making sure it was ok with him, but currently remain unsuccessful. Should he wish me to cease using it, the zip will be removed and I will attempt to try and write this in some other way.

I feel that I have learnt a lot from his work, and would like to thank him for this.


Miscellaneous notes about Jim Port
Lines of code in admincp/jimport.php: 1816
Days to write: 4
Private beta testers: tmhall, DanielP (vb.nl)

The backed up Ryangel's Journal database tables *ARE NOT* removed automatically. This was intentional in case admins wanted something to fall back on should anything go wrong, and can be deleted manually if you are satisfied with the import process.


I'd like to buy Jim Port a beer!
Well, you can't buy him one as he's nothing but code, however, if you'd like to buy me a beer my Paypal address is obk395@yahoo.co.uk. All donations will be drunk with thanks! ;)

[Polo]

Thanks Oblivion Knight, I have seen alot of members ask for this.. :) Pretty sure they will be happy with this importer ;)

[sabret00the]

Congratulations on the release, nice work :)

[Cloud-Warrior]

Great job OBK, just editing files now... Spotted this in the readme.txt:


$text= unhtmlspecialchars(stripslashes($text));
$entry['date']= vbdate($vboptions['dateformat'], $entry['entrydate'], 1);
$entry['time']= vbdate($vboptions['timeformat'], $entry['entrydate']);


It doesn't exist, so i guess it should be:


$text= parse_bbcode2($entry['entrytext'], 0, $setting['allow_imgcode'], $setting['allow_smilies'], $setting['allow_bbcode']);
$entry['date']= vbdate($vboptions['dateformat'], $entry['entrydate'], 1);
$entry['time']= vbdate($vboptions['timeformat'], $entry['entrydate']);


And:

Files to edit: (3) - profile.php, includes/functions.php,
includes/adminfunctions_template.php, journal.php (vBJ)

Remove the admin... one.

[Oblivion Knight]

Great job OBK, just editing files now... Spotted this in the readme.txt:
$text= unhtmlspecialchars(stripslashes($text));
$entry['date']= vbdate($vboptions['dateformat'], $entry['entrydate'], 1);
$entry['time']= vbdate($vboptions['timeformat'], $entry['entrydate']);It doesn't exist, so i guess it should be:
$text= parse_bbcode2($entry['entrytext'], 0, $setting['allow_imgcode'], $setting['allow_smilies'], $setting['allow_bbcode']);
$entry['date']= vbdate($vboptions['dateformat'], $entry['entrydate'], 1);
$entry['time']= vbdate($vboptions['timeformat'], $entry['entrydate']);It should exist, as you're adding the top line of that code as an earlier edit..

I realise that these edits aren't in the correct order, but I was documenting them as I edited the necessary code.


Files to edit: (3) - profile.php, includes/functions.php,
includes/adminfunctions_template.php, journal.php (vBJ)

Remove the admin... one.Thanks, that edit was removed from the RC2 private beta for this release as it wasn't necessary.

[FleaBag]

Hell yeah, you know I've been waiting for this one. My site's members are gonna be wetting themselves now I can install your script! Thanks a lot, I really mean that!

[FleaBag]

OK here we go, heh. Any idea what this means mate? As I certainly don't...


Database error in vBulletin 3.0.5:

Invalid SQL:
UPDATE journals, bjournal_commentcount
SET journals.commentcount = bjournal_commentcount.count
WHERE journals.journal_id = bjournal_commentcount.journal_id

mysql error: You have an error in your SQL syntax near ' bjournal_commentcount
SET journals.commentcount = bjournal_commentcount.coun' at line 2

mysql error number: 1064

Date: Tuesday 29th of March 2005 04:01:03 PM
Script: http://dragonninja.co.uk/admin/jimport.php
Referer: http://dragonninja.co.uk/admin/jimport.php

[Oblivion Knight]

Are you using a table prefix? What is your MySQL version?

[Cloud-Warrior]

It should exist, as you're adding the top line of that code as an earlier edit..

I realise that these edits aren't in the correct order, but I was documenting them as I edited the necessary code.


Thanks, that edit was removed from the RC2 private beta for this release as it wasn't necessary.

Nice one - my mistake - I was looking for order :)

I'm having problems with step seven of the script, due to the huge tables I have... I've had to change the first two SELECT queries to:

SELECT u.username, u.ipaddress, j.journal_id FROM user as u left join journals as j on u.userid=j.journalist_id WHERE j.journal_id<>"";

SELECT u.username, u.ipaddress, c.comment_id FROM user as u left join journal_comments AS c ON u.userid = c.commenter_id WHERE c.comment_id <> "";

Otherwise they run for way too long and the script times out.

[Oblivion Knight]

I'm having problems with step seven of the script, due to the huge tables I have... I've had to change the first two SELECT queries to:

SELECT u.username, u.ipaddress, j.journal_id FROM user as u left join journals as j on u.userid=j.journalist_id WHERE j.journal_id<>"";

SELECT u.username, u.ipaddress, c.comment_id FROM user as u left join journal_comments AS c ON u.userid = c.commenter_id WHERE c.comment_id <> "";

Otherwise they run for way too long and the script times out.Aha, the script hasn't been tested on a huge forum before, so thanks for the feedback. I'll update them shortly..

Other than that, did everything work ok for you? :)

[Cloud-Warrior]

Still checking it out but looks good so far...

I had to update journal titles with a "update journals set journalname=concat(journalist,"'s Journal") where 1;"...

Now to figure out the reverse date ordering (people don't like that); I've just added my RSS 1.0 output bit as well...

Thanks OBK - I owe you a pint :)

[Oblivion Knight]

OK here we go, heh. Any idea what this means mate? As I certainly don't...Try the latest zip (v0.3 PUBLIC BETA).

I'm hoping that I've cracked this one for you.. :)


I had to update journal titles with a "update journals set journalname=concat(journalist,"'s Journal") where 1;"...You don't have to do that, though it's a nice idea. By default when a Journal is created in vBJournal if no title is entered it automatically becomes "x's Journal" without anything being entered in to that field.


Now to figure out the reverse date ordering (people don't like that); I've just added my RSS 1.0 output bit as well...I think this is what you're looking for..

https://vborg.vbsupport.ru/showpost.php?p=572482&postcount=552


Thanks OBK - I owe you a pint :)I'll hold you to that! ;)

[womensden]

where can I dl RyAngel's journal? I've been looking for a journal program (it's taking a while to make our own as we dont have much spare time).

[Oblivion Knight]

where can I dl RyAngel's journal? I've been looking for a journal program (it's taking a while to make our own as we dont have much spare time).It wasn't publicly released here because permission was never received from Ryangel (the original author) to port his vB2 version to vB3.

[zetetic]

where can I dl RyAngel's journal? I've been looking for a journal program (it's taking a while to make our own as we dont have much spare time).
The purpose of this hack is to import data from RyAngels journal into vBJournal (https://vborg.vbsupport.ru/showthread.php?t=69538), which I believe is currently the best supported journal system. You might want to give it a look.

[womensden]

ahhh... ok. thanks for clearing that up.

[zetetic]

Sure thing. :)

[Cloud-Warrior]

OBK - question - what are the edits to journal.php for?

[Oblivion Knight]

OBK - question - what are the edits to journal.php for?Ryangel's Journal stores your entries and comments with the slashes and some weird HTML encoding still in the table for both the title and the text. For example: (It\'s warm in here &gt;_&gt;)

The journal.php edits are done to make these display correctly by stripping the slashes and unencoding the weird HTML stuff on-the-fly. So in the above example, this now shows as: (It's warm in here >_>)

[Cloud-Warrior]

Okay :) I was just checking, as I had to make some changes to my RSS 1.0 exporter for vBJournal and I had to add some of those strip slashes things in as well... Thanks.

[zetetic]

Ryangel's Journal stores your entries and comments with the slashes and some weird HTML encoding still in the table for both the title and the text. For example: (It\'s warm in here &gt;_&gt;)

The journal.php edits are done to make these display correctly by stripping the slashes and unencoding the weird HTML stuff on-the-fly. So in the above example, this now shows as: (It's warm in here >_>)
Oops, that just caused me a little confusion when I went to modify journal.php to parse bbcode in the recent comments section, as I explain here (https://vborg.vbsupport.ru/showpost.php?p=634518&postcount=1007).

Another thing I just noticed is that I ended up with duplicate "jBuddy List" sections in the memberlist template. You might want to tell people to skip that template edit.

[Oblivion Knight]

Another thing I just noticed is that I ended up with duplicate "jBuddy List" sections in the memberlist template. You might want to tell people to skip that template edit.Do you mean the modifylist template? Ryangel's Journal doesn't edit the memberlist template IIRC.

I'll make a note of it.. :)
I forgot since I've edited the Journal to use the Buddy List instead of using a separate jBuddy List.

[Reeve of shinra]

Cloud-Warrior -- Is the RSS exporter for the vbjournal released here? And what changes did you have to make?

[zetetic]

Do you mean the modifylist template? Ryangel's Journal doesn't edit the memberlist template IIRC.

I'll make a note of it.. :)
I forgot since I've edited the Journal to use the Buddy List instead of using a separate jBuddy List.
Doh! Yeah, that's what I meant. I also just posted on Anton's thread about a few missing phrases in his install routine that are missing in yours too.

Under Control Panel Stop Messages:

journal_mood_deleted_successfully --> Mood deleted successfully.
journal_mood_updated_successfully --> Mood updated successfully.
journal_inserted_mood_successfully --> Inserted {1} successfully.

:)

[zetetic]

Also I'm pretty sure RyAngel's hack came with a journalupdate.php file in forum root that can be deleted. If it didn't come with that hack I'm not sure where I got it.

Just let me know when I've pestered you enough. :D

[Oblivion Knight]

It's all useful feedback Tom, cheers! :)

I'll add those phrases to the importer for future use. Ryangel's Journal did come with the journalupdate.php file, but you only needed to use it if you'd previously used the vB2 version of the journal on your database. I'll make a note of it though..


Zip will be updated shortly..

[zetetic]

Excellent. :)

[FleaBag]

Hey Oblivion_Knight, what's the URL of the step I need to go to to carry on from where I left off with the updated file?

[Oblivion Knight]

Hey Oblivion_Knight, what's the URL of the step I need to go to to carry on from where I left off with the updated file?I hope you didn't test this on your live forum, because ideally you need to run the importer from scratch with Ryangel's Journal tables set to their original names.. :nervous:

Please note that the changes mentioned on post #24 and beyond have not yet been implemented, I haven't had the time. When they are, the version will be changed to v0.31..

[FleaBag]

Yep on a live board, I've always had the luck of the Irish before lol. So am I screwed?

[Oblivion Knight]

Yep on a live board, I've always had the luck of the Irish before lol. So am I screwed?Never, ever use a beta hack on your live board without testing it first.. :p
It's very likely that any hack released in a beta state will be changed at least once before released as "final".


You're not completely screwed, but you're making life difficult for yourself.. Remove the database tables journal_comments, journal_entries, journal_moods, journal_settings and journals. Once you've done that, remove the "b" from the database tables bjournal, bjournal_comments and bjournal_entry. If bjournal_commentcount exists, remove it.


You may need to run these queries too..
ALTER TABLE user DROP canhavejournalALTER TABLE usergroup DROP journalpermissionsALTER TABLE session DROP injournalAfter that, you should be able to run the import script once again. You may want to wait for the next version though.. I'll have it done within the next hour or so.

[FleaBag]

Thanks a lot for your support, especially considering I went ahead and did something blatantly dumb that I know I shouldn't have. The problem I have is that my vB database is 400 meg, it's a bit of a bugger to back up as regularly as I'd like to - my host keeps backups on my behalf but his response time is 1-3 weeks. But I will learn from my mistakes! I'll get onto undoing my actions as per your advice and will wait for the new release. ;)

Looking forward to getting your journal running!

[Oblivion Knight]

Under Control Panel Stop Messages:

journal_mood_deleted_successfully --> Mood deleted successfully.
journal_mood_updated_successfully --> Mood updated successfully.
journal_inserted_mood_successfully --> Inserted {1} successfully.

:)journal_inserted_mood_successfully already exists, but the others have been added in v0.31 of the importer.. :)


Thanks a lot for your support, especially considering I went ahead and did something blatantly dumb that I know I shouldn't have. The problem I have is that my vB database is 400 meg, it's a bit of a bugger to back up as regularly as I'd like to - my host keeps backups on my behalf but his response time is 1-3 weeks. But I will learn from my mistakes! I'll get onto undoing my actions as per your adive and will wait for the new release. ;)

Looking forward to getting your journal running!Don't worry about it, everyone has different circumstances.. ;)
However, before using any script that automatically runs queries on your live database I would recommend backing up beforehand and/or testing it on a seperate test board.

vBJournal isn't my Journal script, but hopefully I will be working with AN-net at some point on the project.

v0.31 of Jim Port is now released.. :)

[FleaBag]

Aha sorry a little confusion on my part there. That's all those brain cells dying from substance abuse lol. It actually just occured to me that I can just make a dump of those tables as a backup... Silly me. Will fire it up in a moment!

[FleaBag]

Success! I had to edit the jimport a little to remove certain steps but it all worked like a dream from thereon in! I've now got the journal script up and running 100% with all of my existing entries. Thanks a whole lot Oblivion_Knight. :)

[Oblivion Knight]

No problem, and for future reference there's no "_" in my name.. ;)

[FleaBag]

Oops... What am I like lol. :D

[zetetic]

I can't remember if I created this myself or if it was part of RyAngel's hack, but I had a phrase called viewing_journals --> Viewing Journals and matching entries in functions_online so Who's Online would be accurate when people were in journals. That phrase seems to have been removed by the importer.

[Oblivion Knight]

I can't remember if I created this myself or if it was part of RyAngel's hack, but I had a phrase called viewing_journals --> Viewing Journals and matching entries in functions_online so Who's Online would be accurate when people were in journals. That phrase seems to have been removed by the importer.Yup, the importer removes any phrases that contain the words "journal" or "jbuddy".

I'm afraid you'll have to re-add these (they sound like custom ones to me).

[zetetic]

No worries, already taken care of. Thanks. :)

[Cloud-Warrior]

Yep the RSS export is somewhere in the original vBJournal thread, link from here:

http://www.johnbreslin.com/blog/2004/10/21/rss-10-export-for-vbjournal-module/

[Ghostsuit]

Ok jsut done this on a test board and I'm having a little problem.

The main page for a users journal takes over a minute to load

Page generated in 82.32792211 seconds (0.36% PHP - 99.64% MySQL) with 18 queries

Any idea what might be causing that?

[Ghostsuit]

Ok done a bit of digging and getting

Query:

SELECT journal_entries.entry_id, journal_entries.entrytitle,
journal_entries.entrytext, journal_entries.mood, journal_entries.allowbuddies,journal_entries.entry date,
journal_entries.entry_totalvotes, journal_moods.mood_name, journal_entries.ipaddress,journal_moods.mood_image , journal_entries.entry_totalrating,
journal_entries.private, journal_entries.whocanview,
COUNT(journal_comments.comment_text) AS comments
FROM journal_entries AS journal_entries
LEFT JOIN journal_comments AS journal_comments ON (journal_entries.entry_id=journal_comments.entry_i d)
LEFT JOIN journal_moods AS journal_moods ON (journal_moods.mood_id=journal_entries.mood)
WHERE journal_entries.journal_id='3'
AND entry_active='1'
GROUP BY journal_entries.entry_id
ORDER BY entrydate ASC
LIMIT 0,10

Time before: 1.1279599666595
Time after: 15.869854927063
Time taken: 14.741894960403

table type possible_keys key key_len ref rows Extra
journal_entries ALL 16789 Using where; Using temporary; Using filesort
journal_comments ALL 13251
journal_moods eq_ref PRIMARY PRIMARY 4 journal_entries.mood 1

Should it really take that long for that process? I'm thinking not and I jsut found if I click one of the members with 900 entrys the time taken seems to be about 250 seconds at that point so I think somethign definitely up with that querie :S.

[Ghostsuit]

Paul M found the solution to my problem it's to do with Indexes on Journal we have over 16,000 entries so required indexes anyway this problem was nothing to do with the rygel transfer but a actual problem with the original Journal script. You can find my fix over in the journal thread around post 1095

[Oblivion Knight]

Thanks for the follow-up Ghostsuit.! :)

[Kaelon]

Paul M found the solution to my problem it's to do with Indexes on Journal we have over 16,000 entries so required indexes anyway this problem was nothing to do with the rygel transfer but a actual problem with the original Journal script. You can find my fix over in the journal thread around post 1095

And an excellent fix that they are. Added the three indexes, and performance has gotten so much better.

[Kaelon]

Is there any way we can use this script with an already up-and-running vB3.5 version of AN-Net's vBJournal?

[Reeve of shinra]

You probably could but just be careful since this script will drop any existing tables (so backup! backup! backup!).

edit:

If you already have a running journal on 3.5 and your looking to import these older journals...

- backup your current db
- restore it somewhere else
- do a table dump of the 3.5 joural tables
- rename those tables to get them out of the way for a few min
- run the converter to import the old journals
- dump those tables
- in excel or whatever the open office spreadsheet program is, copy the newly imported journals into the 3.5 journal tables you downloaded.
- fix the autoincrement ids
- save as csv and reimport that to your db.

[Oblivion Knight]

Is there any way we can use this script with an already up-and-running vB3.5 version of AN-Net's vBJournal?Untested, unsupported and unlikely.. :speechless:


You probably could but just be careful since this script will drop any existing tables (so backup! backup! backup!).Eh? No it doesn't.. :p

[kgirl]

I've just tried running this, and got the following error


Database error in vBulletin 3.0.6:

Invalid SQL:
INSERT INTO journals (journal_id, journalist_id, journaldate, journalviews, entrycount, active)
SELECT journalid, userid, timestamp, views, entries, enabled
FROM bjournal
ORDER BY userid ASC

mysql error: Duplicate entry '3' for key 1

mysql error number: 1062


Not being an expert, I've no idea what to do. I've searched here and couldn't find anything.

Can anyone help PLEASE!! :cry: