Database
Database
- Agile database practice [1]
- Terminology
- Collation @ msdn.microsoft.com
- Cursors: Rather than executing a whole query at once, it is possible to set up a cursor that encapsulates the query, and then read the query result a few rows at a time. One reason for doing this is to avoid memory overrun when the result contains a large number of rows. [2]
- Relational database Management System[3]
- Indexes & Hints
- Index
- Hints - Hints are options or strategies specified for enforcement by the SQL Server query processor on SELECT, INSERT, UPDATE, or DELETE statements. The hints override any execution plan the query optimizer might select for a query.
- Query Hints - http://msdn.microsoft.com/en-us/library/ms181714.aspx
- Index vs Index Hint http://www.doens.be/2010/10/index-vs-index-hint/
- Trigger - A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be defined to execute either before or after any INSERT, UPDATE, orDELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.[4]
- Wiki
- Oracle Server Manual
- MySQL - Syntax [5]
- PostgreSQL- Syntax [6]
- Stored Procedures
- Transactions
- Mapping Objects to Relational Databases @ agiledata.org
- Converting Charsets [7]
Persistence
- Eclipse Tutorial Videos on Persistence
- XML/Object Binding and Object Persistence = XStream + XBird (XML database system]
- data persistence with JDO
- java world jdo
- JDBC
- JDBC @ http://en.wikipedia.org/wiki/JDBC
- Relational database Management System @ http://en.wikipedia.org/wiki/Relational_database_management_system
- JDBC Driver @ http://en.wikipedia.org/wiki/JDBC_driver
- Drivers table http://devapp.sun.com/product/jdbc/drivers
JDBC
- One issue is that JDBC does not do any client-side SQL parsing or syntax checking. SQL statements are passed off transparently to the database, whether or not they are valid. Therefore, if the SQL is valid on one vendor's database, but invalid on another vendor's database the implementation won't know until the actual connection is made and the SQL is sent across. Sun is attempting to deal with this problem, and there may be some provisions made to correct this, either in later versions of JDBC or in a different standard.
PostgreSQL and MySQL
- Postgre
- Installation: [8]
- Tutorial
- postgre connection
- String literals and string functions in postgreSQL [9]
- postgre gotchas [10]
- Pattern Matching [http://pgsqld.active-venture.com/functions-matching.html
- Character set support -
- The character set support in PostgreSQL allows you to store text in a variety of character sets (also called encodings), including single-byte character sets such as the ISO 8859 series and multiple-byte character sets such as EUC (Extended Unix Code), UTF-8, and Mule internal code. All supported character sets can be used transparently by clients, but a few are not supported for use within the server (that is, as a server-side encoding). The default character set is selected while initializing your PostgreSQL database cluster using initdb. It can be overridden when you create a database, so you can have multiple databases each with a different character set. An important restriction, however, is that each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) Note! Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10. [11]
- BLOBs [12]
- MySQL
- Syntax
- Tutorial
- Mysql Connector
- Retrieving AUTO_INCREMENT Column Values [13]
- LikeEscape:[14]
- Because MySQL uses C escape syntax in strings (for example, “\n” to represent a newline character), you must double any “\” that you use in LIKE strings. For example, to search for “\n”, specify it as “\\n”. To search for “\”, specify it as “\\\\”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.
- Exception: At the end of the pattern string, backslash can be specified as “\\”. At the end of the string, backslash stands for itself because there is nothing following to escape. Suppose that a table contains the following values:
mysql> SELECT filename FROM t1;
filename : C: | C:\ | C:\Programs | C:\Programs\
- To test for values that end with backslash, you can match the values using either of the following patterns:
mysql> SELECT filename, filename LIKE '%\\' FROM t1;
- filename | filename LIKE '%\\' |
- C: | 0 |
- C:\ | 1 |
- C:\Programs | 0 |
- C:\Programs\ | 1 |
mysql> SELECT filename, filename LIKE '%\\\\' FROM t1; ( Will have the same result as above)
- The current versions are MySQL 5.1 and PostgreSQL 8.4.
- PostgreSQL is a unified database server with a single storage engine. MySQL has two layers, an upper SQL layer and a set of storage engines. [15]
- MySQL began development with a focus on speed while PostgreSQL began development with a focus on features and standards. Thus, MySQL was often regarded as the faster of the two.
- Both PostgreSQL and MySQL support Not-Null, Unique, Primary Key and Foreign Key constraints. However MySQL silently ignores the CHECK constraint
- MySQL supports stored procedures, per se; PostgreSQL supports stored functions, which are in practice very similar.
- Trigger - Both PostgreSQL and MySQL support triggers. A PostgreSQL trigger can execute any user-defined function from any of its procedural languages, not just PL/pgsql.MySQL triggers are activated by SQL statements only. They are not activated by changes in tables made by APIs that do not transmit SQL statements to the MySQL Server; in particular, they are not activated by updates made using the NDB API. PostgreSQL has always been strict about making sure data is valid before allowing it into the database, and there is no way for a client to bypass those checks.
- DataTypes - PostgreSQL does not have an unsigned integer data type, but it has a much richer data type support in several aspects: standards compliance, the logically fundamental data type BOOLEAN, user-defined data types mechanism, built-in and contributed data types. PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any built-in or user-defined base type, enum type, or composite type can be created. Arrays of domains are not yet supported. MySQL does not have network IP address data types that PostgreSQL has but does provide INET_ATON() and INET_NTOA() functions to convert IPv4 addresses to and from easily stored integers. Postgres does very good job supporting referential integrity, has transactions and rollbacks, foreign keys ON DELETE CASCADE and ON UPDATE CASCADE.
- Security - MySQL has exceptionally good fine-grained access control. You can GRANT and REVOKE whatever rights you want, based on user name, table name and client host name.
- Alter table - Postgres supports ALTER TABLE to some extent. You can ADD COLUMN, RENAME COLUMN and RENAME TABLE. MySQL has all options in ALTER TABLE - you can ADD column, DROP it, RENAME or CHANGE its type on the fly - very good feature for busy servers, when you don't want to lock the entire database to dump it, change definition and reload it back.
- Diagnostic Log - By default, PostgreSQL logs to stderr, meaning that it's highly installation specific where the dianostic information is put; on this author's system, the default ends up in /var/lib/pgsql/pgstartup.log. The default can be set to something more reasonable (such as syslog on unix, eventlog on Windows) by adjusting thelog_destination configuration parameter.
- Automatic key generation -
- PostgreSQL doesn't support the standard's IDENTITY attribute. PostgreSQL's best offering for a column with auto-generated values is to declare a column of 'type' SERIAL:
CREATE TABLE tablename (
tablename_id SERIAL,
...
)- MySQL doesn't support the standard's IDENTITY attribute. As an alternative, an integer column may be assigned the non-standard AUTO_INCREMENT attribute:
CREATE TABLE tablename (
tablename_id INTEGER AUTO_INCREMENT PRIMARY KEY,
...
)