Volker Zimmermann

Die Umstellung der Kommunikation mit der Datenbank auf Webservices verfolgt vor allem das Ziel, eine plattformunabhängige Business-Logik zu erstellen. Sollte ich die Applikation auf ein weiteres Betriebssystem portieren, dann bräuchte ich mich um diesen Teil nicht mehr zu kümmern.

Die Webservices folgen dem RESTful API Paradigma. REST stellt eine einfache Alternative zu ähnlichen Verfahren wie SOAP und WSDL und dem verwandten Verfahren RPC dar.

Die Daten der Client-Applikation werden für die Ein- und Ausgabe in Dictionaries abgelegt. Bei der Übergabe dieser Dictionaries an ein URL-Connection Object (hier "WS_POSTone" kann Xojo automatisch eine Payload im JSON-Format erzeugen:

// Request an Webservice
Var json As JSONItem = MyDictData
WS_POSTone.SetRequestContent(json.ToString, "application/json")
WS_POSTone.Send("POST", "https://vzdocs.web.local/document/postOne/")

Der Request erzeugt durch eine der Rewrite-Direktiven einen Redirect durch .htaccess auf die PHP-Datei doc_controller.php mit dem Parameter

# Redirects fuer Webservices
#
RewriteEngine on
RewriteRule ^document/postOne/$   doc_controller.php?request=postOne [NC,QSA]

getAll();
		break;
	case "getSel":
		// REST URI /document/getSel/ verarbeiten
		require_once($_SERVER['DOCUMENT_ROOT']."/document/get_document.php");
		$get_documents = new get_document();
		$get_documents->getSel();
		break;
	case "maxid":
		// REST URI /document/maxid/ verarbeiten
		require_once($_SERVER['DOCUMENT_ROOT']."/document/get_document.php");
		$get_documents = new get_document();
		$get_documents->getMaxID();
		break;	
	case "getOne":
		// REST URI /document/show// verarbeiten
		require_once($_SERVER['DOCUMENT_ROOT']."/document/get_document.php");
		$get_documents = new get_document();
		$get_documents->getOne($_GET["id"]);
		break;
	case "postOne":
		// REST URI /document/postOne/ verarbeiten
		require_once($_SERVER['DOCUMENT_ROOT']."/document/post_document.php");
		$post_documents = new post_document();
		$post_documents->postOne();
		break;
	case "putOne":
		// REST URI /documents/put/ verarbeiten
		require_once($_SERVER['DOCUMENT_ROOT']."/documents/put_documents.php");
		$put_documents = new put_documents();
		$put_documents->putOne($GET["id"]);
		break;	
	case "" :
		//404 - not found;
		break;
}
?>

doc_controller.php leitet den Request an post_document.php weiter:

getConnection();
		// Prepare Objekt
		$document = new Document($db);
		$data = json_decode(file_get_contents("php://input"));
		// Daten aus der Payload setzen
		$document->usr_id = $data->usr_id;
		$document->doc_subject = $data->doc_subject;
		$document->doc_direction = $data->doc_direction;
		$document->doc_status = $data->doc_status;
		$document->doc_file = $data->doc_file;
		$document->doc_type = $data->doc_type;
		$document->doc_posted = $data->doc_posted;
		$document->cnt_id = $data->cnt_id;
		$document->fld_id = $data->fld_id;
		$document->arc_id = $data->arc_id;
		// Dokument angelegt?
		if($document->insertOne()){
			// Response Code 200 (ok)
			http_response_code(200);
			// User benachrichtigen
			echo json_encode(array("Das Dokument wurde angelegt."));
		}
		// Dokument nicht angelegt!
		else{
			// Response Code 503 (Service unavailable)
			http_response_code(503);
			// User benachrichtigen
			echo json_encode(array("Das Dokument wurde nicht angelegt!"));
		}
	}
}
?>

Die Definition der Klasse "Document" mit den erforderlichen Prepared Statements für die Datenbankoperationen SELECT, INSERT und UPDATE sind in document.php hinterlegt. (DELETE findet nicht statt. Datensätze werden - falls erforderlich - als markiert) :

conn = $db;
	}
	// Alle Dokumente lesen (DATE_FORMAT(doc_posted,'%d.%m.%Y') AS doc_posted,)
	function selectAll(){
		// Select all Query
		$query = 
			"SELECT
				doc_id,
				usr_id,
				doc_subject,
				doc_direction,
				doc_file,
				doc_type,
				doc_status,
				doc_posted,
				cnt_id,
				fld_id,
				arc_id
			FROM " . $this->table_name . "
			ORDER BY doc_posted DESC";
			
		// Prepare Query Statement
		$stmt = $this->conn->prepare($query);
		// Statement ausführen
		$stmt->execute();
		return $stmt;
	}
	// Dokumente selektiert lesen
	function selectSel(){
		// Select Query
		$query = 
			"SELECT
				doc_id,
				usr_id,
				doc_subject,
				doc_posted,
				doc_file,
				doc_status,
				doc_type,
				cnt_id,
				cnt_description,
				fld_id,
				fld_path
			FROM view_doclistshort
			ORDER BY doc_posted DESC";
		
		// Prepare Query Statement
		$stmt = $this->conn->prepare($query);
		// Statement ausführen
		$stmt->execute();
		return $stmt;
	}
	// Ein Dokument lesen (ID)
	function selectOne(){
		// Single select Query
		$query = 
			"SELECT
				doc_id,
				usr_id,
				doc_subject,
				doc_direction,
				doc_status,
				doc_file,
				doc_type,
				doc_posted,
				cnt_description,
				fld_path,
				arc_description 
			FROM view_docshow
			WHERE doc_id = ?
			LIMIT 0,1";
		// Prepare Query Statement
		$stmt = $this->conn->prepare( $query );
		// Bind übergebene ID ans Statement 
		$stmt->bindParam(1, $this->id);
		// Query ausführen
		$stmt->execute();
		return $stmt;		
	}
	// Höchste DocID auslesen
	function selectMaxID(){
		$query = 
			"SELECT 
				MAX(doc_id) As doc_id
				FROM " . $this->table_name;
		// Prepare Query Statement
		$stmt = $this->conn->prepare($query);
		
		// Statement ausführen
		$stmt->execute();
		return $stmt;
	}
	// Dokument anlegen
	function insertOne(){
		// Insert Query
		$query = 
			"INSERT 
				INTO " . $this->table_name . "
			SET
				usr_id=:usr_id, 
				doc_subject=:doc_subject, 
				doc_direction=:doc_direction, 
				doc_status=:doc_status, 
				doc_file=:doc_file,
				doc_type=:doc_type,
				doc_posted=:doc_posted,
				cnt_id=:cnt_id,
				fld_id=:fld_id,
				arc_id=:arc_id";
		// Prepare Query
		$stmt = $this->conn->prepare($query);
		// Sanitize
		$this->usr_id=htmlspecialchars(strip_tags($this->usr_id));
		$this->doc_subject=htmlspecialchars(strip_tags($this->doc_subject));
		$this->doc_direction=htmlspecialchars(strip_tags($this->doc_direction));
		$this->doc_status=htmlspecialchars(strip_tags($this->doc_status));
		$this->doc_file=htmlspecialchars(strip_tags($this->doc_file));
		$this->doc_type=htmlspecialchars(strip_tags($this->doc_type));
		$this->doc_posted=htmlspecialchars(strip_tags($this->doc_posted));		
		$this->cnt_id=htmlspecialchars(strip_tags($this->cnt_id));
		$this->fld_id=htmlspecialchars(strip_tags($this->fld_id));
		$this->arc_id=htmlspecialchars(strip_tags($this->arc_id));
		// Bind Werte an Objekteigenschaften
		$stmt->bindParam(":usr_id", $this->usr_id);
		$stmt->bindParam(":doc_subject", $this->doc_subject);
		$stmt->bindParam(":doc_direction", $this->doc_direction);
		$stmt->bindParam(":doc_status", $this->doc_status);
		$stmt->bindParam(":doc_file", $this->doc_file);
		$stmt->bindParam(":doc_type", $this->doc_type);				
		$stmt->bindParam(":doc_posted", $this->doc_posted);	
		$stmt->bindParam(":cnt_id", $this->cnt_id);		
		$stmt->bindParam(":fld_id", $this->fld_id);
		$stmt->bindParam(":arc_id", $this->arc_id);
		// Query ausführen
		if($stmt->execute()){
			return true;
		}
		return false;
	}
	// Kontakt aktualisieren
	function update(){
		// Update Query
		$query = "
			UPDATE " . $this->table_name . "
			SET
				usr_id=:usr_id, 
				doc_subject=:doc_subject, 
				doc_direction=:doc_direction, 
				doc_status=:doc_status,
				doc_file=:doc_file,
				doc_type=:type,
				doc_posted=:doc_posted,
				cnt_id=:cnt_id,
				fld_id=:fld_id,
				arc_id=:arc_id
			WHERE
				doc_id=:doc_id";
		// prepare query statement
		$stmt = $this->conn->prepare($query);
		// Sanitize
		$this->usr_id=htmlspecialchars(strip_tags($this->usr_id));
		$this->doc_subject=htmlspecialchars(strip_tags($this->doc_subject));
		$this->doc_direction=htmlspecialchars(strip_tags($this->doc_direction));
		$this->doc_status=htmlspecialchars(strip_tags($this->doc_status));
		$this->doc_file=htmlspecialchars(strip_tags($this->doc_file));
		$this->doc_type=htmlspecialchars(strip_tags($this->doc_type));
		$this->doc_posted=htmlspecialchars(strip_tags($this->doc_posted));
		$this->cnt_id=htmlspecialchars(strip_tags($this->cnt_id));
		$this->fld_id=htmlspecialchars(strip_tags($this->fld_id));
		$this->arc_id=htmlspecialchars(strip_tags($this->arc_id));
		// Bind Werte an Objekteigenschaften
		$stmt->bindParam(":usr_id", $this->usr_id);
		$stmt->bindParam(":doc_subject", $this->doc_subject);
		$stmt->bindParam(":doc_direction", $this->doc_direction);
		$stmt->bindParam(":doc_status", $this->doc_status);
		$stmt->bindParam(":doc_file", $this->doc_file);
		$stmt->bindParam(":doc_type", $this->doc_type);
		$stmt->bindParam(":doc_posted", $this->doc_posted);
		$stmt->bindParam(":cnt_id", $this->cnt_id);
		$stmt->bindParam(":fld_id", $this->fld_id);
		$stmt->bindParam(":arc_id", $this->arc_id);
		
		// execute the query
		if ($stmt->execute() and $stmt->rowCount()>0) {
			return true;
		}
		return false;
	}
	// Dokumente suchen
	function search($keywords){
		// Select all Query
		$query = "
			SELECT
				doc_id,
				usr_id,
				doc_subject,
				doc_direction,
				doc_status,
				doc_file,
				doc_type,
				doc_posted,
				cnt_id,
				fld_id,
				arc_id 	
			FROM 
				" . $this->table_name . "
			WHERE
				doc_subject LIKE ? OR doc_direction LIKE ? OR doc_status LIKE ?
			ORDER BY doc_posted ASC";
		// Prepare Query Statement
		$stmt = $this->conn->prepare($query);
		// Sanitize
		$keywords=htmlspecialchars(strip_tags($keywords));
		$keywords = "%{$keywords}%";
		// Suchbegriffe binden
		$stmt->bindParam(1, $keywords);
		$stmt->bindParam(2, $keywords);
		$stmt->bindParam(3, $keywords);
		// Execute Query
		$stmt->execute();
		return $stmt;
	}
	// Dokumente in Chargen ausgeben
	public function readPaging($from_record_num, $records_per_page){
		// Select Query
		$query = "
			SELECT
				doc_id,
				usr_id,
				doc_subject,
				doc_direction,
				doc_status,
				doc_file,
				doc_type,
				doc_posted,
				cnt_id,
				fld_id,
				arc_id 	
			FROM
				" . $this->table_name . "
			ORDER BY doc_subject DESC
			LIMIT ?, ?";
		// Prepare Query Statement
		$stmt = $this->conn->prepare($query);
		// Werte binden
		$stmt->bindParam(1, $from_record_num, PDO::PARAM_INT);
		$stmt->bindParam(2, $records_per_page, PDO::PARAM_INT);
		// Query ausführen
		$stmt->execute();
		return $stmt;
	}
	// Anzahl Datensätze ermitteln
	public function count(){
		$query = "SELECT COUNT(*) as total_rows FROM " . $this->table_name . "";
		$stmt = $this->conn->prepare( $query );
		$stmt->execute();
		$row = $stmt->fetch(PDO::FETCH_ASSOC);
		return $row['total_rows'];
	}
}
?>

Für jede Datenbanktabelle existiert eine Klassendefinition, eine controller.php, sowie für jede erforderliche CRUD-Operation auf die Tabelle eine weitere PHP-Datei.

Zurück zum Inhalt

V.Z.docs, Webservices

Tags

Mehr zum Thema

V.Z.docs, Dokumente scannen

V.Z.docs, ein Softwareprojekt