We present the social network (PHP) with Android client utilizing the mobile design in WebView controller. The SQL scripts for data modelling and data validation are presented as well as Visual Basic for Applications (VBA) scripts in Excel workbooks
Introduction
In this tip, we will present to the reader the basics of useful scripting which can be compared to the functionality of utility programs.
The PHP scripts for creating a communication software like social network is also presented along with data model based on MySQL.
VBA scripts accompany the same model for SQL server and other data-driven software.
Background
When doing particular jobs, it's enough to know scripting techniques rather than coding complex tasks.
Thus, we avoid the development of this software using autonomous modules like libraries for doing the programmed data instructions.
Using the Code
Before proceeding to PHP and VBA scripts, it's necessary to learn SQL scripts too.
For our social network, the data model is as follows in MySQL:
CREATE TABLE `connection` (
`id` int(11) NOT NULL,
`usera` int(32) NOT NULL,
`userb` int(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `message` (
`id` int(32) NOT NULL,
`user` int(32) NOT NULL,
`text` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `user` (
`id` int(32) NOT NULL,
`name` varchar(32) NOT NULL,
`password` varchar(32) NOT NULL,
`hash` varchar(32) NOT NULL,
`comment` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `connection`
ADD PRIMARY KEY (`id`),
ADD KEY `connection_usera_index` (`usera`) USING HASH,
ADD KEY `connection_userb_index` (`userb`) USING HASH;
ALTER TABLE `message`
ADD PRIMARY KEY (`id`),
ADD KEY `message_user_index` (`user`) USING HASH;
ALTER TABLE `user`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `user_name_unique_index` (`name`),
ADD UNIQUE KEY `user_hash_unique_index` (`hash`);
ALTER TABLE `connection`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
ALTER TABLE `message`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT;
ALTER TABLE `user`
MODIFY `id` int(32) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
ALTER TABLE `connection`
ADD CONSTRAINT `fk_connection_usera` FOREIGN KEY (`usera`) REFERENCES `user` (`id`),
ADD CONSTRAINT `fk_connection_userb` FOREIGN KEY (`userb`) REFERENCES `user` (`id`);
ALTER TABLE `message`
ADD CONSTRAINT `fk_message_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`);
COMMIT;
The PHP scripts for our social networks can be described in the main page as follows:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Главная страница</title>
<link rel="icon" type="image/jpeg" href="images/favicon.jpg">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<script>
var hash = null, id = null;
</script>
</head>
<body>
<?php include "db.php"; ?>
<?php include "authentication.php"; ?>
<center>
<img src="images/Logo.jpg">
<br>
<br>
<hr>
<div id="profile">
<iframe id="profileFrame" width="100%" height="320"
src="profile.php?hash=<?php echo $hash . "&id=" .
$userInformation["id"]; ?>">
</iframe>
</div>
<div id="message">
<iframe id="messageFrame" width="100%" height="320"
src="message.php?hash=<?php echo $hash; ?>">
</iframe>
</div>
<div id="search">
<iframe id="searchFrame" width="100%" height="320"
src="search.php?hash=<?php echo $hash; ?>">
</iframe>
</div>
</center>
</body>
</html>
Here, the meta value viewport is tuned for responsive page layout. The variable hash stands for security reasons, whereas id is an id of the current user in the same table.
The accompanying Android client is defined as follows in the main activity class:
package com.example.baldar;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.webkit.WebResourceRequest;
import android.webkit.WebView;
import android.webkit.WebViewClient;
import android.widget.Button;
public class MainActivity extends AppCompatActivity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
Button refresh = (Button) findViewById(R.id.refresh);
final WebView myWebView = (WebView) findViewById(R.id.webview);
myWebView.getSettings().setJavaScriptEnabled(true);
myWebView.setWebViewClient(new WebViewClient() {
@Override
public boolean shouldOverrideUrlLoading
(WebView view, WebResourceRequest request) {
view.loadUrl(request.getUrl().toString());
return false;
}
});
myWebView.loadUrl("http://baldar.great-site.net/?i=1");
refresh.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
myWebView.reload();
}
});
}
}
VBA script for generating SQL schema on the Excel work sheet is defined as follows:
Sub Generate_Scripts()
Dim i As Integer
Dim j As Integer
Dim tableName As String
i = 2
j = i
tableName = CStr(Cells(i, 1).Value)
While CStr(Cells(i, 1).Value) <> "END"
If tableName <> CStr(Cells(i, 1).Value) Then
Open tableName & ".sql" For Output As #1
Print #1, "create table " & tableName; "("
For k = j To i - 1
Print #1, " " & CStr(Cells(k, 2).Value) & " " & CStr(Cells(k, 3).Value)
If CStr(Cells(k, 4).Value) = "Y" Then
Print #1, " primary key"
End If
If CStr(Cells(k, 5).Value) <> "" _
And CStr(Cells(k, 6).Value) <> "" Then
Print #1, " references " & CStr(Cells(k, 5).Value) & _
"(" & CStr(Cells(k, 6).Value) & ")"
End If
If k < i - 1 Then
Print #1, " ,"
End If
Next
Print #1, ");"
Close #1
tableName = CStr(Cells(i, 1).Value)
j = i
End If
i = i + 1
Wend
End Sub
The SQL script for gathering statistics for SAP objects is defined as follows:
SELECT A.RNR,
A.NUM_ROWS,
B.START_LOAD,
B.END_LOAD
FROM
(SELECT RNR,
sum(REQ_SELECT) NUM_ROWS
FROM sapsr3.RSMONFACT
WHERE RNR in
(SELECT DISTINCT RNR
FROM sapsr3.RSMONICTAB
WHERE INFOCUBE in ('X_CUBE' )
AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _
BETWEEN (sysdate - 3) AND (sysdate + 1) )
GROUP BY RNR) A
INNER JOIN
(SELECT RNR,
(to_date(min(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) START_LOAD,
(to_date(max(TIMESTAMP), 'YYYYMMDDHH24MISS') + 0.25) END_LOAD
FROM sapsr3.RSMONMESS
WHERE RNR in
(SELECT DISTINCT RNR
FROM sapsr3.RSMONICTAB
WHERE INFOCUBE in ('X_CUBE' )
AND to_date(substr(to_char(TIMESTAMP), 1, 8), 'yyyymmdd') _
BETWEEN (sysdate - 3) _
AND (sysdate + 1) )
GROUP BY RNR) B ON A.RNR = B.RNR
WHERE B.START_DATE BETWEEN (sysdate - 2) AND sysdate
Points of Interest
Thus, we have learned the scripting programming techniques and showed the way which is enough to solve common tasks without using the complex programming with separate module support for data composition and analysis on the example of social network, Android client, SQL and VBA scripts.
History
- 27th October, 2022 - Initial release
- 2nd November, 2022 - Removed links
- 4th November, 2022 - License changed
