SQL-скрипт для генерации базы данных

CREATE GENERATOR genUslPropsKeys;

CREATE GENERATOR genUslProps;

CREATE GENERATOR genPhonesRegions;

CREATE GENERATOR genPhonesStations;

CREATE GENERATOR genPhonesStreets;

CREATE GENERATOR genPhonesBanks;

CREATE GENERATOR genTalksPay;

CREATE GENERATOR genTalks;

CREATE GENERATOR genNach;

CREATE GENERATOR genNachBillings;

CREATE GENERATOR genNachBillDates;

CREATE GENERATOR genNachConstUsl;

CREATE GENERATOR genUslDivisions;

CREATE GENERATOR genUslLgots;

CREATE GENERATOR genUslsKeys;

CREATE GENERATOR genUsls;

CREATE GENERATOR genUslCatKeys;

CREATE GENERATOR genUslCat;

CREATE GENERATOR genPhones;

CREATE GENERATOR genPhonesOwnersKeys;

CREATE GENERATOR genPhonesOwners;

CREATE GENERATOR genSysSettings;

CREATE GENERATOR genPhonesKeys;

CREATE GENERATOR genPlat;

CREATE GENERATOR genPhonesPostStations;

CREATE GENERATOR genSysLog;

CREATE GENERATOR genUslTypes;

CREATE GENERATOR genUslDivisionsKeys;

CREATE DOMAIN CALLTIME_TYPE INTEGER NOT NULL;

CREATE DOMAIN CURR_TYPE FLOAT DEFAULT 0 NOT NULL;

CREATE DOMAIN DATE_TYPE DATE NOT NULL;

CREATE DOMAIN DESCR_TYPE CHAR(32);

CREATE DOMAIN PHONE_TYPE CHAR(7) NOT NULL;

CREATE DOMAIN PROCENT_TYPE FLOAT DEFAULT 100 NOT NULL

CHECK (VALUE BETWEEN 0 AND 300);

CREATE TABLE Nach (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER,

UslSum CURR_TYPE,

NachDate DATE_TYPE,

BillDate DATE_TYPE
);

ALTER TABLE Nach

ADD CONSTRAINT XPKNach PRIMARY KEY (Code);

CREATE TABLE NachBillDates (

Code INTEGER NOT NULL,

BillingDate INTEGER NOT NULL
);

ALTER TABLE NachBillDates

ADD CONSTRAINT XPKBillDates PRIMARY KEY (Code);

CREATE TABLE NachBillings (

Code INTEGER NOT NULL,

Division INTEGER NOT NULL,

Owner INTEGER NOT NULL,

BillDateCode INTEGER NOT NULL
);

ALTER TABLE NachBillings

ADD CONSTRAINT XPKNachBillings PRIMARY KEY (Code);

CREATE TABLE NachConstUsl (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER NOT NULL,

UslSum CURR_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE
);

ALTER TABLE NachConstUsl

ADD CONSTRAINT XPKNachConstUsl PRIMARY KEY (Code);

CREATE TABLE Phones (

Code INTEGER NOT NULL,

Street INTEGER NOT NULL,

Owner INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Comment DESCR_TYPE,

PhoneNmb PHONE_TYPE,

InstallDate DATE_TYPE,

RemoveDate DATE_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE
);

ALTER TABLE Phones

ADD CONSTRAINT XPKPhones PRIMARY KEY (Code);

CREATE TRIGGER Phones_BUH FOR Phones
BEFORE UPDATE POSITION 0
AS
BEGIN

/* Изменение BegDate */
IF (new.BegDate old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */
IF (new.EndDate old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM Phones

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM PhonesOwners

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslCat

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslDivisions

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslDivisions

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslDivisions

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslProps

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslProps

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslProps

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE Usls

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE Usls

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM Usls

WHERE ((BegDate >= new.BegDate) AND (EndDate = new.BegDate) AND (EndDate BufferLength =

(unsigned int)((long)_StubMsg.Buffer - (long)_pRpcMessage->Buffer);

}

static const MIDL_STUB_DESC CommService_StubDesc =

{

(void __RPC_FAR *)& CommService___RpcServerInterface,

MIDL_user_allocate,

MIDL_user_free,

0,

0,

0,

0,

0,

__MIDLTypeFormatString.Format,

0, /* -error bounds_check flag */

0x10001, /* Ndr library version */

0,

0x300000f, /* MIDL Version 3.0.15 */

0,

0,

0, /* Reserved1 */

0, /* Reserved2 */

0, /* Reserved3 */

0, /* Reserved4 */

0 /* Reserved5 */

};

static RPC_DISPATCH_FUNCTION CommService_table[] =

{

CommService_RefreshIniProps,

0

};
RPC_DISPATCH_TABLE CommService_DispatchTable =

{

1,

CommService_table

};

#if !defined(__RPC_WIN32__)
#error Invalid build platform for this stub.
#endif

static const MIDL_PROC_FORMAT_STRING __MIDLProcFormatString =

{

0,

{

0x5b, /* FC_END */

0x5c, /* FC_PAD */

0x0

}

};

static const MIDL_TYPE_FORMAT_STRING __MIDLTypeFormatString =

{

0,

{

0x0

}

};

ShedulerServ.c
/**********************************************************//* Task
Sheduler
/**********************************************************/

#include
#include "ShedulerServ.h"
#include "CommonConfig.h"
#include "EventLog.h"

#define SLEEP_INTERVAL 5000
#define ACTIVATE_INTERVAL 60000

BOOL TerminateSheduling = FALSE;
DWORD ShedulingThread;

DWORD WINAPI TaskProc(LPVOID ThreadParm); void AnalyseTaskTable();
DWORD GetTimeInMins();

DWORD WINAPI ShedulingProc(LPVOID ThreadParm)
{ long i = 0;

while (!TerminateSheduling) {

if ((i += SLEEP_INTERVAL) >= ACTIVATE_INTERVAL) {

i = 0; if (TaskCount == 0) UpdateVariables();

AnalyseTaskTable();

}

Sleep(SLEEP_INTERVAL);

} return 0;
}

DWORD WINAPI TaskProc(LPVOID ThreadParm)
{

HINSTANCE hLib;

FARPROC hProc;

InterlockedIncrement(&TaskCount);

WaitForSingleObject(hConfigMutex, INFINITE);

MessageBeep(1); if (hLib = LoadLibrary((char *)&((struct TASKENTRY*)ThreadParm)-
>DllName)) {

if (((struct TASKENTRY*)ThreadParm)->TermProc =
GetProcAddress(hLib, (LPCSTR)TaskProcName))

{

((struct TASKENTRY*)ThreadParm)->Active = TRUE;

ReleaseMutex(hConfigMutex);

if (hProc = GetProcAddress(hLib, (LPCSTR)TaskProcName)) { hProc();

__try {

WaitForSingleObject(hConfigMutex, INFINITE);

((struct TASKENTRY*)ThreadParm)->Active =
FALSE;

CloseHandle((HANDLE)((struct
TASKENTRY*)ThreadParm)->TaskThread);

}

__finally {

ReleaseMutex(hConfigMutex);

}

} else LogEvent(EVENTLOG_ERROR_TYPE, "Error Getting
Procedure Address");

} else LogEvent(EVENTLOG_ERROR_TYPE, "Error Getting
TermProc Address");

} else {

ReleaseMutex(hConfigMutex);

LogEvent(EVENTLOG_ERROR_TYPE, "Error Loading Library");

}

InterlockedDecrement(&TaskCount);

return 0;
}

void AnalyseTaskTable()
{

DWORD CurrTime; int i;

CurrTime = GetTimeInMins();

__try {

WaitForSingleObject(hConfigMutex, INFINITE);

for (i = 0; i < EntryCount; i++) { if ((TaskTable[i].ExecTime == CurrTime) &&

(!TaskTable[i].Active)) {

CreateThread(NULL, 0, TaskProc, &TaskTable[i],
0, &TaskTable[i].TaskThread);

}

}

}

__finally {

ReleaseMutex(hConfigMutex);

}
}

DWORD GetTimeInMins()
{

SYSTEMTIME SysTime;

GetLocalTime(&SysTime);

return SysTime.wHour*60+SysTime.wMinute;
}

void TerminateTasks()
{ int i;

DWORD TaskIndex = 0;

HANDLE Handles[TASK_COUNT];

for (i = 0; i < EntryCount; i++) { if (TaskTable[i].Active) {

TaskTable[i].TermProc();

Handles[TaskIndex++] = (HANDLE)TaskTable[i].TaskThread;

}

}

WaitForMultipleObjects(TaskIndex, Handles, TRUE, INFINITE);
}
Comm.h
/* this ALWAYS GENERATED file contains the definitions for the interfaces
*/

/* File created by MIDL compiler version 3.00.15 */
/* at Tue Jun 03 11:35:46 1997
*/
/* Compiler settings for comm.idl:

Os, W1, Zp8, env=Win32, ms_ext, c_ext, oldnames error checks: none
*/
//@@MIDL_FILE_HEADING( )
#include "rpc.h"
#include "rpcndr.h"

#ifndef __comm_h__
#define __comm_h__

#ifdef __cplusplus extern "C"{
#endif

/* Forward Declarations */

void __RPC_FAR * __RPC_USER MIDL_user_allocate(size_t); void __RPC_USER MIDL_user_free( void __RPC_FAR * );

#ifndef __CommService_INTERFACE_DEFINED__
#define __CommService_INTERFACE_DEFINED__

/****************************************
* Generated header for interface: CommService
* at Tue Jun 03 11:35:46 1997
* using MIDL 3.00.15
****************************************/
/* [implicit_handle][version][uuid] */

void RefreshIniProps( void);

extern handle_t CommServ_IfHandle;

extern RPC_IF_HANDLE CommService_ClientIfHandle; extern RPC_IF_HANDLE CommService_ServerIfHandle;
#endif /* __CommService_INTERFACE_DEFINED__ */

/* Additional Prototypes for ALL interfaces */

/* end of Additional Prototypes */

#ifdef __cplusplus
}
#endif

#endif

DoService.h
/**************************************************************/
/* DoService Module - implementation of initialisation other
/* tasks */
/* */
/* Copuright (c) 1997 by Malkov O.V. */
/* JSC "Svyazinform" RM */
/**************************************************************/

#ifndef __DOSERVICE
#define __DESERVICE

void ServerProcess();
DWORD ServerInit(); void ServerDoPause(); void ServerDoStop(); void ServerDoContinue();

#endif

CommonConfig.h
/**************** Server Engine Header File *******************/

#ifndef __COMMON_CONFIG
#define __COMMON_CONFIG

#define TASK_COUNT 10

#include "../RegistryConfig.h"

extern DWORD ConfigWatchingThread;

extern HANDLE hConfigMutex; extern HANDLE hTaskMutex;

extern unsigned char *pszProtocolSequence; extern unsigned char *pszSecurity; extern unsigned char *pszEndpoint; extern unsigned int cMinCalls; extern unsigned int cMaxCalls; extern unsigned int fDontWait;

extern struct TASKENTRY TaskTable[TASK_COUNT]; extern int EntryCount; extern DWORD TaskThreads[TASK_COUNT]; extern int TaskCount;

DWORD WINAPI CommonConfigWatcher(LPVOID ThreadParm); void UpdateVariables();

#endif

EventLog.h

#ifndef __EVENT_LOG
#define __EVENT_LOG

void LogEvent(WORD EventType, LPSTR EventMsg);

#endif

ClientHandler.h

#ifndef __CLIENT_HANDLER
#define __CLIENT_HANDLER

extern DWORD ClientHandlingThread;

DWORD WINAPI RPCClientHandling(LPVOID ThreadParm); void RefreshIniProps();

#endif

ShedulerServ.h

#ifndef __SHEDULING_SERVICE
#define __SHEDULING_SERVICE

#define TaskProcName "TaskProc"

extern BOOL TerminateSheduling; extern DWORD ShedulingThread;

DWORD WINAPI ShedulingProc(LPVOID ThreadParm); void TerminateTasks();

#endif

RegistryConfig.h

#ifndef __REGISTRY_CONFIG
#define __REGISTRY_CONFIG

#define REGISTRY_TASKS_PATH "SOFTWARESvyazinformCommServiceTasks"

struct TASKENTRY
{

DWORD ExecTime; char DllName[256];

FARPROC TermProc;

DWORD TaskThread;

BOOL Active;
};

#endif

Comm.idl
/* IDL File */

[ uuid (4a25d2e0-6703-11d0-8927-00a02413850e), version(1.0)
] interface CommService
{ void RefreshIniProps();
}

Comm.acf
/* acf file for TimeInclude Service (RPC) */

[ implicit_handle(handle_t CommServ_IfHandle)
]interface CommService
{
}

Приложение 4

Исходные тексты программы установки коммуникационного сервиса

#include
#include

void RegEventSource();

VOID _CRTAPI1 main(void)
{

LPCTSTR lpszBinaryPathName =

TEXT("c:ibserverbinCommServ.exe");

SC_HANDLE schSCManager;
SC_HANDLE schService;
/* Open a handle to the SC Manager database. */

schSCManager = OpenSCManager(

NULL, /* local machine */

NULL, /* ServicesActive database */

SC_MANAGER_ALL_ACCESS); /* full access rights */

if (schSCManager == NULL) { printf("nError opening Service Manager.n"); return;
}

schService = CreateService( schSCManager, /* SCManager database */

TEXT("CommServ"), /* name of service */

TEXT("JSC "SvjazInform" Communication Service"), /* service name to display */

SERVICE_ALL_ACCESS, /* desired access */

SERVICE_WIN32_OWN_PROCESS, /* service type */

SERVICE_DEMAND_START, /* start type */

SERVICE_ERROR_NORMAL, /* error control type */ lpszBinaryPathName, /* service's binary */

NULL, /* no load ordering group */

NULL, /* no tag identifier */

NULL, /* no dependencies */

NULL, /* LocalSystem account */

NULL); /* no password */

if (schService == NULL) { printf("nFailed to create service!n");
} else printf("CreateService SUCCESSn");

CloseServiceHandle(schService);
CloseServiceHandle(schSCManager);

RegEventSource();

}

void RegEventSource()
{

HKEY hk;

DWORD dwData;

UCHAR szBuf[80];

if (RegCreateKey(HKEY_LOCAL_MACHINE,
"SYSTEMCurrentControlSetServices

EventLogApplicationCommServ", &hk)) {

printf("could not create registry key"); return;

}

/* Set the Event ID message-file name. */

strcpy(szBuf, "c:ibserverbinCommServ.exe");

/* Add the Event ID message-file name to the subkey. */

if (RegSetValueEx(hk, /* subkey handle */

"EventMessageFile", /* value name */

0, /* must be zero */

REG_EXPAND_SZ, /* value type */

(LPBYTE) szBuf, /* address of value data */ strlen(szBuf) + 1)) /* length of value data*/

{ printf("could not set event message file"); return;

}

/* Set the supported types flags. */

dwData = EVENTLOG_ERROR_TYPE | EVENTLOG_WARNING_TYPE |

EVENTLOG_INFORMATION_TYPE;

if (RegSetValueEx(hk, /* subkey handle */

"TypesSupported", /* value name */

0, /* must be zero */

REG_DWORD, /* value type */

(LPBYTE) &dwData, /* address of value data*/ sizeof(DWORD))) /* length of value data */

{ printf("could not set supported types"); return;

}

RegCloseKey(hk);
}

Приложение 5

Исходные тексты программы удаления коммуникационного сервиса

#include
#include

void CleanRegistry();

VOID _CRTAPI1 main(void)
{

SC_HANDLE schSCManager;
SC_HANDLE schService;
/* Open a handle to the SC Manager database. */

schSCManager = OpenSCManager(

NULL, /* local machine */

NULL, /* ServicesActive database */

SC_MANAGER_ALL_ACCESS); /* full access rights */

if (schSCManager == NULL) { printf("nError opening Service Manager.n"); return;
}

schService = OpenService( schSCManager, /* SCManager database */

TEXT("CommServ"), /* name of service */

DELETE); /* only need DELETE access */

if (! DeleteService(schService) ) printf("nFailed to Delete service!n"); else printf("DeleteService SUCCESSn");

CloseServiceHandle(schService);
CloseServiceHandle(schSCManager);
}

void CleanRegistry()
{ if (RegDeleteKey(HKEY_LOCAL_MACHINE,
"SYSTEMCurrentControlSetServices

EventLogApplicationCommServ"))

{ printf("nError Cleaning Registry");

} else { printf("nCleaning Registry SUCCESS");

} return;
}

Приложение 6

Структуры баз данных

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14