![]() |
|
Lors de l'ajout d'un disque dur et de la réinstallation de Windows, j'ai oublié de récupérer mes bases de données MySQL dans le dossier d'EasyPhp. Pour pallier à ce problème, j'ai donc cherché comment déplacer le dossier des bases vers mon dossier contenant l'ensemble des codes sources de mes sites sur mon ancien disque dur.
Pré-requis
Evidemment, il faut que vous ayez installé EasyPhp sur votre machine. Pour ma part, je suis sous la version 1.8, disponible sur le site d'EasyPhp. Il faut également que vous lanciez EasyPhp et que vous stoppiez le serveur MySql, en cliquant sur le bouton "MySql" puis "Arrêter".
1. Déplacer le dossier des bases
Maintenant, ouvrez le dossier d'installation d'EasyPhp, qui est par défaut "C:\Program Files\EasyPHP1-8" pour la version 1.8 bien entendu. Regardez dans le dossier "mysql/data", et vous pourrez constater qu'il contient des sous-dossiers aux noms de vos différentes bases, définies via PhpMyAdmin par exemple. Il s'agira donc de déplacer ce dossier seulement, et non pas tout le dossier mysql. Cela permettra de garder une cohérence en laissant les programmes installés au même endroit. Copiez donc le dossier data vers le dossier de destination que vous souhaitez. Pour ma part, je l'ai installé dans le dossier "D:\Sites Internet\MySQL".
2. Modifier la configuration MySql
EasyPhp étant toujours en cours d'exécution, vous pouvez facilement accéder à la configuration de MySql via la fenêtre d'EasyPhp et l'icone située à la gauche du bouton "Apache". Allez dans "Configuration" puis cliquez sur "MySql".
Cela va alors vous ouvrir le fichier my.ini. Les lignes précédées d'un point-virgule sont des commentaires, les autres sont interprétées pour la configuration. Les deux variables qui définissent des chemins d'accès sont datadir et basedir, qui sont respectivement les chemins d'emplacements du dossier "data" contenant les bases et du dossier "mysql" contenant l'application MySql. C'est donc la première variable que nous allons modifier. Je vous conseille néanmoins de ne pas supprimer la valeur originale, mais de la placer en commentaire (en mettant un point-virgule en début de ligne). Voici donc la valeur que j'ai affecté à cette variable, qui correspond au nouvel emplacement de mon dossier data : datadir=D:/Sites Internet/MySql/data. A vous d'adapter cette variable à votre chemin... Vous pouvez ensuite sauvegarder le fichier en faisant CTRL+S.
3. Relancer le serveur MySql
Si tout se passe bien et que vous avez correctement renseigné le chemin de votre nouveau dossier, le serveur MySql devrait se relancer normalement en cliquant sur "MySql" puis "Démarrer" via la fenêtre d'EasyPhp.
Vous pourrez ensuite vous connecter à PhpMyAdmin et vérifier que la modification a bien été effectuée, par exemple en créant une base "Test" et en vérifiant qu'un dossier "Test" a bien été créé dans le nouvel emplacement du dossier data.
MySQL, EasyPhp
Fonction PHP pour ajouter un filigrane sur une image
 Une fonction plutôt longue mais pas compliquée ..
Elle permet tout simplement d'ajouter un filigrane sur une image avec un pourcentage d'opacité, et de redimensionner cette image. Finalement ça revient à superposer 2 images puis à redimensionner le tout .. rien de bien exceptionnel mais ça peut servir ...
Voici donc cette fonction :
<?php
// $img_src ==> Adresse de l'image source
// $img_finale ==> Adresse et nom de l'image finale
// $filigrane ==> Adresse du filigrane au format .gif
// Pour utiliser un format .jpeg
// modifier $image_c=imagecreatefromgif($filigrane);
// par $image_c=imagecreatefromjpeg($filigrane);
//
//$taillelarg ==> Largeur maximum de l'image après redimensionnement
// Il ne se passe rien si l'image est inférieure
//$taillehaut ==> Hauteur maximum de l'image après redimensionnement
// Il ne se passe rien si l'image est inférieure
//
//$opacity = 80 par defaut
function filigrane($img_src,$img_finale,$filigrane,$taillelarg,$taillehaut, $opacity = 80){
//largueur de l'affichage
$taille_larg=$taillelarg;
//hauteur de l'affichage
$taille_haut=$taillehaut;
//récuperation de l'extension
$extension = strrchr($img_src,'.');
switch($extension){
case ".gif":
$image_s=imagecreatefromgif($img_src) or die("Erreur à l'ouverture");break;
case".png":
$image_s=imagecreatefrompng($img_src) or die("Erreur à l'ouverture");break;
case ".jpg"||".jpeg"||".jpe"||".JPG"||".JPEG":
$image_s=imagecreatefromjpeg($img_src) or die("Erreur à l'ouverture");break;
}
//récupération des dimensions de l'image principale
$width=imagesx($image_s);
$height=imagesy($image_s);
//redimention de l'image source
if($width>=$height) //visuel horizontal
{
$ratio=max($width/$taille_larg, $height/$taille_haut);
$new_width=$taille_larg;
$new_height=$height/$ratio;
}
else //visuel vertical
{
$ratio=max($width/$taille_larg, $height/$taille_haut);
$new_width=$width/$ratio;
$new_height=$taille_haut;
}
//création de l'image source redimensionnée
$thumb=imagecreatetruecolor($new_width,$new_height);
imagecopyresized($thumb,$image_s,0,0,0,0,$new_width,$new_height,$width,$height);
//dimensions de l'image principale redimensionnée
$width_thumb=imagesx($thumb);
$height_thumb=imagesy($thumb);
//création de l'image copyright
$image_c=imagecreatefromgif($filigrane);
//récupération des dimensions de l'image du copyright
$larg_cop=imagesx($image_c);
$long_cop=imagesy($image_c);
//calcule la position du copyright sur l'axe des abscisses
$x=($width_thumb-$larg_cop)-10;
//calcule la position du copyright sur l'axe des ordonnées
$y=($height_thumb-$long_cop)-10;
//réalisation de la superposition
imagecopymerge($thumb,$image_c, $x, $y, 0, 0, $larg_cop, $long_cop, $opacity);
//sauvegarde de l'image
switch($extension){
case ".gif":
imagegif($thumb,$img_finale) or die ("Erreur de création gif");
break;
case".png":
imagepng($thumb,$img_finale) or die ("Erreur de création png");
break;
case ".jpg"||".jpeg"||".jpe"||".JPG"||".JPEG":
imagejpeg($thumb,$img_finale) or die ("Erreur de création jpeg");
break;
}
//libération de la mémoire
imagedestroy($image_s);
}
?>
php, filigrane
Advertisement
When it comes to design of modern web-applications, Ajax is considered as a standard approach. Interactive solutions for lightboxes, form validation, navigation, search, tooltips and tables are developed using Ajax libraries and nifty Ajax scripts. Ajax is useful and powerful. However, when using Ajax, one should keep in mind its drawbacks in terms of usability and accessibility. With an extensive use of Ajax, you can easily confuse your visitors offering too much control and too many features.
Nevertheless, it’s important to know what’s possible, particularly since you can develop new ideas further, improving the quality of your web applications. Since our last article 80+ AJAX-Solutions For Professional Coding many things have changed — new scripts were introduced, new creative solutions were developed, new robust development kits have been released. They all are supposed to serve a better user experience and provide more comfort for web-developers.
This post presents over 60 new useful Ajax scripts, libraries and solutions which you can use in your future projects. License agreements can change from time to time — please read them carefully before using the script in a commercial web-application.
You might want to consider checking out the following related posts:
- 30 Scripts For Galleries, Slideshows and Lightboxes presents scripts of impressive slideshows, lightboxes and galleries you can use for effective presentations of your images,
- 40+ Tooltips Scripts With AJAX, JavaScript & CSS with handy tooltips scripts for intuitive and well-designed visual clues,
- Data Grids with AJAX, DHTML and JavaScript with free solutions for data grids, developed with AJAX, DHTML and/or JavaScript,
- Powerful CSS-techniques For Effective Coding which features 50 new CSS-techniques, ideas and ready-to-use solutions for effective CSS-coding.
Please notice: the overview presented below is not just a yet-another-one-collection of Ajax-scripts. It’s a collection of really useful ones, the ones you can use in almost every project you’ll be working on.
Useful Ajax Scripts
Mocha UI
Mocha is a web applications user interface library built on the Mootools javascript framework. The Mocha GUI components are made with <canvas> tag graphics.
An Accessible Slider
“Recently we designed and developed an interface that required a slider control, which allows users to choose one or a range of values on a continuum. Values on a slider can represent anything from hours on a clock to the volume on a music player to a complex, proprietary data set. In its simplest form, the slider is displayed as an axis of values with a handle to drag and select a value, or two handles for selecting a range.”
FancyUpload
Swf meets Ajax. An upload widget that allows queued multiple-file upload including progress bars.
Coda Popup Bubbles
“When you move the mouse over the popup, this triggers a mouseout on the image used to trigger the popup being shown. I’ll explain (carefully) how to make sure the effect doesn’t fail in this situation.”
Facebook Style Input Box
The approach to re-create the autocomplete method of adding multiple recipients to messages used on Facebook. “I’d seen it in Facebook before, which has a really decent implementation of this concept (it work well, but it doesn’t respect any modern programming principles; basically, it’s a big tag soup with lots of inline Javascript)”
Rich Text Editor
The Rich Text Editor is a UI control that replaces a standard HTML textarea. It allows for the rich formatting of text content, including common structural treatments like lists, formatting treatments like bold and italic text, and drag-and-drop inclusion and sizing of images. The Rich Text Editor’s Toolbar is extensible via a plugin architecture so that advanced implementations can achieve a high degree of customization. The tool is based upon Yahoo UI Library.
iCarousel
iCarousel is an open source (free) javascript tool for creating carousel like widgets.
Ext JS - JavaScript Library
An extensive JavaScript-Framework with numerous modules and components such as tables, trees, windows, layouts, forms, and tabs. All of them look as if they’ve been used in standard desktop-applications.
Moo Wheel
The purpose of this script is to provide a unique and elegant way to visualize data using Javascript and the <canvas>-object. This type of visualization can be used to display connections between many different objects, be them people, places, things, or otherwise. The script is licensed under an MIT-style license.
Product Slider
This ‘product slider’ is similar to a straight forward gallery, except that there is a slider to navigate the items, i.e. the bit the user controls to view the items.
Taggify Tooltips
This post demonstrates how you can use Taggify widget to enhance your blog with the functionality to show popup tooltips for parts of your images.
Gettyone Search Options Menu
Learn how to implement a Gettyone-like search options menu which display a layer with some search options below the input search field, when an user click on the input field to searching for something.
Moo Canvas
Modern browser support the <canvas> tag to allow 2D command-based drawing. This script provides the third dimension, allowing for browser drawing with pure JavaScript. To use, web developers only need to include a single script tag in their existing web pages.
Relay - Ajax Directory Manager
Relay is an Ajax-powered file management library. It has a multi-user access restriction, allowing the administrator to control user access to uploaded files. Features: drag-n-drop files and folders, dynamic loading file structure, upload progress bar, thumbnail view, including pdf and multiple users & accounts.
GlassBox
GlassBox is a compact Javascript User Interface (UI) library, which use Prototype and Script.aculo.us for some effects. With GlassBox you can build transparent border, colorful layouts and “Flash-like” effects. Take a look at the site itself: you can use the keyboard navigation: Keys 1-8 (display page), arrows left/right (previous/next page) and arrows up/down (Scroll content).
qGallery
qGallery is a Prototype-based gallery script which automatically takes care of the image processing, offers multipple viewing modes and comes with a number of transition effects.
Amberjack
Amberjack is a lightweight Open Source library, enabling you to create site tours. The JavaScript library is lightweight (~4K), stable, LGPL licensed, browser compatible, set up in 2 minutes & super-easy to customize.
GWT-Ext Widget Library
GWT-Ext is a powerful widget library that provides rich widgets like Grid with sort, paging and filtering, Tree’s with Drag & Drop support, highly customizable ComboBoxes, Tab Panels, Menus & Toolbars, Dialogs, Forms and a lot more right out of the box with a powerful and easy to use API. It uses GWT and Ext.
Flexigrid
Lightweight but rich data grid with resizable columns and a scrolling data to match the headers, plus an ability to connect to an xml based data source using Ajax to load the content.
cforms II
cforms is a plugin for WordPress, offering convenient deployment of multiple contact forms throughout your blog or even on the same page. The form submission utilizes AJAX, falls back, however, to a standard method in case AJAX/Javascript is not supported or disabled.
Masked Input Plugin
A masked input plugin for the jQuery javascript library. It allows a user to more easily enter fixed width input where you would like them to enter the data in a certain format (dates,phone numbers, etc). It has been tested on Internet Explorer 6/7, Firefox 1.5/2, Safari, and Opera.
Oversized Sliding Tabs
Sliding Tabs is a mootools 1.11 plugin which adds a pretty neat effect. It’s a clone of something seen on Panic Software’s Coda site, which in turn is very heavily inspired by a widget used in the iTunes Music Store. Similar jQuery solution.
Custom Checkbox with jQuery
This script provides you with the ability to customize the design of checkboxes in your web forms. You can use the default skin and the Safari skin which are provided with the package.
NicEdit
NicEdit is a Javascript/AJAX inline content editor to allow easy editing of web site content on the fly in the browser. It integrates into any site in seconds to make any element/div editable or convert standard textareas to rich text editing.
AJAX Instant Messenger
is a browser-based instant messaging client. It uses AJAX to create a near real-time IM environment that can be used in conjunction with community, intranet, and social websites. No refreshing of the page is ever needed for this "web application" to work, as everything is updated in real-time via JavaScript.
Mootools animated sidebar menu
This tutorial explains how to implement an animated menu using mootools. You can see how it works directly from mootools site.
LiveValidation
LiveValidation is a small open source javascript library built for giving users real-time validation information as they fill out forms. Not only that, but it serves as a sophisticated validation library for any validations you need to make elsewhere in your javascript, it is not just limited to form fields.
Creating a table with dynamically highlighted columns
There is a number of impressive things happening within this small area.
Tablecloth
Tablecloth is lightweight, easy to use, unobtrusive way to add style and behaviour to your html table elements. By simply adding 2 lines of code to your html page you will have styled and active tables that your visitors will love :) Try to mouseover or click on a table below.
Unobtrusive Table Actions Script
An attempt at writing an unobtrusive (and fast) script that adds commonly required "actions" to data tables. Can Zebra stripe the table. And supports row hover, column hover and cell hover effects
FancyForm
FancyForm is a powerful checkbox replacement script used to provide the ultimate flexibility in changing the appearance and function of HTML form elements. It’s easy to use and degrades gracefully on all older, non-supporting browsers.
Starbox
Starbox allows you to easily create all kinds of rating boxes using just one PNG image. The library is build on top of the Prototype javascript framework. For some extra effects you can add Scriptaculous as well. Check the demos below to see what Starbox is all about and read on for more information on how to customize your own Starboxes.
Style Your Website’s Search Field with JS/CSS
Continuing to provide unobtrusive solutions, CSSG is happy to present SearchField. It serves as a way to style your search field and add behavior without any additional JavaScript or modifications in your markup. It features plug & play onfocus and onblur behaviors and auto suggestion like you’ve never seen before.
The sliding Date-Picker
Due to the development of Qash.nl, a Dutch personal finance website full of cool javascript features, it’s somewhat quiet around here. But to keep you satisfied, we present the sliding date-picker. This element enables you to pick dates with a simple slider bar. By dragging the bar over the time-line, the dates change instantly.
Carousel
Prototype UI. Carousel are great to display a large set of data like images.
minishowcase
minishowcase is a small and simple php/javascript online photo gallery, powered by AJAX that allows you to easily show your images online, without complex databases or coding, allowing to have an up-and-running gallery in a few minutes.
Timeline
Timeline is a DHTML-based AJAXy widget for visualizing time-based events. It is like Google Maps for time-based information. Below is a live example that you can play with. Pan the timeline by dragging it horizontally.
Displaying source code with Ajax
The script fires off an Ajax request, gets the document the link points to, replaces the special characters and adds line numbers.
mooSlideBox 3
The mooSlideBox v3 is a small and slim ajax based extension or replacement of the common "lightbox" that can be found on nearly every page. This lightbox clone works in IE 6/7, Opera and Firefox.
Accessible News Slider
Accessible News Slider is a JavaScript plugin built for the jQuery library. It meets the accessibility requirements as outlined by WCAG 1.0.
jsProgressBarHandler (Dynamic Unobtrusive Javascript Progress/Percentage Bar)
jsProgressBarHandler is a Javascript based Percentage Bar / Progress Bar, inspired upon JS-code by WebAppers and CSS-code by Bare Naked App. Next to a structural rewrite of the WebAppers code, this javascript progress bar can easily be extended and tweaked just by setting a few parameters.
CNN Style Scrolling Ticker with the Marquee Toolkit Control
Besides scrolling the items from right to left, the liScroll plugin supports two additional features
mooSocialize - ajax based social bookmark widget
Enough of having to submit interesting articles by hand to your favorite social networks and newsgroups? Then this is for you. Based on ajax, this small widget allows to integrate many many bookmarks for every post on your blog, website etc.
CheckBoxList hover extender
“We created an AJAX Control Toolkit Extender that asynchronously calls a web service method (or a page method) to obtain the information displayed in the popup control, when the user hovers over an item.”
Cornerz
Bullet Proof Corners plugin for jQuery using Canvas/VML
Lightview
Lightview was built to change the way you overlay content on a website. Works on all modern browsers
lightWindow
Another script you can use to integrate lightboxes and online-galleries in your web-pages. This lightweight script supports 5 different types of Media: Pages, Inline Content, Media (movies, SWF, etc), images (galleries, single), External Websites (via IFrame)
DatePicker using Prototype and Scriptaculous
An unobtrusive and flexible date picker widget which uses Prototype and Scriptaculous libraries. 52 More Calendars and Date Picker Designs.
ModalBox
ModalBox is a JavaScript technique for creating modern modal dialogs or even wizards (sequences of dialogs) without using conventional pop-ups and page reloads.
Accordion v2.0
A lightweight accordion that is built with Scriptaculous, has vertical, horizontal and nested styles and works properly in every browser.
New unobtrusive dynamic flickr badge
A compact Flickr-Badge for presentation of Flickr-images with a navigation option.
13 Awesome Javascript CSS Menus
13 “fresh” JavaScript+CSS-based navigation menus in a brief overview. Among other things Slashdot Menu and Sexy Sliding Menu displayed below.
Further Ajax scripts
Maillist
An AJAX addon for your site, a Mail list. An email address can be submitted without having to reload the whole page.
ClickHeat
ClickHeat is an Ajax-powered visual heatmap of clicks on a page, showing hot and cold click zones. You can also use the heatmap generator outside ClickHeat for your own applications, using PHP and GD library.
Prototype UI
Prototype UI is an open-source configurable Modal Window system. The library allows you to add a window or a dialogue. Windows can have a shadow and be scannable; modal mode is available, and there is a window manager for Web OS behavior.
Protoload makes it easy to show the User what is going on.
Imagine a complex Rich Internet Application using different hidden requests and processes working side by side. Protoload makes it easy to show the User what is going on.
Step by Step - Show and explain visitors what your page has for them
You might have encountered interactive demos created with screencasting and screengrabbing software that explain an interface to users in a step-by-step manner. This is exactly what this script does for web sites.
FontSize slider
Enables visitors to define the font-size of the page.
Facebox
Facebox is a jQuery-based, Facebook-style lightbox which can display images, divs, or entire remote pages. It’s simple to use and easy on the eyes. Download the tarball, view the examples, then start enjoying the curves.
Resources
- Ajax Rain is a growing collection of Ajax / Javascript / DHTML examples and demos you can download for free. The site currently offers over 1000 examples.
- 14 Cool Mootools Scripts lists useful Ajax-scripts for the Mootools library.
- 45 Fresh jQuery Plugins offers a growing list of references for jQuery plugins and solutions.
Are Proprietary Databases Doomed?
Times of change are upon the database market. The major established database companies are being challenged by open source upstarts like MySQL and PostgreSQL. For years, Open Source Databases (OSDBs) have been quietly increasing their penetration, but until recently they have lacked the capabilities to seriously threaten proprietary databases like Oracle, IBM's DB2, and Microsoft's SQL Server.
All that has changed. OSDBs now boast the necessary features and robustness to support commercial databases hundreds of Gigabytes in size. And a growing trickle of competitive benchmark results shows them performing more than acceptably well against their better-established cousins, while offering significant benefits in Total Cost of Ownership (TCO).
What does this mean for proprietary databases? Are they doomed? And more importantly, are there opportunities for end users to benefit from the rise of OSDBs? I will explore these topics in a multi-part blog:
- Feature Stagnation In The Traditional Database Market
- License Costs: the Soft Underbelly of Proprietary Databases
- The Looming Open Source Database Tsunami
- The Perfect Storm for Proprietary Databases
- Proprietary Counter Strategies
- Conclusion
The standard disclaimer applies as always: these are my opinions and not necessarily those of Sun or anyone else.
1. Feature Stagnation In The Traditional Database Market
When I joined Sun in the late 80s, choosing a database was still an important issue for end users. Large customers routinely issued tenders for databases as well as for computer systems, and, to help in the selection process, customers often staged performance bakeoffs between competing database vendors using home-grown benchmarks.
In the 90s, fierce competition led to a rapid explosion in features as well as dramatic improvements in performance. Sun in particular invested a lot of engineering effort in working with the major database companies to improve performance and scalability. At the same time, a variety of new technologies appeared, many claiming they would knock the relational database from its throne. Distributed databases, object relational, shared nothing, and in-memory database implementations all made cameo appearances. Relational databases simply absorbed their best features and continued to rule. Simple database features like triggers and stored procedures gave way to more sophisticated technologies like replication, online backup, and cluster support.
By the turn of the millenium, relational databases had already pretty much met the essential requirements of end users, and proprietary database companies were either pointing their vaccuum cleaners toward other interesting money piles, or losing the plot entirely and sailing off the edge of the world. Today, database releases continue to tout new features, but they're frosting on the cake rather than essentials. No-one issues a tender for a database unless they have unusual requirements. No-one loses their job because they chose the wrong database. And it's been that way for years.
Put very simply, the database has arguably become a commodity.
2. License Costs: the Soft Underbelly of Proprietary Databases
Databases may have become commodities, but selling them is still very profitable. Historically, as CPU performance increased with faster clock speeds, users continued to pay the same price for database licenses on the newer, more powerful systems. But that all changed as the industry moved to multi-core CPUs. As we will see, the licensing policies adopted by proprietary database companies have ensured that license charges have increased steeply as a result of this revolution in processor chip technology.
Some years ago, chip manufacturers began turning to multi-core CPU designs as a way of continuing to drive improvements in CPU performance. As it becomes more difficult to increase the transistor density of CPU chips and increase clock speeds, multi-core chips offer a simple alternative by packing more than one core on a single chip running at a lower clock speed. At the same time, proprietary database vendors began basing license charges on the number of cores in a system.
A practical example is Sun's dual-core UltraSPARC-IV chip. It replaced the single-core UltraSPARC-III chip at the same clock speed. By delivering two cores instead of one, the UltraSPARC-IV offered twice the performance of its predecessor. A typical system was the popular UltraSPARC-IV-based Sun Fire V490 which included four dual-core chips (eight cores). This system replaced the Sun Fire V480 with four single-core UltraSPARC-III chips.
The customer received twice the CPU performance for the same hardware price.
Not so for the Oracle database price, though. Based on per-core licensing, the new system was now treated
as an 8-core system instead of a 4-core system as previously. And worse, users were forced to a significantly more expensive database edition if they deployed systems with more than four cores. So, compared to the V480, the V490 now attracted a much higher per-core charge, on top of the requirement to pay for twice as many core licenses.
The following table illustrates the extraordinary windfall received by Oracle:
| System | V480 | V490 |
|---|
| Chips | 4 | 4 |
| Cores | 4 | 8 |
| Relative Performance | 1.0 | 2.0 |
| Relative Hardware Price | 1.0 | 1.0 |
| Database Core Licenses Required | 4 | 8 |
| Relative Core License Price | 1.0 | 2.7 |
| Relative Database License Price | 1.0 | 5.3 |
In the face of considerable pushback from the industry, Oracle responded with "discounts" for the second core in a chip. In the case of the V490, that meant the discounted database price was still four times the license charge for the V480!
So while users continued to enjoy more powerful and more feature-rich hardware at the same or lower price, they were paying a lot more to use the same database software on the new hardware.
The situation prompted comments like those from Stephen Elliot, an enterprise systems analyst at IDC, who anticipated increased pressure on Oracle to be more flexible with pricing, and reported that "Oracle is becoming the lone force on the processor issue". Since that time, under pressure from Microsoft, which introduced per-chip database licensing back in Feb 2005, Oracle has continued to tweak its licensing model, but so far without making wholesale changes.
It should be noted that Oracle is not alone in following this path - similar anomalies apply to the pricing of IBM's DB2 database. Nor is Microsoft the only vendor to embrace a per-chip pricing strategy - as long ago as 2005, the Register reported that BEA was adopting the same per-socket licensing model as Microsoft and VMware, and noted that "the software maker's move puts it in prime fighting position against Oracle and IBM, which have been slow to adjust their pricing models for new chips from AMD, Intel and others." According to Ashlee Vance in July 2007, "Most software vendors have had the decency to settle on a per-socket basis for their pricing schemes, ignoring the number of cores per chip. Meanwhile, IBM and Oracle, the vendors with the most to lose, prefer to keep you in a state of pricing confusion."
Anecdotally, some companies are finding that database licenses have become their single biggest IT cost. The impact is probably greater on small and medium-sized companies that don't have the same ability to command the hefty discounts that larger companies typically enjoy from database vendors. A colleague related a story that illustrates the issue. His brother worked for a 200-person company that decided it was time to upgrade their database applications. They set out to deploy a well-known proprietary database until they discovered that the database license fee was going to exceed their entire current annual IT budget! They ended up deploying an open source database instead.
3. The Looming Open Source Database Tsunami
In August 2007, Tom Daly revealed the results of a SPECjAppServer2004 benchmark based on an entirely open source software stack, with PostgreSQL running on a Sun Fire T2000 server and the Glassfish Application Server on two Sun Fire X4200 servers. The announcement was revealing for two reasons:
- It showed PostgreSQL capable of supporting more than 6,000 concurrent users on a commodity hardware platform
- The benchmark result was within 10% of a published result from an HP/Oracle configuration costing more than three times as much. The major reason for the huge price difference was the cost of Oracle (at $110,000 compared to PostgreSQL $0).
For the record, it should be noted that the SPECjAppServer2004 benchmark does not include a pricing metric, so these are not official prices. Nonetheless, since benchmark configurations clearly cost actual money, it seems reasonable to assess the prices involved. In this case, all hardware and software prices were drawn from publicly-available sources.
Open source databases still do not scale as well as proprietary databases, but they now perform well enough to manage a broad range of challenging applications. End users who previously saw OSDBs as primarily suitable for simple low-volume applications are now able to reasonably consider them for departmental database server deployments.
Do OSDBs have the features needed for serious deployment, though? 12 months ago, Forrester Research released a report suggesting that eighty percent of applications typically only use 30 per cent of the features found in commercial databases, and that the open source databases deliver those features today. While Forrester noted that OSDBs still lag for mission critical applications, those holes are likely to be plugged as bigger players announce 24x7 technical support and service (as has already happened, for example, with PostgreSQL).
A recent survey of Oracle users showed 20% having open source databases larger than 50 Gbytes and two thirds citing cost as the driver to adoption of open source. Open source database adoption is still relatively small. Does that mean OSDBs should be dismissed? Not according to a Gartner analyst, quoted last year as saying "We think it is a big deal. Granted, in the DBS market right now, they are very small players. Remember about 10 years ago, Linux in the market was a very small player? Not so much, anymore."
The comparison may be apt. With the combination of essential features, improved performance, robust support, and compelling price, OSDBs today bear a striking resemblence to Linux a few years ago. Many believe that the wave looming on the horizon is a tsunami. Time alone will tell.
4. The Perfect Storm for Proprietary Databases
Underneath major end-user applications like ERP and data warehouse software, every major hardware and software component is now subject to commodity pricing. As we have seen, each year hardware prices continue to decline while processing power increases. At the same time, leading operating systems like Sun's Solaris and Linux are now open source and can be deployed for free. The same is true of most other components of the software stack, including virtualization software, databases, application servers, web servers, and other middleware. Even Sun's UltraSPARC T1 and T2 chips and RTL have been open sourced, allowing community members to build on proven hardware at a much lower cost.
Why, then, is proprietary database software becoming more expensive while everything else reduces in price? End users normally expect to benefit from the cost savings resulting from improvements in technology. I am writing this blog, for example, on an affordable computer that would easily outperform expensive commercial systems from just 10 years ago.
It seems difficult to resist the conclusion that proprietary database companies have managed to redirect a good chunk of these savings away from end users and into their own coffers. Successful as this strategy has been, though, it could ultimately backfire. The more expensive proprietary databases become, the more attractive lower cost alternatives appear.
A number of forces are currently at work in the market:
- The momentum around open source software has continued to build, and many open source products, while not as capable as proprietary alternatives, have become "good enough" to replace them. This is also true of open source databases.
- Large technology suppliers are beginning to bundle OSDBs, with the result that customers are able to take out support contracts with established companies as well as startups. Sun, for example, ships and supports PostgreSQL.
- Benchmarks are beginning to feature OSDBs. Thus OSDBs are starting down a path that has been trod by proprietary databases over many years. Benchmarks can be expected to highlight the capabilities of OSDBs, accelerate the process of OSDB performance improvement, and, increasingly, expose the price difference between OSDBs and proprietary databases. And as the scalability of OSDBs increases, benchmarks will be published on larger systems, opening up an even wider gap in database pricing.
- The sweet spot in the hardware market is a two- to four-chip server. With the advent of quad-core chips from Intel and AMD, and the 8-core UltraSPARC T1 and T2 chips released by Sun, such systems have become powerful enough to carry out processing that required much larger and more expensive systems in the past. The pricing chasm between low cost hardware and high cost proprietary databases continues to widen.
For example, the UltraSPARC T1-based servers, the Sun Fire T1000 and T2000, shipped with 8 cores on a single chip. The recently-released single-chip, 8-core UltraSPARC T2 servers, which deliver twice the performance at roughly the same cost, now attract a DB2 license charge that has increased 66% compared to the T1 platforms. So IBM has taken the opportunity to significantly hike the price of the database software on that platform, even though the number of chips and number of cores has not changed.
At the same time, 8-core UltraSPARC T1 servers attracted a 2-core license charge from Oracle (a very reasonable pricing decision on Oracle's part - see this table which is referenced from here). At the time of writing, Oracle has not announced a final decision on pricing for the T2 platform, but it is unlikely that Oracle will be able to resist the temptation to emulate or even outdo the cynicism of IBM. Why such a pessimistic expectation? Because the same table referred to above announces that the 1.4GHz UltraSPARC version of the older T1 servers will be subject to a 0.5 multiplier instead of the 0.25 multiplier that applies to the 1.0 GHz and 1.2 GHz versions of the same platform. So a simple 17% clock speed increase in the hardware, with no other changes at all, prompted Oracle to double the license charge!
To be fair to Oracle, since the UltraSPARC T1 and T2 platforms are single chip systems, customers can purchase Standard Edition and Standard Edition One licenses for them at greatly reduced prices. But these editions do not offer the full Oracle feature set, and in particular they do not offer the parallel processing capabilities essential for efficient processing on the 32-way T1 and 64-way T2 platforms; if customers want parallel capabilities they must purchase the vastly more expensive Enterprise Edition with its core-based licensing and inexplicable and inconsistent "discounts".
- Web 2.0 is gathering momentum, and the new breed of companies leading the charge are largely ignoring proprietary databases for their deployments. Probably much of the scepticism relates to the cost implications.
In the past there has been no real alternative to proprietary databases. That has changed, at least for new applications that have no legacy database dependencies. The relative proportion of hardware and software purchase prices has been changing, too, and for some years software has gradually been consuming ever-larger slices of the pie. In recent times the pace has accelerated, though, as commodity-priced hardware has become much more powerful and database prices have increased.
For the most part, customers still have not entirely woken up to these trends. But if they ever do, we may see a significant shift in the database market. Is it possible that the perfect storm for proprietary databases is brewing?
5. Proprietary Counter Strategies
Proprietary Database companies are not without ways of responding to the challenge from OSDBs. Here are a few possibilities.
- Strategy 1: Resistance is Futile - You Will Be Assimilated.
Picking off your competitors can get a lot easier when they are open source companies, because most of them struggle to address a major discrepancy between their penetration and their annual revenue. Putting it another way, they have plenty of users but very little revenue to show for it. Hey, their product is free, after all! So far, not many people have figured out how to become absurdly rich by giving away software.
Buying a competitor gives you access to their Intellectual Property (IP) and their customer base. Sometimes it simply eliminates a competitor from the marketplace. Either way, playing the Borg can be an effective way of reshaping the market in your favor.
Note that Oracle has already made some raids across the border, having acquired InnoBase, maker of InnoDB, MySQL's most popular transactional engine, and Sleepycat Software, maker of Berkeley DB, another transactional engine used with MySQL. In response, MySQL has scrambled to introduce Falcon, a transactional database engine of its own.
Any of the major proprietary database companies could reasonably play the role of the Borg in this scenario, though, since all of them have very deep pockets. MySQL is probably the most vulnerable to takeover, since it's privately held. PostgreSQL may be more difficult to silence, since it is developed by an active community rather than a single company. But in either case, even if you pick off the company or the key community contributors, you haven't removed the IP from the market because the database is open source.
- Strategy 2: Bait And Switch. Offer a cut-down version of your own proprietary database for free, primarily targeting developers and companies doing pilot implementations. The idea is to make it easy for people to develop on your platform, then charge like wounded bulls when they deploy in earnest.
All of the major proprietary databases have free cut-down versions. Oracle Lite supports databases limited to 4 Gbytes of disk and 64 concurrent connections. Microsoft's Sql Server Express Edition supports one CPU only, 1 Gbyte of memory, and 4 Gbytes of disk. IBM's DB2 Express-C supports 2 processor cores, 2 Gbytes of memory, and unlimited disk.
These database editions are free, but they are not open source. The pricing policy could change overnight. And, as outlined above, each has restrictions that limit their usefulness for deployment.
- Strategy 3: Revenue Pull-Through. Include the database as a bundle with other pieces of your software stack. Focus the customer's attention on buying something else, and chances are they won't notice or won't care that they've bought your database as well.
- Strategy 4: Business As Usual. If you wait long enough, perhaps open source databases will stumble or be acquired by someone. Maybe their fall will be as meteoric as their rise. Or maybe the Borg will show up and assimilate them before they build too much more momentum. Either way, it will be one less competitor to worry about.
If you think a wait-and-see strategy sounds implausible, history shows that when they can't make up their minds how to respond, a lot of companies (and countries for that matter) do little more than sit on their hands. Australia's first ice skating gold medalist, Steven Bradbury, demonstrated how to win this way in spectacular fashion at the 2002 Winter Olympics. (Actually the last comparison is not entirely fair to Steven Bradbury - although he did win because the other contestants all stumbled, Bradbury's presence in the final was an achievement in itself that clearly demonstrated his ability and commitment.)
- Strategy 5: Reduce Prices. Much of the imperative for a migration to OSDBs will be removed if proprietary database companies drop their prices significantly. The excellence of proprietary databases is certainly not under question - I can personally attest to the performance, scalability, rich feature set, and robustness of both Oracle and DB2, for example. The search for alternative databases is largely driven by the need for pricing relief. OPEC discovered in the 1970s that inflated oil prices led to both energy conservation and a search for alternative energy sources. When OPEC soon reduced prices again, much of the impetus behind alternative energy disappeared in the West (sadly).
This strategy is only feasible if proprietary database companies derive most of their revenue from other sources. Oracle is probably the most vulnerable here.
My vote for the Strategy Most Likely To Succeed is a tie between Revenue Pull-Through and Reduce Prices. Oracle is arguably becoming the most successful proponent of the pull-through strategy. Oracle wants to supply you with a full software stack, including an OS, virtualization software, a broad range of middleware, a database, and end user applications. The largest component of Oracle's revenue currently still comes from database licenses, but the company is working hard to reduce that dependency. Until that happens, reducing prices across the board will be challenging for Oracle. If Oracle succeeds with a pull-through strategy, it doesn't mean that OSDBs will fail, of course. It simply means that Oracle is less likely to sustain major damage from their success.
Price reductions, if they are large enough and sustained enough, are likely to do more to slow down OSDB penetration. But I suspect that proprietary companies, if they are to do it at all, will need to reduce prices soon; if enough momentum builds around OSDBs we will reach a tipping point where it won't matter any more (witness the rise of Linux).
6. Conclusion
Are proprietary databases doomed, then? Not at all. Even if proprietary database companies pull no surprises, they won't fade away anytime soon. Too much legacy application software currently depends on them. Until ISV applications - like SAP's R/3, for example - support MySQL and PostgreSQL, end users will be wedded to proprietary databases. (Note, though, that SAP does support its own free and open source MaxDB database with R/3). As Oracle builds its software portfolio, too, more applications will ship with the Oracle database bundled. And for the forseeable future, proprietary databases will be the platform of choice for the largest mission-critical database deployments.
Make no mistake, though, open source databases are coming. For established companies it's more likely to be an evolution than a revolution. We will probably see a gradual OSDB surround, where new applications and deployments are increasingly based on OSDBs, driven by the cost savings. In emerging markets, though, it's looking more like a revolution. Last year I met with a small number of high-adrenaline companies in India, a market undergoing very rapid growth. They were openly dismissive of proprietary databases. One company had a small installation that was described as a "legacy" application due for replacement by an open source database. This is the scenario playing out today at high fliers like Google, Facebook, YouTube, and Flickr.
How can you take advantage of the rise of OSDBs? Here are some suggestions:
- If you're considering a new database deployment, examine the possible cost savings of an OSDB.
- If you're an established proprietary database user, don't simply throw out your database. Take the time to establish the feasibility and quantify the benefits of an OSDB solution before making a change.
- If you're unhappy about the prices you're paying for database software, let your supplier know - the more senior the contact, the better. Suppliers do listen to their customers! As a side note, if you're a proprietary database customer looking at OSDBs, you don't need to make a big secret of it. I know of situations where proprietary database suppliers offered deep discounts to keep a customer away from OSDBs!
Perhaps the last word should go to The Economist. The following observations, reported in January 2002, may well prove prescient: "if software firms continue to think they can cash in on every new increase in computer performance, they will only encourage more and more customers to defect. And today, unlike a decade ago, open-source software has become just too good to be ignored."
Allan
proprietary databases, blog, post
php.js, portage des fonctions PHP en javascript !
Php.js  est un projet de Kevin van Zonneveld qui a pour but de reproduire plus d'une centaine de fonctions PHP en javascript ! Dorénavant vous pourrez faire des array_chunk, basename, file_get_contents, md5 et autre stripslashes directement en javascript !
Vous pouvez soit inclure la totalité de la bibliothèque php.js dans vos pages pour avoir accès à l'ensemble des fonctions, soit sélectionner ci-dessous uniquement les fonctions qui vous intéressent ….
Astuces, bibliothèque, fonction, javascript, php, portage
Stu Nicholls | CSSplay | Experiments with cascading style sheets | Doing it with Style
www.cssplay.co.uk/
css, tutorial, howto
 Ajax Blog Scripts | Ajax Forms | Ajax RSS Scripts | Ajax Calendar Scripts
| Ajax Forum Scripts
|
| Ajax Chat Scripts
| Ajax Frameworks
|
| Ajax CMS Scripts
| Ajax Image Galleries | WYSIWYG Editors | File Manipulation
| Ajax Joomla Scripts | Game Scripts | |
Welcome to the biggest collection of ;) Free AJAX Scripts. We have separated them in different categories as Ajax Image Galleries, Ajax blog scripts, file manipulating systems and many more. The Free Ajax Scritps directory is constantly expanding in order to help many webmasters all over to spice up their site with some free ajax web 2.0 features. Most of the scripts have demo, so you can test before loosing any time in downloading and installing the scripts. If you are an Ajax programmer and you have written a script, We will be happy to spread it to the world.
Ajax, or AJAX, is a web development technique used for creating interactive web applications. The intent is to make web pages feel more responsive by exchanging small amounts of data with the server behind the scenes, so that the entire web page does not have to be reloaded each time the user requests a change. This is intended to increase the web page's interactivity, speed, functionality, and usability.All of the Ajax scripts bellow are free and have demos so you can test them before download and install. |
How to Import .SQL Dump File into MySQL
Execute SQL Statements from a SQL script file with the MySQL Command Line Client.
Wednesday, August 16, 2006
|
|
Open the MySQL Command Line Client and enter your password.
|
|
At the mysql command line type: USE your_db_name to change to the database you want to use for importing a SQL script file into.
|
|
Type in the mysql source or \. command, followed by the location of your script file to execute it against your database.
|
|
|
|
This tutorial goes through the steps of how to import a SQL script file into a MySQL database. The computer setup I'm using for this step-by-step guide includes Windows XP Professional and MySQL 5.0, however you should also be able to use this same process for importing SQL dump files with MySQL 4.1.
1. Open the MySQL Command Line Client and type in your password, and press Enter to login. See Figure 1.
2. Change to the database you want to use for importing the .sql file data into. Do this by typing
USE your_database_name
and press Enter. See Figure 2.
For example the full line would look like: mysql> USE myTestDatabase
3. Now locate the .sql file you want to execute. For instance if the file is located in the main local C: drive directory and the .sql script file name is currentSqlTable.sql, (See Figure 3) you would type the following:
\. C:\currentSqlTable.sql
and press Enter to execute the sql script file.
For example, the full line would look like: mysql> \. C:\ currentSqlTable.sql
Note: You can use either the source or \. command to execute a SQL script file. For instance:
mysql> source file_name
mysql> \. file_name
SQL, dump, MySQL
Une fonction PHP pour afficher des listes déroulantes
 Cette fonction n'a rien d'exceptionnel, elle va simplement vous aider à afficher des listes déroulantes de chiffres, je m'en sers surtout pour les champs date de naissance sur mes formulaires. J'ai ajouté quelques petites options pour certains cas de figure, comme ajouter un style css à la liste etc. ...
Liste déroulante pour afficher une liste de chiffres
<?php
function liste_deroul( $nom, $debut, $fin, $id, $class = 'dateP'){
echo'<select name="'.$nom.'" size="1" id="'.$id.'" class="dateP" >';
echo'<option value="">--</option>';
if ( isset ( $_POST['submit'] ) ) {
echo'<option value="'.$_POST[$nom].'">'.$_POST[$nom].'</option>';
}
for ($i=$debut; $i<=$fin; $i++){
echo'<option value="'.$i.'">'.$i.'</option>';
}
echo'</select>';
}
?>
pour afficher la liste des 31 jours du mois faites par exemple :
<?php
liste_deroul( 'jours', 1, 31, 'jours', 'date');
?>
facile non ? répétez-le pour la liste des mois de 1 à 12
et pour les années utilisez des variables !! Par exemple pour afficher la listes de années de naissance des 12 à 90 ans :
<?php $debut = $an-12; $fin = $an-90; ?>
<?php liste_deroul( 'aa' , $fin, $debut, 'aa', 'dateP'); ?>
Autre dérivée, la même fonction qui récupère des valeurs passées en paramètres
<?php
function liste_deroul_edit( $nom, $debut, $fin, $id, $val, $class = 'dateP'){
echo'<select name="'.$nom.'" size="1" id="'.$id.'" class="'.$class.'" >';
echo'<option value="'.$val.'">'.$val.'</option>';
for ($i=$debut; $i<=$fin; $i++){
echo'<option value="'.$i.'">'.$i.'</option>';
}
echo'</select>';
}
?>
Cas concret : après l'inscription je veux récupérer le jour de naissance d'un membre qui édite son profil !
<?php
$val = 25; //résultats issus d'une requête !!
liste_deroul_edit('jours', 1, 31, 'jours', $val, 'date');
?>
menu déroulant, php, fonction
PHP MySQL Tutorial
Soon you will know just how easy it is to build dynamic web pages using PHP
and MySQL. Really, PHP and MySQL is easy to learn, and I hope this tutorial
will help you realise that :-)
I'm sure that by the end of this tutorial you will have enough knowledge
to build your own database driven website using PHP & MySQL. Of course this
tutorial is not perfect. So if you have any critiques, question,
problem or suggestion please let
me know.
For a fast start just go straight to the fourth section
where you can learn how to connect
to MySQL using PHP. However, if you haven't installed PHP and MySQL go
visit the first section. It explains how to install PHP, MySQL and Apache.
And if you are a newcomer in PHP go to the second section to learn the basics
of PHP.
Ok, here is the list of tutorials you can find in this website. Feel free
to browse around.. .
|
- Installing
Apache PHP and MySQL
In case you haven't installed the trio yet don't skip this
section. This page explains about installing Apache, PHP and MySQL on Windows
plus some images to make things clearer. It also covers modifying Apache
configuration and PHP configuration so the two can work together.
- PHP
Tutorial
Give you enough to get started. First, you will learn how to open and close
PHP blocks, continued with using comments, a brief explanation about PHP
variables and types. Then you'll learn about manipulating strings, control
structures, functions and how to use web forms.
- MySQL
Tutorial
You will learn about starting MySQL, adding new MySQL user, creating a database
and tables. Then you'll learn about the SQL queries to insert data,get the
data, update and delete.
- Connecting to MySQL database
This is where you start to put PHP and MySQL together. This page explains
how to open and close MySQL connection with PHP.
- Creating a MySQL database
Obviously you will need to create your database first. This part explains
how to create MySQL database and table through PHP
- Insert Data To MySQL Database
After you have the database and tables ready it's time to learn how to insert
your data into the database.
- Getting The Data
Once you have the data stored in the database surely you want to get it back.
This page explains how to get your data out of MySQL plus how to convert
your query result into Excel format
- Using Paging
This one explains how to show your query result into multiple pages and how
to create the navigation link. Also show the problem that might happen when
using paging and the solution.
- Update and Delete
Explains how to update and delete your data and how to use table locking
to prevent violation of data integrity.
- Using PHP To Backup MySQL Database
In this page you can learn three different ways to backup your MySQL database
- Form Validation
This one explains how to validate HTML form on server side using PHP plus
client side form validation using Javascript to make your form more user
friendly.
- Creating a Guestbook
Guestbook is one of the most common feature for a website and this tutorial
will teach you how to create your own guestbook using PHP and MySQL. It also
explain how to use PHP functions to prevent code injection and the use of
paging.
- Uploading Files to MySQL
This page describe how to upload a file to MySQL database and how to download
it back.
- Creating a Content Management System (CMS)
Content Management System is getting more and more popular by the day. This
tutorial explains how to create a simple CMS, how to add, modify and delete
content using web form.
- User Authentication
This part explain three methods of authenticating a user. The first is hardcoding
the user info in the script itself. The second one check for the user id
and password in database. The third one add an random number verification.
- Image Gallery
Just another tutorial on making an image gallery
- Finding Web Hosting for PHP and MySQL
Just some tips for choosing the right host.
- Freelance PHP and MySQL jobs
Internet is the best place to find freelance jobs. This page show one
of them and also some list you need to think about before going freelancing.
- Q & A
I put some of the questions that i received here ( plus the answers ). Also added the common queries regarding php and mysql
- PHP MySQL Bookstore
Since this tutorial doesn't cover everything about php and mysql programming I decided to add a book store on this site. Here you can find several great books about programming with PHP and MySQL. And if you're thinking about replacing your old computer checkout the computer store
- Shopping
Cart Tutorial
I finally complete this tutorial. It's kindof big so i put it in it's own
domain. Because it's new i'm begging you to send me your critiques. The demo
site is working with the administration stuff disabled ( like add/modify/delete
product ) but you can download the source code so you can try it on your
computer.
- Online resources
Some website related to PHP and MySQL. Actually a couple of these resources
are not related to PHP or MySQL but I put them there anyway because they
are useful. Check it out, you may find some that interest you
|
NOTE:
This tutorial currently only covers PHP4. When my web host support PHP5 I'll
start adding some PHP5 specific features. Stay tuned for more php mysql tutorial |
tutorial
PHP est un acronyme récursif, qui signifie
"PHP: Hypertext Preprocessor" :
c'est un langage de script HTML, exécuté côté serveur.
Sa syntaxe est empruntée aux langages C, Java et Perl, et est facile
à apprendre. Le but de ce langage est de permettre aux développeurs
web d'écrire des pages dynamiques rapidement, mais vous pouvez
faire beaucoup plus avec PHP.
- Auteurs et Contributeurs
Ce manuel est essentiellement une
référence des fonctions, mais il contient
aussi des informations de
référence sur le langage, des explications sur
les fonctionnalités principales de PHP et
diverses informations supplémentaires.
Vous pouvez télécharger ce manuel sous divers formats,
sur » http://www.php.net/download-docs.php,
et sur le site de
» http://www.nexen.net/.
Plus d'informations sur ce manuel sont disponibles dans l'appendice
À propos du manuel. Si vous voulez découvrir
l'histoire de PHP.
Nous mettons en avant les personnes les
plus actives dans la préface du manuel mais il y a bien plus de contributeurs
qui nous aident actuellement dans notre travail ou qui ont fournis une aide
préciseuse au projet dans le passé. Il y a beaucoup d'inconnus qui nous ont
aidés à travers leurs notes concernant les pages du manuel qui sont
continuellement incluses dans le manuel, travail dont nous sommes très
reconnaissants. La liste fournie ci-dessous est classée par ordre
alphabétique.
Les contributeurs suivants
ont eu un impact énorme en ajoutant du contenu dans le manuel :
Bill Abt,
Jouni Ahto,
Alexander Aulbach,
Daniel Beckham,
Stig Bakken,
Jesus M. Castagnetto,
Ron Chmara,
Sean Coates,
John Coggeshall,
Simone Cortesi,
Markus Fischer,
Wez Furlong,
Sara Golemon,
Rui Hirokawa,
Brad House,
Pierre-Alain Joye,
Etienne Kneuss,
Moriyoshi Koizumi,
Rasmus Lerdorf,
Andrew Lindeman,
Hannes Magnusson,
Stanislav Malyshev,
Rafael Martinez,
Rick McGuire,
Yasuo Ohgaki,
Derick Rethans,
Rob Richards,
Sander Roobol,
Egon Schmid,
Thomas Schoefbeck,
Sascha Schumann,
Dan Scott,
Masahiro Takagi,
Michael Wallner,
Lars Torben Wilson,
Jim Winstead,
Jeroen van Wolffelaar et
Andrei Zmievski.
Les contributeurs suivants
ont énormément aidé dans l'édition du manuel :
Stig Bakken,
Gabor Hojtsy,
Hartmut Holzgraefe et
Egon Schmid.
Mainteneurs des notes utilisateurs
Les mainteneurs actuellement les plus actifs sont :
Mehdi Achour,
Friedhelm Betz,
Etienne Kneuss,
Nuno Lopes,
Hannes Magnusson,
Bobby Matthis et
Philip Olson.
Ces personnes ont également déployé énormément
d'efforts dans le maintien des notes utilisateurs :
Daniel Beckham,
Victor Boivie,
Jesus M. Castagnetto,
Nicolas Chaillan,
Ron Chmara,
Sean Coates,
James Cox,
Vincent Gevers,
Sara Golemon,
Zak Greant,
Szabolcs Heilig,
Oliver Hinckel,
Hartmut Holzgraefe,
Rasmus Lerdorf,
Matthew Li,
Andrew Lindeman,
Aidan Lister,
Maxim Maletsky,
James Moore,
Sebastian Picklum,
Derick Rethans,
Sander Roobol,
Damien Seguy,
Jason Sheets,
Maciek Sokolewicz,
Tom Sommer,
Jani Taskinen,
Yasuo Ohgaki,
Jakub Vrana,
Lars Torben Wilson,
Jim Winstead,
Jared Wyles et
Jeroen van Wolffelaar.
<?php
// Connexion, sélection de la base de données
$dbconn = pg_connect("host=localhost dbname=publishing user=www password=foo")
or die('Connexion impossible : ' . pg_last_error());
// Exécution de la requête SQL
$query = 'SELECT * FROM auteurs';
$result = pg_query($query) or die('Échec requête : ' . pg_last_error());
// Affichage des résultats en HTML
echo "<table>\n";
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
echo "\t<tr>\n";
foreach ($line as $col_value) {
echo "\t\t<td>$col_value</td>\n";
}
echo "\t</tr>\n";
}
echo "</table>\n";
// Libère le resultset
pg_free_result($result);
// Ferme la connexion
pg_close($dbconn);
?>
Recently, I had a possible corrupt index problem which required that
I determine what relation an OID
(object identifier) was referring to,
and, more importantly, if it was a newly created relation (a relation is
a table, view, index, or other database object). Almost all relations
in Postgres are stored in the
pg_class table (and, to be precise about it,
the pg_class table within the pg_catalog
schema). When an item is created,
it is assigned the next available OID. These are unique and issued in
ascending order, although that does not necessarily mean they are sequential
for any two relations.
The error I received looked like this:
Could not open relation with OID 7834
So I was given an OID number, and wanted to see which relation it mapped to,
as well as what its "neighbors" looked like: all the relations that were created
just before and just after it was. Checking if a relation was recently created is
easy enough - just see what the "current" OID is, and how it compared to the one you want:
SELECT max(OID) FROM pg_catalog.pg_class;
Since the result of the query above was over four billion, it was a safe bet that the
relation I was looking for was created a long time ago. However, to be sure,
I wanted to see it in context of its neighbors. Normally, you can view surrounding
rows in a table by using BETWEEN like this:
SELECT foo FROM bar
WHERE foo BETWEEN 1 and 10
ORDER BY foo;
This works great if the foo column is sequential, and has no gaps. However, in our example,
not only are gaps posible, they are quite common. How can we view things on
both sides of a given number? We use
UNION
to combine two queries: one which lists rows "before" our number, and one which
lists rows "after" our number.
To view all the relations that were created before ours, we can do this:
SELECT oid, relname, relkind
FROM pg_catalog.pg_class
WHERE oid <= 7834
ORDER BY oid DESC LIMIT 6;
This shows us the relation we are interested in, and the 5 immediately before it:
oid | relname | relkind
------+----------------------+---------
7834 | tusk | r
7730 | pg_toast_7826_index | i
7728 | pg_toast_7826 | t
7826 | city | r
7720 | sales_range_pk_index | i
7718 | sales_range | r
The relkind field indicates what type of object this is: r for a relation (aka table),
i for an index, v for a view, S for a
sequence, and t for
TOAST.
In the example above,
we can see that the OID we were given is for a table named tusk, and that it was
created after the table named city (which has a column needing a toast relation and its
related index). Before that, a table named sales_range was created which
has a single index. Note that even the things created "at the same time" do not always
have sequential OIDs.
Of course, we also want to see the other side of the picture, the ones that
were created afterwards:
SELECT oid, relname, relkind
FROM pg_catalog.pg_class
WHERE oid >= 7834
ORDER BY oid ASC LIMIT 6;
oid | relname | relkind
--------+--------------------+---------
7834 | tusk | r
7839 | picture_seq | S
7841 | picture | r
7844 | pg_toast_7841 | t
7846 | pg_toast_7841_index | i
7847 | picture_pkey | i
Here we see that after our table tusk was created, a sequence
named picture_seq was created,
followed by a table named picture and some supporting objects for it.
How can we combine those two queries? Since they are returning the same columns, we can
use a UNION to combine them. I decided to use UNION rather than UNION ALL so that I
would not have to change one of the queries from <= or >= to simply =. Our target
OID row will thus be returned twice, but filtered out by UNION to a single row:
SELECT * FROM (
SELECT oid, relname, relkind
FROM pg_catalog.pg_class
WHERE oid <= 7834
ORDER BY oid DESC LIMIT 6
) AS foo
UNION (
SELECT oid, relname, relkind
FROM pg_catalog.pg_class
WHERE oid >= 7834
ORDER BY oid ASC LIMIT 6
)
ORDER BY oid ASC;
We had to use parenthesis (and a subselect) to ensure that the ORDER BY ... LIMIT runs first, and
the UNION happens at the very end. We also order the whole thing by OID for a better output. The final
result looks like this:
oid | relname | relkind
------+----------------------+---------
7718 | sales_range | r
7720 | sales_range_pk_index | i
7826 | city | r
7728 | pg_toast_7826 | t
7730 | pg_toast_7826_index | i
7834 | tusk | r
7839 | picture_seq | S
7841 | picture | r
7844 | pg_toast_7841 | t
7846 | pg_toast_7841_index | i
7847 | picture_pkey | i
At some point, I received a different error, and had to look up another OID. When I find myself performing the same
action more than once while using Linux, I create an alias, a Bash script, or a
Perl script. The same rule applies to SQL: anytime you find yourself writing the same
query with minor changes over and over, write a view or a function. In this case, we'll
make our query into a function with the OID as an argument. Since this function will
be returning more than one row, we'll create a
custom type for it first:
CREATE TYPE oidnear AS (oid oid, relname name, type "char");
CREATE FUNCTION oidnear(OID)
RETURNS SETOF oidnear
LANGUAGE plpgsql
AS
$_$
DECLARE myrec RECORD;
BEGIN
FOR myrec IN
SELECT * FROM (
SELECT oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid <= $1
ORDER BY oid DESC LIMIT 6) foo
UNION (
SELECT oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid >= $1
ORDER BY oid ASC LIMIT 6)
ORDER BY oid
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$_$;
Now all we have to do to look up the neighbors of an OID is:
SELECT * FROM oidnear(7834);
One of the nice things about Postgres is the ability to use polymorphic functions. This
simply means that you can have more than one function with the same name, as long as the
arguments are different. We can use this to remove the hard-coded limit of five items
before and after the entry we want. We'll create a new function with the same name, but
with an additional argument stating how many rows we want to show on each side of our
target one. Luckily,
plpgsql
has no problem using the input arguments in the LIMIT portion
of the SQL clause. We can even perform arithmetic on it, in this case to ensure that we
also account for the row itself in the limit counting:
CREATE FUNCTION oidnear(OID,INT)
RETURNS SETOF oidnear
LANGUAGE plpgsql
AS
$_$
DECLARE myrec RECORD;
BEGIN
FOR myrec IN
SELECT * FROM (
SELECT oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid <= $1
ORDER BY oid DESC LIMIT $2 + 1) foo
UNION (
SELECT oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid >= $1
ORDER BY oid ASC LIMIT $2 + 1)
ORDER BY oid
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$_$;
At this point, both of these return the same thing:
SELECT * FROM oidnear(7638);
SELECT * FROM oidnear(7638,5);
However, the same code is repeated in both functions. Let's make the two-argument version
the "real" function and have the single argument one call it with a default argument
of 5 rows on a side:
DROP FUNCTION oidnear(OID);
CREATE FUNCTION oidnear(OID)
RETURNS SETOF oidnear LANGUAGE SQL AS
$_$
SELECT * FROM oidnear($1,5)
$_$;
Now we have two functions we can call, which act as if they are a single function that happens to
take an extra argument, indicating the exact number of rows to return. For a final tweak, let's
add a visual indicator for our row to make things a little easier to view. We'll add another column
at the start of the output to do so. We'll need to redefine the custom type we created earlier to
do so. Adding a CASCADE argument like so removes the type and the functions:
DROP TYPE oidnear CASCADE;
CREATE TYPE oidnear AS (
"!" text, oid oid, relname name, type "char"
);
CREATE FUNCTION oidnear(OID,INT)
RETURNS SETOF oidnear
LANGUAGE plpgsql
AS
$_$
DECLARE myrec RECORD;
BEGIN
FOR myrec IN
SELECT * FROM (
SELECT CASE WHEN oid=$1 THEN '**' ELSE '' END::text,
oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid <= $1
ORDER BY oid DESC LIMIT $2 + 1) foo
UNION (
SELECT CASE WHEN oid=$1 THEN '**' ELSE '' END::text,
oid,relname,relkind
FROM pg_catalog.pg_class
WHERE oid >= $1
ORDER BY oid ASC LIMIT $2 + 1)
ORDER BY oid
LOOP
RETURN NEXT myrec;
END LOOP;
RETURN;
END;
$_$;
CREATE FUNCTION oidnear(OID) RETURNS SETOF oidnear
LANGUAGE SQL AS
$_$
SELECT * FROM oidnear($1,5);
$_$;
The final output:
SELECT * FROM oidnear(7834);
! | oid | relname | relkind
----+------+----------------------+---------
| 7718 | sales_range | r
| 7720 | sales_range_pk_index | i
| 7826 | city | r
| 7728 | pg_toast_7826 | t
| 7730 | pg_toast_7826_index | i
** | 7834 | tusk | r
| 7839 | picture_seq | S
| 7841 | picture | r
| 7844 | pg_toast_7841 | t
| 7846 | pg_toast_7841_index | i
| 7847 | picture_pkey | i
Chapter 7. Queries The previous chapters explained how to create tables, how to fill them with data, and how to manipulate that data. Now we finally discuss how to retrieve the data out of the database.
User Comments
No comments could be found for this page.
Add Comment
Please use this form to add your own comments regarding your experience with particular features of PostgreSQL, clarifications of the documentation, or hints for other users. Please note, this is not a support forum, and your IP address will be logged. If you have a question or need help, please see the faq, try a mailing list, or join us on IRC. Note that submissions containing URLs or other keywords commonly found in 'spam' comments may be silently discarded. Please contact the webmaster if you think this is happening to you in error.
In order to submit a comment, you must have a community account.
* denotes required field
PostgreSQL PHP Generator Freeware Version : 7.10
Author : SQL Maestro Group
OS : Win95,Win98,WinME,WinNT 4.x,Windows2000,WinXP,Windows2003
Postgresql php generator license : Freeware (Price: free)
Added date : 7. November 2007
Release date : 24. October 2007
Download page : Download
Download Size : 3266 kBytes
Publisher : How to visit SQL Maestro Group?
Freeware description : PostgreSQL PHP Generator is a freeware but powerful PostgreSQL GUI frontend that allows you to generate high-quality PostgreSQL PHP scripts for the selected tables, views and queries for the further working with these objects through the web. Key features include: data management abilities (adding, editing and deleting records), support of images and hyperlinks, customization of the HTML appearance, filtering and sorting abilities, data protection with a lot of security settings, lookup options for master-detail relations, integrated script navigation and ability to create multilingual web apps.
postgresql, php, generator, freeware
38. PostgreSQL and XML
I'm been feeling mildly guilty about not publishing the work I did with John Gray last year on XPath support in PostgreSQL, but the interest in Jon Udell's 'SQL and XPath' post has goaded me into action: here's a brief outline of the four production-ready XPath functions which represent the most complete XML support of any open source RDBMS.
The four functions extend John's previous work (see contrib/xml directory in any recent Postgres installation) and rely on:
- Postgres's support of user-defined functions written in C, Perl and PL/SQL.
- The fast, light, C-based libxml library from xmlsoft.org.
As the example below demonstrates, Postgres UDFs can be used in both the SELECT and WHERE clauses of a SQL statement.
CREATE TABLE t_articles (
article_id int4 NOT NULL,
article_title varchar(255) NOT NULL,
article_xml text
);
INSERT INTO t_articles (article_id, article_title, article_xml)
VALUES (1, 'Some Beatles',
'<beatles id = "b1">
<beatle instrument = "guitar" alive = "no">john lennon</beatle>
<beatle instrument = "guitar" alive = "no">george harrison</beatle>
</beatles>');
INSERT INTO t_articles (article_id, article_title, article_xml)
VALUES (2, 'Other Beatles',
'<beatles id = "b2">
<beatle instrument = "bass" alive = "yes">paul mccartney</beatle>
<beatle instrument = "drums" alive = "yes">ringo starr</beatle>
</beatles>');
SELECT
article_id, xpath_string(article_xml,'/beatles/@id') AS beatle_id
FROM
t_articles
WHERE
xpath_bool(article_xml,'/beatles/beatle[@alive="yes"]');
/*
-- returns:
--
-- article_id | beatle_id
-- ------------ -----------
-- 2 | b2
*/
Usage of the two functions shown here - xpath_string and xpath_bool - should be self-explanatory. They each take two arguments: a column identifier and an XPath statement. xpath_string returns a string (the textual content of a node) and xpath_bool returns a boolean value. xpath_number returns a number, which is useful for numerical comparisons or running mathematical functions on the returned value:
WHERE xpath_number(article_xml,'//job_title/@year') > 2001
The fourth function, xpath_nodeset, is a somewhat kludgey solution to the problem of returning values from repeated nodes in a single record. Here's an example of how it might be used:
SELECT
xpath_nodeset(article_xml,'//beatle/@instrument') AS beatle_instrument
FROM
t_articles
WHERE
article_id = 2;
/*
-- returns:
--
-- beatle_instrument
-- -------------------
-- <instrument>bass</instrument><instrument>drums</instrument>
*/
This problem highlights the potentially awkward marriage between SQL sets and arbitrarily structured data, but I'm sure that better solutions are possible (how does Virtuoso handle repeated nodes?). PostgreSQL 7.3 allows UDFs to return multiple rows, which might help... suggestions are very welcome.
Contact me for the software in its current unlicensed, undocumented state, or watch this space for a properly packaged distribution.
Update: the functions have been updated and are now part of the standard PostgreSQL distribution.
php, postgresql, xml, howto, tutorial
60. PostgreSQL and XML updated
The XML functions for PostgreSQL (introduced here) have been updated and, as of version 8, ship with the standard distribution of PostgreSQL. The package now includes support for in-database XSLT transformations and a flexible new xpath_table function which evaluates a set of XPath queries and returns the results as a virtual table.
Installation
To build the functions, follow the instructions in contrib/xml2. You'll need recent versions of the libxml2 and libxslt libraries somewhere your compiler can see them. We've compiled and used the functions for PostgreSQL 7.4+ under Linux (Red Hat and Debian), Windows and OS X; here's a Win32 binary with statically linked libxml2 and libxslt libraries. If you get stuck with the installation, do get in touch.
XSLT support
An example:
SELECT xslt_process(article_xml, '/home/tom/standard.xsl')
AS rendered_article
FROM t_articles
WHERE article_id = 55;
A second:
SELECT xslt_process(article_xml, template_xslt)
AS rendered_article
FROM t_articles
INNER JOIN t_templates
ON article_template_id = template_id;
A third:
SELECT xslt_process('/home/book.xml', styles_xslt, 'title=Herzog')
AS rendered_book
FROM t_books
INNER JOIN t_styles
ON book_style_id = style_id;
These demonstrate the function signature:
xslt_process(document, stylesheet [,paramlist]) RETURNS text
Document and stylesheet can be URLs or XML strings, which are identified by a leading left angle bracket. The optional paramlist argument is a list of parameter assignments to be used in the transformation, specified in the form 'a=1,b=2'.
XPath table
This makes use of the table functions which were introduced in PostgreSQL 7.3. It's an attempted solder for the impedance mismatch of many-elements-per-document XML and one-unit-per-row SQL.
Here's an example, modified from John's docs:
SELECT article_id, author, title
FROM
xpath_table('article_id',
'article_xml',
'articles',
'/article/author|/article/title',
'date_entered > ''2003-01-01'' ')
AS t_virtual_table(article_id integer, author text, title text);
The function signature is
xpath_table(key, document, relation, xpaths, criteria)
where key is the column to be used as the primary key of the generated table; document is the name of the column containing a blob of XML; relation specifies the table which holds the column with the XML blob; xpaths are multiple, pipe-separated XPath expressions; criteria is the contents of the WHERE clause which constrains the generation of the virtual table (use 1=1 to return all rows).
postgresql, xml, php, tutorial
Cet article a pour but d'expliquer aux débutants comment prendre en main PostgreSQL très simplement à l'aide de l'outil graphique PgAdmin III.
. Introduction
1. CREATION DE LA BASE DE DONNEES
2. CREATION DES TABLES
2.1. TABLE GENRE
2.1.1. Ouvrir schéma public
2.1.2. création des colonnes
2.1.3. création d'une clé primaire
2.1.4. Visualiser le contenu d'une table
2.1.5. Le panneau sql affiche les commandes, pour la colonne idgenre :
2.1.6. table année tabannée
2.1.7. table film tabfilm
2.2. RELATION UN A PLUSIEURS ENTRE DEUX TABLES
2.2.1.. Créer une clé étrangère
2.3. RELATION PLUSIEURS A PLUSIEURS ENTRE DEUX TABLES
2.3.1. table intermédiaire entre tabfilm et tagenre
2.3.2. Ajouter les clés étrangères pour les liaisons avec tabfilm et tabgenre
2.3.2. Créer une deuxième clé étrangère genre
3. INSERER LES DONNEES DANS LES TABLES
3.1.
3.2. Insérer les genres ligne par ligne pour le tableau genre
3.3. Insérer des données dans tabannée
3.4. Insérer des données dans tabfilm
3.5. Insérer des données dans tab_film_genre
4. POUR AFFICHER LE CONTENU D'UNE SEULE TABLE
4.1. le contenu d'une table
4.2. Le contenu d'une colonne d'une table
4.3. Le contenu sélectionné d'une colonne d'une table
4.4. Le contenu trié d'une colonne d'une table
4.5. Même principe pour genre
4.6. Compter le nombre de films
5. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE CLE ETRANGERE
5.1. Afficher le film et l'année de sortie
5.2. afficher le film et l'année de sortie, tri sur l'année
5.3. afficher le film et l'année de sortie, sur une année
5.4. afficher le film et l'année de sortie, depuis une année
6. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE TABLE INTERMEDIAIRE DE TYPE n-n
6.1. Edition triée des genres par films
6.2. Edition triée des films par genres
6.3. Edition triée des films par genres
6.4. Edition du nombre de films par genre et avec du texte
. Introduction
L'objectif est l'utilisation d'une base de données pour gérer une liste de films avec l'année de sortie ou de réalisation ainsi que le genre du film un peu comme IMDB
Le nom donné à cette base sera AMDB
Hypothèses
Le film a un seul nom
Un film a une seule année de sortie, et peut avoir un ou plusieurs genres
3 tableaux seront utilisés un comportant le nom des films, un autre les années et un troisième les genres.
Le tableau année pourra servir à d'autres relations avec d'autres tableaux dans le cas d'une extension de la base. Il faudra créer un 4è tableau pour les relations multiples entre les films et les genres ( Tab_film_genre).
Relations entre les tables
1. CREATION DE LA BASE DE DONNEES
Juste après l'installation, vous avez des palettes de composants en plus :
 | Sélectionner postgrest – ajouter une objet - ajouter une base de données
|
 | indiquer le nom de la base amdb
|
on obtient
2. CREATION DES TABLES
2.1. TABLE GENRE
2.1.1. Ouvrir schéma public
 |
ensuite table , souris bouton droit
sélectionner ajouter une table
|
 |
Ajout d'une table tabgenre
Et validation
|
 |
La table est crée
|
2.1.2. création des colonnes
La table comporte 2 colonnes Genre et idgenre, idgenre sera utilisée comme clé primaire avec incrémentation automatique
 |
Ajout des colonnes
|
 | Nom de la colonne : idgenre
|
 |
sélectionner le type de données
sérial pour l'incrémentation automatique
valider la commande par ok
|
 |
La colonne idgenre est créée
|
 |
Création de la colonne genre
De 40 caractères
Non nuls
|
 |
La table contient 2 colonnes :genre et idgenre
|
2.1.3. création d'une clé primaire
 |
nom de la clé : clégenre
|
 |
ensuite sélectionner colonne
Sélectionner comme colonne idgenre correspondant à clé primaire
Ajouter et ok
|
2.1.4. Visualiser le contenu d'une table
 |
il n'y a rien , seules les colonnes apparaissent
|
2.1.5. Le panneau sql affiche les commandes, pour la colonne idgenre :
2.1.6. table année tabannée
 |
2 colonnes :
idannée type serial
année type nombreentier
cléannée clé primaire sur idannée
|
2.1.7. table film tabfilm
 |
et enfin la table tabfilm
idfilm : type serial,
titre : type 40 caractères,
annéesortie : type entier (clé etrangère, pointe sur la tabannée).
)
cléfilm clé primaire colonne idfilm
|
2.2. RELATION UN A PLUSIEURS ENTRE DEUX TABLES
Entre film et année, une seule année de sortie pour un film et plusieurs films pour une année
( Créer une colonne du type annéesortie dans tabfilm)
La colonne annéesortie de la table tabfilm va pointer sur la cléannée de tabannée type de relation n – 1
Annéesortie et cléannée sont de même type intéger
La clé étrangère est créée dans la table ou il y a « plusieurs »
2.2.1.. Créer une clé étrangère
 | Nom de la clé étrangère = keyannée
Référence = la table tabannée
|
2.3. RELATION PLUSIEURS A PLUSIEURS ENTRE DEUX TABLES
Entre film et genre, plusieurs genres pour un film et plusieurs films pour un genre
Relation entre deux tables du type 1- n et n - 1
Une table intermédiaire est nécessaire
2.3.1. table intermédiaire entre tabfilm et tagenre
 |
Créer une table intermédiaire ou de jointure tab_film_genre
|
 |
avec 3 colonnes
la 1ère pour la clé de la table key de type serial
la 2è pour pointer sur la clé du tableau tabfilm nom de la colonne film type entier
la 3è pour pointer sur la clé de tabgenre nom de la colonne genre type entier
clé primaire sur colonne key
|
2.3.2. Ajouter les clés étrangères pour les liaisons avec tabfilm et tabgenre
 |
Créer une clé étrangère ayant pour nom titre et référence la table tabfilm
ensuite
|
 |
définir la colonne locale film qui va correspondre à la clé idfilm de tabfilm.
|
2.3.2. Créer une deuxième clé étrangère genre
 |
genre
qui a pour référence la table tabgenre
|
 |
avec colonne locale genre et
référencé dans le tableau tabgenre par la clé de ce tableau idgenre
|
 |
Résultat
|
3. INSERER LES DONNEES DANS LES TABLES
3.1.
 |
Pour insérer des données dans une table utiliser la fonction script :
cliquer sur l'icône SQL
|
 |
On obtient
|
3.2. Insérer les genres ligne par ligne pour le tableau genre
 |
on indique le nom du tableau et ensuite les valeurs dans l'ordre des colonnes
idgenre = 1 et genre = comédie
INSERT INTO tabgenre VALUES ( 1,'comédie');
|
 |
ou uniquement la valeur de genre, idgenre sera automatiquement incrémenté
INSERT INTO tabgenre(genre) VALUES ( 'drame');
|
 |
résultats
|
 |
ajouter les genres suivants
INSERT INTO tabgenre(genre) VALUES ( 'policier');
INSERT INTO tabgenre(genre) VALUES ('horreur');
INSERT INTO tabgenre(genre) VALUES ('aventure');
INSERT INTO tabgenre(genre) VALUES ( 'histoire');
INSERT INTO tabgenre(genre) VALUES ( 'fiction');
|
3.3. Insérer des données dans tabannée
 |
INSERT INTO tabannée(année) VALUES ( 2000');
|
3.4. Insérer des données dans tabfilm
 |
ajouter des films
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Men ', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ('Forge', 3);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Star', 4);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'histoire', 2);
INSERT INTO tabfilm(titre,annéesortie) VALUES ( 'Soleil' , 1);
|
3.5. Insérer des données dans tab_film_genre
 |
structure de la table
|
 |
INSERT INTO tab_film_genre(film,genre) VALUES ( 4,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,4);
INSERT INTO tab_film_genre(film,genre) VALUES ( 3,2);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,3);
INSERT INTO tab_film_genre(film,genre) VALUES ( 2,6);
INSERT INTO tab_film_genre(film,genre) VALUES ( 1,1);
|
 |
Contenu de la table
|
4. POUR AFFICHER LE CONTENU D'UNE SEULE TABLE
4.1. le contenu d'une table
 |
SELECT * from tabannée;
|
4.2. Le contenu d'une colonne d'une table
 |
SELECT année FROM tabannée;
|
4.3. Le contenu sélectionné d'une colonne d'une table
 |
SELECT année FROM tabannée WHERE année>2000;
|
4.4. Le contenu trié d'une colonne d'une table
 |
SELECT * FROM tabannée ORDER BY année;
|
 |
ou
SELECT année FROM tabannée ORDER BY année
Pour afficher la colonne année
|
4.5. Même principe pour genre
4.6. Compter le nombre de films
 |
pour la liste des films
SELECT titre FROM tabfilm;
|
 | pour le nombre de films
SELECT count( titre )FROM tabfilm;
|
5. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE CLE ETRANGERE
5.1. Afficher le film et l'année de sortie
 | SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie
|
5.2. afficher le film et l'année de sortie, tri sur l'année
 | SELECT tabfilm.titre,tabannée.année FROM tabfilm , tabannée WHERE tabannée.idannée=tabfilm.annéesortie ORDER BY année
il est plus rapide de remplacer le nom de la table par une lettre
SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie ORDER BY année
|
5.3. afficher le film et l'année de sortie, sur une année
 | SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année=2000
|
5.4. afficher le film et l'année de sortie, depuis une année
 | SELECT F.titre,A.année FROM tabfilm AS F, tabannée AS A WHERE A.idannée=F.annéesortie and a.année>2000
|
6. AFFICHER LE CONTENU DE DEUX TABLES LIEES PAR UNE TABLE INTERMEDIAIRE DE TYPE n-n
 | tabfilm F
|
 | tab_film_genre A
|
 | tabgenre G
|
6.1. Edition triée des genres par films
 | SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A
where
F.idfilm = A.film
AND
A.genre=G.idgenre
ORDER BY titre, genre
|
6.2. Edition triée des films par genres
 | SELECT F.titre,G.genre
FROM tabfilm AS F, tabgenre AS G ,tab_film_genre AS A where F.idfilm = A.film AND A.genre=G.idgenre
ORDER BY genre,titre
|
 | Variante avec du texte
|
6.3. Edition triée des films par genres
6.4. Edition du nombre de films par genre et avec du texte
tutoriel, débutant, pgadmin III, postgresql
PHP and XMLPHP has its own built in functions for XML handling. To process an XML file first of all you need an XML parser. You can create it with the following code:
$parser = xml_parser_create();
This code creates an empty XML parser object which will process our XML file later. The parser requires the XML document as a string so we read the file as follows:
$document = file_get_contents("test.xml");
Now we have an XML document in string format and an XML parser. Let's process the file.
xml_parse($parser, $document);
After processing we free up the resources:
xml_parser_free($parser);
The complete code looks like this at the moment:
<?php
$parser = xml_parser_create();
$document = file_get_contents("test.xml");
xml_parse($parser, $document);
xml_parser_free($parser);
?>
And our test XML file (test.xml), which is located in the same directory as our PHP script:
<carlist>
<car type="Mercedes">S 600</car>
<car type="Mercedes">E 270 CDI</car>
<car type="BMW">535 D</car>
<car type="Lexus">IS 220</car>
</carlist>
Try to execute it!
What happened?
Nothing.
It is normal as we didn't define what to do with the results and how the result should look like. Then for example how can I list all Mercedeses? |
|
example 1, parse, tutorial, php
XML processing refinementTo get a list of Mercedes we need to write some additional code. First make some refinement how the parser should process the XML you can set some property with the xml_parser_set_option() function
In our example I set the case folding property to false, which generally means that no tag name will convert to uppercase.
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
XML element handlersBesides this we have to tell the parser what to do if an opening or closing tag is found. To do this I wrote two functions to handle these events. Both have a well defined attribute list.
function openElement($parser, $element, $attributes)
function closeElement($parser, $element)
The first parameter in both cases is the parser object. The second is the element name. The openElement has a third parameter the attributes which is an array representation of the actual element attribute names and values.
You can assign these function to the parser as follows:
xml_set_element_handler($parser, "openElement", "closeElement");
Take care that the functions defined above must have the same names as the parameters of the xml_set_element_handler. As result if the parser finds an open element than it will call the openElement function. Of course it calls closeElement if a closing tag was found. |
|
example 2, tutorial, php
XML processing refinementTo get a list of Mercedes we need to write some additional code. First make some refinement how the parser should process the XML you can set some property with the xml_parser_set_option() function
In our example I set the case folding property to false, which generally means that no tag name will convert to uppercase.
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
XML element handlersBesides this we have to tell the parser what to do if an opening or closing tag is found. To do this I wrote two functions to handle these events. Both have a well defined attribute list.
function openElement($parser, $element, $attributes)
function closeElement($parser, $element)
The first parameter in both cases is the parser object. The second is the element name. The openElement has a third parameter the attributes which is an array representation of the actual element attribute names and values.
You can assign these function to the parser as follows:
xml_set_element_handler($parser, "openElement", "closeElement");
Take care that the functions defined above must have the same names as the parameters of the xml_set_element_handler. As result if the parser finds an open element than it will call the openElement function. Of course it calls closeElement if a closing tag was found.
|
|
example 3, tutorial, php
XML data handlerOne more thing is missing. A handler function to process the XML data values. For that I implemented a new function:
function characterData($parser, $data);
The first parameter is already known. The second contains the actual data value. To make the assignment you should use the following statement:
xml_set_character_data_handler($parser, "characterData");
Now the full code looks like this:
<?php
function openElement($parser, $element, $attributes) {
}
function closeElement($parser, $element) {
}
function characterData($parser, $data) {
}
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
xml_set_element_handler($parser, "openElement", "closeElement");
xml_set_character_data_handler($parser, "characterData");
$document = file_get_contents("test.xml");
xml_parse($parser, $document);
xml_parser_free($parser);
?>
Of course this code will result again is an empty page. Just to have some output and demonstrate how the functions are called I added some code in the handler functions.
The new code is:
<?php
function openElement($parser, $element, $attributes) {
foreach ($attributes as $key => $value) $attr .= $key." : ".$value." - ";
echo "-> openElement element: $element, attribute: $attributes ($attr) <br/>";
}
function closeElement($parser, $element) {
echo "-> closeElement element is: $element<br/>";
}
function characterData($parser, $data) {
echo "-> characterData data is: [$data]<br/>";
}
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
xml_set_element_handler($parser, "openElement", "closeElement");
xml_set_character_data_handler($parser, "characterData");
$document = file_get_contents("test.xml");
xml_parse($parser, $document);
xml_parser_free($parser);
?> |
|
example 4, tutorial, php
|
And the output is:
-> openElement element: carlist, attribute: Array ()
-> characterData data is: [ ]
-> characterData data is: [ ]
-> openElement element: car, attribute: Array (type : Mercedes - )
-> characterData data is: [S 600]
-> closeElement element is: car
-> characterData data is: [ ]
-> characterData data is: [ ]
-> openElement element: car, attribute: Array (type : Mercedes - )
-> characterData data is: [E 270 CDI]
-> closeElement element is: car
-> characterData data is: [ ]
-> characterData data is: [ ]
-> openElement element: car, attribute: Array (type : BMW - )
-> characterData data is: [535 D]
-> closeElement element is: car
-> characterData data is: [ ]
-> characterData data is: [ ]
-> openElement element: car, attribute: Array (type : Lexus - )
-> characterData data is: [IS 220]
-> closeElement element is: car
-> characterData data is: [ ]
-> closeElement element is: carlist
In the output you can find the root element <carlist>, the four child elements <car> the attribute list and the data values.
The only interesting thing is why we have so many time characterData with an empty string?
To understand this you should open the XML file and displays the hidden characters. After it you can see that there is a new line character after a <carlist> element and it is handled as a data value. After it in the second line there are some spaces before the <car> element and it results again a characterData call.
Try to remove all hidden character from the test.xml.
The result will be the following:
-> openElement element: carlist, attribute: Array ()
-> openElement element: car, attribute: Array (type : Mercedes - )
-> characterData data is: [S 600]
-> closeElement element is: car
-> openElement element: car, attribute: Array (type : Mercedes - )
-> characterData data is: [E 270 CDI]
-> closeElement element is: car
-> openElement element: car, attribute: Array (type : BMW - )
-> characterData data is: [535 D]
-> closeElement element is: car
-> openElement element: car, attribute: Array (type : Lexus - )
-> characterData data is: [IS 220]
-> closeElement element is: car
-> closeElement element is: carlist
Now it is much more clear. Of course you don't have to remove these characters from the XML. I did it just to demonstrate how the processing works. |
|
example 5, tutorial, php
Last stepsWe are almost ready. Some further modification and we have the list of Mercedeses. To do that we have to change the handler functions code as follows:
function openElement($parser, $element, $attributes) {
global $flag;
if (($element == 'car') && ($attributes['type'] == 'Mercedes')) $flag = true;
}
function closeElement($parser, $element) {
global $flag;
$flag = false;
}
function characterData($parser, $data) {
global $flag,$mblist;
if ($flag) $mblist[] = $data;
}
The $flag and $type are defined outside of the functions and used inside as global variables. - The openElement function sets the flag to true if a car with the type of Mercedes was found. - The closeElement function resets the flag, to represent we are using not more the relevant element. - The characterData function checks the flag and if it is set - means that the actual element is a car and the attribute type is Mercedes - than add the data value to the type array. At the end we have an array with the requested list. Just display it. The complete code looks like this: <?php
$mblist = '';
$flag = false;
function openElement($parser, $element, $attributes) {
global $flag;
if (($element == 'car') && ($attributes['type'] == 'Mercedes')) $flag = true;
}
function closeElement($parser, $element) {
global $flag;
$flag = false;
}
function characterData($parser, $data) {
global $flag,$mblist;
if ($flag) $mblist[] = $data;
}
$parser = xml_parser_create();
xml_parser_set_option($parser, XML_OPTION_CASE_FOLDING, false);
xml_set_element_handler($parser, "openElement", "closeElement");
xml_set_character_data_handler($parser, "characterData");
$document = file_get_contents("test.xml");
xml_parse($parser, $document);
xml_parser_free($parser);
foreach ($mblist as $value) {
echo $value.'<br/>';
}
?>
And the output is:
S 600
E 270 CDI
Final wordsI hope that this small tutorial helps you to understand the basics of PHP XML processing. If everything is clear then it will not cause any further problem to extend the script and process your own XML file. Thanks for your time and attention! [/html] |
|
example 6, tutorial, php
Get MySQL database layout in XML format
Get MySQL database layout in XML format
In this tutorial I will show you how to get your MySQL database description / layout in an XML format. If you are an absolute beginner in XML than it can be useful to read first an XML introduction tutorial. Step 1.
To create a description in XML format first we need to connect to the database. The connection parameters are hard coded in this tutorial but of course you can write a small html form to get the values dynamically from the user. So the connection code is quite simple: <?php
$host = 'localhost';
$user = 'testuser';
$pass = 'mypass';
$db = 'demo';
// Connecto to the MySQL server
$link = mysql_connect($host,$user,$pass);
if ($link){
// Select the database
mysql_selectdb($db,$link);
?> Step 2.
As we have a connection first we write this information in our XML document. In this case we just output the XML content on the display and you can copy the content to a file if you want. To compose the XML header is not so complicated but you have to take care using the PHP built in function htmlspecialchars() to convert special characters to HTML entities. Without this echo will not display the XML code correctly. To make the output more readable I just add a simple HTML br tag at the end (outside of the htmlspecialchars()). The code is this: <?php
// Write xml header information
echo htmlspecialchars(
'<DBSchema db="'.$db.'" host="'.$host.'" user="'.$user.'" pass="'.$pass.'">');
echo "<br/>";
?> Step 3.
Now we have to get all tables inside this database. We can do this by using the SQL command SHOW TABLES. The result of this command will give you the complete list of tables. Now we have to go through all of the tables and create their description. Let's create a while loop where each iteration has a new table name. The code looks like this: <?php
// Get all tables
$result = mysql_query('SHOW TABLES');
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
// Get table name
$table = $row[0] ;
?> Ok, now we have the actual table name but how we can get the table description with field names, types and so on. There is again a nice SQL command called DESCRIBE we can use to do this. The correct syntax is DESCRIBE tablename. As we have the tablename it is quite easy. Before we process table description we need to output the actual table header to the XML output. It is similar to main header output. Because of readability I added some HTML spaces to make some text indent in the output. The code for this is: <?php
// Get table info
$struct = mysql_query('DESCRIBE '.$table);
// Write table info
echo " "
.htmlspecialchars('<Table name="'.$table.'">')."<br/>";
?> Step 4.
In this step we make a new while loop to go through all of the fields inside the table. We process the resultset and build an XML output for each field. I have separated all field property in a new line to make the code more readable. The fields output is this: <?php
// Get table description
while ($row2 = mysql_fetch_array($struct, MYSQL_NUM)) {
$autoi = (strstr($row2[5],'auto_increment')) ? 'true' : 'false';
echo " ";
echo htmlspecialchars('<Field name="'.$row2[0].'" ');
echo htmlspecialchars('type="'.$row2[1].'" ');
echo htmlspecialchars('null="'.$row2[2].'" ');
echo htmlspecialchars('key="'.$row2[3].'" ');
echo htmlspecialchars('default="'.$row2[4].'" ');
echo htmlspecialchars('autoincrement="'.$autoi.'"/>')."<br/>";
}
?> Step 5.
As final step we need to close all relevant tags and we are ready. Just set connection parameters and run the script and you get the complete description of your database in XML format. You can extend this script by adding an outer loop and you can get all database description on the actual MySQL server. The complete code looks like this: <?php
$host = 'localhost';
$user = 'testuser';
$pass = 'mypass';
$db = 'demo';
// Connecto to the MySQL server
$link = mysql_connect($host,$user,$pass);
if ($link){
// Select the database
mysql_selectdb($db,$link);
// Write xml header information
echo htmlspecialchars(
'<DBSchema db="'.$db.'" host="'.$host.'" user="'.$user.'" pass="'.$pass.'">');
echo "<br/>";
// Get all tables
$result = mysql_query('SHOW TABLES');
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
// Get table name
$table = $row[0] ;
// Get table info
$struct = mysql_query('DESCRIBE '.$table);
// Write table info
echo " "
.htmlspecialchars('<Table name="'.$table.'">')."<br/>";
// Get table description
while ($row2 = mysql_fetch_array($struct, MYSQL_NUM)) {
$autoi = (strstr($row2[5],'auto_increment')) ? 'true' : 'false';
echo " ";
echo htmlspecialchars('<Field name="'.$row2[0].'" ');
echo htmlspecialchars('type="'.$row2[1].'" ');
echo htmlspecialchars('null="'.$row2[2].'" ');
echo htmlspecialchars('key="'.$row2[3].'" ');
echo htmlspecialchars('default="'.$row2[4].'" ');
echo htmlspecialchars('autoincrement="'.$autoi.'"/>')."<br/>";
}
// Write table section close element
echo " "
.htmlspecialchars('</Table>')."<br/>";
}
// Write the final close element
echo htmlspecialchars('</DBSchema>')."<br/>";
}
?> Download
You can also download a complete DB to XML code from this site. |
|
MySQL, database, xml
This function converts a CSV file to a simple XML file.
<?php
/**
* Converts a CSV file to a simple XML file
*
* @param string $file
* @param string $container
* @param string $rows
* @return string
*/
function csv2xml ($file, $container = 'data', $rows = 'row')
{
$r = "<{$container}>\n";
$row = 0;
$cols = 0;
$titles = array();
$handle = @ fopen($file, 'r');
if (! $handle) return $handle;
while (($data = fgetcsv($handle, 1000, ',')) !== FALSE)
{
if ($row > 0) $r .= "\t<{$rows}>\n";
if (! $cols) $cols = count($data);
for ($i = 0; $i < $cols; $i++ )
{
if ($row == 0)
{
$titles[$i] = $data[$i];
continue;
}
$r .= "\t\t<{$titles[$i]}>";
$r .= $data[$i];
$r .= "</{$titles[$i]}>\n";
}
if ($row > 0) $r .= "\t</{$rows}>\n";
$row++;
}
fclose($handle);
$r .= "</{$container}>";
return $r;
}
?>
$xml = csv2xml('/home/user/myfile.csv', 'people', 'person');
<people>
<person>
<name>John Smith</name>
<zip>19100</zip>
</person>
<person>
<name>Jane Doe</name>
<name>19200</name>
</person>
</people>
xml, script, convert
May 6th, 2006 by global in PHP Tutorials, PHP
XML is a markup language that lets you describe nearly any type of data. Examples of it are RSS, and Atom feeds. Many web services provide API data in the form of XML. It's a format that's easy to understand, even for the non-nerds.
In this tutorial, we're going to have a dummy XML document, and then use PHP to parse it into in an array. Our XML will contain a list of songs currently in our playlist, complete with artist names and a rating. From there, PHP will load it, then do it's thing by getting it into a neatly formatted array. (Song name => array(title, rating)).
First off, we're going to create our XML document. I'll show you mine:
<?xml version="1.0" encoding="iso-8859-1"?>
<playlist>
<song>
<title>Dani California</title>
<artist>The Red Hot Chili Peppers</artist>
<rating>4/5</rating>
</song>
<song>
<title>We Are All on Drugs</title>
<artist>Weezer</artist>
<rating>3/5</rating>
</song>
<song>
<title>Are We The Waiting</title>
<artist>Green Day</artist>
<rating>5/5</rating>
</song>
</playlist>
As you can see, XML is easy to understand and write, a rarity in programming/scripting. Now, on to the PHP sides of things. The XML parser needs three functions. One to handle open XML elements, one to handle closing XML elements, and one to handle character data. These functions will be created by us and customized to do exactly what we need. We're going to use a bunch of variables that will help us determine where we are exactly in the document, and what the character data is.
//stores the current element the XML parser is on
$current_element = "";
//are we parsing a song?
$in_song = false;
//current song name
$current_song = "";
//song data
$songs = array();
Next is the functions to handle opening and closing XML elements. These will set the boolean in_song and the current_element.
function open_element ($parser, $el, $attr)
{
global $current_element, $in_song;
//are we about to parse a song
if($el == "SONG")
{
//Yep, set it so
$in_song = true;
}
$current_element = $el;
}
function close_element($parser, $el)
{
global $current_element, $in_song;
//No longer in a song
if($el == "SONG")
{
$in_song = false;
}
else
{
//Otherwise, reset the current element
$current_element = '';
}
}
All paramaters in the functions will be automatically sent the XML parser. Also, XML element names are in CAPS! Next up is where the real stuff is, the character data handling. The comments should shed some light on it.
function character_data ($parser, $data)
{
global $in_song, $songs, $current_song, $current_element;
if($in_song)
{
if($current_element == "SONG")
{
//This data is just blank, do nothing with it
}
else if($current_element == "TITLE")
{
//Make a new array with the current song title
$songs[$data] = array();
//Set the current song, will be the key in the array for the artist, rating
$current_song = $data;
}
else if($current_element !== '')
{
//Now, it's either the artist, or rating
//Get's the current song (array key)
//Will be either artist or rating, so we don't have to do seperate ones
$songs[$current_song][strtolower($current_element)] = $data;
}
}
}
Now, we have all our functions set up, but we still need to tell PHP to parse the XML. The XML functions have long names, but don't let them confuse you.
After this, our song array is populated, and you could do something like:
echo "My playlist currently includes:<ul>";
foreach($songs as $n => $v)
{
echo "<li>$n - {$v['artist']} (Rated: {$v['rating']})</li>";
}
echo "</ul>";
This only shows you a teeny bit of XML and it's uberness, if you play with it, you'll be able to do some pretty cool things with it. Good luck! 
xml, parse, tutorial, php, howto
| | Converting XML Into a PHP Data Structure (Page 1 of 9 ) Dante explains how to convert data from XML to the more PHP friendly arrays and arrays in arrays so that it can be used easily in your code.In the last few years, XML has received great media attention, and most languages support the parsing and extraction of data from XML documents. Besides being a great three-letter anacronym to sprinkle on your résumé, XML is actually a useful data storage structure for PHP programmers. Before you begin to use XML, you must first determine if your project really needs what XML offers. There are alternative data storage formats like fixed-width column files, tab-delimited files, CSV files, and database tables, but these formats typically can only manage a simple grid of rows and columns of data. XML provides several additional benefits for programmers including: - data format abstraction,
- simple document tag/data validation,
- the ability to store data in a tree-like hierarchy,
- platform independence,
- ease of integration,
- and more...
...but you already know that. What you want to do is use XML data inside your sparkling new web application. We'll explore one simple way to do this in the remainder of this article. |
xml, php, tutorial, 9 pages, howto
Voici quelques exemples de code PHP analysant un document XML.
Ce premier exemple affiche la structure de l'élément
de début dans un document avec indentation.
Exemple 2801. Afficher une structure XML
<?php
$file = "donnees.xml";
$depth = array();
function debutElement($parser, $name, $attrs)
{
global $depth;
for ($i = 0; $i < $depth[$parser]; $i++) {
echo " ";
}
echo "$name\n";
$depth[$parser]++;
}
function finElement($parser, $name)
{
global $depth;
$depth[$parser]--;
}
$xml_parser = xml_parser_create();
xml_set_element_handler($xml_parser, "debutElement", "finElement");
if (!($fp = fopen($file, "r"))) {
die("Impossible d'ouvrir le fichier XML");
}
while ($data = fread($fp, 4096)) {
if (!xml_parse($xml_parser, $data, feof($fp))) {
die(sprintf("erreur XML : %s à la ligne %d",
xml_error_string(xml_get_error_code($xml_parser)),
xml_get_current_line_number($xml_parser)));
}
}
xml_parser_free($xml_parser);
?>
Exemple 2802. Transtypage XML -> HTML
Cet exemple remplace les balises XML d'un document par des balises
HTML. Les éléments inconnus seront ignorés.
Bien entendu, cet exemple sera appliqué à un type
précis de fichiers XML.
<?php
$file = "data.xml";
$map_array = array(
"BOLD" => "B",
"EMPHASIS" => "I",
"LITERAL" => "TT"
);
function startElement($parser, $name, $attrs)
{
global $map_array;
if (isset($map_array[$name])) {
echo "<$map_array[$name]>";
}
}
function endElement($parser, $name)
{
global $map_array;
if (isset($map_array[$name])) {
echo "</$map_array[$name]>";
}
}
function characterData($parser, $data)
{
echo $data;
}
$xml_parser = xml_parser_create();
// Utilisons la gestion de casse, de manière à être sûrs de trouver la balise dans $map_array
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, true);
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
if (!($fp = fopen($file, "r"))) {
die("Impossible de trouver le fichier XML");
}
while ($data = fread($fp, 4096)) {
if (!xml_parse($xml_parser, $data, feof($fp))) {
die(sprintf("erreur XML : %s à la ligne %d",
xml_error_string(xml_get_error_code($xml_parser)),
xml_get_current_line_number($xml_parser)));
}
}
xml_parser_free($xml_parser);
?>
Cet exemple exploite les références externes de XML :
il est possible d'utiliser un gestionnaire d'entité externe
pour inclure et analyser les documents, tous comme les instructions
exécutables peuvent servir à inclure et analyser
d'autres documents, et aussi fournir une indication de confiance
(voir plus bas).
Le document XML qui est utilisé dans cet exemple est fourni plus
loin dans l'exemple (xmltest.xml et
xmltest2.xml).
Exemple 2803. Entité externe
<?php
$file = "xmltest.xml";
function trustedFile($file)
{
// faites seulement confiance aux fichiers locaux dont vous êtes le propriétaire
if (!eregi("^([a-z]+)://", $file)
&& fileowner($file) == getmyuid()) {
return true;
}
return false;
}
function startElement($parser, $name, $attribs)
{
echo "<<font color=\"#0000cc\">$name</font>";
if (count($attribs)) {
foreach ($attribs as $k => $v) {
echo " <font color=\"#009900\">$k</font>=\"<font
color=\"#990000\">$v</font>\"";
}
}
echo ">";
}
function endElement($parser, $name)
{
echo "</<font color=\"#0000cc\">$name</font>>";
}
function characterData($parser, $data)
{
echo "<strong>$data</strong>";
}
function PIHandler($parser, $target, $data)
{
switch (strtolower($target)) {
case "php":
global $parser_file;
// si le document analysé est de confiance, nous déclarons qu'il est sûr
// d'exécuter le code PHP qu'il contient. Si ce n'est pas le cas, le code est affiché
// à la place.
if (trustedFile($parser_file[$parser])) {
eval($data);
} else {
printf("Untrusted PHP code: <em>%s</em>",
htmlspecialchars($data));
}
break;
}
}
function defaultHandler($parser, $data)
{
if (substr($data, 0, 1) == "&" && substr($data, -1, 1) == ";") {
printf('<font color="#aa00aa">%s</font>',
htmlspecialchars($data));
} else {
printf('<font size="-1">%s</font>',
htmlspecialchars($data));
}
}
function externalEntityRefHandler($parser, $openEntityNames, $base, $systemId,
$publicId) {
if ($systemId) {
if (!list($parser, $fp) = new_xml_parser($systemId)) {
printf("Could not open entity %s at %s\n", $openEntityNames,
$systemId);
return false;
}
while ($data = fread($fp, 4096)) {
if (!xml_parse($parser, $data, feof($fp))) {
printf("erreur XML : %s à la ligne %d lors de l'analyse de l'entité %s\n",
xml_error_string(xml_get_error_code($parser)),
xml_get_current_line_number($parser), $openEntityNames);
xml_parser_free($parser);
return false;
}
}
xml_parser_free($parser);
return true;
}
return false;
}
function new_xml_parser($file)
{
global $parser_file;
$xml_parser = xml_parser_create();
xml_parser_set_option($xml_parser, XML_OPTION_CASE_FOLDING, 1);
xml_set_element_handler($xml_parser, "startElement", "endElement");
xml_set_character_data_handler($xml_parser, "characterData");
xml_set_processing_instruction_handler($xml_parser, "PIHandler");
xml_set_default_handler($xml_parser, "defaultHandler");
xml_set_external_entity_ref_handler($xml_parser, "externalEntityRefHandler");
if (!($fp = @fopen($file, "r"))) {
return false;
}
if (!is_array($parser_file)) {
settype($parser_file, "array");
}
$parser_file[$xml_parser] = $file;
return array($xml_parser, $fp);
}
if (!(list($xml_parser, $fp) = new_xml_parser($file))) {
die("Impossible d'ouvrir le fichier XML");
}
echo "<pre>";
while ($data = fread($fp, 4096)) {
if (!xml_parse($xml_parser, $data, feof($fp))) {
die(sprintf("Erreur XML : %s à la ligne %d\n",
xml_error_string(xml_get_error_code($xml_parser)),
xml_get_current_line_number($xml_parser)));
}
}
echo "</pre>";
echo "parse complete\n";
xml_parser_free($xml_parser);
?>
xml, examples, scripts, tutorial, howto |
10.0 - 1 vote - 
Snippet Menu
Mon. Oct. 9th, 2006 9:57 PM 
