24 April 2012, 2:07

I am trying to receover data for a user from an old sql dump (the database it is taken from is no longer in use). I have the database open in PhPMyadmin and have worked out that by looking at the USR table a username has an ID and by searching the artefacts table using that ID number I can find their blog posts and a list of any files they uploaded. I can also use that ID to see what views they had created in the views table. What I can't seem to work out is within the view, the user had created multiple text boxes and typed into them - where is the contents of the test box stored???

Regards

24 April 2012, 3:41

Hi Ian,

The content will be in the block_instance table in the configdata serialized array:

For example:

maharadev=# select * from block_instance where id='23';
 id | blocktype |  title   |                  configdata                  | view | column | order 
----+-----------+----------+----------------------------------------------+------+--------+-------
 23 | textbox   | Text Box | a:1:{s:4:"text";s:19:"<p>texttexttext</p>";} |    6 |      2 |     1
(1 row)

Hope this helps :)

Thanks,
Melissa

24 April 2012, 4:39

Many thanks for your reply Melissa, I can certainly see loads of data in there! The ID field doesn't appears to be the users id field, I think it's an id unique to the block_instance table (it looks like an incremental number). Any idea how how I could search for all the configdata for an individual user?

Thanks Again. 

24 April 2012, 5:30

Hi Ian,

If you know the view number, you can select only block instances with that view.

maharadev=# select * from block_instance where view='6';
 id |  blocktype   |  title   |                         configdata                          | view | column | order 
----+--------------+----------+-------------------------------------------------------------+------+--------+-------
 24 | externalfeed |          | a:3:{s:5:"count";s:2:"10";s:4:"full";b:0;s:6:"feedid";i:1;} |    6 |      1 |     1
 23 | textbox      | Text Box | a:1:{s:4:"text";s:19:"<p>texttexttext</p>";}                |    6 |      2 |     1
(2 rows)

To find the view ids you can search the views table; the '1' would be whatever the user's id is:

maharadev=# select id from view where owner='1';
 id 
----
  6
  5
  4
(3 rows)

Good luck!

Thanks,
Melissa

24 April 2012, 6:00

Ok, to my logic the block_instance table seems to use "view" as the way to identify things. Therefore if I can list the views created by an individual then use that view ID to search the block_instance type it will show me what was in the text boxes of each view??????

I think!

24 April 2012, 6:27

Hi Ian,

Yep.

You could also try using subqueries.

select * from block_instance where view IN(select id from view where owner='1');

This will get you all the block instances for user 1.

select * from block_instance where view IN(select id from view where owner='1') AND blocktype='textbox';

will get you just the textbox blocktypes for user id 1.

Thanks,
Melissa.

24 April 2012, 22:45

Hello Ian,

Richard wrote a wiki page on how to restore a deleted user. Maybe that's also helpful for you to look at esp. if all the data was removed.

Cheers

Kristina

25 April 2012, 1:12

Thanks Kristina but the user wasn't deleted and wwe don't have the old datadir which begs the question...In a test environment is it possible to hook up and old sql dump to a new mahara intsall without having the datadir???? Of course, file uploads etc wouldn't be there but presumably views, blogposts etc would be??? I unsuccessfully tried this in the past but the more I think about it the reasons for it not working could be down to the contents of the config table. 

25 April 2012, 17:07

Ian,

Yes, you can do that, but don't install a new mahara, just restore the dump into a fresh database, put the Mahara code in place, and edit config.php to point at the newly restored db (and an empty dataroot directory).

It should work if you use the same code version as the one that created the database (with broken download links, etc. due to the missing dataroot).

You can try using a later code version too, but you'll be forced to upgrade the first time you hit the site in your browser.

R.

26 April 2012, 6:23

Richard, many thanks, that works brilliantly. I can now login as admin and masquerade as a user to see their views wetc. What I cant do is export them. Mahara is telling me 

"Your system does not have the zip command. Please install zip to enable this feature"

I have read one thread in these forums about this erro but I am afraid it didn't make a lot of sense to me. I am running things on Unbuntu 10.4 

Regards