SQLite semi Embedded database functionality in AutoIt

[ptrex]

@piccaso

Thanks for the feedback !!

I see you looked at the LiteX specifications.

But nevertheless we are not speaking the same language !!

I problem is NOT that I am missing something, like you keep asking. Most of the things are there in your UDF.

But they are badly structured !!

Sorry that I can't seem to explain to you what I mean. Since I gave you already a detailed explenation in my previous answers.

.execute("BEGIN TRANSACTION") = _SQLite_Exec(-1,"BEGIN TRANSACTION") = TO BE TESTED

.execute("COMMIT TRANSACTION") = _SQLite_Exec(-1,"COMMIT TRANSACTION") = TO BE TESTED

.execute("Begin;") = _SQLite_Exec(-1,"Begin;") = TO BE TESTED

.execute("Commit;") = _SQLite_Exec(-1,"Commit;") = TO BE TESTED

.ColumnName(idx) = _SQLite_FetchNames = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

.ColumnCount = _SQLite_FetchData -> The size of the returned array = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

.ColumnType( idx ) = we have no types but you can query for that in the master table = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

.ColumnValue( idx, [type] ) = _SQLite_FetchData -> $aResult[idx] = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

.Step( [nSteps] ) = _SQLite_FetchData could be called repeatly to do more than one step, this can be done in sql too = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

.Row = _SQLite_FetchData -> $aResult = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

there is no '_SQLite_QuerySingleRow' in litex = YES THERE IS CALLED .ROW

.Done = just tells you if the last row was fetched = OK

i miss 'finalize' ...

.BatchExecute( sql ) = _SQLite_Exec = OK

.ParameterCount & .ParameterName( idx ) = for bindings = OK

.ActiveConnection = returns the 'Connection object' wich is like $hDB = OK

.RowCount = this is possible by counting rows yourself, but it would be a big slowdown if done every query = I DON'T LIKE THE UDF I WOULD LIKE TO SEE 1 SERPARATE UDF FOR THIS FUNCTION ONLY.

imo the number of methods/functions isnt that what counts.

Not realy the methods/functions available in the some UDF should be broken down.

For the sake of easy scripting. It is only a small step to take, so why not take it.

Look at the how the PHP guys broke down there UDF's

sqlite_array_query -- Execute a query against a given database and returns an array

sqlite_busy_timeout -- Set busy timeout duration, or disable busy handlers

sqlite_changes -- Returns the number of rows that were changed by the most recent SQL statement

sqlite_close -- Closes an open SQLite database

sqlite_column -- Fetches a column from the current row of a result set

sqlite_create_aggregate -- Register an aggregating UDF for use in SQL statements

sqlite_create_function -- Registers a "regular" User Defined Function for use in SQL statements

sqlite_current -- Fetches the current row from a result set as an array

sqlite_error_string -- Returns the textual description of an error code

sqlite_escape_string -- Escapes a string for use as a query parameter

sqlite_exec -- Executes a result-less query against a given database

sqlite_factory -- Opens a SQLite database and returns a SQLiteDatabase object

sqlite_fetch_all -- Fetches all rows from a result set as an array of arrays

sqlite_fetch_array -- Fetches the next row from a result set as an array

sqlite_fetch_column_types -- Return an array of column types from a particular table

sqlite_fetch_object -- Fetches the next row from a result set as an object

sqlite_fetch_single -- Fetches the first column of a result set as a string

sqlite_fetch_string -- Alias of sqlite_fetch_single()

sqlite_field_name -- Returns the name of a particular field

sqlite_has_more -- Finds whether or not more rows are available

sqlite_has_prev -- Returns whether or not a previous row is available

sqlite_key -- Returns the current row index

sqlite_last_error -- Returns the error code of the last error for a database

sqlite_last_insert_rowid -- Returns the rowid of the most recently inserted row

sqlite_libencoding -- Returns the encoding of the linked SQLite library

sqlite_libversion -- Returns the version of the linked SQLite library

sqlite_next -- Seek to the next row number

sqlite_num_fields -- Returns the number of fields in a result set

sqlite_num_rows -- Returns the number of rows in a buffered result set

sqlite_open -- Opens a SQLite database and create the database if it does not exist

sqlite_popen -- Opens a persistent handle to an SQLite database and create the database if it does not exist

sqlite_prev -- Seek to the previous row number of a result set

sqlite_query -- Executes a query against a given database and returns a result handle

sqlite_rewind -- Seek to the first row number

sqlite_seek -- Seek to a particular row number of a buffered result set

sqlite_single_query -- Executes a query and returns either an array for one single column or the value of the first row

sqlite_udf_decode_binary -- Decode binary data passed as parameters to an UDF

sqlite_udf_encode_binary -- Encode binary data before returning it from an UDF

sqlite_unbuffered_query -- Execute a query that does not prefetch and buffer all data

sqlite_valid -- Returns whether more rows are available

This is what I mean. We need to have the functions be available as SEPERATE functions to call in the UDF.

Botem line is, I will never use the current UDF's, as it is structured today.

Sorry.

regards

ptrex

Edited September 26, 2006 by ptrex

[JSThePatriot]

Since PHP, Apache, and many other large communities are accepting SQLite, I think it would be adviseable to look at their syntax, and see how they are doing it, and follow along that route if possible. I am suggesting this because so far we are basing more off of LiteX COM Wrapper than any type of standard. This may not be a bad thing as they may be coming from some set Standards, but I would say the PHP route would be worth looking into.

JS

[ptrex]

Since PHP, Apache, and many other large communities are accepting SQLite, I think it would be adviseable to look at their syntax, and see how they are doing it, and follow along that route if possible. I am suggesting this because so far we are basing more off of LiteX COM Wrapper than any type of standard. This may not be a bad thing as they may be coming from some set Standards, but I would say the PHP route would be worth looking into.

JS

I fully agree. Now it's the time to rethink the basic structure of the current UDF !!

And it' s not just that we need to copy what the others are doing.

But rather think about why did they created these seperate functions in there module.

They have already been through this stage, and BELIEVE ME it all makes sence.

regards

ptrex

[JSThePatriot]

I fully agree. Now it's the time to rethink the basic structure of the current UDF !!

And it' s not just that we need to copy what the others are doing.

But rather think about why did they created these seperate functions in there module.

They have already been through this stage, and BELIEVE ME it all makes sence.

regards

ptrex

Right there with you. They have definitely been through this a few times... MySQL PostgreSQL

hehe

It definitely makes sense, and we should follow their lead. I am not saying we should copy as you stated, but atleast make our similar enough that someone coming from PHP or going to PHP wouldnt have the least bit of trouble understanding our syntax.

JS

[piccaso]

enough with that litex, its realy something different.

and ptrex, not all of your answers make sence

but i like the php syntax.

how about stepping to version 2 with php like syntax?

and i'll keep v1 supported and fix bugs and update the included version...

it wount be a complete rewrite, just a bunch of function renameings and some have to be done.

still much work but this makes sence for me to

[JSThePatriot]

enough with that litex, its realy something different.

and ptrex, not all of your answers make sence

but i like the php syntax.

how about stepping to version 2 with php like syntax?

and i'll keep v1 supported and fix bugs and update the included version...

it wount be a complete rewrite, just a bunch of function renameings and some have to be done.

still much work but this makes sence for me to

Excellent. I am happy to see we have all agreed on something. Now we each need to study how they have it named. Unfortunately we cannot rename the LiteX COM Wrappers can we? So I assume that means we are going to solely use the DllCall() version until which time I can make the plugin work and available.

JS

[piccaso]

you want to work on both projekts?

dont get me wrong, your very welcome

im just jellous, i wouldnt have that much time

[ptrex]

@piccaso

but i like the php syntax.

how about stepping to version 2 with php like syntax?

and i'll keep v1 supported and fix bugs and update the included version...

Yes I agree this is the way to go !!

Like I was saying from the beginning I don't ask a lot, just some remaning and some new functions.

It took us a long time to explain wasn't it

If you have the new functions and/or names. I will help to rewrite the help files, by bits and pieces.

regards,

ptrex

Edited September 26, 2006 by ptrex

[JSThePatriot]

you want to work on both projekts?

dont get me wrong, your very welcome

im just jellous, i wouldnt have that much time

I dont know that I have time to work on both, but I am willing to lend any help I can as anything that helps the DllCall() version especially like deciding on the naming of everything will help me when I can get this plugin going. I am actually working on it as we speak. I am going to try and get a database created, from there it should almost be gravy .

AutoIt Projects

The above link is a link to my web page that talks about all of my current AutoIt projects. I dont really have time for them all, but at the same time they help me in my work

JS

[piccaso]

lets split this up a bit...

renameing:

sqlite_array_query -- Execute a query against a given database and returns an array

sqlite_busy_timeout -- Set busy timeout duration, or disable busy handlers

sqlite_changes -- Returns the number of rows that were changed by the most recent SQL statement

sqlite_close -- Closes an open SQLite database

sqlite_current -- Fetches the current row from a result set as an array

sqlite_error_string -- Returns the textual description of an error code

sqlite_escape_string -- Escapes a string for use as a query parameter

sqlite_exec -- Executes a result-less query against a given database

sqlite_fetch_array -- Fetches the next row from a result set as an array

sqlite_last_error -- Returns the error code of the last error for a database

sqlite_last_insert_rowid -- Returns the rowid of the most recently inserted row

sqlite_udf_encode_binary -- Encode binary data before returning it from an UDF

sqlite_open -- Opens a SQLite database and create the database if it does not exist

sqlite_libversion -- Returns the version of the linked SQLite library

sqlite_single_query -- Executes a query and returns either an array for one single column or the value of the first row

to do:

sqlite_column -- Fetches a column from the current row of a result set

sqlite_fetch_all -- Fetches all rows from a result set as an array of arrays

sqlite_fetch_column_types -- Return an array of column types from a particular table

sqlite_fetch_single -- Fetches the first column of a result set as a string

sqlite_fetch_string -- Alias of sqlite_fetch_single()

sqlite_field_name -- Returns the name of a particular field

sqlite_has_more -- Finds whether or not more rows are available

sqlite_has_prev -- Returns whether or not a previous row is available

sqlite_key -- Returns the current row index

sqlite_libencoding -- Returns the encoding of the linked SQLite library

sqlite_next -- Seek to the next row number

sqlite_num_fields -- Returns the number of fields in a result set

sqlite_num_rows -- Returns the number of rows in a buffered result set

sqlite_popen -- Opens a persistent handle to an SQLite database and create the database if it does not exist

sqlite_prev -- Seek to the previous row number of a result set

sqlite_query -- Executes a query against a given database and returns a result handle

sqlite_rewind -- Seek to the first row number

sqlite_seek -- Seek to a particular row number of a buffered result set

sqlite_udf_decode_binary -- Decode binary data passed as parameters to an UDF

sqlite_valid -- Returns whether more rows are available

undoable/not sure about:

sqlite_unbuffered_query -- Execute a query that does not prefetch and buffer all data

sqlite_create_aggregate -- Register an aggregating UDF for use in SQL statements

sqlite_create_function -- Registers a "regular" User Defined Function for use in SQL statements

sqlite_factory -- Opens a SQLite database and returns a SQLiteDatabase object

sqlite_fetch_object -- Fetches the next row from a result set as an object

for some things of the todo side of this list there is no interface from the dll.

some things would be doable in autoit, but may slow it all down.

time will tell...

[ptrex]

@piccaso

Tested the COMMIT example and it seems to work.

This is an example you can add to your distribution.

example_Commit.au3

This means if the COMMIT works the rest will work as well.

Thanks for all the rest so far !!

regards

ptrex

[piccaso]

Commit is a sqlite feature, why shoult it differ from the litex version?

Line 16 : _SQLite_Exec(-1,"Rollback;") raises an error...

[ptrex]

@piccaso

Like you said FORGET LiteX.

I know COMMIT is an SQLite feature, but I never tested it.

Neither did you have examples of it ditributed (as far as I know)

It should give an error !! Because after a commit you can't rollback.

So I propose to add this function _SQLite_Commit into the distribution.

It is less typing, rather than _SQLite_Exec(-1,"commit;") ...

Agree ?

Regards

ptrex

[piccaso]

do you want to have em all?

_SQLite_Analyze($hDB,$sTable)
_SQLite_Attach($hDB,$sDatabase)
_SQLite_Detach($hDB,$sDatabase)
_SQLite_Explain($hDB,$sSQL)
_SQLite_Vakuum($hDB = -1, $sTable = "")
_SQLite_Begin($hDB = -1)
_SQLite_End($hDB = -1)
_SQLite_Commit($hDB = -1)
_SQLite_Rollback($hDB = -1)
...

i'd rather work on v2

[ptrex]

@piccaso

Yes, put them in version 2.

The reason why I suggest to make them available in a seperate function, is for scripting purposes.

For those who are not very familiar with the SQLite syntax they can start using the functions in the UDF.

And it is less typing for those who are familiar with the syntax.

And you can find a 1 to 1 relation in of the function in the SQL syntax help CHM file

And ...

To many advantages to sum up.

I hope you see my point.

Regards,

ptrex

[JSThePatriot]

Out of Curiosity:

Which would be the prefered syntax?

;Example 1
$hSqlite3 = SQLite_Open($dbFilename)

;Example 2
SQLite_Open($dbFilename, $hSQLite3)

Edit: My vote will go with example 2 due to that is how SQLite does it in their functions, and is also my preferred method. I would like to know what you two think.

I can create my plugin either way.

JS

Edited September 26, 2006 by JSThePatriot

[ptrex]

@JSThePatriot

I would prefer the first because it is most close to the existing function

_SQLite_Open($sDatabase_Filename)

[JSThePatriot]

@JSThePatriot

I would prefer the first because it is most close to the existing function

_SQLite_Open($sDatabase_Filename)

Does this follow the standard for the PHP? I havent checked yet. That is the standard.

I will be leaving off the precluding _ due to making the functions appear more natural.

JS

Edited September 26, 2006 by JSThePatriot

[ptrex]

Does this follow the standard for the PHP? I havent checked yet.

JS

This is the PHP syntax

sqlite_open ( string filename [, int mode [, string &error_message]] )

Far more complex than what we need.

ptrex

[JSThePatriot]

This is the PHP syntax

Far more complex than what we need.

ptrex

Agreed. It will be as follows.

;Example 1
$hDB = SQLite_Open($dbFilename)

I am having some plugin issues, that I hope will soon be resolved so that this plugin will work properly.

Other than that all is well,

JS