Database Tricks

IMPORTANT: Zanata's wiki has moved to https://github.com/zanata/zanata-platform/wiki

New link: https://github.com/zanata/zanata-platform/wiki/Database-Tricks

---

Create an Admin User

To create an administrator user with username admin and password admin123. (This script asumes an empty database):

INSERT INTO `zanata`.`HAccount` (id,creationDate,lastChanged,versionNum,apiKey,enabled,passwordHash,username,mergedInto)
VALUES (1,{ts '2013-01-07 15:28:47'},{ts '2013-01-07 15:31:24'},3,'b1f997395af864968fce5b62abdc340f',true,'rQfxz8UooTY515xgpb5JnA==','admin',null);

INSERT INTO `zanata`.`HPerson` (id,creationDate,lastChanged,versionNum,email,name,accountId)
VALUES (1,{ts '2013-01-07 15:28:47'},{ts '2013-01-07 15:28:47'},0,'[email protected]','Administrator',1);

INSERT INTO `zanata`.`HAccountMembership` (accountId,memberOf) VALUES (1,4);

Delete an empty project by ID

To delete an empty project 'myprojectid' by ID:

    delete HProject_Maintainer
    from HProject, HProject_Maintainer
    where HProject.slug='myprojectid' and HProject.id=HProject_Maintainer.projectId;

    delete HProject
    from HProject
    where HProject.slug='myprojectid';

DANGER: Delete project with translations

To delete project 'TEST1' by ID (ignoring HDocumentHistory, HTextFlowHistory, HProject_Locale, HProjectIteration_Locale, HPoHeader, HPotEntryData, HSimpleComment):

    delete HTextFlowTargetHistory
    from HProject, HProjectIteration, HDocument, HTextFlow, HTextFlowTarget, HTextFlowTargetHistory
    where HProject.slug='TEST1' and HProject.id=HProjectIteration.project_id
    and HProjectIteration.id=HDocument.project_iteration_id and HDocument.id=HTextFlow.document_id
    and HTextFlow.id=HTextFlowTarget.tf_id and HTextFlowTarget.id=HTextFlowTargetHistory.target_id;

    delete HTextFlowTarget
    from HProject, HProjectIteration, HDocument, HTextFlow, HTextFlowTarget
    where HProject.slug='TEST1' and HProject.id=HProjectIteration.project_id
    and HProjectIteration.id=HDocument.project_iteration_id and HDocument.id=HTextFlow.document_id
    and HTextFlow.id=HTextFlowTarget.tf_id;

    delete HTextFlow
    from HProject, HProjectIteration, HDocument, HTextFlow
    where HProject.slug='TEST1' and HProject.id=HProjectIteration.project_id
    and HProjectIteration.id=HDocument.project_iteration_id and HDocument.id=HTextFlow.document_id;

    delete HDocument
    from HProject, HProjectIteration, HDocument
    where HProject.slug='TEST1' and HProject.id=HProjectIteration.project_id
    and HProjectIteration.id=HDocument.project_iteration_id;

    delete HProjectIteration
    from HProject, HProjectIteration
    where HProject.slug='TEST1' and HProject.id=HProjectIteration.project_id;

    delete HProject_Maintainer
    from HProject, HProject_Maintainer
    where HProject.slug='TEST1' and HProject.id=HProject_Maintainer.projectId;

    delete HProject
    from HProject
    where HProject.slug='TEST1';