the bind value at index 1 is null

博客指出数据库查询报错原因,在数据库中查询name为name1的数据时,name1未赋值为空,导致查询出现问题。

报错原因: 在数据库中查name为name1的数据 但是name1忘了赋值,为空

# influxd --help WARN[0000]log.go:228 gosnowflake.(*defaultLogger).Warn DBUS_SESSION_BUS_ADDRESS envvar looks to be not set, this can lead to runaway dbus-daemon processes. To avoid this, set envvar DBUS_SESSION_BUS_ADDRESS=$XDG_RUNTIME_DIR/bus (if it exists) or DBUS_SESSION_BUS_ADDRESS=/dev/null. Start up the daemon configured with flags/env vars/config file. The order of precedence for config options are as follows (1 highest, 3 lowest): 1. flags 2. env vars 3. config file A config file can be provided via the INFLUXD_CONFIG_PATH env var. If a file is not provided via an env var, influxd will look in the current directory for a config.{json|toml|yaml|yml} file. If one does not exist, then it will continue unchanged. Usage: influxd [flags] influxd [command] Available Commands: downgrade Downgrade metadata schema used by influxd to match the expectations of an older release help Help about any command inspect Commands for inspecting on-disk database data recovery Commands used to recover / regenerate operator access to the DB run Start the influxd server upgrade Upgrade a 1.x version of InfluxDB version Print the influxd server version Flags: --assets-path string override default assets by serving from a specific directory (developer mode) --bolt-path string path to boltdb database (default "/root/.influxdbv2/influxd.bolt") --e2e-testing add /debug/flush endpoint to clear stores; used for end-to-end tests --engine-path string path to persistent engine files (default "/root/.influxdbv2/engine") --feature-flags stringToString feature flag overrides (default []) --flux-log-enabled enables detailed logging for flux queries --hardening-enabled enable hardening options (disallow private IPs within flux and templates HTTP requests; disable file URLs in templates) -h, --help help for influxd --http-bind-address string bind address for the REST HTTP API (default ":8086") --http-idle-timeout duration max duration the server should keep established connections alive while waiting for new requests. Set to 0 for no timeout (default 3m0s) --http-read-header-timeout duration max duration the server should spend trying to read HTTP headers for new requests. Set to 0 for no timeout (default 10s) --http-read-timeout duration max duration the server should spend trying to read the entirety of new requests. Set to 0 for no timeout --http-write-timeout duration max duration the server should spend on processing+responding to requests. Set to 0 for no timeout --influxql-max-select-buckets int The maximum number of group by time bucket a SELECT can create. A value of zero will max the maximum number of buckets unlimited. --influxql-max-select-point int The maximum number of points a SELECT can process. A value of 0 will make the maximum point count unlimited. This will only be checked every second so queries will not be aborted immediately when hitting the limit. --influxql-max-select-series int The maximum number of series a SELECT can run. A value of 0 will make the maximum series count unlimited. --instance-id string add an instance id for replications to prevent collisions and allow querying by edge node --log-level Log-Level supported log levels are debug, info, and error (default info) --metrics-disabled Don't expose metrics over HTTP at /metrics --no-tasks disables the task scheduler --overwrite-pid-file overwrite PID file if it already exists instead of exiting --pid-file string write process ID to a file --pprof-disabled Don't expose debugging information over HTTP at /debug/pprof --query-concurrency int32 the number of queries that are allowed to execute concurrently. Set to 0 to allow an unlimited number of concurrent queries (default 1024) --query-initial-memory-bytes int the initial number of bytes allocated for a query when it is started. If this is unset, then query-memory-bytes will be used --query-max-memory-bytes int the maximum amount of memory used for queries. Can only be set when query-concurrency is limited. If this is unset, then this number is query-concurrency * query-memory-bytes --query-memory-bytes int maximum number of bytes a query is allowed to use at any given time. This must be greater or equal to query-initial-memory-bytes --query-queue-size int32 the number of queries that are allowed to be awaiting execution before new queries are rejected. Must be > 0 if query-concurrency is not unlimited (default 1024) --reporting-disabled disable sending telemetry data to https://telemetry.influxdata.com every 8 hours --secret-store string data store for secrets (bolt or vault) (default "bolt") --session-length int ttl in minutes for newly created sessions (default 60) --session-renew-disabled disables automatically extending session ttl on request --sqlite-path string path to sqlite database. if not set, sqlite database will be stored in the bolt-path directory as "influxd.sqlite". --storage-cache-max-memory-size Size The maximum size a shard's cache can reach before it starts rejecting writes. (default 1.0 GiB) --storage-cache-snapshot-memory-size Size The size at which the engine will snapshot the cache and write it to a TSM file, freeing up memory. (default 25 MiB) --storage-cache-snapshot-write-cold-duration Duration The length of time at which the engine will snapshot the cache and write it to a new TSM file if the shard hasn't received writes or deletes. (default 10m0s) --storage-compact-full-write-cold-duration Duration The duration at which the engine will compact all TSM files in a shard if it hasn't received a write or delete. (default 4h0m0s) --storage-compact-throughput-burst Size The rate limit in bytes per second that we will allow TSM compactions to write to disk. (default 48 MiB) --storage-max-concurrent-compactions int The maximum number of concurrent full and level compactions that can run at one time. A value of 0 results in 50% of runtime.GOMAXPROCS(0) used at runtime. Any number greater than 0 limits compactions to that value. This setting does not apply to cache snapshotting. --storage-max-index-log-file-size Size The threshold, in bytes, when an index write-ahead log file will compact into an index file. Lower sizes will cause log files to be compacted more quickly and result in lower heap usage at the expense of write throughput. (default 1.0 MiB) --storage-no-validate-field-size Skip field-size validation on incoming writes. --storage-retention-check-interval Duration The interval of time when retention policy enforcement checks run. (default 30m0s) --storage-series-file-max-concurrent-snapshot-compactions int The maximum number of concurrent snapshot compactions that can be running at one time across all series partitions in a database. --storage-series-id-set-cache-size int The size of the internal cache used in the TSI index to store previously calculated series results. --storage-shard-precreator-advance-period Duration The default period ahead of the endtime of a shard group that its successor group is created. (default 30m0s) --storage-shard-precreator-check-interval Duration The interval of time when the check to pre-create new shards runs. (default 10m0s) --storage-tsm-use-madv-willneed Controls whether we hint to the kernel that we intend to page in mmap'd sections of TSM files. --storage-validate-keys Validates incoming writes to ensure keys only have valid unicode characters. --storage-wal-flush-on-shutdown Flushes and clears the WAL on shutdown --storage-wal-fsync-delay Duration The amount of time that a write will wait before fsyncing. A duration greater than 0 can be used to batch up multiple fsync calls. This is useful for slower disks or when WAL write contention is seen. (default 0s) --storage-wal-max-concurrent-writes int The max number of writes that will attempt to write to the WAL at a time. (default <nprocs> * 2) --storage-wal-max-write-delay storage-wal-max-concurrent-writes The max amount of time a write will wait when the WAL already has storage-wal-max-concurrent-writes active writes. Set to 0 to disable the timeout. (default 10m0s) --storage-write-timeout duration The max amount of time the engine will spend completing a write request before cancelling with a timeout. (default 10s) --store string backing store for REST resources (disk or memory) (default "disk") --strong-passwords enable password strength enforcement --template-file-urls-disabled disable template file URLs --testing-always-allow-setup ensures the /api/v2/setup endpoint always returns true to allow onboarding --tls-cert string TLS certificate for HTTPs --tls-key string TLS key for HTTPs --tls-min-version string Minimum accepted TLS version (default "1.2") --tls-strict-ciphers Restrict accept ciphers to: ECDHE_ECDSA_WITH_AES_128_GCM_SHA256, ECDHE_RSA_WITH_AES_128_GCM_SHA256, ECDHE_ECDSA_WITH_AES_256_GCM_SHA384, ECDHE_RSA_WITH_AES_256_GCM_SHA384, ECDHE_ECDSA_WITH_CHACHA20_POLY1305, ECDHE_RSA_WITH_CHACHA20_POLY1305 --tracing-type string supported tracing types are log, jaeger --ui-disabled Disable the InfluxDB UI --vault-addr string address of the Vault server expressed as a URL and port, for example: https://127.0.0.1:8200/. --vault-cacert string path to a PEM-encoded CA certificate file on the local disk. This file is used to verify the Vault server's SSL certificate. This environment variable takes precedence over VAULT_CAPATH. --vault-capath string path to a directory of PEM-encoded CA certificate files on the local disk. These certificates are used to verify the Vault server's SSL certificate. --vault-client-cert string path to a PEM-encoded client certificate on the local disk. This file is used for TLS communication with the Vault server. --vault-client-key string path to an unencrypted, PEM-encoded private key on disk which corresponds to the matching client certificate. --vault-client-timeout duration timeout variable. The default value is 60s. --vault-max-retries int maximum number of retries when a 5xx error code is encountered. The default is 2, for three total attempts. Set this to 0 or less to disable retrying. --vault-skip-verify do not verify Vault's presented certificate before communicating with it. Setting this variable is not recommended and voids Vault's security model. --vault-tls-server-name string name to use as the SNI host when connecting via TLS. --vault-token string vault authentication token Use "influxd [command] --help" for more information about a command. 翻译并解析
最新发布
11-12
@Slf4j @Component public class BidirectionRequestHeaderProcessor extends AuthRequiredHeaderProcessor<BidirectionRequestChannel, BidirectionRequestChannelInformation> { private ChannelManager channelManager; private ContainerManager containerManager; private CloudAccessChannelHelper cloudAccessChannelHelper; @Autowired public BidirectionRequestHeaderProcessor(AuthClient authClient, BidirectionRequestHeaderProcessorProp prop, ChannelManager channelManager, ContainerManager containerManager, CloudAccessChannelHelper cloudAccessChannelHelper) { super(authClient, prop); this.channelManager = channelManager; this.containerManager = containerManager; this.cloudAccessChannelHelper = cloudAccessChannelHelper; } @Override protected BidirectionRequestChannel doBeforeAddExecutor(BidirectionRequestChannel bidirectionChannel) { if (log.isDebugEnabled()) { log.debug("[sid:{}] Disable auto read of Bidirection channel:", bidirectionChannel.getSid()); } bidirectionChannel.disableAutoRead(); return bidirectionChannel; } @Override public void doHeaderProcess(BidirectionRequestChannel bidirectionChannel) throws Exception { // Add into channel manager String channelId = bidirectionChannel.getChannelId(); channelManager.addRequestChannel(channelId, bidirectionChannel); // Add life time event if necessary. As the bidirection has sink channel characteristic, the life time event // should be created before channel adding into container. if (bidirectionChannel instanceof ILifeTimeControl) { ((ILifeTimeControl) bidirectionChannel).createLifeTimeEvent(ILifeTimeControl.LifeTimeType.CON_IDLE); } // Obtain the container. BidirectionContainer container = containerManager.obtainBidirectionContainer(bidirectionChannel); if (container == null) { log.info("[sid:{}] Bidirection channel is kicked off by others when obtaining container.", bidirectionChannel.getSid()); bidirectionChannel.sendHttpResponseAndClose(503, "Kicked off", RelayConsts.CloseReason.BIDIRECTION_KICKED_OFF); return; } if (bidirectionChannel instanceof CloudAccessBidirectionRequestChannel) { doCloudAccessHeaderProcess(bidirectionChannel, container); } // Initialize the container. if (log.isDebugEnabled()) { log.debug("[sid:{}] Bidirection channel obtain container successfully: container={}", bidirectionChannel.getSid(), container.toString()); } boolean hasPeerChannel = bidirectionChannel.initContainer(container); if (hasPeerChannel) { if (log.isDebugEnabled()) { log.debug("[sid:{}] Initialize Bidirection channel after container initialization", bidirectionChannel.getSid()); } bidirectionChannel.startDataAcceptanceAfterSinkChannelAccess(); } } public void doCloudAccessHeaderProcess(final BidirectionRequestChannel bidirectionChannel, BidirectionContainer container) throws Exception { RequestChannel cloudAccessChannel = cloudAccessChannelHelper .createCloudAccessChannel(bidirectionChannel.getBindingId(), bidirectionChannel.getInformation().getOrignalRequest(), bidirectionChannel.getNettyChannel()); if (cloudAccessChannel instanceof CloudAccessBidirectionRequestChannel) { CloudAccessBidirectionRequestChannel cloudRelayChannel = (CloudAccessBidirectionRequestChannel) cloudAccessChannel; channelManager.addRequestChannel(cloudRelayChannel.getChannelId(), cloudRelayChannel); cloudRelayChannel.createLifeTimeEvent(ILifeTimeControl.LifeTimeType.CON_IDLE); containerManager.obtainBidirectionContainer(cloudRelayChannel); cloudRelayChannel.initContainer(container); } else { log.error("failed to create cloud access channel for {}", bidirectionChannel.getBindingId()); } } } @Slf4j @Component public class PassthroughGetRequestDataProcessor extends AbstractProcessor { private ChannelManager channelManager; @Autowired public PassthroughGetRequestDataProcessor(ChannelManager channelManager) { this.channelManager = channelManager; } @Override protected PassthroughGetRequestChannel doProcess(PassthroughGetRequestChannel passthroughGetChannel) throws Exception { String bindingId = passthroughGetChannel.getBindingId(); PostRequestChannel postChannel = (PostRequestChannel) channelManager.getRequestChannel(bindingId); if (postChannel == null) { log.info("[sid:{}] POST channel is missing when passthrough GET data arrives.", passthroughGetChannel.getSid()); return passthroughGetChannel; } // Start Get channel transmission: only for 1.3 multiple mapping request passthroughGetChannel.startTransmission(postChannel); List<IHttpData> dataList = passthroughGetChannel.getDataList(); IHttpResponseGenerator generator = postChannel.getSuccessResponseGenerator(); while (!dataList.isEmpty()) { IHttpData data = dataList.remove(0); if (generator != null && !data.isLastData()) { postChannel.sendData(generator.getHttpData(data)); } else { // Maybe POST channel is Version 1.2, this should not happen. data.release(); } } return passthroughGetChannel; } @Override public void exceptionCaught(PassthroughGetRequestChannel passthroughGetChannel, Throwable cause) { if (cause instanceof IllegalArgumentException || cause instanceof IllegalRequestException) { log.error("[sid:{}] Illegal request data:", passthroughGetChannel.getSid(), cause); passthroughGetChannel.close(RelayConsts.CloseReason.ILLEGAL_REQUEST_DATA_FORMAT); } else { log.error("[sid:{}] PassthroughGetRequestDataProcessor failed:", passthroughGetChannel.getSid(), cause); passthroughGetChannel.close(RelayConsts.CloseReason.SERVER_INTERNAL_ERROR); } } } @Slf4j @Component public class BufferedRequestDataProcessor extends RelayPostRequestDataProcessor { @Override protected DataProcessState processData(BufferedPostRequestChannel bufferedPostChannel, DataProcessState dataProcessState, IHttpData data) { if (dataProcessState == DataProcessState.TRANSMIT_DIRECT) { return super.transmitDataDirectly(bufferedPostChannel, DataProcessState.TRANSMIT_DIRECT, data); } else if (dataProcessState == DataProcessState.WANT_BUFFERED_DATA) { if (log.isDebugEnabled()) { log.debug("[sid:{}] Process buffered data: index={}", bufferedPostChannel.getSid(), data.getIndex()); } // store buffered data int left = bufferedPostChannel.addBufferedHttpData(data); if (left > 0) { // transmit buffered data to bound channels bufferedPostChannel.transmitData(data); // keep state return DataProcessState.WANT_BUFFERED_DATA; } else if (left == 0) { // transmit buffered data to bound channels bufferedPostChannel.transmitData(data); // change data process stat to DataProcessStat. return bufferedPostChannel.changeToNextState(); } else { // This will not happen super.transmitDataDirectly(bufferedPostChannel, DataProcessState.TRANSMIT_DIRECT, data); return bufferedPostChannel.changeToNextState(); } } else if (dataProcessState == DataProcessState.WANT_RESPONSE_MOULD) { return super.processHttpResponseMould(bufferedPostChannel, data); } else { // Use direct transmission as default action. This case should not happen. return super.transmitDataDirectly(bufferedPostChannel, DataProcessState.TRANSMIT_DIRECT, data); } } } @Slf4j @Component(“relayPostRequestDataProcessor”) public class RelayPostRequestDataProcessor extends PostRequestDataProcessor { @Override protected DataProcessState processData(T postChannel, DataProcessState dataProcessState, IHttpData data) { if (dataProcessState == DataProcessState.TRANSMIT_DIRECT) { return super.transmitDataDirectly(postChannel, DataProcessState.TRANSMIT_DIRECT, data); } else if (dataProcessState == DataProcessState.WANT_RESPONSE_MOULD) { return processHttpResponseMould(postChannel, data); } else { // Use direct transmission as default action. This case should not happen. return super.transmitDataDirectly(postChannel, DataProcessState.TRANSMIT_DIRECT, data); } } /** * Parse the response mould into response header, set it into POST request channel and transmit it to all GET * request channels related with the POST request channel. * * @param postChannel The POST request channel which received the response mould data. * @param data The response mould data */ protected DataProcessState processHttpResponseMould(T postChannel, IHttpData data) throws IllegalRequestException { log.debug("[sid:{}] Process first data: index={}", postChannel.getSid(), data.getIndex()); try { String content = null; if (data instanceof MultipartHttpData) { content = ((MultipartHttpData) data).addedContents().toString(data.getContentCharset()); } else { content = data.toString(); } HttpResponse httpResponse = parseHttpResponse(content); // If the Transfer-Encoding: chunked is set, do not modified as Chunked is used to measure the message // length of the HTTP response even if the Content-Length is set at the same time. if (!HttpUtil.isTransferEncodingChunked(httpResponse)) { String type = postChannel.getInformation().getParams().get(RelayConsts.ParamKey.TYPE); if (RelayConsts.Type.FILE.equals(type)) { // If the type is file, the Content-Length field in response should be the file size and multipart // should not be used. if (postChannel.getInformation().getContentType().isMultiPart() && httpResponse.headers().contains(HttpHeaderNames.CONTENT_LENGTH)) { throw new IllegalRequestException( "file type with multipart package should use Transfer-Encoding: chunked."); } } else { // If the type is other value, only when the Content-Length field in response does not exist or is // negtive, new Content-Length will be set. if (HttpUtil.getContentLength(httpResponse, -1L) < 0) { long contentLength = HttpUtil.getContentLength(postChannel.getInformation().getOrignalRequest(), -1L); httpResponse.headers().set(RelayConsts.HttpNames.CONTENT_LENGTH, contentLength > 0 ? contentLength : Long.MAX_VALUE); } } } // if status is 429, it shows device has too many relay connections at the same time // if (postChannel.hasSegmenter() && // httpResponse.status().code() == HttpResponseStatus.TOO_MANY_REQUESTS.code()) { // postChannel.stopSegmenterWhenCanNotGetVideoStream(RelayConsts.SegmenterStopReason.RELAY_CONNECTION_EXCEEDED); // } postChannel.setResponseHeader(httpResponse); postChannel.transmitHttpResponse(httpResponse); return postChannel.changeToNextState(); } finally { data.release(); } } /** * Parse response header from {@link String} format to {@link HttpResponse} format. * * @param stringValue {@link String} format of response header. * * @return {@link HttpResponse} format of response header. */ public HttpResponse parseHttpResponse(String stringValue) throws IllegalRequestException { String[] parts = stringValue.split("\r\n", 2); if (parts.length != 2 || parts[1].isEmpty()) { throw new IllegalRequestException("Invalid first data as a response header: " + stringValue); } String[] statusLine = parts[0].split(" ", 3); if (statusLine.length != 3 || statusLine[1].isEmpty() || statusLine[2].isEmpty()) { throw new IllegalRequestException("Invalid first data with an invalid response line: " + parts[0]); } HttpResponse httpResponse = null; try { HttpResponseStatus status = new SpecificCodeHttpResponseStatus(statusLine[1], statusLine[2]); httpResponse = new DefaultHttpResponse(HttpVersion.HTTP_1_1, status); } catch (NumberFormatException e) { throw new IllegalRequestException("Invalid first data with an unresovled status code: " + parts[0]); } String[] entries = parts[1].split("\r\n"); for (String entry : entries) { String[] content = entry.split(":", 2); if (content.length != 2 || content[1].isEmpty()) { throw new IllegalRequestException("Invalid first data with an unresovled header: " + entry); } String name = content[0].trim(); String value = content[1].trim(); if (name.isEmpty()) { throw new IllegalRequestException("Invalid first data with an empty key of header:" + entry); } httpResponse.headers().add(name, value); } // Netty channel of GET could not be reused. httpResponse.headers().set(HttpHeaderNames.CONNECTION, HttpHeaderValues.CLOSE); httpResponse.headers().set(HttpHeaderNames.PRAGMA, HttpHeaderValues.NO_CACHE); httpResponse.headers().set(HttpHeaderNames.CACHE_CONTROL, HttpHeaderValues.NO_CACHE); return httpResponse; } } @Slf4j @Component(“postRequestDataProcessor”) public class PostRequestDataProcessor extends AbstractProcessor { @Override protected T doProcess(T sourceChannel) throws IllegalRequestException { DataProcessState dataProcessStat = sourceChannel.getDataProcessState(); List<IHttpData> dataList = sourceChannel.getDataList(); while (!dataList.isEmpty()) { IHttpData data = dataList.remove(0); dataProcessStat = processData(sourceChannel, dataProcessStat, data); } return sourceChannel; } protected DataProcessState processData(T sourceChannel, DataProcessState dataProcessState, IHttpData data) { return transmitDataDirectly(sourceChannel, dataProcessState, data); } protected DataProcessState transmitDataDirectly(T sourceChannel, DataProcessState dataProcessStat, IHttpData data) { assert dataProcessStat == DataProcessState.TRANSMIT_DIRECT; // Transmit the data sourceChannel.transmitData(data); // Release data original reference data.release(); return DataProcessState.TRANSMIT_DIRECT; } @Override public void exceptionCaught(T sourceChannel, Throwable cause) { if (cause instanceof IllegalArgumentException || cause instanceof IllegalRequestException) { log.error("[sid:{}] Illegal request data format:", sourceChannel.getSid(), cause); sourceChannel.close(RelayConsts.CloseReason.ILLEGAL_REQUEST_DATA_FORMAT); } else { log.error("[sid:{}] PostRequestDataProcessor failed:", sourceChannel.getSid(), cause); sourceChannel.close(RelayConsts.CloseReason.SERVER_INTERNAL_ERROR); } } } @Slf4j public class DefaultGetRequestChannel extends GetRequestChannel implements ILifeTimeControl, IHeartBeatControl { private static final RuntimeException HEART_BEAT_TIMEOUT_EXCEPTION = new RuntimeException("Heart beat timeout"); protected ILifeTimeControl lifeTimeController; protected AtomicLong closedTimeStamp; public DefaultGetRequestChannel(String channelId, Channel nettyChannel, String bindingId, int basicLifeTime, GetRequestChannelInformation information) { super(channelId, nettyChannel, bindingId, information); this.lifeTimeController = buildLifeTimeController(nettyChannel, basicLifeTime); this.closedTimeStamp = new AtomicLong(System.currentTimeMillis() + IHeartBeatControl.PROLONGED_TIME_MS); } protected ILifeTimeControl buildLifeTimeController(Channel nettyChannel, int basicLifeTime) { IEventCallBack callback = new IEventCallBack() { @Override public void handleEvent() { if (log.isDebugEnabled()) { log.debug("[sid:{}] Channel is closing as timeout: channelType={}", getSid(), DefaultGetRequestChannel.this.getClass().getSimpleName()); } sendHttpResponseAndClose(HttpResponseStatus.NOT_FOUND, RelayConsts.CloseReason.GET_LIFETIME_EXPIRE); } }; return new LifeTimeController(getSid(), ILifeTimeControl.GET_IDLE_TIME_S, basicLifeTime, EventTimer.getLifeTimeEventKey(nettyChannel), getClass().getSimpleName(), callback); } @Override protected ISinkHandler buildBasicSinkHandler(GetRequestChannelInformation information) { assert !information.isFFmpegChannel(); if (isAudio(information)) { return new AudioSinkHandler(this); } else { return new DefaultSinkHandler<>(this); } } private boolean isAudio(GetRequestChannelInformation information) { String type = information.getParams().get(RelayConsts.ParamKey.TYPE); switch (type) { case RelayConsts.Type.AUDIO: return true; case RelayConsts.Type.NVR: case RelayConsts.Type.SMART_NVR: String resolution = information.getParams().get(RelayConsts.ParamKey.RESOLUTION); if (resolution == null) { return false; } switch (resolution) { case RelayConsts.AudioResolution.AAC: case RelayConsts.AudioResolution.MP2: case RelayConsts.AudioResolution.PCM: return true; case RelayConsts.VideoResolution.HD: case RelayConsts.VideoResolution.QVGA: case RelayConsts.VideoResolution.VGA: default: return false; } default: return false; } } @Override public long getClosedTimeStamp() { return closedTimeStamp.get(); } @Override public void setClosedTimeStamp(long timeStamp) { closedTimeStamp.lazySet(timeStamp); information.addHeartBeatNum(); } @Override public String getLifeTimeEventKey() { return lifeTimeController.getLifeTimeEventKey(); } @Override public int getBasicLifeTime() { return lifeTimeController.getBasicLifeTime(); } @Override public int createLifeTimeEvent(LifeTimeType type) throws TimerEventException { return lifeTimeController.createLifeTimeEvent(type); } @Override public void prolongLeftTimeTo(int newLifeTime) throws TimerEventException { lifeTimeController.prolongLeftTimeTo(newLifeTime); } @Override public void shortenLeftTimeTo(int newLifeTime) { lifeTimeController.shortenLeftTimeTo(newLifeTime); } @Override public void removeLifeTimeEvent() { lifeTimeController.removeLifeTimeEvent(); } /** * {@inheritDoc} */ @Override public boolean updateAfterContainerInitialization(ISourceRequestChannel postRequestChannel) { try { boolean isSuccess = super.updateAfterContainerInitialization(postRequestChannel); if (isSuccess) { lifeTimeController.prolongLeftTimeTo(lifeTimeController.getBasicLifeTime()); } return isSuccess; } catch (Exception e) { log.error("[sid:{}] Exception caught when updating Get channel after container initialization", getSid(), e); return false; } } /** * {@inheritDoc} */ @Override public boolean updateWhenBinding(PostRequestChannel postRequestChannel) { try { boolean isSuccess = super.updateWhenBinding(postRequestChannel); if (isSuccess) { createLifeTimeEvent(LifeTimeType.BASIC); } return isSuccess; } catch (Exception e) { log.error("[sid:{}] Exception caught when binding Get channel", getSid(), e); return false; } } /** * If the heart beat is expired, the Get channel will be closed instead of sending template data. At this time, the * template data reference will not be increased. * * @param templateData The template data */ @Override public ChannelFuture sendTemplateData(IHttpData templateData) { if (System.currentTimeMillis() > getClosedTimeStamp()) { if (log.isDebugEnabled()) { log.debug("[sid:{}] Close GET channel as losing heartbeat:", getSid()); } close(RelayConsts.CloseReason.GET_LACK_HEART_BEAT); DefaultChannelPromise promise = new DefaultChannelPromise(getNettyChannel()); promise.setFailure(HEART_BEAT_TIMEOUT_EXCEPTION); return promise; } else { return super.sendTemplateData(templateData); } } @Override protected void releaseResourceAfterNettyChannelClosure() { super.releaseResourceAfterNettyChannelClosure(); removeLifeTimeEvent(); } } public class PassthroughGetRequestChannel extends DefaultGetRequestChannel implements IDataFollowedRelayRequestChannel { private RelayServiceDataController<PassthroughGetRequestChannel> dataController; private volatile boolean isStartDataProcess; public PassthroughGetRequestChannel(String channelId, Channel nettyChannel, String bindingId, // ClientInfoController clientInfoController, int basicLifeTime, GetRequestChannelInformation information) { super(channelId, nettyChannel, bindingId, // clientInfoController, basicLifeTime, information); this.dataController = new RelayServiceDataController<>(this, false); } @Override public boolean isStartDataProcess() { return isStartDataProcess; } @Override public void setStartDataProcess() { this.isStartDataProcess = true; } @Override public boolean updateAfterContainerInitialization(ISourceRequestChannel postRequestChannel) { if (super.updateAfterContainerInitialization(postRequestChannel)) { setStartDataProcess(); enableAutoRead(); addPushDataTask(); return true; } else { return false; } } /** * {@inheritDoc} */ @Override public boolean updateWhenBinding(PostRequestChannel postRequestChannel) { if (super.updateWhenBinding(postRequestChannel)) { setStartDataProcess(); enableAutoRead(); addPushDataTask(); return true; } else { return false; } } @Override public void disableAutoRead() { dataController.disableAutoRead(); } @Override public void enableAutoRead() { dataController.enableAutoRead(); } @Override public List<IHttpData> getDataList() { return dataController.getDataList(); } @Override public void addPushDataTask() { dataController.addPushDataTask(); } @Override protected void releaseResourceAfterNettyChannelClosure() { super.releaseResourceAfterNettyChannelClosure(); dataController.releaseDataList(); } } @Slf4j public class BidirectionRequestChannel extends SourceRequestChannel<BidirectionContainer, BidirectionRequestChannelInformation> implements ISinkRequestChannel { private DefaultSinkHandler<BidirectionRequestChannel> sinkHandler; public BidirectionRequestChannel(String channelId, Channel nettyChannel, String bindingId, BidirectionRequestChannelInformation information) { super(channelId, nettyChannel, bindingId, information); this.sinkHandler = new DefaultSinkHandler<>(this); } @Override public String getRequestType() { if (isDeviceChannel()) { return "relayservice-deviceStream"; } else { return "relayservice-appStream"; } } @Override public boolean initContainer(BidirectionContainer container) { assert this.container == null : "initContainer() should only be called once."; this.container = container; BidirectionRequestChannel peerChannel = getPeerDirectionChannel(container); if (peerChannel == null) { return false; } if (peerChannel.updateAfterContainerInitialization(this)) { return true; } else { // As peer channel is closing, this bidirection channel will be closing soon. close(RelayConsts.CloseReason.BIDIRECTION_CLOSED_BY_PEER); return false; } } public BidirectionRequestChannel getPeerDirectionChannel() { BidirectionContainer container = this.container; if (container != null) { return getPeerDirectionChannel(container); } else { return null; } } private BidirectionRequestChannel getPeerDirectionChannel(BidirectionContainer container) { if (isDeviceChannel()) { return container.getAppChannel(); } else { return container.getDeviceChannel(); } } /** * Only contains peer channel if it exists. */ @Override protected List<? extends ISinkRequestChannel> getTransmittedSinkRequestChannels() { BidirectionContainer container = this.container; if (container == null) { return new ArrayList<>(); } List<BidirectionRequestChannel> list = new ArrayList<>(); BidirectionRequestChannel peerChannel = getPeerDirectionChannel(container); if (peerChannel != null) { list.add(peerChannel); } return list; } /** * Do nothing, waiting terminal to close the request channel. */ @Override protected void doProcessBeforeTransmitData(IHttpData templateData) { // do nothing } @Override protected void transmitDataToSinkRequestChannel(ISinkRequestChannel sinkChannel, IHttpData templateData) { // 当channel来自回放请求时,channel为长连接,此时为了适配web无法发起正常http长连接,手动将lastData的空包丢掉 String type = this.getInformation().getParamValue(RelayConsts.ParamKey.TYPE); if ((Objects.equals(type, RelayConsts.Type.SDVOD) || Objects.equals(type, RelayConsts.Type.DOWNLOAD)) && templateData.isLastData()) { return; } sinkChannel.sendTemplateData(templateData); } @Override public void startDataAcceptanceAfterSinkChannelAccess() { assert container != null : "initContainer should be called first"; BidirectionRequestChannel peerChannel = getPeerDirectionChannel(container); if (peerChannel == null) { log.warn("[sid:{}] Peer channel is missing, may be closed?", getSid()); return; } if (information.getStatisticInformation().getIsWs()) { // Start data process setStartDataProcess(); // Enable auto read enableAutoRead(); return; } doBindInitialization(peerChannel); } @Override public DataProcessState getDataProcessState() { return DataProcessState.TRANSMIT_DIRECT; } @Override public DataProcessState changeToNextState() { return DataProcessState.TRANSMIT_DIRECT; } @Override public boolean updateAfterContainerInitialization(ISourceRequestChannel sourceRequestChannel) { if (log.isDebugEnabled()) { log.debug("[sid:{}] Do bind initialization after container initialization", getSid()); } doBindInitialization(sourceRequestChannel); return true; } protected void doBindInitialization(ISourceRequestChannel sourceRequestChannel) { // Update sid String sourceSid = sourceRequestChannel.getSid(); if (sourceSid.indexOf('-') == -1) { String oldSid = getSid(); if (oldSid.indexOf('-') == -1) { updateSid(sourceSid + '-' + oldSid); } } // Try to send response header HttpResponse responseHeader = sourceRequestChannel.getResponseHeader(); if (responseHeader != null) { sendTemplateHttpResponse(responseHeader); } // Start data process setStartDataProcess(); // Enable auto read enableAutoRead(); // Add push data task addPushDataTask(); } @Override public ChannelFuture sendTemplateHttpResponse(HttpResponse templateHttpResponse) { return sinkHandler.sendTemplateHttpResponse(templateHttpResponse); } @Override public ChannelFuture sendTemplateHttpResponseAndClose(HttpResponse templateHttpResponse, RelayConsts.CloseReason closeReason) { return sinkHandler.sendTemplateHttpResponseAndClose(templateHttpResponse, closeReason); } @Override public ChannelFuture sendTemplateData(IHttpData templateData) { return sinkHandler.sendTemplateData(templateData); } @Override public ChannelFuture sendTemplateDataAndClose(IHttpData templateData, RelayConsts.CloseReason closeReason) { return sinkHandler.sendTemplateDataAndClose(templateData, closeReason); } @Override protected void releaseResourceBeforeNettyChannelClosure() { if (information.getCloseReason() != RelayConsts.CloseReason.BIDIRECTION_CLOSED_BY_PEER) { ContainerManager.getInstance().removeBidirectionContainer(this); } super.releaseResourceBeforeNettyChannelClosure(); } } @Slf4j public abstract class SourceRequestChannel<T extends IRequestChannelContainer<? extends ISinkRequestChannel>, I extends AbstractTransactionRequestChannelInformation> extends TransactionRequestChannel implements ISourceRequestChannel { private String bindingId; private ChannelTrafficShapingHandler trafficHandler; private RelayServiceDataController<SourceRequestChannel<T, I>> dataController; private volatile boolean isStartDataProcess; private HttpResponse responseHeader; protected T container; public SourceRequestChannel(String channelId, Channel nettyChannel, String bindingId, I information) { super(channelId, nettyChannel, information); this.bindingId = bindingId; this.dataController = new RelayServiceDataController<>(this, false); } @Override public String getBindingId() { return bindingId; } public ChannelTrafficShapingHandler getTrafficHandler() { return trafficHandler; } public void setTrafficHandler(ChannelTrafficShapingHandler trafficHandler) { this.trafficHandler = trafficHandler; } @Override public void disableAutoRead() { dataController.disableAutoRead(); } @Override public void enableAutoRead() { dataController.enableAutoRead(); } @Override public List<IHttpData> getDataList() { return dataController.getDataList(); } @Override public void addPushDataTask() { dataController.addPushDataTask(); } @Override public HttpResponse getResponseHeader() { return responseHeader; } @Override public void setResponseHeader(HttpResponse responseHeader) { this.responseHeader = responseHeader; } @Override public boolean isStartDataProcess() { return isStartDataProcess; } @Override public void setStartDataProcess() { this.isStartDataProcess = true; } /** * Initialize the source channel with a container in which the sink channel is put into. * * @param container The container * * @return True if the container has at least one sink channel. */ public abstract boolean initContainer(T container); public boolean isInitContainer() { return container != null; } public T getContainer() { return container; } /** * Only used for Junit test. */ void setContainer(T container) { this.container = container; } /** * Start source channel data acceptance after sink channel access. */ public abstract void startDataAcceptanceAfterSinkChannelAccess(); /** * {@inheritDoc} */ @Override public void transmitHttpResponse(HttpResponse templateHttpResponse) { List<? extends ISinkRequestChannel> currentList = getTransmittedSinkRequestChannels(); for (ISinkRequestChannel sinkChannel : currentList) { try { sinkChannel.sendTemplateHttpResponse(templateHttpResponse); } catch (Exception e) { log.error("[sid:{}] Failed to transmit response to GET channel: channelId={}:", getSid(), sinkChannel.getChannelId(), e); sinkChannel.close(RelayConsts.CloseReason.SERVER_INTERNAL_ERROR); } } } /** * {@inheritDoc} */ @Override public void transmitData(IHttpData templateData) { List<? extends ISinkRequestChannel> currentList = templateData.isLastData() ? getTransmittedSinkRequestChannels() : null; doProcessBeforeTransmitData(templateData); currentList = currentList == null ? getTransmittedSinkRequestChannels() : currentList; for (ISinkRequestChannel sinkChannel : currentList) { try { transmitDataToSinkRequestChannel(sinkChannel, templateData); } catch (Exception e) { log.error("[sid:{}] Failed to transmit data to GET channel: channelId={}, index={}", getSid(), sinkChannel.getChannelId(), templateData.getIndex(), e); sinkChannel.close(RelayConsts.CloseReason.SERVER_INTERNAL_ERROR); } } } /** * Obtain all sink channel which can be transmitted response and data to. * * @return The sink channel which can be transmitted response and data to. */ protected abstract List<? extends ISinkRequestChannel> getTransmittedSinkRequestChannels(); /** * Process before transmit template data. * * @param templateData The template data */ protected abstract void doProcessBeforeTransmitData(IHttpData templateData); /** * Transmit template data to sink channel. * * @param sinkChannel The sink channel * @param templateData The template data. */ protected abstract void transmitDataToSinkRequestChannel(ISinkRequestChannel sinkChannel, IHttpData templateData); @Override protected void releaseResourceAfterNettyChannelClosure() { super.releaseResourceAfterNettyChannelClosure(); dataController.releaseDataList(); } } @Slf4j public abstract class TransactionRequestChannel extends RequestChannel { protected String channelId; @Getter @Setter private String content; public TransactionRequestChannel(String channelId, Channel nettyChannel, I information) { super(nettyChannel, information); this.channelId = channelId; } public String getChannelId() { return channelId; } public ChannelVersion getChannelVersion() { return information.getChannelVersion(); } public boolean isDeviceChannel() { return information.isFromDevice(); } public AbstractAuthContext getAuthContext() { return information.getAuthContext(); } public RequestLevel getRequestLevel() { return getAuthContext().getLevel(); } @Override protected void releaseResourceBeforeNettyChannelClosure() { // Call super method super.releaseResourceBeforeNettyChannelClosure(); // Reomve itself from ChannelManager immediately if (isRequestChannelRemovedImmediately()) { removeFromChannelManager(); } } @Override protected void releaseResourceAfterNettyChannelClosure() { // Call super method super.releaseResourceAfterNettyChannelClosure(); // Reomve itself from ChannelManager after 1s. if (!isRequestChannelRemovedImmediately()) { getNettyChannel().eventLoop().schedule(new Runnable() { @Override public void run() { removeFromChannelManager(); } }, 1000, TimeUnit.MILLISECONDS); } } protected boolean isRequestChannelRemovedImmediately() { return true; } ; protected void removeFromChannelManager() { if (log.isDebugEnabled()) { log.debug("[sid:{}] Remove request channel {} channelId={} from channel manager", getSid(), this.getClass().getSimpleName(), getChannelId()); } ChannelManager.getInstance().removeRequestChannel(channelId, this); } } @Slf4j public class GetRequestChannel extends TransactionRequestChannel implements ISinkRequestChannel { protected String bindingId; protected AtomicBoolean isAllowTransmission; protected ISinkHandler sinkHandler; public GetRequestChannel(String channelId, Channel nettyChannel, String bindingId, GetRequestChannelInformation information) { super(channelId, nettyChannel, information); this.bindingId = bindingId; this.isAllowTransmission = new AtomicBoolean(false); this.sinkHandler = buildSinkHandler(information); } /** * Build the sink hanlder according to the sink channel. When any template response and data are sent to this Get * channel, the sending action will be processed by using sink hanlder. * * @param information The Get channel information * * @return The sink hanlder. */ protected ISinkHandler buildSinkHandler(GetRequestChannelInformation information) { if (information.isNeedMultipleMappingSource()) { // Flow control is only used in Preview type(such as video or nvr) return new FlowControlHandler(buildBasicSinkHandler(information)); } else { return buildBasicSinkHandler(information); } } /** * The basic sink handler which is used to do actual sending action. * * @param information The Get channel information * * @return The basic sink hanlder. */ protected ISinkHandler buildBasicSinkHandler(GetRequestChannelInformation information) { return new DefaultSinkHandler<>(this); } @Override public String getRequestType() { if (getChannelVersion() == ChannelVersion.VERSION_1_2) { return "relayservice-getStream"; } else if (isDeviceChannel()) { return "relayservice-deviceStream"; } else { return "relayservice-appStream"; } } @Override public String getBindingId() { return bindingId; } public void updateBindingId(String newBindingId) { this.bindingId = newBindingId; } public boolean isAllowTransmission() { return isAllowTransmission.get(); } public boolean setAllowTransmission() { return this.isAllowTransmission.compareAndSet(false, true); } /** * Whether the type is a multiple mapping type, such as video, audio, mixed and nvr. * * @return True if the type is a multiple mapping type. */ public boolean isNeedMultipleMappingSource() { return information.isNeedMultipleMappingSource(); } /** * If the Get channel arrives before the corresponding Post channel, the Get channel will be put into a container. * When the Post channel arrives, the Post channel will find and initialize the container to check whether any Get * channel is bound with it. At this time, using this method the notify the Get channel of binding action after * container initialization. * <p> * <p> * This method will be called in following cases: * <ul> * <li>After the Post channel initializes the container, any Get channel in the container will be notified of the * initialization by calling this.</li> * <li>If the container has been initialized by Post channel when the Get channel is added into container directly. * The Get channel should be notified of the initialization by calling this.</li> * </ul> * </p> * * @param postRequestChannel The source channel which this sink channel is bound with. * * @return Whether the sink channel is successfully updated. If false, the sink channel may be closed and the source * channel should remove it. */ @Override public boolean updateAfterContainerInitialization(final ISourceRequestChannel postRequestChannel) { doBindInitialization(postRequestChannel); return true; } /** * Update Get channel when the Get channel is bound with Post channel. * * @param postRequestChannel The corresponding Post channel. * * @return True if the updating operation is successful. */ public boolean updateWhenBinding(final PostRequestChannel postRequestChannel) { doBindInitialization(postRequestChannel); return true; } private void doBindInitialization(final ISourceRequestChannel requestChannel) { EventLoop eventLoop = getNettyChannel().eventLoop(); if (eventLoop.inEventLoop()) { doBindInitializationInEventLoop(requestChannel); } else { eventLoop.execute(() -> doBindInitializationInEventLoop(requestChannel)); } } /** * The initialization of Get channel when bound with Post channel * * @param postRequestChannel The corresponding Post channel. */ protected void doBindInitializationInEventLoop(ISourceRequestChannel postRequestChannel) { // Update sid updateSidByPrefix(postRequestChannel.getSid()); // Try to send response header HttpResponse responseHeader = postRequestChannel.getResponseHeader(); if (responseHeader != null) { sendTemplateHttpResponse(responseHeader); } } /** * To initialize the Get request channel transmission. * * @param postRequestChannel The corresponding Post request channel. */ public void startTransmission(final PostRequestChannel postRequestChannel) { EventLoop eventLoop = getNettyChannel().eventLoop(); if (eventLoop.inEventLoop()) { doStartTransmission(postRequestChannel); } else { eventLoop.execute(() -> doStartTransmission(postRequestChannel)); } } private void doStartTransmission(PostRequestChannel postRequestChannel) { assert getNettyChannel().eventLoop().inEventLoop(); // This is helpful when the 2nd App for smart codec stream. initSmartCodec(postRequestChannel); if (setAllowTransmission()) { int num = postRequestChannel.updateTransmittedGetRequestChannels(); if (log.isDebugEnabled()) { log.debug("[sid:{}] Start transmission of GET channel: transmittedGetChannelNum={}", getSid(), num); } // This is helpful when the 1st App and smart codec notification may be comming soon. initSmartCodec(postRequestChannel); /* List<IHttpData> templateDataList = getDataListToStartTransmission(postRequestChannel); for (IHttpData templateData : templateDataList) { sendTemplateData(templateData); }*/ } } private void initSmartCodec(PostRequestChannel postRequestChannel) { if (postRequestChannel.isSmartCodec()) { startSmartCodec(); } else { stopSmartCodec(); } } /** * If the corresponding channel is {@link BufferedPostRequestChannel}, the buffered data will be returned. * Otherwise, empty array will be returned. * * @param postRequestChannel The corresponding Post request channel. * * @return The data list for initialization of transmission */ protected List<IHttpData> getDataListToStartTransmission(PostRequestChannel postRequestChannel) { List<IHttpData> dataList; if (postRequestChannel instanceof BufferedPostRequestChannel) { dataList = ((BufferedPostRequestChannel) postRequestChannel).getAllBufferedData(); if (log.isDebugEnabled()) { log.debug("[sid:{}] Add buffered HTTP data: size={}", getSid(), dataList.size()); } } else { dataList = new ArrayList<>(); } IHttpData iFrame = postRequestChannel.getIFrame(); if (iFrame != null) { dataList.add(iFrame); } return dataList; } @Override public ChannelFuture sendTemplateHttpResponse(HttpResponse templateHttpResponse) { return sinkHandler.sendTemplateHttpResponse(templateHttpResponse); } @Override public ChannelFuture sendTemplateHttpResponseAndClose(HttpResponse templateHttpResponse, RelayConsts.CloseReason closeReason) { return sinkHandler.sendTemplateHttpResponseAndClose(templateHttpResponse, closeReason); } @Override public ChannelFuture sendTemplateData(IHttpData templateData) { return sinkHandler.sendTemplateData(templateData); } @Override public ChannelFuture sendTemplateDataAndClose(IHttpData templateData, RelayConsts.CloseReason closeReason) { return sinkHandler.sendTemplateDataAndClose(templateData, closeReason); } public void startSmartCodec() { ISinkHandler handler = sinkHandler; FlowControlHandler flowControlHandler = findFlowControlHandler(handler); if (flowControlHandler != null) { flowControlHandler.setSmartCodec(true); } if (!(handler instanceof SmartCodecSinkHandler)) { sinkHandler = new SmartCodecSinkHandler(handler); } } public void stopSmartCodec() { ISinkHandler handler = sinkHandler; FlowControlHandler flowControlHandler = findFlowControlHandler(handler); if (flowControlHandler != null) { flowControlHandler.setSmartCodec(false); } if (handler instanceof SmartCodecSinkHandler) { sinkHandler = ((SmartCodecSinkHandler) handler).unwrap(); } } public void updateKeyFrameUTCTime(long utcTime) { FlowControlHandler h = findFlowControlHandler(sinkHandler); if (h != null) { h.updateUTCTime(utcTime); } } public void updateWritabilityEvent(boolean isWritable) { FlowControlHandler h = findFlowControlHandler(sinkHandler); if (h != null) { h.setSinkChannelWriterable(isWritable); } } private FlowControlHandler findFlowControlHandler(ISinkHandler h) { while (h instanceof FilterSinkHandler) { if (h instanceof FlowControlHandler) { return (FlowControlHandler) h; } else { h = ((FilterSinkHandler) h).unwrap(); } } return null; } @Override protected void doClose() { if (RelayConsts.Type.FILE.equals(information.getParams().get(RelayConsts.ParamKey.TYPE))) { // When the last data is sent, netty channel may be closed immediately and the elb will not discard the last // data. The delay added here is like SO_LINGER configuration. if (log.isDebugEnabled()) { log.debug("[sid={}] Delay close netty channel when file type", getSid()); } getNettyChannel().eventLoop().schedule(GetRequestChannel.super::doClose, 100, TimeUnit.MILLISECONDS); } else { super.doClose(); } } @Override protected void releaseResourceBeforeNettyChannelClosure() { // Call super method super.releaseResourceBeforeNettyChannelClosure(); // As this channel has unbounded, unbindGetRequestChannel() should be avoided. RelayConsts.CloseReason closeReason = information.getCloseReason(); if (closeReason == RelayConsts.CloseReason.GET_CLOSED_BY_POST || closeReason == RelayConsts.CloseReason.GET_CLOSED_AFTER_LAST_DATA_SENT || closeReason == RelayConsts.CloseReason.GET_CLOSED_BY_KILL_SHARER_CMD) { return; } ChannelManager manager = ChannelManager.getInstance(); PostRequestChannel postRequestChannel = (PostRequestChannel) manager.getRequestChannel(bindingId); if (postRequestChannel != null) { postRequestChannel.unbindGetRequestChannel(this); } else { ContainerManager.getInstance().removeFromSingleDirectionContainer(this); } } } @Slf4j public class BufferedPostRequestChannel extends DefaultPostRequestChannel { private volatile IHttpData[] buffedDataList; public BufferedPostRequestChannel(String channelId, Channel nettyChannel, String shortBindingId, DataProcessState initDataProcessState, int basicLifeTime, int bufferedNum, PostRequestChannelInformation information) { super(channelId, nettyChannel, shortBindingId, initDataProcessState, basicLifeTime, information); this.buffedDataList = new IHttpData[bufferedNum]; } /** * Try to add data into buffer. The return value represents the left number in the buffer after adding this data. If * the data is added successfully, the return value is non-negtive, especially 0 indicates the buffer is full after * adding this data. If the data is failed to be added into buffer when the buffer is already full, -1 will be * return. No matter the data is added successfully or not, the data reference will not be modified. * * @return left number in the buffer after adding this data. * <ul> * <li>If the value is positive, the buffer still have capcacity after adding this data</li> * <li>If the value is 0, the buffer is full after adding this data</li> * <li>If the value is -1, the data is not added into this data as the buffer is already full</li> * </ul> */ public int addBufferedHttpData(IHttpData bufferedHttpData) { IHttpData[] oldList = this.buffedDataList; int bufferedNum = oldList.length; int index = 0; for (; index < bufferedNum; index++) { if (oldList[index] == null) { break; } } if (index < bufferedNum) { IHttpData[] newList = Arrays.copyOf(oldList, bufferedNum); newList[index] = bufferedHttpData; this.buffedDataList = newList; return bufferedNum - (index + 1); } else { return -1; } } /** * Obtain all buffered data at this time. The return list will be instantiated each time this method is called. * * @return The buffered data list. */ public List<IHttpData> getAllBufferedData() { IHttpData[] current = this.buffedDataList; ArrayList<IHttpData> ret = new ArrayList<>(current.length); for (int index = 0; index < current.length; index++) { if (current[index] != null) { ret.add(current[index]); } else { break; } } return ret; } @Override protected DataProcessState getNextState(DataProcessState oldState) { if (oldState == DataProcessState.WANT_RESPONSE_MOULD) { return DataProcessState.WANT_BUFFERED_DATA; } else if (oldState == DataProcessState.WANT_BUFFERED_DATA) { return DataProcessState.TRANSMIT_DIRECT; } else if (oldState == DataProcessState.TRANSMIT_DIRECT) { return DataProcessState.TRANSMIT_DIRECT; } else { throw new RuntimeException("Bugs in data process state change:" + oldState); } } @Override protected void releaseResourceAfterNettyChannelClosure() { // release wave header if (log.isDebugEnabled()) { log.debug("[sid:{}] Release buffered HTTP data", getSid()); } IHttpData[] current = this.buffedDataList; this.buffedDataList = new IHttpData[0]; for (IHttpData data : current) { ReferenceCountUtil.safeRelease(data); } if (information.isFromDevice()) { log.debug("cloud access channel for {} from device is closing", getBindingId()); CloudStreamInfoContainer.getInstance().removeInfo(getBindingId()); } super.releaseResourceAfterNettyChannelClosure(); } } @Slf4j public class PostRequestChannel extends SourceRequestChannel<SingleDirectionContainer, PostRequestChannelInformation> { /* It is used for multiple mapping type when social share starts and the preview of single stream output IPC */ private String shortBindingId; private DataProcessState dataProcessState; private boolean isNeedResponseAfterGetAccess; private IHttpResponseGenerator successResponseGenerator; private IFrameHolder iFrameHolder; public PostRequestChannel(String channelId, Channel nettyChannel, String shortBindingId, DataProcessState initDataProcessState, PostRequestChannelInformation information) { super(channelId, nettyChannel, channelId, information); this.shortBindingId = shortBindingId; this.dataProcessState = initDataProcessState; if (this.getChannelVersion() == ChannelVersion.VERSION_1_2) { Boolean isNeedSuccessResponse = information.isNeedSuccessResponse(); if (isNeedSuccessResponse == null) { this.isNeedResponseAfterGetAccess = false; this.successResponseGenerator = buildSuccessResponseGenerator(nettyChannel.alloc()); } else { this.isNeedResponseAfterGetAccess = isNeedSuccessResponse; this.successResponseGenerator = isNeedSuccessResponse ? buildSuccessResponseGenerator(nettyChannel.alloc()) : null; } } else { this.isNeedResponseAfterGetAccess = true; this.successResponseGenerator = buildSuccessResponseGenerator(nettyChannel.alloc()); } } private IHttpResponseGenerator buildSuccessResponseGenerator(ByteBufAllocator alloc) { String boundary = ContentType.generateBoundary(); ContentType contentType = new ContentType("multipart/mixed", boundary, ContentType.DEFAULT_DATA_CHARSET); return new MultiPartResponseGenerator(alloc, contentType, HttpUtil.isTransferEncodingChunked(information .getOrignalRequest())); } @Override public String getRequestType() { if (getChannelVersion() == ChannelVersion.VERSION_1_2) { return "relayservice-postStream"; } else if (isDeviceChannel()) { return "relayservice-deviceStream"; } else { return "relayservice-appStream"; } } public boolean isMultipleMapping() { return information.isMultipleMapping(); } public boolean isNeedResponseAfterGetAccess() { return isNeedResponseAfterGetAccess; } public boolean isSingleStream() { return information.isSingleStream(); } public IHttpResponseGenerator getSuccessResponseGenerator() { return successResponseGenerator; } public String getShortBindingId() { return shortBindingId; } @Override public DataProcessState getDataProcessState() { return dataProcessState; } /** * Change to next state and return the new state. As this method is called in data processor only, the state field * only uses <code>volitale</code> instead of <code>AtomicReference</code> * * @return The next data process state. */ @Override public DataProcessState changeToNextState() { assert getNettyChannel().eventLoop().inEventLoop(); DataProcessState state = getNextState(this.dataProcessState); this.dataProcessState = state; return state; } protected DataProcessState getNextState(DataProcessState oldState) { if (oldState == DataProcessState.WANT_RESPONSE_MOULD) { return DataProcessState.TRANSMIT_DIRECT; } else if (oldState == DataProcessState.TRANSMIT_DIRECT) { return DataProcessState.TRANSMIT_DIRECT; } else { throw new RuntimeException("Bugs in data process state change:" + oldState); } } /** * Initialize the Post channel with a container. The Post channel only transmit data to the Get channel which has * the same version(except ffmpeg Get channel). If the Get channel version does not match with the Post version, the * Get channel will be response 404/400 and closed. Especially, when the Get channel is 1.2 version and Post channel * is 1.3 version, Get channel will be shown hint video whe next access. * * @param container The allocated container * * @return Whether the container has contained any Get channels, if <code>true</code>, the container contains GET * channels. */ @Override public boolean initContainer(SingleDirectionContainer container) { assert this.container == null : "initContainer() should only be called once."; synchronized (PostRequestChannel.class) { this.container = container; return doInitContainer(container); } } protected boolean doInitContainer(SingleDirectionContainer container) { boolean hasGetChannels = false; Iterator<GetRequestChannel> iter = container.boundIterator(); while (iter.hasNext()) { GetRequestChannel getChannel = iter.next(); if (getChannel.getChannelVersion() != getChannelVersion() // && !getChannel.isFFmpegChannel() ) { iter.remove(); information.addVersionUnmatchNum(); getChannel.getInformation().setVersionUnmatch(true); if (getChannel.getChannelVersion() == ChannelVersion.VERSION_1_2) { getChannel.sendHttpResponseAndClose(HttpResponseStatus.NOT_FOUND, CloseReason.GET_CLOSED_WITH_LOW_VERSION); } else { getChannel.sendHttpResponseAndClose(HttpResponseStatus.BAD_REQUEST, CloseReason.GET_CLOSED_WITH_HIGHT_VERSION); } continue; } if (getChannel.getChannelVersion() == ChannelVersion.VERSION_1_2) { getChannel.setAllowTransmission(); } boolean isSuccess = getChannel.updateAfterContainerInitialization(this); if (isSuccess) { isSuccess = doUpdateBindingInformation(getChannel); } if (isSuccess) { hasGetChannels = true; } else { iter.remove(); getChannel.sendHttpResponseAndClose(HttpResponseStatus.NOT_FOUND, CloseReason.GET_CLOSED_WHEN_BIND_FAILURE); } } if (hasGetChannels) { // As Post channel data doesnot arrive or is buffered in decoder, startTransmission of Get channel does // not need to be called. container.synchronizeTransmittedSinkRequestChannelList(); } return hasGetChannels; } /** * Update Get and Post channel after container initialization. This is called when Get channel is added into * container which has been initialized by this Post channel. * <ul> * <li>For Get channel, using {@link GetRequestChannel#updateAfterContainerInitialization(ISourceRequestChannel)} to * update Get channel.</li> * <li>For Post channel, updating binding information</li> * </ul> * * @param getRequestChannel The Get channel which is added into an initialized container. * * @return True if the updating operation is successfully executed. */ public boolean updateAfterContainerInitialization(GetRequestChannel getRequestChannel) { // As this method is called after isInitContainer() checking, the container has been initialized. assert container != null : "initContainer() or isInitContainer() should be called first"; SingleDirectionContainer container = this.container; synchronized (container) { if (getRequestChannel.getChannelVersion() == ChannelVersion.VERSION_1_2) { getRequestChannel.startTransmission(this); } boolean isSuccess = getRequestChannel.updateAfterContainerInitialization(this); if (isSuccess) { isSuccess = doUpdateBindingInformation(getRequestChannel); } if (!isSuccess) { container.removeBoundSinkRequestChannel(getRequestChannel); container.synchronizeTransmittedSinkRequestChannelList(); } return isSuccess; } } /** * Call it after Post channel is added into channel manager. This method is used to obtain and process data which is * stored in decoder, which is useful to sticky package for version 1.3. */ public void callAfterAddIntoChannelManager() { for (GetRequestChannel getChannel : getBoundGetRequestChannels()) { if (getChannel instanceof IDataFollowedRelayRequestChannel) { ((IDataFollowedRelayRequestChannel) getChannel).setStartDataProcess(); ((IDataFollowedRelayRequestChannel) getChannel).enableAutoRead(); ((IDataFollowedRelayRequestChannel) getChannel).addPushDataTask(); } } } /** * Bind the given Get channel with this Post channel and return the binding result. If the Post channel is closed, * false will be returned.The Post channel only transmit data to the Get channel which has the same version(except * ffmpeg Get channel). If the Get channel version does not match with the Post version, the binding result will be * false. * * @param getRequestChannel The Get channel needs to be bound with this Post channel * * @return The binding action is sucessful or not. True if the Get channel is successfully bound with this Post * channel. False if the Post channel is closed and fails to bind the Get channel. */ public BindingResult bindGetRequestChannel(GetRequestChannel getRequestChannel) { // As this method is called after PostRequestChannel being put into channel manager. the container has been // initialized. assert container != null : "initContainer() or isInitContainer() should be called first"; if (isClosed()) { return BindingResult.SOURCE_CHANNEL_CLOSURE; } SingleDirectionContainer container = this.container; synchronized (container) { return doBindGetRequestChannel(container, getRequestChannel); } } protected BindingResult doBindGetRequestChannel(SingleDirectionContainer container, GetRequestChannel getRequestChannel) { BindingResult result = container.addBoundSinkRequestChannel(getRequestChannel); if (result != BindingResult.SUCCESS) { return result; } if (getRequestChannel.getChannelVersion() == ChannelVersion.VERSION_1_2) { getRequestChannel.startTransmission(this); } if (BooleanUtils.isTrue(getRequestChannel.getWs())) { if (getRequestChannel instanceof IDataFollowedRelayRequestChannel) { ((IDataFollowedRelayRequestChannel) getRequestChannel).setStartDataProcess(); ((IDataFollowedRelayRequestChannel) getRequestChannel).enableAutoRead(); } return BindingResult.SUCCESS; } boolean isSuccess = getRequestChannel.updateWhenBinding(this); if (isSuccess) { isSuccess = doUpdateBindingInformation(getRequestChannel); } if (!isSuccess) { container.removeBoundSinkRequestChannel(getRequestChannel); container.synchronizeTransmittedSinkRequestChannelList(); } return isSuccess ? BindingResult.SUCCESS : BindingResult.UPDATE_INFORMATION_FAILURE; } private boolean doUpdateBindingInformation(GetRequestChannel getRequestChannel) { switch (getRequestChannel.getRequestLevel()) { case OWNER: information.addOwnerNum(); break; case SLAVE: information.addSlaveNum(); break; case SHARED: information.addSharedNum(); break; case SOCIAL: information.addSocialNum(); break; default: break; } return true; } /** * Send pull-stream command when ffmpeg channel arrives and the Post channel version is 1.3 */ public void sendPullStreamCommandIfNecessary() { if (getChannelVersion() == ChannelVersion.VERSION_1_3) { assert successResponseGenerator != null; HttpResponse response = successResponseGenerator.getHttpResponse(HttpResponseStatus.OK); if (response != null) { sendHttpResponse(response); } JSONObject command = buildPullStreamCommand(getInformation().getParams()); sendData(successResponseGenerator.getHttpData(command)); } } /** * <p> * Build pull-stream command. * </p> * <p> * In 1.3 version, channel of video type is 0, and channel of nvr type starts from 0 and has following mapping. * * <pre> * 'channel' in relay request url ----> 'channel' in JSON command * 0 ----> 0 * 1 ----> 1 * ... ----> ... * </pre> * * </p> * * @param params request url parameters. * * @return The pull-stream command */ private JSONObject buildPullStreamCommand(Map<String, String> params) { // 实际上设备使用此信息控制推流RESOLUTION int channel = 0; if (Type.NVR.equals(params.get(ParamKey.TYPE)) || Type.SMART_NVR.equals(params.get(ParamKey.TYPE))) { channel = Integer.parseInt(params.get(ParamKey.CHANNEL)); } JSONObject preview = new JSONObject(); preview.put("channels", new JSONArray().put(channel)); String resolution = params.get(ParamKey.RESOLUTION); if (resolution != null) { preview.put("resolutions", new JSONArray().put(resolution)); } JSONObject command = new JSONObject(); command.put("type", "request"); command.put("seq", "0"); command.put("params", new JSONObject().put("method", "get").put("preview", preview)); return command; } /** * {@inheritDoc} * * <ul> * <li>For 1.3 version Post channel, as the terminal will not send data before receiving pull-data command, the * response and auto read COULD be sent and enabled after Get access.</li> * <li>For 1.2 version Post channel, the response and auto read SHOULD be sent and enabled after Get access.</li> * </ul> */ @Override public void startDataAcceptanceAfterSinkChannelAccess() { // Send success response header if necessary if (isNeedResponseAfterGetAccess()) { HttpResponse httpResponse = successResponseGenerator.getHttpResponse(HttpResponseStatus.OK); if (httpResponse != null) { sendHttpResponse(httpResponse); } } // Start data process setStartDataProcess(); // Try to enable auto read of POST channel enableAutoRead(); // Add pull data task addPushDataTask(); } /** * Unbind the given GET channel. If the GET channel exists in the binding list, true will be returned. * * @param getRequestChannel The GET channel needs to be unbound. * * @return True if the GET channel exists in the binding list. */ public boolean unbindGetRequestChannel(GetRequestChannel getRequestChannel) { // As this method is called after PostRequestChannel being put into channel manager. the container has been // initialized. assert container != null : "Should call initContainer() first."; SingleDirectionContainer container = this.container; synchronized (container) { boolean isExisted = container.removeBoundSinkRequestChannel(getRequestChannel); if (isExisted) { // If the ffmpeg channel is closed explicitly, the channel will not exist in list. container.synchronizeTransmittedSinkRequestChannelList(); // 取消FFmpeg重试 // if (getRequestChannel.isFFmpegChannel()) { // doRestartSegmenter(container); // } else { doTryToClose(container, CloseReason.POST_CLOSED_AFTER_GET_EXIT); // } } return isExisted; } } /** * Try to close the Post channel. The closure strategy is shown in {@link #doTryToClose(SingleDirectionContainer, * CloseReason)} * * @param closeReason The specific close reason. */ public void tryToClose(CloseReason closeReason) { SingleDirectionContainer container = this.container; if (container == null) { return; } synchronized (container) { doTryToClose(container, closeReason); } } /** * Try to close current POST channel after GET channel is unbound. For single mapping channel, the POST channel will * be closed immediately if no GET channel is bound any more. For multiple mapping channel, the POST channel will be * closed in following cases: * <ul> * <li>When the bound channel number is 0, the segmenter doesn't exist or is in Segmenter.MAX_RESTART_RETRIES state, * or doesn't have consumer.</li> * <li>When the bound channel number is 1, the segmenter is in Segmenter.SEGMENTING state and doesn't have consumer</li> * </ul> * * @param closeReason The reason of closing action */ protected void doTryToClose(SingleDirectionContainer container, CloseReason closeReason) { // Before doTryToClose, container has been checked and could not be null. if (isMultipleMapping()) { // int bindedChannelNum = container.getBoundSinkRequestChannelNum(); // if (bindedChannelNum == 0) { // Segmenter s = segmenter; // if (s == null || s.getState() == Segmenter.MAX_RESTART_RETRIES || !s.hasConsumer()) { // close(closeReason); // } // } else if (bindedChannelNum == 1) { // Segmenter s = segmenter; // if (s != null && s.isSegmenting() && !s.hasConsumer()) { // close(closeReason); // } // } //log.error("Is multiple mapping"); close(closeReason); } else { if (container.hasNoBoundSinkRequestChannel()) { close(closeReason); } } } /** * Close all GET channels bound with this POST channel. The method is only used in {@link * #releaseResourceAfterNettyChannelClosure()} */ private void closeAllGetRequestChannels() { SingleDirectionContainer container = this.container; if (container == null) { return; } synchronized (container) { Iterator<GetRequestChannel> iter = container.boundIterator(); while (iter.hasNext()) { GetRequestChannel getChannel = iter.next(); if (log.isDebugEnabled()) { log.debug("[sid:{}] Close GET Channel: channelId={}", getSid(), getChannel.getChannelId()); } getChannel.close(CloseReason.GET_CLOSED_BY_POST); } container.removeAllBoundSinkeRequestChannels(); container.synchronizeTransmittedSinkRequestChannelList(); } } public List<GetRequestChannel> getBoundGetRequestChannels() { SingleDirectionContainer container = this.container; if (container == null) { return new ArrayList<>(); } synchronized (container) { return container.getBoundSinkRequestChannels(); } } public int getBoundGetRequestChannelNum() { SingleDirectionContainer container = this.container; if (container == null) { return 0; } synchronized (container) { return container.getBoundSinkRequestChannelNum(); } } @SuppressWarnings("unchecked") public List<GetRequestChannel> getTransmittedGetRequestChannels() { return (List<GetRequestChannel>) getTransmittedSinkRequestChannels(); } public int getTransmittedGetRequestChannelNum() { SingleDirectionContainer container = this.container; if (container == null) { return 0; } return container.getTransmittedSinkRequestChannelNum(); } public int updateTransmittedGetRequestChannels() { SingleDirectionContainer container = this.container; if (container == null) { return 0; } synchronized (container) { return container.synchronizeTransmittedSinkRequestChannelList(); } } public class ControlRequestChannel extends BaseHttpRequestChannel { public ControlRequestChannel(String channelId, Channel nettyChannel, ControlRequestChannelInformation information) { super(channelId, nettyChannel, ILifeTimeControl.IDLE_LIFE_TIME_S, information); } @Override public String getRequestType() { String cmdMethod = information.getCmdMethod(); return cmdMethod != null ? RelayConsts.Service.CONTROL + '-' + cmdMethod : RelayConsts.Service.CONTROL; } }
09-11
一个Python 作为Sqlite 数据传输同步到Oracle ,源码 import sqlite3 import oracledb from datetime import datetime, timedelta import logging import re # 日志配置 logging.basicConfig( filename='db_sync_debug.log', level=logging.DEBUG, format='%(asctime)s - %(levelname)s - %(message)s', encoding='utf-8' ) # 增强的日期格式正则表达式 DATE_REGEX = re.compile( r'^(\d{4})-(\d{2})-(\d{2})[T ](\d{2}):(\d{2}):(\d{2})(?:\.(\d+))?$' ) # 主键映射表 TABLE_PRIMARY_KEYS = { "OEE_AvaiableTime": "OEE_AvaiableTimeId", "OEE_CycleTime": "OEE_CycleTimeId", "OEE_CycleTime_Config": "OEE_CycleTimeConfigId", "OEE_LineReport": "OEE_LineReportId", "OEE_PerformanceComment": "OEE_PerformanceCommentId", "OEE_ProductStatus": "OEE_ProductStatusId", "OEE_Quality": "OEE_QualityId", "OEE_ShiftPlan_Config": "OEE_ShiftPlanConfigId", "OEE_ShiftWorkTime": "OEE_ShiftWorkTimeId", "OEE_Target_Config": "OEE_TargetConfigId" } # Oracle 关键字列表(扩展) ORACLE_KEYWORDS = { 'ACCESS', 'ADD', 'ALL', 'ALTER', 'AND', 'ANY', 'AS', 'ASC', 'AUDIT', 'BETWEEN', 'BY', 'CHAR', 'CHECK', 'CLUSTER', 'COLUMN', 'COMMENT', 'COMPRESS', 'CONNECT', 'CREATE', 'CURRENT', 'DATE', 'DECIMAL', 'DEFAULT', 'DELETE', 'DESC', 'DISTINCT', 'DROP', 'ELSE', 'EXCLUSIVE', 'EXISTS', 'FILE', 'FLOAT', 'FOR', 'FROM', 'GRANT', 'GROUP', 'HAVING', 'IDENTIFIED', 'IMMEDIATE', 'IN', 'INCREMENT', 'INDEX', 'INITIAL', 'INSERT', 'INTEGER', 'INTERSECT', 'INTO', 'IS', 'LEVEL', 'LIKE', 'LOCK', 'LONG', 'MAXEXTENTS', 'MINUS', 'MLSLABEL', 'MODE', 'MODIFY', 'NOAUDIT', 'NOCOMPRESS', 'NOT', 'NOWAIT', 'NULL', 'NUMBER', 'OF', 'OFFLINE', 'ON', 'ONLINE', 'OPTION', 'OR', 'ORDER', 'PCTFREE', 'PRIOR', 'PRIVILEGES', 'PUBLIC', 'RAW', 'RENAME', 'RESOURCE', 'REVOKE', 'ROW', 'ROWID', 'ROWNUM', 'ROWS', 'SELECT', 'SESSION', 'SET', 'SHARE', 'SIZE', 'SMALLINT', 'START', 'SUCCESSFUL', 'SYNONYM', 'SYSDATE', 'TABLE', 'THEN', 'TO', 'TRIGGER', 'UID', 'UNION', 'UNIQUE', 'UPDATE', 'USER', 'VALIDATE', 'VALUES', 'VARCHAR', 'VARCHAR2', 'VIEW', 'WHENEVER', 'WHERE', 'WITH', 'TYPE', 'MI', 'SS' # 添加了额外的关键字 } def generate_bind_name(col_name, is_date=False): """生成安全的绑定变量名""" base_name = col_name.lower().replace(' ', '_') if is_date: base_name = f"{base_name}_dt" if base_name.upper() in ORACLE_KEYWORDS: base_name = f"_{base_name}_" if re.search(r'[^a-z0-9_]', base_name): base_name = f"_{base_name}_" return base_name def validate_bind_variables(sql, params): """ 改进的绑定变量验证: 1. 修复未初始化变量问题 2. 增强字符串字面值处理 """ bind_names = set() in_string = False current_name = "" collecting = False # 关键初始化 # 解析SQL查找所有绑定变量占位符 for char in sql: # 处理字符串字面值开始/结束 if char == "'": in_string = not in_string continue # 只在非字符串区域处理 if not in_string: # 遇到冒号开始收集 if char == ':': collecting = True continue # 收集有效绑定变量名字符 if collecting and (char.isalnum() or char == '_'): current_name += char continue # 结束当前绑定变量名收集 if collecting and current_name: bind_names.add(current_name) current_name = "" collecting = False # 处理最后一个绑定变量(如果存在) if collecting and current_name: bind_names.add(current_name) # 验证参数匹配 param_keys = set(params.keys()) missing = bind_names - param_keys extra = param_keys - bind_names if missing: logging.warning(f"SQL中有但参数字典中缺失的绑定变量: {missing}") return False if extra: logging.warning(f"参数字典中有但SQL中未使用的绑定变量: {extra}") return False return True def debug_log_sql(sql, params): """安全的SQL日志记录,避免错误替换字符串字面值""" logging.debug("SQL语句:") logging.debug(sql) logging.debug("绑定参数:") safe_params = {} for k, v in params.items(): if isinstance(v, str) and len(v) > 50: safe_params[k] = f"[{v[:20]}...]" else: safe_params[k] = v logging.debug(safe_params) def generate_insert_sql(table, columns, oracle_columns, row_data): """更健壮的INSERT SQL生成器""" cols = [] values = [] bind_params = {} for col in columns: col_upper = col.upper() if col_upper not in oracle_columns: continue col_type = oracle_columns[col_upper] value = row_data[col] # 生成安全的绑定变量名 is_date_field = "DATE" in col_type or "TIMESTAMP" in col_type bind_name = generate_bind_name(col, is_date_field) # 处理日期字段 if is_date_field: values.append(f"TO_DATE(:{bind_name}, 'YYYY-MM-DD HH24:MI:SS')") bind_params[bind_name] = value # 处理NULL值 elif value is None: values.append("NULL") else: values.append(f":{bind_name}") bind_params[bind_name] = value cols.append(f'"{col_upper}"') cols_str = ", ".join(cols) values_str = ", ".join(values) sql = f"INSERT INTO {table} ({cols_str}) VALUES ({values_str})" # 验证绑定变量匹配性 if not validate_bind_variables(sql, bind_params): logging.error("生成的SQL绑定变量验证失败!") return sql, bind_params def sync_sqlite_to_oracle(sqlite_db, oracle_dsn, username, password, tables, test_mode): """最终优化的数据库同步方案""" # 计算时间范围 - 支持测试模式 if test_mode: # 指定固定测试时间范围 start_time = '2025-10-05 08:20:00' end_time = '2025-10-06 08:20:00' else: # 动态计算时间范围 now = datetime.datetime.now() start_time = (now - datetime.timedelta(days=1)).strftime('%Y-%m-%d %H:%M:%S') end_time = now.strftime('%Y-%m-%d %H:%M:%S') logging.info(f"同步时间范围: {start_time} 到 {end_time}") try: sqlite_conn = sqlite3.connect(sqlite_db) sqlite_cursor = sqlite_conn.cursor() oracle_conn = oracledb.connect( user=username, password=password, dsn=oracle_dsn ) oracle_cursor = oracle_conn.cursor() # 设置Oracle会话日期格式 oracle_cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'") oracle_conn.commit() for table in tables: primary_key = TABLE_PRIMARY_KEYS.get(table) if not primary_key: logging.error(f"未定义主键的表: {table}") continue logging.info(f"开始同步表: {table}, 主键: {primary_key}") # 获取SQLite表结构 sqlite_cursor.execute(f"PRAGMA table_info({table})") columns = [col[1] for col in sqlite_cursor.fetchall()] # 获取Oracle列信息 oracle_cursor.execute(""" SELECT column_name, data_type FROM user_tab_columns WHERE table_name = UPPER(:table_name) """, table_name=table) oracle_columns = {col[0].upper(): col[1].upper() for col in oracle_cursor.fetchall()} # 查询变更记录 sqlite_cursor.execute(f""" SELECT * FROM {table} WHERE UpdateAt BETWEEN ? AND ? """, (start_time, end_time)) rows = sqlite_cursor.fetchall() for row in rows: row_dict = dict(zip(columns, row)) oracle_compatible_data = {} # 格式化数据为Oracle兼容格式 for col_name, value in row_dict.items(): col_upper = col_name.upper() if col_upper in oracle_columns: col_type = oracle_columns[col_upper] # 处理日期字段 if "DATE" in col_type or "TIMESTAMP" in col_type: formatted = format_date_value(value) oracle_compatible_data[col_name] = formatted if formatted else value else: oracle_compatible_data[col_name] = value else: oracle_compatible_data[col_name] = value # 检查记录是否存在 pk_value = oracle_compatible_data[primary_key] oracle_cursor.execute(f""" SELECT COUNT(*) FROM {table} WHERE "{primary_key.upper()}" = :pk_value """, pk_value=pk_value) if oracle_cursor.fetchone()[0] > 0: # 更新记录 update_sql, update_params = generate_update_sql( table, columns, primary_key, oracle_columns, oracle_compatible_data ) sql_to_execute = update_sql params_to_execute = update_params else: # 插入记录 insert_sql, insert_params = generate_insert_sql( table, columns, oracle_columns, oracle_compatible_data ) sql_to_execute = insert_sql params_to_execute = insert_params # 执行SQL try: debug_log_sql(sql_to_execute, params_to_execute) oracle_cursor.execute(sql_to_execute, params_to_execute) except oracledb.DatabaseError as e: handle_oracle_error(e, sql_to_execute, params_to_execute) raise oracle_conn.commit() logging.info(f"{table} 同步完成") logging.info("所有表同步成功") except Exception as e: logging.error(f"同步失败: {str(e)}", exc_info=True) if 'oracle_conn' in locals(): oracle_conn.rollback() finally: if 'sqlite_conn' in locals(): sqlite_conn.close() if 'oracle_conn' in locals(): oracle_conn.close() def format_date_value(value): """格式化日期值为Oracle兼容格式""" if isinstance(value, str): match = DATE_REGEX.match(value) if match: groups = match.groups() return f"{groups[0]}-{groups[1]}-{groups[2]} {groups[3]}:{groups[4]}:{groups[5]}" elif isinstance(value, datetime): return value.strftime('%Y-%m-%d %H:%M:%S') return value def handle_oracle_error(error, sql, params): """详细的Oracle错误处理""" try: error_obj = error.args[0] logging.error(f"Oracle错误: {error_obj.message} (代码: {error_obj.code})") except: logging.error(f"数据库错误: {str(error)}") logging.error(f"问题SQL: {sql}") logging.error(f"绑定参数: {params}") # 提供具体解决方案 if "ORA-01745" in str(error): logging.error("解决方案: 检查绑定变量名是否包含无效字符或Oracle关键字") elif "DPY-4008" in str(error): logging.error("解决方案: 确保所有绑定变量在SQL和参数字典中精确匹配") logging.error("检查步骤: 使用validate_bind_variables函数验证一致性") def generate_update_sql(table, columns, primary_key, oracle_columns, row_data): """更健壮的UPDATE SQL生成器""" set_clauses = [] bind_params = {} for col in columns: if col == primary_key: continue col_upper = col.upper() if col_upper not in oracle_columns: continue col_type = oracle_columns[col_upper] value = row_data[col] # 生成安全的绑定变量名 is_date_field = "DATE" in col_type or "TIMESTAMP" in col_type bind_name = generate_bind_name(col, is_date_field) # 处理日期字段 if is_date_field: set_clauses.append(f'"{col_upper}" = TO_DATE(:{bind_name}, \'YYYY-MM-DD HH24:MI:SS\')') bind_params[bind_name] = value # 处理NULL值 elif value is None: set_clauses.append(f'"{col_upper}" = NULL') else: set_clauses.append(f'"{col_upper}" = :{bind_name}') bind_params[bind_name] = value # 添加主键条件 pk_bind_name = generate_bind_name(primary_key) bind_params[pk_bind_name] = row_data[primary_key] set_clause = ", ".join(set_clauses) sql = f"UPDATE {table} SET {set_clause} WHERE \"{primary_key.upper()}\" = :{pk_bind_name}" # 验证绑定变量匹配性 if not validate_bind_variables(sql, bind_params): logging.error("生成的SQL绑定变量验证失败!") return sql, bind_params if __name__ == "__main__": # 配置参数 SQLITE_DB = "D:\\IISwebOEE\\App_Data\\webFrameworkEF6.db" ORACLE_DSN = "at3-pacc-f2db.zf-world.com/AT3PACC2" USERNAME = "acc_oee2" PASSWORD = "accZF_2025" TABLES = [ "OEE_AvaiableTime", "OEE_CycleTime", "OEE_LineReport", "OEE_PerformanceComment", "OEE_ProductStatus", "OEE_Quality", "OEE_ShiftPlan_Config", "OEE_ShiftWorkTime", "OEE_Target_Config", "OEE_CycleTime_Config" ] # 启用测试模式使用固定时间范围 sync_sqlite_to_oracle( SQLITE_DB, ORACLE_DSN, USERNAME, PASSWORD, TABLES, test_mode=True # 启用测试模式 ) 目前是遇到问题 2025-11-07 09:42:42,481 - INFO - 开始同步表: OEE_AvaiableTime, 主键: OEE_AvaiableTimeId 2025-11-07 09:42:47,025 - DEBUG - SQL语句: 2025-11-07 09:42:47,025 - DEBUG - INSERT INTO OEE_AvaiableTime ("OEE_AVAIABLETIMEID", "STARTTIME", "ENDTIME", "DURATION", "TYPE", "OP", "PARTNO", "LOSSREASON", "COMMENT", "SHIFTWORKTIMEID", "CREATEAT", "UPDATEAT", "BYUSER") VALUES (:oee_avaiabletimeid, :starttime, :endtime, :duration, :_type_, NULL, NULL, NULL, :_comment_, :shiftworktimeid, TO_DATE(:createat_dt, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(:updateat_dt, 'YYYY-MM-DD HH24:MI:SS'), :byuser) 2025-11-07 09:42:47,025 - DEBUG - 绑定参数: 2025-11-07 09:42:47,025 - DEBUG - {'oee_avaiabletimeid': '6b245d77-56ae-429e-809b-f8b71ddf19fb', 'starttime': '176', 'endtime': '179', 'duration': 15, '_type_': '13', '_comment_': '2人,22小时', 'shiftworktimeid': 'd3369a2c-5097-4654-b63c-4a7812b6e54b', 'createat_dt': '2025-10-04 12:37:08', 'updateat_dt': '2025-10-05 18:51:51', 'byuser': 'AD'} 2025-11-07 09:42:47,026 - ERROR - Oracle错误: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text (代码: 0) 2025-11-07 09:42:47,026 - ERROR - 问题SQL: INSERT INTO OEE_AvaiableTime ("OEE_AVAIABLETIMEID", "STARTTIME", "ENDTIME", "DURATION", "TYPE", "OP", "PARTNO", "LOSSREASON", "COMMENT", "SHIFTWORKTIMEID", "CREATEAT", "UPDATEAT", "BYUSER") VALUES (:oee_avaiabletimeid, :starttime, :endtime, :duration, :_type_, NULL, NULL, NULL, :_comment_, :shiftworktimeid, TO_DATE(:createat_dt, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(:updateat_dt, 'YYYY-MM-DD HH24:MI:SS'), :byuser) 2025-11-07 09:42:47,026 - ERROR - 绑定参数: {'oee_avaiabletimeid': '6b245d77-56ae-429e-809b-f8b71ddf19fb', 'starttime': '176', 'endtime': '179', 'duration': 15, '_type_': '13', '_comment_': '2人,22小时', 'shiftworktimeid': 'd3369a2c-5097-4654-b63c-4a7812b6e54b', 'createat_dt': '2025-10-04 12:37:08', 'updateat_dt': '2025-10-05 18:51:51', 'byuser': 'AD'} 2025-11-07 09:42:47,026 - ERROR - 解决方案: 确保所有绑定变量在SQL和参数字典中精确匹配 2025-11-07 09:42:47,027 - ERROR - 检查步骤: 使用validate_bind_variables函数验证一致性 2025-11-07 09:42:47,027 - ERROR - 同步失败: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text Traceback (most recent call last): File "C:\UserData\Python\SyncSqlitedb\sync_service.py", line 275, in sync_sqlite_to_oracle oracle_cursor.execute(sql_to_execute, params_to_execute) ~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "C:\Users\guc3\AppData\Roaming\Python\Python313\site-packages\oracledb\cursor.py", line 708, in execute impl.execute(self) ~~~~~~~~~~~~^^^^^^ File "src/oracledb/impl/thin/cursor.pyx", line 275, in oracledb.thin_impl.ThinCursorImpl.execute File "src/oracledb/impl/thin/cursor.pyx", line 182, in oracledb.thin_impl.BaseThinCursorImpl._preprocess_execute File "src/oracledb/impl/base/cursor.pyx", line 351, in oracledb.base_impl.BaseCursorImpl._perform_binds File "src/oracledb/impl/thin/var.pyx", line 95, in oracledb.thin_impl.ThinVarImpl._bind File "C:\Users\guc3\AppData\Roaming\Python\Python313\site-packages\oracledb\errors.py", line 199, in _raise_err raise error.exc_type(error) from cause oracledb.exceptions.DatabaseError: DPY-4008: no bind placeholder named ":_type_" was found in the SQL text 请帮忙解决
11-08
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值