circle.ch :: Database

circle.ch weblog by Urs Gehrig

A weblog about libre software, law, technology, politics and the like.

2010-08-25T08:13:16

Academic, Android, Apache, Apple, Art, Best Of, Biotech, Civil Society, Content Management, Cooking, Copyright, Creative Commons, Crosspost, Culture, Database, Deutsch, DRM, Economy, Education, Event, Gadget, General, Geodata, Government, Health, Howto, Humor, Innovation, Intellectual Property, Java, Language, LaTeX, Law, Linux, Media, Moblog, Mozilla, Music, Office, Open Content, Open Source, P2P, PHP, Podcast, Politics, Privacy, Projects, Random Thought, Rant, Science, Search, Social Network, Software, Sport, Talks, Technology, Technology Transfer, Travelling, Weblog, Wiki, Wireless and Mobile, XML

31. December 2004

Mediawiki on MySQL 4.1
Database (wiki) | Wiki (wiki) - urs @ 15:55:04

Just in case you tried to intstall mediawiki-1.4beta3 on MySQL 4.1.8-win; it does not work, at least, not on my machine. Just step back to MySQL 4.0.23-win and it installes perfectly.

Related post(s):

Gallery 2 add-on to Mediawiki (9.97)
OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
MySQL 4.1 with multiline queries (5.89)
NuSphere vs. MySQL AB (5.64)
MySql AB has a new CI (5.64)

Related tags(s):

wiki, database, technology, moblog, software

Comments (1) Permalink del.icio.us

26. August 2004

OpenOffice and ODBC, MySQL Howto
Database (wiki) | Software (wiki) - urs @ 10:28:04

Came across a tutorial discussing the interoperation of OpenOffice and MySQL via ODBC [1].

[1] http://www.unixodbc.org/doc/OOoMySQL9.pdf

Related post(s):

Enhanced ODBC driver for MySQL released (11.0)
OpenOffice 1.9.60 and Angela (7.31)
nanoweb and OpenOffice.org releases (6.89)
OpenOffice Impress (*.sxi) docs to pres2 (6.65)
OpenOffice Source Project (6.57)

Related tags(s):

office, open_source, database, technology, moblog

Comments (0) Permalink del.icio.us

15. July 2004

SQLite running as Client/Server
Database (wiki) | Open Source (wiki) - urs @ 16:09:04

An unofficial release of SQLite Client/Server, called SQLite_On_Sockets is available for Windows [1]. Get more background information from the SQLite site [2].

[1] http://www.it77.de/sqlite/sqlite.htm
[2] http://www.sqlite.org/cvstrac/wiki?p=ClientServer

Related post(s):

PECL SQLite installation (12.2)
SQLite wrapper for PEAR:DB (10.3)
Cvstrac - Ready to run within a minute (9.73)
PECL extension for SQLite database (8.76)
PHP client-server for SOAP by Dietrich Ayala (8.59)

Related tags(s):

wiki, php, creative_commons, mozilla, random_thought

Comments (0) Permalink del.icio.us

13. May 2004

Phase - PHP Content Management System
Database (wiki) | Database (wiki) | Database (wiki) - urs @ 22:48:48

Phase is a CMS based on PHP and MySQL [1]. It is looks quite promising for small or medium size Website projects. One of its current drawbacks is, that you can not access some of its menus while running it in Mozilla. Apart from that, it is worth having a look at it...

[1] http://www.cookiebean.com

Related post(s):

Wyona XPS Content Management (11.2)
PHPope - content management (11.1)
CMS based on XML, XSL, PHP and SQL (7.85)
X-Mentor Content Management System (7.75)
Midgard CMS becomes PHP extension (6.74)

Related tags(s):

moblog, php, media, talks

Comments (0) Permalink del.icio.us

Phase - PHP Content Management System
Database (wiki) | Database (wiki) | Database (wiki) - urs @ 22:48:48

Related post(s):

Wyona XPS Content Management (11.2)
PHPope - content management (11.1)
CMS based on XML, XSL, PHP and SQL (7.85)
X-Mentor Content Management System (7.75)
Midgard CMS becomes PHP extension (6.74)

Related tags(s):

moblog, php, media, talks

Comments (0) Permalink del.icio.us

Phase - PHP Content Management System
Database (wiki) | Database (wiki) | Database (wiki) - urs @ 22:48:48

Related post(s):

Wyona XPS Content Management (11.2)
PHPope - content management (11.1)
CMS based on XML, XSL, PHP and SQL (7.85)
X-Mentor Content Management System (7.75)
Midgard CMS becomes PHP extension (6.74)

Related tags(s):

moblog, php, media, talks

Comments (0) Permalink del.icio.us

12. May 2004

MySQL 4.1 INSERT Syntax
Database (wiki) - urs @ 12:49:27

MySQL of version 4.1 and above has a nice new feature in the INSERT syntax [1]. You can build a query statement like:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;

The command is quite self-explaining. It saves you some additional SELECT query magic before an INSERT statement.

[1] http://www.mysql.de/doc/en/INSERT.html

Related post(s):

MySQL 4.1 with multiline queries (9.17)
Dump specific row with mysqldump (6.61)
Microsoft Word to Wiki syntax conversion (6.18)
OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
SQLite - Embeddable SQL Database Engine (5.73)

Related tags(s):

database, technology, wiki, projects, moblog

Comments (0) Permalink del.icio.us

14. August 2003

DATE_FORMAT with MySQL
Database (wiki) - urs @ 16:38:43

A friend gave me that hint with the DATE_FORMAT function in MySQL to get all post within a month; real handy:

SELECT * FROM posts WHERE DATE_FORMAT( postdate, '%Y-%m' ) = '2003-07';

Yet another query helps to get the posts per month based on a different solution (not using the date formatting):

SELECT substring( postdate, 1, 7 ) AS months, COUNT( ID ) FROM posts GROUP BY months

[1] http://www.mysql.com/doc/en/Date_and_time_functions.html

Related post(s):

OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
MySQL 4.1 with multiline queries (5.89)
NuSphere vs. MySQL AB (5.64)
MySql AB has a new CI (5.64)
MySQL 4.0 alpha released (5.54)

Related tags(s):

database, wiki, technology, moblog, software

Comments (0) Permalink del.icio.us

03. July 2003

FULLTEXT searching with MySQL
Database (wiki) - urs @ 14:37:55

Joe Stump [1,2] discusses the FULLTEXT search feature with MySQL [3,4].

SELECT ID, post_title FROM b2posts WHERE MATCH ( post_title, post_content ) AGAINST ('sqlite');

CREATE TABLE b2posts (
ID int(10) unsigned NOT NULL auto_increment,
post_author int(4) NOT NULL default '0',
post_date datetime NOT NULL default '0000-00-00 00:00:00',
post_content text NOT NULL,
post_title text NOT NULL,
post_category int(4) NOT NULL default '0',
post_karma int(11) NOT NULL default '0',
PRIMARY KEY (ID),
UNIQUE KEY ID (ID),
FULLTEXT KEY title (post_title,post_content)
) TYPE=MyISAM;

An adaption on b2's b2posts table produces reasonable results; not investigated further though.

[1] http://www.joestump.net
[2] http://www.joestump.net/pages/articles.php/contentID,307943731
[3] http://www.onlamp.com/lpt/a/3941
[4] http://www.mysql.com/doc/en/Fulltext_Search.html

Related post(s):

TagCloud to analyze my blog (10.3)
OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
MySQL 4.1 with multiline queries (5.89)
NuSphere vs. MySQL AB (5.64)
MySql AB has a new CI (5.64)

Related tags(s):

database, technology, moblog, mozilla

Comments (0) Permalink del.icio.us

23. May 2003

SAPDB to join MySQL
Database (wiki) - urs @ 22:21:36

[via Wolfgang Drews, 1] According to an article at heise [2], the software giant SAP [3] was received a partnership with the Swedish data base developer MySQL [4]. As a goal of co-operation SAP calls the common development of a new open SOURCE data base system for applications of enterprises.

[1] http://dwp.cx
[2] http://www.heise.de/newsticker/data/hps-23.05.03-000/
[3] http://www.sapdb.org
[4] http://www.mysql.org

Related post(s):

SAP DB Programming Interface for PHP (8.37)
SAP DB goes OpenSource and PHP will be ready for it! (7.77)
Should MT, Blogger and UserLand et.al. join forces? (6.23)
OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
MySQL 4.1 with multiline queries (5.89)

Related tags(s):

weblog, database, wiki, technology, moblog

Comments (0) Permalink del.icio.us

12. May 2003

MySQL to SQLite migration
Database (wiki) - urs @ 21:47:08

Found that Perl based script to migrate MySQL tables to SQLite on Perlmonks [1].

[1] http://perlmonks.thepen.com/150476.html

Related post(s):

SQLite - Embeddable SQL Database Engine (8.50)
SQLite running as Client/Server (8.27)
SQLite wrapper for PEAR:DB (8.12)
PECL SQLite installation (8.03)
PHP 5.0.0 Beta 1 (7.36)

Related tags(s):

technology, xml, database, random_thought, open_source

Comments (0) Permalink del.icio.us

14. April 2003

MySQL compile error: gethostbyname_r
Database (wiki) - urs @ 22:24:08

To come around those errors:

libmysql.c:1329: warning: passing arg 5 of `gethostbyname_r' from incompatible pointer type
libmysql.c:1329: too few arguments to function `gethostbyname_r'
libmysql.c:1329: warning: assignment makes pointer from integer without a cast
make[2]: *** [libmysql.lo] Error 1

... I had to edit config.h like this:

/* Solaris define gethostbyname_r with 5 arguments. glibc2 defines
this with 6 arguments */
/* #undef HAVE_GETHOSTBYNAME_R_GLIBC2_STYLE */
#define HAVE_GETHOSTBYNAME_R_GLIBC2_STYLE 1

It is mainly a gcc problem, but I found [1] no other way round; until that:

me:~# apt-get install g++-2.95
Reading Package Lists... Done
Building Dependency Tree... Done
The following extra packages will be installed:
cpp-2.95 g++ g++-3.2 gcc-2.95 libstdc++2.10-dev libstdc++5-dev
The following NEW packages will be installed:
cpp-2.95 g++ g++-2.95 g++-3.2 gcc-2.95 libstdc++2.10-dev libstdc++5-dev
0 packages upgraded, 7 newly installed, 0 to remove and 0 not upgraded.[..]

After probably an hour of compilation it results in this; which is quite positive:
me:/usr/local/src/archives/mysql-4.1.0-alpha# mysql -V
mysql Ver 13.5 Distrib 4.1.0-alpha, for unknown-linux (powerpc)

[1] http://www.mysql.com/doc/en/Compilation_problems.html

Related post(s):

Apache compile HOWTOs in English and German (7.79)
OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
MySQL 4.1 with multiline queries (5.89)
NuSphere vs. MySQL AB (5.64)
MySql AB has a new CI (5.64)

Related tags(s):

apache, database, technology, moblog, weblog

Comments (1) Permalink del.icio.us

13. April 2003

MySQL 4.1 with multiline queries
Database (wiki) - urs @ 18:56:45

MySQL 4.1 offers now multiline queries [1]:

/ Connect to server with option CLIENT_MULTI_QUERIES /
mysql_real_query(..., CLIENT_MULTI_QUERIES);

/ Now execute multiple queries /
mysql_query(mysql,"DROP TABLE IF EXISTS test_table;\
CREATE TABLE test_table(id INT);\
INSERT INTO test_table VALUES(10);\
UPDATE test_table SET id=20 WHERE id=10;\
SELECT * FROM test_table;\
DROP TABLE test_table";
while (mysql_more_results(mysql))
{
/ Process all results /
mysql_next_result(mysql);
...
printf("total affected rows: %lld", mysql_affected_rows(mysql));
...
if ((result= mysql_store_result(mysql))
{
/* Returned a result set, process it */
}
}

[1] http://www.mysql.com/doc/en/C_API_multiple_queries.html

Related post(s):

OS X MySQL binary installer .pkg by Liyanage becomes official (6.12)
NuSphere vs. MySQL AB (5.64)
MySql AB has a new CI (5.64)
MySQL 4.0 alpha released (5.54)
Mediawiki on MySQL 4.1 (5.51)

Related tags(s):

database, wiki, technology, moblog, software

Comments (0) Permalink del.icio.us

01. April 2003

Generating XML with mysqldump
Database (wiki) - urs @ 20:37:13

[via http://whump.com ] Since version 3.23.48 MySQL offers the export of a database directly to XML with a single line command [1].

C:\tmp>mysqldump --xml cmsdemo > filename.xml

[1] http://techupdate.zdnet.co.uk/story/0,,t481-s2112200,00.html

Related post(s):

Dump specific row with mysqldump (9.17)
Generating Word documents using XSLT (3.80)
Generating SVG Images with PHP (3.54)
XmlWriter: Generating XML from PHP (3.40)
SWF Ming version 0.0.4 out now (3.29)

Related tags(s):

database, xml, weblog, open_source

Comments (0) Permalink del.icio.us

26. January 2003

Dump specific row with mysqldump
Database (wiki) - urs @ 13:46:57

If you want to dump just certain rows of your table with mysqldump, you can perform the following command:

C:\tmp>mysqldump "--where=post_category='1'" -t dbname b2posts > dump.sql.txt
 
 --- dump.sql.txt ---
 # MySQL dump 8.16
 #
 # Host: localhost    Database: dbname
 #--------------------------------------------------------
 # Server version 3.23.43-max-debug
 #
 # Dumping data for table 'b2posts'
 # WHERE:  post_category='1'
 #
 
 INSERT INTO b2posts VALUES (1,1,'2003-01-22 23:21:38','This is the first post. Edit or delete it, then start blogging !','Hello world !',1,0);
 

Leaving attribute "-t" away, will add the table structure to the dump as well [1].

[1] http://www.mysql.com/doc/en/mysqldump.html

Related post(s):

Generating XML with mysqldump (8.59)
The IT-Specific encyclopedia (3.67)
CC-ch weblog (3.64)
Add the Google Toolbar to your browser (3.17)
Printerfriendly Page made with CSS (3.13)

Related tags(s):

database, creative_commons, xml, moblog

Comments (0) Permalink del.icio.us

08. January 2003

OS X MySQL binary installer .pkg by Liyanage becomes official
Database (wiki) - urs @ 12:00:00

Marc Liyanage [1] who provides MySQL binary installer packages in the .pkg format for a long time has motivated MySQL to use his scripts and offers those packages officially.

"Through a partnership with Liyanage, MySQL is building on the tools and scripts Liyanage developed to offer the MySQL .pkg binaries for Mac OS X on the MySQL Web site."

[1] http://www.entropy.ch
[2] http://www.mysql.com/press/release_2002_18.html

P.S.: Kind of swiss-made by Liyanage...