Announcement

**Mike Doty** · 8 Aug 2020, 07:37 PM

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

**Michael Rice** · 9 Aug 2020, 06:24 AM

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.

**Mike Doty** · 9 Aug 2020, 06:47 AM

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.

**Michael Rice** · 9 Aug 2020, 07:36 AM

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.

**Mike Doty** · 9 Aug 2020, 07:42 AM

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.

**Mike Doty** · 9 Aug 2020, 02:11 PM

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

**Gary Beene** · 10 Aug 2020, 06:57 PM

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

**Mike Doty** · 10 Aug 2020, 07:06 PM

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

**Mike Doty** · 10 Aug 2020, 07:09 PM

Uploaded SQLitening.inc

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

Attached Files

SQLitening.inc (10.7 KB, 5 views)

**Mike Doty** · 10 Aug 2020, 11:06 PM

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

**Gary Beene** · 10 Aug 2020, 11:17 PM

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?

**Mike Doty** · 10 Aug 2020, 11:18 PM

Michael Rice is hosting the database on his SQLitening server.

**Gary Beene** · 10 Aug 2020, 11:29 PM

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!

**Mike Doty** · 10 Aug 2020, 11:34 PM

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

**Gary Beene** · 10 Aug 2020, 11:42 PM

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.

**Mike Doty** · 10 Aug 2020, 11:43 PM

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

**Stuart McLachlan** · 11 Aug 2020, 12:55 AM

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?

**Michael Rice** · 11 Aug 2020, 04:20 AM

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!!

Announcement

Create local copy of PBforum.db

Create local copy of PBforum.db

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment