+++ /dev/null
-
-Administrative tasks with SQL
------------------------------
-
-If you run your Wiki with one of the SQL backends, you can leverage
-the advanced functions of your database server in some cases. It is
-not necessary to create a complicated or slow script to clean things
-up then.
-
-Suddenly many settings in ewiki pages are encoded into the {meta}
-field, which isn't accessible from SQL queries. But the most basic
-page flags always remain accessible, so you can utilize it within
-queries.
-
-A few examples for common tasks follow. It is recommended that you
-always perform a SELECT, before you start an unrecoverable DELETE
-or UPDATE or something else.
-Enter the given SQL commands into PhpMySqlAdmin or your commandline
-'mysql' program, if you have a direct or shell/ssh access to the
-Web server near your Wikis database. The examples here are wrapped
-across multiple lines to make them more readable, but you should
-enter them without any newline in between, and append the ;
-semicolon always at the end.
-
-
-
-deleting old binary entries
----------------------------
-
-If your users upload lots of images and data
-files, and you want to get rid of them, this
-recipie may be for you.
-
- The {flags} field is an integer, with basic page settings encoded
- bit wise. Of course you can utilize this in SQL with the bit
- operations that every database supports. You however first need
- to look up the page flag values in the core script (ewiki.php).
-
- The EWIKI_DB_F_BINARY flag for example corresponds to the 2 (two,
- decimal).
-
- You also have a {lastmodified} field with every page / file entry
- in your ewiki database, which you can query for. You however need
- to calculate your wanted time frame into seconds, because all time
- fields in the ewiki database use Unix timestamps (seconds since
- 01-01-1970 00:00).
- Once day has 24 hours, 60 minutes, a 60 seconds. So for 120 days
- you would multiply 24*60*60 with *120 and get 10368000 seconds,
- which we could use in the SQL query (we however simply embed the
- formula here):
-
- SELECT pagename, version
- FROM ewiki
- WHERE (lastmodified + 120*24*60*60 < unix_timestamp())
- AND (flags & 2) ;
-
- And after testing it, to really delete the old binary entries
- then:
-
- DELETE
- FROM ewiki
- WHERE (lastmod ...
- AND (flags ...
-
-
-
-deleting similarily named but redundant pages
----------------------------------------------
-
-If some spammer pestered you with almost same-
-named pages (by using a script or so), you can
-easily get rid of it.
-
- Regular expression provide a powerful means to select multiple
- database entries at once. Allthough you could do the same with
- the 'ewikictl' utility or a similar of our database tools/
- collection, the according SQL query may help you too.
-
- You probably know *.* from old DOS days, Windows UIs or the Linux
- commandline - regular expressions are almost the same, only that
- you write ".+" to say "one or more random characters". There are
- other things like "[0-9]" or "[a-z]" supported in regular
- expressions, but you should better read a real reference on this;
- perlre(1) or regex(3) are a good start.
-
- But just the example, assuming you wanted to delete a bunch of
- pages called "SsSs..." (with some numbers at the end) from your
- Wiki database:
-
- DELETE FROM ewiki
- WHERE (pagename REGEXP '^SsSs.+') ;
-
- Or for PostgreSQL:
-
- DELETE FROM ewiki
- WHERE (pagename ~ '^SsSs.+') ;
-
-
-
-delete non-existent _BINARY + _DISABLED entries
------------------------------------------------
-
- Referenced images, that ewiki couldn't find on a remote web server,
- and therefore didn't cache as internal:// entry remain in the DB
- as empty entries. You may want to delete them (allthough they don't
- occupy much space).
-
- DELETE FROM ewiki
- WHERE (flags & (2+4))
- AND (pagename REGEXP 'http://')
-
-
-
-
projects / atutor.git / blobdiff