Jump to content
UBot Underground

[SELL] SQLite Database Plugin


Recommended Posts

v 2.1 Update Released  :

-Fixed sqlite error function sometimes returning errors on successful queries

-Added lock feature to database queries to prevent errors when accessing the database from different threads!

Aymen,

 

how does that database lock feature work exactly? Will it retry read and write operations when the DB is locked from another thread? Or will it return an error?

Does it support read and write?

 

How many threads have you tested it with?

 

Kindest regards

Dan

Link to post
Share on other sites
  • Replies 263
  • 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

Posted Images

Aymen,

 

how does that database lock feature work exactly? Will it retry read and write operations when the DB is locked from another thread? Or will it return an error?

Does it support read and write?

 

How many threads have you tested it with?

 

Kindest regards

Dan

 

locking in .NET means multiple threads will have access at certain object once at a time!

Link to post
Share on other sites

locking in .NET means multiple threads will have access at certain object once at a time!

So the threads will wait until the db is unlocked so that they can write?

 

Dan

Link to post
Share on other sites

So the threads will wait until the db is unlocked so that they can write?

 

Dan

basically ensures one access at a time and yes they will wait like a Queue system

Link to post
Share on other sites

isnt that anyway managed by "Transaction [your query] Comit"

i was belive that sqlite can handle tramsactions or?

sqlite does that. But the plugin was not able to repeat / que the requests. 

It just showed an "access denied database locked" error.

 

Dan

Link to post
Share on other sites

sqlite does that. But the plugin was not able to repeat / que the requests. 

It just showed an "access denied database locked" error.

 

Dan

 

Got this "database is locked" error on the new version

 

Seems that the queue thing is not worked?

Link to post
Share on other sites

Is it possible to add a list directly to the database?

plugin command("SQLlite.dll", "SQLite Query", "Database.sql", "INSERT INTO ProfilesIDs (UID) Values ({%profile ids})")

Its just a list that later I will process.

Also when I process it, how can I delete hte UID from the database?

Link to post
Share on other sites

HGi

 

Is it possible to add a list directly to the database?

plugin command("SQLlite.dll", "SQLite Query", "Database.sql", "INSERT INTO ProfilesIDs (UID) Values ({%profile ids})")

Its just a list that later I will process.

Also when I process it, how can I delete hte UID from the database?

Hi.

 

You could add the list to a table. And then import that table into the sqlite DB. 

 

Or you build a build your sql statement (insert into...) dynamically.

But you should not execute a sql command for every single list item. That's to slow from a performance point of view. 

 

You can add up to 250 entries into a single sql query. Here's a small define I wrote a while ago.

 

 

define AddToDatabase {
    set(#sqlcommand$nothing"Global")
    if($comparison($list total(%list2), ">=", 250)) {
        then {
            set list position(%list2, 0)
            loop while($comparison($list position(%list2), "!="$list total(%list2))) {
                set(#sqlcommand"INSERT INTO \'{#tablename}\'""Global")
                set(#sqlcommand"{#sqlcommand}
SELECT \'{$next list item(%list2)}\' AS \'urls\'""Global")
                loop(249) {
                    if($comparison($list position(%list2), "!="$list total(%list2))) {
                        then {
                            set(#sqlcommand"{#sqlcommand}
UNION SELECT \'{$next list item(%list2)}\'""Global")
                        }
                        else {
                        }
                    }
                }
                plugin command("SQLlite.dll""SQLite Query"#databasefile#sqlcommand)
            }
        }
        else {
            set list position(%list2, 0)
            set(#sqlcommand"INSERT INTO \'{#tablename}\'""Global")
            set(#sqlcommand"{#sqlcommand}
SELECT \'{$next list item(%list2)}\' AS \'urls\'""Global")
            loop($subtract($list total(%list2), 1)) {
                set(#sqlcommand"{#sqlcommand}
UNION SELECT \'{$next list item(%list2)}\'""Global")
            }
            plugin command("SQLlite.dll""SQLite Query"#databasefile#sqlcommand)
        }
    }
    set(#sqlcommand$nothing"Global")
}

 

 

Regarding deletion of UID. What do you mean exactly? Do you want to empty the thing after you have processed it? 

Why do you want to do that? I normally work with an additional flag in the database. Like status. And when an entries is processed I set the status to "processed".

 

And then you can query only the entry who haven't been processed:

SELECT UID FROM db WHERE status=''

 

And don't forget that you can also work with rowids.

SELECT * FROM  [main].[data1] WHERE [rowid] between 1 and 10

 

Cheers

Dan

 

Link to post
Share on other sites

HGi

 

Hi.

 

You could add the list to a table. And then import that table into the sqlite DB. 

 

Or you build a build your sql statement (insert into...) dynamically.

But you should not execute a sql command for every single list item. That's to slow from a performance point of view. 

 

You can add up to 250 entries into a single sql query. Here's a small define I wrote a while ago.

 

 

define AddToDatabase {

    set(#sqlcommand$nothing"Global")

    if($comparison($list total(%list2), ">=", 250)) {

        then {

            set list position(%list2, 0)

            loop while($comparison($list position(%list2), "!="$list total(%list2))) {

                set(#sqlcommand"INSERT INTO \'{#tablename}\'""Global")

                set(#sqlcommand"{#sqlcommand}

SELECT \'{$next list item(%list2)}\' AS \'urls\'""Global")

                loop(249) {

                    if($comparison($list position(%list2), "!="$list total(%list2))) {

                        then {

                            set(#sqlcommand"{#sqlcommand}

UNION SELECT \'{$next list item(%list2)}\'""Global")

                        }

                        else {

                        }

                    }

                }

                plugin command("SQLlite.dll""SQLite Query"#databasefile#sqlcommand)

            }

        }

        else {

            set list position(%list2, 0)

            set(#sqlcommand"INSERT INTO \'{#tablename}\'""Global")

            set(#sqlcommand"{#sqlcommand}

SELECT \'{$next list item(%list2)}\' AS \'urls\'""Global")

            loop($subtract($list total(%list2), 1)) {

                set(#sqlcommand"{#sqlcommand}

UNION SELECT \'{$next list item(%list2)}\'""Global")

            }

            plugin command("SQLlite.dll""SQLite Query"#databasefile#sqlcommand)

        }

    }

    set(#sqlcommand$nothing"Global")

}

 

 

Regarding deletion of UID. What do you mean exactly? Do you want to empty the thing after you have processed it? 

Why do you want to do that? I normally work with an additional flag in the database. Like status. And when an entries is processed I set the status to "processed".

 

And then you can query only the entry who haven't been processed:

SELECT UID FROM db WHERE status=''

 

And don't forget that you can also work with rowids.

SELECT * FROM  [main].[data1] WHERE [rowid] between 1 and 10

 

Cheers

Dan

 

 

Dan, you are the man!

Working with NoSQL has given me problems forgeting SQL

Thanks a lot! I will follow up your directions!

 

Last question:

I have this to create the table as you told me:

add list to table as column(&profiles ids, 0, 0, %profile ids)

How do I add and repeat a false statement in the column 1 and start from row 0 to assign it to every ID I add to table?

Im not sure how to do this to add 2 columns, first column from list and add the boolean on the second column so later I mark it as true.

 

 

Also I am doing the following:

add list to table as column(&profiles ids, 0, 0, %profile ids)
plugin command("SQLlite.dll", "SQLite Table To DB", "{$special folder("Application")}/Database.sql", "UIDs", "UID", &profiles ids)

And Database.sql is still blank. Not sure why!

What am I doing wrong?

Link to post
Share on other sites

Dan, you are the man!

Working with NoSQL has given me problems forgeting SQL

Thanks a lot! I will follow up your directions!

 

Last question:

I have this to create the table as you told me:

add list to table as column(&profiles ids, 0, 0, %profile ids)

How do I add and repeat a false statement in the column 1 and start from row 0 to assign it to every ID I add to table?

Im not sure how to do this to add 2 columns, first column from list and add the boolean on the second column so later I mark it as true.

 

 

Also I am doing the following:

add list to table as column(&profiles ids, 0, 0, %profile ids)
plugin command("SQLlite.dll", "SQLite Table To DB", "{$special folder("Application")}/Database.sql", "UIDs", "UID", &profiles ids)

And Database.sql is still blank. Not sure why!

What am I doing wrong?

 

 

The amount of columns in your table has to match the amount of fields in the sqlite database. Otherwise you can't import it. 

The SQLite plugin has an error function. You should run that to see what it tells you.

 alert($plugin function("SQLlite.dll""sqlite error"))

 

 

Here's an example how you can fill the second column if some infos:

 

clear list(%list)

clear table(&table1)

set(#var1"test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1""Global")

add list to list(%list$list from text(#var1$new line), "Don\'t Delete""Global")

add list to table as column(&table1, 0, 0, %list)

set(#rowcounter, 0, "Global")

loop($table total rows(&table1)) {

    set table cell(&table1#rowcounter, 1, "False")

    increment(#rowcounter)

}

 

You can also do that in SQLite directly if you like. Just let me know if you need that as well and I can send you an example.

 

Dan

Link to post
Share on other sites

The amount of columns in your table has to match the amount of fields in the sqlite database. Otherwise you can't import it. 

The SQLite plugin has an error function. You should run that to see what it tells you.

 alert($plugin function("SQLlite.dll""sqlite error"))

 

 

Here's an example how you can fill the second column if some infos:

 

clear list(%list)

clear table(&table1)

set(#var1"test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1

test1""Global")

add list to list(%list$list from text(#var1$new line), "Don\'t Delete""Global")

add list to table as column(&table1, 0, 0, %list)

set(#rowcounter, 0, "Global")

loop($table total rows(&table1)) {

    set table cell(&table1#rowcounter, 1, "False")

    increment(#rowcounter)

}

 

You can also do that in SQLite directly if you like. Just let me know if you need that as well and I can send you an example.

 

Dan

 

 

Thats awesome Dan.

Thanks a lot.

I might study your courses soon. By the way, last and important question.

Do I need to create a Table on the Database.sql before I start adding content?

Im not sure why the error is giving me that "no such table: NameOfTable"

 

Do I have to create a table inside? What command shall I use? Just CREATE TABLE NameOfTable ?

Link to post
Share on other sites

Woah, this plugin is great.

 

Can I reach SELECT second_clumn FROM table1 WHERE uid_column = '{tablerow_counter}'

And I can work on data like that in every loop request?

 

Or downloading all the data to a &table and editing from there, droping all table and create it again with new modified table?

 

Whats the best method to work on this?

Im really concerned of doing a software that will not crash out others pcs using bad methods.

I will be really grateful.

Link to post
Share on other sites

Woah, this plugin is great.

 

Can I reach SELECT second_clumn FROM table1 WHERE uid_column = '{tablerow_counter}'

And I can work on data like that in every loop request?

 

Or downloading all the data to a &table and editing from there, droping all table and create it again with new modified table?

 

Whats the best method to work on this?

Im really concerned of doing a software that will not crash out others pcs using bad methods.

I will be really grateful.

Hi.

 

I always design my databases upfront. And deliver a template together with my bots. I don't use the create db feature from the plugin. A while ago I purchased a professional software to work with SQlite database:

http://www.sqliteexpert.com/

 

This is really great.  It has a 30 day trial. And you can use the free version if you don't use the advanced features.

 

Regarding your query. You can use:

SELECT * FROM  [main].[data1] WHERE [rowid] between 1 and 10

 

This will return entries 1-10 from table "data1"

 

Or

SELECT * FROM UID WHERE status='false'

 

Updating the entries in the database is a little bit more tricky. But there are also different ways to do it. 

I would not drop and recreate the whole table. Depending on the amount of entries that might not work. 

Some of my databases have 3 million entries... :-)

 

Dan

Link to post
Share on other sites

there is also a limit function in SQLite that  I use all the time. For example you could create a query that pulls all records that satisfy a query sort them. You then use the limit command to pull chunks for example starting at the 10th record returning only 25 records. I use this for table displaying with a page function works well.

Link to post
Share on other sites

v 2.1 Update Released  :

-Fixed sqlite error function sometimes returning errors on successful queries

-Added lock feature to database queries to prevent errors when accessing the database from different threads!

 

The plugin still keeps a lock on the sqlite database file. Bot is not running, but database file is locked.

Can not zip, delete or delete the file in the file browser. 

 

Bot was not running. Script has finished but file handle is not closed. 

 

Tester in 2.1

 

Dan

  • Like 1
Link to post
Share on other sites

The plugin still keeps a lock on the sqlite database file. Bot is not running, but database file is locked.

Can not zip, delete or delete the file in the file browser. 

 

Bot was not running. Script has finished but file handle is not closed. 

 

Tester in 2.1

 

Dan

 

Same problem here

 

I thought it was just me

Link to post
Share on other sites

Im struggling to set some columns to not being repeated.

Specially the UIDs or other IDs.

Anyone knows how to create table and assign a column as unique? This might be a very basic question.

Is it like...

CREATE TABLE IF NOT EXISTS NameOfTable (id integer primary key autoincrement,uid integer unique , data);

Am I correct?

Link to post
Share on other sites

Im struggling to set some columns to not being repeated.

Specially the UIDs or other IDs.

Anyone knows how to create table and assign a column as unique? This might be a very basic question.

Is it like...

CREATE TABLE IF NOT EXISTS NameOfTable (id integer primary key autoincrement,uid integer unique , data);

Am I correct?

 

Hi. 

 

The following examples might help:

http://www.tutorialspoint.com/sqlite/sqlite_constraints.htm

http://stackoverflow.com/questions/2701877/sqlite-table-constraint-unique-on-multiple-columns

 

I use:

http://www.sqliteexpert.com/

 

To create and edit all my Sqlite databases. That's a lot easier than doing it in the bot directly.

It also helps you to create select and update statements when you have a lot of fields to update.

 

Dan

Link to post
Share on other sites

there's a slight issue with SQLite get data command. if for example a two column SQLite table and only the first column has values thus the second are all null. it causes a  object reference not set to an instance error when reading back.

Link to post
Share on other sites

Aymen, quick question.

How can I integrate this to a Jquery pagination, I mean just loading the table as sources of paginations.

Shall I use your JSON plugin to mix it with this plugin to set as source? Your $database to html is nice but very very simple.

It would be awesome to let us modify it as we please.

Any ideas how can achieve this?

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...