Re: [sqlite] Need pointers for better build integration and support of schema updates

[Gary_Gabriel]

Hi Andreas,

Our application uses 3 Sqlite dbs and System.Data.Sqlite.dll (dropped by
installer). Functionality allows users to start a new instance of one of the
databases at run time. Hence we ship empty databases in a binary format,
created by one of our developers. The process of making changes to the DDL
is basically using a sqlite admin tool, make the changes, then update the
installer with the new db file.  I am looking for a better way how to
automate this and have better source control integration.  At my other job,
we got MS SQL ddl source files as text files in source control, verify them
at build time, and then ship the scripts.  

 


I would like accomplish these (or at least some of these) for the Sqlite
dbs:
- [R1] text files for sql in source control
- [R2] build them from command line during msbuild for syntax verification
- ship text files in the installer (I know how to do that)
- [R3]run the text files against a newly created db at run time in order
"install" the schema and data (I think this is just a simple execute the sql
ddl agains the db, right?)
- [R4] support schema updates.  If V2 of the product ships, all dbs that are
older should automatically be migrated to V2 when loaded.  Is this as simple
as writing the ddl as IF COLUMN EXISTS etc. statements?
- [R5] version detection. At run time need to detect that a given db is of
version v1. simple column in dbversion table?

  


TCL automates the installation of a dev schema or DDL schema version in 
a production database. The script generates an empty version_db from the 
new, development db schema. The script attaches the version_db to the 
production database production_db. It copies the data from the 
production db  into the new, empty version_db.   Using SQL statements of 
INSERT SELECT copies the data. After version_db has the new data; SQL 
queries recursively validate the data using SQL statements. Finally an 
execution of the SQLite3 CLI checks the version_db integrity to avoid 
any generation errors. The automation script does:

- Backup dev_db and production_db using SQLite3 Backup API.
- Use SQLite3 CLI .schema command to generate and backup the schemas for 
dev_db and production_db.
- Attach version_db to production_db to copy the production data to the 
new db.
- Recursively INSERT SELECT the data from each table in the 
production_db to the version_db.

- Validate and check the data in each table using natural keys.
- Use SQLite3 CLI to check the integrity of version_db.


The schemas generated satisfy [R1].
Use SQLite3 CLI to complete [R2].
[R3] is the TCL automation script. You are correct in "simple execute 
the sql ddl against the db".

The TCL script should also complete [R4].
[R5] To quote the mailing list "PRAGMA user_version is intended for this 
very purpose."


I added a further comment of explanation below that does not disturb the 
flow.


I would be interested in what installer and how you install in [R2].

Good luck

- Gary Gabriel

Explanation [R1]. Originally the script queried sqlite_master for the 
metadata to generate the schema. Avoiding using the CLI would decrease 
dependencies. However the query generated differently formatted schemas 
for the two dbs. They were not exactly the same and required further 
processing. The CLI generates matching schemas for a good diff.
[R4] INSERT SELECT. It took some work to find SQL INSERT SELECT 
statements to populate the tables that would also validate.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syncing databases on different servers

[Gary_Gabriel]

Hi Ian,

> I do not need instant syncing of SQLite databases, however I do have
>  a table stored on server X which I would like to gradually send 
> changes of to various other servers connected by TCP.
> 
> In the first instance, I'd be quite happy to send rows with ids which
>  do not exist in the other servers' tables, and not worry about 
> changes to rows

Here are two methods that you may consider, they are suitable to send
new rows or sync entry value changes.

1) The first is a standard logging method. To make an example of a schema:
- There are three tables: SearchIndex, ThreadSession and
ThreadIndex. Each table has three indexing columns or fields. Indexing
in this case means if the entry value changes, then the new information
in this row should be synced with the server. There are other columns 
but their content is further desciption relative to the indexing columns 
in the table.
- INSERT, UPDATE and DELETE triggers monitor the 3 indexing columns
in each table using the LIKE operator. A change in the entry value of
the indexing fields fires the trigger which inserts a record in a
logging table with the table name and Integer Primary Key. Joining the
data table with the log table generates the row containing the current
values.
- Use the generated row to sync the dbs.
- This method has been heavily exercised and tested over consider time
and proves to be reliable. Using the LIKE operator in this scenario has
not been detrimental to performance and accurate.

2) Method 2 uses temporary tables to hold the queried values in a user
session. The same tables as in 1) are used in this example. One 
temporary table holds the query result rows for each table. So for the 
tables SearchIndex, ThreadSession and ThreadIndex there are also
SearchIndex_temp, ThreadSession_temp and ThreadIndex_temp. In addition
one column flags changes based on the same criteria as 1). If the
contents of one of the three indexing columns or fields in the temp
tables changes, then the temp row is flagged.
- In the three tables DELETE the rows that have not changed and are not
flagged and what remains can be used to sync the changes.


- Gary Gabriel



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (UML) Visualisation of a SQLite schema ?

[Gary_Gabriel]

Hi Fredrik,
> I'm looking for a tool that would generate a visualisation (UML?) of
> the SQLite database schema I'm using, with table constraints and
> references between tables if possible. Is there such a tool (for Mac)?
>   
If you are interested in doing something yourself- then this might be a
gentle reminder of a thread on the mailing list.
Subject: [sqlite] Creating directed graphs and simple examples To:
sqlite-users@sqlite.org From: Gary Briggs  Date: 15,
Mar 2011

Archive:  http://www.mail-archive.com/sqlite-users@sqlite.org/msg59602.html

Graphviz: http://www.graphviz.org/

As you probably know Graphviz integrates into Tcl/Tk with TclDot which
is available for Posix.

Good luck with your search- Gary Gabriel






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to read schema file into database with tcl

[Gary_Gabriel]

Hi mweiguo,
> hi all:
> i am writing a tcl script, it will create a empty database and read a
> schema file into database ...
> in command line, it works when i use
>   
>>> .read schema.sql
>>>   
> but in tcl script enviroment, how can i do that ?
>   
This script reads the *.sql file as a transaction and executes it in 
Tcl. It reliably works and has been tested with a > 500 line .sql 
transaction.

proc tssScript { } {
 variable tssfile ; # database defined schema in sql file
 
set fid [open $tssfile]
 set tsscontent [read $fid]
close $fid
   
# Open process db1 connection
sqlite3 db1 $dbfile
   
# Read the SQLExe file contents to exec the SQL in the file. 
Write the SQL statements to the file delimited by semicolon
sqlite3 db $dbfile
db1 eval [subst {$tsscontent}] {
}
# test reading the file   
set tempq [db1 eval {SELECT * FROM SearchIndex_temp;}]
puts "tempq $tempq"
   
   db1 eval {INSERT INTO TABLE (VALUES);}
db1 eval {INSERT INTO TABLE (VALUES_2);}   
  
set tempq1 [db1 eval {SELECT * FROM TABLE;}]
puts "tempq1 $tempq"
   
db1 close
}

A Tcl equivalent to .read schema.sql.

Good luck- Gary
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite server

[Gary_Gabriel]

>> Thanks for that, and the analysis that preceded it.  That was my guess,
>> especially point 1, the most significant thing.  
>> Simon, I read both your suggestion and the Richard's good explanation about
>> network problems. I think that the idea still deserves to live in some form
>> :). I sometimes access sqlite db on a remote computer accessed with sqlite
>> shell executed in telnet/ssh. It works and it looks like good design for
>> interaction in terms of network bandwidth. If it can be implemented in more
>> friendly way toward the developer, I think it would still be a lighter
>> version of a sql-aware dbms than SQLite/PostgreSQL. Another thought, imagine
>> that a remote computer contains many sqlite databases and moreover, many
>> versions of the same database. It's easy to imagine this like hierarchy of
>> sqlite files accessed remotely, but I just can't imagine straightforward way
>> to implement this with conventional client/server dbms.
>> 
I agree with this concept and can place test results in the discussion. 
Profiling the application puts a sharper edge on the discussion. 
Consider the viewpoint of a small, flexible, highly skilled team 
dependent on prompt response. Assume that the communication profile 
tends to concentrate the information resources locally at each desktop. 
However team success depends on the ability to exchange focused 
information within a short response time. Further assume that much of 
the success depends on an accurate analysis of the information provided 
from the team members. Distribution of needed assets tends to be 
relatively even throughout the group without repetition..

Internet Messaging does not place information resources centrally; but 
enables a timely exchange between team members of filtered, focused 
assets. I have been testing SQLite under these requirements for the last 
two years with satisfying results. The tests confirm results reported in 
the list. The following article presents the Proof of Concept, and for 
the interested further information on implementation.

Progressing farther could employ concepts such as attaching to minimize 
needs of a client-server architecture while still fulfilling the 
profiled team needs. Team members could query local resources and 
prevent the problems of the db engine given in the previous example. 
Delving into the model as the article suggests shows that this 
architecture is scalable and does not rely on a network server. This 
model moves away from dependence on the network to dependence on 
intelligence to query local resources and share the results. I think 
this model has the adaptability to offer potential to the suppositions 
made by Max.

Search Session Threading based on Spiral Modeling for Internet 
Messaging  Scribd article: 

 


I've gotten a lot of good ideas from this friendly and helpful list. - 
Gary Gabriel





Internet Messaging has been in a test phase for the last two years using 
a small network with SQLite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Date format definition on bulk csv insert

[Gary_Gabriel]

Hi Albert,
> in the csv; the field is defined like:
>
> 2010.03.31 16:01:24.284  For a datetime field.
>
> I would like to tell sqlite that this is in 
> .mm.dd hh:mm:ss.sss
>
> How can I define this in sqlite's program so it imports the datetime
> correctly?
>   
I have used the SQLite C extension csv which works very nicely. It is a 
virtual table implementation. It is found in the sources and then 
compiled. You load the extension and it reads a file in csv format as a 
table. I use it for messaging data files for up to 30 Mbyte. It works 
with wrappers that load extensions in particular the Virtual Text 
extension: SQLite Expert; and Spatialite has a very nice implementation 
of the Virtual Text Module. For work of this type; an import table 
stores and manipulates the data. After manipulation; copy the data to 
another table for use. In this case another column would allow to 
reformat it if the import failed.

Because .csv format is useful as an application interface, 
programmatically I tried the Tcllib csv2Matrix which also works well. 
The code is short to read a csv file into a matrix and insert the matrix 
data into an SQLite table. Other scripting languages offer csv processing.

I do not know if these methods introduce performance limits in regards 
to the size of the import file.

Both methods offer the user the opportunity to define the csv format.

- Gary Gabriel



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Oracle connection

[Gary_Gabriel]

Simon Hax wrote:
> for clearification:
>
> the roots of the question:
> business needs; our client has a big Oracle infrastructure.
> Now they want, in relation to their infrastructure a litte App based on 
> SQlite.
>
> as I mentioned earlier:
> to copy data from an Oracle DB to another Oracle DB there is just one single 
> line necessary:
>
> import into mytable select a,b,c from remotetable remoteOracleDB
>
> somewhere in my local OracleDB I have to define a bit earlier a database link 
> with IP-adress, Port, SID UserID and passwort concerning the remote OracleDB.
> That's it.
>
> Are am I to lazy to implement by my self ? :  Yes and No
>
> I have implemented it within C# and ADO.net.
> Then it's not only one line of code, which is easy to read an easy to 
> maintain.
>   
Moving outside of the Oracle environment increases complexity (work).
> It's a lot more:
> The basic concept is a loop, suprise suprise !
> But you have to take into account that you have to convert every single 
> datatype,
> (e.g. DATE, there is a different representation Sqlite versus Oracle)
> you have to look for character-set and so on and so on.
>
> My wish:
> If someone else already has done this work 
> Thatfore I think an ODBC driver has to be implemented (there I am not an 
> expert) ...
>
> and a database Link to Oracle should be defineable by ATTACH (IP-Adress:Port, 
> OracleSID, UserId, Password)
>
> that would be heaven on earth.
>   
Hi Simon,

"I have implemented it within C# and ADO.net.
Then it's not only one line of code, which is easy to read an easy to maintain."

It is not clear does this refer to the Oracle installation or to SQLite? 
- If it refers to SQLite why is it not sufficient?

There's an SQLite ODBC driver that is proven and Googling has shown that 
it has been applied in Oracle installations in the past:

Christian Werner: SQLite ODBC Driver 
. http://www.ch-werner.de/sqliteodbc/

Using the ODBC driver
- Query the SQLite app table for the data and put it into a temporary 
table. Use a temporary table or memory db to make the management and 
querying easier. Install the ODBC driver and you can query the SQLite 
database over the ODBC connection. Christian Werner's instructions are 
sufficient. Past Googling informs of satisfactory Oracle ODBC support. 
Query the SQLite db using ODBC as it is then an Oracle resource.
- You might want to start formating the data in this table during this 
session.
- Use the ODBC connection with the local Oracle(local) db and pass the 
data in the temp table to Oracle(local)
- The IP link between the local and remote Oracle dbs should be 
transparent and enable passing the (SQLite) data which has now become 
native. Between the Oracle(remote) and Oracle(local); it is an Oracle task.

Working a bit more conceptually use the SQLite C .csv extension.
- After compiling the extension; load it.
- Query the SQLite app table for the data and put it into a table 
generated by the extension. This extension reads and writes from a .csv 
formatted file.
- Writing the data to this (extension) table after creating the table 
puts the table data into the .csv file. Oracle(local) reads the .csv 
table as it would a spreadsheet.

To do it programmatically requires bindings for SQLite and Oracle, as 
Pavel pointed out.

Alternatively write the data in the temp table to a file and import the 
file data into Oracle(local)

- Gary Gabriel
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Oracle connection

[Gary_Gabriel]

Hi Pavel,

> Note: SQLite *API*, nobody says about databases. These words from
> press-release basically say that now Berkeley DB "back end" has
> SQLite's "front end", i.e. you can work with BDB database through the
> same set of functions as with SQLite database. But you cannot mix them
> with each other and of course you cannot mix both of them with Oracle
> database. It's 3 different technologies and if you want any bridges
> between them you have to write them yourself.
>   
You are correct. above is an interfacing and implementation issue of how 
to employ the API within the interfacing structure. Your answer below 
answered it adequately from the SQLite perspective.

But the post "Re: [sqlite] releasing EXCLUSIVE lock after writing dirty 
pages from the memory cache into the DB ?" [1]  presents a strategy 
expanding beyond the press release and offers a complementary 
perspective. It addresses the questions of supporting business teams and 
the infrastructure by collecting information assets from embedded 
databases. These questions include -> Where are the assets? What is the 
availability of the assets? How readily and easily can these assets be 
used? These questions are complementary to the SQLite perspective.

 After the announcements of Oracle on this list I visited the 
manufacturer site looking for qualified information on the integration 
strategy, product and implementation. Information retrieval was 
difficult and as this is not a priority issue for my projects, I passed 
on without qualified technical information that would focus my 
interpretation. I am sure that the info is there, but I left with only 
an overview and interpretation and limited knowledge of the intents for 
interfacing and implementation that could provide guidance. Oracle 
information and the post cited below seem to indicate that the 
integration goes beyond the API and enhances performance.  There are a 
number of other platforms moving in the direction of providing backend 
or networking SQLite platforms and most offer strategic information 
sufficient for preliminary qualification.

- Gary Gabriel

[1] [sqlite] releasing EXCLUSIVE lock after writing dirty pages from the 
memory cache into the DB ? 28.4.2010.  
http://article.gmane.org/gmane.comp.db.sqlite.general/56055/match=releasing+exclusive+lock+after+writing+dirty+pages+memory+cache+into+db

> @Simon: to be honest I don't understand where the roots of the
> question are. Either you're too lazy to implement it yourself or for
> some reason you believe that SQLite could do some magic and could
> transfer data from Oracle without copying anything into local memory.
> That's impossible, computers don't work that way. If you know how to
> work with Oracle and how to work with SQLite then you can quickly
> write your application to execute this kind of data transfer. The
> pseudo code for that follows:
>
> issue select statement A to Oracle
> while there's record returned by statement
> issue insert statement B to SQLite with data retrieved from Oracle
> move to the next row in statement A
> end of while
>
> I'm pretty sure that if SQLite could do what you want, Simon, it would
> do it exactly like this.
>
>
> Pavel
>
> On Thu, Jun 3, 2010 at 2:59 PM, Gary_Gabriel
>  wrote:
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Oracle connection

[Gary_Gabriel]

Simon Hax wrote:
> I don't know what JET is.
> I testet ADO. That works.
> But it'is slow and you have to write some (to much) code.
>
> Much more better would be a solution like a direct LINK (like an Oracle 
> Database Link).
>
> as shown in my first email:
>
> insert into sqlitetable_columA select ColX from ta...@oracledb
>
> If you have 2 OracleDBs you can define a LINK from one OracleDB to the other,
> which allows you then to do .
Hi Simon, As you demonstrated in your email, it is relatively easy to 
remain in a homogeneous environment. Within this environment you should 
post any questions to the provider being Oracle. The provider interfaces 
the necessary connections. When these connections no longer suffice; 
external links may solve your problem.  But normally you have to acquire 
the know-how.

As you have not explained the reasons why Oracle cannot fulfill your 
requirements and why you are trying to link to SQLite; any answers 
remain superficial. SQLite attaches dbs very easily and in the manner 
you indicated. Use Attach. If Oracle and SQLite were compatible you 
could simply attach dbs.

As your example shows; Oracle and SQLite are two entirely different 
technologies offering diametrically opposed technologies. However it 
makes sense to connect these environments. Oracle has advertised on this 
list that Berkeley Db attaches to backend SQLite databases. Based on the 
Oracle concepts summarily presented; use Berkeley Db to connect Oracle 
to SQLite to achieve your targets if you cannot Attach. Unless you 
provide more information; this should keep you within homogeneous 
interfaces and fulfill your requirements. See the links at the bottom of 
this email for what Oracle advertises as solutions.

- Gary Gabriel

Oracle links from Oracle sources:

"As you may have already heard, the latest release of Oracle Berkeley DB 
now supports SQL, via integration with the SQLite API."

You can read the press release 
, review the 
documentation 
 
or download 
 
the release from the Oracle web site. You can get technical assistance 
on the Berkeley DB Forum 
 or directly 
from Oracle Support 

 




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cannot import blank columns in a Sqlite table

[Gary_Gabriel]

Hi,
am65 wrote:
> What is strange is that it used to do the import correctly. But from a
> certain point of time it started to ignore the blank columns.
>   
If you check the issues for SQLite Manager you will determine that ver. 
0.5.12- 0.5.14 did not insert properly. According to the change log this 
is fixed in ver. 0.5.15. As of yesterday Mozilla had not yet configured 
the automatic update for ver. 0.5.15, but it is available as .xpi on 
Google Code  . You should 
still be able to import, but this bug may affect it. The fact that it 
used to work and now causes problems may relate it to the bug.

- Gary Gabriel



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite template files

[Gary_Gabriel]

Hi Tom,
> One feature of this software is its ability to facilitate data entry in views 
> (ie not just tables). So I am especially interested in SQLite files that make 
> use of views, triggers, relationships etc. Some simple, some advanced.
>
> Can you direct me to were I might find some useful templates?
>   
I am completing an SQLite application that maybe of interest to you. It
builds temporary tables for user entry of Internet Messaging information
resources and assets. Views (will) play an useful role for user to
enable him to gain different perspectives of his stored information
resources and assets for use in project management and threading
information. These perspectives begin to put the resources into
relationships that provide potential to approach project management issues.

If you are interested, I don't feel comfortable discussing details on
this busy mailing list; send me an email with any questions or remarks
you may have. We can clarify the details and come back to the list later.

Let me know what you think- Gary Gabriel

BTW- if you haven't done so already; it may be of use to the user to add
extensions: VirtualText and Jean-Christophe Deschamps has an extension
for fuzzy search for example.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Alexey Pechnikov wrote:
> Original SQLite source tree has "ext " directory for extensions and I did
> place my extensions into this directory
>
> $ ls sqlite3-3.6.20/ext
> async  billing  compress  empty  env  fts1  fts2  fts3  functions  iconv  icu 
>  inet  key  md5  README.txt  rtree  tablefunc  undo  uuid  versioning  
> virtualtext
>
>   
Ok! That's good to know. Now I understand better.

Based on your comments I reversed the changes to determine the essential 
ones. Here's the one that fit's with your corrections and works:
Best. gcc -fPIC -lm -L../iconv/ -c -shared virtualtext.c -o 
libsqlitevirtualtext.so. The  -c command is essential.

Now I want to load the library. Spatialite doesn't cover loading the 
extension because it loads with the database. It starts with creating 
the Virtual Table.

Here is what I tried and the errors:
 SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .load libsqlitevirtualtext.so (also tried 
'libsqlitevirtualtext.so')
Error: %1 ist keine zulässige Win32-Anwendung.
 Query: SELECT load_extension('libsqlitevirtualtext.so'); - executeStep 
failed
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 
0x80004005 (NS_ERROR_FAILURE)
 SQLiteManager: Query: select load_extension('libsqlitevirtualtext.so', 
'auto_load'); - executeStep failed
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 
0x80004005 (NS_ERROR_FAILURE)

Thanks for help to move me along- Gary Gabriel





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Thanks Alexey,

Great response. I'll make the changes and get back to you.

Take care- Gary Gabriel


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Hi Alexey

Thanks for your input. I have tried variations of your suggestions, but
there are still questions about the errors and warnings that I would
like to clarify. I am still new at this so I maybe making some errors as
well, any suggestions would be appreciated.

Concluding test today: by placing virtualtext.c in the \iconc directory
together with these files then SQLite3 compiled and produced the *.exe
and .dll. But VirtualText did not compile.

Previous tests on the weekend: This test used two subdirectories where
VirtualText compiled, and SQLite3 did not compile. The best
configuration was as you suggested to put iconv in it's own subdirectory
so for your info the Msys configuration looks like:
home\ SQLite\virtualtext.c
iconv\iconv.h, iconv.c
In configuring iconv there were path errors so I changed the compiler
commands to:
gcc -fPIC -lm -shared -I ../iconv/hdr -c ../sqlite/virtualtext.c -o
libsqlitevirtualtext.so to eliminate the errors. The changes reference
this document: HOWTO Specify the Header File Include Path for use with
MinGW Compilers | MinGW MinGW Paths


Is it right that VirtualText compiles to
C:\msys\1.0\home\iconv\libsqlitevirtualtext.so and \iconv as well?

SQLite3 does not compile the *.dll, *.exe now. What needs to be changed
so that it compiles properly? The current errors follow below.

Thanks I appreciate your effort- Gary Gabriel

PS- the MinGw mailing list discussed placement of the -l option. Could
this be an issue?

Compile Iconv, VirtualText.
$ gcc -fPIC -lm -shared -I ../iconv/hdr -c ../sqlite/virtualtext.c -o
libsqlitevirtualtext.so
../sqlite/virtualtext.c:1: Warnung: -fPIC f├╝r Ziel ignoriert (der
gesamte Codeist positionsunabhängig)
../sqlite/virtualtext.c: In Funktion »text_clean_text«:
../sqlite/virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von
Ganzzahl ohne Typkonvertierung
../sqlite/virtualtext.c: In Funktion »text_parse«:
../sqlite/virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von
Ganzzahl ohne Typkonvertierung
Compile SQLite
$ gcc -O2 -DNDEBUG=1 -DTHREADSAFE=1 -DSQLITE_ENABLE_COLUMN_METADATA
-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_MEMORY_MANAGEMENT
-DSQLITE_ENABLE_RTREE -DSQLITE_DEFAULT_FILE_FORMAT=4 -c *.c
virtualtext.c: In Funktion »text_clean_text«:
virtualtext.c:328: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
virtualtext.c: In Funktion »text_parse«:
virtualtext.c:587: Warnung: Zuweisung erzeugt Zeiger von Ganzzahl ohne
Typkonvertierung
$ gcc -s -o sqlite3 *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'collect2: ld gab 1 als Ende-Status zur├╝ck
$ gcc -shared -s -o sqlite3.dll *.o
virtualtext.o:virtualtext.c:(.text+0x83a): undefined reference to
`iconvCreateUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0x94e): undefined reference to
`iconvFreeUTF8Converter'
virtualtext.o:virtualtext.c:(.text+0xd48): undefined reference to
`iconvConvertToUTF8'
virtualtext.o:virtualtext.c:(.text+0xfa5): undefined reference to
`iconvFreeUTF8Converter'collect2: ld gab 1 als Ende-Status zur├╝ck








___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Firefox SQLite Manager extension troubles.

[Gary_Gabriel]

Ted Rolle wrote:
> I'm using the Firefox SQLite Manager extension.
> I've renamed the database file.
> I get this message: 'The file does not exist anymore: E:\Documents and
> Settings\ted\Books\William R. Denslow\10'
> Well, duh.
> Where is the SQL database file name stored in the Firefox SQLite
>   
Hi Ted,

Go to Database -> Connect Database -> Select SQLite Database -> Browse 
to the new file.
It opens this SQLite file.

- Gary



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Compile Virtualtext extension

[Gary_Gabriel]

Hi Alexey thanks for the prompt answer. I saw your extension and tried 
it in the Spatialite GUI. The trial worked well in the GUI and it is 
useful for messaging environments. However I am still not sure what the 
recommended way of compiling it is.

- Should it be compiled when compiling SQLite or is it (normally) 
compiled alone afterwards?
- What is the order of compiling?
- First http://mobigroup.ru/files/sqlite-ext/iconv/ extension. Compile 
as gcc -fPIC -lm -shared ../iconv/iconv.c virtualtext.c -o 
libsqlitevirtualtext.so
- Virtualtext extension. Compile as gcc -fPIC -lm -shared virtualtext.c 
-o libsqlitevirtualtext.so.
- I assume that both *.c files are put into the SQLite directory for 
compiling. Does "../iconv/iconv.c" from the compile commands mean that 
iconv stores in a sub-directory?


Thanks for the clarifications- I'm looking forward to using it. - Gary

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Installing SQLite

[Gary_Gabriel]

Hi All;

I'm a newbie trying to get up to speed on SQL and SQLite and first- 
thanks for the product, and thanks for the group access and the chance 
to extensively learn. I agree with the following and would volunteer to 
document my learning experience. I used this thread as an opportunity to 
look around and found some interesting compilers to use for SQLite. I 
would take the opportunity to use collective experience and 
recommendations to choose and install the compiler under XP and build 
SQLite. Then to document it for Windows users. I would suggest 
submitting the docs for group comment, make any changes; and then making 
it available for use. I found some tutorials that look as if they make 
significant contributions.

If there is interest in working together to bundle the collective 
experience and document it, then there are general questions to discuss 
that would speed, and benefit the installation as well as supporting the 
documentation. This would start the work. I would follow with my 
questions and continue with any that the group complements.

Thanks again for so actively sharing experience to help-

Gary Gabriel

PS. As a short intro: I am not new to databases having developed, built 
and implemented a DOS relational database for sales and distribution 
management with DataPerfect that successfully drove a business for 20 
years. I am also not new to documentation. I applied the database above 
to Sales Cycle Management/ Internet Messaging and wrote a series of 
articles to develop the application:
Internet Messaging >>> 

  
Sales Cycle Management 


My goal is to replace DP with SQLite and more tightly integrate into 
Internet Messaging. I am in the process of re-desiging and testing using 
SQLite/ Perl. I am grateful for any helpful comments.

Vance E. Neff wrote:
> If there was a zip file that included an open source compiler and linker 
> and a well commented makefile along with SQLite's source code so that 
> anyone (at least under Windows) can generate the version SQLite dll and 
> command shell that they want without having to search for tools, I think 
> more people would be more likely to experiment with some of the non 
> standard features.
>
> Vance
>
> Jay A. Kreibich wrote:
>   
>>   Grrr I didn't meant to send this just yet.  But since I did, I
>>   guess I need to finish it.
>>
>> On Thu, Jul 23, 2009 at 10:50:37AM -0500, Jay A. Kreibich scratched on the 
>> wall:
>> 
>>> On Thu, Jul 23, 2009 at 09:46:24AM -0400, Wilson, Ron P scratched on the 
>>> wall:
>>>
>>>   
 I think the OP just has the wrong expectations.
 
>>>   Yes, and no.  While SQLite doesn't have a one-click-to-install
>>>   download, I have to agree that the current build and distribution
>>>   state of SQLite is... let's just say "less than ideal."  I've been
>>>   writing a lot of documentation on just this issue, and unless you
>>>   want a perfect vanilla install, there are definitely a lot of hoops
>>>   you have to jump through compared to most open-source projects of
>>>   similar design.
>>>   
>>>   A few versions ago we transitioned from a traditional UNIX style
>>>   project, complete with "configure" script, to having the amalgamation
>>>   be the "standard" distribution.  I've always felt like that
>>>   transition is incomplete, and we've never gotten back to where we
>>>   were before.
>>>
>>>   The amalgamation works well enough if what you want is mostly
>>>   defaults.  The issue is that, while you can change a few of the
>>>   #defines for numeric defaults, most of the more interesting build
>>>   options won't work with the amalgamation.  Only that's it.  As the
>>>   website clearly states, there is no other supported option.  
>>>   
>>   The "by the file" distribution is bad enough, but you're totally out
>>   of luck if you need to go to the tree for some of the really complex
>>   build options.  Of course, the "by the file" distribution is there
>>   and available for download because a lot of people still need it, but
>>   apparently not enough to justify keeping it updated.  That's a bit of 
>>   a contradiction... for a piece of software that prides itself on its
>>   testing systems, the end-users sees a whole lot of "there but not
>>   supported; it might work it might not; you're on your own" stuff.
>>   That's normally a big red flag in my book.  Stuff should be there, be
>>   supported, and be documented, or it shouldn't.  The current situation
>>   is only easily understandable if you've been following SQLite for a few
>>   years.
>>
>>   And the OP is right... the build docs suck.  There are no build docs
>>   for most downloads, just a archive file with source.  No Makefiles, no
>>   nothing.