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://')