Archive for the ‘Database’ Category.

Oracle Installer: Wow, can you say poor and unprofessional

So I am installing the Oracle client for the first time in my life (Yes, there are still some geek things I haven’t done yet) and wow was I surprised at the poor quality of the installer.

Here is the ugly:

1. I start the install and immediate a big black Command Prompt window comes up.
2. Once it comes up, it tries to install to a c:\app\jbarneck folder. Really? You haven’t figured out C:\Program Files\Oracle yet?
3. I installed only Instant Client. I needed Administrator as well. On reinstall it installed a new second instance of the Oracle Client. No it didn’t work and I have reverted my VM to try again.
4. When you have gone through their wizard and it is time to start the install, you click Finish. Yes, you heard correctly, you click Finish to start. When the install finishes, there is a small line of text letting me know it was successful and a close button.
5. Then I have to go get some text file called a .ORA file and copy it myself.

Anyway. If it was an Open Source project it wouldn’t be this poor. This is something I would have expected in the NT 4.0 days.

So if this is the lack of enterprise quality that I can expect from Oracle, which is a very rich “enterprise”, I will continue to stay away and recommend others do the same.

All I can say is: Wow, Oracle! Get your act together.

Using OSQL with Microsoft SQL databases

OSQL basic commands

Logging into a database with osql

c:\> osql -S [servername\instance] -U username [-P password]

Example1: Specifying the user and letting the command line prompt for a password.

c:\> osql -S Core\ldmsdata -U sa
password:

Example2: Specifying the user and the password.

c:\> osql -S ld87\ldmsdata -U sa -P pw

Example3: Using a trusted connection

c:\> osql -E

Show databases

1>
2>
select * from sysdatabases
go

Note: Or to see only the database Name row do the following:

1>
2>
select Name from sysdatabases
go

Creating a database

1>
2>
create database DatabaseName
go

Selecting a database

1>
2>
USE master
go

Drop a database

1>
2>
drop database DatabaseName
go

Show tables

1>
2>
1>
2>
USE DatabaseName
go
select * from INFORMATION_SCHEMA.TABLES
go

Note: Or to see only the Table_Name row do the following:

1>
2>
1>
2>
USE DatabaseName
go
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
go

Drop a table

1>
2>
1>
2>
USE DatabaseName
go
drop table TableName
go

Insert a row into a table

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName Values(“Column1value”,”Column2value”,”Column3value”)
go

Note: Or to insert by only providing values for a few columns and letting the other columns take the default values. This is useful when the first column is set to AUTO_INCREMENT.

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName (Col2, Col3) Values(“Column2value”,”Column3value”)
go

Update a value in row of a table

1>
2>
1>
2>
USE DatabaseName
go
UPDATE TableName set ColumnName=’NewValue’ where SomeColumn=’whereValue’
go

Drop a view

1>
2>
1>
2>
USE DatabaseName
go
drop view ViewName
go

Backup a database

1>
2>
BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT
go

You can do this at the command prompt with one single command:

c:\> osql -S ld87\ldmsdata -U sa -P pw -Q “BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT”

Change the SA password with one line in a command prompt

c:> osql -E -S localhost\ldmsdata -d ULDDB -Q “sp_password NULL,’P@ssword’,’sa'”

I hope this helps you.

More Information

For more information see the following website:

  1. Administering SQL Server Using osql
  2. osql Utility

Drupal, Joomla, WordPress, SilverStripe, or other, which Content Management System (CMS) is best for a BSD user?

Choosing a Content Management System

Ok, so as today, August 26, 2010, my blog is hosted by WordPress.com.  It will be this way for some time.

However, I am probably going to eventually have my own server hosted somewhere. Probably with my brother’s company, Fusion Networks.

So I will probable build a FreeBSD server that will host my site.  At this point, I think I will stop using WordPress and use a different Content Management System.

Content Management Systems Lists

So I went into this having done reasearch before, so I knew what I was getting into.  There are plenty of different Content Management Systems and unfortunately there is no way I could find time to test them all.

Wikipedia had a great list of Content Management Systems.
http://en.wikipedia.org/wiki/List_of_content_management_systems

Ok, now that  you have seen the very large list of Content Management Systems, you realize that I have to narrow my list down without really testing them all.

Narrowing down the list

First, let me document what I would like and see if which fit into the list.  These are not in order of priority, and some are important and some are not.

I want it to

  1. use PHP
  2. have a large community
  3. use a BSD License
  4. use Postgresql (as it is BSD licensed and MySQL is not)
  5. import my existing WordPress blog
  6. have great feature sets
  7. have common plugins
  8. support multiple Blogs with one install
  9. manages website as well as blog
  10. manages multiple sites
  11. have a descent editor
  12. be well documenated

PHP-Based

They have their lists separated  into tables by programming language.  The PHP table has the most options and since I for sure want to use PHP, it is the only table I will focus on.  These tables even lists the license.

However, I did look at the other tables, just in case one was mostly PHP-based but didn’t show up in the PHP list.  For the full PHP list, go here:
http://en.wikipedia.org/wiki/List_of_content_management_systems#PHP_2

Large community

So the big three Content Management Systems seems to be these.  I don’t have any data to back this up.  I think this is just because these three seem to be more mentioned when doing Google searches.

  • Drupal
  • Joomla
  • WordPress

BSD Licensed

However, none of those are BSD Licensed, which I prefer. I am not opposed to GPL or LGPL, but I feel more comfortable with BSD licenses software.

The Content Management Systems that are PHP-based and use the BSD License are these:

Had one of the Big 3 been BSD licensed, my decision would have probably been easy.  However, obviously I am going to have to test some of the three BSD licensed Content Management Systems.

Supports Postgresql

Of the six Content Management Systems listed above, the following support Postgresql.

  • Drupal
  • Serendipity
  • SilverStripe

Can import my WordPress blog

It appears that they all can do this.

I am worried about links, however.  My blog entries often link to each other.  I question whether they do it perfectly.  For example, a link to one of my WordPress blogs might be this:

http://rhyous.com/2010/08/25/freebsd-now-has-the-service-command/

So the link has this format:

http://homepage/YYYY/MM/DD/name-of-blog-entry/

So if I go with something other than WordPress, then after importing my blogs, are all the links going to be broken?

Making the Decision

So here is the rating, after a very small overview of the different CMS web sites, and before verifying their marketing claims.

I am giving 0 to 3 points for each feature. (This is subject to change over time, should I want to come back after the decision and report on something).

Features Worpress Drupal Joomla Serendipity SilverStripe Pimcore
1. PHP-Based 3 3 3 3 3 3
2. Large community 3 3 3 1 1 1
3.BSD License 0 0 0 3 3 3
4. Postgresql Support 0 3 0 3 3 0
5. Imports WordPress 3 1 ? 1 1 ?
6. Great Feature set 3 3 3 1 3 ?
7. Has common plugins 3 3 3 3 2 ?
8. Multiple Blogs 3 3 3 3 2 ?
9. Manages Website as well as blog 3 3 3 0 3 ?
10. Multiple Sites 2 3 ? 0 3 ?
11. Editor 3 3 ? 2 3 ?
12. Documenation 3 3 ? 1 3 ?
Score: 29 31 ? 21 30 ?

Here is what I am going to do:

I am going to build a BSD, Apache, PHP, Postgresl virtual machine and test Drupal and Serendipity to see how the import goes.

Perhaps I will have a follow-up some time because:

  1. I have to test these different CMSes
  2. I am sure there are a dozen other items that should be scored 1-3 that I haven’t thought of.

Serendipity

I started out looking at Serendipity first.

Positives

http://rhyous.wordpress.com/wp-admin/post.php?post=2212&action=edit&message=1
There was a FreeBSD port.

The install was painless.

Negatives

Their web site didn’t seem very good, which is a big turn off for a company that is a CMS.

The links to their Forum on their web site and other pages were broken.  I did find that the forum does exist, so the links are just broken.

I tried to sign up for the Forum to report the broken links.  I registered, but was supposed to be sent a confirmation email, which I never received, even after re-requesting it, so I have never reported the broken links.  They are still broken a day later. Also, there appear to only be a handful of people on their forums at any given time. Both suggest they have a small community.

I couldn’t figure out how to design my home page. It seemed the blog was my home page.  I couldn’t figure out how to move my blog entries to a different page.

Result

If you want one blog and nothing else, this would work for you.

I need more. I stopped trying and have dropped Serendipity from the list.

SilverStripe

I looked at SilverStripe second and I way more pleased with its feature set.

Positives

Though I had some install problems, I found solutions on their forums.

There are a number of theme available for download.

The default web site had some common pages premade:

  • Home
  • About Us
  • Contact Us
  • Page not found

They had both Site Management and Blog management.

There are many extensions: modules, themes, widgets.

Modules include a Blog Modules and Sub-site module, so I could manage multiple sites and multiple blogs.

It is simple to download and install modules.

The user interface is simple and easy to use.

The documentation is sufficient.

Negatives

I had install/setup problems (that were resolved) by the Forum.  Supposedly they only occur with Postgresql.

Not a lot is included in the default install.

The community is smaller than I would like.

The documentation is only sufficient.

Conclusion

I really like the SilverStripe interface.

It had almost everything I am looking for.  It is BSD Licensed.  It supports Postgresql.

It is a Content Management System first, and has modules to add to it.  There a blog module, a multi-site module, which really interested me.

However, while it has a module to import a WordPress xml, I have yet to get it to work.  That is something I can live with, but I would prefer it to work.

Drupal

Positives

It has many features, and any feature it lacks is pretty much filled by an add-on or widget.

The documentations seems thorough.

The community looks to be large.

Negatives

I had to install a different version of wordpress to support multiple blogs.

It is GPL instead of BSD Licensed.

The admin site seemed rather overwhelming. There were almost too many options.

Conclusion

If this were BSD licensed, it would be my pick.  It seems great.

WordPress

Positives

It is popular and familiar.  The most common platform of the blogging world.

It has many features, and any feature it lacks is pretty much filled by an add-on or widget.

Migrating from my existing WordPress blog is flawless.

The community is huge.

The documentation is a plenty.

Negatives

It doesn’t support Postgresql.

It is GPL instead of BSD Licensed.

Joomla

I almost feel like apologizing to Joomla.  I had two BSD servers, one with FreeBSD, Apache, PHP, MySQL where I installed WordPress a year ago for testing.  I have another brand new server with FreeBSD, Apache, PHP, and Postgresql where I tested all the postgresql capable CRMs.  However, I installed Joomla to the wordpress server and it just didn’t work.  The page was blank.  I actually think there was a php error, not a Joomla error.

Unfortunately, Joomla doesn’t support postgresql, it isn’t BSD licensed.  I did research and it appears like it would score high with WordPress, Drupal, and Silverstripe, but I can’t really see how it would be any better than Drupal, or Silverstripe so I really had to cut it loose.  Lets be honest.  As a BSD users, it was beaten out by Drupal and Silverstripe for the same reason I am looking to replace WordPress.  It doesn’t support Postgresql.

Others

There are two other Content Management Systems that I feel are worth mentioning.

Django is also a CMS that is BSD licensed and supports Postgresql. However, it runs on Python, a language which I have occasionally touched but never really developed in.  If you know Python, this might be the choice for you.

Bricolage is also a CMS that is BSD licensed and supports Postgresql. However, it runs on Perl, which I have written a few things with, but not as much as PHP.  If you know Python, this might be the choice for you.

My Decision

I know, you all want to know which I chose to use.

Drumb roll please….

I have chosen…

But don’t expect my blog to move to Silverstripe tomorrow.  It move slowly with things like this.  It could take months or get delayed for a year.

Anyone who might choose a CMS differently would be completely happy with any of the others they evaluate and feel meets their needs.  I am not saying I chose the best.  I am saying I chose the best for my needs.  Hopefully, you know your needs and this will help you choose the best Content Management System for your needs.

Installing and Using Postgresql on FreeBSD

Postrgesql is an excellent alternate to MySQL.

It is BSD Licensed instead of GPL, which is especially more attractive if you need to distribute the database software.  In fact, if you are reselling a product, and paying a license fee to MySQL you probably should save your company the money and move to Postgresql.  LANDesk, the company I work for, had just such an experience with our Management Gateway device.

Setting up FreeBSD

Ok, so I already have a post on this here:

How do I install FreeBSD?

Also, make sure to download ports.

What are the first commands I run after installing FreeBSD

Installing Postgresql on FreeBSD

As always, installing software on FreeBSD is simple using the ports system.

#
#
cd /usr/ports/databases/postgresql84-server
make WITH_OPTIMIZED_CFLAGS=true BATCH=yes install clean

Post-installation Setup

There a few post-installation steps.

Initialize the database

# /usr/local/etc/rc.d/postgresql initdb

Or starting with FreeBSD 8.1, you can now run this command:

# service postgresql initdb

Make any changes to the postgresql.conf

The postgresql.conf is located in /usr/local/pgsql/data.

Open the file and read through it and make any desired changes.

Common Changes

Here are two common changes.

Enabled remote connections

If the database is to be accessed by the network, then you should at least uncomment the setting #listen_addresses = 'localhost' and change it to listen_addresses = '*'.

Changing the default TCP Port

Uncomment the setting #port = 5432 and change the port number to the desired value.

Configure password authentication

  1. Change to the /usr/local/etc/pgsql/data directory.
  2. Edit the pg_hba.conf and change the default authentication method to something more secure, such as md5.
    # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD# "local" is for Unix domain socket connections only
    local   all         all                               md5
    # IPv4 local connections:
    host    all         all         127.0.0.1/32          md5
    # IPv6 local connections:
    host    all         all         ::1/128               md5
    

Configure the postgresql server service to start on reboot

Add the string postgresql_enable="YES" to /etc/rc.conf.

# echo ‘postgresql_enable=”YES”‘ >> /etc/rc.conf

Creating a Database

To create a database, su to pgsql and run createdb.

#
$
su pgsql
createdb MyDBName

Note: Similarly, use dropdb to drop a database.

Creating a User or Role

To create a user, su to pgsql and run createuser.

#
$
su pgsql
createuser -P

Enter name of role to add: MyUserOrRoleName
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

If you are not using password authentication, then you can exclude the -P.

Using psql client

A simple way to connect to postgresql is using the shell-based client, psql.

Connecting

To connect, use the following command.

$ psql MyDBName

MyDBName-#

You are now at the psql prompt.

Connecting as a different user

To connect, use the following command.

$ psql MyDBName MyUserOrRoleName

MyDBName-#

You are now at the psql prompt.

Changing a a User or Role Password

To change a password, use the following syntax.

MyDBName-# alter role pgsql with password ‘pw’;

Show Databases

To show database, simply type \l, (which is the lowercase letter L not the number 1) which is short hand in psql for this query:
SELECT datname FROM pg_database;

MyDBName-# \l

Show Tables

To show tables in the current database, simply type \d, which is short hand in psql for this query:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

MyDBName-# \d

Show Tables

To show columns of a table in the current database, simply type \d table, which is short hand in psql for this query:
SELECT column_name FROM information_schema.columns WHERE table_name ='table';

MyDBName-# \d MyTableName

Creating a Table

To create a table, use the following syntax:

MyDBName-# CREATE TABLE Users (
FirstName text,
LastName text,
DateOfBirth date
);

Inserting data into a Table

To insert data into a table, use the following syntax:

MyDBName-# CREATE TABLE Users (
UserId serial,
FirstName text,
LastName text,
DateOfBirth date
);

Ok, from here you should be able to start figuring everything out on your own.

Resources:
http://www.postgresql.org/docs/8.4/interactive/index.html


Copyright ® Rhyous.com – Linking to this page is allowed without permission and as many as ten lines of this page can be used along with this link. Any other use of this page is allowed only by permission of Rhyous.com.

How to use DataTable.Select() when the DataColumn.ColumnName contains a space?

I have a DataColumn that contains an space and I just couldn’t get the DataTable.Select(“Column Name=value) function to work.

So i found a solution on some other guys wordpress blog here.

The answer should be obvious to those who use SQL. In SQL to use a space, it often adds square brackets around the column names. [Column Name]. Yes, using square brackets is the solution.

String colName = "Column Name";
String Value = "some data";
DataTable.Select("[" + colName + "]='" + value + "'");

What is and how do I install the Northwind database?

The Northwind database is referenced online in sample code quite often. However, this renders the sample code useless to someone who doesn’t know what Northwind is or how to use it.

The Northwind database is basically just an example database that runs under SQL Server. This database is populated with data that represents an imaginary company’s sales data. It is a very common example database for SQL Server testing and sampling.

You might be wondering, what is SQL Server (though for your sake, I hope not). Well, SQL Server is Microsoft’s database software.

How do I know if SQL is installed?
If you have Visual Studio 2008 installed, you probably have SQL Server 2008 installed and you don’t even know it.

You can go to Add / Remove Programs and look for Microsoft SQL Server.

Or you can check for the services.

Or if you aren’t good with the GUI, you can open a command prompt and run this command to see if you have the SQL services:

C:\Users\UserName> sc query state= all |findstr SQL |findstr DISPLAY_NAME

DISPLAY_NAME: SQL Server (SQLEXPRESS)
DISPLAY_NAME: SQL Active Directory Helper Service
DISPLAY_NAME: SQL Server Agent (SQLEXPRESS)
DISPLAY_NAME: SQL Server Browser
DISPLAY_NAME: SQL Server VSS Writer

If you don’t see the services ouptut, you don’t have SQL Server Express installed. If you do have it, it is installed.

SQL Server is installed, now where do I get the Northwind Database?
Well, this was a struggle even for me. All the posts say the database creation script was installed with Visual Studio, but I sure don’t have any database creation scripts installed.

So I searched the web and Microsoft’s site for a while.

I finally found this link, but not until after about an hour of searching, hopefully this saves you some time.
http://www.codeplex.com/Wikipage?ProjectName=SqlServerSamples

So as of 3/4/2010, there was not “new” Northwind database, just and old one for SQL 2000. Which is fine because that old database is what most the sample code you find will be using.

I clicked on the Download link next to SQL Server 2000 Sample DBs.
I downloaded a ZIP file.
I extracted it.
I found the instnwnd.sql script among the extracted files.

How do I use the instnwnd.sql file to install the Northwind Database
This instnwnd.sql is nothing more than SQL script that will install the Northwind database for you. Well, you basically need your SQL server to run this script file and that is it.

If you have SQL Server Management Studio, just open it up and connect to your database, then File | Open the script and run it. But maybe you don’t know what SQL Server Management Studio is, let alone how to open it.

Sound easy right.

Well, everything sounds easy to some one who knows exactly how to do it, but if you don’t now how, it doesn’t sound easy. If you are among those that are hearing about this for the first time, let me help you.

Well, every server that has SQL Server installed has a command line tool installed called sqlcmd.exe. Hey, if I give you a command line you can run it, even if you don’t know what is really going on.

So just open a command prompt and run this command:

C:\Users\UserName> sqlcmd -E -i c:\path\to\instnwnd.sql

Changed database context to ‘master’.
Changed database context to ‘Northwind’.

Ok, the database installed now what?
Well, now you have the Northwind database installed.

From here you are one your own getting whatever sample code you have to connect to this database and compile.

How to configure Subversion to use Cyrus-SASL2 to authenticate to a MySQL database?

Ok, so I want to have Subversion authentication work from a MySQL database. I am going to try to use Cyrus SASL for this.

I already have instructions for installing the necessary parts:

  1. Install FreeBSD.
    How do I install FreeBSD?

  2. Update FreeBSD and download the ports tree.
    What are the first commands I run after installing FreeBSD

  3. Then install Subversion, however, one difference you need to make to the install instructions for subversion. You need to install with SASL2 support. When you run make install it is an option.
  4. How to install subversion 1.6.6 on FreeBSD 7.2

Ok, now that you have everything is installed, you are were I am and ready to try to get this configured.

Configuring Subversion to use SASL to Authenticate to a MySQL database

  1. Create a simple MySQL database. The following is a simple database creation script that creates a database with one table and two rows.
    CREATE DATABASE UserDB;
    USE UserDB;
    CREATE TABLE `users` ('username' varchar(255), 'password' varchar(255) )
    INSERT INTO users VALUES ('user1','pw1');
    INSERT INTO users VALUES ('user2@MyReal.com','pw2');
    INSERT INTO users VALUES ('user3@myemailaddress.com','pw3');
    

    Note: I use these accounts to show what works and what does not work because the idea of “realms” is confusing.

    You may be asking why I don’t have three rows, one for each item: User, Password, Realm.

    Well, if you really are creating a new database to handle SVN Users then that is how you should do it and here is it is.

    CREATE DATABASE UserDB;
    USE UserDB;
    CREATE TABLE `users` ('username' varchar(255), 'password' varchar(255) , 'realm' varchar(255))
    INSERT INTO users VALUES ('user1','pw1','realm');
    INSERT INTO users VALUES ('user2@MyReal.com','pw2','realm');
    INSERT INTO users VALUES ('user3@myemailaddress.com','pw3','realm');
    

    However, because I am assuming that you want to authenticate to users that are in an already existing database, realm won’t really exist. However, you may have usernames that are in email format, or not in email format an that makes a difference because Subversion splits the username at an @ symbol and the username is only what is before the @ symbol. See the troubleshooting realms section below.

  2. Edit the following file:
    /home/svn/repos/MyApp/conf/svnserve.conf

    # ee /home/svn/repos/MyApp/conf/svnserve.conf

    The following are the lines that should NOT be commented out.

    [general]
    anon-access = none
    auth-access = write
    realm = MyDomain.com

    [sasl]
    use-sasl = true

  3. Create and edit the following file:
    /usr/local/lib/sas2/svn.conf

    # ee /usr/local/lib/sas2/svn.conf

    The following are the lines that should NOT be commented out.

    [general]
    pwcheck_method: auxprop
    mech_list: plain
    auxprop_plugin: sql
    sql_hostnames: localhost
    sql_engine: mysql
    sql_user: root
    sql_passwd: pw
    sql_database: UserDB
    sql_select: SELECT password FROM users WHERE username='%u'

    Note: For debugging add log_level: 7 to this file and then watch the /var/log/debug file.

You should now be able to connect with a client such as TortoiseSVN and connect

Troubleshooting Realms

I found some issues with realms that were really confusing.

Change your /usr/local/lib/sas2/svn.conf file to look like this:

[general]
log_level: 7
pwcheck_method: auxprop
mech_list: plain
auxprop_plugin: sql
sql_hostnames: localhost
sql_engine: mysql
sql_user: root
sql_passwd: pw
sql_database: UserDB
sql_select: SELECT password FROM users WHERE username='%u' or username='%u@%r'

I added two changes:

  1. Turned on logging.
  2. Changes the sql statement to look for username='username' or username='username@realm'

I had to do this because if the user was using an email address, such as john@domain.tld, then it actually makes %u only equal John and seems to drop the @domain.tld and replace it with the name of the realm. However, if your username is John@domain.tld and your realm is domain.tld (so realm and domain are the same), then the code above works. If you domain and realm are not the same, I haven't been able to get this to work.

Go ahead and run this command:

# tail -f -n 30 /var/log/debug

And then try to authenticate using a client, such as TortoiseSVN. Test all three users. You will see the SQL Queries that are run. It should work to authenticate as user1 or User2, but it cannot authenticate user3 because it just doesn't work due to the way it handles realms. If you have users that don't have email address in your realm, then you need them to have username that are not email addresses.

Final Question
If the password is stored as an md5, sha1, sha256 hash in the database, how do I make this work?


Copyright ® Rhyous.com - Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to install Bugzilla on a FreeBSD 7.2 with Apache + SSL and MySQL?

How to install Bugzilla 3.4.2 on FreeBSD 7.2.

The basic overivew.

  1. Install FreeBSD.
    How do I install FreeBSD?

  2. Update FreeBSD and download the ports tree.
    What are the first commands I run after installing FreeBSD

  3. Then install Apache + SSL.
    Installing an Apache + SSL on FreeBSD using the ports tree

  4. Then install MySQL.
    How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

  5. Configure MySQL to be Unicode.
    How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

  6. Then install Bugzilla

I have previous documents about installing each of the steps above installing Bugzilla. This document will over cover bugzilla.

Installing Bugzilla From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$

su

Password:

ServerName#
ServerName#
ServerName#
portsnap fetch
portsnap extract
portsnap udpate

Then just do this to install Bugzilla 3.4.2 on FreeBSD 7.2.

ServerName#
ServerName#
cd /usr/ports/devel/bugzilla
make BUGZILLADIR=/usr/local/www/apache22/data/bugzilla install

Note: Make sure you choose the correct install directory for the BUGZILLADIR parameter. By default Apache 2.2 is only serving up files in /usr/local/www/apache22/data/ so by install bugzilla there, you will be able to access bugzilla with this url: http://www.YourWebSite.com/bugzilla

You will be asked to select your compile options throughout. If you don’t want to be promtped, and you want to accept the defaults, use this command.

ServerName# make BATCH=yes install

Now that you have Bugzilla 3.4.2 on your FreeBSD 7.2 server, you are not finished. We now need to connect to connect it to a database, which I am assuming is MySQL but could just as easily be Postgresql.

Resetting the file ownership recursively on the bugzilla folder

Make sure that the bugzilla folder and all subfolders are owned by www:www.

ServerName# chown -R www:www /usr/local/www/apache22/data/bugzilla

Creating a MySQL Database

  1. Log into mysql. I use the command line and type in mysql -p, enter my password when prompted.
  2. Create a database for Bugzilla.
  3. Create a user that can access Bugzilla.
  4. I use the followiing SQL commands for these steps:

    CREATE DATABASE BugDB
    
    GRANT SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
               CREATE TEMPORARY TABLES, DROP, REFERENCES
               ON BugDB.* TO BugDBUser@localhost
               IDENTIFIED BY 'P@sswd!';
    FLUSH PRIVILEGES;
    

Run Install Check Script

  1. In a command prompt go to /usr/local/www/data/bugzilla
    ServerName# cd /usr/local/www/data/bugzilla
  2. Run the setup checking script.
    ServerName# ./checksetup.pl
  3. Now you are ready to open and edit the localconfig file.
    ServerName# ee localconfig
  4. Change the following values:

    $webservergroup = ‘www’
    $db_name = ‘BugDB’
    $db_user = ‘BugDBUser’
    $db_pass = ‘P@sswd!’

    Then close and save the localconfig file.

  5. Run ./checksetup.pl again.
  6. Note: If you have installled everything including MySQL using the defaults, you will see this warning:

    WARNING: You need to set the max_allowed_packet parameter in your MySQL configuration to at least 3276750. Currently it is set to 1048576. You can set this parameter in the [mysqld] section of your MySQL configuration file.

    Resolve this using the MySQL configuration file called my.cnf. I discussed the my.cnf earlier in this article, so you should already be familiar with it.
    How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

    Find the max_allowed_packet settings and change it to 4M.

    max_allowed_packet = 4M

    Restart MySQL.

    ServerName# /usr/local/etc/rc.d/mysql-server restart
  7. Run checksetup.pl again.

    I got this error:

    Creating ./lib/.htaccess…
    No such file or directory at Bugzilla/Install/Filesystem.pm line 445, line 275.

    I had to manually create the /usr/local/www/apache22/data/bugzilla/lib directory then this error disappeared when I ran checksetup.pl again.

  8. Now create an Apache configuration file for bugzilla and put it in /usr/local/etc/apache22/Includes. I name it bugzilla.conf.

    bugzilla.conf

    <Directory "/usr/local/www/apache22/data/bugzilla">
      Options +ExecCGI
      AllowOverride Limit
      DirectoryIndex index.cgi
      AddHandler cgi-script .cgi
    </Directory>
    

    Restart Apache

    ServerName# /usr/local/etc/rc.d/apache22 restart
  9. You should now be able to connect to your server: http://YourServer/bugzilla


    Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to start, stop, restart MySQL on FreeBSD or Red Hat?

FreeBSD

Starting MySQL


/usr/local/etc/rc.d/mysql-server start

Stopping MySQL


/usr/local/etc/rc.d/mysql-server stop

Restarting MySQL


/usr/local/etc/rc.d/mysql-server restart

Red Hat

Starting MySQL


/etc/init.d/mysql start

or


/sbin/service mysql start

Stopping MySQL


/etc/init.d/mysql stop

or


/sbin/service mysql stop

Restarting MySQL


/etc/init.d/mysql restart

or


/sbin/service mysql restart

How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

FreeBSD
There are two easy ways on FreeBSD:

From Ports

You can install easily from Ports. Make sure your ports tree is up to date:

$ su

Password:

ServerName#

ServerName#

ServerName#

portsnap fetch

portsnap extract

portsnap udpate

Then just do this to install MySQL on FreeBSD.

ServerName#

ServerName#

cd /usr/ports/databases/mysql51-server

make install

Or if you want to use utf8 by default, run this command:

ServerName# make WITH_CHARSET=utf8 install

MySQL 5.1 Server (and MySQL 5.1 client) will download, compile, and install automagically for you.

From Packages

You can also install easily as a binary package with this simple command.

ServerName# pkg_add -r mysql51-server

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html

Red Hat
Using RPM

You have to go to the MySQL site and download the MySQL 5.1 server RPM and install it.
http://dev.mysql.com/downloads/

It does not automatically install the MySQL client, you have to download that as a separate RPM and install it.

Using yum

Since I didn’t have a MySQL license, yum didn’t work, so I don’t know if it can be installed using yum.

Make sure to secure you MySQL installation.
http://dev.mysql.com/doc/mysql-security-excerpt/5.1/en/default-privileges.html


Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to create a UTF-8 Unicode Database on MySQL and make UTF-8 Unicode the default?

How to create a UTF-8 Unicode Database on MySQL?

I am not going to cover installing, I have done that here:
How to install MySQL on FreeBSD 7.2 or on Red Hat 5.4?

So when you open MySQL using the command line MySQL client, you can see what Character Set your server is configured to use with this command:

show variables like 'character_set_server';

Often the default is Latin-1. I wish UTF-8 was the default but it is not.

You can see the language your database is created with by using this command:

show create database dbname

Again, usually the default is Latin-1 and again, I wish the default were UTF-8 but it is not.

So how do I make my MySQL database UTF-8?
How do I make UTF-8 the default?

I am going to find out…

Ok, so I have MySQL installed on two different platforms:
FreeBSD 7.2 x64.
Red Hat 5.4 x64.

My question are these:
What level do you set the Unicode setting at? Install instance, database, or column type.

MySQL – Looks like it can be configured globally in the my.cnf or it can be database specific.

To configure globally

Add the following to the my.cnf file:

[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

Note: There are other options for collation besides utf8_general_ci such as utf8_unicode_ci. See this article:
http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

Do I have to create the database in a special way?

Not if you configure the setting globally. However, if you don’t configure unicode support globally then yes you have to create your database in a specific way.

I found this post that is for an applications that uses a MySQL Unicode database. I don’t care about the application, just the MySQL data.
http://dev.mysql.com/doc/refman/5.1/en/create-database.html
http://dev.mysql.com/doc/refman/5.1/en/charset-applications.html

So the syntax will be:

CREATE DATABASE mydb   DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

Do I have to compile differently to get unicode support?

I didn’t have to recompile on either FreeBSD or Red Hat.

Is there differences for each platform?

Slight differences.

FreeBSD

FreeBSD has the MySQL client as a dependency so it gets installed with the server with out any extra work.

The Database folder is /var/db/mysql.

For the global configuration there is not a my.cnf file created by default.

FreeBSD has example my.cnf files located here:

/usr/local/share/mysql
/usr/local/share/mysql/my-huge.cnf
/usr/local/share/mysql/my-innodb-heavy-4G.cnf
/usr/local/share/mysql/my-large.cnf
/usr/local/share/mysql/my-medium.cnf
/usr/local/share/mysql/my-small.cnf

You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/local/share/mysql/my-medium.cnf /var/db/mysql/my.cnf
chown mysql:mysql /var/db/mysql/my.cnf
cat << EOF >> /var/db/mysql/my.cnf
[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF

Red Hat

Red Hat does not have the MySQL client installed with the server, you have to download a separate RPM and install it. But it is really easy. Download both RPMs and install them.

The Database folder is /var/lib/mysql.

For the global configuration there is not a my.cnf file created by default.

Red Hat has example my.cnf files located here:

/usr/share/mysql
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf

Same as FreeBSD, there isn’t one used by default and you have to copy one and use it.
You can create your own my.cnf or you can copy one of the examples.

In order to get the my.cnf to work, you should copy it and change the owner and add the [mysqld] settings.

#
#
#
?
?
?
?
?
?
?
?
#
cp /usr/share/mysql/my-medium.cnf /var/lib/mysql/my.cnf
chown mysql:mysql /var/lib/mysql/my.cnf
cat << EOF >> /var/lib/mysql/my.cnf
[mysqld]
init_connect=’SET collation_connection = utf8_general_ci’
init_connect=’SET NAMES utf8′
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
EOF

Copyright ® Rhyous.com – Linking to this article is allowed without permission and as many as ten lines of this article can be used along with this link. Any other use of this article is allowed only by permission of Rhyous.com.

How to execute SQL statement that has a single quote in C# or insert a row with a value that has a quote?

Imagine you have a query such as the following:

SELECT * FROM User WHERE LastName='O'Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo'conner','joconner@somedomain.tld')

Well, that is obviously not going to work, because the apostrophe or single quote in the name O’Conner is going to break the query syntax.

You have to have two single quotes to use a quote.

SELECT * FROM User WHERE LastName='O''Conner'

INSERT INTO User (FirstName, LastName, UserName, Email) VALUES ('John','O'Conner','jo''conner','joconner@somedomain.tld')

Ok, so there are two ways to make sure you have two quotes in C#:

  1. You manage the query string yourself.
  2. You use a DataTable and let it manage the query string for you.

Managing the query string yourself

Ok, the answer is simple. You need two single quotes next to each other.

Now, when you have single string, this is easy to do. You need to replace each instance of a single quote with two single quotes using this function which already exists for you:

string.replace(string inStringToBeReplaced, string inNewString)

Here is an example of doing it wrong, then fixing it. Step through this in a debugger.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email     = "joconner@domain.tld";

            // Both these queries are broken because of the space.
            string strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            string strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" +
                        "'" + FirstName + "'," +
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";

            // This will actually break your query too, because it will replace valid single quotes
            // with two single quotes.  You need to do this on the actually data strings.
            strQuery1 = strQuery1.Replace("'", "''"); //
            strQuery2 = strQuery1.Replace("'", "''");

            // Replace any intance of a single quote with two single quotes, ''.
            // IMPORTANT: Typing two single quotes ('') is not the same as a double quote (").
            FirstName = FirstName.Replace("'", "''");
            LastName = LastName.Replace("'", "''");
            UserName = UserName.Replace("'", "''");
            Email = Email.Replace("'", "''");

            // Both these queries are working now;
            strQuery1 = "SELECT * FROM User WHERE LastName='" + LastName + "'";
            strQuery2 = "INSERT INTO User (FirstName, LastName, UserName, Email) VALUES (" +
                        "'" + FirstName + "'," +
                        "'" + LastName + "'," +
                        "'" + UserName + "'," +
                        "'" + Email + "')";
        }
    }
}

Using a DataTable to manage this for you automagically

This actually looks like more work at first, but really when handling a lot of data, it is much more easy to code using DataTables and DataRows.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SingleQuoteInSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            string FirstName  = "John";
            string LastName  = "O'Conner";
            string UserName  = "joconner";
            string Email        = "joconner@domain.tld";

            // Create the connection
            string mConnectionString = "Data Source=ServerName; user id=UserName; password=pw; Initial Catalog=DatabaseName;";
            SqlConnection mSqlConnection = new SqlConnection(mConnectionString);

            // Create a data adapter, this is what does the magic.
            String mQueryForSqlDataAdapter = "Select * from TableName";
            SqlDataAdapter tmpSqlDataAdapter;
            SqlCommandBuilder tmpSqlCommandBuilder;
            DataTable tmpDataTable = new DataTable();
            tmpSqlDataAdapter = new SqlDataAdapter(mQueryForSqlDataAdapter, mSqlConnection);

            // Use the SqlDataAdapter to create a table with the right schema but no data
            tmpDataTable = tmpSqlDataAdapter.FillSchema(tmpDataTable, SchemaType.Mapped);

            // Create a SqlCommandBuilder
            tmpSqlCommandBuilder = new SqlCommandBuilder(tmpSqlDataAdapter);

            // Create a DataRow and populate it
            DataRow row = tmpDataTable.NewRow();
            row["FirstName"] = FirstName;
            row["LastName"] = LastName;
            row["UserName"] = UserName;
            row["Email"] = Email;

            // Add this row to the DataTable
            tmpDataTable.Rows.Add(row);

            // Write this to the database
            tmpSqlDataAdapter.Update(tmpDataTable);
        }
    }
}

Notice we didn’t have to do a string replace of ‘ for ”.

How to restart the AUTOINCREMENT number for a table in Microsoft SQL 2008?

Ok, so I am in the middle of developing a database tool and so I populate a bunch of data, (by adding a bunch of rows), to a column that is AUTOINCREMENT.

So I have 3725 rows for a feature that is working. Now I am developing other features and debuggin them and so I want to reset the database to the same point it was before I started debugging.

So I am deleting all the rows above 3725, however my next AUTOINCREMENT number continues to go up.

I have this handy little SQL statement that should fix that:

To set the table back to 0.

DBCC CHECKIDENT (MyTable, RESEED, 0)

Now, if I set the table back to 0 that can be a problem if you still have rows in it.

The next row you try to insert will give you an error.

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_Person’. Cannot insert duplicate key in object ‘dbo.Person’.
The statement has been terminated.

So if you have rows, try this:

DECLARE @size int
SET @size=(SELECT COUNT(*) From MyTable)
DBCC CHECKIDENT (MyTable, RESEED, @size)

Happy day, it works!!

Equivalent of mysqldump for Microsoft SQL Server 2008

There is a Database Publishing Wizard 1.1 you can download that may work for SQL Server 2005, but didn’t work for me with SQL Server 2008. However,Database Publishing Wizard 1.3 is installed with Visual Studio 2008 but I cannot find a separate download. This tool gets you the schema and data and everything but the “drop and create database” script.

So I think you need Visual Studio 2008 for this for SQL Server 2008 to get it. I am not sure why I cannot find it separately. Maybe Microsoft has a reason.

Step 1 – In Visual Studio 2008, go to Tools | Connect to Database and connect to a MS SQL database.

Under the Server Explorer window, the connection now appears.

Step 2 – Expand Data Connections.

Step 3 – Right-click on the connection and choose Publish to provider.

Step 4 – Click Next.

Step 5 – Choose the database.

Step 6 – Click Next.

Step 7 – Select the publishing options (such as to export the schema and data or just the schema).

Step 8 – Choose a file.

Step 9 – Click Finish.

Step 10 – The one thing this is missing is the script to drop and create the database. You can easily get this from Microsoft SQL Server Management Studio 2008 (there is a free Express version if you don’t have it). Just connect to the database, right-click on the database and choose Script Database as | Drop And Create to | Clipboard. Now past this text to the top of your file you just created.

What is the Microsoft SQL equivalent to MySQL's "Limit" feature in a SQL query?

Here is a MySQL Query

SELECT * FROM Table LIMIT 10

Here is a Microsoft SQL Query to perform the same

SELECT TOP10 * FROM Table