Tag: MySQL

Posted on

ORM Designer 2

I thought I’ll help plug a tool that looks to be quite useful and that is basing their development on direct community input, I’ve made my suggestions hope you do too 🙂

Martin Kulhavy writes; ORM Designer. It’s primary target is to create ORM Definitions fast and easily with the help of visual diagram instead of manually written text definitions.
Most distinctive features allows you to:
  • Save time and work 4 times faster.
  • Repeatedly export your definition files.
  • Import and visualize existing project schemas.
  • Eliminate errors and the need to look for typos.
Download our 14-day trial version and let us know what you think. If you write a review, we will give you 20% discount off the price of ORM Designer. Either way, let us know, what you think about it. If you found it helpful, and if not, why not. Any feedback is incredibly valuable to us, as we strive to make our product the best we possibly can.
Posted on

Zend Server 5.6 is out

Kevin Schroeder writes; Zend Server 5.6 is out.  I’m actually pretty stoked about this release.  Here’s a few reasons why.

  1. Zend Server CE now contains the full Zend Server stack.  Why do I find this exciting?  Because the upgrade path from CE to the full version is stupid easy.  That means you can try out all of the features in Zend Server and if you don’t want them, just let the license expire.  I call it a gateway drug.  Seriously.  Get Studio or PDT and try the deployment feature.  If you’re not using it you’ll ask yourself “why am I not doing this?”
  2. All of you Mac users can finally stop bitching and complaining about the lack of Mac support.  We don’t (and probably won’t) support Mac for production (does anyone besides Apple actually use their server products in production?) but for dev work you now get the full stack on your Mac.  This is not GA yet but will be later on.  If you want a preview release you can go to http://forums.zend.com/viewtopic.php?f=8&t=26873 .
  3. Fault tolerance in the Job Queue through MySQL clustering.  In clustered environments job queue information has moved from local storage to remote MySQL storage.  So you can have a single MySQL server sitting there, a clustered setup or RDS or some other cloud-based DB, it doesn’t matter.  If it can be accessed using the MySQL drivers then it can be used by the new Job Queue.

via ESchrade – Pure PHP Goodness.

Posted on

About using UTF-8 fields in MySQL

Joshua Thijssen writes; I sometimes hear: “make everything utf-8 in your database, and all will be fine”. This so-called advice could not be further from the truth. Indeed, it will take care of internationalization and code-page problems when you use UTF-8, but it comes with a price, which may be too high for you to pay, especially if you have never realized it’s there..Indexing is everything… or at least.. good indexing makes or breaks your database. The fact remains: the smaller your indexes, the more index records can be loaded into memory and the faster the searches will be. So using small indexes pays off. Period. But what has got this to do with UTF-8?

Read the entire article here>>  A day in the life of…

Posted on

Akrabat_Db_Schema_Manager: table prefix support

Rob Allen posts; I’ve updated Akrabat_Db_Schema_Manager so that it now supports table prefixes.

It uses the application.ini key of resources.db.table_prefix as I couldn’t think of a better one 🙂 and then uses that for the schema_version table’s name and also makes it available in your change objects.

For example, if application.ini contains resources.db.table_prefix = “myapp”, then the manager will create the table myapp_schema_version to store the current version of the schema. In your change classes, you can then do this:

001-Users.php:

class Users extends Akrabat_Db_Schema_AbstractChange
{
function up()
{
$tableName = $this->_tablePrefix . 'users';
$sql = "
CREATE TABLE IF NOT EXISTS $tableName (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(50) NOT NULL,
password varchar(75) NOT NULL,
role varchar(200) NOT NULL DEFAULT 'user',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;";
$this->_db->query($sql);

$data = array();
$data['username'] = 'admin';
$data['password'] = sha1('password');
$data['role'] = 'admin';
$this->_db->insert($tableName, $data);
}

function down()
{
$tableName = $this->_tablePrefix . 'users';
$sql= "DROP TABLE IF EXISTS $tableName";
$this->_db->query($sql);
}

}

which will create a table called myapp_users. Note that you are responsible for using the prefix property as the change classes cannot enforce what you do within the up() and down() methods. It also follows that you’ll have to ensure that your models also use the correct prefix.

I have also made a change to the provider (Akrabat_Tool_DatabaseSchemaProvider) so that it loads the correct application.ini file based on the data in the project’s profile. This shouldn’t affect anyone using Akrabat_Db_Schema_Manager, except that we no longer define APPLICATION_ENV and APPLICATION_PATH for you.

Enjoy!

via Rob Allen’s DevNotes.

Posted on

MySQL does support preparing some DDL statements, However…

Bill Karwin gives some insight into some work arounds when creating functions, triggers and procedures using Zend Framework;

MySQL does support preparing some DDL statements, even in older versions. See http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html
for lists of what statements can be prepared.

However, some DDL statements are still not supported as prepared statements, for example CREATE FUNCTION, CREATE TRIGGER, CREATE PROCEDURE.

DELIMITER is not supported as an executable statement at all, whether you prepare it or whether you do an immediate execute. Statements like DELIMITER, PAGER, SOURCE, CONNECT, and QUIT and others are builtins of the mysql command-line client. These commands are not recognized by the MySQL server.

You need to set the DELIMITER only if you’re running the CREATE FUNCTION statement in an SQL script. The default API for SQL statements does not support multiple statements per call. So you don’t have to delimit statements and you don’t have to change the delimiter.

So Nils’s solution should be the following:

1. Don’t worry about DELIMITER, you don’t need it.

2. You must DROP and CREATE in two separate statements.

3. Bypass the default ZF query method. Go directly to the
PDO::query() method when you execute a statement that isn’t preparable. You can access the PDO object using the getConnection() method of your ZF Db adapter:

$db->getConnection()->query( $drop_function_statement );
$db->getConnection()->query( $create_function_statement );

Regards,
Bill Karwin

Posted on

MySQL :: Managing Hierarchical Data in MySQL

Mike Hillyer wrote a very good article on Managing Hierarchical data in MySQL, defenitely worth a read;

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

via MySQL :: Managing Hierarchical Data in MySQL.

Posted on

Migrating MySQL latin1 to utf8 – Preparation

This entry is part [part not set] of 4 in the series Migrating MySQL latin1 to utf8

Before undertaking such migration the first step is a lesson in understanding more about how latin1 and utf8 work and interact in MySQL. latin1 in a common and historical character set used in MySQL. utf8 first available in MySQL Version 4.1 is an encoding supporting multiple bytes and is the system default in MySQL 5.0

via Migrating MySQL latin1 to utf8 – Preparation

Proudly powered by WordPress