fish-404 / fish-404.github.io Goto Github PK

Null in SQL is more complex than I think before, will open a new post to record learning.

• null with max()
• null with count()
• null in join
• null with not in
• case without else
• null in Aggregate, Analytic functions
• null in Date & time functions
• null with FIRST_VALUE | LAST_VALUE
• null with order by

Reference:

• Handle NULLs - Learn | Microsoft Docs

Would like to realize:

1. use a GitHub action to sync public repo issues to private issues (open, reopen, closed)
2. use a GitHub action to sync branches between public and private
3. when new commits push to private repo, deploy to public repo in a preview status
4. when preview confirmed, publish

Reference :
GitHub Pages | Hexo

Reference:

https://csharpindepth.com/articles/Strings
https://jonskeet.uk/csharp/stringbuilder.html

Discussion #7 to record choice investigation.
Related to #3.

• LAST_VALUE
• FIRST_VALUE
• ROW_NUMBER
• ……

Reference (checkbox means has read completely or not)

• https://stackoverflow.com/questions/47808336/last-value-returning-multiple-records
• https://docs.microsoft.com/en-us/sql/t-sql/functions/first-value-transact-sql?toc=%2Fazure%2Fazure-sql-edge%2Ftoc.json&view=sql-server-ver16
• https://docs.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql?view=sql-server-ver16
• https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/introduction-to-t-sql-window-functions/
• https://stackoverflow.com/questions/15388892/sql-last-value-returns-wrong-result-but-first-value-works-fine/26806428#26806428
• https://tapoueh.org/blog/2013/08/understanding-window-functions/
• https://blog.jooq.org/probably-the-coolest-sql-feature-window-functions/
• https://blog.jooq.org/the-difference-between-row_number-rank-and-dense_rank/
• https://blog.jooq.org/dont-miss-out-on-awesome-sql-power-with-first_value-last_value-lead-and-lag/

Case:

• one badge has multiple card_code
• each card_code has a remove_date if it has been removed
• need get each badge last card_code and update date (i.e. removed date last removed card_code)

May related to #3

Background:

Many applications I managed has string connected SQL methods. It's hard to read and debug and maintain. When I try out to find some better methods to write SQL in C#, find something more about this problem. It seems a topic which deserve to deep into.

1. Concatenated SQL
2. Parameterized SQL
3. SQL Injection
4. Query Plan
5. Table-Valued Parameter (maybe a post)

Reference:

• Two Phase Commit (part of Patterns of Distributed Systems | ZH)
• 设计数据密集型应用 (第七章）
• 凤凰架构 (事务处理部分）
• Error and Transaction Handling in SQL Server
• XACT_STATE(Transact-SQL) - SQL Server | Microsoft Docs
• BEGIN TRANSACTION(Transact-SQL)-SQL Server | Microsoft Docs
• TRY...CATCH(Transact-SQL)-SQL Server | Microsoft Docs

.Net seems have many methods to get the current folder. For different types of the application/service, use the some method to get folder may get nothing.

Write logs usually need to get folder first and write the log. If you can't get the folder, you also can't write the log.

• HttpServerUtility.MapPath(String)
• HostingEnvironment.MapPath(String)
• HttpRequest.MapPath(String)
• Assembly.Location
• Directory.GetCurrentDirectory()

Reference:

• c# - Best way to get application folder path - Stack Overflow
• Application current folder in Asp.net C# - AspTricks.net

Troels' links: Relational database systems
https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-ver16&source=docs
https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver16#guidelines-for-creating-and-using-common-table-expressions
https://stackoverflow.com/questions/51176971/how-do-recursive-ctes-work-in-sql-server
Tutorial: Using the hierarchyid Data Type - SQL Server | Microsoft Learn

Image you have a group of data struct like below, and you need order and group each card_id in a certain time interval, after the interval, you should open a new group.

DB Fiddle

If the time interval is 2 minutes, you want get this:

Reference:

https://stackoverflow.com/questions/49830909/partition-by-time-interval/49831036#49831036

Compare:

• Difference between WCF, Web API, WCF REST and Web Service? - Stack Overflow
• WCF and ASP.NET Web API - WCF | Microsoft Learn
• SOAP vs REST API protocols - What is ? Difference & Benefits

Packages:

• imagemagick - ImageMagick
• ffmpeg¹
• libxml2-dev - libxml2-devel
• libxslt1-dev - libxslt-devel
• libyaml-dev - libyaml-devel
• libffi-dev - libffi-devel
• g++ gcc autoconf build-essential - group 'Development Tools' ²³
• redis-server redis-tools - redis ⁴
• libjemalloc-dev - jemalloc-devel ⁵
• libssl-dev - openssl-devel
• pkg-config - pkgconfig
• libprotobuf-dev - protobuf-devel
• libgdbm-dev - gdbm-devel
• libreadline6-dev - readline-devel
• zlib1g-dev - zlib-devel
• libidn11-dev - libidn-devel
• libncurses5-dev - ncurses-devel

Some deploy guide⁶ writes clearly, but miss some packages after mastodon has upgraded to a new version.

About Node.js and Yarn:

• When you read guides for installation, you sometimes find curl https://xxxxxxx/setup_[version].x, be careful with the version.⁷

• If you have installed Node.js from other source, you will get a warning, follow the installation hint to install Yarn package manager.

   curl -sL https://dl.yarnpkg.com/rpm/yarn.repo | sudo tee /etc/yum.repos.d/yarn.repo
   sudo yum install yarn

Useful websites:

• Packages for Linux and Unix
• Yum Info Search

根据 next-theme/hexo-theme-next#4 的说明，NexT 社区仓库已经迁移， 新版本的发布都在新仓库进行

一些需要前置处理的问题背景：

• NexT 8.0 版本已不再支持 Valine 作为内置评论服务
• LeanCloud 于2022-08-01起将停止向国内提供国际版服务
• NexT 将不再支持任何与 LeanCloud 相关的评论系统，也不再支持类 Valine 的衍生评论系统

Related: #2 #9