MySQL Workbench

MySQL Workbench is a GUI for database development. Perhaps it's a better tool when teaching databases than the PhpMyAdmin.

MySQL Workbench

The workflow could be:

1. Pen & paper for the first drafts (alternative: Dia, if you want something on a screen).
2. ER-D and database development in MySQL Workbench
3. Implement the database in PHP via MySQLi.
4. Visualize the data in creative UX solutions ...

Python and MySQL on Linux Mint

Got an error because the MySQLdb package wasn't found. Solution:

# apt-cache search MySQLdb

python-mysqldb - Pythongrænseflade for MySQL
python-mysqldb-dbg - Pythonbrugerflade til MySQL (fejlsøgningsudvidelse)
bibus - Bibliografisk database
mysql-utilities - collection of scripts for managing MySQL servers
petj-HP-EliteBook-850-G1 ~ # apt-get install python-mysqldb

Then

# sudo apt-get install python-mysqldb

Then everything worked like a charm. 

Lo and Behold! My BibTex compiler works ...

A (oh horror!) .docx file in Libre Office

In order to make a literature review I need to compile a MySQL table in the BibTex format.

Now I've got a file, that looks pretty much like a BibTex formatted file. It's compiled from a MySQL database and the output is formatted by PHP.

• file: byb2.php

In the php code this loop will format the output:

while($row = $result->fetch_assoc()){
    $books[] = "\n@". $row['Type'] 
     ."{" . strtoupper( substr($row['Author'], 0,4)) 
     . "_" . $row['Year']
     . ",\n Author={" . $row['Author'] . "},\n"
     . "Title={" . $row['Title'] . "},\n"
     . "Publisher={" . $row['Where'] . "},\n"
     . "Year=" . $row['Year'] . "}\n";
   }

I made a simple markdown file and compiled it with pandoc. The result was a .docx file.

Here is the output from the database formatted via PHP:

@Book{ARIS_1958,
 Author={Aristoteles},
Title={Poetik},
Publisher={Hans Reitzels Forlag},
Year=1958}

etc. etc. etc. etc. etc.

The markdown contains a reference to a book in the BibTex file:

[@ARIS_1958]

Finally the markdown is compiled via pandoc thus:

# pandoc --bibliography=libri.bib test.md -o test.docx

The image above is a screendump from the docx-file in Libre Office. I only refered to Aristoteles, and that's the only work in the list of sources.

Ergo: Q.E.D.

That is: this is just a toolbox. The next step is literature research for the WordPress and Open Source CMS Research Project @ Business Academy Aarhus. And perhaps: come up with title with > x-factor.

WordPress and open source CMS research

I have to make a litterature review about open source CMS with focus on WordPress.

Yesterday and today I made a BibTex compiler for WordPress. I have to make a litterature review. By now I'm inspired by Umberto Eco's cardboard file cards. However, nowadays I think that a MySQL database is better suited for the storage.

Here is the system:

• https://github.com/asathoor/libri

Today the final pieces came together. The database has all entries from my bibtex file. Via PHP I can convert the MySQL data to a bibtex format. Here's the trick:

while($row = $result->fetch_assoc()){     print "\n@Book{" . strtoupper( substr($row['Author'], 0,4)) . "_" . $row['Year']     . ",\n Author={" . $row['Author'] . "},\n"     . "Title={" . $row['Title'] . "},\n"     . "Publisher={" . $row['Where'] . "},\n"     . "Year=" . $row['Year'] . "}\n";   }

Some formatting may be done via SQL, here's a sample from my Github repo:

SELECT '@Book{' as 'Book',

concat(left(`Author`,3),`Year`,',') as 'slug',
concat('Author={{' , `Author`, '}},') as 'Author',
concat('Title={{' ,`Title`, '}},') as 'Title',
concat('Year=',`Year`,'}')

FROM `libri`

So the environment for developing the project is almost there. 

json og ÆØÅæøå

Løsningen er at formattere strengen til utf-8. Se dette eksempel. Først sættes karaktersættet til utf-8, så udføres en query:

// utf-8
mysqli_query($mysqli, 'SET CHARACTER SET utf8');

// A QUICK QUERY
$query = "SELECT * FROM `Albums`";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

Sqlite - import af eksport fra MySql

Umiddelbart er det vanskeligt at importere filen direkte, fordi Sqlite ikke bruger ` og ligenende tegn. Løsningen kræver lidt editering i filen.

Jeg fjernede modellen for en tabel; men beholdt data fra INSERT INTO ... - altså noget i stil med dette:

INSERT INTO `Albums` (`Id`, `Title`, `Who`, `Year`, `Price`, `Note`) VALUES

(1, 'The Photographer', 'Philip Glass', 1983, 50, 'Minimalism'),

(2, 'Den Blå Hund', 'Gnags', 1984, 50, 'Reggae. flip'),

(3, 'London Calling', 'The Clash', 1979, 60, 'Punk, New Wave'),

(4, 'Dreamtime', 'The Stranglers', 1986, 50, 'Alternative'),

(5, 'Under a Bloody Red Sky', 'U2', 1983, 60, 'Rock'),

(6, 'Gasolin'' 3', 'Gasolin', 1973, 200, 'Pop');

Derefter oprettede jeg tablellen manuelt (men det må kunne gøres mere elegant) med udgangspunkt i dette fra MySql:

CREATE TABLE `Albums` (

  `Id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'Id',

  `Title` tinytext ,

  `Who` text ,

  `Year` year(4) ,

  `Price` int(11) ,

  `Note` varchar(2000),

  PRIMARY KEY (`Id`)

) 

Eftersom Sqlite ikke forstår  ` og andre detaljer i MySQL syntaksen fjernede jeg dette, og oprettede tabellen manuelt. Da tabellen var klar prøvede jeg en import. Det kan enten gøres i terminalen eller ved at vælge import i Sqlitebrowser (et udmærket GUI, der virker i Linux).

Og det virkede så helt uden problemer.