Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9e1e98e

Browse files
author
Russell Smith
committed
Don't distinct where it's not needed.
DISTINCT and UNION will remove duplicates, requiringa forced sort, unique and temporary table step. It can'tbe flattened or optimized in other ways. So they are removedto allow SQLite to do optimization of searches.
1 parent7f08f0b commit9e1e98e

11 files changed

+71
-89
lines changed

‎Duplicati/Library/Main/Database/LocalBugReportDatabase.cs‎

Lines changed: 9 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -48,10 +48,15 @@ public void Fix()
4848
{
4949

5050
upcmd.Transaction=tr;
51-
upcmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""ID"" INTEGER PRIMARY KEY, ""RealPath"" TEXT NOT NULL, ""Obfuscated"" TEXT NULL)",tablename));
52-
upcmd.ExecuteNonQuery(string.Format(@"INSERT INTO ""{0}"" (""RealPath"") SELECT DISTINCT ""Path"" FROM ""File"" ORDER BY ""Path"" ",tablename));
53-
upcmd.ExecuteNonQuery(string.Format(@"UPDATE ""{0}"" SET ""Obfuscated"" = ? || length(""RealPath"") || ? || ""ID"" || (CASE WHEN substr(""RealPath"", length(""RealPath"")) = ? THEN ? ELSE ? END) ",tablename),Platform.IsClientPosix?"/":"X:\\",Util.DirectorySeparatorString,Util.DirectorySeparatorString,Util.DirectorySeparatorString,".bin");
54-
51+
52+
varstartPath=;
53+
54+
upcmd.ExecuteNonQuery($@"CREATE TEMPORARY TABLE ""{tablename}"" AS
55+
SELECT DISTINCT ""Path"" AS ""RealPath"",
56+
? || length(""RealPath"") || ? || row_number() OVER () ||
57+
CASE WHEN substr(""RealPath"", length(""RealPath"")) = ? THEN ? ELSE ? END) AS ""Obfuscated"" FROM ""File""",
58+
Platform.IsClientPosix?"/":"X:\\",Util.DirectorySeparatorString,Util.DirectorySeparatorString,".bin");
59+
5560
/*long id = 1;
5661
using(var rd = cmd.ExecuteReader(string.Format(@"SELECT ""RealPath"", ""Obfuscated"" FROM ""{0}"" ", tablename)))
5762
while(rd.Read())

‎Duplicati/Library/Main/Database/LocalDatabase.cs‎

Lines changed: 27 additions & 48 deletions
Original file line numberDiff line numberDiff line change
@@ -129,7 +129,7 @@ private LocalDatabase(System.Data.IDbConnection connection)
129129
m_selectremotevolumesCommand.CommandText=@"SELECT ""ID"", ""Name"", ""Type"", ""Size"", ""Hash"", ""State"", ""DeleteGraceTime"" FROM ""Remotevolume""";
130130

131131
m_selectremotevolumeCommand.CommandText=m_selectremotevolumesCommand.CommandText+@" WHERE ""Name"" = ?";
132-
m_selectduplicateRemoteVolumesCommand.CommandText=string.Format(@"SELECTDISTINCT""Name"", ""State"" FROM ""Remotevolume"" WHERE ""Name"" IN (SELECT ""Name"" FROM ""Remotevolume"" WHERE ""State"" IN (""{0}"", ""{1}"")) AND NOT ""State"" IN (""{0}"", ""{1}"")",RemoteVolumeState.Deleted.ToString(),RemoteVolumeState.Deleting.ToString());
132+
m_selectduplicateRemoteVolumesCommand.CommandText=string.Format(@"SELECT ""Name"", ""State"" FROM ""Remotevolume"" WHERE ""Name"" IN (SELECT ""Name"" FROM ""Remotevolume"" WHERE ""State"" IN (""{0}"", ""{1}"")) AND NOT ""State"" IN (""{0}"", ""{1}"")",RemoteVolumeState.Deleted.ToString(),RemoteVolumeState.Deleting.ToString());
133133

134134
m_selectremotevolumeCommand.AddParameter();
135135

@@ -413,18 +413,18 @@ public void RemoveRemoteVolumes(IEnumerable<string> names, System.Data.IDbTransa
413413
deletecmd.Parameters.Clear();
414414

415415

416-
varbsIdsSubQuery=string.Format(
417-
@"SELECTDISTINCT""BlocksetEntry"".""BlocksetID"" FROM ""BlocksetEntry"", ""Block"""
418-
+@"WHERE ""BlocksetEntry"".""BlockID"" = ""Block"".""ID"" AND ""Block"".""VolumeID"" IN ({0}) "
419-
+@"UNION ALL "
420-
+@"SELECT DISTINCT""BlocksetID"" FROM ""BlocklistHash"""
421-
+@"WHERE ""Hash"" IN (SELECT ""Hash"" FROM ""Block"" WHERE ""VolumeID"" IN ({0}))"
422-
,volIdsSubQuery);
416+
varbsIdsSubQuery=
417+
$@"SELECT ""BlocksetEntry"".""BlocksetID"" FROM ""BlocksetEntry"", ""Block""
418+
WHERE ""BlocksetEntry"".""BlockID"" = ""Block"".""ID"" AND ""Block"".""VolumeID"" IN ({0})
419+
UNION ALL
420+
SELECT""BlocksetID"" FROM ""BlocklistHash""
421+
WHERE ""Hash"" IN (SELECT ""Hash"" FROM ""Block"" WHERE ""VolumeID"" IN ({volIdsSubQuery}))";
422+
423423

424424
// Create a temporary table to cache subquery result, as it might take long (SQLite does not cache at all).
425425
deletecmd.ExecuteNonQuery(string.Format(@"CREATE TEMP TABLE ""{0}"" (""ID"" INTEGER PRIMARY KEY)",blocksetidstable));
426426
deletecmd.ExecuteNonQuery(string.Format(@"INSERT OR IGNORE INTO ""{0}"" (""ID"") {1}",blocksetidstable,bsIdsSubQuery));
427-
bsIdsSubQuery=string.Format(@"SELECTDISTINCT""ID"" FROM ""{0}"" ",blocksetidstable);
427+
bsIdsSubQuery=string.Format(@"SELECT ""ID"" FROM ""{0}"" ",blocksetidstable);
428428
deletecmd.Parameters.Clear();
429429

430430
// Create a temp table to associate metadata that is being deleted to a fileset
@@ -441,7 +441,7 @@ WHERE Metadataset.BlocksetID IN ({bsIdsSubQuery})
441441

442442
// Delete FilesetEntry rows that had their metadata deleted
443443
deletecmd.ExecuteNonQuery($@"DELETE FROM FilesetEntry
444-
WHERE FilesetEntry.FilesetID IN (SELECTDISTINCTFilesetID FROM ""{metadataFilesetTable}"")
444+
WHERE FilesetEntry.FilesetID IN (SELECT FilesetID FROM ""{metadataFilesetTable}"")
445445
AND FilesetEntry.FileID IN (
446446
SELECT FilesetEntry.FileID
447447
FROM FilesetEntry
@@ -470,9 +470,9 @@ SELECT ID FROM FileLookup
470470

471471
// Delete from Fileset if FilesetEntry rows were deleted by related metadata and there are no references in FilesetEntry anymore
472472
deletecmd.ExecuteNonQuery($@"DELETE FROM Fileset WHERE Fileset.ID IN
473-
(SELECTDISTINCTFilesetID FROM ""{metadataFilesetTable}"")
473+
(SELECT FilesetID FROM ""{metadataFilesetTable}"")
474474
AND Fileset.ID NOT IN
475-
(SELECTDISTINCTFilesetID FROM FilesetEntry)");
475+
(SELECT FilesetID FROM FilesetEntry)");
476476

477477
// Clean up temp tables for subqueries. We truncate content and then try to delete.
478478
// Drop in try-block, as it fails in nested transactions (SQLite problem)
@@ -777,7 +777,7 @@ GROUP BY ""BlocksetEntry"".""BlocksetID""
777777
}
778778

779779
varreal_count=cmd.ExecuteScalarInt64(@"SELECT Count(*) FROM ""BlocklistHash""",0);
780-
varunique_count=cmd.ExecuteScalarInt64(@"SELECT Count(*) FROM (SELECTDISTINCT""BlocksetID"", ""Index"" FROM ""BlocklistHash"")",0);
780+
varunique_count=cmd.ExecuteScalarInt64(@"SELECT Count(*) FROM (SELECT ""BlocksetID"", ""Index"" FROM ""BlocklistHash"")",0);
781781

782782
if(real_count!=unique_count)
783783
thrownewInvalidDataException(string.Format("Found {0} blocklist hashes, but there should be {1}. Run repair to fix it.",real_count,unique_count));
@@ -799,7 +799,7 @@ GROUP BY ""BlocksetEntry"".""BlocksetID""
799799
using(varcmd2=m_connection.CreateCommand(transaction))
800800
foreach(varfilesetidincmd.ExecuteReaderEnumerable(@"SELECT ""ID"" FROM ""Fileset"" ").Select(x=>x.ConvertValueToInt64(0,-1)))
801801
{
802-
varexpandedCmd=string.Format(@"SELECT COUNT(*) FROM (SELECTDISTINCT""Path"" FROM ({0}) UNIONSELECT DISTINCT ""Path"" FROM ({1}))",LocalDatabase.LIST_FILESETS,LocalDatabase.LIST_FOLDERS_AND_SYMLINKS);
802+
varexpandedCmd=string.Format(@"SELECT COUNT(DISTINCT ""Path"") FROM (SELECT ""Path"" FROM ({0}) UNIONALL SELECT ""Path"" FROM ({1}))",LocalDatabase.LIST_FILESETS,LocalDatabase.LIST_FOLDERS_AND_SYMLINKS);
803803
varexpandedlist=cmd2.ExecuteScalarInt64(expandedCmd,0,filesetid,FOLDER_BLOCKSET_ID,SYMLINK_BLOCKSET_ID,filesetid);
804804
//var storedfilelist = cmd2.ExecuteScalarInt64(string.Format(@"SELECT COUNT(*) FROM ""FilesetEntry"", ""FileLookup"" WHERE ""FilesetEntry"".""FilesetID"" = ? AND ""FileLookup"".""ID"" = ""FilesetEntry"".""FileID"" AND ""FileLookup"".""BlocksetID"" != ? AND ""FileLookup"".""BlocksetID"" != ?"), 0, filesetid, FOLDER_BLOCKSET_ID, SYMLINK_BLOCKSET_ID);
805805
varstoredlist=cmd2.ExecuteScalarInt64(@"SELECT COUNT(*) FROM ""FilesetEntry"" WHERE ""FilesetEntry"".""FilesetID"" = ?",0,filesetid);
@@ -838,7 +838,7 @@ public Block(string hash, long size)
838838
publicIEnumerable<IBlock>GetBlocks(longvolumeid,System.Data.IDbTransactiontransaction=null)
839839
{
840840
using(varcmd=m_connection.CreateCommand(transaction))
841-
using(varrd=cmd.ExecuteReader(@"SELECTDISTINCT""Hash"", ""Size"" FROM ""Block"" WHERE ""VolumeID"" = ?",volumeid))
841+
using(varrd=cmd.ExecuteReader(@"SELECT ""Hash"", ""Size"" FROM ""Block"" WHERE ""VolumeID"" = ?",volumeid))
842842
while(rd.Read())
843843
yieldreturnnewBlock(rd.GetValue(0).ToString(),rd.GetInt64(1));
844844
}
@@ -1158,59 +1158,38 @@ public FilteredFilenameTable(System.Data.IDbConnection connection, Library.Utili
11581158
{
11591159
using(varcmd=m_connection.CreateCommand())
11601160
{
1161-
// TODO: Optimize this to not rely on the "File" view, and not instantiate the paths in full
1162-
1163-
cmd.Transaction=transaction;
1164-
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL)",Tablename));
1165-
using(vartr=newTemporaryTransactionWrapper(m_connection,transaction))
1166-
{
1167-
cmd.CommandText=string.Format(@"INSERT INTO ""{0}"" (""Path"") VALUES (?)",Tablename);
1168-
cmd.AddParameter();
1169-
cmd.Transaction=tr.Parent;
1170-
using(varc2=m_connection.CreateCommand())
1171-
using(varrd=c2.ExecuteReader(@"SELECT DISTINCT ""Path"" FROM ""File"" "))
1172-
while(rd.Read())
1173-
{
1174-
varp=rd.GetValue(0).ToString();
1175-
if(Library.Utility.FilterExpression.Matches(filter,p))
1176-
{
1177-
cmd.SetParameterValue(0,p);
1178-
cmd.ExecuteNonQuery();
1179-
}
1180-
}
1181-
1182-
1183-
tr.Commit();
1184-
}
1161+
cmd.ExecuteNonQuery($@"CREATE TEMPORARY TABLE{Tablename} AS
1162+
SELECT DISTINCT ""B"".""Prefix"" || ""A"".""Path"" AS ""Path"",
1163+
FROM ""FileLookup"" ""A""
1164+
JOIN ""PathPrefix"" ""B"" ON (""A"".""PrefixID"" = ""B"".""ID"")");
11851165
}
11861166
}
11871167
else
11881168
{
11891169
varsb=newStringBuilder();
1170+
varwhereList=newList<string>();
11901171
varargs=newList<object>();
11911172
foreach(varfin((Library.Utility.FilterExpression)filter).GetSimpleList())
11921173
{
11931174
if(type==FilterType.Wildcard)
11941175
{
1195-
sb.Append(@"""Path"" LIKE ? OR");
1176+
whereList.Add(@"""Path"" LIKE ?");
11961177
args.Add(f.Replace('*','%').Replace('?','_'));
11971178
}
11981179
else
11991180
{
1200-
sb.Append(@"""Path"" = ? OR");
1181+
whereList.Add(@"""Path"" = ?");
12011182
args.Add(f);
12021183
}
12031184
}
12041185

1205-
sb.Length=sb.Length-" OR ".Length;
1206-
12071186
using(varcmd=m_connection.CreateCommand())
1208-
using(vartr=newTemporaryTransactionWrapper(m_connection,transaction))
12091187
{
1210-
cmd.Transaction=tr.Parent;
1211-
cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" (""Path"" TEXT NOT NULL)",Tablename));
1212-
cmd.ExecuteNonQuery(string.Format(@"INSERT INTO ""{0}"" SELECT DISTINCT ""Path"" FROM ""File"" WHERE "+sb,Tablename),args.ToArray());
1213-
tr.Commit();
1188+
cmd.ExecuteNonQuery($@"CREATE TEMPORARY TABLE{Tablename} AS
1189+
SELECT DISTINCT ""B"".""Prefix"" || ""A"".""Path"" AS ""Path"",
1190+
FROM ""FileLookup"" ""A""
1191+
JOIN ""PathPrefix"" ""B"" ON (""A"".""PrefixID"" = ""B"".""ID"")
1192+
WHERE{string.Join(" OR ",whereList)}",args.ToArray());
12141193
}
12151194
}
12161195
}

‎Duplicati/Library/Main/Database/LocalDeleteDatabase.cs‎

Lines changed: 9 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -85,17 +85,17 @@ public IEnumerable<KeyValuePair<string, long>> DropFilesetsFromTable(DateTime[]
8585
thrownewException(string.Format("Unexpected number of deleted filesets {0} vs {1}",deleted,toDelete.Length));
8686

8787
//Then we delete anything that is no longer being referenced
88-
cmd.ExecuteNonQuery(@"DELETE FROM ""FilesetEntry"" WHERE ""FilesetID"" NOT IN (SELECTDISTINCT""ID"" FROM ""Fileset"")");
89-
cmd.ExecuteNonQuery(@"DELETE FROM ""ChangeJournalData"" WHERE ""FilesetID"" NOT IN (SELECTDISTINCT""ID"" FROM ""Fileset"")");
90-
cmd.ExecuteNonQuery(@"DELETE FROM ""FileLookup"" WHERE ""ID"" NOT IN (SELECTDISTINCT""FileID"" FROM ""FilesetEntry"") ");
91-
cmd.ExecuteNonQuery(@"DELETE FROM ""Metadataset"" WHERE ""ID"" NOT IN (SELECTDISTINCT""MetadataID"" FROM ""FileLookup"") ");
92-
cmd.ExecuteNonQuery(@"DELETE FROM ""Blockset"" WHERE ""ID"" NOT IN (SELECTDISTINCT""BlocksetID"" FROM ""FileLookup"" UNIONSELECT DISTINCT ""BlocksetID"" FROM ""Metadataset"") ");
93-
cmd.ExecuteNonQuery(@"DELETE FROM ""BlocksetEntry"" WHERE ""BlocksetID"" NOT IN (SELECTDISTINCT""ID"" FROM ""Blockset"") ");
94-
cmd.ExecuteNonQuery(@"DELETE FROM ""BlocklistHash"" WHERE ""BlocksetID"" NOT IN (SELECTDISTINCT""ID"" FROM ""Blockset"") ");
88+
cmd.ExecuteNonQuery(@"DELETE FROM ""FilesetEntry"" WHERE ""FilesetID"" NOT IN (SELECT ""ID"" FROM ""Fileset"")");
89+
cmd.ExecuteNonQuery(@"DELETE FROM ""ChangeJournalData"" WHERE ""FilesetID"" NOT IN (SELECT ""ID"" FROM ""Fileset"")");
90+
cmd.ExecuteNonQuery(@"DELETE FROM ""FileLookup"" WHERE ""ID"" NOT IN (SELECT ""FileID"" FROM ""FilesetEntry"") ");
91+
cmd.ExecuteNonQuery(@"DELETE FROM ""Metadataset"" WHERE ""ID"" NOT IN (SELECT ""MetadataID"" FROM ""FileLookup"") ");
92+
cmd.ExecuteNonQuery(@"DELETE FROM ""Blockset"" WHERE ""ID"" NOT IN (SELECT ""BlocksetID"" FROM ""FileLookup"" UNIONALL SELECT ""BlocksetID"" FROM ""Metadataset"") ");
93+
cmd.ExecuteNonQuery(@"DELETE FROM ""BlocksetEntry"" WHERE ""BlocksetID"" NOT IN (SELECT ""ID"" FROM ""Blockset"") ");
94+
cmd.ExecuteNonQuery(@"DELETE FROM ""BlocklistHash"" WHERE ""BlocksetID"" NOT IN (SELECT ""ID"" FROM ""Blockset"") ");
9595

9696
//We save the block info for the remote files, before we delete it
97-
cmd.ExecuteNonQuery(@"INSERT INTO ""DeletedBlock"" (""Hash"", ""Size"", ""VolumeID"") SELECT ""Hash"", ""Size"", ""VolumeID"" FROM ""Block"" WHERE ""ID"" NOT IN (SELECTDISTINCT""BlockID"" AS ""BlockID"" FROM ""BlocksetEntry"" UNIONSELECT DISTINCT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");
98-
cmd.ExecuteNonQuery(@"DELETE FROM ""Block"" WHERE ""ID"" NOT IN (SELECTDISTINCT""BlockID"" FROM ""BlocksetEntry"" UNIONSELECT DISTINCT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");
97+
cmd.ExecuteNonQuery(@"INSERT INTO ""DeletedBlock"" (""Hash"", ""Size"", ""VolumeID"") SELECT ""Hash"", ""Size"", ""VolumeID"" FROM ""Block"" WHERE ""ID"" NOT IN (SELECT ""BlockID"" AS ""BlockID"" FROM ""BlocksetEntry"" UNIONALL SELECT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");
98+
cmd.ExecuteNonQuery(@"DELETE FROM ""Block"" WHERE ""ID"" NOT IN (SELECT ""BlockID"" FROM ""BlocksetEntry"" UNIONALL SELECT ""ID"" FROM ""Block"", ""BlocklistHash"" WHERE ""Block"".""Hash"" = ""BlocklistHash"".""Hash"") ");
9999

100100
//Find all remote filesets that are no longer required, and mark them as delete
101101
varupdated=cmd.ExecuteNonQuery(@"UPDATE ""RemoteVolume"" SET ""State"" = ? WHERE ""Type"" = ? AND ""State"" IN (?, ?, ?) AND ""ID"" NOT IN (SELECT ""VolumeID"" FROM ""Fileset"") ",RemoteVolumeState.Deleting.ToString(),RemoteVolumeType.Files.ToString(),RemoteVolumeState.Uploaded.ToString(),RemoteVolumeState.Verified.ToString(),RemoteVolumeState.Temporary.ToString());

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp