Changing the metadata (like createDate) fields on Asset Publisher Web Content
I've spent the last trying to figure out a quick and dirty way to get some of my web content to appear as if I'd posted it in the past. This was necessary since I was porting our old website over to a new Liferay deployment, but wanted to post news on the orignal post date and have it show up in my asset publisher as having been posted in the past.
I know that hacking with the database is a no-no, but for the relatively small number of articles I wanted to change [in this case, 10-15] it didn't make sense for me to create a dummy portlet just to access the Liferay API, which does indeed have a function for "setCreateDate".
Instead, I found that I could modify the createDate field in two tables in the database, then clear the cache and reindex in the Control Panel to get Liferay to pick up the changes. I just wanted to outline how this was done as I'm sure I'm not the first person who has ever wondered how to do this.
First of all, add your new piece of web content to the asset publisher. Make sure to note the ID in the upper left corner, you'll need this to key into your database table. In this example, the ID is 16332.
Next, you'll have to get into your mysql database. Here is the command I ran to get into lportal:
mysql -u root -p
It will prompt you for your password. Once you're in, you'll have to select Liferay's database:
mysql> use lportal;
From here, we first will search for the articleID in the table "JournalArticle", and then use the UPDATE command to modify the createDate field. Here is what it looks like to query the table:
mysql> SELECT * FROM JournalArticle WHERE ArticleID=16332;
This spits out a whole bunch of unnecessary information and is hard to read, so you can use this modified query to get us what we want:
mysql > SELECT uuid_,id_,userID,createDate,articleID,layoutUuid,resourcePrimKey FROM JournalArticle WHERE ArticleID=16332;
Much cleaner. You'll see only the fields we listed above, and their values. Note that every time you edit or change your piece of web content, it will create a new version-- so there very well might be more than one entry with your ArticleID. That's fine. Keying on that tuple will ensure that when you do the update, it updates all the revisions.
So-- the createDate for my journal article was being listed as:
and I wanted to set it to March 8th.
Now for the fun part. Let me stress how important it is that you follow the date syntax here. I'm not a database expert, nor a Liferay expert, and I have no idea what would happen if you entered the date in the wrong format. So proceed at your own risk. That said... I just copy/pasted the createDate that mysql gave me and changed the month to avoid syntax issues. Here is the command to update the entry:
mysql> UPDATE JournalArticle SET createDate='2012-03-08 15:17:12' WHERE ArticleID=16332;
Bam. That was pretty easy right? Now we just have to do this in one other spot and we're done. There is also an entry in the table "AssetEntry" that needs to be changed before Liferay will pick up the new createDate, and the common tuple between the two tables is called "resourcePrimKey" in JournalArticle and "classPK" in AssetEntry. If you look at the original query we did, look for resourcePrimKey and that is what you'll want to search for in the AssetEntry table. Should be a 5 digit number:
Now I know there are fancy ways to pass sql query output into update commands, but to keep this simple, I will just show you how to copy and paste to get the job done. So first, let's query the new table to make sure the layoutUuid is correct.
mysql> SELECT * FROM AssetEntry WHERE classPK = 16334;
This should spit out an entry in the table, and you can verify that it's the right one by looking at the Title-- it'll contain an xml tag with plaintext of the title of your piece of content. Now that we've verified it is there, we do another UPDATE to change the createDate again. This time it looks like this:
mysql> UPDATE AssetEntry SET createDate='2012-03-08 15:17:12' WHERE classPK = 16334;
That's all you need to do in the database. Now, to get Liferay to see your changes (this is for 6.1, not sure about older versions) go into the Control Panel -> Server Administration, and clear the database cache, then reindex the search incides-- then go to your page and do a reload and you should see your new createDates.
Worked like a charm for me. Hope this was helpful.
I do not advocate hacking up the database to achieve desired behavior. I only did this as it was fairly straightforward and I didn't need a permanent solution [I don't foresee having to do this ever again, at least not in the foreseeable future.]
I would love to see somebody show how this can be done by leveraging the Liferay API. That was the route I was going to take but figured it was too much work to learn for so small a job, but I would love to know how to do it!