public function run()
{
$csv = new CsvFile($this->fullPath());
$entries = Entry::all();
$header = array_keys((new Entry())->toArray());
$csv->writeRow($header);
foreach ($entries as $entry) {
$row = array_values($entry->toArray());
$csv->writeRow($row);
}
$this->updateFileSize();
return true;
}
/**
* @param $query
* @param $outputTable
* @param bool $incremental
* @param null $primaryKey
* @throws \Exception
* @throws \Keboola\Csv\Exception
* @throws null
*/
public function export($query, $outputTable, $incremental = false, $primaryKey = null)
{
$outFilename = tempnam("/tmp/ex-db", $outputTable) . ".csv";
$csv = new CsvFile($outFilename);
$this->logger->info("Exporting to " . $outputTable);
$cursorName = 'exdbcursor' . intval(microtime(true));
$curSql = "DECLARE {$cursorName} CURSOR FOR {$query}";
try {
$this->conn->beginTransaction();
// cursors require a transaction.
$stmt = $this->conn->prepare($curSql);
$stmt->execute();
$innerStatement = $this->conn->prepare("FETCH 1 FROM {$cursorName}");
$innerStatement->execute();
// write header and first line
$resultRow = $innerStatement->fetch(\PDO::FETCH_ASSOC);
if (is_array($resultRow) && !empty($resultRow)) {
$csv->writeRow(array_keys($resultRow));
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$csv->writeRow($resultRow);
// write the rest
$innerStatement = $this->conn->prepare("FETCH 5000 FROM {$cursorName}");
while ($innerStatement->execute() && count($resultRows = $innerStatement->fetchAll(\PDO::FETCH_ASSOC)) > 0) {
foreach ($resultRows as $resultRow) {
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$csv->writeRow($resultRow);
}
}
// close the cursor
$this->conn->exec("CLOSE {$cursorName}");
$this->conn->commit();
$this->writeTable($csv, $outputTable, $incremental, $primaryKey);
} else {
$this->logger->warning("Query returned empty result. Nothing was imported.");
}
} catch (\PDOException $e) {
try {
$this->conn->rollBack();
} catch (\Exception $e2) {
}
$innerStatement = null;
$stmt = null;
throw new DbException("DB query failed: " . $e->getMessage(), $e);
}
}
/**
* {@inheritdoc}
*/
public function writerTest()
{
$csv = new CsvFile($this->path);
foreach ($this->generateRawData() as $row) {
$csv->writeRow($row);
}
}
public function export($query, $outputTable, $incremental = false, $primaryKey = null)
{
$outFilename = tempnam("/tmp/ex-db", $outputTable) . ".csv";
$csv = new CsvFile($outFilename);
$this->logger->info("Exporting to " . $outputTable);
try {
$stmt = oci_parse($this->conn, $query);
oci_execute($stmt);
} catch (\Exception $e) {
throw new DbException("DB query failed: " . $e->getMessage(), $e);
}
// write header and first line
try {
$resultRow = oci_fetch_assoc($stmt);
} catch (\Exception $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
}
if (is_array($resultRow) && !empty($resultRow)) {
$csv->writeRow(array_keys($resultRow));
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$resultRow = str_replace('""', '"', $resultRow);
$csv->writeRow($resultRow);
// write the rest
try {
while ($resultRow = oci_fetch_assoc($stmt)) {
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$resultRow = str_replace('""', '"', $resultRow);
$csv->writeRow($resultRow);
}
} catch (\Exception $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
}
if (!count($csv->getHeader())) {
throw new ApplicationException("Trying to upload an empty file");
}
$this->writeTable($csv, $outputTable, $incremental, $primaryKey);
} else {
$this->logger->warning("Query returned empty result. Nothing was imported.");
}
}
public function saveToCsv(array $data, Profile $profile, CsvFile $csv, $incremental = false)
{
$cnt = 0;
/** @var Result $result */
foreach ($data as $result) {
$metrics = $result->getMetrics();
$dimensions = $result->getDimensions();
// CSV Header
if ($cnt == 0 && !$incremental) {
$headerRow = array_merge(array('id', 'idProfile'), array_keys($dimensions), array_keys($metrics));
$csv->writeRow($headerRow);
}
if (isset($dimensions['date'])) {
$dimensions['date'] = date('Y-m-d', strtotime($dimensions['date']));
}
$row = array_merge(array_values($dimensions), array_values($metrics));
$outRow = array_merge(array(sha1($profile->getGoogleId() . implode('', $dimensions)), $profile->getGoogleId()), $row);
$csv->writeRow($outRow);
$cnt++;
}
}
protected function transpose($csvRow, $csvHeaderRaw, $csvTransposeHeader)
{
$transposeFrom = $this->config['transform']['transpose']['from'];
$outRowArr = array_slice($csvRow, 0, $transposeFrom - 1);
$transposeCsvRow = array_slice($csvRow, $transposeFrom - 1, null, true);
foreach ($transposeCsvRow as $k => $v) {
$outRowArr['key'] = $csvHeaderRaw[$k];
$outRowArr['value'] = $v;
if (!is_null($csvTransposeHeader) && !empty($csvTransposeHeader[$k])) {
$outRowArr[$this->config['header']['transpose']['name']] = $csvTransposeHeader[$k];
}
$this->outputCsv->writeRow($outRowArr);
}
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
// Avoid PHP timeouts when querying large clusters
set_time_limit(0);
//Create Sabre XML Reader Object
$reader = new Reader();
//Create Guzzle REST client Object
$client = new Client(['headers' => ['Accept' => 'application/xml', 'Content-Type' => 'application/xml']]);
//Pick up the csv report created in the calling controller
$csvFile = new CsvFile($this->csvFileName);
//Write the csv headers
$csvFile->writeRow(explode(',', $this->csv_headers));
//Loop the devices in $this->deviceList to get firmware info
foreach ($this->deviceList as $device) {
//Only get firmware if the device is registered
if ($device['IsRegistered']) {
\Log::debug('Running with', [$device]);
try {
//Query the phones web interface for the XML device info
$response = $client->get('http://' . $device['IpAddress'] . '/DeviceInformationX', ['connect_timeout' => 2]);
} catch (RequestException $e) {
\Log::debug('Phone request exception', [$e]);
continue;
}
$body = $response->getBody()->getContents();
\Log::debug('XML:', [$body]);
//Consume the XML with Sabre XML Reader
if ($reader->xml($body)) {
//Parse the XML
$deviceInfoX = $response = $reader->parse();
} else {
\Log::debug('Error, there was no XML', []);
}
//Find the index for XML key holding the Firmware information
$index = searchMultiDimArray($deviceInfoX['value'], 'name', '{}versionID');
//Place the firmware info into our $device array
$device['Firmware'] = $deviceInfoX['value'][$index]['value'];
}
//Write the firmware info to csv
$csvFile->writeRow($device);
}
}
public function export($query, $outputTable, $incremental = false, $primaryKey = null)
{
$outFilename = $this->dataDir . "/out/tables/" . $outputTable . ".csv";
$outManifestFilename = $this->dataDir . "/out/tables/" . $outputTable . ".csv.manifest";
$csv = new CsvFile($outFilename);
try {
$stmt = $this->conn->prepare($query);
$stmt->execute();
} catch (\PDOException $e) {
throw new DbException("DB query failed: " . $e->getMessage(), $e);
}
// write header and first line
try {
$resultRow = $stmt->fetch(\PDO::FETCH_ASSOC);
} catch (\PDOException $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
}
if (is_array($resultRow) && !empty($resultRow)) {
$csv->writeRow(array_keys($resultRow));
$csv->writeRow($resultRow);
// write the rest
try {
while ($resultRow = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$csv->writeRow($resultRow);
}
} catch (\PDOException $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
} catch (\Exception $e) {
// catch warnings
throw new DbException("Db query fetch failed: " . $e->getMessage(), $e);
}
// write manifest
file_put_contents($outManifestFilename, Yaml::dump(['destination' => $outputTable, 'incremental' => $incremental, 'primary_key' => $primaryKey]));
} else {
print_r("Query returned empty result. Nothing was imported.");
}
return $csv;
}
/**
*
* Export Duo Auth Log data as csv
*
*/
public function exportLogData()
{
// Define the SQL query
$logs = Log::join('duo_users', 'duo_logs.duo_user_id', '=', 'duo_users.id', 'left outer')->leftJoin('duo_group_duo_user', 'duo_users.id', '=', 'duo_group_duo_user.duo_user_id')->leftJoin('duo_groups', 'duo_groups.id', '=', 'duo_group_duo_user.duo_group_id')->select(['duo_logs.integration', 'duo_logs.factor', 'duo_logs.device', 'duo_logs.ip', 'duo_logs.new_enrollment', 'duo_logs.reason', 'duo_logs.result', 'duo_logs.timestamp', 'duo_users.username', 'duo_groups.name'])->orderBy('duo_logs.integration', 'asc');
// Create the Datatables object from the query
$data = Datatables::of($logs)->editColumn('duo_logs.timestamp', function ($log) {
return $log->timestamp->format('Y/m/d');
})->make()->getData(true);
// Get the query data and column names
$rowData = $data['data'];
$columns = $data['input']['columns'];
// Create the csv column headers
$headers = [];
foreach ($columns as $column) {
array_push($headers, ucfirst($column['data']));
}
// Create the CsvFile object to store our data
$csvFile = new CsvFile(storage_path() . '/reports/duo/auth-logs/' . Carbon::now()->toDateTimeString() . '-' . \Auth::user()->username . '.csv');
// Write the header row
$csvFile->writeRow(array_slice($headers, 0, 10));
// Write the data rows
foreach ($rowData as $row) {
$csvFile->writeRow(array_slice($row, 0, 10));
}
// Return the csv file
return response()->download($csvFile);
}
public function testReadTablesS3Redshift()
{
// Create bucket
if (!$this->client->bucketExists("in.c-docker-test-redshift")) {
$this->client->createBucket("docker-test-redshift", Client::STAGE_IN, "Docker Testsuite", "redshift");
}
// Create table
if (!$this->client->tableExists("in.c-docker-test-redshift.test")) {
$csv = new CsvFile($this->tmpDir . "/upload.csv");
$csv->writeRow(["Id", "Name"]);
$csv->writeRow(["test", "test"]);
$this->client->createTableAsync("in.c-docker-test-redshift", "test", $csv);
$this->client->setTableAttribute("in.c-docker-test-redshift.test", "attr1", "val2");
}
$root = $this->tmpDir;
$reader = new Reader($this->client);
$configuration = [["source" => "in.c-docker-test-redshift.test", "destination" => "test-redshift.csv"]];
$reader->downloadTables($configuration, $root . "/download", "s3");
$adapter = new TableManifestAdapter();
$manifest = $adapter->readFromFile($root . "/download/test-redshift.csv.manifest");
$this->assertEquals("in.c-docker-test-redshift.test", $manifest["id"]);
$this->assertEquals("val2", $manifest["attributes"][0]["value"]);
$this->assertS3info($manifest);
}
public function testWriteWithWindowsNewline()
{
$fileName = __DIR__ . '/_data/_out.csv';
if (file_exists($fileName)) {
unlink($fileName);
}
$csvFile = new \Keboola\Csv\CsvFile($fileName, ',', '"', '', "\r\n");
$rows = array(array('col1', 'col2'), array('line without enclosure', 'second column'), array('enclosure " in column', 'hello \\'), array('line with enclosure', 'second column'), array('column with enclosure ", and comma inside text', 'second column enclosure in text "'), array("columns with\nnew line", "columns with\ttab"), array('column with \\n \\t \\\\', 'second col'));
foreach ($rows as $row) {
$csvFile->writeRow($row);
}
$expectedFileContents = "\"col1\",\"col2\"\r\n" . "\"line without enclosure\",\"second column\"\r\n" . "\"enclosure \"\" in column\",\"hello \\\"\r\n" . "\"line with enclosure\",\"second column\"\r\n" . "\"column with enclosure \"\", and comma inside text\",\"second column enclosure in text \"\"\"\r\n" . "\"columns with\nnew line\",\"columns with\ttab\"\r\n" . "\"column with \\n \\t \\\\\",\"second col\"\r\n";
$this->assertEquals($expectedFileContents, file_get_contents($fileName));
}
public function testCreateSandboxInputMapping()
{
$temp = new Temp();
$tmpDir = $temp->getTmpFolder();
$temp->initRunFolder();
$csv = new CsvFile($tmpDir . "/upload.csv");
$csv->writeRow(["Id", "Name"]);
$csv->writeRow(["test", "test"]);
$this->client->createTableAsync("in.c-sandbox", "test", $csv);
unset($csv);
$this->createECSServer();
$jobData = ['params' => ['backend' => 'docker', 'type' => 'rstudio', 'input' => ['tables' => [['source' => 'in.c-sandbox.test', 'destination' => 'source.csv']]]]];
$data = $this->executeJob('create', $jobData);
$this->assertArrayHasKey('credentials', $data);
$this->assertArrayHasKey('id', $data['credentials']);
$doctrine = self::$kernel->getContainer()->get("doctrine");
$server = new Docker($doctrine, new Client(["token" => "dummy"]));
$account = $server->getActiveAccount($data["credentials"]["id"]);
/** @var $account \Keboola\ProvisioningBundle\Entity\Account\Docker */
$this->assertNotEmpty($account->getCredentials());
$credentials = $account->getCredentials();
$this->assertArrayHasKey('hostname', $credentials);
$this->assertArrayHasKey('port', $credentials);
$this->assertArrayHasKey('user', $credentials);
$this->assertArrayHasKey('password', $credentials);
$client = $this->getGuzzleClient();
$url = $credentials['hostname'] . ':' . $credentials['port'];
$res = $client->request('GET', $url);
$response = (string) $res->getBody();
$this->assertContains('RStudio: Browser Not Supported', $response);
}
public function export($query, $outputTable, $incremental = false, $primaryKey = null)
{
$outFilename = tempnam("/tmp/ex-db", $outputTable) . ".csv";
$csv = new CsvFile($outFilename);
$this->logger->info("Importing to " . $outputTable);
$this->logger->info(sprintf("Query '%s' started.", $query));
try {
$stmt = $this->conn->prepare($query);
$stmt->execute();
} catch (\PDOException $e) {
throw new DbException("DB query failed: " . $e->getMessage(), $e);
} catch (ContextErrorException $e) {
throw new DbException("DB query failed: " . $e->getMessage(), $e);
}
// write header and first line
try {
$resultRow = $stmt->fetch(\PDO::FETCH_ASSOC);
} catch (\PDOException $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
}
if (is_array($resultRow) && !empty($resultRow)) {
$csv->writeRow(array_keys($resultRow));
if (isset($this->dbConfig['driver']) && $this->dbConfig['driver'] == 'mssql') {
$resultRow = $this->convertEncoding($resultRow);
}
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$csv->writeRow($resultRow);
// write the rest
try {
while ($resultRow = $stmt->fetch(\PDO::FETCH_ASSOC)) {
if (isset($this->dbConfig['driver']) && $this->dbConfig['driver'] == 'mssql') {
$resultRow = $this->convertEncoding($resultRow);
}
if (isset($this->dbConfig['null'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['null']);
}
$csv->writeRow($resultRow);
}
} catch (\PDOException $e) {
throw new DbException("DB query fetch failed: " . $e->getMessage(), $e);
} catch (ContextErrorException $e) {
// catch warnings
throw new DbException("Db query fetch failed: " . $e->getMessage(), $e);
}
$this->logger->info("Query finished successfully. Uploading results to Storage.");
$this->writeTable($csv, $outputTable, $incremental, $primaryKey);
} else {
$this->logger->warning("Query returned empty result. Nothing was imported.");
}
}
protected function mergeFiles(CsvFile $file1, CsvFile $file2)
{
// CsvFile::getHeader resets it to the first line,
// so we need to forward it back to the end to append it
// Also, this is a dirty, dirty hack
for (; $file1->valid(); $file1->next()) {
}
$header = true;
foreach ($file2 as $row) {
if ($header) {
$header = false;
continue;
}
$file1->writeRow($row);
}
}
public function testExecutor()
{
$executor = $this->getExecutor(self::DRIVER);
$executor->run();
$conn = $this->getWriter(self::DRIVER)->getConnection();
$stmt = $conn->query("SELECT * FROM encoding");
$res = $stmt->fetchAll(\PDO::FETCH_ASSOC);
$resFilename = tempnam('/tmp', 'db-wr-test-tmp');
$csv = new CsvFile($resFilename);
$csv->writeRow(["col1", "col2"]);
foreach ($res as $row) {
$csv->writeRow($row);
}
$sourceFilename = $this->dataDir . "/" . self::DRIVER . "/in/tables/encoding.csv";
$this->assertFileEquals($sourceFilename, $resFilename);
}
/**
*
* prepare and open CSV files
*
* @throws Exception
*/
private function _prepareCsvFiles()
{
foreach ($this->runConfig as $queryNumber => $query) {
// Fill configuration variables in query with values
$parsedQuery = $query->query;
if (strpos($parsedQuery, '[') !== false) {
$importConfig = $this->importConfig->toArray();
if (is_array($importConfig)) {
foreach ($importConfig as $key => $value) {
if (!is_array($value)) {
$parsedQuery = str_replace('[' . $key . ']', $value, $parsedQuery);
}
}
}
}
$columnsToDownload = array();
if (in_array($query->type, array('insights', 'insightsPages', 'insightsLifetime', 'insightsPosts', 'insights_pivoted', 'insightsLifetime_pivoted', 'insightsPages_pivoted', 'insightsPosts_pivoted'))) {
// Check if Insights type contains right url
if (strpos($parsedQuery, '/insights') === FALSE) {
throw new UserException(sprintf('Configuration query on row %d is not valid. Insights query needs
to have format {objectId}/insights.', $queryNumber));
}
if (in_array($query->type, array('insights_pivoted', 'insightsLifetime_pivoted', 'insightsPages_pivoted', 'insightsPosts_pivoted'))) {
try {
$columnsToDownload = \Zend_Json::decode($query->columns);
if (!$columnsToDownload) {
$columnsToDownload = array();
}
} catch (\Exception $e) {
throw new UserException("Can't decode column mapping for insightsPages_pivoted or insightsPosts_pivoted.");
}
}
} else {
// Find out which values to download
$columnsToDownload = explode(',', $query->columns);
}
// Create csv file for data
$csvFileName = sprintf('%s/%d-%s.csv', $this->tmpDir, $queryNumber, uniqid());
$csvHandle = new CsvFile($csvFileName);
// Add csv header
if (in_array($query->type, array('insights', 'insightsPages'))) {
$csvHandle->writeRow(array('ex__primary', 'ex__account', 'ex__object', 'metric', 'period', 'end_time', 'key', 'value'));
} elseif (in_array($query->type, array('insightsLifetime', 'insightsPosts'))) {
$csvHandle->writeRow(array('ex__primary', 'ex__account', 'ex__object', 'metric', 'end_time', 'key', 'value'));
} elseif (in_array($query->type, array('insights_pivoted', 'insightsLifetime_pivoted', 'insightsPages_pivoted', 'insightsPosts_pivoted'))) {
$columns = array('ex__primary', 'ex__account', 'ex__object', 'end_time');
foreach ($columnsToDownload as $column) {
$columns[] = $column["column"];
}
$csvHandle->writeRow($columns);
} else {
if ($query->type == "value") {
if (in_array("#timestamp", $columnsToDownload)) {
array_unshift($columnsToDownload, "#timestamp");
}
if (in_array("#datestamp", $columnsToDownload)) {
array_unshift($columnsToDownload, "#datestamp");
}
array_unshift($columnsToDownload, "#objectId");
$columnsToDownload = array_unique($columnsToDownload);
}
$columnNames = $this->_columnsNames($columnsToDownload);
$csvHandle->writeRow($columnNames);
}
$csvFile = array("fileName" => $csvFileName, "handle" => $csvHandle);
$this->_csvFiles[$queryNumber] = $csvFile;
}
}
public function run($options = null)
{
$accounts = $this->configuration->getAccounts();
$status = array();
$dateFrom = isset($options['since']) ? date('Y-m-d', strtotime($options['since'])) : date('Y-m-d', strtotime('-4 days'));
$dateTo = isset($options['until']) ? date('Y-m-d', strtotime($options['until'])) : date('Y-m-d', strtotime('-1 day'));
$dataset = isset($options['dataset']) ? $options['dataset'] : null;
if (isset($options['account'])) {
if (!isset($accounts[$options['account']])) {
throw new UserException(sprintf("Account '%s' does not exist.", $options['account']));
}
$accounts = array($options['account'] => $accounts[$options['account']]);
}
if (isset($options['config'])) {
if (!isset($accounts[$options['config']])) {
throw new UserException(sprintf("Config '%s' does not exist.", $options['config']));
}
$accounts = array($options['config'] => $accounts[$options['config']]);
}
if ($dataset != null && !isset($options['config']) && !isset($options['account'])) {
throw new UserException("Missing parameter 'config'");
}
/** @var Account $account */
foreach ($accounts as $accountId => $account) {
// check if account has been authorized
if (null == $account->getAttribute('accessToken')) {
continue;
}
$this->currAccountId = $accountId;
if (null == $account->getAttribute('outputBucket')) {
$this->configuration->initDataBucket($account->getAccountId());
} else {
if (!$this->configuration->getStorageApi()->bucketExists($account->getAttribute('outputBucket'))) {
$outBucketArr = explode('.', $account->getAttribute('outputBucket'));
$this->configuration->getStorageApi()->createBucket(str_replace('c-', '', $outBucketArr[1]), $outBucketArr[0], 'Google Analytics data bucket');
}
}
$this->gaApi->getApi()->setCredentials($account->getAccessToken(), $account->getRefreshToken());
$this->gaApi->getApi()->setRefreshTokenCallback(array($this, 'refreshTokenCallback'));
$tmpFileInfo = $this->temp->createFile("profiles-" . $accountId . "-" . microtime() . ".csv");
$profilesCsv = new CsvFile($tmpFileInfo->getPathname());
$profilesCsv->writeRow(array('id', 'name'));
/** @var Profile $profile */
foreach ($account->getProfiles() as $profile) {
$profilesCsv->writeRow(array($profile->getGoogleId(), $profile->getName()));
try {
$configuration = $account->getConfiguration();
// Download just the dataset specified in request
if ($dataset != null) {
if (!isset($configuration[$dataset])) {
throw new UserException(sprintf("Dataset '%s' doesn't exist", $dataset));
}
$configuration = [$dataset => $configuration[$dataset]];
}
foreach ($configuration as $tableName => $cfg) {
// Download dataset only for specific profile
if (isset($cfg['profile']) && $cfg['profile'] != $profile->getGoogleId()) {
continue;
}
$antisampling = isset($cfg['antisampling']) ? $cfg['antisampling'] : false;
$this->getData($account, $profile, $tableName, $dateFrom, $dateTo, $antisampling);
$status[$accountId][$profile->getName()][$tableName] = 'ok';
}
} catch (RequestException $e) {
if ($e->getCode() == 401) {
throw new UserException("Expired or wrong credentials, please reauthorize.", $e);
}
if ($e->getCode() == 403) {
if (strtolower($e->getResponse()->getReasonPhrase()) == 'forbidden') {
$this->logger->warning("You don't have access to Google Analytics resource. Probably you don't have access to profile, or profile doesn't exists anymore.");
continue;
} else {
throw new UserException("Reason: " . $e->getResponse()->getReasonPhrase(), $e);
}
}
if ($e->getCode() == 400) {
throw new UserException($e->getMessage());
}
if ($e->getCode() == 503) {
throw new UserException("Google API error: " . $e->getMessage(), $e);
}
throw new ApplicationException($e->getResponse()->getBody(), $e);
}
}
$this->getDataManager()->uploadCsv($profilesCsv->getPathname(), $this->getOutputTable($account, 'profiles'));
}
return $status;
}
public function writeRow($csvFile, $row)
{
$file = new CsvFile($csvFile);
$file->writeRow($row);
}
/**
* @param $query
* @param CsvFile $csv
* @return int Number of rows returned by query
* @throws CsvException
*/
protected function executeQuery($query, CsvFile $csv)
{
$stmt = @$this->db->prepare($query);
@$stmt->execute();
$resultRow = @$stmt->fetch(\PDO::FETCH_ASSOC);
if (is_array($resultRow) && !empty($resultRow)) {
// write header and first line
$csv->writeRow(array_keys($resultRow));
$csv->writeRow($resultRow);
// write the rest
$numRows = 1;
while ($resultRow = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$csv->writeRow($resultRow);
$numRows++;
}
return $numRows;
}
$this->logger->warn("Query returned empty result. Nothing was imported.");
return 0;
}
protected function executeQuery($query, CsvFile $csv)
{
$cursorName = 'exdbcursor' . intval(microtime(true));
$curSql = "DECLARE {$cursorName} CURSOR FOR {$query}";
try {
$this->db->beginTransaction();
// cursors require a transaction.
$stmt = $this->db->prepare($curSql);
$stmt->execute();
$innerStatement = $this->db->prepare("FETCH 1 FROM {$cursorName}");
$innerStatement->execute();
// write header and first line
$resultRow = $innerStatement->fetch(\PDO::FETCH_ASSOC);
if (is_array($resultRow) && !empty($resultRow)) {
$csv->writeRow(array_keys($resultRow));
if (isset($this->dbConfig['replaceNull'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['replaceNull']);
}
$csv->writeRow($resultRow);
// write the rest
$innerStatement = $this->db->prepare("FETCH 5000 FROM {$cursorName}");
while ($innerStatement->execute() && count($resultRows = $innerStatement->fetchAll(\PDO::FETCH_ASSOC)) > 0) {
foreach ($resultRows as $resultRow) {
if (isset($this->dbConfig['replaceNull'])) {
$resultRow = $this->replaceNull($resultRow, $this->dbConfig['replaceNull']);
}
$csv->writeRow($resultRow);
}
}
// close the cursor
$this->db->exec("CLOSE {$cursorName}");
$this->db->commit();
return true;
} else {
$this->logger->warning("Query returned empty result. Nothing was imported.");
return false;
}
} catch (\PDOException $e) {
try {
$this->db->rollBack();
} catch (\Exception $e2) {
}
$innerStatement = null;
$stmt = null;
throw $e;
}
}
public function testWrite()
{
$fileName = __DIR__ . '/_data/_out.csv';
if (file_exists($fileName)) {
unlink($fileName);
}
$csvFile = new \Keboola\Csv\CsvFile($fileName);
$rows = array(array('col1', 'col2'), array('line without enclosure', 'second column'), array('enclosure " in column', 'hello \\'), array('line with enclosure', 'second column'), array('column with enclosure ", and comma inside text', 'second column enclosure in text "'), array("columns with\nnew line", "columns with\ttab"), array('column with \\n \\t \\\\', 'second col'));
foreach ($rows as $row) {
$csvFile->writeRow($row);
}
}
