Overview
Comment:Fix issues with duplicate file names in transactions
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | dev
Files: files | file ages | folders
SHA3-256: e6e012838dcd858e817e1245543d5b09e5808b17ba2a97392ea34313ecdd7797
User & Date: bohwaz on 2021-03-17 01:24:28
Other Links: branch diff | manifest | tags
Context
2021-03-17
02:42
Fix 1.1.0 migration regarding web pages paths and directory creation check-in: 3786504a5f user: bohwaz tags: dev
01:24
Fix issues with duplicate file names in transactions check-in: e6e012838d user: bohwaz tags: dev
00:46
Migrate files after commiting schema update check-in: fccfcb5cbf user: bohwaz tags: dev
Changes

Modified src/include/data/1.1.0_migration.sql from [9cc2337781] to [ad24737748].

42
43
44
45
46
47
48














49
50




51

52
53
54

55
56
57
58
59
60
61
62
63
64
65
66
67
68
		cacher
	FROM membres_categories_old;

DROP TABLE membres_categories_old;

UPDATE recherches SET contenu = REPLACE(contenu, 'id_categorie', 'id_category') WHERE cible = 'membres' AND contenu LIKE '%id_categorie%';















-- Copy existing files for transactions
INSERT INTO files (path, parent, name, type, mime, modified, size, image)




	SELECT 'transaction/' || t.id || '/' || f.nom, 'transaction/' || t.id, f.nom, 1, f.type, f.datetime, c.taille, f.image

	FROM fichiers f
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu
		INNER JOIN fichiers_acc_transactions t ON t.fichier = f.id;


INSERT INTO files_contents (id, compressed, content)
	SELECT f2.id, 0, c.contenu
	FROM fichiers f
		INNER JOIN files f2 ON f2.name = f.nom AND f2.parent = 'transaction/' || t.id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu
		INNER JOIN fichiers_acc_transactions t ON t.fichier = f.id;

-- Copy wiki pages content
CREATE TEMP TABLE wiki_as_files (old_id, new_id, path, content, title, uri,
	old_parent, new_parent, created, modified, author_id, encrypted, type, public);

INSERT INTO wiki_as_files
	SELECT







>
>
>
>
>
>
>
>
>
>
>
>
>
>


>
>
>
>
|
>
|
|
|
>


|

|
|
<







42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80

81
82
83
84
85
86
87
		cacher
	FROM membres_categories_old;

DROP TABLE membres_categories_old;

UPDATE recherches SET contenu = REPLACE(contenu, 'id_categorie', 'id_category') WHERE cible = 'membres' AND contenu LIKE '%id_categorie%';

CREATE TEMP TABLE files_transactions (old_id, old_transaction, old_name, new_path, new_id, same_name);

-- Adding an extra step as some file names can be the same!
INSERT INTO files_transactions
	SELECT f.id, t.id, f.nom, NULL, NULL, NULL
	FROM fichiers f
		INNER JOIN fichiers_acc_transactions t ON t.fichier = f.id;

UPDATE files_transactions SET same_name = 1
	WHERE old_id IN (SELECT old_id FROM files_transactions GROUP BY old_transaction, old_name HAVING COUNT(*) > 1);

-- Make file name is unique!
UPDATE files_transactions SET new_path = 'transaction/' || old_transaction || '/' || COALESCE(same_name, (old_id || '_'), '') || old_name;

-- Copy existing files for transactions
INSERT INTO files (path, parent, name, type, mime, modified, size, image)
	SELECT
		ft.new_path,
		dirname(ft.new_path),
		basename(ft.new_path),
		1, f.type, f.datetime, c.taille, f.image
	FROM files_transactions ft
		INNER JOIN fichiers f ON f.id = ft.old_id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu;

UPDATE files_transactions SET new_id = (SELECT id FROM files WHERE path = new_path);

INSERT INTO files_contents (id, compressed, content)
	SELECT ft.new_id, 0, c.contenu
	FROM fichiers f
		INNER JOIN files_transactions ft ON ft.old_id = f.id
		INNER JOIN fichiers_contenu c ON c.id = f.id_contenu;


-- Copy wiki pages content
CREATE TEMP TABLE wiki_as_files (old_id, new_id, path, content, title, uri,
	old_parent, new_parent, created, modified, author_id, encrypted, type, public);

INSERT INTO wiki_as_files
	SELECT