csharp/0ffffffffh/sozluk-cgi-revival/sozluk_backend/Core/Sys/DataStore/SozlukDataStore.cs

SozlukDataStore.cs
using sozluk_backend.Core.Cache;
using sozluk_backend.Core.Sys.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace sozluk_backend.Core.Sys.DataStore
{

    [Serializable]
    clast QueryResultBase
    {
        public List Entries;
        public int TotalRecordCount;
        public int LogicalRecordCount;
        public int PhysicalRecordCount;


        public bool HasEntry
        {
            get
            {
                if (this.Entries == null)
                    return false;

                return this.Entries.Count > 0;
            }
        }
    }

    [Serializable]
    clast SearchAndIndexQueryResult : QueryResultBase
    {
        public string PagerHash;

        public SearchAndIndexQueryResult()
        {
            this.Entries = new List();
        }
    }

    [Serializable]
    clast ViewQueryResult : QueryResultBase
    {
        public int BaslikId;

        public ViewQueryResult()
        {
            this.Entries = new List();
        }
    }

    [Serializable]
    clast BaslikBasicInfo
    {
        public long TotalEntries;
        //additional info defs here.
    }

    internal enum RecordPerPageType
    {
        Entries,
        Basliks
    }

    internal clast SozlukDataStore
    {
        static readonly TimeSpan EntriesOfBaslikTimeout = TimeSpan.FromMinutes(30);
        static readonly TimeSpan TodaysTimeout = TimeSpan.FromSeconds(30);
        static readonly TimeSpan AllBasliksTimeout = TimeSpan.FromMinutes(5);
        static readonly TimeSpan IndexedBasliksTimeout = TimeSpan.FromMinutes(15);
        static readonly TimeSpan SearchResultTimeout = TimeSpan.FromMinutes(5);
        static readonly TimeSpan SuserInfoTimeout = TimeSpan.MaxValue;

        private static object lockObj;

        internal static int RecordsPerPage
        {
            get
            {
                return Config.Get().RecordCountPerPage;
            }
        }

        internal static int BasliksPerPage
        {
            get
            {
                return Config.Get().BaslikCountPerPage;
            }
        }

        internal static int CalcPageCount(long totalEntries, RecordPerPageType type)
        {
            int recordPerPage = 0;

            switch (type)
            {
                case RecordPerPageType.Basliks:
                    recordPerPage = BasliksPerPage;
                    break;
                case RecordPerPageType.Entries:
                    recordPerPage = RecordsPerPage;
                    break;
                default:
                    return 1;
            }

            int pageCount = (int)(totalEntries / recordPerPage) + 1;

            if (pageCount > 1 && totalEntries % recordPerPage == 0)
            {
                pageCount--;
            }

            return pageCount;
        }

        private static readonly string ADD_SUSER_SQL =
            "IF NOT EXISTS (SELECT Suser FROM Susers WHERE Suser = '{0}') " +
            "BEGIN INSERT INTO Susers(Suser,Pastword,DummyMail) VALUES('{0}','{1}','{2}') END";

        private static readonly string GET_SUSER_SQL =
            "SELECT Suser, Pastword FROM Susers WHERE Suser = '{0}';";

        private static readonly string NEW_ENTRY_SQL =
            "DECLARE @BaslikIdent AS INT; " +
            "DECLARE @NewBaslikInsert AS INT = 0; " +
            "SELECT @BaslikIdent = Id FROM Basliks WHERE Baslik = '{0}'; " +
            "IF @BaslikIdent IS NULL " +
            "BEGIN " +
            "INSERT INTO Basliks(Baslik) VALUES('{0}'); " +
            "SET @BaslikIdent = @@IDENsatY; " +
            "SET @NewBaslikInsert = 1;" +
            "END " +
            "INSERT INTO Entries (BaslikId, SuserId, Date,Descr) " +
            "VALUES(@BaslikIdent, (SELECT Id FROM Susers WHERE Suser = '{1}'), '{2}', '{3}'); " +
            "SELECT @BaslikIdent As BaslikId, @NewBaslikInsert As IsNewBaslikInsert;";
        
        private static readonly string SEARCH_SUSER_ID_GET_SQL =
            "DECLARE @SuserIdent INT = 0;" +
            "SELECT @SuserIdent = Id FROM Susers WHERE Suser = '{0}';";


        private static readonly string SEARCH_COND_COUNT_CONTENT =
            "CASE WHEN Baslik LIKE '%{0}%' AND Descr NOT LIKE '%{0}%' THEN NULL ELSE Baslik END";

        private static readonly string SEARCH_COND_COUNT_ALL = "Baslik";

        private static readonly string SEARCH_SQL_ALL_BASE =
            "SELECT * FROM (" +
            "SELECT ROW_NUMBER() OVER(Order By Basliks.Baslik) As RowNum, " +
            "Basliks.Baslik, " +
            "COUNT(Entries.BaslikId) As EntryCount, " +
            "COUNT(*) OVER() As TotalRecordCount " +
            "FROM Entries INNER JOIN Basliks ON Basliks.Id = Entries.BaslikId %%CONDITION%% GROUP BY Basliks.Baslik " +
            ") X WHERE %%ROW_LIMIT_CONDITION%%";

        private static readonly string GET_ENTRIES_OF_BASLIK_SQL_BASE =
            "SELECT * FROM(" +
            "SELECT " +
            "ROW_NUMBER() OVER(Order By Basliks.Id) as RowNum," +
            "Basliks.Id, " + 
            "Basliks.Baslik," +
            "Susers.Suser," +
            "Entries.Descr As[Entry]," +
            "Entries.Date As SubmitDate, " +
            "COUNT(*) OVER() AS TotalRecord " +
            "FROM Entries " +
            "INNER JOIN Basliks ON Basliks.Id = Entries.BaslikId " +
            "INNER JOIN Susers ON Susers.Id = Entries.SuserId " +
            "WHERE %%BASLIK_SEARCH_CONDITION%% " +
            ") X WHERE %%ROW_LIMIT_CONDITION%%;";

        private static readonly string SEARCH_SQL_BASE =
            "SELECT * FROM ( " +
            "SELECT " +
            "ROW_NUMBER() OVER(Order By Baslik) as RowNum, Baslik," +
            "COUNT( %%COUNT_CONDITION%% ) As EntryCount," +
            "COUNT(*) OVER() As TotalRecordCount " +
            "FROM " +
            "(SELECT Entries.Id, Basliks.Baslik, Susers.Suser, Entries.Descr,Entries.Date FROM Basliks " +
            "INNER JOIN Entries ON Basliks.Id = Entries.BaslikId " +
            "INNER JOIN Susers ON Susers.Id = Entries.SuserId " +
            "%%CONDITIONS%%" +
            ") ConditionSubQuery GROUP BY Baslik " +
            ") FilterQuery WHERE %%ROW_LIMIT_CONDITION%%";

        private static readonly string SEARCH_COND_SUSER =
            "(Entries.SuserId = @SuserIdent)";

        private static readonly string SEARCH_COND_CONTENT =
            "(Basliks.Baslik LIKE '%{0}%' OR Entries.Descr LIKE '%{0}%')";

        private static readonly string SEARCH_COND_DATE =
            "(Entries.Date BETWEEN '{0}' AND '{1}')";

        static SozlukDataStore()
        {
            lockObj = new object();
        }

        private static void GetTodaysDateRange(out DateTime todayBegin, out DateTime justNow)
        {
            DateTime now = DateTime.Now;
            todayBegin = new DateTime(now.Year, now.Month, now.Day, 0, 0, 0);
            justNow = new DateTime(now.Year,now.Month,now.Day,now.Hour,now.Minute,now.Second = 'a' && indexChar  0)
                {
                    cond.Insert(0, "WHERE ");
                    sb.Replace("%%CONDITIONS%%", cond.ToString());
                }
                else
                {
                    sb.Replace("%%CONDITIONS%%", string.Empty);
                }

                if (!string.IsNullOrEmpty(content))
                    sb.Replace("%%COUNT_CONDITION%%", string.Format(SEARCH_COND_COUNT_CONTENT, content));
                else
                    sb.Replace("%%COUNT_CONDITION%%", SEARCH_COND_COUNT_ALL);


                if (!string.IsNullOrEmpty(suser))
                    sb.Append(" AND EntryCount > 0");

                sb.Append(";");

                baseQueryHash = Helper.Md5(sb.ToString());

                CacheManager.CacheObject(baseQueryHash, sb.ToString());
            }
            else
            {
                sb.Append(query);
            }

            sb.Replace("%%ROW_LIMIT_CONDITION%%",
                string.Format("RowNum BETWEEN {0} AND {1}", rowBegin, rowEnd));
            
            query = sb.ToString();

            cond.Clear();
            sb.Clear();

            cond = null;
            sb = null;

            return query;
        }

        public static bool AddSuser(Suser suser, out bool registered)
        {
            bool result;

            registered = false;

            if (GetSuser(suser.SuserName) != null)
            {
                return true;
            }

            SqlServerIo sql = SqlServerIo.Create();

            if (!sql.Ready)
                return false;

            result = sql.Execute(
                true, 
                ADD_SUSER_SQL, 
                suser.SuserName, suser.PastwordHash, "[email protected]"
                );

            if (result)
            {
                registered = sql.RecordCount > 0;
            }


            SqlServerIo.Release(sql);

            return result;
        }

        public static bool AddEntry(Entry entry)
        {
            bool result;

            if (string.IsNullOrEmpty(entry.Content))
                return false;

            SqlServerIo sql = SqlServerIo.Create();

            if (!sql.Ready)
                return false;

            //naa
            entry.FixForMultipleLineFeeds();

            result = sql.Execute(false, NEW_ENTRY_SQL, entry.Baslik,entry.Suser,entry.Date.ToString(),entry.Content);

            if (result)
            {
                
                if (!sql.Read())
                    result = false;
                else
                {
                    entry.SetId(sql.GetValueOfColumn("BaslikId"));

                    
                    CacheManager.InvalidateCacheSet(KeysetId.Baslik(entry.BaslikID));

                    var indexKeyid = KeysetId.Index(entry.Baslik[0]);
                    CacheManager.InvalidateCacheSet(indexKeyid);
                    
                    if (sql.GetValueOfColumn("IsNewBaslikInsert") == 1)
                    {
                        //invalidate also todays section keyset a.k.a Taze
                        CacheManager.InvalidateCacheSet(KeysetId.Todays());
                    }
                }
                
            }


            SqlServerIo.Release(sql);

            return result;
        }

        public static Suser GetSuser(string suser)
        {

            string query = string.Format(GET_SUSER_SQL, suser.Trim().ToLower());
            Suser suserObject = null;

            if (!CacheManager.TryGetCachedQueryResult(query,out suserObject))
            {
                SqlServerIo sql = SqlServerIo.Create();
                
                if (sql.Execute(false, query))
                {
                    if (sql.Read())
                    {
                        suserObject = new Suser(
                            0,
                            sql.GetValueOfColumn("Suser"),
                            sql.GetValueOfColumn("Pastword")
                            );

                    }
                    
                }

                SqlServerIo.Release(sql);
                
            }
            
            return suserObject;
        }

        private static SearchAndIndexQueryResult FetchBasliksIndexed(int pageNumber, char beginChar, string pagerHash)
        {
            SearchAndIndexQueryResult resultSet;
            SqlServerIo sql;
            TimeSpan invTimeout;
            int rowBegin, rowEnd;
            string query, baslik;
            int entryCount;

            rowBegin = (pageNumber * BasliksPerPage) + 1;
            rowEnd = rowBegin + BasliksPerPage - 1;

            beginChar = char.ToLower(beginChar);

            if (beginChar == '.')
                invTimeout = AllBasliksTimeout;
            else
                invTimeout = IndexedBasliksTimeout;
            
            query = BuildFetchAllSQLQuery(ref pagerHash, rowBegin, rowEnd,beginChar);
            
            if (!CacheManager.TryGetCachedQueryResult(query, out resultSet))
            {
                sql = SqlServerIo.Create();

                if (!sql.Execute(false, query))
                {
                    SqlServerIo.Release(sql);
                    return new SearchAndIndexQueryResult();
                }

                resultSet = new SearchAndIndexQueryResult
                {
                    PagerHash = pagerHash
                };

                while (sql.Read())
                {
                    baslik = sql.GetValueOfColumn("Baslik");
                    entryCount = sql.GetValueOfColumn("EntryCount");

                    if (resultSet.TotalRecordCount == 0)
                        resultSet.TotalRecordCount = sql.GetValueOfColumn("TotalRecordCount");

                    resultSet.Entries.Add(new Entry(baslik, string.Empty,string.Empty, string.Empty, entryCount));

                    resultSet.PhysicalRecordCount++;
                }

                resultSet.LogicalRecordCount = resultSet.Entries.Count;

                SqlServerIo.Release(sql);
                
                CacheManager.CacheObject(KeysetId.Index(beginChar,true),true, query, resultSet,invTimeout);
            }

            return resultSet;
        }



        private static string Strev(string s)
        {
            string ns = "";

            for (int i = s.Length - 1; i >= 0; i--)
                ns += s[i];

            return ns;
        }

        public static SearchAndIndexQueryResult FetchBasliksUsingSearch(bool fresh, string content, string suser, DateTime begin, DateTime end, int pageNumber, string pagerHash, bool leaveDatesAsIs)
        {
            SearchAndIndexQueryResult resultSet;
            SqlServerIo sql;
            TimeSpan invTimeout;
            int rowBegin, rowEnd;
            string query;
            KeysetId keysetId;
            string baslik, descr, deceptedDate;
            int entryCount;
            bool resultCached;

            rowBegin = (pageNumber * BasliksPerPage) + 1;
            rowEnd = rowBegin + BasliksPerPage - 1;

            //Workarounds, workarounds, workarounds !
            if (begin != DateTime.MinValue && end != DateTime.MinValue)
            {
                if (leaveDatesAsIs)
                {
                    deceptedDate = begin.AddTicks((end - begin).Ticks / 2).ToString();
                }
                else
                {
                    //push it out of from the search date range to reverse daterange check logic
                    deceptedDate = end.AddDays(2).ToString();
                }
            }
            else
                deceptedDate = string.Empty;

            query = BuildFetchSQLQuery(ref pagerHash, content, suser, begin, end, rowBegin, rowEnd);


            if (fresh)
            {
                keysetId = KeysetId.Todays(true);
                invTimeout = TodaysTimeout;
            }
            else
            {
                keysetId = KeysetId.Search(pagerHash,true);
                invTimeout = SearchResultTimeout;
            }

            resultCached = CacheManager.TryGetCachedQueryResult(query, out resultSet);

            if (!resultCached)
            {
                sql = SqlServerIo.Create();

                if (!sql.Execute(false, query))
                {
                    SqlServerIo.Release(sql);
                    return new SearchAndIndexQueryResult();
                }

                resultSet = new SearchAndIndexQueryResult
                {
                    PagerHash = pagerHash
                };

                while (sql.Read())
                {
                    baslik = sql.GetValueOfColumn("Baslik");
                    entryCount = sql.GetValueOfColumn("EntryCount");

                    if (resultSet.TotalRecordCount == 0)
                        resultSet.TotalRecordCount = sql.GetValueOfColumn("TotalRecordCount");

                    if (entryCount>0)
                        descr = content;
                    else
                        descr = Strev(content);

                    if (string.IsNullOrEmpty(suser))
                        suser = string.Empty;

                    resultSet.Entries.Add(
                        new Entry(
                            baslik, 
                            suser, 
                            deceptedDate, 
                            descr,entryCount)
                            );

                    resultSet.PhysicalRecordCount++;
                }

                resultSet.LogicalRecordCount = resultSet.Entries.Count;

                SqlServerIo.Release(sql);

                CacheManager.CacheObject(keysetId,true, query, resultSet,invTimeout);
            }

            return resultSet;
        }

        public static SearchAndIndexQueryResult FetchBasliks(string index, int pageNumber, string pagerHash)
        {
            DateTime begin, end;
            
            //fresh contents
            if (string.IsNullOrEmpty(index))
            {
                GetTodaysDateRange(out begin, out end);

                return FetchBasliksUsingSearch(true,string.Empty, string.Empty, begin, end, pageNumber,pagerHash,true);
            }

            index = index.ToLower();

            if (index == "all")
                return FetchBasliksIndexed(pageNumber,'.',pagerHash);

            if (index == "*")
                return FetchBasliksIndexed(pageNumber, '*',pagerHash);

            if (index[0] >= 'a' && index[0]  0)
                searchCondition = "Basliks.Id = " + baslikId.ToString();
            else
                searchCondition = string.Format("Basliks.Baslik = '{0}'", baslik);

            query = GET_ENTRIES_OF_BASLIK_SQL_BASE.Replace("%%BASLIK_SEARCH_CONDITION%%", searchCondition);


            query = query.Replace("%%ROW_LIMIT_CONDITION%%",
                string.Format("RowNum BETWEEN {0} AND {1}", rowBegin, rowEnd));

            return query;
        }

        public static ViewQueryResult FetchEntriesOfBaslik(string baslik, int baslikId, int pageNumber)
        {
            SqlServerIo sql;
            string query;
            ViewQueryResult resultSet;

            query = BuildEntryFetchSQL(baslik, baslikId, pageNumber);

            
            if (!CacheManager.TryGetCachedQueryResult(query, out resultSet))
            {
                sql = SqlServerIo.Create();

                if (!sql.Execute(false, query))
                {
                    SqlServerIo.Release(sql);
                    return new ViewQueryResult();
                }

                resultSet = new ViewQueryResult();

                while (sql.Read())
                {
                    if (resultSet.TotalRecordCount == 0)
                    {
                        resultSet.TotalRecordCount = sql.GetValueOfColumn("TotalRecord");
                        resultSet.BaslikId = sql.GetValueOfColumn("Id");
                    }

                    Entry e = new Entry(
                        sql.GetValueOfColumn("Baslik"),
                        sql.GetValueOfColumn("Suser"),
                        sql.GetValueOfColumn("SubmitDate").ToString(),
                        sql.GetValueOfColumn("Entry"));

                    resultSet.Entries.Add(e);
                    resultSet.PhysicalRecordCount++;

                }

                SqlServerIo.Release(sql);

                //Dont cache for empty recordset for baslik
                if (!resultSet.HasEntry)
                    return resultSet;

                resultSet.LogicalRecordCount = resultSet.PhysicalRecordCount;

                CacheManager.CacheObject(KeysetId.Baslik(resultSet.BaslikId), true, query, resultSet);

                BaslikBasicInfo bbi = new BaslikBasicInfo()
                {
                    TotalEntries = resultSet.TotalRecordCount
                };

                CacheManager.CacheObject("BBI_" + resultSet.BaslikId.ToString(),
                    bbi,
                    TimeSpan.FromMinutes(10));


                //if this request initial fetch using the baslik string.
                //rebuild sql with baslikid and cache the result with its hashkey
                if (baslikId == 0)
                {
                    query = BuildEntryFetchSQL(null, resultSet.BaslikId, pageNumber);
                    CacheManager.CacheObject(KeysetId.Baslik(resultSet.BaslikId),true, query, resultSet);
                }
            }

            return resultSet;
        }

    }

}