Advertisement
Guest User

testdb.vbs

a guest
Jun 22nd, 2017
120
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Option Explicit
  2.  
  3. Const adExecuteNoRecords = 128
  4.  
  5. Const adModeRead = 1
  6. Const adModeReadWrite = 3
  7. Const adModeRecursive = 4194304
  8. Const adModeShareDenyNone = 16
  9. Const adModeShareDenyRead = 4
  10. Const adModeShareDenyWrite = 8
  11. Const adModeShareExclusive = 12
  12. Const adModeUnknown = 0
  13. Const adModeWrite = 2
  14.  
  15. Const adUseClient = 3
  16. Const adUseServer = 2
  17.  
  18. Const adOpenDynamic = 2
  19. Const adOpenForwardOnly = 0
  20. Const adOpenKeyset = 1
  21. Const adOpenStatic = 3
  22.  
  23. Const adLockBatchOptimistic = 4
  24. Const adLockOptimistic = 3
  25. Const adLockPessimistic = 2
  26. Const adLockReadOnly = 1
  27.  
  28. Const adCmdFile = 256
  29. Const adCmdStoredProc = 4
  30. Const adCmdTable = 2
  31. Const adCmdTableDirect = 512
  32. Const adCmdText = 1
  33. Const adCmdUnknown = 8
  34.  
  35. Const adBigInt = 20
  36. Const adBinary = 128
  37. Const adBoolean = 11
  38. Const adBSTR = 8
  39. Const adChapter = 136
  40. Const adChar = 129
  41. Const adCurrency = 6
  42. Const adDate = 7
  43. Const adDBDate = 133
  44. Const adDBTime = 134
  45. Const adDBTimeStamp = 135
  46. Const adDecimal = 14
  47. Const adDouble = 5
  48. Const adEmpty = 0
  49. Const adError = 10
  50. Const adFileTime = 64
  51. Const adGUID = 72
  52. Const adIDispatch = 9
  53. Const adInteger = 3
  54. Const adIUnknown = 13
  55. Const adLongVarBinary = 205
  56. Const adLongVarChar = 201
  57. Const adLongVarWChar = 203
  58. Const adNumeric = 131
  59. Const adPropVariant = 138
  60. Const adSingle = 4
  61. Const adSmallInt = 2
  62. Const adTinyInt = 16
  63. Const adUnsignedBigInt = 21
  64. Const adUnsignedInt = 19
  65. Const adUnsignedSmallInt = 18
  66. Const adUnsignedTinyInt = 17
  67. Const adUserDefined = 132
  68. Const adVarBinary = 204
  69. Const adVarChar = 200
  70. Const adVariant = 12
  71. Const adVarNumeric = 139
  72. Const adVarWChar = 202
  73. Const adWChar = 130
  74.  
  75. Const adColFixed = 1
  76. Const adColNullable = 2
  77.  
  78. Dim cnCON, cmCMD, rsREC
  79. Dim fso, arg
  80.  
  81. Set fso = CreateObject("Scripting.FileSystemObject")
  82. Set arg = WScript.Arguments
  83.  
  84. TestDB
  85.  
  86. Sub TestDB()
  87.   Dim sDBname
  88.  
  89.   sDBname = "PathNames.mdb"
  90.   If fso.FileExists(sDBname) Then
  91.     fso.DeleteFile sDBname
  92.   End If
  93.  
  94.   CreateDB sDBname
  95.   CreateTables sDBname
  96.   PopulateDB sDBname, "c:\hpg"
  97.   DumpData sDBname
  98. End Sub
  99.  
  100. Sub ConnectDB(sDBname, bOpen)
  101.   If bOpen = False Then
  102.     Set rsREC.ActiveConnection = Nothing
  103.     Set rsREC = Nothing
  104.     Set cmCMD.ActiveConnection = Nothing
  105.     Set cmCMD = Nothing
  106.     cnCON.Close
  107.     Set cnCON = Nothing
  108.     Exit Sub
  109.   End If
  110.  
  111.   Set cnCON = CreateObject("ADODB.Connection")
  112.   cnCON.Open  "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  113.               "Data Source=" & sDBname
  114.  
  115.   Set cmCMD = CreateObject("ADODB.Command")
  116.   With cmCMD
  117.     Set .ActiveConnection = cnCON
  118.     .CommandType = adCmdText
  119.   End With
  120.  
  121.   Set rsREC = CreateObject("ADODB.Recordset")
  122.   With rsREC
  123.     Set .ActiveConnection = cnCON
  124.     .CursorLocation = adUseClient
  125.     .CursorType = adOpenDynamic
  126.     .LockType = adLockOptimistic
  127.   End With
  128. End Sub
  129.  
  130.  
  131. Sub DumpData(sDBname)
  132.   Dim sSQL, fdFld, lCount
  133.   Dim rsData
  134.  
  135.   ConnectDB sDBname, True
  136.  
  137.   ' all files in a given folder, in descending order by date/time
  138.  sSQL = "SELECT * FROM Folders LEFT JOIN Files ON Folders.RecID = Files.Path " & _
  139.          "WHERE Folders.Path='c:\hpg\Logs' " & _
  140.          "ORDER BY Files.DateTime DESC"
  141.   cmCMD.CommandText = sSQL
  142.   Set rsData = cmCMD.Execute
  143.   lCount = 0
  144.   While Not rsData.EOF
  145.     lCount = lCount + 1
  146.     WScript.StdOut.Write lCount
  147.     For Each fdFld In rsData.Fields
  148.       WScript.StdOut.Write "," & fdFld.Value
  149.     Next  
  150.     WScript.StdOut.WriteLine ""
  151.     rsData.MoveNext
  152.   Wend  
  153.   rsData.Close
  154.   WScript.StdOut.WriteLine ""
  155.  
  156.   ' all files with a ".dat" extension ordered by folder path and filename
  157.  sSQL = "SELECT * FROM Files LEFT JOIN Folders ON Files.Path = Folders.RecID " & _
  158.          "WHERE Files.Name Like '%.dat' " & _
  159.          "ORDER BY Folders.Path, Files.Name"
  160.   cmCMD.CommandText = sSQL
  161.   Set rsData = cmCMD.Execute
  162.   lCount = 0
  163.   While Not rsData.EOF
  164.     lCount = lCount + 1
  165.     WScript.StdOut.Write lCount
  166.     For Each fdFld In rsData.Fields
  167.       WScript.StdOut.Write "," & fdFld.Value
  168.     Next  
  169.     WScript.StdOut.WriteLine ""
  170.     rsData.MoveNext
  171.   Wend  
  172.   rsData.Close
  173.   WScript.StdOut.WriteLine ""
  174.          
  175.   ConnectDB sDBname, False
  176. End Sub
  177.  
  178. Sub PopulateDB(sDBname, sPathName)
  179.   ConnectDB sDBname, True
  180.   ScanFolder sPathName
  181.   ConnectDB sDBname, False
  182. End Sub
  183.  
  184. Sub ScanFolder(sPathName)
  185.   Dim fld, sfd, fil
  186.  
  187.   Set fld = fso.GetFolder(sPathName)
  188.   For Each fil In fld.Files
  189.     ProcessFile fil
  190.   Next
  191.   For Each sfd In fld.SubFolders
  192.     ScanFolder sfd.Path
  193.   Next
  194. End Sub
  195.  
  196. Sub ProcessFile(fil)
  197.   Dim sPath, lPathID
  198.  
  199.   'WScript.Echo fil.Path
  200.  
  201.   sPath = fil.ParentFolder.Path
  202.   lPathID = GetFolderID(sPath)
  203.   rsREC.Open "Files"
  204.   With rsREC
  205.     .AddNew
  206.     .Fields("Path") = lPathID
  207.     .Fields("Name") = fil.Name
  208.     .Fields("Size") = fil.Size
  209.     .Fields("DateTime") = fil.DateLastModified
  210.     .Fields("Attributes") = fil.Attributes
  211.     .Update
  212.   End With
  213.   rsREC.Close
  214. End Sub
  215.  
  216. Function GetFolderID(sPath)
  217.   Dim sSQL, rsFLD, lRecID
  218.  
  219.   sSQL = "SELECT RecID FROM Folders WHERE Path='" & sPath & "'"
  220.   cmCMD.CommandText = sSQL
  221.   Set rsFLD = cmCMD.Execute
  222.   If Not rsFLD.EOF Then
  223.     lRecID = rsFLD.Fields("RecID")
  224.   Else      
  225.     rsREC.Open "Folders"
  226.     With rsREC
  227.       .AddNew
  228.       .Fields("Path") = sPath
  229.       .Update
  230.     End With
  231.     rsREC.Close    
  232.     sSQL = "SELECT MAX(RecID) AS NewID FROM Folders"
  233.     cmCMD.CommandText = sSQL
  234.     Set rsFLD = cmCMD.Execute
  235.     lRecID = CLng(rsFLD.Fields("NewID"))
  236.   End If
  237.   rsFLD.Close
  238.   GetFolderID = lRecID  
  239. End Function
  240.  
  241.  
  242. Sub CreateDB(sDBname)
  243.   Dim objCAT
  244.  
  245.   Set objCAT = CreateObject("ADOX.Catalog")
  246.   objCAT.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  247.                 "Data Source=" & sDBname & _
  248.                 ";Jet OLEDB:Engine Type=5;"
  249. End Sub
  250.  
  251. Sub CreateTables(sDBname)
  252.   CreateFolders sDBname
  253.   CreateFiles sDBname
  254. End Sub
  255.  
  256.  
  257. Sub CreateFolders(sDBname)
  258.   Dim objCAT, objTBL, objCOL, objIDX
  259.  
  260.  
  261.   Set objCAT = CreateObject("ADOX.Catalog")
  262.   objCAT.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  263.                             "Data Source=" & sDBname
  264.  
  265.   Set objTBL = CreateObject("ADOX.Table")
  266.   objTBL.Name = "Folders"
  267.  
  268.   Set objCOL = CreateObject("ADOX.Column")
  269.   With objCOL
  270.     .ParentCatalog = objCAT
  271.     .Type = adInteger
  272.     .Name = "RecID"
  273.     .Properties("Autoincrement") = True
  274.   End With
  275.   objTBL.Columns.Append objCOL
  276.  
  277.   With objTBL
  278.     .Columns.Append "Path", adLongVarWChar
  279.   End With
  280.   objCAT.Tables.Append objTBL
  281.  
  282.  
  283.   Set objIDX = CreateObject("ADOX.Index")
  284.   With objIDX
  285.     .Name = "PK_FOLDERS"
  286.     .PrimaryKey = True
  287.     .Unique = True
  288.     .Columns.Append "RecID"
  289.   End With
  290.   objTBL.Indexes.Append objIDX
  291.  
  292.  
  293.   Set objIDX = CreateObject("ADOX.Index")
  294.   With objIDX
  295.     .Name = "K1_FOLDERS"
  296.     .Unique = True
  297.     .Columns.Append "Path"
  298.   End With
  299.   objTBL.Indexes.Append objIDX
  300. End Sub
  301.  
  302.  
  303. Sub CreateFiles(sDBname)
  304.   Dim objCAT, objTBL, objCOL, objIDX
  305.  
  306.  
  307.   Set objCAT = CreateObject("ADOX.Catalog")
  308.   objCAT.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  309.                             "Data Source=" & sDBname
  310.  
  311.   Set objTBL = CreateObject("ADOX.Table")
  312.   objTBL.Name = "Files"
  313.  
  314.   Set objCOL = CreateObject("ADOX.Column")
  315.   With objCOL
  316.     .ParentCatalog = objCAT
  317.     .Type = adInteger
  318.     .Name = "RecID"
  319.     .Properties("Autoincrement") = True
  320.   End With
  321.   objTBL.Columns.Append objCOL
  322.  
  323.   With objTBL
  324.     .Columns.Append "Path", adInteger
  325.     .Columns.Append "Name", adLongVarWChar
  326.     .Columns.Append "Size", adInteger
  327.     .Columns.Append "DateTime", adDate
  328.     .Columns.Append "Attributes", adInteger
  329.   End With
  330.  
  331.   objCAT.Tables.Append objTBL
  332.  
  333.   Set objIDX = CreateObject("ADOX.Index")
  334.   With objIDX
  335.     .Name = "PK_FILES"
  336.     .PrimaryKey = True
  337.     .Unique = True
  338.     .Columns.Append "Name"
  339.     .Columns.Append "RecID"
  340.   End With
  341.   objTBL.Indexes.Append objIDX
  342.  
  343.   Set objIDX = CreateObject("ADOX.Index")
  344.   With objIDX
  345.     .Name = "K1_FILES"
  346.     .Columns.Append "Path"
  347.   End With
  348.   objTBL.Indexes.Append objIDX
  349. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement