2 Administrative tasks with SQL
3 -----------------------------
5 If you run your Wiki with one of the SQL backends, you can leverage
6 the advanced functions of your database server in some cases. It is
7 not necessary to create a complicated or slow script to clean things
10 Suddenly many settings in ewiki pages are encoded into the {meta}
11 field, which isn't accessible from SQL queries. But the most basic
12 page flags always remain accessible, so you can utilize it within
15 A few examples for common tasks follow. It is recommended that you
16 always perform a SELECT, before you start an unrecoverable DELETE
17 or UPDATE or something else.
18 Enter the given SQL commands into PhpMySqlAdmin or your commandline
19 'mysql' program, if you have a direct or shell/ssh access to the
20 Web server near your Wikis database. The examples here are wrapped
21 across multiple lines to make them more readable, but you should
22 enter them without any newline in between, and append the ;
23 semicolon always at the end.
27 deleting old binary entries
28 ---------------------------
30 If your users upload lots of images and data
31 files, and you want to get rid of them, this
32 recipie may be for you.
34 The {flags} field is an integer, with basic page settings encoded
35 bit wise. Of course you can utilize this in SQL with the bit
36 operations that every database supports. You however first need
37 to look up the page flag values in the core script (ewiki.php).
39 The EWIKI_DB_F_BINARY flag for example corresponds to the 2 (two,
42 You also have a {lastmodified} field with every page / file entry
43 in your ewiki database, which you can query for. You however need
44 to calculate your wanted time frame into seconds, because all time
45 fields in the ewiki database use Unix timestamps (seconds since
47 Once day has 24 hours, 60 minutes, a 60 seconds. So for 120 days
48 you would multiply 24*60*60 with *120 and get 10368000 seconds,
49 which we could use in the SQL query (we however simply embed the
52 SELECT pagename, version
54 WHERE (lastmodified + 120*24*60*60 < unix_timestamp())
57 And after testing it, to really delete the old binary entries
67 deleting similarily named but redundant pages
68 ---------------------------------------------
70 If some spammer pestered you with almost same-
71 named pages (by using a script or so), you can
74 Regular expression provide a powerful means to select multiple
75 database entries at once. Allthough you could do the same with
76 the 'ewikictl' utility or a similar of our database tools/
77 collection, the according SQL query may help you too.
79 You probably know *.* from old DOS days, Windows UIs or the Linux
80 commandline - regular expressions are almost the same, only that
81 you write ".+" to say "one or more random characters". There are
82 other things like "[0-9]" or "[a-z]" supported in regular
83 expressions, but you should better read a real reference on this;
84 perlre(1) or regex(3) are a good start.
86 But just the example, assuming you wanted to delete a bunch of
87 pages called "SsSs..." (with some numbers at the end) from your
91 WHERE (pagename REGEXP '^SsSs.+') ;
96 WHERE (pagename ~ '^SsSs.+') ;
100 delete non-existent _BINARY + _DISABLED entries
101 -----------------------------------------------
103 Referenced images, that ewiki couldn't find on a remote web server,
104 and therefore didn't cache as internal:// entry remain in the DB
105 as empty entries. You may want to delete them (allthough they don't
109 WHERE (flags & (2+4))
110 AND (pagename REGEXP 'http://')