OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



[SOLVED] Cleaning my database.

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

[SOLVED] Cleaning my database.

Postby mbria » Sat May 06, 2006 5:29 pm

Hi all,

I made some tests and now I have 6 issues and close to 250 articles in my database and 50 users that I like to drop.

I found the same issue descrived here:
viewtopic.php?t=608&highlight=clean+database

... but I don't wanna belive there isn't a fastest way to deal with this. Archive and droping 200 articles isn't my idea about a good working morning, so I was thinking in:

a) Drop articles directly from the database.
b) Reinstall the full system and restore the base configuration (not articles and users)

To go with a), wich tables I need to drop to avoid inconsistences?
To go with b), any reference about how to backup and restore main information?

Wich one do you think is more feasible?

Thanks a lot in advance,

m.
Last edited by mbria on Thu Sep 07, 2006 2:39 am, edited 2 times in total.
mbria
 
Posts: 292
Joined: Wed Dec 14, 2005 4:15 am

Postby asmecher » Mon May 08, 2006 9:56 am

Hi mbria,

I've used several approaches when dealing with this sort of backlog:
  • Drop data from the database directly. This requires some background on table references.
  • Script actions using a tool like "wget" to simulate browser requests
  • Write a quick PHP script to accomplish the tasks
In this case, I'd suggest writing a quick bit of PHP. Copy index.php in the OJS root to something like cleanup.php, and append to the bottom:
Code: Select all
$articleIds = array(130, 131, 132, 133, 134, 135, 180, 182);
$articleDao =& DAORegistry::getDAO('ArticleDAO');
foreach ($articleIds as $articleId) {
    $articleDao->deleteArticleById($articleId);
}

If you replace the numbers in $articleIds with the actual article IDs you want to delete, and point your browser at cleanup.php, the articles will be deleted along with all dependent items. To accomplish the same with issues, look at the similar delete function in the IssueDAO class.

Regards,
Alec Smecher
Open Journal Systems Team
asmecher
 
Posts: 8321
Joined: Wed Aug 10, 2005 12:56 pm

thanks !!

Postby mbria » Mon May 08, 2006 4:25 pm

Extemely useful.

I will try.

Cheers,

m.
mbria
 
Posts: 292
Joined: Wed Dec 14, 2005 4:15 am

Solved: Worked like a charm ! :-)

Postby mbria » Wed May 10, 2006 11:35 am

Thanks. Your solution worked really well.

I modify a little your code because I had 2000 articles to delete and an for loop was better for me. Code pasted for newbies with a similar issue:

Code: Select all
$firstArticle=1;
$articleDao =& DAORegistry::getDAO('ArticleDAO');
for ($i = $firstArticle; $i <= 1830; $i++) {
    $articleDao->deleteArticleById($i);
    print ("Droped article: " . $i);
    print ("<br/>");
    //print_r($articleDao['_errorMsg']);
    print ("<hr/>");
}


If you get an ugly error like:

Fatal error: Call to a member function getJournalId() on a non-object in /var/www/test/atheneaDigital/classes/file/ArticleFileManager.inc.php on line 59


Don't worry... it means this article id isn't found, so just increase your $firstArticle variable to fit your begining article.

Just a couple of nuances:

article_search_keyword_list table isn't deleted. I don't see much relations in the database schema, so I hope won't be a problem to delete it's rows.

Although issue is empty, issues table isn't deleted, but if you like to delete them by hand (only if you droped all it's articles) I think you can do it without harm.

BTW, first of all: backup your database (you are warned... I'm not responsible any more :-P).

Cheers and thanks for your help,

m.
mbria
 
Posts: 292
Joined: Wed Dec 14, 2005 4:15 am

Postby asmecher » Wed May 10, 2006 1:18 pm

Hi mbria,

Regarding the article_search_keyword_list table, this is used for full-text indexing and can be re-generated using the tools/rebuildSearchIndex.php script.

Regards,
Alec Smecher
Open Journal Systems Team
asmecher
 
Posts: 8321
Joined: Wed Aug 10, 2005 12:56 pm

Postby mbria » Wed May 10, 2006 5:22 pm

thanks again.

I suposse your solution with the keywords will be cleaner than deleting rows.

Just for newbies (as me), if you imported some issues I suposse you will have some attached galleys. Those files are saved on your disk in the directory pointed in your config.inc.php.

Cheers,

m.
mbria
 
Posts: 292
Joined: Wed Dec 14, 2005 4:15 am


Return to OJS Technical Support

Who is online

Users browsing this forum: No registered users and 5 guests