[sqlite] whish list for 2016
Hi, All! It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. Presently it is generally useless for indexed databases which don't fit entirely in RAM (and which usually need checking much strongly than smaller ones). Valentin Davydov.
[sqlite] whish list for 2016
> On Dec 25, 2015, at 12:51 PM, Stephen Chrzanowski > wrote: > > *Part 2;* > > More along with your application style, but a complete database schema > overhaul, think of a contact form that allows for multiple methods of > communication. Multiple email addresses, multiple phone or fax numbers, can > all be associated to one contact. Typically you'd have a table sitting aside > with the contact type (email, phone, fax, maybe in the future Telepresence > ID?), and another table containing the actual data. You could adopt this > method to what you're describing. To add or delete fields to your UI > (Telpresence info doesn't exist in any common contact manager I know of), all > you'd have to do is add or delete rows to a table, and your application > written to adapt to random(?) changes to the field changes, regardless of > additions or deletions. > > All you'd need is one table that would hold the fields unique identifier, a > field title, the order in which it is displayed on the UI, and possibly a > default value field. > > Another table contains a unique identifier, a FK field pointing to the UID of > the above table, and the raw data. > > Your software would then make whatever required SELECT call to obtain the > required information and either store that data in a new temp table, or, > store the data in memory either via a stringlist or class, then render the > data to your UI using just this new data. This is called a dynamic database. One table holds the schema with one row for each field/column. Another table holds the data with one row for each field/column. Use joins to put the whole thing together. I used the scheme for an iOS app where the user can define how many fields there are for each "row" of the data they are saving. Like the Contacts app mentioned by Stephen. The trick is to maintain integrity. For example, when deleting a field be sure to delete all the data associated with that field. Use constraints on foreign keys to help during development (find those bugs early). Jeff
[sqlite] whish list for 2016
On 25 Dec 2015, at 12:39pm, Valentin Davydov wrote: > It would be desirable to improve algorithm of INTEGRITY_CHECK pragma. > Presently it is generally useless for indexed databases which don't fit > entirely in RAM (and which usually need checking much strongly than > smaller ones). Valentin, could you expand on that ? I'm guessing that you're saying that it's very slow but I may have missed your point. Also, do you know about PRAGMA schema.foreign_key_check; ? Does it have the same problem as far as you're concerned ? Simon.
[sqlite] Malloc getting segmentation fault in sqlite
Thanks for that.
I do have a simple stdcall dll that sits in between my VB6 ActiveX dll and
sqlite3.dll
This does nil else than simply things like this:
SQLITE3_STDCALL_API int __stdcall
sqlite3_stdcall_column_count(sqlite3_stmt* pStmt)
{
return sqlite3_column_count(pStmt);
}
So, that takes care of your point 1 and this works all perfectly fine.
> I think that using the AddressOf keyword won't really work with SQLite
in this context due to mismatched calling conventions (cdecl versus
stdcall).
This is interesting as I do use VB6 AddressOf to pass the pointer of the
UDF to SQLite.
It must be working OK though as I pass this pointer again via that stdcall
dll and also because there is no problem with only one UDF in a SQL
statement.
I have to mention that I do use a .tlb that sits in between my ActiveX dll
and that stdcall dll.
I don't think this is the problem, because it makes no difference if I do
instead plain Declares in my VB6 dll.
I take it your .net dll handles UDF's fine, even when there are 2 in one
SQL statement?
I agree it is all tricky, but apart from this particular UDF problem all
runs perfectly fine (both data producing SQL and non-data producing SQL)
and very fast as well. See also my reply to Mr Hipp.
RBS
On Thu, Dec 24, 2015 at 9:56 PM, Joe Mistachkin
wrote:
>
> It's been quite a long while since I used vb6 on a regular basis; however,
> integrating with native DLLs can be quite tricky for several reasons:
>
> 1. It cannot call any native function that does not conform to the
> "stdcall" calling convention.
>
> 2. It has a very Win32-centric way of marshalling data types.
>
> 3. Using 64-bit integers at all is somewhat tricky, IIRC. You may need
> to use a ByVal structure to pass them and I cannot remember how to use them
> as returned values.
>
> 4. Properly declaring and calling (e.g. using the ByVal keyword
> strategically) is critically important. Another issue is ANSI versus
> Unicode (UCS2 for COM) versus UTF-8 (SQLite) and knowing when to use which
> and how to convert between them (or marshal them).
>
> 5. Doing inbound callbacks to VB6 code is very very tricky, mostly due to
> [apartment] threading issues. I think that using the AddressOf keyword
> won't really work with SQLite in this context due to mismatched calling
> conventions (cdecl versus stdcall).
>
> I'll try looking for my old VB6 SQLite integration code when I have some
> spare cycles.
>
> Sent from my iPhone
>
> > On Dec 24, 2015, at 11:10 AM, Bart Smissaert
> wrote:
> >
> > OK, thanks
> > I don't use any of 1 to 3, I only use the standard SQLite functions such
> as
> > sqlite3_open_v2, sqlite3_prepare16_v2, sqlite3_step, sqlite3_bind,
> > sqlite3_column, sqlite3_create_function_v2, sqlite3_finalize,
> > sqlite3_reset, sqlite3_result, sqlite3_value and qlite3_close.
> > So, in that case I don't need sqlite3_free, sqlite3_malloc or
> > sqlite3_realloc, I take it.
> > Given that I don't use theses it then likely that my problem is to do
> with
> > a buffer overwrite?
> >
> > RBS
> >
> >
> >
> >> On Thu, Dec 24, 2015 at 8:35 PM, Richard Hipp wrote:
> >>
> >>> On 12/24/15, Bart Smissaert wrote:
> >>> My question is if there is ever any need in this situation to run one
> of
> >>> the sqlite3 memory procedures, that is
> >>> sqlite3_free, sqlite3_malloc or sqlite3_realloc?
> >>> Currently I am not using this anywhere in my VB6 code.
> >>> Should I?
> >>
> >> Cases when you might use sqlite3_malloc():
> >>
> >> (1) You are using on of SQLite's built-in memory allocators. The
> >> built-in memory allocators are disabled unless you use certain
> >> compile-time options. And even then, you have to turn them on using
> >> sqlite3_config(SQLITE_CONFIG_HEAP,...).
> >>
> >> (2) You need to use sqlite3_msize().
> >>
> >> (3) If you use sqlite3_mprintf(), then sqlite3_free() must be used to
> >> release the string once you are done with it.
> >>
> >> Otherwise, there is no real advantage to using SQLite memory allocator
> >> interface in place of your standards system memory allocator.
> >>
> >> --
> >> D. Richard Hipp
> >> drh at sqlite.org
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Malloc getting segmentation fault in sqlite
Thanks for clearing that up.
I know all this has very little to do with SQLite, but people using SQLite
with VB6 or VBA might be interested in this.
What puzzles me that all is perfectly fine when the SQL statement has only
one UDF in it, but there is a serious problem
when there are two in that same statement.
I made a simple procedure that finds string x in string y and clears that
string y, starting at the point where string x occurs:
Sub ClearStartAtFixedStringX(ByVal lPtr_ObjContext As Long, _
ByVal lArgCount As Long, _
ByVal lPtr_ObjSQLite3_Value As Long)
Dim i As Long
Dim lPtr1 As Long
Dim lPtr2 As Long
Dim lPos As Long
Dim lBytes1 As Long
Dim arrBytes1() As Byte
10 On Error GoTo ERROROUT
'field value to alter
'
20 lPtr1 = MemLong(lPtr_ObjSQLite3_Value)
30 LogData strLogFile, vbCrLf & "ClearStartAtFixedStringX line 20,
lPtr1: " & lPtr1
40 lBytes1 = sqlite3_value_bytes(lPtr1)
50 If lBytes1 = 0 Then
60sqlite3_result_null lPtr_ObjContext
70Exit Sub
80 End If
90 LogData strLogFile, "ClearStartAtFixedStringX line 90, lBytes1: " &
lBytes1
100 lPtr2 = sqlite3_value_text(lPtr1)
110 LogData strLogFile, "ClearStartAtFixedStringX line 110, lPtr2: " &
lPtr2
120 ReDim arrBytes1(lBytes1 - 1) As Byte
'just CopyMemory API might be faster here
130 For i = 0 To lBytes1 - 1
140 arrBytes1(i) = MemByte(lPtr2 + i)
150 Next i
'this shows that for normal ANSI characters we have one byte
'per character here for the SQLite UTF-8 string
'---
160 LogData strLogFile, "ClearStartAtFixedStringX line 160,
BytesAsString(arrBytes1): " & _
BytesAsString(arrBytes1)
170 If bDoneArrBytes2 = False Then
'this will fill up arrBytes2 and set lBytes2
'as the string to find is fixed we only need to do this once
'---
180 On Error GoTo 0
190 MakeArrBytes2 lPtr_ObjSQLite3_Value + 4
200 On Error GoTo ERROROUT
210 End If
220 On Error GoTo 0
230 lPos = GetStringPosB(arrBytes1, arrBytes2, False)
240 On Error GoTo ERROROUT
250 LogData strLogFile, "ClearStartAtFixedStringX line 250, lPos: " &
lPos
'string not found, so return original field string
'-
260 If lPos = 0 Then
270 sqlite3_result_value lPtr_ObjContext, lPtr1
280 Exit Sub
290 End If
'lPos -1 because if string found at byte position 2 then we only
want one byte
'-
300 LogData strLogFile, "ClearStartAtFixedStringX line 250,
VarPtr(arrBytes1(0)): " & _
VarPtr(arrBytes1(0))
310 sqlite3_result_text lPtr_ObjContext, VarPtr(arrBytes1(0)), lPos -
1, SQLITE_TRANSIENT
320 LogData strLogFile, "ClearStartAtFixedStringX line 310 (last line
in UDF), error message: " & _
cSQL.PointerToString(sqlite3_errmsg(lDBHandle))
& vbCrLf
330 Exit Sub
ERROROUT:
340 cMsgBoxClass.MsgBoxDLL oExcel, _
Err.Description, _
"ClearStartAtFixedStringX error at line " &
Erl, _
lFormColour:=ColourMainForm,
lButtonColour:=ColourButtons, _
lActiveButtonColour:=ColourMsgBoxActiveButton
End Sub
I am testing this on a table with a text field holding this string:
a(((bcdefghijklmnopqrstuvwyz
This string data comes from Excel, so they are Unicode strings, 2 bytes per
character.
They are stored in SQLite as UTF-8.
Now if I run this SQL:
SELECT
ClearStartAtFixedStringX(FIELD1,'(((') as x1
FROM
CLEAR_FROM
Then all is perfectly fine, so it will produce the string: a
But when I run instead this SQL:
SELECT
ClearStartAtFixedStringX(FIELD1,'(((') as x1,
ClearStartAtFixedStringX(FIELD1,'(((') as x2
FROM
CLEAR_FROM
I get a crash, producing the following message in Excel:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 12.0.6739.5000
Application Timestamp: 5643f6e7
Fault Module Name: sqlite3.dll
Fault Module Version: 3.9.2.0
Fault Module Timestamp: 5637af4b
Exception Code: c005
Exception Offset: 0005a319
OS Version: 6.1.7601.2.1.0.256.48
Locale ID: 2057
This crash happens directly after the second run of the above callback
procedure, although that procedure
runs to full completion with no errors.
My own debug output (LogData strLogFile etc.) up to that point is all fine:
ClearStartAtFixedStringX line 20, lPtr1: 33418304
ClearStartAtFixedStringX line 90, lBytes1: 28
ClearStartAtFixedStringX line 110, lPtr2: 33539976
ClearStartAtFixedStringX line 160,
[sqlite] whish list for 2016
On 25 Dec 2015, at 2:24am, John McKown wrote: > DELETE is normally done as: ALTER TABLE table-name DROP ?COLUMN > column_file_name; and would be a very nice addition. I hadn't noticed that > it is missing. I wonder why. SQlite stores all the data for a row together in column order. col1, col2, col3, col4 ... If someone dropped column 3 then there are two ways to handle this internally: A) Make a note that col3 no longer exists by changing its name to something unusable and changing its affinity to "DELETED". Existing table data stays the way it is. New rows inserted into that table get a NULL value in that column. Extremely fast but the database now takes up more space than it needs to. B) Do something like the above but then immediately VACUUM that table. No longer extremely fast but now the database file is smaller. Doing this would involve writing code which would implement a selective form of the VACUUM command: VACUUM [schema.]tablename which VACUUMs just that one table. Which might be another good reason to go this way since that could be useful just by itself. C) Choose to do either (A) or (B) depending on a PRAGMA setting. Or maybe just look at the setting of PRAGMA auto_vacuum. The above isn't hard to do. What makes "ALTER TABLE table-name DROP ?COLUMN" hard is checking the schema to make sure that nothing in the schema refers to the dropped column. That column you dropped might be in a CHECK constraint, or a FOREIGN KEY or an index or probably a bunch of things not coming to mind right now. Figuring that out requires SQLite to parse the master table down to the level of column names. That's difficult. Simon.
[sqlite] whish list for 2016
On 25 Dec 2015, at 2:35am, Bernardo Sulzbach wrote: >> ALTER TABLE table-name RENAME COLUMN column_field_name TO >> new_column_field_name; > > Are you sure? The documentation does not have anything about this and > I get a syntax error using 3.9.2 (a bit outdated, I know). John's confused. The ALTER table RENAME command is for renaming tables, not columns. Simon.
[sqlite] whish list for 2016
On Fri, Dec 25, 2015 at 12:24 AM, John McKown wrote: > On Thu, Dec 24, 2015 at 4:09 PM, Christian Schmitz < > realbasiclists at monkeybreadsoftware.de> wrote: > >> Hi, >> >> better ALTER command would be very welcome. >> >> e.g. RENAME/DELETE column or field. >> > > RENAME exists. > > ALTER TABLE table-name RENAME COLUMN column_field_name TO > new_column_field_name; > Are you sure? The documentation does not have anything about this and I get a syntax error using 3.9.2 (a bit outdated, I know). Anyway, if you are right, the documentation likely should be updated. -- Bernardo Sulzbach
