data import

Submitted by suminda on 2013-04-20

hi Michal,

since I have bulk data to import externally to a particular project, can I know the logic behind generating issue id, if so what are the minimum tables required to update,(my data contains only issues with attributes, no history and comments).

regards,
suminda

The wiki contains the description of the database schema.

In order to add an issue with attribute, you have to do the following (based on System_Api_IssueManager::addIssue): 

INSERT INTO stamps ( user_id, stamp_time ) VALUES ( %user_id, %stamp_time )

Here %user_id is the ID of a user (you can use 1 for Administrator) and %stamp_time is the UNIX time stamp (as an integer) representing the current time. The generated stamp_id (which is the auto-incremented primary key of the stamps table) becomes the issue ID. 

INSERT INTO issues ( issue_id, folder_id, issue_name, stamp_id ) VALUES ( %stamp_id, %folder_id, %issue_name, %stamp_id )

Here %folder_id is the ID of the folder, %issue_name is the name of the issue and %stamp_id is the previously generated ID. 

INSERT INTO changes ( change_id, issue_id, change_type, stamp_id, value_new ) VALUES ( %stamp_id, %stamp_id, 0, %stamp_id, %issue_name )

This records the IssueCreated event in the history of changes with the initial name of the issue. 

INSERT INTO attr_values ( issue_id, attr_id, attr_value ) VALUES ( %stamp_id, %attr_id, %attr_value )

This sets the values of the attributes. For each non-empty attribute, pass the ID of the attribute and the value. Note that generally WebIssues first sets all attributes to default values, and then modified if necessary, so that the initial modifications are recorded in the history, but this is not strictly necessary. 

UPDATE folders SET stamp_id = %stamp_id WHERE folder_id = %folder_id

This updates the stamp of the folder so that automatic updates work in the Desktop Client.

Regards,
Michał

hi Michal,

thanks for your info, this will help me to fully migrate from manual system to your live system, better if you can implement "import issues"same as "add issues", which makes compatible to everybody to handle bulk issues.

regards,
suminda,

hi Michal,

Until you implement 'import' feature, i had a little effort to import csv which given below for your review before implement.
Upload a file with import.php simply like coding as 'add attachment' and passing only $folder and $attachment variables to new function in issuemanager called 'importIssue' with below coding which is almost similar to 'addissue' function.the changes made to read the csv is highlighted between * marks. csv contains fields as [issue name];[attribute1];[att2];etc.
submit function in import.php is as follows, 

private function submit()
    {
        $issueManager = new System_Api_IssueManager();
		$issueId = $issueManager->addIssue( $this->folder, $this->attachment );
            $this->issue = $issueManager->getIssue( $issueId );
            $this->parentUrl = $this->mergeQueryString( '/client/index.php', array( 'issue' => $issueId, 'folder' => null ) );
    }

import function in issuemanager is, 

public function importIssue( $folder, $attachment )
    {
        $principal = System_Api_Principal::getCurrent();

        $folderId = $folder[ 'folder_id' ];

        $transaction = $this->connection->beginTransaction( System_Db_Transaction::ReadCommitted );
		//*******
		$fp = fopen($attachment,"rb");
						
		while (!feof($fp))
				{
					$line = fgets($fp);
					$values = explode(',',$line);
		//*******
        try {
            $query = 'INSERT INTO {stamps} ( user_id, stamp_time ) VALUES ( %d, %d )';
            $this->connection->execute( $query, $principal->getUserId(), time() );
            $issueId = $this->connection->getInsertId( 'stamps', 'stamp_id' );

            $query = 'INSERT INTO {issues} ( issue_id, folder_id, issue_name, stamp_id ) VALUES ( %d, %d, %s, %d )';
            $this->connection->execute( $query, $issueId, $folderId, $values[0], $issueId );

            $query = 'INSERT INTO {changes} ( change_id, issue_id, change_type, stamp_id, value_new ) VALUES ( %d, %d, %d, %d, %s )';
            $this->connection->execute( $query, $issueId, $issueId, System_Const::IssueCreated, $issueId, $values[0] );

            $query = 'INSERT INTO {attr_values} ( issue_id, attr_id, attr_value ) VALUES ( %d, %d, %s )';
            foreach ( $values as $attributeId => $value ) {
                if ( $value != '' )
                    $this->connection->execute( $query, $issueId, $attributeId, $value );
            }

            $query = 'UPDATE {folders} SET stamp_id = %1d WHERE folder_id = %2d AND COALESCE( stamp_id, 0 ) < %1d';
            $this->connection->execute( $query, $issueId, $folderId );

            $transaction->commit();
        } catch ( Exception $ex ) {
            $transaction->rollback();
            throw $ex;
        }
		//*********
		}
		fclose($fp);
		//*********
        return $issueId;
    }

kindly comment on this logic or any sugestion to improve the coding?

regards,
suminda,

Hello suminda, I was wondering how is your import script working? I was looking to replicate it and wanted to ask if you had any other advice.