Generating and using Schema files :: Schema management and migrations :: Core Console Applications :: The Manual :: 1.2 Collection

Generating and using Schema files

A generated schema file allows you to easily transport a database agnostic schema. You can generate a schema file of your database using:
$ cake schema generate
This will generate a schema.php file in you app/config/sql directory.
The schema shell will only process tables for which there are models defined. To force the schema shell to process all the tables, you must add the -f option in the command line.

To later rebuild the database schema from your previously made schema.php file run:
$ cake schema run create
This will drop and create the tables based on the contents of the schema.php.
Schema files can also be used to generate sql dump files. To generate a sql file containing the CREATE TABLE statements, run:

$ cake schema dump filename.sql

Where filename.sql is the desired filename for the sql dump. If you omit filename.sql the sql dump will be output to the console but not written to a file.

Configuring Solr with MySQL Database

Configuring with MySQL Database

1. Download jdbc driver for mysql (mysql-connector-java-5.1.12-bin.jar) put it in the “lib” folder , this is the one which is responsible for communication between MySQL db and apache Solr

2. Open data-config.xml in your conf folder under example directory (example->conf->data-config.xml) in any text editor
3.Add XML as below

4. Data source is the tag which holds the data about the connecter (which database ,user, password, url, driver etc.,), we can have many data source in a single file and use the name of data source to connect
5. Change the “Query” in the “entity” tag as you want to create index it can also use joins and db functions in it

6. There is another set of tags called “Field” which used to specify what are the column which are returned by the query is going to be used (take care in giving values to name attribute),”Column” attribute is used to map the column name to the name attribute

7. Open “schema.xml” in the same folder search for “fields” tag and remove the content of it and add your columns names as picture below

8. Tag “copyField” use to copy more than one column into a single string, In the above example we are using “text “ to hold all the values
9. Tag “uniqueKey” is used to identify the index unqiue

MySQL Storage Engine Comparision

 MyISAM Storage Engine Features(default storage engine)

Storage limits	256TB	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial datatype support	Yes	Geospatial indexing support	Yes
B-tree indexes	Yes	Hash indexes	No	Full-text search indexes	Yes
Clustered indexes	No	Data caches	No	Index caches	Yes
Compressed data	Yes[a]	Encrypted data[b]	Yes	Cluster database support	No
Replication support[c]	Yes	Foreign key support	No	Backup / point-in-time recovery[d]	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes

InnoDB Storage Engine Features

Storage limits	64TB	Transactions	Yes	Locking granularity	Row
MVCC	Yes	Geospatial datatype support	Yes	Geospatial indexing support	No
B-tree indexes	Yes	Hash indexes	No	Full-text search indexes	No
Clustered indexes	Yes	Data caches	Yes	Index caches	Yes
Compressed data	Yes[a]	Encrypted data[b]	Yes	Cluster database support	No
Replication support[c]	Yes	Foreign key support	Yes	Backup / point-in-time recovery[d]	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes

 MEMORY Storage Engine Features

The MEMORY storage engine creates tables with contents that are stored in memory. Formerly, these were known as HEAP tables. MEMORY is the preferred term, although HEAP remains supported for backward compatibility.

Storage limits	RAM	Transactions	No	Locking granularity	Table
MVCC	No	Geospatial datatype support	No	Geospatial indexing support	No
B-tree indexes	Yes	Hash indexes	Yes	Full-text search indexes	No
Clustered indexes	No	Data caches	N/A	Index caches	N/A
Compressed data	No	Encrypted data[a]	Yes	Cluster database support	No
Replication support[b]	Yes	Foreign key support	No	Backup / point-in-time recovery[c]	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes

ARCHIVE Storage Engine Features

The ARCHIVE storage engine is used for storing large amounts of data without indexes in a very small footprint.

Storage limits	None	Transactions	No	Locking granularity	Row
MVCC	No	Geospatial datatype support	Yes	Geospatial indexing support	No
B-tree indexes	No	Hash indexes	No	Full-text search indexes	No
Clustered indexes	No	Data caches	No	Index caches	No
Compressed data	Yes	Encrypted data[a]	Yes	Cluster database support	No
Replication support[b]	Yes	Foreign key support	No	Backup / point-in-time recovery[c]	Yes
Query cache support	Yes	Update statistics for data dictionary	Yes

Others

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it. 

The CSV storage engine stores data in text files using comma-separated values format.

The FEDERATED storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables.

The EXAMPLE storage engine is a stub engine that does nothing. Its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge MyISAM tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order.

The IBMDB2I storage engine is designed as a fully featured transaction-capable storage engine that enables MySQL to store its data in DB2 tables running on IBM i. With the IBMDB2I storage engine, data can be shared between MySQL applications and applications coded for native DB2 for i interfaces.