23CREATE TABLE IF NOT EXISTS projects
28 last_audio_preview_save INTEGER,
30 last_modified INTEGER,
33 PRIMARY KEY (project_id)
36CREATE INDEX IF NOT EXISTS local_path_index ON projects (local_path);
38CREATE TABLE IF NOT EXISTS block_hashes
43 PRIMARY KEY (project_id, block_id)
46CREATE INDEX IF NOT EXISTS block_hashes_index ON block_hashes (hash);
48CREATE TABLE IF NOT EXISTS pending_snapshots
53 PRIMARY KEY (project_id, snapshot_id)
56CREATE TABLE IF NOT EXISTS pending_project_blobs
66 PRIMARY KEY (project_id, snapshot_id)
69CREATE TABLE IF NOT EXISTS pending_project_blocks
79 block_sample_format INTEGER,
81 PRIMARY KEY (project_id, snapshot_id, block_id)
84CREATE TABLE IF NOT EXISTS project_users
88 PRIMARY KEY (project_id)
91CREATE TABLE IF NOT EXISTS migration
96INSERT INTO migration (version) VALUES (0);
98DELETE FROM migration WHERE ROWID != 1;
102ALTER TABLE projects ADD COLUMN synced_dialog_shown INTEGER DEFAULT 1;
132std::optional<DBProjectData>
140 auto statement = connection->CreateStatement(
141 "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects WHERE project_id = ? LIMIT 1");
150 const std::string& projectFilePath)
const
157 auto statement = connection->CreateStatement(
158 "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects WHERE local_path = ? LIMIT 1");
166std::vector<DBProjectData>
169 std::vector<DBProjectData> result;
171 auto connection = GetConnection();
176 auto statement = connection->CreateStatement(
177 "SELECT project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown FROM projects");
182 auto runResult = statement->Prepare().Run();
184 for (
auto row : runResult)
186 auto data = DoGetProjectData(row);
189 result.push_back(*data);
196 std::string_view projectId)
198 auto connection = GetConnection();
203 auto tx = connection->BeginTransaction(
"DeleteProject");
204 if (DeleteProject(connection, projectId))
209 std::string_view projectId, std::string_view snapshotId)
216 auto statement = connection->CreateStatement(
217 "UPDATE projects SET sync_status = ? WHERE project_id = ? AND snapshot_id = ?");
222 auto result = statement
225 projectId, snapshotId)
231 return result.GetModifiedRowsCount() > 0;
242 auto inProjectSet = connection->CreateScalarFunction(
243 "inProjectSet", [&blockSet](int64_t blockIndex)
244 {
return blockSet.find(blockIndex) != blockSet.end(); });
246 auto statement = connection->CreateStatement(
247 "DELETE FROM block_hashes WHERE project_id = ? AND NOT inProjectSet(block_id)");
249 auto result = statement->Prepare(projectId).Run();
258 std::string_view projectId, int64_t blockId)
const
265 auto statement = connection->CreateStatement(
266 "SELECT hash FROM block_hashes WHERE project_id = ? AND block_id = ? LIMIT 1");
271 auto result = statement->Prepare(projectId, blockId).Run();
273 for (
auto row : result)
277 if (!row.Get(0, hash))
287 std::string_view projectId,
288 const std::vector<std::pair<int64_t, std::string>>& hashes)
295 const int localVar {};
296 auto transaction = connection->BeginTransaction(
297 std::string(
"UpdateBlockHashes_") +
298 std::to_string(
reinterpret_cast<size_t>(&localVar)));
300 auto statement = connection->CreateStatement(
301 "INSERT OR REPLACE INTO block_hashes (project_id, block_id, hash) VALUES (?, ?, ?)");
303 for (
const auto& [blockId, hash] : hashes)
304 statement->Prepare(projectId, blockId, hash).Run();
306 transaction.Commit();
316 auto tx = connection->BeginTransaction(
"UpdateProjectData");
318 auto updateProjectData = connection->CreateStatement(
319 "INSERT OR REPLACE INTO projects (project_id, snapshot_id, saves_count, last_audio_preview_save, local_path, last_modified, last_read, sync_status, synced_dialog_shown) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
321 if (!updateProjectData)
324 auto result = updateProjectData
336 auto listMissingProjects = connection->CreateStatement (
337 "SELECT project_id FROM projects WHERE project_id != ? AND local_path = ?");
339 if (!listMissingProjects)
342 auto missingProjects = listMissingProjects->Prepare(
345 for (
auto row : missingProjects)
347 std::string missingProjectId;
349 if (!row.Get(0, missingProjectId))
356 return tx.Commit().IsOk();
360 std::string_view projectId)
const
362 auto connection = GetConnection();
367 auto statement = connection->CreateStatement (
368 "SELECT synced_dialog_shown FROM projects WHERE project_id = ? LIMIT 1");
373 auto result = statement->Prepare(projectId).Run();
375 for (
auto row : result)
379 if (!row.Get(0, shown))
389 std::string_view projectId,
bool shown)
391 auto connection = GetConnection();
396 auto statement = connection->CreateStatement (
397 "UPDATE projects SET synced_dialog_shown = ? WHERE project_id = ?");
402 statement->Prepare(shown, projectId).Run();
413 auto statement = connection->CreateStatement(
414 "SELECT user_name FROM project_users WHERE project_id = ? LIMIT 1");
419 auto result = statement->Prepare(projectId).Run();
421 for (
auto row : result)
425 if (!row.Get(0, slug))
435 std::string_view projectId, std::string_view slug)
442 auto statement = connection->CreateStatement(
443 "INSERT OR REPLACE INTO project_users (project_id, user_name) VALUES (?, ?)");
448 statement->Prepare(projectId, slug).Run();
452 std::string_view projectId, int64_t blockId)
const
459 auto statement = connection->CreateStatement(
460 "SELECT 1 FROM pending_project_blocks WHERE project_id = ? AND block_id = ? LIMIT 1");
465 auto result = statement->Prepare(projectId, blockId).Run();
467 for (
auto row : result)
481 auto statement = connection->CreateStatement(
482 "INSERT OR REPLACE INTO pending_snapshots (project_id, snapshot_id, confirm_url) VALUES (?, ?, ?)");
495 std::string_view projectId, std::string_view snapshotId)
502 static const char* queries[] = {
503 "DELETE FROM pending_snapshots WHERE project_id = ? AND snapshot_id = ?",
504 "DELETE FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?",
505 "DELETE FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?",
508 auto tx = connection->BeginTransaction(
"RemovePendingSnapshot");
510 for (
auto query : queries)
512 auto statement = connection->CreateStatement(query);
517 if (!statement->Prepare(projectId, snapshotId).Run().IsOk())
524std::vector<PendingSnapshotData>
532 auto statement = connection->CreateStatement(
533 "SELECT project_id, snapshot_id, confirm_url FROM pending_snapshots WHERE project_id = ?");
538 auto result = statement->Prepare(projectId).Run();
540 std::vector<PendingSnapshotData> snapshots;
542 for (
auto row : result)
555 snapshots.push_back(data);
569 auto statement = connection->CreateStatement(
570 "INSERT OR REPLACE INTO pending_project_blobs (project_id, snapshot_id, upload_url, confirm_url, fail_url, blob) VALUES (?, ?, ?, ?, ?, ?)");
586 std::string_view projectId, std::string_view snapshotId)
593 auto statement = connection->CreateStatement(
594 "DELETE FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?");
599 statement->Prepare(projectId, snapshotId).Run();
602std::optional<PendingProjectBlobData>
604 std::string_view projectId, std::string_view snapshotId)
const
611 auto statement = connection->CreateStatement(
612 "SELECT project_id, snapshot_id, upload_url, confirm_url, fail_url, blob FROM pending_project_blobs WHERE project_id = ? AND snapshot_id = ?");
617 auto result = statement->Prepare(projectId, snapshotId).Run();
619 for (
auto row : result)
638 const auto size = row.GetColumnBytes(6);
651 const std::vector<PendingProjectBlockData>& blockData)
658 auto tx = connection->BeginTransaction(
"AddPendingProjectBlocks");
660 auto statement = connection->CreateStatement(
661 "INSERT OR REPLACE INTO pending_project_blocks (project_id, snapshot_id, upload_url, confirm_url, fail_url, block_id, block_sample_format, block_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
666 for (
const auto& data : blockData)
668 .Bind(1, data.ProjectId)
669 .Bind(2, data.SnapshotId)
670 .Bind(3, data.UploadUrl)
671 .Bind(4, data.ConfirmUrl)
672 .Bind(5, data.FailUrl)
673 .Bind(6, data.BlockId)
674 .Bind(7, data.BlockSampleFormat)
675 .Bind(8, data.BlockHash)
682 std::string_view projectId, int64_t blockId)
689 auto statement = connection->CreateStatement(
690 "DELETE FROM pending_project_blocks WHERE project_id = ? AND block_id = ?");
695 statement->Prepare(projectId, blockId).Run();
699 std::string_view projectId, std::string_view snapshotId)
706 auto statement = connection->CreateStatement(
707 "DELETE FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?");
712 statement->Prepare(projectId, snapshotId).Run();
715std::vector<PendingProjectBlockData>
717 std::string_view projectId, std::string_view snapshotId)
724 auto statement = connection->CreateStatement(
725 "SELECT project_id, snapshot_id, upload_url, confirm_url, fail_url, block_id, block_sample_format, block_hash FROM pending_project_blocks WHERE project_id = ? AND snapshot_id = ?");
730 auto result = statement->Prepare(projectId, snapshotId).Run();
732 std::vector<PendingProjectBlockData> blocks;
734 for (
auto row : result)
762 blocks.push_back(data);
768std::optional<DBProjectData>
795 if (!row.
Get(7, status))
806std::optional<DBProjectData>
810 for (
auto row : result)
812 if (
auto data = DoGetProjectData(row))
825 const auto configPath = configDir +
"/audiocom_sync.db";
849 auto getMigrationVersion =
850 connection->CreateStatement(
"SELECT version FROM migration LIMIT 1");
852 if (!getMigrationVersion)
855 auto versionResult = getMigrationVersion->Prepare().Run();
857 if (!versionResult.IsOk())
862 for (
auto row : versionResult)
864 if (!row.Get(0, version))
871 if (version >= migrationsCount)
874 auto tx = connection->BeginTransaction(
"RunMigrations");
876 for (
int i = version; i < migrationsCount; ++i)
878 auto result = connection->Execute(
migrations[i]);
885 connection->CreateStatement(
"UPDATE migration SET version = ?");
890 if (!updateVersion->Prepare(migrationsCount).Run().IsOk())
893 return tx.Commit().IsOk();
899 static const char* queries[] = {
900 "DELETE FROM projects WHERE project_id = ?",
901 "DELETE FROM block_hashes WHERE project_id = ?",
902 "DELETE FROM pending_snapshots WHERE project_id = ?",
903 "DELETE FROM pending_project_blobs WHERE project_id = ?",
904 "DELETE FROM pending_project_blocks WHERE project_id = ?",
905 "DELETE FROM project_users WHERE project_id = ?",
908 for (
auto query : queries)
915 if (!statement->Prepare(projectId).Run().IsOk())
std::unordered_set< SampleBlockID > SampleBlockIDSet
Declare functions to perform UTF-8 to std::wstring conversions.
std::optional< DBProjectData > DoGetProjectData(const sqlite::Row &result) const
void UpdateBlockHashes(std::string_view projectId, const std::vector< std::pair< int64_t, std::string > > &hashes)
void DeleteProject(std::string_view projectId)
std::optional< std::string > GetBlockHash(std::string_view projectId, int64_t blockId) const
bool UpdateProjectData(const DBProjectData &projectData)
void AddPendingSnapshot(const PendingSnapshotData &snapshotData)
static CloudProjectsDatabase & Get()
void UpdateProjectBlockList(std::string_view projectId, const SampleBlockIDSet &blockSet)
bool MarkProjectAsSynced(std::string_view projectId, std::string_view snapshotId)
std::mutex mConnectionMutex
void SetProjectUserSlug(std::string_view projectId, std::string_view slug)
std::vector< PendingProjectBlockData > GetPendingProjectBlocks(std::string_view projectId, std::string_view snapshotId)
std::optional< DBProjectData > GetProjectData(std::string_view projectId) const
std::shared_ptr< sqlite::SafeConnection > mConnection
void SetFirstSyncDialogShown(std::string_view projectId, bool shown=true)
void RemovePendingProjectBlock(std::string_view projectId, int64_t blockId)
bool IsFirstSyncDialogShown(std::string_view projectId) const
void AddPendingProjectBlocks(const std::vector< PendingProjectBlockData > &blockData)
void AddPendingProjectBlob(const PendingProjectBlobData &blobData)
void RemovePendingSnapshot(std::string_view projectId, std::string_view snapshotId)
bool IsProjectBlockLocked(std::string_view projectId, int64_t blockId) const
std::vector< PendingSnapshotData > GetPendingSnapshots(std::string_view projectId) const
std::optional< DBProjectData > GetProjectDataForPath(const std::string &projectPath) const
void RemovePendingProjectBlob(std::string_view projectId, std::string_view snapshotId)
std::vector< DBProjectData > GetCloudProjects() const
void RemovePendingProjectBlocks(std::string_view projectId, std::string_view snapshotId)
std::string GetProjectUserSlug(std::string_view projectId)
std::optional< PendingProjectBlobData > GetPendingProjectBlob(std::string_view projectId, std::string_view snapshotId) const
sqlite::SafeConnection::Lock GetConnection()
Result< Statement > CreateStatement(std::string_view sql) const
Prepares the given SQL statement for execution.
A class representing a row in a result set.
bool Get(int columnIndex, bool &value) const
A class representing a result of a run operation.
static std::shared_ptr< SafeConnection > Open(std::string_view path, OpenMode mode=OpenMode::ReadWriteCreate, ThreadMode threadMode=ThreadMode::Serialized, Error *openError=nullptr)
FILES_API FilePath ConfigDir()
Audacity user config directory.
const char * migrations[]
const char * createTableQuery
const auto addProjectSyncedDialogShownColumn
std::string ToUTF8(const std::wstring &wstr)
enum audacity::cloud::audiocom::sync::DBProjectData::SyncStatusType SyncStatus
bool FirstSyncDialogShown
std::vector< uint8_t > BlobData