Jump to content
UBot Underground

[SELL] SQLite Database Plugin


Recommended Posts

about the performance issue , can't really have it reproduced properly , what i concluded is that it can be tied to ubot itself , i won't promise you anything about this , will see how things goes !

about new features and options i'll have a look at them when releasing the next version which will be in a week or so from now!

 

It's a query that returns between 10k and 50k URLs. So it's a bit of data. When I run it natively with your plugin, the execution of this command takes almost 5 minutes.

 

When I use the commandline SQL tool via the shell plugin:

 

    plugin command("Advanced Shell.dll", "shell batch hidden", "\"{$special folder("Application")}\\sqlite3.exe\" \"{#databasefile}\"  \"select urls from profiles WHERE {#query}\" > \"{$special folder("Application")}\\sqltmp.txt\"", "Yes")
    plugin command("Bigtable.dll", "large List from text", "tmp", $read file("{$special folder("Application")}\\sqltmp.txt"), $new line, "replace")
    plugin command("Advanced Shell.dll", "delete file permanently", "{$special folder("Application")}\\sqltmp.txt")
 
Then this executes in under 30seconds.

 

Thanks for looking into it.

Dan

  • Like 1
Link to post
Share on other sites
  • Replies 257
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

Are you sick of using text files and CSV files to store your data in the most unprofessional way possible ?   Now with SQLite plugin for Ubot , you will be able to store data , update data , delete da

Hi Everyone, I wanted to confirm the same issues with SQLite and NULL values and DOUBLE QUOTES in your literal strings. I wrestled with this for several hours till I had a breakthrough via trial&

Update 1.2   -Added a grid window command   http://content.screencast.com/users/aymen99/folders/Jing/media/ec539139-86a3-4465-afa6-c33278242026/2013-04-06_1906.png   http://content.screencast.com/user

Hey , what i can promise you is that i will certainly take a deep look at it , and will try to at least improve it!

 

Regards

  • Like 1
Link to post
Share on other sites
  • 2 weeks later...
  • 1 month later...

@Aymen can you please update this to work with UBS 5.5.13+ I get 0 results using the sqlite get data command... I believe the other commands work ok, but have not fully tested it or anything to see...

 

Thanks

Link to post
Share on other sites

@Aymen can you please update this to work with UBS 5.5.13+ I get 0 results using the sqlite query with results... I believe the other commands work ok, but have not fully tested it or anything to see...

 

Thanks

 

Working fine for me:

sql1.jpg

 

I'm testing my databases with:

http://www.sqliteexpert.com/

 

Maybe that could help to find the error?

 

Dan

Link to post
Share on other sites

Working fine for me:

sql1.jpg

 

I'm testing my databases with:

http://www.sqliteexpert.com/

 

Maybe that could help to find the error?

 

Dan

 

@Dan , Thanks, Yes, that function/parameter works fine, it is the COMMAND "SQLite Get Data" that returns the results in a table that is not working. Can you see if that command is working for you? And thanks for the tip on the sqliteexpert software, it looks really nice. I just use sqlite manager plugin for firefox. I am pretty comfortable with sql, been using it for many years, but I do like to use visual tools too, and this looks alot nicer than sqlite manager, I think I'm gonna pick this one up:)

Link to post
Share on other sites

Yes, that one is working fine for me as well:

 

sql2.jpg

All my table fields are declared as TEXT. Not sure if it makes a difference if you have other Types in your DB.

The Sqlite plugin is using the:

Csharp Community SQLite library:

https://www.nuget.org/packages/Community.CsharpSqlite.SQLiteClient/
Which is based on SQLite 3.7.7.3

So depending on your sqlite database, that could lead to trouble.

But if you create your DB with the plugin, save data to it and load data with the plugin, that should all work.

The only problem I could see is if your database was created externally and is maybe using stuff, that is not "supported" with the plugin.

 

 

Dan

Link to post
Share on other sites

Yes, that one is working fine for me as well:

 

sql2.jpg

 

All my table fields are declared as TEXT. Not sure if it makes a difference if you have other Types in your DB.

 

The Sqlite plugin is using the:

Csharp Community SQLite library:

https://www.nuget.org/packages/Community.CsharpSqlite.SQLiteClient/

Which is based on SQLite 3.7.7.3

 

So depending on your sqlite database, that could lead to trouble.

 

But if you create your DB with the plugin, save data to it and load data with the plugin, that should all work.

 

The only problem I could see is if your database was created externally and is maybe using stuff, that is not "supported" with the plugin.

 

 

Dan

 

Interesting... yes, my db was created using sqlite manager, and the only datatypes I use are varchar, integer and text... I will experiment with this to see if maybe that's the issue. Thanks for your help. Whats wierd about is tho, it works just fine on the same database using ubot studio 4....  I just tinkered a little bit with it, and maybe it has to do with the mixed datatypes... if I do not use the wildcard and choose the specific columns I want it works.  Again, whats strange is, it woks fine in ub 4 with the wildcard and on the exact same file.  The error being thrown is "object not set to in instance of an object" when I use the wildcard, 

 

edit:

I have narrowed this down to being an issue with the contents of one )or more) of my TEXT fields... there is probably some sort of strange character in one of the fields is whats at the root of this. Do you know of any quick and easy way to serialize a variable?  I have the communications plugin and it can do that with lists, but not sure of a way to do this inside ubot with a variable (or table or anything other than a list)... maybe I'll just base64 encode these larger fields before I insert them from now on...

Link to post
Share on other sites

I used the free encryption plugin for something like that in the past.

If you need more flexibility you could also use the command line plugin and get the data via SQLite.exe via the command line. I'm doing that in one oft bots. Because the query on a large table (5million entries) id 20x faster via the SQLite.exe compared to the plugin.

 

Dan

Link to post
Share on other sites

@Dan I seem to have isolated this down even further now... it was not a funky character, its throwing this error if there are any of the results that contain a null value.  I went through and bas64 encoded all the fields, and still had the problem, and there were 3 fields that were null... I added some dummy text and it worked fine... I worked my way column by column through my database and had the same issue with ever column if there were any null values in it. At least thats what is happening on my end, can you confirm this? Do any of your columns that the query returns contain null values?

Link to post
Share on other sites

I've confirmed this with Aymen and have personal experience on the same, the plugin only works properly when there are no null values, if you have a null value in a field, you need to put something in the field rather than a null value.

 

HTH

  • Like 1
Link to post
Share on other sites

I've confirmed this with Aymen and have personal experience on the same, the plugin only works properly when there are no null values, if you have a null value in a field, you need to put something in the field rather than a null value.

 

HTH

 

Thanks...

Link to post
Share on other sites
  • 3 months later...

Hi Everyone,

I wanted to confirm the same issues with SQLite and NULL values and DOUBLE QUOTES in your literal strings. I wrestled with this for several hours till I had a breakthrough via trial&error and research on this forum and the web, so I wanted to make this post to save someone else a headache.

PROBLEM #1: SQLite databases and NULL values (you can't do it)

My Solution: In the Ubot code below, I had to do a $comparison when a variable contained nothing/NULL value, I chose to set the value to the string "None" in my case.

if($comparison(#xProdViews,"= Equals",$nothing)) {
    then {
        set(#xProdViews,"None","Global")
    }


I also found out the hard way that when I tried to pass to SQLite literal strings that contained DOUBLE QUOTES via the Ubot plugin command "SQLite Table To DB". The results were the SQLite database would break or reject the transfer completely.

PROBLEM #2: SQLite databases and DOUBLE QUOTES (you can do it, but you have to use two double quotes)

My Solution: You have to replace DOUBLE QUOTES with a set of DOUBLE QUOTES like this ""

What I did in the node view was put a set of DOUBLE QUOTES "" and click the "" icon button in the SET() node.

In the Ubot code below, I'm pulling string data with $xpath parser expression and then $trim whitespaces off the string, then I $replace the html code """ with a set of DOUBLE QUOTES so that SQLite does not reject it and then Ubot is escaping the set of DOUBLE QUOTES with the node view "" icon clicked which are \ escaped characters in the Ubot code view below.

You'll also noticed that I $replace the html code "'" with single quote which oddly the Ubot plugin command "SQLite Table To DB" and SQLite database themselves don't have any problems with single quotes!? But, Ubot code view does escape character the single quote below.

set(#xProdTitle,$replace($replace($trim($plugin function("HTTP post.dll", "$xpath parser", #get, "//meta[@property=\"og:title\"]", "content", "HTML")),""","\"\""),"'","\'"),"Global")

SUMMARY: Watch out and replace those Nothing/NULL values and DOUBLE QUOTES in yuor strings when working with SQLite database and the Ubot plugin command "SQLite Table To DB"

Just wanted to give back to this awesome Ubot community and I truly hope this helps someone out and saves them coding time :)

Thanks,
-Rich

Edited by mobileconsultant
  • Like 2
Link to post
Share on other sites
  • 1 month later...

Hey Aymen,

 

I've been getting inconsistent results with some data queries. I'm going to try and hammer things out but thought I'd throw it out there.

Link to post
Share on other sites
  • 1 month later...

Ok, after a month of working with sqlite, I'm really starting to get it. I do have a request to the dev, is it possible to create two commands in the plugin to backup and restore a database?

 

Frank

Link to post
Share on other sites
  • 2 weeks later...

Hi aymen, I am facing Stopped working error of ubot. When i run grid window command. 
 
i have tested it many times. not sure why is it so. there is also no error inside the code.

 

--------------------------

set(#databasefile,"{$special folder("Application")}\\database.sqlite","Global")
if($not($file exists(#databasefile))) {
    then {
        plugin command("SQLlite.dll""SQLite Create Database"#databasefile)
        plugin command("SQLlite.dll""SQLite Query"#databasefile"CREATE TABLE `campaigns` (
    `Campaign Name`    TEXT
);")
    }
    else {
        plugin command("SQLlite.dll""SQLite Get Data"#databasefile"select * from campaigns"&tubeViewer)
        if($contains($plugin function("SQLlite.dll""sqlite error"),"no such table")) {
            then {
                plugin command("SQLlite.dll""SQLite Query"#databasefile"CREATE TABLE `campaigns` (
    `Campaign Name`    TEXT
);")
            }
            else {
                clear table(&campaigns)
                plugin command("SQLlite.dll""SQLite Get Data"#databasefile"select `Campaign Name` from campaigns"&campaigns)
                clear list(%campaigns)
                with every cell in column(&campaigns,0,#campaignVar) {
                    add item to list(%campaigns,#campaignVar,"Don\'t Delete","Global")
                }
                set(#campaigns,$replace(%campaigns,"
",","),"Global")
            }
        }
    }
}
clear table(&campaigns)
plugin command("SQLlite.dll""SQLite Get Data"#databasefile"select * from campaigns"&campaigns)
alert($plugin function("SQLlite.dll""sqlite error"))
if($is blank(&campaigns)) {
    then {
        alert("No campaigns found in database!

Create some campaigns first. Thanks")
    }
    else {
        plugin command("SQLlite.dll""grid window""select * from campaigns"#databasefile, 200, "Campaigns", 575, 500)
    }
}

Link to post
Share on other sites
  • 4 weeks later...

Way back when, someone was talking about jquery tables to be created and used. Any further developments here? I'd love to add more intelligence to my table queries and change writes.

Link to post
Share on other sites

Think Aymen mentioned that he is not checking regularly here due to his schedule recently and that emailing him was best way if it's urgent.

HTH

 

Aymen where are you? please check my order  (Unique Transaction ID #5AK021069B227733J)

Link to post
Share on other sites

Aymen where are you? please check my order  (Unique Transaction ID #5AK021069B227733J)

 

 

Think Aymen mentioned that he is not checking regularly here due to his schedule recently and that emailing him was best way if it's urgent.

HTH

 

 

Yup what he said !

Best way to send me an email if you need me fast (you the contact page at my website) , i usually response in the same day !

Anyways , PMed you back!

 

Sorry for the trouble

Link to post
Share on other sites
  • 4 weeks later...
  • 3 weeks later...

What is current version of this? all I could find is my payment reciept from ages ago and no download link in my email.  I have 2.1.0 is that thje current version?  Was hoping maybe the issue with the blank fields causing no results would be fixed by now in ubot 5.

Link to post
Share on other sites

×
×
  • Create New...