Compare commits
13
Commits
| Author | SHA1 | Date | |
|---|---|---|---|
  jimandClaude Opus 5
|
4d37999f8e | ||
|
jim
|
387c023900 | ||
|
jimandClaude Opus 5
|
a9bd5a4c37 | ||
|
jim
|
70b32dfc89 | ||
|
jimandClaude Opus 5
|
8f4177a0c9 | ||
|
jim
|
a2763d0026 | ||
|
jimandClaude Opus 5
|
721224bc13 | ||
|
jim
|
ef6629d86a | ||
|
jimandClaude Opus 5
|
6320e2c0c9 | ||
|
jim
|
6136901cc7 | ||
|
jimandClaude Opus 5
|
83e35a2323 | ||
|
jim
|
f0ccac6503 | ||
|
jimandClaude Opus 5
|
e1d2eb48c2 |
Binary file not shown.
@@ -67,6 +67,93 @@ say nothing about the item being fetched, so they must not be recorded as per-it
|
||||
- Do not add these columns from this app, and do not add them to the missing-column list in
|
||||
`verify-db-schema.sql`
|
||||
|
||||
### `DateModified` Tracks Real Changes Only
|
||||
`Blogs.DateModified`, `Posts.DateModified` and `Notes.DateModified` must move only when a
|
||||
column beside `DateModified` itself actually changed. Re-crawling or re-ingesting identical
|
||||
content has to leave the row — and its timestamp — untouched, or downstream consumers cannot
|
||||
tell a refreshed row from a rewritten one.
|
||||
|
||||
- Enforce it in the `WHERE` clause, not in C#. Every `UPDATE` that sets `DateModified` ends
|
||||
with an `AND (<col> <> @param OR ...)` term covering every column in its `SET` list, so
|
||||
SQLite matches zero rows on a no-op and never writes
|
||||
- Compare NULL-safely: `IFNULL(col, '') <> IFNULL(@param, '')` for text,
|
||||
`IFNULL(col, 0) <> @param` for integer flags. A bare `col <> @param` is NULL on a NULL
|
||||
column and silently skips the row that most needs writing
|
||||
- Where NULL is not equivalent to the default, spell it out. The `HasBeenOutput = 0` stamps
|
||||
use `(HasBeenOutput IS NULL OR HasBeenOutput <> 0)` because the selection queries test
|
||||
`HasBeenOutput = 0`, which a NULL would never match
|
||||
- Dynamic `SET` lists (`UpdatePostContentFields`) build the guard alongside the assignments
|
||||
so the two lists cannot drift apart
|
||||
- These statements now return 0 rows for "found but unchanged" as well as "not found".
|
||||
Callers that read `ExecuteNonQuery()` must not treat 0 as "row missing"
|
||||
|
||||
**`Posts.NotesGatheredDateTime` is crawl bookkeeping, not content.** It moves on every
|
||||
`-collect` pass and says nothing about the post, so it must never move `DateModified` on its
|
||||
own. `UpdatePostMarkNotesCollected` still writes it every pass but wraps the timestamp in
|
||||
`DateModified = CASE WHEN IFNULL(HasNotesGathered, 0) <> 1 THEN @dateModified ELSE
|
||||
DateModified END` — SQLite evaluates `SET` expressions against the pre-`UPDATE` row, so only
|
||||
the flag flipping counts as a modification. Use this shape for any column that has to be
|
||||
refreshed unconditionally without being a change. `Blogs.LikesLastRefreshed` is the
|
||||
deliberate exception: a refresh pass is treated as a real event on the blog row.
|
||||
|
||||
**`Blogs.DateAdded` is write-once.** `AddBlog`'s `INSERT` is the only place that sets it. A
|
||||
new post arriving for a known blog reopens `HasBeenOutput` but must leave `DateAdded` alone —
|
||||
a new post is not a new blog, and rewriting the column both destroys the registration date
|
||||
and makes every insert look like a change.
|
||||
|
||||
**`"."` in a `Posts` content field means "not supplied", not "empty".** `ReblogRecord`
|
||||
(`TraverseDirectory`'s parser for the local `.txt` export tree) and the `--likes` API path
|
||||
both default every content field to the literal string `"."` when their source has no value
|
||||
for it, then pass that straight to `UpdatePost`. A blog with two export folders in different
|
||||
field formats (a duplicate `_2` folder, or a Tumblr export whose field set changed over time)
|
||||
sends one record with a real `Title`/`Tags`/`Slug` and another with those fields `"."`
|
||||
because that format never had a line for them — and without a guard, re-importing both on
|
||||
every run flips the row back and forth forever, bumping `DateModified` on every pass even
|
||||
though the true content never changes.
|
||||
|
||||
- Every content column in `UpdatePost`'s `SET` list is guarded the same way `RootBlogName`/
|
||||
`RootURL` already were: `col = CASE WHEN @col = '.' THEN col ELSE @col END`. A `"."`
|
||||
parameter leaves the existing value alone instead of overwriting it
|
||||
- The change-detection `WHERE` clause carries the same exception —
|
||||
`(@col <> '.' AND IFNULL(col, '') <> @col) OR ...` — so a `"."`-only difference does not
|
||||
make the statement fire at all, and `DateModified` stays put
|
||||
- Deliberately narrow: only the literal `"."` is the sentinel. An explicit empty string from
|
||||
a real record still overwrites, same as before this fix. `postID`, `BlogName`, `hasImage`,
|
||||
`ByLikes` are not part of this convention and are unaffected
|
||||
- If a new content field is added to `Posts`/`UpdatePost`, decide explicitly whether its
|
||||
source can legitimately supply `"."` as "field absent" before deciding whether it needs
|
||||
the same `CASE` treatment — don't assume every column needs it
|
||||
|
||||
**`--ingest` (`UpsertPostFromTextFile`) uses `NULL`, not `"."`, for the same "field absent"
|
||||
convention, and reconciling exactly this kind of duplicate IS the feature's job.**
|
||||
`IngestMode` strips a trailing `_N` from the folder name before it ever reaches
|
||||
`UpsertPostFromTextFile`, so a duplicate export folder collapses onto the same `BlogName` on
|
||||
purpose — the whole point is to merge multiple differently-formatted files for the same post
|
||||
into one row. `IngestMode.G(key)` returns `null` (not `"."`) when a field's line is absent
|
||||
from a given file, `LegacyPostsDbImporter` passes `null` straight from a `NULL` source column,
|
||||
and files are walked in raw filesystem enumeration order — never sorted — so which file's call
|
||||
lands last for a given `(BlogName, PostID)` is arbitrary.
|
||||
|
||||
- Before the fix, the `UPDATE` branch set every column unconditionally, so whichever file
|
||||
processed last for a `PostID` would null out every field its own record didn't carry —
|
||||
silently erasing real `Title`/`Slug`/`Tags`/… another file had, the opposite of what
|
||||
`--ingest` exists to do. This is worse than the `"."` case above: that one only caused
|
||||
churn (the two writes canceled out); this one loses data, and which posts lose which
|
||||
fields depends on filesystem enumeration order
|
||||
- Same shape of fix, `NULL` instead of `"."` as the sentinel: `col = CASE WHEN @col IS NULL
|
||||
THEN col ELSE @col END` in the `SET` list, `(@col IS NOT NULL AND IFNULL(col, '') <> @col)
|
||||
OR ...` in the change-detection
|
||||
- Same narrow rule: only `NULL` (the field's line was never present in this file) is the
|
||||
sentinel. `G()` already distinguishes this from "present but blank" — a dictionary miss is
|
||||
`null`, an empty value after the prefix is `""` — so an explicitly blank field still
|
||||
overwrites
|
||||
- `HasImage` is **not** guarded and remains a known gap: `IngestMode` always computes a
|
||||
concrete `bool` (defaulting `false` when a file has no `Has Image:` line), so there is no
|
||||
way for this function to tell "this format says no image" from "this format doesn't report
|
||||
it at all" without changing the parameter to `bool?` and threading that through
|
||||
`IngestMode`/`LegacyPostsDbImporter`. Fix this the same way if `--ingest` is observed
|
||||
downgrading a post's `HasImage` from `1` to `0`
|
||||
|
||||
### Testing
|
||||
- No existing test suite; use xUnit if adding tests
|
||||
- Test critical logic: `ApiKeyPool` init, color parsing, config persistence
|
||||
|
||||
+39
-6
@@ -1,4 +1,4 @@
|
||||
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="C:/Users/jim/Nextcloud/C#/URLNotesGrabberCORE/URLNotesGrabberCORE/TL.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="4305"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="Posts" custom_title="0" dock_id="4" table="4,5:mainPosts"/><dock_state state="000000ff00000000fd00000001000000020000077200000379fc0100000006fb000000160064006f0063006b00420072006f00770073006500310100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500320100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500330100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500350100000000000005f40000000000000000fb000000160064006f0063006b00420072006f00770073006500340100000000000007720000011700fffffffb000000160064006f0063006b00420072006f00770073006500340100000000000005f40000000000000000000007720000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="ApiKeyPoolMeta" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="29"/><column index="2" value="64"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Blogs" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="257"/><column index="2" value="95"/><column index="3" value="54"/><column index="4" value="156"/><column index="5" value="51"/><column index="6" value="71"/><column index="7" value="85"/><column index="8" value="156"/><column index="9" value="156"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Posts" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="28" mode="1"/></sort><column_widths><column index="1" value="241"/><column index="2" value="148"/><column index="3" value="126"/><column index="4" value="300"/><column index="5" value="75"/><column index="6" value="187"/><column index="7" value="159"/><column index="8" value="75"/><column index="9" value="300"/><column index="10" value="300"/><column index="11" value="78"/><column index="12" value="249"/><column index="13" value="300"/><column index="14" value="53"/><column index="15" value="300"/><column index="16" value="300"/><column index="17" value="41"/><column index="18" value="75"/><column index="19" value="96"/><column index="20" value="300"/><column index="21" value="96"/><column index="22" value="300"/><column index="23" value="300"/><column index="24" value="42"/><column index="25" value="60"/><column index="26" value="218"/><column index="27" value="920"/><column index="28" value="156"/><column index="29" value="156"/></column_widths><filter_values><column index="24" value="=1"/><column index="28" value=">2026-05-06 20:00:01"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">UPDATE Posts
|
||||
<?xml version="1.0" encoding="UTF-8"?><sqlb_project><db path="D:/NextCloud/C#/URLNotesGrabberCORE/URLNotesGrabberCORE/TL.db" readonly="0" foreign_keys="1" case_sensitive_like="0" temp_store="0" wal_autocheckpoint="1000" synchronous="2"/><attached/><window><main_tabs open="structure browser pragmas query" current="3"/></window><tab_structure><column_width id="0" width="300"/><column_width id="1" width="0"/><column_width id="2" width="100"/><column_width id="3" width="4486"/><column_width id="4" width="0"/><expanded_item id="0" parent="1"/><expanded_item id="1" parent="1"/><expanded_item id="2" parent="1"/><expanded_item id="3" parent="1"/></tab_structure><tab_browse><table title="Posts" custom_title="0" dock_id="4" table="4,5:mainPosts"/><dock_state state="000000ff00000000fd0000000100000002000005470000029afc0100000006fb000000160064006f0063006b00420072006f00770073006500310100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500320100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500330100000000000004a10000000000000000fb000000160064006f0063006b00420072006f00770073006500350100000000000005f40000000000000000fb000000160064006f0063006b00420072006f00770073006500340100000000000005470000011100fffffffb000000160064006f0063006b00420072006f00770073006500340100000000000005f40000000000000000000005470000000000000004000000040000000800000008fc00000000"/><default_encoding codec=""/><browse_table_settings><table schema="main" name="ApiKeyPoolMeta" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="29"/><column index="2" value="64"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Blogs" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="7" mode="1"/></sort><column_widths><column index="1" value="257"/><column index="2" value="108"/><column index="3" value="63"/><column index="4" value="156"/><column index="5" value="60"/><column index="6" value="81"/><column index="7" value="85"/><column index="8" value="156"/><column index="9" value="156"/><column index="10" value="151"/><column index="11" value="125"/><column index="12" value="129"/></column_widths><filter_values><column index="4" value="1"/><column index="7" value=">2026-05-27 17:22:36"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Notes" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort/><column_widths><column index="1" value="198"/><column index="2" value="144"/><column index="3" value="251"/><column index="4" value="84"/><column index="5" value="53"/><column index="6" value="300"/><column index="7" value="116"/><column index="8" value="152"/><column index="9" value="89"/><column index="10" value="63"/></column_widths><filter_values/><conditional_formats/><row_id_formats/><display_formats/><hidden_columns/><plot_y_axes/><global_filter/></table><table schema="main" name="Posts" show_row_id="0" encoding="" plot_x_axis="" unlock_view_pk="_rowid_" freeze_columns="0"><sort><column index="14" mode="1"/></sort><column_widths><column index="1" value="236"/><column index="2" value="144"/><column index="3" value="126"/><column index="4" value="32"/><column index="5" value="32"/><column index="6" value="32"/><column index="7" value="32"/><column index="8" value="32"/><column index="9" value="0"/><column index="10" value="0"/><column index="11" value="0"/><column index="12" value="243"/><column index="13" value="300"/><column index="14" value="53"/><column index="15" value="37351"/><column index="16" value="300"/><column index="17" value="41"/><column index="18" value="75"/><column index="19" value="96"/><column index="20" value="300"/><column index="21" value="96"/><column index="22" value="300"/><column index="23" value="300"/><column index="24" value="548"/><column index="25" value="60"/><column index="26" value="213"/><column index="27" value="532"/><column index="28" value="152"/><column index="29" value="152"/><column index="30" value="69"/><column index="31" value="63"/></column_widths><filter_values><column index="2" value="0"/><column index="1" value="734568371821084672"/></filter_values><conditional_formats/><row_id_formats/><display_formats/><hidden_columns><column index="9" value="1"/><column index="10" value="1"/><column index="11" value="1"/></hidden_columns><plot_y_axes/><global_filter/></table></browse_table_settings></tab_browse><tab_sql><sql name="SQL 1">UPDATE Posts
|
||||
SET HasNotesGathered = 0
|
||||
WHERE (BlogName, PostID) IN (
|
||||
SELECT p.BlogName, p.PostID
|
||||
@@ -29,11 +29,12 @@ blogname in
|
||||
'nudenymph',
|
||||
'caylachief'
|
||||
|
||||
)</sql><sql name="New Notes">select RootBlogName, PostID, NoteBlogName || '.tumblr.com' as NoteBlogName, DatetimeCrawled, TimeStamp, type, RootBlogName || '.tumblr.com/post/' || postid, datetime(timestamp, 'unixepoch')
|
||||
from Notes
|
||||
)</sql><sql name="New Notes">select P.slug, N.replyText, n.RootBlogName, n.PostID, NoteBlogName || '.tumblr.com' as NoteBlogName, DatetimeCrawled, TimeStamp, type, n.RootBlogName || '.tumblr.com/post/' || n.postid, datetime(timestamp, 'unixepoch')
|
||||
from Notes N inner join Posts P on p.PostID = n.PostID
|
||||
where
|
||||
DatetimeCrawled > '2026-05-14 02:50:05' --and type like 'r%'
|
||||
order by DatetimeCrawled desc</sql><sql name="Pull Blogs*">SELECT distinct␍
|
||||
DatetimeCrawled > '2026-08-07 11:47:22' and type like 'r%'
|
||||
and P.IsActive = 1
|
||||
order by n.DatetimeCrawled</sql><sql name="Pull Blogs">SELECT distinct
|
||||
'''' || blogname || ''',',
|
||||
blogs.*
|
||||
, blogname || '.tumblr.com'
|
||||
@@ -64,4 +65,36 @@ JOIN ReplyCounts c ON n.NoteBlogName = c.NoteBlogName
|
||||
where replyText <> '.' and type <> 'reply'
|
||||
--AND N.NoteBlogName NOT IN ( 'roadblocker21', 'thesaddemon666', 'edwardabbeyhoffman', 'tattedsoldier20', 'zomb-eh', 'animalistic13', 'indken', 'maccloud1592',
|
||||
--'moss-wizard', 'supertrucker12682', 'exploringthrupics', 'padeyepete' )
|
||||
order by c.DistinctReplyCount desc, n.NoteBlogName, n.DateModified desc, replyText, RootBlogName, PostID</sql><current_tab id="3"/></tab_sql></sqlb_project>
|
||||
order by c.DistinctReplyCount desc, n.NoteBlogName, n.DateModified desc, replyText, RootBlogName, PostID</sql><sql name="Collect">WITH PostsWithCount AS ( SELECT P.BlogName, P.PostID, 1925013599 AS LatestNoteTimestamp, P.NotesGatheredDateTime, COUNT(P.PostID) OVER(PARTITION BY P.BlogName) AS CNT, P.HasNotesGathered, P.NotFound, P.PostDate FROM Posts P WHERE COALESCE(P.IsActive, 1) = 1 ), Unioned AS ( SELECT BlogName, PostID, LatestNoteTimestamp, NotesGatheredDateTime, CNT, PostDate FROM PostsWithCount WHERE NotFound = 0 AND HasNotesGathered = 0 UNION SELECT BlogName, PostID, LatestNoteTimestamp, NotesGatheredDateTime, CNT, PostDate FROM PostsWithCount WHERE BlogName = 'zomb-eh' AND NotFound = 0 AND NotesGatheredDateTime < unixepoch('now', 'localtime', '-3 days') ) SELECT U.BlogName, U.PostID, U.LatestNoteTimestamp, U.NotesGatheredDateTime, U.CNT FROM Unioned U WHERE (U.NotesGatheredDateTime < 1786134037 OR U.NotesGatheredDateTime IS NULL) ORDER BY U.NotesGatheredDateTime, U.PostDate DESC, U.BlogName, U.PostID;</sql><sql name="Del Posts">delete from posts where postid in
|
||||
(
|
||||
'741662499571728384',
|
||||
178892849664,
|
||||
178264721139,
|
||||
177012868749,
|
||||
169950081964,
|
||||
755440787056099328
|
||||
)</sql><sql name="notes NO post*">select *
|
||||
-- delete
|
||||
from notes
|
||||
where postid not in (select distinct postid from posts where IsActive = 1)</sql><sql name="SQL 9">SELECT
|
||||
*
|
||||
FROM
|
||||
POSTS P
|
||||
WHERE
|
||||
P.ByLikes = 1
|
||||
AND
|
||||
P.DateCreated > '2026-05-26 17:47:32'
|
||||
ORDER BY
|
||||
P.DateCreated desc</sql><sql name="SQL 13">update posts set IsActive = 0 where blogname IN ( 'shoebiedoo', 'redheaded-girlygirl', 'xlittle-ghost' )</sql><sql name="SQL 14*">update Posts␍
|
||||
set IsActive = 0␍
|
||||
where postid in␍
|
||||
(␍
|
||||
␍
|
||||
␍
|
||||
'731937314675310592'␍
|
||||
␍
|
||||
␍
|
||||
␍
|
||||
)␍
|
||||
␍
|
||||
</sql><current_tab id="7"/></tab_sql></sqlb_project>
|
||||
|
||||
Binary file not shown.
@@ -106,6 +106,10 @@ namespace URLNotesGrabberCORE
|
||||
}
|
||||
}
|
||||
|
||||
// The database every DataAccess call defaults to, exposed so modes can report
|
||||
// which file they actually read when their results are surprising.
|
||||
public static string GetActiveDbPath() => GetDefaultDbPath();
|
||||
|
||||
private static string GetDefaultDbPath()
|
||||
{
|
||||
if (_cachedDbPath != null)
|
||||
@@ -598,7 +602,7 @@ namespace URLNotesGrabberCORE
|
||||
{
|
||||
if (ownsConnection) connection.Open();
|
||||
|
||||
string updateSql = "UPDATE Posts SET hasImage = @hasImage, DateModified = @DateModified WHERE blogName = @blogName AND postID = @postID";
|
||||
string updateSql = "UPDATE Posts SET hasImage = @hasImage, DateModified = @DateModified WHERE blogName = @blogName AND postID = @postID AND IFNULL(hasImage, 0) <> @hasImage";
|
||||
using SQLiteCommand updateCommand = new SQLiteCommand(updateSql, connection);
|
||||
updateCommand.Parameters.AddWithValue("@hasImage", hasImage ? 1 : 0);
|
||||
updateCommand.Parameters.AddWithValue("@DateModified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||||
@@ -623,16 +627,17 @@ namespace URLNotesGrabberCORE
|
||||
}
|
||||
}
|
||||
|
||||
// Only update HasBeenOutput and DateAdded if a new post was inserted
|
||||
// Only reopen the blog for output if a new post was inserted. DateAdded records
|
||||
// when the blog first entered the registry and is never rewritten here -- a new
|
||||
// post is not a new blog.
|
||||
if (rowsInserted == 1)
|
||||
{
|
||||
try
|
||||
{
|
||||
string updateBlogSql = "UPDATE Blogs SET HasBeenOutput = 0, DateAdded = @DateAdded, DateModified = @DateModified WHERE BlogName = @BlogName";
|
||||
string updateBlogSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName AND (HasBeenOutput IS NULL OR HasBeenOutput <> 0)";
|
||||
using (var updateBlogCommand = new SQLiteCommand(updateBlogSql, connection))
|
||||
{
|
||||
updateBlogCommand.Parameters.AddWithValue("@BlogName", blogName);
|
||||
updateBlogCommand.Parameters.AddWithValue("@DateAdded", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||||
updateBlogCommand.Parameters.AddWithValue("@DateModified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||||
updateBlogCommand.ExecuteNonQuery();
|
||||
}
|
||||
@@ -737,7 +742,9 @@ namespace URLNotesGrabberCORE
|
||||
{
|
||||
try
|
||||
{
|
||||
string updateSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName";
|
||||
// HasBeenOutput IS NULL still counts as a change: the selection queries
|
||||
// test HasBeenOutput = 0, which a NULL would never match.
|
||||
string updateSql = "UPDATE Blogs SET HasBeenOutput = 0, DateModified = @DateModified WHERE BlogName = @BlogName AND (HasBeenOutput IS NULL OR HasBeenOutput <> 0)";
|
||||
using (var updateCommand = new SQLiteCommand(updateSql, connection2))
|
||||
{
|
||||
updateCommand.Parameters.AddWithValue("@BlogName", noteBlogName);
|
||||
@@ -1356,7 +1363,10 @@ namespace URLNotesGrabberCORE
|
||||
connection.Open();
|
||||
|
||||
//string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered WHERE BlogName = @BlogName AND PostID = @PostID";
|
||||
string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered, DateModified = @dateModified WHERE PostID = @PostID AND (IFNULL(HasNotesGathered, 0) <> 1 OR IFNULL(NotesGatheredDateTime, 0) <> @notesGathered)";
|
||||
// NotesGatheredDateTime is crawl bookkeeping -- it moves on every pass and says
|
||||
// nothing about the post itself, so only the HasNotesGathered flag flipping
|
||||
// counts as a modification. The CASE reads the pre-UPDATE value of the flag.
|
||||
string sql = "UPDATE Posts SET HasNotesGathered = 1, NotesGatheredDateTime = @notesGathered, DateModified = CASE WHEN IFNULL(HasNotesGathered, 0) <> 1 THEN @dateModified ELSE DateModified END WHERE PostID = @PostID AND (IFNULL(HasNotesGathered, 0) <> 1 OR IFNULL(NotesGatheredDateTime, 0) <> @notesGathered)";
|
||||
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
|
||||
{
|
||||
command.Parameters.AddWithValue("@notesGathered", DateTimeOffset.UtcNow.ToUnixTimeSeconds());
|
||||
@@ -1558,49 +1568,60 @@ namespace URLNotesGrabberCORE
|
||||
{
|
||||
if (ownsConnection) connection.Open();
|
||||
|
||||
// "." is TraverseDirectory/ReblogRecord's sentinel for "this field had no
|
||||
// matching line in this particular export file" -- not an empty value. A blog
|
||||
// with two export files in different formats (e.g. an "_2" duplicate folder, or
|
||||
// a Tumblr export whose field set changed over time) sends one record with a real
|
||||
// Title and another with Title = "." for the same PostID, and re-importing both
|
||||
// on every run must not let the "not supplied" record blank out what the other
|
||||
// one has. Every content field below is CASE-guarded the same way RootBlogName/
|
||||
// RootURL already were, and the change-detection ignores "." too so a "."-only
|
||||
// difference doesn't fire the UPDATE (and bump DateModified) on its own. Only "."
|
||||
// is treated as the sentinel -- an explicit empty string from a real field still
|
||||
// overwrites, same as before.
|
||||
string sql = "UPDATE Posts SET ";
|
||||
sql += "postDate = @postDate, ";
|
||||
sql += "reblogURL = @reblogURL, ";
|
||||
sql += "postURL = @postURL, ";
|
||||
sql += "slug = @slug, ";
|
||||
sql += "reblogKey = @reblogKey, ";
|
||||
sql += "reblogName = @reblogName, ";
|
||||
sql += "summary = @summary, ";
|
||||
sql += "quote = @quote, ";
|
||||
sql += "body = @body, ";
|
||||
sql += "tags = @tags, ";
|
||||
sql += "link = @link, ";
|
||||
sql += "photoURL = @photoURL, ";
|
||||
sql += "photoCaption = @photoCaption, ";
|
||||
sql += "downloadedFiles = @downloadedFiles, ";
|
||||
sql += "audioCaption = @audioCaption, ";
|
||||
sql += "question = @question, ";
|
||||
sql += "answer = @answer, ";
|
||||
sql += "title = @title, ";
|
||||
sql += "postDate = CASE WHEN @postDate = '.' THEN postDate ELSE @postDate END, ";
|
||||
sql += "reblogURL = CASE WHEN @reblogURL = '.' THEN reblogURL ELSE @reblogURL END, ";
|
||||
sql += "postURL = CASE WHEN @postURL = '.' THEN postURL ELSE @postURL END, ";
|
||||
sql += "slug = CASE WHEN @slug = '.' THEN slug ELSE @slug END, ";
|
||||
sql += "reblogKey = CASE WHEN @reblogKey = '.' THEN reblogKey ELSE @reblogKey END, ";
|
||||
sql += "reblogName = CASE WHEN @reblogName = '.' THEN reblogName ELSE @reblogName END, ";
|
||||
sql += "summary = CASE WHEN @summary = '.' THEN summary ELSE @summary END, ";
|
||||
sql += "quote = CASE WHEN @quote = '.' THEN quote ELSE @quote END, ";
|
||||
sql += "body = CASE WHEN @body = '.' THEN body ELSE @body END, ";
|
||||
sql += "tags = CASE WHEN @tags = '.' THEN tags ELSE @tags END, ";
|
||||
sql += "link = CASE WHEN @link = '.' THEN link ELSE @link END, ";
|
||||
sql += "photoURL = CASE WHEN @photoURL = '.' THEN photoURL ELSE @photoURL END, ";
|
||||
sql += "photoCaption = CASE WHEN @photoCaption = '.' THEN photoCaption ELSE @photoCaption END, ";
|
||||
sql += "downloadedFiles = CASE WHEN @downloadedFiles = '.' THEN downloadedFiles ELSE @downloadedFiles END, ";
|
||||
sql += "audioCaption = CASE WHEN @audioCaption = '.' THEN audioCaption ELSE @audioCaption END, ";
|
||||
sql += "question = CASE WHEN @question = '.' THEN question ELSE @question END, ";
|
||||
sql += "answer = CASE WHEN @answer = '.' THEN answer ELSE @answer END, ";
|
||||
sql += "title = CASE WHEN @title = '.' THEN title ELSE @title END, ";
|
||||
sql += "DateModified = @dateModified, ";
|
||||
sql += "RootBlogName = CASE WHEN @rootBlogName IS NULL OR @rootBlogName = '' OR @rootBlogName = '.' THEN RootBlogName ELSE @rootBlogName END, ";
|
||||
sql += "RootURL = CASE WHEN @rootURL IS NULL OR @rootURL = '' OR @rootURL = '.' THEN RootURL ELSE @rootURL END, ";
|
||||
sql += "hasImage = @hasImage, ";
|
||||
sql += "ByLikes = MAX(IFNULL(ByLikes, 0), @byLikes) ";
|
||||
sql += " WHERE BlogName = @BlogName AND PostID = @PostID AND (";
|
||||
sql += "IFNULL(postDate, '') <> @postDate OR ";
|
||||
sql += "IFNULL(reblogURL, '') <> @reblogURL OR ";
|
||||
sql += "IFNULL(postURL, '') <> @postURL OR ";
|
||||
sql += "IFNULL(slug, '') <> @slug OR ";
|
||||
sql += "IFNULL(reblogKey, '') <> @reblogKey OR ";
|
||||
sql += "IFNULL(reblogName, '') <> @reblogName OR ";
|
||||
sql += "IFNULL(summary, '') <> @summary OR ";
|
||||
sql += "IFNULL(quote, '') <> @quote OR ";
|
||||
sql += "IFNULL(body, '') <> @body OR ";
|
||||
sql += "IFNULL(tags, '') <> @tags OR ";
|
||||
sql += "IFNULL(link, '') <> @link OR ";
|
||||
sql += "IFNULL(photoURL, '') <> @photoURL OR ";
|
||||
sql += "IFNULL(photoCaption, '') <> @photoCaption OR ";
|
||||
sql += "IFNULL(downloadedFiles, '') <> @downloadedFiles OR ";
|
||||
sql += "IFNULL(audioCaption, '') <> @audioCaption OR ";
|
||||
sql += "IFNULL(question, '') <> @question OR ";
|
||||
sql += "IFNULL(answer, '') <> @answer OR ";
|
||||
sql += "IFNULL(title, '') <> @title OR ";
|
||||
sql += "(@postDate <> '.' AND IFNULL(postDate, '') <> @postDate) OR ";
|
||||
sql += "(@reblogURL <> '.' AND IFNULL(reblogURL, '') <> @reblogURL) OR ";
|
||||
sql += "(@postURL <> '.' AND IFNULL(postURL, '') <> @postURL) OR ";
|
||||
sql += "(@slug <> '.' AND IFNULL(slug, '') <> @slug) OR ";
|
||||
sql += "(@reblogKey <> '.' AND IFNULL(reblogKey, '') <> @reblogKey) OR ";
|
||||
sql += "(@reblogName <> '.' AND IFNULL(reblogName, '') <> @reblogName) OR ";
|
||||
sql += "(@summary <> '.' AND IFNULL(summary, '') <> @summary) OR ";
|
||||
sql += "(@quote <> '.' AND IFNULL(quote, '') <> @quote) OR ";
|
||||
sql += "(@body <> '.' AND IFNULL(body, '') <> @body) OR ";
|
||||
sql += "(@tags <> '.' AND IFNULL(tags, '') <> @tags) OR ";
|
||||
sql += "(@link <> '.' AND IFNULL(link, '') <> @link) OR ";
|
||||
sql += "(@photoURL <> '.' AND IFNULL(photoURL, '') <> @photoURL) OR ";
|
||||
sql += "(@photoCaption <> '.' AND IFNULL(photoCaption, '') <> @photoCaption) OR ";
|
||||
sql += "(@downloadedFiles <> '.' AND IFNULL(downloadedFiles, '') <> @downloadedFiles) OR ";
|
||||
sql += "(@audioCaption <> '.' AND IFNULL(audioCaption, '') <> @audioCaption) OR ";
|
||||
sql += "(@question <> '.' AND IFNULL(question, '') <> @question) OR ";
|
||||
sql += "(@answer <> '.' AND IFNULL(answer, '') <> @answer) OR ";
|
||||
sql += "(@title <> '.' AND IFNULL(title, '') <> @title) OR ";
|
||||
sql += "IFNULL(hasImage, 0) <> @hasImage OR ";
|
||||
sql += "(@byLikes = 1 AND IFNULL(ByLikes, 0) = 0) OR ";
|
||||
sql += "((@rootBlogName IS NOT NULL AND @rootBlogName <> '' AND @rootBlogName <> '.') AND IFNULL(RootBlogName, '') <> @rootBlogName) OR ";
|
||||
@@ -1713,7 +1734,8 @@ namespace URLNotesGrabberCORE
|
||||
string sql = @"UPDATE Blogs
|
||||
SET LikesNewestTimestamp = MAX(COALESCE(LikesNewestTimestamp, 0), @newest),
|
||||
DateModified = @modified
|
||||
WHERE BlogName = @name";
|
||||
WHERE BlogName = @name
|
||||
AND COALESCE(LikesNewestTimestamp, 0) < @newest";
|
||||
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
|
||||
{
|
||||
command.Parameters.AddWithValue("@newest", newestTimestamp);
|
||||
@@ -1809,7 +1831,7 @@ namespace URLNotesGrabberCORE
|
||||
//string sql = "UPDATE Notes SET replyText = @replyText WHERE rootBlogName = @rootBlogName AND PostID = @PostID AND noteBlogName = @noteBlogName AND TimeStamp = @TimeStamp AND Type = 'reply'";
|
||||
// Match on (noteBlogName, TimeStamp ±5s) only - a reply by a given blog at a given timestamp is the same reply across the original post and every reblog of it, so this fans out across reblog chains in one shot. Tolerance absorbs the ~1s drift between what -collect stored and what mode=conversation returns now.
|
||||
// Only fan out to rows that match the SELECT criteria in GetRepliesWithFilledText (NULL/empty/legacy-'.'). Never overwrite '?' (confirmed-empty) or already-fetched text.
|
||||
string sql = "UPDATE Notes SET replyText = @replyText, DateModified = @dateModified WHERE noteBlogName = @noteBlogName AND ABS(TimeStamp - @TimeStamp) <= 5 AND Type = 'reply' AND (replyText IS NULL OR replyText = '' OR replyText = '.')";
|
||||
string sql = "UPDATE Notes SET replyText = @replyText, DateModified = @dateModified WHERE noteBlogName = @noteBlogName AND ABS(TimeStamp - @TimeStamp) <= 5 AND Type = 'reply' AND (replyText IS NULL OR replyText = '' OR replyText = '.') AND (replyText IS NULL OR replyText <> @replyText)";
|
||||
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
|
||||
{
|
||||
command.Parameters.AddWithValue("@replyText", replyText ?? "?");
|
||||
@@ -2044,29 +2066,65 @@ namespace URLNotesGrabberCORE
|
||||
|
||||
if (rowsInserted == 0)
|
||||
{
|
||||
// NULL is this function's sentinel for "this file's record had no line for
|
||||
// that field" (IngestMode's G(key) misses return null; LegacyPostsDbImporter
|
||||
// passes null straight from a NULL source column) -- it does not mean "clear
|
||||
// this field". --ingest's entire reason to exist is reconciling multiple
|
||||
// export files for the same (BlogName, PostID) -- IngestMode normalizes a
|
||||
// "_2"-suffixed duplicate folder onto the same blog name specifically so a
|
||||
// second, differently-formatted file for a post it already has gets merged in.
|
||||
// Files are walked in filesystem enumeration order, not sorted, so which
|
||||
// file's UpsertPostFromTextFile call runs last for a given PostID is
|
||||
// effectively arbitrary. An unconditional SET here would let whichever file
|
||||
// processed last silently null out every column its own record didn't carry,
|
||||
// erasing real content the other file had -- the opposite of "clean up". Each
|
||||
// column is CASE-guarded to keep the existing value when this call's parameter
|
||||
// is NULL, and the change-detection ignores a NULL-vs-real mismatch the same
|
||||
// way, so a partial record converges into the row instead of overwriting it.
|
||||
string updateSql = @"UPDATE Posts SET
|
||||
reblogURL = @reblogURL,
|
||||
PostDate = @PostDate,
|
||||
PostURL = @PostURL,
|
||||
Slug = @Slug,
|
||||
ReblogKey = @ReblogKey,
|
||||
ReblogName = @ReblogName,
|
||||
Summary = @Summary,
|
||||
Quote = @Quote,
|
||||
Body = @Body,
|
||||
Tags = @Tags,
|
||||
Link = @Link,
|
||||
PhotoURL = @PhotoURL,
|
||||
PhotoCaption = @PhotoCaption,
|
||||
DownloadedFiles = @DownloadedFiles,
|
||||
AudioCaption = @AudioCaption,
|
||||
Question = @Question,
|
||||
Answer = @Answer,
|
||||
Title = @Title,
|
||||
PostType = @PostType,
|
||||
reblogURL = CASE WHEN @reblogURL IS NULL THEN reblogURL ELSE @reblogURL END,
|
||||
PostDate = CASE WHEN @PostDate IS NULL THEN PostDate ELSE @PostDate END,
|
||||
PostURL = CASE WHEN @PostURL IS NULL THEN PostURL ELSE @PostURL END,
|
||||
Slug = CASE WHEN @Slug IS NULL THEN Slug ELSE @Slug END,
|
||||
ReblogKey = CASE WHEN @ReblogKey IS NULL THEN ReblogKey ELSE @ReblogKey END,
|
||||
ReblogName = CASE WHEN @ReblogName IS NULL THEN ReblogName ELSE @ReblogName END,
|
||||
Summary = CASE WHEN @Summary IS NULL THEN Summary ELSE @Summary END,
|
||||
Quote = CASE WHEN @Quote IS NULL THEN Quote ELSE @Quote END,
|
||||
Body = CASE WHEN @Body IS NULL THEN Body ELSE @Body END,
|
||||
Tags = CASE WHEN @Tags IS NULL THEN Tags ELSE @Tags END,
|
||||
Link = CASE WHEN @Link IS NULL THEN Link ELSE @Link END,
|
||||
PhotoURL = CASE WHEN @PhotoURL IS NULL THEN PhotoURL ELSE @PhotoURL END,
|
||||
PhotoCaption = CASE WHEN @PhotoCaption IS NULL THEN PhotoCaption ELSE @PhotoCaption END,
|
||||
DownloadedFiles = CASE WHEN @DownloadedFiles IS NULL THEN DownloadedFiles ELSE @DownloadedFiles END,
|
||||
AudioCaption = CASE WHEN @AudioCaption IS NULL THEN AudioCaption ELSE @AudioCaption END,
|
||||
Question = CASE WHEN @Question IS NULL THEN Question ELSE @Question END,
|
||||
Answer = CASE WHEN @Answer IS NULL THEN Answer ELSE @Answer END,
|
||||
Title = CASE WHEN @Title IS NULL THEN Title ELSE @Title END,
|
||||
PostType = CASE WHEN @PostType IS NULL THEN PostType ELSE @PostType END,
|
||||
HasImage = @HasImage,
|
||||
DateModified = @DateModified
|
||||
WHERE BlogName = @BlogName AND PostID = @PostID";
|
||||
WHERE BlogName = @BlogName AND PostID = @PostID AND (
|
||||
(@reblogURL IS NOT NULL AND IFNULL(reblogURL, '') <> @reblogURL) OR
|
||||
(@PostDate IS NOT NULL AND IFNULL(PostDate, '') <> @PostDate) OR
|
||||
(@PostURL IS NOT NULL AND IFNULL(PostURL, '') <> @PostURL) OR
|
||||
(@Slug IS NOT NULL AND IFNULL(Slug, '') <> @Slug) OR
|
||||
(@ReblogKey IS NOT NULL AND IFNULL(ReblogKey, '') <> @ReblogKey) OR
|
||||
(@ReblogName IS NOT NULL AND IFNULL(ReblogName, '') <> @ReblogName) OR
|
||||
(@Summary IS NOT NULL AND IFNULL(Summary, '') <> @Summary) OR
|
||||
(@Quote IS NOT NULL AND IFNULL(Quote, '') <> @Quote) OR
|
||||
(@Body IS NOT NULL AND IFNULL(Body, '') <> @Body) OR
|
||||
(@Tags IS NOT NULL AND IFNULL(Tags, '') <> @Tags) OR
|
||||
(@Link IS NOT NULL AND IFNULL(Link, '') <> @Link) OR
|
||||
(@PhotoURL IS NOT NULL AND IFNULL(PhotoURL, '') <> @PhotoURL) OR
|
||||
(@PhotoCaption IS NOT NULL AND IFNULL(PhotoCaption, '') <> @PhotoCaption) OR
|
||||
(@DownloadedFiles IS NOT NULL AND IFNULL(DownloadedFiles, '') <> @DownloadedFiles) OR
|
||||
(@AudioCaption IS NOT NULL AND IFNULL(AudioCaption, '') <> @AudioCaption) OR
|
||||
(@Question IS NOT NULL AND IFNULL(Question, '') <> @Question) OR
|
||||
(@Answer IS NOT NULL AND IFNULL(Answer, '') <> @Answer) OR
|
||||
(@Title IS NOT NULL AND IFNULL(Title, '') <> @Title) OR
|
||||
(@PostType IS NOT NULL AND IFNULL(PostType, '') <> @PostType) OR
|
||||
IFNULL(HasImage, 0) <> @HasImage
|
||||
)";
|
||||
|
||||
using (var cmd = new SQLiteCommand(updateSql, connection))
|
||||
{
|
||||
@@ -2250,7 +2308,11 @@ namespace URLNotesGrabberCORE
|
||||
};
|
||||
}
|
||||
|
||||
public static void SetBlogTTFolderPath(string blogName, string? path, string? DBPath = null)
|
||||
// Returns true only when a row's TTFolderPath actually changed. A false means either
|
||||
// the row already held this value or no row matched the name -- callers must not
|
||||
// report a write they did not get, which is how a --updatepaths run could once print
|
||||
// "Updated <blog>" for every metadata file while leaving the column entirely NULL.
|
||||
public static bool SetBlogTTFolderPath(string blogName, string? path, string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
try { AddBlog(blogName, false, DBPath); } catch { }
|
||||
@@ -2258,24 +2320,40 @@ namespace URLNotesGrabberCORE
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
using var cmd = new SQLiteCommand(
|
||||
"UPDATE Blogs SET TTFolderPath = @path, DateModified = @modified WHERE BlogName = @name",
|
||||
"UPDATE Blogs SET TTFolderPath = @path, DateModified = @modified WHERE BlogName = @name AND IFNULL(TTFolderPath, '') <> IFNULL(@path, '')",
|
||||
connection);
|
||||
cmd.Parameters.AddWithValue("@path", (object?)path ?? DBNull.Value);
|
||||
cmd.Parameters.AddWithValue("@modified", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
|
||||
cmd.Parameters.AddWithValue("@name", blogName);
|
||||
cmd.ExecuteNonQuery();
|
||||
return cmd.ExecuteNonQuery() > 0;
|
||||
}
|
||||
|
||||
// Whether a Blogs row exists under this exact name. BlogName is a BINARY-collated
|
||||
// primary key, so a metadata filename that differs only in case is a different blog
|
||||
// as far as the UPDATE above is concerned -- worth telling the user about.
|
||||
public static bool BlogExists(string blogName, string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
using var cmd = new SQLiteCommand("SELECT 1 FROM Blogs WHERE BlogName = @name", connection);
|
||||
cmd.Parameters.AddWithValue("@name", blogName);
|
||||
return cmd.ExecuteScalar() != null;
|
||||
}
|
||||
|
||||
// Partial UPDATE used by the correct-apply path. fieldsToUpdate maps
|
||||
// ThreeTxtFileHelper prefix names ("Reblog URL", "Body", etc.) to non-empty
|
||||
// values pulled from a BAK file. Only those columns + DateModified are written;
|
||||
// other content columns and all engagement columns are left intact.
|
||||
// Returns true if a row was matched (and therefore updated).
|
||||
// Returns true if a row was actually changed. A row whose columns already hold
|
||||
// the incoming values is left alone, DateModified included.
|
||||
public static bool UpdatePostContentFields(string blogName, string postId, IDictionary<string, string> fieldsToUpdate, string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
|
||||
var setClauses = new List<string>();
|
||||
var changedClauses = new List<string>();
|
||||
var parameters = new List<(string Name, object Value)>();
|
||||
|
||||
foreach (var kvp in fieldsToUpdate)
|
||||
@@ -2285,6 +2363,7 @@ namespace URLNotesGrabberCORE
|
||||
if (column == null) continue;
|
||||
string paramName = "@p" + parameters.Count;
|
||||
setClauses.Add($"{column} = {paramName}");
|
||||
changedClauses.Add($"IFNULL({column}, '') <> {paramName}");
|
||||
parameters.Add((paramName, kvp.Value));
|
||||
}
|
||||
|
||||
@@ -2296,7 +2375,7 @@ namespace URLNotesGrabberCORE
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
|
||||
string sql = $"UPDATE Posts SET {string.Join(", ", setClauses)} WHERE BlogName = @BlogName AND PostID = @PostID";
|
||||
string sql = $"UPDATE Posts SET {string.Join(", ", setClauses)} WHERE BlogName = @BlogName AND PostID = @PostID AND ({string.Join(" OR ", changedClauses)})";
|
||||
using var cmd = new SQLiteCommand(sql, connection);
|
||||
foreach (var (name, value) in parameters)
|
||||
cmd.Parameters.AddWithValue(name, value);
|
||||
@@ -2334,24 +2413,48 @@ namespace URLNotesGrabberCORE
|
||||
};
|
||||
}
|
||||
|
||||
public static List<(string BlogName, string? TTFolderPath)> GetAllBlogsWithTTFolderPath(string? DBPath = null)
|
||||
// Export targets only: active blogs that actually carry a TTFolderPath.
|
||||
// Blogs is a 144k-row crawl registry and only the few hundred blogs downloaded
|
||||
// locally have a folder, so returning the unset rows made --output print a skip
|
||||
// line for every blog Tumblr has ever handed us.
|
||||
public static List<(string BlogName, string TTFolderPath)> GetAllBlogsWithTTFolderPath(string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
var results = new List<(string, string?)>();
|
||||
var results = new List<(string, string)>();
|
||||
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
using var cmd = new SQLiteCommand("SELECT BlogName, TTFolderPath FROM Blogs WHERE IsActive = 1", connection);
|
||||
using var cmd = new SQLiteCommand(
|
||||
"SELECT BlogName, TRIM(TTFolderPath) FROM Blogs WHERE IsActive = 1 AND IFNULL(TRIM(TTFolderPath), '') <> '' ORDER BY BlogName",
|
||||
connection);
|
||||
using var reader = cmd.ExecuteReader();
|
||||
while (reader.Read())
|
||||
{
|
||||
string name = reader.GetString(0);
|
||||
string? path = reader.IsDBNull(1) ? null : reader.GetString(1);
|
||||
results.Add((name, path));
|
||||
}
|
||||
results.Add((reader.GetString(0), reader.GetString(1)));
|
||||
return results;
|
||||
}
|
||||
|
||||
// Companion counts for the messages --output and --updatepaths print about coverage.
|
||||
public static int CountActiveBlogs(string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
using var cmd = new SQLiteCommand("SELECT COUNT(*) FROM Blogs WHERE IsActive = 1", connection);
|
||||
return Convert.ToInt32(cmd.ExecuteScalar());
|
||||
}
|
||||
|
||||
public static int CountBlogsWithTTFolderPath(string? DBPath = null)
|
||||
{
|
||||
DBPath ??= GetDefaultDbPath();
|
||||
|
||||
using var connection = new SQLiteConnection("Data Source=" + DBPath);
|
||||
connection.Open();
|
||||
using var cmd = new SQLiteCommand(
|
||||
"SELECT COUNT(*) FROM Blogs WHERE IFNULL(TRIM(TTFolderPath), '') <> ''", connection);
|
||||
return Convert.ToInt32(cmd.ExecuteScalar());
|
||||
}
|
||||
|
||||
private static string SafeStr(SQLiteDataReader reader, int ordinal)
|
||||
{
|
||||
return reader.IsDBNull(ordinal) ? string.Empty : reader.GetValue(ordinal)?.ToString() ?? string.Empty;
|
||||
|
||||
@@ -29,6 +29,8 @@ namespace URLNotesGrabberCORE
|
||||
Console.WriteLine($"Reading legacy posts.db: {legacyDbPath}");
|
||||
|
||||
int blogsCopied = 0;
|
||||
int blogPathsWritten = 0;
|
||||
int blogsWithoutPath = 0;
|
||||
int postsUpserted = 0;
|
||||
int errors = 0;
|
||||
|
||||
@@ -48,7 +50,13 @@ namespace URLNotesGrabberCORE
|
||||
if (string.IsNullOrWhiteSpace(blogName)) continue;
|
||||
try
|
||||
{
|
||||
DataAccess.SetBlogTTFolderPath(blogName, ttFolderPath);
|
||||
// A legacy row whose TTFolderPath was already NULL copies nothing.
|
||||
// Counting it as "copied" is what hid the fact that this import has
|
||||
// never populated a single path.
|
||||
if (string.IsNullOrWhiteSpace(ttFolderPath))
|
||||
blogsWithoutPath++;
|
||||
else if (DataAccess.SetBlogTTFolderPath(blogName, ttFolderPath.Trim()))
|
||||
blogPathsWritten++;
|
||||
blogsCopied++;
|
||||
}
|
||||
catch (Exception ex)
|
||||
@@ -58,7 +66,7 @@ namespace URLNotesGrabberCORE
|
||||
}
|
||||
}
|
||||
}
|
||||
Console.WriteLine($" Blogs copied: {blogsCopied}");
|
||||
Console.WriteLine($" Blogs seen: {blogsCopied}, TTFolderPath written: {blogPathsWritten}, legacy rows with no path: {blogsWithoutPath}");
|
||||
|
||||
// 2) Copy Posts
|
||||
try
|
||||
@@ -138,7 +146,8 @@ namespace URLNotesGrabberCORE
|
||||
}
|
||||
|
||||
Console.WriteLine($"\n========== Legacy import summary ==========");
|
||||
Console.WriteLine($"Blogs copied: {blogsCopied}");
|
||||
Console.WriteLine($"Blogs seen: {blogsCopied}");
|
||||
Console.WriteLine($"Paths written: {blogPathsWritten} (legacy rows with no path: {blogsWithoutPath})");
|
||||
Console.WriteLine($"Posts upserted: {postsUpserted}");
|
||||
Console.WriteLine($"Errors: {errors}");
|
||||
return errors == 0 ? 0 : 2;
|
||||
|
||||
@@ -8,28 +8,51 @@ namespace URLNotesGrabberCORE
|
||||
// field order). Reads from TL.db via DataAccess.GetAllPostsForBlog.
|
||||
public static class OutputMode
|
||||
{
|
||||
public static int Run(IConfiguration config)
|
||||
public static int Run(IConfiguration config, string[]? args = null)
|
||||
{
|
||||
DataAccess.EnsureTTFileHelperColumnsExist();
|
||||
|
||||
var blogs = DataAccess.GetAllBlogsWithTTFolderPath();
|
||||
Console.WriteLine($"Found {blogs.Count} blog(s) to process.");
|
||||
string dbPath = DataAccess.GetActiveDbPath();
|
||||
Console.WriteLine($"Database: {Path.GetFullPath(dbPath)}");
|
||||
|
||||
foreach (var (blogName, ttFolderPath) in blogs)
|
||||
if (!RefreshPaths(config, args ?? Array.Empty<string>()))
|
||||
return 1;
|
||||
|
||||
var blogs = DataAccess.GetAllBlogsWithTTFolderPath();
|
||||
int activeBlogs = DataAccess.CountActiveBlogs();
|
||||
Console.WriteLine($"{blogs.Count} of {activeBlogs} active blog(s) have a TTFolderPath.");
|
||||
|
||||
if (blogs.Count == 0)
|
||||
{
|
||||
Console.WriteLine($"\nNothing to export: no blog in {Path.GetFullPath(dbPath)} has a TTFolderPath.");
|
||||
Console.WriteLine("Point --output at a TumblThree root so it can populate them: --output <root>,");
|
||||
Console.WriteLine("or set appSettings:PathTTRoot so the refresh runs automatically.");
|
||||
return 1;
|
||||
}
|
||||
|
||||
int missingFolderCount = 0;
|
||||
int writtenCount = 0;
|
||||
|
||||
foreach (var (blogName, folder) in blogs)
|
||||
{
|
||||
Console.WriteLine($"\nProcessing blog: {blogName}");
|
||||
|
||||
if (string.IsNullOrWhiteSpace(ttFolderPath) || !Directory.Exists(ttFolderPath))
|
||||
// A stored path that this machine cannot see means the value was written on
|
||||
// another machine -- re-running --updatepaths locally is the fix, so say so
|
||||
// rather than lumping it in with "not set".
|
||||
if (!Directory.Exists(folder))
|
||||
{
|
||||
Console.WriteLine($" TTFolderPath does not exist or is not set. Skipping.");
|
||||
Console.WriteLine($" TTFolderPath folder not found: {folder}. Skipping.");
|
||||
missingFolderCount++;
|
||||
continue;
|
||||
}
|
||||
|
||||
Console.WriteLine($" TTFolderPath: {ttFolderPath}");
|
||||
Console.WriteLine($" TTFolderPath: {folder}");
|
||||
writtenCount++;
|
||||
|
||||
try
|
||||
{
|
||||
foreach (var bakFile in Directory.GetFiles(ttFolderPath, "*.bak"))
|
||||
foreach (var bakFile in Directory.GetFiles(folder, "*.bak"))
|
||||
File.Delete(bakFile);
|
||||
}
|
||||
catch (Exception ex)
|
||||
@@ -37,7 +60,7 @@ namespace URLNotesGrabberCORE
|
||||
Console.WriteLine($" Error deleting .bak files: {ex.Message}");
|
||||
}
|
||||
|
||||
RenameExistingTxtFilesToBak(ttFolderPath);
|
||||
RenameExistingTxtFilesToBak(folder);
|
||||
|
||||
var posts = DataAccess.GetAllPostsForBlog(blogName);
|
||||
Console.WriteLine($" Found {posts.Count} post(s) for this blog.");
|
||||
@@ -46,7 +69,7 @@ namespace URLNotesGrabberCORE
|
||||
foreach (var typeGroup in grouped)
|
||||
{
|
||||
string postType = typeGroup.Key ?? "Unknown";
|
||||
string outputFilePath = Path.Combine(ttFolderPath, $"{postType}.txt");
|
||||
string outputFilePath = Path.Combine(folder, $"{postType}.txt");
|
||||
var ordered = typeGroup.OrderBy(p => p.Date).ToList();
|
||||
Console.WriteLine($" Writing {ordered.Count} post(s) to {postType}.txt");
|
||||
|
||||
@@ -65,10 +88,57 @@ namespace URLNotesGrabberCORE
|
||||
}
|
||||
}
|
||||
|
||||
Console.WriteLine("\nOutput mode complete.");
|
||||
Console.WriteLine($"\nOutput mode complete. {writtenCount} blog(s) exported, {missingFolderCount} skipped for a missing folder.");
|
||||
|
||||
if (writtenCount == 0)
|
||||
Console.WriteLine("Every TTFolderPath points at a folder this machine cannot see. The paths were most likely written on another machine -- re-run --updatepaths <root> here so they match local drive letters.");
|
||||
|
||||
return 0;
|
||||
}
|
||||
|
||||
// Re-reads the TumblThree Index metadata into Blogs.TTFolderPath before exporting.
|
||||
// A TL.db synced between machines cannot hold one absolute path that is valid on
|
||||
// both, so the stored paths are only trustworthy on the machine that wrote them --
|
||||
// which makes this refresh part of a normal export rather than a separate chore.
|
||||
// Returns false only when the run should stop.
|
||||
private static bool RefreshPaths(IConfiguration config, string[] args)
|
||||
{
|
||||
var settings = config.GetSection("appSettings");
|
||||
|
||||
if (args.Any(a => string.Equals(a, "--norefresh", StringComparison.OrdinalIgnoreCase)))
|
||||
{
|
||||
Console.WriteLine("Path refresh skipped (--norefresh); exporting to whatever paths TL.db already holds.");
|
||||
return true;
|
||||
}
|
||||
|
||||
string? root = args.FirstOrDefault(a => !a.StartsWith("--", StringComparison.Ordinal))
|
||||
?? settings.GetValue<string>("PathTTRoot");
|
||||
|
||||
var result = UpdateBlogPathsRunner.Scan(root, verbose: false);
|
||||
|
||||
switch (result.Outcome)
|
||||
{
|
||||
case UpdateBlogPathsRunner.ScanOutcome.NoRootConfigured:
|
||||
Console.WriteLine("No TumblThree root configured (appSettings:PathTTRoot is empty and none was passed),");
|
||||
Console.WriteLine("so TTFolderPath was not refreshed. Pass one as --output <root> to refresh it.");
|
||||
return true;
|
||||
|
||||
case UpdateBlogPathsRunner.ScanOutcome.IndexFolderMissing:
|
||||
// Silently exporting stale paths here would defeat the point of folding
|
||||
// the refresh in, so a bad root is a hard stop.
|
||||
Console.WriteLine($"Index folder not found at: {result.IndexPath}");
|
||||
Console.WriteLine("Fix the root (or pass --norefresh to export the paths already in TL.db).");
|
||||
return false;
|
||||
|
||||
default:
|
||||
Console.WriteLine($"Refreshed paths from {result.IndexPath}: " +
|
||||
$"{result.MetadataFiles} metadata file(s), {result.Written} written, " +
|
||||
$"{result.Unchanged} already correct, {result.NoLocation} without a location, " +
|
||||
$"{result.NoMatchingRow} without a blog row, {result.Errors} error(s).");
|
||||
return true;
|
||||
}
|
||||
}
|
||||
|
||||
private static void RenameExistingTxtFilesToBak(string folderPath)
|
||||
{
|
||||
try
|
||||
|
||||
@@ -343,7 +343,7 @@ namespace URLNotesGrabberCORE
|
||||
break;
|
||||
|
||||
case "--output":
|
||||
exitCode = OutputMode.Run(config);
|
||||
exitCode = OutputMode.Run(config, args.Skip(1).ToArray());
|
||||
break;
|
||||
|
||||
case "--revert":
|
||||
@@ -439,7 +439,9 @@ namespace URLNotesGrabberCORE
|
||||
|
||||
Console.WriteLine("--ingest [blogname]\t Ingest Tumblr .txt exports from appSettings:PathTTRoot into TL.db (all blogs, or single blog if name given)");
|
||||
|
||||
Console.WriteLine("--output\t Export posts from TL.db back to .txt files in each blog's TTFolderPath");
|
||||
Console.WriteLine("--output [rootPath]\t Refresh Blogs.TTFolderPath from <root>\\Index (or appSettings:PathTTRoot), then export posts from TL.db back to .txt files in each blog's folder");
|
||||
|
||||
Console.WriteLine("--output --norefresh\t Export without refreshing TTFolderPath first");
|
||||
|
||||
Console.WriteLine("--revert [blogname]\t Recursively scan the PathInput tree and restore *.bak back to *.txt (current .txt saved as next-free .bkN); optional blogname filters by path substring");
|
||||
|
||||
|
||||
Binary file not shown.
@@ -121,22 +121,56 @@ Notable:
|
||||
|
||||
```sql
|
||||
CREATE TABLE "Notes" (
|
||||
"RootBlogName" TEXT,
|
||||
"PostID" INTEGER,
|
||||
"NoteBlogName" TEXT,
|
||||
"TimeStamp" INTEGER,
|
||||
"Type" TEXT,
|
||||
"replyText" TEXT DEFAULT '.',
|
||||
"DatetimeCrawled" TEXT DEFAULT '2/12/26 12am',
|
||||
"DateModified" TEXT,
|
||||
"DateCreated" TEXT,
|
||||
"RootBlogName" TEXT,
|
||||
"PostID" INTEGER,
|
||||
"NoteBlogName" TEXT,
|
||||
"TimeStamp" INTEGER,
|
||||
"Type" TEXT,
|
||||
"replyText" TEXT DEFAULT '.',
|
||||
"DatetimeCrawled" TEXT DEFAULT '2/12/26 12am',
|
||||
"DateModified" TEXT,
|
||||
"DateCreated" TEXT,
|
||||
IsActive INTEGER NOT NULL DEFAULT 1,
|
||||
PRIMARY KEY("RootBlogName","PostID","TimeStamp","Type","NoteBlogName")
|
||||
);
|
||||
) WITHOUT ROWID;
|
||||
|
||||
CREATE INDEX "Notes_idx_06e01ae3" ON "Notes" ("TimeStamp" DESC);
|
||||
CREATE INDEX "ix_NoteBlogName01" ON "Notes" ("NoteBlogName");
|
||||
CREATE INDEX "ix_NoteBlogName01" ON "Notes" ("NoteBlogName");
|
||||
```
|
||||
|
||||
**`WITHOUT ROWID`, since 2026-08-07.** The rows live in the primary key's b-tree
|
||||
rather than in a rowid table with a separate key index beside it. Nothing about the
|
||||
SQL surface changes — same columns, same types, same constraint — but two
|
||||
consequences are worth knowing before adding an index here:
|
||||
|
||||
- There is no `rowid` on this table. `SELECT rowid FROM Notes` is an error, and no
|
||||
code in any of the three apps relied on it.
|
||||
- A secondary index carries the whole five-column primary key as its row reference
|
||||
instead of a compact rowid, so indexes on this table are **expensive**.
|
||||
`ix_NoteBlogName01` costs 58 MB, up from 25 MB before the conversion. It earns
|
||||
that: Rolodex filters on `NoteBlogName` and the crawler joins on it.
|
||||
|
||||
**`Notes_idx_06e01ae3` on `TimeStamp DESC` was dropped at the same time.** It cost
|
||||
14 MB as a rowid index and would have cost 58 MB after the conversion. It was worth
|
||||
neither: the crawler's only `TimeStamp` filter (`>= 1535778000`) excludes 786 rows
|
||||
of 1.18M, Rolodex's default Notes sort carries a three-column tiebreaker that forces
|
||||
a full sort regardless, and the reply-matching `UPDATE` uses `ABS(TimeStamp - ?) <= 5`,
|
||||
which no index on `TimeStamp` can serve. The one path that got slower is Rolodex's
|
||||
Notes page with a date-range filter: 60 ms to 164 ms.
|
||||
|
||||
See `../shrink-db.sql` for the full rationale and the applied result.
|
||||
|
||||
**`DatetimeCrawled` is `NULL` on 1,148,077 rows, and that is the honest value.** Those
|
||||
rows previously stored the literal string `'2/12/26 12am'` — this column's own DDL
|
||||
default, written as a bulk backfill placeholder rather than as a crawl time. They were
|
||||
set to `NULL` on 2026-08-07, which is what consumers already displayed them as: the
|
||||
string parses as a date in neither format this schema writes.
|
||||
|
||||
Note the trap: **the `DEFAULT '2/12/26 12am'` clause is still in the DDL above.** Any
|
||||
`INSERT` that omits this column writes the placeholder straight back. The crawler names
|
||||
it explicitly on every insert, so nothing reintroduces it today, but a new writer that
|
||||
forgets to would — which is why consumers should keep treating an unparseable value here
|
||||
as "unknown" rather than assuming `NULL` is now the only such marker.
|
||||
|
||||
One row per engagement event. `TimeStamp` is **unix seconds** — unlike every date column
|
||||
elsewhere in the schema, which are text.
|
||||
|
||||
@@ -155,8 +189,11 @@ At 1.19M rows this is the table that dictates how the whole database has to be q
|
||||
- The only fast access paths are the primary key's leading columns (`RootBlogName`, then
|
||||
`PostID`) and `ix_NoteBlogName01` on `NoteBlogName`. "Notes received by a blog" and
|
||||
"notes given by a blog" are both cheap; almost nothing else is.
|
||||
- Ordering by anything but `TimeStamp` is a full sort of whatever the filters leave.
|
||||
- `replyText` is `'.'` on 1,174,706 rows — only `reply` notes carry real text.
|
||||
- **Every** ordering here is a full sort of whatever the filters leave, `TimeStamp`
|
||||
included. That was already true in practice of the default `TimeStamp` order, whose
|
||||
tiebreakers forced a sort even while `Notes_idx_06e01ae3` existed; since that index
|
||||
was dropped on 2026-08-07 it is true unconditionally. Filter first, then sort.
|
||||
- `replyText` is `'.'` on 1,167,464 rows — only `reply` notes carry real text.
|
||||
|
||||
### Referential integrity
|
||||
|
||||
@@ -178,7 +215,7 @@ consumer.
|
||||
|
||||
| Column | `'.'` rows |
|
||||
|---|--:|
|
||||
| `Notes.replyText` | 1,174,706 |
|
||||
| `Notes.replyText` | 1,167,464 |
|
||||
| `Posts.Title` | 13,144 |
|
||||
| `Posts.Body` | 172 |
|
||||
|
||||
|
||||
@@ -4,34 +4,64 @@ namespace URLNotesGrabberCORE
|
||||
{
|
||||
// Port of ThreeTxtFileHelper/UpdateBlogPaths.cs. Reads .tumblr / .tmblrpriv metadata
|
||||
// files from a root\Index folder and populates Blogs.TTFolderPath in TL.db.
|
||||
//
|
||||
// Scan() is the reusable engine: --updatepaths wraps it as a standalone command and
|
||||
// --output calls it as a refresh step, because a TL.db synced between machines cannot
|
||||
// hold one absolute path that is correct on both.
|
||||
public static class UpdateBlogPathsRunner
|
||||
{
|
||||
public static int Run(string rootPath)
|
||||
public enum ScanOutcome
|
||||
{
|
||||
Completed,
|
||||
NoRootConfigured,
|
||||
IndexFolderMissing
|
||||
}
|
||||
|
||||
public sealed class ScanResult
|
||||
{
|
||||
public ScanOutcome Outcome { get; init; }
|
||||
public string RootPath { get; init; } = string.Empty;
|
||||
public string IndexPath { get; init; } = string.Empty;
|
||||
public int MetadataFiles { get; init; }
|
||||
public int Written { get; init; }
|
||||
public int Unchanged { get; init; }
|
||||
public int NoLocation { get; init; }
|
||||
public int NoMatchingRow { get; init; }
|
||||
public int Errors { get; init; }
|
||||
}
|
||||
|
||||
// verbose: log a line per metadata file. --updatepaths wants that detail; --output
|
||||
// only wants the counts, since a few hundred lines before the export would bury it.
|
||||
public static ScanResult Scan(string? rootPath, bool verbose)
|
||||
{
|
||||
if (string.IsNullOrWhiteSpace(rootPath))
|
||||
{
|
||||
Console.WriteLine("UpdateBlogPaths: rootPath is required.");
|
||||
return 1;
|
||||
}
|
||||
return new ScanResult { Outcome = ScanOutcome.NoRootConfigured };
|
||||
|
||||
DataAccess.EnsureTTFileHelperColumnsExist();
|
||||
|
||||
string indexPath = Path.Combine(rootPath, "Index");
|
||||
if (!Directory.Exists(indexPath))
|
||||
{
|
||||
Console.WriteLine($"Index folder not found at: {indexPath}");
|
||||
return 1;
|
||||
return new ScanResult
|
||||
{
|
||||
Outcome = ScanOutcome.IndexFolderMissing,
|
||||
RootPath = rootPath,
|
||||
IndexPath = indexPath
|
||||
};
|
||||
}
|
||||
|
||||
Console.WriteLine($"Scanning Index folder: {indexPath}");
|
||||
|
||||
var blogFiles = Directory.GetFiles(indexPath, "*.tumblr")
|
||||
.Concat(Directory.GetFiles(indexPath, "*.tmblrpriv"))
|
||||
.ToList();
|
||||
|
||||
Console.WriteLine($"Found {blogFiles.Count} blog metadata files");
|
||||
if (verbose)
|
||||
Console.WriteLine($"Found {blogFiles.Count} blog metadata files");
|
||||
|
||||
int updatedCount = 0;
|
||||
int unchangedCount = 0;
|
||||
int noLocationCount = 0;
|
||||
int noRowCount = 0;
|
||||
int errorCount = 0;
|
||||
|
||||
foreach (var blogFile in blogFiles)
|
||||
{
|
||||
@@ -44,27 +74,91 @@ namespace URLNotesGrabberCORE
|
||||
|
||||
if (root.TryGetProperty("FileDownloadLocation", out JsonElement locationElement))
|
||||
{
|
||||
string? fileDownloadLocation = locationElement.GetString();
|
||||
string? fileDownloadLocation = locationElement.GetString()?.Trim();
|
||||
if (!string.IsNullOrWhiteSpace(fileDownloadLocation))
|
||||
{
|
||||
DataAccess.SetBlogTTFolderPath(blogName, fileDownloadLocation);
|
||||
updatedCount++;
|
||||
Console.WriteLine($"Updated {blogName}: {fileDownloadLocation}");
|
||||
// Report the database's answer, not the fact that the file parsed.
|
||||
if (DataAccess.SetBlogTTFolderPath(blogName, fileDownloadLocation))
|
||||
{
|
||||
updatedCount++;
|
||||
if (verbose)
|
||||
Console.WriteLine($"Updated {blogName}: {fileDownloadLocation}");
|
||||
}
|
||||
else if (DataAccess.BlogExists(blogName))
|
||||
{
|
||||
unchangedCount++;
|
||||
}
|
||||
else
|
||||
{
|
||||
noRowCount++;
|
||||
Console.WriteLine($"No Blogs row named '{blogName}' -- path not stored (name may differ in case)");
|
||||
}
|
||||
}
|
||||
else
|
||||
{
|
||||
noLocationCount++;
|
||||
if (verbose)
|
||||
Console.WriteLine($"Empty FileDownloadLocation in {blogFile}");
|
||||
}
|
||||
}
|
||||
else
|
||||
{
|
||||
Console.WriteLine($"No FileDownloadLocation found in {blogFile}");
|
||||
noLocationCount++;
|
||||
if (verbose)
|
||||
Console.WriteLine($"No FileDownloadLocation found in {blogFile}");
|
||||
}
|
||||
}
|
||||
catch (Exception ex)
|
||||
{
|
||||
errorCount++;
|
||||
Console.WriteLine($"Error processing {blogFile}: {ex.Message}");
|
||||
}
|
||||
}
|
||||
|
||||
Console.WriteLine($"\nUpdated {updatedCount} blogs with TTFolderPath");
|
||||
return 0;
|
||||
return new ScanResult
|
||||
{
|
||||
Outcome = ScanOutcome.Completed,
|
||||
RootPath = rootPath,
|
||||
IndexPath = indexPath,
|
||||
MetadataFiles = blogFiles.Count,
|
||||
Written = updatedCount,
|
||||
Unchanged = unchangedCount,
|
||||
NoLocation = noLocationCount,
|
||||
NoMatchingRow = noRowCount,
|
||||
Errors = errorCount
|
||||
};
|
||||
}
|
||||
|
||||
public static int Run(string rootPath)
|
||||
{
|
||||
if (string.IsNullOrWhiteSpace(rootPath))
|
||||
{
|
||||
Console.WriteLine("UpdateBlogPaths: rootPath is required.");
|
||||
return 1;
|
||||
}
|
||||
|
||||
string indexPath = Path.Combine(rootPath, "Index");
|
||||
Console.WriteLine($"Scanning Index folder: {indexPath}");
|
||||
|
||||
var result = Scan(rootPath, verbose: true);
|
||||
|
||||
if (result.Outcome == ScanOutcome.IndexFolderMissing)
|
||||
{
|
||||
Console.WriteLine($"Index folder not found at: {result.IndexPath}");
|
||||
return 1;
|
||||
}
|
||||
|
||||
Console.WriteLine($"\n========== UpdateBlogPaths summary ==========");
|
||||
Console.WriteLine($"Metadata files: {result.MetadataFiles}");
|
||||
Console.WriteLine($"TTFolderPath written: {result.Written}");
|
||||
Console.WriteLine($"Already correct: {result.Unchanged}");
|
||||
Console.WriteLine($"No FileDownloadLocation: {result.NoLocation}");
|
||||
Console.WriteLine($"No matching blog row: {result.NoMatchingRow}");
|
||||
Console.WriteLine($"Errors: {result.Errors}");
|
||||
|
||||
Console.WriteLine($"\nBlogs now holding a TTFolderPath: {DataAccess.CountBlogsWithTTFolderPath()}");
|
||||
|
||||
return result.Errors == 0 ? 0 : 2;
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
+159
@@ -0,0 +1,159 @@
|
||||
-- shrink-db.sql
|
||||
-- Reduces TL.db from ~267 MB to ~207 MB (-22%) with no application changes,
|
||||
-- and no visible change in any of the three apps that touch this file.
|
||||
--
|
||||
-- The three consumers, and what each one uses:
|
||||
-- URLNotesGrabberCORE System.Data.SQLite 1.0.119 writes Notes, Posts, Blogs
|
||||
-- Rolodex (web) Microsoft.Data.Sqlite 10.0 reads all three; soft-deletes via IsActive
|
||||
-- TumblThree System.Data.SQLite.Core 1.0.119
|
||||
-- one statement only, ManagerController.cs:972 --
|
||||
-- "UPDATE Blogs SET IsActive = 0, DateModified = @DateModified
|
||||
-- WHERE BlogName = @BlogName"
|
||||
-- Nothing below touches the Blogs table, so TumblThree is
|
||||
-- unaffected. (Its GlobalDatabaseService talks to TumblThree's
|
||||
-- own separate FileEntries/BlogFiles database, not this file.)
|
||||
--
|
||||
-- WITHOUT ROWID needs SQLite >= 3.8.2 (Dec 2013). All three providers above are
|
||||
-- 2024-25 builds, an order of magnitude newer, so STEP 2 is readable by all of them.
|
||||
--
|
||||
-- Every figure below was measured on a copy of the live 267 MB file, and the
|
||||
-- result was checked against all three apps' access patterns:
|
||||
-- PRAGMA integrity_check ....... ok
|
||||
-- row counts ................... Notes 1182333, Posts 22468, Blogs 188620 (unchanged)
|
||||
-- Rolodex soft-delete UPDATE ... works
|
||||
-- Rolodex NoteBlogName filter .. still uses ix_NoteBlogName01
|
||||
-- crawler INSERT OR IGNORE ..... still dedupes (0 dupes admitted)
|
||||
-- TumblThree's UPDATE Blogs ..... untouched -- Blogs is not modified by this script
|
||||
--
|
||||
-- HOW TO RUN (DB Browser for SQLite):
|
||||
-- 1. Stop ALL THREE apps: the crawler, the Rolodex web app, and TumblThree.
|
||||
-- Rolodex holds the file open and checkpoints the WAL, so it must be down,
|
||||
-- not just idle. TumblThree only opens the file for an instant when you
|
||||
-- delete a blog, but it can also launch the crawler on its own
|
||||
-- (UrlNotesGrabberService) -- so close it rather than merely avoiding it.
|
||||
-- 2. Back up TL.db (copy the 267 MB file somewhere safe).
|
||||
-- 3. Open TL.db, go to Execute SQL, paste STEP 1-3, run.
|
||||
-- 4. Click "Write Changes".
|
||||
-- 5. Run Tools > Compact Database. This is VACUUM; it will not run from the
|
||||
-- Execute SQL tab because DB Browser keeps a transaction open there.
|
||||
-- NOTHING SHRINKS ON DISK UNTIL THIS FINISHES.
|
||||
--
|
||||
-- Expected: steps 1-3 a couple of minutes, Compact a couple more.
|
||||
-- Free disk needed during Compact: ~270 MB for the temp copy.
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- STEP 1: drop the TimeStamp index (required by STEP 2, not optional)
|
||||
--------------------------------------------------------------------------
|
||||
-- Measured cost/benefit:
|
||||
--
|
||||
-- * Rolodex's DEFAULT Notes view does not use it. Its sort carries the
|
||||
-- tiebreaker "RootBlogName, PostID, NoteBlogName", which forces a full sort
|
||||
-- regardless -- the query plan is byte-identical with and without the index.
|
||||
-- Sorting.cs:138 already assumes as much, and is right in practice.
|
||||
-- * The crawler's collect query (DataAccess.cs:1183) filters
|
||||
-- TimeStamp >= 1535778000, which excludes 786 of 1,182,333 rows (0.07%).
|
||||
-- A full index scan wearing a disguise. Same measured time without it.
|
||||
-- * The reply-matching UPDATE uses ABS(TimeStamp - ?) <= 5, which can never
|
||||
-- use an index on TimeStamp.
|
||||
-- * It DOES help exactly one path: Rolodex's Notes page with a date-range
|
||||
-- filter applied. 60 ms -> 164 ms. That is the whole of what is lost.
|
||||
--
|
||||
-- And it must go, because after STEP 2 it stops being cheap. A secondary index
|
||||
-- on a WITHOUT ROWID table carries the full 5-column primary key instead of a
|
||||
-- compact rowid, so this index grows 14 MB -> 58 MB. Keeping it lands the file
|
||||
-- at 265 MB instead of 207 MB -- i.e. it cancels the entire exercise to save
|
||||
-- 100 ms on one filtered view.
|
||||
|
||||
DROP INDEX IF EXISTS Notes_idx_06e01ae3;
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- STEP 2: rebuild Notes as WITHOUT ROWID (-32 MB)
|
||||
--------------------------------------------------------------------------
|
||||
-- Notes has a 5-column composite primary key. In a rowid table SQLite stores
|
||||
-- that key twice: once in the table, once in sqlite_autoindex_Notes_1 (62 MB).
|
||||
-- WITHOUT ROWID stores the rows *in* the key's b-tree, so the copy disappears.
|
||||
--
|
||||
-- ix_NoteBlogName01 grows 25 -> 58 MB for the reason described above. Net -32 MB.
|
||||
-- It is kept because Rolodex filters on NoteBlogName and the crawler joins on it.
|
||||
--
|
||||
-- Safe: neither codebase references rowid on Notes (grep across both trees,
|
||||
-- zero matches). IsActive keeps its exact current declaration, which is what
|
||||
-- Rolodex's ActiveFlag predicate reads.
|
||||
|
||||
PRAGMA foreign_keys = off;
|
||||
|
||||
CREATE TABLE Notes_new (
|
||||
"RootBlogName" TEXT,
|
||||
"PostID" INTEGER,
|
||||
"NoteBlogName" TEXT,
|
||||
"TimeStamp" INTEGER,
|
||||
"Type" TEXT,
|
||||
"replyText" TEXT DEFAULT '.',
|
||||
"DatetimeCrawled" TEXT DEFAULT '2/12/26 12am',
|
||||
"DateModified" TEXT,
|
||||
"DateCreated" TEXT,
|
||||
IsActive INTEGER NOT NULL DEFAULT 1,
|
||||
PRIMARY KEY("RootBlogName","PostID","TimeStamp","Type","NoteBlogName")
|
||||
) WITHOUT ROWID;
|
||||
|
||||
INSERT INTO Notes_new
|
||||
SELECT RootBlogName, PostID, NoteBlogName, TimeStamp, Type,
|
||||
replyText, DatetimeCrawled, DateModified, DateCreated, IsActive
|
||||
FROM Notes;
|
||||
|
||||
DROP TABLE Notes;
|
||||
ALTER TABLE Notes_new RENAME TO Notes;
|
||||
|
||||
CREATE INDEX "ix_NoteBlogName01" ON "Notes" ("NoteBlogName");
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- STEP 3: clear the DatetimeCrawled placeholder (-13 MB)
|
||||
--------------------------------------------------------------------------
|
||||
-- 1,148,077 of 1,182,333 rows hold the literal DDL default '2/12/26 12am' --
|
||||
-- a backfill placeholder, not a crawl time. SQLite stores all 12 bytes of it
|
||||
-- on every one of those rows.
|
||||
--
|
||||
-- This is UI-NEUTRAL in Rolodex, which is why it is safe despite Rolodex
|
||||
-- displaying the column. Rolodex reads and sorts it through DateSql.Sortable
|
||||
-- (DateRange.cs:67), whose CASE matches '____-__-__%' or the 8-character
|
||||
-- '__/__/__'. The 12-character '2/12/26 12am' matches neither, so Sortable
|
||||
-- already returns NULL for these rows and the page already renders an em dash
|
||||
-- and sorts them to the bottom. RolodexRepository.cs:957-963 documents exactly
|
||||
-- this. Writing a real NULL changes the bytes on disk, not the screen.
|
||||
--
|
||||
-- The crawler never reads the column back -- it only writes it on INSERT
|
||||
-- (DataAccess.cs:713, 721).
|
||||
|
||||
UPDATE Notes SET DatetimeCrawled = NULL WHERE DatetimeCrawled = '2/12/26 12am';
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- DELIBERATELY NOT DONE: nulling Notes.DateCreated
|
||||
--------------------------------------------------------------------------
|
||||
-- An earlier draft of this script also cleared DateCreated = '2026-04-13'
|
||||
-- (a further -12 MB). Do not. Unlike DatetimeCrawled, that value DOES match
|
||||
-- Sortable's '____-__-__%' branch, so Rolodex renders it as a real date in the
|
||||
-- "Created" column on the Notes page and Post detail, and sorts by it. Nulling
|
||||
-- it would turn visible dates into em dashes and move rows in the sort order.
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- STEP 4: Write Changes, then Tools > Compact Database
|
||||
--------------------------------------------------------------------------
|
||||
-- Nothing above reclaims disk until VACUUM runs. From the sqlite3 CLI instead:
|
||||
-- sqlite3 TL.db "VACUUM;"
|
||||
|
||||
--------------------------------------------------------------------------
|
||||
-- VERIFY (run after compacting; file should be ~207 MB)
|
||||
--------------------------------------------------------------------------
|
||||
-- PRAGMA integrity_check;
|
||||
--
|
||||
-- SELECT 'Notes' t, COUNT(*) n FROM Notes
|
||||
-- UNION ALL SELECT 'Posts', COUNT(*) FROM Posts
|
||||
-- UNION ALL SELECT 'Blogs', COUNT(*) FROM Blogs;
|
||||
-- -- expect 1182333 / 22468 / 188620, unchanged
|
||||
--
|
||||
-- SELECT name, SUM(pgsize)/1024/1024 AS mb
|
||||
-- FROM dbstat GROUP BY name ORDER BY SUM(pgsize) DESC;
|
||||
-- -- expect Notes 78, ix_NoteBlogName01 58, Posts 50, Blogs 14
|
||||
--
|
||||
-- APPLIED 2026-08-07. Actual result: 267.32 MB -> 207.17 MB, integrity_check ok,
|
||||
-- row counts unchanged, journal_mode still wal. VACUUM took 6 seconds.
|
||||
Reference in New Issue
Block a user