[Class] SQLiteDB - Update on 2022-10-04

Post your working scripts, libraries and tools for AHK v1.1 and older
hasantr
Posts: 933
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by hasantr » 19 Aug 2019, 06:45

hasantr wrote:
19 Aug 2019, 06:39
' and " I need to enter some text into the Sqlite database that contains a large number of marks. But I get a mistake. I can't duplicate these signs because sometimes there are hundreds.

I cannot enter the following sample text into the sqlite database. Because there are cut marks.
Test"sample"test'sample"1:12'3""4'5%8'''99[ghhh[[58]{{fgfg]]]]))))
There is such a solution, but it slows things down in large texts.

Code: Select all

StringReplace, data, data,',+, All

hasantr
Posts: 933
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by hasantr » 19 Aug 2019, 08:07

just me wrote:
19 Dec 2013, 04:19
Is there a way to use parameters with this class?

https://www.devart.com/dotconnect/sqlite/docs/Parameters.html

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 19 Aug 2019, 08:31

https://www.sqlite.org/c3ref/bind_blob.html

I don't know that @just me implemented these though.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 07 Oct 2019, 19:22

EDIT: NOT A LIBRARY ISSUE.
@just me I'm having a spot of trouble trying to do database exports.
First of all, I'm using thisas guide.

I confirm the code works in SQLite Studio.

So here's the issue: I'm trying to "cut up" some databases for other processing. I'm trying to sort them out based on a specific column.
Within the context of your library I can't get a database merge that has a WHERE clause to function.

Code generation

Code: Select all

query	:=	"BEGIN;" "`n"		
for k,v in tableset
	QUERY .= "INSERT OR IGNORE INTO toMerge." TableSet["Rows",a_index,1] "" 
			. " SELECT * FROM " TableSet["Rows",a_index,1]
			;. " WHERE user_id_str = " id_user 
			. ";" "`n" 
query .=	"COMMIT;" "`n"
WHERE - fails

Code: Select all

BEGIN;
INSERT OR IGNORE INTO toMerge.users SELECT * FROM users WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.tweets SELECT * FROM tweets WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.retweets SELECT * FROM retweets WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.replies SELECT * FROM replies WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.favorites SELECT * FROM favorites WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.followers SELECT * FROM followers WHERE user_id_str = '19091173';
INSERT OR IGNORE INTO toMerge.following SELECT * FROM following WHERE user_id_str = '19091173';
COMMIT;
no WHERE - succeeds... with all the records

Code: Select all

BEGIN;
INSERT OR IGNORE INTO toMerge.users SELECT * FROM users;
INSERT OR IGNORE INTO toMerge.tweets SELECT * FROM tweets;
INSERT OR IGNORE INTO toMerge.retweets SELECT * FROM retweets;
INSERT OR IGNORE INTO toMerge.replies SELECT * FROM replies;
INSERT OR IGNORE INTO toMerge.favorites SELECT * FROM favorites;
INSERT OR IGNORE INTO toMerge.followers SELECT * FROM followers;
INSERT OR IGNORE INTO toMerge.following SELECT * FROM following;
COMMIT;

and full code context:
Spoiler
Last edited by Qriist on 08 Oct 2019, 10:49, edited 1 time in total.

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 07 Oct 2019, 19:24

Do you need to alias the table in your SELECT?

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 07 Oct 2019, 19:39

no, it's from the main attached DB @kczx3

It's literally the WHERE clause causing the fail.

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 07 Oct 2019, 19:48

I can’t imagine how this library would effect that. Single quotes shouldn’t be an issue either

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 07 Oct 2019, 19:48

Also, you mention it fails. Is there an error that appears?

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 07 Oct 2019, 20:52

No error, just a silent abort, I guess. @kczx3

I'll post a sample here shortly, along with the CreateDB schema it uses

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 07 Oct 2019, 21:10

schema of dB with my pragma edits afterwards. The attached is a compilation of recent tweets with the word SQLite. It Is a nonsensitive representative of my dataset. What want is to separate into different external databases based on user_id_str. I'm annoyed because it feels like I'm doing it right.

Code: Select all

CREATE TABLE users(
                    id integer not null,
                    id_str text not null,
                    name text,
                    username text not null,
                    bio text,
                    location text,
                    url text,
                    join_date text not null,
                    join_time text not null,
                    tweets integer,
                    following integer,
                    followers integer,
                    likes integer,
                    media integer,
                    private integer not null,
                    verified integer not null,
                    profile_image_url text not null,
                    background_image text,
                    hex_dig  text not null,
                    time_update integer not null,
                    CONSTRAINT users_pk PRIMARY KEY (id, hex_dig)
                );
CREATE TABLE tweets (
                    id integer not null,
                    id_str text not null,
                    tweet text default '',
                    conversation_id text not null,
                    created_at integer not null,
                    date text not null,
                    time text not null,
                    timezone text not null,
                    place text default '',
                    replies_count integer,
                    likes_count integer,
                    retweets_count integer,
                    user_id integer not null,
                    user_id_str text not null,
                    screen_name text not null,
                    name text default '',
                    link text,
                    mentions text,
                    hashtags text,
                    cashtags text,
                    urls text,
                    photos text,
                    quote_url text,
                    video integer,
                    geo text,
                    near text,
                    source text,
                    time_update integer not null,
                    PRIMARY KEY (id)
                );
CREATE TABLE retweets(
                    user_id integer not null,
                    username text not null,
                    tweet_id integer not null,
                    retweet_id integer not null,
                    retweet_date integer not null,
                    CONSTRAINT retweets_pk PRIMARY KEY(user_id, tweet_id),
                    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES users(id),
                    CONSTRAINT tweet_id_fk FOREIGN KEY(tweet_id) REFERENCES tweets(id)
                );
CREATE TABLE replies(
                    tweet_id integer not null,
                    user_id integer not null,
                    username text not null,
                    CONSTRAINT replies_pk PRIMARY KEY (user_id, tweet_id),
                    CONSTRAINT tweet_id_fk FOREIGN KEY (tweet_id) REFERENCES tweets(id)
                );
CREATE TABLE favorites(
                    user_id integer not null,
                    tweet_id integer not null,
                    CONSTRAINT favorites_pk PRIMARY KEY (user_id, tweet_id),
                    CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES users(id),
                    CONSTRAINT tweet_id_fk FOREIGN KEY (tweet_id) REFERENCES tweets(id)
                );
CREATE TABLE followers (
                    id integer not null,
                    follower_id integer not null,
                    CONSTRAINT followers_pk PRIMARY KEY (id, follower_id),
                    CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES users(id),
                    CONSTRAINT follower_id_fk FOREIGN KEY(follower_id) REFERENCES users(id)
                );
CREATE TABLE following (
                    id integer not null,
                    following_id integer not null,
                    CONSTRAINT following_pk PRIMARY KEY (id, following_id),
                    CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES users(id),
                    CONSTRAINT following_id_fk FOREIGN KEY(following_id) REFERENCES users(id)
                );
CREATE TABLE followers_names (
                    user text not null,
                    time_update integer not null,
                    follower text not null,
                    PRIMARY KEY (user, follower)
                );
CREATE TABLE following_names (
                    user text not null,
                    time_update integer not null,
                    follows text not null,
                    PRIMARY KEY (user, follows)
                );
CREATE TABLE related_users (
				related_users not null,
				notes not null,
				PRIMARY KEY (related_users)
			);
CREATE TABLE media(
				media_id not null,
				found_in_tweets not null,
				found_in_users not null,
				file_size
				hash_CRC32
				hash_MD5
				hash_SHA1
				PRIMARY KEY (id)
			);
COMMIT;
PRAGMA auto_vacuum = 1;
PRAGMA journal_mode=WAL;
PRAGMA synchronous = 0;
COMMIT;
Attachments
sqlite.7z
(137 KiB) Downloaded 126 times
Last edited by Qriist on 07 Oct 2019, 21:23, edited 1 time in total.

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 07 Oct 2019, 21:19

As a workaround I guess I can export the dB to a third temporary dB and delete all records that aren't the present Id, then import THAT. would be slow but effective, I guess.

just me
Posts: 9407
Joined: 02 Oct 2013, 08:51
Location: Germany

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by just me » 08 Oct 2019, 08:32

Two notes:
  1. AFAICS user_id_str is defined only for the table tweets.
  2. Code: Select all

    		query .=	"COMMIT;" "`n"
    		clipboard :=  query
    		Reform.query(query) <<<<< ?????
    		Reform.exec(query)
    		Reform.exec("detach toMerge;" "`n")	;DETACH must be .exec
    		...
    		...
    		;Deform.gettable("SELECT * FROM tweets;", thing)
    		Deform.gettable("SELECT * FROM tweets WHERE user_id_str = " id_user ";", thing)	
    		msgbox % st_printarr(thing)
    		exitapp <<<<< ?????
    
    

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 08 Oct 2019, 09:24

EDIT: Nevermind. You are correct in your usage.

Also, you have a comma here when I think you actually want to concatenate A_Index onto the word "Rows", right?

Code: Select all

for k,v in RecordSet.Rows
	{
		
		id_user := RecordSet.Rows[a_index,1]
		id_path := a_scriptdir "\scraped\reformed\" id_user ".db"
		CreateDB(id_path) ;GOOD
		Reform.exec("ATTACH DATABASE '" id_path "' AS toMerge;" "`n")	;ATTACH must be .exec
		Reform.gettable("PRAGMA database_list;",test)
		query	:=	"BEGIN;" "`n"		
		for k,v in tableset
			QUERY .= "INSERT OR IGNORE INTO toMerge." TableSet["Rows",a_index,1] ""   ; <<<<<<<<<<<<<<<<<<<<<<
					. " SELECT * FROM " TableSet["Rows",a_index,1]   ; <<<<<<<<<<<<<<<<<<<<<<
					;. " WHERE user_id_str = " id_user 
					. ";" "`n" 
		query .=	"COMMIT;" "`n"
		clipboard :=  query
		Reform.query(query)
		Reform.exec(query)
		Reform.exec("detach toMerge;" "`n")	;DETACH must be .exec

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 08 Oct 2019, 09:35

just me wrote:
08 Oct 2019, 08:32
Two notes:
  1. AFAICS user_id_str is defined only for the table tweets.
    Yep, that was the issue. Revamped the code and all is well.
  2. Code: Select all

    Reform.query(query) <<<<< ?????
    I was trying a lot of things trying to figure out what was going on.
revamped code:

Code: Select all

for k,v in tableset
		{
			QUERY .= "INSERT OR IGNORE INTO toMerge." TableSet["Rows",a_index,1] "" 
				. " SELECT * FROM " TableSet["Rows",a_index,1]
			if (TableSet["Rows",a_index,1] = "tweets")
				query .= " WHERE user_id_str=" sqlQuote(id_user)
			query .= ";" "`n" 
		}
I still have some fluency issues with SQLite. Not every language is as forgiving as AHK. ^^;

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 08 Oct 2019, 10:27

So you're still having the problem though?

Qriist
Posts: 81
Joined: 11 Sep 2016, 04:02

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by Qriist » 08 Oct 2019, 10:48

kczx3 wrote:
08 Oct 2019, 10:27
So you're still having the problem though?
Nope! All is well. Not a library issue at all.

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 08 Oct 2019, 12:44

Excellent!

DRocks
Posts: 565
Joined: 08 May 2018, 10:20

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by DRocks » 16 Oct 2019, 07:01

Thanks for the class!
1 year later with more AHK knowledge I can finally understand enough to follow along the example, this is great stuff good job.

Here's the simplest example I made following your default example.
I basically just used the class functions without including the AHK Gui stuff.

Code: Select all

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
#SingleInstance, Force ; Prevent double execution of this script

#Include Class_SQLiteDB.ahk

dbFile := A_ScriptDir . "\mySQLite.DB"
If FileExist(dbFile) {
	FileDelete, %dbFile%
}
db := new SQLiteDB ; create SQLiteDB class instance
dbVersion := db.Version
dbTableName := "mySQLite"

If !db.OpenDB(dbFile) {
	MsgBox, 16, SQLite Error, % "Msg:`t" . db.ErrorMsg . "`nCode:`t" . db.ErrorCode
	ExitApp
}

SQL := "CREATE TABLE " . dbTableName . " (Name, Phone, PRIMARY KEY(Name ASC));"
If !db.Exec(SQL)
	MsgBox, 16, SQLite Error, % "Msg:`t" . db.ErrorMsg . "`nCode:`t" . db.ErrorCode

db.Exec("BEGIN TRANSACTION;")
SQL := "INSERT INTO " . dbTableName . " VALUES('Alexandre Desroches', '(999) 123-4567');"
SQL .= "INSERT INTO " . dbTableName . " VALUES('Cathy Diacono', '(777) sky-gods');"
If !db.Exec(SQL)
	MsgBox, 16, SQLite Error, % "Msg:`t" . db.ErrorMsg . "`nCode:`t" . db.ErrorCode
db.Exec("COMMIT TRANSACTION;")
SQL := ""

If !db.LastInsertRowID(RowID)
	MsgBox, 16, SQLite Error, % "Msg:`t" . db.ErrorMsg . "`nCode:`t" . db.ErrorCode
MsgBox % "LastInsertRowID() = " RowID


result := ""
SQL := "SELECT * FROM " . dbTableName . ";"
If !db.GetTable(SQL, result)
	MsgBox, 16, SQLite Error, % "Msg:`t" . db.ErrorMsg . "`nCode:`t" . db.ErrorCode
If (result.HasNames) {
	Loop, % result.ColumnCount
		MsgBox % "result.ColumnNames[A_Index] = " result.ColumnNames[A_Index]
	If (result.HasRows) {
		Loop, % result.RowCount {
			rowNumber := A_Index
			result.Next(row)
			Loop, % result.ColumnCount
				MsgBox % "Row#"rowNumber " column#"A_Index " " result.ColumnNames[A_Index] " = " row[A_Index]
		}
	}
}
return
Question: a DB file can contain more than one table ? or each table = a new database file ?

User avatar
kczx3
Posts: 1640
Joined: 06 Oct 2015, 21:39

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by kczx3 » 16 Oct 2019, 07:51

Correct, you can have more than one table per database file.

See limits

DRocks
Posts: 565
Joined: 08 May 2018, 10:20

Re: [Class] SQLiteDB - Update on 2019-07-12

Post by DRocks » 16 Oct 2019, 13:25

nice feature, thank you!

Post Reply

Return to “Scripts and Functions (v1)”