Bot-Factory 602 Posted June 11, 2014 Report Share Posted June 11, 2014 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 regardsDan Quote Link to post Share on other sites
Aymen 385 Posted June 11, 2014 Author Report Share Posted June 11, 2014 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 regardsDan locking in .NET means multiple threads will have access at certain object once at a time! Quote Link to post Share on other sites
Bot-Factory 602 Posted June 11, 2014 Report Share Posted June 11, 2014 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 Quote Link to post Share on other sites
kev123 132 Posted June 11, 2014 Report Share Posted June 11, 2014 So the threads will wait until the db is unlocked so that they can write? Danbasically ensures one access at a time and yes they will wait like a Queue system Quote Link to post Share on other sites
blumi40 222 Posted June 11, 2014 Report Share Posted June 11, 2014 isnt that anyway managed by "Transaction [your query] Comit"i was belive that sqlite can handle tramsactions or? Quote Link to post Share on other sites
Bot-Factory 602 Posted June 11, 2014 Report Share Posted June 11, 2014 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 Quote Link to post Share on other sites
blumi40 222 Posted June 11, 2014 Report Share Posted June 11, 2014 ah okay i see Quote Link to post Share on other sites
jamesfar 15 Posted June 11, 2014 Report Share Posted June 11, 2014 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? Quote Link to post Share on other sites
Bot-Factory 602 Posted June 11, 2014 Report Share Posted June 11, 2014 Got this "database is locked" error on the new version Seems that the queue thing is not worked?Haven't tested it yet. Hopefully Aymen has tested that.. Dan Quote Link to post Share on other sites
Aymen 385 Posted June 12, 2014 Author Report Share Posted June 12, 2014 Got this "database is locked" error on the new version Seems that the queue thing is not worked? I'll check that out! Quote Link to post Share on other sites
whoami 26 Posted June 20, 2014 Report Share Posted June 20, 2014 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? Quote Link to post Share on other sites
Bot-Factory 602 Posted June 20, 2014 Report Share Posted June 20, 2014 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 CheersDan Quote Link to post Share on other sites
whoami 26 Posted June 20, 2014 Report Share Posted June 20, 2014 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 CheersDan Dan, you are the man!Working with NoSQL has given me problems forgeting SQLThanks 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? Quote Link to post Share on other sites
Bot-Factory 602 Posted June 20, 2014 Report Share Posted June 20, 2014 Dan, you are the man!Working with NoSQL has given me problems forgeting SQLThanks 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, "test1test1test1test1test1test1test1test1test1test1test1test1test1", "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 Quote Link to post Share on other sites
whoami 26 Posted June 23, 2014 Report Share Posted June 23, 2014 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, "test1test1test1test1test1test1test1test1test1test1test1test1test1", "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 ? Quote Link to post Share on other sites
whoami 26 Posted June 23, 2014 Report Share Posted June 23, 2014 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. Quote Link to post Share on other sites
Bot-Factory 602 Posted June 23, 2014 Report Share Posted June 23, 2014 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" OrSELECT * 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 Quote Link to post Share on other sites
kev123 132 Posted June 23, 2014 Report Share Posted June 23, 2014 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. Quote Link to post Share on other sites
Bot-Factory 602 Posted June 26, 2014 Report Share Posted June 26, 2014 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 1 Quote Link to post Share on other sites
jamesfar 15 Posted June 26, 2014 Report Share Posted June 26, 2014 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 Quote Link to post Share on other sites
Aymen 385 Posted June 27, 2014 Author Report Share Posted June 27, 2014 K i'll check that out very soon ! Quote Link to post Share on other sites
whoami 26 Posted June 28, 2014 Report Share Posted June 28, 2014 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? Quote Link to post Share on other sites
Bot-Factory 602 Posted June 28, 2014 Report Share Posted June 28, 2014 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.htmhttp://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 Quote Link to post Share on other sites
kev123 132 Posted July 3, 2014 Report Share Posted July 3, 2014 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. Quote Link to post Share on other sites
whoami 26 Posted July 3, 2014 Report Share Posted July 3, 2014 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? Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.