RunWiki for MySQL

[Carl Gundel - admin]

Here is a quick example of RunWiki modified to use MySQL instead of SQLite.

The biggest differences are:

-The MYSQLCONNECT command expects a username and password, which are not there for the SQLITECONNECT command.
-The SQL that creates the tables is different.
-I needed to use BIGINT instead of INT for the seconds column in the pages table because SQLite supports much bigger values for INT.

Since I am a MySQL newbie I welcome any pointers from those more experienced.

-Carl
'Run Wiki v1.01 for MySQL
'This is the original Run Wiki but for MySQL instead of SQLite.  There is a
'new Run Wiki 2, but this one is included also so that users can have a simple
'wiki to easily understand and extend for their own purposes.

'define these for your MySQL database server
global dbEnvironment$, dbUser$, dbPassword$
dbEnvironment$ = "localhost"   'probably keep localhost if MySQL runs on same machine as Run BASIC
dbUser$ = "root"  'this is the default for MySQL
dbPassword$ = "Mysql"  'this is the default for MySQL

call setCSS
global currentName$, currentContent$, #editPage, #wiki, #user, #password, user$
call createDatabaseIfNeeded

currentName$ = "HOME"
call loadCurrentPage
call displayCurrentPage
wait

sub displayCurrentPage
 cls
 div title
   print "Run Wiki"
   div copyright
     print "Copyright 2008, Shoptalk Systems"
   end div
 end div
 div navigation
   if user$ = "" then
       print "Welcome, Visitor"
       print "User: ";
       textbox #user, "", 5 : print
       print "Password: ";
       passwordbox #password, "", 5 : print
       link #login, "Log in", [login]
     else
       print "Welcome, "; user$
       link #logout, "Log out", [logout]
   end if
 end div
 div page
   print "This page: "; currentName$
   if user$ <> "" then
       link #editPage, "Edit this page", [editPage] : print
   end if
   print
   call renderPage
 end div
end sub

[login]
 call connect
 #wiki execute("SELECT * from USERS where name = '"; #user contents$(); "' and password = '"; #password contents$(); "'")
 if #wiki hasanswer() then
     user$ = #user contents$()
     call disconnect
   else
     call disconnect
     cls
     div notice
       print "Login failed.  Bad username or password."
       link #retry, "Try again.", [tryAgain]
       wait
    end div
 end if
[tryAgain]
 call displayCurrentPage
 wait

[logout]
 user$ = ""
 call displayCurrentPage
 wait
 

sub renderPage

 buffer$ = currentContent$
 while buffer$ <> ""
   leftBrace = instr(buffer$, "[")
   if leftBrace = 0 then
     html buffer$
     buffer$ = ""
   else
     if leftBrace > 1 then
       html left$(buffer$, leftBrace-1)
       buffer$ = mid$(buffer$, leftBrace)
     else  'leftBrace = 1
       rightBrace = instr(buffer$, "]")
       if rightBrace < 3 then
         html buffer$
         buffer$ = ""
       else
         queryName$ = mid$(buffer$, 2, rightBrace - 2)
         if (pageExists(queryName$)) then
           link #exists, queryName$, loadPage
           #exists setkey(queryName$)
         else
           link #doesntExist, "?"+queryName$, createPage
           #doesntExist setkey(queryName$)
         end if
         buffer$ = mid$(buffer$, rightBrace+1)
       end if
     end if
   end if
 wend
end sub

sub createPage name$
 cls
 html "<h2>Create Page - "; name$; "</h2>"
 textarea #editPage, "" : print
 link #accept, "Accept", [acceptNew] : print " ";
 #accept setkey(name$)
 link #cancel, "Cancel", [cancelNew]
end sub

sub loadPage name$
 currentName$ = name$
 call loadCurrentPage
 call displayCurrentPage
end sub

[acceptNew]
 newContent$ = #editPage contents$()
 call connect
 #wiki execute("insert into pages (NAME, CONTENT, DATE, TIME) values ("""; EventKey$; """, """; doubleQuote$(newContent$); """, "; date$("days"); ", "; time$("seconds"); ")")
 call disconnect
 currentName$ = EventKey$
 currentContent$ = newContent$
[cancelNew]
 call displayCurrentPage
 wait

function pageExists(name$)
 call connect
 #wiki execute("SELECT * from pages where NAME="""; name$; """")
 pageExists = #wiki hasAnswer()
 call disconnect
end function

[editPage]
 cls
 html "<h2>Edit page "; currentName$; "</h2>"
 textarea #area, currentContent$ : print
 link #acceptEdit, "Accept", [acceptEdit] : print " ";
 link #cancelEdit, "Cancel", [cancelEdit]
 wait

[acceptEdit]
 newContent$ = #area contents$()
 query$ = "update pages set CONTENT = """; doubleQuote$(newContent$); """ where NAME = """; currentName$; """"
 call execute query$
 currentContent$ = newContent$
[cancelEdit]
 call displayCurrentPage
 wait

sub loadCurrentPage
 call connect
   #wiki execute("select (CONTENT) from pages where NAME = """; currentName$; """")
   if #wiki hasAnswer() then
     currentContent$ = #wiki nextRow$("")
   else
     currentContent$ = "Page named "; currentName$; " does not exist."
   end if
 call disconnect
end sub

sub createDatabaseIfNeeded
 mysqlconnect #wiki, dbEnvironment$, dbUser$, dbPassword$
 #wiki execute("show databases")
 count = #wiki rowcount()
 for x = 1 to count
   dbName$ = #wiki nextrow$()
   print dbName$
   if dbName$ = "runwiki" then found = 1
 next x
 if not(found) then
   'create the database
   #wiki execute("create database runwiki")
   #wiki execute("use runwiki")
   'create the pages table and add a home page
   #wiki execute("create table pages (NAME text, CONTENT text, DATE int, TIME bigint) ")
   content$ = "This is your initial home page for Run Wiki.  Please edit this and make it your own."
   exec$ = "INSERT INTO PAGES (NAME, CONTENT, DATE, TIME) VALUES ('HOME', '"; content$; "', "; date$("days"); ", "; time$("seconds"); ")"
   print exec$
     #wiki execute(exec$)
   'add the default admin user
   #wiki execute("create table users(NAME text, PASSWORD text)")  
   #wiki execute("INSERT INTO users (NAME, PASSWORD) VALUES('admin', 'password')")
 end if
 #wiki disconnect()
end sub

sub connect
 mysqlconnect #wiki, dbEnvironment$, dbUser$, dbPassword$
 #wiki execute("use runwiki")
end sub

sub disconnect
 #wiki disconnect()
end sub

sub execute sqlStatement$
 call connect
 #wiki execute(sqlStatement$)
 call disconnect
end sub

function doubleQuote$(string$)
 if instr(string$, chr$(34)) then
   for x = 1 to len(string$)
     if mid$(string$, x, 1) = chr$(34) then doubleQuote$ = doubleQuote$ + chr$(34)
     doubleQuote$ = doubleQuote$ + mid$(string$, x, 1)
   next x
 else
     doubleQuote$ = string$
 end if
end function

sub setCSS
 cssid #title, "{ font-family: Tahoma; font-size: 20pt; font-weight: bold; background-repeat: no-repeat; background-image: url('/resources/electronicbanner.JPG') ; height: 80px}"
 cssid #copyright, "{ font-size: 8pt }"
 cssid #navigation, "{font-family: Tahoma; padding: 4px; background: #CFC; width: 150px; height: 600px; float: left }"
 cssid #page, "{font-family: Tahoma; padding: 4px; margin-left: 155px}"
 cssid #dialog, "{ font-family: Tahoma; background: #CFC; border-bottom: 1px solid #898; padding: 8px; width: 784px}"
end sub

[kokenge]

Looks good to me.
From the little I know about both DB's there isn't much difference in the SQL commands.
mySQL information is easier to get to such as table names, field names, and field attributes. It's also easier to modify the database since mySQL has the necessary commands.

One major mySQL function I miss is the ability to place "IF" commands within the SQL.

mySQL triggers have a lot more power and are a lot easier to maintain.

There are lots of minor differences, but nothing major.

And yes it is managed, so you must have security to use the DB.

OK! So how do I try this mySQL???


Have a great day..

[Carl Gundel - admin]

I can't release it into the wild yet because I am having other growing pains that would prevent it from being useful yet.

-Carl