Announcement

Collapse
No announcement yet.

Create local copy of PBforum.db

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Create local copy of PBforum.db

    The attached code will use a combination of SQLitening (remote database) and SQLite (local database) to make a row by row copy of the remote database to a local copy.

    If the local database doesn't exist it will be created. If the database exists, new remote rows for each table are copied over to the local database tables.

    WARNING: The database is currently 678MB...the first build may take a few minutes. My testing was taking about 3.5 minutes to get the full database. Lots of things impact this speed.

    Code:
    #BREAK         ON
    #COMPILE       EXE
    #DEBUG DISPLAY ON
    #DEBUG ERROR   ON
    #DIM           ALL
    #OPTIMIZE      SPEED
    #REGISTER      NONE
    #TOOLS         OFF
    
    #INCLUDE ONCE "SQLitening.inc"
    #INCLUDE ONCE "sqlite3.inc"
    
    #IF NOT %DEF(%NULL)
       %NULL = 0???
    #ENDIF
    
    ' SQLite macro helpers
    MACRO SQL_EXE(phDB_Stmt,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_exec(phDB_Stmt,BYVAL STRPTR(Variable),%NULL,%NULL,%NULL)
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "Error...""sqlite3_exec"" failed   Error Code = " + FORMAT$(dwRC)
          CON.STDOUT Variable
       END IF
    END MACRO
    
    MACRO SQL_BindInt(phDB_Stmt,SQL_StmtParmIndex,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_bind_int(phDB_Stmt,SQL_StmtParmIndex,Variable)
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "Error...""sqlite3_bind_int"" failed   Error Code = " + FORMAT$(dwRC)
       END IF
    END MACRO
    
    MACRO SQL_BindString(phDB_Stmt,SQL_StmtParmIndex,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_bind_text(phDB_Stmt,SQL_StmtParmIndex,BYVAL STRPTR(Variable),LEN(Variable),%SQLITE_STATIC)
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "Error...""sqlite3_bind_text"" failed   Error Code = " + FORMAT$(dwRC)
       END IF
    END MACRO
    
    FUNCTION PBMAIN
    
       LOCAL dwRC                                    AS LONG
       LOCAL dwWork                                  AS LONG
    
       LOCAL pDB                                     AS LONG POINTER
       LOCAL hSQL                                    AS DWORD
       LOCAL sSQL                                    AS STRING
       LOCAL sDB_LOCAL_FileName                      AS STRING
    
       ' Database table fields
       LOCAL dwCol_RowID                             AS DWORD
       LOCAL sCol_RecordAddedDate                    AS STRING
       LOCAL sCol_Name                               AS STRING
       LOCAL sCol_ForumURL                           AS STRING
       LOCAL sCol_Author                             AS STRING
       LOCAL dwCol_ThreadNumber                      AS DWORD
       LOCAL sCol_ThreadURL                          AS STRING
       LOCAL dwCol_Forum_Link                        AS DWORD
       LOCAL sCol_ThreadTitle                        AS STRING
       LOCAL dwCol_Thread_Link                       AS DWORD
       LOCAL dwCol_PostNumber                        AS DWORD
       LOCAL dwCol_Author_Link                       AS DWORD
       LOCAL sCol_PostDate                           AS STRING
       LOCAL sCol_PostContent                        AS STRING
    
       sDB_LOCAL_FileName = EXE.Path$ + "PBforum_LOCAL.db"
    
       ' Create/Open the LOCAL SQLite database
       dwRC = sqlite3_open_v2(BYVAL STRPTR(sDB_LOCAL_FileName),pDB,%SQLITE_OPEN_READWRITE OR %SQLITE_OPEN_CREATE,"")
    
       IF dwRC <> %SQLITE_OK THEN
          STDOUT "Error opening database"
          EXIT FUNCTION
       END IF
    
       ' Set SQLite Database Options
       sSQL = "PRAGMA foreign_keys=TRUE; "                                       + _
              "PRAGMA journal_mode=DELETE; "                                     + _
              "PRAGMA synchronous=NORMAL;"
       SQL_EXE(pDB,sSQL)
    
       ' Create the LOCAL database tables if they don't exist
       sSQL = "CREATE TABLE IF NOT EXISTS Forums "                               + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Name TEXT(50) UNIQUE NOT NULL COLLATE NOCASE, "                  + _
              " ForumURL TEXT UNIQUE NOT NULL COLLATE NOCASE); "                 + _
              "CREATE TABLE IF NOT EXISTS ForumAuthors "                         + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Author TEXT(25) UNIQUE NOT NULL COLLATE NOCASE); "               + _
              "CREATE TABLE IF NOT EXISTS Threads "                              + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " ThreadNumber INTEGER UNIQUE NOT NULL, "                          + _
              " ThreadURL TEXT UNIQUE NOT NULL COLLATE NOCASE, "                 + _
              " Forum_Link INTEGER NOT NULL, "                                   + _
              " ThreadTitle TEXT(20) NOT NULL COLLATE NOCASE, "                  + _
              " FOREIGN KEY(Forum_Link) REFERENCES Forums(RowID)); "             + _
              "CREATE TABLE IF NOT EXISTS ThreadContent "                        + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Thread_Link INTEGER NOT NULL, "                                  + _
              " PostNumber INTEGER NOT NULL, "                                   + _
              " Author_Link INTEGER NOT NULL, "                                  + _
              " PostDate TEXT(15) NOT NULL, "                                    + _
              " PostContent TEXT NOT NULL, "                                     + _
              " FOREIGN KEY(Thread_Link) REFERENCES Threads(ThreadNumber),"      + _
              " FOREIGN KEY(Author_Link) REFERENCES ForumAuthors(RowID));"
       SQL_EXE(pDB,sSQL)
    
       ' Connect and Open the remote database
       slConnect "38.17.54.213",51234
       slOpen "PBforum\PBforum.db" + $BS + "Read%Only", "R"  '1 open with read-only password
    
       sSQL = "BEGIN TRANSACTION"
       SQL_EXE(pDB,sSQL)         ' Begin SQLite transaction
    
       ' ------------------------   Copy table "Forums"   --------------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO Forums "                                              + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Name, "                                               + _
              "            ForumURL) "                                           + _
              "     VALUES(?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_prepare_v2 failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Name, "                                                 + _
              "       T1.ForumURL "                                              + _
              "  FROM Forums T1 "                                                + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"Forums"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          sCol_Name            = slF(3)
          sCol_ForumURL        = slF(4)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindString(hSQL,3,sCol_Name)
          SQL_BindString(hSQL,4,sCol_ForumURL)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             CON.STDOUT "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             CON.STDOUT "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ----------------------   Copy table "ForumAuthors"   ----------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO ForumAuthors "                                        + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Author) "                                             + _
              "     VALUES(?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_prepare_v2 failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Author "                                                + _
              "  FROM ForumAuthors T1 "                                          + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"ForumAuthors"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          sCol_Author          = slF(3)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindString(hSQL,3,sCol_Author)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             CON.STDOUT "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             CON.STDOUT "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ------------------------   Copy table "Threads"   -------------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO Threads "                                             + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            ThreadNumber, "                                       + _
              "            ThreadURL, "                                          + _
              "            Forum_Link, "                                         + _
              "            ThreadTitle) "                                        + _
              "     VALUES(?,?,?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_prepare_v2 failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.ThreadNumber, "                                         + _
              "       T1.ThreadURL, "                                            + _
              "       T1.Forum_Link, "                                           + _
              "       T1.ThreadTitle "                                           + _
              "  FROM Threads T1 "                                               + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"Threads"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          dwCol_ThreadNumber   = VAL(slF(3))
          sCol_ThreadURL       = slF(4)
          dwCol_Forum_Link     = VAL(slF(5))
          sCol_ThreadTitle     = slF(6)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindInt(hSQL,3,dwCol_ThreadNumber)
          SQL_BindString(hSQL,4,sCol_ThreadURL)
          SQL_BindInt(hSQL,5,dwCol_Forum_Link)
          SQL_BindString(hSQL,6,sCol_ThreadTitle)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             CON.STDOUT "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             CON.STDOUT "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ---------------------   Copy table "ThreadContent"   ----------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO ThreadContent "                                       + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Thread_Link, "                                        + _
              "            PostNumber, "                                         + _
              "            Author_Link, "                                        + _
              "            PostDate, "                                           + _
              "            PostContent) "                                        + _
              "     VALUES(?,?,?,?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_prepare_v2 failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Thread_Link, "                                          + _
              "       T1.PostNumber, "                                           + _
              "       T1.Author_Link, "                                          + _
              "       T1.PostDate, "                                             + _
              "       T1.PostContent "                                           + _
              "  FROM ThreadContent T1 "                                         + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"ThreadContent"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          dwCol_Thread_Link    = VAL(slF(3))
          dwCol_PostNumber     = VAL(slF(4))
          dwCol_Author_Link    = VAL(slF(5))
          sCol_PostDate        = slF(6)
          sCol_PostContent     = slF(7)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindInt(hSQL,3,dwCol_Thread_Link)
          SQL_BindInt(hSQL,4,dwCol_PostNumber)
          SQL_BindInt(hSQL,5,dwCol_Author_Link)
          SQL_BindString(hSQL,6,sCol_PostDate)
          SQL_BindString(hSQL,7,sCol_PostContent)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             CON.STDOUT "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             CON.STDOUT "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       sSQL = "END TRANSACTION"
       SQL_EXE(pDB,sSQL)         ' End SQLite transaction
    
       sSQL = "VACUUM"
       SQL_EXE(pDB,sSQL)         ' Vacuum the database
    
       ' Close and Disconnect the remote database
       slClose
       slDisconnect
    
       ' Close the LOCAL database
       dwRC = sqlite3_close(pDB)
    
       IF dwRC <> %SQLITE_OK THEN
          STDOUT "Error closing SQLite database " + FORMAT$(dwRC)
       END IF
    
    END FUNCTION
    
    FUNCTION GetTableMaxRowID(pDB AS DWORD,sTableName AS STRING) AS DWORD
    
       LOCAL dwRC                AS DWORD
       LOCAL hSQL                AS DWORD
       LOCAL sSQL                AS STRING
       LOCAL dwRowCount          AS DWORD
    
       sSQL = "SELECT MAX(RowID) FROM " + sTableName
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_prepare_v2 failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       dwRC = sqlite3_step(hSQL)
    
       IF dwRC <> %SQLITE_ROW THEN
          CON.STDOUT "sqlite3_step failed " + FORMAT$(dwRC)
          CON.STDOUT sSQL
          EXIT FUNCTION
       END IF
    
       dwRowCount = sqlite3_column_int(hSQL,0)
    
       dwRC = sqlite3_reset(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_reset failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          CON.STDOUT "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       FUNCTION = dwRowCount
    
    END FUNCTION

  • #2
    The attached code will use a combination of SQLitening (remote database) and SQLite (local database) to make a row by row copy of the remote database to a local copy.

    If the local database doesn't exist it will be created. If the database exists, new remote rows for each table are copied over to the local database tables.

    WARNING: The database is currently 678MB...the first build may take a few minutes. My testing was taking about 3.5 minutes to get the full database. Lots of things impact this speed.
    Also runs with PBWin by using ? instead of stdout.
    Timed-out once, so increased time-out in slConnect as suggested. I used 90000.
    The file PBForum_LOCAL.db created was 711,372,800 bytes.

    Great job, Michael Rice.

    Code:
    #DIM ALL
    #INCLUDE "sqlitening.inc"
    #INCLUDE ONCE "SQLitening.inc"
    #INCLUDE ONCE "sqlite3.inc"
    
    #IF NOT %DEF(%NULL)
       %NULL = 0???
    #ENDIF
    
    ' SQLite macro helpers
    MACRO SQL_EXE(phDB_Stmt,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_exec(phDB_Stmt,BYVAL STRPTR(Variable),%NULL,%NULL,%NULL)
       IF dwRC <> %SQLITE_OK THEN
          ? "Error...""sqlite3_exec"" failed   Error Code = " + FORMAT$(dwRC) + $CR + Variable
       END IF
    END MACRO
    
    MACRO SQL_BindInt(phDB_Stmt,SQL_StmtParmIndex,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_bind_int(phDB_Stmt,SQL_StmtParmIndex,Variable)
       IF dwRC <> %SQLITE_OK THEN
          ? "Error...""sqlite3_bind_int"" failed   Error Code = " + FORMAT$(dwRC)
       END IF
    END MACRO
    
    MACRO SQL_BindString(phDB_Stmt,SQL_StmtParmIndex,Variable)
       MACROTEMP dwRC
       LOCAL dwRC AS DWORD
       dwRC = sqlite3_bind_text(phDB_Stmt,SQL_StmtParmIndex,BYVAL STRPTR(Variable),LEN(Variable),%SQLITE_STATIC)
       IF dwRC <> %SQLITE_OK THEN
          ? "Error...""sqlite3_bind_text"" failed   Error Code = " + FORMAT$(dwRC)
       END IF
    END MACRO
    
    FUNCTION PBMAIN
    
       LOCAL dwRC                                    AS LONG
       LOCAL dwWork                                  AS LONG
    
       LOCAL pDB                                     AS LONG POINTER
       LOCAL hSQL                                    AS DWORD
       LOCAL sSQL                                    AS STRING
       LOCAL sDB_LOCAL_FileName                      AS STRING
    
       ' Database table fields
       LOCAL dwCol_RowID                             AS DWORD
       LOCAL sCol_RecordAddedDate                    AS STRING
       LOCAL sCol_Name                               AS STRING
       LOCAL sCol_ForumURL                           AS STRING
       LOCAL sCol_Author                             AS STRING
       LOCAL dwCol_ThreadNumber                      AS DWORD
       LOCAL sCol_ThreadURL                          AS STRING
       LOCAL dwCol_Forum_Link                        AS DWORD
       LOCAL sCol_ThreadTitle                        AS STRING
       LOCAL dwCol_Thread_Link                       AS DWORD
       LOCAL dwCol_PostNumber                        AS DWORD
       LOCAL dwCol_Author_Link                       AS DWORD
       LOCAL sCol_PostDate                           AS STRING
       LOCAL sCol_PostContent                        AS STRING
    
       sDB_LOCAL_FileName = EXE.PATH$ + "PBforum_LOCAL.db"
    
       ' Create/Open the LOCAL SQLite database
       dwRC = sqlite3_open_v2(BYVAL STRPTR(sDB_LOCAL_FileName),pDB,%SQLITE_OPEN_READWRITE OR %SQLITE_OPEN_CREATE,"")
    
       IF dwRC <> %SQLITE_OK THEN
          ? "Error opening database"
          EXIT FUNCTION
       END IF
    
       ' Set SQLite Database Options
       sSQL = "PRAGMA foreign_keys=TRUE; "                                       + _
              "PRAGMA journal_mode=DELETE; "                                     + _
              "PRAGMA synchronous=NORMAL;"
       SQL_EXE(pDB,sSQL)
    
       ' Create the LOCAL database tables if they don't exist
       sSQL = "CREATE TABLE IF NOT EXISTS Forums "                               + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Name TEXT(50) UNIQUE NOT NULL COLLATE NOCASE, "                  + _
              " ForumURL TEXT UNIQUE NOT NULL COLLATE NOCASE); "                 + _
              "CREATE TABLE IF NOT EXISTS ForumAuthors "                         + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Author TEXT(25) UNIQUE NOT NULL COLLATE NOCASE); "               + _
              "CREATE TABLE IF NOT EXISTS Threads "                              + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " ThreadNumber INTEGER UNIQUE NOT NULL, "                          + _
              " ThreadURL TEXT UNIQUE NOT NULL COLLATE NOCASE, "                 + _
              " Forum_Link INTEGER NOT NULL, "                                   + _
              " ThreadTitle TEXT(20) NOT NULL COLLATE NOCASE, "                  + _
              " FOREIGN KEY(Forum_Link) REFERENCES Forums(RowID)); "             + _
              "CREATE TABLE IF NOT EXISTS ThreadContent "                        + _
              "(RowID INTEGER PRIMARY KEY AUTOINCREMENT, "                       + _
              " RecordAddedDate TEXT(15) NOT NULL, "                             + _
              " Thread_Link INTEGER NOT NULL, "                                  + _
              " PostNumber INTEGER NOT NULL, "                                   + _
              " Author_Link INTEGER NOT NULL, "                                  + _
              " PostDate TEXT(15) NOT NULL, "                                    + _
              " PostContent TEXT NOT NULL, "                                     + _
              " FOREIGN KEY(Thread_Link) REFERENCES Threads(ThreadNumber),"      + _
              " FOREIGN KEY(Author_Link) REFERENCES ForumAuthors(RowID));"
       SQL_EXE(pDB,sSQL)
    
       ' Connect and Open the remote database
       slConnect "38.17.54.213",51234,"T:90000"
       slOpen "PBforum\PBforum.db" + $BS + "Read%Only", "R"  '1 open with read-only password
    
       sSQL = "BEGIN TRANSACTION"
       SQL_EXE(pDB,sSQL)         ' Begin SQLite transaction
    
       ' ------------------------   Copy table "Forums"   --------------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO Forums "                                              + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Name, "                                               + _
              "            ForumURL) "                                           + _
              "     VALUES(?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_prepare_v2 failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Name, "                                                 + _
              "       T1.ForumURL "                                              + _
              "  FROM Forums T1 "                                                + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"Forums"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          sCol_Name            = slF(3)
          sCol_ForumURL        = slF(4)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindString(hSQL,3,sCol_Name)
          SQL_BindString(hSQL,4,sCol_ForumURL)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             ?  "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             ?  "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ----------------------   Copy table "ForumAuthors"   ----------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO ForumAuthors "                                        + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Author) "                                             + _
              "     VALUES(?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_prepare_v2 failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Author "                                                + _
              "  FROM ForumAuthors T1 "                                          + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"ForumAuthors"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          sCol_Author          = slF(3)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindString(hSQL,3,sCol_Author)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             ?  "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             ? "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ------------------------   Copy table "Threads"   -------------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO Threads "                                             + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            ThreadNumber, "                                       + _
              "            ThreadURL, "                                          + _
              "            Forum_Link, "                                         + _
              "            ThreadTitle) "                                        + _
              "     VALUES(?,?,?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_prepare_v2 failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.ThreadNumber, "                                         + _
              "       T1.ThreadURL, "                                            + _
              "       T1.Forum_Link, "                                           + _
              "       T1.ThreadTitle "                                           + _
              "  FROM Threads T1 "                                               + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"Threads"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          dwCol_ThreadNumber   = VAL(slF(3))
          sCol_ThreadURL       = slF(4)
          dwCol_Forum_Link     = VAL(slF(5))
          sCol_ThreadTitle     = slF(6)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindInt(hSQL,3,dwCol_ThreadNumber)
          SQL_BindString(hSQL,4,sCol_ThreadURL)
          SQL_BindInt(hSQL,5,dwCol_Forum_Link)
          SQL_BindString(hSQL,6,sCol_ThreadTitle)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             ? "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             ? "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       ' ---------------------   Copy table "ThreadContent"   ----------------------
    
       ' Prepare the INSERT statement for the LOCAL database
       sSQL = "INSERT INTO ThreadContent "                                       + _
              "           (RowID, "                                              + _
              "            RecordAddedDate, "                                    + _
              "            Thread_Link, "                                        + _
              "            PostNumber, "                                         + _
              "            Author_Link, "                                        + _
              "            PostDate, "                                           + _
              "            PostContent) "                                        + _
              "     VALUES(?,?,?,?,?,?,?)"
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_prepare_v2 failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       ' Select rows from the REMOTE database
       sSQL = "SELECT T1.RowID, "                                                + _
              "       T1.RecordAddedDate, "                                      + _
              "       T1.Thread_Link, "                                          + _
              "       T1.PostNumber, "                                           + _
              "       T1.Author_Link, "                                          + _
              "       T1.PostDate, "                                             + _
              "       T1.PostContent "                                           + _
              "  FROM ThreadContent T1 "                                         + _
              " WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"ThreadContent"))
    
       slSel sSQL
    
       DO WHILE slGetRow
    
          dwCol_RowID          = VAL(slF(1))
          sCol_RecordAddedDate = slF(2)
          dwCol_Thread_Link    = VAL(slF(3))
          dwCol_PostNumber     = VAL(slF(4))
          dwCol_Author_Link    = VAL(slF(5))
          sCol_PostDate        = slF(6)
          sCol_PostContent     = slF(7)
    
          SQL_BindInt(hSQL,1,dwCol_RowID)
          SQL_BindString(hSQL,2,sCol_RecordAddedDate)
          SQL_BindInt(hSQL,3,dwCol_Thread_Link)
          SQL_BindInt(hSQL,4,dwCol_PostNumber)
          SQL_BindInt(hSQL,5,dwCol_Author_Link)
          SQL_BindString(hSQL,6,sCol_PostDate)
          SQL_BindString(hSQL,7,sCol_PostContent)
    
          dwRC = sqlite3_step(hSQL)
    
          IF dwRC <> %SQLITE_DONE THEN
             ? "sqlite3_step failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
          dwRC = sqlite3_reset(hSQL)
    
          IF dwRC <> %SQLITE_OK THEN
             ? "sqlite3_reset failed " + FORMAT$(dwRC)
             EXIT FUNCTION
          END IF
    
       LOOP
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       sSQL = "END TRANSACTION"
       SQL_EXE(pDB,sSQL)         ' End SQLite transaction
    
       sSQL = "VACUUM"
       SQL_EXE(pDB,sSQL)         ' Vacuum the database
    
       ' Close and Disconnect the remote database
       slClose
       slDisconnect
    
       ' Close the LOCAL database
       dwRC = sqlite3_close(pDB)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "Error closing SQLite database " + FORMAT$(dwRC)
       END IF
    
    END FUNCTION
    
    FUNCTION GetTableMaxRowID(pDB AS DWORD,sTableName AS STRING) AS DWORD
    
       LOCAL dwRC                AS DWORD
       LOCAL hSQL                AS DWORD
       LOCAL sSQL                AS STRING
       LOCAL dwRowCount          AS DWORD
    
       sSQL = "SELECT MAX(RowID) FROM " + sTableName
    
       dwRC = sqlite3_prepare_v2(pDB,BYVAL STRPTR(sSQL),LEN(sSQL),hSQL,%NULL)
    
       IF dwRC <> %SQLITE_OK THEN
          ?  "sqlite3_prepare_v2 failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       dwRC = sqlite3_step(hSQL)
    
       IF dwRC <> %SQLITE_ROW THEN
          ? "sqlite3_step failed " + FORMAT$(dwRC) + $CR + sSQL
          EXIT FUNCTION
       END IF
    
       dwRowCount = sqlite3_column_int(hSQL,0)
    
       dwRC = sqlite3_reset(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_reset failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       dwRC = sqlite3_finalize(hSQL)
    
       IF dwRC <> %SQLITE_OK THEN
          ? "sqlite3_finalize failed " + FORMAT$(dwRC)
          EXIT FUNCTION
       END IF
    
       FUNCTION = dwRowCount
    
    END FUNCTION

    Comment


    • #3
      Mike, thanks for testing and reporting back. I’m glad it’s working. I also had a couple of issues where the connect statement would timeout... Simply restarting my client program, would immediately connect and start the load. Not sure yet why I’m seeing such mixed results...it’s on my list of things I’m monitoring.

      I use PBCC for almost all my utility type programs...primarily because I do this “progname >> MyApp.log” so I can append results to my app log file. Thanks for creating an alternative for anyone not having both compilers.

      Thanks again for letting me know it worked for you. Hopefully you find it useful.

      Comment


      • #4
        Because of your efforts this works!
        Others can optionally be given time to work or not (see A a S P switches.)
        I am not sure about the T switch (for that matter any of them.) Need to test more.

        Access is denied to any DestinationFileName that has a colon, a double dot, or begins with a backslash.
        This does not apply using slcopydatabase.

        Code:
        slConnect sIpAddress,PortNumber,"T:9000000"
        
        slOpen sDatabase
        
        slcopydatabase ":memory:","A"     'read from remote server into memory
        
        slcopydatabase "newdatabase.db3"  'copy in-memory database to disk
        The second slcopydatabase copies to the server (if allowed), not the client. My bad.
        Almost there.

        Comment


        • #5
          This made me laugh “Because of your efforts this works...
          Because almost everything I’ve done here is simply a derivative work of code posted here on the forum!! There are many great artists here that post their works. And as others before me have mentioned...a little of this combined with a little of that and suddenly you have yourself some new art...

          I do appreciate the feedback, so I know it’s working at more than just one place.

          Comment


          • #6
            I hear ya! It is easy to get the whole database into memory. Now just need to copy to a local disk.
            I'm experimenting with a second slcopydatabase from "new.db", "a", ":memory:"
            There must be examples of copying an in-memory database to disk.

            Comment


            • #7
              Code:
              Work with a remote COPY of any SQLitening database
              Still working on copy from remote to local hard disk
              
              #INCLUDE "sqlitening.inc"   'copygreat.bas
              
              FUNCTION PBMAIN () AS LONG
              
               LOCAL s,sIp,sPort AS STRING
              
               'connect to remote database
               sIp = ""
               sPort = "default"
               slConnect sIp,VAL(sPort),"T:9000000"
              
               'open database
               slOpen "sample.db3" + $BS + "Read%Only", "R"
              
               '[B]copy current database to temp and close current[/B]
               slcopydatabase "","A" 'temp database now current
              
               'sql statements
               s = GetRs("select * from parts limit 1")
               s+= GetRs("select count(*) from parts")
              
               ? s,%MB_SYSTEMMODAL,"Done"
              
               slClose
               slDisconnect
              
              END FUNCTION
              
              FUNCTION GetRs(s AS STRING) AS STRING
               LOCAL sArray() AS STRING
               slSelAry s,sArray(),"Q124"
               FUNCTION = JOIN$(sArray(),$CR) + $CR+$CR
              END FUNCTION

              Comment


              • #8
                I'm missing the SQLitening.inc file. Does someone have a link to it? Thanks!

                Comment


                • #9
                  It is in the installation file. I will find original installation file without my extra stuff.

                  Comment


                  • #10
                    Uploaded SQLitening.inc

                    Entire package with executables and source:
                    https://sqlitening.planetsquires.com...p?topic=9427.0
                    Attached Files

                    Comment


                    • #11
                      Copy remote database to a local file and test. '1,346,560 bytes.
                      Reading a very large file will take some more work.
                      Code:
                      #INCLUDE "sqlitening.inc"
                      
                      FUNCTION PBMAIN() AS LONG
                      
                       LOCAL sBuffer AS STRING
                      
                       'Connect and  get database to a string
                       slConnect "38.17.54.213"
                       slGetFile "sample.db3" + $BS + "Read%Only", sBuffer
                       slDisconnect
                      
                       'Create new file on local drive
                       OPEN  "sample.db3.new" FOR OUTPUT AS #1
                       PRINT #1, sBuffer;
                       CLOSE #1
                      
                       'Test the new database on local drive (assumes SQLitening is available)
                       slopen "sample.db3.new","C"
                       slSel "select count(*) from parts"
                       slGetRow
                       sBuffer = slf(1)
                       slclose
                       ? sBuffer,,"Enjoy"
                      
                      END FUNCTION

                      Comment


                      • #12
                        I'm missing something here. Is there an SQL database on vBulletin by the name of "PBforum.db"? Is it located at "38.17.54.213"? And how would we know that?

                        And if it does exist, I'm finding it hard to think that vBulletin allows someone to access the database without using server-side code, even if it just a Copy operation.

                        Can someone give me a big picture view?



                        Comment


                        • #13
                          Michael Rice is hosting the database on his SQLitening server.

                          Comment


                          • #14
                            Howdy, Mike!

                            Thanks for that clarification.

                            Is that database one that contains forum thread information? Content that Michael creates by downloading thread information other than by direction accessing the vBulletin forum database ... such as how I capture thread info for gbThreads?

                            Wait ... Michael and I have had some email exchanges and your comment makes me realize I'd not fully understood what he has done.

                            He does have a mechanism that updates his server database with thread information. I'm not sure how much content he has - just thread summary information or total thread content. But, I'll contact him and get a better reading on that.

                            The published gbThreads code allows downloading/formatting of threads, which Michael can, or may have, put into his database.

                            Yep - thanks for the response. I have a better understanding of what Michael is doing.

                            But, why the need for any special code to get a copy of the database? Can't Michael just let a user download the entire file, such as what I do with letting folks download files from my server?

                            I guess I have even more questions to ask Michael!



                            Comment


                            • #15
                              Yes, he could let us download the SQLite database like any other file without using SQLitening to do it.

                              Comment


                              • #16
                                Hi Mike!
                                I don't understand all of his code above, but I'd assume that an incremental update of a local copy would be possible, avoiding the need for a full download. Full first time, incremental thereafter.

                                Comment


                                • #17
                                  Correct. Just insert statement(s) with the new data into the SQLite database.

                                  Comment


                                  • #18
                                    Originally posted by Mike Doty View Post
                                    Yes, he could let us download the SQLite database like any other file without using SQLitening to do it.
                                    Out of interest, how big a download would a zipped version of the PBForum.db file be?

                                    Comment


                                    • #19
                                      Gary, Mike, Stuart,
                                      You’ve got questions...I’ve got some explaining to do...

                                      So first, Gary no black magic here. I’m getting that information exactly the way you do using gbBuilder for your gbThreads program. I thank you again for sharing that info with all of us.

                                      I take each piece of the forum posting and place it into a free (and easy to use via PB) database format (SQLite). After reading several posts from Mike Doty about SQLitening, I thought it would be interesting to attempt hosting the database on the internet. Allowing the information to just be queried from anywhere using standard SQL syntax. There are some challenges with this currently, but overall those goals have been accomplished. The IP address listed in these posts is where I’m hosting the database from.

                                      I'm missing something here. Is there an SQL database on vBulletin by the name of "PBforum.db"?
                                      No sadly there isn’t...so with yours and the help of other here...I created one

                                      I'm not sure how much content he has - just thread summary information or total thread content. But, I'll contact him and get a better reading on that.
                                      Full content...see this post https://forum.powerbasic.com/forum/u...ld-postcontent

                                      but I'd assume that an incremental update of a local copy would be possible, avoiding the need for a full download. Full first time, incremental thereafter.
                                      I would assume the same as you...so the code in post #1 does exactly that: “If the database exists, new remote rows for each table are copied over to the local database tables.”
                                      Code:
                                      WHERE T1.RowID > " + FORMAT$(GetTableMaxRowID(pDB,"Forums")
                                      Out of interest, how big a download would a zipped version of the PBForum.db file be?” Obviously a changing value with every post added to the forum. But zipped database takes approximately half the space.

                                      Has anyone run the code in post #1? How did it work for you? How long did it take? I was pleasantly surprised by the row-by-row copy running as quickly as it did... Curious If others had similar results.

                                      If the database doesn’t already exist locally and you run the code in post #1...you will get a row-by-row copy of the entire database... i.e. an exact copy of the database is placed on your system.

                                      I think I answered the questions posted. Let me know if I missed any

                                      p.s. “PBforum.db“ was just the name I choose for the file. Seemed somewhat self descriptive...as I get older “KISS” is important!!
                                      Last edited by Michael Rice; 11 Aug 2020, 07:19 AM.

                                      Comment

                                      Working...
                                      X