Database Design. /#generator. 10 Essential SQL Tips for Developers | Nettuts+ SQL is yet another essential language for developers wishing to create data-driven websites. However, many developers are unfamiliar with various aspects of SQL; so in this article, we'll analyze ten essential tips. 1. Use The Right Language Web developers often have a plethora of languages at their disposal.
It is crucial for developers to use the proper language for the job. Let's review the following code. In the first example, the developer is selecting all columns and all rows from the customer table. When you are writing code, make sure that it works efficiently. Too many developers are satisfied with code that performs adequately on 100 rows of data, with little thought ahead to when the database will have 10,000 rows. 2.
Databases store valuable information. In case you aren't convinced about the seriousness of database security, these two articles should help drive the point home: Let's review another example using pseudo-code. How Can You Write Secure Code? 3. Self Joins 4. 5. 6. 7. CodeProject: Improving the performance of queries using SQL Server Part 2. Free source code and programming help. Introduction In the previous article: Improving the performance of queries using SQL Server: Part 1, we discussed using SQL Profiler to identify queries that need looking at, to see if performance can be improved. We learned how to set the criteria that allow us to focus on the queries that are most likely to give us performance gains if we fix them.
In this article, we are going to look at how to take the information we gleaned from the SQL Profiler and use it to enhance our queries so that they are more performant. Starting out Before we dive into looking at indexes and execution plans, we need to consider whether or not a query is well written or not. This query is a prime example of a badly written query that needs a serious kick up the backside before we even consider the plan: As we can see, the big problem with this query is that it is just badly written.
Right, the first thing that we need to do then is to rewrite the query so that it cuts out the unnecessary work that it is doing. ConnectionStrings.com - Forgot that connection string? You will find it right here! Database Design - Introduction. Also available on tomjewett.com: color tutorial, demo application, and video; for Web accessibility resources and consulting, please see The Enabled Web. This third edition of dbDesign is a general update, both to meet legal requirements for U.S. “Section 508” accessibility and to bring the code into compliance with the latest World Wide Web Consortium standards.
In the process, I've tried to make the SQL examples as generic as possible, although you will still have to consult the documentation for your own database system. Graphics no longer require the SVG plugin; large-image and text-only views of each graphic are provided for all readers; the menu is now arranged by topic areas; and the print version (minus left-side navigation) is done automatically by a style sheet.
The second edition was largely motivated by the very helpful comments of Prof. The original site was the outgrowth of a previous book project, Practical Relational Database Design, by Wayne Dick and Tom Jewett. DbUnit - Database Testing. Database Testing Richard Dallaway notes about database unit testing inspired me the realization of the DbUnit framework. I think this is a very good text about this subject. Hopefully he gave me the permission to present excerpts from his notes here. The original text is much longer and I strongly suggest you to read it as well. See resources for reference to it. - Manuel Laflamme Unit testing database These are my notes on how I've gone about unit testing database functionality.
The problem is this: you have a SQL database, some stored procedures, and a layer of code sitting between your application and the database. Why bother? I'm guessing some, if not a lot, of database development goes like this: set up database, write code to access database, run code, do a SELECT to see if the records showed up in the database. The problem with visual inspection is this: you don't do it often, and you don't check everything every time. You need [multiple] databases The production database. Factoring a database interface class: the "DBIGenerator" class into action. I firmly believe that dynamically building DB interfaces will be best understood by example. Bearing that concept in mind, let's build an example that implements the "DBIGenerator" class and quickly creates a "User" DB interface class, which behaves as a centralized mechanism to access rows in a sample "users" table.
Here's the corresponding code: // include MySQLConnector wrapping classrequire_once 'mysqlclass.php';// include the "DBIGenerator" classrequire_once 'dbigeneratorclass.php';// instantiate a new MySQLConnector object and connect to MySQL$db=&new MySQLConnector(array('host'=>'host','user'=>'user' ,'password'=>'password','database'=>'databasename'));// set class properties-accessors and modifiers$options=array('id','firstname','lastname','email');// instantiate a DBIGenerator object$gn=&new DBIGenerator('User','DBICLASSES/',$options);// create User class$gn->create();// get $user objectif(!
The above example demonstrates how simple it is to create a DB interface class. Summary. Identifying and non-identifying relationships. An identifying relationship means that the child table cannot be uniquely identified without the parent. For example, you have this situation in the intersection table used to resolve a many-to-many relationship where the intersecting table's Primary Key is a composite of the left and right (parents) table's Primary Keys.
Example... Account (AccountID, AccountNum, AccountTypeID) PersonAccount (AccountID, PersonID, Balance) Person(PersonID, Name) The Account to PersonAccount relationship and the Person to PersonAccount relationship are identifying because the child row (PersonAccount) cannot exist without having been defined in the parent (Account or Person). In other words: there is no personaccount when there is no Person or when there is no Account. A non-identifying relationship is one where the child can be identified independently of the parent ( Account - AccountType) Example... Resources LearnDeZign for Databases: Learn more about DeZign for Databases. Secure Your Forms With Form Keys - Nettuts+ Security is a hot topic. Ensuring that your websites are secure is extremely important for any web application. In fact, I spend 70% of my time securing my applications. One of the most important things we must secure are forms. Today, we are going to review a method to prevent XSS (Cross-site scripting) and Cross-site request forgery on forms.
Why? POST data can be sent from one website to another. A user, logged into your website, visits another website during his session. We also must protect our pages against attacks using cURL How Do We Fix This? With form keys! What We Must Do: Add a form key to every form.Store the form key in a session.Validate the form key after a form submit. Step 1: A Simple Form First we need a simple form for demonstration purposes. Now we have a simple XHTML page with a login form. Step 2: Creating a Class We are going to create a PHP class for our form keys. Above, you see a class with three parts: two variables and a function.
And that's all! Step 4: Validating. SQL Injection Attacks by Example. A customer asked that we check out his intranet site, which was used by the company's employees and customers. This was part of a larger security review, and though we'd not actually used SQL injection to penetrate a network before, we were pretty familiar with the general concepts. We were completely successful in this engagement, and wanted to recount the steps taken as an illustration. "SQL Injection" is subset of the an unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises. We'll note that this was a somewhat winding road with more than one wrong turn, and others with more experience will certainly have different -- and better -- approaches.
But the fact that we were successful does suggest that we were not entirely misguided. SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK. Relational databases, like Microsoft’s SQL Server, use locks to prevent multiple users from making conflicting modifications to a set of data. When a set of data is locked by a user, no other users can modify that same set of data until the first user finishes modifying the data and relinquishes the lock. There are exceptions, but let’s not go there. Some databases, SQL Server included, use locks to prevent users from seeing uncommitted modifications. In these systems if UserA is modifying some set of data, then UserB and all the rest of the users must wait until UserA is done modifying that data before they can even get a shot at reading the data, let alone modifying it.
Databases place locks at all levels of their physical hierarchies: rows, pages (typically a few KB of rows), extents (typically a few pages), entire tables, and entire databases. Lock Contention Described Unfortunately, lock escalation introduces and amplifies a whole new problem: deadlocks. For example: Continues… The Datomic Information Model. Datomic is a new database designed as a composition of simple services.
It strives to strike a balance between the capabilities of the traditional RDBMS and the elastic scalability of the new generation of redundant distributed storage systems. Motivations Datomic seeks to accomplish the following goals: Provide for a sound information model, eschewing update-in-place Leverage redundant, scalable storage systems Provide ACID transactions and consistency Enable declarative data programming in applications Datomic considers a database to be an information system, where information is a set of facts, and facts are things that have happened. Since one can't change the past, this implies that the databaseaccumulates facts, rather than updates places, and that while the past may be forgotten, it is immutable.
Traditional databases (and many new ones!) It's interesting to consider why keeping active history is even in question. Structure and Representation A Datom has the following components: Viktor's Home Page: Oracle Cheat Sheet. Details Created on Monday, 25 July 2005 20:00 I did a fair bit of work recently with Oracle.
Being a regular MySQL user, I found some Oracle-isms a little strange, so I put together a cheat sheet: 1. I found some of these at a few others at 2. The easiest way to add Oracle Single Sign On authentication to a Web page/application just uploaded to an Oracle 9i application server is to hand-edit mod_osso.conf, inserting the following lines: <Location /base-URL> require valid-user AuthType Basic </Location> See also To administer SSO accounts, try SELECT user_name FROM orasso.wwsec_person$; If you receive the following error: then poke around in the Apache/Apache subdirectory in your Oracle9i AS installation to see if you can find a log file explaining what happens. 3. 4. 5.